[大数据]MySQL(7)：SQL之DQL

一、DQL

? DQL：数据查询语言：select、from、where

（1）基础查询

SELECT  列名 FROM 表名

? 1、查询所有字段

SELECT * FROM student;
SELECT id,name,sex,birthday FROM student;

? 2、查询指定字段

SELECT name FROM student;

注意：生产环境下，优先使用列名查询。*的方式需转换成全列名，效率低，可读性差。

? 3、查询常量

SELECT 常量值; -- 字符型和日期型的常量，必须用单引号引起来

? 4、查询计算表达式（+、-、*、/）

SELECT 100/25-5 AS 计算结果;

注意：%是占位符，而非模运算符。

? 5、查询函数（后面跟函数）

SELECT CONCAT('姓名',`name`) AS 新名字 FROM student;

? 6、取别名（AS）

SELECT `id` AS 学号,`name` AS 学生姓名 FROM student AS 学生表; -- AS 可以省略

? 7、去重（DISTINCT）

SELECT DISTINCT name FROM student;

（2）条件查询

SELECT 列名 FROM 表名 WHERE 条件;

1、等值查询：=、<=>

#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;

注意：=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL

2、非等值查询（>、<、>=、<=、!=、<>）

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;

3、逻辑判断查询（and、or、not）

#查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;

4、区间查询（between and）

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间，包含区间边界的两个值 [6000,10000]

5、空值查询（IS NULL、IS NOT NULL）

#查询没有提成的员工信息（编号，名字，薪资 , 提成）
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;

6、枚举查询（ IN (值 1，值 2，值 3 ) ）

#查询部门编号为70、80、90的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
-- 注：in的查询效率较低，可通过多条件拼接。

7、模糊查询（like）

_ （单个任意字符）

%（任意长度的任意字符）

#查询名字以"L"开头的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';


#查询名字以"L"开头并且长度为4的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___';

8、分支结构查询（case end）

注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。

类似 Java 中的switch。

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END

#eg:
#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT employee_id , first_name , salary , department_id , 
       CASE
           WHEN salary>=10000 THEN 'A'
           WHEN salary>=8000 AND salary<10000 THEN 'B'
           WHEN salary>=6000 AND salary<8000  THEN 'C'
           WHEN salary>=4000 AND salary<6000  THEN 'D'
   ELSE 'E'
       END as "LEVEL"
FROM t_employees;

（3）排序查询

语法：

SELECT 列名 FROM 表名 ORDER BY 排序列 [ASC | DESC];

? 1、单字段排序

# 学生表按身高升序
SELECT * FROM student ORDER BY height; 

? 2、多字段排序

# 按年龄升序，如果年龄相同就按身高降序
SELECT age,height FROM students ORDER BY age ASC,height DESC;

（4）分组查询

SELECT 聚合函数，分组后的字段
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的字段
[HAVING 分组后的筛选]
[ORDER BY 排序列表]

#eg:
# 1.查询每个工种有奖金的员工且最高工资>6000的最高工资和公众编号
SELECT 
  MAX(salary) m,
  job_id
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING m > 6000;

# 2.查询每个工种每个部门的最低工资并按最低工资降序
SELECT 
  MIN(salary),
  job_id,
  department_id 
FROM
  employees 
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC ;

注：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

区别：

1. 分组查询的字段必须是group by后出现的字段
2. 筛选区别

3. 分组可以按单个字段也可以按多个字段
4. 分组可以搭配着排序使用

技巧：

-- 知道sql执行顺序，对分组、分组前筛选、分组后筛选就容易理解了
1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY ： 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果

（5）分页查询

SELECT 查询列表
FROM 表
LIMIT offset,pagesize; -- offset：起始的条目索引，默认从0开始 ；pagesize：显示的多少条

#eg:
SELECT *
FROM student
LIMIT 0,10;

offset = (pageNum-1)*pagesize

pageNum : 当前页数

第一页 limit 0，10

第二页 limit 10，10

第三页 limit 20，10

…

第n页 limit (n-1)*pagesize

（6）关联查询

1、交叉连接（cross join）

SELECT * FROM tb_course CROSS JOIN tb_students_info; -- 产生笛卡尔积

SELECT * FROM tb_course CROSS JOIN tb_students_info 
WHERE tb_students_info.course_id = tb_course.id; 
-- 先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。一般不建议使用

笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：

• 两个集合相乘，不满足交换率，即 A×B≠B×A。
• A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

2、内连接（inner join）

-- 1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID

-- 2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID

第一种属于 SQL 标准，与其他关系型数据库通用

3、左外连接（left join）

-- 查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name 
FROM t_employees e
LEFT JOIN t_departments d 
ON e.department_id = d.department_id;

• 注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果
• 匹配不到，则返回 NULL 值填充

4、右外连接（right join）

-- 查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name 
FROM t_employees e 
RIGHT JOIN t_departments d 
ON e.department_id = d.department_id;

• 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果
• 匹配不到，则返回 NULL 值填充

（7）联合查询（union）

查询语句1
union [all]
查询语句2
union [all]
...

应用场景：

? 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

1、要求多条查询语句的查询列数是一致的！

2、要求多条查询语句的查询的每一列的类型和顺序最好一致

3、union关键字默认去重，如果使用union all 可以包含重复项

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

-- 查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,name FROM `user` WHERE csex='男'
UNION ALL  -- 包含重复项
SELECT f_id,f_name FROM `foreign` WHERE tGender='male';

（8）子查询

1、单行单列子查询

标量子查询

单行操作符使用：>、>=、<、<=、!=、<>、=、<=>

-- select后：1.查询每个部门的员工个数
SELECT d.*, (
  SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.`id`
) 个数 
FROM
  departments d ;
  
-- where后：2.查询工资大于 Bruce 的员工信息
SELECT * FROM employees WHERE salary > 
	(SELECT salary FROM employees WHERE FIRST_NAME = 'Bruce' );

2、多行单列子查询

列子查询

多行操作符使用：in、not in、any、some、all、exits

-- 1. 查询与名为'King'同一部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM employees
WHERE department_id in 
(SELECT department_id from employees WHERE last_name = 'King'); #N行一列

-- 2.查询高于 001 部门所有人的工资的员工信息（高于所有）
SELECT * FROM employees WHERE salary >  ALL (SELECT salary FROM employees WHERE department_id= 001);

-- 3.查询高于 001 部门的工资的员工信息（高于子查询中最小的数据）
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id= 001);

3、多行多列子查询

表子查询

将子查询 ”多行多列“的结果作为外部查询的一张表，做第二次查询。

注意：子查询作为临时表，为其赋予一个临时表名

-- 找出每个部门平均工资的工资等级。
SELECT a.*, s.grade
FROM 
(SELECT deptno, AVG(sal) AS avgsal 
 FROM emp 
 GROUP BY deptno
) AS a
JOIN salgrade s
ON a.avgsal BETWEEN s.lowsal AND s.highsal;