民治制作网站,wordpress 主题 标签,wordpress 煎蛋主题,遵义网站建设网站系列文章目录 更新ing...
MySQL操作全攻略#xff1a;库、表、数据、事务全面指南深入探索MySQL SELECT查询#xff1a;从基础到高级#xff0c;解锁数据宝藏的密钥MySQL SELECT查询实战#xff1a;练习题精选#xff0c;提升你的数据库查询技能PyMySQL#xff1a;连接P…
系列文章目录 更新ing...
MySQL操作全攻略库、表、数据、事务全面指南深入探索MySQL SELECT查询从基础到高级解锁数据宝藏的密钥MySQL SELECT查询实战练习题精选提升你的数据库查询技能PyMySQL连接Python与MySQL的桥梁… 文章目录 系列文章目录前言0.准备一张表先1.简单语句查询1.1 基本查询1.2 条件查询1.3 逻辑运算符 and or not1.4 模糊查询 like1.5 正则查询 rlike1.6 范围查询1.7 空判断1.8 非空判断 2.排序3.聚合函数4.分组与过滤5.分页6.关键字排序7.连接查询7.1 概念7.2 分类7.3 案例7.4 普通内连接语法案例 7.5 左外连接语法案例 7.6 右外连接语法案例 7.7 自关联案例语法案例 8.子查询8.1 概念8.2 分类8.3 案例8.4 子查询中关键字的使用算术运算符in、not inany、someallexists 9.索引9.1 概念9.2 分类9.3 创建索引9.3.1 在创建表时直接创建索引9.3.2 给已经创建好的表增加索引9.3.3 给已经创建好的表增加索引 9.4 查询表中索引9.5 删除索引9.6 索引实战9.6.1 pymysql往表中增加一万条数据9.6.2查询sql执行时间 前言 在数据驱动的世界中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 nullinsert 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; XX4.分组与过滤 分组 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. 将班级表与学生表左外连接--》将学生表作为表1select * from student as s left join class as c on cls_id c.id;2. 将班级表与学生表左外连接--》将班级表作为表1select * 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. 将班级表与学生表右外连接--》将学生表作为表1select * from student right join class on cls_id class.id;2. 将班级表与学生表右外连接--》将班级表作为表1select * 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 崂山区 7create 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.唯一索引uniqueprimary 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(hostlocalhost,port3306,userroot,password123456,databasetest01,charsetutf8)# 2.获取游标对象
c1 con.cursor()for i in range(1,10001):# 3.编写sql语句sql finsert into aa value({i},value-{i})# 4.游标对象执行sql语句c1.execute(sql)# 5.提交事务
con.commit()# 6.关闭
con.close()9.6.2查询sql执行时间
初始表中没有索引# 第一步开启时间检测set profiling1;# 第二步通过id查询第10000条数据没有索引的查询select * from aa where id10000;# 第三步给表中id字段增加索引alter table aa add index(id);# 第四步通过id查询第10000条数据有索引的查询select * from aa where id10000;# 第五步查询所有的执行时间show profiles;
