MySQL进阶，not in，join，exists

DQL查询

REGEXP正则查询

select * from app_user where name regexp "^用户" limit 20,10;

in、not in查询

select count(*) from app_user where name not in (select name from app_user where id < 10);
#在id为1，name字段为空字符串的情况下，查询的结果为999991，总数1000000
#在id为1，name字段为null的情况下，查询的结果为0，总数1000000,这是因为在not in中嵌套子查询结果有一个为null，则所有的结果为null

select *  from app_user where name in (select name from app_user where id < 10);

join连接

inner join == join

select s.id,s.cid,s.name,c.name from student s join class c on s.cid = c.id;
select s.id,s.cid,s.name,c.name from student s inner join class c on s.cid = c.id

left join

select s.id,s.cid,s.name,c.name className from student s left join class c on s.cid = c.id;

right join

select s.id,s.cid,s.name,c.name className from class c right join student s on s.cid = c.id;

full outer join

select s.id,s.cid,s.name,c.name className from student s full outer join class c on s.cid = c.id;

MySQL不支持full join

union 实现full join

select s.id,s.cid,s.name,c.name className from student s left join class c on s.cid = c.id
union
select s.id,s.cid,s.name,c.name className from student s right join class c on s.cid = c.id;
#连接两张表的查询结果，重复的不显示

select s.id,s.cid,s.name,c.name className from student s left join class c on s.cid = c.id
union all
select s.id,s.cid,s.name,c.name className from student s right join class c on s.cid = c.id;
#连接两张表的查询结果，显示重复
#order by只能对当前结果进行排序，不能再union前面使用order by排序

select s.id,s.cid,s.name,c.name className from student s left join class c on s.cid = c.id
union all
select s.id,s.cid,s.name,c.name className from student s right join class c on s.cid = c.id where s.id = 1;
#后面查询条件中的where只表示当前查询的where条件，和上面的查询无关

select into 及表复制

# select s.id,s.cid,s.name,c.name className from student s join class c on s.cid = c.id;查询

create table new_table
as
select s.id,s.cid,s.name,c.name className from student s join class c on s.cid = c.id;
#将查询的数据列作为new_table的，并且将数据插入的新表中，新建了一个没有主键的表。
#如果new_table存在，将会报错，提示这张表已经存在。

#只复制表的结构
craete table new_table like old_table #只复制old_table结构，会创建主键
create table new_table as select * from old_table where 1 = 2; #这种方式不会创建主键

insert into select

insert into new_table select s.id,s.cid,s.name,c.name className from student s join class c on s.cid = c.id;
#将查询到的数据插入已经存在的表中,new_table必须存在，不存在报错。
#表中的主键必须在查询中存在，如果没有，设置自增也不会自增
insert into new_table(cid,name,className)
select s.cid,s.name,c.name className from student s join class c on s.cid = c.id;
#可以通过插入数据对于列的方式实现主键自增，但是主键自增不是连续的

函数

count()

select deptno,
count(1) 总人数,
count(case when job ='SALESMAN' then '1' end) 销售人数,
count(case when job ='MANAGER' then '1' end) 主管人数
from emp
group by deptno; #group分组

row_number() over (order by)

rank() over(order by)

dense_rank() over (order by)

exists运算

exists运算用来判断查询子句是否有记录，如果有一条记录或多条记录则返回true，否则fasle;

员工打卡时间统计

select no,date_format(time,'%Y-%m-%d') day,date_format(time,'%H:%i') time from emp 
# 先获取时间
select a.no,a.day day,min(a.time) time from 
(select no,date_format(time,'%Y-%m-%d') day,date_format(time,'%H:%i') time from emp) a
where a.time between '08:00' and '18:00'
group by a.no,a.day
union 
select b.no,b.day day,min(b.time) time from
(select no,date_format(time,'%Y-%m-%d') day,date_format(time,'%H:%i') time from emp) b 
where b.time between '17:30' and '18:00'
group by b.no,b.day
order by day,time
# 缺点，没有统计超出打卡时间范围的人

排序

set @rank = 0,@prev = null;
select id,score,
case
	when @prev = score then
		@rank
	when @prev := score then
		@rank := @rank + 1
	end rank
from student
order by score desc,id asc