《MySQL数据库应用案例教程》教学课件 第5章 数据表基本操作

主键 外键
订单编号 添加时间 商品编号
选择数据库db_shop后，执行以下SQL语句，创建数据表orders。
CREATE TABLE orders(
设置外键约束时应注意以下几点：
o_id INT(11) PRIMARY KEY, add_time DATETIME, goods_id INT(11), CONSTRAINT goo_ord FOREIGN KEY(goods_id) REFERENCES goods(id) );
— 11 —
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
7
设置外键约束
设置外键约束的主要作用是保证数据的完整性。 外键可以不是所属数据表的主键，但会对应着另外一张数据表的主键。例如，商品和订单之间具有 一定关系，订单数据表会有一个字段存储商品的编号，而这个字段的值对应着商品数据表中的商品编号， 订单数据表可以称为从表，商品数据表可以称为主表，订单数据表中的商品编号字段就可以称为外键。 设置外键约束的语法形式如下：
mysql> SHOW CREATE TABLE goods \G *************************** 1. row ***************************
Table: goods Create Table: CREATE TABLE `goods` (
— 12 —
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
7
设置外键约束
【实例5-2】
字段 o_id add_time goods_id
根据表5-2创建从表orders，为goods_id字段设置外键约束。
表5-2 orders表结构
数据类型
约束
注释
INT(11) DATETIME
INT(11)
表5-4 comment表结构
数据类型 INT(11) INT(11) INT(11) TEXT
约束 主键、自增 非空、无符号 非空、无符号
默认
DATETIME
注释 评价编号 评价商品 评价用户 评价内容 添加时间
字段 id
comment_id user_id r_content add_time
表5-5 reply表结构
—7—
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
3
设置非空约束
设置非空约束的关键字为NOT NULL，作用是规定字段的值不能为空，用户在向数据表中插入数据 时，如果设置非空约束的字段没有指定值，系统就会报错。
语法形式如下：
col_name data_type NOT NULL
实例5-1创建的数据表goods中的type字段便设置了非空约束，SQL语句如下：
| Field | Type
| Null
| Key | Default | Extra
|
+--------------+------------------------------+-------------+-------+---------+--------------------------+
—6—
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
2
设置自增约束
在向数据表中插入数据时，如果用户希望每条记录的“编号”自动生成，并且按顺序排列，可以为 该字段设置自增约束。
设置自增约束的关键字为AUTO_INCREMENT，语法形式如下：
col_name data_type AUTO_INCREMENT
5
设置无符号约束
为字段设置无符号约束的关键字为UNSIGNED，其作用是规定此列所存储的数据不为负数。
语法形式如下：
col_name data_type UNSIGNED;
实例5-1创建的数据表goods中的price字段便设置了无符号约束，SQL语句如下：
CREATE TABLE goods( price DECIMAL(7,2) UNSIGNED, …… );
— 14 —
5.1 创建数据表
➢ 5.1.3 使用图形化工具创建表并设置约束条件
实际工作中，使用图形化工具可以更简单快捷地创建数据表。本节将通过创建商品评价表comment， 并为其设置约束条件，介绍使用图形化工具创建表和设置约束条件的方法。comment表结构如表5-4所示。
字段 id
goods_id user_id content add_time
1）定义字段时设置主键约束 语法形式为：
col_name data_type PRIMARY KEY
实例5-1创建的数据表goods中的id字段便设置了主键约束，SQL语句如下：
CREATE TABLE goods ( id INT(11) PRIMARY KEY, …… );
—5—
5.1 创建数据表
表54comment表结构idint11主键自增评价编号goodsidint11非空无符号评价商品useridint11非空无符号评价用户contenttext默认评价内容addtimedatetime添加时间表55reply表结构idint11主键自增回复编号commentidint11非空无符号评价编号useridint11非空无符号评价用户rcontenttext回复内容addtimedatetime添加时间52查看表结构1752查看表结构52查看表结构521使用sql语句查看表结构describetablename
—4—
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
1
设置主键约束
主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，并且主键值不能为空。 主键约束是最常用的一种约束，设置主键约束的关键字为PRIMARY KEY，使用SQL语句可以在定 义字段时设置主键约束，也可以在定义好表中所有字段后再设置主数据表
➢ 5.1.2 使用SQL语句设置约束条件
6
设置默认约束
设置默认约束的关键字为DEFAULT，语法形式如下：
col_name data_type DEFAULT value
实例5-1创建的数据表goods中的num字段便设置了默认约束，SQL语句如下：
CREATE TABLE goods( num INT(11) DEFAULT 0, …… );
CONSTRAINT key_name FOREIGN KEY(child_col_name) REFERENCES parent_table_name(parent_col_name)
CONSTRAINT，FOREIGN KEY和REFERENCES为设置外键约束的关键字，key_name表示外键 名 ， child_col_name 表 示 从 表 中 需 要 设 置 外 键 约 束 的 字 段 名 ， parent_table_name 表 示 主 表 名 ， parent_col_name表示主表中主键的字段名。
实例5-1创建的数据表goods中的id字段便设置了自增约束，SQL语句如下：
CREATE TABLE goods( id INT(11) PRIMARY KEY AUTO_INCREMENT, …… );
一张表中只能设置一个字段为自增约束，并且该字段必须为主键。 默认的初始值为1，每增加一条记录，字段值自动增加1。 字段类型必须为整数型。
数据类型
约束
INT(11)
主键、自增
INT(11) INT(11)
非空、无符号 非空、无符号
TEXT
DATETIME
注释 回复编号 评价编号 评价用户 回复内容 添加时间
— 15 —
5.2 查看表结构
5.2 查看表结构
➢ 5.2.1 使用SQL语句查看表结构
数据表创建完成后，可以通过查看表结构或者建表语句，来确认表的定义是否正确。
主表和从表必须使用 InnoDB
存储引擎。
设置外键约束的字段和关联的 主键必须具有相同的数据类型。 — 13 —
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
8
设置表的存储引擎
【实例5-3】
根据表5-3创建category表，并设置其存储引擎为MyISAM，用于存储商品类别。
字段 id
name p_id
表5-3 category表结构
数据类型
约束
INT(11)
主键
VARCHAR(30)
INT(11)
注释 类别编号 类别名称 父类编号
选择数据库db_shop后，执行以下SQL语句，创建数据表category。
CREATE TABLE category ( id INT(11) PRIMARY KEY, name VARCHAR(30), p_id INT(11) ) ENGINE=MyISAM;
| id
| int(11)
| NO
| PRI | NULL | auto_increment |
| type
| varchar(30)
| NO
| | NULL |
|
| name | varchar(30)
| YES
| UNI | NULL |
|
| price | decimal(7,2) unsigned| YES | | NULL |
实例5-1创建的数据表goods中的name字段便设置了唯一性约束，SQL语句如下：
CREATE TABLE goods( name VARCHAR(30) UNIQUE, …… );
2）定义所有字段后设置唯一性约束 语法形式为：
UNIQUE KEY(col_name)
—9—
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
|
| num
| int(11)
| YES
| |0
|
|
|add_time | datetime
| YES
|
| NULL |
|
+--------------+------------------------------+-------------+-------+----------+-------------------------+
根据表5-1的数据信息创建数据表goods。
表5-1 goods表结构
数据类型 INT(11) VARCHAR(30) VARCHAR(30) DECIMAL(7,2) INT(11) DATETIME
约束 主键、自增
非空 唯一 无符号 默认值为0
注释 商品编号 商品类别 商品名称 商品价格 商品库存 添加时间
第5 章
数据表基本操作
5.1 创建数据表
5.1 创建数据表
➢ 5.1.1 创建表的语法形式
创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法形式如下：
CREATE TABLE table_name( col_name1 data_type [Constraints], col_name2 data_type [Constraints], …… col_namen data_type [Constraints] );
6 rows in set (0.09 sec)
— 17 —
5.2 查看表结构
➢ 5.2.1 使用SQL语句查看表结构
2
查看建表语句
使用SHOW CREATE TABLE语句可以查看表的建表语句，语法形式如下：
SHOW CREATE TABLE table_name \G
【实例5-5】
执行SHOW CREATE TABLE语句，查看goods表的建表语句，效果如下所示。
数据表命名应遵循以下原则：
长度最好不超过30个字符； 多个单词之间使用下划线“_”分隔，不允许有空格； 不允许为MySQL关键字； 不允许与同一数据库中的其他数据表同名。
—3—
5.1 创建数据表
➢ 5.1.1 创建表的语法形式
【实例5-1】
字段 id
type name price num add_time
➢ 5.1.2 使用SQL语句设置约束条件
1
设置主键约束
2）定义所有字段后设置主键约束 语法形式为：
PRIMARY KEY (col_name)
为goods表id字段设置主键约束也可以使用这种方法，SQL语句如下：
CREATE TABLE goods ( id INT(11), …… PRIMARY KEY (id) );
CREATE TABLE goods( type VARCHAR(30) NOT NULL, …… );
—8—
5.1 创建数据表
➢ 5.1.2 使用SQL语句设置约束条件
4
设置唯一性约束
设置唯一性约束的关键字为UNIQUE。 1）定义字段时设置唯一性约束 语法形式为：
col_name data_type UNIQUE
1
查看表基本结构
查看表结构的关键字为DESCRIBE，语法形式如下：
DESCRIBE table_name;
【实例5-4】
执行DESCRIBE语句，查看goods表结构，效果如下所示。
mysql> DESCRIBE goods;
+--------------+------------------------------+-------------+-------+---------+--------------------------+