数据的操作 标准语言SQL

       结构化查询语言（SQL）是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库言语。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。

       SQL集数据查询、数据操纵、数据定义、数据控制功能与一体。  

综合统一：数据库系统的主要功能是通过数据库支持的数据语言来实现的。

非关系模型（层次模型，网状模型）的数据语言一般都分为：

（1）模式数据定义语言（DDL）

（2）外模式数据定义语言（外模式DDL）

（3）数据存储有关的语言（DSDL）

（4）数据操纵（DML）

！！！！！！！！！！

重点：

SQL操作要求：

1、定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库

2、对数据库中的数据进行查询和维护

3、数据库的重构和维护

4、数据库安全性、完整性控制，以及事物控制

5、嵌入式SQL和动态SQL定义

SQL的动词SQL功能动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE

                         数据定义

SQL的数据定义语句操作对象创建删除修改模式CREATE SCHEMADROP SCHEMA 表CREATE TABLEDROP TABLEALTER TABLE视图CREATE  VIEWDROP VIEW 索引CREATE INDEXDROP INDEXALTER INDEX

一、模式的定义和删除

（1）定义模式：

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

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

例如：为用户WANG定义一个学生—课程模式S-T

CREATE SCHEMA S-T AUTHORIZATION WANG;

在CREATE SCHEMA中可以加入CREATE TABLE，CREATE VIEW和GRANT子句

即

CREATE SCHEMA <SCHEMA NAME>AUTHORIZATION<用户名>[<TABLE 定义子句>|<VIEW 定义子句> |<GRANT 定义子句>]

（2）删除模式：

DROP SCHEMA <NAME> <CASCADE （级联）|RESTRICT（限制）>（在写语句时二选一！！）

CASCADE：表示在 DROP SCHEMA的同时把该模式中所有的数据库对象全部删除

RESTRICT：表示如果该模式中已经定义了下属的数据库对象（如TABLE，VIEW），则拒绝该DROP SCHEMA 语句的执行！

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

（1）定义基本表

创建了一个模式就是建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表

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

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

………………

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

例：创建一个学生表

CREATE TABLE stduent

(

Sno  CHAR(9) PRIMARY KEY,

Sname CHAR(20) UNIQUE,

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

);

数据类型：

CHAR（n），长度为n的定长字符串

INT，长整数

SMALLINT，短整数

CLOB 字符串大对象

BLOB二进制大对象

BIGINT 大整数

DATE 日期  格式为YYYY-MM-DD

TIME 时间  格式为HH：MM：SS

TIMESTAMP  时间戳类型

INTERVAL  时间间隔类型

FLOAT（n） 精度至少为n位数字。

DOUBLE 双精度浮点数

修改基本表

ALTER TABLE<TABLE name>

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

[ADD<表级完整性>]

[DROP[COULUMN][CASCADE | RESTRICT]]

删除基本表

DROP TABLE <TABLE name>[CASCADE | RESTRICT]

三、索引建立和删除

（1）索引的建立

CTREAT [UNIQUE]  [CLUSTER] INDEX<INDEX name>

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

次序中可以写ASC（升序），DESC（降序）；

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

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

（2）索引的修改

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

（3）DROP INDEX<索引名>

                                     数据查询

查询的格式：

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

FROM<表名或者视图名> | （<SELECT>）[AS]<别名>

[WHERE<条件表达式>]

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

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

下面给出例题表

Student学号
Sno姓名
Sname性别
Ssex年龄
Sage所在系
Sdept201215121李勇男20CS201215122刘晨男19CS201215123王敏女18MA201215125张立女19IS

                     

Course课程号
Cno课程名
Cname先行课
Cpno学分
Ccredit省略……省略……省略……省略……省略……省略……省略……省略……

SC学号
Sno课程号
Cno成绩
Grade省略……省略……省略……省略……省略……省略……

            单表查询

SELECT 关键字

不同操作大全

功能：（1）查询指定列

实现：SELECT  Sno

           FROM   Student;

 功能：（2）查询全部列

 实现：   SELECT  *（或者将全部列的名称打出来）

              FORM  Student；

 功能：（3）查询经过计算的值

 实现：   SELECT 2014-Sage                             //查询结果的计算值，此为一个运算式 ，Sage是一个表的列

              FROM   Students；

功能：（4）改变显示列名

实现：SELECT  2014-Sage birthday //显示时，不显示2014-Sage，而显示birthday

功能：（5）消除重复项

实现：关键字 DISTINCT

            SELECT DISTINCT Sno

注释：如果没有 写DISTINCT ，默认为ALL ，也就是可以显示重复的项

WHERE 关键字

1、比较大小

用于进行比较的运算符一般包括=，>, < , >= , <= , !=和<> same，!> ,!< （用于WHERE 格式）

功能：（6）查询计算机科学系全体学生的名单

实现：    SELECT Sname

              FROM  Student

              WHERE Sdept='CS';

功能：（7）查询所有年龄在20岁以下的学生姓名及其年龄

实现：SELECT    Sname，Sage

     　   FROM Student

           WHERE  Sage<20;

2、确定范围  用BETWEEN……AND……和NOT BETWEEN……AND……

功能：（8）查询年龄在和不在的20~23岁的学生           

实现：在WHERE BETWEEN 20 AND 23 （NOT BETWEEN 20 AND 23）

3、字符匹配：语法格式： [NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']

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

%：代表的是任意长度的字符串（可以为0）

_：代表的是任意单个的字符

功能：（9）将_和%转义                    查找DB_Design的课程号

实现：“\”转义字符

               SELECT Cno

               FROM Course

               WHERE  Cname LIKE  'DB\_Design'  ESCAPE'\' ;

4、确定集合 谓词 IN 可用来查找属性值属于指定集合的元组，NOT IN查属性值不属于指定集合的元组

功能：（10）查询多个列的集合元组

实现：  SELECT  Sname，Ssex

             FROM  Student

             WHERE Sdept （NOT）IN  （'CS','MA','IS'）;  //不属于或属于CS,MA,IS集合的元组

ORDER BY关键字

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的缺省值：升序（ASC）和DESC（降序）排序

功能：查询某某某的成绩，查询结果按照分数高低的排序

实现：SELACT Sno，GRADE

          FROM SC
          WHERE Cno=‘3’

         ORDER BY GRADE DESC；

聚集函数：

COUNT（*） 统计

COUNT（DISTINCT  列名）   计算某一列的个数

SUM （DISTINCT 列名）       计算某列的总和

AVG  （DISTINCT 列名）       计算某列的平均值

MAX  （DINTINCT 列名）      计算某列的最大值

MIN     （DINTINCT 列名）    计算某列的最小值

实现： SELECT AVG（GRADE）

          FROM SC

          WHERE Cno='1';

注释：当聚集函数遇到空值时，除了COUNT(*)外，都跳过空值而只处理非空值。COUNT（*）是对元组进行计算，某一个元组的一个或部分空值不影响COUNT的统计结果。

！！！！！！聚集函数只能用于SELECLT子句和GROUP BY的子句HAVING 子句

GROUP BY关键字

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

分组后聚集函数将作用于每一组，即每一组都有一个函数值

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

功能：求各个课程号及相应的选课人数

实现： SELECT Cno，COUNT（Sno）

           FROM SC

           GROUP BY Cno；                 // 先按照不同Cno的值分组，一样为一组， 在计算选同一Cno的数量的值

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT（*）>3；      //先将Sno值一样的分为一组，而此语句的作用是计算此组 元组的个数是几个并选出大于3的那一个组。！

                                        连接查询

连接查询包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、自身连接查询、外连接查询和复合条件连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

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

比较运算符有=，>、<、>=、<=、!=(或<>)

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

1、等值连接查询

当运算符为=时，称为等值连接。使用其他运算符时称为非等值连接

功能：查询每一个学生及选修课的情况

实现：SELECT Student.*,SC.*

           FROM Student,SC

          WHERE Student.Cno=SC.Cno;

2、自然连接

除去目标列中重复的属性列

功能：对上一等值连接改为自然连接

实现： SELECT Student.Cno,Sname,Ssex,Sage,Sdept,Cno,GRADE

            FROM  Student,SC

            WHERE Student.Cno=SC.Cno;

功能：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

实现：SELECT  Student.Sno,Sname

          FROM   Studnet,SC

         WHERE   Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>'90';        //这里不能将SC省去，因为此时数据库管理系统还未将TABLE Student和TABLE SC连接在一起（个人见解，错误请指出）

3、自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接

功能：查询每门课的间接先行课

实现： SELECT First.Cno ,Second.Cpno

            FROM  Course First，Course Second

           WHERE First.Cno=Second.Cno;      //此功能的关键是将Course 表利用 别名 分成两个具有相同的属性列的副本，再将两个副本连接

4、外连接

关键字：<TABLE name1>LEFT（RIGHT 或两个都不写（为外连接）） OUTER JION <TABLE name2>  ON（USING （功能为去掉重复项））（Student.Sno=SC.Sno）或者（Sno）

5、多表连接

功能：查询每个学生的学号、姓名、选修的课程名及成绩

实现：SELECT Student.Sno,Sname,Course.Cname,Grade

           FROM Student,Coures,SC

           WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;  //关键是AND和“=” 将三表连起来

 

                                  嵌套查询

定义：在SQL语言中，一个SELECT-FROM-WHEWE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子局或HAVING短语的条件中的查询称为嵌套查询

例如：SELECT Sname

           FROM  Student

           WHERE Sno IN

//上面为父查询，下面为子查询

                               （SELECT  省略……

                                   省略……）

先执行子查询，再执行父查询

1、不相关子查询

定义：子查询的查询条件不依赖于父查询

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

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(SOME)或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。

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

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

其他比较运算符的意义类似

例题：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

解：

        SELECT  Sname，Sage

        FROM    Student

        WHERE  Sage<ALL(

                                                                           SELECT Sage

                                                                            FROM   Student

                                                                             WHERE  Sdept='CS'

)AND Sdept!=(或<>)'CS';

注释：聚集函数实现通常比直接用ANY查询效率高

ANY、ALL与聚集函数、IN谓词的等价关系 =<>或！=<<=>>=ANYIN……<MAX<=MAX>MIN>=MINALL……NOT IN<MIN<=MIN>MAX>=MAX 

4、带有EXISTS

EXISTS代表存在量词 （反E）。带有EXISTS谓词的子查询不返回任何数据，只要产生逻辑真或假（true和false）

如果EXISTS子句中为非空值，则返回真值，否则返回假值

而NOT EXISTS子句中为非空值，则返回假值，否则返回真值

 注释：一些EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS等价代替

在SQL语言没有全称量词。出现全称量词时用存在量词替换，（利用离散数学的知识来解决问题）

 语言格式：

SELECT  某属性列名

FROM   某表名

WHERE （NOT）EXISTS

{ SELECT *

FROM 表名

WHERE 某条件

}

                      集合查询

定义：SELECT 语句的查询结果是元组的集合，所以多个SELECT的结果可进行集合操作。集合操作主要包括并操作UNION，交操作INTERSECT和差操作EXCEPT

注释：参加集合操作的个查询结果的列数必须相同；对应的数据类型必须相同

UNION，INTERSECT和EXCEPT可等价替换

在UNION操作时，系统会自动去掉重复项，UNION ALL 可将重复项保留

                       基于派生表的查询

通俗易懂的讲：就是将已经经过查询操作后显示的这个表 去个新名字，将这个表放到  FROM （TABLE 表名）中，是接下来的查询可以使用这个已经查询出来的表

产生的派生表只是一个临时table，当此次操作结束时，派生表将被删除，如何下一操作还想用派生表，必须重新建立

例子：

 SELECT Sname

FROM    SC，（SELECT Sname  FROM Student WHERE Sdept='CS'）AS newname

WHERE     SC.Sno=newname.Sno;

                                 数据更新

数据更新有三种：添加（INSERT），删除（DELETE），修改（UPDATE）

1、插入数据

  INSERT

 INTO <表名>（<属性列1>，<属性列2>,<属性列3>，……）

  VALUES(<常量1>，<常量2>,<常量3>……)；

注释：如果只写表名的话，在VALUES中某一常量为空，也必须在对应的位置填上NULL，NULL不用加单引号：如何将属性列写出的话，要一一对应写出常量，系统自动会将没有的属性列附NULL

2、插入子查询结果

INSERT

INTO <表名>（<属性列1>，<属性列2>,<属性列3>，……）

子查询；

例子：

INSERT

INTO newtavlename(Sno)

SELECT Sno                     //子查询的属性列要和要插入另一个表中所显示的属性列一样

FROM SC

WHERE Sdept='CS'；

3、修改数据

UPDATE <table name>

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

[WHERE <条件> ]；

 修改一个学生的值；

例子：将某学号的学生的年龄改为23岁；

UPDATE Student

SET Sage=23

WHERE Sno='某学号'；

 

修改多个学生的值；

UPDATE Student

SET Sage=Sage+1

WHERE  Sno  IN(

SELECT  Sno

FROM  Student

WHERE   Sdept='CS'

) ;                          //带有子查询的修改多个数据

4、删除数据

DELETE

FROM <表名>

WHERE <条件>；

删除也分三种：单个删除，多个删除，带子查询的删除

                                 空值的处理

定义：空值就是“不知道”或“不存在”或“无意义的值”。

情况的讨论：1、本有值，却不知道值

                     2、该属性不应该有值

                     3、由于某种情况不给出值

 1、空值的产生

在INSERT时系统给出的空值

 2、空值的判断

 IS NULL 或者IS NOT NULL来表示

 3、空值的约束条件

 属性定义中有NOT NULL约束条件时，不能取空；加UNIOUE限制的属性不能取空

 4、空值的运算、比较运算和逻辑运算

一个空值与另一个值（包括空值）的算术运算的结果为空值，空值与另一个值（包括空值）的比较运算的结果为UNKNOWN

只有使WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

                                                  视图

定义：从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，不存放视图对应的数据。从这个意义上讲，视图就想一个窗口，透过它可以看到数据库中自己感兴趣的数据及变化

 视图是一个虚表，对视图的操作最终要转换为对基本表的操作

1、建立视图

CREATE VIEW<VIEW name>[（(列名)[,<列名>]……）]

AS<子查询>

[WITH  CHECK  OPTION];

组成视图的属性列或者全部省略或全部指定，没有第三种选择。如果视图中的省略，那视图中的属性由SELECT 子句中的属性列组成

有三种情况，视图的属性列必须指定：

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

                                                      2、多表连接时选出了几个同名作为视图的字段

                                                      3、需要在视图中为某一列启用新的更合适的名字

例题：

建立信息系学生的视图

CREATE VIEW IS_Student

AS 

SELECT Sno,Sage,Sname

FROM Student

WHERE Sdept='IS';

现在给出两个的概念：

行列子集视图：此视图的属性列是基本表中有的是属性列

分组视图：带有聚集函数和GROUP BY子句的查询来定义视图

2、删除视图

DROP VIEW 视图名 CESCADE

CESCADE（级联）可强行删除包含其他视图的视图

不加CESCADE，将无法删除包含有其他视图的视图

3、查询视图

视图消解：从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，然后再执行修正的查询的这一过程

查询视图有两种情况：

1、对查询非行列子集视图（带有聚集函数和列表达式的视图），须要对视图所对应的基本表直接查询，另一种方法为利用派生表

2、对查询行列子集视图，可以查询视图，也可以直接查询基本表

4、更新视图

与数据更新类似，但是视图的更新的条件更加苛刻

（不可更新（真理）和不允许更新（假设，无法实现）概念不同）

在DB2：1、由两个及以上的基本表出的视图

              2、基本表中有聚集函数

              3、视图的字段来自字段表达式或常数

              3、视图定义中有GRUOP BY语句

              5、视图定义中有IDSTINCT短语

              6、视图定义中有嵌套语句

              7、一个不允许更新的视图上定义的视图也不允许更新

例题：

UPDATE IS_Student  //视图名

SET Sname='刘晨'

WHERE Sno='201501554';

程序并不直接运行此语句

而是将他转化为对基本表的修改（视图消解）

UPDATE  Student

SET  Sname='刘晨'

WHERE  Sno='201501554' AND Sdept='IS'；

INSERT 

INTO 视图名（列名集）

VALUES（常数集）；

和

DELETE

FROM 视图名

WHERE 条件；

和UAPDATE 操作类似

视图的优点：
1、视图简化用户的操作

2、视图使用户多种角度看同一数据

3、视图对重构数据库提供了一定程度的逻辑独立性

4、视图能够对机密数据提供安全

5、适当利用视图可以更加清晰地表达查询

…………………… THE  END………………………