mysql进阶查询方法

这里写目录标题

mysql进阶查询方法拼接别名通配符聚合函数子查询联结组合查询

mysql进阶查询方法

本文使用到一个学生表，一个成绩表如下：

mysql> select * from students; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+ mysql> select * from scores; +-------+-------+-------+ | s_num | c_num | score | +-------+-------+-------+ | 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 | +-------+-------+-------+

拼接

拼接是将需要查询的一些字段和字符串连接起来输出 ，语法为 concat(str1, str2 , str3,…) , 如下栗 ，

mysql> select concat ( s_name, '学号是',s_num,',班级是', s_class, '班的。' ) from students; +----------------------------------------------------------------+ | concat ( s_name, '学号是',s_num,',班级是', s_class, '班的。' ) | +----------------------------------------------------------------+ | 曾华学号是101,班级是95033班的。 | | 匡明学号是102,班级是95031班的。 | | 王丽学号是103,班级是95033班的。 | | 李军学号是104,班级是95033班的。 | | 王芳学号是105,班级是95031班的。 | | 陆军学号是106,班级是95031班的。 | | 王尼玛学号是107,班级是95033班的。 | | 张全蛋学号是108,班级是95031班的。 | | 赵铁柱学号是109,班级是95031班的。 | | NULL | +----------------------------------------------------------------+

也可以用concat_ws(connector, str1, str2 , str3,…) , 将每个字符串或列之间插入一个连接符。

mysql> select concat_ws(':' ,'姓名',s_name,' 班级', s_class, ' 出生日期',s_birthday) from students; +------------------------------------------------------------------------------+ | concat_ws(':' ,'姓名',s_name,' 班级', s_class, ' 出生日期',s_birthday) | +------------------------------------------------------------------------------+ | 姓名:曾华: 班级:95033: 出生日期:1977-09-01 00:00:00 | | 姓名:匡明: 班级:95031: 出生日期:1975-10-02 00:00:00 | | 姓名:王丽: 班级:95033: 出生日期:1976-01-23 00:00:00 | | 姓名:李军: 班级:95033: 出生日期:1976-02-20 00:00:00 | | 姓名:王芳: 班级:95031: 出生日期:1975-02-10 00:00:00 | | 姓名:陆军: 班级:95031: 出生日期:1974-06-03 00:00:00 | | 姓名:王尼玛: 班级:95033: 出生日期:1976-02-20 00:00:00 | | 姓名:张全蛋: 班级:95031: 出生日期:1975-02-10 00:00:00 | | 姓名:赵铁柱: 班级:95031: 出生日期:1974-06-03 00:00:00 | | 姓名:刘表: 班级: 出生日期 | +------------------------------------------------------------------------------+

别名

从前面的输出可以看到， select 语句可以很好的拼接字段和字符串。但是新计算的结果是什么呢，实际上它没有名字， 只是一列值， 如果只查看结果可以， 但是不能引用它。 为了解决这个问题， mysql支持别名（alias) 。

别名是一个字段或者值的替换名， 用as 关键词赋予 。

mysql> select concat('姓名:',s_name, ' 学号:', s_num,' 班级:', s_class, ' 出生日期:',s_birthday) as '学生基本信息' from students; +-----------------------------------------------------------------------+ | 学生基本信息 | +-----------------------------------------------------------------------+ | 姓名:曾华 学号:101 班级:95033 出生日期:1977-09-01 00:00:00 | | 姓名:匡明 学号:102 班级:95031 出生日期:1975-10-02 00:00:00 | | 姓名:王丽 学号:103 班级:95033 出生日期:1976-01-23 00:00:00 | | 姓名:李军 学号:104 班级:95033 出生日期:1976-02-20 00:00:00 | | 姓名:王芳 学号:105 班级:95031 出生日期:1975-02-10 00:00:00 | | 姓名:陆军 学号:106 班级:95031 出生日期:1974-06-03 00:00:00 | | 姓名:王尼玛 学号:107 班级:95033 出生日期:1976-02-20 00:00:00 | | 姓名:张全蛋 学号:108 班级:95031 出生日期:1975-02-10 00:00:00 | | 姓名:赵铁柱 学号:109 班级:95031 出生日期:1974-06-03 00:00:00 | | NULL | +-----------------------------------------------------------------------+

可以使用order by，group by 和having 子句中的字段别名来引用它。

对别名进行引用

mysql> select s_num as s from students having s>105 order by s desc ; +-----+ | s | +-----+ | 110 | | 109 | | 108 | | 107 | | 106 | +-----+ 5 rows in set (0.00 sec)

通配符

通配符是用来匹配值的一部分的特殊字符。只能用于搜索字符串 , mysql中非字符串数据类型已经不能使用通配符搜索。 （就是正则表达式）

通配符描述%替代 0 个或多个任意字符_替代一个任意字符[charlist]字符列中的任何单一字符[!charlist]不在字符列中的任何单一字符

找出姓王的学生

mysql> select s_name from students where s_name like '王%'; +--------+ | s_name | +--------+ | 王丽 | | 王芳 | | 王尼玛 | +--------+

找出名字为两个字的姓王的学生

mysql> select s_name from students where s_name like '王_'; +--------+ | s_name | +--------+ | 王丽 | | 王芳 | +--------+

注意 ：%看起来能匹配任何字符， 但不会匹配NULL , 因为NULL 是什么都没有。 MySQL扩展的正规表达式匹配 , 需要用rlike

名字中有王或张的学生

mysql> select * from students where s_name rlike '[王张]'; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+ mysql> select * from students where s_name rlike '[!王张]'; # 这个加否定为什么结果一样， 我没有弄懂 ，知道的麻烦评论告诉我 +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

聚合函数

SQL 聚合函数计算从列中取得的值，返回一个单一的值。常用的聚合函数如下：

AVG() - 返回平均值COUNT() - 返回行数MAX() - 返回最大值MIN() - 返回最小值FIRST() - 返回第一个记录的值LAST() - 返回最后一个记录的值

比如返回计算一共有多少学生

mysql> select count(s_num) from students; +--------------+ | count(s_num) | +--------------+ | 10 | +--------------+

子查询

mysql 允许创建子查询 ，即嵌套在其他查询方法中的查询 。在查询信息涉及到多个关系表时子查询思路清晰，方便。 现在查询 学生成绩大于90分的学生姓名

mysql> select s_name from students -> where s_num in -> ( select s_num from scores where score>90); +--------+ | s_name | +--------+ | 王丽 | +--------+

联结

sql最强大的功能之一就是能在数据查询中执行联结表 。简单的说， 连接是一种机制，在select 运行时关联正确的行， 可以联结多个表返回一组数据。比如现在要查询 成绩表 和学生表 中共有的所有学生的姓名及其成绩。 而这两个表是通过s_num 联系的。

mysql> select s_name ,score from students, scores -> where students.s_num = scores.s_num; +--------+-------+ | s_name | score | +--------+-------+ | 王丽 | 92 | | 王丽 | 86 | | 王丽 | 85 | | 王芳 | 88 | | 王芳 | 75 | | 王芳 | 79 | | 赵铁柱 | 76 | | 赵铁柱 | 68 | | 赵铁柱 | 81 | +--------+-------+

组合查询

组合查询就是将多个select 语句的结果组合起来输出 。

常用于对从不同的表进行同样的查询的结果组合 ，或者对一个表执行多个查询的结果组合 。

例如查询 学生表 中班级为 ‘95031’ 的学生学号 和 成绩表中 成绩大于85分的学生学号 。

mysql> select s_num from students where s_class = '95031' -> UNION -> select s_num from students where s_class = '95031' ; +-------+ | s_num | +-------+ | 102 | | 105 | | 106 | | 108 | | 109 | +-------+

或者查询 同一个表中 班级为’95031’ 和班级为’95033’ 的学生

mysql> select s_name , s_class from students where s_class = '95031' -> UNION -> select s_name , s_class from students where s_class = '95033'; +--------+---------+ | s_name | s_class | +--------+---------+ | 匡明 | 95031 | | 王芳 | 95031 | | 陆军 | 95031 | | 张全蛋 | 95031 | | 赵铁柱 | 95031 | | 曾华 | 95033 | | 王丽 | 95033 | | 李军 | 95033 | | 王尼玛 | 95033 | +--------+---------+

UNION 对查询的数目没有限制（必须两个以上） UNION 默认取消重复的行，可以用UNION ALL 来显示全部 UNION每个查询必须包含相同的列，表达式或者 聚集函数 列的数据必须兼容 ，也就是说不必完全相同， 但必须是DBMS可以隐含转化的类型 （如不同类型的数值型数据，一个是int ,一个是tinyint）