MYSQL（2）
Day04笔记
一、外键(foreign key)
1、定义 ：让当前表的字段值在另一张表的范围内去选择
2、语法格式
create table 表名(
字段名 数据类型,
constraint 外键名 [外键名这可省略不写]
foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
3、使用规则
1、主表的被参考字段、从表的参考字段数据类型要一致
2、主表 ：被参考字段是主键（主表字段必须为KEY的一种）
4、示例
1、表1：缴费信息表(财务) :jftab
id 姓名 班级 缴费金额
1 唐伯虎 AID07 300
2 点秋香 AID07 300
表2：学生信息表(班主任) :bjtab
stu_id 姓名 缴费金额
2 点秋香 300
2、创建表
表1 ：jftabk
create table jftab(
id int primary key,
name varchar(20) not null,
class char(5),
money smallint
)character set utf8;
insert into jftab values
(1,"唐伯虎","AID07",300),
(2,"点秋香","AID07",300),
(3,"文征明","AID07",300);
表2 ：bjtab
create table bjtab(
stu_id int,
name varchar(20),
money smallint,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
)character set utf8;
3、删除外键(从表)
alter table 表名 drop foreign key 外键名;
外键名查看 ：show create table 表名;（CONSTRAINT后面的名字就是外键名）
4、已有表添加外键
alter table 表名 add
foreign key(字段) references 主表(被参考字段名)
on delete 级联动作
on update 级联动作;
注意：在已有表添加外键时，会受到表中原有数据的限制。
5、级联动作
1、cascade
数据级联删除,级联更新，即删除、更新同步(参考字段)
从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。
2、restrict(默认)
从表中有相关联记录,不允许主表操作。拒绝删除或者更新父表。
3、set null
主表删除、更新,从表相关联记录字段值为NULL
从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。
二、嵌套查询(子查询)
1、定义 ：把内层的查询结果作为外层的查询条件
2、语法
select ... from 表名 where 字段名 运算符 (select ... from 表名 where 条件);
3、练习
1、把攻击值小于平均攻击值的名字和攻击值显示出来
1、先求平均值
select avg(gongji) from sanguo;
2、找结果
select name,gongji from sanguo where gongji<值;
select name,gongji from sanguo where
gongji<(select avg(gongji) from sanguo);
2、找出每个国家攻击力最高的英雄的名字和攻击值
# 有问题语句
select name,gongji from sanguo
where
gongji in(select max(gongji) from sanguo group by country);
# 无问题语句
select name,gongji from sanguo
where
(country,gongji) in(select country,max(gongji) from sanguo group by country);
三、多表查询
1、两种方式
1、笛卡尔积 （不常用）：不加where条件
select ... from 表1,表2;
2、多表查询，只显示匹配到的：加where条件
select ... from 表1,表2 where 条件;
3、笛卡尔积的匹配方式
1、记录多的表的每一条记录,去匹配另一张表的所有记录
2、2张表记录条数相同,则后表的每一条记录去匹配前表的所有记录
2、练习
1、显示 省、市详细信息
河北省 石家庄市 河北省 廊坊市
广东省 广州市 广东省 深圳市
select sheng.s_name,city.c_name from sheng,city
where
sheng.s_id=city.cfather_id;
2、显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from
sheng,city,xian
where
sheng.s_id=city.cfather_id and city.c_id=xian.xfather_id;
四、连接查询
1、内连接(inner join)：只显示匹配到的记录
1、语法格式
select 字段名列表 from 表1
inner join 表2 on 条件
inner join 表3 on 条件;
2、练习
1、显示省、市详细信息(只显示匹配到的)
select sheng.s_name,city.c_name from sheng
inner join city
on sheng.s_id=city.cfather_id;
2、显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng
inner join city on sheng.s_id=city.cfather_id
inner join xian on city.c_id=xian.xfather_id;
2、外连接
1、左连接(left join)
1、以 左表为主 显示查询结果
2、select 字段名列表 from 表1
left join 表2 on 条件
left join 表3 on 条件;
3、练习
1、显示省、市详细信息,要求省全部显示
select sheng.s_name,city.c_name from sheng
left join city on sheng.s_id=city.cfather_id;
2、显示省市县详细信息,要求省全部显示
select sheng.s_name,city.c_name,xian.x_name from sheng
left join city on sheng.s_id=city.cfather_id
left join xian on city.c_id=xian.xfather_id;
2、右连接(right join)
以右表为主显示查询结果,用法同左连接
五、锁
1、目的 ：解决客户端并发访问的冲突问题
2、锁分类
1、锁类型
1、读锁(共享锁)
查询(select):加读锁之后,别人不能更改表记录,但可以进行查询
2、写锁(互斥锁、排他锁)
更新(update):加写锁之后别人不能查、不能改
2、锁粒度
1、行级锁 ：Innodb
select ：加读锁,锁1行
update ：加写锁,锁1行
2、表级锁 ：MyISAM
select ：加读锁,锁1张表
update ：加写锁,锁1张表
六、存储引擎(engine ：处理表的处理器)
1、基本操作命令
1、查看所有存储引擎
show engines;
2、查看已有表的存储引擎
show create table 表名;
3、创建表指定存储引擎
create table 表名(...)engine=myisam,charset=utf8;
4、已有表（不常用，会受到表中原有数据的限制。）
alter table 表名 engine=myisam;
2、常用存储引擎的特点
1、InnoDB特点
1、支持事务、外键、行级锁
2、共享表空间
表名.frm ：表结构和索引信息
表名.ibd : 表记录
2、MyISAM特点
1、支持表级锁
2、独享表空间
表名.frm ：表结构
表名.MYD ：表记录 （mydata）
表名.MYI ：索引信息 （myindex）
3、Memory存储引擎（不常用）
1、数据存储在内存中,速度快（表结构存储在硬盘，表记录存储在内存）
2、服务器重启、MySQL服务重启后表记录消失
3、如何决定使用哪个存储引擎
1、查询操作多的表使用MyISAM(使用InnoDB浪费资源)
2、写操作多的表使用InnoDB
七、MySQL用户账户管理（授权）
1、开启MySQL远程连接(改配置文件)
1、sudo -i
2、cd /etc/mysql/mysql.conf.d/
3、cp mysqld.cnf mysqld.cnf.bak2
4、vi mysqld.cnf
#bind-address=127.0.0.1
把前面 # 去掉,保存退出
vi mysqld.cnf
按a -> 改内容 -> 按ESC -> 按 shift + : -> wq
5、/etc/init.d/mysql restart
2、用root用户添加授权用户
1、用root用户登录mysql
mysql -uroot -p123456
2、授权
grant 权限列表 on 库名.表名
to "用户名"@"%" identified by "密码"
with grant option;
权限列表 ：all privileges、select、update
库名.表名 ：db4.* 、*.*(所有库的所有表)
3、示例
1、添加授权用户tiger,密码123,对所有库的所有表有所有权限,可从任何IP去连接
grant all privileges on *.*
to "tiger"@"%"
identified by "123"
with grant option;
2、添加授权用户rabbit,密码123,对db4库所有表只有查询权限,可从任何IP去连接
grant select on db4.*
to "rabbit"@"%" identified by "123"
with grant option;
====================================================================
Day05笔记
一、数据备份(mysqldump,在Linux终端操作)
1、命令格式
mysqldump -u用户名 -p 源库名 > ***.sql
2、源库名的表示方式
--all-databases 备份所有库
库名 备份1个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份多张表
3、练习
1、备份所有库,放到mydata目录下 ：All.sql
mkdir mydata #新建文件夹mydata
cd mydata #进入mydata
mysqldump -uroot -p --all-databases > All.sql
2、备份db4库中的 sheng、city、xian三张表,db4scx.sql
mysqldump -uroot -p db4 sheng city xian > db4scx.sql
3、备份MOSHOU和db4两个库,md.sql
mysqldump -uroot -p -B db4 MOSHOU > md.sql
二、数据恢复
1、命令格式(Linux终端)
mysql -u用户名 -p 目标库名 < ***.sql
2、从所有库备份All.sql中恢复某一个库
mysql -u用户名 -p --one-database 库名 < All.sql
例：mysql -uroot -p --one-database db4 < All.sql
3、示例
1、在db4.sheng添加一条记录
insert into sheng values(null,300000,"黑龙江");
2、在db4库,新建一张表t888
create table t888(id int);
3、从 db4.sql 恢复db4库
mysql -uroot -p db4 < db4.sql
注意：
1、恢复库时,如果恢复到原库会将表中数据覆盖,新增表不会删除
2、恢复库时,如果库不存在,则必须先创建空库
三、MySQL调优
1、创建索引
在select、where、order by常涉及到的字段建立索引
2、选择合适存储引擎
1、读操作多 ：MyISAM
2、写操作多 ：InnoDB
3、SQL语句优化(避免全表扫描)
1、where子句尽量不使用 != ,否则放弃索引全表扫描
2、尽量避免NULL判断,否则全表扫描
优化前:
select number from t1 where number is null;
优化后:
在number字段设置默认值0,确保number字段无NULL
select number from t1 where number=0;
3、尽量避免用or连接条件,否则全表扫描
优化前：
select id from t1 where id=10 or id=20;
优化后：(可以用union all拼接多个sql命令)
select id from t1 where id=10
union all
select id from t1 where id=20;
4、模糊查询尽量避免使用前置 %,否则全表扫描
select variable from t1 where name="secure%";
5、尽量避免使用in和not in,否则全表扫描
优化前 ：(连续的可以用and替换)
select id from t1 where id in(1,2,3,4);
优化后 ：
select id from t1 where id between 1 and 4;
6、不能使用 select * ...
用具体字段代替*,不要返回用不到的任何字段
四、事务和事务回滚
1、定义 ：一件事从开始发生到结束的整个过程
事务回滚：要么全都做，要么全不做。
2、作用 ：确保数据一致性
3、事务和事务回滚应用
1、SQL命令会 autocommit（自动提交） 到数据库执行
2、事务操作
1、开启事务
mysql> begin; | start transactions;
mysql> 多条SQL命令 ...
## 此时autocommit被禁用 ##
2、终止事务
mysql> commit;（成功提交） | rollback; （失败回滚）
3、案例
1、背景
你 ：建行卡
朋友 ：工行卡
你在建行自动取款机 给你朋友 转5000块钱
2、建表
表1、CCB
create table CCB(
name varchar(20),
money decimal(20,2)
);
insert into CCB values("有钱人",100000);
表2、ICBC
create table ICBC(
name varchar(20),
money decimal(20,2)
);
insert into ICBC values("没钱人",0);
3、开始转账
成功：
mysql> begin;
mysql> update CCB set money=money-5000 where name="有钱人";
mysql> update ICBC set money=money+5000 where name="没钱人";
mysql> commit;
#####转账成功#####
失败 ：
mysql> begin;
mysql> update CCB set money=money-5000 where name="有钱人";
mysql> update ICBC set 工行服务器故障... ;
mysql> rollback;
#####转账失败#####
五、与Python交互
1、交互类型
1、python3
模块名 ：pymysql
安装 ：
1、在线 ：sudo pip3 install pymysql
2、离线 ：pymysql.tar.gz
解压：setup.py
python3 setup.py install
2、python2
模块名 ：MySQLdb
安装 ：sudo pip install mysql-python
2、pymysql使用流程
1、建立数据库连接对象(db=pymysql.connect("root"....))
2、创建游标对象cur(操作数据库的对象) cur = db.cursor()
3、游标对象:cur.execute("insert into sheng ...;")
4、提交到数据库执行 ：db.commit()
5、关闭游标对象cur.close()
6、关闭数据库连接对象db.close()
示例 ：01_pymysql示例.py
import pymysql # 1.创建数据库连接对象 db = pymysql.connect(host="localhost",\ user="root",password="123456",\ database="db4",charset="utf8") # 2.利用 db 创建游标对象 cursor = db.cursor() # 3.利用cursor的execute()方法执行SQL命令 sql_insert = "insert into sheng values(30,400000,'吉林省');" cursor.execute(sql_insert) # 4.提交到数据库执行 db.commit() print("ok") # 5.关闭游标对象 cursor.close() # 6.断开数据库连接 db.close()
在默认情况下cursor方法返回的是BaseCursor类型对象，BaseCursor类型对象在执行查询后每条记录的结果以列表(list)表示。如果要返回字典(dict)表示的记录，就要设置cursorclass参数为MySQLdb.cursors.DictCursor类。
cur = conn.cursor(cursorclass=pymysql.cursors.DictCursor)
Python-MySQLdb中的DictCursor使用方法简介 pymysql模块的使用
3、connect连接对象
1、db = pymysql.connect(参数列表)
1、host ：主机地址
2、user ：用户名
3、password ：密码
4、database ：库
5、charset ：编码方式,推荐utf8
6、port ：端口(3306)
2、db (数据库连接对象) 的方法
1、db.close() ：断开连接
2、db.commit() ：提交到数据库执行
3、db.cursor() ：游标对象,用来执行SQL命令
4、db.rollback() ：回滚
import pymysql db = pymysql.connect(host="localhost", user="root",password="123456", database="db4",charset="utf8") cursor = db.cursor() try: # 1.在sheng表中插入1条记录 sql_insert = 'insert into sheng(s_name) values("湖北省");' cursor.execute(sql_insert) # 2.在sheng表中删除id为8的记录 sql_delete = 'delete from sheng where id=8;' cursor.execute(sql_delete) # 3.在sheng表中把id为1的记录的省名改为 浙江省 sql_update = 'update sheng set s_name="浙江省" where id=1;' cursor.execute(sql_update) # 都执行成功再提交 db.commit() print("ok") except Exception as e: db.rollback() print("Failed",e) cursor.close() db.close()
3、cursor 游标对象 的方法
1、execute(SQL命令,[列表]) ：执行SQL命令，列表用于传参。使用方法见 04_SQL语句参数化.py
2、close() ：关闭游标对象
3、fetchone() ：获取查询结果的第1条数据
4、fetchmany(n) ：获取n条数据
5、fetchall() ：获取所有数据
## fetchmany(n) 和 fetchall() 得到的结果一定是一个大元组套着小元组 ((),(),())
游标类型 描述
Cursor 普通的游标对象，默认创建的游标对象
SSCursor 不缓存游标，主要用于当操作需要返回大量数据的时候
DictCursor 以字典的形式返回操作结果
SSDictCursor 不缓存游标，将结果以字典的形式进行返回
class pymysql.cursors.Cursor(connection)#游标结果作为元祖的元祖返回 说明： 这是您用于与数据库交互的对象。 不要自己创建Cursor实例。调用connections.Connection.cursor() class pymysql.cursors.SSCursor(connection)#无缓冲游标结果作为元祖的元祖返回， 用途： 用于返回大量数据查询，或慢速网络连接到远程服务器 不将每行数据复制到缓冲区，根据需要获取行。客户端内存使用少 在慢速网络上或结果集非常大时行返回速度快 限制： MySQL协议不支持返回总行数，判断有多少行唯一方法是迭代返回的每一行。 目前无法向后滚动，因为只有当前行保存在内存中。 class pymysql.cursors.DictCursor(connection) #将结果作为字典返回游标 class pymysql.cursors.SSDictCursor(connection)#无缓冲游标结果作为字典返回 ———————————————— 原文链接：https://blog.csdn.net/tcy23456/article/details/86665211
from pymysql import connect # 创建数据库连接对象 db = connect(host="localhost",user="root", password="123456",database="db4", charset="utf8",port=3306) # 创建游标对象 cursor = db.cursor() # 执行查询语句 sql_select = "select * from sheng;" cursor.execute(sql_select) # 所有的查询结果放到了游标对象 cursor 中 # fetchone()得到的为1个元组，结果为第一条 data1 = cursor.fetchone() print(data1) print("*" * 50) data2 = cursor.fetchmany(3) ##得到第二条开始到第四条数据 print(data2) print("*" * 50) data3 = cursor.fetchall() ## 得到从第五条到剩余的数据 print(data3) cursor.close() db.close()
4、sql语句参数化
import pymysql db = pymysql.connect(host="localhost", user="root",password="123456", database="db4",charset="utf8") cursor = db.cursor() try: s_name = input("请输入省份:") s_id = input("请输入该省编号:") sql_insert = 'insert into sheng(s_name,s_id) \ values(%s,%s);' L = [s_name,s_id] cursor.execute(sql_insert,L) #此处必须用列表传参 db.commit() print("ok") except Exception as e: db.rollback() print("Failed",e) cursor.close() db.close()
5、用类封装方法
封装成类
from pymysql import connect class MysqlHelp: def __init__(self,database,host="localhost", user="root",password="123456", charset="utf8",port=3306): self.database = database self.host = host self.user = user self.password = password self.charset = charset self.port = port # 连接数据方法 def open(self): # 创建数据库连接对象conn self.conn = connect(host=self.host, user=self.user,password=self.password, database=self.database, charset=self.charset, port=self.port) # 创建游标cur self.cur = self.conn.cursor() # 关闭 def close(self): self.cur.close() self.conn.close() # 执行SQL语句 def workOn(self,sql,L=[]): self.open() try: self.cur.execute(sql,L) self.conn.commit() print("ok") except Exception as e: self.conn.rollback() print("Failed",e) self.close() # getAll查询方法 def getAll(self,sql,L=[]): self.open() self.cur.execute(sql,L) # print("ok") result = self.cur.fetchall() self.close() return result if __name__ == "__main__": # 测试 mysql = MysqlHelp("db4") # 插入测试 # sql_insert = "insert into sheng(s_name) values('河北省');" # mysql.workOn(sql_insert) # 查询测试 # sql_select = "select * from sheng;" # result = mysql.getAll(sql_select) # print(result)
测试查询
from MysqlPython import MysqlHelp mysql = MysqlHelp("db4") sql_select = "select * from sheng;" result = mysql.getAll(sql_select) print(result)
6、用户登录程序
from MysqlPython import MysqlHelp # 加密模块 from hashlib import sha1 username = input("请输入用户名:") password = input("请输入密码:") # 给password加密 s1 = sha1() # 创建sha1加密对象 s1.update(password.encode("utf-8")) # 转码 password2 = s1.hexdigest() # 返回十六进制加密结果 # 和数据库中表记录进行匹配 mysql = MysqlHelp("db4") sql_select = 'select password from user where \ username=%s' result = mysql.getAll(sql_select,[username]) # 正确 # result : (('7c4a8d09ca3762af61e59520943dc26494f8941b',),) # 用户名错误 # result： () if len(result) == 0: print("用户名不存在") elif result[0][0] == password2: print("登录成功") else: print("密码错误")
六、图形化界面管理工具
WorkBench : Linux中图形化界面管理工具
Navicat : Windows中图形化界面管理工具
七、orm(Object Relation Mapping) 对象关系映射
1、示例
import sqlalchemy
class User:
__tablename__ = "t1"
id = Column(Integer,primary_key=True)
name = Column(String(20))
解释：
一个类 User --> 数据库1张表
表中2个字段 ：id 和 name
exists：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
truncate：当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.
posted on 2018-09-27 17:03 破天荒的谎言、谈敷衍 阅读(311) 评论(0) 收藏 举报
浙公网安备 33010602011771号