DQL-数据查询

-- DQL(*****)（Data Query Language）：数据查询语言，用来查询记录（数据）-- 查询语句书写顺序：select – from- where- group by- having- order by-limit-- 查询语句执行顺序：from - where -group by - having - select - order by-limit-- 查询所有列 SELECT * FROM emp;-- 查询指定列SELECT empno,ename FROM emp;-- 字段控制查询:放置于select后（all:保留所有结果，Distinct：去除重复结果）SELECT DISTINCT * FROM emp;SELECT ALL * FROM emp;-- 条件查询:WHERE（不可以使用字段别名）-- 比较运算符(=、!=、<>、<、<=、>、>=)SELECT * FROM emp WHERE deptno=10 AND sal>=100 AND ename!='king';-- 逻辑运算符:&&（and）,||(or),!(not)SELECT * FROM emp WHERE deptno !=10;SELECT * FROM emp WHERE NOT deptno=10;SELECT * FROM emp WHERE sal =3000 || sal=5000;SELECT * FROM emp WHERE sal =3000 OR sal=5000;SELECT * FROM emp WHERE sal=5000 && ename='king';SELECT * FROM emp WHERE sal=5000 AND ename='king';-- BETWEEN…AND:(在....之间)SELECT * FROM emp WHERE sal BETWEEN 3000 AND 6000;-- IN(set)：查询条件为set 集合内的数据SELECT * FROM emp WHERE deptno IN(10,20); -- 查询部门编号为10，20的所有数据-- IS NULL:为空(代替=null )SELECT * FROM emp WHERE comm IS NULL;-- AS:添加别名（可省略）SELECT * FROM emp e;SELECT * FROM emp AS e;SELECT ename e ,deptno d FROM emp ;-- 模糊查询：（_ 任意一个字母，% 任意0~ n 个字母）SELECT * FROM emp WHERE ename LIKE '_i%'   -- 第二个字符为i后面为任意字符SELECT * FROM emp WHERE ename LIKE '____'; -- 长度为4个字符的条件-- 排序 ORDER BY（ASC:升序，DESC：降序）:order by  字段名 [asc | desc] SELECT * FROM emp ORDER BY sal ASC;SELECT * FROM emp ORDER BY sal DESC;SELECT * FROM emp ORDER BY deptno DESC,sal ASC;  -- 先排部门后排工资-- 聚合函数 : sum avg max min count-- COUNT()：统计指定列不为NULL的记录行数SELECT COUNT(*) FROM emp;-- MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；SELECT MAX(sal) FROM emp;-- MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；SELECT MIN(sal) FROM emp;-- SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；SELECT SUM(comm) FROM emp;-- AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；SELECT AVG(sal) FROM emp;-- 分组查询:GROUP BY(GROUP BY  属性名  [ HAVING 条件表达式 ] [ WITH ROLLUP ])SELECT COUNT(*) FROM emp GROUP BY deptno;        SELECT deptno FROM emp GROUP BY deptno;SELECT deptno,COUNT(*) FROM emp WHERE sal>1000 GROUP BY deptno;SELECT deptno,COUNT(*) FROM emp WHERE deptno>10 GROUP BY deptno;SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*)>3; -- 组人数大于3的组的编号-- HAVING:分组后判断(可以使用别名，基本能做where的所有事情,后可再跟GROUP BY)SELECT deptno d FROM emp GROUP BY d HAVING COUNT(d)>3;-- LIMIT:用来限定查询结果的起始行，以及总行数-- LIMIT S,L  （S=从第几个开始（第一个为0），L=查询几条）SELECT * FROM emp LIMIT 0,5;-- 多表查询-- 合并结果集：UNION：去除重复记录，UNION ALL：不去除重复记录（被合并的两个结果：列数、列类型必须相同）SELECT * FROM t1 UNION ALL SELECT * FROM t2;SELECT * FROM t1 UNION SELECT * FROM t2;-- 连接查询（不过滤时产生笛卡尔积）,一般使用主外键约束-- 内连接:两表必须同时满足约束条件（查询不完全）-- 内连接（方言版）SELECT * FROM t1,t2;SELECT * FROM t1,t2 WHERE t1.id=t2.id;    -- 约束条件去除无用信息SELECT *FROM emp,dept WHERE emp.deptno=dept.deptno;   -- 主外键约束（可以没有）SELECT e1.deptno,e1.comm,e2.dname FROM emp e1,dept e2 WHERE e1.deptno=e2.deptno;   -- 查询某表某列（可不起别名）-- 内连接（标准版,INNER可省略--MYSQL默认内连接）-- INNER JOIN代替 FORM中的',' ，ON 代替 WHERESELECT * FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;-- 外链接（查询出的结果存在不满足条件的可能,OUTER可以省略）-- 左连接(先查询出左表全部（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL)select * from emp LEFT outer join  dept on emp.deptno=dept.deptno;-- 右连接（原理同左连接）SELECT * FROM emp RIGHT OUTER JOIN  dept ON emp.deptno=dept.deptno;-- 自然连接（自动寻找相同约束，可用左右连接条件）SELECT * FROM emp NATURAL JOIN dept;SELECT * FROM emp NATURAL LEFT JOIN dept;SELECT * FROM emp NATURAL RIGHT JOIN dept;-- 子查询(嵌套查询):一个select语句中包含另一个完整的select语句(子语句用括号包裹)-- 出现在FROM后：做为表，必须要起别名（子查询的结果集为：多行多列）SELECT * FROM (SELECT * FROM dept)d; SELECT * FROM (SELECT * FROM dept)d WHERE d.deptno=10; -- 出现在WHERE后：做为条件（子查询的结果集为：单行单列、单行多列、多行单列）可使用ANY,ALL-- 单行单列SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');SELECT * FROM emp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=30); SELECT * FROM emp WHERE sal>all(SELECT sal FROM emp WHERE deptno=30); -- ALL-- 单行多列SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN');-- 多行单列select * from emp where sal in(select sal from emp where sal<3000); select * from emp where sal >all(select sal from emp where sal<3000);  -- ALLselect * from emp where sal >any(select sal from emp where sal>2000);  -- ANY-- 自连接:自己连接自己，起别名-- 求7369员工编号、姓名、经理编号和经理姓名SELECT e1.empno , e1.ename,e2.mgr,e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;-- 部门最高工资的员工的所有信息SELECT * FROM emp,(SELECT MAX(sal) msal,deptno FROM emp GROUP BY deptno)e1 WHERE emp.deptno=e1.deptno AND emp.sal=e1.msal;-- 将分组后的多个字段整合为一个（c.name） SELECT s.id ,s.stuName,ss.sexName ,GROUP_CONCAT(c.cname) cnameFROM student s LEFT JOIN sex ss ON s.sexId=ss.id LEFT JOIN t_s_c tc ON tc.sid=s.id LEFT JOIN course c ON tc.cid=c.id GROUP BY s.id-- 自动返回最后一个 INSERT 或 UPDATE 操作为 AUTO_INCREMENT 列设置的第一个发生的值-- LAST_INSERT_ID()SELECT LAST_INSERT_ID();