数据库概论学习笔记——第三单元

第三章 关系数据库标准语言SQL

3.1 SQL概述

SQL（Structured Query Language）

结构化查询语言，是关系数据库的标准语言

SQL是一个通用的、功能极强的关系数据库语言

SQL 的产生与发展

目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。

SQL的特点

1.综合统一

2.高度非过程化

3.面向集合的操作方式

4.以同一种语法结构提供多种使用方式

5.语言简洁，易学易用

SQL的基本概念

 

3.2 数据定义

SQL的数据定义功能:

模式定义 CREATE SCHEMA 删除 DROP SCHEMA

表定义 CREATE TABLE 删除 DROP TABLE 修改 ALTER TABLE

视图定义 CREATE VIEW 删除 DROP VIEW

索引定义 CREATE INDEX 删除 DROP INDEX 修改 ALTER INDEX

 

模式定义与删除

模式定义

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

删除模式

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

        CASCADE（级联）

            删除模式的同时把该模式中所有的数据库对象全部删除

        RESTRICT（限制）

            如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。 仅当该模式中没有任何下属的对        象时才能执行。

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

定义基本表

    CREATE TABLE <表名>      

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

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

    …      

    [,<表级完整性约束条件> ] );

    <列级完整性约束条件>：涉及相应属性列的完整性约束条件。

    <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件。

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

模式与表

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

修改基本表

    ALTER TABLE <表名>

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

    [ ADD <表级完整性约束>]

    [ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]

    [ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]

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

    ADD：子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件

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

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

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

    DROP CONSTRAINT：子句用于删除指定的完整性约束条件

    ALTER COLUMN：子句用于修改原有的列定义，包括修改列名和数据类型

删除基本表

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

    RESTRICT：删除表是有限制的。

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

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

    CASCADE：删除该表没有限制。

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

索引的建立与删除

建立索引

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

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

    UNIQUE：此索引的每一个索引值只对应唯一的数据记录

    CLUSTER：表示要建立的索引是聚簇索引

修改索引

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

删除索引

    DROP INDEX <索引名>;

3.3 数据查询

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.选择表中的若干列

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

        SELECT Sno,Sname         

        FROM Student;                              /*代表全部*/

    查全体学生的姓名及其出生年份。     /*查询经过计算的值*/

        SELECT Sname,2018-Sage          /*假设当时为2018年*/

        FROM Student;

    使用列别名改变查询结果的列标题

        SELECT Sname NAME,'Year of Birth:'  BIRTH, 2014-Sage  BIRTHDAY,LOWER(Sdept)  DEPARTMENT     

        FROM Student;

2.选择表中的若干元组

    消除取值重复的行

        SELECT DISTINCT Sno    

        FROM SC;

    查询满足条件的元组

        查询所有姓刘学生的姓名、学号和性别。

SELECT Sname, Sno, Ssex      

FROM Student      

WHERE  Sname LIKE '刘%';        /*%代表任意长度通配符，_代表单个字符长度，ESCAPE '＼' 表示“ ＼” 为换码字符*/

3.ORDER BY子句

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

    查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

        SELECT Sno, Grade        

        FROM    SC        

        WHERE  Cno= ' 3 '        

        ORDER BY Grade DESC;

4.聚集函数

    统计元组个数      

        COUNT(*)

    统计一列中值的个数      

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

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

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

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

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

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

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

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

5.GROUP BY子句

    细化聚集函数的作用对象  

        如果未对查询结果分组，聚集函数将作用于整个查询结果  

        对查询结果分组后，聚集函数将分别作用于每个组

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

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

        SELECT Sno      

        FROM  SC      

        GROUP BY Sno      

        HAVING  COUNT(*) >3;        /*HAVING用来写分组条件*/

连接查询

1.等值与非等值连接查询

    查询每个学生及其选修课程的情况

        SELECT  Student.*, SC.*                 

        FROM     Student, SC                 

        WHERE  Student.Sno = SC.Sno;

    （1）嵌套循环法（NESTED-LOOP）

    （2）排序合并法（SORT-MERGE）

        首先按连接属性对表1和表2排序

        对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起        来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续

    （3）索引连接（INDEX-JOIN）

        对表2按连接字段建立索引

        对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与    该元组拼接起来，形成结果表中一个元组

2.自身连接

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

需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

    查询每一门课的间接先修课（即先修课的先修课）    

        SELECT  FIRST.Cno, SECOND.Cpno      

        FROM  Course  FIRST, Course  SECOND      

        WHERE FIRST.Cpno = SECOND.Cno;

3.外连接

    普通连接操作只输出满足连接条件的元组

    外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

4.多表连接

    多表连接：两个以上的表进行连接

嵌套查询

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

不相关子查询：子查询的查询条件不依赖于父查询

相关子查询：子查询的查询条件依赖于父查询

1.带有IN谓词的子查询

    查询与“刘晨”在同一个系学习的学生。

        SELECT Sno, Sname, Sdept         

        FROM Student        

        WHERE Sdept  IN                  

            (SELECT Sdept                    

            FROM Student                    

            WHERE Sname= ' 刘晨 ');

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

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

        SELECT Sno, Cno    

        FROM    SC  x    

        WHERE Grade >=(SELECT AVG（Grade）                                 

                                      FROM  SC y                                    

                                      WHERE y.Sno=x.Sno);

3.带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算 语义为：      

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

> ALL    大于子查询结果中的所有值

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

< ALL    小于子查询结果中的所有值

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

>= ALL    大于等于子查询结果中的所有值

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

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

4.带有EXISTS谓词的子查询(表示除运算)

    查询所有选修了1号课程的学生姓名。

        SELECT Sname      

        FROM Student      

        WHERE EXISTS                     

                                  (SELECT *                    

                                  FROM SC                    

                                  WHERE Sno=Student.Sno AND Cno= ' 1 ');

集合查询

并操作UNION

交操作INTERSECT

差操作EXCEPT

    查询选修了课程1或者选修了课程2的学生。

        SELECT Sno        

        FROM SC        

        WHERE Cno=' 1 '        

        UNION        

        SELECT Sno        

        FROM SC        

        WHERE Cno= ' 2 ';

3.5 数据更新

插入数据

1. 插入元组

INSERT     

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

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

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

2. 插入子查询结果

INSERT      

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

子查询;

修改数据

UPDATE  <表名>    

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

[WHERE <条件>];

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

实体完整性

主码不允许修改

用户定义的完整性  

    NOT NULL约束  

    UNIQUE约束  

    值域约束

删除数据

DELETE        

FROM     <表名>        

[WHERE <条件>];

3.6 空值的处理

1.空值的判断

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

2.空值的约束条件

    有NOT NULL约束条件的不能取空值

    加了UNIQUE限制的属性不能取空值

    码属性不能取空值

3.7 视图

视图的特点

1.虚表，是从一个或几个基本表（或视图）导出的表

2.只存放视图的定义，不存放视图对应的数据

3.基表中的数据发生变化，从视图中查询出的数据也随之改变

定义视图

1.建立视图

CREATE  VIEW              

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

AS  <子查询>        

[WITH  CHECK  OPTION];

WITH CHECK OPTION 

    对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件

2. 删除视图

DROP  VIEW  <视图名>[CASCADE];

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

查询视图

查询视图与查询基本表相同