linux下mysql操作进阶

实战操作

• 创建数据库

create database bbb;

用数据库之前要指定

use bbb;

• 部门表

1. 创建部门表字段

create table dept (
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30));

2. 插入数据

insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');

image.png

• 职员表

1. 创建职员表字段

create table emp (
  empno int primary key,
  ename nvarchar(30),
  job nvarchar(30),
  mgr int,
  hiredate datetime,
  sal numeric(9,2),
  comm numeric(9,2),
  deptno int);

2. 插入数据

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

image.png

SQL操作

• 删除某字段记录

--删除雇员表中工资为2000和2500的记录
delete from emp where sal=2000 or sal=2500;

• 查询操作

1. 查询表

select * from emp;
select * from dept;

2. 查询列

--查询指定列
select ename from emp;

image.png

• MySQL 中where 的使用

1. 查询某一具体字段在SQL中不区分大小写

--查询SMITH的薪水、工作和所在部门
select sal,job,deptno from emp where ename='SMITH';--在sql server 中不区分大小写,在oracle中区分
select sal,job,deptno from emp where ename='SmiTH';--此语句和上一句效果一样
select sal,job,dname  from emp, dept where emp.ename='smITH' and emp.deptno=dept.deptno ;

image.png

2. 显示工资高于(>和>=区别)3000的员工

select ename from emp where sal>3000;
select ename from emp where sal>=3000;

3. 查找1982-1-1后入职的员工

select * from emp where hiredate>'1982-1-1;

image.png

4. 查找工资在2000-2500员工的情况
   方法一: > 和<

select * from emp where sal>2000 and sal<2500;

方法二: between包含边界即包含2000和2500

select * from emp where sal between 2000 and 2500;

image.png

5. 在where 条件中使用in
   如何显示empno为123,345,800的情况
   方法一:

select * from emp where empno=123 or empno=345 or empno=800;

方法二:

select * from emp where empno in(123,345,800,7782,7900);

image.png

6. 使用逻辑操作符号

--查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为j
select * from emp where (sal>500 or job='MANAGER') and ename like 'j%';

image.png

• MySQl中distinct的使用

1. 统计部门编号

select distinct deptno from emp;

image.png

• MySQL中like的使用

1. 查询首字母为s的员工和员工工资

select ename,sal from emp where ename like 's%';--%表示0到多个字符

image.png

2. 查询第三个字母为o的员工姓名和工资

select ename,sal from emp where ename like '__o%';--_(下划线)表示单个字符 

image.png

• MySQL排序-----order by

1. order by操作 asc升序排列

--按照工资从低到高的顺序显示员工的信息
select * from emp order by sal asc; --从低到高排列,在此处asc写不写都可以，因为默认就是asc
select * from emp order by sal desc;--从高到低排列

image.png
image.png

2. order by操作 desc降序排列

--按照入职先后顺序排列  默认就是asc
select * from emp order by hiredate;

image.png

3. order by根据不同字段排序，先排第一个字段，再排第二个字段

--按照部门号升序而雇员工资降序排列
select * from emp order by deptno,sal desc;

image.png

4. order by和ifnull组合使用

--使用列的别名，年薪排序，->>先把年薪算出来，再排序
select ename,sal*13+ifnull(comm,0)*13 年薪 from emp order by 年薪 desc;

image.png

• MySQL GROUPBY语句
  group by语句根据一个或多个列对结果集进行分组，在分组的列上可以使用COUNT、SUM、AVG等函数

1. count和group by结合使用

-- 统计每个部门多少人
select count(*) from emp group by deptno;

image.png

复杂查询

• max和min的实际用法

--1、如何显示所有员工中的最高工资和最低工资？
select max(sal) 最高工资 ,min(sal) 最低工资 from emp;

image.png

• avg()和sum()的实际用法

--2、显示所有员工的平均工资和工资总和？
select avg(sal) 平均工资 ,sum(sal) 工资总和 from emp;

image.png

• count()的实际用法

--3、计算共有多少员工？
select count(empno) 员工数量 from emp;
select count(*) 员工数量 from emp;

image.png

• SQL语句执行顺序

--4、显示工资最高的员工的姓名、工作岗位？--SQL语句执行时是从右到左
select ename,job from emp where sal=(select max(sal) from emp);

image.png

--5、显示工资高于平均工资的员工信息？
select ename,sal from emp where sal>(select avg(sal) from emp);

image.png

• group by用于对查询的结果分组统计

1. 显示每个部门的平均工资和最高工资？

select avg(sal) 平均工资,max(sal) 最高工资,deptno from emp group by deptno;

image.png

2. 显示每个部门的平均工资和最高工资？并显示部门名称

--如何显示部门名称  多表查询
select avg(sal) 平均工资,max(sal) 最高工资,dname,emp.deptno from emp,dept 
 where emp.deptno=dept.deptno group by emp.deptno;

image.png

3. 如何显示每个部门的每种岗位的平均工资和最低工资？

select avg(sal) 平均工资,min(sal) 最低工资,deptno,job from emp group by deptno,job order by deptno;

image.png

• having和group by结合使用，对分组查询到的结果进行筛选

显示平均工资低于2000的部门和它的平均工资？

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000

image.png

注意:如果在select 语句中同时包含有group by、having、order by 那么它们的顺序是 group by、having、order by 。

• 复杂查询
  多表匹配原理笛卡尔集，从右往左匹配

1. 如果两张表都有相同的字段，则需要带表名（或表的别名） ->不是相同的字段，表名可带可不带

--显示雇员名、雇员工资和其所在部门的名字，部门号 <下两句话效果一样>
select emp.ename,emp.sal,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno
select ename,sal,dname,dept.deptno from emp,dept where emp.deptno=dept.deptno

image.png

2. 用表的别名代替表名(在表名较长较复杂时)

select ename,sal,dname,d.deptno from emp e,dept d where e.deptno=d.deptno

image.png

3. 显示sales部门位置和其员工的姓名

select dept.loc,emp.ename from dept,emp where dept.dname='sales' and emp.deptno=dept.deptno 

image.png

4. 如何显示部门号为10的部门名字、员工名和工资

select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno=10 and emp.deptno=dept.deptno

image.png

5. 显示雇员名，雇员工资及所在部门的名字，并按部门排序

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dept.dname 

image.png

6. 显示某个员工的上级领导的姓名->比如显示‘ford’的上级

首先知道'frod'的上级编号
select mgr from emp where ename='ford'
select ename from emp where empno=(select mgr from emp where ename='ford')

image.png

7. 如何显示公司每个员工名字和他上级的名字

把emp看作两张表 worker 和 boss
select worker.ename 员工,boss.ename 上级 from emp worker,emp boss where boss.empno=worker.mgr

image.png

• 子查询
  单行子查询用 =，多行子查询用 in

1. 单行子查询

如何查找与’smith’同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='smith')
select * from emp where deptno in (select deptno from emp where ename='smith')

image.png image.png

2. 多行子查询

如何查询和部门10的工作相同的雇员的名字，岗位、工资、部门号  ->>如何排除10号部门?
select ename,job,sal,deptno from emp 
where job in (select distinct job from emp where deptno=10) 
and deptno<>10  --在此语句中把 in 换为 = 会出错
--不等号 <> 换为 != 也可以

image.png

3. 在from子句中使用子查询
   如何显示高于部门平均工资的员工姓名、工资、部门平均工资

3.1、先查找部门平均工资
select avg(sal),emp.deptno aa from emp group by deptno

image.png

3.2、把上面的查询结果当作一张临时表对待
--查找高于部门平均工资员工姓名、工资、部门平均工资、部门编号
select emp.ename,emp.sal,qq.aa,emp.deptno from emp,
(select avg(sal) aa,emp.deptno from emp group by deptno) qq --qq为别名->>必须取
where emp.sal>qq.aa and emp.deptno=qq.deptno

image.png

• MySQL NULL 值处理

1. 使用is null操作符

select * from emp where mgr is null;--千万不能等于即mgr='null'

image.png

2. 显示每个雇员的年工资=13个月工资+奖金
   ifnull(comm,0),判别为null，为null返回0，不为null返回本身数值
   年工资是指定的列的别名
   别名即年工资 加不加单引号 ，或双引号 ，或啥也不加 都可以,即下面三句话效果一样

select ename,sal*13+ifnull(comm,0)*13 年工资 from emp;
select ename,sal*13+ifnull(comm,0)*13 '年工资' from emp;
select ename,sal*13+ifnull(comm,0)*13 "年工资" from emp;

image.png

3. isnull && is not null

isnull(exper) 判断exper是否为空，是则返回1 ，否则返回0
is not null(exper) 判断exper是否为空，否则返回1 ，是则返回0

4. isnull VS ifnull

isnull(exper) 判断exper是否为空，是则返回1 ，否则返回0
ifnull(exper1,exper2) 判断exper1是否为空，是则用exper2代替

• MySQL连接的使用

1. 内连接:显示左右两边共有的

显示每个员工和他上级的名字
select worker.ename 员工, boss.ename 上级 from emp worker, emp boss where worker.mgr=boss.empno

image.png

2. 左连接:左边有的，右边没有的为null

显示每个员工和他上级的名字，要求没有上级的人的名字也要显示出来
select worker.ename 员工, boss.ename 上级 from emp worker left join emp boss on worker.mgr=boss.empno

image.png

3. 右连接:右边有的，左边没有的为null

显示每个员工和他上级的名字，要求没有下级的人的名字也要显示出来
select worker.ename 员工, boss.ename 上级 from emp worker right join emp boss on worker.mgr=boss.empno

image.png