【数据库】 数据库中表的基本操作_数据库表操作

目录

 表的基本操作

一， 创建表

1，单行命令创建表：

2，分行命令创建表：

二， 数据类型

1，文本类型：

2，数值类型：

3，日期/时间类型：

三， 查看表

1，查看创建表的指令：

2，查看mysql数据库里面所有的表：

3，查看数据库下指定的表

4，显示当前数据库中已有的数据表的信息【结构和创建信息】

四，删除表

1，删除数据库chap03下面的表t1:

五，修改表的结构

1，修改列类型

2，增加列

3，删除列

4，修改列名

5，修改表名

六，复制表的结构

1，复制表结构

2，复制表的结构以及表的内容

3，两张表结构相同，复制内容

七，表的约束

1、非空约束(NOT NULL)

2、唯一约束（UNIQUE）

3、主键约束（PRIMARY KEY）

4、外键约束（FOREIGN KEY）

5、检查约束（CHECK ）

6、 默认值约束（DEFAULT）

八， 数据库字典

九， 存储引擎

---

 表的基本操作

一， 创建表

数据表的每行称为一条记录（record）；每一列称为一个字段（field）【列之间以英文逗号隔开】。

简单语法：在当前数据库中创建一张表

    CREATE TABLE 表名(

    列名 列数据类型，

    列名 列数据类型

    );

1，单行命令创建表：

在 chap03数据库中创建表t1，列名分别为id,name 且列名属性分别为int，char(30)

（1）创建chap03数据库

mysql8.0.30 [mysql]>create database chap03;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql8.0.30 [mysql]>

（2）使用chap03数据库

mysql8.0.30 [mysql]>use chap03;
 
Database changed

（3）创建表t1，列名分别为id,name 且列名属性分别为int，char(30)

mysql8.0.30 [chap03]>create table t1(id int,name char(30));
 
Query OK, 0 rows affected (0.00 sec)

 

（4）创建t1表的结果如下，查看chap03数据库下的所有表可以看到表t1

mysql8.0.30 [chap03]>show tables;
 
+------------------+
 
| Tables_in_chap03 |
 
+------------------+
 
| t1               |
 
+------------------+
 
1 row in set (0.00 sec)

（5）表t1中的所有信息如下所示

mysql8.0.30 [chap03]>select * from t1;
 
+------+----------+
 
| id   | name     |
 
+------+----------+
 
|    1 | xiaoming |
 
+------+----------+
 
1 row in set (0.00 sec)

2，分行命令创建表：

（1）使用分行命令创建表t2，列名分别为id,name,address且列属性分别为int,char(30),char(50);

mysql8.0.30 [chap03]>create table t2(
 
    -> id int,
 
    -> name char(30),
 
    -> address char(50)
 
    -> );
 
Query OK, 0 rows affected (0.00 sec)

（2）此时查看数据库chap03，会出现两张表

mysql8.0.30 [chap03]>show tables;
 
+------------------+
 
| Tables_in_chap03 |
 
+------------------+
 
| t1               |
 
| t2               |
 
+------------------+
 
2 rows in set (0.00 sec)

二， 数据类型

在 MySQL 中，有三种主要的类型：文本、数值和日期/时间类型。

1，文本类型：

 

2，数值类型：

 

注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED属性，那么范围将从 0 开始，而不是某个负数。

3，日期/时间类型：

 

常用数据类型：

三， 查看表

查看某数据库下的表

方法一：show tables from 数据库名 like ' 表名';

方法二：use 数据库名；

              show tables;

1，查看创建表的指令：

mysql8.0.30 [chap03]>show create table chap03.t1;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                   |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `id` int DEFAULT NULL,
 
  `name` char(30) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

2，查看mysql数据库里面所有的表：

mysql8.0.30 [chap03]>show tables from mysql;
 
+------------------------------------------------------+
 
| Tables_in_mysql                                      |
 
+------------------------------------------------------+
 
| columns_priv                                         |
 
| component                                            |
 
| db                                                   |
 
| default_roles                                        |
 
| engine_cost                                          |
 
| func 

3，查看数据库下指定的表

查看mysql数据库里面有没有与server相关的表：

mysql8.0.30 [chap03]>show tables from mysql like '%server%';
 
+----------------------------+
 
| Tables_in_mysql (%server%) |
 
+----------------------------+
 
| server_cost                |
 
| servers                    |
 
+----------------------------+
 
2 rows in set (0.00 sec)

4，显示当前数据库中已有的数据表的信息【结构和创建信息】

1、语法：{DESCRIBE|DESC} 表名[列名];

2、语法：show columns from 表名称;

语法一：

（1）查看数据库chap03下面表t1的表结构，表的字段

mysql8.0.30 [chap03]>describe chap03.t1;
 
+-------+----------+------+-----+---------+-------+
 
| Field | Type     | Null | Key | Default | Extra |
 
+-------+----------+------+-----+---------+-------+
 
| id    | int      | YES  |     | NULL    |       |
 
| name  | char(30) | YES  |     | NULL    |       |
 
+-------+----------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

（2）查看数据库mysql下面表user的表结构

mysql8.0.30 [chap03]>desc mysql.user;
 
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
 
| Field                    | Type                              | Null | Key | Default               | Extra |
 
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
 
| Host                     | char(255)                         | NO   | PRI |                       |       |
 
| User                     | char(32)                          | NO   | PRI |                       |       |

pri为主键

方法二：

（1）查看数据库chap03下面表t1的结构：

mysql8.0.30 [chap03]>show columns from chap03.t1;
 
+-------+----------+------+-----+---------+-------+
 
| Field | Type     | Null | Key | Default | Extra |
 
+-------+----------+------+-----+---------+-------+
 
| id    | int      | YES  |     | NULL    |       |
 
| name  | char(30) | YES  |     | NULL    |       |
 
+-------+----------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

四，删除表

删除指定的表

语法：DROP TABLE [IF EXISTS] 表名;

1，删除数据库chap03下面的表t1:

mysql8.0.30 [chap03]>drop table chap03.t1;
 
Query OK, 0 rows affected (0.01 sec)

五，修改表的结构

修改表的语法如下：

修改列类型：ALTER TABLE 表名 MODIFY 列名 列类型;

增加列：ALTER TABLE 表名 ADD 列名 列类型;

删除列：ALTER TABLE 表名 DROP 列名;

修改列名：ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;

更改表名：

方式1：ALTER TABLE 表名 RENAME 新表名;

方式2：RENAME TABLE 表名 TO 新表名;

  

此时t1表的结构为：

1，修改列类型

（1）把t1表的name列的类型有char改变为varchar:

mysql8.0.30 [chap03]>alter table t1 modify name varchar(30);
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
mysql8.0.30 [chap03]>

（2）修改成功，查看表修改后表t1的结构：

mysql8.0.30 [chap03]>desc t1;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| id    | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

2，增加列

（1）给表t1增加一个birthday列（只写年月日）：

mysql8.0.30 [chap03]>alter table t1 add birthday date;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

（2）修改成功后，查看修改后的表：

mysql8.0.30 [chap03]>desc t1;
 
+----------+-------------+------+-----+---------+-------+
 
| Field    | Type        | Null | Key | Default | Extra |
 
+----------+-------------+------+-----+---------+-------+
 
| id       | int         | YES  |     | NULL    |       |
 
| name     | varchar(30) | YES  |     | NULL    |       |
 
| birthday | date        | YES  |     | NULL    |       |
 
+----------+-------------+------+-----+---------+-------+
 
3 rows in set (0.00 sec)

3，删除列

（1）删除表t1的birthday列：

mysql8.0.30 [chap03]>alter table t1 drop birthday;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

（2）删除列成功后，查看删除列birthdayt后的表结构：

mysql8.0.30 [chap03]>desc t1;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| id    | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

4，修改列名

（1）把t1表中id列名改为num列：

mysql8.0.30 [chap03]>alter table t1 change id num int;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

（2）修改成功后，查看修改后的表结构：

mysql8.0.30 [chap03]>desc t1;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| num   | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

5，修改表名

方法一：

（1）把表t1(b2)的名称改为m2：

mysql8.0.30 [chap03]>alter table b2 rename m2;
 
Query OK, 0 rows affected (0.01 sec)

（2）修改成功后，查看改完表名的表结构：

mysql8.0.30 [chap03]>desc m2;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| num   | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

方法二：

（1）把表m2的名称改为t3：

mysql8.0.30 [chap03]>rename table m2 to t3;
 
Query OK, 0 rows affected (0.00 sec)

（2）修改成功后，查看t3的表结构：

mysql8.0.30 [chap03]>desc t3;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| num   | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.01 sec)

六，复制表的结构

复制一个表结构的实现方法有以下几种：

方法1：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法：create table 新表名 like 源表

mysql8.0 [chap03]>create table t4 like t3;

方法2：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。

语法：create table 新表名 select * from 源表

mysql8.0 [chap03]>create table t5 select * from t4;

方法3：如果已经存在一张结构一致的表，复制数据

语法：insert into 表 select * from 原表;

1，复制表结构

（1）创建一个新表t4，想用旧表t3的结构：

mysql8.0.30 [chap03]>create table t4 like t3;
 
Query OK, 0 rows affected (0.00 sec)

（2）查看t4表的结构：

mysql8.0.30 [chap03]>desc t4;
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| num   | int         | YES  |     | NULL    |       |
 
| name  | varchar(30) | YES  |     | NULL    |       |
 
+-------+-------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

t3表的数据如下：

2，复制表的结构以及表的内容

（1）创建一个新表t5，使用t3的结构并拷贝t3表的内容：

mysql8.0.30 [chap03]>create table t5 select * from t3;
 
Query OK, 1 row affected (0.01 sec)
 
Records: 1  Duplicates: 0  Warnings: 0

（2）查看t5表的信息：

mysql8.0.30 [chap03]>select * from t5;
 
+------+----------+
 
| num  | name     |
 
+------+----------+
 
|    1 | xiaohong |
 
+------+----------+
 
1 row in set (0.00 sec)

t3表的数据如下：

 

3，两张表结构相同，复制内容

（1）表t4和表t3的结构相同，把表t3的内容拷贝到表t4中：

mysql8.0.30 [chap03]>insert into t4 select * from t3;
 
Query OK, 1 row affected (0.01 sec)
 
Records: 1  Duplicates: 0  Warnings: 0

（2）查看t4的表的内容：

mysql8.0.30 [chap03]>select *from t4;
 
+------+----------+
 
| num  | name     |
 
+------+----------+
 
|    1 | xiaohong |
 
+------+----------+
 
1 row in set (0.00 sec)

t3表的数据如下：

七，表的约束

约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时，可以保护相关的数据不被删除。

 

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

• 根据约束数据列的限制，约束可分为：
  • 单列约束：每个约束只约束一列。
  • 多列约束：每个约束可约束多列数据。
• 根据约束的作用范围，约束可分为：
  • 列级约束：只能作用在一个列上，跟在列的定义后面，语法：列定义 约束类型
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

 

语法：[CONSTRAINT 约束名] 约束类型(列名)

约束名的取名规则，推荐采用：表名_列名_约束类型

例如：alter table 表名 add constraint 约束名 约束类型(要约束的列名)

表级约束类型有四种：主键、外键、唯一、检查

根据约束起的作用，约束可分为：

NOT NULL 非空约束，规定某个字段不能为空

UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

PRIMARY KEY 主键(非空且唯一)约束

FOREIGN KEY 外键约束

CHECK 检查约束

DEFAULT 默认值约束

1、非空约束(NOT NULL)

列级约束，只能使用列级约束语法定义。确保字段值不允许为空。

mysql8.0 [chap03]>create table t6_stu(

    -> id int,

    -> name char(30) not null);

说明：所有数据类型的值都可以是NULL。空字符串不等于NULL。0也不等于NULL。

（1）在数据库chap03中创建一个表t6，name列有非空约束

mysql8.0.30 [chap03]>create table t6(
 
    -> id int,
 
    -> name char(30) not null
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)

（2）约束成功后，输入id，不输入name会报错

（3）输入name值才能保存数据

（4）查看输入数据后的t6表信息

mysql8.0.30 [chap03]>select * from t6;
 
+------+----------+
 
| id   | name     |
 
+------+----------+
 
| NULL | xiaoming |
 
|    1 | xiaohong |
 
+------+----------+
 
2 rows in set (0.00 sec)

（5）删除表t6的非空约束

mysql8.0.30 [chap03]>alter table t6 modify name char(30);
 
Query OK, 0 rows affected (0.02 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

（6）查看创建表t6的指令

mysql8.0.30 [chap03]>show create table t6;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                   |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t6    | CREATE TABLE `t6` (
 
  `id` int DEFAULT NULL,
 
  `name` char(30) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

2、唯一约束（UNIQUE）

• 唯一性约束条件确保所在的字段或者字段组合不出现重复值。
• 唯一性约束条件的字段允许出现多个NULL。
• 同一张表内可建多个唯一约束。
• 唯一约束可由多列组合而成。
• 建唯一约束时MySQL会为之建立对应的索引。
• 如果不给唯一约束起名，该唯一约束默认与列名相同。

 

（1）创建一个id唯一，name非空的表t8:

mysql8.0.30 [chap03]>create table t8(
 
    -> id int unique,
 
    -> name char(30) not null
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)

 

 

 

（2）查看创建表t8的指令：

mysql8.0.30 [chap03]>show create table t8;
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                         |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t8    | CREATE TABLE `t8` (
 
  `id` int DEFAULT NULL,
 
  `name` char(30) NOT NULL,
 
  UNIQUE KEY `id` (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

 

 

（3）查看t8的表结构，可以看到Id列的key为uni表示唯一；name列的null值为no,即不能为空

mysql8.0.30 [chap03]>desc t8;
 
+-------+----------+------+-----+---------+-------+
 
| Field | Type     | Null | Key | Default | Extra |
 
+-------+----------+------+-----+---------+-------+
 
| id    | int      | YES  | UNI | NULL    |       |
 
| name  | char(30) | NO   |     | NULL    |       |
 
+-------+----------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

 

 

（4）给表t8增加一列host char(255)，不做约束

mysql8.0.30 [chap03]>alter table t8 add host char(255);
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

 

 

（5）查看修改后的表t8结构：host列没有受到约束

mysql8.0.30 [chap03]>desc t8;
 
+-------+-----------+------+-----+---------+-------+
 
| Field | Type      | Null | Key | Default | Extra |
 
+-------+-----------+------+-----+---------+-------+
 
| id    | int       | YES  | UNI | NULL    |       |
 
| name  | char(30)  | NO   |     | NULL    |       |
 
| host  | char(255) | YES  |     | NULL    |       |
 
+-------+-----------+------+-----+---------+-------+
 
3 rows in set (0.00 sec)

 

 

 

表级约束

（1）把name 和host做一个组合

mysql8.0.30 [chap03]>alter table t8 add constraint unique(name,host);
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

 

 

（2）查看创建表t8的指令，name和host都必须唯一

mysql8.0.30 [chap03]>show create table t8;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                               |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t8    | CREATE TABLE `t8` (
 
  `id` int DEFAULT NULL,
 
  `name` char(30) NOT NULL,
 
  `host` char(255) DEFAULT NULL,
 
  UNIQUE KEY `id` (`id`),
 
  UNIQUE KEY `name` (`name`,`host`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

 

 

（3）删除唯一约束

列级约束：(按索引)；删除t8表的id列

mysql8.0.30 [chap03]>alter table t8 drop index id;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

 

 

表级约束；连续删除两列

mysql8.0.30 [chap03]>alter table t8 drop index id;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
mysql8.0.30 [chap03]>alter table t8 drop index name;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

 

 

 

 

3、主键约束（PRIMARY KEY）

主键从功能上看相当于非空且唯一，一个表中只允许一个主键，主键是表中唯一确定一行数据的字段，主键字段可以是单字段或者是多字段的组合 当建立主键约束时，MySQL为主键创建对应的索引。

 

创建主键

（1）创建表t9，主键为id

mysql8.0.30 [chap03]>create table t9(
 
    -> id int primary key,
 
    -> name char(30)
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)

 

 

 （2）查看t9的表结构，可以看到key值为pri表示主键，非空且唯一

mysql8.0.30 [chap03]>desc t9;
 
+-------+----------+------+-----+---------+-------+
 
| Field | Type     | Null | Key | Default | Extra |
 
+-------+----------+------+-----+---------+-------+
 
| id    | int      | NO   | PRI | NULL    |       |
 
| name  | char(30) | YES  |     | NULL    |       |
 
+-------+----------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)

 

 

 （3）查看创建表t9的指令

mysql8.0.30 [chap03]>show create table t9;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                     |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t9    | CREATE TABLE `t9` (
 
  `id` int NOT NULL,
 
  `name` char(30) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

 

 

 

自动增长

自动增长：auto_increment ：自动增长,为新的行产生唯一的标识，一个表只能有一个auto_increment，且该属性必须为主键的一部分。auto_increment的属性可以是任何整数类型。

（1）创建表t10,id可以自增

mysql8.0.30 [chap03]>create table t10( id int primary key auto_increment, name char(30) );
 
Query OK, 0 rows affected (0.03 sec)

（2）只输入name，id会自动增加

删除主键

（1）删除表t9的主键：

mysql8.0.30 [chap03]>alter table t9 drop primary key;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

4、外键约束（FOREIGN KEY）

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系，外键确保了相关的两个字段的两个关系。

子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。

当主表的记录被子表参照时，主表记录不允许被删除。

外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

语法：FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

（1）创建一个表tb_dept主键为dept_id 

mysql8.0.30 [chap03]>create table tb_dept(
 
    -> dept_id int primary key,
 
    -> name char(30)
 
    -> );
 
Query OK, 0 rows affected (0.00 sec)

（2）创建表tb_employee，外键key (department_id)参考表tb_dept的主键dept_id 

mysql8.0.30 [chap03]>create table tb_employee(
 
    -> employee_id int primary key,
 
    -> name char(30),
 
    -> department_id int,
 
    -> foreign key(department_id) references tb_dept(dept_id)
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)

（3）给表tb_dept输入记录

（4）给表tb_employee输入记录时，外键key (department_id)必须参考表tb_dept的主键dept_id ，不能随意输入

删除外键：

删除后没有约束，可以随意填写

mysql8.0.30 [chap03]>alter table tb_employee drop FOREIGN KEY tb_employee_ibfk_1;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

5、检查约束（CHECK ）

（1）创建表t对age进行约束，即只能输入大于18的数字；对gender进行约束，即只能输入M,F

          两个选项中的任意一个。

mysql8.0.30 [chap03]>create table t11(
 
    -> id int,
 
    -> age int check (age>18),
 
    -> gender char(1) check (gender in ('M','F'))
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)

 

 

（2）年龄不满14岁，无法保存

（3）性别不在m和f中，无法保存

（4）年龄>18；性别为F即可保存成功

（5）查看创建表t11的指令

mysql8.0.30 [chap03]>show create table t11;
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                                                    |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t11   | CREATE TABLE `t11` (
 
  `id` int DEFAULT NULL,
 
  `age` int DEFAULT NULL,
 
  `gender` char(1) DEFAULT NULL,
 
  CONSTRAINT `t11_chk_1` CHECK ((`age` > 18)),
 
  CONSTRAINT `t11_chk_2` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F')))
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

（6）删除年龄的约束：

mysql8.0.30 [chap03]>alter table t11 drop CHECK t11_chk_1;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

6、 默认值约束（DEFAULT）

可以使用default关键字设置每一个字段的默认值。

（1）创建表t12,默认20岁，默认男m；

mysql8.0.30 [chap03]>create table t12( id int, age int default 20,gender char(1) default 'M' check (gender in ('M','F')));
 
Query OK, 0 rows affected (0.01 sec)

（2）填写一个id，自动按照默认值生成age ;gender

（3）删除默认值：

mysql8.0.30 [chap03]>show create table t12;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                        |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t12   | CREATE TABLE `t12` (
 
  `id` int DEFAULT NULL,
 
  `age` int DEFAULT '20',
 
  `gender` char(1) DEFAULT 'M',
 
  CONSTRAINT `t12_chk_1` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F')))
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

（4）删除默认年龄

mysql8.0.30 [chap03]>alter table t12 modify age int;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

（5）查看创建表t12的指令

mysql8.0.30 [chap03]>show create table t12;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                        |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t12   | CREATE TABLE `t12` (
 
  `id` int DEFAULT NULL,
 
  `age` int DEFAULT NULL,
 
  `gender` char(1) DEFAULT 'M',
 
  CONSTRAINT `t12_chk_1` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F')))
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

八， 数据库字典

由information_schema数据库负责维护：

tables－存放数据库里所有的数据表、以及每个表所在数据库。

schemata－存放数据库里所有的数据库信息

views－存放数据库里所有的视图信息。

columns－存放数据库里所有的列信息。

triggers－存放数据库里所有的触发器。

routines－存放数据库里所有存储过程和函数。

key_column_usage－存放数据库所有的主外键

table_constraints－存放各个表的约束。

statistics－存放了数据表的索引。

九， 存储引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存）中， 每种技术都使用不同的存储机制、 索引技巧、 锁定水平。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型）。

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。

InnoDB        事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。应用于对事务的完整性要求高,在并发条件下要求数据的一致性的情况。 MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

MyISAM        是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。应用于以读写操作为主， 很少更新 、 删除 ， 并对事务的完整性、 并发性要求不高的情况。

MEMORY        存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。

 MEMORY：表的数据存放在内存中，访问效率高 ，但一旦服务关闭，表中的数据全部丢失。

MERGE: 是一组MyISAM表的组合。 可以突破对单个MyISAM表大小的限制， 并提高访问效率。

默认情况下， 创建表不指定表的存储引擎， 则会使用配置文件my.cnf中 default-storage-engine=InnoDB指定InnoDB。

在创建表时， 可以指定表的存储引擎:

CREATE TABLE (...)  ENGINE=InnoDB; 

完整的创建表的命令语法

CREATE TABLE 表名（

列名 列类型 [AUTO_INCREMENT] [DEFAULT 默认值][列约束]

...

[表约束]

) [ENGINE=表类型] [DEFAULT] CHARSET=字符集;

列类型： 该列的数据的存储类型

AUTO_INCREMENT: 自动增长，只能是数值类型的列

DEFAULT 默认值：设置该列的默认值

列约束：对列的一些限制

ENGINE: 表类型， 也叫表的存储引擎

CHARSET： 设置表的字符集