《SQL高级应用和数据仓库基础（MySQL版）》学习笔记 ·004【标识列、外键、复制表】

一、标识列

介绍
标识列，又称为自增列，可以不手动插入数值，系统提供默认的序列值。

特点

• 标识列必须和一个key搭配（key：主键、唯一键、外键、……）
• 一个表最多只有一个标识列
• 标识列的类型只能是数值类型

Tips：如果需要查看当前自增列的计数值，可以通过show create table 表名;在创表语句的末尾进行观察

设置标识列（举例）

• 创表格时添加

  create table users(
  	id int primary key auto_increment,
  	name varchar(20)
  );
  

• 修改表时添加

  alter table 表名 modify column id int primary key auto_increment;
  

• 修改表时删除

  alter table 表名 modify column id int primary key;
  

二、外键约束

1、外键约束创建要求

• 外键约束施加在从表（外键表）中
• 从表的外键列与主表的关联列类型要求一致或兼容，名字无要求
• 主表的关联列必须为一个key（一般为主键或唯一键，外键也可以但是没有意义）
• 插入数据时，先插入主表，再插入从表
• 删除数据时，先删除从表，再删除主表

2、级联操作

级联操作就是——当你操作主表的数据时，自动对从表进行相应操作。

级联更新

on update cascade

级联删除

on delete cascade

级联置空

on delete set null

不进行操作

on delete no action

拒绝操作

on delete restrict

语法

alter table 表名 add [constraint 外键名]
	foreign key(外键字段名) references 主表名称(引用列)
	on update cascade;
	
alter table 表名 add [constraint 外键名]
	foreign key(外键字段名) references 主表名称(引用列)
	on delete cascade;

使用示例
打算构造一个选课系统，包括[学生表]、[课程表]、[选课表]

• 学生表

  create table if not exists student(
  	sno int auto_increment,
  	sname varchar(20) not null,
  	gender enum('男', '女') default '男',
  	constraint student_sno_pk primary key(sno)  -- 可以主动给主键起名字
  );
  

• 课程表

  create table if not exists course(
  	cno int primary key auto_increment,  -- 也可以不主动给主键起名字（但是会有默认的主键名字）
  	cname varchar(14) not null unique,
  	cteacher set('张三', '李四', '王五') not null,
  	credit int default 0
  );
  

• 选课表

  create table if not exists sc(
  	scid int primary key auto_increment,
  	sno int,
  	cno int,
  	constraint sc_sno_fk foreign key(sno) references student(sno)  -- 可以主动给外键起名字
  		on update cascade
  		on delete cascade,
  	foreign key(cno) references course(cno)  -- 也可以不主动给外键起名字（但是会有默认的外键名字）
  );
  

三、复制表

方法一：在create table语句末尾加上like子句
这种方式可以将表的结构复制过来，包括约束对应的索引（不包括数据）

create table 新表 like 源表;

方法二：在create table语句末尾加上select子句

• 在create table语句末尾加上select * from 源表
  这种方式可以将表的结构复制过来，并且可以将表格的数据全部拷贝过来（不包括约束）

  create table 新表 [as] select * from 源表;
  

• 在create table语句末尾加上select * from 源表 where 1=0
  这种方式可以将表的全部结构复制过来（不包括约束和数据）

  create table 新表 [as] select * from 源表 where 1=0;