SQL常用查询语句

SQL常用语句、关键字，子查询，多表查询，连接查询等。

常用语句、关键字

1. select … from … where ；

2. AND / OR

3. IN / NOT IN

4. YEAR()函数

获取日期格式数据的年份

SELECT birthday FROM student WHERE no IN (101, 108);
+------------+
| birthday   |
+------------+
| 1977-09-01 |
| 1975-02-10 |
+------------+
SELECT YEAR(birthday) FROM student WHERE no IN (101, 108);
+----------------+
| YEAR(birthday) |
+----------------+
|           1977 |
|           1975 |
+----------------+

5. UNION 合并两个表

将两次查询的结果合并在一个表中显示。

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

-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);

6. ANY / ALL

ANY 表示集合中任意一个，ALL表示集合中所有的。

degree > ANY (76, 46, 95) 表示degree > 46 即成立。

7. ORDER BY … DESC 降序排序

在查询语句后加上order by 某字段，会将查询的结果按这个字段的大小排序。

DESC 降序；ASC 升序；

可以同时按多个列排序：

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

8. AVG() 计算某列的平均值，常与GROUP BY 搭配使用

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

group by

直接用group by，会取出第一条数据，

如 SELECt * from salaries group by emp_no; ， 当同一个emp_no有多条数据时，会得到第一条数据。

然后可以加条件在分组中选某条数据，比如同一个emp_no的数据（同一组）中选工资最低的。

比如下面这个经典的例子：

SELECT
    emp_no,
    salary
FROM
    salaries
GROUP BY
    emp_no
HAVING
    min( from_date )
ORDER BY
    emp_no DESC

9. COUNT()

10. HAVING

HAVING: 表示持有，与where用法类似。

SELECT * FROM student where  sex='男';
SELECT * FROM student having sex='男';

HAVING COUNT(c_no) >= 2。

11. SELECT … FROM … GROUP BY … HAVING COUNT(..)>=n;

12. NOT 取反

13. LIKE 模糊查询

查询 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 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

14. MAX、MIN

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

15. LIMIT、OFFSET

用来在排好序的结果中选取前几个，或第几个。

select * from employees order by hire_date desc limit 0,1;

上面语句是排好序后，从第0个开始，选取1个。

以下的两种方式均表示取2,3,4三条条数据。

1.select* from test LIMIT 1,3;

当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。

2. select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)

当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

16. ON、WHERE

on是按再条件连接，where是先连接再按条件筛选。

详细介绍：https://www.cnblogs.com/guanshan/articles/guan062.html

一些概念

1. 别名

1. as

   -- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
   SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
   +-----------+------+
   | name      | age  |
   +-----------+------+
   | 曾华      |   42 |
   | 匡明      |   44 |
   | 王丽      |   43 |
   | 李军      |   43 |
   | 王芳      |   44 |
   | 陆军      |   45 |
   | 王尼玛    |   43 |
   | 张全蛋    |   44 |
   | 赵铁柱    |   45 |
   | 张飞      |   45 |
   +-----------+------+

2. 同一张表联合查询时

   SELECT * FROM score a WHERE degree < (
     SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no);

   ​

子查询

子查询是比较复杂的查询，通常有多个条件，需要先查询一次，然后在第一次的查询结果中再次查询。

1. 查询 “男” 教师及其所上的课程。

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

2. 查询最高分同学的 score 表。

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

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

3. 查询和 “李军” 同性别且同班的同学 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 |
+-----------+-----+-------+
| 曾华      | 男  | 95033 |
| 李军      | 男  | 95033 |
| 王尼玛    | 男  | 95033 |
+-----------+-----+-------+

多表查询

from两个表时，会先将这两个表全连接。

连接查询

连接查询是将两个表连接起来，join后面需要跟on，表示连接的条件，不能直接跟where。

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

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 | 邮政卡    |
+------+--------+--------+------+-----------+

复杂查询例子

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

-- 将表 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 |
+------+-------+--------+

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

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

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

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 |
+-------+-------------+----------+

4. 查询 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 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

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

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

需要的 “计算机导论” 和性别为 “男” 的编号可以在 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 |
+------+-------+--------+