关系数据库标准语言.ppt

2023/9/5,3.1 SQL概述3.2 数据定义3.3 数据查询3.4 数据更新 3.5 视图 3.6 案例2：活期储蓄管理系统 数据库上的查询,2023/9/5,3.1 SQL概述,SQL(Structured Query Language)的字面意思是结构化的查询语言，但它的功能并不仅仅是查询，它是关系数据库管理系统的标准语言。3.1.1 SQL的主要功能 SQL语言按照功能可以分为3大类：DDL（Data Definition Language）数据定义语言 用于定义关系数据库的模式、外模式和内模式，以实现对数据库基本表、视图及索引文件的定义、修改和删除等操作。最常用的DDL语句是CREATE、DROP和ALTER命令。DML（Data Manipulation Language）数据操纵语言 用于完成数据查询和数据更新操作。其中数据更新指对数据进行插入、删除和修改操作。最常使用的DML语句是SELECT、INSERT、UPDATE和DELETE命令。,2023/9/5,DCL（Data Control Language）数据控制语言 用于控制对数据库的访问，服务器的关闭、启动等操作。常使用的DCL命令有：GRANT、REVOKE等。3.1.2 SQL的特点语言简洁，风格统一，易学易懂 SQL语言接近英语，只用几个英文单词的组合就能完成所有功能，因此初学者非常容易上手。既是自含式语言，又是嵌入式语言 作为自含式语言，SQL能够独立地用于联机交互的使用方式。当作为嵌入式语言使用时，SQL语句几乎可以不加修改地嵌入到如VB、PB这样的前端开发平台上，利用前端工具的计算能力和SQL的数据库操纵能力，可以快速地建立数据库应用程序。高度非过程化 用SQL语言进行数据操作，只要提出“做什么”，而无须知道“怎么做”，SQL语句的实现过程由系统自动完成。,2023/9/5,3.1.3 SQL对象命名约定 SQL对象包括数据库、表、视图、属性名等。这些对象名必须符合一定规则或约定，一般应遵守下列规则。数据对象名可以为1-30个字符（在MS Access为64个字符），但有些DBMS限制为8个字符，例如：Oracle数据库就是如此。数据对象名应以字母开头，其余字符可以由字母、数字、下划线组成。3.1.4 SQL语句结构和书写准则在SQL语句语法格式中的一些约定符号：尖括号“”中的内容为实际语义。中括号“”中的内容为任选项。，.意思是“等等”，即前面的项可以重复。,2023/9/5,大括号“”与竖线“|”表明此处为选择项，在所列出的各项中仅需选择一项。例如：A|B|C|D 意思是A、B、C、D中取其一。SQL中的数据项（包括列项、表和视图）分隔符为“，”；其字符串常数的定界符用单引号“”表示。在编写SQL语句时，遵从某种准则以提高语句的可读性，使其易于编辑，是很有好处的。以下是一些通常的准则：SQL语句对大小写不敏感 为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式。SQL语句可写成一行或多行，习惯上每个子句占用一行。关键字不能在行与行之间分开，并且很少采用缩写形式。SQL语句的结束符为分号“；”，分号必须放在语句中的最后一个子句后面，但可以不在同一行。,2023/9/5,3.2 数据定义,SQL语言的数据定义功能包括定义数据库、定义基本表、定义索引和定义视图。其基本语句如表3-1所示。表3-1 SQL的数据定义语句,2023/9/5,基本表是独立存储在数据库中的表 在SQL中一个关系对应一个基本表，一个（或多个）基本表对应一个存储文件，基本表对应的数据必须在数据库中存放。存储文件的物理结构对用户而言是透明的，用户无需关心。一个基本表可以根据需要带一个或多个索引，索引也存放在存储文件中。视图是由一个或几个基本表导出的，它的外部形式也是一个表，是由基本表中选取的行和列组成的 视图本身不独立，它依附于基本表，在数据库中不存放视图对应的数据，而只存放关于视图的定义，因此视图只是一个虚表。,2023/9/5,3.2.1 数据库的创建与删除 创建数据库 SQL使用命令CREATE DATABASE创建数据库，其一般语法如下：CREATE DATABASE；【例3-1】创建一个简单数据库。CREATE DATABASE MyDb；删除数据库 SQL使用命令DROP DATABASE删除一个或多个数据库，其一般语法如下：DROP DATABASE，,；【例3-2】删除数据库MyDb。DROP DATABASE MyDb；,2023/9/5,3.2.2 表的创建与删除 创建基本表 创建基本表的结构是建立数据库最重要的一步，其一般语法如下：CREATE TABLE（，,)；说明：是指要创建的基本表的名称，该名称应符合具体DBMS的标识符的命名规则。指的是表的属性名称。,2023/9/5,定义表的各个属性时应指出其相应的数据类型和长度，不同DBMS支持的数据类型不完全相同，表3-2列出的是IBM DB2 SQL支持的主要数据类型：,2023/9/5,关于完整性约束条件，需要说明以下几点：完整性约束条件分为列级完整性约束条件和表级完整性约束条件，它们之间的区别在于：列级完整性约束条件只能用于列，而表级完整性约束条件能够用于一张表中的多列。SQL的完整性约束条件有以下几种：NOT NULL 或NULL约束 这个约束条件为列级完整性约束条件。NOT NULL 为不允许该列存在空值，而NULL 为允许该列存在空值。UNIQUE约束 UNIQUE约束是惟一性约束。即不允许表中的某一列或者某几列有重复的属性值。DEFAULT约束 DEFAULT约束为默认值约束，是列级完整性约束条件。当向表中插入一个新行时，如果对于特定列没有指定数值，则使用DEFAULT子句指定的默认值。,2023/9/5,CHECK约束 CHECK约束是检验约束，为插入列中的数据指定约束条件。PRIMARY KEY 约束 PRIMARY KEY 约束即主键约束，是表级完整性约束条件。表中的主键可以是一列或列组，PRIMARY KEY 约束可以使得主键的数值在每一行中各不相同。注意：PRIMARY KEY约束类似于UNIQUE约束，差别在于PRIMARY KEY约束中的列不可以为空。FOREIGN KEY约束 FOREIGN KEY约束是参照完整性约束，是用于约束外键的，也是表级完整性约束条件。,2023/9/5,【例3-3】创建“学生表”student，它由学号id、姓名name、性别sex、班级号class、出生日期birthday等5个属性组成。其中学号不能为空，值必须惟一，且姓名也必须惟一和非空。CREATE TABLE student(id CHAR(8)NOT NULL UNIQUE,name VARCHAR(20)NOT NULL UNIQUE,sex CHAR(1)DEFAULT 男 NOT NULL,class CHAR(4),birthday DATE,sum int constraint c2 check sum2000,CONSTRAINT C1 CHECK(sex IN(男，女)；,2023/9/5,【例3-4】创建“班级信息表”class，它由班级号id、班级名name、班长monitor等3个属性组成，其中班级号是主键，班长是外键，它是【例3-3】学生表中学号的某个值。CREATE TABLE class(id CHAR(4)NOT NULL,name VARCHAR(50)NOT NULL,monitor CHAR(8)CONSTRAINT C2 PRIMARY KEY(id),CONSTRAINT C3 FOREIGN KEY(monitor)REFERENCES student(id)；说明：本例定义了2个列级约束、2个表级约束。CONSTRAINT子句定义的是表级约束，C2、C3是约束名，分别将id定义为主键，monitor定义为外键。,2023/9/5,删除基本表 当某个基本表不再需要时，可以使用DROP TABLE 语句将它删除。其一般语法为：DROP TABLE；【例3-5】删除student表。DROP TABLE student；该语句一旦执行，基本表的定义、数据、此表上建立的索引和视图都将自动被删除掉。,2023/9/5,3.2.3 表结构的修改 SQL语言用ALTER TABLE命令修改基本表，其基本语法为：ALTER TABLE ADD(完整性约束,)DROP MODIFY(,)；基本表的修改分3种情况：使用ADD子句增加新列和新的完整性约束条件【例3-6】在student表中增加“籍贯native_place”列，数据类型为字符型。ALTER TABLE student ADD native_place VARCHAR(50)；,1,1,2023/9/5,使用DROP子句删除指定的完整性约束条件【例3-7】删除student表学生姓名必须取惟一值的约束条件。ALTER TABLE student DROP UNIQUE(name)；使用MODIFY子句修改基本表的列定义【例3-8】将student 表name列的数据类型改为定长字符串型。ALTER TABLE student MODIFY name char(8)NOT NULL；注意：(1)修改列定义时，要将原来的列级约束条件写上，否则原有的列级约束会不起作用。(2)修改列定义时，有可能会破坏已有的数据，应事先作好备份工作。(3)SQL未提供删除属性列的语句，只能采取间接的方法。,2023/9/5,3.2.4 建立索引 索引的概念 索引是建立在列上的一种数据库对象，它对表中的数据提供逻辑顺序，当在数据库表中搜索某一行时，可以通过使用索引来找到它的物理位置。索引建立后，什么时候使用索引以及使用哪一个索引（当有多个索引存在时），由DBMS内部根据情况自行决定，不需要人员干预。索引是动态的，每当数据库表的数据更新一次，相应的索引也随之更新。,2023/9/5,建立索引 在SQL语言中，建立索引使用CREATE INDEX命令，其一般语法为：CREATE UNIQUE CLUSTER INDEX ON(，,)；说明：(1)表名是要建立索引的基本表的名字。(2)列名是被建立索引的列的名称。索引可以建立在某一列或多个列上。(3)次序是指按照该列名的索引值的排列顺序。次序可以取值ASC（升序）或DESC（降序），默认值是ASC。(4)UNIQUE表示创建的索引是惟一索引，索引列上的数据不能有重复值。(5)CLUSTER表示要建立的是聚簇索引。聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引。,2023/9/5,【例3-9】为学生表student和班级信息表class建立索引。其中，student表按学号id升序建立惟一索引，class表按班级号降序建立惟一索引。CREATE UNIQUE INDEX stuid_ind ON student(id ASC)；CREATE UNIQUE INDEX claid_ind ON class(id DESC)；【例3-10】为表student在姓名name列上建立一个聚簇索引。CREATE CLUSTER INDEX stu_cluind ON student(name)；该语句执行后，student表中记录的物理顺序将按照name列值的升序存放。一个基本表只能建立一个聚簇索引，因为表中的数据不能以多种方式进行物理排序。可以在经常查询的列上建立聚簇索引，以提高查询效率。但对于经常更新的列则不宜建立聚簇索引，因为建立聚簇索引后，更新索引列数据时，要更新表中记录的物理顺序，开销较大。,2023/9/5,删除索引 在SQL语言中，使用DROP INDEX命令删除索引，其语法如下：DROP INDEX；【例3-11】删除为student表建立的索引stu_cluind。DROP INDEX stu_cluind；删除索引时，系统会同时从数据库中删去有关该索引的描述。对于数据库系统而言，索引一经建立，一般不应随意删除。SQL没有提供修改索引的语句，对于一些在使用中证明不合适的索引，只能先删除后重建。,2023/9/5,3.3数据查询,3.3.1 SELECT语句的结构 SQL语言提供的SELECT语句的一般格式如下：SELECT ALL|DISTINCT，,FROM,WHERE GROUP BY HAVING ORDER BY ASC|DESC；说明：SELECT语句的含义是，根据WHERE子句指定的条件，从FROM子句后面的基本表或视图中找出满足条件的记录，再按照SELECT子句指定的目标列表达式，选出这些记录相应的列形成结果集返回。其中，SELECT子句和FROM子句是必选的，而WHERE子句、GROUP BY子句、HAVING子句以及ORDER BY子句都是可选的。,2023/9/5,3.3.2 选择行和列-选择和投影运算的实现 下面以人事工资管理系统的员工表employee和部门表dept为例介绍SELECT语句的使用方法。Employee(emp_id,ename,job,mgr_id,workdate,sal,comm,dept_id)员工号 姓名 职位 主管 参加工作时间 月薪 岗位津贴 部门号 Dept(dept_id，dname，tel)部门号，部门名称，电话 用下面语句创建表结构：CREATE TABLE employee(emp_id CHAR(4)PRIMARY KEY,ename VARCHAR(20),job VARCHAR(9),mgr_id CHAR(4),workdate DATE,sal SMALLINT,comm SMALLINT,dept_id CHAR(2)NOT NULL FOREIGN KEY(dept_id)REFERENCES Dept(dept_id),FOREIGN KEY(mgr_id)REFERENCES employee(emp_id);,2023/9/5,CREATE TABLE dept(dept_id CHAR(2)PRIMARY KEY,dname VARCHAR(12),tel VARCHAR(20);选择运算的实现查询一个表的所有行 在SELECT语句中只要没有WHERE子句，查询结果就包含了所有行。【例3-12】查询表employee的所有员工的员工号和姓名。SELECT emp_id,ename FROM employee;查询满足条件的某些行 查询满足条件的某些行，可以通过WHERE子句来实现。【例3-13】查询月薪超过1800的员工姓名和月薪。SELECT ename,sal FROM employee WHERE sal1800;,2023/9/5,【例3-14】查询员工号为1001的员工姓名及部门号。SELECT ename,dept_id FROM employee WHERE emp_id=1001;投影运算的实现查询表的全部列【例3-15】查询部门表中部门号为11的全部内容。SELECT dept_id,dname,tel FROM dept WHERE dept_id=11;在SQL语言中，可以用星号“*”代表所有列名，列的显示顺序与基本表中列的顺序一致。【例3-16】下面语句的结果等价于【例3-15】的语句。SELECT*FROM dept WHERE dept_id=11;,2023/9/5,查询表的部分列【例3-17】查询全体员工的员工号，姓名和参加工作时间。SELECT emp_id,ename,workdate FROM employee;查询经过计算的值 SELECT子句的不仅可以是基本表的属性，也可以是表达式，包括算术表达式、字符串常量和函数等。【例3-18】查询全体员工的姓名及年薪。SELECT ename,sal*12 FROM employee;结果:ename sal*12 吴伟 15600 岳玲 13200 王斌 18000 徐欢 9600,2023/9/5,【例3-19】上例查询如果在中使用字符串常量，结果会更清晰。SELECT ename,年薪：,sal*12 FROM employee;查询结果如下：ename 年薪：sal*12 吴伟 年薪：15600 岳玲 年薪：13200 王斌 年薪：18000 徐欢 年薪：9600,2023/9/5,【例3-20】对【例3-18】查询可以通过指定别名来改变查询结果的列标题，这样也可以使结果更清晰。SELECT ename AS 姓名,sal*12 AS 年薪 FROM employee;查询结果如下：姓名 年薪 吴伟 15600 岳玲 13200 王斌 18000 徐欢 9600,2023/9/5,3.3.3 SQL的运算符SQL语言使用的运算符包括算术运算符、比较运算符、逻辑运算符等。算术运算符 算术运算符有4种：+、-、*、/比较运算符基本比较运算符 共9种：=、=、！=或、！（不大于）、！（不小于）特殊比较运算符 有4类：BETWEENAND、IN、LIKE 和 IS NULL,2023/9/5,运算符BETWEENAND 运算符BETWEENAND是用于确定记录的范围，即某属性值在指定的范围之内（包括边界值）的记录，其中BETWEEN后面是下限，AND后面是上限。【例3-21】查询月薪在1000到1500之间的员工姓名及月薪。SELECT ename,sal FROM employee WHERE sal BETWEEN 1000 and 1500;查询结果包括那些月薪等于1000和月薪等于1500的记录。运算符IN 运算符IN是用于查找某属性值包含在指定集合内的记录，IN后面跟的是指定集合。【例3-22】查找部门号属于11和21的员工姓名及部门号。SELECT ename,dept_id FROM employee WHERE dept_id IN(11,21);,2023/9/5,运算符LIKE 运算符LIKE可用来进行字符串的匹配，LIKE后面跟的是匹配模式。匹配模式可以是一个包含通配符“%”（百分号）和“_”（下划线）的字符串。“%”代表任意长度（长度可以为0）的字符串。“_”代表任意单个字符。【例3-23】查询所有姓张员工的姓名。SELECT ename FROM employee WHERE ename LIKE 张%;【例3-24】查找所有1990年参加工作的员工姓名及参加工作时间。SELECT ename,workdate FROM employee WHERE workdate LIKE%1990;【例3-25】查找姓名第2个字为“小”的员工姓名。SELECT ename FROM employee WHERE ename LIKE _小%;,2023/9/5,运算符IS NULL 运算符IS NULL用来测试某个属性值是否为空。【例3-27】在employee表中，职位是总经理的员工在属性mgr_id这栏应该不填，因为他没有上司。因此查询总经理的姓名的语句应为：SELECT ename FROM employee WHERE mgr_id IS NULL;【例3-28】可能有这样的情况：新进员工暂时没有岗位津贴，等试用期满后再领岗位津贴。查询没有岗位津贴的员工姓名。SELECT ename FROM employee WHERE comm IS NULL;,2023/9/5,逻辑运算符SQL语言的逻辑运算符有3种：NOT、AND、OR。【例3-29】查询月薪超过1500的部门主管的姓名及月薪。SELECT ename,sal FROM employee WHERE sal1500 AND job=部门主管;【例3-30】查询月薪超过1500的员工和所有部门主管的姓名及月薪。SELECT ename,sal FROM employee WHERE sal1500 OR job=部门主管;,2023/9/5,【例3-31】查询领有岗位津贴的员工姓名。SELECT ename FROM employee WHERE comm IS NOT NULL;【例3-32】查找部门号不属于11和21的员工姓名及部门号。SELECT ename,dept_id FROM employee WHERE dept_id NOT IN(11,21);,2023/9/5,3.3.4 对查询结果排序 SQL语言中用ORDER BY子句实现对查询结果的排序，可以根据包含的一列或者多列的表达式进行ASC（升序）或DESC（降序）的排列，默认值是ASC。【例3-33】查询所有员工的姓名及月薪，结果按月薪的降序排列。SELECT ename,sal FROM employee ORDER BY sal DESC;ORDER BY子句指定的排序列可以不只一个。,2023/9/5,【例3-34】查询所有员工的姓名、部门号及月薪，结果按部门号升序排列，同一部门按月薪降序排列。SELECT ename,dept_id,sal FROM employee ORDER BY dept_id,sal DESC;说明：上例中dept_id称为主排序关键字，sal成为次排序关键字。注意：(1)ORDER BY子句不改变基本表中行或列的顺序，只改变查询显 示的顺序。(2)ORDER BY子句指定排序的列必须出现在SELECT子句的列表达式中。(3)排序是查询语句的最后一步工作，所以ORDER BY子句一般放在查询语句的最后。,2023/9/5,3.3.5 消除重复行 基本表中不相同的行，经过对某些指定列进行投影运算后，可能会变成完全相同的行，显示结果不直观，这时需要用DISTINCT选项消除重复的行。【例3-35】查询表employee中的所有职位。SELECT DISTINCT job FROM employee;注意：在一个SELECT语句中DISTINCT只能出现一次，并且DISTINCT必须在所有列名之前，否则会发生语法错误。与DISTINCT选项含义相反的是ALL选项，在SELECT语句中使用ALL选项，表示结果重复的行也将显示。ALL选项是默认选项。,2023/9/5,3.3.6 SQL的统计函数 SQL语言提供了许多统计函数，主要的统计函数见表3-3。,2023/9/5,【例3-36】统计员工总人数。SELECT COUNT（*）FROM employee;也可以写成：SELECT COUNT（emp_id）FROM employee;【例3-37】统计部门号“11”的部门领取岗位津贴的人数。SELECT COUNT(comm)FROM employee WHERE dept_id=11;这里统计的是属性列comm不为空值的行数。,2023/9/5,【例3-38】统计部门号“11”的部门全体员工人数。SELECT COUNT(*)FROM employee WHERE dept_id=11;【例3-39】查询最早参加工作时间和最晚参加工作时间。SELECT MIN(workdate),MAX(workdate)FROM employee;【例3-39】统计所有员工的岗位津贴总数及平均岗位津贴。SELECT SUM(comm),AVG(comm)FROM employee;注意：除COUNT（*）外，所有的统计函数都不包括取值为空值的行。,2023/9/5,3.3.7 数据分组 利用GROUP BY子句可以将查询结果按照一列或者多列分组，值相等的为一组。基于单列的分组【例3-40】按部门号查询各部门的平均月薪。SELECT dept_id,AVG(sal)FROM employee GROUP BY dept_id;基于多列的分组 GROUP BY子句还可以作用于多列上，此时的数据分组意义是分大组之后再分小组。【例3-41】分各部门各职位统计月薪总额。SELECT dept_id,job,sum(sal)FROM employee GROUP BY dept_id,job;说明：先按照部门号分组，部门号相同的组再按职位细分，最后部门号和职位完全相同的行才会分在一组，然后每组统计一个月薪总额。,2023/9/5,HAVING子句 如果分组后还要根据一定条件对这些组进行筛选，则使用HAVING子句来实现。【例3-42】按部门号查询各部门的平均月薪，要求只显示平均月薪在1000以上的部门编号和平均月薪。SELECT dept_id,AVG(sal)FROM employee GROUP BY dept_id HAVING AVG(sal)1000;注意:WHERE子句和HAVING子句有相似之处，即后面都跟指定条件；但是它们又有区别：前者直接用于SELECT子句中，作用于基本表或视图；而后者一定跟在GROUP BY子句后面，作用于分组。,2023/9/5,3.3.8 连接查询 连接查询的概念 如果一个查询需要从两个或两个以上的数据表中获取数据时，则称之为连接查询。连接查询包括广义笛卡尔积、等值连接、自然连接、外连接、内连接、左连接、右连接和自连接等。广义笛卡尔积 广义笛卡尔积是不带连接条件的连接操作。两个表的广义笛卡尔积即是两个表中所有记录的交叉组合，其形成的结果集是所有连接种类中最大的。比如：表1有3条记录，表2有5条记录，则广义笛卡尔积产生3*5=15条记录。由于这种连接操作是不带条件的表的拼接，因此实际意义不大。,2023/9/5,等值连接.=.等值连接又称为内连接。若将查询结果的目标列中重复的列去掉，则称为自然连接，在实际中等值连接一般以自然连接的形式出现。【例3-43】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。SELECT emp_id,ename,dept.dept_id,dname,tel FROM employee,dept WHERE employee.dept_id=dept.dept_id;说明：(1)如果属性列名在参加连接的各表中是惟一的，可以省略表名前缀；如果属性列名是两个表共同的属性，则一定要加表名前缀。(2)在书写连接查询时，为了简化，可以为表名取别名，别名应该简单。别名只在本次查询有效。,2023/9/5,【例3-44】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。SELECT emp_id,ename,d.dept_id,dname,tel FROM employee e,dept d WHERE e.dept_id=d.dept_id;不等连接 当连接条件中的比较运算符不为“=”时，此时的连接查询称为不等连接。,2023/9/5,假设有JOB表（职位表），它包含两个属性：职位job_level和标准月薪std_sal，该表包含记录如下：job_level std_sal 部门经理 2200 出纳 1200【例3-45】列出可提供给employee表中每位员工比现在薪水高的职位。SELECT ename,sal,job_level,std_sal FROM employee,job WHERE std_salsal 查询结果如下：ename sal job_level std_sal 吴伟 1300 部门经理 2200 岳玲 1100 部门经理 2200 王斌 1500 部门经理 2200 徐欢 800 部门经理 2200 岳玲 1100 出纳 1200 徐欢 800 出纳 1200,2023/9/5,3.4 数据更新,3.4.1 插入记录插入单条记录 INSERT INTO(,)VALUES(,);注意：（1）属性列的个数与常量的个数要相等，且顺序一致，否则会产生语法错误。（2）在表结构定义中未说明为NOT NULL的属性列，如果没有出现在INTO子句后，这些列将取空值。已经说明为NOT NULL的属性列，则必须出现在INTO子句后。（3）如果INTO子句后没有指定任何列，则VALUES子句后面的常量个数必须与基本表中列的个数相等，且类型、顺序一致，否则会出语法错误或导致赋值不正确。,2023/9/5,【例3-47】插入一条部门新记录。INSERT INTO dept(dept_id,dname,tel)VALUES(31,产品开发部,08667864532);该语句等价于：INSERT INTO dept VALUES(31,产品开发部,08667864532);【例3-48】插入一条员工新记录。INSERT INTO employee VALUES(1311,淳,NULL,NULL,to_date(2004/08/15),800,11);,2023/9/5,插入子查询的结果 SELECT语句可以作为子查询嵌套在INSERT语句中，用以插入批量记录。其语句格式一般为：INSERT INTO(,)子查询;【例3-49】求出每个部门平均月薪，将部门号和平均月薪放入一张新表dept_sal中。先创建新表的结构：CREATE TABLE dept_sal(dept_id CHAR(2),avg_sal SMALLINT);然后将子查询求出的数据批量插入新表中：INSERT INTO dept_sal(dept_id,avg_sal)SELECT dept_id,AVG(sal)FROM employee GROUP BY dept_id;,2023/9/5,3.4.2 修改记录 SQL语言修改记录的语句为UPDATE。该语句有3种形式：修改单条记录、修改多条记录以及使用子查询修改记录。其一般语句格式为：UPDATE SET=,=,WHERE;SQL语言的修改语句功能是将表中符合WHERE子句条件的记录找出，以表达式的值替代相应属性列的值。,2023/9/5,修改单条记录【例3-50】修改1311号员工的月系薪为1700。UPDATE employee SET sal=1700 WHERE emp_id=1311;修改多条记录【例3-51】所有员工月薪上调5%。UPDATE employee SET sal=sal*1.05;【例3-52】21号部门的所有员工取消岗位津贴。UPDATE employee SET comm=0 WHERE dept_id=21;,2023/9/5,用子查询修改记录 UPDATE语句可以和SELECT语句联合使用。后者作为子查询嵌套。这种情况也属于批量修改。【例3-53】将产品开发部的部门主管的岗位津贴加500。UPDATE employee SET comm=comm+500 WHERE job=部门主管 AND dept_id=(SELECT dept_id FROM dept WHERE dept.dname=产品开发部);说明：(1)因为对应产品开发部的部门号只有一个，所以子查询检索出的记录只有一条，因此子查询前面可以用“=”。(2)这条UPDATE语句的WHERE子句有两个条件，它们是逻辑与关系。根据这两个条件查询找出的记录可能是多条。,2023/9/5,3.4.3 删除记录 DELETE语句一般格式：DELETE FROM WHERE;DELETE语句的功能是：先按照WHERE子句中指定的条件范围将记录找出来，然后进行删除。删除单条记录【例3-54】删除员工号为1045的记录。DELETE FROM employee WHERE emp_id=1045;,2023/9/5,删除多条记录【例3-55】删除部门号为31的所有记录。DELETE FROM employee WHERE dept_id=31;【例3-56】删除所有员工记录。DELETE FROM employee;删除所有记录，是清空表中数据，因此不加WHERE子句。这类操作的执行应非常小心。,2023/9/5,使用子查询删除 SELECT语句同样也可以和DELETE语句联合使用。【例3-57】删除产品开发部和事业推广部的所有员工的记录。DELETE FROM employee WHERE dept_id IN(SELECT dept_id FROM dept WHERE dept.dname=产品开发部 OR dept.dname=事业推广部);因为SELECT子查询得到的记录一般不止一条，所以子查询前面的运算符不能为“=”，而应该用表示取值范围的“IN”。,2023/9/5,3.5 视图,3.5.1 视图的概念 视图不包含任何数据，只是定义在一个或多个基表上或其他视图上，并且提供一种访问基表数据的方法。在物理磁盘上存储的有关视图的信息是：视图的名称和视图的定义。视图的所有数据来自基本表。因此，当基本表的数据发生变化时，对应视图中的查询出的数据也会随之变化。视图一经定义，就可以象基本表一样执行查询、删除等操作，也可以在视图上定义新的视图，但对视图的更新操作则有一定限制。3.5.2 视图的作用可以满足不同用户的需求 不同的用户对数据库操作有不同的需求，即使相同的数据也可能有不同的操作要求。一张基本表可能有很多属性列，利用视图，用户可以把自己感兴趣的属性列集中起来，放在一个视图中，此后用户可以将视图作为一张表来对待。,2023/9/5,可以简化数据读取 查询数据时，通常要用SELECT语句编写复杂的连接、统计、函数等，以产生所需要的结果。使用视图，可以隐蔽这种复杂性。可以将经常用到的复杂查询的语句定义为视图，不必每次查询都写上复杂查询条件，这样就简化了用户的查询操作。保证了基本表数据和应用程序的逻辑独立性 当应用程序通过视图来访问数据时，视图实际上成为应用程序和基本表数据之间的桥梁。如果应用程序直接调用基本表，则一旦基本表的数据发生变化时，应用程序必须随之改动。而通过视图访问数据，则可以通过改变视图来适应基本表的变化，使应用程序不必作改变，保证了基本表数据和应用程序的逻辑独立性。可以提供安全机制 利用视图可以限制数据访问。如果某个用户需要访问表中的某些列，但另一些属性列必须对该用户保密，则可以利用视图达到此目的，将视图建立在该用户需要访问的那些列上。,2023/9/5,3.5.3 视图的建立 SQL语言用CREATE VIEW语句来建立视图，其一般格式为：CREATE VIEW(，)AS WITH CHECK OPTION；说明：(1)选项WITH CHECK OPTION确保用户只能查询和修改他们所看到的数据，强制所有在视图上使用的数据修改语句满足定义视图时的条件。(2)组成视图的各属性列可以显式指定，也可以省略。如果省略不写，则组成视图的各属性列由子查询中SELECT子句的各目标列组成。下列情形建立视图时必须显式指定属性列：1)视图的某列不是原属性列，而是统计函数或者表达式。2)多表连接时选出了两个或者多个同名列作为视图的属性列。3)需要对视图中的某些列重新命名。,2023/9/5,【例3-58】建立仅包含部门主管视图。CREATE VIEW mgr_vu AS SELECT*FROM employee WHERE JOB=部门主管;【例3-59】为21号部门的所有员工的员工号、姓名及月薪建立视图。CREATE VIEW sal21_vu(eno,ename,salary)AS SELECT emp_id,ename,sal FROM employee WHERE dept_id=21;,2023/9/5,【例3-60】为21号部门月薪超过1500的员工的员工号、姓名及月薪建立视图。CREATE VIEW sal21_vu1(eno,ename,salary)AS SELECT emp_id,ename,sal FROM employee WHERE dept_id=21 AND sal1500;该题也可以对【例3-55】定义的视图进行进一步的筛选。由于视图不仅可以建立在基本表之上，还可以建立在视图上，因此该语句也可写作：CREATE VIEW sal21_vu1 AS SELECT*FROM sal21_vu WHERE sal1500;,2023/9/5