1、存储引擎(处理表的处理器)
1、基本操作
1、查看所有存储引擎
mysql> show engines;
2、查看已有表的存储引擎
mysql> show create table 表名;
3、创建表指定存储引擎
create table 表名(...)engine=myisam;
4、已有表修改存储引擎
alter table 表名 engine=innodb;
2、锁
解决客户端并发访问的冲突问题
2、锁分类
1、锁类型
1、读锁(共享锁)
select :加读锁之后别人不能更改表记录,但可以进行查询
2、写锁(互斥锁、排他锁)
insert、delete、update
加写锁之后别人不能查、不能改
2、锁粒度
1、表级锁 :myisam
2、行级锁 :innodb
3、常用存储引擎特点
1、InnoDB特点
1、共享表空间
表名.frm :表结构和索引文件
表名.ibd :表记录
2、支持行级锁
3、支持外键、事务操作
2、MyISAM特点
1、独享表空间
表名.frm :表结构
表名.myd :表记录 mydata
表名.myi :索引文件 myindex
2、支持表级锁
4、如何决定使用哪个存储引擎
1、执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用 InnoDB
3、MySQL调优
1、选择合适的存储引擎
1、读操作多 :MyISAM
2、写操作多 :InnoDB
2、创建索引
在 select、where、order by常涉及到的字段建立索引
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 or id=30;
优化后:
select id from t1 where id=10
union all
select id from t1 where id=20
union all
select id from t1 where id=30;
前置
select name from t1 where name like "%c%";
5、尽量避免使用 in 和 not in,否则全表扫描
select id from t1 where id in(1,2,3,4);
select id from t1 where id between 1 and 4;
6、尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的任何字段
'''SQL语句参数化'''
1 '''SQL语句参数化'''
2
3 import pymysql
4
5 # 1.创建数据库连接对象
6 db = pymysql.connect(host="localhost",user="root",
7 password="123456",database="db4",
8 charset="utf8")
9 # 2.创建游标对象
10 cur = db.cursor()
11
12 s_id = input("请输入省编号:")
13 name = input("请输入省名称:")
14
15 try:
16 sql_insert = "insert into sheng(s_id,s_name) \
17 values(%s,%s);"
18 cur.execute(sql_insert,[s_id,name])# 列表传参
19 print("ok")
20 db.commit()
21 except Exception as e:
22 db.rollback()
23 print("Failed",e)
24
25 cur.close()
26 db.close()
View Code
4、事务和事务回滚
1、定义 :一件事从开始发生到结束的整个过程
2、作用 :确保数据一致性
3、事务和事务回滚应用
1、MySQL中sql命令会自动commit到数据库
show variables like "autocommit";
2、事务应用
1、开启事务
mysql> begin;
mysql> ...一条或多条SQL语句
## 此时autocommit被禁用
2、终止事务
mysql> commit; | rollback;
3、案例
1、背景
你 :建行卡
你朋友 :工商卡
你在建行自动取款机给你朋友的工商卡转账5000元
2、建表
表1、CCB
create table CCB(
name varchar(15),
money decimal(20,2)
);
insert into CCB values("只手遮天",10000);
表2、ICBC
create table ICBC(
name varchar(15),
money decimal(20,2)
);
insert into ICBC values("为所欲为",1000);
3、开始转账
mysql> begin;
mysql> update CCB set money=money-5000 where name="只手遮天";
mysql> update ICBC set money=money+5000 where name="为所欲为";
mysql> commit;
#### 转账成功 ####
1 import pymysql
2
3 # 1.创建数据库连接对象
4 db = pymysql.connect(host="localhost",user="root",
5 password="123456",database="db4",
6 charset="utf8")
7 # 2.创建游标对象
8 cur = db.cursor()
9 # 3.执行SQL语句
10 # 在sheng表中插入1条记录,云南省
11 try:
12 sql_insert = "insert into sheng values\
13 (19,300002,'西藏');"
14 cur.execute(sql_insert)
15 # 把云南省的 id 号改为 666
16 sql_update = "update sheng set id=666 where id=17;"
17 cur.execute(sql_update)
18 # 把台湾省在 sheng 表中删除
19 sql_delete = "delete from sheng where s_name='台湾省';"
20 cur.execute(sql_delete)
21 print("ok")
22 db.commit()
23 except Exception as e:
24 db.rollback()
25 print("出现错误,已回滚",e)
26
27
28 # 5.关闭游标对象
29 cur.close()
30 # 6.断开数据库连接
31 db.close()
View Code
5、与python交互
1、交互类型
1、python3
模块名 :pymysql
安装:
在线 :sudo pip3 install pymysql
离线 :pymysql-0.7.11.tar.gz
$ tar -zxvf pymyql-0.7.11.tar.gz
$ cd pymysql-0.7.11
$ sudo python3 setup.py install
2、python2
模块名 :MySQLdb
安装 :sudo pip install mysql-python
2、pymysql使用流程
1、建立数据库连接(db = pymysql.connect(...))
2、创建游标对象(c = db.cursor())
3、游标方法: c.execute("insert ....")
4、提交到数据库 : db.commit()
5、关闭游标对象 :c.close()
6、断开数据库连接 :db.close()
3、connect对象
1、db = pymysql.connect(参数列表)
1、host :主机地址,本地 localhost
2、port :端口号,默认3306
3、user :用户名
4、password :密码
5、database :库
6、charset :编码方式,推荐使用 utf8
2、数据库连接对象(db)的方法
1、db.close() 关闭连接
2、db.commit() 提交到数据库执行
3、db.rollback() 回滚
4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令
3、游标对象(cur)的方法
1、cur.execute(sql命令,[列表]) 执行SQL命令
2、cur.close() 关闭游标对象
3、cur.fetchone() 获取查询结果集的第一条数据
(1,100001,"河北省")
4、cur.fetchmany(n) 获取n条
((记录1),(记录2))
5、cur.fetchall() 获取所有记录
错误:
1、root@"localhost" denied,Using password:YES
2、"localhostt"
3、connect object has no attribute "rollbake"
4、pymysql has no attribute "connect"
1 import pymysql
2
3 # 1.创建与数据库连接对象
4 db = pymysql.connect(host="localhost",user="root",
5 password="123456",database="db4",
6 charset="utf8")
7 # 2.利用db方法创建游标对象
8 cur = db.cursor()
9
10 # 3.利用游标对象的execute()方法执行SQL命令
11 cur.execute("insert into sheng values\
12 (16,300000,'台湾省');")
13
14 # 4.提交到数据库执行
15 db.commit()
16 print("ok")
17 # 5.关闭游标对象
18 cur.close()
19 # 6.断开数据库连接
20 db.close()
View Code
6、orm(Object Relation Mapping 对象关系映射)
1、定义
把对象模型映射到MySQL数据库中
2、sqlalchemy安装:
在线 :sudo pip3 install sqlalchemy
离线 :
$ tar -zxvf SQLAlchemy-1.2.10.tar.gz
$ cd SQLAlchemy-1.2.10
$ sudo python3 setup.py install
验证:
$ python3
>>> import sqlalchemy
>>>
3、示例
class User(Base):
__tablename__ = "t1" #声明要创建的表名
id = Column(Integer,primary_key=True)
name = Column(String(20))
解释:
一个类User --> 一张表 t1
表中有两个字段 :id 和 name
1 select = "select password from user where\
2 username=%s;"
3
4 print(select)
View Code
1 from mysqlpython import Mysqlpython
2
3 # 创建数据库连接对象
4 sqlh = Mysqlpython("db4")
5
6 # sql_update = "update sheng set s_name='辽宁省' \
7 # where s_name='云南省';"
8 # sqlh.zhixing(sql_update)
9
10 sql_select = "select * from sheng where id=%s;"
11 data = sqlh.all(sql_select,[1])
12 print(data)
View Code
实例一:创建一张表
1 # 连接数据库的模块
2 from sqlalchemy import create_engine
3 from sqlalchemy.ext.declarative import declarative_base
4 from sqlalchemy import Column,Integer,String
5
6 engine = create_engine("mysql+pymysql://root:123456@localhost/db4",encoding="utf8")
7 Base = declarative_base() # orm基类
8
9 class User(Base): # 继承Base基类
10 __tablename__ = "t123"
11 id = Column(Integer,primary_key=True)
12 name = Column(String(20))
13 address = Column(String(40))
14
15 Base.metadata.create_all(engine)
View Code
1 from mysqlpython import Mysqlpython
2 from hashlib import sha1
3
4 uname = input("请输入用户名:")
5 pwd = input("请输入密码:")
6 # 用sha1给pwd加密
7
8 s1 = sha1() # 创建sha1加密对象
9 s1.update(pwd.encode("utf8")) # 指定编码
10 pwd2 = s1.hexdigest() # 返回16进制加密结果
11
12 sqlh = Mysqlpython("db4")
13 select = "select password from user where \
14 username=%s;"
15 result = sqlh.all(select,[uname])
16 # print(result)
17 # (('7c4a8d09ca3762af61e59520943dc26494f8941b',),)
18
19 if len(result) == 0:
20 print("用户名不存在")
21 elif result[0][0] == pwd2:
22 print("登录成功")
23 else:
24 print("密码错误")
加密
1 from pymysql import *
2
3 class Mysqlpython:
4 def __init__(self,database,
5 host="localhost",
6 user="root",
7 password="123456",
8 port=3306,
9 charset="utf8"):
10 self.host = host
11 self.user =user
12 self.password = password
13 self.port = port
14 self.charset = charset
15 self.database = database
16
17 def open(self):
18 self.db = connect(host=self.host,
19 user=self.user,
20 port=self.port,
21 database=self.database,
22 password=self.password,
23 charset=self.charset)
24 self.cur = self.db.cursor()
25
26 def close(self):
27 self.cur.close()
28 self.db.close()
29
30 def zhixing(self,sql,L=[]): # pymysql.execute(sql)
31 try:
32 self.open()
33 self.cur.execute(sql,L)
34 self.db.commit()
35 print("ok")
36 except Exception as e:
37 self.db.rollback()
38 print("Failed",e)
39 self.close()
40
41 def all(self,sql,L=[]):
42 try:
43 self.open()
44 self.cur.execute(sql,L)
45 result = self.cur.fetchall()
46 return result
47 except Exception as e:
48 print("Failed",e)
49 self.close()
View Code
1 import pymysql
2
3 # 1.创建数据库连接对象
4 db = pymysql.connect(host="localhost",user="root",
5 password="123456",database="db4",
6 charset="utf8")
7 # 2.创建游标对象
8 cur = db.cursor()
9
10 try:
11 sql_select = "select * from sheng;"
12 cur.execute(sql_select)
13
14 data1 = cur.fetchone()
15 print(data1)
16 print("*******************")
17
18 data2 = cur.fetchmany(3)
19 for m in data2:
20 print(m)
21 print("*******************")
22
23 data3 = cur.fetchall()
24 for m in data3:
25 print(m)
26 print("*******************")
27
28 db.commit()
29 except Exception as e:
30 print(e)
31
32 cur.close()
33 db.close()
View Code