查询员工的年薪(字段可以参与数学运算)
select ename ,sal*12+ifnull(comm,0)*12 as yearsal from emp;
mysql> select ename ,sal*12+ifnull(null,0)*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
查询工资等于5000的员工姓名
select ename ,sal from emp where sal = 5000;
mysql> select ename ,sal from emp where sal = 5000;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
查询smith的工资
select ename ,sal from emp where ename = 'SMITH';
查看表结构**
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
找出工资不等于3000的
mysql> select ename ,sal from emp where sal <> 3000;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)
找出工资在1100-3000的员工,包括1100与3000
select ename , sal from emp where sal > 1100 and sal <3000;
mysql> select ename , sal from emp where sal > 1100 and sal <3000;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
找出工资在1100-3000的员工,包括1100与3000(between)
select ename ,sal from emp where sal between 1100 and 3000;
mysql> select ename ,sal from emp where sal between 1100 and 3000;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
找出员工没有津贴或津贴为0的(is null is not null)
select ename , comm from emp where comm = 0 or comm is null;
mysql> select ename , comm from emp where comm = 0 or comm is null;
+--------+------+
| ename | comm |
+--------+------+
| SMITH | NULL |
| JONES | NULL |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 0.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+------+
11 rows in set (0.00 sec)
找出工作岗位是MANAGER和SALESMAN的员工? in 等同于or
select ename ,job from emp where job = 'MANAGER' or job ='SALESMAN';
select ename ,job from emp where job in ('MANAGER','SALESMAN') ;
mysql> select ename ,job from emp where job in ('MANAGER','SALESMAN') ;
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
找出薪资大于3000的并且部门标号是20或30的员工。
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno =30);
mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno =30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+--------+---------+--------+
9 rows in set (0.00 sec)
案例:查找部门号不是’SALESMAN’, ‘MANAGER’
select ename ,job from emp where job not in ('MANAGER','SALESMAN') ;
mysql> select ename ,job from emp where job not in ('MANAGER','SALESMAN') ;
+--------+-----------+
| ename | job |
+--------+-----------+
| SMITH | CLERK |
| SCOTT | ANALYST |
| KING | PRESIDENT |
| ADAMS | CLERK |
| JAMES | CLERK |
| FORD | ANALYST |
| MILLER | CLERK |
+--------+-----------+
7 rows in set (0.00 sec)
找出名字当中第二个字母是A的(模糊查询)
select ename,sal from emp where ename like '_A%';
mysql> select ename,sal from emp where ename like '_A%';
+--------+---------+
| ename | sal |
+--------+---------+
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| JAMES | 950.00 |
+--------+---------+
3 rows in set (0.00 sec)
02
找出emp表中字段为ename,sal的字段,并且按照薪资值降序
select ename ,sal from emp order by sal desc;
mysql> select ename ,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
按照工资的降序排列,当工资相同的时候在按照名字的升序排列
select ename, sal from emp order by sal desc,ename asc;
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排序列
select ename ,job ,sal from emp where job = 'SALESMAN' order by sal desc;
mysql> select ename ,job ,sal from emp where job = 'SALESMAN' order by sal desc;
+--------+----------+---------+
| ename | job | sal |
+--------+----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
4 rows in set (0.00 sec)
计算每个员工的年薪ifnullifnull(comm,0)
select ename , sal,comm,(sal+ifnull(comm,0))*12 yearsal from emp ;
mysql> select ename , sal,comm,(sal+ifnull(comm,0))*12 yearsal from emp ;
+--------+---------+---------+----------+
| ename | sal | comm | yearsal |
+--------+---------+---------+----------+
| SMITH | 800.00 | NULL | 9600.00 |
| ALLEN | 1600.00 | 300.00 | 22800.00 |
| WARD | 1250.00 | 500.00 | 21000.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| MARTIN | 1250.00 | 1400.00 | 31800.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| KING | 5000.00 | NULL | 60000.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)
统计薪资的总和
select sum(sal) from emp;
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
找出总人数
select count(*) from emp;
select count(ename) from emp;
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
1 row in set (0.00 sec)
select count(ifnull(comm,0)) from emp;
mysql> select count(ifnull(comm,0)) from emp;
+-----------------------+
| count(ifnull(comm,0)) |
+-----------------------+
| 14 |
+-----------------------+
1 row in set (0.00 sec)
找出工资高于平均工资的员工
select avg(sal) from emp;/*平均工资*/
select ename,sal from emp where sal > (select avg(sal) from emp);
mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
找出每个工作岗位的最高薪资 group by having
select job, max(sal) from emp group by job;
ysql> select job, max(sal) from emp group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| CLERK | 1300.00 |
| SALESMAN | 1600.00 |
| MANAGER | 2975.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
找出每个部门不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno ,job order by deptno asc;
mysql> select deptno,job,max(sal) from emp group by deptno ,job order by deptno asc;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
案例:找出每个部门的最高薪资,要求显示薪资大于2500的数据。
select deptno,max(sal) from emp group by deptno having max(sal) > 2500;
mysql> select deptno,max(sal) from emp group by deptno having max(sal) > 2500;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.00 sec)
案例:找出每个部门的平均薪资,要求显示薪资大于2000的数据。
select deptno ,avg(sal) from emp group by deptno having avg(sal) >2000;
mysql> select deptno ,avg(sal) from emp group by deptno having avg(sal) >2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
语句 | 执行内容 | 执行优先级 |
---|---|---|
select … | 显示那些字段 | 5 |
from … | 来自于那张表 | 1 |
where … | 查找条件 | 2 |
group by … | 按什么分组 | 3 |
having … | 分组二次过滤 | 4 |
order by … | 按什么顺序排列 | 6 |
三
案例:统计岗位的数量。 去重 distinct
select count(distinct job) from emp;
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.10 sec)
连接查询分类:
内连接 :等值连接,非等值连接,自连接
外连接:左外连接,右外连接
全连接
如何避免笛卡尔积现象
添加过滤条件,但是不会减少匹配次数
案例:找出每一个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
select e1.ename '员工名', e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
mysql> select e1.ename '员工名', e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
+--------+-------+
| 员工名 | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
少了一条数据,利用外连接可以写出来
案例:找出每个员工的上级领导
左外连接 | 右外连接 |
---|---|
select | select |
e1.ename ‘员工名’, e2.ename ‘领导名’ | e1.ename ‘员工名’, e2.ename ‘领导名’ |
from | from |
emp e1 | emp e2 |
left join | right join |
emp e2 | emp e1 |
on | on |
e1.mgr = e2.empno; | e1.mgr = e2.empno; |
外连接中,其实缺省了 outer,完全写法为 left/right outer join。 outer可以省略。
案例:找出每一个员工的部门名称以及工资等级。
select e.ename ,d.dname,s.grade
from
emp e join salgrade s
on e.sal between s.losal and s.hisal
join dept d
on e.deptno = d.deptno;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
案例:找出每一个员工的部门名称,工资等级,以及上级领导。
select e1.ename'员工姓名',d.dname '部门名称',s.grade'工资等级',e2.ename '上级领导'
from
emp e1
left join emp e2
on e1.mgr = e2.empno
join salgrade s
on e1.sal between s.losal and s.hisal
join dept d
on e1.deptno = d.deptno;
+----------+------------+----------+----------+
| 员工姓名 | 部门名称 | 工资等级 | 上级领导 |
+----------+------------+----------+----------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+----------+------------+----------+----------+