你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

数据库基础-03

2021-11-17 18:39:54

三、关系数据库标准查询语言SQL

一、SQL概述

(一)、SQL语言的组成

1.数据定义(DDL)

——定义数据库的逻辑结构,包括基本表、视图、索引等

2.数据操纵(DML)

——包括查询和更新,更新又包含插入、删除和修改

3.数据控制(DCL)

——授权、完整性规则描述、事务控制等

4.嵌入式SQL(ESQL)

——在宿主语言中使用SQL的规则

(二)、SQL语言的特点

——综合统一、面向集合的操作方式、高度非过程化、统一的语法结构提供两种使用方式(自含式、嵌入式)、语言简洁

-DDL: create drop alter

-DML: select insert delete update

-DCL: grant revoke commit rollback

二、数据定义语言(DDL)

(一)、定义、删除与修改基本表

1.定义基本表语法

create table <表名> (<列名><数据类型>(列级约束条件),<列名><数据类型>(列级约束条件),...,<表集完整性约束条件>);

2.修改表语法

alter table <表名>    ADD<新列名><数据类型>(列级约束条件);
alter table <表名>    MODIFY<列名><数据类型>;
alter table <表名>    DROP<完整性约束条件>;

3.删除表语法

drop table <表名>;

(二)、建立和删除索引

1.索引的建立语法

create unique index <索引名> on <表名>(<列名1><次序>,...)
    <次序>可以是ASC和DESC

2.索引的删除语法

​
drop index [<表名>]<索引名>

​

三、SQL的数据查询(DML)

    SELECT <目标表达式1> ,<目标表达式2> ... ...
    FROM <表名或视图名1> ,<表名或视图名2>... ...
    WHERE <条件表达式>
    GROUP BY <列名表达式1>,<列名表达式2> HAVING <条件表达式>
    ORDER BY <列名表达式1> ASC|DESC, <列名表达式2> ASC|DESC

—先按WHERE子句条件从FROM子句指定的表/视图中 找出满足条件的元组(选择)

—如有GROUP子句,则将结果按<列名表达式>的值分组,该<列名表达式>值相等的元组为一个组,通常会在每组中使用聚合函数。

—如果GROUP子句带HAVING子句,则对组过虑,将满足条件的组输出

—再按SELECT子句中的目标表达式选择出元组中的属性,形成结果表(投影)

—如果ORDER子句,则将结果按<列名表达式1>的值 升序或降序排列

(一)、单表查询

——消除取值重复行

 SELECT DISTINCT SD FROM S 

——查询满足条件的元组

  1. 比较大小:<、<= 、>、>=、=、<>

 SELECT SN,SA FROM S WHERE SD=’CS’
 SELECT * FROM S WHERE SA<20
  1. 确定范围:BETWEEN... AND

 SELECT * FROM S WHERE SA BETWEEN 20 AND 21 

     2. 确定集合:IN

 SELECT * FROM S WHERE SD IN (‘CS’,’IS’,’MA’) 

     3. 字符匹配:LIKE,转义字符’\’

 SELECT * FROM S WHERE S# LIKE ‘TB%’
 SELECT * FROM S WHERE SN LIKE ‘刘_’

     4.涉及空值的查询:IS NULL

SELECT * FROM SC WHERE GR IS NULL 

     5.多重条件查询:

 SELECT * FROM S WHERE SD=’CS’ AND SA<20 

——查询结构排序

ORDER BY <字段表达式> ASC|DESC 
SELECT * FROM SC WHERE C#=’3’ ORDER BY GR DESC

——使用集(聚合)函数

COUNT 、SUM、AVG、MAX、MIN
SELECT COUNT(*) FROM S
SELECT COUNT(DISTINCT S#) FROM SC
SELECT AVG(GR) FROM SC WHERE S#=’95001’
SELECT MAX(GR) FROM SC WHERE C#=’1’

——查询分组:GROUP BY

SELECT C#,COUNT(*) FROM SC GROUP BY C# 
SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3        

(二)、连接查询

——等值与非等值链接查询 自然连接

SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S# 

——自身连接

SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C# 

——外连接

SELECT S#,SN,SS,SA,SD,C#,GR 
FROM S LEFT OUTER JOIN SC ON S.S#=SC.S#

——复合条件连接

#检索选修课程号‘2’且成绩在90分以上的所有学生 
SELECT S.S# ,SN FROM S,SC 
WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90

(三)、嵌套查询

——带IN谓词的子查询

检索与“刘晨”同在一系的学生信息 

SELECT S#,SN,SD FROM S WHERE SD IN
(SELECT SD FROM S WHERE SN=‘刘晨’)
​
本例可以通过自连接来实现 
SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2
WHERE s1.SD = s2.SD AND s2.SN=’刘晨’

——带比较运算的子查询

当确定子查询的返回值是唯一时,可以使用比较运算
符(注意子查询在比较符后)
SELECT S#,SN FROM S WHERE SD=
(SELECT SD FROM S WHERE CN=’刘晨’)

——带ANY和ALL的子查询(子查询返回多值时用)

检索其他系中比IS系任一学生年龄小的学生名单 
SELECT S#,SN FROM S WHERE SA < ANY
(SELECT SA FROM S WHERE SD=‘IS’)
AND SD<>‘IS’
ORDER BY SA DESC
等价于
SELECT S#,SN FROM S WHERE SA < 
(SELECT MAX(SA) FROM S WHERE SD=‘IS’)
AND SD <> ‘IS’
ORDER BY SA DESC 

——带EXISTS的子查询(不返回任何数据,只返回True和False)

检索所有选修了课程号为‘1’的学生姓名 

SELECT SN FROM S WHERE EXISTS
(SELECT * FROM SC WHERE S# = S.S# AND C# = ‘1’)
注意:此例中子查询的查询条件依赖于外层父查询,称此类查询为相关子查询(corelated subquery)。 
​
等价连接实现:
SELECT SN FROM S,SC WHERE S.S# = SC.S# AND C# = ‘1’ 

注:SQL中没有(任取x)p,故须转换为¬(存在x(¬p)),p->q应被等价为¬p∨q

(四)、集合查询

INTERSECT、UNION、MINUS

检索选修了课程号为C01或C02的学生学号
SELECT S# FROM SC WHERE C#=‘C01’
UNION  SELECT S# FROM SC WHERE C#=‘C02’
等价于:
SELECT S# FROM SC WHERE C# IN (‘C01’,‘C02’)

四、SQL的数据更新(DML)

(一)、数据插入

——插入单个元组

insert into <表名> <列名1>,... values(<'常量1'>,<'常量2'>);

——插入子查询结果

insert into <表名> <列名1>,... <子查询>

(二)、数据修改

update <表名> set <列名> = <表达式> where<条件>

——修改某一个元组的值

UPDATE S SET SA=22 WHERE S# =’S001’ 

——修改多个元组的值

UPDATE S SET SA=SA+1

——带子查询的修改语句

将计算机科学系所有的学生成绩置零 
UPDATE SC SET GR=0
WHERE ‘CS’ = (SELECT SD FROM S WHERE S# = SC.S#) 

(三)、数据删除

delete from <表名> where <条件>

——删除某一个元组的值

delete from S where S# = 'S001

——删除多个元组的值

delete from SC

——带子查询的删除语句

删除计算机科学系所有学生的选课记录 
DELETE FROM SC WHERE ‘CS’=(
SELECT SD FROM S WHERE S#=SC.S#) (相关子查询) 
DELETE from SC where S# in 
(SELECT S# from S where SD=’CS’) (非相关子查询) 

五、视图

(一)、定义视图

1.建立视图

create view <视图名> (<列名1>,...)
as <子查询> with check option
with check option 表示对视图更新时自动验证子查询条件
建立一个反映学生出生年月的视图 

    CREATE VIEW BT_S(S#,SN,SB)as
    SELECT S#, SN,2003-SA FROM S  
建立一个学生学号和平均成绩的视图 
    CREATE VIEW S_G(S#,AVG_GR)AS 
    SELECT S#, AVG(GR) FROM SC GROUP BY S# 

2.删除视图

drop view <视图名>

(二)、查询视图

——对视图的查询转化为对基本表的查询成为视图的消解

SELECT S#,SA FROM IS_S WHERE SA <20 
消解为:

SELECT S# ,SA FROM S WHERE SD=’IS’ AND SA <20 
SELECT * FROM S_G WHERE AVG_GR>90 
消解为:
SELECT S#, AVG(GR) FROM SC WHERE AVG(GR)>90 GROUP BY S#  (错误)
SELECT S#, AVG(GR) FROM SC GROUP BY S# HAVING AVG(GR)>90  (正确)

(三)、更新视图

——视图的修改

将信息系学生视图中学号为S001的学生姓名改为‘刘辰’

UPDATE IS_S SET SN=‘刘辰’ WHERE S#=‘S001’  
视图消解为:
UPDATE S SET SN=‘刘辰’ WHERE S#=‘S001’AND SD=‘IS’

——视图的插入

INSERT INTO IS_S VALUES (‘S001’,‘刘辰’,20) 
视图消解: 
INSERT INTO S VALUES (‘S001’,‘刘辰’,NULL,20,‘IS’) 

——视图的删除

在信息系学生视图中删除记录 
DELETE FROM IS_S WHERE S#=‘S001’
视图消解 :
DELETE FROM S WHERE S#=‘S001’AND SD=‘IS’  

注:不允许更新的视图规则

  1. 由两个以上基本表导出

  2. 视图的字段来自常数或表达式,只允许delete

  3. 视图的字段来自集函数

  4. 视图中含有group by

  5. 视图中含义distinct

  6. 视图定义由嵌套查询,且内层查询涉及到导出本视图的基本表

  7. 不允许更新视图上定义的视图

(四)、视图的用途

  1. 简化用户的操作

  2. 使用户多角度看待同一数据

  3. 对重构数据库提供了一定的逻辑独立性

  4. 对数据提供安全保护

六、数据控制语言(DCL)

(一)、授权

  • 语法

    GRANT {ALL PRIVILEGES <权限>}
    [on <对象类型><对象名>]
    TO {PUBLIC <用户>,...}
    WITH GRANT OPTION
  • 例子

    GRANT SELECT ON TABLE S To USER1
    GRANT ALL Privileges ON TABLE S,C TO U2,U3
    GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION

(二)、收回权限

  • 语法

    ​
     REVOKE {ALL PRIVILEFES <权限>}
     ON <对象类型><对象名>
     FROM {PUBLIC<用户>,..}
    
    ​
  • 示例

REVOKE SELECT ON TABLE SC FROM PUBLIC;
REVOKE UPDATE(SD),SELECT ON TABLE S FROM U4;
REVOKE INSERT ON TABLE SC FROM U5;

七、嵌入式SQL语言

  • SQL语言是非过程的,而应用大多是过程化的,故通过高级语言来弥补SQL过程控制的不足。

  • 将SQL嵌入高级语言来执行,称嵌入式SQL语言

(一)、嵌入式SQL的一般形式

  • 对于ESQL的处理,DBMS一般有两种处理方式:预编译修改和扩产宿主语言以处理SQL

  • ESQL一般形式:EXEC SQL <SQL 语句>

  • ESQL根据其作用不同分为两类:

    • 可执行语句

    • 说明性语句

(二)、嵌入式SQL语句与主语言之间的通信

  • 数据库工作单元和主语言工作单元之间的通信有

    • 向主语言传递SQL语句的执行状态

    • 主语言向SQL语句提供参数

    • 将SQL语句查询数据库结果交主语言进一步处理

  • 相应的通过SQLCA、主变量、游标来实现

  • SQL通信区

    • SQLCA是一个数据结构,定义语句

    EXEC SQL INCLUDE SQLCA
    • SQLCODE反映每次执行SQL语句的结果

  • 主变量

    • 主要功能:ESQL可以使用主语言的变量来输入和输出数据

    • 分类:输入、输出主变量、指示变量

    • 使用方法

      • 所有主变量在定义区定义

      • 可以在SQL中任意表达式的地方出现

      • 在SQL语句中,主变量前加';',在诸语言中不必加

      • 指示变量用于为输入变量赋空值或指示输出变量是否空值

  • 游标

    • 使用原因:SQL语句是面向集合的,而主语言是面向记录的

    • 主语言和SQL语言的分工

      • SQL语言负责直接和数据库打交道

      • 主语言用来控制程序流程以及对SQl的执行结构进一步处理

      • SQL语言用主变量从主语言接受执行参数操作数据库->SQL语言的执行状态由DBMS送至SQLCA->主语言从SQLCA取出状态信息,据此决定下一步操作

      • SQL的执行结果通过主变量或游标传给主语言处理

(三)、不使用游标的SQL语句

  • 说明性语句

EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECALRE SECTION;
EXeC SQL END DECALRe SECTION;
  • 数据定义语句

EXEC SQL CREATE TABLE S(
    S# char(10),
    SN char(10),
    SS char(2),
    SA int,
    SD char(5)));
    EXEC SQL DROP TABLE;
    EXEC SQL DROP TABLE :tablename;(错误,不允许使用主变量)
)
  • 数据控制语句

授权:EXEC SQL GRANT SELECT ON TABLE S TO U1;
连接数据库:EXEC SQL CONNECT :user IDENTIFIED BY :pass USING :tnasname;
出错处理:EXEC SQL WHEREVER SQLERROR do sql_error()
  • 查询结构为单条记录SELECT语句

    • 语法

    EXEC SQL SELECT <目标表达式>,.. INTO <主变量1><指示变量1>,.. FROM <表名或视图名>,... WHERE/GROUP BY/  HAVING/ ORDER BY..
    • 例子

    EXEC SQL SELECT S#,SN INTO :sno,:sn FROM S WHERE S# =: GibenSno
    • 注意

      • into,where和having语句字句中均可以使用主变量,需要事先申明。

      • 返回值某列为NULL时,系统会将指示变量赋值为-1,主变量不变

      • 查询结果满足条件的记录,则DBMS置sqlcode值为100,正常有结果为0

      • 如结果不止单条,程序出错,SQLCA中包含发挥信息

  • 非CURRENT形式的UPDATE语句

    EXEC SQL UPDATE SC SET GR = GR+:Raise WHERE C# = 'C01';
    EXEC SQL UPDATE SC SET GR=:newgr WHERE S# = 'S001';
    Grid = -1;
    EXEC SQL UPDATE SC SET GR =:newgr :grid WHERE S# IN (SELECT S# FROM S WHERE SD = 'CS')
  • 非CURRENT形式的DELETE语句

   EXEC SQL DELETE FROM SC WHERE S# IN (SELECT S# FROm S WHERE SN=:name)
  • INSERT语句

grid = -1;
EXEC SQL INSERT INTO SC VALUES(:sno, :cno, :gr, :grid);

(四)、使用游标的SQL语句

  • 查询结果为多条记录的SELECT语句

    • 游标在SELECT语句的集合和主语言的一次只能处理一条记录之间架起桥梁

    • 游标步骤:

      • 说明游标:EXEC SQL DECLARE <游标名> CURSOR FOR <SElECT 语句>

      • 打开游标:执行相应的查询,把结果放进缓冲区,并把指针指向第一条记录EXEC SQL OPEN<游标名>

      • 读取当前记录并推进游标指针EXEC SQL FETCH <游标名> INTO <主变量><指示变量>

      • 关闭游标:EXEC SQL CLOSE <游标名>

  • CURRENT 形式的UPDATE和DELETE语句

    • 操作步骤

    • 说明游标:EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 查询> FOR UPDATE OF<列名>

    • OPEN游标

    • FETCH游标

    • 检查是否要修改或删除

    • 处理完毕CLOSE游标

(五)、动态SQL语句

  • 在预编译时无法获得如下信息的必须使用动态SQL技术,未知信息可能包括:

    • SQL语句正文

    • 主变量个数

    • 主变量数据类型

    • SQL语句引用的数据对象

八、存储过程

  • 语法

create procedure [owned.]procedure_name as<SQL_statements>
  • 语言要素

    • 语句块

    begin <statement block> end
    • 变量

      • 以@开始的为用户变量,以@@开始的为全局变量。定义变量:declare

    • 条件控制

      • if else

    • 循环控制

      • while break continue

    • 顺序控制

      • label: goto label

    • 返回值

      • return

    • 打印信息

      • print

    • 执行

      • excute

  • 例子

CREATE PROCEDURE get_gr @sno varchar(10), @GR int OUTPUT 
AS
DECLARE @cno varchar(5)
BEGIN
   SELECT top 1 @cno=C#,@GR=GR FROM SC WHERE S# = @sno
   IF (@cno =’C01’)
      select @GR=@GR+1
   ELSE
     select @GR=@GR+2
END
执行:
    declare @gr int
    execute get_gr ‘s001’,@gr  output 
    select @gr 或print @gr