MySQL DDL 和 DML

1.使用 DDL 定义数据库表结构

1.SQL 简介

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

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

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

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

)

 

2.使用 DDL 维护数据库表结构

1.修改表结构

2.重命名表

3.添加外键约束

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

ALTER TABLE 表名 修改子句

语法说明如下：

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

 

 1.修改表结构

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

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

3.使用 DML 新增和更新表数据

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

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

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

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

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

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

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

语法说明如下：

关键字 INTO 可以省略。

字段列表是可选项。

字段列表由若干个需要插入数据的字段名组成，各字段使用“,”隔开。若省略了字段列表，则表示需要为表的所有字段插入数据。

值列表为必选项，该列表给出了待插入的若干个字段值，各字段值使用“,”隔开，并与字段列表一一对应。

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

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

 update 语句的语法格式如下：

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

 

语法说明如下：

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

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

 

4.使用 DML 删除表数据

删除表记录的语法如下：

DELETE FROM table_name [WHERE 条件表达式 ]

语法说明如下：

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

删除表中指定条件记录:

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

删除存在外键约束的主表记录:

delete from line where lineNo='....';

 总结：

创建数据表的 DDL 语法

添加字段和外键约束的 DDL 语法

解决存在外键约束的主表记录的删除异常：

设置“删除时”状态为“SET NULL”。这样当删除被从表引用的主表记录时，从表中这些引用记录的外键值均被系统赋值为 null。

设置“删除时”状态为“CASCADE”。这样当删除被从表引用的主表记录时，从表中这些引用记录均被级 联删除。