MySQL查询相关

男娘i 2022-06-18 10:24 189阅读 0赞

##### 基本查询语句 #####

SELECT 属性列表
           FROM 表名
           [WHERE XXXX]
           [GROUP BY 属性名1, 属性名2 [, 属性名i ...] [HAVING 条件表达式]]
           [ORDER BY 属性名3 [ASC | DESC]]
           [LIMIT 初始位置, 记录数]

##### 先准备点数据, 要不然下面的戏没法唱了. #####

MariaDB [carltest]> select * from score;
    +----+--------+-----------+-------+
    | id | stu_id | c_name    | grade |
    +----+--------+-----------+-------+
    |  1 |    901 | 计算机    |    98 |
    |  2 |    901 | 英语      |    80 |
    |  3 |    902 | 计算机    |    65 |
    |  4 |    902 | 中文      |    88 |
    |  5 |    903 | 中文      |    95 |
    |  6 |    904 | 计算机    |    70 |
    |  7 |    904 | 英语      |    92 |
    |  8 |    905 | 英语      |    94 |
    |  9 |    906 | 计算机    |    90 |
    | 10 |    906 | 英语      |    85 |
    +----+--------+-----------+-------+
    10 rows in set (0.00 sec)
    
    MariaDB [carltest]> select * from student;
    +-----+-----------+------+-------+--------------+--------------------+
    | id  | name      | sex  | birth | department   | address            |
    +-----+-----------+------+-------+--------------+--------------------+
    | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
    | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
    | 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |
    | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
    | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
    | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
    +-----+-----------+------+-------+--------------+--------------------+
    6 rows in set (0.00 sec)

##### LIMIT限定 #####

MariaDB [carltest]> SELECT * FROM student LIMIT 1, 3;
    +-----+-----------+------+-------+------------+--------------------+
    | id  | name      | sex  | birth | department | address            |
    +-----+-----------+------+-------+------------+--------------------+
    | 902 | 张老二    | 男   |  1986 | 中文系     | 北京市昌平区       |
    | 903 | 张三      | 女   |  1990 | 中文系     | 湖南省永州市       |
    | 904 | 李四      | 男   |  1990 | 英语系     | 辽宁省阜新市       |
    +-----+-----------+------+-------+------------+--------------------+
    3 rows in set (0.00 sec)

##### IN关键字 #####

MariaDB [carltest]> SELECT * FROM student WHERE department IN ('计算机系', '英语系');
    +-----+-----------+------+-------+--------------+--------------------+
    | id  | name      | sex  | birth | department   | address            |
    +-----+-----------+------+-------+--------------+--------------------+
    | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
    | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
    | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
    | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
    +-----+-----------+------+-------+--------------+--------------------+
    4 rows in set (0.00 sec)

##### GROUP BY关键字 #####

GROUP BY关键字通常与集合函数一起使用. 集合函数包括COUNT(), SUM(), AVG(), MAX()和MIN()等.

如果GROUP BY不与上述函数一起使用, 那么查询结果就是字段取值的分组情况. 字段中取值相同的记录为一组,  
但只显示该组的**第一条**记录

MariaDB [carltest]> SELECT department, COUNT(id) as sum_of_department FROM student GROUP BY department;
    +--------------+-------------------+
    | department   | sum_of_department |
    +--------------+-------------------+
    | 中文系       |                 2 |
    | 英语系       |                 2 |
    | 计算机系     |                 2 |
    +--------------+-------------------+
    3 rows in set (0.00 sec)
    
    # 首先按照c_name字段对score表中的记录进行分组. 然后使用MAX()函数计算每组的最大值
    MariaDB [carltest]> SELECT c_name, MAX(grade) FROM score GROUP BY c_name;
    +-----------+------------+
    | c_name    | MAX(grade) |
    +-----------+------------+
    | 中文      |         95 |
    | 英语      |         94 |
    | 计算机    |         98 |
    +-----------+------------+
    3 rows in set (0.01 sec)

GROUP BY 后面多个属性的时候, 先按照属性1分组, 如果属性1相同, 再按照属性2分组

##### 嵌套查询 #####

MariaDB [carltest]> SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');
    +-----------+-------+
    | c_name    | grade |
    +-----------+-------+
    | 计算机    |    70 |
    | 英语      |    92 |
    +-----------+-------+
    2 rows in set (0.03 sec)

##### UNION, UNION ALL用法 #####

使用UNION关键字时, 数据库系统会将所有的查询结果合并到一起, **然后去掉相同的记录**. 而UNION ALL关键字则只是简单的合并到一起.

MariaDB [carltest]> SELECT id FROM student UNION SELECT stu_id FROM score;
    +-----+
    | id  |
    +-----+
    | 901 |
    | 902 |
    | 903 |
    | 904 |
    | 905 |
    | 906 |
    +-----+
    6 rows in set (0.00 sec)
    
    MariaDB [carltest]> SELECT id FROM student UNION ALL SELECT stu_id FROM score;
    +-----+
    | id  |
    +-----+
    | 901 |
    | 902 |
    | 903 |
    | 904 |
    | 905 |
    | 906 |
    | 901 |
    | 901 |
    | 902 |
    | 902 |
    | 903 |
    | 904 |
    | 904 |
    | 905 |
    | 906 |
    | 906 |
    +-----+
    16 rows in set (0.00 sec)

##### ANY关键字 #####

MariaDB [carltest]> SELECT * FROM student WHERE id=ANY (SELECT stu_id FROM score WHERE stu_id in (SELECT stu_id FROM score WHERE c_name='计算机') AND c_name='英语');
    +-----+-----------+------+-------+--------------+--------------------+
    | id  | name      | sex  | birth | department   | address            |
    +-----+-----------+------+-------+--------------+--------------------+
    | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
    | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
    | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
    +-----+-----------+------+-------+--------------+--------------------+
    3 rows in set (0.04 sec)

##### 正则表达式匹配查询 #####

SELECT * FROM table_name where xx REGEXP '匹配方式';
    
    模式字符      含义
    
    ^             匹配字符串开始的部分
    $             匹配字符串结束的部分
    .             代表字符串中的任意一个字符, 包括回车和换行
    [字符集合]    匹配"字符集合"中的任何一个字符
    [^字符集合]   匹配除了"字符集合"以外的任何一个字符
    S1|S2|S3      匹配S1, S2和S3中的任意一个字符串
    *             代表多个该符号之前的字符, 包括0和1个
    +             代表多个该符号之前的字符, 包括1个
    字符串{N}     字符串出现N次
    字符串{M,N}   字符串出现至少M次, 最多N次
    
    MariaDB [carltest]> SELECT student.id name, sex, birth, department address, c_name, grade FROM student, score WHERE (name LIKE '张%' OR name LIKE '王%') AND student.id=score.stu_id;
    +------+------+-------+--------------+-----------+-------+
    | name | sex  | birth | address      | c_name    | grade |
    +------+------+-------+--------------+-----------+-------+
    |  901 | 男   |  1985 | 计算机系     | 计算机    |    98 |
    |  901 | 男   |  1985 | 计算机系     | 英语      |    80 |
    |  902 | 男   |  1986 | 中文系       | 计算机    |    65 |
    |  902 | 男   |  1986 | 中文系       | 中文      |    88 |
    |  903 | 女   |  1990 | 中文系       | 中文      |    95 |
    |  905 | 女   |  1991 | 英语系       | 英语      |    94 |
    |  906 | 男   |  1988 | 计算机系     | 计算机    |    90 |
    |  906 | 男   |  1988 | 计算机系     | 英语      |    85 |
    +------+------+-------+--------------+-----------+-------+
    8 rows in set (0.00 sec)

##### 连接查询 #####

# 先从student表中删除id为902和903两行, 方便下面区分
    MariaDB [carltest]> select * from student;
    +-----+-----------+------+-------+--------------+--------------------+
    | id  | name      | sex  | birth | department   | address            |
    +-----+-----------+------+-------+--------------+--------------------+
    | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
    | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
    | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
    | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
    +-----+-----------+------+-------+--------------+--------------------+
    4 rows in set (0.00 sec)
    
    MariaDB [carltest]> select * from score;
    +----+--------+-----------+-------+
    | id | stu_id | c_name    | grade |
    +----+--------+-----------+-------+
    |  1 |    901 | 计算机    |    98 |
    |  2 |    901 | 英语      |    80 |
    |  3 |    902 | 计算机    |    65 |
    |  4 |    902 | 中文      |    88 |
    |  5 |    903 | 中文      |    95 |
    |  6 |    904 | 计算机    |    70 |
    |  7 |    904 | 英语      |    92 |
    |  8 |    905 | 英语      |    94 |
    |  9 |    906 | 计算机    |    90 |
    | 10 |    906 | 英语      |    85 |
    +----+--------+-----------+-------+
    10 rows in set (0.00 sec)
    
    # 内连接查询
    MariaDB [carltest]> SELECT student.id name, sex, birth, department, address, c_name grade FROM student, score WHERE student.id=score.stu_id;
    +------+------+-------+--------------+--------------------+-----------+
    | name | sex  | birth | department   | address            | grade     |
    +------+------+-------+--------------+--------------------+-----------+
    |  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 计算机    |
    |  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 英语      |
    |  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机    |
    |  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 英语      |
    |  905 | 女   |  1991 | 英语系       | 福建省厦门市       | 英语      |
    |  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机    |
    |  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 英语      |
    +------+------+-------+--------------+--------------------+-----------+
    7 rows in set (0.00 sec)
    
    # 给student和score起个别名
    MariaDB [carltest]> SELECT s1.id name, sex, birth, department, address, c_name grade FROM student s1, score s2 WHERE s1.id=s2.stu_id;
    +------+------+-------+--------------+--------------------+-----------+
    | name | sex  | birth | department   | address            | grade     |
    +------+------+-------+--------------+--------------------+-----------+
    |  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 计算机    |
    |  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 英语      |
    |  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机    |
    |  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 英语      |
    |  905 | 女   |  1991 | 英语系       | 福建省厦门市       | 英语      |
    |  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机    |
    |  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 英语      |
    +------+------+-------+--------------+--------------------+-----------+
    7 rows in set (0.00 sec)
    
    # 外连接查询
    
    # LEFT JOIN, 返回student所有的数据
    MariaDB [carltest]> SELECT student.id, c_name FROM student LEFT JOIN  score on student.id=score.stu_id;
    +-----+-----------+
    | id  | c_name    |
    +-----+-----------+
    | 901 | 计算机    |
    | 901 | 英语      |
    | 904 | 计算机    |
    | 904 | 英语      |
    | 905 | 英语      |
    | 906 | 计算机    |
    | 906 | 英语      |
    +-----+-----------+
    7 rows in set (0.00 sec)
    
    # RIGHT JOIN, 返回score所有的数据
    MariaDB [carltest]> SELECT student.id, c_name FROM student RIGHT JOIN  score on student.id=score.stu_id;
    +------+-----------+
    | id   | c_name    |
    +------+-----------+
    |  901 | 计算机    |
    |  901 | 英语      |
    | NULL | 计算机    |
    | NULL | 中文      |
    | NULL | 中文      |
    |  904 | 计算机    |
    |  904 | 英语      |
    |  905 | 英语      |
    |  906 | 计算机    |
    |  906 | 英语      |
    +------+-----------+
    10 rows in set (0.00 sec)