数据定义和操作

一、数据定义语言和操作语言

1. 分析需求设计数据库表

根据“优乐网”系统需求：

 此系统满足以下需求：

系统支持生成商品的入库和出库。入库之后的商品可以在平台显示

所有用户都可以浏览系统的商品信息，只有注册用户才能订购团购商品和服务

系统支持订单数据自动生成

为方便用户检索所需的团购商品和服务信息，系统支持按商品和服务的类型、商家以及

所在地区进行分类

设计数据库表：

2. 数据定义语言

（1）概念：数据定义语言（Data Definition Language，DDL），主要是用在定义或改变

表的结构、数据类型、表之间的连接和约束等初始化工作上，比如 create、alter、drop

等。

（2）常用DDL语句：

数据库创建和维护的DDL语句：

 create database database_name 创建数据库

 show databases 查看数据库

 drop database database_name 删除数据库

 rename database old_database_name to new_database_name 更改数

据库名

 数据库表创建：

 create table table_name(

字段名 1, 数据类型【列级别约束条件】【默认值】,

字段名 2, 数据类型【列级别约束条件】【默认值】,

...

【列级别约束条件】

)

 演示例子：

CREATE TABLE `t_user` (

 `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',

`NAME` varchar(50) NOT NULL COMMENT '用户名',

`PASSWORD` varchar(10) DEFAULT NULL COMMENT '用户密码',

PRIMARY KEY (`ID`)

 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk

1 数据库表结构修改：

2 //表结构语法：

3 alter table 表名 修改子句

4 修改子句类型：

5 新增字段子句：

6 add column_name data_type [not null] [default default_value]

7 变更字段名子句：

8 change column_name new_column_name data_type [not null] [default default_

value]

9 更新字段类型子句：

10 modify column_name new_data_type [not null] [default default_value]

11 删除字段：

12 drop column column_name

13 删除约束：

14 drop unique constraint_name;//删除唯一约束

15 drop constraint 约束名；//删除约束

16 drop primary key;//删除主键

修改表结构例子：

1 具体要求如下：

2 新增字段供应商地址 address，类型为 varchar(50)。

3 将字段 mobile 更名为 tel，字段类型维持不变，仍为 varchar(20)。

4 将字段 pName 的类型修改为 varchar(50)，原类型为 varchar(20)。

5 删除字段 contact。

6 DDL语句：

7 alter table provider add address varchar(50), change mobile tel varchar(2

0),

8 modify pName varchar(50), drop column contact;

9 例子：

10 alter table t_user add address varchar(50), change NAME USER_NAME varcha

r(20),

11 modify PASSWORD varchar(8)

3. 数据操作语言

（1）概念：数据操作语言DML，实现对数据库中数据的操作，以 insert、update、

delete 3 种指令为核心，分别代表插入、更新与删除。

(2)插入语句

1 语法结构：如果同时插入多行则values后跟多个(值列表)，以都好相隔

2 insert [into] table_name[( 字段列表 )] values( 值列表 )

3 例子：

4 insert into t_user(USER_NAME,PASSWORD,address)

5 values('张三','11111111','xxx学院');（3）更新语句：使用“update”SQL 语句来操作，该语句可以对表中的一行、多行甚至

所有记录进行修改

1 语法结构：

2 update table_name set

3 字段名 1= 值 1, 字段名 2= 值 2, … , 字段名 n= 值 n;

4 [where 条件表达式 ]

5 例子：

6 update t_user set USER_NAME=concat('欧阳',USER_NAME);

（4）删除语句：

1 语法结构：

2 delete from table_name [where 条件表达式 ]

3 注意：当删除有主从关系的表记录时，为避免发生删除时异常，一定要事先设置从表

4 外键约束的“删除时”状态为 null，或者为“cascade”。在通常情况下，都是设置从表

5 外键约束的“删除时”状态为 null，而不是“cascade”，因为这样容易误删除从表数据。

6

7 例子：

8 DELETE from t_user where USER_NAME='欧阳张三'

二、数据查询

1. select语句基本结构

（1）概念：select 语句可以使数据库服务器根据客户的要求查询所需要的信息，并按规定

的格式返回给客户

（2）select语法结构：

1 select <column1, column2, column3 … > from <table_name>

2 [where < 条件表达式 >]

3 [group by column1, column2, column3 … | having < 条件表达式 >]

4 [order by < column1, column2, column3 … > [asc 或 desc]]

（3）字段取别名，表取别名，按需查字段

1 select shopName as 商家名 , shopAddress as 商家地址 from product shop

2

3 例子：

4 SELECT * from t_user;

5 select user_name as '姓名' ,password as '密码' from t_user as userinfo

2. 查询结果排序

order by 字段名 排序方式:

可以对查询结果按照“字段 1，字段 2…”的次序进行升序或降序显示。

关键字 asc 表示升序（默认为升序，故升序时可省略 asc），desc 表示降序。

多列排序的规则为：依照排序列从左至右的次序依次降低例子：

1 SELECT * from t_user u order by id desc,phone asc;

3. 过滤重复字段

1 distinct：用于过滤重复的字段信息。

2 select distinct director from movie

4. 限定指定记录

limit子句：MySQL 提供的 limit 子句可以被用于强制select 语句返回指定的记录数。

limit [offset,] rows

参数 offset 和 rows 必须为整数，offset 可以省略。

offset 指定第一个返回记录行的偏移量。注意：初始记录行的

偏移量是 0，而不是 1。

rows 指定返回记录行的最大数目

1 select movieName 电影名 ,filmLength 片长（分钟） from movie

2 order by filmLength

3 limit 0,5

5. 模糊查询

like 运算符：进行模式匹配查询

通配符：

n%：包含零个或多个字符的任意字符串。

n_（下划线）：任意单个字符。

n[]：指定范围（如 [a-d]）或集合（[abcdef]）中的任意单个 字符。

n[^]：不属于指定范围（如 [^a-d]）或集合（[^abcdef]）中

的任意单个字符

1 例子：

2 select movieName 电影名 ,filmLength 片长（分钟） from movie

3 where actors like ‘%黄%’

三、聚合函数和分组统计

1. 聚合函数

（1）概念：聚合函数是 SQL 基本函数，它对一组值执行计算，并返回单个值。

（2）聚合函数：

除了 count(*) 函数以外，聚合函数都会忽略空值。

 例子：

1 获取电影的数量、最长片长、最短片长和平均片长

2 select count(*) 数量 ,max(filmLength)（分钟） 最长片长 ,

3  min(filmLength) 最短片长（分钟）,avg(filmLength) 平均片长（分钟）

4  from movie

5 select count(director) 导演信息不为空的电影数量 from movie

2. group by分组统计

（1）概念：group by 子句可以将数据划分到不同的组中，实现对记录的分组统计

（2）语法结构：

1 group by 字段列表 [having 条件表达式 ]

2 例子：

3 select typeID 类型 ID,count(*) 数量 ,avg(filmLength) 平均片长（分钟）,

4 max(ticketPrice) 最高票价（元） from movie

5 where ticketPrice<=100 ‐‐分组前过滤

6 group by typeId

7 having avg(filmLength)>100 ‐‐对分组结果过滤

8 order by avg(filmLength)

四、连接查询

1. 内连接查询

（1）概念：内连接也称为连接，它还可以被称为完全匹配连接。

内连接使用比较运算

符比较每个表共有的列的值。(what)

（2）语法结构：

1 select fieldlist from table1

2 [inner] join table2

3 on table1.column1=table2.column2 ‐‐column1 和 column2 通常为主键和外键

2. 多表连接查询

（1）概念：

如果在 from 子句中，直接列出所有要连接的表，然后在 where 子句中指定连

接条件，此为简单多表查询。

简单多表连接查询是内连接查询的简单表现形式，它与内连接查询的功能相同

（2）语法结构：

1 select fieldlist from table1, table2 where table1.column1=table2.column2

3. 外连接查询

（1）概念

外连接可以是左外连接、右外连接或全连接.（what） 在内连接查询中，只有满足连接条件的记录才能出现在查询结果中。但在实际应 用中，如果希望不满足连接条件的记录也在查询结果中出现，这时就需要使用外连接 查询。(why)

（2）左外连接（left join）

结果集包括 left join 子句中指定的左表的所有行，而不仅仅是连接列所匹配的 行。

如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择 列表列均为空值。

 例子：

1 ‐‐需求

2 采用左外连接方式显示所有电影的相关信息，要求输出电影名、导演名、票价和类型

3 ‐‐sql语句

4 select movieName 电影名 ,director 导演名 ,ticketPrice 票价（元）,typeName

类型

5 from movie m

6 left join movie_type mt

7 on m.typeId=mt.id

（3）右外连接（right join）

右外连接是左外连接的反向连接，它返回右表的所有行。 如果右表的某行在左表中没有匹配行，则在相关联的结果集行中，表的所有选择 列表列均为空值。

 例子：

1 ‐‐需求：

2 采用右外连接方式显示所有电影类型的相关信息，要求输出电影名、导演名、票价和类型。

3 ‐‐sql语句：

4 select movieName 电影名 ,director 导演名 ,ticketPrice 票价（元）,typeName

类型

5from movie m right join movie_type mt on m.typeId=mt.id

（4）全连接（full join）

全连接返回左表和右表中的所有行。

当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。MySQL 目前在语法上暂不支持全连接，但可以通过将左外连接和右外连接使用

union 进行连接来实现全连接。