MySQL进阶查询

排序按关键字排序按单个字段排序按多个字段排序 分组对结果进行分组GROUP BY分组 限制条目别名设置别名用法（一）设置别名用法（二） 通配符子查询补充视图

排序

按关键字排序

使用 order by 语句来实现排序排序可针对一个或多个字段ASC：升序，默认排序方法DESC：降序ORDER BY 的语法结构

语法结构： select 字段1，字段2… from 表名 order by 字段1，字段2… ASC|DESC;

按单个字段排序

升序语法： select * from 表名 order by 列名 asc;

方式（一）mysql> select * from info order by score; 方式（二）mysql> select * from info order by score asc; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 4 | zhaoliu | 54.00 | 4 | | 10 | T1 | 58.00 | 9 | | 2 | lisi | 68.00 | 2 | | 3 | wangwu | 74.00 | 3 | | 1 | zhansan | 88.00 | 1 | | 5 | xiaoqi | 99.00 | 5 | +----+---------+-------+-------+

降序语法： select * from 表名 order by 列名 desc;

mysql> select * from info order by score desc; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 5 | xiaoqi | 99.00 | 5 | | 1 | zhansan | 88.00 | 1 | | 3 | wangwu | 74.00 | 3 | | 2 | lisi | 68.00 | 2 | | 10 | T1 | 58.00 | 9 | | 4 | zhaoliu | 54.00 | 4 | +----+---------+-------+-------+

按多个字段排序

多字段排序：优先参考先写的字段进行排序，第一个字段有相同的数据就视为一组，再用另一个字段进行排序，如果没有相同数值的时候，后面的字段将失效无意义。

第一个字段有相同的情况，第二个字段才会进行排序。

语法： select * from 表名 条件语句 order by 列名 desc,列名 desc;

mysql> select * from info where 1=1 order by hobby desc,id desc; #where 1=1可以删去 +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 4 | zhaoliu | 54.00 | 4 | | 3 | wangwu | 74.00 | 3 | | 10 | T1 | 58.00 | 2 | | 5 | xiaoqi | 99.00 | 2 | | 2 | lisi | 68.00 | 2 | | 1 | zhansan | 88.00 | 1 | +----+---------+-------+-------+

分组

对结果进行分组

使用GROUP BY语句来实现分组通常结合聚合函数一起使用可以按一个或多个字段对结果进行分组GROUP BY的语法结构

语法： select 字段,聚合函数(字段) from 表名 条件语句 group by 字段;

GROUP BY分组

语法： select count(字段) as 字段名,字段 from 表名 group by 字段;

mysql> select count(name) as hob,hobby from info group by hobby; +-----+-------+ | hob | hobby | +-----+-------+ | 1 | 1 | | 3 | 2 | | 1 | 3 | | 1 | 4 | +-----+-------+

限制条目

只返回SELECT查询结果的第一行或前几行使用LIMIT语句限制条目LIMIT语法结构

select 字段1，字段2，… from 表名 limit 数字1,数字2； 注解： 位置偏移量从0开始 （3，5）从第四行开始，后五行

mysql> select * from info; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 2 | lisi | 68.00 | 2 | | 3 | wangwu | 74.00 | 3 | | 4 | zhaoliu | 54.00 | 4 | | 5 | xiaoqi | 99.00 | 2 | | 10 | T1 | 58.00 | 2 | +----+---------+-------+-------+ mysql> select * from info limit 2,2; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 3 | wangwu | 74.00 | 3 | | 4 | zhaoliu | 54.00 | 4 | +----+---------+-------+-------+

别名

设置别名用法（一）

使用AS语句设置别名，关键字AS可省略设置别名时，保证不能与库中其他表或字段名称冲突别名的语法结构

字段设置别名： select 原字段名 as 新字段名 from 表名； #as 可省略

mysql> select name as 姓名,score as 成绩 from info; +---------+--------+ | 姓名 | 成绩 | +---------+--------+ | zhansan | 88.00 | | lisi | 68.00 | | wangwu | 74.00 | | zhaoliu | 54.00 | | xiaoqi | 99.00 | | T1 | 58.00 | +---------+--------+

表设置别名： select 字段名 as 新字段名 from 原表名 as 新表名； #适用于多表

mysql> select i.name as 姓名,i.score as 成绩 from info as i; +---------+--------+ | 姓名 | 成绩 | +---------+--------+ | zhansan | 88.00 | | lisi | 68.00 | | wangwu | 74.00 | | zhaoliu | 54.00 | | xiaoqi | 99.00 | | T1 | 58.00 | +---------+--------+

设置别名用法（二）

AS的用法AS作为连接语句 mysql> create table test as select * from info where score >=80; mysql> desc test; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(3) | NO | | 0 | | | name | varchar(10) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | | hobby | int(4) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> desc info; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(3) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | | hobby | int(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+

新表和原来表结构区别：字段的约束条件没被复制，但数值类型一样

通配符

用于替换字符串中的部分字符通常配合LIKE一起使用，并协调WHERE完成查询常用通配符 %表示零个、一个或多个_表示单个字符 mysql> select * from info ; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 2 | lisi | 68.00 | 2 | | 3 | wangwu | 74.00 | 3 | | 4 | zhaoliu | 54.00 | 4 | | 5 | xiaoqi | 99.00 | 2 | | 10 | T1 | 58.00 | 2 | +----+---------+-------+-------+

%表示零个、一个或多个

mysql> select * from info where name like 'z%'; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 4 | zhaoliu | 54.00 | 4 | +----+---------+-------+-------+

_表示单个字符

mysql> select * from info where name like 'l_s_'; +----+------+-------+-------+ | id | name | score | hobby | +----+------+-------+-------+ | 2 | lisi | 68.00 | 2 | +----+------+-------+-------+

_和%

mysql> select * from info where name like '__ao%'; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 4 | zhaoliu | 54.00 | 4 | | 5 | xiaoqi | 99.00 | 2 | +----+---------+-------+-------+

子查询

也称作为查询或者嵌套查询先于主查询被执行，其结果将作为外层主查询的条件在增删改查中都可以使用子查询支持多层嵌套IN语句是用来判断某个值是否在给定1的结果集中 mysql> select * from num; +------+ | id | +------+ | 1 | | 2 | | 4 | | 8 | +------+ mysql> select * from info; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 2 | lisi | 68.00 | 2 | | 3 | wangwu | 74.00 | 3 | | 4 | zhaoliu | 54.00 | 4 | | 5 | xiaoqi | 99.00 | 2 | | 10 | T1 | 58.00 | 2 | +----+---------+-------+-------+ mysql> select * from info where id in (select id from num); +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 2 | lisi | 68.00 | 2 | | 4 | zhaoliu | 54.00 | 4 | +----+---------+-------+-------+

补充视图

视图： 数据库中的虚拟表，这张虚拟表中不包含任何数据，只是做了数据映射；

mysql> create view v_score as select * from info where score >=80; #创建视图 mysql> select * from v_score; #查看视图信息 +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 88.00 | 1 | | 5 | xiaoqi | 99.00 | 2 | +----+---------+-------+-------+ mysql> update info set score=66 where name='zhansan'; #修改zhansan成绩 mysql> select * from info; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 66.00 | 1 | | 2 | lisi | 68.00 | 2 | | 3 | wangwu | 74.00 | 3 | | 4 | zhaoliu | 54.00 | 4 | | 5 | xiaoqi | 99.00 | 2 | | 10 | T1 | 58.00 | 2 | +----+---------+-------+-------+ mysql> select * from v_score; #zhansan不在视图表中了（不满足视图表的要求了） +----+--------+-------+-------+ | id | name | score | hobby | +----+--------+-------+-------+ | 5 | xiaoqi | 99.00 | 2 | +----+--------+-------+-------+ mysql> select * from info where id in (select id from num); #前后数据类型要一致 +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 1 | zhansan | 66.00 | 1 | | 2 | lisi | 68.00 | 2 | | 4 | zhaoliu | 54.00 | 4 | +----+---------+-------+-------+ mysql> select a.id from (select id,name from info) a; #a是设置的别名 +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 10 | +----+

创建的视图表只是一个虚拟表

mysql> select count(*) from info where exists (select * from info where name='zhansan'); +----------+ | count(*) | +----------+ | 6 | +----------+ mysql> select count(*) from info where exists (select * from info where name='222'); +----------+ | count(*) | +----------+ | 0 | +----------+