网络编程
位置:首页>> 网络编程>> 数据库>> mysql主键,外键,非空,唯一,默认约束及创建表的方法

mysql主键,外键,非空,唯一,默认约束及创建表的方法

作者:公号运维家  发布时间:2024-01-17 18:18:06 

标签:mysql,主键,外键,默认,约束,表,创建

前言:

在数据库中,数据表是数据库中最重要、最基本的操作对象,是数据存储的基本单位。数据表被定义为列的集合,数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录,每一列代表记录中的一个域。

一、操作前提

创建数据表必须是在已经有数据库的前提下的哈,首先需要切换到数据库中,使用??use??命令进行切换。

mysql> use yunweijia;
Database changed
mysql>

二、mysql创建/新建表

create table <表名> (
字段1,数据类型 [列级别约束条件] [默认值],
字段2,数据类型 [列级别约束条件] [默认值],
字段3,数据类型 [列级别约束条件] [默认值],
------
[表级别约束条件]
)

例如我们创建一个表结构如下:

字段名称

数据类型

备注

id

INT

员工编号

name

VARCHAR(25)

员工名称

deptid

INT

所在部门编号

money

FLOAT

工资

1、首先我们需要创建一个数据库;

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql>

2、然后进入这个数据库;

mysql> use test_db;
Database changed
mysql>

3、创建表;

mysql> CREATE TABLE `test_user` (
-> `id` int(0) NULL DEFAULT NULL COMMENT '员工编号',
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
-> `deptid` int(0) NULL DEFAULT NULL COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

4、查看表;

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user |
+-------------------+
1 row in set (0.00 sec)

mysql> desc test_user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int | YES | | NULL | |
| money | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

释义:

??desc???命令是查看??mysql??表结构的命令;

三、使用主键约束

主键,又称主码,是表中一列或多列的组合。主键约束(??Primary KeyConstraint??)要求主键列的数据唯一,并且不允许为空。主键能够唯一地标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系,它们之间是一一对应的。主键分为两种类型:单字段主键多字段联合主键

1、单字段主键

主键由一个字段组成,??SQL??语句格式分为以下两种情况。

(1.1)在定义列的时候指定主键

语法:

字段名 数据类型 PRIMARY KEY [默认值]

示例:

mysql> CREATE TABLE `test_user_2` (
-> `id` int(0) PRIMARY KEY COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
-> `deptid` int(0) NULL DEFAULT NULL COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

看下结果:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user |
| test_user_2 |
+-------------------+
2 rows in set (0.00 sec)

mysql> desc test_user_2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int | YES | | NULL | |
| money | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

(1.2)在定义完所有列之后指定主键;

语法:

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]示例:

mysql> CREATE TABLE `test_user_3` (
-> `id` int(0) COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
-> `deptid` int(0) NULL DEFAULT NULL COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资',
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

看下结果:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user |
| test_user_2 |
| test_user_3 |
+-------------------+
3 rows in set (0.00 sec)

mysql> desc test_user_3;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int | YES | | NULL | |
| money | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

2、多字段联合主键

主键由多个字段联合组成,语法规则如下:

PRIMARY KEY [字段1, 字段2, ...,字段n]

示例:

mysql> CREATE TABLE `test_user_4` (
-> `id` int(0) COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '员工姓名',
-> `deptid` int(0) COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资',
-> PRIMARY KEY(name, deptid)
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

看下结果:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user |
| test_user_2 |
| test_user_3 |
| test_user_4 |
+-------------------+
4 rows in set (0.00 sec)

mysql> desc test_user_4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(25) | NO | PRI | NULL | |
| deptid | int | NO | PRI | NULL | |
| money | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

语句执行后,便创建了一个名为??test_user_4???的数据表,??name???字段和??deptid???字段组合在一起成为??test_user_4??的多字段联合主键。

四、使用外键约束

外键用来在两个表的数据之间建立连接,可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的外键可以是空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值

1、mysql中外键是什么?

外键:首先他是表中的一个字段,虽可以不是本表的主键,但要对应另外一个表的主键。外键的主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。

2、什么是主表?什么是从表?

  • 主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表是主表

  • 从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表是从表

3、如何在??mysql??中创建外键呢?

语法:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,&hellip;]
REFERENCES <主表名> 主键列1 [ ,主键列2,&hellip;]

外键名为定义的外键约束的名称,一个表中不能有相同名称的外键

字段名表示子表需要添加外键约束的字段列;

主表名即被字表外键所依赖的表的名称;

主键列表示主表中定义的主键列,或者列组合。

例如我们新建一个??test_dept??的表,表结构如下:

字段名称数据类型备注idINT部门编号nameVARCHAR(30)部门名称locationVARCHAR(50)部门位置

首先我们创建一下这个表:

mysql> CREATE TABLE `test_dept` (
-> `id` int(0) PRIMARY KEY COMMENT '部门编号',
-> `name` varchar(30) CHARACTER set utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '部门名称',
-> `localhost` varchar(50) CHARACTER set utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '部门位置'
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_dept |
| test_user |
| test_user_2 |
| test_user_3 |
| test_user_4 |
+-------------------+
5 rows in set (0.00 sec)

mysql>

定义数据表??test_user_5???,让他的键??deptid???作为外键关联到??test_dept???中的主键??id???,那么我们的??SQL??语句应该这么写;

mysql> CREATE TABLE `test_user_5` (
-> `id` int(0) PRIMARY KEY COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '员工姓名',
-> `deptid` int(0) COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资',
-> CONSTRAINT fk_user_dept FOREIGN KEY(deptid) REFERENCES test_dept(id)
-> );
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql>

通过上面的语句,可以看到,我们创建了一个名为??test_user_5???的表,主键为??id???,且创建了一个名为??fk_user_dapt???的外键约束,外键名称为??deptid???,其依赖于??test_dept???表中的主键??ID??。

注意点:

子表的外键必须关联父表的主键,且关联字段的数据类型必须匹配,如果类型不一样,则创建子表时,就会出现错误??ERROR 1005 (HY000):Can't create table 'database.tablename'(errno: 150)??。

五、使用非空约束

非空约束(??Not Null Constraint??)指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错。

语法:

字段名 数据类型 not null

那么,我们根据上面的内容,再创建一个??test_user_6???表,并指定??name??不能为空;

mysql> CREATE TABLE `test_user_6` (
-> `id` int(0) PRIMARY KEY COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
-> `deptid` int(0) COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

六、使用唯一性约束

唯一性约束(??Unique Constraint??)要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

语法:

字段名 数据类型 UNIQUE

那么,我们根据该内容,创建一个??test_user_7???表,并指定??id??唯一;

mysql> CREATE TABLE `test_user_7` (
-> `id` int(0) UNIQUE PRIMARY KEY COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '员工姓名',
-> `deptid` int(0) COMMENT '所在部门编号',
-> `money` float NULL DEFAULT NULL COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql>

注意点:

??UNIQUE???和??PRIMARY KEY???的区别:一个表中可以有多个字段声明为??UNIQUE???,但只能有一个??PRIMARY KEY???声明;声明为??PRIMARY KEY???的列不允许有空值,但是声明为??UNIQUE???的字段允许空值(??NULL??)的存在。

七、使用默认约束

默认约束(??Default Constraint??)指定某列的默认值。如男性同学较多,性别就可以默认为"男"。如果插入一条新的记录时没有为这个字段赋值,那么系统会自动为这个字段赋值为"男"。

语法:

字段名 数据类型 DEFAULT 默认值

那我们在这个基础上,再新建一个??test_user_8???表,并指定默认所在部门编号为??0001??,那么创建的时候就应该这么写;

mysql> CREATE TABLE `test_user_8` (
-> `id` int(0) COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '员工姓名',
-> `deptid` int(0) DEFAULT 0001 COMMENT '所在部门编号',
-> `money` float COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

八、设置表的属性值自动增加

在数据库应用中,经常希望在每次插入新记录时,系统自动生成字段的主键值。

可以通过为表主键添加??AUTO_INCREMENT??关键字来实现。

默认的,在??MySQL???中??AUTO_INCREMENT???的初始值是??1???,每新增一条记录,字段值自动加??1??。一个表只能有一个字段使用??AUTO_INCREMENT??约束,且该字段必须为主键的一部分。??AUTO_INCREMENT???约束的字段可以是任何整数类型(??TINYINT、SMALLIN、INT、BIGINT??等)。

语法:

字段名 数据类型 AUTO_INCREMENT

例如我们这里创建一个??test_user_9??表,并执行员工编号自动递增,那么我们应该这么写:

mysql> CREATE TABLE `test_user_9` (
-> `id` int(0) PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号' ,
-> `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '员工姓名',
-> `deptid` int(0) DEFAULT 0001 COMMENT '所在部门编号',
-> `money` float COMMENT '工资'
-> );
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql>

我们在这个表里插入两条数据试试看;

mysql> insert into test_user_9 (name, deptid, money) value ('张三', '0001', '10000'), ('李四', '0001', '10000'), ('王五', '0001', '10000');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test_user_9;
+----+--------+--------+-------+
| id | name | deptid | money |
+----+--------+--------+-------+
| 1 | 张三 | 1 | 10000 |
| 2 | 李四 | 1 | 10000 |
| 3 | 王五 | 1 | 10000 |
+----+--------+--------+-------+
3 rows in set (0.00 sec)
mysql>

可以看到??id??确实递增了哈。

来源:https://blog.51cto.com/u_12386780/5478673

0
投稿

猜你喜欢

手机版 网络编程 asp之家 www.aspxhome.com