《尚硅谷》MySQL系统课程一共6天,下面介绍第5天的学习内容,主要涉及常见约束、标识列、TCL(事务控制语言)和视图。干货满满,跟着课程的进度来的,可能篇幅略长,但是看完一定会有收获,那我们现在开始吧。
目录
TCL(Transaction Control Language) 事务控制语言
常见约束
含义:
一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性。
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空。比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值。比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空。比如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空。比如座位号
CHECK:检查约束【mysql中不支持-不报错但是没有效率】。比如年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值;在从表添加外键约束,用于引用主表中某列的值。比如学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束在语法上都支持,但外键约束执行没有效果
表级约束:
除了非空、默认,其他的都支持
主键和唯一的大对比:
保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 | |
主键 | √ | × | 至多有1个 | √,但不推荐 PRIMARY KEY(id,stuname) |
唯一 | √ | √ | 可以有多个 | √,但不推荐 UNIQUE(seat,seat2) |
外键:
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
4、插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表
语法:
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
代码分析:
CREATE DATABASE students;
#一、创建表时添加约束
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可。
只支持:默认、非空、主键、唯一,CHECK和外键都不支持
*/
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查IN
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引,包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
#2.添加表级约束
/*
语法:在各个字段的最下面
【constraint 约束名(根据需要自己取)】 约束类型(字段名)
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;
#通用的写法:★
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;#NULL可以省略
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
【案例讲解】常见约束
#1.向表emp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
#2. 向表dept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)
ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
#3. 向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束,与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
列级约束和表级约束的区别:
位置 | 支持的约束类型 | 是否可以起约束名 | |
列级约束 | 列的后面 | 语法都支持,但外键没有效果 | 不可以 |
表级约束 | 所有列的下面 | 默认和非空不支持,其他支持 | 可以(主键没有效果) |
标识列
含义:
又称为自增长列,可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key,如UNIQUE
2、一个表可以有几个标识列?至多一个!
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值。
代码分析:
#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT PRIMARY KEY, #自己控制不重复
NAME VARCHAR(20)
);
INSERT INTO tab_identity(id,NAME) VALUES(1,'john');
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT, #自增
NAME VARCHAR(20)
);
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
CREATE TABLE tab_identity(
id INT,
NAME FLOAT UNIQUE AUTO_INCREMENT, #标识列的类型只能是数值型
seat INT
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;
#查看自增变量-设置
SHOW VARIABLES LIKE '%auto_increment%';
#MySQL仅仅支持设置步长
SET auto_increment_increment=3; #设置步长为3
INSERT INTO tab_identity(id,NAME) VALUES(10,'john'); #手动设置从10开始
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;
TCL(Transaction Control Language) 事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
案例引入:转账
张三丰 -1000
郭襄 +1000
update 表 set 张三丰的余额=500 where name='张三丰'
意外-发生中断
update 表 set 郭襄的余额=1500 where name='郭襄'
事务的概念:
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
MySQL中的存储引擎[了解]
1、概念:在mysql中的数据用各种不同的技术存储在文件(或内存)中。
2、通过show engines;来查看mysql支持的存储引擎。
3、在mysql中用的最多的存储引擎有:innodb,myisam ,memory 等。其中innodb支持事务,而myisam、memory等不支持事务。
事务的特性(ACID):
原子性(A):一个事务不可再分割,要么都执行要么都不执行
一致性(C):一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性(I):一个事务的执行不受其他事务的干扰
持久性(D):一个事务一旦提交,则会永久的改变数据库的数据.
事务的创建:
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事务
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3:结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
数据库的隔离级别
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
1、脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段。之后,若T2回滚,T1读取的内容就是临时且无效的。
2、不可重复读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段。之后,若T1再次读取同一个字段,值就不同了。
3、幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后,若T1再次读取同一个表,就会多出几行。
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱。
数据库提供的4种事务隔离级别:
隔离级别 | 描述 |
READ UNCOMMITED(读未提交数据) | 允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现。 |
READ COMMITED(读已提交数据) | 只允许事务读取已经被其他事务提交的变更,可以避免脏读,但不可重复读和幻读问题仍然可能出现。 |
REPEATABLE READ(可重复读) | 确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间, 禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读,但幻读的问题仍然存在。 |
SERIALIZABLE(串行化) | 确保事务可以从一个表中读取相同的行。在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作。所有并发问题都可以避免,但性能十分低下。 |
Oracle支持的2种事务隔离级别:READ COMMITED,SERIALIZABLE。Oracle默认的事务隔离级别为READ COMMITED。
MySQL支持4中事务隔离级别。MySQL默认的事务隔离级别为:REPEATABLE READ。
事务的隔离级别:(一般而言幻读出现在insert)出现即 √
脏读 | 不可重复读 | 幻读 | |
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read | × | × | √ |
serializable | × | × | × |
在MySQL中设置隔离级别
每启动一个 mysql 程序,就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量 @@tx_isolation,表示当前的事务隔离级别。
#查看当前的隔离级别:
SELECT @@tx_isolation;
#设置当前 mySQL 连接的隔离级别:
set transaction isolation level read committed;
#设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;
总结:
mysql中默认第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别:select @@tx_isolation;
设置隔离级别:set session|global transaction isolation level 隔离级别;
开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;
代码分析:
SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;
#1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#commit;
#验证事务操作结果
SELECT * FROM account;
#2.演示事务对于delete和truncate的处理的区别
#演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
SELECT * FROM account; #执行成功了,但是回滚之后数据还在,表明delete支持回滚。
#演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
SELECT * FROM account; #执行成功,回滚之后数据不在,表明truncate不支持回滚。
#3.演示savepoint 的使用---节点、断点
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
视图
含义:
虚拟表,和普通表一样使用。mysql5.1版本出现的新特性,是通过普通表动态生成的数据。比如:舞蹈班和普通班级的对比。
视图的好处:
• 重用sql语句
• 简化复杂的sql操作,不必知道它的查询细节
• 保护数据,提高安全性(只提供一些对方需要的信息,原始表的信息不暴露)
视图和表的对比:
创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
视图 | create view | 只是保存了sql逻辑 | 增删改查,只是一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |
代码分析:
一、创建视图
语法:
create view 视图名
as
查询语句;
#引入案例:查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';
#创建视图
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
#通过视图查看结果
SELECT * FROM v1 WHERE stuname LIKE '张%';
USE myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;#非等值连接
#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
#4.查询平均工资最低的部门名和工资-复用视图
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
二、视图的修改
#方式一:
/*
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM myv3;
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#方式二:
/*
语法:
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
三、删除视图
/*
语法:drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;
四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
【案例讲解】视图
#1.创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%'; #注意分开写的视图里面没有phone_number,所以写一起
#2.创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;
五、视图的更新-增删改
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;#修改的同时原始表也修改了,所以往往给视图添加权限。
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
具备以下特点的视图不允许更新(6种)
#①包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
#报错:[Err] 1288 - The target table myv1 of the UPDATE is not updatable
#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME='lucy';
#报错:[Err] 1288 - The target table myv2 of the UPDATE is not updatable
#③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
#SELECT (SELECT MAX(salary) FROM employees) ;
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
#报错:[Err] 1288 - The target table myv3 of the UPDATE is not updatable
#④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
#更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';#可以执行
INSERT INTO myv4 VALUES('陈真','xxxx');
#报错:[Err] 1394 - Can not insert into join view 'myemployees.myv4' without fields list
#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3; #一个不能更新的视图myv3
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
#报错:[Err] 1288 - The target table myv5 of the UPDATE is not updatable
#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
#报错:[Err] 1288 - The target table myv6 of the UPDATE is not updatable
说明:记录学习笔记,发现有错误欢迎指正!转载请联系我。