SQL语句补充--增删改查 limit 排序 分组 连表操作

   create table tb1( 
		   id int auto_increment primary key,
		   name
		   varchar(32),
		   age int 
	   )engine=innodb default charset=utf8;

增:

  insert into tb1(name,age) values('alex',12);
  insert into tb1(name ,age) values('alex',12),('root',18);
  insert into tb2(name,age) select name,age from tb1;

删;

delete from tb2;
delete from tb2 where id !=2;
delete from tb2 where id =2;
delete from tb2 where id >=2;
delete from tb2 where id <=2;
delete from tb2 where id <=2 or name='alex';

改：

update tb2 set name='alex' where id >10 and name='xx';
update tb2 set name='alex' , age=12 where id >10 and name='xx';

查：

select * from tb2;
select id,name from tb2;
select id,name from tb2 where id>10 or name='xx';
select id,name as cname from tb2 where id>10 or name='xx';  #as取个别名
select id,name,1 from tb2; #常量1
select * from tb2 where id in (1,2,5);
select * from tb2 where id not in (1,2,5);
select * from tb2 where id in 2 between 7; #2 和 7都包括在内
select * from tb2 where id not in (select id from 表名);

模糊查询（通配符）

 select * from tb2 where name like "%a"; 	
 select * from tb2 where name  like "a_";

分页

 - select * from tb limit 10; #取前10
 - select * from tb limit 0,10; #从0开始向后取10条
 - select * from tb limit 0 offset 11;

排序：

 - select * from tb2 order by id desc; #从大到小 	
 - select * from tb2 order by id desc；#从小到大
 - select * from tb2 order by id desc limit 10; #取后10条数据
 - select * from tb2 order by id desc，age desc;

分组:

 - select count(id) from tb2 group by part_id;
 - #聚合函数 
 - count;
 - sum
 - avg
 - max
 - min
 - 对于聚合函数二次筛选时，必须使用having 不能用where
 - select count(id) from tb2 group by part_id having count(id) >1;

连表操作：

 - select * from tb1 ,tb2 where tb1.id = tb2.id;
 -  select * from tb1 left join tb2 on tb1.id = tb2.id;#左边全部显示
 -  select * from tb1 right join tb2 on tb1.id = tb2.id #右边全部显示