数据库索引备份外键和pymysql
1、数据控制
1）事务
事务把一组操作看成是一个整体，要么都操作成功，要么就都操作失败
数据库的引擎：引擎是驱动数据库系统工作的核心，MySQL数据库常见的引擎有，myisam,innodb,archive,ndb,memory等
表的数据引擎操作是innodb，innodb可以支持事务，myisam不支持事务
修改表的引擎：alter table 表名 engine=innodb;
提交：
mysql数据库默认的提交事务是自动提交，我们写的sql语句一旦执行产生的数据就会直接被提交数据库保存，如果不是自动提交产生的数据首先会在内存的缓存区域保存，然后我们手动的提交以后才能把数据存入数据库表。
查询是否是自动提交：
select @@autocommit; #如果为0则是手动 为1则是自动
设置自动提交方式
set autocommit=0 ;# 为0关闭自动变成手动，为1则开启自动
需要自己输入 commit;
回滚：在提交手动的情况下，撤回缓存中的数据。
在内存的缓存区域操作，我们执行了sql语句产生的数据首先存入内存的缓存区，我们可以通过回滚将本次操作产生的数据从缓存中撤回（在提交之前，如果已经提交则无法撤回）
rollback;
2）授权管理
创建用户
#创建一个用户
create user '用户名'@'服务器地址' identified by '密码';
#删除用户
drop user '用户名'@'服务器地址';
修改密码
# 修改当前用户密码
set password=password('123456');
# 修改其他用户密码
set password for '用户名'@'服务器' = password('123456');
#刷新
flush privileges;
授权
grant 权限 on 数据库.表 to '用户名'@'服务器'
grant select on jiaowudb.student to 'bobo'@'%';
grant all on *.* to 'bobo'@'%';
注意： %代表服务器为本地，all代表所有权限,*.*代表所有的表
权限包括：select,insert,update,alter,drop等
回收权限
在本地服务器上回收用户老王的查找权限
revoke select on testdb.test from ‘laowang’@’%’;
2、外键
1）外键设置
如果表A的主关键字是表B的一个字段，则称该字段为表B的外键，此时表A为主表，表B为表A的从表。
要设置外键：
1）数据库表的引擎必须是innodb
2）主表和从表相关的外键字段类型必须兼容，最好一致
3）外键的取值：要么为空，要么从主表的主键中取
# 添加外键
ALTER TABLE 表名 #此表名是从表的名，外键名称：fk_从表名_主表名_字段名
ADD CONSTRAINT 外键名称 FOREIGN KEY (从表的外键列字段) REFERENCES 主表名 (主键列字段)
[ON DELETE reference_option]
[ON UPDATE reference_option]；
reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION
# 删除外键
ALTER TABLE 从表 DROP FOREIGN KEY 外键名;
1）CASCADE:从父表中删除或者更新对应的行，同时自动会删除或者更新子表中匹配的行
2）NO ACTION：Innodb拒绝删除或者更新付表
3）SET NULL：从父表中删除或者更新对应的行，同时会把子表中的对应行的外键置空注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
4）RESTRICT：拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
2）关系表的对应
1）一对一
表A中的每一条记录在表B中有且只有一条和它对应
表A可以是表B主表，表B也可以是表A的主表。
2）一对多
表A中每一条记录在表B中可以有多条记录和它对应
表A作为主表，表B作为表A从表
3）多对多
表A中的每一条记录在表B中有多条和它对应，表B中的每一条记录在表A中也可以有多条和它对应
多对多的关系需要一个关系表C来体现
C既做A的从表，又做B的从表，联系AB
案例：金庸武侠中有三个实体，人物、技能、派别；
1）创建三张表
create table renwu(rid int primary key auto_increment,name varchar(10),sex enum('男','女'),level varchar(10));
create table jineng(jid int primary key auto_increment,name varchar(10),level int,cd int);
create table paibie(pid int primary key auto_increment,name varchar(10),address varchar(100),kouhao varchar(100));
2）确立关系
画E-R图，再建立关系
rewu和派别是多对一，人物和技能是多对多
人物和派别的关系，是一对多：
分析：人物是多，派别是一，即派别是人物的主表，人物是从表
alter table renwu add paibie_id int; #
alter table renwu add constraint paibie_id foreign key (paibie_id) references paibie (pid);
人物和技能的关系，多对多：
多对多关系需要先创建一个关系表：
create table renji(id int primary key auto_increment,renwu_id int,jineng_id int,score float);
然后确立renji表示人物和技能的从表
alter table renji add constraint renwu_id foreign key (renwu_id) references renwu (rid);
alter table renji add constraint jineng_id foreign key (jineng_id) references jineng (jid);
注意：一对一关系：A和B只需要把A的主键设置为B外键即可
3、索引视图
1）索引
索引就相当于图书的目录，可以加速查询，一般在where，order by,group by, having后的字段建立索引。
索引的优点：
加速检索速度，唯一索引保证数据的唯一性，降低分组、排序的时间,可以使用查询优化器提高系统性能
索引的缺点：
-建立索引会建立对应索引文件，占用大量空间
-建立索引会降低增、删、改的效率
不建立索引的情况：
-频繁更新的字段不建议建索引，没有出现在where、having不建议建索引，数据量少的表没有必要建索引，唯一性比较差的字段不要建索引
索引的分类：
普通索引
create index 索引名 on 表名(字段 asc/desc); # 默认是asc 升序 #索引名格式 index_字段名
唯一索引
# 在唯一索引所在的列不能有重复值，增加和修改会受影响
create unique index 索引名 on 表名(字段 asc/desc);
主键索引
创建表，主键索引会自动添加，要求在主键上不能有重复值和空值
复合索引(联合索引) 索引了多个列
create index 索引名 on 表名（字段 asc/desc) 默认asc升序
索引名index_字段名1_字段名2
使用联合索引 ，必须包含左前缀
a
a,b a,c
a,b,c
全文索引
一般会用全文索引服务器，不会直接创建索引
create FULLTEXT index 索引名 on 表名（字段 asc/desc）
删除索引
drop index 索引名 on 表;
查看索引(分析索引，sql语句执行效率)
show index from 表;
用explain关键字来查看是否使用了索引
explain select * from jineng where name = '乾坤大挪移';
+----+-------------+--------+------------+-------+--------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+--------------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | jineng | NULL | const | name_2,j_name,name | name_2 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+--------------------+--------+---------+-------+------+----------+-------+
explain select * from jineng where level=80;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | jineng | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
type:
all 代表全表扫描
index 使用索引
range 在指定范围内查找
const 常量查询
其他创建索引的方式
alter table 表名 add index(字段1,字段2,…);
alter table 表名 add primary key(字段1,字段2,…);
alter table 表名 add unique(字段1,字段2,…);
alter table 表名 add fulltext(字段1,字段2,…);
不使用索引的情况
-
- 查询时的联合索引没有左前缀，不使用索引 - or条件里，如果一方字段没有索引，则不使用索引 - 类型不对应的不会使用索引 - like '%tom' ,如果左边是通配符，不会使用索引 - 使用!=、<>、not in操作，不使用索引
2）视图
有时候经常会遇到复杂的查询，写起来比较麻烦，这个时候我们可以使用视图来简化查询。视图就是固化sql语句，可以不把视图看做基本表使用
创建视图
create view 视图名(字段列表) as select语句;
使用视图
select * from 视图名 where 条件
删除视图
drop view 视图名;
查询视图的创建语句
show create view 视图名;
4、数据库的备份与恢复
备份
不用登录MySQL，直接执行mysqldump指令，将数据库备份到指定的家目录下或者文件夹下
mysqldump -uroot -p 数据库名 > ~/备份目录/备份文件名.sql;
备份所有数据库：
mysqldump –u用户名 –p –h 主机名 --all-databases > 备份文件名.sql
恢复
首先要创建一个数据库，然后退出mysql，执行恢复指令
mysql -uroot -p 数据库名 < ~/备份目录/备份文件名.sql;
图形化界面：sudo apt install -y mysql -workbench
5、pymysql
-
安装pymysql
pip install pymysql (在pycharm terminal)
-
连接数据库
import pymysql link=pymysql.Connect(host='182.92.7.134',port=3306,user='min',password='123',database='yu1',charset='utf8') print(link) 参数说明： host – 数据库服务器所在的主机,公网ip或域名本地 localhost。 user – 登录用户名。 password – 登录用户密码。 database – 连接的数据库。 port – 数据库开放的端口。（默认: 3306） charset – 连接字符集。 返回值： 返回连接对象- 连接对象方法
方法 说明 begin() 开启事务 commit() 提交事务 cursor(cursor=None) 创建一个游标用来执行sql语句 rollback() 回滚事务 close() 关闭连接 select_db(db) 选择数据库 -
创建游标
cursor = link.cursor() print(cursor.rowcount) #打印受影响行数方法 说明 close() 关闭游标 execute(query, args=None) 执行单条语句，传入需要执行的语句，是string类型；同时可以给查询传入参数，参数可以是tuple、list或dict。执行完成后，会返回执行语句的影响行数。 fetchone() 取一条数据 fetchmany(n) 取多条数据 fetchall() 取所有数据 -
执行sql语句
# 执行sql语句 sql = 'select * from student(表名)' # 执行完sql语句，返回受影响的行数 num = cursor.execute(sql) -
获取结果集
result1 = cursor.fetchone() print(result1) -
关闭连接
cursor.close() link.close()
-
注意：
写完代码后，需要将py文件添加可执行权限
sudo chmod +x xxx.py ./xxx.py
pymysql事务处理
pymysql默认是没有开启自动提交事务，所以我们如果进行增、删、改，就必须手动提交或回滚事务。
sql = 'delete from student where 条件
# 如果要执行增删改语句的时候，下面的就是固定格式
try:
cursor.execute(sql)
# 如果全部执行成功，提交事务
link.commit()
print(cursor.lastrowid) #获取最后插入记录的自增id号
except Exception as e:
print(e)
link.rollback()
finally:
cursor.close()
link.close()
