1、查询所有字段：
在SELECT语句中列出所有字段名名来查询表中的所有的数据。语法格式如下：
select 字段名1,字段名2,.... form 表名;
2、查询指定字段：
在SELECT语句中使用（“*”）通配符来代替所有的字段名。语法格式如下：
select * form 表名;
包括is null 和 is not null
注意：is null不能写成 = null，同样，is not null不能写成!=null
select * from student where address is null; select * from student where address is not null;
mysql中in常用于where表达式中，其作用是查询某个范围内的数据。
select * from student where age in(10,17,24);
#上面SQL语句等效于下面语句
select * from student where age = 10 or age=17 or age = 24;
#查询张姓且地址中含有北京的学生信息
select * from student where name like ‘张%‘ and address like ‘%北京%’;
#查询张姓或地址中含有北京的学生信息
select * from student where name like ‘张%‘ or address like ‘%北京%’;
#查询年龄小于28岁的学生信息
select * from student where age<28;
表示一个范围，包含两端，但是前面值不能大于后面值
select * from student where age between 10 and 24 select * from student where age >= 10 and age <=24
select * from student where name like ‘王%‘ select * from student where name like ‘张三%‘
# ‘%’匹配0个或多个字符, ‘_‘只匹配一个
select * from student where name like ‘张_‘ #_必须匹配一个，只能匹配一个 select * from student where name like ‘%A%%‘ escape ‘A‘ select * from student where name like ‘%B_%‘ escape ‘B‘
字段名1，字段名2，。。。。 desc（降） asc（升）
select * from student order by age asc select * from student order by age desc
select * from student where address is not null order by age asc
#多字段排序，前面字段数据相同时，才会对下个字段内数据进行排序 select * from student where address is not null order by age asc,mobile desc
select distinct mobile from student #查询去除重复 select name, distinct mobile from student #distinct 必须直接放在select后面 select distinct name, mobile from student #去重 重——所有字段数据相同
数据库已经定义好了，多行函数：只对多条数据进行统计 count()统计各组有多少条数据
select count(id) from student select count(id) from student group by sex select sex,count(id) from student group by sex
select min(age) from student #统计全表中最小年龄 select min(age) from student group by sex select sex,min(age) from student group by sex
select sum(age) from student select sum(age) from student group by sex select sex,sum(age) from student group by sex
select avg(age) from student select avg(age) from student group by sex select name, avg(age) from student group by sexselect sum(age) from student select name, sum(age) from student
select sex, avg(age) from student where age>0 group by sex order by avg(age) desc where age>0 获取有效数据 group by sex 进行分组 sex, avg(age) 进行统计 order by avg(age) desc 对统计结果进行排序
select sex, avg(age) from student where age>0 group by sex order by avg(age) desc where age>0 获取有效数据 group by sex 进行分组 sex, avg(age) 进行统计 having 对统计结果进行过滤 order by avg(age) desc 对统计结果进行排序
select name from student where age>0 group by name select name ,count(name) from student where age>0 group by name select name ,count(name) from student where age>0 group by name having count(name)>1 select name from student where age>0 group by name having count(name)>1
原文：https://www.cnblogs.com/ll1130/p/14607474.html