数据库MySQL-查询语句
- 2020 年 3 月 27 日
- 筆記
1.5 查询语句
语法：select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序][having 条件] [limit 限制]
1.5.1 字段表达式
-- 可以直接输出内容 mysql> select '锄禾日当午'; +------------+ | 锄禾日当午 | +------------+ | 锄禾日当午 | +------------+ 1 row in set (0.00 sec) -- 输出表达式 mysql> select 10*10; +-------+ | 10*10 | +-------+ | 100 | +-------+ 1 row in set (0.00 sec) mysql> select ch,math,ch+math from stu; +------+------+---------+ | ch | math | ch+math | +------+------+---------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL | -- 表达式部分可以用函数 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.6669325378415478 | +--------------------+ 1 row in set (0.00 sec)
通过as给字段取别名
mysql> select '锄禾日当午' as '标题'; -- 取别名 +------------+ | 标题 | +------------+ | 锄禾日当午 | +------------+ 1 row in set (0.00 sec) mysql> select ch,math,ch+math as '总分' from stu; +------+------+------+ | ch | math | 总分 | +------+------+------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL | -- 多学一招：as可以省略 mysql> select ch,math,ch+math '总分' from stu; +------+------+------+ | ch | math | 总分 | +------+------+------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL |
1.5.2 from子句
from：来自，from后面跟的是数据源。数据源可以有多个。返回笛卡尔积。
插入测试表
create table t1( str char(2) ); insert into t1 values ('aa'),('bb'); create table t2( num int ); insert into t2 values (10),(20);
测试
-- from子句 mysql> select * from t1; +------+ | str | +------+ | aa | | bb | +------+ 2 rows in set (0.00 sec) -- 多个数据源,返回笛卡尔积 mysql> select * from t1,t2; +------+------+ | str | num | +------+------+ | aa | 10 | | bb | 10 | | aa | 20 | | bb | 20 | +------+------+ 4 rows in set (0.00 sec)
1.5.3 dual表
dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。
mysql> select 10*10 as 结果 from dual; +------+ | 结果 | +------+ | 100 | +------+ 1 row in set (0.00 sec)
1.5.4 where子句
where后面跟的是条件，在数据源中进行筛选。返回条件为真记录
MySQL支持的运算符
-- 比较运算符 > 大于 < 小于 >= 大于等于 <= 小于等于 = 等于 != 不等于 -- 逻辑运算符 and 与 or 或 not 非 -- 其他 in | not in 字段的值在枚举范围内 between…and|not between…and 字段的值在数字范围内 is null | is not null 字段的值不为空
例题：
-- 查找语文成绩及格的学生 mysql> select * from stu where ch>=60; -- 查询语文和数学都及格的学生 mysql> select * from stu where ch>=60 and math>=60; -- 查询语文或数学不及格的学生 mysql> select * from stu where ch<60 or math<60;
思考：如下语句输出什么？
mysql> select * from stu where 1; -- 输出所有数据 mysql> select * from stu where 0; -- 不输出数据
思考：如何查找北京和上海的学生
-- 通过or实现 mysql> select * from stu where stuaddress='北京' or stuaddress='上海'; -- 通过in语句实现 mysql> select * from stu where stuaddress in ('北京','上海'); -- 查询不是北京和上海的学生 mysql> select * from stu where stuaddress not in ('北京','上海');
思考：查找年龄在20~25之间
-- 方法一： mysql> select * from stu where stuage>=20 and stuage<=25; -- 方法二： mysql> select * from stu where not(stuage<20 or stuage>25); -- 方法三：between...and... mysql> select * from stu where stuage between 20 and 25; -- 年龄不在20~25之间 mysql> select * from stu where stuage not between 20 and 25;
思考：
-- 查找缺考的学生 mysql> select * from stu where ch is null or math is null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | +--------+----------+--------+--------+---------+------------+------+------+ -- 查找没有缺考的学生 mysql> select * from stu where ch is not null and math is not null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 7 rows in set (0.00 sec) -- 查找需要补考的学生 mysql> select * from stu where ch<60 or math<60 or ch is null or math is null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec)
练习：
-- 1、查找学号是s25301,s25302,s25303的学生 mysql> select * from stu where stuno in ('s25301','s25302','s25303'); +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec) -- 2、查找年龄是18~20的学生 mysql> select * from stu where stuage between 18 and 20; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | +--------+---------+--------+--------+---------+------------+------+------+ 1 row in set (0.00 sec)
1.5.5 group by 【分组查询】
将查询的结果分组，分组查询目的在于统计数据。
-- 查询男生和女生的各自语文平均分 mysql> select stusex,avg(ch) '平均分' from stu group by stusex; +--------+---------+ | stusex | 平均分 | +--------+---------+ | 女 | 72.2500 | | 男 | 77.0000 | +--------+---------+ 2 rows in set (0.00 sec) -- 查询男生和女生各自多少人 mysql> select stusex,count(*) 人数 from stu group by stusex; +--------+------+ | stusex | 人数 | +--------+------+ | 女 | 4 | | 男 | 5 | +--------+------+ 2 rows in set (0.00 sec) -- 查询每个地区多少人 mysql> select stuaddress,count(*) from stu group by stuaddress; +------------+----------+ | stuaddress | count(*) | +------------+----------+ | 上海 | 1 | | 北京 | 3 | | 天津 | 2 | | 河北 | 2 | | 河南 | 1 | +------------+----------+ 5 rows in set (0.00 sec) -- 每个地区的数学平均分 mysql> select stuaddress,avg(math) from stu group by stuaddress; +------------+-----------+ | stuaddress | avg(math) | +------------+-----------+ | 上海 | 76.0000 | | 北京 | 74.5000 | | 天津 | 83.0000 | | 河北 | 72.0000 | | 河南 | 56.0000 | +------------+-----------+ 5 rows in set (0.00 sec)
查询字段是普通字段，只取第一个值
通过group_concat()函数将同一组的值连接起来显示
mysql> select group_concat(stuname),stusex,avg(math) from stu group by stusex; +-------------------------------------+--------+-----------+ | group_concat(stuname) | stusex | avg(math) | +-------------------------------------+--------+-----------+ | 李斯文,诸葛丽丽,梅超风,Tabm | 女 | 70.5000 | | 张秋丽,李文才,欧阳俊雄,争青小子,Tom | 男 | 77.2500 | +-------------------------------------+--------+-----------+ 2 rows in set (0.00 sec)
多列分组
mysql> select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex; +------------+--------+-----------+ | stuaddress | stusex | avg(math) | +------------+--------+-----------+ | 上海 | 男 | 76.0000 | | 北京 | 女 | 82.0000 | | 北京 | 男 | 67.0000 | | 天津 | 男 | 83.0000 | | 河北 | 女 | 72.0000 | | 河南 | 女 | 56.0000 | +------------+--------+-----------+ 6 rows in set (0.00 sec)
小结：
1、如果是分组查询，查询字段是分组字段和聚合函数。 2、查询字段是普通字段，只取第一个值 3、group_concat()将同一组的数据连接起来
1.5.6 order by排序
asc：升序【默认】
desc：降序
-- 按年龄的升序排列 mysql> select * from stu order by stuage asc; mysql> select * from stu order by stuage; -- 默认是升序 -- 按总分降序 mysql> select *,ch+math '总分' from stu order by ch+math desc;
多列排序
-- 年龄升序，如果年龄一样，按ch降序排列 mysql> select * from stu order by stuage asc,ch desc;
思考如下代码表示什么含义
select * from stu order by stuage desc,ch desc; #年龄降序，语文降序 select * from stu order by stuage desc,ch asc; #年龄降序，语文升序 select * from stu order by stuage,ch desc; #年龄升序、语文降序 select * from stu order by stuage,ch; #年龄升序、语文升序
1.5.7 having条件
having：是在结果集上进行条件筛选
例题
-- 查询女生 mysql> select * from stu where stusex='女'; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec) -- 查询女生 mysql> select * from stu having stusex='女'; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec) -- 查询女生姓名 mysql> select stuname from stu where stusex='女'; +----------+ | stuname | +----------+ | 李斯文 | | 诸葛丽丽 | | 梅超风 | | Tabm | +----------+ 4 rows in set (0.00 sec) -- 使用having报错，因为结果集中没有stusex字段 mysql> select stuname from stu having stusex='女'; ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'
小结：having和where的区别：
where是对原始数据进行筛选，having是对记录集进行筛选。
1.5.8 limit
语法：limit [起始位置]，显示长度
-- 从第0个位置开始取，取3条记录 mysql> select * from stu limit 0,3; -- 从第2个位置开始取，取3条记录 mysql> select * from stu limit 2,3; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 | +--------+----------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec)
起始位置可以省略，默认是从0开始
mysql> select * from stu limit 3; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec)
例题：找出班级总分前三名
mysql> select *,ch+math total from stu order by (ch+math) desc limit 0,3; +--------+----------+--------+--------+---------+------------+------+------+-------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | total | +--------+----------+--------+--------+---------+------------+------+------+-------+ | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | 178 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | 165 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | 153 | +--------+----------+--------+--------+---------+------------+------+------+-------+ 3 rows in set (0.00 sec)
多学一招：limit在update和delete语句中也是可以使用的。
-- 前3名语文成绩加1分 mysql> update stu set ch=ch+1 order by ch+math desc limit 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 -- 前3名删除 mysql> delete from stu order by ch+math desc limit 3; Query OK, 3 rows affected (0.00 sec)
1.5.9 查询语句中的选项
查询语句中的选项有两个：
1、 all：显示所有数据 【默认】
2、 distinct：去除结果集中重复的数据
mysql> select all stuaddress from stu; +------------+ | stuaddress | +------------+ | 北京 | | 北京 | | 天津 | | 河南 | | 河北 | | 北京 | +------------+ 6 rows in set (0.00 sec) -- 去除重复的项 mysql> select distinct stuaddress from stu; +------------+ | stuaddress | +------------+ | 北京 | | 天津 | | 河南 | | 河北 | +------------+ 4 rows in set (0.00 sec)
