mysql插入记录到有约束的表_MySQL学习笔记-约束以及修改数据表

最新推荐文章于 2023-06-24 02:14:17 发布

约翰福特

最新推荐文章于 2023-06-24 02:14:17 发布

阅读量180

点赞数

文章标签： mysql插入记录到有约束的表

本文链接：https://blog.csdn.net/weixin_33711795/article/details/113610960

版权

MySQL学习笔记-约束以及修改数据表

约束：

按功能划为：

NOT NULL , PRIMARY KEY , UNIQUE KEY , DEFAULT , FOREIGN KEY

按数据列的数目划为：

表级约束，列级约束

修改数据表：

针对字段的操作：添加 / 删除字段、修改列定义、修改列名称等

针对约束的操作：添加 / 删除各种约束

针对数据表的操作：数据表更名(两种方式)

*** 约束保证数据的完整性和一致性

*** 约束分为表级约束和列级约束

*** 约束类型包括：

NOT NULL(非空约束)

PRIMARY KEY(主键约束)

UNIQUE KEY(唯一约束)

DEFAULT(默认约束)

FOREIGN KEY(外键约束)

1.外键约束的要求解析

*** 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

*** 数据表的存储引擎只能为InnoDB。

*** 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

主键是默认自带索引的而外键列创建时一般参照的是带有主键那一列因此如果外键列没有创建索引的话就会被MYSQL根据参照列的索引创建一个索引

*** 外键列是不可以以一个没有索引的列作为参照列的

1.参照列必须要创建一个索引(如果用的是主键默认自带索引所以不用创建)

2.外键列随意如果不创建索引会被创建

【1】编辑数据表的默认存储引擎,配置文件/etc/my.cnf

1 [mysqld]2 default-storage-engine=INNODB

【2】创建省份数据表，查看存储引擎mysql> USE test;

mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL

-> );

mysql> SHOW CREATE TABLE provinces;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb0 | CREATE TABLE `provinces` (

`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`pname` varchar(20) NOT NULL, PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+

【3】创建用户数据表,其中外键列必须和参照列必须有相似的数据类型mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) -> );

注意：其中有外键列的users表为子表，有参照列id列provinces为父表创建主键时自动创建索引，查看父表自动创建的1个索引mysql> SHOW INDEXES FROM provinces\G;*************************** 1. row ***************************

Table: provinces

Non_unique: 0

Key_name: PRIMARYSeq_in_index: 1

Column_name: id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

1 row in set (0.00 sec)

若外键列没有索引，自动创建：mysql> SHOW INDEXES FROM users\G;

*************************** 1. row ***************************

Table: users

Non_unique: 0

Key_name: PRIMARYSeq_in_index: 1

Column_name: id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

*************************** 2. row ***************************

Table: users

Non_unique: 1

Key_name: pid

Seq_in_index: 1

Column_name: pid

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

2 rows in set (0.00 sec)

2.外键约束的参照操作

*** cascade ：从父表删除或更新且自动删除或更新子表中匹配的行

*** set null ：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

*** restrict ：拒绝对父表的删除或更新操作

*** no action ：标准的SQL的关键词，在MySQL中与restrict相同

CASCADE例：

mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL

-> );

mysql> CREATE TABLE users1( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE

-> );

mysql> INSERT provinces(pname) VALUES('Tom');

mysql> INSERT provinces(pname) VALUES('John');

mysql> INSERT provinces(pname) VALUES('Driver');

mysql> INSERT users1(username,pid) VALUES('Huang',2);

mysql> INSERT users1(username,pid) VALUES('Li',3);

mysql> INSERT users1(username,pid) VALUES('Pan',3);

mysql> INSERT users1(username,pid) VALUES('He',1);

mysql> INSERT users1(username,pid) VALUES('Long',2);

mysql> SELECT * FROM users1;+----+----------+------+| id | username | pid |+----+----------+------+| 1 | Huang | 2 || 2 | Li | 3 || 5 | Pan | 3 || 6 | He | 1 || 7 | Long | 2 |+----+----------+------+mysql> SELECT * FROM provinces;

+----+--------+| id | pname |+----+--------+| 1 | Tom || 2 | John || 3 | Driver |+----+--------+ mysql> DELETE FROM provinces WHERE id=3;

mysql> SELECT * FROM provinces;+----+-------+| id | pname |+----+-------+| 1 | Tom || 2 | John |+----+-------+mysql> SELECT * FROM users1;

+----+----------+------+| id | username | pid |+----+----------+------+| 1 | Huang | 2 || 6 | He | 1 || 7 | Long | 2 |+----+----------+------+

3.表级约束和列级约束

*** 对一个数据列建立的约束，称为列级约束

*** 对多个数据列建立的约束，称为表级约束

*** 列级约束既可以在列定义时声明，也可以在列定义后声明

*** 表级约束只能在列定以后声明

4.修改数据表

添加单列ALTER TABLE tbl_name(数据表名称) ADD [COLUMN] col_name(列名) column_definition(列定义) [FIRST(插入列到最前面)|AFTER(插入列到指定列的后方) col_name(列名)(如果不写FIRST和AFTER，则插入到最后一列)]

例：mysql> SHOW COLUMNS FROM users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | |+----------+----------------------+------+-----+---------+----------------+

添加age列到最后一列：mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;