华子目录
- 表操作
- 创建表
- 数据类型
- 文本类型
- 数值类型
- 日期/时间类型
- Bit数据类型
- 常见数据类型
- MySQL存储引擎
- 创建表的三个操作
- 创建表时指定存储引擎,字符集,校对规则,行格式
- 查看表
- 显示数据库中所有表
- 显示数据库中表的信息(表结构)
- 显示表中各列的==详细信息==
- 删除表
- 修改表结构((alter table 表名)开头)
- 修改表名(rename)
- 修改列名(change)
- 修改==列的数据类型==(modify)
- 删除列(drop)
- 增加列(add)
- 举例
- 复制表结构(2种方法)
- 方法1
- 方法2
- 复制表数据
- 表数据字典
- 表的约束条件
- 创建约束的时机
- 1.在建表的同时创建
- 列级约束
- 表级约束
- 2.在建表之后创建
- 外键约束
- 主键约束
- 检查约束
- 唯一约束
- 自动增长auto_increment
- 非空约束
- 默认值约束
- 删除约束
- 完整的建表操作
- 表物理存储结构
表操作
创建表
简单语法
create table 表名(
列名 列数据类型,
列名 列数据类型
);
mysql> create table stu(
-> name char(10),
-> age int(3)
-> );
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> show tables; #显示当前数据库中所有的表
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu |
+-----------------+
1 row in set (0.00 sec)
数据类型
在MySQL中,有三种主要的类型:文本,数值,日期/时间类型
文本类型
数据类型 | 作用 |
---|---|
char(size) | 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 |
varchar(size) | 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。 注释:如果值的长度大于 255,则被转换为 text 类型。 |
tinytext | 存放最大长度为255个字符的字符串。 |
text | 存放最大长度为 65535 个字符的字符串。 |
blob | 用于 BLOBs (Binary Large OBjects),二进制形式的长文本数据。存放最多 65535 字节的数据。 |
mediumtext | 存放最大长度为 16,777,215 个字符的字符串。 |
mediumblob | 用于 BLOBs (Binary Large OBjects),二进制形式的中等长度文本数据。存放最多 16,777,215 字节的数据。 |
longtext | 存放最大长度为 4,294,967,295 个字符的字符串。 |
longblob | 用于 BLOBs (Binary Large OBjects),二进制形式的极大文本数据。 存放最多 4,294,967,295 字节的数据。 |
enum(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。 注释:这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值: enum(‘X’,‘Y’,‘Z’) |
set | 与enum类似, set最多只能包含 64 个列表项,不过set可存储一个以上的值。 |
数值类型
类型 | 用途 | 存储需求 | 范围(有符号) | 范围(无符号) |
---|---|---|---|---|
tinyint | 小整数值 | 1Bytes | (-128,127) | (0,255) |
smallint | 大整数值 | 2Bytes | (-32768,32767) | (0,65535) |
mediumint | 大整数值 | 3Bytes | (-8388608,8388607) | (0,16777215) |
int或integer | 大整数值 | 4Bytes | (-2147483648,2147483647) | (0,4294967295) |
bigint | 极大整数值 | 8Bytes | (-9223372036854775808,9223372036854775807) | (0,18446744073709551615) |
float(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 | 4Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) |
double(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 | 8Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
decimal(size,d) | 作为字符串存储的 DOUBLE 类型,允许固定的小数点。 | (size+2)Byte |
注:这些数值类型拥有额外的选项 unsigned。通常,整数可以是负数或正数。如果添加 unsigned属性,那么范围将从 0 开始,而不是某个负数。
日期/时间类型
数据类型 | 存储需求 | 描述 |
---|---|---|
date | 3Bytes | 日期。格式: YYYY-MM-DD 注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ |
datetime | 8Bytes | 日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是’1000-01-01 00:00:00’ 到 ‘9999-12- 31 23:59:59’ |
timestamp | 4Bytes | 时间戳。 timestamp值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式: YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC |
time | 3Bytes | 时间。格式: HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’ |
year | 1Bytes | 2 位或 4 位格式的年。 注释: 4 位格式所允许的值: 1901 到 2155。 2 位格式所允许的值: 70 到69,表示从 1970 到 2069 |
Bit数据类型
数据类型 | 说明 | 用途 |
---|---|---|
bit | 表示是/否的数据 | 存储布尔数据类型 |
常见数据类型
MySQL存储引擎
- 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指定的存储引擎。
创建表的三个操作
- 1.指定字段以及类型
- 2.设定存储引擎
- 3.设置字符集和校对规则
创建表时指定存储引擎,字符集,校对规则,行格式
- InnoDB 存储引擎支持四种行格式:redundant,compact,dynamic,compressed
mysql> create table stu(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb default charset=gbk row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`age` tinyint DEFAULT NULL COMMENT '年龄',
`gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> drop table stu; # 删除表
Query OK, 0 rows affected (0.03 sec)
mysql> create table stu(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb default charset=gbk collate=gbk_chinese_ci row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`age` tinyint DEFAULT NULL COMMENT '年龄',
`gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> create table teacher(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb charset=gbk collate=gbk_chinese_ci row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show create table teacher;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`age` tinyint DEFAULT NULL COMMENT '年龄',
`gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table worker(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show create table worker;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| worker | CREATE TABLE `worker` (
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`age` tinyint DEFAULT NULL COMMENT '年龄',
`gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table body(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb default; # 会报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> create table body(
-> name varchar(10) comment '姓名',
-> age tinyint(3) comment '年龄',
-> gender enum('男','女')
-> )engine=innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)
总结:在定义表时,写不写default都不影响。
在定义表名和列名时,本质是使用反引号包裹
查看表
显示数据库中所有表
语法:
mysql> show tables [from 数据库名] [like wild];
mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu |
+-----------------+
1 row in set (0.00 sec)
mysql> show tables from huazi like "%u";
+----------------------+
| Tables_in_huazi (%u) |
+----------------------+
| stu |
+----------------------+
1 row in set (0.00 sec)
显示数据库中表的信息(表结构)
语法:
mysql> desc 表名 [列名];
或
mysql> describe 表名 [列名];
或
mysql> show columns from 表名;
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc stu name;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show columns from stu;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
显示表中各列的详细信息
语法:
mysql> show create table 表名\G;
(\G表示向mysql服务器发送命令,垂直显示结果)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`name` char(10) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
定义表中的列名和表名,本质上是使用反引号包裹
删除表
语法
mysql> drop table [if exists] 表名;
mysql> create table stu(
-> `姓名` varchar(10),
-> `年龄` int(3),
-> `学号` int
-> )engine=innodb default charset=utf8 row_format=dynamic;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`姓名` varchar(10) DEFAULT NULL,
`年龄` int DEFAULT NULL,
`学号` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> drop table if exists stu;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
修改表结构((alter table 表名)开头)
修改表名(rename)
语法
mysql> alter table 表名 rename 新表名;
或
mysql> rename table 表名 to 新表名;
修改列名(change)
语法
mysql> alter table 表名 change 原列名 新列名 数据类型;
修改列的数据类型(modify)
语法
mysql> alter table 表名 modify 列表 数据类型;
或
mysql> alter table 表名 modify 列表 数据类型 约束类型; #修改数据类型时添加约束
删除列(drop)
语法
mysql> alter table 表名 drop 列名;
增加列(add)
语法
mysql> alter table 表名 add 列名 数据类型;
mysql> alter table 表名 add 列名 数据类型 after 列名; (在指定列名之后添加字段)
mysql> alter table worker add 性别 char(1) not null; (添加列时加约束)
举例
mysql> create table stu(
-> name char(10) comment '姓名',
-> age int(3) comment '年龄'
-> )engine=innodb default charset=gbk row_format=dynamic;
mysql> alter table stu rename stu_info; # 修改表名
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu_info |
+-----------------+
1 row in set (0.03 sec)
mysql> alter table stu_info change `name` `姓名` char(10); # 修改列名
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table stu_info modify `age` tinyint(3); # 修改数据类型
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table stu_info drop `age`; # 删除列
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> alter table stu_info add `id` int after `姓名`; # 添加列
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| id | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制表结构(2种方法)
方法1
在create table语法的末尾添加like子句,可以将原表的表结构复制到新表中。
语法:
mysql> create table 新表名 like 原表名;
mysql> show create table stu_info\G;
*************************** 1. row ***************************
Table: stu_info
Create Table: CREATE TABLE `stu_info` (
`姓名` char(10) DEFAULT NULL,
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> create table stu like stu_info; # 复制表结构
Query OK, 0 rows affected (0.02 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`姓名` char(10) DEFAULT NULL,
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| id | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| id | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
方法2
在create table语句的末尾添加一个select语法,不仅可以实现表结构的复制,还可以将原表中的数据拷贝到新表中。
语法:
mysql> create table 新表名 select * from 原表名;
复制表数据
方法:如果已经存在一张结构一致的表,复制数据
语法:
mysql> insert into 表名 select * from 原表名;
表数据字典
由information_schema数据库负责维护:
tables-存放数据库里所有的数据表、以及每个表所在数据库。
schemata-存放数据库里所有的数据库信息
views-存放数据库里所有的视图信息。
columns-存放数据库里所有的列信息。
triggers-存放数据库里所有的触发器。
routines-存放数据库里所有存储过程和函数。
key_column_usage-存放数据库所有的主外键
table_constraints-存放各个表的约束。
statistics-存放了数据表的索引。
表的约束条件
- 约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除。
- 可以在创建表时规定约束(通过create table语句),或者在表创建之后通过alter table语句规定约束。
- 根据约束数据列的限制,约束可分为:
- 单列约束:每个约束只约束一列。
- 多列约束:每个约束可约束多列数据。
- 根据约束的作用范围,约束可分为:
- 列级约束:只能作用在一个列上,跟在列的定义后面,语法:列定义 约束类型
- 表级约束:可以作用在多个列上,不与列一起,而是单独定义
- 表级约束类型有4种:主键,外键,唯一,检查
mysql> [constraint 约束名] 约束类型
约束名的取名规则:表名_列名_约束类型 (推荐)
mysql> alter table 表名 add constraint 约束名 约束类型;
- 根据约束起的作用,约束可以分为以下6种约束类型:
- not null:非空约束,规定某个字段不能为空
- unique:唯一约束,规定某个字段在整个表中是唯一的
- primary key:主键约束,非空且唯一
- foreign key:外键约束
- check:检查约束
- default:默认值约束
创建约束的时机
1.在建表的同时创建
列级约束
只能作用在一个列上,跟在列的定义后面,语法:列定义 约束类型
mysql> create table stu(
-> id int primary key auto_increment comment '学号',
-> name varchar(20) not null comment '姓名',
-> age tinyint(3) check(age>=18) comment '年龄不小于18',
-> gender char(1) default 'M' check(gender in ('M','F')) comment '性别',
-> address varchar(50) unique
-> )auto_increment=1001;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`age` tinyint DEFAULT NULL COMMENT '年龄不小于18',
`gender` char(1) DEFAULT 'M' COMMENT '性别',
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `address` (`address`),
CONSTRAINT `stu_chk_1` CHECK ((`age` >= 18)),
CONSTRAINT `stu_chk_2` CHECK ((`gender` in (_gbk'M',_gbk'F')))
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)
mysql> desc stu;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint | YES | | NULL | |
| gender | char(1) | YES | | M | |
| address | varchar(50) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+----------------+
总结:
- 当使用列级约束进行check约束时,系统会自动起一个check约束名
- 一个表中primary key和auto_increment只能有一个
- 每一行可以定义多个约束
表级约束
可以作用在多个列上,不与列一起,而是单独定义
- 约束名的取名规则,推荐采用:表名_列名_约束类型,也可以随便起
- 表级约束类型有四种:主键、外键、唯一、检查
- 当出现外键约束时,最好使用表级约束
mysql> create table class(
-> class_id int primary key comment '班号'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> create table stu(
-> id int auto_increment comment '学号',
-> name varchar(20) not null comment '姓名',
-> age tinyint(3) not null comment '年龄不小于18',
-> gender char(1) default 'M' not null comment '性别',
-> address varchar(50) not null comment '住址',
-> classid int not null comment '班号',
-> constraint stu_id_primary primary key(id), # 主键约束
-> constraint stu_age_check check(age>=18), # 检查约束
-> constraint stu_gender_check check(gender in ('M','F')), # 检查约束
-> constraint stu_address_unique unique(address), # 唯一约束
-> constraint stu_class_foreign foreign key(classid) references class(class_id) # 外键约束
-> )auto_increment=1001;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`age` tinyint NOT NULL COMMENT '年龄不小于18',
`gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别',
`address` varchar(50) NOT NULL COMMENT '住址',
`classid` int NOT NULL COMMENT '班号',
PRIMARY KEY (`id`),
UNIQUE KEY `stu_address_unique` (`address`),
KEY `stu_class_foreign` (`classid`),
CONSTRAINT `stu_class_foreign` FOREIGN KEY (`classid`) REFERENCES `class` (`class_id`),
CONSTRAINT `stu_age_check` CHECK ((`age` >= 18)),
CONSTRAINT `stu_gender_check` CHECK ((`gender` in (_gbk'M',_gbk'F')))
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
总结
- class表是主表,stu表是子表
- constraint所在行可以有主键,外键,唯一,检查
- 普通行可以有非空,默认值,自动增加
- 写外键约束之前,必须存在主表
- 外键参照的只能是主表的主键约束或唯一约束
- 当主表的记录被子表参照时,主表数据不允许被删除。
- 外键是构建于一个表的两个字段或者两个表的两个字段(两个表中各一个) 之间的关系,外键确保了相关的两个字段的两个关系。
- 子表(从表)外键参照的的值必须在主表参照列值的范围内,或者为空(也可以加非空约束,强制不允许为空)。
- 外键约束必须单独定义,不能在普通行中定义。
注:
1.列级约束和表级约束可以联合写(最推荐的写法)
2.当使用列级约束进行check约束时,系统会自动起一个check约束名
3.一个表中primary key和auto_increment只能有一个
2.在建表之后创建
mysql> create table class(
-> class_id int primary key comment '班号'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> create table stu(
-> id int comment '学号',
-> name varchar(30),
-> age tinyint(3),
-> gender char(1),
-> address varchar(50),
-> classid int
-> );
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> alter table stu add constraint stu_id_primary primary key(id);
mysql> alter table stu add constraint stu_age_check check(age>=18);
mysql> alter table stu add constraint stu_gender_check check(gender in ('M','F'));
mysql> alter table stu add constraint stu_address_unique unique(address);
mysql> alter table stu add constraint stu_classid_foreign foreign key(classid) references class(class_id);
mysql> alter table stu modify name varchar(30) not null;
mysql> alter table stu modify gender char(1) default 'M';
mysql> alter table stu modify id tinyint not null auto_increment;
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` tinyint NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` tinyint DEFAULT NULL,
`gender` char(1) DEFAULT 'M',
`address` varchar(50) DEFAULT NULL,
`classid` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `stu_address_unique` (`address`),
KEY `stu_classid_foreign` (`classid`),
CONSTRAINT `stu_classid_foreign` FOREIGN KEY (`classid`) REFERENCES `class` (`class_id`),
CONSTRAINT `stu_age_check` CHECK ((`age` >= 18)),
CONSTRAINT `stu_gender_check` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
总结
- 表级约束使用add
- 普通行约束使用modify
外键约束
总结
- class表是主表,stu表是子表
- constraint所在行可以有主键,外键,唯一,检查
- 普通行可以有非空,默认值,自动增加
- 写外键约束之前,必须存在主表
- 外键参照的只能是主表的主键约束或唯一约束
- 当主表的记录被子表参照时,主表数据不允许被删除。
- 外键是构建于一个表的两个字段或者两个表的两个字段(两个表中各一个) 之间的关系,外键确保了相关的两个字段的两个关系。
- 子表(从表)外键参照的的值必须在主表参照列值的范围内,或者为空(也可以加非空约束,强制不允许为空)。
- 外键约束必须在constraint行中单独定义,不能在普通行中定义。
- 有外键约束时,当子表中存在主表的数据值,该主表中的数据值不能被删除;当主表中某数据值不在子表中时,主表中该数据值可以被删除
主键约束
- 主键从功能上看相当于非空且唯一,一个表中只允许一个主键,主键是表中唯一确定一行数据的字段,主键字段可以是单字段或者是多字段的组合。
- 当建立主键约束时,MySQL为主键创建对应的索引。
检查约束
- 检查约束在8.0之前,MySQL默认但不会强制的遵循check约束(写不报错,但是不生效,需要通过触发器完成),8之后就开始正式支持这个约束了。
唯一约束
- 唯一约束条件确保所在的字段或者字段组合不出现重复值。
- 唯一性约束条件的字段允许出现多个NULL。
- 同一张表内可建多个唯一约束。
- 唯一约束可由多列组合而成。
- 建唯一约束时MySQL会为之建立对应的索引。
- 如果不给唯一约束起名,该唯一约束默认与列名相同。
自动增长auto_increment
- 自动增长,为新的行产生唯一的标识,一个表只能有一个auto_increment,且该属性必须为主键的一部分。
- auto_increment的属性可以是任何整数类型。(自动排序)
- 允许插入null,会自动添加值
非空约束
- 列级约束,只能使用列级约束语法定义。确保字段值不允许为空。
默认值约束
- 可以使用default关键字设置每一个字段的默认值。
总结:只要有唯一性的存在,MySQL都会为之建立对应的索引。
删除约束
mysql> alter table stu drop foreign key 外键约束的约束名;(删除外键约束)
mysql> alter table stu drop primary key [主键约束的约束名];(删除主键约束)
mysql> alter table stu drop index 唯一约束的约束名;(删除唯一约束)
mysql> alter table stu drop check 检查约束的约束名;(删除检查约束)
mysql> alter table stu modify id tinyint;(删除default和not null和auto_increment)
总结
- 表级约束的删除和它们自己的约束名有关
- 普通行约束的删除直接使用modify置空即可
完整的建表操作
create table 表名 (
列名 列类型 [primary key AUTO_INCREMENT] [默认值] [列级约束]
...
[表级约束]
) [ENGINE=存储引擎类型] [auto_increment=初始值] [DEFAULT] [CHARSET=字符集] [collate=校对规则] [row_format=行格式];
表物理存储结构
此部分内容,等待博主下次揭晓!!!