|
2023_10_10_MYSQL_DAY_02_笔记
- #在 FROM 子句中使用子查询
- SELECT a.ename, a.sal, a.deptno, b.salavg
- FROM emp a, (SELECT deptno, AVG(sal) salavg
- FROM emp
- GROUP BY deptno) b
- WHERE a.deptno = b.deptno AND a.sal > b.salavg;
- #子查询里的空值处理
- SELECT ename
- FROM emp
- WHERE empno NOT IN (SELECT IFNULL (mgr,0) FROM emp);
- #多列子查询
- SELECT deptno,ename,hiredate
- FROM emp
- WHERE (deptno,hiredate) IN (SELECT deptno ,MIN(hiredate)
- FROM emp
- GROUP BY deptno);
- #课堂练习41
- SELECT ename,job
- FROM emp
- WHERE job = ANY (SELECT job FROM emp WHERE deptno=10)
- AND deptno <> 10;
-
-
- #课堂练习40
- SELECT ename,hiredate
- FROM emp
- WHERE hiredate > ALL (SELECT hiredate FROM emp WHERE deptno=10)
- AND deptno <> 10;
- #课堂练习39
- SELECT ename,hiredate
- FROM emp
- WHERE hiredate > ANY (SELECT hiredate FROM emp WHERE deptno=10)
- AND deptno <> 10;
- #多行子查询 all操作符
- SELECT empno, ename, job, sal
- FROM emp
- WHERE sal > ALL (SELECT sal FROM emp WHERE deptno= 20)
- AND deptno <> 20;
- SELECT empno, ename, job, sal
- FROM emp
- WHERE sal < ALL (SELECT sal FROM emp WHERE deptno= 20)
- AND deptno <> 20;
- #多行子查询 any操作符
- SELECT ename,sal,empno
- FROM emp
- WHERE empno < ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
- SELECT ename,sal,empno
- FROM emp
- WHERE empno > ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
- SELECT ename,sal,empno
- FROM emp
- WHERE empno = ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
- #多行子查询 in操作符
- SELECT ename,sal
- FROM emp
- WHERE empno IN (SELECT mgr FROM emp);
- #课堂练习38
- SELECT deptno,AVG(sal)
- FROM emp
- GROUP BY deptno
- HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=10);
- #课堂练习37
- SELECT ename,sal,dname
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- AND sal > (SELECT sal FROM emp WHERE ename='SMITH')
- AND d.`loc`='CHICAGO';
- #课堂练习36
- SELECT ename,hiredate
- FROM emp
- WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
- # HAVING子句中使用子查询
- SELECT deptno, MIN(sal)
- FROM emp
- GROUP BY deptno
- HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 20);
- #子查询中使用组函数
- SELECT ename,job,sal
- FROM emp
- WHERE sal = (SELECT MIN(sal) FROM emp);
- #单行子查询语句
- SELECT ename,job
- FROM emp
- WHERE job = (SELECT job FROM emp WHERE empno=7369)
- AND sal > (SELECT sal FROM emp WHERE empno=7876);
- #子查询 单行子查询
- SELECT ename
- FROM emp
- WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
- #课堂练习35
- SELECT ename,hiredate,deptno
- FROM emp
- LIMIT 0,5; #第1页
- SELECT ename,hiredate,deptno
- FROM emp
- LIMIT 5,5; #第2页
- SELECT ename,hiredate,deptno
- FROM emp
- LIMIT 10,5; #第3页
- #课堂练习34
- SELECT e.deptno,dname,COUNT(*),AVG(sal)
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- GROUP BY e.deptno,dname
- HAVING COUNT(*) > 2 AND AVG(sal) > 2000
- ORDER BY COUNT(*);
- #七个子句的查询
- SELECT job,SUM(sal)
- FROM emp
- WHERE job NOT LIKE 'SALES%'
- GROUP BY job
- HAVING SUM(sal) > 5000
- ORDER BY SUM(sal)
- LIMIT 0,2;
- #分页查询
- SELECT empno,ename,deptno,sal
- FROM emp
- ORDER BY empno
- LIMIT 5,5; #第2页
- SELECT empno,ename,deptno,sal
- FROM emp
- ORDER BY empno
- LIMIT 0,5; #第1页
- #课堂练习33
- SELECT e.deptno,dname,COUNT(*)
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- GROUP BY e.deptno,dname
- HAVING COUNT(*) > 2;
- 总结:
- 1、WHERE子句用来过滤分组之前的记录,不能使用组函数
- 2、HAVING子句用来过滤分组之后的记录,可以使用组函数
- #6个子句的查询
- SELECT job,SUM(sal)
- FROM emp
- WHERE job NOT LIKE 'SALES%'
- GROUP BY job
- HAVING SUM(sal) > 5000
- ORDER BY SUM(sal);
- 查询语句执行过程:
- 1、通过FROM子句中找到需要查询的表;
- 2、通过WHERE子句进行非分组函数筛选判断;
- 3、通过GROUP BY子句完成分组操作;
- 4、通过HAVING子句完成组函数筛选判断;
- 5、通过SELECT子句选择显示的列或表达式及组函数;
- 6、通过ORDER BY子句进行排序操作。
- #HAVING 子句
- SELECT deptno,COUNT(empno)
- FROM emp
- GROUP BY deptno
- HAVING COUNT(empno) > 3;
- SELECT deptno,MAX(sal)
- FROM emp
- GROUP BY deptno
- HAVING MAX(sal) > 2900;
- #课堂练习32
- SELECT COUNT(*),MAX(sal),MIN(sal),d.`loc`
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno` AND d.`loc`='CHICAGO'
- GROUP BY d.`loc`;
- #课堂练习31 自连接
- SELECT e2.`empno` 经理编号,e2.`ename` 经理姓名,COUNT(*)
- FROM emp e,emp e2
- WHERE e.`mgr`=e2.`empno`
- GROUP BY e2.`empno`,e2.`ename`;
- #课堂练习30
- SELECT d.`deptno`,d.`dname`,e.`job`,COUNT(empno),MAX(sal),MIN(sal)
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- GROUP BY d.`deptno`,d.`dname`,e.`job`;
- #多表查询分组查询
- SELECT d.`deptno`,d.`dname`,COUNT(empno),MAX(sal)
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- GROUP BY d.`deptno`,d.`dname`;
- #按多列分组查询
- SELECT deptno,job,SUM(sal)
- FROM emp
- GROUP BY deptno,job;
- #分组子句
- SELECT job,COUNT(empno)
- FROM emp
- GROUP BY job;
- SELECT deptno,AVG(sal)
- FROM emp
- GROUP BY deptno;
- #课堂练习29
- SELECT COUNT(*),MAX(sal),MIN(sal)
- FROM emp
- WHERE deptno=30;
- #课堂练习28
- SELECT (sal+IFNULL(comm,0))*12 年收入
- FROM emp
- #课堂练习27
- SELECT COUNT(job), COUNT(DISTINCT job)
- FROM emp
- #课堂练习26
- SELECT SUM(sal),AVG(sal)
- FROM emp
- WHERE deptno=20;
- #除COUNT(*)之外,其它所有分组函数都会忽略列中的空值,然后再进行运算; IFNULL==空值处理函数
- SELECT AVG(comm),AVG(IFNULL(comm,0))
- FROM emp;
- #空值处理函数 IFNULL(comm,0)
- SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+IFNULL(comm,0) 总收入
- FROM emp;
- SELECT COUNT(deptno),COUNT(DISTINCT deptno)
- FROM emp
- #五个组函数--聚合函数
- SELECT MIN(hiredate),MAX(hiredate),MIN(sal),MAX(sal),AVG(sal),SUM(sal),COUNT(*)
- FROM emp;
- #课堂练习25
- SELECT e.empno,e.ename,e.`job`,d.deptno,d.`loc`
- FROM emp e,dept d
- WHERE e.deptno = d.deptno AND (d.`loc`='CHICAGO' OR job='MANAGER');
- #联合查询 UNION 去除重复数据
- SELECT e.empno,e.ename,d.deptno,d.dname
- FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
- UNION
- SELECT e.empno,e.ename,d.deptno,d.dname
- FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
- #联合查询 UNION ALL 保留重复数据
- SELECT e.empno,e.ename,d.deptno,d.dname
- FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
- UNION ALL
- SELECT e.empno,e.ename,d.deptno,d.dname
- FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
- #课堂练习24 右外连接 题目? 没有下属的也要查出来
- SELECT e.`ename` ,e2.`ename`
- FROM emp e RIGHT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
- #课堂练习23 左外连接 没有领导的也要查出来
- SELECT e.`ename` ,e2.`ename`
- FROM emp e LEFT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
- #课堂练习22
- SELECT ename,dname,hiredate
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno` AND e.`hiredate` > '1980-5-1'
- #课堂练习21 笛卡尔积数据
- SELECT ename,dname
- FROM emp,dept
- #右外连接
- SELECT e.`ename` ,e.`deptno`,d.`loc`
- FROM emp e RIGHT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
- #左外连接
- SELECT e.`ename` ,e.`deptno`,d.`loc`
- FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
- #等值连接 自然连接
- SELECT e.`ename` ,e.`deptno`,d.`loc`
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- #课堂练习20 自连接
- SELECT e.`ename` 员工姓名,e.`empno` 员工编号,e2.`ename` 经理姓名,e2.`empno` 经理编号
- FROM emp e,emp e2,dept d
- WHERE e.`mgr`=e2.`empno` AND e.`deptno`=d.`deptno` AND (d.`loc`='NEW YORK' OR d.`loc`='CHICAGO');
- #自连接
- SELECT e.`ename` 员工姓名,e2.`ename` 上级姓名
- FROM emp e,emp e2
- WHERE e.`mgr`=e2.`empno`
- #课堂练习19
- SELECT e.`empno`,e.`ename`,e.`sal`,s.`grade`,d.`loc`
- FROM emp e,dept d,salgrade s
- WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
- ORDER BY s.`grade` DESC;
- #多于两个表的连接
- SELECT e.`ename`,e.`sal`,d.`dname`,s.`grade`
- FROM emp e,dept d,salgrade s
- WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`;
- #非等值连接
- SELECT ename,sal,grade
- FROM emp e,salgrade s
- WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal`;
- #综合练习4
- SELECT ename,e.deptno,dname,sal
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno` AND dname = 'RESEARCH' AND sal < 1500;
- #综合练习3
- SELECT ename,loc
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno` AND ename LIKE '%A%';
- #综合练习2
- SELECT ename,comm,loc
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno` AND loc='CHICAGO' AND comm IS NOT NULL;
- #综合练习1
- SELECT ename,e.deptno,dname
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- #多表等值连接查询
- SELECT empno,ename,dept.deptno,dname
- FROM emp,dept
- WHERE emp.`deptno`=dept.`deptno`
- #使用表的别名
- SELECT empno,ename,d.deptno,dname
- FROM emp e,dept d
- WHERE e.`deptno`=d.`deptno`
- #课堂练习18
- SELECT ename,hiredate,job
- FROM emp
- WHERE hiredate BETWEEN '1982-1-1' AND '1983-12-31' AND (job LIKE 'SALES%' OR job LIKE 'MAN%')
- ORDER BY hiredate DESC
- #课堂练习17
- SELECT ename,deptno,sal
- FROM emp
- WHERE deptno NOT IN (10) AND sal BETWEEN 2000 AND 3000
- ORDER BY deptno ASC,sal DESC
- #课堂练习16
- SELECT ename,deptno,sal
- FROM emp
- WHERE deptno IN (20,30)
- ORDER BY sal
- SELECT ename,deptno
- FROM emp
- WHERE deptno IN (20,30)
- ORDER BY sal
- #同时按多列排序
- SELECT ename,deptno,sal
- FROM emp
- ORDER BY deptno ASC,sal DESC;
- #DESC 降序 从大到小 == 空值小
- SELECT ename,job,deptno,hiredate
- FROM emp
- ORDER BY hiredate DESC
- #按三种方式排序
- SELECT ename,job,deptno,hiredate
- FROM emp
- ORDER BY hiredate
- SELECT ename,job,deptno,hiredate 入职日期
- FROM emp
- ORDER BY 入职日期
- SELECT ename,job,deptno,hiredate 入职日期
- FROM emp
- ORDER BY 4
- 可以按照3种方式进行排序:分别是按列名排序、按列别名排序、按列序号排序。
- ASC表示按升序排序(默认值), DESC表示按降序排序。
- 可以同时按照多个列名进行排序
- 空值在升序排列中排在最前面,在降序排列中排在最后 == 空值小
- 4种特殊比较运算符 BETWEEN..AND.. , IN, LIKE, IS NULL
- #课堂练习15
- SELECT ename,job,deptno
- FROM emp
- WHERE job IN ('SALESMAN','MANAGER') AND deptno IN (10,20) AND ename LIKE '%A%';
- SELECT ename,job,deptno
- FROM emp
- WHERE (job ='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';
- #课堂练习14
- SELECT ename,hiredate,job
- FROM emp
- WHERE hiredate BETWEEN '1981-1-1' AND '1981-12-31' AND job NOT LIKE 'SALES%';
- SELECT ename,hiredate,job
- FROM emp
- WHERE hiredate >= '1981-1-1' AND hiredate <='1981-12-31' AND job NOT LIKE 'SALES%';
- #课堂练习13 写法1 使用特殊比较运算符
- SELECT ename,deptno,sal
- FROM emp
- WHERE deptno IN (10,20) AND sal BETWEEN 3000 AND 5000;
- #写法2 使用逻辑运算符
- SELECT ename,deptno,sal
- FROM emp
- WHERE (deptno = 10 OR deptno=20) AND (sal >= 3000 AND sal <= 5000);
- #课堂练习12
- SELECT ename,job,sal
- FROM emp
- WHERE sal > 2000 AND (job='MANAGER' OR job='SALESMAN');
- #运算符的优先级
- SELECT ename, job, sal
- FROM emp
- WHERE ( job='SALESMAN'
- OR job='PRESIDENT')
- AND sal>1500;
- SELECT ename, job, sal
- FROM emp
- WHERE job='SALESMAN'
- OR job='PRESIDENT'
- AND sal>1500;
- SELECT ename,comm
- FROM emp
- WHERE comm IS NOT NULL;
- SELECT ename,sal
- FROM emp
- WHERE sal NOT BETWEEN 3000 AND 5000;
- NOT BETWEEN .. AND .. :不在某个区间
- NOT IN (集合):不在某个集合内
- NOT LIKE :不像.....
- IS NOT NULL: 不是空
- #课堂练习11
- SELECT ename,comm
- FROM emp
- WHERE comm IS NULL;
- #课堂练习10
- SELECT *
- FROM emp
- WHERE ename LIKE '%T_';
- #课堂练习9
- SELECT *
- FROM emp
- WHERE ename LIKE 'W%';
- # IS NULL
- SELECT ename,mgr
- FROM emp
- WHERE mgr IS NULL;
- SELECT ename,comm
- FROM emp
- WHERE comm IS NULL;
- #Like运算符
- SELECT ename
- FROM emp
- WHERE ename LIKE 'S%';
- SELECT ename
- FROM emp
- WHERE ename LIKE 'S_';
- SELECT ename
- FROM emp
- WHERE ename LIKE '%A%';
- SELECT ename
- FROM emp
- WHERE ename LIKE '__A%';
- #课堂练习8
- SELECT ename,sal
- FROM emp
- WHERE sal BETWEEN 3000 AND 5000;
- #课堂练习7
- SELECT ename,hiredate
- FROM emp
- WHERE hiredate BETWEEN '1982-1-1' AND '1985-12-31';
- #IN运算符
- select empno,ename,deptno
- from emp
- where deptno in (10,20);
- #使用BETWEEN .. AND.. 可以查询出某列的值在某个范围内(包括边界值)的数据行
- SELECT empno,ename,sal
- FROM emp
- WHERE sal BETWEEN 1250 AND 1600;
- #课堂练习6
- SELECT empno,ename,deptno
- FROM emp
- WHERE deptno <> 10;
- SELECT empno,ename,deptno
- FROM emp
- WHERE deptno != 10;
- #课堂练习5
- SELECT empno,ename,hiredate
- FROM emp
- WHERE hiredate < '1985-12-31';
- #课堂练习4
- SELECT empno,ename,job
- FROM emp
- WHERE job='SALESMAN';
- #带条件查询2 非等值情况
- SELECT empno,ename,hiredate
- FROM emp
- WHERE hiredate > '1985-01-01';
- #带条件查询2
- SELECT empno,ename,job
- FROM emp
- WHERE job='CLERK';
- SELECT empno,ename,job
- FROM emp
- WHERE job="CLERK";
- #带条件查询1
- SELECT empno,ename,deptno
- FROM emp
- WHERE deptno=20;
- #课堂练习3
- SELECT DISTINCT job FROM emp
- #排除重复数据的查询
- SELECT DISTINCT deptno FROM emp
- #课堂练习2 ==?
- SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+comm 总收入 FROM emp;
- #课堂练习1
- SELECT ename,sal 涨薪前,sal*1.2 涨薪后 FROM emp;
- #空值参与算术运算,运算后的结果仍为NULL ==待解决?
- SELECT ename,sal,comm,sal+comm 月总收入 FROM emp
- #数值类型的字段可以做数学运算
- SELECT empno,ename,sal,sal*12 年薪 FROM emp
- #按字段别名查询 单双引号的使用
- SELECT empno 编号,ename "姓 名",sal '工 资',comm "奖,金" FROM emp
- #按字段别名查询
- SELECT empno 编号,ename 姓名,sal 工资,comm 奖金 FROM emp
- #查询指定字段
- SELECT empno,ename,sal,comm FROM emp
- #查询所有字段
- SELECT * FROM emp
复制代码 来源:https://www.cnblogs.com/Qinyyds/p/17756058.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
|