数据库笔记-关系数据库的操作

SQL数据库的三级结构

三级结构

关系模式称为"基本表"，存储模式称为"存储文件"，子模式称为"视图"，元组称"行"，属性称"列"。

SQL数据库体系的结构要点如下：

• (1)一个SQL数据库是表的汇集。
• (2)一个SQL表由行集构成，行是列的序列，每列对应一个数据项。
• (3)表或者是基本表，或者是视图。基本表是实际存储在数据库中的表，视图由是由若干基本表或其他视图构成的表的定义。
• (4)一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。存储文件与物理文件对应。
• (5)用户可以用SQL语句对表进行操作，包括视图和基本表。
• (6)SQL的用户可以是应用程序，也可以是终端用户。

SQL语句（结构化查询语言）

• 数据定义语言（DDL）负责创建，修改，删除表，索引和视图等对象 -Create，Alter，Drop
• 数据操作语言（DML）负责数据库中的数据的插入，修改，查询和删除操作 -Insert，Update，Select，Delete
• 数据控制语言（DCL）用来授权和撤销用户对数据的操作权限 -Grant，Revoke

数据定义

基本数据类型

• 数值型：包括 int、smallint、decimal(p，d)、numeric(p，d)
• 字符串型：char(n) 、nchar(n)、varchar(n)、nvarchar(n)前2者是定长，后2者为变长串
• 时间型：datetime、date
• 以上类型中不常见的解释：
  • decimal(p，d)(精确数值型，共p位，小数点后有d位)
  • numeric(p，d) (共p位，小数点后有d位)
  • nchar：其定义形式为Nchar（n）。它与Char数据类型类似，不同的是Nchar数据类型n的取值为1~4000。Nchar数据类型采用Unicode标准字符集，Unicode标准用两个字节为一个存储单位，其一个存储单位的容纳量就大大增加了，可以将全世界的语言文字都囊括在内，在一个数据列中就可以同时出现中文、英文、法文等，而不会出现编码冲突。
  • Nvarchar：其定义形式Nvarchar（n）。它与Varchar数据类型相似，Nvarchar数据类型也采用Unicode标准字符集，n的取值范围为1~4000。

基本表

基本表的创建：(建立表结构)

CREATE TABLE 基本表名 
(列名，类型， 
…… 
完整性约束...) 
--完整性约束包括主键子句(PRIMARY KEY)、检查子句(CHECK)和外键子句(Foreign KEY).

基本表结构的修改

-- 增加新列： 
ALTER TABLE 基本表名 ADD 列名 类型 [列的完整性约束]
-- 删除原有的列： 
ALTER TABLE 基本表名 DROP COLUMN 列名
-- 增加表级约束： 
ALTER TABLE 基本表名 ADD 表级完整性约束 
或 ALTER TABLE 基本表名 ADD CONSTRAINT 表级完整性约束名 约束条件
-- 删除原有表级约束： 
ALTER TABLE 基本表名 DROP CONSTRAINT 约束名
--修改数据列，只能修改数据列的数据类型，注意其中的限制：
ALTER TABLE 基本表名 ALTER COLUMN 列名 新的数据类型

基本表的撤消

DROP TABLE 基本表名 

索引

索引的创建：

CREATE [CLUSTERED][UNIQUE] INDEX 索引名 ON 表名(列名[ASC|DESC]) 

说明

• 列名后面需要指定ASC[升序]或DESC[降序] 若不指定，默认为升序
• 本语句建立索引的排列方式为首先以列名中的第一个排序，该列值相同的记录，按下一列名的值排序
• UNIQUEL规定索引的每一个索引值只对应表或视图中唯一的记录，可允许存在NULL值，但不允许出现多个NULL值
• CLUSTERED：表示该索引为聚簇索引，一个表或者视图中只允许有一个聚簇索引，建立索引后，表在磁盘中的物理顺序与聚簇索引一致。在最常查询的列上建立聚簇索引可以加快查询速度，在经常更新的列上建立聚簇索引，则DBMS维护索引的代价太大
• NONCLUSTERED：规定此索引为非聚簇索引，是SQL的默认选择。一个表可以有多个非聚簇索引。对于非聚簇索引，数据的物理存储顺序独立于索引存储顺序。每个索引行均包含非聚簇键值和一个或多个含定位器（指向包含该值的行）每个索引均可以提供对数据的不同排序次序的访问。

设计索引的情况分析

• 适合建索引
  • 经常被查询，如经常在WHERE子句中出现的属性
  • 在order by子句使用的属性
  • 频繁出现在连接条件中的属性，即主码或外部码
  • 该列的值唯一的属性
  • 主码或外部码应建立索引
  • 对于以查询为主或只读的表，可以多建索引
  • 对于范围查询（=、<、>、>=、<=比较符确定查询范围）可在相关属性上建立索引
  • 使用聚集函数（MIN、MAX、AVG、SUM、COUNT）或者排序输出的属性最好建立索引
• 不适合建索引
  • 太小的表
  • 在查询中很少被引用的属性
  • 包含太多重复选用值得属性
  • 一些特殊数据类型的属性不能建立索引，BIT，TEXT，IMAGE等

索引的撤消：

DROP INDEX 表名.索引名 

数据查询

SELECT语句的基本句法

SELECT-FROM-WHERE句型

SELECT 列名表(逗号隔开) 
FROM 基本表或视图序列 
WHERE 条件表达式 

Notice:掌握条件表达式中各种运算符的应用.

• 算术比较运算符=，>,<,<>或!=,<=,>=；
• 逻辑运算符AND、OR、NOT；
• 集合成员资格运算符：IN，NOT IN；like， not like；between and ，any（存在一个），all（所有）；
• 谓词:EXISTS(存在量词)，NOT EXISTS；
• 聚合函数

聚合函数

如果语句没有Group By则返回单个值；如果语句带Group By则每个小组返回一个值。

函数名	函数含义
COUNT（*)	计算元组的个数
COUNT（列名)	求一列中值的计算个数
COUNT(DISTINCT 列名)	求一列中值的种类数
SUM（列名)	求一列中值的总和
AVG（列名)	求一列中值的平均值
MAX（列名)	求一列中值的最大值
MIN（列名)	求一列中值的最小值

比较运算符的嵌套查询

select *
from emplyee 
where age > any(select age 
               from employee
               where Dno='02' and Dno <>'02')

• > any 只要大于其中一个就行
• >all 必须大于所有结果
• < any 只要小于其中一个就行
• < all 必须小于所有结果
• >=any 只要大于等于其中一个就行
• >=all 必须大于等于所有结果
• <=any 只要小于等于其中一个就行
• <=all 必须小于等于所有结果
• =any 只要等于一个就行
• <> any 只要不等于其中一个就行
• <>all 必须不等于所有结果

完整SELECT语句

SELECT 列名表(逗号隔开) 
FROM 基本表或视图序列 
[WHERE 条件表达式] (行条件子句) 
[GROUP BY 列名序列] (分组子句) 
    [HAVING 组条件表达式] (组条件子句) 
[ORDER BY列名[ASC|DESC]..] (排序子句) 

• SELECT子句中的规定

  • 如果要求输出表格中不允许出现重复元组，则在SELECT后加一DISTINCT
  • SELECT 子句中允许出现 +,-,*,/,以及列名、常数、函数的算术表达式

• 条件表达式的算术比较操作

  • WHERE 子句中可以用BETWEEN…AND…来限定一个值的范围

• 列和基本表的改名操作

  • 同一个基本表在SELECT语句中多次引用时可用AS来增加别名

• 字符串的匹配操作

  • WHERE子句中字符串匹配用LIKE和两个通配符，%和下划线_. %为任意长度的字符串，_表示单个字符

• 集合的并、交、差操作

  • 查询结果的结构完全一致时，可将两个查询进行并(UNION)、交（InterSect）、差（Except）

• 空值的比较操作

  • 查询空值操作不是用=‘null’,而是用 IS NULL来测试。

• 集合的比较操作

  • 集合成员资格比较用 IN/NOT IN
  • 集合成员算术比较用元组θ ANY/ALL (θ是算术比较运算符）（少用）

• 导出表的使用

  • 使用INTO关键字，可以给子查询结果起个表名，保存相关查询数据。 （select into）

连接查询

等值连接

select [distinct/all]<目标列表达式[别名]清单>
from <关系名1[别名1]> inner join <关系名2[别名2]>
on 连接条件表达式
或
select [distinct/all]<目标列表达式[别名]清单>
from <关系名清单>
on 连接条件表达式

左外连接（left join）：除了返回两表中满足条件的元组以外，还将左侧表中不匹配的元组，右侧表中以空值代替

右外连接（right join）：除了返回两表中满足条件的元组以外，还将右侧表中不匹配的元组，左侧表中以空值代替

全外连接（full join）：除了返回两表中满足条件的元组以外，还返回左侧表中不匹配元组，右侧表中以空值代替，以及右侧表中不匹配元组，左侧表中以空值代替

自身连接：有些特殊的查询需要对同一个关系进行多次连接查询

-- 自身连接
-- emgr表示该员工直接负责人，连接查询得到该员工的间接负责人，也就是找该负责人的负责人
select emp1.cno,emp2.emger
from employee emp1, employee emp2
where emp1.emgr = emp2.cno

数据更新

数据插入

(1) 元组值的插入

INSERT INTO 基本表名(列名表) 
values(元组值) 

(2) 查询结果的插入

INSERT INTO 基本表名(列名表) 
SELECT 查询语句 

数据删除

DELETE FROM 基本表名 [WHERE条件表达式] 
--（语义：从基本表中删除满足条件表达式的元组） 

视图

创建：

CREATE VIEW 视图名(列名表) AS SELECT 查询语句 [with check option];

若有[with check option]，则在今后进行insert，update，delete操作时，系统自动检查是否符合原定义视图子查询中的条件表达式

注意：如果在视图中碰到聚合函数，必须创建表名

撤消：

DROP VIEW 视图名 

视图更新

对试图的更新其实是DBMS自动将视图的更新，转换成了对基本表的更新。

如果在定义时有添加了[with check option]，对试图更新时系统发将自动检查原定义时的条件是否满足，若不满足，则拒绝执行。

视图的可更新性

• 如果视图是从多个基本表使用连接操作导出的，则不允许更新。
• 如果导出的视图使用了分组和聚合操作，也不允许更新。
• 如果视图是从单个基本表使用选择、投影操作导出的，并且包括了基本表的主键和没有缺省值的不可为空的列， 那么这样的视图称为“行列子集视图”，则可以执行操作。