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

为区分 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

非空

name

姓名

varchar

非空

gender

性别

varchar

—

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 维护数据库表结构

修改表结构的 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

重命名表的 DDL 语法如下：

RENAME TABLE table_name TO new_table_name

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

示例：rename table employee to emp;

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

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 分为两类四种：检索（查询）和更新（插入、删除、修改）。

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

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

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

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

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

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

示例：新增3 条线路。

线路号

所属分公司

起点站

终点站

线路长度（km）

车辆数

548

公交二公司

长丰大道东风村

发展大道红旗建材家居

22.8

625

公交一公司

南湖路保利公园九里

武汉植物园

18.1

523

公交六公司

芦沟桥路

古田二路陈家墩

17.5

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

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 子句，则删除整张表中的数据。

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

“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=' 公交二公司 ';

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

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

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

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

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