第三章 关系数据库语言SQL(第一二三四节).ppt

2020 1 5 1 3 1SQL特征3 2数据定义3 3数据查询3 4数据更新3 5数据视图 2020 1 5 2 3 1SQL特征 SQL StructuredQueryLanguage 的字面意思是结构化的查询语言 但它的功能并不仅仅是查询 它是关系数据库管理系统的标准语言 3 1 1SQL的主要功能SQL语言按照功能可以分为3大类 DDL DataDefinitionLanguage 数据定义语言用于定义关系数据库的模式 外模式和内模式 以实现对数据库基本表 视图及索引文件的定义 修改和删除等操作 最常用的DDL语句是CREATE DROP和ALTER命令 DML DataManipulationLanguage 数据操纵语言用于完成数据查询和数据更新操作 其中数据更新指对数据进行插入 删除和修改操作 最常使用的DML语句是SELECT INSERT UPDATE和DELETE命令 2020 1 5 3 DCL DataControlLanguage 数据控制语言用于控制对数据库的访问 服务器的关闭 启动等操作 常使用的DCL命令有 GRANT REVOKE等 3 1 2SQL的特点语言简洁 风格统一 易学易懂SQL语言接近英语 只用几个英文单词的组合就能完成所有功能 因此初学者非常容易上手 既是自含式语言 又是嵌入式语言作为自含式语言 SQL能够独立地用于联机交互的使用方式 当作为嵌入式语言使用时 SQL语句几乎可以不加修改地嵌入到如VB PB这样的前端开发平台上 利用前端工具的计算能力和SQL的数据库操纵能力 可以快速地建立数据库应用程序 高度非过程化用SQL语言进行数据操作 只要提出 做什么 而无须知道 怎么做 SQL语句的实现过程由系统自动完成 2020 1 5 4 3 1 3SQL对象命名约定SQL对象包括数据库 表 视图 属性名等 这些对象名必须符合一定规则或约定 一般应遵守下列规则 数据对象名可以为1 30个字符 在MSAccess为64个字符 但有些DBMS限制为8个字符 例如 Oracle数据库就是如此 数据对象名应以字母开头 其余字符可以由字母 数字 下划线组成 3 1 4SQL语句结构和书写准则在SQL语句语法格式中的一些约定符号 尖括号 中的内容为实际语义 中括号 中的内容为任选项 意思是 等等 即前面的项可以重复 2020 1 5 5 大括号 与竖线 表明此处为选择项 在所列出的各项中仅需选择一项 例如 A B C D 意思是A B C D中取其一 SQL中的数据项 包括列项 表和视图 分隔符为 其字符串常数的定界符用单引号 表示 在编写SQL语句时 遵从某种准则以提高语句的可读性 使其易于编辑 是很有好处的 以下是一些通常的准则 SQL语句对大小写不敏感为了提高SQL语句的可读性 子句开头的关键字通常采用大写形式 SQL语句可写成一行或多行 习惯上每个子句占用一行 关键字不能在行与行之间分开 并且很少采用缩写形式 SQL语句的结束符为分号 分号必须放在语句中的最后一个子句后面 但可以不在同一行 2020 1 5 6 3 2数据定义 SQL语言的数据定义功能包括定义数据库 定义基本表 定义索引和定义视图 其基本语句如表3 1所示 表3 1SQL的数据定义语句 2020 1 5 7 基本表是独立存储在数据库中的表在SQL中一个关系对应一个基本表 一个 或多个 基本表对应一个存储文件 基本表对应的数据必须在数据库中存放 存储文件的物理结构对用户而言是透明的 用户无需关心 一个基本表可以根据需要带一个或多个索引 索引也存放在存储文件中 视图是由一个或几个基本表导出的 它的外部形式也是一个表 是由基本表中选取的行和列组成的视图本身不独立 它依附于基本表 在数据库中不存放视图对应的数据 而只存放关于视图的定义 因此视图只是一个虚表 2020 1 5 8 3 2 1数据库的创建与删除创建数据库SQL使用命令CREATEDATABASE创建数据库 其一般语法如下 CREATEDATABASE 例3 1 创建一个简单数据库 CREATEDATABASEMyDb 删除数据库SQL使用命令DROPDATABASE删除一个或多个数据库 其一般语法如下 DROPDATABASE 例3 2 删除数据库MyDb DROPDATABASEMyDb 2020 1 5 9 3 2 2表的创建与删除创建基本表创建基本表的结构是建立数据库最重要的一步 其一般语法如下 CREATETABLE 说明 是指要创建的基本表的名称 该名称应符合具体DBMS的标识符的命名规则 指的是表的属性名称 2020 1 5 10 定义表的各个属性时应指出其相应的数据类型和长度 不同DBMS支持的数据类型不完全相同 表3 2列出的是IBMDB2SQL支持的主要数据类型 2020 1 5 11 关于完整性约束条件 需要说明以下几点 完整性约束条件分为列级完整性约束条件和表级完整性约束条件 它们之间的区别在于 列级完整性约束条件只能用于列 而表级完整性约束条件能够用于一张表中的多列 SQL的完整性约束条件有以下几种 NOTNULL或NULL约束这个约束条件为列级完整性约束条件 NOTNULL为不允许该列存在空值 而NULL为允许该列存在空值 UNIQUE约束UNIQUE约束是惟一性约束 即不允许表中的某一列或者某几列有重复的属性值 DEFAULT约束DEFAULT约束为默认值约束 是列级完整性约束条件 当向表中插入一个新行时 如果对于特定列没有指定数值 则使用DEFAULT子句指定的默认值 2020 1 5 12 CHECK约束CHECK约束是检验约束 为插入列中的数据指定约束条件 PRIMARYKEY约束PRIMARYKEY约束即主键约束 是表级完整性约束条件 表中的主键可以是一列或列组 PRIMARYKEY约束可以使得主键的数值在每一行中各不相同 注意 PRIMARYKEY约束类似于UNIQUE约束 差别在于PRIMARYKEY约束中的列不可以为空 FOREIGNKEY约束FOREIGNKEY约束是参照完整性约束 是用于约束外键的 也是表级完整性约束条件 2020 1 5 13 例3 3 创建 学生表 student 它由学号id 姓名name 性别sex 班级号class 出生日期birthday等5个属性组成 其中学号不能为空 值必须惟一 且姓名也必须惟一和非空 CREATETABLEstudent idCHAR 8 NOTNULLUNIQUE nameVARCHAR 20 NOTNULLUNIQUE sexCHAR 1 DEFAULT 男 NOTNULL classCHAR 4 birthdayDATE sumintconstraintc2checksum 2000 CONSTRAINTC1CHECK sexIN 男 女 2020 1 5 14 例3 4 创建 班级信息表 class 它由班级号id 班级名name 班长monitor等3个属性组成 其中班级号是主键 班长是外键 它是 例3 3 学生表中学号的某个值 CREATETABLEclass idCHAR 4 NOTNULL nameVARCHAR 50 NOTNULL monitorCHAR 8 CONSTRAINTC2PRIMARYKEY id CONSTRAINTC3FOREIGNKEY monitor REFERENCESstudent id 说明 本例定义了2个列级约束 2个表级约束 CONSTRAINT子句定义的是表级约束 C2 C3是约束名 分别将id定义为主键 monitor定义为外键 2020 1 5 15 删除基本表当某个基本表不再需要时 可以使用DROPTABLE语句将它删除 其一般语法为 DROPTABLE 例3 5 删除student表 DROPTABLEstudent 该语句一旦执行 基本表的定义 数据 此表上建立的索引和视图都将自动被删除掉 2020 1 5 16 3 2 3表结构的修改SQL语言用ALTERTABLE命令修改基本表 其基本语法为 ALTERTABLE ADD 完整性约束 DROP MODIFY 基本表的修改分3种情况 使用ADD子句增加新列和新的完整性约束条件 例3 6 在student表中增加 籍贯native place 列 数据类型为字符型 ALTERTABLEstudentADDnative placeVARCHAR 50 1 1 2020 1 5 17 使用DROP子句删除指定的完整性约束条件 例3 7 删除student表学生姓名必须取惟一值的约束条件 ALTERTABLEstudentDROPUNIQUE name 使用MODIFY子句修改基本表的列定义 例3 8 将student表name列的数据类型改为定长字符串型 ALTERTABLEstudentMODIFYnamechar 8 NOTNULL 注意 1 修改列定义时 要将原来的列级约束条件写上 否则原有的列级约束会不起作用 2 修改列定义时 有可能会破坏已有的数据 应事先作好备份工作 3 SQL未提供删除属性列的语句 只能采取间接的方法 2020 1 5 18 3 2 4建立索引索引的概念索引是建立在列上的一种数据库对象 它对表中的数据提供逻辑顺序 当在数据库表中搜索某一行时 可以通过使用索引来找到它的物理位置 索引建立后 什么时候使用索引以及使用哪一个索引 当有多个索引存在时 由DBMS内部根据情况自行决定 不需要人员干预 索引是动态的 每当数据库表的数据更新一次 相应的索引也随之更新 2020 1 5 19 建立索引在SQL语言中 建立索引使用CREATEINDEX命令 其一般语法为 CREATE UNIQUE CLUSTER INDEXON 说明 1 表名是要建立索引的基本表的名字 2 列名是被建立索引的列的名称 索引可以建立在某一列或多个列上 3 次序是指按照该列名的索引值的排列顺序 次序可以取值ASC 升序 或DESC 降序 默认值是ASC 4 UNIQUE表示创建的索引是惟一索引 索引列上的数据不能有重复值 5 CLUSTER表示要建立的是聚簇索引 聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引 2020 1 5 20 例3 9 为学生表student和班级信息表class建立索引 其中 student表按学号id升序建立惟一索引 class表按班级号降序建立惟一索引 CREATEUNIQUEINDEXstuid indONstudent idASC CREATEUNIQUEINDEXclaid indONclass idDESC 例3 10 为表student在姓名name列上建立一个聚簇索引 CREATECLUSTERINDEXstu cluindONstudent name 该语句执行后 student表中记录的物理顺序将按照name列值的升序存放 一个基本表只能建立一个聚簇索引 因为表中的数据不能以多种方式进行物理排序 可以在经常查询的列上建立聚簇索引 以提高查询效率 但对于经常更新的列则不宜建立聚簇索引 因为建立聚簇索引后 更新索引列数据时 要更新表中记录的物理顺序 开销较大 2020 1 5 21 删除索引在SQL语言中 使用DROPINDEX命令删除索引 其语法如下 DROPINDEX 例3 11 删除为student表建立的索引stu cluind DROPINDEXstu cluind 删除索引时 系统会同时从数据库中删去有关该索引的描述 对于数据库系统而言 索引一经建立 一般不应随意删除 SQL没有提供修改索引的语句 对于一些在使用中证明不合适的索引 只能先删除后重建 2020 1 5 22 3 3数据查询 3 3 1SELECT语句的结构SQL语言提供的SELECT语句的一般格式如下 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 说明 SELECT语句的含义是 根据WHERE子句指定的条件 从FROM子句后面的基本表或视图中找出满足条件的记录 再按照SELECT子句指定的目标列表达式 选出这些记录相应的列形成结果集返回 其中 SELECT子句和FROM子句是必选的 而WHERE子句 GROUPBY子句 HAVING子句以及ORDERBY子句都是可选的 2020 1 5 23 3 3 2选择行和列 选择和投影运算的实现下面以人事工资管理系统的员工表employee和部门表dept为例介绍SELECT语句的使用方法 Employee emp id ename job mgr id workdate sal comm dept id 员工号姓名职位主管参加工作时间月薪岗位津贴部门号Dept dept id dname tel 部门号 部门名称 电话用下面语句创建表结构 CREATETABLEemployee emp idCHAR 4 PRIMARYKEY enameVARCHAR 20 jobVARCHAR 9 mgr idCHAR 4 workdateDATE salSMALLINT commSMALLINT dept idCHAR 2 NOTNULLFOREIGNKEY dept id REFERENCESDept dept id FOREIGNKEY mgr id REFERENCESemployee emp id 2020 1 5 24 CREATETABLEdept dept idCHAR 2 PRIMARYKEY dnameVARCHAR 12 telVARCHAR 20 选择运算的实现查询一个表的所有行在SELECT语句中只要没有WHERE子句 查询结果就包含了所有行 例3 12 查询表employee的所有员工的员工号和姓名 SELECTemp id enameFROMemployee 查询满足条件的某些行查询满足条件的某些行 可以通过WHERE子句来实现 例3 13 查询月薪超过1800的员工姓名和月薪 SELECTename salFROMemployeeWHEREsal 1800 2020 1 5 25 例3 14 查询员工号为1001的员工姓名及部门号 SELECTename dept idFROMemployeeWHEREemp id 1001 投影运算的实现查询表的全部列 例3 15 查询部门表中部门号为11的全部内容 SELECTdept id dname telFROMdeptWHEREdept id 11 在SQL语言中 可以用星号 代表所有列名 列的显示顺序与基本表中列的顺序一致 例3 16 下面语句的结果等价于 例3 15 的语句 SELECT FROMdeptWHEREdept id 11 2020 1 5 26 查询表的部分列 例3 17 查询全体员工的员工号 姓名和参加工作时间 SELECTemp id ename workdateFROMemployee 查询经过计算的值SELECT子句的不仅可以是基本表的属性 也可以是表达式 包括算术表达式 字符串常量和函数等 例3 18 查询全体员工的姓名及年薪 SELECTename sal 12FROMemployee 结果 enamesal 12吴伟15600岳玲13200王斌18000徐欢9600 2020 1 5 27 例3 19 上例查询如果在中使用字符串常量 结果会更清晰 SELECTename 年薪 sal 12FROMemployee 查询结果如下 ename年薪 sal 12吴伟年薪 15600岳玲年薪 13200王斌年薪 18000徐欢年薪 9600 2020 1 5 28 例3 20 对 例3 18 查询可以通过指定别名来改变查询结果的列标题 这样也可以使结果更清晰 SELECTenameAS 姓名 sal 12AS 年薪 FROMemployee 查询结果如下 姓名年薪吴伟15600岳玲13200王斌18000徐欢9600 2020 1 5 29 3 3 3SQL的运算符SQL语言使用的运算符包括算术运算符 比较运算符 逻辑运算符等 算术运算符算术运算符有4种 比较运算符基本比较运算符共9种 或 不大于 不小于 特殊比较运算符有4类 BETWEEN AND IN LIKE和ISNULL 2020 1 5 30 运算符BETWEEN AND 运算符BETWEEN AND 是用于确定记录的范围 即某属性值在指定的范围之内 包括边界值 的记录 其中BETWEEN后面是下限 AND后面是上限 例3 21 查询月薪在1000到1500之间的员工姓名及月薪 SELECTename salFROMemployeeWHEREsalBETWEEN1000and1500 查询结果包括那些月薪等于1000和月薪等于1500的记录 运算符IN运算符IN是用于查找某属性值包含在指定集合内的记录 IN后面跟的是指定集合 例3 22 查找部门号属于11和21的员工姓名及部门号 SELECTename dept idFROMemployeeWHEREdept idIN 11 21 2020 1 5 31 运算符LIKE运算符LIKE可用来进行字符串的匹配 LIKE后面跟的是匹配模式 匹配模式可以是一个包含通配符 百分号 和 下划线 的字符串 代表任意长度 长度可以为0 的字符串 代表任意单个字符 例3 23 查询所有姓张员工的姓名 SELECTenameFROMemployeeWHEREenameLIKE 张 例3 24 查找所有1990年参加工作的员工姓名及参加工作时间 SELECTename workdateFROMemployeeWHEREworkdateLIKE 1990 例3 25 查找姓名第2个字为 小 的员工姓名 SELECTenameFROMemployeeWHEREenameLIKE 小 2020 1 5 32 运算符ISNULL运算符ISNULL用来测试某个属性值是否为空 例3 27 在employee表中 职位是总经理的员工在属性mgr id这栏应该不填 因为他没有上司 因此查询总经理的姓名的语句应为 SELECTenameFROMemployeeWHEREmgr idISNULL 例3 28 可能有这样的情况 新进员工暂时没有岗位津贴 等试用期满后再领岗位津贴 查询没有岗位津贴的员工姓名 SELECTenameFROMemployeeWHEREcommISNULL 2020 1 5 33 逻辑运算符SQL语言的逻辑运算符有3种 NOT AND OR 例3 29 查询月薪超过1500的部门主管的姓名及月薪 SELECTename salFROMemployeeWHEREsal 1500ANDjob 部门主管 例3 30 查询月薪超过1500的员工和所有部门主管的姓名及月薪 SELECTename salFROMemployeeWHEREsal 1500ORjob 部门主管 2020 1 5 34 例3 31 查询领有岗位津贴的员工姓名 SELECTenameFROMemployeeWHEREcommISNOTNULL 例3 32 查找部门号不属于11和21的员工姓名及部门号 SELECTename dept idFROMemployeeWHEREdept idNOTIN 11 21 2020 1 5 35 3 3 4对查询结果排序SQL语言中用ORDERBY子句实现对查询结果的排序 可以根据包含的一列或者多列的表达式进行ASC 升序 或DESC 降序 的排列 默认值是ASC 例3 33 查询所有员工的姓名及月薪 结果按月薪的降序排列 SELECTename salFROMemployeeORDERBYsalDESC ORDERBY子句指定的排序列可以不只一个 2020 1 5 36 例3 34 查询所有员工的姓名 部门号及月薪 结果按部门号升序排列 同一部门按月薪降序排列 SELECTename dept id salFROMemployeeORDERBYdept id salDESC 说明 上例中dept id称为主排序关键字 sal成为次排序关键字 注意 1 ORDERBY子句不改变基本表中行或列的顺序 只改变查询显示的顺序 2 ORDERBY子句指定排序的列必须出现在SELECT子句的列表达式中 3 排序是查询语句的最后一步工作 所以ORDERBY子句一般放在查询语句的最后 2020 1 5 37 3 3 5消除重复行基本表中不相同的行 经过对某些指定列进行投影运算后 可能会变成完全相同的行 显示结果不直观 这时需要用DISTINCT选项消除重复的行 例3 35 查询表employee中的所有职位 SELECTDISTINCTjobFROMemployee 注意 在一个SELECT语句中DISTINCT只能出现一次 并且DISTINCT必须在所有列名之前 否则会发生语法错误 与DISTINCT选项含义相反的是ALL选项 在SELECT语句中使用ALL选项 表示结果重复的行也将显示 ALL选项是默认选项 2020 1 5 38 3 3 6SQL的统计函数SQL语言提供了许多统计函数 主要的统计函数见表3 3 2020 1 5 39 例3 36 统计员工总人数 SELECTCOUNT FROMemployee 也可以写成 SELECTCOUNT emp id FROMemployee 例3 37 统计部门号 11 的部门领取岗位津贴的人数 SELECTCOUNT comm FROMemployeeWHEREdept id 11 这里统计的是属性列comm不为空值的行数 2020 1 5 40 例3 38 统计部门号 11 的部门全体员工人数 SELECTCOUNT FROMemployeeWHEREdept id 11 例3 39 查询最早参加工作时间和最晚参加工作时间 SELECTMIN workdate MAX workdate FROMemployee 例3 39 统计所有员工的岗位津贴总数及平均岗位津贴 SELECTSUM comm AVG comm FROMemployee 注意 除COUNT 外 所有的统计函数都不包括取值为空值的行 2020 1 5 41 3 3 7数据分组利用GROUPBY子句可以将查询结果按照一列或者多列分组 值相等的为一组 基于单列的分组 例3 40 按部门号查询各部门的平均月薪 SELECTdept id AVG sal FROMemployeeGROUPBYdept id 基于多列的分组GROUPBY子句还可以作用于多列上 此时的数据分组意义是分大组之后再分小组 例3 41 分各部门各职位统计月薪总额 SELECTdept id job sum sal FROMemployeeGROUPBYdept id job 说明 先按照部门号分组 部门号相同的组再按职位细分 最后部门号和职位完全相同的行才会分在一组 然后每组统计一个月薪总额 2020 1 5 42 HAVING子句如果分组后还要根据一定条件对这些组进行筛选 则使用HAVING子句来实现 例3 42 按部门号查询各部门的平均月薪 要求只显示平均月薪在1000以上的部门编号和平均月薪 SELECTdept id AVG sal FROMemployeeGROUPBYdept idHAVINGAVG sal 1000 注意 WHERE子句和HAVING子句有相似之处 即后面都跟指定条件 但是它们又有区别 前者直接用于SELECT子句中 作用于基本表或视图 而后者一定跟在GROUPBY子句后面 作用于分组 2020 1 5 43 3 3 8连接查询连接查询的概念如果一个查询需要从两个或两个以上的数据表中获取数据时 则称之为连接查询 连接查询包括广义笛卡尔积 等值连接 自然连接 外连接 内连接 左连接 右连接和自连接等 广义笛卡尔积广义笛卡尔积是不带连接条件的连接操作 两个表的广义笛卡尔积即是两个表中所有记录的交叉组合 其形成的结果集是所有连接种类中最大的 比如 表1有3条记录 表2有5条记录 则广义笛卡尔积产生3 5 15条记录 由于这种连接操作是不带条件的表的拼接 因此实际意义不大 2020 1 5 44 等值连接 等值连接又称为内连接 若将查询结果的目标列中重复的列去掉 则称为自然连接 在实际中等值连接一般以自然连接的形式出现 例3 43 查询每位员工的员工号 姓名 部门号 部门名称及部门电话 SELECTemp id ename dept dept id dname telFROMemployee deptWHEREemployee dept id dept dept id 说明 1 如果属性列名在参加连接的各表中是惟一的 可以省略表名前缀 如果属性列名是两个表共同的属性 则一定要加表名前缀 2 在书写连接查询时 为了简化 可以为表名取别名 别名应该简单 别名只在本次查询有效 2020 1 5 45 例3 44 查询每位员工的员工号 姓名 部门号 部门名称及部门电话 SELECTemp id ename d dept id dname telFROMemployeee deptdWHEREe dept id d dept id 不等连接当连接条件中的比较运算符不为 时 此时的连接查询称为不等连接 2020 1 5 46 假设有JOB表 职位表 它包含两个属性 职位job level和标准月薪std sal 该表包含记录如下 job levelstd sal部门经理2200出纳1200 例3 45 列出可提供给employee表中每位员工比现在薪水高的职位 SELECTename sal job level std salFROMemployee jobWHEREstd sal sal查询结果如下 enamesaljob levelstd sal吴伟1300部门经理2200岳玲1100部门经理2200王斌1500部门经理2200徐欢800部门经理2200岳玲1100出纳1200徐欢800出纳1200 2020 1 5 47 3 4数据更新 3 4 1插入记录插入单条记录INSERTINTO VALUES 注意 1 属性列的个数与常量的个数要相等 且顺序一致 否则会产生语法错误 2 在表结构定义中未说明为NOTNULL的属性列 如果没有出现在INTO子句后 这些列将取空值 已经说明为NOTNULL的属性列 则必须出现在INTO子句后 3 如果INTO子句后没有指定任何列 则VALUES子句后面的常量个数必须与基本表中列的个数相等 且类型 顺序一致 否则会出语法错误或导致赋值不正确 2020 1 5 48 例3 47 插入一条部门新记录 INSERTINTOdept dept id dname tel VALUES 31 产品开发部 08667864532 该语句等价于 INSERTINTOdeptVALUES 31 产品开发部 08667864532 例3 48 插入一条员工新记录 INSERTINTOemployeeVALUES 1311 淳 NULL NULL to date 2004 08 15 800 11 2020 1 5 49 插入子查询的结果SELECT语句可以作为子查询嵌套在INSERT语句中 用以插入批量记录 其语句格式一般为 INSERTINTO 子查询 例3 49 求出每个部门平均月薪 将部门号和平均月薪放入一张新表dept sal中 先创建新表的结构 CREATETABLEdept sal dept idCHAR 2 avg salSMALLINT 然后将子查询求出的数据批量插入新表中 INSERTINTOdept sal dept id avg sal SELECTdept id AVG sal FROMemployeeGROUPBYdept id 2020 1 5 50 3 4 2修改记录SQL语言修改记录的语句为UPDATE 该语句有3种形式 修改单条记录 修改多条记录以及使用子查询修改记录 其一般语句格式为 UPDATESET WHERE SQL语言的修改语句功能是将表中符合WHERE子句条件的记录找出 以表达式的值替代相应属性列的值 2020 1 5 51 修改单条记录 例3 50 修改1311号员工的月系薪为1700 UPDATEemployeeSETsal 1700WHEREemp id 1311 修改多条记录 例3 51 所有员工月薪上调5 UPDATEemployeeSETsal sal 1 05 例3 52 21号部门的所有员工取消岗位津贴 UPDATEemployeeSETcomm 0WHEREdept id 21 2020 1 5 52 用子查询修改记录UPDATE语句可以和SELECT语句联合使用 后者作为子查询嵌套 这种情况也属于批量修改 例3 53 将产品开发部的部门主管的岗位津贴加500 UPDATEemployeeSETcomm comm 500WHEREjob 部门主管 ANDdept id SELECTdept idFROMdeptWHEREdept dname 产品开发部 说明 1 因为对应产品开发部的部门号只有一个 所以子查询检索出的记录只有一条 因此子查询前面可以用 2 这条UPDATE语句的WHERE子句有两个条件 它们是逻辑与关系 根据这两个条件查询找出的记录可能是多条 2020 1 5 53 3 4 3删除记录DELETE语句一般格式 DELETEFROM WHERE DELETE语句的功能是 先按照WHERE子句中指定的条件范围将记录找出来 然后进行删除 删除单条记录 例3 54 删除员工号为1045的记录 DELETEFROMemployeeWHEREemp id 1045 2020 1 5 54 删除多条记录 例3 55 删除部门号为31的所有记录 DELETEFROMemployeeWHEREdept id 31 例3 56 删除所有员工记录 DELETEFROMemployee 删除所有记录 是清空表中数据 因此不加WHERE子句 这类操作的执行应非常小心 2020 1 5 55 使用子查询删除SELECT语句同样也可以和DELETE语句联合使用 例3 57 删除产品开发部和事业推广部的所有员工的记录 DELETEFROMemployeeWHEREdept idIN SELECTdept idFROMdeptWHEREdept dname 产品开发部 ORdept dname 事业推广部 因为SELECT子查询得到的记录一般不止一条 所以子查询前面的运算符不能为 而应该用表示取值范围的 IN 2020 1 5 56 3 5视图 3 5 1视图的概念视图不包含任何数据 只是定义在一个或多个基表上或其他视图上 并且提供一种访问基表数据的方法 在物理磁盘上存储的有关视图的信息是 视图的名称和视图的定义 视图的所有数据来自基本表 因此 当基本表的数据发生变化时 对应视图中的查询出的数据也会随之变化 视图一经定义 就可以象基本表一样执行查询 删除等操作 也可以在视图上定义新的视图 但对视图的更新操作则有一定限制 3 5 2视图的作用可以满足不同用户的需求不同的用户对数据库操作有不同的需求 即使相同的数据也可能有不同的操作要求 一张基本表可能有很多属性列 利用视图 用户可以把自己感兴趣的属性列集中起来 放在一个视图中 此后用户可以将视图作为一张表来对待 2020 1 5 57 可以简化数据读取查询数据时 通常要用SELECT语句编写复杂的连接 统计 函数等 以产生所需要的结果 使用视图 可以隐蔽这种复杂性 可以将经常用到的复杂查询的语句定义为视图 不必每次查询都写上复杂查询条件 这样就简化了用户的查询操作 保证了基本表数据和应用程序的逻辑独立性当应用程序通过视图来访问数据时 视图实际上成为应用程序和基本表数据之间的桥梁 如果应用程序直接调用基本表 则一旦基本表的数据发生变化时 应用程序必须随之改动 而通过视图访问数据 则可以通过改变视图来适应基本表的变化 使应用程序不必作改变 保证了基本表数据和应用程序的逻辑独立性 可以提供安全机制利用视图可以限制数据访问 如果某个用户需要访问表中的某些列 但另一些属性列必须对该用户保密 则可以利用视图达到此目的 将视图建立在该用户需要访问的那些列上 2020 1 5 58 3 5 3视图的建立SQL语言用CREATEVIEW语句来建立视图 其一般格式为 CREATEVIEW AS WITHCHECKOPTION 说明 1 选项WITHCHECKOPTION确保用户只能查询和修改他们所看到的数据 强制所有在视图上使用的数据修改语句满足定义视图时的条件 2 组成视图的各属性列可以显式指定 也可以省略 如果省略不写 则组成视图的各属性列由子查询中SELECT子句的各目标列组成 下列情形建立视图时必须显式指定属性列 1 视图的某列不是原属性列 而是统计函数或者表达式 2 多表连接时选出了两个或者多个同名列作为视图的属性列 3 需要对视图中的某些列重新命名 2020 1 5 59 例3 58 建立仅包含部门主管视图 CREATEVIEWmgr vuASSELECT FROMemployeeWHEREJOB 部门主管 例3 59 为21号部门的所有员工的员工号 姓名及月薪建立视图 CREATEVIEWsal21 vu eno ename salary ASSELECTemp id ename salFROMemployeeWHEREdept id 21 2020 1 5 60 例3 60 为21号部门月薪超过1500的员工的员工号 姓名及月薪建立视图 CREATEVIEWsal21 vu1 eno ename salary ASSELECTemp id ename salFROMemployeeWHEREdept id 21 ANDsal 1500 该题也可以对 例3 59 定义的视图进行进一步的筛选 由于视图不仅可以建立在基本表之上 还可以建立在视图上 因此该语句也可写作 CREATEVIEWsal21 vu1ASSELECT FROMsal21 vuWHEREsal 1500 2020 1 5 61 例3 61 为各部门的平均月薪建立视图CREATEVIEWsalavg vu dno sal avg ASSELECTdept id AVG sal FROMemployeeGROUPBYdept id 本例中因使用了统计函数 因而对视图的列作了显式指定 视图还可以建立在多个表或视图之上 例3 62 为产品开发部的员工的员工号 姓名 月薪以及工龄建立视图 CREATEVIEWdept vu eno name salary wage ASSELECTemp id ename sal 2008 year workdate FROMemployee deptWHEREemployee