1.查询语句
只查询符合条件的数据
Select 字段,字段 from 表名 where 条件
#等值查询 select * from t_student where id=2; select * from t_student where name='lisi'; #不等值查询 > >= < <= != <> select * from t_student where id>2; #多条件查询 and or select * from t_student where (id>2 and name='lisi') or...; select * from t_student where id>2 or name='lisi'; #空值/非空查询 select * from t_student where birthdate is null; select * from t_student where birthdate is not null; #区间查询 select * from t_student where id>=1 and id<=3; select * from t_student where id between 1 and 3; select * from t_student where id not between 1 and 3; #枚举查询 select * from t_student where id=1 or id=2 or id=4; select * from t_student where id in(1,2,4); select * from t_student where id not in(1,2,4); #模糊查询 #所有姓zhang的 select * from t_student where name like 'zhang%'; #所有姓名中包含字母l的 select * from t_student where name like '%l%'; #所有姓zhang,并且姓名为8个字符的 select * from t_student where name like 'zhang___'; #所有姓名为4个字符的 select * from t_student where name like '____'; #所有姓名至少5个字符的 select * from t_student where name like '_____%'; #所有不姓zhang的 select * from t_student where name not like 'zhang%'; #所有以zhang开头,以san结尾的 select * from t_student where name like 'zhang%san'; # %:通配任意个字符 # _:只能通配一个字符 |
2.case语句
select id,name, case when sex=0 then 'nan' when sex=1 then 'nv' else '' end sex from t_student; | Select 其他字段, Case When 条件 then 值1 When 条件 then 值2 …… Else 默认值 End 别名 From 表名; Else可以没有,没有else为null |
select id,name, case sex when 0 then 'nan' when 1 then 'nv' else '' end sex from t_student; | Select 其他字段, Case 字段名 When 值1 then 转换的值1 When 值2 then 转换的值2 …… Else 默认值 End 别名 From 表名; #只能对一个字段进行转换 #只能做等值判断 |
3.函数
执行特定功能的一组代码
分类:单行函数:一条记录计算的结果为一个数据
str_to_date()
date_format()
ifnull(值1,值2):如果值1为null,则返回值2;否则返回值1
#查询考试成绩,如果成绩为null,则显示0 select id,name,ifnull(score,0) score from t_student; |
组函数:多条记录计算的结果为一个数据(统计
Sum():求和
Avg():求平均值
Max():求最大值
Min():求最小值
Count():统计个数
使用组函数统计时,会忽略空值
#统计总分 select sum(score) from t_student; select sum(score) from t_student where sex=0; #统计平均分 select avg(score) from t_student; #把缺考视为0分 select avg(ifnull(score,0)) from t_student; #最低分 select min(score) from t_student; #统计不重复值的个数 select count(distinct name) from t_student; |
4.分组查询
按照某个字段分组进行统计
select count(id) from t_student; #分组查询,按照sex字段分组进行统计人数 select sex,count(id) from t_student group by sex; #统计男女生中的最高分分别为: select sex,max(score) from t_student group by sex; #在标准的SQL查询语句中,分组查询只能查询分组依据字段和组函数 #同时按照班级和性别分组统计 select cid,sex,count(id) from t_student group by cid,sex; #查询后按照人数排序 select cid,sex,count(id) from t_student group by cid,sex order by count(id); #只查询参加过考试的人数 select cid,sex,count(id) from t_student where score is not null group by cid,sex order by count(id); |
select 后的字段,除组函数之外的所有要求显示的字段必须被group by,不然语句错误。
5.分组后筛选
#统计1班和2班中的人数 select cid,count(id) from t_student group by cid; #使用where做条件查询 select cid,count(id) from t_student where cid in(1,2) group by cid; #使用having做分组后筛选 select cid,count(id) from t_student group by cid having cid in(1,2);
#统计平均分数>=85的班中每个班的人数 select cid,count(id) from t_student group by cid having avg(score)>=85; |
where中不允许使用组函数作为筛选条件,因为SQL语句的执行顺序where在group by之前,在where作筛选的时候组函数还无法使用(如果有别的字段,不用group by来分组将无法使用组函数)。结论:having是在分组后筛选,如果能在分组前使用where筛选,优先使用where,效率高,但是不是所有的需求都可以使用where
6.select语句执行顺序
查询语句的写法:
Select 字段------------------5
From 表------------------1
Where 条件---------------2
Group by 分组依据---------3
Having 筛选条件-----------4
Order by 排序规则---------6
7.子查询
一次查询可以以前一次查询的结果的基础上进行查询。
(1)子查询的结果为一个数据(一行一列)
#查询考试分数大于平均分的学生 #1查询平均分 select avg(score) from t_student; #2查询分数大于平均分的学生 select * from t_student where score>(select avg(score) from t_student); |
(2)子查询的结果为一个字段(多行一列)
#查询所有和姓zhang的同班的同学 #1查询有姓zhang的学生的班级id select distinct cid from t_student where name like 'zhang%'; #2根据cid查询学生 select * from t_student where cid in( select distinct cid from t_student where name like 'zhang%'); |
(3)子查询的结果为一张表(多行多列)
把子查询的结果视为一张表,在此基础上再次查询
#查询所有参加考试的女生 select * from t_student where sex=1; select * from ( select * from t_student where sex=1) r1 where score is not null; |
8.集合查询
#查询姓张的学生或女生 select * from t_student where name like 'zhang%' or sex=1;
#并集,去掉重复数据 select * from t_student where name like 'zhang%' union select * from t_student where sex=1; #并集,不去掉重复元素 select * from t_student where name like 'zhang%' union all select * from t_student where sex=1; |
注意:集合运算不一定从一个表中查询数据,但是必须保证字段个数、顺序、类型一致
查询结果字段名以第一次查询为准
9.连接查询
(1)外连接:
a)左外连接:以左表为主表,查询出左表中所有的数据,如果左表中有无法和右表中连接的数据,右表中数据以null表示
#左外连接 select s.name sname,c.name cname from t_student s left join t_class c on s.cid=c.id; |
b)右外连接:以右表为主表,查询出右表中所有的数据,如果右表中无无法和左表中连接的数据,左表中数据以null表示
#右外连接 select s.name sname,c.name cname from t_student s right join t_class c on s.cid=c.id; |
c)全外连接:以两个表为主表,会查询出两个表中所有的数据,无法连接的数据以null表示
#全外连接 #select s.name sname,c.name cname from t_student s # full join t_class c on s.cid=c.id;#MySQL不支持 select s.name sname,c.name cname from t_student s left join t_class c on s.cid=c.id union select s.name sname,c.name cname from t_student s right join t_class c on s.cid=c.id; |
MySQL不支持全外连接,所以只能用左外连接和右外连接的并集来实现
(2)内连接:没有主表,只查询能够连接的数据
#内连接 select s.name sname,c.name cname from t_student s inner join t_class c on s.cid=c.id; #使用条件查询实现内连接 select s.name sname,c.name cname from t_student s,t_class c where s.cid=c.id; |
(3)自连接:一个表中的字段是一个指向本表中主键的外键
#自连接 create table t_type( id int primary key, name varchar(50) not null, pid int references t_type(id) ) charset=utf8; insert into t_type values(1,'电子产品',null); insert into t_type values(2,'食品',null); insert into t_type values(3,'手机',1); insert into t_type values(4,'智能手机',3);
select * from t_type; #查询所有大类 select * from t_type where pid is null; #查询类别和上级类别 select t1.name,t2.name pname from t_type t1 left join t_type t2 on t1.pid=t2.id; select t1.name,t2.name pname from t_type t1 inner join t_type t2 on t1.pid=t2.id; |
注意:自连接只是一种表结构,可以使用内连接或外连接查询。