本文主要是介绍Day77（常见约束，标识列，事务，视图，变量），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
常见预约
#常见约束/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束NOT NULL：非空，用于保证该字段的值不能为空比如姓名、学号等DEFAULT:默认，用于保证该字段有默认值比如性别PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空比如学号、员工编号等UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空比如座位号CHECK:检查约束【mysql中不支持】比如年龄、性别FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值在从表添加外键约束，用于引用主表中某列的值比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：1.创建表时2.修改表时
约束的添加分类：列级约束：六大约束语法上都支持，但外键约束没有效果表级约束：除了非空、默认，其他的都支持
主键和唯一的大对比：保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合主键 √ × 至多有1个 √，但不推荐唯一 √ √ 可以有多个 √，但不推荐
外键：1、要求在从表设置外键关系2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求3、主表的关联列必须是一个key（一般是主键或唯一）4、插入数据时，先插入主表，再插入从表删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名 (字段名 字段类型 列级约束,字段名 字段类型,表级约束
);#一、创建表时添加约束
#1.添加列级约束/*
语法：
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
*/
CREATE DATABASE students;CREATE TABLE stuinfo (id INT PRIMARY KEY,stuName VARCHAR (7) NOT NULL,gender CHAR (1) CHECK (gender = '男'OR gender = '女'),#检查seat INT UNIQUE,age INT DEFAULT 18,majorId INT REFERENCES major (id) #外键(在列级约束中没有效果)
);CREATE TABLE major (id INT PRIMARY KEY,major_name VARCHAR (10) UNIQUE
);#查看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 un UNIQUE (seat),CONSTRAINT fk_stu_maj FOREIGN KEY (majorid) REFERENCES major (id)
);SHOW INDEX FROM stuinfo;#通用的写法：★CREATE TABLE IF NOT EXISTS stuinfo (id INT PRIMARY KEY,stuname VARCHAR (20),gender 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
);#1.添加非空约束ALTER TABLE stuinfoMODIFY COLUMN stuname VARCHAR (20) NOT NULL;#2.添加默认约束ALTER TABLE stuinfoMODIFY COLUMN age INT DEFAULT 18;#3.添加主键
#①列级约束ALTER TABLE stuinfoMODIFY COLUMN id INT PRIMARY KEY;#②表级约束ALTER TABLE stuinfoADD PRIMARY KEY (id);#4.添加唯一
#①列级约束ALTER TABLE stuinfoMODIFY COLUMN seat VARCHAR (10) UNIQUE;#②表级约束ALTER TABLE stuinfoADD UNIQUE (seat);#5.添加外键ALTER TABLE stuinfoADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id);#三、修改表时删除约束
#1.删除非空约束ALTER TABLE stuinfoMODIFY COLUMN stuname VARCHAR (10) NULL;#2.删除默认约束ALTER TABLE stuinfoMODIFY COLUMN age INT;#3.删除主键ALTER TABLE stuinfoDROP PRIMARY KEY;#4.删除唯一ALTER TABLE stuinfoDROP INDEX seat;#5.删除外键ALTER TABLE stuinfoDROP FOREIGN KEY fk_stuinfo_major;#案例讲解：
#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
CREATE TABLE emp2 (
id INT
）;
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
#2.向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
ALTER TABLE emp2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
#3.向表emp2中添加列dept_id，
# 并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT for_key FOREIGN KEY(dept_id) REFERENCES dept2(id);
/*位置 支持的约束类型 是否可以起约束名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）
*/
标识列
#标识列/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值
*/
#一、创建表时设置标识列DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity (id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR (10)
);TRUNCATE TABLE tab_identity;INSERT INTO tab_identity (id, NAME)
VALUES(NULL, 'john');INSERT INTO tab_identity (NAME)
VALUES('lucy');SELECT*
FROMtab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment = 3;
事务
#TCL/*
Transaction Control Language 事务控制语言
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如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 节点名;设置保存点事务的隔离级别：脏读 不可重复读 幻读
read uncommitted：√ √ √
read committed： × √ √
repeatable read： × × √
serializable × × ×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;#开启事务SET autocommit = 0;START TRANSACTION;#编写一组事务的语句UPDATEaccount
SETbalance = 1000
WHERE username = '张无忌';UPDATEaccount
SETbalance = 1000
WHERE username = '赵敏';#结束事务ROLLBACK;#commit;SELECT*
FROMaccount;#2.演示事务对于delete和truncate的处理的区别SET autocommit = 0;START TRANSACTION;DELETE
FROMaccount;ROLLBACK;#3.演示savepoint 的使用SET autocommit = 0;START TRANSACTION;DELETE
FROMaccount
WHERE id = 25;SAVEPOINT a;#设置保存点DELETE
FROMaccount
WHERE id = 28;ROLLBACK TO a;#回滚到保存点SELECT*
FROMaccount;
视图
#视图/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
*/
#案例：查询姓张的学生名和专业名SELECT*
FROM`stuinfo`;CREATE VIEW v1 AS
SELECTstuname,major_name
FROMstuinfo sINNER JOIN major mON s.majorid = m.id;SELECT*
FROMv1
WHERE stuname LIKE '张%';#一、创建视图/*
语法：
create view 视图名
as
查询语句;
*/
USE myemployees;#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建CREATE VIEW view1 AS
SELECTlast_name,department_name,job_title
FROMemployees eINNER JOIN departments dON e.department_id = d.department_idINNER JOIN jobs jON e.job_id = j.job_id;SELECT*
FROMview1
WHERE last_name LIKE '%a%';#2.查询各部门的平均工资级别CREATE VIEW view2 AS
SELECTAVG (salary) ag_sal,department_id
FROMemployees
GROUP BY department_id;SELECTgrade_level,department_id
FROMview2JOIN job_gradesON ag_sal BETWEEN lowest_salAND highest_sal;#3.查询平均工资最低的部门信息SELECT*
FROMview2
ORDER BY ag_sal
LIMIT 1;#4.查询平均工资最低的部门名和工资SELECTdepartment_name,ag_sal
FROMview2 vJOIN departments dON v.`department_id` = d.`department_id`
ORDER BY ag_sal
LIMIT 1;#二、视图的修改#方式一：/*
create or replace view 视图名
as
查询语句;
*/
SELECT*
FROMmyv3;CREATE OR REPLACE VIEW myv3 AS
SELECTAVG (salary),job_id
FROMemployees
GROUP BY job_id;#方式二：/*
语法：
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3ASSELECT*FROMemployees;#三、删除视图/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW myv1,
myv3;#四、查看视图DESC view1;SHOW CREATE VIEW view1;#五、视图的更新CREATE OR REPLACE VIEW myv1 AS
SELECTlast_name,email,salary * 12 * (1+ IFNULL (commission_pct, 0)) "annual salary"
FROMemployees;CREATE OR REPLACE VIEW view1 AS
SELECTlast_name,email
FROMemployees;#1.插入SELECT*
FROMview1;INSERT INTO view1
VALUES('张三丰', 'zsf@gmail.com');#2.修改UPDATEview1
SETlast_name = '张无忌'
WHERE last_name = '张三丰';#3.删除DELETE
FROMview1
WHERE last_name = '张无忌';#具备以下特点的视图不允许更新
#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union allCREATE OR REPLACE VIEW myv1 AS
SELECTMAX (salary) m,department_id
FROMemployees
GROUP BY department_id;SELECT*
FROMmyv1;#更新UPDATEmyv1
SETm = 9000
WHERE department_id = 10;#②常量视图CREATE OR REPLACE VIEW myv2 AS
SELECT'john' NAME;SELECT*
FROMmyv2;#更新UPDATEmyv2
SETNAME = 'lucy';#③Select中包含子查询CREATE OR REPLACE VIEW myv3 AS
SELECTdepartment_id,(SELECTMAX (salary)FROMemployees) 最高工资
FROMdepartments;#更新SELECT*
FROMmyv3;UPDATEmyv3
SET最高工资 = 100000;#④joinCREATE OR REPLACE VIEW myv4 AS
SELECTlast_name,department_name
FROMemployees eJOIN departments dON e.department_id = d.department_id;#更新SELECT*
FROMmyv4;UPDATEmyv4
SETlast_name = '张飞'
WHERE last_name = 'Whalen';INSERT INTO myv4
VALUES('陈真', 'xxxx');#⑤from一个不能更新的视图CREATE OR REPLACE VIEW myv5 AS
SELECT*
FROMmyv3;#更新SELECT*
FROMmyv5;UPDATEmyv5
SET最高工资 = 10000
WHERE department_id = 60;#⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6 AS
SELECTlast_name,email,salary
FROMemployees
WHERE employee_id IN(SELECTmanager_idFROMemployeesWHERE manager_id IS NOT NULL);#更新SELECT*
FROMmyv6;UPDATEmyv6
SETsalary = 10000
WHERE last_name = 'k_ing';#案例讲解：
#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%';
SELECT * FROM emp_v1;
#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE VIEW emp_v2
AS
SELECT MAX(salary)max_sal,department_id
FROM employees
GROUP BY department_id
HAVING max_sal>12000;
SELECT d.*
FROM departments d
JOIN emp_v2 e
ON e.department_id=d.department_id;SELECT d.* FROM departments d
INNER JOIN (
SELECT MAX(salary) max_sal,department_id
FROM employees
GROUP BY department_id
HAVING max_sal>12000
) max_dep
ON max_dep.department_id = d.department_id;
变量
#变量/*
系统变量：全局变量会话变量
自定义变量：用户变量局部变量
*/
#一、系统变量/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一：
set global|【session】系统变量名=值;
方式二：
set @@global|【session】系统变量名=值;*/
#1》全局变量/*
作用域：针对于所有会话（连接）有效，但不能跨重启
*/
#①查看所有全局变量SHOW GLOBAL VARIABLES;#②查看满足条件的部分系统变量SHOW GLOBAL VARIABLES LIKE '%char%';#③查看指定的系统变量的值SELECT@@global.autocommit;#④为某个系统变量赋值SET @@global.autocommit = 0;SET GLOBAL autocommit = 0;#2》会话变量/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量SHOW SESSION VARIABLES;#②查看满足条件的部分会话变量SHOW SESSION VARIABLES LIKE '%char%';#③查看指定的会话变量的值SELECT@@autocommit;SELECT@@session.tx_isolation;#④为某个会话变量赋值SET @@session.tx_isolation = 'read-uncommitted';SET SESSION tx_isolation = 'read-committed';#二、自定义变量/*
说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）
*/
#1》用户变量/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/
#赋值操作符：=或:=
#①声明并初始化SET @变量名 = 值;SET @变量名 := 值;SELECT@变量名 := 值;#②赋值（更新变量的值）
#方式一：SET @变量名 = 值;SET @变量名 := 值;SELECT@变量名 := 值;#方式二：SELECT字段 INTO @变量名
FROM表;#③使用（查看变量的值）SELECT@变量名;#2》局部变量/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#①声明DECLARE 变量名 类型;DECLARE 变量名 类型 【DEFAULT 值】;#②赋值（更新变量的值）
#方式一：SET 局部变量名 = 值;SET 局部变量名 := 值;SELECT局部变量名 := 值;#方式二：SELECT字段 INTO 具备变量名
FROM表;#③使用（查看变量的值）SELECT局部变量名;#案例：声明两个变量，求和并打印
#用户变量SET @a = 3;SET @b = 6;SET @sum = @a + @b;SELECT@sum;#局部变量DECLARE m INT DEFAULT 1;DECLARE n INT DEFAULT 1;DECLARE `sum` INT;`sum` = m + n
SELECT`sum`;#用户变量和局部变量的对比作用域 定义位置 语法 用户变量 当前会话 会话的任何地方 加 @符号，不用指定类型 局部变量 定义它的BEGIN END中
BEGINEND的第一句话 一般不用加 @,需要指定类型
这篇关于Day77（常见约束，标识列，事务，视图，变量）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
