3.Mysql数据定义与操作

3.Mysql数据定义与操作

3.1 SQL发展史

SQL：结构化查询语言

MySQL：最流行的关系型数据库管理系统，体积小，速度快，总体拥有成本低，开放源码。

数据库基本语言

• 数据定义语言：SQL DDL （用来创建数据库中的各类对象，如表的创建、撤销与更改）
• 数据操作语言：SQL DML==（数据查询与数据更新）==（投影、选择、笛卡尔积、联接、聚集查询和数据插入、删除、操作）
• 数据保护语言：SQL DPL （授予或收回访问数据库的权限，协调事务间的动作）（维护数据的保密性，完整性，可用性）

数据库语言的特点

（1）SQL是非过程化的第四代编程语言

（2）一种语言多种使用方式

（3）SQL是关系数据库的标准语言

（4）结构简洁、易学易用

3.2 数据定义

数据定义包括数据库对象的创建、删除和修改三个部分。

对象标识符和保留字必须以一个字母或下划线开头，随后的字符可以是字母、下划线、数字、美元符号$($)$

MySQL中大小写不敏感，只有引号里面的字符才区分大小写

SQL的保留字用大写字母，表名、属性名等所有数据库对象名全部使用小写字母

表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。

数据在表中是按照行和列的格式来存储的。

创建表就是约定表中各个属性的属性名及其数据类型

MySQL数据类型

• 数值类型
• 字符串
• 日期/时间类型

表的创建：

create table <表名>(<列名><数据类型>[<列级别约束条件><默认值>][,<列名><数据类型>[<列级别约束条件><默认值>]]);

$[]$内的是可选项，$<表名>$是所要定义的表的名称

注意：

• 表的定义就是逐列说明属性名称和属性类型
• 每个语句末尾用分号表示语句的结束

表结构的修改

新增表结构

新增表结构的数据都为NULL

alter table <表名> add <列名> <类型>

删除表结构

alter table <表名> drop <列名>

修改属性的数据类型

alter table <表名> modify <属性名> <数据类型>

修改属性的属性名

alter table <表名> change <旧属性名><新属性名><新属性类型>

表的改名

alter table <旧表名> rename [to] <新表名>

表的撤销

drop table [if exists] <表名>

可以同时删除多张数据表，多个表名之间用逗号分隔

3.3 投影与广义投影

select [all | distinct] <目标列表达式，……>

from <表名，……>

• 查询输入是from子句中列出的关系
• *代表所有，目标列表达式可以是一个属性名，也可以是常量表达式
• 默认不去重
• order by排序，默认为升序
• • 升序ASC，排序列为空值的行最先显示
  • 降序DESC，排序列为控制的行最后显示
• 投影不对表做改变。

3.4 选择

from子句给出查询涉及的关系表

where子句给出查询条件

select子句给出满足查询条件元组行的哪些属性

• 比较：=，>, <, >=, <=, !=, <> 比较两个表达式的值
• 确定范围：<表达式1> [not] between <表达式2> and <表达式3> （不）在给定范围
• 是否空值：is [not] null 判断是否为空
• 是否属于集合：<元组行> [not] in <集合>
• 限定比较判断：all | some | any
• 串匹配：like | % | _
• 逻辑表达式：and | or | not

注意：in是集合选择，不是一个区间，例如 where ersalary in (6000, 9000)

串匹配选择：

字符串匹配可以使用”=“，”like“，like可以使用通配符。不使用通配符的话，=和like等价

通配符：

_：（下划线匹配任何单个字符）

%：一个百分号匹配0个或多个字符

$：表示以该符号紧跟的字符或字符串结尾的字符串

^：表示以该符号紧跟的字符或字符串开头的字符串

.：表示任意一个字符

$+$：表示前面的字符至少出现1次

*：表示前面的字符出现0次或任意多次

[]:表示一个字符集合中的任意一个字符

[^]：表示不在括号中的任何字符

{n}：表示前面的字符出现n次

{m,n}：表示前面的字符出现至少$m$次，最多$n$次

\：转义符

3.5 集合操作

MySQL只支持并操作

select 语句1 union[all] select 语句2

计算并，必须要兼容的

结果若不声明，则自动去重

3.6 联接查询

联接类型：内联接和（左/右/全外联接）

联接条件：决定两个关系表中哪些元组应该匹配

联接形式如下：

t1 {[inner] | {LEFT | RIGHT | FULL} [outer]} join t2 on <逻辑表达式>
t1 {[inner] | {LEFT | RIGHT | FULL} [outer]} join t2 using (列1， ……，列n)
t1 natural {[inner] | {LEFT | RIGHT | FULL} [outer]} join t2

默认inner，内联接。

left，right，full默认外联接

on子句接收一个布尔表达式

using接收一个用逗号分隔开的属性名列表，必须是联接表共有的，其值相同时匹配。

natural是using的缩写形式，自动形成一个由两个表中同名的属性组成的using列表

在一个查询的from子句中，可以用自然联接将多个关系表结合在一起

P63总结

3.7 更名

from 表 [AS] 别名 (列别名1，……)

取了别名之后就不允许再用最初的名字了，如果声明的属性别名比表实际属性少，那么后面的属性就没有别名

select子句中，可以用旧名 as 别名更名

3.8 聚集查询

COUNT（数目），SUM（总和），AVG（均值），MAX，MIN

不允许对聚集函数进行复合运算

可以使用GROUP BY子句将聚集函数作用在单个或多个行集上。

利用后面所给的属性进行分组，所有属性上取值相同的元组行将被分在一个组。

例如：

select erdepa, AVG(erage) AS avg_age
from examiner
GROUP BY erdepa
ORDER BY avg_age

出现在SELECT语句中但没有被聚集的属性只能出现在GROUP BY中

也就是说，任何没有出现在GROUP BY子句中的属性如果出现在SELECT子句中的话，它只能出现在聚集函数中

用having进行条件限制

RANK（）函数可以给出元组的排名值

在执行过程中，先where进行筛选指定的，再通过group by分组。

3.10 嵌套查询

不相关子查询：子查询不依赖于父查询

相关子查询：子查询依赖于父查询

子查询作为表

• from子句嵌套
• with子句嵌套：可以用with子句定义临时关系，只在包含该with子句的查询语句中有效，with子句中的as不能省略。

子查询作为集合

主要用于where和having子句嵌套（带有IN）

带有any（some）或all谓词的子查询

比较运算符可与any或all谓词配合使用

例如

• $>$any 大于某一个 >all 大于所有
• != (<>) 不等于

带有EXISTS谓词的子查询

EXISTS谓词，即存在量词。带有子查询的EXISTS谓词返回逻辑真值TRUE或FALSE

子查询非空，则返回TRUE；子查询为空，EXISTS返回假值。

子查询作为标量

select，where，group by， order by子句嵌套

3.11 递归查询

递归查询必须是单调的

3.12 数据修改

元组的插入

insert into 表 属性…… values 常量……

表的插入

insert into 表 列 table 表

数据删除

delete from 表 where ……

数据更新

update 表 set 列名=表达式 where

题目：

查询（SELECT）语句中其它子句都可以不出现，但至少要有一个SELECT子句。（对）

PG中使用单引号做字符串常量的标识，对于包含单引号的字符串，直接使用双引号。（错）

详细解释如下：

如果要查询带单引号的字符串，需要在其单引号后再加一个单引号，或者在 ’ 符号前加一个转义符号 \ ，并在整个字符串前加一个 E 字母。

PostgreSQL支持聚集查询，允许从多个输入行中计算出一个结果。（对）

SUM和AVG的输入必须是数值型的。（对）解释： SUM和AVG的输入必须是数值型的，除这两个函数之外的其他聚集函数还可以用在非数值数据类型的列上。

SUM和AVG可作用在非数值数据类型的列上。（错）

select 3*3

from examinee；

假设examinee表中有三行元组，该语句的输出是9、9、9

在写嵌套查询语句时，如果能确定查询块只返回单行单列的单个值，查询块可以出现在单个属性名、单个表达式、单个常量，即单值表达式能够出现的任何地方。（对）

查询块也可以出现在集合能够出现的任何合适的地方。（对）

谓词EXISTS不可以用来测试一个集合是否有重复元组

MySQL不支持offset和limit的嵌套查询

<>ALL和!=ALL这两个符号与not in等价

下列_____不是数据定义语句。

选项：

A: UPDATE

B: CREATE

C: DROP

D: ALTER

答案: 【 UPDATE】

下列_____子句用来指定从哪些表里检索数据。（FROM）

下列表述中不正确的是（ ）。

选项：

A: 如果有 GROUP BY 子句，对 WHERE 选出的行按 GROUP BY 子句中指定列的值分组。

B: 如果有 GROUP BY 子句，对 HAVING 选出的行按 GROUP BY 子句中指定列的值分组。

C: 如果有 ORDER BY 子句， 输出按 ORDER BY 子句的要求进行排序。

D: 没有 FROM 子句就不能有 WHERE 子句；没有 GROUP BY 子句就不能有 HAVING 子句。

答案: 【 如果有 GROUP BY 子句，对 HAVING 选出的行按 GROUP BY 子句中指定列的值分组。】