一、视图 view
1.定义:
当查询的复杂度很高时,指多字段,多关系查询,会导致sql语句混乱,不利于sql语句的编写和编写的sql语句的可读性,引入了视图
视图是一张虚拟的表,有字段和数据,只存放查询语句
视图有字段有行,但是视图基于源表
2.特点:
视图的优点
1.简单:
视图构建了一个虚拟的表,表里的数据是来源于复杂的查询语句,我们将复杂的查询语句存入视图,使用时直接调用视图
2.安全:
数据库有对库和表的权限管理,但是没有对字段权限,可以通过视图来实现权限的功能
3.数据的独立性:
视图基于源表,当源表的结构发生变化时,不会对视图产生影响
视图的缺点
1.降低查询效率
使用视图时,一般存的都是复杂的查询,如果存的是简单的查询,在使用视图时,会作为复杂查询来去执行,会降低查询效率
2.会增加数据库的维护和管理成本,会对数据迁移造成很大影响
3.视图的操作
创建视图
CREATE VIEW 视图名 AS(查询语句);
查看视图
SHOW TABLE STATUS WHERE COMMENT=’view’;
使用视图
SELECT * FROM 视图名;
删除视图
DROP VIEW 视图名;
查看是否删除成功
向源表插入数据,源表中有数据,视图也可以看见,但添加字段,不会对视图产生影响
视图中同样可以看见
添加字段源表可以看到
但视图却没有
二、触发器 Trigger
1.定义及触发条件:
定义:
触发器是一个特殊的存储过程,不需要手动触发,也不用需要call语句调用
触发条件:
由事件触发,当我们在做 添加 删除 修改操作时会自动触发触发器
注意:在创建触发器之前要修改数据的默认结束符 ;创建完触发器记得把结束符改回来
修改方式: \d 修改后的结束符
2.触发器的操作
创建触发器
格式:
CREATE TRIGGER
触发器名字 触发时机 触发事件
ON 表名
FOR EACH ROW
BEGIN
触发器要执行的sql语句
EDN;
给user表创建一个触发器,当向user表中添加数据时 给userinfo也添加一条数据
创建一个userinfo表
创建一个触发器
使用触发器
在创建触发器条件触发成功就执行对应操作;
如下面语句插入成功
查看触发器
Select * from information_schema.triggers;
删除触发器
Drop trigger 触发器名字
查看是否删除成功
数据备份
在创建触发器前要创建一个和源表表格式相同的表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.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()
查询
增
改
删
封装成类,用的时候直接实例化对象调用函数
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)
运行成功
在别的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))
插入成功
获取返回内容的方法
fetchone() 获取一条数据
Fetchall() 获取返回的所有的数据
Rowcount 属性 获取操作影响的行数
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()