day05（约束，标识列，TCL，视图）

约束

-- 约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
   NOT NULL：非空，用于保证该字段的值不能为空
   比如姓名、学号等
   DEFAULT:默认，用于保证该字段有默认值
   比如性别
   PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
   比如学号、员工编号等
   UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空,null可以多个
   比如座位号
   CHECK:检查约束【mysql中不支持】
   比如年龄、性别
   FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
      在从表添加外键约束，用于引用主表中某列的值
   比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
   1.创建表时
   2.修改表时
约束的添加分类：
   列级约束：
      六大约束语法上都支持，但外键约束没有效果
   表级约束：
      除了非空、默认，其他的都支持
主键和唯一的大对比：
      保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
   主键 √     ×             至多有1个           √，但不推荐
   唯一 √     √             可以有多个          √，但不推荐
外键：
   1、要求在从表设置外键关系
   2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
   3、主表的关联列必须是一个key（一般是主键或唯一）
   4、插入数据时，先插入主表，再插入从表
   删除数据时，先删除从表，再删除主表
*/
use mysqltest;
#一、创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
*/
create table majr(
    id int primary key
);
create table stu_info(
    id int primary key ,
    stuname varchar(20) not null unique,
    gender char(1) check ( gender = '男' or gender = '女'),
    seat int unique ,
    age int default 18,
    major int references majr(id)
);
desc stu_info;
#2.添加表级约束
/*

语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名)
*/
create table stu_info2(
                id int,
                stuname varchar(20) not null unique,
                gender char(1) check ( gender = '男' or gender = '女'),
                seat int unique ,
                age int default 18,
                major int,
                constraint primary key (id),
                constraint fro foreign key (major) references majr(id)
);
desc stu_info2;
SHOW INDEX FROM stu_info1;
#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
create table if not exists stu_info3(
    id int,
    major int
);
desc stu_info3;
alter table stu_info1 modify column id int primary key ;
alter table stu_info3 add foreign key (major) references majr(id);
create table if not exists stu_info4(
                                        id int unique
);
insert into stu_info4 values (null),(null);-- 正确
insert into stu_info4 values (1),(1);-- 错误
#三、修改表时删除约束
desc stu_info;
-- 删除主键
alter table stu_info drop primary key ;-- 正确的方式
alter table stu_info modify id int;-- 错误的方式
-- 删除唯一
alter table stu_info drop index stuname;
-- 删除外键
alter table stu_info1 drop foreign key stu_info1_ibfk_1;
SHOW INDEX FROM stu_info1;

标识列

#标识列
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值
*/
create table stu_info6(
                         id int primary key auto_increment,
                         stuname varchar(20) not null unique
);
insert into stu_info6(stuname) values ('11'),('222');
truncate stu_info6;
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=3;
insert into stu_info6(stuname) values ('111'),('22');

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='郭襄'
结束事务的语句;
*/
create database if not exists mytest;
use mytest;
create table if not exists count1(
    name varchar(20),
    countsum int
);
insert into count1 values ('张三丰',1000),('郭襄',1000);
show variables like 'autocommit';
#1.演示事务的使用步骤
#开启事务
set autocommit = 0;
start transaction ;
#编写一组事务的语句
update count1 set countsum = 1500 where name = '郭襄';
update count1 set countsum = 500 where name = '张三丰';
#结束事务
commit ;
rollback ;
#2.演示事务对于delete和truncate的处理的区别
#开启事务
set autocommit = 0;
start transaction ;
#编写一组事务的语句
delete from count1;
#结束事务
rollback ;
#开启事务
set autocommit = 0;
start transaction ;
#编写一组事务的语句
truncate count1;
#结束事务
rollback ;
#3.演示savepoint 的使用
set autocommit = 0;
savepoint a;
update count1 set countsum = 1500 where name = '郭襄';
update count1 set countsum = 500 where name = '张三丰';
rollback to a;

视图

-- 视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比
       创建语法的关键字       是否实际占用物理空间         使用
视图 create view           只是保存了sql逻辑         增删改查，只是一般不能增删改
表      create table      保存了数据                 增删改查
*/
#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/
use myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
select last_name,department_name,job_title
from employees inner join departments on employees.department_id = departments.department_id
inner join jobs on employees.job_id = jobs.job_id
where last_name like '%a%';
-- 视图的写法
create view v1 as
select last_name,department_name,job_title
from employees inner join departments on employees.department_id = departments.department_id
               inner join jobs on employees.job_id = jobs.job_id;
select *
from v1 where last_name like '%a%';
#2.查询各部门的平均工资级别
select department_id,job_grades.grade_level
from (select avg(salary) avgs, department_id
      from employees group by department_id) ag
    inner join job_grades on ag.avgs between lowest_sal and highest_sal;
select avg(salary) department_id
from employees  group by department_id;
#①创建视图查看每个部门的平均工资
create view my3 as
select avg(salary) avs,department_id
from employees group by department_id;
#②使用
select job_grades.grade_level,avs
from my3 inner join job_grades on avs between lowest_sal and highest_sal;
#3.查询平均工资最低的部门信息
create view my4 as
select avg(salary) avs,department_id
from employees group by department_id;
select min(avs),department_name
from my4 inner join departments on my4.department_id = departments.department_id;
#二、视图的修改
#方式一：
/*
create or replace view  视图名
as
查询语句;
*/
create view my6 as select * from employees;
create or replace view my6 as
select * from departments;
select *
from myemployees.my6;
#方式二：
/*
语法：
alter view 视图名
as
查询语句;
*/
alter view myemployees.my6 as select * from employees;
select *
from myemployees.my6;
#三、删除视图
/*

语法：drop view 视图名,视图名,...;
*/
drop view myemployees.my6;
#四、查看视图
-- 看表
desc myemployees.my3;
-- 看语句
show create view myemployees.my3;
#五、视图的更新
#具备以下特点的视图不允许更新
#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
#②常量视图
#③Select中包含子查询
#④join
#⑤from一个不能更新的视图
#⑥where子句的子查询引用了from子句中的表

---