备考软考数据库 9 SQL

SQL数据库的体系结构:

  1. 关系模式被称为基本表(base table)
  2. 存储模式被称为存储文件(stored file)
  3. 子模式被称为视图(view)
  4. 元组为行(row)
  5. 属性为列(column)

SQL的组成:

  1. 数据定义语言SQL DDL
  2. 数据操纵语言SQL DML
  3. 嵌入式SQL语言的使用规定
  4. 数据控制语言SQL DCL

SQL数据的定义:

  1. 数据类型:
    数值型:INTEGER,SMALLINT,REAL,DOUBLE PRECISION,FLOAT(n),NUMERIC(p,d)
    字符串型:CHAR(n),VARCHAR(n)
    位串型:BIT(n),BIT VARYING(n)
    时间型:DATE,TIME
    用户定义,例如:CREATE DOMAIN PERSON_NAME CHAR(8)
  2. SQL模式的创建:
    CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
  3. SQL模式的撤销:
    DROP SCHEMA <模式名> [CASCADE | RESTRICT]
  4. 基本表的创建:
    CREATE TABLE <基本表名>  ( <列名     类型>,......  <完整性约束>,  ...... )
  5. 基本表的修改:
    增添 ALTER TABLE <基本表名> ADD <列名> <类型>
    删除 ALTER TABLE <基本表名> DROP <列名> [CASCADE | RESTRICT]
    修改 ALTER TABLE <基本表名> MODIFY <列名> <类型>
  6. 基本表的撤销:
    DROP TABLE <基本表名> [CASCADE | RESTRICT]
  7. 索引的创建:
    CREATE [UNIQUE] INDEX <索引名> ON <基本表名> (<列名序列>)
  8. 索引的撤销:
    DROP INDEX <索引名>

SQL的数据查询:

  1. SELECT语句语法:
    SELECT [ ALL | DISTINCT ] <目标表的列名或列表达式序列>
    FROM <基本表名和/或视图序列>
    [WHERE <行条件表达式>]
    [GROUP BY <列名序列>
    [HAVING <组条件表达式]]
    [ORDER BY <列名 [ASC | DESC]> , ... ]
    其中[]中的部分可有可无
  2. WHERE子句的条件表达式中可用如下运算符:
    算术比较运算符:<,<=,>,>=,=,<>,!=,BETWEEN AND
    逻辑运算符:AND,OR,NOT
    字符串:[NOT] LIKE
    空值判断:IS [NOT] NULL
    集合成员资格运算符:[NOT] IN
    谓词:[NOT] EXISTS,ALL,SOME,UNIQUE,ANY
    聚合函数:AVG,MIN,MAX,SUM,COUNT
    选择子句中还可以包含另一个SELECT子句
  3. 查询语句中的改名操作:
    一个基本表在SELECT语句的FROM子句中被多次调用,为区别不同的引用,应给每次引用标上不同的名字,把基本表定义为一个元组变量。
    SELECT 旧名 AS 新名
  4. 集合的并、交、差操作:
    当两个子查询结果的结构一致时,可以进行这三种操作:
    (SELECT语句1)UNION/INTERSECT/EXCEPT [ALL](SELECT语句2)
    如果不带ALL字,可消除重复元组

嵌套查询的改写:

  1. 使用导出表:
    例如:(SELECT AVG(SCORE) FROM SC GROUP BY S#) AS RESULT (AVG_SCORE)
    在之下面的语句中可以用RESULT来指该导出表
  2. 使用WITH子句和临时视图:
    例如:WITH RESULT (AVG_SCORE) AS SELECT AVG(SCORE) FROM SC GROUP BY S#
    在之下面的语句中可以用RESULT来指该临时视图

基本表的联接操作:

  • 联接类型:
  1. 内联接 INNER JOIN
  2. 左外联接 LEFT OUTER JOIN
  3. 右外联接 RIGHT OUTER JOIN
  4. 完全外联接 FULL OUTER JOIN
  • 联接条件:
  1. NATURAL (JOIN) 表明是自然联接,在公共属性上的等值联接
  2. JOIN ON等值联接条件
  3. JOIN USING(A_1,A_2,dots,A_k)

递归查询:

  • 关系模式:COURSE(C#,CNAME, PC#)属性分别为:课程号、课程名称,先修课程号
  • 查询C4课程的所有先修课程号:
    WITH RECURSIVE PRE (C#, PC# ) AS
    ((SELECT C#, PC# FROM COURSE)
    UNION
    (SELECT COURSE.C#, PRE.PC#
    FROM COURSE, PRE
    WHERE COURSE.PC#=PRE.C# ))
    SELECT PC# FROM PRE WHERE C# = 'C4'

SQL的数据更新:

  1. 单元组的插入:
    INSERT INTO <基本表名> [(<列名序列>)]
    VALUES (<元组值>)
  2. 多元组的插入:
    INSERT INTO <基本表名> [(<列名序列>)]
    VALUES (<元组值>),(<元组值>), ... , (<元组值>)
  3. 查询结果的插入:
    INSERT INTO <基本表名> [(<列名序列>)]
    <SELECT 查询语句>
  4. 表的插入:
    INSERT INTO <基本表格1> [(<列名序列>)]
    TABLE <基本表名2>
  5. 数据的删除:
    DELETE FROM <基本表名>
    [WHERE <条件表达式>]
  6. 数据的修改:
    UPDATE <基本表名>
    SET <列名> = <值表达式> [, <列名>=<值表达式>...]
    [WHERE <条件表达式>]

视图:

  1. 视图的创建:
    CREATE VIEW <视图名> (<列表序列>)
    AS  <SELECT 查询语句>
  2. 视图的撤销:
    DROP VIEW <视图名>

SQL对事务的支持

  1. 事务的存取模式:
    只读型:SET  TRANSACTION  READ  ONLY
    读写型:SET  TRANSACTION  READ  WRITE
  2. 事务隔离级别:
    可串行化:允许事务与其他事务并发执行,但是系统必须保证并发调度是可串行化的    等于X锁
    SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE
    可重复读:只允许事务读已提交的数据,并且在两次读同一数据时不允许其他事务修改此数据   等于X锁
    SET  TRANSACTION  ISOLATION  LEVEL  REPEATABLE  READ
    读提交数据:允许事务读已提交的数据,但不要求“可重复读”   等于S锁
    SET  TRANSACTION  ISOLATION  LEVEL  READ  COMMITTED
    可以读未提交数据:允许事务读已提交或未提交的数据   等于无锁
    SET  TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED

SQL完整性约束:

  1. 域约束:
    例如:
    CREATE  DOMAIN  COLOR  CHAR(6)  DEFAULT  'BLACK'
    CONSTRAINT  VALID_COLORS
    CHECK  (VALUE  IN ('RED','BLACK','BLUE'));
  2. 基本表约束:
    候选键定义、主键定义:
    UNIQUE  ((列名序列))
    PRIMARY  KEY  ((列名序列))
    外键定义:
    FOREIGN  KEY  ((列名序列))
    REFERENCE  <参照表>  [(列名序列)]
    [ON DELETE <参照动作>]
    [ON UPDATE <参照动作>]
    参照动作包括:NO ACTION, CASCADE, RESTRICT, SET NULL, SET DEFAULT
    检查约束:
    CHECK  (<条件表达式>)
  3. 断言:
    CHECK  ASSERTION  <断言名>  CHECK  (<条件>)
    DROP  ASSERTION  <断言名>
  4. 触发器:
    触发器的构成ECA:
    事件:对数据库的插入、删除、修改等操作
    条件:如果条件成立则执行相应动作
    动作规则:条件成立时执行的数据库操作
    例如,要求修改SC中成绩时,修改后的成绩一定不能比原来的低,否则就拒绝修改:
    CREATE  TRIGGER  TRIG1
    AFTER  UPDATE  OF  GRADE  ON  SC
    REFERENCING
    OLD  AS  OLDTUPLE
    NEW  AS  NEWTUPLE
    FOR  EACH  ROW
    WHEN  (OLDTUPLE.GRADE  >  NEWTUPLE.GRADE)
    UPDATE  SC
    SET  GRADE  =  OLDTUPLE>GRADE
    WHERE  C#  =  NEWTUPLE.C#

SQL用户权限:

六类用户使用权限:SELECT,INSERT,DELETE,UPDATE,REFERENCES,USAGE

授权语句:GRANT  <权限表>  ON  <数据库元素>  TO  <用户名表>  [ WITH  GRANT  OPTION ]

回收权限语句:REVOKE  <权限表>  ON  <数据库元素>  FROM  <用户名表>  [ RESTRICT  |  CASCADE ]

回收转让权限:REVOKE  GRANT  OPTION  FOR  <权限表>  ON  <数据库元素>  FROM  <用户名表>

Leave a Reply

Your email address will not be published. Required fields are marked *