MySQL数据操纵语言学习笔记（一）

(之前在hexo上写的MySQL笔记，还是放到CSDN看比较方便一点)

Hexo上的地址:https://genmcai.github.io/2019/08/16/MySqlDML1/

记录查询

◆ 最基本的查询语句是由SELECT和FROM 关键字组成的

SELECT * FROM t_emp;
SELECT empno,ename,sal FROM t_emp;

◆ SELECT语句屏蔽了物理层的操作，用户不必关心数据的真是存储，交由数据库高效的查找数据

使用列别名

◆ 通常情况下，SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列名重命名的机制

SELECT 
	empno,
	sal*12 AS "income"
FROM t_emp;

数据分页

◆ 比如我们查看朋友圈，只会加载少量部分信息，不用一次性加载全部朋友圈，那样只会浪费CPU时间、内存和网络宽带

◆ 如果结果集的记录很多，则可以使用LIMIT关键字限定结果集数量。

SELECT ...... FROM ...... LIMIT 起始位置，偏移量;

例子：

SELECT empno,ename FROM t_emp LIMIT 0,20;

◆ 如果LIMIT子句只有一个参数，它表示的是偏移量，起始值默认为0

SELECT empno,ename FROM t_emp LIMIT 10;
SELECT empno,ename FROM t_emp LIMIT 0,10;
# 执行顺序 FROM -> SELECT -> LIMIT

结果集排序

◆ 如果没有设置，查询语句不会对结果集进行排序。也就是说，如果想让结果集按照某种顺序排列，就必须使用ORDER BY子句。

SELECT ...... FROM ...... ORDER BY 列名 [ASC|DESC];

例：

SELECT ename,sal FROM t_emp ORDER BY sal;
# 默认为ASC

◆ ASC代表升序（默认），DESC代表降序

◆ 如果排序列是数字类型，数据库就按照数字大小排序，如果是日期类型就按照日期大小排序，如果是字符串就按照字符集序号排序

◆ 我们可以使用ORDER BY规定首要排序条件和次要排序条件。数据库会先按照首要排序条件排序，如果遇到首要排序内容相同的记录，那么就会启用次要排序条件接着排序

◆ ORDER BY子句书写的时候放在LIMIT子句的前面

FROM -> SELECT -> ORDER BY -> LIMIT

去除重复记录

◆ 加入我们要查询员工表有多少种职业，写出来的SQL语句如下：

SELECT job FROM t_emp;

◆ 如果我们需要去除重复的数据，可以使用DISTINCT关键字来实现

SELECT DISTINCT 字段 FROM ......;

例：

SELECT DISTINCT job FROM t_emp;

◆ 使用DISTINCT的SELECT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效

SELECT DISTINCT job,ename FROM t_emp;

◆ DISTINCT关键字只能在SELECT子句中使用一次

SELECT DISTINCT job,DISTINCT ename FROM t_emp;
SELECT ename,DISTINCT job FROM t_emp;

条件查询

◆ 很多时候，用户感兴趣的并不是逻辑表中的全部记录，而只是它们当中能够满足某一种或几种条件的记录。这类条件要用WHERE子句来实现数据的筛选

SELECT ...... FROM ...... WHERE 条件[AND|OR] 条件 ......

例：

SELECT empno,ename,sal FROM t_emp
WHERE deptno=10 AND sal >= 2000;

◆ 不管怎么样的数字和null做加减乘除运算都是null

◆ 如果想要让null值参与运算的话，可以使用IFNULL(字段的名称或NULL都可以,值)

SELECT 10+IFNULL(NULL,0);
# NULL当成0来运算

◆ DATEDIFF(NOW(),hiredate) 第一个日期减去第二个日期，求相差多少天

◆ 比较运算符中的表达式IN,是包含的意义。如：deptno IN(10,30,40)

比较运算符（部分）:

表达式	意义	例子
IS NULL	为空	comm IS NULL
IS NOT NULL	不为空	comm IS NOT NULL
BETWEEN AND	范围	sal BETWEEN 2000 AND 3000
LIKE	模糊查询	ename LIKE"A%"
REGEXP	正则表达式	ename REGEXP “[a-zA-Z]{4}”

逻辑运算符： AND,OR,NOT,XOR

按位运算符： &,|,~,^,<<,>>

◆ WHERE子句中，条件执行的顺序是从左到右的。所以我们应该把索引条件，或者筛选掉记录最多的条件写在最左侧

◆ FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT

聚合函数

◆ 聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。

例：

SELECT AVG(sal+INNULL(comm,0)) FROM t_emp;
# 可以取别名，缩短字段名的长度

◆ SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加。

◆ MAX函数用于获得非空值的最大值。

SELECT MAX(LENGTH(ename)) FROM t_emp;
# LENGTH()取字符串长度

◆ MIN函数用于获得非空值的最小值

◆ AVG函数用于获得非空值的平均值，非数字数据统计结果为0

◆ COUNT(*)用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数。

SELECT COUNT(*) FROM t_emp;
SELECT COUNT(comm) FROM t_emp;

分组查询

◆ 默认情况下汇总函数是对全表范围内的数据做统计

◆ GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理

SELECT deptno,AVG(sal) FROM t_emp
GROUP BY deptno;

◆ 数据库支持多列分组条件，执行的时候逐级分组

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno,job
ORDER BY deptno;

◆ 查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中

◆ WITH ROLLUP对汇总函数再次执行汇总计算

SELECT deptno,AVG(sal),SUM(sal),MAX(sal),MIN(sal),COUNT(*)
FROM t_emp
GROUP BY deptno WITH ROLLUP;

◆ GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串

SELECT deptno,GROUP_COUNT(ename),COUNT(*)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

◆ FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

HAVING

◆ 必须配合GROUP BY子句进行使用，用法近乎于WHERE，为分组后的条件查询

◆ 按照数字1分组，MySQL会一句SELECT子句中的列进行分组，HAVING子句也可以正常使用

SELECT deptno,COUNT(*) FROM t_emp
GROUP BY 1 HAVING deptno IN (10,20);
# 不建议替代WHERE的功能

表连接

◆ 从多张表中提取数据，必须指定关联的条件。如果不定义关联条件，就会出现无条件连接，两张表的数据会交叉连接，产生笛卡儿积。

◆ 规定了连接条件的表连接语句，就不会出现笛卡儿积

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

◆ 表连接分为两种：内连接和外连接

◆ 内连接是结果集中只保留符合连接条件的记录

◆ 外连接是不管符不符合连接条件，记录都要保留在结果集中

内连接

◆ 内连接是最常见的一种表连接，用于查询多张关系表符合连接条件的记录。

SELECT ...... FROM 表1
[INNER] JOIN 表2 ON 条件
{INNER} JOIN 表3 ON 条件
SELECT ...... FROM 表1 JOIN 表2 ON 连接条件;
SELECT ...... FROM 表1 JOIN 表2 WHERE 连接条件；
SELECT ...... FROM 表1，表2 WHERE 连接条件；

低效率：

SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename!="SCOTT";

高效率：

SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

SELECT
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno;

◆ MySQL存在FLOOR()和CELL()用法

外连接

◆ 外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录

SELECT
	e.empno,e.ename,d.dname
FROM 
	t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno;

◆ 左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

右连接：

SELECT e.empno,e.ename,d.dname
FROM t_dept d RIGHT JOIN t_emp e
ON e.deptno = d.deptno;

◆ 内连接只保留符合条件的记录，所以查询条件卸载ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来。

SELECT 
	e.ename,d.dname,d.deptno
FROM 
	t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE e.deptno = 10;

子查询

◆ 子查询是一种查询中嵌套查询的语句

◆ 子查询可以写在三个地方：WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的

WHERE子拆线呢

◆ 这种子查询最简单，最容易理解，但是却是效率很低的子查询

SELECT 
	empno,ename,sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);
# 比较每条记录都要重新执行子查询

FROM子查询

◆ 这种子查询只会执行一次，所以查询效率很高

SELECT
	e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
	(SELECT deptno,AVG(sal) as avg)
	FROM t_emp GROUP BY deptno) t
ON e.deptno=t,deptno AND e.sal>=t.avg;

SELECT子查询

◆ 这种子查询每输出一条记录的时候都要执行一次，查询效率很低

SELECT
	e.empno,
	e.ename,
	(SELECT dename FROM t_dept WHERE deptno=e.deptno)
FROM t_emp e;

单行子查询和多行子查询

◆ 单行子查询的结果集只有一条记录，多行子查询结果集有多行记录

◆ 多行子查询只能出现在WHERE子句和FROM子句中

◆ WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断

SELECT ename FROM t_emp
WHERE sal > ALL
(SELECT sal FROM t_emp
WHERE ename IN("FORD","MARTIN"));
# ALL必须比结果集中的记录都要高
# ANY只要比结果集中的一条记录高就行了

◆ EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询里面

SELECT ...... FROM 表名 WHERE [NOT] EXISTS (子查询);