3.

去重：select distinct 字段名 from 表名
排序：select f_name,f_price from fruits order by f_price,f_name; （desc（降序），asc（升序））
单组分组：select s_id,count() from fruits group by s_id;
多组分组：select * from fruits group by s_id,f_name（MySQL5.7被限制）
别名：select s_id,count() as ‘种类’ from fruits group by s_id
以组进行划分：select s_id,group_concat(f_name) as ‘种类’ from fruits group by s_id;
过滤分组（可与group by 一起使用）：select s_id,group_concat(f_name) as ‘水果名’ from fruits group by s_id having count(f_name) > 1;
having与where的区别：having：在数据分组之后进行过滤来选择分组 where：在分组之前用来选择纪录，排除的记录不再包括再分组中
求总和（记录数量）：select s_id,count(*) from fruits group by s_id with rollup;
合并分组：select * from fruits group by s_id order by s_id（MySQL5.7被限制）
限制查询：select * from fruits limit 4
select * from fruits limit 4,3;(第4行开始的后面3行)
select avg(f_price) from fruits（平均数）
select sum(f_price) from fruits;（求和）
select max(f_price) from fruits;（最大值）
select min(f_price) from fruits;（最小值）
select count(f_price) from fruits（行数）

（联合查询）连接查询：内连接：inner join 自连接 返回查询结果中的仅是符合查询条件和连接条件的行
外连接：返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表或右表中的或两个表（全外连接）中所有的数据行
左连接：left join 返回包括左表中的所有记录和右表中连接字段相等的记录
有连接：right join 返回包括右表中的所有记录和左表中连接字段相等的记录
内连接：select * from fruits inner join suppliers on fruits.s_id = suppliers.id;
自连接：select f1.f_id,f1.f_name from fruits as f1 , fruits as f2 where f1.s_id = f2.s_id and f2.f_id = ‘a1’;
左连接：select * from fruits left join suppliers on fruits.s_id = suppliers.id;
右连结：select * from fruits right join suppliers on fruits.s_id = suppliers.id;
子查询：一个查询嵌套在另外一个查询语句内部的查询 any some
select num1 from tb1 where num1 > any (select num2 from tb2);
select num1 from tb1 where num1 > some (select num2 from tb2);
select num1 from tb1 where num1 > all (select num2 from tb2);
判断是否存在：select * from fruits where exists (select f_name from fruits where s_id = 101);
select * from fruits where not exists (select f_name from fruits where s_id = 101);
合并查询结果：select column。。。。from table1 union [all] select column。。。。from table2
select * from fruits where f_price > 9 union all select * from fruits where s_id in(101,103);
表名、列明 [as] 别名

正则

插删改：
insert into person(id,name,age,info) values(1,‘guangshun’,20,‘henan’);
insert into person(name,age,info) values(‘chaobo’,21,‘henan’);
insert into person(name,age) values(‘aodong’,18);
insert into person(name) values(‘yixin’);
insert into person values(3,‘liuwei’,21,‘henan’);
insert into person values(7,‘xiedong’,21,‘anhui’),(8,‘yulin’,22,‘shanxi’);
insert into person2 select * from person;
insert into person2
update person set age=20 where id=6;
update person set info=‘henan’ where id=5;