MySQL查询语句

博主： AC
发布时间：2022 年 11 月 14 日
1347 次浏览
1 条评论
32234字数
分类： MySQL笔记

新建数据库

新建数据库并插入数据

-- 创建学生表
CREATE TABLE student(
    -- 学生编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 学生名称
    name VARCHAR(20) NOT NULL,
    -- 性别
    sex VARCHAR(10) NOT NULL,
    -- 生日
    birthday DATE,
    -- 所在班级
    class VARCHAR(20)
);

-- 创建教师表
CREATE TABLE teacher(
    -- 教师编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 教师名称
    name VARCHAR(20) NOT NULL,
    -- 性别
    sex VARCHAR(10) NOT NULL,
    -- 生日
    birthday DATE,
    -- 职称
  profession VARCHAR(20) NOT NULL, 
    -- 部门
  department VARCHAR(20) NOT NULL 
);

-- 创建课程表
CREATE TABLE course(
    -- 课程编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 课程名称
    name VARCHAR(20) NOT NULL,
    -- 教师编号
    t_no VARCHAR(20) NOT NULL,
    -- 表示该 t_no 来自于 teacher 表中的 no 字段值 
    FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- 成绩表
CREATE TABLE score(
    -- 学生编号
    s_no VARCHAR(20) NOT NULL,
    -- 课程编号
    c_no VARCHAR(20) NOT NULL,
    -- 成绩
    degree DECIMAL,
    -- 表示该 s_no,c_no 分别来自于 student,course表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no,c_no为联合主键
    PRIMARY KEY(s_no,c_no)
);

-- 查看所有表
SHOW TABLES;

-- 添加学生表数据
INSERT INTO student VALUES('101', '冯熠彤', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '蒋浩宇', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '姚绍辉', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '韦哲瀚', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '张烨华', '男', '1974-06-03', '95031');


-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

简单查询

查询表中的所有行数据

语法：

SELECT * FROM 表名

-- 查询 student 表中的所有行
SELECT * FROM student;

从表中查询指定的字段数据

语法：

SELECT 字段名 FROM 表名;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name,sex,class FROM student;

查询表中不重复的列

语法：

SELECT DISTINCT 字段名 FROM 表名;

-- 查询 teacher表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;

区间查询和运算符查询（AND）

BETWEEN xx AND xx: 查询区间, AND 表示 "并且"

区间查询

语法：

SELECT * FROM 表名 WHERE 字段名 BETWEEN 值 AND 值;

-- 查询 score 表中成绩在60-80之间的所有行
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

运算符查询

语法：

SELECT * FROM 表名 WHERE 字段名> 值 AND 字段名< 值 ;

-- 查询 score 表中成绩在60-80之间的所有行
SELECT * FROM score WHERE degree > 60 AND degree < 80;

从表中查询指定的值（IN）

IN: 查询规定中的多个值

语法：

SELECT * FROM 表名 WHERE 字段名 IN (值);

-- 查询 score 表中成绩为 85, 86 或 88 的行
SELECT * FROM score WHERE degree IN (85, 86, 88);

查询表中有关系的值（or）

or: 表示或者关系

语法：

SELECT * FROM 表名 WHERE 字段名 = '值' or sex = '值';

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
SELECT * FROM student WHERE class = '95031' or sex = '女';

查询表并将数据进行升降操作（DESC）（ASC）

ASC(默认)升序，从低到高

语法

SELECT * FROM 表名 ORDER BY 字段名 ASC;

-- 查询score表中的degree字段并从低到高输出
SELECT * FROM score ORDER BY degree ASC;

DESC: 降序，从高到低

语法：

SELECT * FROM 表名 ORDER BY 字段名 DESC;

-- 查询score表中的degree字段并从高到低输出
SELECT * FROM score ORDER BY degree DESC;

单个字段升序与其他字段降序查询表的所有行

语法：

SELECT * FROM 表名 ORDER BY 字段名 ASC, 字段名 DESC;

统计表中有这个值的数量（COUNT）

COUNT: 统计

语法：

SELECT COUNT(*) FROM 表名WHERE 字段名= '值';

-- 查询 "95031" 班的学生人数
SELECT COUNT(*) FROM student WHERE class = '95031';

子查询或排序查询

需求：查询 score 表中的最高分的学生学号和课程编号

子查询：

(SELECT MAX(degree) FROM score): 子查询，算出最高分

语法：

SELECT 字段名, 字段名 FROM 表名 WHERE 字段名 = (SELECT MAX( 字段名) FROM 表名);

-- 查询 score 表中的最高分的学生学号和课程编号
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

排序查询：

LIMIT r, n: 表示从第r行开始，查询n条数据

语法

SELECT 字段名, 字段名, 字段名 FROM 表名 ORDER BY 字段名 DESC LIMIT 0, 1;

SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

分组查询

分组计算平均成绩（AVG）

查询每门课的平均成绩

AVG: 平均值

语法：

SELECT AVG(分数) FROM 表名 WHERE 科目编号 = '科目';

-- 查询 c_no列中,值=3-105分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
-- 查询 c_no列中,值=3-245分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
-- 查询 c_no列中,值=6-166分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

分组查询（GROUP BY）

分组查询分数平均值

语法：

SELECT 科目编号, AVG(分数) FROM 表名 GROUP BY 科目编号;

SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

分组条件与模糊查询

查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。

SELECT * FROM score;
-- c_no 课程编号
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

分析表发现，至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 ，以 3 开头的课程是 3-105 、3-245 。也就是说，我们要查询所有 3-105 和 3-245 的 degree 平均分。

-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';

-- 把前面的SQL语句拼接起来，
-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

多表查询

示列1

查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree 。

SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | 冯熠彤    |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 蒋浩宇    |
| 105 | 王芳      |
| 106 | 陆军      |
| 107 | 姚绍辉    |
| 108 | 韦哲瀚    |
| 109 | 张烨华    |
+-----+-----------+

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

通过分析可以发现，只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了，如何做呢？

-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score 
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 张烨华    | 3-105 |     76 |
| 张烨华    | 3-245 |     68 |
| 张烨华    | 6-166 |     81 |
+-----------+-------+--------+

示列2

查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。

只有 score 关联学生的 no ，因此只要查询 score 表，就能找出所有和学生相关的 no 和 degree ：

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

SELECT no,name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。

-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 计算机导论      |     92 |
| 105  | 计算机导论      |     88 |
| 109  | 计算机导论      |     76 |
| 103  | 操作系统        |     86 |
| 105  | 操作系统        |     75 |
| 109  | 操作系统        |     68 |
| 103  | 数字电路        |     85 |
| 105  | 数字电路        |     79 |
| 109  | 数字电路        |     81 |
+------+-----------------+--------+

示列3

查询某选修课程多于5个同学的教师姓名。

首先在 teacher 表中，根据 no 字段来判断该教师的同一门课程是否有至少5名学员选修：

-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李诚   |
| 825 | 王萍   |
| 831 | 刘冰   |
| 856 | 张旭   |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- 在这里找到对应的条件
);

查看和教师编号有有关的表的信息：

SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+

我们已经找到和教师编号有关的字段就在 course 表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据 score 表来查询：

-- 在此之前向 score 插入一些数据，以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');

-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+

根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：

SELECT t_no FROM course WHERE no IN (
    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825  |
+------+

在 teacher 表中，根据筛选出来的教师编号找到教师姓名：

SELECT name FROM teacher WHERE no IN (
    -- 最终条件
    SELECT t_no FROM course WHERE no IN (
        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
    )
);
+--------+
| name   |
+--------+
| 王萍   |
+--------+

三表关联查询

查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。

只有 score 表中关联学生的学号和课堂号，我们只要围绕着 score 这张表查询就好了。

SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

只要把 s_no 和 c_no 替换成 student 和 srouse 表中对应的 name 字段值就好了。

首先把 s_no 替换成 student 表中的 name 字段：

SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 张烨华    | 3-105 |     76 |
| 张烨华    | 3-245 |     68 |
| 张烨华    | 6-166 |     81 |
+-----------+-------+--------+

再把 c_no 替换成 course 表中的 name 字段：

-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

-- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替。
SELECT student.name as s_name, course.name as c_name, degree 
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;
+-----------+-----------------+--------+
| s_name    | c_name          | degree |
+-----------+-----------------+--------+
| 王丽      | 计算机导论      |     92 |
| 王丽      | 操作系统        |     86 |
| 王丽      | 数字电路        |     85 |
| 王芳      | 计算机导论      |     88 |
| 王芳      | 操作系统        |     75 |
| 王芳      | 数字电路        |     79 |
| 张烨华    | 计算机导论      |     76 |
| 张烨华    | 操作系统        |     68 |
| 张烨华    | 数字电路        |     81 |
+-----------+-----------------+--------+

子查询

示列1

查询在 3-105 课程中，所有成绩高于 109 号同学的记录。

首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。

SELECT * FROM score 
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 105  | 3-105 |     88 |
+------+-------+--------+

示列2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 6-166 |     79 |
| 109  | 6-166 |     81 |
+------+-------+--------+

示列3

查询 “计算机系” 课程的成绩表。

思路是，先找出 course 表中所有 计算机系 课程的编号，然后根据这个编号查询 score 表。

-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no,name,department FROM teacher WHERE department = '计算机系';
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+-----+--------+--------------+

-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

示列4

查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+

示列5

查询 "男" 教师及其所上的课程。

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系统     | 804  |
| 6-166 | 数字电路     | 856  |
+-------+--------------+------+

示列6

查询和 "匡明同性别的所有同学 name 。

-- 首先将匡明的性别作为条件取出来
SELECT sex FROM student WHERE name = '匡明';
+-----+
| sex |
+-----+
| 男  |
+-----+
-- 根据性别查询 name 和 sex
SELECT name, sex FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '匡明'
);
+-----------+-----+
| name      | sex |
+-----------+-----+
| 冯熠彤    | 男  |
| 匡明      | 男  |
| 蒋浩宇    | 男  |
| 陆军      | 男  |
| 姚绍辉    | 男  |
| 韦哲瀚    | 男  |
| 张烨华    | 男  |
+-----------+-----+

示列7

查询和 "李军" 同性别且同班的同学 name 。

SELECT name, sex, class FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '匡明'
) AND class = (
    SELECT class FROM student WHERE name = '匡明'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 匡明      | 男  | 95031 |
| 陆军      | 男  | 95031 |
| 韦哲瀚    | 男  | 95031 |
| 张烨华    | 男  | 95031 |
+-----------+-----+-------+

示列8

查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。

需要的 "计算机导论" 和性别为 "男" 的编号可以在 course 和 student 表中找到。

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
    SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+

按等级查询

建立一个 grade 表代表学生的成绩等级，并插入数据：

CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);

INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

查询所有学生的 s_no 、c_no 和 grade 列。

思路是，使用区间 ( BETWEEN ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。

SELECT s_no, c_no, grade FROM score, grade 
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+

子查询加分组求平均分

查询 95031 班学生每门课程的平均成绩。

在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：

-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

多层嵌套子查询

查询 '李诚' 教师任课的学生成绩表。

首先找到教师编号：

SELECT NO FROM teacher WHERE NAME = '李诚';
+-----+
| NO  |
+-----+
| 804 |
+-----+

通过 sourse 表找到该教师课程号：

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '李诚' );
+-------+
| NO    |
+-------+
| 3-245 |
+-------+

通过筛选出的课程号查询成绩表：

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = ( 
        SELECT no FROM teacher WHERE NAME = '李诚' 
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

函数

YEAR 函数与带 IN 关键字查询

查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。

-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));
+-----+-----------+------------+
| no  | name      | birthday   |
+-----+-----------+------------+
| 101 | 冯熠彤    | 1977-09-01 |
| 102 | 匡明      | 1975-10-02 |
| 105 | 王芳      | 1975-02-10 |
| 108 | 韦哲瀚    | 1975-02-10 |
+-----+-----------+------------+

YEAR 与 NOW 函数

查询 student 表中每个学生的姓名和年龄。

-- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name      | age  |
+-----------+------+
| 冯熠彤    |   45 |
| 匡明      |   47 |
| 王丽      |   46 |
| 蒋浩宇    |   46 |
| 王芳      |   47 |
| 陆军      |   48 |
| 姚绍辉    |   46 |
| 韦哲瀚    |   47 |
| 张烨华    |   48 |
+-----------+------+

MAX 与 MIN 函数

查询 student 表中最大和最小的 birthday 值。

SELECT MAX(birthday), MIN(birthday) FROM student;

+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+

MAX 函数与子查询

查询最高分同学的 score 表。

-- 找出最高成绩（该查询只能有一个结果）
SELECT MAX(degree) FROM score;
+-------------+
| MAX(degree) |
+-------------+
|          92 |
+-------------+

-- 根据上面的条件筛选出所有最高成绩表，
-- 该查询可能有多个结果，假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+

操作符

UNION 和 NOTIN 的使用

查询 计算机系 与 电子工程系 中的不同职称的教师。

-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 通过UNION合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);
+-----+--------+-----+------------+------------+-----------------+
| no  | name   | sex | birthday   | profession | department      |
+-----+--------+-----+------------+------------+-----------------+
| 804 | 李诚   | 男  | 1958-12-02 | 副教授     | 计算机系        |
| 856 | 张旭   | 男  | 1969-03-12 | 讲师       | 电子工程系      |
+-----+--------+-----+------------+------------+-----------------+

ANY 表示至少一个 - DESC ( 降序 )

查询课程 3-105 且成绩 <u>至少</u> 高于 3-245 的 score 表。

SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

-- ANY: 符合SQL语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

表示所有的 ALL

查询课程 3-105 且成绩高于 3-245 的 score 表。

-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+------+-------+--------+

NOTLIKE 模糊查询取反

查询 student 表中不姓 "王" 的同学记录。

-- NOT: 取反
-- LIKE: 模糊查询
mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
+-----+-----------+-----+------------+-------+

条件查询

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的 score 表。

-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 查询 score 表
SELECT degree FROM score;
+--------+
| degree |
+--------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+--------+

-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b)，
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

条件加组筛选

查询 student 表中至少有 2 名男生的 class 。

-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
+-----+-----------+-----+------------+-------+

-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

多段排序

以 class 和 birthday 从大到小的顺序查询 student 表。

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

连接查询

准备用于测试连接查询的数据：

CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+

分析两张表发现，person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话，person 中 cardId 字段值为 6 的行就插不进去，因为该 cardId 值在 card 表中并没有。

内连接

要查询这两张表中有关系的数据，可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。

-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉，结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;

注意：card 的整张表被连接到了右边。

左外连接

完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL 。

-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

右外链接

完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL 。

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

全外链接

完整显示两张表的全部数据。

-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

正文到此结束

本文作者：AC
本文链接：https://wangziwang.top/index.php/archives/204/
版权声明：本博客所有文章除特别声明外，均默认采用 CC BY-NC-SA 4.0 许可协议。

最后修改：2022 年 11 月 15 日

如果觉得我的文章对你有用，请随意赞赏

1 条评论

阿星
November 15th, 2022 at 05:25 pm

哇塞666啊

回复

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址 *

转转好物交易平台
评论数： 6
集合框架
评论数： 4
Linux环境安装
评论数： 3
测试用例总结
评论数： 3
初识Vue
评论数： 2

hfrwcbsidv
看的我热血沸腾啊www.jiwenlaw.com
pihgwqtaok
想想你的文章写的特别好www.jiwenlaw.com
kvemguonip
不错不错，我喜欢看 www.jiwenlaw.com
emhppbvqlg
不错不错，我喜欢看 www.jiwenlaw.com
hkojtdkrlt
不错不错，我喜欢看 https://www.ea55.com/

保险管理系统
评论数： 0
MySQL基本语法
评论数： 0
Ngrok
评论数： 0
初识MySQL
评论数： 0
关系数据库设计
评论数： 0

MySQL查询语句

AC • 2022 年 11 月 14 日

<h1>新建数据库</h1><h2>新建数据库并插入数据</h2><pre><code class="lang-sql">-- 创建学生表
CREATE TABLE student(
    -- 学生编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 学生名称
    name VARCHAR(20) NOT NULL,
    -- 性别
    sex VARCHAR(10) NOT NULL,
    -- 生日
    birthday DATE,
    -- 所在班级
    class VARCHAR(20)
);

-- 创建教师表
CREATE TABLE teacher(
    -- 教师编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 教师名称
    name VARCHAR(20) NOT NULL,
    -- 性别
    sex VARCHAR(10) NOT NULL,
    -- 生日
    birthday DATE,
    -- 职称
  profession VARCHAR(20) NOT NULL, 
    -- 部门
  department VARCHAR(20) NOT NULL 
);

-- 创建课程表
CREATE TABLE course(
    -- 课程编号 设置为主键
    no VARCHAR(20) PRIMARY KEY,
    -- 课程名称
    name VARCHAR(20) NOT NULL,
    -- 教师编号
    t_no VARCHAR(20) NOT NULL,
    -- 表示该 t_no 来自于 teacher 表中的 no 字段值 
    FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- 成绩表
CREATE TABLE score(
    -- 学生编号
    s_no VARCHAR(20) NOT NULL,
    -- 课程编号
    c_no VARCHAR(20) NOT NULL,
    -- 成绩
    degree DECIMAL,
    -- 表示该 s_no,c_no 分别来自于 student,course表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no,c_no为联合主键
    PRIMARY KEY(s_no,c_no)
);

-- 查看所有表
SHOW TABLES;

-- 添加学生表数据
INSERT INTO student VALUES('101', '冯熠彤', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '蒋浩宇', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '姚绍辉', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '韦哲瀚', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '张烨华', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;</code></pre><h1>简单查询</h1><h2>查询表中的所有行数据</h2><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名</code></strong></p><pre><code class="lang-sql">-- 查询 student 表中的所有行
SELECT * FROM student;</code></pre><h2>从表中查询指定的字段数据</h2><p><strong>语法：</strong></p><p><strong><code>SELECT 字段名 FROM 表名;</code></strong></p><pre><code class="lang-sql">-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name,sex,class FROM student;</code></pre><h2>查询表中不重复的列</h2><p><strong>语法：</strong></p><p><strong><code>SELECT DISTINCT 字段名 FROM 表名;</code></strong></p><pre><code class="lang-sql">-- 查询 teacher表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;</code></pre><h2>区间查询和运算符查询    （AND）</h2><p><strong>BETWEEN xx <code>AND</code> xx: 查询区间, <code>AND</code> 表示 "并且"</strong></p><p><strong>区间查询</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 WHERE 字段名 BETWEEN 值 AND 值;</code></strong></p><pre><code class="lang-sql">-- 查询 score 表中成绩在60-80之间的所有行
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;</code></pre><p><strong>运算符查询</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 WHERE 字段名&gt; 值 AND 字段名&lt; 值 ;</code></strong></p><pre><code class="lang-sql">-- 查询 score 表中成绩在60-80之间的所有行
SELECT * FROM score WHERE degree &gt; 60 AND degree &lt; 80;</code></pre><h2>从表中查询指定的值    （IN）</h2><p><strong><code>IN</code>: 查询规定中的多个值</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 WHERE 字段名 IN (值);</code></strong></p><pre><code class="lang-sql">-- 查询 score 表中成绩为 85, 86 或 88 的行
SELECT * FROM score WHERE degree IN (85, 86, 88);</code></pre><h2>查询表中有关系的值    （or）</h2><p><strong><code>or</code>: 表示或者关系</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 WHERE 字段名 = '值' or sex = '值';</code></strong></p><pre><code class="lang-sql">-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
SELECT * FROM student WHERE class = '95031' or sex = '女';</code></pre><h2>查询表并将数据进行升降操作    （DESC）（ASC）</h2><p><strong><code>ASC</code>(默认)升序，从低到高</strong></p><p><strong>语法</strong></p><p><strong><code>SELECT * FROM 表名 ORDER BY 字段名 ASC;</code></strong></p><pre><code class="lang-sql">-- 查询score表中的degree字段并从低到高输出
SELECT * FROM score ORDER BY degree ASC;</code></pre><p><strong><code>DESC</code>: 降序，从高到低</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 ORDER BY 字段名 DESC;</code></strong></p><pre><code class="lang-sql">-- 查询score表中的degree字段并从高到低输出
SELECT * FROM score ORDER BY degree DESC;</code></pre><p><strong>单个字段升序与其他字段降序查询表的所有行</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT * FROM 表名 ORDER BY 字段名 ASC, 字段名 DESC;</code></strong></p><h2>统计表中有这个值的数量    （COUNT）</h2><p><strong><code>COUNT</code>: 统计</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT COUNT(*) FROM 表名WHERE 字段名= '值';</code></strong></p><pre><code class="lang-sql">-- 查询 &quot;95031&quot; 班的学生人数
SELECT COUNT(*) FROM student WHERE class = '95031';</code></pre><h2>子查询或排序查询</h2><p><strong>需求：查询 score 表中的最高分的学生学号和课程编号</strong></p><p><strong>子查询：</strong></p><p><code>(SELECT MAX(degree) FROM score)</code>: 子查询，算出最高分</p><p><strong>语法：</strong></p><p><strong><code>SELECT 字段名, 字段名 FROM 表名 WHERE  字段名 = (SELECT MAX( 字段名) FROM 表名);</code></strong></p><pre><code class="lang-sql">-- 查询 score 表中的最高分的学生学号和课程编号
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);</code></pre><p><strong>排序查询：</strong></p><p><code>LIMIT r, n</code>: 表示从第r行开始，查询n条数据</p><p>语法</p><p>SELECT  字段名,  字段名, 字段名 FROM 表名 ORDER BY 字段名 DESC LIMIT 0, 1;</p><pre><code class="lang-sql">SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;</code></pre><h1>分组查询</h1><h2>分组计算平均成绩    （AVG）</h2><p><strong>查询每门课的平均成绩</strong></p><p><strong><code>AVG</code>: 平均值</strong></p><p><strong>语法：</strong></p><p><strong><code>SELECT AVG(分数) FROM 表名 WHERE 科目编号 = '科目';</code></strong></p><pre><code class="lang-sql">-- 查询 c_no列中,值=3-105分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
-- 查询 c_no列中,值=3-245分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
-- 查询 c_no列中,值=6-166分数的平均数
SELECT AVG(degree) FROM score WHERE c_no = '6-166';</code></pre><h2>分组查询    （GROUP BY）</h2><p><strong>分组查询分数平均值</strong></p><p>语法：</p><p><strong><code>SELECT 科目编号, AVG(分数) FROM 表名 GROUP BY 科目编号;</code></strong></p><pre><code class="lang-sql">SELECT c_no, AVG(degree) FROM score GROUP BY c_no;</code></pre><h2>分组条件与模糊查询</h2><p><strong>查询 <code>score</code> 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。</strong></p><pre><code class="lang-sql">SELECT * FROM score;
-- c_no 课程编号
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><p>分析表发现，至少有 2 名学生选修的课程是 <code>3-105</code> 、<code>3-245</code> 、<code>6-166</code> ，以 3 开头的课程是 <code>3-105</code> 、<code>3-245</code> 。也就是说，我们要查询所有 <code>3-105</code> 和 <code>3-245</code> 的 <code>degree</code> 平均分。</p><pre><code class="lang-sql">-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) &gt;= 2

-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询，&quot;%&quot; 是一个通配符，匹配 &quot;3&quot; 后面的任意字符。
AND c_no LIKE '3%';

-- 把前面的SQL语句拼接起来，
-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) &gt;= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+</code></pre><h1>多表查询</h1><h2>示列1</h2><p><strong>查询所有学生的 <code>name</code>，以及该学生在 <code>score</code> 表中对应的 <code>c_no</code> 和 <code>degree</code> 。</strong></p><pre><code class="lang-sql">SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | 冯熠彤    |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 蒋浩宇    |
| 105 | 王芳      |
| 106 | 陆军      |
| 107 | 姚绍辉    |
| 108 | 韦哲瀚    |
| 109 | 张烨华    |
+-----+-----------+

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><p>通过分析可以发现，只要把 <code>score</code> 表中的 <code>s_no</code> 字段值替换成 <code>student</code> 表中对应的 <code>name</code> 字段值就可以了，如何做呢？</p><pre><code class="lang-sql">-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score 
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 张烨华    | 3-105 |     76 |
| 张烨华    | 3-245 |     68 |
| 张烨华    | 6-166 |     81 |
+-----------+-------+--------+</code></pre><h2>示列2</h2><p><strong>查询所有学生的 <code>no</code> 、课程名称 ( <code>course</code> 表中的 <code>name</code> ) 和成绩 ( <code>score</code> 表中的 <code>degree</code> ) 列。</strong></p><p>只有 <code>score</code> 关联学生的 <code>no</code> ，因此只要查询 <code>score</code> 表，就能找出所有和学生相关的 <code>no</code> 和 <code>degree</code> ：</p><pre><code class="lang-sql">SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

SELECT no,name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+</code></pre><p>只要把 <code>score</code> 表中的 <code>c_no</code> 替换成 <code>course</code> 表中对应的 <code>name</code> 字段值就可以了。</p><pre><code class="lang-sql">-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 计算机导论      |     92 |
| 105  | 计算机导论      |     88 |
| 109  | 计算机导论      |     76 |
| 103  | 操作系统        |     86 |
| 105  | 操作系统        |     75 |
| 109  | 操作系统        |     68 |
| 103  | 数字电路        |     85 |
| 105  | 数字电路        |     79 |
| 109  | 数字电路        |     81 |
+------+-----------------+--------+</code></pre><h2>示列3</h2><p><strong>查询某选修课程多于5个同学的教师姓名。</strong></p><p>首先在 <code>teacher</code> 表中，根据 <code>no</code> 字段来判断该教师的同一门课程是否有至少5名学员选修：</p><pre><code class="lang-sql">-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李诚   |
| 825 | 王萍   |
| 831 | 刘冰   |
| 856 | 张旭   |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- 在这里找到对应的条件
);</code></pre><p>查看和教师编号有有关的表的信息：</p><pre><code class="lang-sql">SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+</code></pre><p>我们已经找到和教师编号有关的字段就在 <code>course</code> 表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据 <code>score</code> 表来查询：</p><pre><code class="lang-sql">-- 在此之前向 score 插入一些数据，以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');

-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+</code></pre><p>根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：</p><pre><code class="lang-sql">SELECT t_no FROM course WHERE no IN (
    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5
);
+------+
| t_no |
+------+
| 825  |
+------+</code></pre><p>在 <code>teacher</code> 表中，根据筛选出来的教师编号找到教师姓名：</p><pre><code class="lang-sql">SELECT name FROM teacher WHERE no IN (
    -- 最终条件
    SELECT t_no FROM course WHERE no IN (
        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) &gt; 5
    )
);
+--------+
| name   |
+--------+
| 王萍   |
+--------+</code></pre><h2>三表关联查询</h2><p><strong>查询所有学生的 <code>name</code> 、课程名 ( <code>course</code> 表中的 <code>name</code> ) 和 <code>degree</code> 。</strong></p><p>只有 <code>score</code> 表中关联学生的学号和课堂号，我们只要围绕着 <code>score</code> 这张表查询就好了。</p><pre><code class="lang-sql">SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><p>只要把 <code>s_no</code> 和 <code>c_no</code> 替换成 <code>student</code> 和 <code>srouse</code> 表中对应的 <code>name</code> 字段值就好了。</p><p>首先把 <code>s_no</code> 替换成 <code>student</code> 表中的 <code>name</code> 字段：</p><pre><code class="lang-sql">SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 张烨华    | 3-105 |     76 |
| 张烨华    | 3-245 |     68 |
| 张烨华    | 6-166 |     81 |
+-----------+-------+--------+</code></pre><p>再把 <code>c_no</code> 替换成 <code>course</code> 表中的 <code>name</code> 字段：</p><pre><code class="lang-sql">-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

-- 由于字段名存在重复，使用 &quot;表名.字段名 as 别名&quot; 代替。
SELECT student.name as s_name, course.name as c_name, degree 
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;
+-----------+-----------------+--------+
| s_name    | c_name          | degree |
+-----------+-----------------+--------+
| 王丽      | 计算机导论      |     92 |
| 王丽      | 操作系统        |     86 |
| 王丽      | 数字电路        |     85 |
| 王芳      | 计算机导论      |     88 |
| 王芳      | 操作系统        |     75 |
| 王芳      | 数字电路        |     79 |
| 张烨华    | 计算机导论      |     76 |
| 张烨华    | 操作系统        |     68 |
| 张烨华    | 数字电路        |     81 |
+-----------+-----------------+--------+</code></pre><h1>子查询</h1><h2>示列1</h2><p><strong>查询在 <code>3-105</code> 课程中，所有成绩高于 <code>109</code> 号同学的记录。</strong></p><p>首先筛选出课堂号为 <code>3-105</code> ，在找出所有成绩高于 <code>109</code> 号同学的的行。</p><pre><code class="lang-sql">SELECT * FROM score 
WHERE c_no = '3-105'
AND degree &gt; (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 105  | 3-105 |     88 |
+------+-------+--------+</code></pre><h2>示列2</h2><p><strong>查询所有成绩高于 <code>109</code> 号同学的 <code>3-105</code> 课程成绩记录。</strong></p><pre><code class="lang-sql">-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree &gt; (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 6-166 |     79 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><h2>示列3</h2><p><strong>查询 “计算机系” 课程的成绩表。</strong></p><p>思路是，先找出 <code>course</code> 表中所有 <code>计算机系</code> 课程的编号，然后根据这个编号查询 <code>score</code> 表。</p><pre><code class="lang-sql">-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no,name,department FROM teacher WHERE department = '计算机系';
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+-----+--------+--------------+

-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+</code></pre><h2>示列4</h2><p><strong>查询所有任课 ( 在 <code>course</code> 表里有课程 ) 教师的 <code>name</code> 和 <code>department</code></strong> 。</p><pre><code class="lang-sql">SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+</code></pre><h2>示列5</h2><p><strong>查询 "男" 教师及其所上的课程。</strong></p><pre><code class="lang-sql">SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系统     | 804  |
| 6-166 | 数字电路     | 856  |
+-------+--------------+------+</code></pre><h2>示列6</h2><p><strong>查询和 "匡明 同性别的所有同学 <code>name</code> 。</strong></p><pre><code class="lang-sql">-- 首先将匡明的性别作为条件取出来
SELECT sex FROM student WHERE name = '匡明';
+-----+
| sex |
+-----+
| 男  |
+-----+
-- 根据性别查询 name 和 sex
SELECT name, sex FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '匡明'
);
+-----------+-----+
| name      | sex |
+-----------+-----+
| 冯熠彤    | 男  |
| 匡明      | 男  |
| 蒋浩宇    | 男  |
| 陆军      | 男  |
| 姚绍辉    | 男  |
| 韦哲瀚    | 男  |
| 张烨华    | 男  |
+-----------+-----+</code></pre><h2>示列7</h2><p><strong>查询和 "李军" 同性别且同班的同学 <code>name</code> 。</strong></p><pre><code class="lang-sql">SELECT name, sex, class FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '匡明'
) AND class = (
    SELECT class FROM student WHERE name = '匡明'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 匡明      | 男  | 95031 |
| 陆军      | 男  | 95031 |
| 韦哲瀚    | 男  | 95031 |
| 张烨华    | 男  | 95031 |
+-----------+-----+-------+</code></pre><h2>示列8</h2><p><strong>查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。</strong></p><p>需要的 "计算机导论" 和性别为 "男" 的编号可以在 <code>course</code> 和 <code>student</code> 表中找到。</p><pre><code class="lang-sql">SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
    SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+</code></pre><h2>按等级查询</h2><p>建立一个 <code>grade</code> 表代表学生的成绩等级，并插入数据：</p><pre><code class="lang-sql">CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);

INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+</code></pre><p><strong>查询所有学生的 <code>s_no</code> 、<code>c_no</code> 和 <code>grade</code> 列。</strong></p><p>思路是，使用区间 ( <code>BETWEEN</code> ) 查询，判断学生的成绩 ( <code>degree</code> )  在 <code>grade</code> 表的 <code>low</code> 和 <code>upp</code> 之间。</p><pre><code class="lang-sql">SELECT s_no, c_no, grade FROM score, grade 
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+</code></pre><h2>子查询加分组求平均分</h2><p><strong>查询 <code>95031</code> 班学生每门课程的平均成绩。</strong></p><p>在 <code>score</code> 表中根据 <code>student</code>  表的学生编号筛选出学生的课堂号和成绩：</p><pre><code class="lang-sql">-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><p>这时只要将 <code>c_no</code> 分组一下就能得出 <code>95031</code> 班学生每门课的平均成绩：</p><pre><code class="lang-sql">SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+</code></pre><h2>多层嵌套子查询</h2><p><strong>查询 <code>'李诚'</code> 教师任课的学生成绩表。</strong></p><p>首先找到教师编号：</p><pre><code class="lang-sql">SELECT NO FROM teacher WHERE NAME = '李诚';
+-----+
| NO  |
+-----+
| 804 |
+-----+</code></pre><p>通过 <code>sourse</code> 表找到该教师课程号：</p><pre><code class="lang-sql">SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '李诚' );
+-------+
| NO    |
+-------+
| 3-245 |
+-------+</code></pre><p>通过筛选出的课程号查询成绩表：</p><pre><code class="lang-sql">SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = ( 
        SELECT no FROM teacher WHERE NAME = '李诚' 
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+</code></pre><h1>函数</h1><h2>YEAR 函数与带 IN 关键字查询</h2><p><strong>查询所有和 <code>101</code> 、<code>108</code> 号学生同年出生的 <code>no</code> 、<code>name</code> 、<code>birthday</code> 列。</strong></p><pre><code class="lang-sql">-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));
+-----+-----------+------------+
| no  | name      | birthday   |
+-----+-----------+------------+
| 101 | 冯熠彤    | 1977-09-01 |
| 102 | 匡明      | 1975-10-02 |
| 105 | 王芳      | 1975-02-10 |
| 108 | 韦哲瀚    | 1975-02-10 |
+-----+-----------+------------+</code></pre><h2>YEAR 与 NOW 函数</h2><p><strong>查询 <code>student</code> 表中每个学生的姓名和年龄。</strong></p><pre><code class="lang-sql">-- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name      | age  |
+-----------+------+
| 冯熠彤    |   45 |
| 匡明      |   47 |
| 王丽      |   46 |
| 蒋浩宇    |   46 |
| 王芳      |   47 |
| 陆军      |   48 |
| 姚绍辉    |   46 |
| 韦哲瀚    |   47 |
| 张烨华    |   48 |
+-----------+------+</code></pre><h2>MAX 与 MIN 函数</h2><p><strong>查询 <code>student</code> 表中最大和最小的 <code>birthday</code> 值。</strong></p><pre><code class="lang-sql">SELECT MAX(birthday), MIN(birthday) FROM student;

+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+</code></pre><h2>MAX 函数与子查询</h2><p><strong>查询最高分同学的 <code>score</code> 表。</strong></p><pre><code class="lang-sql">-- 找出最高成绩（该查询只能有一个结果）
SELECT MAX(degree) FROM score;
+-------------+
| MAX(degree) |
+-------------+
|          92 |
+-------------+

-- 根据上面的条件筛选出所有最高成绩表，
-- 该查询可能有多个结果，假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+</code></pre><h1>操作符</h1><h2>UNION 和 NOTIN 的使用</h2><p><strong>查询 <code>计算机系</code> 与 <code>电子工程系</code> 中的不同职称的教师。</strong></p><pre><code class="lang-sql">-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 通过UNION合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);
+-----+--------+-----+------------+------------+-----------------+
| no  | name   | sex | birthday   | profession | department      |
+-----+--------+-----+------------+------------+-----------------+
| 804 | 李诚   | 男  | 1958-12-02 | 副教授     | 计算机系        |
| 856 | 张旭   | 男  | 1969-03-12 | 讲师       | 电子工程系      |
+-----+--------+-----+------------+------------+-----------------+</code></pre><h2>ANY 表示至少一个 - DESC ( 降序 )</h2><p><strong>查询课程 <code>3-105</code> 且成绩 &lt;u&gt;至少&lt;/u&gt; 高于 <code>3-245</code> 的 <code>score</code> 表。</strong></p><pre><code class="lang-sql">SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

-- ANY: 符合SQL语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree &gt; ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+</code></pre><h2>表示所有的 ALL</h2><p><strong>查询课程 <code>3-105</code> 且成绩高于 <code>3-245</code> 的 <code>score</code> 表。</strong></p><pre><code class="lang-sql">-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree &gt; ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+------+-------+--------+</code></pre><h2>NOTLIKE 模糊查询取反</h2><p><strong>查询 <code>student</code> 表中不姓 "王" 的同学记录。</strong></p><pre><code class="lang-sql">-- NOT: 取反
-- LIKE: 模糊查询
mysql&gt; SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
+-----+-----------+-----+------------+-------+</code></pre><h1>条件查询</h1><h2>复制表的数据作为条件查询</h2><p><strong>查询某课程成绩比该课程平均成绩低的 <code>score</code> 表。</strong></p><pre><code class="lang-sql">-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 查询 score 表
SELECT degree FROM score;
+--------+
| degree |
+--------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+--------+

-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b)，
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree &lt; (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+</code></pre><h2>条件加组筛选</h2><p><strong>查询 <code>student</code> 表中至少有 2 名男生的 <code>class</code> 。</strong></p><pre><code class="lang-sql">-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
+-----+-----------+-----+------------+-------+

-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) &gt; 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+</code></pre><h2>多段排序</h2><p><strong>以 <code>class</code> 和 <code>birthday</code> 从大到小的顺序查询 <code>student</code> 表。</strong></p><pre><code class="lang-sql">SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 蒋浩宇    | 男  | 1976-02-20 | 95033 |
| 107 | 姚绍辉    | 男  | 1976-02-20 | 95033 |
| 101 | 冯熠彤    | 男  | 1977-09-01 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 109 | 张烨华    | 男  | 1974-06-03 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 108 | 韦哲瀚    | 男  | 1975-02-10 | 95031 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+</code></pre><h1>连接查询</h1><p>准备用于测试连接查询的数据：</p><pre><code class="lang-sql">CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+</code></pre><p>分析两张表发现，<code>person</code> 表并没有为 <code>cardId</code> 字段设置一个在 <code>card</code> 表中对应的 <code>id</code> 外键。如果设置了的话，<code>person</code> 中 <code>cardId</code> 字段值为 <code>6</code> 的行就插不进去，因为该 <code>cardId</code> 值在 <code>card</code> 表中并没有。</p><h2>内连接</h2><p>要查询这两张表中有关系的数据，可以使用 <code>INNER JOIN</code> ( 内连接 ) 将它们连接在一起。</p><pre><code class="lang-sql">-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉，结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;</code></pre><blockquote><p>注意：<code>card</code> 的整张表被连接到了右边。</p></blockquote><h2>左外连接</h2><p>完整显示左边的表 ( <code>person</code> ) ，右边的表如果符合条件就显示，不符合则补 <code>NULL</code> 。</p><pre><code class="lang-sql">-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+</code></pre><h2>右外链接</h2><p>完整显示右边的表 ( <code>card</code> ) ，左边的表如果符合条件就显示，不符合则补 <code>NULL</code> 。</p><pre><code class="lang-sql">SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+</code></pre><h2>全外链接</h2><p>完整显示两张表的全部数据。</p><pre><code class="lang-sql">-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+</code></pre>

新建数据库

新建数据库并插入数据

简单查询

查询表中的所有行数据

从表中查询指定的字段数据

查询表中不重复的列

区间查询和运算符查询 （AND）

从表中查询指定的值 （IN）

查询表中有关系的值 （or）

查询表并将数据进行升降操作 （DESC）（ASC）

统计表中有这个值的数量 （COUNT）

子查询或排序查询

分组查询

分组计算平均成绩 （AVG）

分组查询 （GROUP BY）

分组条件与模糊查询

多表查询

示列1

示列2

示列3

三表关联查询

子查询

示列1

示列2

示列3

示列4

示列5

示列6

示列7

示列8

按等级查询

子查询加分组求平均分

多层嵌套子查询

函数

YEAR 函数与带 IN 关键字查询

YEAR 与 NOW 函数

MAX 与 MIN 函数

MAX 函数与子查询

操作符

UNION 和 NOTIN 的使用

ANY 表示至少一个 - DESC ( 降序 )

表示所有的 ALL

NOTLIKE 模糊查询取反

条件查询

复制表的数据作为条件查询

条件加组筛选

多段排序

连接查询

内连接

左外连接

右外链接

全外链接

1 条评论

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

MySQL查询语句

区间查询和运算符查询（AND）

从表中查询指定的值（IN）

查询表中有关系的值（or）

查询表并将数据进行升降操作（DESC）（ASC）

统计表中有这个值的数量（COUNT）

分组计算平均成绩（AVG）

分组查询（GROUP BY）

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款