mysql-2(外键、DML)

外键

现在有两张表
年级表：grade
id grade_name
id 是grade表的主键
学生表：student
stu_id stu_name gid（外键 引用grade表的id）
此时，grade是主表，student是子表或从表
外键：建立或加强两个表数据之间的链接的一列或多列
通过将表中主键值的一列或多列添加到另一个表中，可创建两个表之间的连接，这个列就成为第二个表的外键
外键主要是用来控制数据库中的数据完整性的，当对一个表的数据进行操作时，和他有关联的一个表或多个表的数据能够同时发生改变
一个表可以有一个主键，也可以没有主键
一个表可以有一个或多个外键，也可以没有外键
定义主键和外键主要是为了维护关系数据库的完整性，总结一下：
1.主键是能确定一条记录的唯一标识。比如，一条记录包括身份证号，姓名，家庭住址等。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
2.外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的某字段，是B表的主键，那他就可以是A表的外键。

创建外键

创建年级表grade
mysql> create table if not exists grade(
-> GradeID int(11) primary key auto_increment,
-> GradeName varchar(50)
-> );

创建表同时增加外键

CREATE TABLE student(
stu_id int(10) unsigned NOT NULL AUTO_INCREMENT,
stu_name varchar(20) NOT NULL,
grade_id int(11) DEFAULT NULL,
gender char(1) DEFAULT ‘男’,
address varchar(2000) DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
IDCard varchar(18) DEFAULT NULL,
PRIMARY KEY (stu_id),
KEY fk_stu_gra_gradeId (grade_id),
CONSTRAINT fk_stu_gra_gradeId FOREIGN KEY (grade_id) REFERENCES grade (GradeID)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
语法：
constraint 约束名/外键名 foreign key(本表字段名) references 主表名(主表字段名/主键)

建表后修改

类型不一致，不能添加外键约束

修改类型后添加外键约束

语法：
alter table 本表名 add constraint 约束名 foreign key(本表字段名) references 主表(主表字段名);

补充：
谁用外键约束自己谁就是从表。
添加数据时，先给主表添加，然后再给从表添加。
如果先给从表添加

删除数据时，先删除从表的，然后再删除主表数据。
如果先删除主表数据

修改从表数据时，外键值必须在主表数据中存在。
如果外键值在主表数据中不存在

删除外键约束

查找约束名

1、show create table student_1;

2、use information_schema;
KEY_COLUMN_USAGE
KEY_COLUMN_USAGE表描述哪些键列具有约束。
KEY_COLUMN_USAGE表有以下列：
CONSTRAINT_CATALOG ：约束所属目录的名称。 该值始终为def。
CONSTRAINT_SCHEMA ：约束所属schema（database）名称
CONSTRAINT_NAME ：约束名称
TABLE_CATALOG ：表所属目录的名称。 该值始终为def。
TABLE_SCHEMA ：表所属schema（database）名称
TABLE_NAME ：具有约束的表的名称
COLUMN_NAME ：具有约束的列的名称。
如果约束是外键，则这是外键的列，而不是外键引用的列。
ORDINAL_POSITION ：列在约束内的位置，而不是列在表中的位置。列位置从1开始编号。
POSITION_IN_UNIQUE_CONSTRAINT：NULL对于唯一和主键约束。对于外键约束，此列是正在引用的表的键中的序号位置。
REFERENCED_TABLE_SCHEMA ：约束引用的schema（数据库）的名称。
REFERENCED_TABLE_NAME ：约束引用的表的名称。
REFERENCED_COLUMN_NAME ：约束引用的列的名称。
查找

删除外键约束

alter table 表名 drop foreign key 约束名；

删除约束索引

alter table 本表名 drop index 约束名;

结构说明：
Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，值是多少。
Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

DML

DML（数据操作语言）
用于操作数据库对象中所包含的数据
包括
INSERT ( 添加数据语句 )
UPDATE ( 更新数据语句 )
DELETE ( 删除数据语句 )

INSERT ( 添加数据语句 )

INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( ‘值1’, ‘值2’, ‘值3’, …)
字段或值之间用英文逗号隔开
“字段1, 字段2…”该部分可省略，但添加的值务必与表结构数据列顺序相对应,且数量一致
可同时插入多条数据，values 后用英文逗号隔开
mysql> insert into tb_score(stu_id,course_id,scores) values(1,1,10),
-> (1,2,20),
-> (1,3,30),
-> (1,4,40),
-> (1,5,50),
-> (1,6,60);

有默认值，比如性别默认男，可以输入’男’或者default

UPDATE ( 更新数据语句 )

UPDATE 表名
SET column_name = value [ , column_name2 = value2, …. ] [ WHERE condition ];
column_name 为要更改的数据列
value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果
condition为筛选条件，如不指定则修改该表的所有列数据

DELETE ( 删除数据语句 )

DELETE FROM 表名 [ WHERE condition ];
condition为筛选条件，如不指定则删除该表的所有列数据
TRUNCATE命令
用于完全清空表数据，但表结构、索引、约束等不变
TRUNCATE [TABLE] 表名
区别于DELETE命令
相同
都能删除数据、不删除表结构，但TRUNCATE 速度更快
不同
使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引
使用TRUNCATE TABLE不会对事务有影响
事务：truncate是不可以rollback的，但是delete是可以rollback的；
原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
在没有条件时，DELETE FROM table1 （成功返回条数）或TRUNCATE TABLE table1（成功返回0） 都是清空表。
truncate 不能触发任何Delete触发器。