php网站开发外包,怎么赚钱,python3.5 做网站,旅游网站流程图文章目录 Day03 数据库管理学习目标1. 事务管理1.1 事务的概念1.2 事务的特性1.3 语法1.4 事务的并发问题1.5 事务隔离级别1.6 设置和查看隔离级别 2 用户管理2.1 创建删除用户2.2 权限管理2.2.1 权限赋予的原则2.2.2 权限赋予2.2.3 权限回收2.2.4 登录管理 3. MySQL8的部分新特… 文章目录 Day03 数据库管理学习目标1. 事务管理1.1 事务的概念1.2 事务的特性1.3 语法1.4 事务的并发问题1.5 事务隔离级别1.6 设置和查看隔离级别 2 用户管理2.1 创建删除用户2.2 权限管理2.2.1 权限赋予的原则2.2.2 权限赋予2.2.3 权限回收2.2.4 登录管理 3. MySQL8的部分新特性3.1 系统表全部为InnoDB表3.2 默认字符集改为utf8mb43.3 支持检查约束见上面检查约束3.4 支持窗口函数见上面窗口函数3.5 用户管理3.6 其他新特性略 Day03 数据库管理
学习目标 能够说出事务的概念和特点 能够使用代码开启和处理事务 能够说出事务脏读幻读和不可重复读的概念 能够说出事务隔离级别的意义 能够使用数据创建和删除用户 能够对用户进行权限管理
1. 事务管理
在日常生活中经常有A用户给B用户转账这种需求。转账100元的基本逻辑就是将A账户的里的余额减掉100元,然后再将B账户里的余额增加100元。
思考是否可能出现A账户的100元减掉了但是此时数据库出现了问题账户B里没有成功增加100元的这种情况
解决方法将A账户减掉100元和B账户增加100元的两个操作放到一起执行要么都成功A减100,B加100转账完成要么都失败A没有减100B也没有加100转账失败但是账户的余额不变。
1.1 事务的概念
事务处理事务操作保证所有事务都作为一个工作单元来执行即使出现了故障都不能改变这种执行方式。当在一个事务中执行多个操作时要么所有的操作都被提交(commit)那么这些修改就永久地保存下来要么数据库管理系统将放弃本次事务中的所有修改将整个事务回滚(rollback)到最初状态。 注意在MySQL中只有InnoDB存储引擎才支持事务。 show variables like %storage_engine%; -- 查看数据使用的存储引擎
show engines; -- 查看各存储引擎1.2 事务的特性
在写入或更新资料的过程中, 为保证事务 (transaction) 是正确可靠的, 所必须具备的四个特性 (ACID)
原子性Atomicity 事务中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节。事务在执行过程中发生错误, 会被回滚 (Rollback) 到事务开始前的状态, 就像这个事务从来没有执行过一样。 一致性Consistency 在事务开始之前和事务结束以后, 数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则, 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 隔离性Isolation 事务的隔离性是指一个事务的执行不能被其他事务干扰即一个事务内部的操作及使用的数据对并发的其他事务是隔离的并发执行的各个事务之间不能互相干扰。持久性Durability 持久性是指一个事务一旦被提交它对数据库中数据的改变就是永久性的接下来的其他操作和数据库故障不应该对其有任何影响。
/*
原子性
例如
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
成功张三账号变为500李四变为1500
失败张三账号是1000李四还是1000.#一致性
例如
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000要么他俩的余额不变 还是1000总和就是2000
要么他俩的余额有变化张三500李四1500总和仍然是2000
错误
张三500李四1000总和是1500结果不对
张三1000李四1500总和是2500结果不对#隔离性
例如张三要给李四转500王五要给李四转500张三转账是否成功和王五是否转账成功无关。#持久性
例如张三要给李四转500一旦成功提交就转账成功撤不回来了。1.3 语法
mysql默认是自动提交执行一句就提交一句即默认情况下每一条sql都是一个独立的sql。
关闭自动提交事务: set autocommitfalse开启事务: BEGIN或START TRANSACTION提交事务: COMMIT, 提交会让所有修改生效回滚: ROLLBACK, 撤销正在进行的所有未提交的修改创建保存点: SAVEPOINT identifier删除保存点: RELEASE SAVEPOINT identifier把事务回滚到保存点: ROLLBACK TO identifier
例如
SET AUTOCOMMIT false;
START TRANSACTION;
UPDATE t_employee SET salary 0
WHERE ename 李冰冰;
commit;1.4 事务的并发问题
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读一个事务读取了另一个事务未提交数据不可重复读同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。幻读一个事务读取了另一个事务新增、删除的记录情况记录数不一样像是出现幻觉。
1.5 事务隔离级别
数据库事务的隔离性数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。**一个事务与其他事务隔离的程度称为隔离级别。**数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
数据库提供的 4 种事务隔离级别
隔离级别描述read-uncommitted允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题read-committed只允许A事务读取其他事务已提交的数据。可以避免脏读但仍然会出现不可重复读、幻读问题repeatable-read确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读。但是幻读问题仍然存在。注意mysql中使用了MVCC多版本控制技术在这个级别也可以避免幻读。serializable确保事务可以从一个表中读取相同的行相同的记录。在这个事务持续期间禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免但性能十分低下。
Oracle 支持的 2 种事务隔离级别READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。
1.6 设置和查看隔离级别
每启动一个 mysql 客户端程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个变量来记录当前连接的事务隔离级别。mysql服务器里也有一个全局变量用来表示所有连接的默认事务隔离级别。
查看当前mysql连接的隔离级别:
select tx_isolation; -- MySQL5.7
select transaction_isolation; -- MySQL8.0查看全局的隔离级别
select global.tx_isolation; -- MySQL5.7
select global.transaction_isolation; -- MySQL8.0设置当前 mysql连接的隔离级别:
set tx_isolation repeatable-read; -- MySQL 5.7
set transaction_isolationrepeatable-read; -- MySQL8.0设置数据库系统的全局的隔离级别:
set global tx_isolation read-committed; -- MySQL5.7
set global.transaction_isolationread-committed; -- MySQL8.0注意这里的隔离级别中间是减号不是下划线。 2 用户管理
初次登录MySQL服务器时能够看到里面有四个默认的数据库其中mysql库主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。 2.1 创建删除用户
MySQL数据库默认有一个root用户这个用户拥有着至高无上的权限。为了保证数据的安全我们不会轻易的使用root用户而是创建一些新的用户并赋予有限的权限从而保证数据库的安全。
MySQL数据库中的用户都保存在mysql库的user表中添加用户其实就是往mysql.user表添加一条记录。但是我们不直接修改mysql.user表而是通过特定的命令来添加用户。 添加用户 create user username允许登录的地址 identified by 密码;
create user chrislocalhost identified by abcd1234; -- 创建了用户chris,密码是abcd1234,它只能本机登录删除用户 drop user username允许登录的主机名; -- 使用 drop 命令删除用户
drop user chrislocalhost;2.2 权限管理
MySQL中的权限验证分为两个阶段:
第一阶段为连接验证主要限制用户连接 MySQL服务器时使用密码是否正确IP地址是否允许。第二阶段为操作检查主要检查用户执行的指令是否被允许一般非管理员账户不被允许执行 drop、delete 等危险操作
使用命令创建用户以后这个用户默认是没有任何权限的也就是说用户不能做任何的操作。我们在给用户赋予权限的时候要遵守一定的原则不能直接把所有的权限都给到用户。
2.2.1 权限赋予的原则
只授予能满足需要的最小权限防止用户执行危险操作。限制用户的登录主机防止不速之客登录数据库。禁止或删除没有密码的用户。禁止用户使用弱密码。定期清理无效的用户回收权限或者删除用户。
2.2.2 权限赋予
show privileges; -- 可以查看系统中所有可用的权限/*
权限列表可以选择 SELECT,UPDATE,DELETE等也可以使用 ALL PRIVILEGES 赋予所有的权限。
数据库名表示权限赋予给了这个用户能够那个数据库。如果是所有数据库可以使用 *
表名表示权限赋予给了这个用户能够访问哪个表。如果是所有的表可以使用 *
WITH GRANT OPTION, 表示权限是否还能向下下发。
*/
GRANT 权限列表 on 数据库名.表名 to 用户名主机 [WITH GRANT OPTION];
-- 刷新使权限生效
flush privileges;GRANT SELECT ON *.* to chrislocalhost; -- chirs 用户能够查询所有数据库的所有表格但是这个权限不能再下发
GRANT ALL PRIVILEGES *.* to chrislocalhost WITH GRANT OPTION; -- chirs 用户对所有数据所有表格都有权限而且这个权限可以下发!2.2.3 权限回收
show grants; -- 查看当前用户的权限
show grants for 用户名主机; -- 查看指定用户的权限revoke 权限列表 ON 数据库名.表名 FROM 用户名主机IP地址;2.2.4 登录管理
MySQL的root用户默认只允许本机登录不能通过其他计算机连接到本机的MySQL服务器。
mysql -h192.168.2.134 -uroot -p # 连接到 192.68.2.134一个远程的MySQL服务器会连接失败如果想要让自己电脑上安装的MySQL服务器被网络上的其他电脑访问需要对账户允许登录的服务器进行修改。
update user set host% where userroot; -- 允许 root 用户从任意的主机登录请注意上述的操作非常危险root用户可以通过网络上任意一台电脑登录由于root用户拥有至高无上的权限这样会导致数据库非常的不安全 3. MySQL8的部分新特性
MySQL8.0在MySQL5.7的基础上做了较大改动下面我们介绍一下MySLQ8的部分新特性。
3.1 系统表全部为InnoDB表
从 MySQL 8.0 开始mysql 系统表和数据字典表使用 InnoDB 存储引擎存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。在 MySQL 5.7 之前这些系统表使用 MyISAM 存储引擎存储在 mysql 数据库文件目录下各自的表空间文件中。关于数据库存储引擎的详细内容在MySQL高级课程讲解。
在MySQL5.7版本中查看系统表类型结果如下
mysql #MySQL5.7
mysql #查看系统表类型
mysql SELECT DISTINCT(ENGINE) FROM information_schema.tables;
----------------------
| ENGINE |
----------------------
| MEMORY |
| InnoDB |
| MyISAM |
| CSV |
| PERFORMANCE_SCHEMA |
| NULL |
----------------------
6 rows in set (0.04 sec)在MySQL8.0版本中查看系统表类型结果如下
mysql #MySQL8.0
mysql #查看系统表类型
mysql SELECT DISTINCT(ENGINE) FROM information_schema.tables;
--------------------
| ENGINE |
--------------------
| InnoDB |
| NULL |
| PERFORMANCE_SCHEMA |
| CSV |
--------------------
4 rows in set (0.00 sec)系统表全部换成事务型的InnoDB表默认的MySQL实例将不包含任何MyISAM表除非手动创建MyISAM表。
3.2 默认字符集改为utf8mb4
在8.0版本之前MySQL默认的字符集为Latin1而8.0版本默认字符集为utf8mb4。
Latin1是ISO-8859-1的别名有些环境下写作Latin-1。ISO-8859-1编码是单字节编码不支持中文等多字节字符但向下兼容ASCII。
MySQL中utf8字符集它是utf8mb3的别称使用三个字节编码表示一个字符。自MySQL4.1版本被引入能够支持绝大多数语言的字符但依然有些字符不能正确编码如emoji表情字符等为此MySQL5.5引入了utf8mb4字符集。在MySQL5.7对utf8mb4进行了大幅优化并丰富了校验字符集。mb4就是“most byte 4”的意思专门用来兼容四字节的Unicodeutf8mb4编码是utf8编码的超集兼容utf8并且能存储4字节的表情字符。如果原来某些库和表的字符集是utf8可以直接修改为utf8mb4不需要做其他转换。但是从uft8mb4转回utf8就会有问题。
使用SHOW语句查看MySQL5.7版本数据库的默认编码。
mysql #查看MySQL5.7数据库的默认编码
mysql SHOW VARIABLES LIKE character_set_database;
--------------------------------
| Variable_name | Value |
--------------------------------
| character_set_database | latin1 |
--------------------------------
1 row in set, 1 warning (0.00 sec)使用SHOW语句查看MySQL8.0版本数据库的默认编码。
mysql #查看MySQL8.0数据库的默认编码
mysql SHOW VARIABLES LIKE character_set_database;
---------------------------------
| Variable_name | Value |
---------------------------------
| character_set_database | utf8mb4 |
---------------------------------
1 row in set, 1 warning (0.00 sec)字符集校对规则是在字符集内用于字符比较和排序的一套规则比如有的规则区分大小写有的则无视。校对规则特征
两个不同的字符集不能有相同的校对规则。每个字符集有一个默认校对规则。校对规则存在命名约定以其相关的字符集名开始中间包括一个语言名并且以_ci、_cs或_bin结尾。其中_ci表示大小写不敏感、_cs表示大小写敏感、bin表示直接比较字符的二进制编码即区分大小写。
使用SHOW语句查看utf8mb4字符集的部分校对规则如下
mysql SHOW COLLATION LIKE utf8mb4_0900%;
-------------------------------------------------------------------
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute|
-------------------------------------------------------------------
|utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
|utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
|utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
|utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
-------------------------------------------------------------------
4 rows in set (0.00 sec)3.3 支持检查约束见上面检查约束
3.4 支持窗口函数见上面窗口函数
3.5 用户管理
在MySQL 8.x中默认的身份认证插件是“caching_sha2_password”替代了之前的“mysql_native_password”。可以通过系统变量default_authentication_plugin和mysql数据库中的user表来看到这个变化。
在MySQL8之前默认的身份插件是“mysql_native_password”即MySQL用户的密码使用PASSWORD函数进行加密。在MySQL 8.x中默认的身份认证插件是“caching_sha2_password”替代了之前的“mysql_native_password”PASSWORD函数被弃用了。
在MySQL版本5.6.6版本起在mysql.user表中添加了“password_expired”字段它允许设置密码是否失效。如果“password_lifetime”字段值不为NULL那么从MySQL服务启动时间开始经过“password_lifetime”字段值的时间间隔之后密码就过期了即“password_expired”字段就为“Y”。任何密码超期的账号想要连接服务器端进行数据库操作都必须更改密码。MySQL8.0版本允许数据库管理员手动设置账户密码过期时间。
从MySQL 8.x版本开始允许限制重复使用以前的密码。
在MySQL8之前如果要给多个用户授予相同的角色需要为每个用户单独授权。在MySQL8之后可以为多个用户赋予统一的角色然后给角色授权即可角色可以看成是一些权限的集合这样就无须为每个用户单独授权。如果角色的权限修改将会使得该角色下的所有用户的权限都跟着修改这就非常方便。
mysql的密码字段有变化 mysql5.7之前mysql系统库的user表密码字段名是password mysql5.7版本mysql系统库的user表密码字段名是authentication_string mysql8.0版本mysql系统库的user表密码字段名是authentication_string另外用户管理还有角色概念mysql系统库中有default_roles表。
3.6 其他新特性略
通用表达式、计算列、DDL操作支持原子性、数据字典合并等等。
通用表达式简称为CTECommon Table Expressions。CTE是命名的临时结果集作用范围是当前语句。CTE可以理解为一个可以复用的子查询但是和子查询又有区别一个CTE可以引用其他CTECTE还可以是自引用(递归CTE)也可以在同一查询中多次引用但子查询不可以。
WITH [RECURSIVE]cte_name [(col_name [, col_name] ...)] AS (subquery)[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...通用表达式以“WITH”开头如果“WITH”后面加“RECURSIVE”就表示接下来在通用表达式中需要递归引用自己否则就不递归引用。每一个通用表达式都需要有一个名字它相当于是子查询结果集的名字。
#1在“t_employee”表中查询每个人薪资和公司平均薪资的的差值。
WITH temp AS (SELECT ROUND(AVG(salary),2) AS pingjun FROM t_employee)
SELECT ename AS 员工姓名,salary AS 薪资,pingjun 公司平均薪资,ROUND(salary - pingjun,2) 差值
FROM t_employee,temp
HAVING ABS(差值)5000;#2查询薪资低于9000的员工编号员工姓名员工薪资领导编号领导姓名领导薪资
WITH
emp AS (SELECT eid,ename,salary,mid FROM t_employee WHERE salary 9000),
mgr(meid,mename,msalary) AS (SELECT eid,ename,salary FROM t_employee)SELECT eid AS 员工薪资,ename AS 员工姓名,salary AS 员工薪资,meid AS 领导编号,mename AS 领导姓名,msalary AS 领导薪资
FROM emp INNER JOIN mgr ON emp.mid mgr.meid;#3查询eid为21的员工和他所有领导直到最高领导。
CREATE TABLE emp AS (SELECT eid,ename,salary,tel,mid FROM t_employee WHERE salary 10000);
UPDATE emp SET MID19 WHERE eid21;
UPDATE emp SET MID17 WHERE eid19;
UPDATE emp SET MID16 WHERE eid17;
UPDATE emp SET MID15 WHERE eid16;
UPDATE emp SET MID4 WHERE eid15;
UPDATE emp SET MIDNULL WHERE eid4;
SELECT * FROM emp;WITH RECURSIVE cte
AS ( SELECT eid,ename,midFROM empWHERE eid 21UNION ALLSELECT emp.eid,emp.ename,emp.midFROM emp INNER JOIN cteON emp.eid cte.midWHERE emp.eid IS NOT NULL
)
SELECT * FROM cte;
