关系数据库标准语言SQL

数据库要期末考试了，来一波SQL语句总结

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

1.定义基本表

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

• <表名>：所要定义的基本表的名字
• <列名>：组成该表的各个属性（列）
• <列级完整性约束条件>：涉及相应属性列的完整性约束条件
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
• 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

1.1 约束

约束	说明
NOT NULL	约束强制列不接受 NULL 值。
UNIQUE	约束不出现重复值，但允许是NULL值。
PRIMARY KEY	约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合，主键可以用来在表中唯一的确定一行数据，一个表上只允许建立一个主键
FOREIGN KEY	外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
CHECK	检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件
DEFAULT	约束用于向列中插入默认值。

1.2 数据类型

数据类型	含义
CHAR(n),CHARACTER(n)	长度为n的定长字符串
VARCHAR(n), CHARACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT，INTEGER	长整数（4字节）
SMALLINT	短整数（2字节）
BIGINT	大整数（8字节）
NUMERIC(p，d)	定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字
DECIMAL(p, d), DEC(p, d)	同NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为n位数字
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

2. 修改基本表

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

• <表名>是要修改的基本表
• ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件

• DROP COLUMN子句用于删除表中的列

  如果指定了CASCADE短语，则自动删除引用了该列的其他对象

  如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列

• DROP CONSTRAINT子句用于删除指定的完整性约束条件
• ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型

3. 删除基本表

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

• RESTRICT：删除表是有限制的。

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

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

• CASCADE：删除该表没有限制。

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

数据查询

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

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

1. 选择表中若干列

1.1 查询指定列

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

SELECT Sno,Sname FROM Student;

1.2 查询全部列

• 选出所有属性列：

  在SELECT关键字后面列出所有列名

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

查询全体学生的详细记录

SELECT  Sno,Sname,Ssex,Sage,Sdept 
FROM Student; 
或
SELECT  *
FROM Student;

1.3 查询经过计算的值

• SELECT子句的<目标列表达式>不仅可以为表中的属性列，也可以是表达式
  查全体学生的姓名及其出生年份

SELECT Sname,2017-Sage          /*假设当时为2017年，2017减去年龄即为出生年份*/
FROM Student;

2. 选择表中的若干元组

2.1 消除取值重复的行

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

2.2 查询条件

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

比较：

/*查询考试成绩有不及格的学生的学号*/
SELECT DISTINCT Sn
FROM  SC
WHERE Grade<60; 

/*查询计算机科学系全体学生的名单*/
SELECT Sname
FROM     Student
WHERE  Sdept=‘CS’; 

确定范围：

/*查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄*/
SELECT Sname, Sdept, Sage
FROM     Student
WHERE   Sage BETWEEN 20 AND 23; 


/*查询年龄不在20~23岁之间的学生姓名、系别和年龄*/
SELECT Sname, Sdept, Sage
FROM    Student
WHERE Sage NOT BETWEEN 20 AND 23; 

确定集合：

/*查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别*/
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA’,'CS' );

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

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

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

/*查询姓"欧阳"且全名为三个汉字的学生的姓名*/
SELECT Sname
FROM   Student
WHERE  Sname LIKE '欧阳__';


/*查询所有不姓刘学生的姓名、学号和性别*/
SELECT Sname, Sno, Ssex
FROM Student
WHERE  Sname NOT LIKE '刘%';

空值:

/*查询缺少成绩的学生的学号和相应的课程号*/
SELECT Sno，Cno
FROM    SC
WHERE  Grade IS NULL

/*查所有有成绩的学生学号和课程号*/
SELECT Sno，Cno
FROM     SC
WHERE  Grade IS NOT NULL;

多重条件查询：
- 逻辑运算符：AND和 OR来连接多个查询条件

AND的优先级高于OR

可以用括号改变优先级

3. ORDER BY子句

• ORDER BY子句

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

  升序：ASC;降序：DESC;缺省值为升序

• 对于空值，排序时显示的次序由具体系统实现来决定

/*查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列*/
SELECT Sno, Grade
FROM    SC
WHERE  Cno= ' 3 '
ORDER BY Grade DESC;

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

4. 聚集函数

• 统计元组个数

  COUNT(*)

• 统计一列中值的个数

  COUNT([DISTINCT|ALL] <列名>)

• 计算一列值的总和（此列必须为数值型）

  SUM([DISTINCT|ALL] <列名>)

• 计算一列值的平均值（此列必须为数值型）

  AVG([DISTINCT|ALL] <列名>)

• 求一列中的最大值和最小值

  MAX([DISTINCT|ALL] <列名>)

  MIN([DISTINCT|ALL] <列名>)

/*查询学生总人数*/
SELECT COUNT(*)
FROM  Student;

/*查询选修了课程的学生人数*/
SELECT COUNT(DISTINCT Sno)
FROM SC;

/*计算1号课程的学生平均成绩*/
SELECT AVG(Grade)
FROM    SC
WHERE Cno= ' 1 ';

/*查询选修1号课程的学生最高分数*/
   SELECT MAX(Grade)
   FROM SC
   WHERE Cno='1';

查询学生201215012选修课程的总学分数。
              SELECT SUM(Ccredit)
              FROM  SC,Course
              WHERE Sno='201215012' AND SC.Cno=Course.Cno; 

5.GROUP BY子句

GROUP BY子句分组：
细化聚集函数的作用对象
- 如果未对查询结果分组，聚集函数将作用于整个查询结果
- 对查询结果分组后，聚集函数将分别作用于每个组
- 按指定的一列或多列值分组，值相等的为一组

/*查询选修了3门以上课程的学生学号*/
SELECT Sno
FROM  SC
GROUP BY Sno
HAVING  COUNT(*) >3; 

/*查询平均成绩大于等于90分的学生学号和平均成绩
*/
SELECT  Sno, AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

6.连接查询

• 连接查询：同时涉及两个以上的表的查询

• 连接条件或连接谓词：用来连接两个表的条件

  一般格式：

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

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

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

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

等值连接:
连接运算符为=

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

自身连接:
- 自身连接：一个表与其自己进行连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性，因此必须使用别名前缀

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

外连接:
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
- 左外连接
列出左边关系中所有的元组
- 右外连接
列出右边关系中所有的元组

/*查询每个学生及其选修课程的情况*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  LEFT OUT JOIN SC ON    
      (Student.Sno=SC.Sno); 

多表连接:

/*查询每个学生的学号、姓名、选修的课程名及成绩*/
SELECT Student.Sno, Sname, Cname, Grade
FROM    Student, SC, Course    /*多表连接*/
WHERE Student.Sno = SC.Sno 
AND SC.Cno = Course.Cno;

7.嵌套查询

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

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

• 上层的查询块称为外层查询或父查询
• 下层查询块称为内层查询或子查询

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

  即一个子查询中还可以嵌套其他子查询

• 子查询的限制

  不能使用ORDER BY子句

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

/*查询选修了课程名为“信息系统”的学生学号和姓名*/
SELECT Sno,Sname  /*最后在Student关系中取出Sno和Sname*/
FROM    Student     
WHERE Sno  IN
    (SELECT Sno  /*然后在SC关系中找出选修了3号课程的学生学号*/
    FROM    SC                      
    WHERE  Cno IN
        (SELECT Cno  /*首先在Course关系中找出“信息系统”的课程号*/
    FROM Course 
    WHERE Cname= '信息系统'                      
    )
);

8. 集合查询

• 集合操作的种类
  
  1. 并操作UNION
  2. 交操作INTERSECT
  3. 差操作EXCEPT
• 参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

UNION：将多个查询结果合并起来时，系统自动去掉重复元组

UNION ALL：将多个查询结果合并起来时，保留重复元组

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

插入数据

• 两种插入数据方式

  1. 插入元组
  2. 插入子查询结果

  可以一次插入多个元组

语句格式：

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

* INTO子句*：
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
- 指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句
- 提供的值必须与INTO子句匹配
值的个数
值的类型

/*将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中*/
INSERT
INTO  Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈冬','男','IS',18);

/*对每一个系，求学生的平均年龄，并把结果存入数据库*/
/*第一步：建表*/
CREATE  TABLE  Dept_age
( Sdept     CHAR(15)                     /*系名*/
Avg_age SMALLINT);              /*学生平均年龄*/
/*第二步：插入数据*/
INSERT
INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept，AVG(Sage)
FROM     Student
GROUP BY Sdept;

修改数据

语句格式：

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

功能
- 修改指定表中满足WHERE子句条件的元组
- SET子句给出<表达式>的值用于取代相应的属性列
- 如果省略WHERE子句，表示要修改表中的所有元组

/*将学生201215121的年龄改为22岁*/
UPDATE  Student
SET Sage=22
WHERE  Sno=' 201215121 '; 


/*将所有学生的年龄增加1岁*/
UPDATE Student
SET Sage= Sage+1;

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

删除数据

语句格式：

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

功能
- 删除指定表中满足WHERE子句条件的元组
WHERE子句
- 指定要删除的元组
- 缺省表示要删除表中的全部元组，表的定义仍在字典中

/*删除学号为201215128的学生记录*/
DELETE
FROM Student
WHERE Sno= 201215128 ';

/*删除所有的学生选课记录*/
DELETE
FROM SC;

/*删除计算机科学系所有学生的选课记录*/
DELETE
FROM  SC
WHERE  Sno  IN
    (SELETE  Sno
    FROM   Student
    WHERE  Sdept= 'CS') ;

视图

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

1. 定义视图

1.1 建立视图

语句格式：

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

• WITH CHECK OPTION

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

• 子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。

/*建立信息系学生的视图*/
CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM     Student
WHERE  Sdept= 'IS';

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

1.2 删除试图

语句格式：

DROP  VIEW  <视图名>[CASCADE];

• 该语句从数据字典中删除指定的视图定义
• 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
• 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

2.查询视图

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

• 关系数据库管理系统实现视图查询的方法

  视图消解法（View Resolution）
  进行有效性检查
  转换成等价的对基本表的查询
  执行修正后的查询

/*在信息系学生的视图中找出年龄小于20岁的学生*/
SELECT   Sno,Sage
FROM      IS_Student
WHERE   Sage<20;

/*视图消解转换后的查询语句为：*/
 SELECT  Sno,Sage       
 FROM  Student
 WHERE  Sdept= 'IS'  AND  Sage<20;