mysql資料庫關係操作

標籤：詳細資料   column   sele   方式   arch   ble   ict   values   相同   

### mysql資料庫

#### 知識要點：

1. 資料操作增、刪、改
2. 外鍵約束要求
3. 一對多表關係
4. 一對一表關係
5. 多對多表關係
6. 外鍵約束的參照操作

### 資料操作

#### 插入資料

方法一：

```sql
INSERT [INTO] table_name [(column_name,...)]
{VALUES|VALUE} ({expr|DEFAULT},...),(...),...;
```

方法二：

```sql
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...;
```

```mysql
#例：
mysql> CREATE TABLE `tb1`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL,
-> `age` INT DEFAULT 18
-> );
Query OK, 0 rows affected (0.54 sec)

mysql> INSERT INTO `tb1`(`name`)
-> VALUES(‘rose‘),
-> (‘taka‘)
-> ;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM `tb1`;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | rose | 18 |
| 2 | taka | 18 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO `tb1` SET `name`=‘taka‘;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO `tb1` SET `name`=‘budong‘,`age`=24;
Query OK, 1 row affected (0.07 sec)
```

#### 更新資料

```mysql
UPDATE tb_name
SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition];
```

```mysql
#例：
mysql> SELECT * FROM `tb1`;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | rose | 18 |
| 2 | tulple | 18 |
| 3 | taka | 18 |
| 4 | budong | 24 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> UPDATE `tb1` SET `age`=`age`+1;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> UPDATE `tb1` SET `age`=20 WHERE `name`=‘taka‘;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE `tb1` SET `age`=21 WHERE `id`<3;
Query OK, 2 rows affected (0.10 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql>
```

#### 刪除資料

```sql
DELETE FROM tbl_name [WHERE where_conditon];
```

不添加WHERE則會刪除全部記錄

```mysql
#例：
mysql> SELECT * FROM `tb1`;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | rose | 21 |
| 2 | tuple | 21 |
| 3 | taka | 21 |
| 4 | budong | 26 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> DELETE FROM `tb1` WHERE `id`=4;
Query OK, 1 row affected (0.06 sec)

mysql> DELETE FROM `tb1`;
Query OK, 3 rows affected (0.13 sec)

mysql> SELECT * FROM `tb1`;
Empty set (0.00 sec)

mysql>
```

### 外鍵約束`FOREIGN KEY`

外鍵約束`FOREIGN KEY`，保持資料一致性，完整性實現一對一或一對多關聯性。

##### 外鍵約束的要求：

資料表的儲存引擎只能為InnoDB
外鍵列和參照列資料類型一致
外鍵必須關聯到鍵上面去

```mysql
#添加外鍵的格式:
ALTER TABLE yourtablename
ADD [CONSTRAINT 外鍵名] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
```

##### 一對多關聯性

舉例，學校中一個學院可以有很多的學生，而一個學生只屬於某一個學院（通常情況下），學院與學生之間的關係就是一對多的關係，通過外部索引鍵關聯來實現這種關係。

```mysql
#例：
#建立學院表：
mysql> CREATE TABLE `department`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(15) NOT NULL
-> );
Query OK, 0 rows affected (0.61 sec)

#建立學生表：
mysql> CREATE TABLE `student`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL,
-> `dept_id` INT,
-> FOREIGN KEY (`dept_id`) REFERENCES `department`(`id`)
-> );
Query OK, 0 rows affected (0.51 sec)

#插入資料
mysql> INSERT INTO `department`(`name`)
-> VALUES(‘A‘),
-> (‘B‘)
-> ;
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO `student`(`name`,`dept_id`)
-> VALUES(‘s1‘,1),
-> (‘s2‘,2),
-> (‘s3‘,2)
-> ;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
```

##### 一對一關聯性

舉例，學生表中有學號、姓名、學院，但學生還有些比如電話，家庭住址等比較私密的資訊，這些資訊不會放在學生表當中，會建立一個學生的詳細資料表來存放。這時的學生表和學生的詳細資料表兩者的關係就是一對一的關係，因為一個學生只有一條詳細資料。用外鍵加唯一鍵的方式來實現這種關係。也可以外鍵加主鍵（包含唯一約束）。

```mysql
#例：
#學生表：
mysql> DESC `student`;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| dept_id | int(11) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)

#建立詳細學生表1111：外鍵+主鍵
mysql> CREATE TABLE `student_details`(
-> `id` INT PRIMARY KEY,
-> `age` INT,
-> `gender` CHAR(1),
-> FOREIGN KEY (`id`) REFERENCES `student`(`id`)
-> );
Query OK, 0 rows affected (0.67 sec)

#建立詳細學生表2222： 外鍵+唯一鍵
mysql> CREATE TABLE `student_details`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `age` INT,
-> `gender` CHAR(1),
-> `s_id` INT UNIQUE KEY,
-> FOREIGN KEY (`s_id`) REFERENCES `student`(`s_id`)
-> );
Query OK, 0 rows affected (0.02 sec)
```

##### 多對多關係

舉例，學生要報名選修課，一個學生可以報名多門課程，一個課程有很多的學生報名，那麼學生表和課程表兩者就形成了多對多關係。對於多對多關係，需要建立第三張關係表，關係表中通過外鍵加主鍵的形式實現這種關係。

```mysql
#例：
#建立課程表：
mysql> CREATE TABLE `course`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL,
-> );
Query OK, 0 rows affected (1.18 sec)

#學生與課程多對多關係表
mysql> CREATE TABLE `select`(
-> `s_id` INT,
-> `crs_id` INT,
-> PRIMARY KEY (`s_id`,`crs_id`),
-> FOREIGN KEY (`s_id`) REFERENCES `student` (`id`),
-> FOREIGN KEY (`crs_id`) REFERENCES `course` (`id`)
-> );
Query OK, 0 rows affected (0.50 sec)
```

##### 外鍵約束的參照操作：

```mysql
1.CASCADE從父表刪除或更新時自動刪除或更新子表中的匹配行
2.SET NULL從父表刪除或更新行時，設定子表中的外鍵列為NULL。
如果使用該選項，必須保證子表列沒有指定NOT NULL
3.RESTRICT拒絕對父表的刪除或更新操作
4.NO ACTION標準的SQL關鍵字，在mysql中與RESTRICT作用相同
```

mysql資料庫關係操作