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 代替 WHERE
SELECT * 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);  -- ALL
select * 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) cname
		FROM 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();