MySQL---外键、添加删除约束、ON DELETE参照、添加|删除|修改数据

程序员文章站 2022-06-14 13:45:36

...

外键(FOREIGN KEY)

外键约束需要满足一些基本要求：

父表和子表必须是相同的引擎，而且禁止使用临时表
数据表的存储引擎只能是InnoDB
外键列和参照列必须具有相似的数据烈性。其中数字的长度或是否有符号为必须相同。而字符串的长度可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MaSQL将自动创建索引。

显示索引，test表由于同时有主键和外键，所以有两个索引

mysql> SHOW INDEX FROM user \G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user
   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:
Index_comment:
2 rows in set (0.00 sec)

ON DELETE 参照操作

CASCADE 从父表删除或更新，自动删除或更新字表中匹配的行
SET NULL 从父表删除或更新行，并设置字表中的外键列为NULL。如果使用该选项必须保障字表列没有指定NOT NULL
RESTRICT 拒绝对父表的删除或更新曹祖
NO ACTION 标准的SQL关键字，在MySQL中与RESTRICT相同

mysql> CREATE TABLE user2 (
    -> id SMALLINT  UNSIGNED PRIMARY KEY ,
    -> name VARCHAR(20) NOT NULL ,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );

表级约束与列级约束

对一个数据列建立的约束，成为列级约束
对多个数据列建立的约束，成为标记约束。
劣迹约束既可以在列定义时声名，也可以在列定义后声名
表级约束只能在列定义后声名

常见的DEFAULT 和 NOT NULL是列级约束

修改表约束

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] PRIMARY KEY [index_type] (index_col_name,…)

 ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_col_name]] UNIQUE [INDEX|KEY] [index_name][index_type](index_col_name,…)

 ALTER TABLE test ADD CONSTRAINT unique_test UNIQUE KEY(name) ;

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] FOREIGN KEY [index_name](index_col_name,…) REFERENCES table_name(col_name)

mysql> ALTER TABLE test0 ADD CONSTRAINT fk_test0 FOREIGN KEY (data) REFERENCES test(id);

添加、删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT value|DROP DEFAULT}

// 添加默认约束
mysql> ALTER TABLE test ALTER COLUMN name SET DEFAULT 'TOM';
// 删除默认约束
mysql> ALTER TABLE test ALTER COLUMN name DROP DEFAULT;

删除主键约束

**ALTER TABLE tbl_name DROP PRIMARY KEY **
删除主键时，如果主键有AUTO_INCREMENT约束，就不可以删除该主键约束，必须要先删除AUTO_INCREMENT，才可以删除主键约束。因为数据库将具有AUTO_INCREMENT约束的列看成主键

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

// 先查看唯一约束的名字
mysql> SHOW CREATE TABLE test ;
| test  | CREATE TABLE `test` (
  `id` int(50) NOT NULL,
  `name` varchar(10),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_test` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

// 根据唯一约束的名字删除对应的约束
mysql> ALTER TABLE test DROP INDEX unique_test;

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_name

mysql> ALTER TABLE test0 DROP FOREIGN KEY fk_test0;

查看索引

**SHOW INDEX FROM tbl_name **

mysql> SHOW INDEX FROM test0 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test0 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test0 |          1 | fk_test0 |            1 | data        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

\G 将查询结果按列输出

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

mysql> CREATE TABLE user (
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL ,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );

查看表信息

SHOW CREATE TABLE tbl_name

mysql> show create table test0 ;
| test0 | CREATE TABLE `test0` (
  `id` int(50) NOT NULL,
  `data` int(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test0` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

修改数据表

添加、删除列

添加列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [(FIRST) | (AFTER col_name)]

// 插入一条记录
ALTER TABLE tb4 ADD COLUMN age INT(10) UNSIGNED DEFAULT 10 ;

// 在最前面插入一条记录
ALTER TABLE tb4 ADD COLUMN truename VARCHAR(20) NOT NULL FIRST;

// 在sex列后面插入一条记录
mysql> ALTER TABLE tb4 ADD COLUMN password VARCHAR(20) AFTER sex ;

添加多列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition (,ADD [column] col_name2 column_definetion…)

// 添加school、和grade列
mysql> ALTER TABLE test ADD COLUMN school VARCHAR(20),ADD COLUMN grade FLOAT;

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

// 删除grade列
mysql> ALTER TABLE test DROP COLUMN grade ;

同时使用多个操作

ALTER TABLE tbl_name DROP(|ADD) [COLUMN] col_name,DROP(|ADD) [COLUMN] col_name2,…

// 删除grade列 ，添加school列
mysql> ALTER TABLE test ADD COLUMN grade FLOAT ,DROP COLUMN school ;

修改数据表列定义

**ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|ALTER col_name] **
在不改变列名的情况下修改列定义

mysql> ALTER TABLE test MODIFY name VARCHAR(20) DEFAULT 'JERRY';
mysql> DESCRIBE test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | JERRY   |                |
| sex   | varchar(5)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

新的参数中如果没有包含原来的约束，那么原来的约束会被删除

mysql> ALTER TABLE test MODIFY name VARCHAR(30) ;
mysql> DESCRIBE test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| sex   | varchar(5)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

**ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] **
可以修改列名，和列定义

mysql> ALTER TABLE test CHANGE name new_name VARCHAR(20) DEFAULT 'TOM' FIRST ;
mysql> DESCRIBE test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| new_name | varchar(20) | YES  |     | TOM     |       |
| id       | int(20)     | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

修改数据表名称

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name 修改单个表名称
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2…] 修改多个表名称

// 修改单个表名称
mysql> ALTER TABLE test RENAME TO  db_test ;

// 修改多个表名称
mysql> RENAME TABLE db_test TO test ,tb4 TO tb5 ;

MySQL---外键、添加删除约束、ON DELETE参照、添加|删除|修改数据

外键(FOREIGN KEY)

ON DELETE 参照操作

表级约束与列级约束

修改表约束

添加主键约束

添加唯一约束

添加外键约束

添加、删除默认约束

删除主键约束

删除唯一约束

删除外键约束

查看索引

查看表信息

修改数据表

添加、删除列

添加列

添加多列

删除列

同时使用多个操作

修改数据表列定义

修改数据表名称

Android launcher中模拟按home键的实现

Win7系统搜索功能失效解决办法三步修改注册表

Win10 Mobile预览版14977今日推送(附更新、修改、已知内容汇总)

美图秀秀图片怎么添加梦幻的光晕效果?

premiere素材怎么添加亮度键转场效果?

premiere视频怎么添加光源闪烁的效果?

Axure网页怎么添加树状控件?

blender多个物体怎么添加到group组?

js删除节点的方法（JSfor循环删除指定节点）

javascript创建对象的几种方式（详解javascript基本数据类型）

MySQL---外键、添加删除约束、ON DELETE参照、添加|删除|修改数据

外键(FOREIGN KEY)

ON DELETE 参照操作

表级约束与列级约束

修改表约束

添加主键约束

添加唯一约束

添加外键约束

添加、删除默认约束

删除主键约束

删除唯一约束

删除外键约束

查看索引

查看表信息

修改数据表

添加、删除列

添加列

添加多列

删除列

同时使用多个操作

修改数据表列定义

修改数据表名称

Android launcher中模拟按home键的实现

Win7系统搜索功能失效解决办法 三步修改注册表

Win10 Mobile预览版14977今日推送(附更新、修改、已知内容汇总)

美图秀秀图片怎么添加梦幻的光晕效果?

premiere素材怎么添加亮度键转场效果?

premiere视频怎么添加光源闪烁的效果?

Axure网页怎么添加树状控件?

blender多个物体怎么添加到group组?

js删除节点的方法（JSfor循环删除指定节点）

javascript创建对象的几种方式（详解javascript基本数据类型）

Win7系统搜索功能失效解决办法三步修改注册表