創建數據表主鍵外鍵

發布時間：2020-08-02 12:44:21 來源：網絡閱讀：357 作者：拎壺沖沖沖欄目：MySQL數據庫

1、創建數據表
按行和列的方式存儲，每一行唯一一條記錄，每一列代表記錄中的某個字段或者是域。
格式：表的名稱不區分大小寫，不能使用SQL關鍵字；存在多列使用逗號分隔。

create table <table_name>
(
字段1，數據類型 [列約束條件]，
字段2，數據類型 [列約束條件]，
字段3，數據類型 [列約束條件]，
........
[表級別約束條件]
);

測試：表名test01

| 字段名 | 數據類型 |
| name | varchar (30) |
| id | int (11) |

（1）主鍵約束
單字段

mysql> create table test02
    -> (id int(11) primary key,
    -> name varchar(30));
Query OK, 0 rows affected (0.08 sec)

mysql> create table test03
    -> (id int(11),
    -> name varchar(30),
    -> primary key (id));
Query OK, 0 rows affected (0.10 sec)

mysql> desc test02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc test03;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

創建數據表主鍵外鍵

列的會隱含一個rowid字段
表的會明確要求id是可識別的標志

多字段

mysql> create table test05 (id int(11), name varchar(30),primary key(id,name)); 
Query OK, 0 rows affected (0.11 sec)

創建數據表主鍵外鍵

刪除主鍵約束

mysql> alter table test0004 drop primary key;  Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql> show create table test0004\G;
*************************** 1. row *************************** Table: test0004 

Create Table: CREATE TABLE 'test0004` (   `username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL DEFAULT '0', 
`age` tinyint(3) unsigned NOT NULL,
UNIQUE KEY `username` (`username`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

mysql> desc test0004; 
+----------+----------------------+------+-----+---------+-------+ 
| Field    | Type                 | Null | Key | Default | Extra | 
+----------+----------------------+------+-----+---------+-------+ |
username | varchar(10)          | NO   | PRI | NULL    |       | | 
pid      | smallint(5) unsigned | YES  |     | NULL    |       | | 
id       | smallint(5) unsigned | NO   |     | 0       |       | | 
age      | tinyint(3) unsigned  | NO   |     | NULL    |       
| +----------+----------------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)

怎么在已有的表中添加主鍵呢？
舉個例子：
創建一個書記表，后在增加一個字段

mysql> create table test0004( username varchar(10) not null, pid smallint unsigned);
Query OK, 0 rows affected (0.13 sec)

mysql> desc test0004;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test0004 add id smallint  unsigned;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

增加一個主鍵然后驗證

mysql> alter table test0004 add constraint PK_test0004_id primary key (id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test0004;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table test0004\G;
*************************** 1. row ***************************
       Table: test0004
Create Table: CREATE TABLE `test0004` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

那我們添加一個唯一約束來看一下

mysql> alter table test0004 add unique (username);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test0004\G;
*************************** 1. row ***************************
       Table: test0004
Create Table: CREATE TABLE `test0004` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

接下來我們給他添加一個字段，并修改和刪除默認值操作

mysql> alter table test0004 add age tinyint unsigned not null;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test0004;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table test0004 alter age set default 15;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test0004;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | 15      |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table test0004 alter age drop default;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（2）外鍵束縛
一個表可以有一個或多個外鍵；保證數據的一致性完整性；定義外鍵之后，不
允許刪除另一個表中具有關聯關系的記錄。

主表：對于兩個表具有關聯關系的，具有主鍵的表；
從表：對于兩個表具有關聯關系的，具有外鍵的表；
constraint <外建名> foreign key<字段名> references <主表名> 主鍵列

mysql> create table test06
    -> (id int(11) primary key,
    -> name varchar(30) not null);
Query OK, 0 rows affected (0.16 sec)

mysql> create table test07 (id int(11) primary key, name varchar(30), constraint test0607 foreign key(id) references test06(id) );
Query OK, 0 rows affected (0.19 sec)

創建數據表主鍵外鍵
加深：
首先創一個provin1表

mysql> create table provin1( id smallint unsigned primary key auto_increment, pnaame varchar(20) not null );
Query OK, 0 rows affected (0.09 sec)

mysql> show create table provin1\G;
*************************** 1. row ***************************
       Table: provin1
Create Table: CREATE TABLE `provin1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

再創建一個test0003的表

mysql> create table test0003( id smallint unsigned primary key auto_increment, ussername varchar(10) not null, pid smallint unsigned, foreign key (pid) referencess provin1 (id) on delete cascade);
Query OK, 0 rows affected (0.08 sec)

mysql> show create table test0003\G;
*************************** 1. row ***************************
       Table: test0003
Create Table: CREATE TABLE `test0003` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `test0003_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

向provin1中插入數據

mysql> insert provin1 (pname) values ('A');
Query OK, 1 row affected (0.00 sec)

mysql> insert provin1 (pname) values ('b');
Query OK, 1 row affected (0.00 sec)

mysql> insert provin1 (pname) values ('C');
Query OK, 1 row affected (0.00 sec)

mysql> select * from provin1;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  3 | b     |
|  5 | C     |
+----+-------+
3 rows in set (0.00 sec)

向test0003中插入數據

mysql> insert test0003(username,pid) values ('tom',3);                          
Query OK, 1 row affected (0.00 sec)

mysql> insert test0003(username,pid) values ('lichao',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert test0003(username,pid) values ('chenchen',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert test0003(username,pid) values ('cat',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert test0003(username,pid) values ('nihao',7);
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`chenchen`.`test0003`, CONSTRAINT
`test0003_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provin1` 
(`id`) ON DELETE CASCADE)

我們看到插入pid =7的時候出錯了，錯誤原因是因為pid所對應provin1表中沒有7這個id，所以他會報錯，我們來看看test0003表中數據

mysql> select * from test0003;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | tom      |    3 |
|  3 | lichao   |    5 |
|  5 | chenchen |    1 |
|  7 | cat      |    3 |
+----+----------+------+
4 rows in set (0.01 sec)

那么我們來去除一下provin1里邊的id為3的字段來查看test0003表中的變化

mysql> delete from provin1 where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from provin1;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  5 | C     |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from test0003;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | lichao   |    5 |
|  5 | chenchen |    1 |
+----+----------+------+
2 rows in set (0.00 sec)

我們看到去除provin1中的id為3的數據后test0003表中對應的pid為3的字段消失，此驗證了外鍵束縛中的 cascade功能。
cascade：從父表刪除或更新且自動刪除或跟新子表中匹配的行.

下邊我會整理其他約束和mysql表操作文檔，陸續更新中，本文禁止轉載，個人總結不易，請諒解

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

創建數據表主鍵外鍵

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

創建數據表主鍵外鍵

猜你喜歡

最新資訊

相關推薦

相關標簽