前言
在数据驱动的世界中，MySQL的SELECT查询语句是解锁数据宝藏的关键。本文将带您领略SELECT语句的魅力，从基础查询到高级应用，包括条件筛选、逻辑运算、模糊与正则匹配、排序、聚合、分组过滤、分页查询以及连接查询和子查询等。此外，我们还将探讨索引对查询性能的重要性。无论您是数据库新手还是资深开发者，这里都将是您提升查询技能的宝贵资源。让我们一同开启MySQL查询的旅程！
0.准备一张表先
创建一个名叫stu的表 id 主键 自增 非空 name 默认空 age 默认0 height 浮点型 gender 枚举 默认保密 isdelete 默认0 create table stu ( id tinyint primary key auto_increment, name varchar(5) default "", age tinyint default 0, height decimal(5,2), gender enum('男','女','中性','保密') default '保密', isdelete tinyint default 0 ) 添加数据 insert into stu values (0,"小明",18,180.00,"女",0), (0,"小月月",18,180.00,"女",1), (0,"彭于晏",29,185.00,"男",0), (0,"刘德华",59,175.00,"男",1), (0,"黄蓉",38,160.00,"女",0), (0,"刘亦菲",25,166.00,"女",0), (0,"程坤",27,181.00,"男",1), (0,"金星",33,162.00,"中性",0), (0,"静香",12,180.00,"女",1), (0,"郭靖",12,170.00,"男",0), (0,"周杰",34,176.00,"男",0);
1.简单语句查询
1.1 基本查询
# a.查询表中所有的字段 *代表所有的字段(列) 语法：>>> select * from 表名; 案例：>>> select * from 表名; # b.查询指定字段 语法：>>> select 字段1，字段2,...... from 表名 案例：>>> select name,age from stu; 案例：>>> select stu.name,stu.age from stu; # c.给字段名起别名 字段名 as 别名 # 别名仅在当前这个sql语句中起效，不是真正的修改字段名 语法：select 字段名 as 别名,字段名 as 别名 ...... from 表名 案例：select name as 姓名,age as 年龄 from stu; # d.给表起别名 表名 as 别名 # 给表起了别名以后，想要使用表必须使用别名，使用原表名会报错 语法：select 字段1,字段2...... from 表名 as 别名; 案例：select name,age from stu as s; 案例：select s.name,s.age from stu as s; # e.查看所有性别（实现去重） distinct 字段 语法：>>> select distinct 字段1 from 表名; 案例：select distinct gender from stu;
1.2 条件查询
# 条件 where 条件 # > 查询大于18岁的信息 案例：select * from stu where age > 18; # < 查询小于18岁的信息 案例：select * from stu where age < 18; # = 查询年龄等于18岁的信息 案例：select * from stu where age = 18; # >= 查询大于等于18岁的信息 案例：select * from stu where age >= 18; # <= 查询小于等于18岁的信息 案例：select * from stu where age <= 18; # !=或者<> 查询不等于18岁的信息 案例：select * from stu where age != 18; 案例：select * from stu where age <> 18;
1.3 逻辑运算符 and or not
# and 查询18-28之间的信息 select * from stu where age >= 18 and age <= 28; # 查询18岁以上的女性 select * from stu where age > 18 and gender = '女'; select * from stu where age > 18 and gender = 2; # or 18岁以上或者身高超过180的 select * from stu where age > 18 or height > 180; # not 不是 18岁以内的女性 select * from stu where not (age < 18 and gender = 2); # 年龄不是小于等于18的 并且是女性 select * from stu where not age <= 18 and gender = 2;
1.4 模糊查询 like
like %：匹配字符0次或无数次 _：匹配字符一次 # 查询姓名中以"小"开始的名字 select * from stu where name like '小%'; # 查询姓名中有"小"的名字 select * from stu where name like '%小%'; # 查询两个字的名字 select * from stu where name like '__'; # 查询有三个字的名字 select * from stu where name like '___'; # 查询至少有2个字的名字 select * from stu where name like '__%'; # 查询以杰结尾的名字 select * from stu where name like '%杰';
1.5 正则查询 rlike
rlike .：匹配除\n以外的任意一个字符 ^：以......开头 *：0次，无数次 ？：0次，无数次 +：1次，无数次 # 查询以刘开始的 select * from stu where name rlike '^刘.'; # 查询以刘开始的 以华结束的 select * from stu where name rlike '^刘.华$';
1.6 范围查询
in：在不连续 的区间内 not in : 不在区间内 between a and b:在a到b范围内 not between and b:不在a-b范围内 # 查询年龄为18、34的信息 select * from stu where age = 18 ot age = 34; select * from stu where age in (18,34); # 查询年龄不是18、34的信息 select * from stu where age not in (18,34); select * from stu where not age in (18,34); # 查询年龄在18-34之间的信息 select * from stu where age >= 18 and age <= 34; select * from stu where age between 18 and 34; # 查询年龄不在18-34之间的信息 select * from stu where age not betwwn 18 and 24;
1.7 空判断
is null insert into stu value(0,'张三',19,null,'男',1); # 查询身高为空的信息 select * from stu where height is null;
1.8 非空判断
is not null # 查询身高不为空的信息 select * from stu where height is not null;
2.排序
排序 order by
order by 字段 排序方式（升序/降序） 升序：asc（不指定排序方式时，默认升序） 降序：desc # 查询年龄在18-34岁之间的男性 select * from stu where age between 18 and 34 and gender = 1; # 查询18-34岁之间的男性并且身高按照由低到高排序 select * from stu where age between 18 and 34 and gender = 1 order by height asc; select * from stu where age between 18 and 34 and gender = 1 order by height; # 查询年龄在18-34之间的女性，身高从小到大排序，如果身高相同的情况下按照id由高到低排序 select * from stu where age between 18 and 34 and gender = 2 order by height,id desc; # 查询年龄在18-34之间的女性，身高从高到低排序，如果身高相同的情况下按照年龄从小到大排序，如果年龄相同按照id从大到小排序 select * from stu where age between 18 and 34 and gender = 2 order by height desc,age,id desc; # 年龄从小到大 如果年龄相同按照身高由高到低排序 select * from stu order by age,height desc;
3.聚合函数
具体种类
# max() # 查询学生中最大的年龄 select max(age) from stu; # min() # 查询学生中最小的年龄 select min(age) from stu; # count() # 查询学生总数 select count(*) from stu; select count(id) from stu; # sum() # 查询学生年龄总和 select sum(age) from stu; # avg() # 查询所有学生年龄的平均值 select avg(age) from stu; select sum(age)/count(*) from stu; # round(小数,保留的位数)(四舍五入) # 计算平均身高保留2位小数 select round(avg(height),2) from stu; select round(avg(age),2) from stu; # 查找平均身高和所有人的名字 ---XXX # 平均身高只有一个值，姓名字段11个值，前后数量不对等 select avg(height),name from stu; XX
4.分组与过滤
分组 group by
# 每种性别、不同的性别-->按性别分 # group_concat():获取分组后的结果，不一定要与分组搭配使用 # 以性别分组，获取每种性别 select distinct gender from stu; select gender from stu group by gender; # 计算每种性别的人数 select gender,count(*) from stu group by gender; # 获取每种性别的人数以及都有谁，统计名字 select gender,count(*),group_concat(name) from stu group by gender; # 按照isdelete字段进行分组，分别统计人数和名称 select isdelete,count(*),group_concat(name) from stu group by isdelete; # 获取每种性别的名字和id都展示出来 select gender,group_concat(name,id) from stu group by gender; # 计算男性的人数 select count(*) from stu where gender = '男'; # 统计男性的人数和姓名 select count(*),group_concat(name) from stu where gender = '男'; # 每种性别的平均年龄、平均身高 select gender,avg(age),avg(height) from stu group by gender;
过滤 having
xxx的性别---按照性别分组,按照条件过滤 # 查询平均年龄超过30岁的性别及这种性别中所有人的姓名 select gender,avg(age),group_concat(name) from stu group by gender having avg(age) > 30; # 查询性别人数多2个的性别及人数 select gender,count(*) from stu group by gender having count(*) > 2;
5.分页
分页 limit
limit num :展示表中前 num 条数据 # 展示前5条数据 select * from stu limit 5; # 显示前三条性别为男的信息 ---> limit 应该放在 where 后 select * from stu where gender = '男' limit 3; --------------------------------------------------- limit start,num start:从哪一条数据开始 --->(页数-1) * 每一页的数据条数 num:取几条数据（每一页存放的数据条数） # select * from stu limit 0,3; # select * from stu limit 3,3; # select * from stu limit 6,3; # 11条数据 每一页要求存放3条数据,获取第三页的数据 select * from stu limit 6,3; # 每一页存放2条数据，获取第四页的数据 select * from stu limit 6,2; # 每一页存放3条数据，获取第三页的数据，数据按照身高降序排序 select * from stu order by height desc limit 6,3 ;
6.关键字排序
关键字执行
select 字段/数据：要查询的数据 from 表名：想要从哪一张表中查询数据 where 条件：按照对应的条件获取数据 order by 字段 排序方式：查询到的结果要按照哪一个字段的值进行升降序排序 group by 字段 ：将数据进行分组 having 条件：对分组后的结果进行过滤 limit start,num：对获取到的结果进行分页查询 #关键字执行顺序 from -->where --> group by-->having--->select-->order by-->limit #编写顺序 select 字段 from 表名 where 条件 group by 分组 having 过滤 order by 排序 limit 分页
5.21
7.连接查询
7.1 概念
当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
7.2 分类
内连接
普通内连接
自关联
外连接
左外连接
右外连接
7.3 案例
一张班级表 create table class( id tinyint primary key auto_increment, name varchar(5) unique ); insert into class value (0,"A班"), (0,"B班"), (0,"C班"); 一张学生表 create table student( id tinyint primary key auto_increment, name varchar(5) unique, age tinyint, gender enum("男","女","保密") default "保密", cls_id tinyint ); insert into student value (0,"张三",18,"男",1), (0,"李四",16,"女",1), (0,"王五",20,"男",1), (0,"赵六",23,"男",2), (0,"田七",17,"女",2), (0,"胡八",30,"男",5);
7.4 普通内连接
语法：
select * from 表1 inner join 表2 on 连接条件 特点：如果连接表中没有与之对应的数据，则该条数据不展示
案例：
1. 将班级表与学生表内连接--》将班级表作为表1-->班级表中数据在左侧展示 >>> select * from class inner join student on cls_id = class.id; 2. 将班级表与学生表内连接--》将学生表作为表1-->学生表中数据在左侧展示 >>> select * from student inner join class on cls_id = class.id; 3. 获取所有的学生姓名及对应的班级名称，将数据按照年龄从大到小进行排序 >>> select c.name,s.name from class as c inner join student as s on cls_id = c.id order by age desc; 4. 查询人数大于2人的班级，班级名称、该班级中学生的数量及学生名称 >>> select c.name,count(*),group_concat(s.name) from student as s inner join class as c on cls_id = c.id group by c.name having count(*) > 2; 5. 查询所有有学生的班级，获取班级名即可 >>> select distinct class.name from class inner join student on cls_id = class.id;
7.5 左外连接
语法：
select * from 表1 left join 表2 on 连接条件 哪张表作为表1，哪张表就是主表 主表中所有的数据在左侧展示，如果从表中没有与之对应的数据则用null填充 从表中没有对应的数据则不展示
案例：
1. 将班级表与学生表左外连接--》将学生表作为表1 >>> select * from student as s left join class as c on cls_id = c.id; 2. 将班级表与学生表左外连接--》将班级表作为表1 >>> select * from class as c left join student as s on cls_id = c.id; 3. 获取所有的学生姓名及对应的班级名称，将数据按照年龄从大到小进行排序 >>> select s.name,c.name from student as s left join class as c on cls_id = c.id order by age desc; 4. 查询没有班级的学生 >>> select * from student as s left join class as c on cls_id = c.id where c.name is null; 5. 查询有学生的班级 >>> select * from class as c left join student as s on cls_id = c.id where cls_id is not null;
7.6 右外连接
语法：
select * from 表1 right join 表2 on 连接条件; 哪张表作为表2，哪张表就是主表 主表中所有的数据在右侧展示，如果从表中没有与之对应的数据则用null填充 从表中没有对应的数据则不展示
案例：
1. 将班级表与学生表右外连接--》将学生表作为表1 >>> select * from student right join class on cls_id = class.id; 2. 将班级表与学生表右外连接--》将班级表作为表1 >>> select * from class right join student on cls_id = class.id;
7.7 自关联
案例：
省份表 id name 1 河南省 2 河北省 3 山东省 市级表 id name pid 1 郑州市 1 2 洛阳市 1 3 石家庄市 2 4 青岛市 3 县级表 id name pid 1 二七区 1 2 金水区 1 3 桥东区 3 4 崂山区 4 将三张表合为一张 id name pid 1 河南省 null 2 河北省 null 3 山东省 null 4 郑州市 1 5 洛阳市 1 6 石家庄 2 7 青岛市 3 8 二七区 4 9 金水区 4 10 桥东区 6 11 崂山区 7 create table area( id tinyint primary key auto_increment, name varchar(5) unique, pid tinyint ); insert into area value (0,"河南省",null), (0,"河北省",null), (0,"山东省",null), (0,"郑州市",1), (0,"洛阳市",1), (0,"石家庄",2), (0,"青岛市",3), (0,"二七区",4), (0,"金水区",4), (0,"桥东区",6), (0,"崂山区",7);
语法：
自关联本质就是内连接 select * from 表1 inner join 表2 on 连接条件;
案例：
1. 实现自关联 # 自己关联自己，表必须起别名 >>> select * from area as a1 inner join area as a2 on a1.id = a2.pid; 2. 查询所有的省 >>> select * from area where pid is null; 3. 查询河南省下面的市 >>> select * from area as a1 inner join area as a2 on a1.id = a2.pid where a1.name = "河南省"; >>> select a1.name,group_concat(a2.name) from area as a1 inner join area as a2 on a1.id = a2.pid group by a1.name having a1.name = "河南省";
8.子查询
8.1 概念
在一个select语句中嵌套另外一个select语句 被嵌套的select语句被称之为子查询语句 起嵌套作用select语句被称之为主查询语句
8.2 分类
标量子查询：子查询的结果是一个具体的数据 >>> select max(age) from stu; 56 行子查询：子查询的结果是一行数据/多个数据 >>> select * from stu where name = "张三"; >>> select avg(height),avg(age),avg(money) from stu; 列子查询：子查询的结果是一列数据 >>> select id from stu;
8.3 案例
# 聚合函数不能作为where条件使用，可以having中直接使用 1. 查询年龄最大的这个人的信息 1>. 按照年龄从大到小排序，取第一个值-->仅限于最大值只有一个 >>> select * from stu order by age desc limit 1; 2>. 使用子查询语句 第一步：查询表中最大的年龄 >>> select max(age) from stu; 第二步：查询该年龄对应的学生的信息 >>> select * from stu where age = 59; 综上所述：>>> select * from stu where age = (select max(age) from stu); 2. 查询身高高于平均身高的人的信息 >>> select * from stu where height > (select avg(height) from stu);
8.4 子查询中关键字的使用
算术运算符
> < = >= <= != # 查询年龄低于平均年龄的人的信息 >>> select * from stu where age < (select avg(age) from stu);
in、not in
# 查询有学生的班级 1>. 内连接 >>> select distinct c.name from class as c inner join student as s on cls_id = c.id; 2>. 外连接 >>> select distinct c.name from class as c left join student as s on cls_id = c.id where cls_id is not null; 3>. 子查询 第一步：从学生表中获取cls_id select cls_id from student; 第二步：从班级中查询哪个id再查询到的cls_id中 select name from class where id in (1,1,1,2,2,5); >>> select name from class where id in (select cls_id from student);
any、some
some是any别名 any:任意一个 # 查询身高高于 (未成年的人)的身高 任意一个的人的信息 1. 获取未成年的人的身高 >>> select height from stu where age < 18; 2. 查询身高高于这一部分身高的人的信息 >>> select * from stu where height > (180,170); 3. 子查询 >>> select * from stu where height > any(select height from stu where age < 18);
all
all：全部 # 查询身高高于 (未成年的人)的身高 全部的人的信息 1. 获取未成年的人的身高 >>> select height from stu where age < 18; 2. 查询身高高于这一部分身高的人的信息 >>> select * from stu where height > (180,170); 3. 子查询 >>> select * from stu where height > all(select height from stu where age < 18);
exists
exists：存在 如果子查询语句有结果，则执行主查询语句 如果子查询语句没有结果，则不执行主查询语句 子查询：select * from stu where height > 190; 主查询：select * from stu; >>> select * from stu where exists (select * from stu where height > 190);
9.索引
9.1 概念
定义：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
目的：加快查询速度，提高查询效率
原理：通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件
9.2 分类
1.普通索引 index 2.唯一索引 unique primary key --> 唯一 + 非空 3.联合索引 unique(id,name) 4.全文索引 fulltext 5.空间索引 spatical 了解
9.3 创建索引
9.3.1 在创建表时直接创建索引
# 如果想要给字符串类型的数据加索引，要求字段后需要带长度 # 特点：索引名与字段名一致 语法：>>> create table 表名( 字段 类型 约束, 字段 类型 约束, 索引类型(字段) ); 案例：>>> create table aa( id int, name varchar(10), index(name(10)) ); 案例：>>> create table aa( id int, name varchar(10), unique(id) );
9.3.2 给已经创建好的表增加索引
# 特点：自定义索引名 语法：>>> create 索引类型 index 索引名 on 表名(字段); 案例：>>> create index a1 on aa(id); 案例：>>> create unique index a1 on aa(name(10));
9.3.3 给已经创建好的表增加索引
# 特点：索引名与字段名一致 语法：>>> alter table 表名 add 索引类型(字段); 案例：>>> alter table aa add index(id); 案例：>>> alter table aa add unique(name(10));
9.4 查询表中索引
语法：>>> show index from 表名; 案例：>>> show index from aa; 注意：出现Empty，说明当前表中没有索引。出现Key_name就是索引名字 +-------+------------+----------+ | Table | Non_unique | Key_name | +-------+------------+----------+ | aa | 1 | name | +-------+------------+----------+
9.5 删除索引
语法：>>> drop index 索引名称 on 表名; 案例：>>> drop index name on aa;
9.6 索引实战
9.6.1 pymysql往表中增加一万条数据
import pymysql # pymysql实现往表中增加数据 # 1.连接数据库 con = pymysql.connect(host="localhost",port=3306,user="root",password="123456",database="test01",charset="utf8") # 2.获取游标对象 c1 = con.cursor() for i in range(1,10001): # 3.编写sql语句 sql = f"""insert into aa value({i},'value-{i}')""" # 4.游标对象执行sql语句 c1.execute(sql) # 5.提交事务 con.commit() # 6.关闭 con.close()
9.6.2查询sql执行时间
初始：表中没有索引 # 第一步：开启时间检测 >>> set profiling=1; # 第二步：通过id查询第10000条数据（没有索引的查询） >>> select * from aa where id=10000; # 第三步：给表中id字段增加索引 >>> alter table aa add index(id); # 第四步：通过id查询第10000条数据（有索引的查询） >>> select * from aa where id=10000; # 第五步：查询所有的执行时间 >>> show profiles;
