第3章关系数据库标准语言SQL

1、第3章 关系数据库标准语言关系数据库标准语言SQL内容综述内容综述lSQL全称全称 ：Structured Query Language（结构化查询语言）（结构化查询语言）lSQL是关系数据库的标准语言。是关系数据库的标准语言。lSQL语言具有综合统一、高度非过程化、面向语言具有综合统一、高度非过程化、面向集合、简捷易学等优点。集合、简捷易学等优点。lSQL语言可以分为四大部分：数据定义、数据语言可以分为四大部分：数据定义、数据查询、数据操作、数据控制。查询、数据操作、数据控制。lSQL语言的数据查询功能非常丰富。语言的数据查询功能非常丰富。目录目录l3.1: SQL概述概述l3.2: 数据定

2、义数据定义l定义数据库、基本表、索引。定义数据库、基本表、索引。l3.3: 查询查询l使用使用SELECT语句实现单表查询、多表查询、嵌语句实现单表查询、多表查询、嵌套查询、集合查询。套查询、集合查询。l3.4: 数据更新数据更新l3.5: 视图视图l3.6: 数据控制数据控制l3.7: 嵌入式嵌入式SQL3.1: SQL概述概述lSQL全称全称 ：Structured Query Language（结构化（结构化查询语言）查询语言）l70年代中期，年代中期，IBM公司的公司的Sam Jose Research Laboratory（圣约瑟研究实验室）在研制（圣约瑟研究实验室）在研制Syste

3、m R关系数据库管理系统中研制并实现了关系数据库管理系统中研制并实现了SQL语言。语言。l1986年年10月，美国国家标准局月，美国国家标准局ANSI采用采用SQL作为关作为关系数据库管理系统的标准语言系数据库管理系统的标准语言(ANSIX3.135-1986)。1987年国际标准化组织年国际标准化组织(ISO)通过了这一标准。通过了这一标准。lSQL成为数据库领域的主流语言，很多成为数据库领域的主流语言，很多DBMS产品都产品都支持支持SQL语言。语言。SQL的特点：的特点：l综合统一综合统一 l高度非过程化高度非过程化l面向集合的操作方式面向集合的操作方式l操作的对象、结果是元组的集合操作

4、的对象、结果是元组的集合l以同一种语法结构提供两种使用方式以同一种语法结构提供两种使用方式l既是自含式语言，又是嵌入式语言既是自含式语言，又是嵌入式语言l语言简捷，易学易用语言简捷，易学易用l核心命核心命令简捷，接近口语，容易学习，容令简捷，接近口语，容易学习，容易使用易使用SQL的基本概念的基本概念lSQL包含包含4个部分：个部分：l数据定义语言数据定义语言DDL(Data Definition Language ) l数据查询语言数据查询语言DQL(Data Query Language)l数据操纵语言数据操纵语言DML(Data Manipulation Language)l数据控制语言

5、数据控制语言DCL(Data Control Language) lSQL支持关系数据库三级模式结构，其中内模支持关系数据库三级模式结构，其中内模式对应于视图、部分基本表，模式对应于基本式对应于视图、部分基本表，模式对应于基本表，内模式对应于存储文件。表，内模式对应于存储文件。3.2: 数据定义数据定义lSQL 的数据定义功能包括：定义表、定义视的数据定义功能包括：定义表、定义视图、定义索引。图、定义索引。l定义表：定义表：create table, drop table ,alter tablel定义视图：定义视图：create view, drop viewl定义索引：定义索引：creat

6、e index, drop index 使用使用CREATE TABLE 语句语句CREATE TABLE 表名表名 (属性列属性列 数据类型数据类型 );定义基本表的属性时需要指明各属性的数据类型、长度及与表相关的完整性约束条件。 SQL CREATE TABLE dept (deptno NUMERIC(2), dname VARCHAR(14), loc VARCHAR(13);Oracle支持的数据类型支持的数据类型DatatypeDescriptionVARCHAR2(size)Variable-length character dataCHAR(size) Fixed-length

7、 character dataNUMBER(p,s) Variable-length numeric dataDATE Date and time valuesLONG Variable-length character data up to 2 gigabytesCLOBSingle-byte character data up to 4 gigabytesRAW and LONG RAW Raw binary dataBLOBBinary data up to 4 gigabytesBFILEBinary data stored in an external file; up to 4 g

8、igabytesSQL Server2000支持的数据类型支持的数据类型Integers（整型）：（整型）：bit、int、smallint、bigint、tinyint。 Decimal（小数）：（小数）：decimal、numeric、float、real。Money and small money（货币）：（货币）：Money、small money Datetime and smalldatetime（日期）：（日期）：Datetime、smalldatetime。 Character Strings（非（非Unicode字符串）：字符串）：char、varchar、text。 Uni

9、code Character Strings（Unicode字符串）：字符串）：nchar、nvarchar、ntext。 Binary Strings（二进制串）：（二进制串）：binary、varbinary、image。其它：其它：cursor、sql variant,table,timestamp、uniqueidentifier。 定义完整性约束定义完整性约束CREATE TABLE 表名表名 (属性列属性列 数据类型数据类型 DEFAULT 表达式表达式属性列级属性列级_约束约束,表级表级_约束约束);CREATE TABLE emp( empno NUMERIC(4), enam

10、e VARCHAR(10), deptno NUMERIC(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO);NOT NULL 约束约束 EMPNO ENAME JOB . COMM DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 7782 CLARKMANAGER 10 7566 JONESMANAGER 20 .UNIQUE 约束约束 DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SA

11、LESCHICAGO 40 OPERATIONS BOSTON 50 SALESDETROIT 60BOSTON-SALES 定义约束定义约束SQL CREATE TABLE emp( 2 empno NUMERIC (4), 3enameVARCHAR(10) NOT NULL, 4jobVARCHAR(9), 5mgrNUMERIC (4), 6hiredate DATETIME, 7salNUMERIC(7,2), 8 commNUMERIC(7,2), 9deptnoNUMERIC(7,2) NOT NULL);SQL CREATE TABLE dept( 2 deptno NUMER

12、IC(2), 3dname VARCHAR(14), 4loc VARCHAR(13), 5CONSTRAINT dept_dname_uk UNIQUE(dname);PRIMARY KEY DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON 20 MARKETINGDALLAS FINANCENEW YORK-FOREIGN KEY DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEAR

13、CHDALLAS . EMPNO ENAME JOB . COMM DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 . 7571 FORDMANAGER . 200 9 7571 FORDMANAGER . 200- -定义约束定义约束SQL CREATE TABLE dept( 2 deptno NUMERIC(2), 3dname VARCHAR(14), 4loc VARCHAR(13), 5CONSTRAINT dept_dname_uk UNIQUE (dname), 6CONSTRAINT dept_deptno_pk PRIMA

14、RY KEY(deptno);SQL CREATE TABLE emp( 2 empno NUMERIC(4), 3enameVARCHAR(10) NOT NULL, 4jobVARCHAR(9), 5mgrNUMERIC(4), 6hiredate DATETIME, 7salNUMERIC(7,2), 8 commNUMERIC(7,2), 9deptnoNUMERIC(2,2) NOT NULL, 10CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 11REFERENCES dept (deptno);定义约束定义约束用户自定义的完整性约束规

15、则用户自定义的完整性约束规则lCHECK可用于定义用户自定义的完整性约可用于定义用户自定义的完整性约束规则，束规则，CHECK既可用于列约束，也可用既可用于列约束，也可用于表约束，其语法格式为：于表约束，其语法格式为：CONSTRAINT CHECK NOT FOR REPLICATION() 练习：练习：例1：建立一个名为example的基本表CREATE TABLE example(A_id smallint primary key, A_desc varchar(50) not null default new position title not formalized yet, Min

16、_a tinyint not null check (min_a = 10 ), Max_a tinyint not null check (max_a ALTER TABLE dept30 ADD (job VARCHAR(9);ALTER TABLEdept30ALTERcolumnename VARCHAR(15);练习练习:SQL ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp(empno);SQL ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk;S

17、QL ALTER TABLEdept DROP COLUMN HIREDATE;练习:l作如下修改：l向student表增加“入学时间”列，数据类型为日期型l将年龄的数据类型改为短整型使用使用Drop Table语句语句SQL DROP TABLE dept30;DROP TABLE student随着时间的变化，有些基本表无用了，可将其删除。删除某基本表后，随着时间的变化，有些基本表无用了，可将其删除。删除某基本表后，该表中数据及表结构将从数据库中彻底删除，表相关的对象如索引、该表中数据及表结构将从数据库中彻底删除，表相关的对象如索引、视图、参照关系等也将同时删除或无法再使用，因此执行删除操

18、作一视图、参照关系等也将同时删除或无法再使用，因此执行删除操作一定要格外小心。删除基本表命令的一般格式为：定要格外小心。删除基本表命令的一般格式为：l注意：删除表需要相应的操作权限，一般只删除自己建立的无用表。注意：删除表需要相应的操作权限，一般只删除自己建立的无用表。 建立和删除索引建立和删除索引(1)l建立索引是加快查询速度的有效手段。建立索引是加快查询速度的有效手段。l索引可以建立在表的一列或多列上。索引可以建立在表的一列或多列上。l系统在存取数据时会自动选择合适的索引，用户系统在存取数据时会自动选择合适的索引，用户不必选择。不必选择。l索引分为聚集索引和非聚集索引。一个基本表上索引分为

19、聚集索引和非聚集索引。一个基本表上最多只能建立一个聚集索引。最多只能建立一个聚集索引。索引的概念索引的概念: : 数据库中的索引是为了加速对表中元组（或记录）的检索数据库中的索引是为了加速对表中元组（或记录）的检索而创建的一种分散存储结构而创建的一种分散存储结构( (如如B B+ +树数据结构树数据结构) )，它实际，它实际上是记录的关键字与其相应地址的对应表上是记录的关键字与其相应地址的对应表 。使用使用Create Index语句语句CREATE INDEX UNIQUE / CLUSTERED 索引名索引名 ON 表名表名 (属性列属性列.);Create clustered index

20、 stusname ON student(Sname)Create unique index snocno on SC(Sno ASC,Cno DESC)Drop index stusname使用使用Create Index语句语句例例9 为学生为学生-课程数据库中的课程数据库中的S、C、SC三个表三个表建立索引。其中建立索引。其中S表按学号升序建惟一索引，表按学号升序建惟一索引，C表按课程号降序建立聚簇索引，表按课程号降序建立聚簇索引，SNO、CNO表按学号升序和课程号降序建非聚簇索表按学号升序和课程号降序建非聚簇索引。引。l CREATE UNIQUE INDEX S_SNO ON S(S

21、NO)l CREATE CLUSTERED INDEX C_CNO ON C(CNO DESC)l CREATE NONCLUSTERED INDEX SC_SNO_CNO ON SC(SNO ASC,CNO DESC)3.3: 查询查询l查询是数据库管理的核心操作。查询是数据库管理的核心操作。SQL语言提供语言提供了了select语句进行数据库的查询。语句进行数据库的查询。lSelect语句的一般格式：语句的一般格式：SELECT ALL|DISTINCT. FROM , .WHERE GROUP BY HAVING ORDER BY ASC|DESCSELECTSELECT语句可以实现语句

22、可以实现: :1.1.单表查询单表查询SELECTDISTINCT *, 目标列表达式目标列表达式 别名别名,.FROM表名表名;lSELECT 指明输出什么列lFROM 指明从哪个表查询基本的SELECT 语句: 选择全部列选择全部列 DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL SELECT * FROM dept; 选择指定的列选择指定的列 DEPTNO LOC- - 10 NEW YORK 20 DALLAS 30 CHIC

23、AGO 40 BOSTONSQL SELECT deptno, locFROM dept;例例: EMP: EMP表表 使用算术运算符使用算术运算符 SQL SELECT ename, sal, sal+300 FROM emp;ENAME SAL SAL+300- - -KING 5000 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900.14 rows selected. 定义列的别名定义列的别名 SQL SELECT ename AS name, sal salary FROM

24、 emp;NAME SALARY- -.SQL SELECT ename Name, sal*12 Annual Salary FROM emp; Name Annual Salary- -.使用字符连接运算符使用字符连接运算符SQL SELECTename + job AS Employees FROM emp;Employees-KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN.14 rows selected.SQL Server2000使用+进行字符连接运算ORACLE使用|进行字符

25、连接运算使用字符连接运算符使用字符连接运算符Employee Details-KING is a PRESIDENTBLAKE is a MANAGERCLARK is a MANAGERJONES is a MANAGERMARTIN is a SALESMAN.14 rows selected. SQL SELECT ename + + is a + +job AS Employee DetailsFROM emp; 消除重复行消除重复行SQL SELECT deptno FROM emp; DEPTNO- 10 30 10 20.14 rows selected. l默认情况下的选择操作

26、产生了重复行 消除重复行消除重复行SQL SELECT DISTINCT deptno FROM emp; DEPTNO- 10 20 30 l使用DISTINCT参数消除重复行练习练习:l如何对SQL Server2000的实例数据库Northwind实现以下查询:1：查询所有订单的详细信息2：查询所有订单的订购日期Select from ordersSelect orderid,orderdate from orders练习练习:3：查询所有订单的订购日期及预计的交货日期（假定通常交货期为30天）Select orderid,orderdate,orderdate+30 from orde

27、rsSelect orderid,orderdate,预计的发货日期预计的发货日期, orderdate+30 from ordersSelect orderid,orderdate+30 as 预计的发货日期预计的发货日期 from orders使用使用SELECT语句实现选择操作语句实现选择操作 EMPNO ENAME JOB . DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 7782 CLARKMANAGER 10 7566 JONESMANAGER 20 . EMPNO ENAME JOB . DEPTNO 7839 KINGPRE

28、SIDENT 10 7782 CLARKMANAGER 10 7934 MILLER CLERK 10使用使用SELECT语句实现选择操作语句实现选择操作查询条件查询条件谓词谓词比 较 运 算比 较 运 算符符=,=,=,!=,!,! SELECT ename, job, deptno FROM emp WHERE job=CLERK;ENAME JOB DEPTNO- - -JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10使用比较运算符使用比较运算符SQL SELECT ename, sal, comm FROM emp WH

29、ERE sal SELECTename, sal FROM emp WHEREsal BETWEEN 1000 AND 1500;LowerlimitHigherlimit使用使用 IN 运算符运算符SQL SELECTempno, ename, sal, mgr FROM emp WHEREmgr IN (7902, 7566, 7788); EMPNO ENAME SAL MGR- - - - 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788使用使用 LIKE 运算符运算符字符

30、匹配字符匹配 l谓词谓词LIKE可以用来进行字符串的匹配。其一般语法格可以用来进行字符串的匹配。其一般语法格式如下：式如下：l属性名属性名 NOT LIKE ESCAPE l 其含义是查找指定的属性列值与其含义是查找指定的属性列值与相匹配的相匹配的元组。元组。可以是一个完整的字符串，也可以含可以是一个完整的字符串，也可以含有通配符有通配符%、_、 与与等等 l 具体含义见下表：具体含义见下表：使用使用 LIKE 运算符运算符通 配通 配符符描述描述示例示例%（ 百（ 百分号）分号）代表零个或更多字符的任代表零个或更多字符的任意字符串。意字符串。WHERE title LIKE %compute

31、r% 将查找处将查找处于书名任意位置的包含单词于书名任意位置的包含单词 computer 的所有的所有书名。书名。_（ 下（ 下划线）划线）代表任何单个字符（长度代表任何单个字符（长度可以为可以为0）。）。WHERE au_fname LIKE _ean 将查找以将查找以 ean 结尾的所有结尾的所有 4 个字母的名字（个字母的名字（Dean、Sean 等）。等）。 （ 中（ 中扩号）扩号）指定范围指定范围 (a-f) 或集合或集合 (abcdef) 中的任何单个中的任何单个字符。字符。WHERE au_lname LIKE C-Parsen 将查找以将查找以arsen 结尾且以介于结尾且以介

32、于 C 与与 P 之间的任何单个字符开始的作者之间的任何单个字符开始的作者姓氏，例如，姓氏，例如，Carsen、Larsen、Karsen 等。等。不属于指定范围不属于指定范围(a-f)或集合或集合 (abcdef) 的任何单个字符。的任何单个字符。WHERE au_lname LIKE del% 将查找以将查找以 de 开开始且其后的字母不为始且其后的字母不为 l 的所有作者的姓氏。的所有作者的姓氏。.l使用 ESCAPE 进行转义后，可以查找 “%”或“_”。SQL SELECTename FROMemp WHEREename LIKE _A%;ENAME- JAMES WARD使用使用

33、LIKE 运算符运算符SQL SELECTename FROMemp WHEREename LIKE _A% escape ;使用使用 IS NULL 运算符运算符l检测是否为空（NULL）值SQL SELECT ename, mgr FROM emp WHERE mgr IS NULL;ENAME MGR- -KING使用使用 AND 运算符运算符SQL SELECT empno, ename, job, sal FROM emp WHERE sal=1100 AND job=CLERK; EMPNO ENAME JOB SAL- - - - 7876 ADAMS CLERK 1100 79

34、34 MILLER CLERK 1300SQL SELECT empno, ename, job, sal FROM emp WHERE sal=1100 OR job=CLERK; EMPNO ENAME JOB SAL- - - - 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250.14 rows selected.使用使用 OR 运算符运算符SQL SELECT ename, job FROM

35、emp WHERE job NOT IN (CLERK,MANAGER,ANALYST);ENAME JOB- -KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN使用使用 NOT 运算符运算符在优先顺序上，比较运算符最优先，逻辑运算符中在优先顺序上，比较运算符最优先，逻辑运算符中NOT优先级第一，优先级第一，AND其次，其次，OR最不优先。最不优先。ENAME JOB SAL- - -KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600T

36、URNER SALESMAN 1500WARD SALESMAN 1250SQL SELECT ename, job, sal FROM emp WHERE job=SALESMAN OR job=PRESIDENT AND sal1500;可以使用括号来定义优先顺序。可以使用括号来定义优先顺序。ENAME JOB SAL- - -KING PRESIDENT 5000ALLEN SALESMAN 1600SQL SELECT ename, job, sal FROM emp WHERE (job=SALESMAN OR job=PRESIDENT) AND sal1500;练习：练习：l如何

37、对SQL Server2000的实例数据库Northwind实现以下查询: 6：查询所有订单中已经交货的订单的详细信息 7：查询所有国家为“UK”的客户的详细信息Select from orders where shippeddate SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate;ENAME JOB DEPTNO HIREDATE- - - -SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81.14 rows selected.按降序排列结果按降序排列结果SQL

38、SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE- - - -ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81KING PRESIDENT 10 17-NOV-81MARTIN SALESMAN 30 28-SEP-81.14 rows selected.按结果

39、列的别名排序按结果列的别名排序SQL SELECT empno, ename, sal*12 annsal FROM emp ORDER BY annsal; EMPNO ENAME ANNSAL- - - 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000.14 rows selected.按多列排序按多列排序SQL SELECT ename, deptno, sal FROM emp ORDER BY d

40、eptno, sal DESC;ENAME DEPTNO SAL- - -KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000.14 rows selected.使用集函数使用集函数l集函数作用在一组元组上 DEPTNO SAL- - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL)- 5000使用集函数使用集函数SELECT目标列表达式目标列表达式, 集函数

41、集函数(列名列名)FROM表名表名WHERE条件表达式条件表达式ORDER BY列名列名;集函数包括:AVG 、COUNT 、 MAX 、MIN 、STDDEV 、SUM 、VARIANCEAVG 和和 SUM适用于数值型数据适用于数值型数据 AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)- - - - 1400 1600 1250 5600SQL SELECTAVG(sal), MAX(sal), MIN(sal), SUM(sal) FROMemp WHEREjob LIKE SALES%; MIN 和和 MAX 适用于不同类型的数据适用于不同类型的数据SQL SE

42、LECTMIN(hiredate), MAX(hiredate) FROMemp;MIN(HIRED MAX(HIRED- -17-DEC-80 12-JAN-83 COUNT 函数用于计数函数用于计数 COUNT(*)- 6SQL SELECTCOUNT(*) FROMemp WHEREdeptno = 30;SQL SELECTCOUNT(comm) FROMemp WHEREdeptno = 30;COUNT(COMM)- 4忽略NULL值创建数据分组创建数据分组 DEPTNO SAL- - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 2

43、0 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667GROUP BY 短语短语SELECT目标列表达式目标列表达式, 集函数集函数(列名列名)FROM表名表名WHERE条件表达式条件表达式GROUP BY分组条件表达式分组条件表达式ORDER BY列名列名;GROUP BY 短句将元组的集合分成了小组。使用使用 GROUP BY 短语短语 lGROUP BY 后的列不一定在SELECT中输出，SQL SELECT AV

44、G(sal) FROM emp GROUP BY deptno; AVG(SAL)- 2916.6667 21751566.6667使用使用 GROUP BY 短语短语lSELECT后的列除非使用了集函数，否则必须包括在GROUP BY短语中SQL SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; DEPTNO AVG(SAL)- - 10 2916.6667 20 2175 30 1566.6667SQL SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job; DEPTNO JO

45、B SUM(SAL)- - - 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900.9 rows selected.按多列分组按多列分组按多列分组按多列分组 DEPTNO JOB SAL- - - 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGE

46、R 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250JOB SUM(SAL)- -CLERK 1300MANAGER 2450PRESIDENT 5000ANALYST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600DEPTNO-101010202020303030对分组的结果进行筛选对分组的结果进行筛选 DEPTNO SAL- - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3

47、000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO MAX(SAL)- - 10 5000 20 3000HAVING 短语短语l使用 HAVING 短语对分组结果进行筛选lHAVING短语专门作用于分组，选择满足条件的分组分组lWHERE短语作用于整个基本表，选择满足条件的元元组，不能用于选择分组。SELECT目标列表达式目标列表达式, 集函数集函数FROM表名表名WHERE条件表达式条件表达式GROUP BY分组条件表达式分组条件表达式HAVING条件表达式条件表达式ORDER BY列名列名;使用使用HAVIN

48、G 短语短语SQL SELECT deptno, max(sal) FROM emp GROUP BY deptno HAVING max(sal)2900; DEPTNO MAX(SAL)- - 10 5000 20 3000使用使用HAVING 短语短语SQL SELECT job, SUM(sal) PAYROLL FROM emp WHERE job NOT LIKE SALES% GROUP BY job HAVING SUM(sal)5000 ORDER BY SUM(sal);JOB PAYROLL- -ANALYST 6000MANAGER 8275嵌套的集函数嵌套的集函数SQ

49、L SELECT max(avg(sal) 2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL)- 2916.6667练习：练习：12：查询客户名以A开头的客户在1996年发出的所有订单，按订单号排序输出13：查询每个供应商供应的所有产品的平均价格Select * from orders where customerid like A% and year(orderdate)=1996 order by orderidSelect supplierid,avg(unitprice) from productsgroup by supllierid EMPNO DE

50、PTNO LOC- - - 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO.14 rows selected. EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698 BLAKE. 30 . 7934 MILLER . 10DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOST

51、ON2.2.连接查询连接查询( (涉及多个表的查询涉及多个表的查询) )连接操作是以两个关系为输入，连接操作是以两个关系为输入，将另一个关系作为结果返回将另一个关系作为结果返回.n 内连接内连接l 它是条件连接，要求参加连接的关系中至它是条件连接，要求参加连接的关系中至少有一少有一 对元组满足连接要求，才能生成连接对元组满足连接要求，才能生成连接关系；关系；l 可以是等值连接，也可以是不等值连接；可以是等值连接，也可以是不等值连接；l 新的关系的属性集是参加连接的关系的属新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性。性的组合，不去除重复属性。用用SELECT语句实现连接操作语句

52、实现连接操作l使用WHERE 短句来说明连接条件l对表与表之间的公共列，需要在列名前加上表名加以标识SELECT表名表名1.目标列目标列 , 表名表名2.目标列目标列FROM表名表名1, 表名表名2WHERE表名表名1.列列 = 表名表名2. 列列; 等值连接等值连接 EMPNO ENAME DEPTNO- - - 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 73

53、69 SMITH 20.14 rows selected. DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected. 等值连接的实现等值连接的实现SQL SELECT em

54、p.empno, emp.ename, emp.deptno, 2dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC- - - - - 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS.14 rows selected.使用表的别名简化工作使用表的别名简化工作SQL SELECT emp.empno,

55、 emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;SQL SELECT e.empno, e.ename, e.deptno, 2 d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno=d.deptno;非等值连接非等值连接 EMPNO ENAME SAL- - - 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654

56、MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950.14 rows selected.GRADE LOSAL HISAL- - -1 70012002 120114003 140120004200130005 30019999非等值连接的实现非等值连接的实现ENAME SAL GRADE- - -JAMES 950 1SMITH 800 1ADAMS 1100 1.14 rows selected.SQL SELECT e.ename, e.sal, s.grade 2FROMemp e, salgrade s 3WHERE

57、e.sal BETWEEN s.losal AND s.hisal;笛卡尔积：笛卡尔积：ENAME DNAME- -KINGACCOUNTINGBLAKE ACCOUNTING .KINGRESEARCHBLAKE RESEARCH.56 rows selected. EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698 BLAKE. 30 . 7934 MILLER . 10WHERE 短句省略短句省略或无效时，表与表或无效时，表与表进行笛卡尔积运算。进行笛卡尔积运算。DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YOR

58、K 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON两个以上的表的连接两个以上的表的连接NAMECUSTID-JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS SPORTS 107.9 rows selected. CUSTID ORDID- - 101 610 102 611 104 612 106 601 102 602 106 604 106 605. 21 rows selected. ORD

59、ID ITEMID- - 610 3 611 1 612 1 601 1 602 1.64 rows selected. n 外连接外连接u 它是条件连接，且条件是必需的。它是条件连接，且条件是必需的。u 若一个关系中的元组在另一个关系中没有相匹若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上另一个关系的其他属性位置上放上Null，而不是像，而不是像内连接那样被忽略。内连接那样被忽略。u新的关系的属性集是参加连接的关系的属性的组新的关系的属性集是参加连接的关系的属性的组合，不合，不 去

60、除重复属性去除重复属性u有左外连接、右外连接、全外连接有左外连接、右外连接、全外连接数据库的内连接和外连接：数据库的内连接和外连接： 有两个表有两个表A和表和表B 举例如下举例如下：表表A表表B1.内连接：利用内连接可获取两表的公共部分的记录内连接：利用内连接可获取两表的公共部分的记录 语句如下：Select * from A JOIN B ON A.Aid=B.Bnameid select * from A, B where A.Aid=B.Bnameid 或者或者运行结果运行结果表表A表表B内连接内连接2.外连接：外连接分为两种，一种是左连接（外连接：外连接分为两种，一种是左连接（Left