**
1,事物的概念特点及控制
**
1,事物的概念:
是一个操作序列,该序列中的多个操作要么都做,要么都不做
2,事物的特点:
a,原子性
原子是自然界中最小的颗粒,具有不可再分的特点
事物中的所有操作可看作是一个原子,要么全部执行,要么全不执行
b,一致性
事物执行的结果必须要保证数据库中数据的一致性
c,隔离性
各个事物的操作是互不干扰的,任意一个事物的内部操作都对其他并发的事物不能产生干扰
d,持久性
指事物一旦提交后,对数据库中数据做出的任何改变都会永久保存
3,事物的控制
a,开启事物
语法为 start transaction|begin 开启一个新的事物
---创建一个库test1
create database test1;
---创建一张表account(id账号主键自增长 username账号名 balance金额)
create table account(
id int(8) primary key auto_increment,
username varchar(30),
balance double
);
---为account表插入数据
insert into account(username,balance) values ("张三",2000),("李四",2000);
---开启新事物,完成张三给李四转账200
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
注意:使用start transaction 开启一个新事物后,该事物不会自动提交,必须手动提交
b,提交事物
语法为commit;
---开启新事物,完成张三给李四转账200,提交事物
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
commit;
c,回滚事物
语法为rollback;
注意:开启的事物,未提交时则可以回滚
---回滚事物
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
rollback;
**
2,事物的隔离级别
**
用于决定如何控制并发用户读写数据的操作
事物的隔离级别由低到高分为:
1,read uncommitted 读取未提交的数据内容
---张三开启事物,进行转账操作,但不提交事物
select * from account;
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
---设置事物隔离级别为read uncommitted
set session transaction isolation level read uncommitted;
---查看事物隔离级别
select @@transaction_isolation;
---开启新的事物,查询表中的数据,可以查询到未提交的数据
start transaction;
select * from account;
注意:张三开启事物进行转账,但未提交事物,
李四设置了隔离级别是read uncommitted,开启事物查询数据时就可以看到张三修改后的数据
如果张三现在做回滚操作,则李四看到的数据就是脏数据
2,read committed 读取提交的数据,该隔离级别下所有事物只能看到其他事物已经提交的数据 解决了脏读的问题
---设置李四窗口的隔离级别为read committed
set session transaction isolation level read committed;
---查看设置是否成功
select @@transaction_isolation;
---开启事物读取数据
start transaction;
select * from account;
--- 打开一个新的查询窗口,张三开启事物进行转账,不提交事物的情况
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
---提交事物的情况下:
commit;
李四设置了隔离级别是read committed,开启事物查询数据
张三开启事物进行转账,但不提交事物,李四查询数据时,没有看到转账的数据,数据还是原来的数据
当张三提交了事物,李四查询数据的时候就可以看到提交后的数据,这样解决了脏读的问题
但是李四前后两次读取数据的结果不一致,则read committed 不能解决重复读的问题
3,repeatable read 可重复读 是MySQL默认的隔离级别
---设置李四窗口的隔离级别为repeatable read
set session transaction isolation level repeatable read;
---查看设置是否成功
select @@transaction_isolation;
---开启事物读取数据
start transaction;
select * from account;
--- 打开一个新的查询窗口,张三开启事物进行转账,不提交事物的情况
start transaction;
update account set balance=balance-200 where username="张三";
update account set balance=balance+200 where username="李四";
---提交事物的情况下:
commit;
李四设置隔离级别repeatable read 开启事物查询数据
张三开启事物进行转账,但不提交事物,李四查询数据,则看到还是原来的数据
张三提交事物,李四查询数据,看到的数据还是原来的数据,查询的数据前后两次都是相同的
则 repeatable read 解决了不可重复读取数据的问题
关于幻读
---设置李四窗口的隔离级别为repeatable read
set session transaction isolation level repeatable read;
---查看设置是否成功
select @@transaction_isolation;
---开启事物读取数据
start transaction;
select * from account;
---打开一个新的查询窗口张三开启事物,插入数据并提交
start transaction;
insert into account (username,balance) values("王五",2000);
commit;
李四设置隔离级别为repeatable read,但是没有产生幻读问题,因为MySQL中InnoDB存储引擎使用MVCC机制
解决了repeatable read 下的幻读问题
设置隔离级别为read committed,演示幻读问题
---设置李四窗口的隔离级别为read committed
set session transaction isolation level read committed;
---查看设置是否成功
select @@transaction_isolation;
---开启事物读取数据
start transaction;
select * from account;
---打开一个新的查询窗口张三开启事物,插入数据并提交
start transaction;
insert into account (username,balance) values("赵柳",2000);
commit;
设置隔离级别为read committed,开启事物查询数据,另一个事物进行插入数据并提交
在查询事物中再次查询,结果可以查询到刚刚插入的数据,则产生幻读
4,serializable(可串行化)
该隔离级别是最高的,同时花费也是最高的,性能最低,一般很少用
因为在该隔离级别下,事物按着顺序执行
---设置李四窗口的隔离级别为serializable
set session transaction isolation level serializable;
---查看设置是否成功
select @@transaction_isolation;
---开启事物读取数据
start transaction;
select * from account;
---打开一个新的查询窗口开启事物,插入数据并提交(查看是否让插入)
start transaction;
insert into account (username,balance) values("小三",2000);
commit;
**
3,视图的简介
**
1,概念:从一张表或者多张表或其他视图中查询的结果构建出来的虚拟表
同表一样,由一系列的行和列组成
数据库中只存放视图的定义,查询的数据并不放在视图中,这些数据依然存放在表中
数据库中表的数据发生变化,视图中相应的数据也会发生变化
2,为什么要使用视图
a,将复杂的查询创建成视图,查询时直接使用查询视图,大大简化了用户的操作难度
b,视图可以对机密数据提供安全保护
对于表中某些行或者列的数据,不想让某些用户看到,可以使用视图
在设计数据库应用系统时,对不同的用户定义不同的视图,就可对机密数据进行保护
c,视图提供了数据逻辑独立性
数据逻辑独立性:数据库中表结构(添加了字段或者添加了关系)发生变化,用户的应用程序不会受影响
视图的结构一旦确定后,如果表结构发生了变化,对视图没有任何影响,这样视图在一定程度上就提供了
对数据的逻辑独立性
**
4,创建视图
**
1,语法如下:
create [or replace] view 视图名称
as 查询语句
[with check option]
or replace 可选的
with check option是可选的,用来限制插入或者更新到视图中的记录
2,在单表上创建视图
---将查询emp表中empno,ename,job,sal信息的结果创建视图
create or replace view view_emp
as select empno,ename,job,sal from emp;
---查询视图
select * from view_emp;
3,在多张表上创建视图(连接表查询)
---将查询雇员中雇员编号,雇员姓名及雇员所在部门信息创建视图
多表查询语句
select e.ename,e.empno,d.*
from emp e join dept d on e.deptno=d.deptno
创建视图
create view view_emp_dept
as select e.ename,e.empno,d.*
from emp e join dept d on e.deptno=d.deptno;
查询视图
select * from view_emp_dept;
4,其他视图上创建视图
---查询视图view_emp_dept中的empno,ename,dname创建新视图
create view view_emp_dept_new
as select empno,ename,dname from view_emp_dept;
---查询新视图
select * from view_emp_dept_new;
**
5,查看视图
**
1,show tables 查看(既可以查看到表,也可以查看到视图)
例如show tables;
2,使用show table status查看
语法为show table status [from|in 数据库名称] [like 视图名称]
例如查看视图及表状态
show table status;
查看某个库里面指定的视图名称
show table status from test like "view_emp_dept";
3,desc 视图名
查看视图名为view_emp_dept的结构
desc view_emp_dept;
4,show create view 语句来查看视图
查看view_emp
show create view view_emp;
5,在views表中查看
在MySQL中库为information_schema中有views表
**
6,修改和删除视图
**
1,修改 create or replace
---修改view_emp视图,去掉sal这一列
create or replace view view_emp
as select empno,ename,job from emp;
2,修改 alter view 视图名 as 查询语句 [with check option]
select * from view_emp_dept;
---使用alter view 修改视图 view_emp_dept,添加雇员入职时间和职位字段;
alter view view_emp_dept as select e.ename,e.empno,e.hiredate,e.job,d.*
from emp e join dept d on e.deptno=d.deptno
3,删除视图 语法为 drop view [if exists] 视图名1,视图名2,视图名3...
---创建两个视图,使用drop删除
create view view1 as select empno,ename from emp;
create view view2 as select * from dept;
---使用drop删除视图
drop view if exists view1,view2;
**
7,更新视图
**
更新视图操作包括:删除,修改,添加数据
因为视图是一张虚表,数据库只保存视图的定义,不保存视图的数据
所以更新视图的操作实际上就是对表的删除 修改 添加操作
1,使用insert 语句对视图添加数据
---查看视图view_emp的结构,并向视图view_emp插入数据
select * from view_emp;
insert into view_emp (empno,ename,job) values(888,"lili","manager");
2,修改视图中的数据
---修改view_emp中lili的职位为clerk
update view_emp set job="clerk" where ename="lili";
select * from emp;
select * from view_emp;
3,使用delete删除视图中的数据
delete from view_emp where ename="lili";
select * from view_emp;
select * from emp;
**
8,用户管理
**
1,用户的登录与退出
语法为:mysql -h hostname -p port -u username -p dbname -e
-h hostname 指定要连接MySQL的主机名或者ip,hostname就是具体的主机名或者ip的值
-p port 指定要连接MySQL的端口 默认端口为3306
-u username 连接数据库的用户名
-p 连接时候提示要输入的密码
dbname 指定要连接的数据库,可以不指定,登录以后使用use来选择
-e 指定要执行的sql语句 例如 select语句
进入mysql数据库的命令有如下两种方式:
mysql -uroot -p
mysql -h127.0.0.1 -p3306 -uroot -p
例如 Windows+r 输入cmd 都是dos窗口使用root命令登录test数据库
mysql -h127.0.0.1 -p3306 -uroot -p test
mysql -h127.0.0.1 -uroot -p test
例如登录时,直接指定密码
mysql -h127.0.0.1 -p3306 -uroot -proot test
mysql -h127.0.0.1 -uroot -proot test
例如在dos窗口,使用root登录 test库,执行select * from dept语句''
mysql -h127.0.0.1 -uroot -p test -e "select * from emp" 不指定密码 注意:必须是英文双引号
mysql -h127.0.0.1 -uroot -proot test -e "select * from emp" 指定密码
使用quit或者exit直接退出数据库
2,创建用户
语法为 create user '用户名'@'ip地址' identified by '密码' [, '用户名'@'ip地址' identified by '密码']....
---创建test01用户,密码也是test01
create user 'test01'@'localhost' identified by 'test01';
---查看用户 是在mysql.user 中保存的
select * from mysql.user;
创建用户成功以后,使用test01用户名登录mysql
通过dos窗口登录
mysql -h127.0.0.1 -utest01 -ptest01
3,删除用户
a,drop user '用户名'@'ip地址';
---删除test01用户
drop user 'test01'@'localhost';
b,删除mysql.user表中的数据 使用delete
语法delete from mysql.user where user=''and host=''
---删除test03用户
delete from mysql.user where user='test03'and host='localhost';
4,修改密码
a,root用户修改自己的密码
1,使用mysqladmin命令修改 语法为mysqladmin -u username -p password ''新密码"
例如将root用户的密码修改为123
mysqladmin -uroot -p password "123"
创建成功之后使用新密码进行登录
mysql -uroot -p123
2,使用set修改自己的密码
首先root用户登录 再使用set修改自己的密码
语法为set password="新密码"; 注意后面的英文;不能少
b,root用户修改普通用户的密码
1,使用set修改普通用户的密码 语法:set password for 'username'@'hostname' = '新密码'
---创建test01用户,密码也是test01
create user 'test01'@'localhost' identified by 'test01';
---查看用户 是在mysql.user 中保存的
select * from mysql.user;
---修改test01的密码
set password for'test01'@'localhost'='123';
在dos窗口使用新密码进行登录
mysql -utest01 -p123
2,使用alter user修改密码 语法结构为 alter user 'username'@'hostname' identified by '新密码'
---将test01的密码修改为test01
alter user 'test01'@'localhost' identified by 'test01';
在dos窗口使用新密码进行登录
mysql -utest01 -ptest01
c,普通用户可以修改自己的密码 语法为set password = '新密码'
---创建test01用户,密码也是test01
create user 'test66'@'localhost' identified by 'test66';
---dos界面使用test66登录mysql
mysql -utest66 -ptest66
---修改自己的密码为123
set password='123';
---使用新密码登录
mysql -utest66 -ptest123
5,找回root用户密码
1,关掉mysql 命令为net stop MySQL80
2,创建一个文本文件,内涵一条密码修改命令
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; (此处新密码为123)
3,初始化该文件 dos命令
mysqld --init-file=e:/mysqlpwd.txt --console
4,执行完之后,关闭cmd 再重新开启cmd
5,开启mysql net start MySQL80
6,登录root用户 mysql -uroot -p123
6,权限管理
1,查看权限 语法为 show grants for 'username'@'hostname'
---查看root用户的权限
show grants for 'root'@'localhost';
2,授予权限 语法如下
grant 权限类型1,权限类型2...on 数据库名.表名
to 'username'@'hostname' [,'username'@'hostname]
[with grant option]
扩展:所有库中所有表可以使用通配符 *.*
---新创建test05用户,密码为test05
create user 'test05'@'localhost' identified by 'test05';
---授予所有库下所有表的查询 删除权限
grant select,delete on *.* to 'test05'@'localhost' with grant option
---查看test05的权限
show grants for 'test05'@'localhost'
3,收回权限
revoke 权限类型1,权限类型2...on 数据库名.表名
from 'username'@'hostname' [,'username'@'hostname]...
---收回test05的delete权限
revoke delete on *.* from 'test05'@'localhost'
**
9,三大范式
**
1,什么是范式 NF NormalForm
范式是符合某一种设计要求的总结
要设计一个结构合理的关系型数据库,必须满足一定的范式