查询删除SQL语句知识总结-白红宇

查询删除SQL语句知识总结

阅读量：5114 次

发布时间：2019-06-13

本文共 11673 字，大约阅读时间需要 38 分钟。

首先声明，我是一个菜鸟。一下文章中出现技术误导情况盖不负责

）

一、模式的定义

在 SQL语句中模式的定义如下：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

如果没有指定<模式名>，那么<模式名>隐含为<模式名>。

二、模式的删除

在SQL语句中删除模式如下：

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

其中CASCADE和RESTRICT两者必选其一；

选择了CASCADE（级联），表现在删除模式的同时在把该模式中的全体的数据库对象全体一同删除；

选择了RESTRICT（制约）,表现如果该模式中已定义了下属的数据库对象（如表，视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

三、基本表的定义

格式：CREATE TABLE <表名> ( <列名><数据类型>[列级完整性约束条件]

[，<列名><数据类型>[列级完整性约束条件]]

····

[,<表级完整性约束条件>]）；

例如：建立课程表Course

CREATE TABLE Course

（Cno CHAR(4) PRIMARY KEY，/*(主码)*/

Cname CHAR(40),

Cpno CHAR(4),

Ccredit SMALLINT,

FOREIGN KEY Cpno REFERENCES Course(Cno) /*最后一句没有逗号*/

/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/

）；（分号能丢）

当主码由两个属性构成，必须作为表级完整性停止定义：

例如：CREATE TABLE SC

(

····

PRIMARY KEY (Sno，Cno)，

····

);

附录：常用数据类型：

数据类型	含义
CHAR(n)	长度为n的定长字符串
VARVHAR(n)	最大长度为n的变长字符串
INT	长整数（也可认为INTEGER）
SMALLINT	短整型
NUMERIC(p,d)	定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d为数字
REAL	取决于呆板精度的浮点数
Double Precision	取决于呆板精度的双精度浮点数
FLOAT(n)	浮点数，精度至少为n为数字
DATE	日期，包括年、月、日，格式为YYYY-MM-DD
TIME	时间，包括一日的时、分、秒，格式为HH:MM:SS

四、修改基本表

一般格式： ALTER TABLE <表名>

[ADD <新列名> <数据类型> [完整性约束]]

[DROP <完整性约束名>]

[ALTER COLUMN <列名> <数据类型>]；

其中<表名>是要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

五．删除基本表

DROP TABLE <表名>［RESTRICT| CASCADE］；

n RESTRICT：删除表是有制约的。

Ø 欲删除的基本表不能被其他表的约束所引用

Ø 如果存在依附该表的对象，则此表不能被删除

n CASCADE：删除该表没有制约。

Ø 在删除基本表的同时，相关的依附对象一同删除

例如： DROP TABLE Student CASCADE ;

n 基本表定义被删除，数据被删除

n 表上建立的索引、视图、触发器等一般也将被删除

n 若表上建有视图，选择RESTRICT时表不能删除

n CREATE VIEW IS_Student

n AS

n SELECT Sno，Sname，Sage

n FROM Student

n WHERE Sdept='IS'；

n DROP TABLE Student RESTRICT;

n --ERROR: cannot drop table Student because other

n objects depend on it

n 如果选择CASCADE时可以删除表，视图也自动被删除

n DROP TABLE Student CASCADE;

n --NOTICE: drop cascades to view IS_Student

n SELECT * FROM IS_Student;

n --ERROR: relation " IS_Student " does not exist

六、索引的建立与删除

1、索引的建立

语句格式：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

§ <表名>

§ <列名>

§ <次序>指定索引值的排列次序，可选ASC(升)或DESC(降)，缺省为ASC

§ UNIQUE标明此索引每个索引值只对应独一的数据记录

CLUSTER表现聚簇索引。即索引项的次序与表中记录的物理次序一致。如电话号码簿。

例如：

CREATE CLUSTER INDEX Stusname

ON Student(Sname)；

§ 在Student表的Sname（姓名）列上建立一个聚簇索引

v 在最经常查询的列上建立聚簇索引以提高查询效率

v 由于聚簇索引决议数据在表中的物理存储次序，因此一个基本表上最多只能建立一个聚簇索引

v 经常更新的列不宜建立聚簇索引

v CREATE UNIQUE INDEX Stusno ON Student(Sno)；

v CREATE UNIQUE INDEX Coucno ON Course(Cno)；

v CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；

v Student表按学号升序建独一索引

v Course表按课程号升序建独一索引

v SC表按学号升序和课程号降序建独一索引

2、索引的删除

v DROP INDEX <索引名>；

删除索引时，系统会从数据字典中删去有关该索引的

描述。

七、数据查询

数据查询的语句格式:

SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>] …

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ]；

1、单表查询

1.1查询表中多少列

A、查询指定列

例如：查询全体学生的学号与姓名。

SELECT Sno，Sname

FROM Student；

B、查询全体列

n 在SELECT关键字后面列出全体列名

n 将<目标列表达式>指定为 *

例如：查询全体学生的具体记录。

SELECT Sno，Sname，Ssex，Sage，Sdept

FROM Student；

或

SELECT *

FROM Student；

C、查询经过计算的列

v SELECT子句的<目标列表达式>可认为：

§ 算术表达式

§ 字符串常量

§ 函数

§ 列别名

例如：

查全体学生的姓名及其出身年份。

SELECT Sname，2004-Sage /*假设当年的年份为2004年*/

FROM Student；

v 使用列别名转变查询结果的列标题(列名+空格+别名）

SELECT Sname NAME，'Year of Birth: ’ BIRTH，

2000-Sage BIRTHDAY，LOWER(Sdept) DEPARTMENT

FROM Student；

1．2选择表中的多少元组

A、清除取值重复的行

如果没有指定DISTINCT关键词，则缺省为ALL

B、查询满足条件的元组

查询条件	谓词
比较	=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符
确定范围	BETWEEN AND，NOT BETWEEN AND
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件（逻辑运算）	AND，OR，NOT

例如：

查询计算机科学系全体学生的名单。

SELECT Sname

FROM Student

WHERE Sdept=‘CS’；

查询全体春秋在20岁以下的学生姓名及其春秋。

SELECT Sname，Sage

FROM Student

WHERE Sage < 20；

查询春秋在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和春秋

SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23；

查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname，Ssex

FROM Student

WHERE Sdept IN ( 'IS'，'MA'，'CS' );

C、字符匹配：

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

1）、匹配串为牢固字符串

查询学号为200215121的学生的具体情况。

SELECT *

FROM Student

WHERE Sno LIKE ‘200215121'；

等价于：

SELECT *

FROM Student

WHERE Sno = ' 200215121 '；

2）、匹配串为含通配符的字符串% _

[查询全体姓刘学生的姓名、学号和性别。

SELECT Sname，Sno，Ssex

FROM Student

WHERE Sname LIKE ‘刘%’；

%（百分号）代表任意长度（长度可认为0）的字符串。例如a%b表现以a扫尾，以b结束的任意长度的字符串。

_（下横线）代表任意单个字符。例如a_b表现以a扫尾，以b开头的长度为3的任意字符串。

如果LINK后面的匹配串中不含有通配符，则可以用=（等于）运算符取代LINK谓词，用！=或<>（不等于）运算符取代NOT LINK谓词

3）、使用换码字符将通配符转义为一般字符

例如：查询以"DB_"扫尾，且倒数第3个字符为 i的课程的具体情况。

SELECT *

FROM Course

WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ ‘；

/* ESCAPE '＼' 表现“ ＼” 为换码字符 */

D、触及空值查询

n ： IS NULL 或 IS NOT NULL

n “IS” 不能用 “=” 代替

例如：某些学生选修课程后没有加入考试，所以有选课记录，但没

有考试成就。查询缺乏成就的学生的学号和相应的课程号。

SELECT Sno，Cno

FROM SC

WHERE Grade IS NULL

E、多重条件查询

逻辑运算符：AND和 OR来联结多个查询条件

• AND的优先级高于OR

• 可以用括号转变优先级

可用来实现多种其他谓词

[NOT] IN

[NOT] BETWEEN … AND …

1.3ORDER BY子句

n 可以按一个或多个属性列排序

n 升序：ASC；降序：DESC；缺省值为升序

当排序列含空值时:

n ASC：排序列为空值的元组最后显示

n DESC：排序列为空值的元组最早显示

例：查询全体学生情况，查询结果按地点系的系号升序排列，同一系中的学生按春秋降序排列。

SELECT *

FROM Student

ORDER BY Sdept，Sage DESC；

1.4聚集函数

计数：

COUNT（[DISTINCT|ALL] *）统计元组个数

COUNT（[DISTINCT|ALL] <列名>）统计一列中值的个数

计算总和：

SUM（[DISTINCT|ALL] <列名>）计算一列中值的总和

计算平均值：

AVG（[DISTINCT|ALL] <列名>）计算一列值的平均值

最大最小值：

MAX（[DISTINCT|ALL] <列名>）求一列值中的最大值

MIN（[DISTINCT|ALL] <列名>）求一列值中的最小值

注意：WHERE子句中不能用聚集函数作为条件表达式

1.5 GROUP BY子句

语句格式：

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ]；

GROUP BY子句将查询结果按某一列或多列的值分组，值相称的为一组。

例：查询选修了3门以上课程的学生学号。

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*) >3；

先用GROUP BY字句按Sno停止分组

再用聚集函数COUNT对每一组计数

HAVING短语给出了选择组的条件，只有满足条件

（元组个数>3），表现此学生选修超越3门，才会被选出来。

HAVING短语与WHERE子句的区别：

§ 作用对象不同

§ WHERE子句作用于基表或视图，从中选择满足条件的元组

§ HAVING短语作用于组，从中选择满足条件的组。

2、连接查询

v 连接查询：同时触及多个表的查询

v 连接条件或连接谓词：WHERE子句中用来连接两个表的条件

一般格式：

n [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

n [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

v 连接字段：连接谓词中的列名称

n 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

3、嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块

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

§ SQL语言允许多层嵌套查询

§ 子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

§ 层层嵌套方法反应了 SQL语言的结构化

§ 有些嵌套查询可以用连接运算替代

罕见的子查询有以下几种：

1、带IN谓词的子查询

2、带有比较运算符的子查询

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

4、带有EXISTS谓词的子查询

5、需要配合使用比较运算符：

6、 > ANY 大于子查询结果中的某个值

7、 > ALL 大于子查询结果中的全体值

8、 < ANY 小于子查询结果中的某个值

9、 < ALL 小于子查询结果中的全体值

10、 >= ANY 大于等于子查询结果中的某个值

11、 >= ALL 大于等于子查询结果中的全体值

12、 <= ANY 小于等于子查询结果中的某个值

13、 <= ALL 小于等于子查询结果中的全体值

14、 = ANY 等于子查询结果中的某个值

15、 =ALL 等于子查询结果中的全体值（平日没有实际意义）

16、 !=（或<>）ANY 不等于子查询结果中的某个值

17、 !=（或<>）ALL 不等于子查询结果中的任何一个值

例：查询至少选修了学生200215122选修的全体课程的学生号码。

解题思绪：

n 用逻辑蕴函表达：查询学号为x的学生，对全体的课程y，只要200215122学生选修了课程y，则x也选修了y。

n 形式化表现：

用P表现谓词 “学生200215122选修了课程y”

用q表现谓词 “学生x选修了课程y”

则上述查询为: ("y) p ® q

n 等价变换：

("y)p ® q ≡ Ø ($y (Ø(p ® q ))

≡ Ø ($y (Ø(Ø p∨ q) ))

≡ Ø $y(p∧Øq)

n 变换后语义：不存在这样的课程y，学生200215122选修了y，而学生x没有选。

n 不存在这样的课程y，学生200215122选修了y，而学生x没有选。用NOT EXISTS谓词表现：

n SELECT DISTINCT Sno

n FROM SC SCX

n WHERE NOT EXISTS

n (SELECT *

n FROM SC SCY

n WHERE SCY.Sno = ' 200215122 ' AND

n NOT EXISTS

n (SELECT *

n FROM SC SCZ

n WHERE SCZ.Sno=SCX.Sno AND

n SCZ.Cno=SCY.Cno))；

4、集合查询

集合查询指多个SELECT语句的结果停止集合操纵

集合操纵的种类：

a) 并操纵UNION

b) 交操纵INTERSECT

c) 差操纵EXCEPT

加入集合操纵的各查询结果的列数必须相同；对应项的数据类型也必须相同

八、数据更新

1、插入数据

两种插入数据方法

1. 插入元组

2. 插入子查询结果

可以一次插入多个元组

v 语句格式

INSERT

INTO <表名> [(<属性列1>[，<属性列2 >…)]

VALUES (<常量1> [，<常量2>] … )

v INTO子句

n 属性列的次序可与表定义中的次序不一致

n 没有指定属性列

n 指定部份属性列

v VALUES子句

n 供给的值必须与INTO子句匹配

Ø 值的个数

Ø 值的类型

2、插入子查询结果

v 语句格式

INSERT

INTO <表名> [(<属性列1> [，<属性列2>… )]

子查询；

v 功能

将子查询结果插入指定表中

v 子查询

n SELECT子句目标列必须与INTO子句匹配

Ø 值的个数

Ø 值的类型

RDBMS在执行插入语句时会检查所插元组是

否破坏表上已定义的完整性规矩

§ 实体完整性

§ 参照完整性

§ 用户定义的完整性

Ø NOT NULL约束

Ø UNIQUE约束

Ø 值域约束

3、修改数据

v 语句格式

UPDATE <表名>

SET <列名>=<表达式>[，<列名>=<表达式>]…

[WHERE <条件>]；

v 功能

n 修改指定表中满足WHERE子句条件的元组

n SET子句

v 指定修改方法

v 要修改的列

v 修改后取值

n WHERE子句

v 指定要修改的元组

v 缺省表现要修改表中的全体元组

v 三种修改方法

1. 修改某一个元组的值

2. 修改多个元组的值

3. 带子查询的修改语句

例：将全体学生的春秋增加1岁

UPDATE Student

SET Sage= Sage+1；

每日一道理

信念是巍巍大厦的栋梁，没有它，就只是一堆散乱的砖瓦；信念是滔滔大江的河床，没有它，就只有一片泛滥的波浪；信念是熊熊烈火的引星，没有它，就只有一把冰冷的柴把；信念是远洋巨轮的主机，没有它，就只剩下瘫痪的巨架。

例：将计算机科学系全体学生的成就置零。

UPDATE SC

SET Grade=0

WHERE 'CS'=

(SELETE Sdept

FROM Student

WHERE Student.Sno = SC.Sno)；

4、删除数据

v 语句格式

DELETE

FROM <表名>

[WHERE <条件>]；

v 功能

n 删除指定表中满足WHERE子句条件的元组

v WHERE子句

n 指定要删除的元组

n 缺省表现要删除表中的全体元组，表的定义仍在字典中

v 三种删除方法

1. 删除某一个元组的值

2. 删除多个元组的值

3. 带子查询的删除语句

九、视图的定义

1、建立视图

v 语句格式

CREATE VIEW

<视图名> [(<列名> [，<列名>]…)]

AS <子查询>

[WITH CHECK OPTION]；

v WITH CHECK OPTON表现对视图停止UPDATE、INSERT、DELETE操纵时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

v 组成视图的属性列名：全体省略或全体指定

若省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。

但是，下列3种情况必须明确指定组成该视图的全体列名

§ 某个目标列不是单纯的属性名、而是聚集函数或列表达式

§ 多表连接时选出几个同名列作为试图的字段

§ 需要在视图中为某个列启用新的更合适的名字

v 子查询不允许含有ORDER BY子句和DISTINCT短语

2、删除视图

v 语句的格式：

DROP VIEW <视图名>；

§ 该语句从数据字典中删除指定的视图定义

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

§ 删除基表时，由该基表导出的全体视图定义都必须显式地使用DROP VIEW语句删除

3、查询视图

例：查询选修了1号课程的信息系学生

SELECT IS_Student.Sno，Sname

FROM IS_Student，SC

WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1'；

综合题：

v 供应商表： Suppliers(SID, SName, City)

v 零件表： Parts(PID , PName,Colour,Weight,City)

v 项目表： Project(PrjNo , PrjName,City,PrjDate)

v 货品供应表：

Shipment (SID,PID,PrjNo,Qty,ShDate)

Suppliers Table

SID	SName	City
S1	Smith	London
S2	Jones	Paris
S3	Blake	Paris
S4	Clark	London
S5	Adams	Athens
S6	Smith	Melbourne

Parts Table

PID	PName	Colour	Weight	City
P1	Nut	Red	12	London
P2	Bolt	Green	17	Paris
P3	Screw	Red	14	London
P4	Cam	Blue	12	Paris
P5	Cog	Red	19	London

Project table

PrjNo	PrjName	City	PrjDate
J1	Sorter	Paris	01-Aug-02
J2	Punch	Rome	04-Jan-00
J3	Reader	Athens	01-Apr-02
J4	Console	Athens	01-Dec-01
J5	Collator	London	05-Dec-01
J6	Terminal	Oslo	01-Feb-01
J7	Tape	Rome	01-Dec-02

Shipment Table

SID	PID	PrjNo	Qty	ShDate
S1	P1	J1	200	03-Apr-02
S1	P1	J4	700	03-Apr-01
S2	P3	J1	400	03-Feb-01
S2	P3	J2	200	03-Jan-02
S2	P3	J3	200	03-Sep-00
S3	P3	J4	500	03-Oct-01
S3	P4	J5	600	13-Apr-02
S4	P5	J6	400	23-Apr-99
S4	P5	J7	800	03-Oct-02
S5	P1	J2	100	13-Apr-01
S5	P2	J1	200	23-Apr-02
S5	P2	J2	500	23-Apr-02
S6	P5	J3	300	03-Oct-00
S6	P5	J7	300	13-Apr-00