Mysql中的select常用语法

一、检索数据

1、检索多个列

select id,name from test;  

2、检索所有列

select * from test;

3、检索不同的行

select distinct age from test;

4、限制结果

select  id from test limit  5,5;

从行5开始的5行

二、排序检索数据

1、排序数据

select *from test order by age (asc);

2、多个列排序

select *from test order by age,name (asc);

3、逆序数据

select *from test order by age desc;

三、过滤数据

1、WHERE子句

select *from test where age=18;

2、不匹配检查

select *from test where age<>18;

3、范围值检查

select *drom test where age between 15 and 18;

4、空值检查

select *from test whereage is null; 

四、高级过滤数据

1、and操作符,匹配全部条件

select *from test where age<18 and id >10;

2、or操作符,匹配单一条件

select *from test where age<18 or id <10;

3、in操作符

select *from test where age in (15 , 16);

4、not操作符

select *from test where age not in(15 , 16); 

五、通配符过滤

1、% 操作符,任意字符任意次

select *from test where name like ‘le%’; 

2、_ 操作符,任意字符单次

select *from test where name like ‘le_’;

六、正则表达式搜索

1、基本字符匹配

select *from test where name regexp 'le'; 

2、. 操作符,任意字符任意次

select *from test where name regexp 'le.';   

3、匹配几个字符之一

select *from test where name regexp '[abc]le';   

4、匹配范围

select *from test where name regexp '[a-c]le';   

5、匹配特殊字符

select *from test where name regexp 'a\\.le';   

七、汇总数据

1、avg(),平均值

select avg(age) as avg_age from test ; 

2、count(),数目

select count(*) as num_id from test;   

3、sum(),总和

select sum(age) as sum_age from test;   

4、max(),最大值

select max(age) as max_age from test;    

5、min(),最小值

select min(age) as min_age from test;   

6、聚集不同值

select age(distinct age) as avg_age from test;   

八、分组数据

1、创建分组

select id,count(*) as num_project from test  group by id; 

2、过滤分组

select id,count(*) as num_project from test  group by id having count(*)>2;    

九、子查询

1、子查询过滤

select *from test where name in (select name from exam where math > 80);

十、联结查询

1、创建联结

select  name,math from test,exam where test.id=exam.id;

2、表别名联结

select  name from test as a,exam as b where a.id=b.id;

3、自联结

select  a.name ,a.china from exam as a,exam as b where a.id=b.id and b.math =100;

查询数学成绩100分的同学的其他科目

4、聚集联结

select  test.name from test inner on eam on test.id=exam.id group by test.id;

十一、组合查询

1、union用法

select  test.id,exam.id from test where age>15 union  select  test.id,exam.id from exam where math>80;

1、union重复行

select  test.id,exam.id from test where age>15 union all  select  test.id,exam.id from exam where math>80;

3、自联结

select  a.name ,a.china from exam as a,exam as b where a.id=b.id and b.math =100;

查询数学成绩100分的同学的其他科目

4、聚集联结

select  test.name from test inner on eam on test.id=exam.id group by test.id;