完整的查询语法组成
注意:sql完整的查询分为8个部分,每个部分要么不写,要么必须按照上面的排序方式来写,顺序不能改变
1.select [选项 all | distinct]
2.字段表达式
3.from 子句
4.where 子句
5.grop by 子句
6.having 子句
7.order by 子句
8.limit 子句
查询语法详解
1.select [选项 all | distinct]
2.字段表达式
语法: a:单独列举字段名 例:select name,sex from student;
b: * 通配符,匹配所有的字段名 例:select * from studen;
c:聚合函数 count(),sum(),avg(),max(),min(),grop_concat()
例:select count(*) from student;
d:为字段取别名:字段名 as 别名 (as可以省略不写)
例:select count (*) as count from student;
例:select count(*) count from student;
3.from 子句 from后面接的是数据源,表示查询数据的来源
数据源:通常由一张表,或多张表(指连表查询)或子查询组成
连表查询:查询学生的姓名及其班级名和教室
例:select student.name,class.class_name,class.class_room
from student
join class on student.class_id=class.class_id;
4.where 子句
语法:where 条件表达式
作用:使用用于对数据源进行过滤和筛选
过滤和筛选原理:根据“条件表达式”的结果,如果结果为True,数据被保留,如果结果为False结果被过滤掉
例:select * from student where sex="女";
例:select * from student where 1; # 查询所有数据
a.条件式的写法:比较运算 > >= < <= 特殊:= != <> (也是不等于)
例:查询性别不为男的学生:
select * from student where sex != "男";
select * from student where sex <> "男";
b.集合判断in 和 not in 语法: 字段 in (值1,值2,值3....)
语法: 字段 not in (值1,值2,值3....)
例:查询学生id为 1,3,6,8,10 的学生的信息
select * from student where id in (1,3,6,8,10);
删除学生id为 1,3,6,8,10 的学生的信息
delete from student_copy where id in (1,3,6,8,10);
c.模糊查询 like 语法:字段名 like "%_关键字"
例:查询姓张的学生
select * from student where name like "张%"
查询姓猪的学生,名字由三个字组成
select * from student where name like "猪__";
d. 范围查询 between .. and ..
语法: 字段名 between 小值 and 大值 , 包含边界值
语法: 字段名 not between 小值 and 大值 , 不包含边界值
例:查询年龄在 30-49 之间的学生
select * from student where age between 30 and 49;
查询年龄不在 30-49 之间的学生
select * from student where age not between 30 and 49;
e.group by 分组查 语法: group by 字段
分组查询的目的:为了统计结果
统计函数:count() sum() avg() max() min() group_concat() : 将组内字段的值通过,拼接成一个字符串
分组查询只能查找聚合函数以及分组的字段
例: 求每个班级的人数, 每个班平均年龄,统计每个班的名单
select class_id,count(*) as count,avg(age) as avg, group_concat(name) as 名单
from student
where class_id is not null
group by class_id
求男女分别的人数
select sex,count(*) as count
from student
group by sex;
5.having 子句:用于分组后的再过滤(用法和where一样,只是位置不同)
语法:having 条件表达式
例:查询班级人数大于2的班级id
select class_id,count(*) as count
from student
where class_id is not null
group by class_id
having count > 2;
7.order by 子句:排序,对数据源进行排序
语法:order by 字段 [规则 asc|desc], 字段2 [规则];
说明:1. asc 默认 升序|desc 降序
2. 按照多个字段排序,先排好第一个,在此基础上的分组的部再按第二个字段排序.
例:查询学生信息,要求按照年龄降序排列
select * from student order by age desc;
查询学生信息,要求按照班级id升序排列,再按照年龄降序排列;
select * from student where class_id is not null order by class_id asc,age desc;
8.limit 子句:限制查询结果的条数,作用是减轻数据库服务器的压力
语法:语法: limit start,length;
表示 从开始索引start(默认0)位置查起,总共查length条记录
主要应用于分页
例:查询学生信息: 每页显4条
查询学生信息: 每页显4条
第一页:
select * from student limit 0,4;
第二页:
select * from student limit 4,4;
第三页:
select * from student limit 8,4;
第n页:
select * from student limit (n-1)*4,4;
union 联合查询
1. union 联合查询 将多个查询结果集合到一起
2. 语法
select 查询语句
union
select 查询语句
union
select 查询语句
union
select 查询语句
例:查询学生信息和班级信息,集合到一起
select class_id,class_name,class_room from class
UNION
select name,age,sex from student;
说明:
1. 联合查询要求列的数量要一致
2. 列名以第一个查询结果为准
例:4. 例子: 查询男同学,要求年龄降序排列, 女同学要求年龄升序排列, 集合一个结果
(select * from student where sex="男" order by age desc limit 100)
union
(select * from student where sex="女" order by age asc limit 100)
说明:
如果查询中有排序
1. 需要将每个查询使用()扩起来
2. 必须在每个查找中结合limit语句使用
5. 例子: 分别查询男同学 和 年龄 大于30的人, 将两个结果集合到一起.
select * from student where sex="男"
union
select * from student where age > 30;
union [选项 all | distinct 默认]
子查询
子查询: 将一条sql查询语句作为中间结果,供另外一条查询语句使用, 这个中间结果查询就是子查询. 就是sql嵌套查询
作用:原本需要执行多条sql完成的事情,如果通过子查询只需要执行一条sql, 这样可以简化执行步骤.(代码中的步骤)
例:查询学生信息,要求学生年龄小于全部学生的平均年龄.
-- a.查询全部学生平均年龄
select avg(age) as avg from student; # 45
-- b. 查询大于平均年龄的学生
select * from student where age < 45;
-- c. 通过子查询实现
select * from student where age < (select avg(age) as avg from student);
子查询的分类:
按位置分:
where 型子查询: 子查询出现在where子句中
select * from student where age < (select avg(age) as avg from student);
from 型子查询: 子查询出现在from子句中
注意实现:
1. 如果子查询出现在from子句中, 相当于一个临时表(派生表), 需要取一个别名;
案例: 查询学生表中的年龄排前5的学生, 要求结果是按照年龄升序排列.
-- a 查询学生表中的年龄排前5的学生
select * from student order by age desc limit 0,5;
-- b 结果是按照年龄升序排列
select * from (select * from student order by age desc limit 0,5) as t order by age asc;
按结果分:
列子查询: 子查询结果为一列多行
说明: 列子查询其实返回的是一个集合,判断使用 in 或者 not in
案例: 查询没有学生的 班级信息
select * from class where class_id not in (select distinct class_id from student where class_id is not null);
-- 经验: 逆向思维
-- 先查询有学生的 班级信息
a. 查询学生表中的班级id(有班级id就说明有学生)
select distinct class_id from student where class_id is not null;
b. 根据查询到的班级id到班级表中查询班级信息
select * from class where class_id in (1,2,3);
c. 子查询形式
select * from class where class_id in (select distinct class_id from student where class_id is not null);
行子查询: 子查询结果为一行多列
说明:
需要使用 小括号() 行构建符, 将多个字段的值构建成一行, 与行子查询的结果做对比.
-- 案例: 请问学生中是否有班级id最大且年龄也最大的学生?
a. 查询班级id最大的值和年龄最大的值
select max(class_id),max(age) from student;
b. 根据以上结果再查询学生信息
select * from student where class_id=3 and age = 100;
c. 子查询形式实现
select * from student where (class_id,age) = (select max(class_id),max(age) from student)
表子查询: 子查询结果为多行多列
-- 案例: 查询每个班中年龄最大的学生的信息.
a. 分组查询每个班中年龄最大的值
select class_id,max(age) as max from student where class_id is not null group by class_id;
b. 根据以上结果 再查询学生的信息
select * from student
where (class_id,age) in (select class_id,max(age) as max from student where class_id is not null group by class_id)