MySQL数据库基础--单表查询
一、常规查询
1、查询所有字段：
在SELECT语句中列出所有字段名名来查询表中的所有的数据。语法格式如下：
select 字段名1,字段名2,.... form 表名;
2、查询指定字段：
在SELECT语句中使用（“*”）通配符来代替所有的字段名。语法格式如下：
select * form 表名;
二、条件查询：
#is null
包括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;
#in
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;
#and or
#查询张姓且地址中含有北京的学生信息
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;
#between and
表示一个范围，包含两端，但是前面值不能大于后面值
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‘
#排序 order by
字段名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
#distinct 去重
select distinct mobile from student #查询去除重复 select name, distinct mobile from student #distinct 必须直接放在select后面 select distinct name, mobile from student #去重 重——所有字段数据相同
三、单行函数
#count()
数据库已经定义好了，多行函数：只对多条数据进行统计 count()统计各组有多少条数据
select count(id) from student select count(id) from student group by sex select sex,count(id) from student group by sex
#min max 获取各组中最小值，最大值
select min(age) from student #统计全表中最小年龄 select min(age) from student group by sex select sex,min(age) from student group by sex
#sum() 统计各组数据之和
select sum(age) from student select sum(age) from student group by sex select sex,sum(age) from student group by sex
#avg() 获取各组中平均数据
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
#where group by order by
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 对统计结果进行排序
#where group by having order by
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