mysql基本操作2

最新推荐文章于 2024-04-03 19:01:16 发布

cyz0202

最新推荐文章于 2024-04-03 19:01:16 发布

阅读量112

点赞数

分类专栏：技术问题 # 数据库

本文链接：https://blog.csdn.net/cyz0202/article/details/113874608

版权

mysql

技术问题同时被 2 个专栏收录

56 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

视图性质
- 概念：利用已有数据构建出来的虚拟表
- 特性：因为是虚拟的，所以真实数据变动，该表数据也会相应变动
- 好处：
  - 子表查询较多时，可以用视图把常用子表创建出来
  - 保护机密数据：如student表涉及全校15个院系的学生数据，此时可以定义15个视图分别给相应院系的人使用和修改；
  - 可以适当修改实体表结构而不影响已有视图
视图操作
- 视图创建
  - 单表查询创建视图
```
	select ename, sal, hiredate, job from emp
	create view emp_view as (select ename, sal, hiredate, job from emp)
	select * from emp_view
```
  - 多表查询创建视图
- 视图查看
  - show tables：显示表和视图
  - show table status like “emp_view”
  - desc emp_view
  - show create view emp_view
- 视图修改
  - create or replace view emp_view as (select …) # 可认为重新创建了视图
  - alter view emp_view as (select …)
- 视图删除
  - drop view [if exists] emp_view[,view_1, view_2]
- 视图更新 # 注意试图更新会同时更新实体表
  - insert into emp_view(ename, sal, hiredate, deptno) values(“abc”, 123, 20000101, 10)
  - update emp_view set sal=20000 where ename=“abc”
- 更新视图的限制条件
  - 包含多行函数（min/max等）不能更新视图
  - 包含group by / having等不能更新视图
索引：多字段排序存储，提高查询效率
- BTree （from itbaizhan）
- R-Tree：只支持空间索引
- Hash：哈希索引
- 查看存储引擎：show engines
  - InnoDB(5.5+)：BTree、RTree，默认BTree
  - MyISAM：BTree、RTree，默认BTree
  - MEMORY: BTree、Hash，默认Hash
  - 索引分类
    - 普通索引：无任何限制
    - 唯一索引：相应字段值是唯一的，允许为空
    - 主键索引：特殊的唯一索引，不允许为空
    - 全文索引：一大串文本中进行查找
    - 空间索引：空间类型字段，非空，INnoDB/MyISAM均支持
    - 复合索引：多字段索引，必须出现第一个字段才能出发该索引
  - 创建索引
    - 自动创建：PRIMARY KEY / UNIQUE 会自动创建主键索引 / 唯一索引
      - 查看索引：show index from emp;
    - 手动创建：
```
	create table stu2(
		stu_id int(7),
		stu_name varchar(20),
		email varchar(20),
		stu_info text,
		index(stu_id)  -- 创建普通索引
		unique (email)  -- 创建唯一索引
		primary key (stu_id)  -- 创建主键索引
		fulltext (stu_info)  -- 创建全文索引
	)
	show index from stu2;
	-- 为已有表添加 复合 索引：
	create index index_name on student(stu_id, stu_name)
	alter table student add index (stu_id)  -- 普通索引
	alter table student add unique [index] (email)
```
  - 删除索引
    - alter table student drop index email
    - drop index email on student
用户管理
- 创建用户：create user “test1”@“localhost” identified by “test1”
- 删除用户：drop user “test1”@“localhost”;
- 删除用户：delete from user where user=“test1” and host=“localhost”
- 修改密码：
  - 修改ROOT: alter user “root”@“localhost” identified by “1234” # 新版本的mysql要求密码复杂
  - 修改ROOT: set password=“1234”
  - 修改普通用户：set password for “test1”@“localhost” = “1234”
  - 修改普通用户：alter user “test1”@“localhost” identified by “1234”
- 找回密码-root：windows举例
  - 停止 mysql: net stop mysql
  - 创建文件： ./mysqlpwd.txt，内含命令行：alter user “root”@“localhost” identified by “newpwd”
  - 执行命令：Mysqld --init-file=./mysqlpwd.txt --console
  - 启动 mysql: net start mysql
- 权限管理
  - 查看权限：show grants for “username”@“host”
  - 授予权限：grant select,create,insert,… on mysql.user to “test1”@“localhost”
  - 收回权限：revoke delete on . from “test1”@“localhost”
  - 收回所有权限：revoke all, grant option from “test1”@“localhost”
事务
- InnoDB支持事务，MySAM（mysql5.5之前）不支持
- 事务特性：ACID
  - 原子性：整体执行
  - 一致性：从一个；一致性状态A 到另一个一致性状态B
  - 隔离性：事务之间隔离
  - 持久性：事务一单提交，数据永久改变
- 事务控制
```
create table account(
	id int primary key auto_increment,
	username varchar(30) not null,
	balance double
);
insert into account(username, balance) values("zs", 1000);
insert into account(username, balance) values("ls", 1000);
-- 开启转账事务
start transaction;
update account set balance = balance - 200 where username="zs";
update account set balance = balance + 200 where username="ls";
-- 执行需要提交该事务\\或者回滚该事务
commit; -- rollback;
```
- 事务隔离级别
  - 决定如何控制并发用户读写数据的操作
    - 脏读：A开启事务并修改，尚未提交时，B读取相应记录
    - 不可重复读：A一直在修改某一条记录，导致B多次查询得到不同结果
    - 幻读：A提交，B看到
  - 隔离级别
    - read uncommitted：可读取未提交内容
    - read committed
    - repeatable read：保证重复读
    - serializable
```
	-- 查看隔离级别
	select @@transtraction_isolation;
	-- 设置隔离级别
	set session transaction isolation level read uncommitted;
```
- 数据库的设计：表结构及表之间的联系；范式/规范：使得表结构合理、消除存储异常、减少冗余、便于插入、删除和更新
  - 第一范式(1NF)：每一列都是不可分割的基本数据项，同一列不能有多个值（计算机3班–计算机系+3班，很可能不符合1NF）
  - 第二范式(2NF)：每一列都和主键相关（通过主键唯一确定每一列的值，如学号确定姓名，不能确定课程、成绩；例子来自itbaizhan）
  - 第三范式(3NF)：不能出现传递依赖，即C->B->A，C传递依赖A

pymysql

pip install PyMySQL
连接mysql

import pymysql
# 获取连接
connection =  pymysql.connect(host="localhost", user="root", password="root", db="test06", charset="utf8")
print(connection)

# 执行sql：查询
cursor = connection.cursor()
sql = "select * from emp"
cursor.execute(sql)
# 查看结果
emps=cursor.fetchall()
for e in emps: 
	print(e, end="\n")

# 执行sql：插入数据
sql = "insert into emp(empno, ename) values(9898, 'merry')"
count = cursor.execute(sql)
print("count:", count)  # count=1
# 执行DML时，会开启一个事务，需要主动提交
connection.commit()

# 执行sql：修改数据
sql = "update emp set sal=10000 where empno=9898"
...  # 同上

# 执行sql：删除数据
sql = "delete from emp where empno=9898"
...  # 同上

# 查询漏洞
sql = "insert into emp(empno, ename) values(%s, %s)"
args = (1234, "marry")
count = cursor.execute(sql,args)

# 添加异常处理
...
connection = None
cursor = None
try:
	connection = pymysql.connect(...)
	...
except Exception as e:
	print(e)
	if connection:
		connection.rollback()
finally:
	if cursor:
		cursor.close()
	if connection:
		connection.close()
...

# 工具类
class DBUtils:
	config = {...}  # 连接数据库的配置
	def __init__(self):
		self.connection = pymysql.connect(**DBUtils.config)
		self.cursor = self.connection.cursor()
	def close(self):
		if self.cursor:
			self.cursor.close()
		if self.connection:
			self.connection.close()
	# 任务类型1：执行插入 修改 删除，事务
	def exeDML(self, sql, *args):  
		try:
			count = self.cursor.execute(sql, args)
			self.connection.commit()
		except Exception as e:
			print(e)
			if self.connection:
				self.connection.rollback()
		finally:
			self.close()
	# 任务类型2：查询单条数据
	def query_one(self, sql, *args):
		try:
			self.cursor.execute(sql, args)  # 查询非事务，无需commit
			return self.cursor.fetchone()
		except Exception as e:
			print(e)
		finally:
			self.close()
	# 任务类型3：查询多条数据（任务2其实可视为该任务的特例）
	def query_all(self, sql, *args):
		try:
			self.cursor.execute(sql, args)
			return self.cursor.fetchall()
		except Exception as e:
			print(e)
		finally:
			self.close()

cyz0202

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
mysql基本操作2

视图性质概念：利用已有数据构建出来的虚拟表特性：因为是虚拟的，所以真实数据变动，该表数据也会相应变动好处：子表查询较多时，可以用视图把常用子表创建出来保护机密数据：如student表涉及全校15个院系的学生数据，此时可以定义15个视图分别给相应院系的人使用和修改；可以适当修改实体表结构而不影响已有视图视图操作视图创建单表查询创建视图 select ename, sal, hiredate, job from emp create view emp_view a..
复制链接

扫一扫

专栏目录