数据查询语言
数据库结构图
语法
// 执行顺序
SELECT 子句 6
FROM 子句 1
JOIN...ON... 子句 2
WHERE 子句 3
GROUP BY 子句 4
HAVING 子句 5
ORDER BY 子句 7
LIMIT 子句(方言) 8
基础查询
语法
WHERE子句
SELECT name, age, salary
FROM teacher
WHERE age >= 30;
使用 AND 和 OR连接多个条件
SELECT name, gender, age, job
FROM student
WHERE age = 7 AND job = '大队长';
SELECT name, gender, age, job, class_id
FROM student
WHERE class_id < 6 AND job = '中队长';
SELECT name, salary, title
FROM teacher
WHERE title = '一级讲师' OR title = '三级讲师';
SELECT name, gender, age, job
FROM student
WHERE job = '大队长' OR job = '中队长' OR job = '小队长';
SELECT name, gender, age, job
FROM student
WHERE class_id <= 6 AND (job = '大队长' OR job = '中队长');
IN(列表):等于列表中的其中之一
SELECT name, gender, age, job
FROM student
WHERE job IN ('大队长', '中队长', '小队长');
SELECT name, salary, title, age
FROM teacher
WHERE title IN ('一级讲师', '二级讲师', '三级讲师');
NOT IN(列表):不在列表中,不能等于列表中任何一项
SELECT name, salary, title
FROM teacher
WHERE title NOT IN ('一级讲师', '二级讲师');
SELECT name, gender, age, job
FROM student
WHERE job NOT IN ('大队长', '中队长', '小队长');
BETWEEN…AND… 在一个范围内
SELECT name, salary, title, age
FROM teacher
WHERE salary BETWEEN 2000 AND 5000;
SELECT name, gender, age
FROM student
WHERE age BETWEEN 7 AND 10;
SELECT name, gender, title, age
FROM teacher
WHERE gender = '男' AND age BETWEEN 20 AND 35;
SELECT name, floor
FROM class
WHERE floor BETWEEN 3 AND 5;
DISTINCT 去重,去除结果集中指定字段值重复的的记录
语法
SELECT DISTINCT title
FROM teacher;
SELECT DISTINCT job
FROM student;
SELECT DISTINCT job, age
FROM student;
LIKE 模糊查询
SELECT name, age, salary
FROM teacher
WHERE name LIKE '%苍%';
SELECT name, age, gender, job
FROM student
WHERE name LIKE '张%';
SELECT name, age, gender, title
FROM teacher
WHERE name LIKE '%晶';
SELECT name, age, gender, job
FROM student
WHERE name LIKE '_平_';
SELECT name, age, gender, job
FROM student
WHERE job LIKE '%课代表';
SELECT name, floor
FROM class
WHERE name LIKE '%2班';
NULL 值判断
SELECT name, salary, comm
FROM teacher
WHERE comm IS NULL;
SELECT name, salary, comm
FROM teacher
WHERE comm IS NOT NULL;
ORDER BY 子句
- ORDER BY 字段名 [ASC]:将结果集按照指定的字段值从小到大排序
- ORDER BY 字段名 DESC:将结果集按照指定的字段值从大到小排序
- ORDER BY 在不指定排序方式时,默认为升序
SELECT name, salary
FROM teacher
ORDER BY salary DESC;
SELECT name, salary, comm
FROM teacher
ORDER BY comm ASC;
SELECT name, birth
FROM student
ORDER BY birth ASC;
SELECT name, gender, age, birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth DESC;
SELECT name, salary, comm
FROM teacher
ORDER BY comm ASC, salary DESC;
分页查询
语法
SELECT name, age, salary, comm
FROM teacher
ORDER BY salary DESC
LIMIT 0, 5;
SELECT name, salary, comm
FROM teacher
ORDER BY comm
LIMIT 12, 3;
DQL 中可以使用函数或表达式进行查询
在 SELECT 子句中使用表达式
SELECT name, salary, salary * 12
FROM teacher;
在 SELECT 子句中使用函数
IFNULL 函数
SELECT name, salary, comm, salary + IFNULL(comm, 0)
FROM teacher;
在 WHERE 子句中使用表达式和函数
在 WHERE 子句中使用表达式
SELECT name, salary, comm, salary * 12
FROM teacher
WHERE salary * 12 < 60000;
在 WHERE 子句中使用函数
SELECT name, salary, comm
FROM teacher
WHERE IFNULL(comm, 0) < 3000;
别名
在 SELECT 子句中使用别名
语法
SELECT name, comm, salary * 12 salary
FROM teacher;
SELECT name, salary * 12 AS salary
FROM teacher;
// 这两种都可以
SELECT name, salary * 12 'salary'
FROM teacher;
SELECT name, salary * 12 AS 'salary'
FROM teacher;
// 这两种都可以
SELECT name, salary * 12 "salary"
FROM teacher;
SELECT name, salary * 12 AS "salary"
FROM teacher;
聚合函数
SELECT AVG(salary)
FROM teacher;
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM teacher;
SELECT name, subject_id, AVG(salary)
FROM teacher
WHERE subject_id = 1;
SELECT COUNT(*)
FROM teacher;
SELECT AVG(IFNULL(comm, 0)), SUM(comm)
FROM teacher;
SELECT COUNT(*)
FROM teacher
WHERE subject_id = 2;
SELECT COUNT(*)
FROM student
WHERE class_id = 1;
SELECT MIN(birth)
FROM student;
SELECT COUNT(*)
FROM student
WHERE age = 11 AND job LIKE '%课代表';
GROUP BY 子句
按照单字段分组
SELECT AVG(salary), subject_id
FROM teacher
GROUP BY subject_id;
SELECT COUNT(*), class_id
FROM student
GROUP BY class_id;
SELECT COUNT(*) '人数', MAX(birth) '最小生日', MIN(birth) '最大生日', job
FROM student
GROUP BY job;
按照多字段分组
SELECT COUNT(*), class_id, job
FROM student
GROUP BY class_id, job;
按照聚合函数排序
SELECT AVG(salary) avg, subject_id
FROM teacher
GROUP BY subject_id
ORDER BY avg ASC;
HAVING 子句
// 正确的代码为
SELECT AVG(salary), subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary) > 6000;
SELECT AVG(salary), subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary) > 9000;
SELECT SUM(salary), subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(IFNULL(comm, 0)) > 4000;
子查询
子查询使用场景
子查询的分类
DQL 中使用子查询
SELECT name, salary
FROM teacher
WHERE salary > (SELECT salary FROM teacher WHERE name = '王克晶');
SELECT name, salary
FROM teacher
WHERE salary > (SELECT AVG(salary) FROM teacher);
SELECT name, class_id
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '李费水');
SELECT name, salary, comm
FROM teacher
WHERE salary = (SELECT MAX(salary) FROM teacher);
SELECT name, class_id
FROM student
WHERE class_id IN (SELECT class_id FROM student WHERE name IN ('祝雷', '李费水'));
SELECT name, salary
FROM teacher
WHERE salary >ALL (SELECT salary FROM teacher WHERE subject_id IN (2, 4));
在 DML(增删改)语句中使用子查询
UPDATE teacher
SET salary = salary + 500
WHERE subject_id = (SELECT subject_id FROM teacher WHERE name = '范传奇');
在 DDL 语句中使用子查询
CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary) max_salary, MIN(salary) min_salary, AVG(salary) avg_salary, SUM(salary) sum_salary, subject_id
FROM teacher
GROUP BY subject_id;
关联查询(重点知识)
在关联查询中,非常重要的一个点就是制定连接条件,用于让数据库知道两张表中的数据是如何对应的。延伸出多张表之间也需要分别指定其中两张表的关系。总结:N张表关联,至少要有N-1个连接条件
关联查询的语法
SELECT teacher.name, teacher.salary, subject.name
FROM teacher, subject
WHERE teacher.subject_id = subject.id;
SELECT t.name, t.salary, s.name
FROM teacher t, subject s
WHERE t.subject_id = s.id;
SELECT teacher.name, class.floor, class.name
FROM teacher, class
WHERE teacher.id = class.teacher_id;
SELECT student.name, student.age, class.name, class.floor
FROM student, class
WHERE student.class_id = class.id;
笛卡尔积的产生
关联查询中使用过滤条件
SELECT t.name, t.salary, c.name, c.floor
FROM teacher t, class c
WHERE t.id = c.teacher_id
AND t.name = '王克晶';
SELECT t.name, t.salary, c.name, c.floor
FROM teacher t, class c
WHERE t.id = c.teacher_id
AND c.name LIKE '3年级%';
SELECT s.name, s.age, s.gender, l.name
FROM student s, location l
WHERE s.location_id = l.id
AND l.name = '南京';
SELECT s.name, s.age, s.gender, s.job, c.name, c.floor
FROM student s, class c
WHERE s.class_id = c.id
AND c.name LIKE '5年级%' AND s.job = '中队长';
SELECT s.name, t.age, c.name, t.name
FROM student s, class c, teacher t
WHERE s.class_id = c.id
AND t.id = c.teacher_id
AND t.name = '范传奇';
SELECT s.name, c.name, l.name
FROM student s, class c, location l
WHERE s.class_id = c.id
AND l.id = s.location_id
AND c.name = '1年级1班';
关联查询中使用聚合函数
SELECT COUNT(*)
FROM teacher t, student s, class c
WHERE t.id = c.teacher_id
AND s.class_id = c.id
AND t.name = '范传奇';
SELECT AVG(t.salary)
FROM teacher t, subject s
WHERE t.subject_id = s.id
AND s.name = '语文';
SELECT AVG(t.salary), s.name
FROM teacher t, subject s
WHERE t.subject_id = s.id
GROUP BY s.name;
SELECT AVG(t.salary) avg, s.name
FROM teacher t, subject s
WHERE t.subject_id = s.id
GROUP BY s.name
HAVING avg(t.salary) > 6000
ORDER BY avg DESC;
SELECT COUNT(*)
FROM teacher t, student s, class c
WHERE t.id = c.teacher_id
AND s.class_id = c.id
AND t.salary = (SELECT MIN(salary) FROM teacher);
主键与外键
外键约束
多多关系
SELECT s.name, b.name, tss.score
FROM student s, subject b, t_stu_subject_score tss
WHERE s.id = tss.stu_id
AND b.id = tss.subject_id
AND s.name = '李费水';
SELECT s.name, b.name
FROM student s, subject b, t_stu_subject_score tss
WHERE s.id = tss.stu_id
AND b.id = tss.subject_id
AND b.name = '语文';
内连接
语法
SELECT s.name, s.age, c.name
FROM student s
JOIN class c on c.id = s.class_id
WHERE c.name = '1年级1班';
SELECT t.name, s.name
FROM teacher t
JOIN subject s on s.id = t.subject_id
WHERE s.name = '英语';
SELECT c.name, t.name
FROM class c
JOIN teacher t ON c.teacher_id = t.id;
SELECT t.name, c.name, s.name, s.gender
FROM teacher t
JOIN class c ON t.id = c.teacher_id
JOIN student s ON c.id = s.class_id
WHERE t.name = '王克晶'
AND s.gender = '女';
SELECT s.name, ts.score, t.name
FROM teacher t
JOIN class c ON t.id = c.teacher_id
JOIN student s ON c.id = s.class_id
JOIN t_stu_subject_score ts ON s.id = ts.stu_id
JOIN subject b ON t.subject_id = b.id
WHERE t.name = '刘苍松'
AND b.name = '英语'
ORDER BY ts.score DESC;
SELECT count(*), l.name
FROM student s
JOIN location l ON s.location_id = l.id
GROUP BY l.name;
外连接
SELECT c.name, t.name
FROM class c LEFT JOIN teacher t ON c.teacher_id = t.id;
SELECT t.name, c.name
FROM class c RIGHT JOIN teacher t ON t.id = c.teacher_id;
SELECT t.name, c.name
FROM class c LEFT JOIN teacher t ON c.teacher_id = t.id
UNION
SELECT t.name, c.name
FROM class c RIGHT JOIN teacher t ON t.id = c.teacher_id;
自连接
SELECT t.name, m.name
FROM teacher t, teacher m
WHERE t.manager = m.id;
SELECT t.name, m.name
FROM teacher t, teacher m
WHERE t.manager = m.id
AND m.name = '刘苍松';
SELECT s.id, s.name, s.team_leader, c.name
FROM student s JOIN class c on c.id = s.class_id
WHERE c.name = '3年级2班'
AND s.id = s.team_leader;
SELECT t.name '班主任', m.name '班主任上司', s.name '学生姓名', s.birth '生日', c.name '所在班级'
FROM student s JOIN class c on c.id = s.class_id
JOIN teacher t ON c.teacher_id = t.id
JOIN teacher m ON t.manager = m.id
WHERE s.birth = (SELECT MIN(birth) FROM student);