
语句查询
# 查询指定字段
# 只查询name和age两个字段语法:>select 字段名1,字段名2 from 表名;案例:>select name,age from stu;案例:>select stu.name,stu. age from stu;# 起别名
# 将字段名改成中文名 仅对当前一条语句起作用,不会改变原数据库语法:>select 字段名 as 别名,字段名 as 别名 from 表名;案例:>select name as 姓名,age as 年龄 from stu;# 给表起别名
语法:>select 字段 from 表名 as 别名;案例:>select s.name,s.age from stu as s;案例:>select name,age from stu as s; # 查看所有性别(实现去重)语法:>select distinct 性别 from 表名;案例:>select distinct gender from stu;条件查询
# > 查询大于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.00;
# not 查询不是18岁以上的女性select * from stu where not (age>18 and gender=2);
# 年龄不是小于等于18的并且是女性select * from stu where not age<=18 and gender =2;几种常见查询
1).等值查询:select * from stu where id=1;查询id为1的学生的所有字段信息
2).条件比较查询:select * from stu where id > 3;查询id大于3的所有学生的所有字段信息
3).比较运算与逻辑运算查询:select * from stu where id > 5 and height >170;查询id大于5并且身高大于170的所有学生的信息模糊查询 like
--like
%可以匹配0个或无数个
# 查询姓名中以"小"开始的名字select * from stu where name like"小%";
# 查询姓名中有"小"的名字select * from stu where name like"%小%";
# 查询两个字的名字 _匹配1个select * from stu where name like"__";
# 查询有三个字的名字select * from stu where name like"___";
# 查询至少有2个字的名字select * from stu where name like"__%";正则查询
# 查询以周开始的select * from stu where name rlike"^周.*$";
# 查询以周开始的 以伦结束的select * from stu where name rlike"^周.*伦$";范围查询
in 在……里 查询不连续的数据
not in 不在……里
between a and b 查找a到b之间的范围 包含a和b
not between a and b 查找不在a到b之间的范围# 查询年龄为18、34之间的信息select * from stu where age in(18,34);
# 查询年龄不在18、34之间的信息select * from stu where age not in(18,34);
# 查询年龄在18-34之间的信息select * from stu where age between 18 and 34;
# 查询年龄不在18-34之间的信息select * from stu where age not between 18 and 34;空判断
# 查询身高为空的信息select * from stu where height is null;非空判断
# 查询身高不为空的信息select * from stu where height is not null;常用查询(二)
4).between...and的使用select * from stu where height between 170 and 180;# 注意: between a and b: 包含a和b查询身高在170都180之间的所有学生信息
5).成员运算:select * from stu where id in (1, 2, 3, 4);查询id值为1, 2, 3, 4的所有学生信息
6).空与非空判断:select * from stu where height is null;查询身高数据为空的所有学生信息select * from stu where height is not null; 查询身高数据不为空的所有学生信息排序 order by
order by 不写默认升序
asc 从小到大即升序
desc 从大到小即降序使用多个字段来进行排序,只需要用逗号隔开,不需要写多个order by# 查询年龄在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=2 order by age desc;# 查询年龄在18-34之间的女性,身高从高到低排序,如果身高相同的情况下按照年龄从小到大排序select * from stu where (age between 18 and 34) and gender=2 order by height desc,age asc;
# 查询年龄在18-34之间的女性,身高从高到低排序,如果身高相同的情况下按照年龄从小到大排序,如果年龄相同按照id从大到小排序select * from stu where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;
# 年龄从小到大 身高从大到小排序select * from stu order by age asc,height desc;聚合函数的使用
# max()
# 查询学生中最大的年龄select max(age) from stu; # min()
# 查询学生中最小的年龄select min(age) from stu;# count()
# 查询学生总数select count(*) from stu;# sum()
# 查询学生年龄总和select sum(age) from stu;# avg()
# 查询所有学生年龄的平均值select avg(age) from stu;# round(小数,保留位数)
# 保留位数写0就是没有小数 写1就是1为小数,写2就是保留两位小数
# 计算平均身高保留2位小数select round(avg(height),2) from stu;# 查找平均身高和所有人的名字select name,round(avg(height),2) from stu;
# 报错:因为平均身高只有一个但是姓名有很多个,无法匹配
# 来引出分组分组与过滤
分组 group by
分组就是和聚合函数一起用,否则没有任何意义# 以性别分组select gender from stu group by gender;
# 计算每种性别的人数select count(*),gender from stu group by gender;
# # 获取每种性别都有谁,统计名字select gender,group_concat(name) from stu group by gender;
# 获取每种性别的名字和id都展示出来select gender,group_concat(id,name) from stu group by gender;select gender,group_concat(id,"-",age,"-",name) from stu group by gender;
# 计算男性的人数select count(*) from stu where gender=1;
# 统计男性的人数和姓名select gender,count(*),group_concat(name) from stu where gender=1;
# 每种性别的平均年龄select gender,avg(age) from stu group by gender;group_concat():将分组的结果连接在一起,但不是一定要跟分组一起用过滤 having
对分组的结果进行过滤 所以用在group by 后面# 查询每种性别的平均年龄select gender,avg(age) from stu group by gender;
# 查询平均年龄 查询超过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
# limit num 代表展示num条数据select * from stu limit 5;# 展示前三条性别为男的信息select * from stu where gender=1 limit 3;# limit start,count
# start代表起始值 count代表每一页要展示的数据个数select * from stu limit 0,5; # id 从1-5select * from stu limit 5,5; # id 从6-10select * from stu limit 10,5; # id 从11-15# 起始值的规律(n-1)*每页的个数第一页 (1-1)*5=0第二页 (2-1)*5=5第三页 (3-1)*5=10# 每一页展示3个数据 查询第三页的所有的数据 按照年龄从小到大排序
(3-1)*3=6select * from stu order by age asc limit 6,3;# 关键字之间是有优先级的存在 order by > limit 关键字排序select:查找数据from:从哪一张表里获取数据where:查找数据时需要的条件# 不能改变的关键字select from where order by group by having limit# 关键字执行顺序from > where > group by > having > select > order by >limitfrom:找到表where:选择条件group by:分组having:对分组后的结果进行筛选select:获取数据order by:对数据进行排序limit:先排序 在进行最后一次的取值# 写sql语句的顺序select * from 表名 where 条件 order by 排序 group by 分组 having 筛选 limit 分页## 连接查询连接查询概念:当查询的字段来源于多张表时我们使用的就是连接查询分类:内连接外连接 左外连接 右外连接交叉连接## 内连接概念内连接查询出两张表中交叉重合的数据,共有的部分表1的字段放在左边语法:select 字段 from 表1 inner join 表名2 on 连接条件;连接条件:两张表产生关系的字段案例:让学生表和班级表进行内连接查询的所有的字段select * from student inner join class on student.cls_id=class.id;select * from class inner join student on class.id = student.cls_id;查询指定字段数据案例:如果两张表里有相同的字段 ,查询的时候需要标明清楚具体是哪一张表里的字段 表名.字段名select name,cls_name from student inner join class on student.cls_id=class.id;给表起别名 表名 as 别名如果起了别名就要用到别名select a.name,b.cls_name from student as a inner join class as b on a.cls_id = b.id;案例:查询班级名称 学生id 学生姓名 学生年龄 学生性别,按照从大到小排序,如果年纪相同按照id从小到大排序;注意:多表联查不影响分组 排序 过滤 聚合函数 分页 关键字的使用select a.id,a.name,age,gender,b.cls_name from student as a inner join class as b on a.cls_id = b.id order by age desc,id;案例:查询不同性别的人数,姓名以及对应的班级名称select gender,count(*),group_concat(a.name,"-",b.cls_name) from student as a inner join class as b on +a.cls_id=b.id group by gender;## 自关联概念一张表 自己关联自己案例insert into area values(1,"河南省",null),-> (2,"山东省",null),-> (3,"山西省",null),-> (4,"郑州市",1),-> (5,"洛阳市",1),-> (6,"菏泽市",2),-> (7,"运城市",3),-> (8,"中牟县",4),-> (9,"汝阳县",5),-> (10,"曹县",6);先找河南的idselect id from area where name="河南省";再用河南的id 匹配 下面市的pidselect * from area where pid=1;自关联 本质就是内连接 一定要用到别名select * from area as a inner join area as b on a.id = b.pid where a.name="河南省";## 外连接 左外连接作用:left join on把表1作为主表 如果关联表没有对应的信息就用null填充语法:select 字段 名 from 表1 left join 表2 on 连接条件;把学生表当作表1 学生表里的数据都存在 如果班级表没有对应值 会用null填充案例:select * from student left join class on student.cls_id=class.id;把班级表当作表1 班级表的数据都存在 如果学生表中没有对应的值 会用null填充案例:select * from class left join student on class.id=student.cls_id; 右外连接作用right join on表2当作主表 如果关联表没有对应的信息就用null填充语法案例:语法:select 字段 from 表1 right join 表2 on 连接条件;表2是主表 表1不对应的数据用null对应案例:select * from student right join class on student.cls_id =class.id;select * from class right join student on class.id=student.cls_id;## 外键约束 作用:外键约束:对外键字段的值进行更新和插入数据时会引用表中字段的数据进行验证,如果数据不合法则更新或插入失败,保证数据的有效性外键约束在哪一张表里,哪一张表就是从表因为有外键约束,导致数据不能随便插入,要按照规则添加 添加外键方式:在创建表的时直接添加外键约束语法:foreign key(字段名) references 表名(字段名)给已经创建好的表增加外键约束语法:alter table 表名 add foreign key(字段名) references 表名(字段名)案例:alter table b add foreign key(a_id) references a(id);注意:如果表中已经存在不合法数据那么就会添加外键失败被约束和约束字段的类型要保持一致否则报3780错误前面被约束后面是谁来进行约束,约束谁就在谁那里添加外键约束 删除外键找到表中的外键查看创建表结构:语法:show create table 表名;案例:show create tabl e b;找到系统随机生 成外键字段名(b_ibfk_1)双引号里的删除语法:语法:alter table 表名 drop foreign key 字段名;案例:alter table b drop foreign key b_ibfk_1;## 子查询概念在一个select语句中在嵌入另一个select语句select * from student;标量子查询:返回的结果是一个数据(一行一列)列子查询:返回的结果是一列(一列多行)行子查询:返回的结果是一行(一行多列)查询班级身高最高的信息select * from stu where height=(select max(height) from stu);查询年龄最大的信息select * from stu where age=(select max(age) from stu);#查询所有有学生的班级 显示班级名称和学生姓名
select cls_name,name from class inner join student on class.id=student.cls_id;#子查询 也能用其他关键字 in #找到有学生的班级班级的名字和学生的名字
select cls_name from class where id in (select cls_id from student);select name from student where cls_id in (select id from class); some、any 作用一样 任一查询年龄大于 身高大于175的人的年龄 的人的信息#身高大于175 的人的年龄select age from student where height>175;#报错 more than 1#需要给予查询语句增加一个any条件查询出 年龄比(身高大于175的人的年龄) 中 任意一个大的都可以select * from student where age>any(select age from student where height>175);select * from student where age>some(select age from student where height>175); all 所有#查询年龄比 身高大于175的人的年龄 中所有年龄的人的信息
select age from student where height>175;
select * from student where age>all(select age from student where height>175);#查询 身高大于 (年龄小于30岁的身高) 中所有人的身高才行
select * from student where height>all(select height from student where age<30); exists只要子查询语句有结果 就会执行主查询语句如果子查询语句没有结果 就不会执行主查询语句
#只要表中有身高有高于185的人,那就执行查询出表中所有人的信息
select * from stu where exists(select * from stu where height>185);#年龄大于50岁的人的信息 查找出来是所有人的信息
select name,age from stu where exists(select * from stu where age>50);## 索引概念是一种特殊文件,它包含着对数据库中所有记录的引用指针,好比一本书的目录,加快查询速度索引原理以及作用原理:通过不断缩小范围来筛选结果作用:加快查询速度 索引分类mysql常见索引普通索引 index唯一索引unique indexprimary key联合索引unique(id,name)全文索引fulltext ---用于搜索一篇很长的文章空间索引spatial 了解即可 索引创建如果给字符串指定长度,这时候如果索引创建在这个字符串上,索引也需要加长度第一种方式 在创建表时直接创建索引(索引名跟字段名一样)语法:create table 表名(字段名 类型 [约束],索引(字段()))案例:create table test_01(title varchar(20),unique(title(10)));第二种方式 在已经创建好的表中创建索引(可以自己指定索引名)语法:create 索引 index 索引名 on 表名(字段名())案例:create unique index t2 on test_01(title(10));创建普通索引案例:create index t1 on test_01(title(10));第三种方式 在已经创建好的表中创建索引(索引名跟字段名一样)语法:alter table 表名 add 索引(字段());案例:alter table test_01 add unique(title(10));altere table test_01 add index(title(10)); 查询索引语法:show index from 表名;案例:show index from test_01;key name 对应的值就是索引名 删除索引语法:drop index 索引名 on 表名;案例:drop index title on test_01; 百万数据实战开启时间检测set profiling=1;查看执行时间show profiles;## 封装 思路目的:封装一个模块功能:可以快捷的操作mysql中的功能分析:确定单独的方法---(想要实现的功能)确定公共的内容---(连接数据库、创建游标对象)更进一步初始化pymysql使用之前1. 先连接mysql,获取连接对象(数据库对象)2. 通过连接对象,获取游标对象具体的方法中如何执行1. 直接执行2. 先执行,再提交 (对数据进行增删改操作的时候,不仅仅是游标对象执行sql语句,连接对象还得向上提交才行)