数据库 视图 触发器 事物的基本操作 使用python连接数据库：Pymysql 和 orm SQLAlchemy_sqlalchemy 视图

一、视图 view

---

1.定义：

当查询的复杂度很高时，指多字段，多关系查询，会导致sql语句混乱，不利于sql语句的编写和编写的sql语句的可读性，引入了视图

视图是一张虚拟的表，有字段和数据，只存放查询语句

视图有字段有行，但是视图基于源表

---

2.特点：

视图的优点

1.简单：
视图构建了一个虚拟的表，表里的数据是来源于复杂的查询语句，我们将复杂的查询语句存入视图，使用时直接调用视图

2.安全：
数据库有对库和表的权限管理，但是没有对字段权限，可以通过视图来实现权限的功能

3.数据的独立性：
视图基于源表，当源表的结构发生变化时，不会对视图产生影响

---

视图的缺点

1.降低查询效率

使用视图时，一般存的都是复杂的查询，如果存的是简单的查询，在使用视图时，会作为复杂查询来去执行，会降低查询效率

2.会增加数据库的维护和管理成本，会对数据迁移造成很大影响

---

3.视图的操作

创建视图

CREATE VIEW 视图名 AS(查询语句);
1

---

查看视图

SHOW TABLE STATUS WHERE COMMENT=’view’;
1

---

使用视图

SELECT * FROM 视图名;
1

---

删除视图

DROP VIEW 视图名;

查看是否删除成功

---

向源表插入数据，源表中有数据，视图也可以看见，但添加字段，不会对视图产生影响

视图中同样可以看见

添加字段源表可以看到

但视图却没有

---

二、触发器 Trigger

1.定义及触发条件：

定义：
触发器是一个特殊的存储过程，不需要手动触发，也不用需要call语句调用

触发条件：
由事件触发，当我们在做 添加 删除 修改操作时会自动触发触发器

---

注意：在创建触发器之前要修改数据的默认结束符 ;创建完触发器记得把结束符改回来

修改方式： \d 修改后的结束符

---

2.触发器的操作

创建触发器

格式：

CREATE TRIGGER 
触发器名字 触发时机 触发事件 
ON 表名 
FOR EACH ROW 
BEGIN
触发器要执行的sql语句
EDN;
1
2
3
4
5
6
7

给user表创建一个触发器，当向user表中添加数据时 给userinfo也添加一条数据

创建一个userinfo表

创建一个触发器

---

使用触发器

在创建触发器条件触发成功就执行对应操作；
如下面语句插入成功

---

查看触发器

Select * from information_schema.triggers;
1

---

删除触发器

Drop trigger 触发器名字
1

查看是否删除成功

---

数据备份

在创建触发器前要创建一个和源表表格式相同的表back_user

备份的数据一定要放在格式相同的表中不然报错

当我们删除user表中的数据时 将删除的数据备份的 back_user表中

代码如下：

Create trigger back_user before delete on user for each row
Begin
Insert into back_user values(old.id,old.uname);
end
1
2
3
4

备份成功

---

三、事物的基本操作

注意在进行操作时要开启两个命令行进行操作（因为操作后的结果可能在一个命令行中无法出现有效结果）

1.Begin 开启一个事物

开启事务后未提交的影响：

---

2.Commit 提交事物

提交后： 数据正常显示

---

3.Rollback 回滚

在提交以后执行回滚： 无效果

在提交前执行回滚 撤销操作

---

四、使用python连接数据库

1.Pymysql

pymysql的使用要求

Pymysql：是一个第三方的模块，并且要求你的python版本为3.5以上
Python2中使用的是 mysqldb

---

安装： pip install pymysql

使用：

1.连接数据库
2.创建游标
3.定义sql语句
4.执行sql语句
5.关闭连接

代码如下：

# 导包
import pymysql

#1.连接数据库
db=pymysql.connect(host='localhost',user='root',password='123456',database='demo', cursorclass=pymysql.cursors.DictCursor)

# 2.创建游标对象
cursor=db.cursor()

# 3.定义查询的sql语句
# 查
# sql='select * from student'

# 增
# sql='insert into student(id,name,age) values(11,"甄姬",16)'

# 改
# sql='update student set name="周瑜" where name="甄姬"'

# 删
sql="delete from student where name='周瑜'"

# 4.执行sql语句
cursor.execute(sql)
# 获取返回的结果
# 获取一条
print(cursor.fetchone())
# 获取全部
print(cursor.fetchall())
# 属性，返回操作条数
# print(cursor.rowcount)
# 如果操作对数据库中的数据产生了影响必须提交
db.commit()

# 5.断开连接
cursor.close()
db.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

查询

增


改


删

---

封装成类，用的时候直接实例化对象调用函数

import pymysql

class MyDB:
    def __init__(self):
        # 连接数据库，创建游标对象
        self.db=pymysql.connect(host='localhost',user='root',password='123456',database='demo', cursorclass=pymysql.cursors.DictCursor)

# 传参数的写法
    # self.db = pymysql.connect(host=h, user=u, password=p, database=db,                    cursorclass=pymysql.cursors.DictCursor)

    self.cursor=self.db.cursor()
# 做查询
def query(self,sql):
    self.cursor.execute(sql)
    return self.cursor.fetchall()
# 做添加 删除 修改
def change(self,sql):
    self.cursor.execute(sql)
    self.db.commit()
    return self.cursor.rowcount
# 析构函数
def __del__(self):
    # 关闭数据库连接
    self.cursor.close()
    self.db.close()

# 测试是否能正常使用
if __name__ == '__main__':
    # 实例化
    database=MyDB()
    res=database.query(sql='select * from student')
    print(res)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

运行成功

在别的py文件里实例化对象进行操作

from mydb import MyDB

# 实例化数据化对象
database=MyDB()

# 插入数据
sql='insert into student(id,name) values(10,"韩信")'
print(database.change(sql))

# 定义sql语句
# sql='select * from student'
# print(database.query(sql))
1
2
3
4
5
6
7
8
9
10
11
12

插入成功

---

获取返回内容的方法

fetchone() 获取一条数据
Fetchall() 获取返回的所有的数据 
Rowcount  属性 获取操作影响的行数
1
2
3

---

2.orm操作

orm数据映射关系

将python语句转义成sql语句和数据库进行交互

---

特点

1.使用方便

2.执行效率下降

---

python 的 orm sqlalchemy

在这之前要安装mysqlclient
然后sqlalchemy创建连接是不需要+pymysql，否则都要加；

创建连接
数据库类型+数据库操作的包:// 用户名:密码@主机地址/你要操作的数据库

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
# 类似于 pymysql中的游标
from sqlalchemy.orm import sessionmaker


# 1.创建连接
# 数据库类型:// 用户名:密码@主机地址/你要操作的数据库
# mysql://scott:tiger@hostname/dbname
db = sqlalchemy.create_engine('mysql://root:123123@localhost/sqlorm')

# 2.创建基类
base = declarative_base(db)

# 3.创建类 必须继承基类  创建模型
class User(base):
    # 表名
    __tablename__='user'
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(32)) # varchar()
    age = sqlalchemy.Column(sqlalchemy.Integer)

class Userinfo(base):
    __tablename__='userinfo'
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    phone = sqlalchemy.Column(sqlalchemy.String(20))

class Shop(base):
    __tablename__='shop'
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(32))




if __name__ == '__main__':
    # 执行数据库迁移 创建表
    base.metadata.create_all(db)
    # 绑定一个实例
    s = sessionmaker(bind=db)
    # 创建会话对象  类似于游标
    session = s()

# 添加
# user = User(name='hello',age=16)
# session.add(user)
# session.commit()
# session.add_all([
#     User(name='world',age=1),
#     User(name='python',age=28),
#     User(name='PHP', age=30),
# ])
# session.commit()
# 查询
# 查询所有的数据 返回一个列表
# res = session.query(User).all()
# for i in res:
#     print(i.name,i.age)
# 通过主键查询一条数据 返回一个对象
# res = session.query(User).get(1)
# print(res.name,res.age)
# 条件查询 返回的是一个列表
# res = session.query(User).filter_by(name='hello').all()
# print(res)
# res = session.query(User).filter(User.name=='hello').all()
# print(res)
# 修改数据
# res = session.query(User).get(1)
# print(res.name)
# res.name='HELLO'
# session.commit()
# 删除数据
res = session.query(User).get(1)
session.delete(res)
session.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

---