MySQL数据操作与查询笔记 • 【第3章 DDL 和 DML】

全部章节   >>>>

---

本章目录

3.1 使用 DDL 定义数据库表结构

3.1.1 SQL 简介

3.1.2 维护数据库和创建数据表

3.2 使用 DDL 维护数据库表结构

3.2.1 修改表结构

3.2.2 重命名表

3.2.3 添加外键约束

3.3 使用 DML 新增和更新表数据

3.3.1 插入表纪录

3.3.2 修改表纪录

3.4 使用 DML 删除表数据

3.4.1 删除没有被关联的表记录

3.4.2 删除被关联的表记录

总结：

---

3.1 使用 DDL 定义数据库表结构

3.1.1 SQL 简介

结构化查询语言（Structured Query Language,SQL）是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，专用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名。

SQL 作为关系型数据库管理系统的标准语言（ANSI X3.135-1986），在 1987 年得到国际标准组织的支持成为国际标准。不过不同数据库系统之间的 SQL 不能完全通用。

SQL 重点内容包括数据定义语言 DDL 和数据操作语言 DML

3.1.2 维护数据库和创建数据表

数据定义语言（Data Definition Language，DDL）是一种用于描述数据库所需存储的现实世界实体的专门语言， 其主要工作是用于定义和维护数据库以及数据表结构。

创建数据库的 DDL 语法如下：

CREATE DATABASE database_name

语法说明：

database_name 为数据库名，如创建“demo”数据库的 DDL 语句为：create database demo。

为区分 SQL 语句中的关键字，通常在进行语法定义时，将 SQL 语句中的关键字设为大写，

如“CREATE DATABASE”。 但 SQL 语 句 本 身 不 区 分 大 小 写， 即“CREATE DATABASE demo”“create dadabase demo”“create database DEMO”这三条语句意义相同。

删除数据库的 DDL 语法如下：

DROP DATABASE database_name

查看数据库的 SQL 语句如下：

SHOW DATABASES

创建数据表的 DDL 语法如下：

CREATE TABLE table_name(
column_name data_type [NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT] [],
...
[PRIMARY KEY(pk_name),]
[FOREIGN KEY(fk_name) REFERENCES referenced_table_name(ref_pk_name)]
)

语法说明：

“[]”中的内容为可选项。table_name：表名。

column_name：字段名。

data_type：字段数据类型。default_value：默认值。

auto_increment：主键值自动增长。pk_name：当前表中的主键名。

fk_name：当前表中的外键名。

referenced_table_name：被引用表名（主表名）。ref_pk_name：被引用表主键名。

示例：使用 DDL 创建驾驶员表

字段名	说明	类型	长度	约束
driverID	驾驶员编号	int	—	主键、自动增长
licenseNo	驾照号码	varchar	20	非空
name	姓名	varchar	20	非空
gender	性别	varchar	2	—
birth	出生年月	date	—	—

use bus; -- 选择数据库
create table driver(
    driverID int primary key auto_increment,
    licenseNo   varchar(20),
    name varchar(20),
    gender varchar(2),
    birth date
)

3.2 使用 DDL 维护数据库表结构

3.2.1 修改表结构

修改表结构的 DDL 语法如下：

ALTER TABLE 表名 修改子句

语法说明：“修改子句”允许指定多个动作，其动作间使用逗号分隔。

新增字段修改子句表达式如下：

ADD column_name data_type [NOT NULL] [DEFAULT default_value]

变更字段子句表达式如下：

CHANGE column_name new_column_name data_type [NOT NULL] [DEFAULT default_value]

更新字段子句表达式如下：

MODIFY column_name new_data_type [NOT NULL] [DEFAULT default_value]

删除字段子句表达式如下：

DROP COLUMN column_name

删除唯一约束子句表达式如下：

DROP UNIQUE constraint_name

示例：修改 driver 表内容，要求如下：

新增字段 phone，它的类型为 varchar(20)。

将字段 birth 更名为 birthday，字段类型维持不变，仍为 date 类型。

将字段 goodsName 的类型修改为 varchar(100)，原类型为 varchar(50)。

删除字段 gender。

alter table driver
    add phone varchar(20),
    change birth birthday date,
    modify goodsName varchar(100),
    drop column gender

3.2.2 重命名表

重命名表的 DDL 语法如下：

RENAME TABLE table_name TO new_table_name

语法说明：将表 table_name 更新为 new_table_name。

示例：rename table employee to emp;

3.2.3 添加外键约束

添加外键约束的语法如下：

ALTER TABLE table_name(
ADD CONSTRAINT constraint_name FOREIGN KEY(fk_name) REFERENCES referenced_table_name(ref_pk_name)]
)

语法说明如下：

constraint_name：约束名。

fk_name：当前表中的外键名。

referenced_table_name：被引用表名（主表名）。ref_pk_name：被引用表主键。

删除外键约束子句表达式如下：

DROP FOREIGN KEY constraint_name

示例：先删除在第 2 章中使用 Navicat for MySQL 在车辆表 vehicle 中所创建的参照线路表 line 的外键约束，然后再使用 DDL 语句重新创建该约束

use bus;
alter table vehicle drop foreign key fk1; -- 删除名为 fk1 外键约束
alter table vehicle add constraint fk_vehicle_line -- fk_vehicle_line 为新添加外键名foreign key(lineID) references line(lineID);

3.3 使用 DML 新增和更新表数据

数据操纵语言（Data Manipulation Language，DML） 由 DBMS 提供，用于让用户或程序员使用，实现对数据库中数据的操作。基本的 DML 分为两类四种：检索（查询）和更新（插入、删除、修改）。

3.3.1 插入表纪录

新增一条新纪录的语法如下：

INSERT [INTO] table_name[( 字段列表 )] VALUES( 值列表 )

语法说明如下：

• 关键字 INTO 可以省略。
• 字段列表是可选项。
• 字段列表由若干个需要插入数据的字段名组成，各字段使用“,”隔开。若省略了字段列表，则表示需要为表的所有字段插入数据。
• 值列表为必选项，该列表给出了待插入的若干个字段值，各字段值使用“,”隔开，并与字段列表一一对应。

注意：

（1）向字符串类型的 char、varchar、text 以及日期型字段插入数据时，字段值要括于单引号中。（在 MySQL 中单引号和双引号没有任何区别，但单引号为 SQL 标准，所以提倡使用单引号。）

（2）向自增型 auto_increment 字段插入数据时，建议插入 null 值，此时将向自增型字段插入下一个编号。

（3）向默认值约束字段插入数据时，字段值可以使用 default 关键字，表示插入的是该字段的默认值。

（4）插入新纪录时，需要注意表之间的外键约束关系，原则上先为主（父）表插入数据，然后再为从（子） 表插入数据。

示例：新增一条新线路，要求新增的记录使用 line 表中所有的字段。

线路号	所属分公司	起点站	终点站	线路长度（km）	车辆数
807	通恒公司	工业四路 23 街坊	振兴路复兴村小区	23.1	18

insert into line values(NULL, '807 ', ' 通恒公司 ', ' 工业四路 23 街坊 ', ' 振兴路复兴村小区 ',23.1,18);

NULL 与主键自增匹配

新增“807”线路的 lineID 值为 9，而其上一条记录的 lineID 值为 7，而不是 8，这是什么原因造成的？

示例：新增一条新线路，向表中指定的字段插入数据。

线路号	所属分公司	起点站	终点站
621	公交一公司	沿海赛洛城公交场站	后湖五路淌湖二村

insert into line(lineNo,company,from_station,end_station)

values('621', ' 公交一公司 ', ' 沿海赛洛城公交场站 ', ' 后湖五路淌湖二村 ');

注意：insert 语句中，字段列表中字段的次序可以任意，但值列表中字段值的次序要与字段列表中的字段次序必须一一对应。

示例：新增一条新线路，在 insert 语句中使用默认值。

线路号	起点站	终点站	线路长度（km）	车辆数
508	幸福路百步亭路口	工农路舵落口	24.5	20

insert into line
values(null,'508',default,' 幸福路百步亭路口 ',' 工农路舵落口 ',24.5,20);

使用 insert 语句可以一次性地向表中批量插入多条记录，语法格式如下：

INSERT INTO table_name[( 字段列表 )] VALUES
( 值列表 1),
( 值列表 2),
...
( 值列表 n)

示例：新增3 条线路。

线路号	所属分公司	起点站	终点站	线路长度（km）	车辆数
548	公交二公司	长丰大道东风村	发展大道红旗建材家居	22.8	15
625	公交一公司	南湖路保利公园九里	武汉植物园	18.1	12
523	公交六公司	芦沟桥路	古田二路陈家墩	17.5	14

insert into line values
(null,'548',default,' 长丰大道东风村 ',' 发展大道红旗建材家居 ',22.8,15),
(null,'625',' 公交一公司 ',' 南湖路保利公园九里 ',' 武汉植物园 ',18.1,12),
(null,'523',' 公交六公司 ',' 芦沟桥路 ',' 古田二路陈家墩 ',17.5,14);

3.3.2 修改表纪录

update 语句的语法格式如下：

UPDATE table_name set
字段名 1= 值 1, 字段名 2= 值 2,..., 字段名 n= 值 n;
[WHERE 条件表达式 ]

语法说明如下：

where 表达式指定了表中的哪些记录需要修改，若省略了 where 子句，则表示修改表中的所有记录。

set 子句指定了要修改的字段以及该字段修改后的值。

示例：在国土测绘部门提供每条线路的具体里程数之前，公交集团拟将所有公交线路的线路长度置为 null，表明线路长度暂时未知。

update line set miles=null;

示例：市交通委员会为加强公交集团在市民出行交通的主导性地位，通过资本运作方式拟将公交集团的控股公司“通恒公司”转变为公交集团的全资子公司，因而需要将“通恒公司”更名为“公交通恒公司”，为增大该公司的运力， 还将该公司所有线路的车辆数增加 4 台。

update line set company=concat(' 公交 ', company), number=number+4 where company='通恒公司 '

concat(str1,str2,…) 为 MySQL 连接字符串函数，用于返回字符串 str1、str2…的连接结果。

3.4 使用 DML 删除表数据

删除表记录的语法如下：

DELETE FROM table_name [WHERE 条件表达式 ]

语法说明如下：

WHERE 条件子句为可选，若不存在 WHERE 子句，则删除整张表中的数据。

3.4.1 删除没有被关联的表记录

示例：删除表中所有记录。

“create table line_new like line;”的作用是创建一个新表 line_new，该表的表结构与 line 相同。
“insert into line_new select * from line”的作用是将 line 表中的所有记录插入到 line_new 表。
“delete from line_new”将删除 line_new 表中所有的记录。

“delete from line_new”  仍然保持自增型字段的值

示例：删除表中指定条件记录。

delete from line_new where company=' 公交二公司 ';

3.4.2 删除被关联的表记录

示例：删除存在外键约束的主表记录

公交集团为方便市民出行、优化公交线路，拟在新建住宅小区周边新增一些线路的同时，裁掉老城区中部分重复度高的线路，如删除 523 公交线路

查看 line 表可知线路 523 的线路编号 lineID 的值为“14”。

查看车辆表 vehicle，发现其中有记录参照主表 523 线路（523 线路的 lineID 值为“14”）。

由于存在从表 vehicle 中部分记录参照（引用）主表 line 中记录（如 523 线路），因此使用 delete 语句删除 523 线路时会出现删除异常。

方法一：设置“删除时”状态为“SET NULL”

方法二：设置“删除时”状态为“CASCADE”

由于方法二使用了级联删除规则，容易误删从表数据，所以使用时需特别慎重。

总结：

• 创建数据表的 DDL 语法
• 添加字段和外键约束的 DDL 语法
• 解决存在外键约束的主表记录的删除异常：
• 设置“删除时”状态为“SET NULL”。这样当删除被从表引用的主表记录时，从表中这些引用记录的外键值均被系统赋值为 null。
• 设置“删除时”状态为“CASCADE”。这样当删除被从表引用的主表记录时，从表中这些引用记录均被级 联删除。