内容回顾
- 多表查询
- 联表查
- 内连接 左右两表中能连上的行才被保留
- 表1 inner join 表2 on 表1.字段1=表2.字段2
- 外连接
- 左外连接 表1中所有的项都会被保留,而表2中只有匹配上表1的项才会被保留
- 表1 left join 表2 on 表1.字段1=表2.字段2
- 右外连接 刚好和左外连接相反right join
- 子查询
- select 字段 from 表 where 字段 = (select 子句)
- select 字段 from 表 where 字段 > (select 子句)
- select 字段 from 表 where 字段 < (select 子句)
- select 字段 from 表 where 字段 in (select子句查出的结果可以是多个)
修改的问题 :
对于条件非常多或者非常复杂的修改操作,都应该先根据条件查,再修改
- '三年二班'的所有同学姓名
select cid from class where caption = '三年二班';
select sname from student where class_id = (select cid from class where caption = '三年二班');
- 男生人数和女生人数
select gender,count(*) from student group by gender;
- 生物最高分
select cid from course where cname = '生物'
select max(number) from score where couse_id = (select cid from course where cname = '生物');
- 体育课平均分
select cid from course where cname = '体育'
select avg(number) from score where couse_id = (select cid from course where cname = '体育');
- 既选体育 又选物理的人名
找到体育的course_id
select cid from course where cname = '体育'
* 找到物理的course_id
select cid from course where cname = '物理'
* 找到选择体育的人,也在找物理的人名中
select student_id from score where course_id = (select cid from course where cname = '体育');
select student_id from score where course_id = (select cid from course where cname = '物理');
select student_id from (
select student_id from score where course_id = (select cid from course where cname = '体育')) as t1
inner join
(select student_id from score where course_id = (select cid from course where cname = '物理')) as t2
on t1.student_id = t2.student_id)
select * from studnet where sid in (
select student_id from (
select student_id from score where course_id = (select cid from course where cname = '体育')) as t1
inner join
(select student_id from score where course_id = (select cid from course where cname = '物理')) as t2
on t1.student_id = t2.student_id)
);
SELECT student.sname,GROUP_CONCAT(course.cname) FROM
course INNER JOIN score INNER JOIN student
ON course.cid=score.course_id AND student.sid=score.student_id
WHERE course.cname IN ('体育', '物理')
GROUP BY student.sname HAVING COUNT(course.cname)=2;
select s1.sname from student as s1
inner join
(select * from score as s inner join course as c on s.course_id = c.cid where c.cname in ('物理','体育')) as s2
on s1.sid = s2.student_id
group by s1.sname having count(s1.sid) = 2 ;
索引原理
b+树
- b 是balance 表示的是这个树最终是能够达到平衡的
- 数据不是平铺直叙的存储在硬盘山
- 影响查询速度的最重要的因素是树的高度
- 我们要做的事情,或者我们想要加速查询 降低树的高度
- 让索引的字段尽量的短
- 让索引的字段区分度高
- b+树和普通的b树比起来有什么区别
- 1. b+树只在叶子节点存数据
- 2. 在所有的叶子节点之间添加了双向链表
- mysql中 innodb myisam的索引都是由b+树完成的
- innodb 支持 聚集索引(叶子节点存具体的数据) + 辅助索引(叶子节点存地址)
- myisam 只支持辅助索引,也叫非聚集索引
创建索引
create index 索引名字 on 表名(字段名)
create index ind_id on s1(id);
今日内容
索引
- 认识mysql中的key
- index key 普通索引,能够加速查询,辅助索引
- unique key 唯一 + 索引,辅助索引
- primary key 唯一 + 非空 + 聚集索引
- 主键作为条件的查询如果能够让索引生效那么效率总是更高
- foreign key 本身没有索引的,但是它关联的外表中的字段是unique索引
- primary key 和unique 标识的字段不需要再添加索引
- 能用unique的时候尽量不用index
- unique除了是索引之外还能做唯一约束,如果做了唯一约束
- b+树就更健康
- 正确的使用索引
- 创建索引 creeat index 索引名 on 表名(字段名)
- 删除索引 drop index 索引名 on 表名
- 1.条件一定是建立了索引的字段,如果条件使用的字段根本就没有创建索引,那么索引不生效
- 2.如果条件是一个范围,随着范围的值逐渐增大,那么索引能发挥的作用也越小
- 3.如果使用like进行模糊查询,那么使用a%的形式能命中索引,%a形式不能命中索引
- 4.尽量选择区分度高的字段作为索引列
- 5.索引列不能在条件中参与计算,也不能使用函数
- 6.在多个条件以and相连的时候,会优点选择区分度高的索引列来进行查询
- 在多个条件以or相连的时候,就是从左到右依次判断
- 7.制作联合索引
- 1.最左前缀原则 a,b,c,d 条件是a的能命中索引,条件是a,b能命中索引,a,b,c能命中,a,c.... 只要没有a就不能命中索引
- 如果在联合查询中,总是涉及到同一个字段,那么就在建立联合索引的时候将这个字段放在最左侧
- 2.联合索引 如果按照定义顺序,从左到右遇到的第一个在条件中以范围为条件的字段,索引失效
- drop index
- 如果我们查询的条件总是多个列合在一起查,那么就建立联合索引
- create index ind_mix on s1(id,email)
- select * from s1 where id = 1000000 命中索引
- select * from s1 where email = 'eva1000000@oldboy' 未命中索引
- 但凡是创建了联合索引,那么在查询的时候,再创建顺序中从左到右的第一列必须出现在条件中
- select count(*) from s1 where id = 1000000 and email = 'eva10%'; 命中索引
- select count(*) from s1 where id = 1000000 and email like 'eva10%'; 可以命中索引
- 范围 :
- select * from s1 where id >3000 and email = 'eva300000@oldboy'; 不能命中索引
- 8.条件中涉及的字段的值必须和定义表中字段的数据类型一致,否则不能命中索引
- 关于索引的两个名词
- 覆盖索引 查一个数据不需要回表
- select name from 表 where age = 20 不是覆盖索引
- select age from 表 where age =20 是覆盖索引
- select count(age) from 表 where age =20 是覆盖索引
- 合并索引
- 当我们为单独的一列创建索引的时候
- 当我们为两列分别创建单独的索引的时候
- 如果这两列都是条件,那么可能只能命中期中一个条件
- 如果这两列都是条件,那么可能会命中两个索引 - 合并索引
- 我们为多列直接创建联合所以
- 执行计划
- 看看mysql准备怎么执行这条语句 可以看到是否命中索引,计划能命中哪些,实际命中了哪些,执行的顺序,是否发生了索引合并,覆盖索引
- explain select * from s1;
##数据库部分的拾遗
- pymysql
- 数据库备份和还原
- 使用事务和锁的方式来保证数据安全
Pymysql模块
import pymysql
查
conn = pymysql.connect(
host='127.0.0.1', user='root', password="123",
database='homework',
)
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
cur.execute('select * from class')
ret1 = cur.fetchone() * cur记录着当前取到多少条了
print(ret1)
ret1 = cur.fetchone() * 不占用内存
print(ret1)
ret1 = cur.fetchone()
print(ret1)
ret= cur.fetchmany(8) * 分页
print(ret)
ret= cur.fetchall() * 因为占用内存所以不常用
print(ret)
cur.close()
conn.close()
conn = pymysql.connect(
host='127.0.0.1', user='root', password="123",
database='homework',
)
cur = conn.cursor()
cur.execute('insert into class (caption) values("六年三班")')
conn.commit()
cur.close()
conn.close()
conn = pymysql.connect(
host='127.0.0.1', user='root', password="123",
database='homework',
)
cur = conn.cursor()
cur.execute('update class set caption="四年三班" where cid=4')
conn.commit()
cur.close()
conn.close()
conn = pymysql.connect(
host='127.0.0.1', user='root', password="123",
database='homework',
)
cur = conn.cursor()
cur.execute('delete from class where cid=4')
conn.commit()
cur.close()
conn.close()