SQL语句知识总结

）

一、 模式的定义

在 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      

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

题目：

1. 查询在伦敦的所有项目的详细信息

2. 查询项目J1的供应商的编号，查询结果按供应商编号排列

3. 查询货物供应量在300到750（包括300和750）之间的所有货物供应

4. 找出零件的所有颜色和零件的储藏地并且消除取值重复的行

5. 查询零件名称中包含字母T的所有零件的信息

6. 查询所有红色和蓝色的零件并且使用关键字IN

7. 查询供应商为S1的货物供应量的总数

8. 查询所有货物供应中的零件名称和颜色

9. 查询供应商从伦敦供应的所有零件的名称

10. 查询项目的项目号，在这个项目中至少有一个供应商且不在同一个城市。

11. 查询供应商不在巴黎，但是供应给巴黎的项目的绿色螺钉的总数

12. 查询零件的储藏位置与项目所在地相同时，货物供应的总数和货物供应的平均值

13. 查询每个城市中有多少个供应商

14. 查询属于一种颜色的零件有多少种

15. 查询所有零件的零件编号，这些零件被供应给至少一个项目，且供应的零件数量的平均值要大于320

答案：

1、Select *

From  Project

Where City=‘London’

2、Select SID

From Shipment

Where PrjNo=‘J1’

Order By SID

3、Select  *

From Shipment

Where Qty Between 300 And 750

4、Select  Distinct Colour,City

From Parts

5、Select *

From Parts

Where Pname LIKE  ‘%T%’

6、Select  *

From Parts 

Where Colour IN (‘Red’,’Blue’)

7、Select  Sum(Qty)

From Shipment

Where SID=‘S1’

8、Select  Shipment.PID,Colour

From Shipment,Parts

Where Shipment.PID=Parts.PID

9、Select  PName

From Parts,Shipment,Suppliers

Where Parts.PID=Shipment.PID

And Shipment.SID=Suppliers.SID

And Suppliers.City=‘London’

10、Select PrjNo

From Project

Where Exists

(Select *    

 From Shipment,Suppliers

Where Project.PrjNo=Shipment.PrjNo

And Shipment.SID=Suppliers.SID

And Project.City!=Suppliers.City 

11、Select Sum(Qty)

From Shipment,Parts,Projects

Where Shipment.PID=Parts.PID    And Shipment.PRjNo=Project.PrjNo

And Project.City=‘Paris’   And colour=‘Green’

And SID Not IN

(Select SID   From Suppliers    Where City=‘Paris’)

12、Select Sum(Qty),Avg(Qty)

From Shipment,Parts,Project

Where Shipment.PID=Parts.PID

And Shipments.PrjNo=Project.PrjNo

And Parts.City=Project.City22

13、Select COUNT(SID)

From Suppliers

Group By City

14、Select Count(PID)

From Parts

Group By Colour

15、Select  PID

From Shipment

Group By PID

Having Avg(Qty)>320