SQL语言合集

最新推荐文章于 2024-10-17 19:06:39 发布

小蜗牛( ˙-˙ )

最新推荐文章于 2024-10-17 19:06:39 发布

阅读量96

点赞数

分类专栏：数据库系统概述文章标签： sql 数据库

本文链接：https://blog.csdn.net/Miss_leading/article/details/129317245

版权

数据库系统概述专栏收录该内容

3 篇文章 0 订阅

订阅专栏

模式的定义与删除

定义模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>  
[<表定义子句>|<视图定义子句>|<授权定义子句>];

删除模式

DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

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

定义基本表

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

修改基本表

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

删除基本表

DROP TABLE <表名> [RESTRICT|CASCADE];

索引的建立与删除

索引类型

顺序文件上的索引
B+树索引
散列索引
位图索引

建立索引

CREATE [UNIQUE][CLUSTER] INDEX<索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);
// CLUSTER 聚簇索引

修改索引

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

删除索引

DROP INDEX <索引名>;

数据查询☆☆☆

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

值得注意的点：

select子句的目标列表达式不仅可以是属性列，而且可以是表达式。例如2014-Sage 'Year of Birth'。
列可以指定别名，<列名> <别名>
DISTINCT去掉查询结果中重复的行
WHERE子句中不能使用聚集函数作为条件表达式，聚集函数只能在SELECT子句和GROUP BY的HAVING子句中出现

查询满足条件的子句（WHERE）

比较大小
确定范围：BETWEEN AND, NOT BETWEEN AND
确定集合：IN, NOT IN
字符匹配：LIKE, NOT LIKE（在使用LIKE进行字符匹配时，如果LIKE后面不含通配符（%, _），则可以用=取代LIKE，用!=取代NOT LIKE）
空值：IS NULL, IS NOT NULL
多重条件： AND, OR, NOT

!!!注：在WHERE子句中不能使用聚集函数作为比较表达式，聚集函数应当放在HAVING后，与GROUP BY配合使用

ORDER BY子句

将查询结果进行升序（ASC）或降序（DESC）的排序
eg:ORDER BY Sno就是按照学号大小进行升序排序，不指定ASC或DESC时默认为ASC。

聚集函数

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

GROUP BY子句

GROUP BY将查询结果按某一列或多列的值分组，值相等的为一组。分组后聚集函数将作用于每一组，即每一组都有一个函数值。

连接查询

等值与非等值连接查询，使用比较运算符或BETWEEN AND进行连接查询
自身连接，起别名
外连接，将一个或者两个表的悬浮元组保存在运算结果中
多表连接

// 第一种
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
// 第二种
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

嵌套查询

查询块：一个SELECT-FROM-WHERE语句称为一个查询块。
eg：查询选修2号课程的学生姓名，这是一个不相关子查询，该查询可以使用连接查询来替代。

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

不相关子查询：子查询的条件不依赖于父查询
相关子查询：子查询的条件依赖于父查询
相关子查询实例：找出每个学生超过他自己选修课程平均成绩的学号和课程号

SELECT Sno, Cno
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
                FROM SC y
                WHERE y.Sno = x.Sno);

带有IN谓词的子查询

eg：查询与’刘晨’在一个系学习的所有学生信息

SELECT *
FROM Student
WHERE Sdept IN
     (SELECT Sdept
      FROM Student
      WHERE Sname = '刘晨');

带有比较运算符的子查询

示例见相关子查询示例。

带有ANY(SOME)或ALL谓词的子查询

ANY和ALL谓词与比较运算符相结合，实现与子查询所有结果相比较。
>ANY代表大于子查询结果中的某一个值
>ALL代表大于子查询结果中的所有值
>、<可以替换为>=、<=、!=、=运算符

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
eg：查询所有选修了1号课程的学生姓名

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

集合查询

包括并操作UNION、交操作INTERSECT和差操作EXCEPT。
eg：查询计算机科学系的学生及年龄不大于19岁的学生

SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;

同理，INTERSECT和EXCEPT用起来也是相同的格式。
UNION可以用连接查询中的OR来替代，INTERSECT可以用连接查询中的AND来替代。

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
eg：找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno, Cno
FROM SC, (SELECT Sno, 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）

插入元组
值的位置和数据类型要同属性的位置和数据类型一一对应，但属性的位置可以同表中的位置不同。

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

插入子查询结果

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

eg：对Student表按系分组求平均年龄，再把系名和平均年龄存入Dept_age表中

INSERT
INTO Dept_age(Sdept, Avg_age)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据（UPDATE）

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

删除数据（DELETE）

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

若没有WHERE子句，则会删除表中的所有元组，使表称为空表。

空值的处理

空值的约束条件：

属性定义中有NOT NULL的不可取空值
码不可取空值

空值的算数运算、比较运算和逻辑运算：

空值与另一个值（包括空值）的算数运算为空值；
空值与另一个值（包括空值）的比较运算结果为UNKNOWN。有了UNKNOWN后，传统的逻辑运算中的二值（TRUE, FALSE）逻辑就扩展成了三值逻辑。
逻辑运算中，UNKNOWN介于TRUE和FALSE之间。例如：T AND U = U, U AND U = U, U AND F = F, T OR U = T, U OR U = U, U OR F = U, NOT U = U。

视图

视图是从一个或几个基本表中导出的表。它与基本表不同，是一个虚表。数据库中只存放视图对应的定义，而不执行其中的SELECT语句、不存放视图对应的数据，这些数据仍存放在对应的基本表中。视图在三级模式中对应外模式。

定义视图

建立视图

CREATE VIEW <表名> [(<列名> [,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION]

其中，子查询可以是任意的SELECT语句。WITH CHECK OPTION表示对视图进行UPDATE, INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。而且，视图不仅可以定义在一个或多个基本表上，也可以定义在一个或多个已定义的视图上，或建立在基本表与视图上。
组成视图的属性列名在下列情况时需全部指定：

某目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

行列子集视图：从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，并且保留了主码的视图。
分组视图：带有聚集函数和GROUP BY子句的查询来定义视图。

建立视图

DROP VIEW <视图名> [CASCADE];

CASCADE级联删除语句将把该视图以及由他导出的所有视图一起删除。

查询视图

在执行对视图的查询时，系统会首先进行有效性检查，即检查对应的表和视图是否存在。然后进行视图消解（view resolution），即从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。但是在有些查询中，转换会出现问题。eg：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

// 用户的查询
SELECT *
FROM S_G
WHERE Gavg >= 90;
// 视图定义中的子查询
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
// 将上述两者结合，形成下列查询语句
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90
GROUP BY Sno;

则在上述示例中发生了转换错误，在WHERE子句中不能使用聚集函数作为条件表达式。
而正确转换的查询语句应该是：

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;

解决这个问题可以使用基于派生表的查询

SELECT Sno, AVG(Grade)
FROM (SELECT Sno, AVG(Grade)
      FROM SC
      GROUP BY Sno) AS S_G(Sno, Gavg)
WHERE Gavg >= 90;

在这个查询过程中，使用FROM中的子查询生成了一个派生表S_G。而该派生表在查询语句完成后就会被删除，是一张临时表。

更新视图

通过使用插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。而所有对视图的更新最终会回到基本表上。
但是，并不是所有的视图都是可更新的，有些视图的更新不能够唯一的有意义的转换成对相应基本表的更新。一般的，行列子集视图是可更新的。

视图的作用

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