数据库___标准语言SQL

SQL概述

SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言
SQL是一个通用的、功能极强的关系数据库语言
特点：

综合统一
集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
可以独立完成数据库生命周期中的全部活动：
定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库;
对数据库中的数据进行查询和更新;
数据库重构和维护
数据库安全性、完整性控制，以及事务控制
嵌入式SQL和动态SQL定义
用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行。
数据操作符统一
高度非过程化
非关系数据模型的数据操纵语言“面向过程”，必须指定存取路径。
SQL只要提出“做什么”，无须了解存取路径。
存取路径的选择以及SQL的操作过程由系统自动完成。
面向集合的操作方式
非关系数据模型采用面向记录的操作方式，操作对象是一条记录
SQL采用集合操作方式
操作对象、查找结果可以是元组的集合
一次插入、删除、更新操作的对象可以是元组的集合
以同一种语法结构提供多种使用方式
SQL是独立的语言
能够独立地用于联机交互的使用方式
SQL又是嵌入式语言
SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用
语言简洁，易学易用
SQL功能极强，完成核心功能只用了9个动词。

SQL的基本概念

SQL支持关系数据库三级模式结构
基本表
本身独立存在的表
SQL中一个关系就对应一个基本表
一个（或多个）基本表对应一个存储文件
一个表可以带若干索引
存储文件
逻辑结构组成了关系数据库的内模式
物理结构对用户是隐蔽的
视图
从一个或几个基本表导出的表
数据库中只存放视图的定义而不存放视图对应的数据
视图是一个虚表
用户可以在视图上再定义视图

数据定义

SQL的数据定义功能:

模式定义
表定义
视图和索引的定义

基本表的定义、删除与修改

定义基本表

CREATE TABLE <表名>
      (<列名> <数据类型>[ <列级完整性约束条件> ]
      [,<列名> <数据类型>[ <列级完整性约束条件>] ]
   …
      [,<表级完整性约束条件> ] );

<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

数据类型
SQL中域的概念用数据类型来实现
定义表的属性时需要指明其数据类型及长度

模式与表
每一个基本表都属于某一个模式 一个模式包含多个基本表

定义基本表所属模式

方法一：在表名中明显地给出模式名
Create table"S-T".Student(......); /模式名为 S-T/
Create table "S-T".Cource(......);
Create table "S-T".SC(......);
创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式。
关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名
搜索路径中的模式名都不存在，系统将给出错误
方法二：在创建模式语句中同时创建表
方法三：设置所属的模式

修改基本表

ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;

<表名>是要修改的基本表

ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN 子句用于删除表中的列
如果指定了CASCADE短语，则自动删除引用了该列的其他对象
如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT 子句用于删除指定的完整性约束条件
ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型

例向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;
不管基本表中原来是否已有数据，新增加的列一律为空值

例增加课程名称必须取唯一值的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表
DROP TABLE <表名>［RESTRICT| CASCADE］;

RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除
(比如表上建立的索引、视图、触发器等，一般删除）

索引的建立与删除

目的：加快查询速度

关系数据库管理系统中常见索引：

顺序文件上的索引
B+树索引（参见爱课程网3.2节动画《B+树的增删改》）
散列（hash）索引
位图索引

特点：
B+树索引具有动态平衡的优点。
HASH索引具有查找速度快的特点

谁可以建立索引
数据库管理员或表的属主（即建立表的人）
谁维护索引
关系数据库管理系统自动完成
使用索引
关系数据库管理系统自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引

语句格式
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>：要建索引的基本表的名字
索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE：此索引的每一个索引值只对应唯一的数据记录
如果不是唯一索引，数据是可以重复的；如果建立唯一索引可以保证数据记录的唯一性。
事实上，人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据。
CLUSTER：表示要建立的索引是聚簇索引

例：为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>

删除索引
DROP INDEX <索引名>;
删除索引时，系统会从数据字典中删去有关该索引的描述。

数据字典

数据字典是关系数据库管理系统内部的一组系统表

它记录了数据库中所有定义信息：

关系模式定义
视图定义
索引定义
完整性约束定义
各类用户对数据库的操作权限
统计信息等

关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。

数据查询

语句格式
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
    [AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];

SELECT子句：指定要显示的属性列
如果想查询所有列，可以使用*
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序

SELECT子句
<目标列表达式>不仅可以为表中的属性列，也可以是表达式

例查全体学生的姓名及其出生年份。
SELECT Sname,2014-Sage /假设当时为2014年/
FROM Student;
输出结果：
Sname 2014-Sage
李勇 1994
刘晨 1995
王敏 1996
张立 1995

FROM子句
如果没有指定DISTINCT关键词，则缺省为ALL
指定DISTINCT关键词，去掉表中重复的行

[例3.21] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
等价于：
SELECT ALL Sno FROM SC;
执行上面的SELECT语句后，结果为：
Sno
201215121
201215121
201215121
201215122
201215122
SELECT DISTINCT Sno FROM SC;
执行结果：
Sno
201215121
201215122

WHERE子句：
常用的查询条件

字符匹配

谓词： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

<匹配串>可以是一个完整的字符串，也可以含有通配符%和 _

% （百分号）代表任意长度（长度可以为0）的字符串
例如a%b表示以a开头，以b结尾的任意长度的字符串
_ （下横线）代表任意单个字符。
例如a_b表示以a开头，以b结尾的长度为3的任意字符串

匹配串为固定字符串,等价于使用“=”
匹配串为含通配符的字符串,使用like才有意义

例：查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';

使用换码字符将通配符转义为普通字符

例查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname LIKE 'DB_Design' ESCAPE '\ ' ;

例查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB_%i_ _' ESCAPE '\ ' ;
ESCAPE '＼' 表示“ ＼” 为换码字符

使用换码字符将通配符转义为普通字符

[例3.34] 查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname LIKE 'DB_Design' ESCAPE '\ ' ;
[例3.35] 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB_%i_ _' ESCAPE '\ ' ;

`ESCAPE '＼' 表示“ ＼” 为换码字符`

涉及空值的查询

谓词： IS NULL 或 IS NOT NULL
“IS” 不能用 “=” 代替

[例3.36] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno，Cno
FROM SC
WHERE Grade IS NULL

[例3.37] 查所有有成绩的学生学号和课程号。
SELECT Sno，Cno
FROM SC
WHERE Grade IS NOT NULL;!

多重条件查询

逻辑运算符：AND和 OR来连接多个查询条件
AND的优先级高于OR
可以用括号改变优先级

ORDER BY子句

可以按一个或多个属性列排序
升序：ASC;降序：DESC;缺省值为升序
对于空值，排序时显示的次序由具体系统实现来决定

例查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;

聚集函数

聚集函数只能用于select子句和having子句

如果指定distinct表示去重，指定all是不去重，默认值是all。

除了count(*)外，其余的所有函数都会跳过空值

统计元组个数
COUNT(*)
统计一列中值的个数
COUNT([DISTINCT|ALL] <列名>)
计算一列值的总和（此列必须为数值型）
SUM([DISTINCT|ALL] <列名>)
计算一列值的平均值（此列必须为数值型）
AVG([DISTINCT|ALL] <列名>)
求一列中的最大值和最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)

GROUP BY子句：

按指定的一列或多列值分组，值相等的为一组

HAVING短语与WHERE子句的区别： 作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组。

连接查询

分类：
1.等值与非等值连接查询
2.自身连接
3.外连接
4.多表连接

等值、自然连接与非等值连接查询
等值连接：连接运算符为=

[例 3.49] 查询每个学生及其选修课程的情况
SELECT Student., SC.
FROM Student, SC
WHERE Student.Sno = SC.Sno;

自然连接

[例 3.50] 对[例 3.49]用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;

自身连接
一个表与其自己进行连接

需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀

[例 3.52]查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

sql 关键字union 与 intersect 属于以行方向为单位的集合运算，进行这些集合运算时，会导致记录行数的增加或减少。
关键字 join 则是属于列方向的运算，将其他表中的列添加过来。
我们称这种操作为连接。

INNER JOIN / 内连接

内联结只会提取同时存在于两张表中的数据。
和用select查询多表是一样的效果，实际较少用到
使用

select
from
inner join t2
on join_condition1
INNER JOIN t3 ON join_condition2
...
where

from：主表名字
inner join：连接的表名字
on：连接条件或连接谓词

OUTER JOIN / 外连接

只要数据存在于某一张表中，就能够提取出来。

分类：

LEFT JOIN / 左外连接
关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
RIGHT JOIN / 右外连接
关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

使用

select
from
left / right join t2
on join_condition1
left / right JOIN t3 ON join_condition2
...
where

from：主表名字
left / right join：连接的表名字
on：连接条件或连接谓词

左外连接: 列出左边关系中所有的元组
右外连接: 列出右边关系中所有的元组

[例 3. 53] 改写[例 3.49]

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM  Student  LEFT OUT JOIN SC ON
                 (Student.Sno=SC.Sno);

CROSS JOIN / 交叉连接

嵌套查询

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SELECT Sname	                           /*外层查询/父查询*/

 FROM Student
 WHERE Sno IN
                    ( SELECT Sno        /*内层查询/子查询*/
                      FROM SC
                      WHERE Cno= ' 2 ');

子查询的限制:不能使用ORDER BY子句

常用的谓语

in
使用ANY或ALL谓词时必须同时使用比较运算

ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值

"<>" "!=" 不等于
exists
存在量词：∃
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
（not exists）
由EXISTS引出的子查询，其目标列表达式通常都用 * 因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
实现全称量词（∀x）P ≡ - （∃ x（- P））

查询选修了全部课程的学生姓名。

 SELECT Sname
 FROM Student
 WHERE NOT EXISTS
       (SELECT *
        FROM Course
        WHERE NOT EXISTS
            (SELECT *
             FROM SC
             WHERE Sno= Student.Sno
                AND Cno= Course.Cno
             )
 );

不相关子查询： 子查询的查询条件不依赖于父查询
由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

查询与“刘晨”在同一个系学习的学生。
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');

相关子查询： 子查询的查询条件依赖于父查询
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
然后再取外层表的下一个元组
重复这一过程，直至外层表全部检查完为止

找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG（Grade）
FROM SC y
WHERE y.Sno=x.Sno);

集合查询

集合操作的种类

并操作UNION
UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组
交操作INTERSECT
差操作EXCEPT

参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。

找出每个学生超过他自己选修课程平均成绩的课程号

    SELECT Sno, Cno
    FROM SC, (SELECTSno, Avg(Grade)
               FROM SC
                GROUP BY Sno)
        AS   Avg_sc(avg_sno,avg_grade)
    WHERE SC.Sno = Avg_sc.avg_sno
          and SC.Grade >=Avg_sc.avg_grade

数据更新

插入数据

可以一次插入多个元组。

两种插入数据方式

插入元组
插入子查询结果

语句格式

	INSERT
	INTO <表名> [(<属性列1>[,<属性列2 >…)]
	VALUES (<常量1> [,<常量2>]… );

或者

INSERT
INTO <表名>  [(<属性列1> [,<属性列2>…  )]
子查询;

关系数据库管理系统在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

实体完整性
参照完整性
用户定义的完整性
NOT NULL约束
UNIQUE约束
值域约束等等

修改数据

语句格式

UPDATE  <表名>
SET  <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];

如果省略WHERE子句，表示要修改表中的所有元组

三种修改方式

修改某一个元组的值
修改多个元组的值
带子查询的修改语句

[例3.73] 将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno=' 201215121 ';

 [例3.74]  将所有学生的年龄增加1岁。

    	 	UPDATE Student
     		SET Sage= Sage+1;

[例3.75] 将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS' );

关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

删除数据

语句格式

DELETE
FROM     <表名>
[WHERE <条件>];

三种删除方式
删除某一个元组的值
删除多个元组的值
带子查询的删除语句

空值

属性定义（或者域定义）中

有NOT NULL约束条件的不能取空值。
加了UNIQUE限制的属性不能取空值。
码属性不能取空值。

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。

空值的算术运算、比较运算和逻辑运算

空值与另一个值（包括另一个空值）的算术运算的结果为空值
空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。
有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑

视图

视图的特点

虚表，是从一个或几个基本表（或视图）导出的表
只存放视图的定义，不存放视图对应的数据
基表中的数据发生变化，从视图中查询出的数据也随之改变

定义查询更新

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

在对视图查询时，按视图的定义从基本表中将数据查出。

建立视图
语句格式

CREATE  VIEW  <视图名>  [(<列名>  [,<列名>]…)]
AS
<子查询>
[WITH  CHECK  OPTION];

WITH CHECK OPTION
对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;

删除视图

   DROP  VIEW  <视图名>[CASCADE];

如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除

查询视图
用户角度：与查询基本表相同

更新视图
用户角度：与查询基本表相同

[例3.95] 将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ';
转换后的语句：
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

允许对行列子集视图进行更新
对其他类型视图的更新不同系统有不同限制

视图的作用

简化用户的操作
使用户能以多种角度看待同一数据
对重构数据库提供了一定程度的逻辑独立性
对机密数据提供安全保护
适当的利用视图可以更清晰的表达查询

posted @ 2022-06-18 22:04 kingwzun 阅读(526) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

kingwzun

数据库___标准语言SQL

SQL概述

数据定义

基本表的定义、删除与修改

索引的建立与删除

数据字典

数据查询

字符匹配

涉及空值的查询

多重条件查询

ORDER BY子句

聚集函数

GROUP BY子句：

连接查询

INNER JOIN / 内连接

OUTER JOIN / 外连接

CROSS JOIN / 交叉连接

嵌套查询

集合查询

基于派生表的查询

数据更新

插入数据

修改数据

删除数据

空值

视图

定义查询更新

视图的作用

公告