SELECT语句,mysql的sql语句不区分大小写。表和列可以有别名

1 查询字段

查询所有字段可以用*或者所有列名，推荐后者，多列列名要用英文逗号分开。
select * from 表名;

 drop table if exists tb_emp1;
 create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,100); -- succ

 select * from tb_emp1;

1 1 a 100 1 2 100

select depid,id,name,salary from tb_emp1;

2 筛选条件

用where过滤

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,100); -- succ

1 条件判断

select * from tb_emp1 where salary>=100;

1 1 a 100
1 2 100

2 IN

select * from tb_emp1 where id in(1,2);

1 1 a 100
1 2 100

select * from tb_emp1 where id not in(1,3);

1 1 a 100

3 BETWEEN AND

select * from tb_emp1 where id BETWEEN 2 AND 4;

1 2 100

select * from tb_emp1 where id NOT BETWEEN 2 AND 4;

1 1 a 100

4 LIKE
_ 匹配一个任意字符， %匹配0到任意个任意字符，如需转义\,其中\在插入时就要转义

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,1,'_a',50); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,2,'%a',200); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,3,'\\a',200); -- succ

 select * from tb_emp1 where name like '_a';

depid id name salary
2 1 _a 50
2 2 %a 200
2 3 \a 200

select * from tb_emp1 where name like '\_a';

2 1 _a 50

select * from tb_emp1 where name like '%';

1 1 a 100
2 1 _a 50
2 2 %a 200
2 3 \a 200

select * from tb_emp1 where name = '\\a';

2 3 \a 200

5 查询空值

select * from tb_emp1 where name IS NULL;

1 2 100

select * from tb_emp1 where name IS NOT NULL;

depid id name salary
2 1 _a 50
2 3 \a 200
2 2 %a 200
1 1 a 100

6 AND 可以有多个

select * from tb_emp1 where name IS NOT NULL AND salary>50 AND id=1;

1 1 a 100

7 OR 可以有多个,优先级低于AND

select * from tb_emp1 where name IS NULL OR salary=50 AND id=1;

1 1 a 100

3 DISTINCT 消除重复记录

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,1,'_a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,2,'%a',100); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,3,'\\a',100); -- succ
select DISTINCT id,salary from tb_emp1;

1 100
2 100
3 100

4 排序

order by 字段名,字段名 [ASC|DESC]
默认就是ASC,多个字段先排前面的，排完基础上再排后面的。

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',10); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,50); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,1,'_a',30); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,2,'%a',20); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,3,'\\a',40); -- succ
select * from tb_emp1 ORDER BY salary;

1 1 a 10
2 2 %a 20
2 1 _a 30
2 3 \a 40
1 2 50

select * from tb_emp1 ORDER BY depid asc,salary desc;

1 2 50
1 1 a 10
2 3 \a 40
2 1 _a 30
2 2 %a 20

5 分组

group by 字段名,字段名 [having 组条件]

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',10); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,50); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,1,'_a',30); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,2,'%a',20); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,3,'\\a',40); -- succ
select depid,id from tb_emp1 GROUP BY depid,id;

1 2
2 1
2 3
2 2
1 1

select depid,count(*) from tb_emp1 GROUP BY depid;

1 2
2 3

select depid,count(*) from tb_emp1 GROUP BY depid having count(*)>2;

2 3

group_concat 合并组内的一个字段

select depid,group_concat(id) from tb_emp1 GROUP BY depid;

1 1,2
2 1,2,3

with rollup 新加一行统计总和，此时不能有order by了

select depid,count(*) from tb_emp1 GROUP BY depid with rollup ;

1 2
2 3
5

集合函数可以和group一起用，也可以用在全部
count(*)统计总行数,count(字段名)忽略该字段空值。
sum(列)求和
avg(列)求平均
max(列)求最大
min(列)求最小

6 limit

limit [行偏移量,] 行数
行偏移量默认0，第一条记录。

drop table if exists tb_emp1;
create table tb_emp1(
     depid int,
     id int,
     name varchar(20),
     salary double not null,
     primary key(depid,id),
     constraint uniq_name unique (name)
 );
              
 insert into tb_emp1(depid,id,name,salary) values(1,1,'a',10); -- succ
 insert into tb_emp1(depid,id,name,salary) values(1,2,null,50); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,1,'_a',30); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,2,'%a',20); -- succ
 insert into tb_emp1(depid,id,name,salary) values(2,3,'\\a',40); -- succ
select depid,id from tb_emp1 limit 2;

1 2
2 1

select depid,id from tb_emp1 limit 0,2;

1 2
2 1

select depid,id from tb_emp1 limit 1,2;

2 1
2 3

7 内连接

inner join

drop table if exists tb_emp1;
create table tb_emp1(
id int primary key,
name varchar(10)
);

drop table if exists tb_part1;
create table tb_part1(
pid int,
id int,
dname varchar(10),
primary key(pid,id)
);
insert into tb_emp1(id,name) values('1','a');
insert into tb_emp1(id,name) values('2','b');
insert into tb_emp1(id,name) values('3','c');
insert into tb_emp1(id,name) values('4','d');
insert into tb_part1(pid,id,dname) values('11','1','aa');
insert into tb_part1(pid,id,dname) values('11','2','aa');
insert into tb_part1(pid,id,dname) values('12','3','bb');
insert into tb_part1(pid,id,dname) values('13','5','cc');

select t1.id,t1.name,t2.pid,t2.dname from tb_emp1 t1, tb_part1 t2 where t1.id=t2.id

1 a 11 aa
2 b 11 aa
3 c 12 bb

select t1.id,t1.name,t2.pid,t2.dname from tb_emp1 t1 inner join tb_part1 t2 on t1.id=t2.id

1 a 11 aa
2 b 11 aa
3 c 12 bb

8 左连接

left join 左表所有行出现，若右表没有则右表列为空

select t1.id,t1.name,t2.pid,t2.dname from tb_emp1 t1 left join tb_part1 t2 on t1.id=t2.id;

1 a 11 aa
2 b 11 aa
3 c 12 bb
4 d

9 右连接

right join 右表所有行出现，若左表没有则左表列为空

select t1.id,t1.name,t2.pid,t2.dname from tb_emp1 t1 right join tb_part1 t2 on t1.id=t2.id;

1 a 11 aa
2 b 11 aa
3 c 12 bb
13 cc

10 子查询

any(some) 与子查询任何值比较为true则为true

select t1.id,t1.name from tb_emp1 t1 where t1.id = any(select id from tb_part1)

1 a
2 b
3 c
all 要满足所有子查询条件

select t1.id,t1.name from tb_emp1 t1 where t1.id <= all (select id from tb_part1)

1 a

exists 子查询能至少返回一行,NOT exists相反

select t1.id,t1.name from tb_emp1 t1 where  exists(select t2.id from tb_part1 t2 where t1.id=t2.id)

1 a
2 b
3 c


in 子查询的结果为外层的比较条件；not in 相反

select t1.id,t1.name from tb_emp1 t1 where  t1.id in (select t2.id from tb_part1 t2)

1 a
2 b
3 c

11 合并查询结果/h3>

union 会删除重复记录，union不删除

select t1.id from tb_emp1 t1 where  t1.id in('1','2')
union 
select t1.id from tb_emp1 t1 where  t1.id in('1','3')

1
2
3

select t1.id from tb_emp1 t1 where  t1.id in('1','2')
union all
select t1.id from tb_emp1 t1 where  t1.id in('1','3')

id 1
2
1
3

12 正则表达式/h3>

mysql是regexp

select t1.pid,t1.dname from tb_part1 t1 where  t1.dname regexp '^a|b'

11 aa
11 aa
12 bb

13 通用表达式

cte:common table expressions，可以复用的子查询，可以在select/update/delete前面

with cte as(select t1.id,t1.name from tb_emp1 t1 )
select t2.pid,t2.dname,cte.id,cte.name from tb_part1 t2,cte where t2.id=cte.id

11 aa 1 a
11 aa 2 b
12 bb 3 c