DQL数据查询 总结 & 实例 - SQL

DQL总结

列控制

查询所有列

SELECT * FROM 表名

查询指定列 Where

SELECT 列名 FROM 表名 WHERE ...

去重查询 DISTINCT

完全重复记录只显示一次

SELECT DISTINCT 列名 FROM 表名

去重查询

列运算

列运算并不会修改原始数据

1. 数量类型的列可以加减乘除

无法转换成数字的类型会按0计算

SELECT *,AGE+1 FROM STU;

数量类型列运算

2. IFNULL 转换NULL值

任何东西 + NULL 都为 NULL

SELECT *,AGE+NUMBER FROM STU;

任何东西 `+ NULL` 都为 `NULL`

SELECT *,AGE+IFNULL(NUMBER,0) FROM STU;

`IFNULL` 转换`NULL`值

3. 字符串类型的列可以连续运算 CONCAT

SELECT CONCAT(... , ...) FROM ...

SELECT CONCAT(EDUCATION, '-', NAME) FROM STU

CONCAT

4. 重命名列 AS

SELECT CONCAT(EDUCATION, '-', NAME)  AS '教育+姓名' FROM STU;

AS 可以省略

SELECT NAME 姓名, GENDER 性别 FROM STU;

条件查询

1. 条件控制 WHERE

类似IF语句

SELECT NAME 姓名,INCOME 收入 FROM STU 
WHERE EDUCATION = 'pg' AND GENDER = 'MALE';

WHERE 语句

2. 模糊查询 LIKE

• 长度一定 用_

# 查5字姓名
SELECT NAME 姓名 FROM STU 
WHERE NAME LIKE '_____';

长度一定，模糊查询

• 长度不定 用%

# 查J开头姓名
SELECT NAME 姓名 FROM STU 
WHERE NAME LIKE 'J%';

长度不定，模糊查询

---

排序 ORDER BY

1. 升序 ASC

SELECT * FROM STU 
ORDER BY AGE ASC;

ASC 可以省略

2. 降序 DESC

SELECT * FROM STU 
ORDER BY AGE DESC;

3. 多列做条件

SELECT * FROM STU 
ORDER BY AGE ASC, INCOME DESC;

先用 AGE 升序排列，AGE 相同时用 INCOME 降序

---

聚合函数 - 纵向查询

纵向查询

结合应用

SELECT COUNT(*) 人数, SUM(INCOME) 工资总和, AVG(SAL) 销售平均 FROM STU ;

聚合函数

---

分组查询 GROUP BY

分组查询

• 直接分组查询

select income,count(*) from stu group by income;

group by

• 分组前条件 WHERE

select education,count(*) from stu 
where gender = 'male' group by education;

• 分组后设置条件 HAVING

select education,count(*) 
from stu group by education having count(*)>1;

• 从titles表获取按照title进行分组
• 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
• 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。

• Group by 代替 distinct
  1. 当对系统的性能高并数据量大时使用group by
  2. 当对系统的性能不高时使用数据量少时两者皆可
  3. 尽量使用group by

select distinct salary 
from salaries 
where to_date='9999-01-01' 
order by salary desc;

select salary 
from salaries 
where to_date='9999-01-01' 
GROUP BY SALARY
order by salary desc;

---

LIMIT 语句

限定查询结果的起始行和总行数。

select * from stu limit 0,5;

每页8行记录 查P17数据：（当前页-1）* 每页记录数

• 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

---

JOIN 语句

join

Join / inner join

如果表中有至少一个匹配，则返回行

• 查找所有员工入职时候的薪水情况
• 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
• 获取所有员工当前的manager
• 获取所有部门中当前员工薪水最高的相关信息

left join

即使右表中没有匹配，也从左表返回所有的行

• 查找所有员工的last_name和first_name以及对应部门编号dept_no
• 获取所有非manager的员工emp_no

RIGHT JOIN

即使左表中没有匹配，也从右表返回所有的行

FULL JOIN

只要其中一个表中存在匹配，则返回行

on 和 where 条件的区别

1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2. where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

Reference

[1] SQL 连接(JOIN)
[2] 数据库SQL实战
[3] SQL-exercises w3resource