与MySQL的零距离接触—约束以及修改数据表

时间:2022-02-12 14:43:46

慕课网视频笔记

1.MySQL外键约束的要求解析:

MySQL外键约束:

  • 约束保证数据的完整性和一致性
  • 约束分为表级约束和列级约束(约束只针对某一个字段这种是列级约束,针对两个或两个以上是表级约束)

约束类型包括(按照功能划分):

  • NOT NULL(非空约束)
  • PRIMARY KEY(主键约束)
  • UNIQUE KEY(唯一约束)
  • DEFAULT(默认约束)
  • FOREIGN KEY(外键约束)

外键约束作用:

  • 保证数据一致性,完整性。
  • 实现一对一或一对多关系。

外键约束的要求:

  • 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
  • 数据表的存储引擎只能为InnoDB。
  • 外键列和参照列必须具有相似的数据类型。若为数字的话,长度以及是否有符号位必须相同;而字符的长度则可以不同。
  • 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。

如果参照列不存在索引的话,MySQL不会自动创建索引。但如果参照列为主键的话,则会自动创建索引。
主键在创建的同时会自动创建索引,所以参照列其实已经有了索引。
而外键列上没有创建索引,Mysql则会自动创建索引。

外键列 不创建索引的话 MySql会自动创建,外键列和参照列最大的区别是 外键是另一个表的主键(主键是默认有索引的),而参照列不一定是主键 所以没有默认索引。

主键与自增之间的关系:

  • 1.自增的列必须是设置主键
  • 2.主键的列不一定设置自增
  • 3.数据库读取顺序默认是根据主键正序排序的

修改存储引擎:

  • 第一种方法:在my.ini的[mysqld]下面加入default-storage-engine=innodb;然后重启mysql服务。通过show engines 命令或者是show create table table_name命令查看当前存储引擎。如果无法修改,请使用第二种方法。
  • 第二种方法:在创建表的过程中指定存储引擎, 或者创建后修改存储引擎。
-- 第一种方法
create table table_name( id smallint unsigned auto_increment primary key, name varvhar(20);
)engine=innode;

-- 第二种方法
create table table_name( id smallint unsigned auto_increment primary key, name varvhar(20);
)
alter table table_name engine=innode;

创建外键约束:

-- 法一:已经建好表后添加外键

alter tableadd constraint 外键约束名称 foreign key (列) references 表(主键列);

-- 法二:建表时添加外键

CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, Id_P int, PRIMARY KEY (O_Id), --主键 FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) --外键);

外键列实例:

-- 省份表proviences
create table proviences( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pname VARCHAR(20) NOT NULL;
);
-- 用户表users
create table users( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(10) NOT NULL;
    pid SMALLINT unsigned, --外键列(pid)和参照列(id)必须有相似的数据类型(SMALLINT),且符号位(unsigned)必须一直。
    FOREIGN KEY (pid) PREFERENCES proviences (id) --设置外键pid,参考proviences表中的id字段。
);

unsigned表示无符号,也就是非负数,只能用于整型数据。
有外键的表是子表,参照的表(proviences)是父表。
pid是外键列,id是参照列。

查看数据库信息:

use test;
-- 查看创建数据库的信息
show create database test;
-- 查看创建数据表的信息
show create table tvalues;

与MySQL的零距离接触—约束以及修改数据表

与MySQL的零距离接触—约束以及修改数据表

附注:想换成网格的形式展示可以用SHOW INDEXS FROM provinces\G;

2.外键约束的参照操作

外键约束的参照的操作:(就是在创建外键约束之后,对于更新表的时候的一些规定)

  • CASCADE:从父表删除或更新 且 自动删除或更新子表中匹配的行。
  • ON DELETE CASCADE 从父表删除,自动删除子表中匹配的行。
  • ON UPDATE CASCADE 从父表更新,自动更新子表中匹配的行。
  • ON UPDATE CASCADE
  • SET NULL:从父表删除或更新行时,并设置子表中的外键列表为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL。
  • RESTRICT:拒绝对父表的删除或更新操作。
  • NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同。
create table proviences( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pname VARCHAR(20) NOT NULL );

create table users( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(10) NOT NULL, pid SMALLINT unsigned, #外键列(pid)和参照列(id)必须有相似的数据类型(SMALLINT),且符号位(unsigned)必须一直。 FOREIGN KEY (pid) PREFERENCES proviences (id) ON DELETE CASCADE #设置外键pid,参考proviences表中的id字段。 #ON DELETE CASCADE表示 从父表中删除或更新时自动删除或更新子表中匹配的行。 );

注:对于插入数据,只能在父表中插入记录,因为子表是参照父表的。

-- proviences表
insert proviences(pname) values("A"), insert proviences(pname) values("B"), insert proviences(pname) values("C"), -- 对于自增的proviences表来说,id为123 -- users表 insert users(username,pid) values("Tom",3), #插入成功(pid 3 关联 id 3 也就是上面表中 C) insert users(username,pid) values("JONE",1), #插入成功 insert users(username,pid) values("rose",3), #插入成功 insert users(username,pid) values("Leo",7), #插入失败,因为pid参照proviences表中的id,id只有123,所以插入失败。 -- 删除父表中的数据 DELETE FROM proviences WHERE id = 3, -- 此时users表中的Tom和rose也会被删除。

与MySQL的零距离接触—约束以及修改数据表

与MySQL的零距离接触—约束以及修改数据表

在外键约束当中,在实际的开发过程中,我们很少使用物理的外键约束,很多都去使用逻辑的外键约束,因为物理的外键约束只有INNODB这种引擎才会支持。

所以说,我们在实际的项目开发中,我们不去定义物理的外键,所谓的逻辑外键指的是就是我们在定义两张表的结构的时候,我们是按照存在的某种结构的方式去定义,但是不去使用FOREIGN KEY这个关键词来定义。

3.表级约束与列级约束

对一个数据建立的约束,称为列级约束。
对多个数据建立的约束,称为表级约束。
列级约束即可以在列定义时声明,也可以在列定义后声明。
表级约束只能在列定义后声明。

在实际开发中,用列级约束比较多,表级约束很少用。

除此之外,在所有的约束中,并不是说每种约束都存在着表级或列级约束,其中,NOT NULL 非空约束,DEFAULT约束这两种约束就不存在表级约束,它们只有列级约束,而对于其他的三种,像主键,唯一,外键,它们都可以存在表级和列级约束。

4.修改数据表-添加/删除列

修改数据表:

  • 列的添加
  • 列的删除
  • 约束的添加
  • 约束的删除
-- 添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name] ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

-- 添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...) -- 删除列 ALTER TABLE tbl_name DROP [COLUMN] col_name -- 删除多列 ALTER TABLE tbl_name DROP col_name, DROP col_name -- 删除列同时添加列 ALTER TABLE tbl_name DROP col_name, ADD [COLUMN] col_name column_definition;

5.修改数据表-添加约束

-- 添加主键约束(sympol 是在添加CONSTRAINT 之后,我们可以为约束添加名称,index_type是索引类型)
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (index_col_name,...) ALTER TABLE user2 ADD id smallint unsigned;
ALTER TABLE user2 ADD CONSTRAINT PRIMARY KEY(id);

-- 添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) ALTER TABLE user2 ADD UNIQUE(username);

-- 添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREG REFERENCE_DEFINITION -- 添加、删除默认约束 ALTER TABLE tbl_name 

主键约束与唯一约束:
每个表可以有多个UNIQUE约束,但是每个表只能有一个PRIMARY KEY约束。

主键约束:

  • 约束唯一标识数据库表中的每条记录;
  • 主键必须包含唯一的值;
  • 主键列不能包含NULL值;
  • 每个表都应该有一个主键,并且每个表只能有一个主键。

附注:一个表只能有一个主键,但是一个主键可以包含多个字段,也就是多个字段组成一个主键。复合主键就是主键含有一个以上的字段组成。

-- 复合主键
create table test( name varchar(19), id number, value varchar(10), primary key(name,id) );

唯一约束:

  • 约束唯一标识数据库表中的每条记录;
  • UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一性的保证。
  • PRIMARY KEY约束拥有自动定义的UNIQUE约束。
  • 唯一约束的字段可以为空值(NULL)

默认约束:

  • 默认值
  • 当插入记录时,如果没有明确为字段赋值,则自动赋予默认值

AUTO_INCREMENT:

  • 自动编号,且必须与主键一起使用。但是主键不一定要和AUTO_INCREMENT一起使用。也就是使用AUTO_INCREMENT的地方必须是主键。
  • 默认情况下,起始值为1,每次的增量为1。

无符号位:
UNSIGNED,比如一个字节占8位,如11111111,如果是无符号位则所有位数表示数值的大小,即是255,若是有符号位,做高位则表示负数,0为正数,即剩下的7为表示数值大小1111111,是127,由于是负数即-127,所以TINYINT UNSIGNED 0

-- 删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY -- 删除唯一约束 ALTER TABLE tbl_name DROP {INDEX|KEY} index_name -- 查看约束名字 SHOW INDEXS FROM user2; -- 删除外键约束 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol -- 查看外键约束的名称 SHOW CREATE TABLE user2;

与MySQL的零距离接触—约束以及修改数据表

与MySQL的零距离接触—约束以及修改数据表

附注:在删除主键索引后,数据库会给数据表一个默认主键索引,这时候如果其他字段定义了唯一约束并且为not null,则那个字段会变为主键,但不是真正意义上的主键,是一种索引。多个唯一约束的情况下,按唯一约束添加的先后顺序。但是用show create table table_name命令查看的话,会看到它并不是主键,只是一个索引,删除它仍需要使用ALTER TABLE tbl_name DROP INDEX index_name,而不能采用DROP PRIMARY KEY,你如果再添加一个外键的话,那一个会自动再次变成外键的。

约束和索引,前者是用来检查数据的准确性,后者用来实现数据查询的优化,目的不同。

7.修改数据表-修改列定义和更名数据表

-- 修改列定义(FIRST|AFTER是可以修改列的位置)
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name] -- A.id字段移动到第一列 ALTER TABLE user2 MODIFY id SMIALLINT UNSIGNED NOT NULL FIRST;
-- B.修改数据类型(由大类型改成小类型可能会造成数据的丢失,例如1-100范围数据改成1-20范围数据)
ALTER TABLE user2 MODITY id TINYINT UNSIGNED;

-- 修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

-- 修改数据表
-- 数据表更名
# 方法1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name ALTER TABLE user2 RENAME user3;
# 方法2(可以为多张表更名)
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name] ... RENAME TABLE user3 TO user2;

总结:

约束

  • 按功能划为:NOT NULL,PRIMARY KEY,UNIQUE KEY,DEFAULT,FOREIGN KEY
  • 按数据列的数目划分:表级约束,列级约束

修改数据表

  • 针对字段的操作:添加/删除字段、修改列定义、修改列名称等
  • 针对约束的操作:添加/删除各种约束
  • 针对数据表的操作:数据表更名(两种方式)

与MySQL的零距离接触—约束以及修改数据表