MySQL之知识点（十二）

一、自增列：AUTO-INCREMENT

作用：某个字段的值自增

特点和要求：

1. 一个表最多只能有一个自增长列
2. 当需要产生唯一标识符或顺序值时，可设置增长
3. 自增长列约束的列必须是键列（主键列、唯一键列）
4. 自增约束的列的数据类型必须是整数类型
5. 如果自增列指定了0和NULL，会在当前最大值的基础上自增自增，如果自增列手动指定了具体值，直接赋值为具体值

代码实现：

1，在CREATE TABLE 时添加

#建表
creat table test4(
id int primary key auto_increment,
last_name varchar(15)
);

#添加
insert into test4(last_name)
values('TOM');

select * from test4;

(注意：开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们添加数据时，就不要给主键对应的字段去赋值了）

2，在ALTER TABLE 时添加

#建表
create table test5(
id int primary key,
last_name varchar(15)
);

#查看信息
desc test5;

#添加自增
alter table test5 modify id int auto_increment;

desc test5;

二、FOREIGN KEY（外键）约束

作用：限定某个表的某个字段的引用完整性

1，在CREATE TABLE 时添加

#先创建主表
create table dept1(
dept_id int,
dept_name varchar(15)
);

#添加主键约束
alter table dept1 add primary key(dept_id);
desc dept1;

#在创建从表
create table emp1(
emp_id int primary key auto_increment,
emp_name varchar(15),
department_id int,

#表级约束
constraint fk_emp1_dept_id foreign key(department_id) references dept1(dept_id)
);

desc emp1;

select * from information_schema.table_constraints where table_name='emp1';

演示：

#添加失败
insert into emp1 values(1001,'TOM',10);

#添加成功（在主表dept1中添加10号部门后，就可以在从表中添加10号部门的员工）
insert into dept1 values(10,'IT');

insert into emp1 values(1001,'TOM',10);

#删除失败
delete from dept1 where dept_id=10;

#更新失败
update dept1 set dept_id=20 where dept_id=10;

2,约束等级

 演示：on update cascade on delete set null

#创建主表
create table dept(
did int primary key,         #部门编号
 dname varchar(50)           #部门名称
);

#创建从表
create table emp(
eid int primary key,         #员工编号
ename varchar(5),            #员工名称
deptid int,                  #员工所在部门

foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

#添加数据
#先添加主表数据 
insert into dept values(1001,'教学楼');
insert into dept values(1002,'财务楼');
insert into dept values(1003,'咨询楼');

#添加从表数据
insert into emp values(1,'张三',1001);  #添加这条数据时，要求部门表有1001部门
insert into emp values(2,'李四',1002);
insert into emp values(3,'王五',1003);

select * from dept;

select * from emp;

#更新修改
update dept set did=1004 where did=1002;
select * from dept;

select * from emp;

#删除
delete from dept where did=1004;

select * from dept;

select * from emp;

结论：对于外键约束，最好采用：‘on update cascade on delete restrict’ 的方式

三、CHECK（检查）约束与DEFAULT（默认值）约束

CHECK约束作用：检查某个字段的值是否符合XX要求，一般指的是值的范围。

#建表
create table test6(
id int,
last_name varchar(15),
salary decimal(10,2) check (salary>2000)
);

#添加数据
#添加失败

insert into test6 values(1,'TOM',1500);
select * from test6;

#添加成功
insert into test6 values(1,'TOM1',2500);
select * from test6;

DEFAULT约束作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显示赋值，则赋值为默认值。

四、视图

1，常见的数据库对象

2，视图理解

视图作用：一方面可以帮助我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

视图的理解：

• 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是SQL中的一个重要概念
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表
• 视图的创建和删除只影响视图本身，不影响对应的基表，但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
• 向视图提供数据内容地语句为SELECT语句，可以将视图理解为存储起来地SELECT语句
• 视图，是向用户提供基表数据的一种表现形式。通常情况下，小型项目地数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸现出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升效率。

                                                     3，创建视图（VIEW）

#准备工作
create database dbtest1;

use dbtest1;

create table emp as select * from atguigudb.employees;

select * from emp;

desc emp;

#针对于单表

#确定视图中字段名的方式1：
create view vu_emp1 as select employee_id emp_id,last_name lname,salary from emp;
#查询语句中字段的别名会作为视图中字段的名称

select * from vu_emp1;

#确定视图中字段名的方式2：
create view vu_emp2（emp_id,name,monthly_sal)  #小括号内字段个数与SELECT中字段个数相同
as select employee_id ,last_name ,salary from emp;

select * from vu_emp2;


#针对于多表

create view vu_emp_dept
as
select e.employee_id,e.department_id,d.department_name
from emps e join depts d
on e.'department_id'=d.'department_id';

select * from vu_emp_dept;

 基于视图创建视图：

create view vu_emp3
as
select employee_id,last_name
from vu_emp1;

select * from vu_emp3;

4，查看视图

语法1：查看数据库的表对象、视图对象

show tables; 

语法2：查看视图结构

describe 视图名称; 

语法3：查看视图的属性信息

show table status like '视图名称'; 

语法4：查看视图 的详细定义信息

show create view 视图名称; 

5，更新视图中的数据（视图中的行和底层基本表中的行之间必须一对一的关系）

#选择视图
select * from vu_emp1;

select employee_id,last_name,salary from emps;

#更新视图数据,会导致基表中数据的修改
update vu_emp1 set salary=2000 where employee_id=101;

#更新表中的数据也会导致视图中数据的修改
update emps set salary=10000 where employee_id=101;

#删除视图中的数据,也会导致表中的数据删除
delete from vu_emp1 where employee_id=101;

 当然也有些不可以更新的视图：

 （注意：虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据，对视图数据的修改，都是通过对实际数据表里的数据的操作来完成的）

6，修改视图

#方式1
create or replace view vu_emp1

as

select employee_id,last_name,salary,email from emps where salary>7000;

#方式2

alter view vu_emp1

as

select employee_id,last_name,salary,email,hire_data from emps;