SQL笔记 --- 数据查询,数据更新,触发器

目录

数据查询
数据更新
触发器

数据查询

一般格式:

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

SELECT 实现:

在表中一列一列地与条件表达式进行判断若满足条件返回列,否则进行下一列

格式解析:

  • 目标表达式可选格式:
    • SELECT *
    • SELECT <表名>. *
    • SELECT COUNT([DISTINCT|ALL]*)
    • SELECT  [<表名>.]<属性列名表达式>[,[<表名>.]<属性列名表达式>] …
      • <属性列名表达式>可以为：
        • 属性列
        • 作用于属性列的聚集函数
        • '字符常量'
        • 或上述三种的任意算术运算（+,-,*,/）组成的运算公式

查询结果去重:

  • 形式:
    • SELECT [ALL|DISTINCT]
  • 说明:
    • ALL(默认) 查询结构不去重
    • DISTINCT 查询结果去重复

聚集函数:

  • 一般格式:

COUNT(列个数或元组个数)
SUM(列值总和)
AVG(列平均值)　　　　　　　+  ([DISTINCT|ALL] <列名>)
MAX(列中最大值)
MIN(列中最小值)
VARIANCE(列的标准方差)
STDDEV(列的标准差)

  • 说明:
    • 聚集函数都只返回一个数值

WHERE 子句的条件表达式可选格式:

  • 　　　　　　　　　　 <属性列名>
    <属性列名> 关系比价符　　　+　 <常量>
    　　　　　　　　　　　　　　　　 [ANY | ALL]<SELECT语句>
    关系比价符为 =,>,<,>=,<=,!=,<>,!>,!<;
  • 　　　　　　　　　　　　　　　　    <属性列名>　　          　　　　    <属性列名>
    <属性列名> [NOT] BETWEEN 　+　<常量>        　 +  AND 　+  　　<常量>
    　　　　　　　　　　　　　　　　    (SELECT语句)             　　　 　   (SELECT语句)
  • 　　　　　　　　　(<值1>)[,<值2>]........)
    <属性列名> [NOT] IN+(SELECT语句)
  • <属性列名> [NOT]LIKE <匹配串>
  • <属性列名> IS [NOT] NULL
  • [NOT] EXISTS(SELECT语句)
  • <条件表达式> AND / OR <条件表达式> [ AND / OR <条件表达式>]........

ORDER BY 子句:

  • 用途:
    • 可以按一个或多个属性列排序
  • 说明:
    • 升序：ASC;(缺省值)
    • 降序：DESC;
    • 当排序列含空值时:
      • ASC：排序列为空值的元组最后显示
      • DESC：排序列为空值的元组最先显示

GROUP BY 子句:

  • 功能:
    • 细化聚集函数的作用对象
  • 说明:
    • 未对查询结果分组,聚集函数将作用于整个查询结果
    • 对查询结果分组后,聚集函数将分别作用于每个组
    • HAVING 短语与 WHERE 子句的区别：
      • 作用对象不同
      • WHERE 子句作用于基表或视图,从中选择满足条件的元组
      • HAVING 短语作用于组,从中选择满足条件的组
  • 对象:
    • 是查询的中间结果表,按指定的一列或多列值分组,值相等的为一组
  • HAVING:
    • 语法格式:HAVING <条件表达式>
    • 作用:使用筛选 HAVING 短语指定筛选条件(条件表达式),筛选出符合条件的分组

常用的查询条件:

查询 　　　　　　　　　条件谓词　　　　　　　　　　　　　　　　　　　　　　　　
比 较　　　　　　　　　=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符
确定范围　　　　　　　 BETWEEN AND,NOT BETWEEN AND
确定集合　　　　　　　 IN,NOT IN
字符匹配 　　　　　　　LIKE,NOT LIKE
空值 　　　　　　　　　IS NULL,IS NOT NULL
多重条件(逻辑运算) 　　AND,OR,NOT

使用列别名显示查询结果的列标题:

例子:

查询语句:

SELECT Sname NAME,'Year of Birth:' BIRTH,2011-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;

 输出结果：

NAME BIRTH BIRTHDAY DEPARTMENT
------- ---------------- ------------- ------------------
李勇 Year of Birth: 1984 cs
刘晨 Year of Birth: 1985 is
王敏 Year of Birth: 1986 ma
张立 Year of Birth: 1985 is

谓词:

  • 确定范围:
    • 谓词:
      • BETWEEN …(范围下限) AND …(范围上限)
      • NOT BETWEEN …(范围下限) AND …(范围上限)
    • 作用:
      • 用来查询属性值在(或不在)指定范围的元组
    • 例子:
      • 查询年龄在 20~23 岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 
        SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
  • 确定集合:
    • 谓词:
      • IN <值表>
      • NOT IN <值表>
    • 作用:
      • 用来查询属性值属于指定集合的原码
    • 注意:
      • 所有带 IN 谓词、比较运算符、ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换
    • 例子:
      • 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别 
        SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'IS','MA','CS' );
      • 查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别 
        SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' );
  • 字符匹配:
    • 谓词:
      • [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
    • 注意:
      • 数据库字符集为 ASCII 时一个汉字需要两个 "_" ,当字符集为 GBK 时只需要 "_"
    • 说明:
      • 含义:
        • 查找指定的属性列值与<匹配串>相匹配的元组
      • <匹配串>:
        • 可以是完整的字符串,也可以含有通配符％(百分号)和_(下划线)
      • 通配符:
        • "%" ：代表任意长度（长度可以为0）的字符串.如a%b表示以a开头,以b结尾的任意长度的字符串
        • "_" ：代表任意单个字符.如a_b表示以a开头,以b结尾的长度为3的任意字符串
      • ESCAPE '<换码字符>':
        • 使用换码字符将通配符转义为普通字符
        • 如ESCAPE '\' 表示“ \” 为换码字符所以"\_"与"\%"表示的变为"_"与"%"字符而"_"与"%"不再是通配符
  • 存在:
    • 谓词:
      • EXISTS
      • NOT EXISTS
    • 说明:
      • EXISTS:
        • 带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值 “true” 或逻辑假值 “false”.
          • 若内层查询结果非空,则外层的 WHERE 子句返回真值
          • 若内层查询结果为空,则外层的 WHERE 子句返回假值
        • 由 EXISTS 引出的子查询,其目标列表达式通常都用* ,因为带 EXISTS 的子查询只返回真值或假值,给出列名无实际意义
      • NOT EXISTS:
        • 带有 NOT EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值 “true” 或逻辑假值 “false”.
          • 若内层查询结果非空,则外层的 WHERE 子句返回假值
          • 若内层查询结果为空,则外层的 WHERE 子句返回真值
        • 由 NOT EXISTS 引出的子查询,其目标列表达式通常都用* ,因为带 NOT EXISTS 的子查询只返回真值或假值,给出列名无实际意义
    • 注意:
      • 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换
    • 例子:
      • 查询没有选修1号课程的学生姓名. 
        SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
  • 谓词 ALL 与谓词 ANY :
    • 用途:
      • 子查询返回多值时,可使用谓词 ANY 或 ALL
    • 语义:
      • ANY：任意一个值
      • ALL：所有值
    • 配合使用比较运算符:
      • > ANY 大于子查询结果中的某个值
      • > ALL 大于子查询结果中的所有值
      • < ANY 小于子查询结果中的某个值
      • < ALL 小于子查询结果中的所有值
      • >= ANY 大于等于子查询结果中的某个值
      • >= ALL 大于等于子查询结果中的所有值
      • <= ANY 小于等于子查询结果中的某个值
      • <= ALL 小于等于子查询结果中的所有值
      • = ANY 等于子查询结果中的某个值
      • = ALL 等于子查询结果中的所有值（通常没有实际意义）
      • !=（或<>）ANY 不等于子查询结果中的某个值
      • !=（或<>）ALL 不等于子查询结果中的任何一个值
    • 比较:

  • 空值:
    • 谓词:
      • IS NULL为空置
      • IS NOT NULL 不为空置
    • 注意:
      • 涉及空值的查询时的谓词： IS NULL 或 IS NOT NULL中的“IS” 不能用 “=” 代替

连接查询:

  • 定义:同时涉及多个表的查询
  • 连接条件或连接谓词:
    • 定义:
      • 用来连接两个表的条件
    • 格式:
      • [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
      • [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
  • 连接字段:
    • 定义:连接谓词中的列名称
    • 说明:连接条件中的各连接字段类型必须是可比的,但名字不必是相同的
  • 等值连接:
    • 说明:连接运算符为=
    • 例子:查询每个学生及其选修课程的情况 
      SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;
  • 自然连接:
    • 说明:在等值连接中把目标列中重复的属性列去掉
  • 自身连接:
    • 说明:一个表与其自己进行连接
    • 要求:需要给表起别名以示区别,由于所有属性名都是同名属性,因此必须使用别名前缀
    • 例子:查询每一门课的间接先修课（即先修课的先修课） 
      SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
  • 复合条件连接:
    • 说明:WHERE子 句中含多个连接条件
    • 例子:查询选修2号课程且成绩在90分以上的所有学生的学号和姓名 
      SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno/* 连接谓词*/
AND SC.Cno='2'AND SC.Grade > 90;/* 其他限定条件 */
  • 外连接:
    • 谓词:
      • 表1 LEFT(RIGHT) OUT JOIN 表2 ON (连接谓词) [USING(要去的列名)]
    • 与普通连接的区别:
      • 普通连接操作只输出满足连接条件的元组
      • 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
    • 例子:(实现等值连接上面例子的等效功能) 
      SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);

嵌套查询:

  • 概述:
    • 一个 SELECT-FROM-WHERE 语句称为一个查询块,将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询
    • 上层查询快称为外层查询或父查询,下层查询块称为内层查询或子查询
  • 说明:
    • 允许多层嵌套,即子查询中还可嵌套其他子查询
    • 子查询的限制(不能使用 ORDER BY 子句)
    • 层层嵌套方式反映了 SQL 语言的结构化
    • 有些嵌套查询可以用连接运算替代
  • 不相关子查询:
    • 要求:
      • 子查询的查询条件不依赖于父查询
    • 实现:
      • 由里向外逐层处理.即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件
    • 例子:查询选修了课程 2 的所有学生姓名 
      SELECT Sname     /*外层查询/父查询*/
FROM Student
WHERE Sno IN（SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= '2'）;
  • 相关子查询:
    • 要求:
      • 子查询的查询条件依赖于父查询
    • 实现:
      • 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表,然后再取外层表的下一个元组,重复这一过程,直至外层表全部检查完为止
    • 例子:找出每个学生超过他选修课程平均成绩的课程号 
      SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

数据更新

数据插入:

  • 两种插入方式:

  • 插入元组:
    • 格式:
      • INSERT
        INTO <表名> [(<属性列1>[,<属性列2 >]…)]
        VALUES (<常量1> [,<常量2>] … )
    • 说明:
      • 如果 INTO 指指出表名,没有指出属性名,这表示新元组要在表的所有属性列删过都指定值,属性列的次序与 CREATE TABLE 中的次序相同
      • 如果 INTO 指指出表名,并指出属性名,属性列的顺序可与表定义中的顺序不一致,也可以指定部分属性列(未指明的必须的都是可以为空的属性)
      • VALUES 子句提供的值必须与INTO子句匹配(值的个数,值的类型)
    • 例子:将一个新学生元组（学号：200215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中 
      INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('200215128','陈冬','男','IS',18);
  • 插入子查询结果:
    • 格式:
      • INSERT
        INTO <表名> [(<属性列1>[,<属性列2 >]…)]
        子查询
    • 说明:
      • 可以一次插入多个元组
      • 如果 INTO 指指出表名,没有指出属性名,这表示子查询结果要在表的所有属性列删过都指定值,属性列的次序与 CREATE TABLE 中的次序相同
      • 如果 INTO 指指出表名,并指出属性名,属性列的顺序可与表定义中的顺序不一致,也可以指定部分属性列(未指明的必须的都是可以为空的属性)
      • 子查询结果提供的值必须与 INTO 子句匹配(值的个数,值的类型)
    • 例子: 
      INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;
  • 要求:
    • RDBMS 在执行插入语句时会检查所插元组是:
      • 否破坏表上已定义的完整性规则
        • 实体完整性
        • 参照完整性
        • 用户定义的完整性
      • NOT NULL 约束
      • UNIQUE 约束
      • 值域约束

数据更改:

  • 语句格式:
    • UPDATE <表名>
      SET <列名>=<表达式>[,<列名>=<表达式>]…
      [WHERE <条件>];
  • 格式功能:
    • 修改指定表中满足 WHERE 子句条件的元组
  • 格式说明:
    • SET 子句:
      • 指定修改方式
      • 要修改的列
      • 修改后取值
    • WHERE 子句:
      • 指定要修改的元组
      • 缺省表示要修改表中的所有元组
  • 三种修改方式:
    • 修改某一个元组的值
    • 修改多个元组的值
    • 带子查询的修改语句
  • 要求:
    • RDBMS 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则:
    • 实体完整性
    • 主码不允许修改
    • 用户定义的完整性
      • NOT NULL 约束
      • UNIQUE 约束
      • 值域约束
  • 例子:
    • 将学生 200215121 的年龄改为22岁(修改某一个元组的值) 
      UPDATE Student
SET Sage=22
WHERE Sno=' 200215121 ';
    • 将所有学生的年龄增加1岁(修改多个元组的值) 
      UPDATE Student
SET Sage= Sage+1;
    • 将计算机科学系全体学生的成绩置零(修改多个元组的值) 
      UPDATE SC
SET Grade=0
WHERE 'CS'= (SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno);

数据删除:

  • 语句格式:
    • DELETE
      FROM <表名>
      [WHERE <条件>];
  • 语句功能:
    • 删除指定表中满足 WHERE 子句条件的元组
  • 语句解析:
    • WHERE 子句:
      • 指定要删除的元组
      • 缺省表示要删除表中的全部元组,表的定义仍在字典中
  • 三种删除方式:
    • 删除某一个元组的值
    • 删除多个元组的值
    • 带子查询的删除语句
  • 例子:
    • 删除学号为 200215128 的学生记录(删除某一个元组的值) 
      DELETE
FROM Student
WHERE Sno='200215128';
    • 删除所有的学生选课记录(删除多个元组的值) 
      DELETE
FROM SC;
    • 删除计算机科学系所有学生的选课记录(带子查询的删除语句) 
      DELETE
FROM SC
WHERE 'CS'= (SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno);

触发器

解析:

  • 触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程
    • 由服务器自动激活
    • 可以进行更为复杂的检查和操作,具有更精细和更强大的数据控制能力

定义触发器:

  • 考虑事项:
    • 创建触发器的权限默认分配给表的所有者,且不能将该权限转给其他用户
    • 触发器为数据库对象,其名称必须遵循标识符的命名规则
    • 触发器可以引用当前数据库以外的对象,但只能在当前数据库中创建触发器
    • 虽然不能在临时表或系统表上创建触发器,但是触发器可以引用临时表
  • 语法格式:
    • CREATE TRIGGER <触发器名>
      {BEFORE | AFTER} <触发事件> ON <表名>
      FOR EACH {ROW | STATEMENT}
      ［WHEN <触发条件>］
      <触发动作体>
  • 语法说明:
    • 创建者:表的拥有者
    • 触发器名
    • 表名:触发器的目标表
    • 触发事件:　　INSERT、DELETE、UPDATE
    • 触发器类型:
      • 行级触发器（FOR EACH ROW）
      • 语句级触发器（FOR EACH STATEMENT）
      • 两者区别:
        • 例如,假设在［例11］的 TEACHER 表上创建了一个 AFTER UPDATE 触发器.如果表 TEACHER 有1000行,执行如下语句：
        • UPDATE TEACHER SET Deptno=5;
        • 如果该触发器为语句级触发器,那么执行完该语句后,触发动作只发生一次
        • 如果是行级触发器,触发动作将执行 1000次
    • 触发条件:触发器被激活时候,只有触发条件为真时候,触发动作体才执行,否则触发动作体不执行
    • 触发动作体:
      • 触发动作体可以是一个匿名 PL/SQL 过程块,也可以是对已创建存储过程的调用.
      • 如果触发体动作执行失败,激活触发器的事件就会终止执行,触发器的目标表或触发器可能影响的其他对象不发生任何变化
  • 例子:
    • 定义一个 BEFORE 行级触发器,为教师表 Teacher 定义完整性规则“教授的工资不得低于 4000元,如果低于 4000 元,自动改为 4000 元” 
      CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
/*触发事件是插入或更新操作*/
FOR EACH ROW /*行级触发器*/
AS BEGIN /*定义触发动作体,是PL/SQL过程块*/
IF (new.Job='教授') AND (new.Sal < 4000) THEN
new.Sal :=4000;
END IF;
END;

激活触发器:

  • 说明:
    • 触发器的执行,是由触发事件激活的,并由数据库服务器自动执行
    • 一个数据表上可能定义了多个触发器
    • 同一个表上的多个触发器激活时遵循如下的执行顺序：
      • 执行该表上的 BEFORE 触发器;
      • 激活触发器的 SQL 语句;
      • 执行该表上的 AFTER 触发器.

删除触发器:

  • 格式:
    • DROP TRIGGER <触发器名> ON <表名>;
  • 要求:
    • 触发器必须是一个已经创建的触发器,并且只能由具有相应权限的用户删除.
  • 例子:
    • 删除教师表 Teacher 上的触发器 Insert_Sal 
      DROP TRIGGER Insert_Sal ON Teacher;
posted @ 2012-07-14 13:11  kzangv  阅读(2166)  评论(0)  编辑  收藏  举报
top