mysql学习2

_模糊查询

select name from user where name like "xxh_"; -- _表示匹配一个字符，%是匹配多个字符

between等价于… <= … <= …
in用法相当于or

select * from user where height in (180,178);

in子查询，若不使用子查询可以给user表添加level字段，若无level字段，level=1的class可能增加，就必须使用子查询

-- 取出一年级的学生
select * from user where class in (select id from class_table where level=1);

交集运算

select * from user1 union select * from user2; -- union自动去除重复记录
-- 两表的字段类型需相同
-- order by子句只能使用一次
select * from user1 union select * from user2 order by add_time;
-- all关键字保留重复行
select * from user1 union all select * from user2;

内连接，on条件对左右两侧表都生效

select u.name,c.name from user as u inner join class as c on u.class=c.id;
-- 和where结合
select u.name,c.name from user as u inner join class as c on u.class=c.id where u.name like "xxh%";

外连接的on条件只对一侧的表有效,主表有多少条记录就返回多少条

select u.name,c.name from user as u right join class as c on u.class=c.id;

三张表的连接

select u.name, c.name, t.name from user as u left join class as c on u.class=c.id left join teacher as t on c.teacher=t.id;

交叉连接(笛卡尔积), 生成user表的行数 * class表的行数

select * from user cross join class;

处理null值为不确定

select u.name, c.name, coalesce(t.name, '不确定') from user as u left join class as c on u.class=c.id left join teacher as t on c.teacher=t.id;

窗口函数，mysql8支持，只能写在select子句中

-- 按班级计算学生成绩排名
select name, class, score, rank() over(partition by class order by score desc) as ranking FROM `user`;
-- 按全校成绩排名,取出排名前三	
select id,`name`,class,score,rank() over(order by score desc) as ranking FROM `user` limit 3;
-- dense_rank, row_number也计算排名，若有两个第一名，返回1、1、2，row_number返回1、2、3，rank返回1、1、3
-- 也可以把聚合函数作为窗口函数使用
-- 取出每个班级排名前3
select * from (select id,`name`,class,score,rank() over(partition by class order by score desc) as ranking FROM `user`) as u where u.ranking <= 3;

聚合函数作为窗口函数,累计统计法

select *, sum(score) over (order by id) as sum_score from user;
-- 结果,逐行累加
+----+------+-------+-------+-----------+
| id | name | score | class | sum_score |
+----+------+-------+-------+-----------+
|  1 | xxh  |   200 |     1 |       200 |
|  2 | xxh2 |   101 |     2 |       301 |
|  3 | xxh3 |    99 |     1 |       400 |
|  4 | xxh4 |   200 |     2 |       600 |
+----+------+-------+-------+-----------+
select *, avg(score) over (order by id) as avg_score from user;
-- 累计求平均，第二行=(200+101)/2, 第三行=(99+101+200)/3
+----+------+-------+-------+-----------+
| id | name | score | class | avg_score |
+----+------+-------+-------+-----------+
|  1 | xxh  |   200 |     1 |  200.0000 |
|  2 | xxh2 |   101 |     2 |  150.5000 |
|  3 | xxh3 |    99 |     1 |  133.3333 |
|  4 | xxh4 |   200 |     2 |  150.0000 |
+----+------+-------+-------+-----------+
-- 累计限制最近的行数
select \*, sum(score) over (order by id rows 2 preceding) as sum\_score from user;
-- 结果，只那当前值和上一行相加
+----+------+-------+-------+-----------+
| id | name | score | class | sum\_score |
+----+------+-------+-------+-----------+
|  1 | xxh  |   200 |     1 |       200 |
|  2 | xxh2 |   101 |     2 |       301 |
|  3 | xxh3 |    99 |     1 |       200 |
|  4 | xxh4 |   200 |     2 |       299 |
+----+------+-------+-------+-----------+
-- 累计前后
select *, sum(score) over (order by id rows between 1 preceding and 1 following) as sum_score from user;
-- 结果
+----+------+-------+-------+-----------+
| id | name | score | class | sum_score |
+----+------+-------+-------+-----------+
|  1 | xxh  |   200 |     1 |       301 |
|  2 | xxh2 |   101 |     2 |       400 |
|  3 | xxh3 |    99 |     1 |       400 |
|  4 | xxh4 |   200 |     2 |       299 |
+----+------+-------+-------+-----------+

总计和小记：同时统计每个班总分和全校总分

-- '总计'为常量，补足列使两倍列数一致
select class,sum(score) from user group by class union select '总计' as class,sum(score) from user;

使用rollup直接得到总计和小记

select class,sum(score) from user group by class with rollup;