sql 语句增强

1、 group 语句

1、创建表

create tabel test(
a varchar(20),
b varchar(20),
c varchar(20)
)

2、 插入语句

insert into test values(1,'a','甲')
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');

例子1：

select   count(a),b from test group by b

例子2：

select count(a),b,c from test group by c

例子3：

select count(a),b,c from test group by b,c  # b和c的排列组合都满足的情况下

distinct 语句

1、创建表

create tabel test(
a varchar(20),
b varchar(20),
c varchar(20)
)

2、 插入语句

insert into test values(1,'a','甲')
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');

例子1 ：

SELECT distinct b from test;

例子2：

SELECT DISTINCT * from test;
或
SELECT DISTINCT a,b,c from test;  # 全部的属性的排列组合都只有一种

例子3：

SELECT DISTINCT b,COUNT(*) from test;   # 加上聚合函数会导致失效

开窗函数

mysql8.0前不支持开窗函数

1、创建表

create tabel test(
a varchar(20),
b varchar(20),
c varchar(20)
)

2、 插入语句

insert into test values(1,'a','甲')
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');

select a,b,(select count(*) from test where b = "a" where b="a"; 

两个的结果相同：

select a,b,count(*) over() from test where b = "a"

开窗函数

case when

1、创建表

create TABLE test_time (
    istime datetime
		)

2、 插入语句

select CASE istime
	WHEN istime THEN
		DATE_FORMAT(istime,"%Y%m%d")
END
 from test_time

is not null 和 ！= null的区别

1、创建表

create tabel test(
a varchar(20),
b varchar(20),
c varchar(20)
)

2、 插入语句

insert into test values(1,'a','甲')
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','甲');
insert into test values(1,'a','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');
insert into test values(1,'b','乙');

例子1：

select * from test
WHERE a != null  # 结果为空的值

例子2：

select * from test
where a is not null   # 存在值

临时表

mysql方式：

create temporary table demo (

)

只在一次连接内有效
我用mysql5.7试了一下，报错

select * into #new_table from test   
select * from #new_table

with as

只有mysql8及以上才可以使用

with demo AS (
	select * from test
)
selct * from demo

相当于创建了一张临时表，然后我们去判断临时表的数据

reverse

优化like
使用环境： 当like中 前面有% 而后面没有%的时候可以使用