1. 数据操作增、删、改
2. 外键约束要求
3. 一对多表关系
4. 一对一表关系
5. 多对多表关系
6. 外键约束的参照操作

数据操作->插入数据

方法一:

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

方法二:

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

例如:

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('bank'),
    ->       ('tom')
    -> ;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

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

数据操作->更新数据

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

例如:

mysql> SELECT * FROM `tb1`;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | bank   |   18 |
|  2 | tom    |   18 |
|  3 | li     |   18 |
|  4 | lk     |   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`='lk';
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>

数据操作->删除数据

DELETE FROM tbl_name [WHERE where_conditon]; 

tip:不添加WHERE则会删除全部记录

例如:

mysql> SELECT * FROM `tb1`;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | bank   |   21 |
|  2 | tom    |   21 |
|  3 | lk     |   21 |
|  4 | li     |   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>

修改表中的列名

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

mysql> ALTER TABLE `tb1`
    -> CHANGE `name` `mingzi` VARCHAR(10) NOT NULL;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
|mingzi | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

外键约束FOREIGN KEY

外键约束FOREIGN KEY，保持数据一致性，完整性实现一对一或一对多关系。

外键约束的要求:

数据表的存储引擎只能为InnoDB
外键列和参照数据类型一致
外键必须关联到键上面去

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> 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('a1',1),
    ->       ('a2',2),
    ->       ('a3',2)
    -> ;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

一对一关系

例如:学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息。用外键加主键的方式来实现这种关系。

例：
学生表：
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)

建立详细学生表：
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)

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)

外键约束的参照操作

1.CASCADE从父表删除或更新时自动删除或更新子表中的匹配行
2.SET NULL从父表删除或更新行时，设置子表中的外键列为NULL。
   如果使用该选项，必须保证子表列没有指定NOT NULL
3.RESTRICT拒绝对父表的删除或更新操作
4.NO ACTION标准的SQL关键字，在mysql中与RESTRICT作用相同

on delete RESTRICT  | on update CASCADE

例如:

mysql> ALTER TABLE `students`
    -> DROP FOREIGN KEY `students_ibfk_1`
    -> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `students`
    -> ADD CONSTRAINT `stu_ibfk_1` FOREIGN KEY(`dept_id`) REFERENCES `department`(`d_id`) ON UPDATE CASCADE;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE `department` SET `d_id`=4  WHERE `name`='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `students`;
+------+------+---------+
| s_id | name | dept_id |
+------+------+---------+
|    1 | a1   |       4 |
|    2 | a2   |       2 |
|    3 | a3   |       2 |
+------+------+---------+
3 rows in set (0.00 sec)

mysql>