MySQL 增强查询语句

插入数据
insert into tab1(name,age) values('alex',18),('jack',19);
insert into tab1(name,age) select name age from tab2; 
 4 select name,age from tab1 where id between 5 and 12; 
 5 select name,age from tab1 where id not in (1,3,5);
select name,age from tab1 where id<1 and id>2 or id>4;
模糊匹配
select name,age from tab1 where name like 'a%';
select age from tab1 where name like 'a_';
查询条数限制
select age from tab1 limit 10 offset 100;  /*从第100条开始取10个*/
select age from tab1 limit 4,5; /*从第4条开始取5条*/
select age from tab1 limit 5;/*取前5条数据*/
排序
select age from tab1 order by id asc,name desc;
分组
select depart_id,count(id) from userinfo group by depart_id;
count() max() min() avg() sum()聚合函数
对于聚合函数结果进行二次筛选 必须使用having
select depart_id,count(id) from userinfo group by depart_id having count(id)>1;

连表操作
select * from tab1,tab2 where tab1.id = tab2.id;
select name,grade from tab1 left join tab2 on tab1.id = tab2.id;
//tab1信息会全部显示
select name,grade from tab1 right join tab2 on tab1.id = tab2.id;
//tab2信息会全部显示

select name,grade from tab1 inner join tab2 on tab1.id = tab2.id;
//将出现null时的行隐藏

 

//导出sql文件 数据表结构+数据
mysqldump -u root db1 > db1.sql -p
//导出sql文件 数据表结构
mysqldump -u root -d db1 > db2.sql -p
//导入sql文件 
mysqldump -u root -d db1 < db1.sql -p