MySQL6天学习笔记——day05

文章目录

• 7. DDL语言的学习
• 8. TCL语言的学习
• 9.视图

---

7. DDL语言的学习

7.3 常见约束

-- 常见约束

/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束
	1.NOT NULL：非空，用于保证该字段的值不能为空
		比如姓名、学号等
	2.DEFAULT:默认，用于保证该字段有默认值
		比如性别
	3.PRIMARY KEY:主键，用于保证该字段的值具有唯一性(不可重复)，并且非空
		比如学号、员工编号等
	4.UNIQUE:唯一，用于保证该字段的值具有唯一性(不可重复)，可以为空
		比如座位号
	5.CHECK:检查约束 [mysql中不支持]
		比如年龄、性别
	6.FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
	在从表添加外键约束，用于引用主表中某列的值
		比如学生表的专业编号，员工表的部门编号，员工表的工种编号
	
添加约束的时机：
	1.创建表时
	2.修改表时
	
约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
		
	表级约束：
		除了非空、默认外，其他的都支持
		
主键(PRIMARY KEY)和唯一(UNIQUE)的大对比：

		保证唯一性  	是否允许为空    一个表中可以有多少个   	是否允许组合
主键	   √		     	 ×		          至多有1个         √，但不推荐
唯一	   √		       √		          可以有多个        	√，但不推荐

外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表中的关联列必须是一个key（一般是主键或唯一键）
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表
*/

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
)

CREATE DATABASE students;
use students;
-- 1.创建表时添加约束

-- 1.1 添加列级约束
/*
语法：
		直接在字段名和字段类型后面 追加约束类型即可
		只支持：默认、非空、主键、唯一
*/
CREATE TABLE stuinfo(
			id INT PRIMARY KEY, #主键约束
			stuName VARCHAR(20) NOT NULL, #非空约束
			gender CHAR(1) CHECK(gender = '男' OR gender = '女'), #检查约束
			seat INT UNIQUE, #唯一约束，可以为空
			age INT DEFAULT 18, #默认约束
			majorId INT REFERENCES major(id) #外键，引用major表的id字段
);

CREATE TABLE major(
			id INT PRIMARY KEY,
			majorName VARCHAR(20)
);
-- 查看表结构
DESC stuinfo;
-- 查看表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

-- 1.2 添加表级约束
/*
语法：在各个字段的最下面
		[constraint 约束名] 约束类型(字段名) 
*/
-- 方式一：
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
				id INT,
				stuName VARCHAR(20),
				gender CHAR(1),
				seat 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) #外键
);

-- 方式二：省略 CONSTRAINT 约束名
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
				id INT,
				stuName VARCHAR(20),
				gender CHAR(1),
				seat INT,
				majorid INT,
				
				PRIMARY KEY(id), #主键
				UNIQUE(seat), #唯一键
				CHECK(gender = '男' OR gender = '女'), #检查约束
				FOREIGN KEY(majorid) REFERENCES major(id) #外键
);

SHOW INDEX FROM stuinfo;

-- 通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY, #主键
	stuname VARCHAR(20) NOT NULL, 
	sex CHAR(1),
	age INT DEFAULT 18, #默认18
	seat INT UNIQUE, #唯一键
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);

-- 2.修改表时添加约束
/*
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;

-- 2.1 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

-- 2.2 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

-- 2.3 添加主键
-- ①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
-- ②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

-- 2.4 添加唯一键
-- ①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
-- ②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

-- 2.5 添加外键(CONSTRAINT 键名，也可以省略)
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorid ) REFERENCES major ( id );

-- 2.5.1 添加外键时,添加级联删除(ON DELETE CASCADE)
/*
添加级联删除后，删除主表的记录，与之关联的从表的记录也会被一起删除
*/
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorid ) REFERENCES major ( id ) ON DELETE CASCADE;

-- 2.5.2 添加外键时,添加级联置空(ON DELETE SET NULL)
/*
添加级联置空后，删除主表的记录，与之关联的从表的字段会被置空
*/
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorid ) REFERENCES major ( id ) ON DELETE SET NULL;

-- 3.修改表时删除约束

-- 3.1 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) [NULL];

-- 3.2 删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

-- 3.3 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

-- 3.4 删除唯一
/*
alter table 表名 drop index 索引名;
*/
ALTER TABLE stuinfo DROP INDEX seat;

-- 3.5 删除外键
/*
alter table 表名 drop foreign key 约束名
*/
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

7.3.1 常见约束练习

-- 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_dept_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);

/*
						位置								支持的约束类型          是否可以起约束名
列级约束：  列的后面          语法都支持，但外键没效果         不可以
表级约束：  所有列的下面      默认和非空不支持，其他支持    可以(但是对主键没效果，主键键名默认就是PRIMARY KEY)   
*/

7.4 标识列

-- 标识列
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
5、可以通过首次手动插入值，设置起始值
*/
-- 1.创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY,
	NAME FLOAT UNIQUE AUTO_INCREMENT, 
	seat INT 
);

TRUNCATE TABLE tab_identity;

INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

-- 设置每次自动增长的步长
SET auto_increment_increment=3;

-- 2.修改表时设置标识列
/*
alter table 表名 modify column 字段名 字段类型 约束 auto_increment;
*/
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

-- 3.修改表时删除标识列
-- 删除表字段id的主键,因为一张表只能有一个主键，所以主键可以默认不写
ALTER TABLE tab_identity MODIFY COLUMN id int; 

8. TCL语言的学习

8.1 事务

-- 事务
/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

案例：转账

张三丰   1000
郭襄	    1000

update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'

事务的特性：
ACID
原子性(Atomicity)：一个事务是不可再分割的，要么都执行要么都不执行
一致性(Consistency)：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性(Isolation)：一个事务的执行不受其他事务的干扰，多个事务互相隔离
持久性(Durability)：一个事务一旦提交，则会永久的持久化到本地

事务的创建
1.隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id =1;

2.显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

-- 关闭当前事务的自动提交
set autocommit=0;

具体步骤：
步骤1：开启事务
set autocommit=0;
start transaction; 可以省略的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit; 提交事务
rollback; 回滚事务

savepoint 节点名; 设置保存点
一般搭配rollback to 节点名;

事务的隔离级别：
															脏读					不可重复读					幻读
read uncommitted(读未提交):	  	√							√								√
read committed(读已提交):  			×							√								√
repeatable read(可重复读): 			×							×								√
serializable(可串行化):	  			×             ×               ×

脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事物"更新"的数据
不可重复读：一个事务多次读取数据，结果都不一样
幻读：一个事务读取了其他事务还没有提交的数据，读到的是其他事物"插入"的数据

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别(mysql8.0+)
select @@transaction_isolation;
查看隔离级别(mysql 5.7)
select @@tx_isolation;
设置隔离级别
set session transaction isolation level 隔离级别;

开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'

update 表 set 郭襄的余额=1500 where name='郭襄' 
结束事务的语句;
*/

-- 查看mysql的存储引擎
SHOW ENGINES;

-- 查看自动提交的状态
SHOW VARIABLES LIKE 'autocommit';

/*
创建表和插入数据
USE test;

DROP TABLE IF EXISTS account;

CREATE TABLE account(
				id INT PRIMARY KEY AUTO_INCREMENT,
				username VARCHAR(20),
				balance DOUBLE
);

INSERT INTO account(username,balance)
VALUES ('张无忌',1000),('赵敏',1000);
*/
-- 1.演示事务的使用步骤

-- 1.1 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 1.2 编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username = '张无忌';
UPDATE account SET balance = 1000 WHERE username = '赵敏';
-- 1.3 结束事务
ROLLBACK; #回滚事务
COMMIT; #提交事务

SELECT * FROM account;

-- 2.演示事务对于delete和truncate的处理区别

-- 2.1 演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK; #delete支持回滚，回滚后数据还存在

-- 2.2 演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK; #truncate不支持回滚，回滚后数据也是删除了

-- 3.演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id = 1;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id = 2;

ROLLBACK TO a; #回滚到保存点处,也就是说id=1的信息会被删除，而id=2的信息会被保存下来

SELECT * FROM account;

9.视图

-- 视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成
好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性

比如：类似于java中的方法

			创建语法的关键字		   是否实际占用物理空间									使用

视图		create view					只是保存了sql逻辑								一般用于查询

表			create table					保存了数据											增删改查
*/

-- 案例：查询姓张的学生名和专业名
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 '张%';

-- 1.创建视图
/*
语法：
		create view 视图名
		as
		查询语句;
*/

-- 案例1：查询姓名中包含a字符的员工名、部门名和工种信息
-- ①创建视图
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER 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,j.grade_level
FROM myv2 
INNER JOIN job_grades j
ON myv2.ag BETWEEN j.lowest_sal AND j.highest_sal;

-- 案例3：查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag ASC LIMIT 1;

-- 案例4：查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag ASC LIMIT 1;

SELECT d.* FROM myv3 m
INNER JOIN departments d
ON m.department_id = d.department_id;

-- 2.视图的修改

-- 2.1 方式一：
/*
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;

-- 2.2 方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;

-- 3.删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;

-- 4.查看视图
DESC myv3;

SHOW CREATE VIEW myv3;

-- 5.视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1
SELECT * FROM employees;

-- 5.1 插入数据
INSERT INTO myv1 VALUES('张飞','[email protected]');

-- 5.2 修改数据
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

-- 5.3 删除数据
DELETE FROM myv1 WHERE last_name = '张无忌';

-- 5.4 具备以下特点的视图不允许更新

-- 5.4.1 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

-- 案例：group by
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; #报错，视图包含了group by

-- 5.4.2 常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

SELECT * FROM myv2;

-- 下面的sql语句也可以算是常量视图
SELECT (SELECT MAX(salary) FROM employees) 最高工资;

-- 更新
UPDATE myv2 SET NAME='lucy'; #报错，视图里面包含常量

-- 5.4.3 select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

SELECT * FROM myv3;

-- 更新
UPDATE myv3 SET 最高工资=100000; #报错，视图包含子查询

-- 5.4.4 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'); #报错，语句包含join

-- 5.4.5 from后面加一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

SELECT * FROM myv5;

-- 更新
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60; #报错

-- 5.4.6 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'; #报错