MySQL之建表操作

华子目录

  • 表操作
    • 创建表
    • 数据类型
      • 文本类型
      • 数值类型
      • 日期/时间类型
      • 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 开始,而不是某个负数。

日期/时间类型

数据类型存储需求描述
date3Bytes日期。格式: YYYY-MM-DD 注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
datetime8Bytes日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是’1000-01-01 00:00:00’ 到 ‘9999-12- 31 23:59:59’
timestamp4Bytes时间戳。 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
time3Bytes时间。格式: HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
year1Bytes2 位或 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;(删除defaultnot nullauto_increment

总结

  • 表级约束的删除和它们自己的约束名有关
  • 普通行约束的删除直接使用modify置空即可

完整的建表操作

create table 表名 (
	列名 列类型 [primary key AUTO_INCREMENT] [默认值] [列级约束]
	...
	[表级约束]
) [ENGINE=存储引擎类型] [auto_increment=初始值] [DEFAULT] [CHARSET=字符集] [collate=校对规则] [row_format=行格式];

表物理存储结构

此部分内容,等待博主下次揭晓!!!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/377263.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Java实现教学过程管理系统 JAVA+Vue+SpringBoot+MySQL

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 教师端2.2 学生端2.3 微信小程序端2.3.1 教师功能如下2.3.2 学生功能如下 三、系统展示 四、核心代码4.1 查询签到4.2 签到4.3 查询任务4.4 查询课程4.5 生成课程成绩 六、免责说明 一、摘要 1.1 项目介绍 基于JAVAVu…

2023:AI疯狂进化年

嘿,大家好!让我们一起来回顾一下这疯狂的 2023 年吧!记得那个二月初吗?ChatGPT 上线了,然后呢?短短两个月,用户数量就像火箭一样突破了 1 亿!这速度,简直比超级赛亚人还快…

设置打印机

一、打开控制面板的设备和打印机选项 二、点击其中的添加打印机选项 三、点我所需的打印机未列出 四、使用IP地址或主机名添加打印机 五、输入IP

python将Word页面纸张方向设置为横向

通过python-docx的章节属性,就可以更改纸张方向、纸张尺寸。 import docx from docx.enum.section import WD_ORIENT from docx.shared import Cmdocument docx.Document() section document.sections[0]# 设置纸张大小为A4大小 section.page_width Cm(21) sect…

稳压二极管应用电路

稳压二极管比较特殊,基本结构与普通二极管一样,也有一个PN结。由于制造工艺的不同,当这种PN结处于反向击穿状态时,PN结不会损坏(普通二极管的PN结是会损坏),在稳压二极管用来稳定电压时就是利用它的这一击穿特性。 由…

泰克示波器(TBS2000系列)数学运算功能使用

目录 1 数学运算菜单1.1 运算符选择1.2 信源选择1.3 数学运算结果 1 数学运算菜单 Math运算按钮,用于实现对两个通道的信号进行实时的“加、减、乘”运算,计算时信源1在前面,信源2在运算符的右边,设置时设置信源与运算符就行了。…

请查收,你的2023京东零售技术年度好文

新春佳节,万象更新!京东零售技术在2023年度发布文章内容145篇,全年阅读量超过20万次~衷心感谢每一位读者一直以来的关注和支持。 在新春到来之际,我们精选年度好文分享给大家,希望大家温故知新&#xff0c…

HarmonyOS 鸿蒙应用开发(十、第三方开源js库移植适配指南)

在前端和nodejs的世界里,有很多开源的js库,通过npm(NodeJS包管理和分发工具)可以安装使用众多的开源软件包。但是由于OpenHarmony开发框架中的API不完全兼容V8运行时的Build-In API,因此三方js库大都需要适配下才能用。 移植前准备 建议在适…

bert+np.memap+faiss文本相似度匹配 topN

目录 任务 代码 结果说明 任务 使用 bert-base-chinese 预训练模型将文本数据向量化后,使用 np.memap 进行保存,再使用 faiss 进行相似度匹配出每个文本与它最相似的 topN 此篇文章使用了地址数据,目的是为了跑通这个流程,数…

DoWhy:Python 中的因果推断库

DoWhy:Python 中的因果推断库 DoWhy 是一个强大的 Python 库,用于因果推断和因果推断分析。本文将介绍 DoWhy 的基本概念、主要功能和使用方法,帮助读者了解如何利用该库进行因果推断,并解决因果关系的相关问题。 什么是DoWhy&…

AI-数学-高中-24-三角函数一般形式的各参数含义

原作者视频:三角函数】12三角函数一般形式的各参数含义(易)_哔哩哔哩_bilibili 1.函数中的A标识符:表示曲线中间平衡位置的振幅,值域为正负A:[-A,A]。 2.函数中的B标识符:决定曲线纵向上下平移…

python实现中国剩余定理

中国剩余定理又称孙子定理,是数论中一个重要定理。最早可见于我国的数学著作《孙子算经》卷下“物不知数”问题,原文如下: 有物不知其数,三三数之剩二,五五数之剩三,七七数之剩二。问物几何?即…

一步步建立一个C#项目(连续读取S7-1200PLC数据)

这篇博客作为C#的基础系列,和大家分享如何一步步建立一个C#项目完成对S7-1200PLC数据的连续读取。首先创建一个窗体应用。 1、窗体应用 2、配置存储位置 3、选择框架 拖拽一个Button,可以选择视图菜单---工具箱 4、工具箱 拖拽Lable控件和TextBook控件 5、拖拽控件 接下来…

Linux文件和目录管理

目录基础 Linux操作系统以目录的方式来组织和管理系统中的所有文件。所谓的目录,就是将所有文件的说明信息采用树状结构组织起来。每个目录节点之下会有文件和子目录。 所有一切都从 ‘根’ 开始,用 ‘/’ 代表, 并且延伸到子目录。 bin:B…

《MySQL 简易速速上手小册》第2章:数据库设计最佳实践(2024 最新版)

文章目录 2.1 规划高效的数据库架构2.1.1 基础知识2.1.2 重点案例2.1.3 拓展案例 2.2 数据类型和表设计2.2.1 基础知识2.2.2 重点案例2.2.3 拓展案例 2.3 索引设计原则2.3.1 基础知识2.3.2 重点案例2.3.3 拓展案例 2.1 规划高效的数据库架构 在开启我们的数据库设计之旅之前&a…

240207-3步设置VSCode插件Inline-Bookmarks自定义颜色及名称

Step 1: 插件安装 Step 2: 配置文件 "inline-bookmarks.expert.custom.styles": {"default": {"gutterIconColor": "#157EFB","overviewRulerColor": "rgba(21, 126, 251, 0.7)","light": {"fontW…

【C++从0到王者】第四十二站:类型转换

文章目录 一、 C语言中的类型转换1. C语言中的类型转换2.一个常见的坑 二、为什么C需要四种类型转换三、C强制类型转换1.static_cast2.reinterpret_cast3.const_cast4.dynamic_cast 四、RTTI 一、 C语言中的类型转换 1. C语言中的类型转换 在C语言中,如果赋值运算符…

MOS管防反接电路设计

电子元件大都是使用直流工作,电源线反接就有可能就会烧坏,那电路如何防反接?首当其冲我们想到的就是二极管了,运用其单向导通特性可有效防止电源反接而损坏电路,但是随之而来的问题是二极管存在PN节电压,通…

【C++航海王:追寻罗杰的编程之路】类与对象你学会了吗?(下)

目录 1 -> 再谈构造函数1.1 -> 构造函数体赋值1.2 -> 初始化列表1.3 -> explicit关键字 2 -> static成员2.1 -> 概念2.2 -> 特性 3 -> 友元3.1 -> 友元函数3.2 -> 友元类 4 -> 内部类5 -> 匿名对象6 -> 拷贝对象时的一些编译器优化 1 -…

Three.js学习7:dat.GUI 参数控制

每个学 Three.js 的都被安利了 dat.GUI 吧? 我也不例外! 今天就来了学习下 dat.GUI,并记录下来自己的学习成果。 一、什么是 dat.GUI? dat.GUI 是一个轻量级的图形用户界面库(GUI 组件),使用这个库可以…