第六章数据库-优快云博客

第六章数据库(Mysql)

　　1. 基本概念

　　　　很多功能如果只是通过文件操作来改变数据是非常繁琐的，对于多台机器或者多个进程操作一份数据会存在安全和并发的问题

　　　　数据库是一种C/S架构的操作数据文件的一个管理软件能够解决并发问题，更加简单快速的完成数据的增删改查，拥有容错和高可用机制

　　　　数据库管理系统(DBMS)专门用来管理数据文件，帮助用户更简洁的操作数据的软件

　　2. 数据库的类型

　　　　关系型数据库：关联性强，一个部分信息可以获取相关全部信息　　　　如：mysql、sql server(收费但是严谨安全性高)、oracle(开源)

　　　　非关系型数据：类似根据字典中的key和value来运行，可通过key来找calue，不能通过value找key　　如：redis、mongodb

　　3. 数据库的操作

　　　　概念：DDL数据库定义语言(创建库、创建表)　　　　

　　　　　　　DML数据库操作语言(存数据、删数据、改数据、查数据)

　　　　　　　DCL数据库控制权限

　　　　3.1 mysql对用户操作

select user()    --查看当前用户
set password = password('xxx')    --修改密码
create user'用户名'@'IP字段'identified by'密码'；    --创建一个用户属于哪个IP段
show grants for ‘用户’@‘IP段’;    --查看当前用户有哪些权限
grant all on *.* to '用户'@'IP段';    --所有的(all)权限所有的库(*)所有的表(.*)给那个用户的IP段
flush privileges;    --给完权限刷新后生效
grant all on *.* to '用户'@'%'identified by '密码'    --授权并创建用户，同样刷新后生效

　　　　3.2 mysql库操作

create database 数据库名;    --创建库
show databases;    --查看当前有多少个数据库
select database();    --查看当前使用的数据库
use 数据库名;    --切换到这个数据库下
drop database 数据库名;    --删除库

　　　　3.3 mysql表操作

create table 表名字(字段1 字段1类型,字段2 字段2类型)    --创建库表
show tables;    --查案当前有多少表
rename 旧名字 to 新名字;    --更改表名字
drop table 表名字;    --删除表
desc 表名字;   show create table 表名字;     --查看表结构
show varables like '%需要查的东西%'    --查看配置项

　　　　3.4 表的存储方式

　　　　　　1. MyISAM 5.5以下默认存储方式：存储的文件个数有表结构、表中的数据、索引

　　　　　　2. InnoDB 5.6以上的默认存储方式：存储的文件个数有表结构、表中的数据

　　　　　　　　　　　　　　　　　　　　　　支持行级锁、表级锁：row-level locking

　　　　　　　　　　　　　　　　　　　　　　支持事务：transactions

　　　　　　　　　　　　　　　　　　　　　　支持外键：froeign key

　　　　　　3. MEMORY 内存：存储的文件个数：表结构、增删改查很快、但是重启数据消失、容量很小

　　　　3.5 表结构修改

alter table 表名 add(字段 数据类型 约束);    --添加表结构
alter table 表名 drop 字段名;    --删除字段
alter table 表名 modify 字段;    --修改已经存在的字段的宽度约束
alter table 表名 modify 字段1 after 字段2;    --把字段1位置改到字段2后面
alter table 表名 modify 字段1 first;    --把字段1放到最前面
alter table 表名 change 字段 新字段 类型 约束;    --修改已经存在的字段名称、类型和约束

　　　　3.6 mysql数据操作

insert into 表名(字段名,字段名) values(值1,值2)    --创建数据，所有的数据值必须和字段名一一对应
delete from 表 where 条件;    --删除数据
update 表 set 字段=新值 where 条件;    --修改数据
select * from 表名;    --查看表中所有的数据
select 字段1,字段2 from 表名;    --只查看表中的1和2字段
select distinct 字段1,字段2 from 表;    --按照查出来的字段去重复
select 字段*12 from 表;    --把字段的数值乘12输出，可加减乘除

　　4. 数据库的数据类型

　　　　4.1 字符串类型

　　　　　　char(字节数)：浪费空间，但存储效率相对高，长度变化小

　　　　　　varchar(字节数)：节省空间、存取效率相对低

　　　　　　例：create table t1(name char(5),job varchar(10));

　　　　4.2 数值类型

　　　　　　int(位数)：整型，位数表示只显示几位，不影响输入几位数

　　　　　　例：create table t2(id int(3),uid int (5) unsigned)　　注：unsigned表示插入的这个数据只能是正数

　　　　4.3 日期时间类型

　　　　　　year：年　　date：年月日　　time：时分秒　　datetime：年月日时分秒

　　　　4.4 enum和set类型

　　　　　　enum：只能在其中的选项总选择一个，选择未出现的选项不打印

　　　　　　set：可以选择选项中的任意多个，选择其中没有的不打印

　　　　　　例：create tabl t3(name char(12),gender ENUM('male','female'),hobby set('抽烟','喝酒','烫头','洗脚');

　　5. 约束

　　　　　　not null：设置一个字段不能为空　　create table t1(id int not null)

　　　　　　default：设置一个字段设置默认值　　create table t2(age int default 18)

　　　　　　unique：设置一个字段不能出现重复(唯一)　　create table t3(id int unique)

　　　　　　auto_increment：只能设置int字段自动增加　　create table t4(id int auto_increment)

　　　　　　primary key：设置一个字段为主键或联合主键(等同于非空且唯一)　　create table t5(id int primary key)

　　　　　　　　　　注：一张表只能设置一个主键，建议自己设置上

　　　　　　　联合主键：设置两个以上为联合主键，任意联合主键都不唯一，但联合起来必须唯一　　create table(id int,name char(5),primary key(id,name))

　　　　　　foreign key(字段) references 表2(字段)：设置一个字段为外键，外键涉及到两张表，一张表的晚间字段值为另一张表的字段值

--员工表
create table staff(
    id int primary key auto_increment,
    age int,
    post_id int,
    foreign key (post_id) references post(pid)
)

--部门表
create table post(
    pid int primary key,
    postname char(10) not null unique,
    phone_num char(11)
)
--外键总是设置在后出现的表中

　　　　　　级联删除和级联更新：当设置外键的时候，更新和删除数据无法在一张表内进行，这时需要级联才可以

　　　　　　　　　　　　　方法：在设置外键的后面加 on update cascade on delete cascade

　　6. 两张表中的数据关系

　　　　　　多对一：一张表中的多个数据对应另一张表中的一个数据，foreign key永远是在多的那一张表设置外键

　　　　　　一对一：一张表中的一个数据对应另一张表中的一个数据，在设置外键的字段后面设置unique两张表就为一对一

　　　　　　多对多：需要三张表，两张表之间对应的多对多关系在第三张表中存储

　　7. 单表查询

　　　　7.1 where语句

　　　　　　比较运算：>,<,=,>=,<=,!=　　　　如：select * from 表名 where 字段>1000;

　　　　　　范围运算：

　　　　　　　　　　　多选一：select * from 表 where 字段 in(条件,条件,条件)

　　　　　　　　　　模糊范围：

　　　　　　　　　　　　　　　在一个数值区间：between and　　如：select name from 表 where 字段 between 条件1 and 条件2

　　　　　　　　　　　　　　　字符串的模糊查询：like　　如：select * from 表 where 字段 like 'P%'(找到表中该字段以P开头的所有数据)

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 select * from 表 where 字段 like '%P'(找到表中该字段以P结尾的所有数据)

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 select * from 表 where 字段 like 'P_'(找到表中该字段以P开头一个字符串的，一个下划线表示一个字符长度)

　　　　　　　　　　　　　　　正则匹配：regexp　　如：select * from 表 where 字段 regexp 正则表达式

　　　　　　逻辑运算-条件拼接：与and　　或or　　非not

　　　　　　特殊：null 只能通过 is nul 和 is not null 来判断

　　　　7.2 分组聚合

　　　　　　分组group by：把group by后面的字段中的每一个不同的项都保留起来，并且把值这一项的所有行归为一组　　如：select * from 表 group by 字段1(查询表根据字段1分组)

　　　　　　聚合函数：把多行的同一字段进行一些统计，最终得到一个结果

　　　　　　　　　　　count(字段)：统计这哥字段有多少项

　　　　　　　　　　　sum(字段)：统计这个字段对应的数值的和

　　　　　　　　　　　avg(字段)：统计这个字段对应的数值的平均值

　　　　　　　　　　　min(字段)：统计这个字段对应的数值的最小值

　　　　　　　　　　　max(字段)：统计这个字段对应的数值的最大值

　　　　　　使用分组聚合：select count/sum/avg/min/max(字段) from 表名 group by 字段；

　　　　7.3 having语句

　　　　　　having：利用聚合函数过滤组　　如：select 字段 from 表 group by 字段 having avg(字段2)>10000(平均薪资大于1000的部门)

　　　　7.4 order by语句

　　　　　　order by：根据字段排序

select * from 表 order by 字段    --从小到大
select * from 表 order by 字段 desc    --从大到小
select * from 表 order by 字段1，字段2    --先根据字段1排序，在字段1中相同的根据字段2排序
selext * from 表 order by 字段 limit 5;    --输出字段的前5个
selext * from 表 order by 字段 limit 0/5/10,5;    --输出字段从0/5/10位置开始取5个值

　　　　7.5 语句顺序

　　　　　　select distinct⑤ 字段 from① 表　　where②　　group by③　　having④　　order by⑥　　limit⑦

　　8. pymysql模块

import pymysql
conn = pysql.connect(host='127.0.0.1',user='root',password='123',database='day40')
cur = conn.cursor()    #数据库的操作符，游标 
cur = conn.curson(pymysql.cursors.DictCursor)    #取出的为字典
cur.execute('select * from 表 where 条件')
ret = cur.fechone()    #拿一行
ret2 = cur.cur.fetchall()    #取出所有
ret3 = cur.fetchmany(x)    #取x条
print(ret,ret2,ret3)    #字典：print(ret['字段'])
conn.commit()    #提交才能到数据库中
conn.close()

#执行可能报错
try :
    cur.executu(sql)    #执行sql语句
    conn.commit()    #提交到数据库
except:
    conn.rollback()    #如果发生错误则回滚

　　9. 多表查询

　　　　select * from 表1，表2：两张表连在一起查，笛卡尔积式表

　　　　9.1 连表查询

　　　　　　inner join 内连接：两张表条件不匹配的项不会出现在结果中　　如：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;

　　　　　　left join 左外连接：只能显示全部的左表(表1)中的数据　　如：select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

　　　　　　right join 右外连接：只能显示全部的右表(表2)中的数据　　如：select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

　　　　9.2 子查询

　　　　　　先找到一张表中的某一字段对应的id：select id from 表1 where 字段 = '查询的'

　　　　　　在找到领一张表与id对应的其他信息：select * from 表2 where 字段 == '查询的'

　　　　　　结合成子查询：select * from 表2 where 字段 = (select id from 表1 where 字段 = '查询的')

--找出年龄大于25岁的员工及员工所在的部门名称
select 表1.name from 表1 where id in (select 字段 from 表2 group by 字段 having avg(age)>25);
--查看不足1人的部门名
select * from department where id not in(select dep_id from emp group by dep_id);
--查询大于所有人平均年龄的员工名与年龄
select * from emp where age >(selet avg(age) from emp);
--查询大于部门内平均年龄的员工名和年龄
select * from emp inner join(select dep_id,avg(age) avg_age from emp group by dep_id) as don emp.dep_id = d.dep_id where emp.age > d.avg_age;

View Code

　　10. 索引

　　　　10.1 概念

　　　　　　索引就是建立一个存储表阶段就有的一个存储结构能在查询的时候加速，主要提升查询速度

　　　　10.2 数据库的存储方式

　　　　　　新的数据结构：平衡树balance——b树，索引加速度，但写的速度变慢

　　　　　　　　　　　　　在b树的基础山改进行了改良——b+树，分支节点的根节点都不再存储实际的数据

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　让分支和根节点能存储更多的索引信息就降低了输的高度

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　所有的实际数据都存储在叶子节点中

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　在叶子节点之间加入了双向的链式结构，方便查询范围条件

　　　　10.3 聚集索引和辅助索引

　　　　　　聚集索引是将数据直接存储在树结构的叶子节点上，相对较快　　如：innodb中是聚集索引和辅助索引并存的

　　　　　　辅助索引时数据不直接存储在树中，找到的为数据的主键，通过再次主键查询得到数据，相对较慢　　如：myisam中只有辅助索引

　　　　10.4 索引的种类

　　　　　　primary key：只有主键是聚焦索引，并约束非空且唯一，可使用联合主键

　　　　　　unique：自带辅助索引，并约束唯一，可使用联合唯一

　　　　　　index：辅助索引，无约束，可根据某两个关键字做联合索引

　　　　10.5 创建、删除和查询

　　　　　　创建索引：create index 索引名字 on 表(字段)　　注：创建索引后再select查，则速度变快

　　　　　　删除索引：drop index 索引名 on 表(字段)　　注：不删除会占用资源

　　　　　　查询索引：show create table 表名　　注：查看表的字段和创建的索引还有表类型

　　　　10.6 联合索引

　　　　　　当a对abc条件进行索引时，不会对b，c进行单列的索引时会用　　例：create index 索引名 on 表(字段1，字段2)

　　　　　　在联合索引中如果使用or条件索引就不能生效

　　　　　　最左前缀原则：在联合索引中，条件必须含有在创建索引的时候的第一索引列

　　　　　　在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了

　　　　10.7 判断索引是否成立

　　　　　　要查询的数据的范围大：>,<, >=,<=,!=　　

　　　　　　　　　　　　　　　　　between and　　

　　　　　　　　　　　　　　　　　like(结果的范围大索引不生效，如果是abc%索引生效，但是%abc索引就不生效)

　　　　　　如果一些内容的区分度不高，索引页不生效

　　　　　　索引列不能再条件中参与计算：select * from s1 where id*10 = 1000000　　注：索引不成立

　　　　　　and：

　　　　　　　　and条件两端的内容，优先选择一个有索引的并且属性结构更好的来进行查询

　　　　　　　　两个条件都成立层能完成where条件，先完成范围小的缩小后面条件的压力

　　　　　　　　例：select * from s1 where id = 100000 and email = 'eva100000@oldboy';

　　　　　　or：or条件的不会进行优化，只是会根据条件从左到右依次筛选，条件中带有or的想要索引，这些条件中所有的列都是索引列

　　　　　　单列索引：选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符

　　　　　　　　　　　使用or来连接多个条件，在满足上述条件的基础上对or相关的所有列分别创建索引

　　　　　　其他：使用函数是索引不成立(select * from tb1 where reverse(email) = 'egon';)

　　　　　　　　　类型不一致索引不成立：如果列是字符串类型，传入条件时必须用引号引起来

　　　　　　　　　排序条件为索引，则select字段必须也是索引字段，否则无法命中

　　　　　　　　　　　如：select name from s1 order by email desc;　　注：不成立

　　　　　　　　　　　　　select email from s1 order by email desc;　　注：成立

　　　　　　　　　　　　　select * from tb1 order id desc;　　特殊：如果对主键排序，则还是成立

　　　　10.8 合并索引

　　　　　　对两个字段分别创建索引，由于sql的条件让两个索引同时生效，那么这个两个索引就成为了合并索引

　　　　10.9 执行计划

　　　　　　概念：如果想在执行sql之前就知道sql语句的执行情况那么就可以使用执行计划

　　　　　　　　　执行计划并不会执行sql语句，会给你列出一个执行计划如果执行计划符合自己的sql的查询则执行，否则就需要修改sql语句

　　　　　　使用：explain+sql索引语句

　　　　　　　　　关键字：possible_keys　　实际索引中可能会用到的索引

　　　　　　　　　　　　　key　　实际会用到的索引，如果一个都没有就是没有命中

　　　　　　　　　　　　　Extra　　如果现实一个Using index则这个语句就是覆盖索引

　　　　10.10 覆盖索引

　　　　　　概念：如果我们使用索引作为条件查询，查询完毕之后不需要回表查就是覆盖索引

　　　　　　判断：explain+索引条件查看Extra字段是否为Using index　　是则为覆盖索引

　　　　10.11 注意事项

　　　　　　避免使用select *　　可以用count(*)　　创建表时尽量使用char代替varchar　　表的字段顺序固定长度的字段优先

　　　　　　组合索引代替多个单列索引(由于mysql中只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引)

　　　　　　尽量使用短索引　　使用连接(join)代替子查询　　连表时注意条件类型需要一致

　　11. 数据库的其他操作

　　　　11.1 数据库和表的备份

--在cmd中
mysqldump -uroot -p123 bd1 > 路径+名字.sql    --登录mysqldump用户，把bd1备份到指定路径
mysqldump -uroot -p123 --database new_db > 路径+名字    --恢复数据

--在mysql中
sourcr 路径+文件名    --恢复数据库

　　　　11.2 事务和锁

begin：    --开启事务
select * from emp where id = 1 for update;    --查询id值，for update添加行锁
update 表 set 字段 = 新值 where id = 1;    --完成更新
commit;    --提交事务

　　　　11.3 sql注入

　　　　　　--表示注释掉--之后的sql语句，如果用户输入的用户名后带--输入数据库后会注释之后sql语句导致直接登录成功

　　　　　　如：select * from userinfo where name = 'dfds' or 1=1;-- and password = 'dsfsd'(因为1=1永久成立，所以可以跳过用户名和密码直接登陆)

　　　　　　解决--导致的跳过登录

import pymysql 
conn = pymysql.connect(host = '127.0.0.1',user = 'root',password =='123',database='day41')
cur conn.cursor()
username = input('>>>')
password = input('>>>')
sql = "select * from userinfo where name = %s and password = %s"    #把字符串格式化交给cur语句做
cur.execute(sql,(username,password))    #把需要传入的内容用元组的形式传入sql语句中可以避免sql注释问题
print(cur.fetchone())
cur.close()
conn.close()