MySQL的查询(超详细附带数据供练习)

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