2.2 查询数据

准备工作：创建两个数据表。

CREATE TABLE students(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    class_id int NOT NULL,
    name varchar(20) NOT NULL,
    gender varchar(1) NOT NULL DEFAULT 'M' COMMENT '性别：M-男性；F-女性',
    score int NOT NULL
);
INSERT INTO students(class_id,name,gender,score) VALUES (1,"小明",'M',90),
    (1,"小红",'F',95),(1,"小军",'M',88),(1,"小米",'F',73),(2,"小白",'F',81),
    (2,"小兵",'M',55),(2,"小林",'M',85),(3,"小新",'F',91),(3,"小王",'M',89),
    (3,"小丽",'F',88);

CREATE TABLE classes(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL
);
INSERT INTO classes(name) VALUES ('一班'),('二班'),('三班'),('四班');

SELECT

使用SELECT * FROM <表名>可以查询一张表中的所有记录。

# 获取students中的所有数据
SELECT * from students;
# 获取classes中的所有数据
SELECT * from classes;

WHERE

使用where语句可以按照条件查询.

# 查询成绩>=80记录
SELECT * FROM students WHERE score>=80;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    90 | 小明   |
|  2 |        1 | F      |    95 | 小红   |
|  3 |        1 | M      |    88 | 小军   |
|  5 |        2 | F      |    81 | 小白   |
|  7 |        2 | M      |    85 | 小林   |
|  8 |        3 | F      |    91 | 小新   |
|  9 |        3 | M      |    89 | 小王   |
| 10 |        3 | F      |    88 | 小丽   |
+----+----------+--------+-------+--------+
*/

AND

使用and条件。

# 查询成绩>=0并且性别为男的记录
SELECT * FROM students WHERE score>=80 AND gender='M';
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    90 | 小明   |
|  3 |        1 | M      |    88 | 小军   |
|  7 |        2 | M      |    85 | 小林   |
|  9 |        3 | M      |    89 | 小王   |
+----+----------+--------+-------+--------+
*/

OR

使用or条件。

# 查询成绩>=0或者性别为男的记录
SELECT * FROM students WHERE score>=80 OR gender='M';
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    90 | 小明   |
|  2 |        1 | F      |    95 | 小红   |
|  3 |        1 | M      |    88 | 小军   |
|  5 |        2 | F      |    81 | 小白   |
|  6 |        2 | M      |    55 | 小兵   |
|  7 |        2 | M      |    85 | 小林   |
|  8 |        3 | F      |    91 | 小新   |
|  9 |        3 | M      |    89 | 小王   |
| 10 |        3 | F      |    88 | 小丽   |
+----+----------+--------+-------+--------+
*/

NOT

使用not条件。

# 查询不是2班的记录
SELECT * FROM students WHERE NOT class_id=2;
# 等价于
SELECT * FROM students WHERE class_id<>2;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    90 | 小明   |
|  2 |        1 | F      |    95 | 小红   |
|  3 |        1 | M      |    88 | 小军   |
|  4 |        1 | F      |    73 | 小米   |
|  8 |        3 | F      |    91 | 小新   |
|  9 |        3 | M      |    89 | 小王   |
| 10 |        3 | F      |    88 | 小丽   |
+----+----------+--------+-------+--------+
*/

IN

SELECT * FROM students WHERE gender IN ('M');
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | 小明   | M      |    90 |
|  3 |        1 | 小军   | M      |    88 |
|  6 |        2 | 小兵   | M      |    55 |
|  7 |        2 | 小林   | M      |    85 |
|  9 |        3 | 小王   | M      |    89 |
+----+----------+--------+--------+-------+

复合条件

# 查询成绩小于80或者大于90的所有男同学
SELECT * FROM students WHERE (score<80 OR score>90) AND gender='M';
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  6 |        2 | M      |    55 | 小兵   |
+----+----------+--------+-------+--------+
*/

投影查询

获取指定列。

# 获取id、score、name
SELECT id,score,name FROM students;
/*
+----+-------+--------+
| id | score | name   |
+----+-------+--------+
|  1 |    90 | 小明   |
|  2 |    95 | 小红   |
|  3 |    88 | 小军   |
|  4 |    73 | 小米   |
|  5 |    81 | 小白   |
|  6 |    55 | 小兵   |
|  7 |    85 | 小林   |
|  8 |    91 | 小新   |
|  9 |    89 | 小王   |
| 10 |    88 | 小丽   |
+----+-------+--------+
*/

# 获取列并且重命名列
SELECT id,score points,name FROM students;
/*
+----+--------+--------+
| id | points | name   |
+----+--------+--------+
|  1 |     90 | 小明   |
|  2 |     95 | 小红   |
|  3 |     88 | 小军   |
|  4 |     73 | 小米   |
|  5 |     81 | 小白   |
|  6 |     55 | 小兵   |
|  7 |     85 | 小林   |
|  8 |     91 | 小新   |
|  9 |     89 | 小王   |
| 10 |     88 | 小丽   |
+----+--------+--------+
*/

OEDER BY

使用oder by对结果进行排序，默认从低到高，使用desc从高到低。

# 成绩从低到高排序
SELECT id,name,gender,score FROM students ORDER BY score;
# 成绩从高到低排序
# SELECT id,name,gender,score FROM students ORDER BY score DESC;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  6 | 小兵   | M      |    55 |
|  4 | 小米   | F      |    73 |
|  5 | 小白   | F      |    81 |
|  7 | 小林   | M      |    85 |
|  3 | 小军   | M      |    88 |
| 10 | 小丽   | F      |    88 |
|  9 | 小王   | M      |    89 |
|  1 | 小明   | M      |    90 |
|  8 | 小新   | F      |    91 |
|  2 | 小红   | F      |    95 |
+----+--------+--------+-------+
*/

# 多个排序条件，ASC可以省略，下述两种方式等价
SELECT id,name,gender,score FROM students ORDER BY score DESC, gender;
# SELECT id,name,gender,score FROM students ORDER BY score DESC, gender ASC;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
|  9 | 小王   | M      |    89 |
| 10 | 小丽   | F      |    88 |
|  3 | 小军   | M      |    88 |
|  7 | 小林   | M      |    85 |
|  5 | 小白   | F      |    81 |
|  4 | 小米   | F      |    73 |
|  6 | 小兵   | M      |    55 |
+----+--------+--------+-------+
*/

# 条件+排序
# 查询一班的成绩，从高到低排序
SELECT id,name,gender,score
FROM students
WHERE class_id=1
ORDER BY score DESC;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  1 | 小明   | M      |    90 |
|  3 | 小军   | M      |    88 |
|  4 | 小米   | F      |    73 |
*/

LIMIT OFFSET

当记录非常多的时候，只显示一部分结果，使用limit <m> offset <n>，从n开始，最多取m条数据。PS. MySQL的记录从0开始。

# 获取记录
SELECT id,name,gender,score
from students
ORDER BY score DESC;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
|  9 | 小王   | M      |    89 |
|  3 | 小军   | M      |    88 |
| 10 | 小丽   | F      |    88 |
|  7 | 小林   | M      |    85 |
|  5 | 小白   | F      |    81 |
|  4 | 小米   | F      |    73 |
|  6 | 小兵   | M      |    55 |
+----+--------+--------+-------+
*/

# 获取从0开始的3条数据
SELECT id,name,gender,score
from students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
+----+--------+--------+-------+
*/

# 获取从2开始的5条数据
SELECT id,name,gender,score
from students
ORDER BY score DESC
LIMIT 5 OFFSET 2;
/*
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  1 | 小明   | M      |    90 |
|  9 | 小王   | M      |    89 |
|  3 | 小军   | M      |    88 |
| 10 | 小丽   | F      |    88 |
|  7 | 小林   | M      |    85 |
+----+--------+--------+-------+
*/

COUNT/SUM/MAX/MIN

# cout统计出现的行数
# 统计所有的行数
SELECT COUNT(*) FROM students;
# 为生成的表格重新命名
# SELECT COUNT(*) num FROM students;
/*
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
*/

# 条件统计
SELECT COUNT(*) boys FROM students WHERE gender='M';
/*
+------+
| boys |
+------+
|    5 |
+------+
*/

# sum统计总和，列属性必须为数字类型
SELECT SUM(score) sum FROM students WHERE gender='M';
/*
+------+
| sum  |
+------+
|  407 |
+------+
*/

# avg统计平均数，列属性必须为数字类型
SELECT AVG(score) avg_score FROM students WHERE gender='M';
/*
+-----------+
| avg_score |
+-----------+
|   81.4000 |
+-----------+
*/

# max/min操作
SELECT MIN(score) min_score FROM students WHERE gender='M';
/*
+-----------+
| min_score |
+-----------+
|        55 |
+-----------+
*/

GROUP BY

# 使用group by分组
SELECT count(*) num FROM students GROUP BY class_id;
/*
+-----+
| num |
+-----+
|   4 |
|   3 |
|   3 |
+-----+
*/

# 同时显示班级的名称
SELECT class_id,count(*) FROM students GROUP BY class_id;
/*
+----------+----------+
| class_id | count(*) |
+----------+----------+
|        1 |        4 |
|        2 |        3 |
|        3 |        3 |
+----------+----------+
*/

# 多种分组条件
SELECT class_id,gender,count(*) num FROM students GROUP BY class_id,gender;
/*
+----------+--------+-----+
| class_id | gender | num |
+----------+--------+-----+
|        1 | M      |   2 |
|        1 | F      |   2 |
|        2 | F      |   1 |
|        2 | M      |   2 |
|        3 | F      |   2 |
|        3 | M      |   1 |
+----------+--------+-----+
*/

SELECT class_id,AVG(score) FROM students GROUP BY class_id;
SELECT class_id,gender,AVG(score) FROM students GROUP BY class_id,gender;

多表查询

# 直接查询，列数相加，行数相乘
SELECT * FROM students,classes;
/*
+----+----------+--------+-------+--------+----+--------+
| id | class_id | gender | score | name   | id | name   |
+----+----------+--------+-------+--------+----+--------+
|  1 |        1 | M      |    90 | 小明   |  1 | 一班   |
|  1 |        1 | M      |    90 | 小明   |  2 | 二班   |
|  1 |        1 | M      |    90 | 小明   |  3 | 三班   |
|  1 |        1 | M      |    90 | 小明   |  4 | 四班   |
|  2 |        1 | F      |    95 | 小红   |  1 | 一班   |
|  2 |        1 | F      |    95 | 小红   |  2 | 二班   |
|  2 |        1 | F      |    95 | 小红   |  3 | 三班   |
|  2 |        1 | F      |    95 | 小红   |  4 | 四班   |
|  3 |        1 | M      |    88 | 小军   |  1 | 一班   |
|  3 |        1 | M      |    88 | 小军   |  2 | 二班   |
|  3 |        1 | M      |    88 | 小军   |  3 | 三班   |
|  3 |        1 | M      |    88 | 小军   |  4 | 四班   |
|  4 |        1 | F      |    73 | 小米   |  1 | 一班   |
|  4 |        1 | F      |    73 | 小米   |  2 | 二班   |
|  4 |        1 | F      |    73 | 小米   |  3 | 三班   |
|  4 |        1 | F      |    73 | 小米   |  4 | 四班   |
|  5 |        2 | F      |    81 | 小白   |  1 | 一班   |
|  5 |        2 | F      |    81 | 小白   |  2 | 二班   |
|  5 |        2 | F      |    81 | 小白   |  3 | 三班   |
|  5 |        2 | F      |    81 | 小白   |  4 | 四班   |
|  6 |        2 | M      |    55 | 小兵   |  1 | 一班   |
|  6 |        2 | M      |    55 | 小兵   |  2 | 二班   |
|  6 |        2 | M      |    55 | 小兵   |  3 | 三班   |
|  6 |        2 | M      |    55 | 小兵   |  4 | 四班   |
|  7 |        2 | M      |    85 | 小林   |  1 | 一班   |
|  7 |        2 | M      |    85 | 小林   |  2 | 二班   |
|  7 |        2 | M      |    85 | 小林   |  3 | 三班   |
|  7 |        2 | M      |    85 | 小林   |  4 | 四班   |
|  8 |        3 | F      |    91 | 小新   |  1 | 一班   |
|  8 |        3 | F      |    91 | 小新   |  2 | 二班   |
|  8 |        3 | F      |    91 | 小新   |  3 | 三班   |
|  8 |        3 | F      |    91 | 小新   |  4 | 四班   |
|  9 |        3 | M      |    89 | 小王   |  1 | 一班   |
|  9 |        3 | M      |    89 | 小王   |  2 | 二班   |
|  9 |        3 | M      |    89 | 小王   |  3 | 三班   |
|  9 |        3 | M      |    89 | 小王   |  4 | 四班   |
| 10 |        3 | F      |    88 | 小丽   |  1 | 一班   |
| 10 |        3 | F      |    88 | 小丽   |  2 | 二班   |
| 10 |        3 | F      |    88 | 小丽   |  3 | 三班   |
| 10 |        3 | F      |    88 | 小丽   |  4 | 四班   |
+----+----------+--------+-------+--------+----+--------+
*/

# 解决重复列名的问题
SELECT
    s.id id,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s,classes c
WHERE s.gender='M' AND c.id=1;
/*
+----+--------+--------+-------+-----+--------+
| id | name   | gender | score | cid | cname  |
+----+--------+--------+-------+-----+--------+
|  1 | 小明   | M      |    90 |   1 | 一班   |
|  3 | 小军   | M      |    88 |   1 | 一班   |
|  6 | 小兵   | M      |    55 |   1 | 一班   |
|  7 | 小林   | M      |    85 |   1 | 一班   |
|  9 | 小王   | M      |    89 |   1 | 一班   |
+----+--------+--------+-------+-----+--------+
*/

UNION

SELECT * FROM students WHERE id=3
UNION
SELECT * FROM students WHERE gender='F';
# 等价与
# SELECT * FROM students WHERE id=8 OR gender='F';
/*
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  3 |        1 | 小军   | M      |    88 |
|  2 |        1 | 小红   | F      |    95 |
|  4 |        1 | 小米   | F      |    73 |
|  5 |        2 | 小白   | F      |    81 |
|  8 |        3 | 小新   | F      |    91 |
| 10 |        3 | 小丽   | F      |    88 |
+----+----------+--------+--------+-------+
*/

# 使用UNION的时候，列数应该一致，下面语句报错
SELECT * FROM students WHERE id=3
UNION
SELECT * FROM classes WHERE id=1;

JOIN

inner join内连接

# 选出所有学生，同时返回班级名称
SELECT
    s.id id,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s
INNER JOIN classes c
ON s.class_id=c.id;
/*
+----+--------+--------+-------+-----+--------+
| id | name   | gender | score | cid | cname  |
+----+--------+--------+-------+-----+--------+
|  1 | 小明   | M      |    90 |   1 | 一班   |
|  2 | 小红   | F      |    95 |   1 | 一班   |
|  3 | 小军   | M      |    88 |   1 | 一班   |
|  4 | 小米   | F      |    73 |   1 | 一班   |
|  5 | 小白   | F      |    81 |   2 | 二班   |
|  6 | 小兵   | M      |    55 |   2 | 二班   |
|  7 | 小林   | M      |    85 |   2 | 二班   |
|  8 | 小新   | F      |    91 |   3 | 三班   |
|  9 | 小王   | M      |    89 |   3 | 三班   |
| 10 | 小丽   | F      |    88 |   3 | 三班   |
+----+--------+--------+-------+-----+--------+
*/

外连接

# 首先增加一行数据
INSERT students(class_id,name,gender,score) values (5,"新生",'M',88);
# right join
SELECT
    s.id id,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s
RIGHT JOIN classes c
ON s.class_id=c.id;
/*
+------+--------+--------+-------+-----+--------+
| id   | name   | gender | score | cid | cname  |
+------+--------+--------+-------+-----+--------+
|    1 | 小明   | M      |    90 |   1 | 一班   |
|    2 | 小红   | F      |    95 |   1 | 一班   |
|    3 | 小军   | M      |    88 |   1 | 一班   |
|    4 | 小米   | F      |    73 |   1 | 一班   |
|    5 | 小白   | F      |    81 |   2 | 二班   |
|    6 | 小兵   | M      |    55 |   2 | 二班   |
|    7 | 小林   | M      |    85 |   2 | 二班   |
|    8 | 小新   | F      |    91 |   3 | 三班   |
|    9 | 小王   | M      |    89 |   3 | 三班   |
|   10 | 小丽   | F      |    88 |   3 | 三班   |
| NULL | NULL   | NULL   |  NULL |   4 | 四班   |
+------+--------+--------+-------+-----+--------+
*/

# left join
SELECT
    s.id id,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s
LEFT JOIN classes c
ON s.class_id=c.id;
/*
+----+--------+--------+-------+------+--------+
| id | name   | gender | score | cid  | cname  |
+----+--------+--------+-------+------+--------+
|  1 | 小明   | M      |    90 |    1 | 一班   |
|  2 | 小红   | F      |    95 |    1 | 一班   |
|  3 | 小军   | M      |    88 |    1 | 一班   |
|  4 | 小米   | F      |    73 |    1 | 一班   |
|  5 | 小白   | F      |    81 |    2 | 二班   |
|  6 | 小兵   | M      |    55 |    2 | 二班   |
|  7 | 小林   | M      |    85 |    2 | 二班   |
|  8 | 小新   | F      |    91 |    3 | 三班   |
|  9 | 小王   | M      |    89 |    3 | 三班   |
| 10 | 小丽   | F      |    88 |    3 | 三班   |
| 11 | 新生   | M      |    88 | NULL | NULL   |
+----+--------+--------+-------+------+--------+
*/

# full outer join
# MySQL不支持，替代方案
SELECT * 
FROM students s 
LEFT JOIN classes c
WHERE s.class_id=c.id
UNION
SELECT *
FROM classes c
RIGHT JOIN students s
WHERE s.class_id=c.id;

join小结

join小结