第3章 关系数据库标准语言SQL 3.1 SQL3.1 SQL概述概述3.2 3.2 数据定义数据定义3.3 3.3 数据查询数据查询3.4 3.4 数据更新数据更新 3.5 3.5 视图视图 3.6 3.6 案例案例2 2：活期储蓄管理系统：活期储蓄管理系统 数据库上的查数据库上的查询询8/29/2024第3章 关系数据库标准语言SQL 3.1 SQL3.1 SQL概述概述 SQL(Structured Query Language) 的字面意思是结构化的查询语言，但它的功能并不仅仅是查询，它是关系数据库管理系统的标准语言。3.1.1 SQL3.1.1 SQL的主要功能的主要功能 SQL语言按照功能可以分为3大类：vDDLDDL（Data Definition LanguageData Definition Language） 数据定义语言数据定义语言 用于定义关系数据库的模式、外模式和内模式，以实现对数据库基本表、视图及索引文件的定义、修改和删除等操作。 最常用的DDL语句是CREATE、DROP和ALTER命令。vDMLDML（Data Manipulation LanguageData Manipulation Language）数据操纵语言）数据操纵语言 用于完成数据查询和数据更新操作。其中数据更新指对数据进行插入、删除和修改操作。 最常使用的DML语句是SELECT、INSERT、UPDATE和DELETE命令。8/29/2024第3章 关系数据库标准语言SQL vDCLDCL（Data Control LanguageData Control Language）数据控制语言）数据控制语言 用于控制对数据库的访问，服务器的关闭、启动等操作。 常使用的DCL命令有：GRANT、REVOKE等。3.1.2 SQL3.1.2 SQL的特点的特点v语言简洁，风格统一，易学易懂语言简洁，风格统一，易学易懂 SQL语言接近英语，只用几个英文单词的组合就能完成所有功能，因此初学者非常容易上手。v既是自含式语言，又是嵌入式语言既是自含式语言，又是嵌入式语言 作为自含式语言，SQL能够独立地用于联机交互的使用方式。 当作为嵌入式语言使用时，SQL语句几乎可以不加修改地嵌入到如VB、PB这样的前端开发平台上，利用前端工具的计算能力和SQL的数据库操纵能力，可以快速地建立数据库应用程序。v高度非过程化高度非过程化 用SQL语言进行数据操作，只要提出“做什么”，而无须知道“怎么做”，SQL语句的实现过程由系统自动完成。8/29/2024第3章 关系数据库标准语言SQL 3.1.3 SQL3.1.3 SQL对象命名约定对象命名约定 SQL对象包括数据库、表、视图、属性名等。这些对象名必须符合一定规则或约定，一般应遵守下列规则。数据对象名可以为1-30个字符（在MS Access为64个字符），但有些DBMS限制为8个字符，例如：Oracle数据库就是如此。数据对象名应以字母开头，其余字符可以由字母、数字、下划线组成。3.1.4 SQL3.1.4 SQL语句结构和书写准则语句结构和书写准则v在在SQLSQL语句语法格式中的一些约定符号：语句语法格式中的一些约定符号：尖括号“”中的内容为实际语义。中括号“ ”中的内容为任选项。，. . .意思是“等等”，即前面的项可以重复。8/29/2024第3章 关系数据库标准语言SQL 大括号“”与竖线“|”表明此处为选择项，在所列出的各项中仅需选择一项。 例如： A | B | C | D 意思是A、B、C、D中取其一。SQL中的数据项（包括列项、表和视图）分隔符为“，”；其字符串常数的定界符用单引号“”表示。v在在编编写写SQLSQL语语句句时时，遵遵从从某某种种准准则则以以提提高高语语句句的的可可读读性性，使使其其易易于编辑，是很有好处的。于编辑，是很有好处的。 以下是一些通常的准则：SQL语句对大小写不敏感 为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式。SQL语句可写成一行或多行，习惯上每个子句占用一行。关键字不能在行与行之间分开，并且很少采用缩写形式。SQL语句的结束符为分号“；”，分号必须放在语句中的最后一个子句后面，但可以不在同一行。8/29/2024第3章 关系数据库标准语言SQL 3.2 3.2 数据定义数据定义 SQL语言的数据定义功能包括定义数据库、定义基本表、定义索引和定义视图。其基本语句如表3-1所示。 表表3-1 SQL3-1 SQL的数据定义语句的数据定义语句操作对象操作方式创建语句删除语句修改语句数据库CREATEDATABASEDROPDATABASEALTERDATABASE基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEW8/29/2024第3章 关系数据库标准语言SQL 基本表是独立存储在数据库中的表 在SQL中一个关系对应一个基本表，一个（或多个）基本表对应一个存储文件，基本表对应的数据必须在数据库中存放。存储文件的物理结构对用户而言是透明的，用户无需关心。 一个基本表可以根据需要带一个或多个索引，索引也存放在存储文件中。视图是由一个或几个基本表导出的，它的外部形式也是一个表，是由基本表中选取的行和列组成的 视图本身不独立，它依附于基本表，在数据库中不存放视图对应的数据，而只存放关于视图的定义，因此视图只是一个虚表。8/29/2024第3章 关系数据库标准语言SQL 3.2.1 3.2.1 数据库的创建与删除数据库的创建与删除v 创建数据库创建数据库 SQL使用命令CREATE DATABASE创建数据库，其一般语法如下： CREATE DATABASE ； 【例3-1】创建一个简单数据库。 CREATE DATABASE MyDb；v 删除数据库删除数据库 SQL使用命令DROP DATABASE删除一个或多个数据库，其一般语法如下： DROP DATABASE ，,；【例3-2】删除数据库MyDb。 DROP DATABASE MyDb； 8/29/2024第3章 关系数据库标准语言SQL 3.2.2 3.2.2 表的创建与删除表的创建与删除v 创建基本表创建基本表 创建基本表的结构是建立数据库最重要的一步，其一般语法如下： CREATE TABLE （ ， , ,)； 说明： 是指要创建的基本表的名称，该名称应符合具体DBMS的标识符的命名规则。指的是表的属性名称。 8/29/2024第3章 关系数据库标准语言SQL 定义表的各个属性时应指出其相应的数据类型和长度，不同DBMS支持的数据类型不完全相同，表3-2列出的是IBM DB2 SQL支持的主要数据类型： 类型表示类型说明数值型SMALLINT半字长二进制整数INT或INTEGER全字长二进制整数DECIMAL(p,q)十进制数，共p位（含小数点），其中小数点后q位FLOAT双字长浮点数字符型CHAR(n)或CHARTER(n)长度为n的定长字符串,如果省略n,字符串长度被假定为1VARCHAR(n)最大长度为n的可变长字符串日期时间型DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间8/29/2024第3章 关系数据库标准语言SQL v关于完整性约束条件，需要说明以下几点：关于完整性约束条件，需要说明以下几点：完整性约束条件分为列级完整性约束条件和表级完整性约束条件，它们之间的区别在于：列级完整性约束条件只能用于列，而表级完整性约束条件能够用于一张表中的多列。SQL的完整性约束条件有以下几种：NOT NULL 或NULL约束 这个约束条件为列级完整性约束条件。NOT NULL 为不允许该列存在空值，而NULL 为允许该列存在空值。UNIQUE约束 UNIQUE约束是惟一性约束。即不允许表中的某一列或者某几列有重复的属性值。DEFAULT约束 DEFAULT约束为默认值约束，是列级完整性约束条件。当向表中插入一个新行时，如果对于特定列没有指定数值，则使用DEFAULT子句指定的默认值。8/29/2024第3章 关系数据库标准语言SQL CHECK约束 CHECK约束是检验约束，为插入列中的数据指定约束条件。PRIMARY KEY 约束 PRIMARY KEY 约束即主键约束，是表级完整性约束条件。表中的主键可以是一列或列组，PRIMARY KEY 约束可以使得主键的数值在每一行中各不相同。 注意：PRIMARY KEY约束类似于UNIQUE约束，差别在于PRIMARY KEY约束中的列不可以为空。FOREIGN KEY约束 FOREIGN KEY约束是参照完整性约束，是用于约束外键的，也是表级完整性约束条件。8/29/2024第3章 关系数据库标准语言SQL 【例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 (男，女)； 8/29/2024第3章 关系数据库标准语言SQL 【例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定义为外键。 8/29/2024第3章 关系数据库标准语言SQL v删除基本表删除基本表 当某个基本表不再需要时，可以使用DROP TABLE 语句将它删除。其一般语法为： DROP TABLE ； 【例3-5】删除student表。 DROP TABLE student； 该语句一旦执行，基本表的定义、数据、此表上建立的索引和视图都将自动被删除掉。8/29/2024第3章 关系数据库标准语言SQL 3.2.3 3.2.3 表结构的修改表结构的修改 SQL语言用ALTER TABLE命令修改基本表，其基本语法为： ALTER TABLE ADD (完整性约束 ,) DROP MODIFY ( ,)；基本表的修改分3种情况：v使用使用ADDADD子句增加新列和新的完整性约束条件子句增加新列和新的完整性约束条件【例3-6】在student表中增加“籍贯native_place”列，数据类型为字符型。 ALTER TABLE student ADD native_place VARCHAR(50)；118/29/2024第3章 关系数据库标准语言SQL v使用使用DROPDROP子句删除指定的完整性约束条件子句删除指定的完整性约束条件 【例3-7】 删除student表学生姓名必须取惟一值的约束条件。 ALTER TABLE student DROP UNIQUE(name)；v使用使用MODIFYMODIFY子句修改基本表的列定义子句修改基本表的列定义 【例3-8】 将student 表name列的数据类型改为定长字符串型。 ALTER TABLE student MODIFY name char(8) NOT NULL； 注意：(1)修改列定义时，要将原来的列级约束条件写上，否则原有的列级约束会不起作用。(2)修改列定义时，有可能会破坏已有的数据，应事先作好备份工作。(3)SQL未提供删除属性列的语句，只能采取间接的方法。8/29/2024第3章 关系数据库标准语言SQL 3.2.4 3.2.4 建立索引建立索引v 索引的概念索引的概念 索引是建立在列上的一种数据库对象，它对表中的数据提供逻辑顺序，当在数据库表中搜索某一行时，可以通过使用索引来找到它的物理位置。索引建立后，什么时候使用索引以及使用哪一个索引（当有多个索引存在时），由DBMS内部根据情况自行决定，不需要人员干预。索引是动态的，每当数据库表的数据更新一次，相应的索引也随之更新。 8/29/2024第3章 关系数据库标准语言SQL v建立索引建立索引 在SQL语言中，建立索引使用CREATE INDEX命令，其一般语法为： CREATE UNIQUE CLUSTER INDEX ON (，,)； 说明： (1)表名是要建立索引的基本表的名字。 (2)列名是被建立索引的列的名称。索引可以建立在某一列或多个列上。 (3)次序是指按照该列名的索引值的排列顺序。次序可以取值ASC（升序）或DESC（降序），默认值是ASC。 (4)UNIQUE表示创建的索引是惟一索引，索引列上的数据不能有重复值。 (5)CLUSTER表示要建立的是聚簇索引。聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引。8/29/2024第3章 关系数据库标准语言SQL 【例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列值的升序存放。一个基本表只能建立一个聚簇索引，因为表中的数据不能以多种方式进行物理排序。可以在经常查询的列上建立聚簇索引，以提高查询效率。但对于经常更新的列则不宜建立聚簇索引，因为建立聚簇索引后，更新索引列数据时，要更新表中记录的物理顺序，开销较大。 8/29/2024第3章 关系数据库标准语言SQL v删除索引删除索引 在SQL语言中，使用DROP INDEX命令删除索引，其语法如下： DROP INDEX ； 【例3-11】删除为student表建立的索引stu_cluind。 DROP INDEX stu_cluind； 删除索引时，系统会同时从数据库中删去有关该索引的描述。 对于数据库系统而言，索引一经建立，一般不应随意删除。SQL没有提供修改索引的语句，对于一些在使用中证明不合适的索引，只能先删除后重建。8/29/2024第3章 关系数据库标准语言SQL 3.33.3数据查询数据查询 3.3.1 SELECT3.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子句都是可选的。8/29/2024第3章 关系数据库标准语言SQL 3.3.2 3.3.2 选择行和列选择行和列-选择和投影运算的实现选择和投影运算的实现 下面以人事工资管理系统的员工表employee和部门表dept为例介绍SELECT语句的使用方法。 Employee(emp_id,ename,job,mgr_id, Employee(emp_id,ename,job,mgr_id, workdate,sal, workdate,sal, comm,dept_id)comm,dept_id) 员工号 姓名 职位 主管 参加工作时间 月薪 岗位津贴 部门号 Dept(dept_idDept(dept_id，dnamedname， tel) 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);8/29/2024第3章 关系数据库标准语言SQL CREATE TABLE dept (dept_id CHAR(2) PRIMARY KEY, dname VARCHAR(12) , tel VARCHAR(20);v 选择运算的实现选择运算的实现查询一个表的所有行 在SELECT语句中只要没有WHERE子句，查询结果就包含了所有行。 【例3-12】查询表employee的所有员工的员工号和姓名。 SELECT emp_id,ename FROM employee;查询满足条件的某些行 查询满足条件的某些行，可以通过WHERE子句来实现。 【例3-13】查询月薪超过1800的员工姓名和月薪。 SELECT ename,sal FROM employee WHERE sal1800;8/29/2024第3章 关系数据库标准语言SQL 【例3-14】查询员工号为1001的员工姓名及部门号。 SELECT ename,dept_id FROM employee WHERE emp_id=1001;v 投影运算的实现投影运算的实现查询表的全部列 【例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;8/29/2024第3章 关系数据库标准语言SQL 查询表的部分列 【例3-17】查询全体员工的员工号，姓名和参加工作时间。 SELECT emp_id,ename,workdate FROM employee ;查询经过计算的值 SELECT子句的不仅可以是基本表的属性，也可以是表达式，包括算术表达式、字符串常量和函数等。 【例3-18】查询全体员工的姓名及年薪。 SELECT ename,sal*12 FROM employee ; 结果: ename sal*12 吴伟 15600 岳玲 13200 王斌 18000 徐欢 96008/29/2024第3章 关系数据库标准语言SQL 【例3-19】上例查询如果在中使用字符串常量，结果会更清晰。 SELECT ename,年薪：,sal*12 FROM employee ; 查询结果如下： ename ename 年薪：年薪： sal*12 sal*12 吴伟 年薪： 15600 岳玲 年薪： 13200 王斌 年薪： 18000 徐欢 年薪： 96008/29/2024第3章 关系数据库标准语言SQL 【例3-20】对【例3-18】查询可以通过指定别名来改变查询结果的列标题，这样也可以使结果更清晰。 SELECT ename AS 姓名 ,sal*12 AS 年薪 FROM employee ; 查询结果如下： 姓名姓名 年薪年薪 吴伟 15600 岳玲 13200 王斌 18000 徐欢 96008/29/2024第3章 关系数据库标准语言SQL 3.3.3 SQL3.3.3 SQL的运算符的运算符SQL语言使用的运算符包括算术运算符、比较运算符、逻辑运算符等。v 算术运算符算术运算符 算术运算符有4种：+、-、*、/v 比较运算符比较运算符基本比较运算符 共9种：=、=、！=或、 ！（不大于）、！1500 AND job= 部门主管 ;【例3-30】查询月薪超过1500的员工和所有部门主管的姓名及月薪。 SELECT ename,sal FROM employee WHERE sal1500 OR job= 部门主管 ; 8/29/2024第3章 关系数据库标准语言SQL 【例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) ;8/29/2024第3章 关系数据库标准语言SQL 3.3.4 3.3.4 对查询结果排序对查询结果排序 SQL语言中用ORDER BY子句实现对查询结果的排序，可以根据包含的一列或者多列的表达式进行ASC（升序）或DESC（降序）的排列，默认值是ASC。 【例3-33】查询所有员工的姓名及月薪，结果按月薪的降序排列。 SELECT ename,sal FROM employee ORDER BY sal DESC ; ORDER BY子句指定的排序列可以不只一个。8/29/2024第3章 关系数据库标准语言SQL 【例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子句一般放在查询语句的最后。8/29/2024第3章 关系数据库标准语言SQL 3.3.5 3.3.5 消除重复行消除重复行 基本表中不相同的行，经过对某些指定列进行投影运算后，可能会变成完全相同的行，显示结果不直观，这时需要用DISTINCT选项消除重复的行。 【例3-35】查询表employee中的所有职位。 SELECT DISTINCT job FROM employee ; 注意：在一个SELECT语句中DISTINCT只能出现一次，并且DISTINCT必须在所有列名之前，否则会发生语法错误。与DISTINCT选项含义相反的是ALL选项，在SELECT语句中使用ALL选项，表示结果重复的行也将显示。ALL选项是默认选项。 8/29/2024第3章 关系数据库标准语言SQL 3.3.6 SQL3.3.6 SQL的统计函数的统计函数 SQL语言提供了许多统计函数，主要的统计函数见表3-3。 统计函数 语义 COUNT(DISTINCT|ALL*) 统计表的记录个数 COUNT(DISTINCT|ALL) 统计一列中值不为NULL值的个数 SUM(DISTINCT|ALL) 计算一列值的总和（此列必须为数值型） AVG(DISTINCT|ALL) 计算一列值的平均值（此列必须为数值型） MAX(DISTINCT|ALL) 给出一列值中的最大值 MIN(DISTINCT|ALL) 给出一列值中的最小值 8/29/2024第3章 关系数据库标准语言SQL 【例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不为空值的行数。8/29/2024第3章 关系数据库标准语言SQL 【例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（*）外，所有的统计函数都不包括取值为空值的行。8/29/2024第3章 关系数据库标准语言SQL 3.3.7 3.3.7 数据分组数据分组 利用GROUP BY子句可以将查询结果按照一列或者多列分组，值相等的为一组。v 基于单列的分组基于单列的分组【例3-40】按部门号查询各部门的平均月薪。 SELECT dept_id,AVG(sal) FROM employee GROUP BY dept_id ;v 基于多列的分组基于多列的分组 GROUP BY子句还可以作用于多列上，此时的数据分组意义是分大组之后再分小组。 【例3-41】分各部门各职位统计月薪总额。 SELECT dept_id,job,sum(sal) FROM employee GROUP BY dept_id,job ; 说明：先按照部门号分组，部门号相同的组再按职位细分，最后部门号和职位完全相同的行才会分在一组，然后每组统计一个月薪总额。 8/29/2024第3章 关系数据库标准语言SQL v HAVINGHAVING子句子句 如果分组后还要根据一定条件对这些组进行筛选，则使用HAVING子句来实现。 【例3-42】按部门号查询各部门的平均月薪，要求只显示平均月薪在1000以上的部门编号和平均月薪。 SELECT dept_id,AVG(sal) FROM employee GROUP BY dept_id HAVING AVG(sal)1000; 注意: WHERE子句和HAVING子句有相似之处，即后面都跟指定条件；但是它们又有区别：前者直接用于SELECT子句中，作用于基本表或视图；而后者一定跟在GROUP BY子句后面，作用于分组。8/29/2024第3章 关系数据库标准语言SQL 3.3.8 3.3.8 连接查询连接查询v 连接查询的概念连接查询的概念 如果一个查询需要从两个或两个以上的数据表中获取数据时，则称之为连接查询。 连接查询包括广义笛卡尔积、等值连接、自然连接、外连接、内连接、左连接、右连接和自连接等。v 广义笛卡尔积广义笛卡尔积 广义笛卡尔积是不带连接条件的连接操作。两个表的广义笛卡尔积即是两个表中所有记录的交叉组合，其形成的结果集是所有连接种类中最大的。比如：表1有3条记录，表2有5条记录，则广义笛卡尔积产生3*5=15条记录。由于这种连接操作是不带条件的表的拼接，因此实际意义不大。8/29/2024第3章 关系数据库标准语言SQL v 等值连接等值连接 . = . 等值连接又称为内连接。若将查询结果的目标列中重复的列去掉，则称为自然连接，在实际中等值连接一般以自然连接的形式出现。 【例3-43】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。 SELECT emp_id,ename,dept.dept_id,dname,tel FROM employee,dept WHERE employee.dept_id=dept.dept_id ; 说明： (1)如果属性列名在参加连接的各表中是惟一的，可以省略表名前缀；如果属性列名是两个表共同的属性，则一定要加表名前缀。 (2)在书写连接查询时，为了简化，可以为表名取别名，别名应该简单。别名只在本次查询有效。 8/29/2024第3章 关系数据库标准语言SQL 【例3-44】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。 SELECT emp_id,ename,d.dept_id,dname,tel FROM employee e,dept d WHERE e.dept_id=d.dept_id ;v 不等连接不等连接 当连接条件中的比较运算符不为“=”时，此时的连接查询称为不等连接。8/29/2024第3章 关系数据库标准语言SQL 假设有JOB表（职位表），它包含两个属性：职位job_level和标准月薪std_sal，该表包含记录如下： job_level std_saljob_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_salename sal job_level std_sal吴伟 1300 部门经理 2200 岳玲 1100 部门经理 2200王斌 1500 部门经理 2200 徐欢 800 部门经理 2200岳玲 1100 出纳 1200 徐欢 800 出纳 12008/29/2024第3章 关系数据库标准语言SQL 3.4 3.4 数据更新数据更新 3.4.1 3.4.1 插入记录插入记录v插入单条记录插入单条记录 INSERT INTO (,) VALUES (,);注意：（1）属性列的个数与常量的个数要相等，且顺序一致，否则会产生语法错误。（2）在表结构定义中未说明为NOT NULL的属性列，如果没有出现在INTO子句后，这些列将取空值。已经说明为NOT NULL的属性列，则必须出现在INTO子句后。（3）如果INTO子句后没有指定任何列，则VALUES子句后面的常量个数必须与基本表中列的个数相等，且类型、顺序一致，否则会出语法错误或导致赋值不正确。 8/29/2024第3章 关系数据库标准语言SQL 【例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(2019/08/15),800,11); 8/29/2024第3章 关系数据库标准语言SQL v插入子查询的结果插入子查询的结果 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;8/29/2024第3章 关系数据库标准语言SQL 3.4.2 3.4.2 修改记录修改记录 SQL语言修改记录的语句为UPDATE。该语句有3种形式：修改单条记录、修改多条记录以及使用子查询修改记录。其一般语句格式为： UPDATE SET =,=, WHERE ; SQL语言的修改语句功能是将表中符合WHERE子句条件的记录找出，以表达式的值替代相应属性列的值。 8/29/2024第3章 关系数据库标准语言SQL v修改单条记录修改单条记录 【例3-50】修改1311号员工的月系薪为1700。 UPDATE employee SET sal=1700 WHERE emp_id=1311 ;v修改多条记录修改多条记录 【例3-51】所有员工月薪上调5%。 UPDATE employee SET sal=sal*1.05 ; 【例3-52】21号部门的所有员工取消岗位津贴。 UPDATE employee SET comm=0 WHERE dept_id=21 ;8/29/2024第3章 关系数据库标准语言SQL v用子查询修改记录用子查询修改记录 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子句有两个条件，它们是逻辑与关系。根据这两个条件查询找出的记录可能是多条。 8/29/2024第3章 关系数据库标准语言SQL 3.4.3 3.4.3 删除记录删除记录 DELETE语句一般格式： DELETE FROM WHERE ; DELETE语句的功能是：先按照WHERE子句中指定的条件范围将记录找出来，然后进行删除。v删除单条记录删除单条记录 【例3-54】删除员工号为1045的记录。 DELETE FROM employee WHERE emp_id=1045 ;8/29/2024第3章 关系数据库标准语言SQL v删除多条记录删除多条记录【例3-55】删除部门号为31的所有记录。 DELETE FROM employee WHERE dept_id=31 ;【例3-56】删除所有员工记录。 DELETE FROM employee ; 删除所有记录，是清空表中数据，因此不加WHERE子句。这类操作的执行应非常小心。8/29/2024第3章 关系数据库标准语言SQL v使用子查询删除使用子查询删除 SELECT语句同样也可以和DELETE语句联合使用。 【例3-57】删除产品开发部和事业推广部的所有员工的记录。 DELETE FROM employee WHERE dept_id IN ( SELECT dept_id FROM dept WHERE dept.dname= 产品开发部 OR dept.dname= 事业推广部) ; 因为SELECT子查询得到的记录一般不止一条，所以子查询前面的运算符不能为“=”，而应该用表示取值范围的“IN”。 8/29/2024第3章 关系数据库标准语言SQL 3.5 3.5 视图视图 3.5.1 3.5.1 视图的概念视图的概念 视图不包含任何数据，只是定义在一个或多个基表上或其他视图上，并且提供一种访问基表数据的方法。在物理磁盘上存储的有关视图的信息是：视图的名称和视图的定义。视图的所有数据来自基本表。因此，当基本表的数据发生变化时，对应视图中的查询出的数据也会随之变化。 视图一经定义，就可以象基本表一样执行查询、删除等操作，也可以在视图上定义新的视图，但对视图的更新操作则有一定限制。3.5.2 3.5.2 视图的作用视图的作用v可以满足不同用户的需求可以满足不同用户的需求 不同的用户对数据库操作有不同的需求，即使相同的数据也可能有不同的操作要求。一张基本表可能有很多属性列，利用视图，用户可以把自己感兴趣的属性列集中起来，放在一个视图中，此后用户可以将视图作为一张表来对待。8/29/2024第3章 关系数据库标准语言SQL v可以简化数据读取可以简化数据读取 查询数据时，通常要用SELECT语句编写复杂的连接、统计、函数等，以产生所需要的结果。使用视图，可以隐蔽这种复杂性。可以将经常用到的复杂查询的语句定义为视图，不必每次查询都写上复杂查询条件，这样就简化了用户的查询操作。v保证了基本表数据和应用程序的逻辑独立性保证了基本表数据和应用程序的逻辑独立性 当应用程序通过视图来访问数据时，视图实际上成为应用程序和基本表数据之间的桥梁。如果应用程序直接调用基本表，则一旦基本表的数据发生变化时，应用程序必须随之改动。而通过视图访问数据，则可以通过改变视图来适应基本表的变化，使应用程序不必作改变，保证了基本表数据和应用程序的逻辑独立性。v可以提供安全机制可以提供安全机制 利用视图可以限制数据访问。如果某个用户需要访问表中的某些列，但另一些属性列必须对该用户保密，则可以利用视图达到此目的，将视图建立在该用户需要访问的那些列上。8/29/2024第3章 关系数据库标准语言SQL 3.5.3 3.5.3 视图的建立视图的建立 SQL语言用CREATE VIEW语句来建立视图，其一般格式为： CREATE VIEW (，) AS WITH CHECK OPTION； 说明： (1)选项WITH CHECK OPTION确保用户只能查询和修改他们所看到的数据，强制所有在视图上使用的数据修改语句满足定义视图时的条件。 (2)组成视图的各属性列可以显式指定，也可以省略。如果省略不写，则组成视图的各属性列由子查询中SELECT子句的各目标列组成。 下列情形建立视图时必须显式指定属性列： 1)视图的某列不是原属性列，而是统计函数或者表达式。 2)多表连接时选出了两个或者多个同名列作为视图的属性列。 3)需要对视图中的某些列重新命名。 8/29/2024第3章 关系数据库标准语言SQL 【例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 ; 8/29/2024第3章 关系数据库标准语言SQL 【例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;8/29/2024第3章 关系数据库标准语言SQL 【例3-61】为各部门的平均月薪建立视图 CREATE VIEW salavg_vu(dno,sal_avg) AS SELECT dept_id,AVG(sal) FROM employee GROUP BY dept_id; 本例中因使用了统计函数，因而对视图的列作了显式指定。 视图还可以建立在多个表或视图之上。 【例3-62】为产品开发部的员工的员工号、姓名、月薪以及工龄建立视图。 CREATE VIEW dept_vu(eno,name,salary,wage) AS SELECT emp_id,ename ,sal ,2019-year(workdate) FROM employee,dept WHERE employee.dept_id= dept.dept_id AND dept.dname= 产品开发部 ;8/29/2024第3章 关系数据库标准语言SQL 3.6 3.6 案例案例2 2：活期储蓄管理系统数据库上的查询：活期储蓄管理系统数据库上的查询 v 示例数据示例数据 由案例1知，活期储蓄管理系统数据库包括：储户基本信息、储户动态信息、储蓄所、存取款4个基本表，根据案例1对关系结构的描述（也称为数据字典），这里给出活期储蓄管理系统数据库中基本表的示例数据。 储蓄所数据储蓄所数据 编号名称电话地址1001中国建行杭州分行秋涛支行(0571)88049082秋涛北街283号1002中国建行杭州分行武林支行(0571)68066080武林南路366号1208中国建行杭州分行下沙支行(0571)28800088下沙22号大街788号1303中国建行杭州分行滨江支行(0571)26880266滨江18号大街632号8/29/2024第3章 关系数据库标准语言SQL 储户基本信息数据储户基本信息数据 账号姓名电话地址开户行开户日期10020199张晓军(0571)22959618杭州10022019-11-2010020193李丹娜(021)86129618上海12082000-08-0610245082张岚(0571)66787968杭州10022019-06-0110204568王立群(021)89727982上海13032019-01-0810204567赵庆(0571)28008986杭州12082019-02-088/29/2024第3章 关系数据库标准语言SQL 储户动态信息数据储户动态信息数据 账号密码信誉存款额元状态10020199ASDZXC0286000110020193WERERT11678120110245082DFGASD13000000110204568SDFCVB035000110204567CVBGHJ112000001注：密码一般都是加密存储的 8/29/2024第3章 关系数据库标准语言SQL 账号储蓄所编号标志存取金额存取日期1002019310021500002019-09-281024508212080800002019-10-161002019310010100002019-12-101002019913031120002019-12-101002019310020200002019-12-161024508210020100002019-01-081020456712081200002019-02-081024508212081300002019-03-16 存取款数据存取款数据8/29/2024第3章 关系数据库标准语言SQL v数据查询数据查询 在上述给出的活期储蓄管理系统数据库表数据中，写出完成如下诸查询SQL命令。(1) 查询储户的姓名和存款额 Select 姓名,存款额(元) From 储户基本信息 b, 储户动态信息 d Where b.账号=d.账号 结果如下： 姓名姓名 存款额存款额( (元元) ) 李丹娜 1678120.0000 赵庆 1200000.0000 王立群 35000.0000 张岚 3000000.00008/29/2024第3章 关系数据库标准语言SQL (2) 查询账号为10245082的储户的存取款记录。 Select 编号,标志,金额(元),日期 From 存取款 Where 账号=10245082结果如下：编号编号 标志标志 金额金额( (元元) ) 日期日期 - 1002 0 10000.0000 2019-01-08 00:00:00.0001208 0 80000.0000 2019-10-16 00:00:00.0001208 1 30000.0000 2019-03-16 00:00:00.0008/29/2024第3章 关系数据库标准语言SQL (3)查询编号为1002的储蓄所2019.10-2019.10的存取款业务详细记录。Select *From 存取款Where 编号=1002 and 日期=2019-10-01 8:00 and 日期=2019-10-01 8:00结果如下： 账号 编号 标志 金额(元) 日期 - 10245082 1002 0 10000.0000 2019-01-08 00:00:00.00010020193 1002 0 20000.0000 2019-12-16 00:00:00.0008/29/2024第3章 关系数据库标准语言SQL (4)查询编号为1002的储蓄所的存取款业务汇总情况。Select 标志,sum(金额(元)From 存取款Where 编号=1002 group by 标志 结果如下： 标志标志 - 0 30000.00001 50000.00008/29/2024