MySQL 数据库管理系统学习Day05
- DDL语言的学习
- 常见约束
- 标识列
- TCL语言的学习
- 事务
- 视图的学习
DDL语言的学习
常见约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
- NOT NULL :非空,用于保证该字段的值不能为空
比如姓名、学号等… - DEFAULT:默认,用于保证该字段有默认值
比如性别 - PRIMARY KEY:主键,用于保证该字段的值的唯一性,并且非空
比如学号、员工编号等 - UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号 - CHECK:检查约束【mysql中不支持】不报错但没效果,兼容性好
比如年龄、性别 - FOREIGN KEY :外键 ,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引入主表中某列的值
比如:学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
- 创建表时
- 修改表时,数据添加之前
约束添加的分类:
- 列级约束:
六大约束语法上都支持,但外键约束没有效果 - 表级约束:
除了非空、默认,其他的都支持
主键和唯一约束的对比:
保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 | |
主键 | ✔ | ✖ | 至多有一个 | ✔,但不推荐 |
唯一 | ✔ | ✔ | 可以有多个 | ✔,但不推荐 |
外键:
1、要求在从表上设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、要求主表中的关联列必须是一个key(一般是主键、或唯一、再或者是外键)
4、插入数据时,先插入主表,再插入从表。
删除数据时,先删除从表,再删除主表。一、创建表时添加约束
1.添加列级约束
语法:
直接在字段名和类型后面追加 约束类型即可
只支持:默认、非空、主键、唯一
CREATE DATABASE stu`students`dents;
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空、唯一 可以加多个约束
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查 没用MySQL不支持
seat INT UNIQUE,#唯一
age INT DEFAULT 18, #默认约束
majorId INT REFERENCES major(id) #外键 其实此约束没效果
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;
#查看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,
seat2 INT,
PRIMARY KEY(id,stuname),#主键 MySQL中primary改名没效果,改了也是默认名PRIMARY
UNIQUE (seat),#唯一键
# primary key(seat2), Multiple primary key defined
CHECK (gender='男' OR gender='nv'),#MySQL不支持
FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
SHOW INDEX FROM stuinfo;
DESC stuinfo;
位置 | 支持的约束类型 | 是否可以取约束名 | |
列级约束 | 列的后面 | 列的后面 语法都支持,但外键没效果 | 不可以 |
表级约束 | 列的后面 | 所有列的下面 默认和非空不支持,其他支持 | 可以(主键没效果) |
二、修改表时添加约束
- 添加列级约束
alter table 表明 modify column 字段名 字段类型 新约束;- 添加表级约束
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(10) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 19;
#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; #或者不写
#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、标识列必须和主键搭配吗?不一定,但要求是一个key
2、一个表中可以有多少个标识列?至多一个
3、标识列的类型只能是数值型,一般是int
4、标识列可以通过SET auto_increment_increment=3;设置步长
可以通过手动插入值,设置起始值
一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT ,
#警告AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release.
NAME FLOAT UNIQUE AUTO_INCREMENT
#,只能有一个自增长列
#seat int unique auto_increment
);
TRUNCATE TABLE tab_identity;
#手动插入一个'起始值'
INSERT INTO tab_identity VALUES (10,'john');
INSERT INTO tab_identity(NAME) VALUES ('Tom');
SELECT * FROM tab_identity;
#MySQL不支持设置起始值
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=3;
#二、修改表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT ,
NAME FLOAT
);
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
TCL语言的学习
事务
Transaction Control Language 事务控制语言
- 事务:
一个或一组SQL语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。- 含义:
通过一组逻辑操作单元(一组DML——sql语句),将数据从一种状态切换到另外一种状态- 事务的存储引擎:
通过show engines;来查看mysql支持的存储引擎。
在mysql中用的最多的存储引擎有:innodb,myisam ,memory 等。其中innodb支持事务,而myisam、memory等不支持事务特点:
(ACID)
原子性(Atomicity):事务是一个不可分割的工作单位,要么都执行,要么都回滚
一致性(Consistency):保证数据的状态,操作前和操作后保持一致
隔离性(Isolation):多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰
持久性(Durability):一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改事务的开启和终止
- 以第一个 DML 语句的执行作为开始
- 以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句(自动提交)
用户会话正常结束
系统异常终止了相关步骤:
1、开启事务
2、编写事务的一组逻辑操作单元(多条sql语句)
3、提交事务或回滚事务事务的分类:
- 隐式事务,没有明显的开启和结束事务的标志
比如
insert、update、delete语句本身就是一个事务- 显式事务,具有明显的开启和结束事务的标志
1. 开启事务
取消自动提交事务的功能
set autocommit = 0;
2. 编写事务的一组逻辑操作单元(多条sql语句)
insert
update
delete
3. 提交事务或回滚事务
commit
rollback使用到的关键字
set autocommit=0;
start transaction;
执行事务的语句;
commit;
rollback;
savepoint 断点;
commit to 断点;
rollback to 断点;
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的处理区别
SET autocommit=0;
START TRANSACTION;
#delete 可以回滚
DELETE FROM account;
ROLLBACK;
#truncate 不能回滚
SET autocommit =0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
SELECT * FROM account;
3.演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=2;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
事务的隔离级别:
- 事务并发问题如何发生?
当多个事务同时操作同一个数据库的相同数据时 - 事务的并发问题有哪些?
脏读:一个事务读取到了另外一个事务未提交的数据
不可重复读:同一个事务中,多次读取到的数据不一致
幻读:一个事务读取数据时,另外一个事务在数据中插入了一些数据,导致第一个事务读取到的数据多了几行
通过设置事务的隔离级别可以避免事务的并发问题:
- READ UNCOMMITTED 不能避免并发问题
- READ COMMITTED 可以避免脏读
- REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
- SERIALIZABLE 可以避免脏读、不可重复读和幻读
设置隔离级别:
set session|global transaction isolation level 隔离级别名;
session:设置当前事务级别
global:设置数据库系统的全局事务级别
查看隔离级别:
select @@tx_isolation; MySQL5.5版本
select @@transaction_isolation;MySQL8.0版本
视图的学习
含义:理解成一张虚拟的表。
mysql 5.1版本出现的新特性,通过表动态生成的数据视图和表的区别:
创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
视图 | create view | 只是保存了SQL逻辑 | 增删改查,一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |
视图的好处:
- sql语句提高重用性,效率高
- 和表实现了分离,提高了安全性
视图的创建
语法:
CREATE VIEW 视图名
AS
查询语句;
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`;
视图的增删改查
1、查看视图的数据 ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name=‘Partners’;
2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES(‘虚竹’,90);
3、修改视图的数据
UPDATE my_v4 SET last_name =‘梦姑’ WHERE last_name=‘虚竹’;
4、删除视图的数据
DELETE FROM my_v4;
#三、删除视图
/*
语法:drop view 视图名,视图名,...; #需要具备权限root用户
*/
DROP VIEW myv1,myv2,myv3;
#四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
某些视图不能更新
包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
#五、视图的更新
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;
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';
#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
#具备以下特点的视图不允许更新
#①包含以下关键字的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;
#更新 #The target table myv1 of the UPDATE is not updatable
UPDATE myv1 SET m = 9000 WHERE department_id=10;
#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME = 'jack';
#③select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资;
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=1000;
#④join 所有的连接都不能更新sql92语句的也不能
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';
#Can not insert into join view 'myemployees.myv4' without fields list
INSERT INTO myv4 VALUES('陈真','xx');
#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资 = 1000;
#⑥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 = 1000 WHERE last_name = 'K_ing';
视图逻辑的更新
二、视图的修改
#方式一:
/*
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 test_v1,test_v2,test_v3;视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;