四级数据库_08_SQL语言

第8章关系数据库操作语言SQL

返回到『四级数据库』

8．1 SQL支持的数据类型

8．1．1 数值型

准确型
近似型

8．1．2 字符串型

普通编码字符串类型；
统一编码字符串类型—Unicode编码；
二进制字符串类型；

8．1．3 日期时间类型

8．1．4 货币类型

8．2 定义和维护关系表

8．2．1 关系表的定义与删除

定义表

CREATE TABLE <表名> (

<列名><数据类型>[列级完整性约束定义]

{ , <列名><数据类型>[列级完整性约束定义]… }

[,表级完整性约束定义]

（not null 和 default不能在表级定义）

)

by 未闻花名🌸vwhm.net

列级完整性约束：
- NOT NULL：取值非空；
- DEFAULT：指定列的默认值，形式：DEFAULT 常量；
- UNIQUE：列取值不重复；
- CHECK：限制列的取值范围，形式：CHECK（约束表达式）；
- PRIMARY KEY：指定本列为主码；
- FOREIGN KEY：定义本列为引用其他表的外码；（foreign key 列名 references 外表名）
删除表

DROP TABLE <表名>

8．2．2

修改表结构

ALTER TABLE <表名>（添加add，删除drop，修改alter）

常用：

alter/drop column 列名；

add 列名；

add/drop constraint

by 未闻花名🌸vwhm.net

8．3 数据操作语言

8．3．1 数据查询

1、查询语句的基本结构：

SELECT <目标列名序列> FROM <数据源> （into 列名3）

{

1、普通选择：WHERE + 选择条件（p138）（结果是选出行）

2、排序（选出的结果排序）：ORDER BY（ASC/DESC）

3、分组: GROUP BY（对列值（属性值），相同为一组）；

HAVING（依据什么条件来分组，相当于对于分组时的WHERE）

4、聚合函数统计数据。

}

在介绍 GROUP BY 和 HAVING 子句前，
我们必需先讲讲sql语言中一种特殊的函数:聚合函数，例如 SUM, COUNT, MAX, AVG 等。
这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

SELECT SUM(population) FROM Japan
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有Japan的总人口数。

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。

当你指定 GROUP BY region 时，属于同一个region(地区)的一组数据将只能返回一行值，

也就是说，表中所有除region(地区)外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。

HAVING子句可以让我们筛选成组后的各组数据，

WHERE子句在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前.
而 HAVING子句在聚合后对组记录进行筛选。

让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句。
SQL实例:
一、显示每个地区的总人口数和总面积:

SELECT region, SUM(population), SUM(area) FROM Japan GROUP BY region

先以region字段把返回所有记录分成多个不同region的组，这就是GROUP BY的字面含义。

分完组后，然后用聚合函数对每组中的不同字段(一或多条记录)作运算。

二、显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM Japan
GROUP BY region
HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，

因为表中基本是不可能会存在这样一条记录的。
相反，HAVING子句可以让我们筛选已经分组后的各组数据.

比较：SELECT age，cup FROM girls WHERE age < 14；
确定范围：WHERE age （NOT）BETWEEN 12 AND 14；
确定集合：WHERE age （NOT）IN （‘12’，‘13’….‘14’）；
字符串匹配：WHERE age LIKE <匹配符>；
四种<匹配符>：

（A）_（下划线）：匹配任意一个字符；

（B）%（百分号）：匹配0个或多个字符；

（C）[ ]：匹配[ ]中的任意一个字符；

（D）[^]：不匹配[ ]中的任意一个字符；

涉及空值的查询：WHERE age IS （NOT）NULL；
多重条件查询：AND（条件必须全部为TRUE，结果才为TRUE），OR（任一条件为TRUE，结果即为TRUE）；
对查询结果进行排序：ORDER BY A [ASC（顺序） | DESC（逆序）]；
列别名：列名 AS 新列名；
消除取值相同的行：SELECT DISTINCT age FROM girls；
使用聚合函数统计数据：SQL的聚合函数：asdf
- COUNT（*）：统计表中元组的个数；
- COUNT（[ALL （全部）| DISTINCT（无重复）] <列名>）：统计本列非空列值的个数；
- SUM（列名）：计算列值的总和（必须是数值型列）；
- AVG（列名）：计算列值平均值（必须是数值型列）；
- MAX（列名）：求列最大值；
- MIN（列名）：求列最小值；
对查询结果进行分组计算：
- 使用GROUP BY；
- 使用HAVING子句；
连接查询；（对于多张表）
- 内连接：SELECT * FROM 表1 JOIN 表2 ON （连接条件）；
- 两个表内连接的结果是两个表满足连接条件的全部列的一个表，连接的根本原因在于，存在有多个表，记录了主码与不同行为的关系，而目的是需要统计主码与不同行为的总体情况
- 自连接：一种特殊的内连接，相互连接的表在物理上是同一张表，但通过为表取别名的方法，在逻辑上分为两张表；
- 外连接：输出不满足连接条件的元组，
- 也就是说，有满足和不满足的格式：

SELECT * FROM 表1

LEFT（表一全) | RIGHT(表二全）

OUTER JOIN 表2 ON （连接条件）

查询语句的扩展：
- 合并多个结果集：
- SELECT 语句1 UNION SELECT 语句2……，
- 使用UNION的两个基本规则：All的作用
  - 所有查询语句中列的个数和列的顺序必须相同；
  - 所有查询语句中对应的数据类型必须兼容；
- 将查询结果保存到新表中：SELECT 查询列表序列 INTO 新表名 FROM 数据源；
- 使用TOP限制结果集行数：TOP n [percent] [WITH TIES]
  - TOP n ：表示取查询结果的前n行；
  - TOP n percent：表示取查询结果的前n%行；
  - WITH TIES：表示包括并列的结果；
- 使用CASE表达式：
  - 简单CASE表达式：
  - 搜索CASE表达式；

5、子查询：如果一个SELECT语句是嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中，则称为子查询或内层查询，包含子查询的语句称为主查询或外层查询；

使用子查询进行基于集合的测试，形式：WHERE 表达式 [NOT] IN （子查询）；
使用子查询进行比较测试，形式：WHERE 表达式比较运算符（子查询）；
使用子查询进行存在性测试，形式：WHERE [NOT] EXISTS （子查询）；

8．3．2 数据修改

添加数据：INSERT [INTO] 表名 VALUE 值列表；
使用插入单行语句时要注意：
- 值列表中的值与列名表中的列按位置顺序对应，要求它们的数据类型必须一致；
- 如果[表名]后边没有指明列名，则值列表中的值的顺序必须与表中列的顺序一致，且每一列均有值；
更新数据：形式 UPDATE 表名 SET [列名（值）=表达式] [WHERE 更新条件（选择行）]；
删除数据：：形式DELETE [FROM] 表名 [WHERE 删除条件]；

8．4 索引

创建索引：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX 索引名 ON 表名

UNIQUE：表示要创建的索引是唯一索引；
CLUSTERED：表示要创建的索引是聚集索引；
NONCLUSTERED：表示要创建的索引是非聚集索引；（默认）

删除索引：DROP INDEX 索引名；

8．5 视图

8．5．1 定义视图

语法格式：CREATE VIEW 视图名 AS SELECT 语句 [WITH CHECK OPTION]
需要注意下列几点：
- 在定义视图时要么指定全部视图列，要么全部省略不写。
- 如果省略了视图列名，则视图的列名与查询语句的列名相同。
- 但如下情况则要明确指出组成视图的所有列名：
  - 某个目标列不是单纯的属性名，而是计算函数或列的表达式；
  - 多表连接时选出了几个同名列作为视图的字段；
  - 需要在视图中为某个列选用新的列名。
- WITH CHECK OPTION选项表示:
- 通过视图对数据进行增加、删除和更改操作时
- （以后）要保证对数据的操作结果要满足定义视图时指定的WHERE子句条件；
视图通常用于查询数据，也可修改基本表中的数据，但不是所有的视图都可以这样。
定义单源表视图—视图数据可只取自一个基本表的部分行、列，这样的视图行列与基本表行列对应，这样定义的视图一般可以进行查询和更改数据操作
定义多源表视图—视图数据可以来自多个表中，这样定义的视图一般只用于查询，不用于修改数据。
在已有视图上定义新视图—可以在视图上再建立视图，这时作为数据源的视图必须是已经建立好的。
定义带表达式的视图—在定义基本表时，为减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。
所以定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。这些派生属性由于在基本表中并不实际存在，因此，也称它们为虚拟列。包含虚拟列的视图也称为带表达式的视图。
含分组统计信息的视图—指定义视图的查询语句中含有GROUP BY 子句，这样的视图只能用于查询，不能修改数据。

8．5．2 删除视图

格式为：DROP VIEW <视图名>

8．5．3 视图的作用

简化数据查询语句；
使用户能从多角度看到同一数据；
提高了数据的安全性；

提供了一定程度的逻辑独立性（外模式）

← 返回
1. 数据类型
2. 定义表
3. SQL
4. 索引
5. 视图