设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 543|回复: 0

[经验分享] MySQL学习笔记 约束以及修改数据表

[复制链接]
4321ddd
累计签到：1 天
连续签到：1 天
发表于 2016-11-25 08:36:20 | 显示全部楼层 |阅读模式
*** 约束保证数据的完整性和一致性
*** 约束分为表级约束和列级约束
*** 约束类型包括：
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)

1.外键约束的要求解析

*** 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
*** 数据表的存储引擎只能为InnoDB。
*** 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
       主键是默认自带索引的  而外键列创建时一般参照的是带有主键那一列  因此如果外键列没有创建索引的话就会被MYSQL根据参照列的索引创建一个索引  
*** 外键列是不可以以一个没有索引的列作为参照列的
1.参照列必须要创建一个索引（如果用的是主键默认自带索引  所以不用创建）  
2.外键列随意  如果不创建索引会被创建
【1】编辑数据表的默认存储引擎,配置文件/etc/my.cnf
1
2
[mysqld]
default-storage-engine=INNODB



【2】 创建省份数据表，查看存储引擎
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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】创建用户数据表,其中外键列必须和参照列必须有相似的数据类型
1
2
3
4
5
6
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个索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SHOW INDEXES FROM provinces\G;
*************************** 1. row ***************************
       Table: provinces
  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:
1 row in set (0.00 sec)

ERROR:
No query specified



  • 若外键列没有索引，自动创建：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> SHOW INDEXES FROM users\G;   
*************************** 1. row ***************************
       Table: users
  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:
*************************** 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)

ERROR:
No query specified



2.外键约束的参照操作
*** cascade ：从父表删除或更新且自动删除或更新子表中匹配的行
*** set null ：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
*** restrict ：拒绝对父表的删除或更新操作
*** no action ：标准的SQL的关键词，在MySQL中与restrict相同
CASCADE例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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.修改数据表
添加单列
1
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]




  • 例：

1
2
3
4
5
6
7
8
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列到最后一列：

1
2
3
4
5
6
7
8
9
10
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
    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      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+




  • 添加password列到username列的后面：

1
2
3
4
5
6
7
8
9
10
11
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
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    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+



  • 添加truename列到第一列：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> ALTER TABLE users1 ADD truename VARCHAR(32) NOT NULL FIRST;
mysql> SHOW COLUMNS FROM users1;                                          
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(32)          | NO   |     | NULL    |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+



    删除列：
1
ALTER TABLE tbl_name(数据表名称) DROP [COLUMN] col_name（列名）




  • 例，删除truename列：

1
2
3
4
5
6
7
8
9
10
11
mysql> ALTER TABLE users1 DROP truename;
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    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+



同时删除age和password列，（也可同时增加删除）用逗号分割：
1
2
3
4
5
6
7
8
9
mysql> ALTER TABLE users1 DROP age,DROP password;
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    |                |
+----------+----------------------+------+-----+---------+----------------+



    添加 / 删除主键约束：
1
2
3
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)       //添加

ALTER TABLE tbl_name DROP PRIMARY KEY     //删除



    添加 / 删除唯一约束：
1
2
3
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)    //添加

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name   //删除



    添加 / 删除外键约束：
1
2
3
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition    //添加

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol   //删除



    添加 / 删除默认约束：
1
2
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}    //添加
mysql> ALTER TABLE tbl_name ALTER age DROP DEFAULT;    //删除




添加主键约束：创建users2数据表，增加id列，设置id列为主键：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> CREATE TABLE users2(
   -> username VARCHAR(20) NOT NULL,
   -> pid SMALLINT UNSIGNED
   -> );   
mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
mysql> ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (id);
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
+----------+----------------------+------+-----+---------+-------+




  • 删除主键约束：

1
mysql> ALTER TABLE users2 DROP PRIMARY KEY;



        添加唯一约束：添加username列为唯一约束：
1
mysql> ALTER TABLE users2 ADD CONSTRAINT OK_users2_username UNIQUE KEY (username)；




  • 删除唯一约束：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
mysql> SHOW INDEXES FROM users2\G;
*************************** 1. row ***************************
       Table: users2
  Non_unique: 0
    Key_name: OK_users2_username
Seq_in_index: 1
Column_name: username
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: users2
  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)

ERROR:
No query specified

mysql> ALTER TABLE users2 DROP INDEX OK_users2_username;
mysql> SHOW INDEXES FROM users2\G;                     
*************************** 1. row ***************************
       Table: users2
  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:
1 row in set (0.00 sec)




  • 添加外键约束：添加pid列为外键列，provincse数据表中的id为参照列：

1
mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);




  • 删除外键约束：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SHOW CREATE TABLE users2;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                  |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(20) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(4) NOT NULL,
  KEY `pid` (`pid`),
  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;



  • 添加默认约束：添加age列，设置age列默认15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> ALTER TABLE users2 ADD age TINYINT NOT NULL;
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(4)           | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
mysql> SHOW COLUMNS FROM users2;                  
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(4)           | NO   |     | 15      |       |
+----------+----------------------+------+-----+---------+-------+




  • 删除默认值：

1
2
3
4
5
6
7
8
9
10
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;
mysql> SHOW COLUMNS FROM users2;                  
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(4)           | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+



            修改列定义：
1
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]



            修改列名称(不能随便更改)：
1
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_dufinition [FIRST|AFTER col_name]



            修改数据表名字(不能随便更改)：

  • 方法一：

1
ALTER TABLE old_tbl_name RENAME new_tbl_name



       方法二：
1
RENAME TABLE old_tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...




数据表

相关帖子


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-305184-1-1.html 上篇帖子： MySQL主从复制架构及原理 下篇帖子： 查看MYSQL数据库中所有用户及拥有权限 数据表
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表