MySQL学习笔记(3) 深藏阁楼爱情的钟 2022-08-28 04:56 117阅读 0赞 连接查询分类 1. 内连接 (1)等值连接:等号连接 (2)非等值连接:除等号之外的操作符连接 (3)自连接:一张表当作多张表来连接 2. 外连接 (1)左外连接:左表为主表,右表可能为null (2)右外连接:右表为主表,左表可能为null 3. 全连接 内连接和外连接的区别是: 1. 内连接:只显示两个表都匹配的数据(使用内连接会导致部分数据丢失) 2. 左外连接:显示左表的全部数据(不管两个表是否匹配),右表中没有匹配的数据用null 3. 右外连接:显示右表的全部数据(不管两个表是否匹配),左表中没有匹配的数据用null > 1. 等值连接 // 查询每个员工的部门名 // inner可以省略 mysql> select e.ename, e.deptno, d.dname -> from emp e -> inner join dept d -> on e.deptno = d.deptno; +--------+--------+------------+ | ename | deptno | dname | +--------+--------+------------+ | SMITH | 20 | RESEARCH | | ALLEN | 30 | SALES | | WARD | 30 | SALES | | JONES | 20 | RESEARCH | | MARTIN | 30 | SALES | | BLAKE | 30 | SALES | | CLARK | 10 | ACCOUNTING | | SCOTT | 20 | RESEARCH | | KING | 10 | ACCOUNTING | | TURNER | 30 | SALES | | ADAMS | 20 | RESEARCH | | JAMES | 30 | SALES | | FORD | 20 | RESEARCH | | MILLER | 10 | ACCOUNTING | +--------+--------+------------+ 14 rows in set (0.00 sec) > 1. 非等值连接 // 找出员工的工资等级 mysql> select e.ename, e.sal, s.grade -> from emp e -> inner 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) > 1. 自连接 // 找出每个员工的上级 // 有13条记录,因为king没有上级领导 mysql> select e1.ename, e2.ename as mgrname -> from emp e1 -> inner join emp e2 -> on e1.mgr = e2.empno; +--------+---------+ | ename | mgrname | +--------+---------+ | 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) > 1. 外连接 // 1. 找出所有员工的上级领导 mysql> select e1.ename, e2.ename as mgrname -> from emp e1 -> left join emp e2 -> on e1.mgr = e2.empno; +--------+---------+ | ename | mgrname | +--------+---------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+---------+ 14 rows in set (0.00 sec) // 2. 找出哪个部门没有员工 mysql> select d.* from dept d -> left join emp e -> on d.deptno = e.deptno -> where e.ename is null; +--------+------------+--------+ | DEPTNO | DNAME | LOC | +--------+------------+--------+ | 40 | OPERATIONS | BOSTON | +--------+------------+--------+ 1 row in set (0.00 sec) > 1. 三表查询 // 1. 找出每个员工的部门名称、工资等级和上级领导 mysql> select e.ename, d.dname, s.grade,ee.ename as mgrname -> from emp e -> join dept d -> on e.deptno = d.deptno -> join salgrade s -> on e.sal between s.losal and s.hisal -> left join emp ee -> on e.mgr = ee.empno; +--------+------------+-------+---------+ | ename | dname | grade | mgrname | +--------+------------+-------+---------+ | 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 | +--------+------------+-------+---------+ 14 rows in set (0.00 sec) > 1. 子查询 // 1. 查找高于平均工资的员工信息 // where后面嵌套子查询 mysql> select * -> from emp -> where sal > (select avg(sal) from emp); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 6 rows in set (0.00 sec) // 2. 找出每个部门的平均工资的工资等级 // from后面嵌套子查询 mysql> select a.*, s.grade -> from (select deptno, avg(sal) as avgsal from emp group by deptno) a -> join salgrade s -> on a.avgsal between s.losal and s.hisal; +--------+-------------+-------+ | deptno | avgsal | grade | +--------+-------------+-------+ | 20 | 2175.000000 | 4 | | 30 | 1566.666667 | 3 | | 10 | 2916.666667 | 4 | +--------+-------------+-------+ 3 rows in set (0.00 sec) // 3. 找出每个部门的平均工资等级 // 下面是错误示范 mysql> select t.deptno, avg(t.grade) -> from (select e.ename,e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t -> group by t.deptno; // 正确的写法如下:求出员工的工资等级之后直接分组即可求得等级平均值,不需要作再为一张表 mysql> select e.deptno, avg(s.grade) -> from emp e -> join salgrade s -> on e.sal between s.losal and s.hisal -> group by e.deptno; +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 20 | 2.8000 | | 30 | 2.5000 | | 10 | 3.6667 | +--------+--------------+ 3 rows in set (0.00 sec) > 1. union操作 // 查找岗位是salseman和manager的员工 // 第一种 mysql> select ename, job -> from emp -> where job = 'salesman' or job = 'manager'; // 第二种 mysql> select ename, job -> from emp -> where job in('salesman', 'manager'); // 第三种 mysql> select ename, job from emp where job = 'salesman' -> union -> select ename, job from emp where job = 'manager'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | MARTIN | SALESMAN | | TURNER | SALESMAN | | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | +--------+----------+ 7 rows in set (0.00 sec) > 1. limit操作 // 1. 取出工资前5名的员工 mysql> select ename, sal -> from emp -> order by sal desc -> limit 5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 5 rows in set (0.00 sec) // 2. 取出工资第4到第9的员工 mysql> select ename, sal -> from emp -> order by sal -> limit 3, 6; +--------+---------+ | ename | sal | +--------+---------+ | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | +--------+---------+ 6 rows in set (0.00 sec)
还没有评论,来说两句吧...