第3章 关系数据库语言SQL

第3章 关系数据库语言SQL

目录

• 1 SQL概述

• 2 数据定义

  • 2.1 表的定义、修改与删除

  • 2.2 索引的建立与删除

• 3 数据查询

  • 3.1 基本格式

  • 3.2 单表查询

  • 3.3 多表查询

  • 3.4 集合查询

  • 3.5 嵌套查询

• 4 数据更新

  • 4.1 插入数据

  • 4.2 修改数据

  • 4.3 删除数据

• 5 视图

• 6 数据控制

1 SQL概述

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

• 标准：现在是SQL4

• 组成：

  • 操作对象为表、视图（特殊的表）

  • 操作分类：数据定义（DDL）、数据操作（DML）、数据控制（DCL）、嵌入式（SQL）

    • 定义：CREATE，DROP，ALTER

    • 查询：SELECT

    • 操纵：INSERT，UPDATE，DELETE

    • 控制：GRANT，REVOKE

• 特点：

  • 综合统一

  • 高度非过程化，面向集合的操作方式

  • 以同一种语法结构提供两种使用方法

  • 语言简洁，易学易用

2 数据定义

2.1 表的定义、修改与删除

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

  • 基本数据类型：

    1. char(n)：字符型，长度为n

    2. varchar(n)：

    3. int/integer[4B]：

    4. tinyint[1B]：

    5. smallint[2B]：

    6. Numeric(p,q)：[定点数，总长度p,小数后为p]

    7. Real：浮点数，4B

    8. Date：日期型

    9. Time：时间型

  • 完整性约束：

    • 定义主键：PRIMARY KEY(<属性列列表>)

      PRIMARY KEY ( Sno, Cno)

    • 定义外键：FOREIGE KEY(<引用列列表>) PEFERENCES<被引用的表>(被引用的列)
      ON DELETE <删除时动作>
      ON UPDATE<更新时动作>

      • 其中删除更新时的动作包括：

        • CASCADE：级联处理

        • NO ACTION：拒绝(缺省)

        • SET NULL：置空

        • SET DEFAULT：置为缺省值

      

    • 列级约束：

      • NOT NULL : Ssex CHAR (2) NOT NULL

      • UNIQUE: Ssex CHAR (2) UNIQUE

      • DEFAULT: Ssex CHAR (2) DEFAULT ‘男’

      • CHECK: Ssex CHAR (2) CHECK( Ssex IN ( ‘男’ , ‘女’ ) )

    • 举例

      

      

• 修改表

  ALTER TABLE

  ADD Birthday date

• 删除表

  DROP TABLE Table_Name

2.2 索引的建立与删除

建立索引，加快查询速度

• 建立索引

  

• 删除索引

  DROP INDEX <表名.索引名> ;

  DROP INDEX S.S_Index, SC.SC_Index ;

3 数据查询

3.1 基本格式

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

3.2 单表查询

• 选中列

  • 选择指定列

    SELECT Sno, Sname FROM S ;

  • 选择全部列

    SELECT * FROM S;

  • 查询经过计算的列

    SELECT Sname AS 姓名, 2017-Sage,LOWER(Sdept) FROM S ;

    包括算术表达式，字符串常量，函数，列别名

• 选中元组

  • 消除重复行(只需要一个DISTINCT)

    SELECT DISTINCT Sno FROM SC ;

    SELECT DISTINCT Cno, Score FROM SC

  • 查询满足条件的元组

    采用WHERE子句查询

    • 比较：=, <, <=, >, >=, !=(<>) , !<, !>

    • 确定范围：BETWEEN AND, NOT BETWEEN AND

    • 确定集合：IN , NOT IN

    • 字符串匹配：LIKE,NOT LIKE

      • %代表任意字符串

        a%b:ab,adb,asdajoldsijld……

      • _代表任意字符

        a_b:acb,asd……

    • 空值：IS NULL,IS NOT NULL

    • 多重条件：AND OR

    • 举例

      SELECT Sname, Sage
      FROM S
      WHERE Sage < 20 ;

      SELECT Sname, Sno, Ssex
      FROM S
      WHERE Sname LIKE '刘%’ ;

• 对查询结果排序

  使用ORDER BY进行拍讯，其中ASC为升序（缺省值），DESC为降序

  举例：

  SELECT * FROM S
  WHERE Sage < 23 AND Ssex=‘男’
  ORDER BY Sage ASC ;

• 使用聚集函数

  • COUNT\SUM\AVG\MAX\MIN

  • 举例

    SELECT COUNT(DISTINCT Sno) FROM SC

    SELECT SUM(Score) FROM SC WHERE Sno= ‘S1’ ;

• 对查询结果分组

  • 将查询结果按照某一列或多列分组，值相等的为一组

  • 分组查询举例

    SELECT Cno, COUNT(*) AS 人数
    FROM SC
    GROUP BY Cno ;（按照Cno分组，将计数作为人数，建立一个新表）

    

    在SELECT子句中只能出现分组属性和聚集函数

3.3 多表查询

• 笛卡尔积查询

  SELECT * FROM S ,SC

• 等值连接查询

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

• 复合条件查询 条件里面家 AND

• 自身连接查询（先将一个自身命名为别的）

  SELECT A.Sno, A.Sname, A.Sdept
  FROM S A, S B
  WHERE A.Sdept = B.Sdept AND B.Sname = ‘刘晨’ ;

3.4 集合查询

• 并操作UNION

• 交操作 INTERSECT

• 差操作 EXCEPT

3.5 嵌套查询

• 带有IN的子查询（父查询和子查询均可定义别名）

  

• SOME AND

  

• EXISTS

  SELECT Sname
  FROM S x
  WHERE EXISTS
  (SELECT * FROM SC WHERE Sno= x.Sno AND Cno=‘C1’);

嵌套查询是先执行父查询，然后对父查询每个元组执行一遍子查询

4 数据更新

4.1 插入数据

• 插入单个元组

  INSERT INTO C VALUES ( ‘C5’, ‘OS’, 40 ) ;(要对应，而且知道各是什么)

  INSERT INTO SC (Sno, Cno) VALUES ( ‘090939’ , ‘C1’ );(要对应,其他列取空值)

• 插入子查询结果

  INSERT INTO SC(Sno, Cno)
  SELECT Sno, ‘C1’
  FROM S ;

4.2 修改数据

• 修改某一个元组的值

  UPDATE S
  SET Sage = 22
  WHERE Sno = ‘090905’ ;

• 修改多个元组的值

  UPDATE S
  SET Sage = Sage+1 ;

• 带子查询的修改语句

  

4.3 删除数据

• 删除某一个元组

  DELETE FROM S
  WHERE Sno = ‘090950’ ;

• 删除多个元组

• 删除子查询

5 视图

视图是从一个或多个表（或视图）导出的表，又称虚表

CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM S
WHERE Sdept = ‘IS’ ;

CREATE VIEW H( A,B,C,D,E )
AS SELECT A, B, R.C, D, E
FROM R, S
WHERE R.C=S.C ;

6 数据控制