0. 准备数据
-- 创建数据库
create database db_python charset=utf8;
-- 使用数据库
use db_python;
-- students表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
-- classes表
create table classes (
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");
1.普通查询
1.1 查询所有字段
-- select * from 表名;
select * from students;
1.2 查询指定字段
-- select 列1,列2,... from 表名;
select name,age from students;
1.3 使用 as 给字段起别名
-- select 字段 as 名字.... from 表名;
select name as '姓名',age as '年龄' from students;
1.4 可以通过 as 给表起别名
-- select 别名.字段 .... from 表名 as 别名;
select s.name as '姓名',s.age from students as s;
1.5 消除重复行(查性别)
-- 在select后面,列的前面使用distinct可以消除重复的行
(去重)
select distinct gender from students;
1.6 删除小明同学
-- 物理删除/硬删除 (通过delete from 把记录从表中删除)
delete from t_students where id=4;
-- 逻辑删除/软删除(用一个字段来表示 这条信息是否已经不能再使用了)
update students set is_delete=1 where id=1;
2.条件查询
select … from 表名 where 条件
2.1 比较运算符 > 、< 、>=、<=、=、!=
-- >
-- 查询大于18岁的学生信息
select * from students where age>18;
-- <
-- 查询小于18岁的学生信息
select * from students where age<18;
-- >=
-- <=
-- 查询小于或者等于18岁的学生信息
select * from students where age<=18;
-- =
-- 查询年龄为18岁的所有学生的名字
select * from students where age=18;
-- != 或者 <>
select * from students where age!=18;
2.2 逻辑运算符 and、or、not
-- and (并且) 年龄在18和28之间的所有学生信息
select * from students where age>18 and age<28;
-- 18岁以上的女性
select * from students where age>18 and gender='女';
-- or (或者)
-- 18以上或者身高高过180(包含)以上的学生信息
select * from students where age>18 or height>=180;
-- not (取反)
-- 不在 18岁以上的女性 这个范围内的信息
select * from students where (not age>18) and gender='女';
2.3 模糊查询(where 列名 like 要查询的数据)
-- like
-- % 替换任意个字符
-- _ 替换1个字符
-- 查询姓周的学生
select * from students where name like '周%';
-- 查询姓名中 包含 "杰" 字的所有名字
select * from students where name like '%杰%';
-- 查询姓周并且“名”是一个字的学生
select * from students where name like '周_';
-- 查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖%';
-- rlike 正则
-- 查询以 周开始的姓名
select * from students where name rlike '^周.*';
select * from students where name rlike '杰.*';
2.4 范围查询
-- in 表示在一个非连续的范围内
-- 查询编号是1或3或8的学生
select * from students where id in (1,3,8);
select * from students where name in ("小明","小月月");
-- between ... and ...表示在一个连续的范围内
-- 查询 年龄在18到34之间的男同学信息
select * from students where age between 18 and 34;
-- 查询 年龄不在在18到34之间的的信息
-- 语法: not between ... and ... : 不再范围内
select * from students where age not between 18 and 34;
select * from students where not (age between 18 and 34);
2.5 空判断
-- is null (判空)
-- 查询身高为空的学生信息
select * from students where height is null;
-- is not null(判非空)
-- 查询身高不为空的学生信息
select * from students where height is not null;
select * from students where not (height is null);
2.6 优先级
--优先级由高到低的顺序为:小括号,比较运算符,逻辑运算符
--not>and>or,如果同时出现并希望先算or,需要结合()使用
--分析以下sql语句:
select * from students where not age>=18 or height>=180 and gender="男";
3.排序
select * from 表名 [where …] order by 列1 asc|desc [,列2 asc|desc,…]
– 有where条件时,order by需放到where条件的后面
– asc从小到大排列,即升序,默认是升序
– desc从大到小排序,即降序
3.1 order by 单个字段字段
-- 查询未删除学生的信息,按年龄降序排序
select * from students where is_delete=0 order by age desc;
3.2 order by 多个字段
-- 查询所有学生信息,按照年龄从小到大、当年龄相同,则身高从高到矮的排序
select * from students order by age asc, height desc;
select * from students order by age , height desc;
4.聚合函数
对查询的数据结果集进行统计分析
4.1 求总数 count
-- count(*)表示计算总行数
-- 统计学生表中的总人数
select count(*) from students;
-- count(列)表示计算某列的总行数,假如该列某个值为null则不会统计
-- 统计学生表中,height不为空的总人数
select count(height) from students;
4.2 求最大值 max
-- max(列) 求此列的最大值
-- 查询最大的年龄
select max(age) from students ;
-- 查询女性的最高身高
select max(height) from students where gender='女';
4.3 求最小值 min
-- min(列)表示求此列的最小值
-- 查询最小身高
select min(height) from students ;
4.2 求和 sum
-- sum(列)表示求此列的和
-- 计算所有学生的年龄总和
select sum(age) from students ;
– 5.5 求平均值
-- avg(列)表示求此列的平均值
-- 计算未删除女生的总人数及平均年龄
select count(*),avg(age) from students where is_delete=0 and gender='女' ;
5.分组
– 在实际业务中,经常会对数据进行分类统计操作,通过 group by 可实现分组,做更精细的聚合统计操作
– select * from tbname where 条件 group by…having 条件…
5.1 group by
-- 将查询结果按照1个或多个字段进行分组,字段值相同的为一组
--注意: select 列 中只能存放分组函数(比如聚合函数),或是出现在group by子句中的分组标签
-- 按照性别分组,查询所有的性别
--去重方式
select distinct gender from students;
--分组方式
select gender from students group by gender;
5.2 group by + 聚合函数
-- 查询每组性别的平均年龄
select gender,avg(age) from students group by gender;
-- 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高
select gender,avg(age) as '平均年龄',max(age),avg(height),max(height) from students group by gender;
5.3 group_concat(列)
-- group_concat(字段名) 拼接字符串, 可以作为一个输出字段的值来使用
-- 表示分组之后,根据分组结果,使用group_concat()来拼接每一组的某字段的值
-- 查询同种性别中的姓名
select gender, group_concat(name), max(age) from students group by gender;
5.4 having条件
-- having 条件表达式:对分组后的数据进一步筛选
-- 除了男生以外的分组的人数
select gender,count(*) from students group by gender having gender!='男';
-- 查询每种性别中的平均年龄avg(age)
select gender,avg(age) from students group by gender;
-- 按性别分组,分别统计出平均年龄超过30岁的组的性别以及姓名 having avg(age) > 30
select gender,group_concat(name) from students group by gender having avg(age)>30;
-- having 和 where 的区别
where: 对源数据做条件筛选,where 条件中 不能接聚合函数
having: 是对分组之后的数据做进一步的筛选操作
有having就一定有group by, 有 group by 不一定有having
having条件中 可以接聚合函数,而where不行
6. 分页
– 当数据量过大时,通过分批、分页加载数据既能提升加载速度,也可更好显示查询结果
– select * from 表名 limit [start,] count
– 限制查询,start为查询的起始位置,count为限制查询的数量
– 注意: limit 放在查询语句的最后面 限定
-- 查询前5个数据
select * from students limit 5;
select * from students limit 0,5;
-- 分页查询
-- 每页显示2个,第1个页面
select * from students limit 0,2;
-- 每页显示2个,第2个页面
select * from students limit 2,2;
-- 每页显示2个,第3个页面
select * from students limit 4,2;
-- 每页显示2个,第4个页面
select * from students limit 6,2;
-- 每页显示2个,显示第6页的信息, 按照年龄从小到大排序
-- 公式: start=(page-1)*count
select * from students order by age limit 10,2;
7.连接查询
7.1 inner join … on (内连接)
– 结果仅包含符合连接条件的两表中的行
– select … from 表A inner join 表B on 条件;
-- 查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
-- 只显示姓名、班级
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
-- 给数据表起名字
select s.name as '姓名',c.name as '班名' from students as s inner join classes as c on s.cls_id=c.id;
7.2 left join … on (左连接)
– 结果仅包含符合连接条件的两表中的行
– select … from 表A left join 表B on 条件;
左表全部行+右表匹配的行,如果左表中某行 在右表中没有匹配的行,则右表该行显示NULL
-- 查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
-- 查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where .....
select * from students as s left join classes as c on s.cls_id=c.id where c.name is null;
7.3 right join … on (右连接)
– 结果仅包含符合连接条件的两表中的行
– select … from 表A right join 表B on 条件;
– 右表全部行+左表匹配的行,如果右表中某行 在左表中没有匹配的行,则左表该行显示NULL
-- 查询没有对应班级信息的学生
select * from classes as c right join students as s on s.cls_id=c.id;
8.子查询 : 查询嵌套
8.1 标量子查询
标量子查询返回的结果是一个数据(一行一列)
-- 查询出高于平均身高的信息(height)
-- 1 查出平均身高
select avg(height) from students;
-- 2 查出高于平均身高的信息
select * from students where height>(select avg(height) from students);
8.2 列子查询
列子查询返回的结果是一列(一列多行)
-- 查询有班级的学生信息
-- select name from students where cls_id in (select id from classes);
-- 1 查出所有的班级id
select id from classes;
-- 2 查出能够对应上班级号的学生信息
select * from students where cls_id in (select id from classes);
8.3 表子查询
表子查询返回的结果是一个临时表(多行多列)
-- 查询编号小于6的男性同学的姓名
select name from (select * from students where id<6) as s where gender='男';
-- select name from (select gender,name from students where id<6) as s where gender="男";
9. 自关联查询
-- 创建areas表(aid,atilte,pid)
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
-- 向areas插入记录
source areas.sql;
-- 查询一共有多少个省
select count(*) from areas where pid is null;
-- 查询广东省中的所有城市
--select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='广东省';
-- 查询属于深圳市的所有的区
--select a.* from areas as a inner join areas as c on a.pid=c.aid where c.atitle='深圳市';
10.总结
SELECT select_expr [,select_expr,...] [
FROM tb_name
[WHERE 条件判断]
[GROUP BY {col_name | postion} [ASC | DESC], ...]
[HAVING WHERE 条件判断]
[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]
完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
执行顺序为:
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count