二、DQL

---

注意：SELECT子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

---

一、基础查询【SELECT…FROM…】

语法：
	USE 库名;

USE myemployees; #使用myemployees库

语法：
	SELECT 列名 FROM 表名;

SELECT last_name FROM employees;

SELECT 
  last_name,
  salary,
  email    #最后一个列名不加","
FROM
  employees ;

SELECT * FROM employees; #顺序与表完全一致

SELECT employees.last_name FROM myemployees.employees; #使用完全限定的列名、表名

SELECT VERSION(); #函数

SELECT 100%98; #表达式

SELECT 100; #常量值
SELECT "john";

---

1.1 AS \ DISTINCT \ Concat() \ LIMIT

SELECT last_name AS 姓; #起别名
SELECT last_name 姓; #AS可省略
SELECT salary AS "out put" FROM employees;

SELECT DISTINCT department_id FROM employees; #去重
#错误写法： SELECT DISTINCT a,b FROM c; 得到的结果不规则

SELECT Concat(last_name,'_',first_name) AS 姓名 FROM employees; 

SELECT last_name FROM employees LIMIT 5; #行0开始，返回不多于5行
SELECT last_name FROM employees LIMIT 5,5; #行5开始，返回不多于5行

---

二、条件查询【WHERE…】

---

2.1 > < = >= <= <> != <=>

条件运算符：>  <  =  >=  <=  <>(不等于)、!=(不等于、新增的)  <=>(安全等于，可判断null和普通类型的值)
	      其中：
		   <> 或 = 不能用于判断null值，可读性高，建议使用
		   <=> 既可以判断null，也可判断普通数值，可读性较低

SELECT * FROM employees WHERE salary>12000;

SELECT last_name, department_id FROM employees WHERE department_id<>90;

SELECT last_name,salary FROM employees WHERE salary <=> 12000; #查询工资为12000的员工信息

---

2.2 AND \ OR \ NOT

逻辑运算符：&& || ！
		  AND OR NOT

SELECT `last_name`,`salary`,`commission_pct`FROM employees WHERE salary>10000&&salary<20000;

SELECT * FROM employees WHERE (department_id<90 OR `department_id`>110) AND salary >= 15000; #AND优先级高，使用AND、OR操作符的WHERE子句都要加括号明确分组

---

2.3 LIKE \ BETWEEN AND \ IN \ IS NOT NULL

like	
		可判断字符型或数值型
        与通配符搭配：%任意多个字符,包括0个
		             _任意单个字符
between and	
        提高简洁度，包含临界值，临界值顺序固定        
in 或 not in
		提高简洁度，判断是否属于IN列表中的某一项，IN列表内值类型兼容，不支持通配符
is null 或 is not null
		判断null值

SELECT * FROM employees WHERE last_name LIKE "%a%"; #查询员工名包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE "__n_l%"; #查询员工名第三个字符为n，第五个为l的员工信息
select * from employees where last_name like “%%”; #不包含null

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; #查询员工编号100~120的员工信息

SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES'); #查询员工工种编号是 IT_PROG、AD_VP、AD_PRES的员工名和工种编号

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; #查询没有奖金的员工名和奖金率

---

三、排序查询【ORDER BY…】

---

3.1 ASC \ DESC

语法：
	SELECT 查询列表
	FROM 表名
	(WHERE 筛选条件)
	ORDER BY 排序列表 (ASC\DESC);
说明：
	ASC升序 DESC降序 不写则【默认升序】
	ORDER BY 子句支持 多字段，表达式，函数，别名
	ORDER BY 子句一般放在最后面，LIMIT子句除外

SELECT * FROM employees ORDER BY salary DESC;

SELECT * FROM employees WHERE department_id >= 90 ORDER BY `hiredate`;  #筛选后排序

SELECT * FROM employees
ORDER BY salary DESC,`employee_id`; #多字段，先按工资，再按编号

SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name); #按函数

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 
FROM employees
ORDER BY  年薪; #按别名、表达式

---

四、常见函数

---

4.1 字符函数

SELECT LENGTH('张三丰hahaha'); #length 获取参数值的字节个数

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees; #concat 拼接字符

SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put; #substr、substring 截取字符串
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; #索引(从1开始）,字符长度

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put; #instr 返回子串第一次出现的索引，如果找不到返回0

SELECT TRIM('a' FROM 'aaaaaaaa张aaa伟aaaaaaa') AS out_put; #trim 去掉字符串中的前后字符
SELECT TRIM('    张伟      ') AS out_put; #默认空格

SELECT LPAD('张伟',10,'*') AS out_put; #lpad 指定长度字符左填充，10为总长度
SELECT LPAD('张伟',2,'*') AS out_put; #不填充
SELECT LPAD('张伟',1,'*') AS out_put; #右截断

SELECT RPAD('张伟',10,'*') AS out_put; #rpad 指定长度字符右填充，10为总长度
SELECT RPAD('张伟',2,'*') AS out_put;#不填充
SELECT RPAD('张伟',1,'*') AS out_put;#右截断

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put; #replace 替换

---

4.2 数学函数

SELECT ROUND(1.65); #round 四舍五入, 先绝对值四舍五入, 再添加负号
SELECT ROUND(-1.65);
SELECT ROUND(1.65,1);

SELECT CEIL(1.52); #ceil 向上取整,返回>=该参数最小整数
SELECT CEIL(1.00);
SELECT CEIL(-1.52);

SELECT FLOOR(1.52); #floor 向下取整,返回<=该参数最小整数
SELECT FLOOR(1.00);
SELECT FLOOR(-1.52);

SELECT TRUNCATE(1.5222,1); #truncate 截断

SELECT MOD(10,3); #mod 取模\取余
SELECT MOD(-10,3); #结果符号看被除数-10
SELECT MOD(-10,-3);

---

4.3 日期函数

SELECT NOW(); #now 返回当前系统日期+时间

SELECT CURDATE(); #curdate 返回当前系统日期,不包含时间

SELECT CURTIME(); #curtime 返回当前系统时间,不包含日期

SELECT YEAR(NOW()) 年; #可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y'); #str_to_date 将日期格式的字符转换成指定格式的日期
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3-1992','%m-%d-%Y'); #案例：查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日'); #date_format 将日期转换成字符

---

4.4 其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

---

4.5 流程控制函数

语法：
	IF(条件，成立返回，不成立返回);

SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'呵呵','嘻嘻') 备注 FROM employees;

语法：
	CASE 要判断的字段或表达式
	when 常量1 then 要显示的值1 或 语句1; #语句加分号
	when 常量2 then 要显示的值2 或 语句2;
	…
	else 要显示的值n 或 语句n;
	END

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

语法：
	CASE
	when 条件1 then 要显示的值1 或 语句1; #语句加分号
	when 条件2 then 要显示的值2 或 语句2;
	…
	else 要显示的值n 或 语句n;
	END

SELECT `employee_id`,`first_name`,`last_name`,`salary`,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END 级别
FROM employees ORDER BY 级别;

---

4.6 聚合函数（统计函数、分组函数）

1、sum avg 处理数值型
   max min count 处理任何类型
2、都忽略null
3、与distinct搭配去重
4、COUNT(*)统计行数
5、和分组函数一同查询的字段要求是group by后的字段

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;

SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;

SELECT COUNT(commission_pct) FROM employees; #count只计算非null的个数

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; #和distinct搭配去重

SELECT COUNT(*) FROM employees;  #统计行数，对所有行，行中任一项有一个不为null即可
SELECT COUNT(1) FROM employees;  #统计行数

---

五、分组查询【GROUP BY…】

语法：
	SELECT 分组函数,列(要求出现在 GROUP BY 的后面)
	FROM 表
	(WHERE 筛选条件)
	GROUP BY 分组的字段
	(ORDER BY 排序的字段);

特点：
	1、和分组函数一同查询的字段必须是group by后出现的字段
	2、筛选分为两类：
			    针对的表		           位置		     连接的关键字
	分组前筛选	原始表			       group by前	 where
		
	分组后筛选	group by后的结果集      group by后	 having

		分组函数做条件肯定放在having子句中
		一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

	3、分组可以按单个字段也可以按多个字段，逗号隔开，无顺序要求，也支持表达式和函数
	4、可以搭配着排序使用，排序放在分组查询最后面

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id; #查询每个工种的员工平均工资

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id; #查询每个地区的部门个数

---

5.1 分组前筛选【WHERE…】

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'  #注意where的位置
GROUP BY department_id; #查询邮箱中包含a字符的 每个部门的最高工资

---

5.2 分组后筛选【HAVING…】

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5; #查询哪个部门的员工个数>5

---

5.3 按表达式或函数分组

SELECT COUNT(*) count,LENGTH(last_name) len_name #MySQL支持group by和having后加别名
FROM employees
GROUP BY len_name
HAVING count>5; #按员工姓名的长度分组，筛选员工个数>5的组        

---

5.4 按多个字段分组，添加排序

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id #department_id,job_id顺序可颠倒
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC; #查询每个工种每个部门的平均工资,筛选工资大于1W的,并按平均工资降序排序

---

六、连接(联结)查询【FROM…JOIN…ON…】

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
		发生原因：没有有效的连接条件
		如何避免：添加有效的连接条件

分类：
	按年代分类：
		sql92标准:仅仅支持内连接
		sql99标准【推荐】：支持内连接、外连接（左外和右外）、交叉连接
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接 

---

6.1 sql92标准

---

6.1.1 内连接

【等值连接】
	① 多表等值连接的结果为多表的交集部分
	② n表连接，至少需要n-1个连接条件
	③ 多表的顺序没有要求
	④ 一般需要为表起别名
	⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id; #查询员工名和对应的部门名


【为表起别名】
	① 提高语句的简洁度
	② 区分多个重名的字段
	注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定！！！

SELECT e.last_name,e.job_id,j.job_title #为表起了别名，则查询的字段就不能使用原来的表名去限定
FROM employees e,jobs j #为表起别名
WHERE e.`job_id`=j.`job_id`; #job_id两个表都有，需要加限定


【可以添加筛选、分组、排序】

SELECT department_name,d.`manager_id`,MIN(salary) #查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资，按最低工资降序
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL #筛选
GROUP BY department_name,d.`manager_id` #分组
ORDER BY MIN(salary) DESC; #排序


【三表连接】

SELECT last_name,department_name,city #查询员工名、部门名和所在的s开头的城市
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;


【非等值连接】

SELECT salary,grade_level #查询员工的工资和工资级别
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY grade_level;


【自连接】

SELECT 
  e.employee_id,
  e.last_name,
  m.employee_id,
  m.last_name  #查询员工名和上级的名称
FROM
  employees e,
  employees m #利用别名把一个表当成两个表，甚至更多表
WHERE e.`manager_id` = m.`employee_id` ;

---

6.2 sql99标准(推荐)

语法：
	SELECT 查询列表
	FROM 表1 别名 
	(INNER\LEFT\RIGHT\FULL\CROSS)
	JOIN 表2 别名 
	ON 连接条件
	(WHERE 筛选条件)
	(GROUP BY 分组)
	(HAVING 筛选条件)
	(ORDER BY 排序列表)

内连接：inner（可省略）

外连接： 
		左外	left(outer)
		右外	right(outer)
		全外	full(outer)

交叉连接：cross

---

6.2.1 内连接

【等值连接】

SELECT `department_name`,COUNT(*) 员工个数  #查询名字中包含e的部门的员工个数>3的部门名和员工个数，并按个数降序
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`= d.`department_id`
WHERE `department_name` LIKE '%e%';
GROUP BY e.`department_id`
HAVING 员工个数>3
ORDER BY 员工个数 DESC;


【三表连接】

SELECT `last_name`,`department_name`,`job_title`  #查询员工名、部门名、工种名，并按部门名降序
FROM `employees` e     #此处千万别加 ',' 好几次错误都在这
INNER JOIN `departments` d ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j ON e.`job_id`=j.job_id
ORDER BY d.`department_name` DESC;


【非等值连接】

SELECT COUNT(*),`grade_level`  #查询工资级别的个数>20的个数，并且按工资级别降序
FROM `employees` e
JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;


【自连接】

SELECT e.`last_name` 员工,m.`last_name` 领导  #查询姓名中包含字符k的员工的名字、上级的名字
FROM `employees` e
JOIN `employees` m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%' 
AND m.`last_name` LIKE '%k%';

---

6.2.2 外连接

应用场景：一个表有，另一个表没有的记录

特点：
 1、查询的信息来自的表就是是主表
    外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join 左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的


【左外连接】

SELECT d.`department_name`  #查询哪个部门没有员工
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id`IS NULL;


【全外连接】！！！注意：【MySQL不支持 Oracle支持】

SELECT g.*,b.*   
FROM girl g
FULL JOIN boys b
ON g.`boyfriend_id` = b.id;  #结果没有的部分用null填充


【交叉连接】

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;  #得到一个笛卡尔乘积

---

6.3 图示

---

七、子查询【其他语句中的SELECT…】

含义：
     出现在其他语句中的select语句，称为子查询或内查询
     外部的查询语句成为主查询或外查询
分类：
     按子查询出现的位置：
			select后面：仅支持标量子查询
			from后面：支持表子查询
			where或having后面：标量子查询、列子查询、行子查询
			exists后面(相关子查询)：表子查询
     按结果集的行列数不同：
			标量子查询(结果集只有一行一列)单行
			列子查询(结果集只有一列多行)多行
			行子查询(结果集有一行多列或多列多行)
			表子查询(结果集一般为多行多列)

---

7.1 where或having后

特点：
	① 子查询放在小括号()内
	② 子查询一般放在条件的右侧
	③ 标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
	    列子查询，一般搭配着多行操作符使用 in/not in、any/some、all
	④ 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

---

7.1.2 标量子查询(单列单行子查询)

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
); #返回公司工资最少的员工的last_name,job_id和salary

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
); #查询最低工资大于50号部门最低工资的部门id和其最低工资

---

7.1.2 列子查询(单列多行子查询)

说明：
	in/not in 等于列表中的任意一个、不等于列表中的任意一个
	any/some 和子查询返回的某一个值比较
	all 和子查询返回的所有值比较

SELECT last_name
FROM employees
WHERE department_id  IN(                 #     in 可换成 =any
	SELECT DISTINCT department_id        # not in 可换成 <>all
	FROM departments
	WHERE location_id IN(1400,1700)
); #返回location_id是1400或1700的部门中的所有员工姓名

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG'; #返回其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id、salary

---

7.1.3 行子查询(一行多列或多行多列、用的少)

SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
); #查询员工编号最小并且工资最高的员工信息

---

7.2 select后（标量子查询）

仅支持标量子查询

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d; #查询每个部门的员工个数

---

7.3 from后（表子查询）

将子查询结果充当一张表，要求【必须起别名】

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; #查询每个部门的平均工资的工资等级

---

7.4 exists后（相关子查询）

语法：
	exists(完整的查询语句,一般为普通的连接查询)
结果：
	查询语句结果有没有，输出1或0
特点：
	先外(主)查询后(内)子查询
	外查询涉及到子查询的相关字段

#in 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);

#exists 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

---

八、分页查询【LIMIT…】

应用场景：如web中，当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	(join type)join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段
	limit (offset,)size;
		offset：要显示条目的起始索引（起始索引从0开始）
		size：要显示的条目个数
特点：
	① limit语句放在查询语句的最后
	② 要显示的页数page，每页的条目数size
	  limit (page-1)*size,size;

SELECT * FROM  employees LIMIT 5;   #从第一条开始,则0可以省略,查询前五条员工信息

SELECT * FROM  employees LIMIT 10,15; #查询第11条——第25条

SELECT * FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10; #有奖金的员工信息，并且工资较高的前10名显示出来

---

九、联合查询【…UNION…】

union 联合\合并：将多条查询语句的结果合并成一个结果

语法：
	查询语句1
	union
	查询语句2
	union
	...

应用场景：
	要查询的结果来自于多个表，且多个表没有直接的连接关系，但【查询的信息一致】时

特点：
	1、要求多条查询语句的查询列数是一致的！
	2、要求多条查询语句的查询的每一列的类型和顺序最好一致
	3、union关键字【默认去重】，如果使用union all可以包含重复项

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90; #查询部门编号>90或邮箱包含a的员工信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male'; #查询中国用户中男性的信息以及外国用户中年男性的用户信息