深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥

创建一个名叫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);

# 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;

# 条件   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;

# 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;

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 '%杰';

rlike

.：匹配除\n以外的任意一个字符
^：以......开头

*：0次，无数次
？：0次，无数次
+：1次，无数次
# 查询以刘开始的
select * from stu where name rlike '^刘.';

# 查询以刘开始的 以华结束的
select * from stu where name rlike '^刘.华$';

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;

is null

insert into stu value(0,'张三',19,null,'男',1);
# 查询身高为空的信息
select * from stu where height is null;

is not null

# 查询身高不为空的信息
select * from stu where height is not null;

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;

# 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

# 每种性别、不同的性别-->按性别分
# 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;

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;

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 ;

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 分页

一张班级表
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);

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;

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;

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;

省份表
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 = "河南省";

在一个select语句中嵌套另外一个select语句
被嵌套的select语句被称之为子查询语句
起嵌套作用select语句被称之为主查询语句

标量子查询：子查询的结果是一个具体的数据
  >>> select max(age) from stu;      56
行子查询：子查询的结果是一行数据/多个数据
  >>> select * from stu where name = "张三";
  >>> select avg(height),avg(age),avg(money) from stu;
列子查询：子查询的结果是一列数据
  >>> select id from stu;

# 聚合函数不能作为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);

>   <   =   >=    <=   !=

# 查询年龄低于平均年龄的人的信息
>>> select * from stu where age < (select avg(age) from stu);

# 查询有学生的班级
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);

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：全部
# 查询身高高于  (未成年的人)的身高   全部的人的信息
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：存在
如果子查询语句有结果，则执行主查询语句
如果子查询语句没有结果，则不执行主查询语句

子查询：select * from stu where height > 190;
主查询：select * from stu;
>>> select * from stu where exists (select * from stu where height > 190);

1.普通索引
  index
2.唯一索引
  unique
  primary key --> 唯一 + 非空
3.联合索引
  unique(id,name)
4.全文索引
  fulltext
5.空间索引
  spatical 了解

# 如果想要给字符串类型的数据加索引，要求字段后需要带长度
# 特点：索引名与字段名一致

语法：>>> create table 表名(
  字段 类型 约束,
  字段 类型 约束,
  索引类型(字段)
);

案例：>>> create table aa(
  id int,
  name varchar(10),
  index(name(10))
);

案例：>>> create table aa(
  id int,
  name varchar(10),
  unique(id)
);

# 特点：自定义索引名

语法：>>> create 索引类型 index 索引名 on 表名(字段);
案例：>>> create index a1 on aa(id);
案例：>>> create unique index a1 on aa(name(10));

# 特点：索引名与字段名一致

语法：>>> alter table 表名 add 索引类型(字段);
案例：>>> alter table aa add index(id);
案例：>>> alter table aa add unique(name(10));

语法：>>> show index from 表名;
案例：>>> show index from aa;

注意：出现Empty，说明当前表中没有索引。出现Key_name就是索引名字
+-------+------------+----------+
| Table | Non_unique | Key_name |
+-------+------------+----------+
| aa    |          1 | name     | 
+-------+------------+----------+

语法：>>> drop index 索引名称 on 表名;
案例：>>> drop index name on aa;

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()

初始：表中没有索引

# 第一步：开启时间检测
>>> 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;