MySQL知识汇总（第二篇）DQL语言 基本操作+联结查询

前言：部分内容学习自B站视频（https://www.bilibili.com/video/BV1NJ411J79W?p=20），
部分内容参考自《MySQL必知必会》
本文用于回顾MySQL知识点

数据查询语言（DQL语言）

SELECT完整语法格式：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条

1.指定查询

1.查询表中所有字段：SELECT * FROM 表名

2.查询表中指定字段：SELECT 字段名1, 字段名2, … FROM 表名

3.给查询出的指定列起别名：SELECT 字段名1 AS 别名1 FROM 表名
（也可以给表起别名）

4.使用函数查询：SELECT CONCAT(要连接的文本, 字段名) AS 别名 FROM 表名;

5.去重查询：SELECT DISTINCT 字段名 FROM 表名

6.查询系统版本：SELECT VERSION()

7.用来计算：SELECT 字段名*3 - 1 AS 计算结果列名

8.查询自增的步长：SELECT @@auto_increment_increment

9.附加条件的查询：SELECT 字段名 FROM WHERE 条件

2.条件->模糊查询

（1）LIKE

1.匹配a开头的此字段任意长度的行：SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ‘a%’;

2.匹配长度为3，中间为a的字段所在行：SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ‘a’;

（2）IN

给定一个用于匹配的值的集合：SELECT 字段名 FROM 表名 WHERE 字段名 IN (值1, 值2, …);

（3）IS [NOT] NULL

SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL

（4）BETWEEN AND

SELECT 字段名 FROM 表名 WHERE 字段名 BWTWEEN 值1　AND　值2；

2.联结查询

首先建立用于测试的两个表：

CREATE TABLE student(
	studentNo INT(10) NOT NULL AUTO_INCREMENT,
	studentName VARCHAR(50) NOT NULL,
	PRIMARY KEY (studentNo)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE result(
	resultNo INT(10) NOT NULL AUTO_INCREMENT,
	studentNo INT(10) NOT NULL,
	subjectNo INT(10) NULL,
	studentResult INT(3) NULL,
	PRIMARY KEY (resultNo)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `subject`(
	subjectNo INT(10) NOT NULL AUTO_INCREMENT,
	subjectName VARCHAR(50) NOT NULL,
	PRIMARY KEY (subjectNo)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

首先插入有正常联系的数据：

INSERT INTO student(studentName) VALUES ('mike'), ('john'), ('zlc');

INSERT INTO result(studentNo, subjectNo, studentResult) VALUES (1, 21, 95), (2, 22, 98), (3, 23, 99);

INSERT INTO `subject`(subjectNo, subjectName) VALUES (21, 'adsf'), (22, 'zxcv'), (23, 'sdsd'), (25, 'rtty');

再插入用于测试左右联结的数据：

INSERT INTO student(studentName) VALUES ('aaa');

INSERT INTO result(studentNo, subjectNo, studentResult) VALUES (5, 25, 100);

student表：

result表：

可见，student表中最后一行，与result表中最后一行并无互联关系，因此，可用于测试左右联结

（1）内部联结（也称等值联结）

只包含在两个表之间有关联的行

INNER JOIN

INNER JOIN查询结果：

SELECT s.studentNo, studentName, subjectNo, studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo;

（2）外部联结

可以包含没有关联的行

LEFT OUTER JOIN（左联结）

LEFT JOIN查询结果：

SELECT s.studentNo, studentName, subjectNo, studentResult
FROM student AS s
LEFT OUTER JOIN result AS r
ON s.studentNo = r.studentNo;

RIGHT OUTER JOIN（右联结）

RIGHT JOIN查询结果：

SELECT s.studentNo, studentName, subjectNo, studentResult
FROM student AS s
RIGHT OUTER JOIN result AS r
ON s.studentNo = r.studentNo;

总结

操作	描述
INNER JOIN	查询出既匹配左右表中都符合条件的行
LEFT JOIN	查询出左表的所有匹配行的字段，不管右表是否符合匹配条件
RIGHT JOIN	查询出右表的所有匹配行的字段，不管左表是否符合匹配条件

附：应用举例

1.查询缺考的同学

SELECT s.studentNo, studentName, subjectNo, studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE studentResult IS NULL;

2.查询参加了考试的同学信息，并显示科目信息

SELECT s.studentNo, studentName, r.subjectNo, subjectName, studentResult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo;

（3）自联结

一个表自己和自己联结

建立测试表

CREATE TABLE category(
	categoryid INT(10) UNSIGNED NOT NULL COMMENT '主题id',
	pid INT(10) NOT NULL COMMENT '父id',
	categoryName VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(categoryid)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO category (categoryid, pid, categoryName)
VALUES('2', '1', '信息技术'),
('3', '1', '软件开发'),
('4', '3', '数据库'),
('5', '1', '美术设计'),
('6', '3', 'Web开发'),
('7', '5', 'PS技术'),
('8', '2', '办公信息');

自联结查询结果：

SELECT a.categoryName AS '父栏目', b.categoryName AS '子栏目'
FROM category AS a, category AS b
WHERE a.categoryid = b.pid;

附：应用举例

1.查询参加了考试的同学信息(学号,学生姓名,科目名,分数)

SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

2.查询学员及所属的年级(学号,学生姓名,年级名)

SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

3.查询科目及所属的年级(科目名称,年级名称)

SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

4.查询数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)

SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'

（4）自然联结

自然联结排除多次出现，使每个列只返回一次

一般的内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结

3.排序与分页

1.排序（ORDER BY）：

ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

例子：

查询数据库结构-1的所有考试结果(学号 学生姓名 科目名称 成绩)并按成绩降序排序

SELECT s.studentNo， studentName, subjectName, studentResult
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY studentResult DESC;

2.分页：

SELECT 字段名 FROM 表名 LIMIT startIndex, num

• startIndex为行下标，如第一行下标为0
• num为查询的行数

推导：

• 第一页 : LIMIT 0,5
• 第二页 : LIMIT 5,5
• 第三页 : LIMIT 10,5
• …
• 第N页 : LIMIT (pageNo - 1) * pageSzie, pageSzie
  [pageNo:页码,pageSize:单页面显示条数]

4.子查询

嵌套的子查询是限制性最里面的sql，再将结果返回给上一层，以此类推

1.例子1：查询数据库结构-1的学生的（只有1个学生数据）所有考试结果（学号，科目编号，成绩），并按成绩降序排序

方法一：使用联结查询

SELECT studentNo, subjectNo, studentResult
FROM result r
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY studentResult DESC;

方法二：使用子查询

SELECT studentNo, subjectNo, studentResult
FROM result
WHERE subjectNo = (
	SELECT subjectNo FROM `subject`
	WHERE subjectName = '数据库结构-1'
)
ORDER BY subjectResult DESC;

2.例子2：高等数学-2分数不小于80分的学生的学号和姓名

方法一：联结查询+子查询

SELECT r.studentNo, studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
WHERE studentResult >= 80 AND subjectNo = (
	SELECT subjectNo FROM `subject`
	WHERE subjectName = '高等数学-2'
);

方法二：子查询

SELECT studentNo, studentName FROM student WHERE studentNo IN(
	SELECT studentNo FORM result
	WHERE studentResult >= 80 AND subjectNo = (
		SELECT subjectNo FROM `subject` WHERE subjectName = '高等数学-2'
	)
);