MySQL常见建表选项及约束

最新推荐文章于 2023-08-07 20:32:21 发布
最新推荐文章于 2023-08-07 20:32:21 发布
阅读量905 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/v1t1p9hvbd/article/details/72151793
版权

一、CREATE TABLE 选项

1、在定义列的时候，指定列选项

1）DEFAULT <literal>：定义列的默认值

　　当插入一个新行到表中并且没有给该列明确赋值时，如果定义了列的默认值，将自动得到默认值 ；如果没有，则为null。

复制代码 
mysql> create table people                                                                 
    -> (
    -> 　　id int not null primary key,
    -> 　　name varchar(20) not null,
    -> 　　sex char(1) default 'm'
    -> );

mysql> insert into people(id,name) values(1,'张三');
mysql> insert into people values(2,'李四','f');

mysql> select * from people;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 张三    | m    |
|  2 | 李四    | f    |
+----+--------+------+
复制代码

当然，也可以在INSERT和UPDATE语句中使用DEFAULT关键字显式地给列赋默认值：

复制代码 
mysql> insert into people values(3,'王五',default);

mysql> update people set sex=default where id=2;

mysql> select * from people;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 张三    | m    |
|  2 | 李四    | m    |
|  3 | 王五    | m    |
+----+--------+------+
复制代码

 函数default(column)可以得到一个列的默认值：

复制代码 
mysql> select default(sex) from people;
+--------------+
| default(sex) |
+--------------+
| m            |
| m            |
| m            |
+--------------+
复制代码

 

2）comment：用来给列添加注释，最多255个字符，注释会保存到数据字典中。

　　创建带有列注释的表stu_comment

复制代码 
mysql> create table stu_comment
    -> (
    -> id int not null primary key
    ->   comment '学号',
    -> name varchar(20) not null
    ->   comment '姓名'
    -> );
复制代码

 　从数据字典查询注释信息

复制代码 
mysql> select column_name,column_comment
    -> from information_schema.columns
    -> where table_name='stu_comment';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id          | 学号            |
| name        | 姓名            |
+-------------+----------------+
复制代码

 

2、在CREATE TABLE语句中的表选项

1）engine：指定表使用的存储引擎

存储引擎：决定了数据如何存储以及如何访问，还有事务如何处理

MySQL允许对每个表使用不同的存储引擎，如果在create table语句中没有指定存储引擎，则使用默认的存储引擎。

　　mysql> show engines;    #查询所有支持的存储引擎

　　mysql> CREATE TABLE sexes(sex char(1) NOT NULL) ENGINE = INNODB; 

注意：存储引擎是个重点，后面我们详细讲解。

2）auto_increment：决定当向表中插入第一行时，自增列得到的第一个值是多少

3）comment：给表添加注释

复制代码 
mysql> create table mycomm(num int) comment '测试表';

mysql> select table_name,table_comment
    -> from information_schema.tables
    -> where table_name='mycomm';
+------------+---------------+
| table_name | table_comment |
+------------+---------------+
| mycomm     | 测试表         |
+------------+---------------+
复制代码

 

 

二、CREATE TABLE 约束

作用：可以为列定义约束（constraint）

　　约束主要是防止非法数据进入到表中，确保数据的正确性和一致性（统称数据完整性）；

　　约束也可以防止一个表被删除 。

注意：

　　1）MySQL中约束保存在information_schema.table_constraints中，可以通过该表查询约束信息；

　　2）进行约束定义的时间：使用create table语句、使用alter table语句。

常用的约束的类型：5种

　　①not null：非空约束，指定某列不为空

　　②unique：唯一约束，指定某列和几列组合的数据不能重复

　　③primary key：主键约束，指定某列的数据不能重复、唯一

　　④foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据

　　⑤check：检查，指定一个表达式，用于检验指定数据

 

约束定义的语法：

复制代码 
列级别：
CREATE TABLE table_name(column_name data_type
　　[ [NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY]
　　|CHECK(expr)],…)

表级别：
CREATE TABLE table_name(
　　column_name  data_type [NOT NULL],
   　　column_name data_type [not null],…,
　　[CONSTRAINT constraint_name] PRIMARY KEY (col_name,...)
　　|[CONSTRAINT constraint_name] unique (col_name,...)
　　|[CONSTRAINT constraint_name] foreign KEY (col_name) REFERENCES tbl_name (index_col_name)
　　|check(expr)
复制代码

注意：

　　1）NOT NULL约束只能在列级别定义，作用在多个列上的约束只能定义在表级别，例如复合主键约束；

　　2）列级别上不能定义外键约束，并且不能给约束起名字，由MySQL自动命名（NOT NULL除外）；

　　3）表级别上定义的约束可以给约束起名字（CHECK约束除外）

 

1、、not null非空约束

作用：用于确保当前列的值不为空。

复制代码 
mysql> create table temp_nn(id int not null);

约束直接对DML操作带来影响
mysql> insert into temp_nn values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into temp_nn values(null);
ERROR 1048 (23000): Column 'id' cannot be null

具有非空约束的列不允许有null值
复制代码

注意：非空约束只能出现在表对象的列上。

 

2、unique唯一约束

　　1.唯一约束是指定table的列或列组合不能重复，保证数据的唯一性，约束的列不允许有重复值；

　　2.唯一约束不允许出现重复的值，但是可以为多个null；

　　3.同一个表可以有多个唯一约束，多个列组合的约束

复制代码 
mysql> create table temp_uk(
    ->   id int not null unique,
    ->   name varchar(20) unique);

mysql> insert into temp_uk values(1,'a');
mysql> insert into temp_uk values(2,'a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'name'
mysql> insert into temp_uk values(2,null);
mysql> insert into temp_uk values(3,null);

mysql> select * from temp_uk;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
|  3 | NULL |
|  1 | a    |
+----+------+
复制代码

可见，唯一性约束的列可以有多个null值，因为null <> null

　　4.在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同；

　　5.唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

复制代码 
mysql> create table test(
    ->   id int not null,
    ->   name varchar(20),
    ->   password varchar(16),
---使用表级约束语法   
    ->   constraint uk_name_pwd unique(name,password)
    -> );    #表示用户名和密码组合不能重复
Query OK, 0 rows affected (0.08 sec)

查询数据字典，查看唯一键约束的信息
mysql> select * from information_schema.table_constraints
    -> where table_name='test';
复制代码

 

3、primary key主键约束

　　primary key = not null + unique

主键：用来唯一的标示表中的每一行(类型一般为整型或者字符串)

　　具有主键约束的列不允许有null值，并且不允许有重复值;

　　每个表最多只允许一个主键(可定义联合主键)，主键名总是PRIMARY。

复制代码 
mysql> create table temp_pk(
    -> id int primary key);

mysql> insert into temp_pk values(1),(2);

mysql> insert into temp_pk values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> update temp_pk set id=1 where id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into temp_pk values(null);
ERROR 1048 (23000): Column 'id' cannot be null
复制代码

!!给主键一个新的名字，但在数据字典中，主键名还是显示primary

 

联合主键（用几个列进行唯一标识一行）

复制代码 
mysql> create table temp_pk(
    -> id int,
    -> name varchar(20),
    -> constraint pk_id_name primary key(id,name)
    -> );
Query OK, 0 rows affected (0.06 sec)

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

mysql> insert into temp_pk values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into temp_pk values(2,'李四');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp_pk values(1,'王五');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temp_pk values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

mysql> select * from temp_pk;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  1 | 王五   |
|  2 | 李四   |
+----+--------+
3 rows in set (0.00 sec)
复制代码

 

4、foreign key外键约束

外键约束：

　　参照完整性约束，保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

注意：

　　1）具有外键约束的列的值不能随便给，必须满足外键所引用的主键的取值；

　　2）一张表中可以定义多个外键；

　　3）外键列默认可以给null值。

按照定义，外键必须引用一个主键或者唯一键，引用的主键一般在另外一张表中，也可以是本表的主键(后者称为“自引用”)。

父子表：

　　外键所在的表叫做子表、从表

　　外键所引用的主键所在的表叫做父表、主表

注意：父子表是相对而言的，表a可以是表b的子表，但同时也可以是表c的父表

示例：创建外键约束

复制代码 
//创建父表
mysql> create table dept(
    ->   deptid int,
    ->   dname varchar(20),
    ->   constraint dept_deptid_pk primary key(deptid)
    -> );

mysql> insert into dept(deptid,dname) values(10,'市场部');
mysql> insert into dept(deptid,dname) values(20,'销售部');

//创建子表（表级别创建外键约束）
mysql> create table emp(
    ->   id int,
    ->   name varchar(20),
    ->   deptid int,
    ->   constraint emp_id_pk primary key(id),
    ->   constraint emp_deptid_fk  foreign key(deptid)
    ->     references dept(deptid)
    -> ); 
复制代码

查询数据字典，查看外键约束的信息：

　　上面创建子表的时候给外键约束命名emp_deptid_fk；

　　如果不给外键约束命名，那么默认的名字是表名_ibfk_n, n是整数,从1开始；

此时，emp表中deptid列(外键约束)受dept主表限制

复制代码 
mysql> insert into emp(id,name,deptid) values(1,'张三',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(2,'李四',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(3,'王五',50);　　#insert主表deptid列没有的数据
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))

mysql> update emp set deptid=30 where id=1;　　#update主表deptid列没有的数据
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))
外键的update更新操作规则如下删除规则……

mysql> delete from dept where deptid=10;　　#delete父表中的行(子表中有引用的数据行)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))

外键的默认删除规则：
　　当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么就不允许删除，并抛出异常（默认对外键使用on delete restrict 或on delete no action选项）
复制代码

 

外键引用定义： 

复制代码 
reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION
复制代码

 在定义外键约束时，通过使用on delete cascade或者on delete set null选项，可以改变外键的默认删除规则：

　　①ON DELETE CASCADE：级联删除。当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除（很危险！！！）

　　②ON DELETE SET NULL：当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为null 

　　……

复制代码 
mysql> create table emp(
    ->   id int,
    ->   name varchar(20),
    ->   deptid int,
    ->   constraint emp_id_pk primary key(id),
    ->   constraint emp_deptid_fk  foreign key(deptid)
　　 ->     references dept(deptid)
    ->   on delete cascade
    -> );
……
复制代码

 

5、check约束

　　MySQL可以使用check约束，但check约束对数据验证没有任何作用。

　　Oracle中可以使用check约束，是有相应的作用的。

复制代码 
mysql> create table test_ck(
    ->   id int check(id>0)
    -> );

mysql> insert into test_ck values(-100);

mysql> select * from test_ck;
+------+
| id   |
+------+
| -100 |
+------+
复制代码

The CHECK clause is parsed but ignored by all storage engines。

定义数据库列时，可以使用ENUM（enumeration，枚举）和SET（集合）类型：变通的实现CHECK约束

两者的区别是：

　　使用ENUM，只能选一个值；

　　使用SET，可以选多个值；

ENUM和SET中的值都必须是字符串类型。

1、enum枚举类型

注意：

　　在内部存储ENUM值时，MYSQL给ENUM中的每个值一个顺序号码：第一个值的顺序号码是1，第二个值的顺序号码是2，以此类推。当排序或比较ENUM的时候，使用这些顺序号码进行。

复制代码 
mysql> select * from student order by sex;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  5 | 王五    | NULL |
|  1 | 张三    | M    |
|  2 | 李四    | F    |
+----+--------+------+
复制代码

2、set类型：因为可以列举多个值，所以在建表列约束中常被使用到。

复制代码 
mysql> create table team(
    ->   teamno int not null,
    ->   division set('north','south','east','west')
    -> );

mysql> insert into team values(1,'west');
mysql> insert into team values(2,'west,south');
mysql> insert into team values(4,null);
mysql> insert into team values(3,'east,asia');
ERROR 1265 (01000): Data truncated for column 'division' at row 1

mysql> select * from team;
+--------+------------+
| teamno | division   |
+--------+------------+
|      1 | west       |
|      2 | south,west |
|      4 | NULL       |
+--------+------------+
v1t1p9hvbd
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql批量建表
01-05
利用mysql存储函数，方便快捷批量建立mysql数据库测试表
mysql表创建选项_使用DEFAULT在MySQL中使用特定选项创建新表？
weixin_40002238的博客
01-19 795
为此，请在列数据类型之后使用DEFAULT关键字。让我们创建一个表-mysql>createtabledemo33−>(−>idintnotnullauto_incrementprimarykey,−>namevarchar(20)notnull,−>start_datedatedefault(current_date),−>...
Mysql 表的约束
weixin_58004346的博客
11-14 756
auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
MySQL 表约束超详细
qq_57570052的博客
08-07 7544
在MySQL里，“约束”指的是对表中数据的一种限制约束，它能够确保数据库中数据的准确性和有效性比如有的数据是必填项，就像身份认证的时候，或者填注册信息的时候，手机号身份证这种就不能空着，所以就有了非空约束；又有的数据比如用户的唯一id，不能跟其他人的一样，所以就需要使用唯一约束等等。在MySQL中主要有6种约束：主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。在建立数据表的时候，一般情况下，为了方便更快地查找表中的记录，都会要求在表中设置一个“主键”。
MySQL建表约束
weixin_57551677的博客
09-08 1226
MySQL创建表时的约束
MYSQL数据库基本数据类型及建表和约束
08-01
MYSQL数据库基本数据类型及建表和约束
mysql建表语句学习
11-17
mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql建表语句mysql...
MYSQL 建表约束
12-14
**以下所有约束状态均能用describe查询 1、主键约束： 它能够唯一确定一张表中的一条记录，也就是我们通过某个字段添加约束，就可以是的改字段不重复且不为空。 **①方法一：** create table user( # 创建表 id int...
Mysql 建库建表技巧分享
09-11
本文中说到的“建”，并非单纯的建一个库，或是建一张表，而是你建好的库和表在项目的运营中，是否能应付各种事件，下面我说说几个我在项目中遇到的问题以及处理的方法，算是一个小小的心得，给大家分享下。
mysql 建表约束
NRapid&JRapid
04-12 945
- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；-- 表示 class_id 的值必须来自于 classes 中的 id 字段值。-- 这里的 class_id 要和 classes 中的 id 字段相关联。-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。-- 2. 主表中的记录被副表引用时，主表不可以被删除。-- 约束某个字段的默认值。-- 建表时创建唯一主键。-- 建表时添加非空约束。-- 建表时添加默认约束。
MySQL表的约束
Q1697354625
07-26 232
真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。所谓约束，就是避免犯一些低级错误，比如类似于语法错误，编译器的编译失败实际上也算是一种约束。 表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。
mysql 的选项和属性
weixin_34195364的博客
11-17 109
转载于:https://blog.51cto.com/fengpiaophp/1577616
mysql _数据库表的创建
LJH_laura_li的博客
10-22 1万+
---1.建立空表 首先第一次使用数据库时，需要建立一个数据库，才可以建表。 以学生系统为例，建立数据库语句为 create database stu; //(stu为数据库名) 使用如下语句查看数据库 show databases; 我们便可以看到刚才建的数据库 建好数据库后，我们需要用如下语句使用数据库 use stu; 下一步便是在这个数据库中建表，...
mysql命令一些常用的选项
liaomin416100569的专栏
03-26 1565
要想禁用列名，可以使用下面的形式来指定选项： --disable-column-names --skip-column-names --column-names=0 --disable和--skip前缀与=0后缀的效果相同：它们均关闭选项。 可以用下述方法“启用”选项： --column-names --enable-column-names --column-names=1 例
MySQL配置选项
炎溟墨的博客
10-11 479
文章目录内存相关的系统变量key_buffer_size 内存相关的系统变量 MySQL内存系统变量能够影响全局内存的使用,也能够影响到多线程的单一MySQL进程中会话的内存使用情况. 很多MySQL变量是动态的,他们的值可以在运行时通过MySQL客户端的set命令更改. 全局内存缓冲区: 全局/会话内存缓冲区 会话缓冲区: key_buffer_size key_buffer_size...
MySQL 列选项
RisenWang 的博客
12-12 402
table schema 由列定义和其他东西组成，一个列定义由 列名，数据类型，空指定，以及完整性约束组成，也可为每个列定义添加几个选项。语法 <column definition> ::= <column name> <data type> [ <null specification> ] [ <column integrity constraint> ] [ <column
MYSQL建表时数据类型的选择
热门推荐
黄雄杰
06-09 1万+
1、为表中的字段选择合适的数据类型当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。2、如何选择正确的整数类型类型大小范围（SIGNED）范围（UNSIGNED）用途TINYINT1 字节(-128，127)(0，255)小整数值，如年龄SMALLINT2 字节(-32768，32 767)(0，...
数据库表的约束、设计、范式、多表查询
wseeyouagain的博客
08-25 130
数据库表的约束 概念 对表中的数据进行限定，保证数据的正确性、有效性和完整性。 分类 主键约束：primary key 非空约束：not null 唯一约束：unique 外键约束：foreign key 作用 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无 法插入到表中。约束在创建表的时候添加比较合适。 非空约束：not null，值不能为null 1. 创建表时添加约束 CREATE TABLE stu( id INT, NAME VA
mysql workbench建表
最新发布
09-11
要使用MySQL Workbench建表，可以按照以下步骤进行操作： 1. 打开MySQL Workbench软件并登录到你的MySQL服务器。 2. 在左侧的“模式”选项卡下，选择你想要创建表的数据库。 3. 在库视图中，右键单击数据库名称，并选择“创建表”选项。 4. 在创建表对话框中，输入表的名称和所需的列。 5. 对于每一列，可以指定列名、数据类型、长度、约束等属性。 6. 点击“添加列”按钮以继续添加更多列，直到定义完所有需要的列。 7. 在对话框的底部，可以选择设置主键、索引或其他约束。 8. 点击“应用”按钮以保存表的定义。 这样就创建了一个新的表，可以在工作台的“模式”选项卡下看到它。你还可以使用MySQL Workbench的其他功能，如导入和导出数据，编辑表结构等。请注意，以上步骤仅适用于MySQL Workbench的可视化界面，如果你更喜欢使用SQL语句来创建表，也可以在MySQL Workbench中打开查询编辑器并编写相应的SQL语句来创建表。如有需要，你可以参考中的Mysql WorkBench安装配置图文教程和中关于数据库和数据表的组织结构的内容。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值