课件3关系数据库语言sql

1、第三章关系数据库标准语言SQL2022/8/26数据库系统原理2 本章主要介绍标准SQL的数据定义、数据查询、数据更新等语句的基本用法、视图创建和使用、嵌入式SQL的语句形式，以及游标的定义、打开、推进、关闭。本章主要内容2022/8/26数据库系统原理3结构化查询语言SQL（structured query language）是一种介于关系代数和关系演算之间的语言，其功能包括数据定义、数据操纵、数据查询和数据控制四个方面，是关系数据库系统的国际标准。SQL86：第一个SQL标准，ANSI（American National Standards Institute，美国国家标准协会）。SQL8

2、9：1989年作了少许改进SQL92：也被称作SQL2，ANSI和ISO（International Organization for Standardization，国际标准化组织）作了较大改动和完善，这是目前绝大多数商用RDBMS支持的版本。SQL99：也被称作SQL3，是在SQL92的基础上扩展而成的。SQL2003：也称为SQL4，扩展了对XML的支持。此后又依次推出了SQL2006和SQL2008。2022/8/26数据库系统原理4目录3.1 银行数据库 3.2 数据定义 3.3 数据查询 3.4 数据操纵 3.5 视图 3.6 嵌入式SQL 2022/8/26数据库系统原理53.1

3、 银行数据库 支行：Branch（BName，BCity，Assets）客户：Customer（CId，CName，CStreet，CCity）存款账户：Account（ANo， BName，balance）贷款账户：Loan（LNo，BName，amount）储蓄：Deposits（CId，ANo）借贷：Borrows（CId，LNo）2022/8/26数据库系统原理63.2 数据定义 3.2.1 数据库的创建 3.2.2 表的创建 3.2.3 索引的创建 2022/8/26数据库系统原理73.2.1 数据库的创建 数据库是一个存储空间，用于存放数据库中的数据库对象，包括：表、视图、索引、存

4、储过程、触发器、安全控制信息以及其他对象等。 SQL Server 2000将数据库映射为一组操作系统文件，数据和日志信息分别存储在不同的文件中。 SQL Server数据库中有三种物理文件：主数据文件、辅助数据文件和日志文件。 2022/8/26数据库系统原理81、主数据文件 主数据文件也称为基本数据文件，是数据库的起点，包含了数据库的初始信息，并记录数据库还拥有哪些文件。每个数据库有且只能有一个主数据文件。主数据文件是数据库必须的文件。其文件扩展名是*.mdf。 2022/8/26数据库系统原理92、辅助数据文件 辅助数据库文件又称从属文件，用于保存所有主数据文件中容纳不下的数据，不是数据

5、库必须的文件。一个数据库中可以没有、也可以有一个或多个辅助数据文件。其文件扩展名是*.ndf。 2022/8/26数据库系统原理103、日志文件 是数据库故障恢复的重要手段和方法，用于记录对数据库的各种操作及所涉及的相关数据。每个数据库至少拥有一个日志文件，日志文件的大小最少是1MB。其文件扩展名是*.ldf。 2022/8/26数据库系统原理11一、文件组为了有助于数据布局和管理任务，可将多个数据文件集合起来形成一个整体，这个整体就是文件组，每个组有一个组名。一个数据文件只能存在于一个文件组中，日志文件不属于任何文件组。SQL Server 2000的文件组有三种类型：主文件组、用户定义文件

6、组、默认文件组 2022/8/26数据库系统原理121、主文件组 主文件组中包含着主数据文件以及相关内容。在创建数据库时，系统会自动创建主文件组，并将主数据文件及系统表的所有页都分配到主要文件组中。 2022/8/26数据库系统原理132、用户定义文件组 由用户通过SQL Server企业管理器创建的文件组称为用户定义文件组（User-Defined Filegroup）。该组中包含逻辑上一体的数据文件和相关信息，大多数数据库只需要一个文件组和一个日志文件就可很好的运行。但如果库中的文件很多，就要创建用户定义文件组，以便管理。使用时，可以通过企业管理器或Transact-SQL语句中的file

7、group子句指定需要的用户定义文件组。 2022/8/26数据库系统原理143、默认文件组 在每个数据库中，同一时间只能有一个文件组是默认文件组（Default Filegroup）。当进行数据库操作时，如果不指定文件组，则系统自动选择默认文件组。可使用Transact-SQL语句中的alter database语句指定数据库的默认文件组。在不特别指定的情况下，系统将主要文件组认定为默认文件组。 2022/8/26数据库系统原理15二、数据库的物理存储结构 数据文件的结构按照层次可以划分为页面（page）和扩展盘区（extent）。 2022/8/26数据库系统原理161、页面 是数据存储的

8、基本单位。页的尺寸是8KB。数据库中的每个页面中只能存储一种数据库对象的数据。一个页面可以存放多条记录，但一条记录不能跨页存放，即SQL Server 2000中一条记录不能超过8060B。数据文件中的页有8种类型：数据页、索引页、文本/图像页、全局分配映射表页、页空闲空间、索引分配映射表页、大容量更改映射表页和差异更改映射表页。 2022/8/26数据库系统原理172、扩展盘区 由8个连续页面组成的数据结构称为一个盘区。是SQL Server分配给表和索引的基本单位。扩展盘区可分为统一扩展盘区和混合扩展盘区。统一扩展盘区由一个数据库对象所有。混合扩展盘区可以为多个数据库所有，即其中最多可以放

9、8种数据库对象。 2022/8/26数据库系统原理18注意 一个数据库是由文件组成的，文件是由盘区组成的，而盘区是由页面组成的。 所有的SQL Server数据文件都会拥有两个文件名：逻辑文件名和物理文件名。逻辑文件名是在Transact-SQL语句中引用物理文件时所使用的名称，逻辑文件名必须是唯一的。物理文件名是包括路径在内的物理文件名。 2022/8/26数据库系统原理19三、创建数据库 CREATE DATABASE database_nameON ，nLOG ON，n语法说明：database_name：新创建的数据库的名称。ON：用于定义存放“数据”的空间。LOG ON：用于定义存储

10、“日志”的空间。在filespec中的内容包括：文件逻辑名（NAME）；操作系统下的物理文件名（FILENAME）；文件的初始大小（SIZE）；文件大小的最大值（MAXSIZE）；文件大小的递增量等（FILEGROWTH）。 2022/8/26数据库系统原理20例 创建银行Bank数据库CREATE DATABASE BankON ( NAME=Bank_dat, FILENAME=c:mssqldatabankdat.mdf, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5MB )LOG ON ( NAME=Bank_log, FILENAME=c:mssqlda

11、tabanklog.ldf, SIZE=5MB, MAXSIZE=25MB, FILEGROWTH=5MB )2022/8/26数据库系统原理21四、删除数据库 DROP DATABASE database_name语法说明：master、tempdb、model、msdb系统数据库不能删除。正在使用的数据库不能删除。 2022/8/26数据库系统原理223.2.2 表的创建 数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。要对表中数据进行操纵（查询、插入、删除或修改表中的数据），应建立表的结构，建表的同时还应定义好相关的完整性约束。 2022/8/26数据库系统

12、原理23SQL Server2000数据类型 整数数据类型 浮点数据类型 二进制数据类型 字符数据类型 逻辑型日期/时间数据类型 货币数据类型 文本和图像型 其他数据类型 用户自定义数据类型 2022/8/26数据库系统原理24一、整数数据类型 Tinyint：可以存储从0到255之间的所有正整数。所占存储空间大小为1个字节。 Smallint：可存储从215到2151之间的所有正负整数。所占存储空间大小为2个字节，其中1位表示整数值的正负号，其它15位表示整数值的长度和大小。Int：可存储从231到2311之间的所有正负整数。所占存储空间大小为4个字节，其中1位表示整数值的正负号，其它31位

13、表示整数值的长度和大小。Bigint：可存储从263到2631之间的所有正负整数。所占存储空间大小为8个字节。 2022/8/26数据库系统原理25二、浮点数据类型 real：可精确到第7位小数，其范围为从3.40E+38到3.40E+38，占用4个字节的存储空间。Float：可精确到第15位小数，其范围为从1.79E+308到1.79E+308，占用8个字节的存储空间。Decimal：可以提供小数所需要的实际存储空间，可以用2到17个字节来存储从10381到10381之间的数值。Numeric：与decimal数据类型完全相同。 2022/8/26数据库系统原理26三、二进制数据类型 Bin

14、ary(n)：固定长度为n个字节二进制数据，最大长度可达8KB。n取值为1到8000。Varbinary(n)：n个字节可变长二进制数据，不能超过8KB。 2022/8/26数据库系统原理27四、字符数据类型 Char(n)：使用固定长度来存储字符，每个字符和符号占一个字节的存储空间。n表示所有字符所占的存储空间，n的取值为1到8000，默认值为1。Varchar(n)：与char类型相似，n的取值为1到8000。Varchar数据类型具有变动长度的特性，存储长度为实际数值长度。Nchar(n)：与char 类型相似，n的取值为1到4000。NCHAR类型采用UNICODE标准字符集（Char

15、acterSet）。UNICODE标准规定每个字符占用两个字节的存储空间，存储空间是：字符数2（字节）。Nvarchar(n)：与varchar类型相似，n的取值为1到4000。nvarchar数据类型采用UNICODE标准字符集。 2022/8/26数据库系统原理28五、逻辑型 bit：bit数据类型占用1个字节的存储空间，其值为0或1。如果输入0或1以外的值，将被视为1。 2022/8/26数据库系统原理29六、日期/时间数据类型 Datetime：存储从1753年1月1日零时起到9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达三百分之一秒，即3.33毫秒。所占存

16、储空间为8个字节。Smalldatetime：存储从1900年1月1日到2079年6月6日，但精度较低，只能精确到分钟。所占存储空间为4个字节。 2022/8/26数据库系统原理30七、货币数据类型 Money：有4位小数的DECIMAL值，其取值从263到2631，数据精度为万分之一货币单位。所占存储空间为8个字节。Smallmoney：其取值从214,748.3648到+214,748.3647，所占存储空间为4个字节。 2022/8/26数据库系统原理31八、文本和图像型 Text：专门用于存储数量庞大的变长字符数据。最大长度可达2311个字符。Ntext：用于存储可变长度的unicod

17、e数据，最多可存储2301个unicode数据。Image：可用于存储字节数超过8KB的数据，如Microsoft Word文档、Microsoft Excel图表以及图像数据等，其最大长度为2311个字节。 2022/8/26数据库系统原理32九、其他数据类型 Cursor：是游标型数据类型，用于创建游标变量或定义存储过程的输出参数。Sql_variant：可以存储除文本、图形数据（text、ntext、image）和timestamp类型数据外的其它任何合法的SQL Server数据。Table：用于存储对表或视图处理后的结果集。Timestamp：相当于binary(8)或varbina

18、ry(8)，当定义的列在更新或插入数据行时，此列的值会被自动更新，每个数据库表中只能有一个TIMESTAMP数据列。Uniqueidentifier：存储一个16位的二进制数字，由NEWID函数产生的惟一的编码。 2022/8/26数据库系统原理33十、用户自定义数据类型 用户定义数据类型基于SQL Server 2000中的系统数据类型。当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型。 2022/8/26数据库系统原理343.2.2 表的创建 数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。要对表

19、中数据进行操纵（查询、插入、删除或修改表中的数据），应建立表的结构，建表的同时还应定义好相关的完整性约束。 2022/8/26数据库系统原理35一、创建表 CREATE TABLE database_name. owner .| owner. table_name（column_definition|table_constraint，n ）语法说明：table_name：新建的表的名称。在column_definition中的主要内容包括：列名、列类型、是否允许空值、初值和列级约束等。table_constraint指的是设定表级约束，包括主码约束、唯一约束和外码约束、检查等。 2022/8/

20、26数据库系统原理36例 创建银行数据库中的六个表结构CREATE TABLE Bank.dbo.Branch（BName CHAR（15）PRIMARY KEY,BCity VARCHAR（20）NOT NULL,Assets DECIMAL（20,2）NOT NULL）CREATE TABLE Bank.dbo.Customer（CId CHAR（7）PRIMARY KEY NONCLUSTERED,CName CHAR（8）NOT NULL,CStreet VARCHAR（20）NULL,CCity VARCHAR（20）NULL）CREATE TABLE Bank.dbo.Accoun

21、t（ANo CHAR（4）PRIMARY KEY,BName CHAR（15）NOT NULL FOREIGN KEY REFERENCES Branch,balance DECIMAL（10,2）NOT NULL）2022/8/26数据库系统原理37例 创建银行数据库中的六个表结构CREATE TABLE Bank.dbo.Loan（LNo CHAR（4）PRIMARY KEY,BName CHAR（15）NOT NULL FOREIGN KEY REFERENCES Branch,amount DECIMAL（10,2）NOT NULL）CREATE TABLE Bank.dbo.Depo

22、sits（CId CHAR（7）NOT NULL FOREIGN KEY REFERENCES Customer,ANo CHAR（4）NOT NULL FOREIGN KEY REFERENCES AccountPRIMARY KEY（CId,ANo）CREATE TABLE Bank.dbo.Borrows（CId CHAR（7）NOT NULL,LNo CHAR（4）NOT NULLPRIMARY KEY（CId,LNo）2022/8/26数据库系统原理38二、修改表 ALTER TABLE table_name ALTER COLUMN column_name new_data_typ

23、e ( precision , scale ) NULL | NOT NULL | ADD | ADD ,n | DROP CONSTRAINT constraint_name | COLUMN column ,n 2022/8/26数据库系统原理39修改表（续）语法说明：ALTER COLUMN用于修改表中原有列，可修改内容包括数据类型、大小和可空性。ADD 用于增加新的列。ADD 用于增加新的表级约束。DROP CONSTRAINT constraint_name | COLUMN column 用于删除约束或原有的列。 2022/8/26数据库系统原理40例子例 修改客户Customer

24、表的结构，改变说明列的数据类型。ALTER TABLE Bank.dbo.Customer ALTER COLUMN CName CHAR(10)ALTER TABLE Bank.dbo.Customer ALTER COLUMN CCity CHAR(20)例 修改借贷Borrows表结构，新增外码约束。ALTER TABLE Bank.dbo.Borrows ADD FOREIGN KEY (CId) REFERENCES CustomerALTER TABLE Bank.dbo.Borrows ADD FOREIGN KEY (LNo) REFERENCES Loan例 修改客户Cust

25、omer表结构，增加一新列phone。ALTER TABLE Bank.dbo.Customer ADD phone CHAR(10)例 修改客户Customer表结构，删除上面的新增列。ALTER TABLE Bank.dbo.Customer DROP COLUMN phone2022/8/26数据库系统原理41三、删除表 DROP TABLE table_name 语法说明：删除表操作会将表中所有数据删除，而且直接或间接地建立在该表上的视图及相关授权等与此表有关的内容也会被自动撤销。 2022/8/26数据库系统原理423.2.3 索引的创建 索引（index）是关于数据位置信息的关键字

26、表，由表中的一列或若干列字段值以及相应的指向表中物理标识这些值的数据页的逻辑指针构成。利用索引，系统可较快地在磁盘上定位所需数据，从而加快了数据查询的速度；通过在表中创建索引还可以保证数据记录的唯一性；此外，利用索引还可以加速表与表之间的连接操作。2022/8/26数据库系统原理43一、索引的类型聚簇（clustered）索引非聚簇（nonclustered）索引复合索引唯一（unique）索引2022/8/26数据库系统原理441、聚簇索引 磁盘上表的数据存储在相邻物理空间，并且表中行的物理顺序与索引的顺序一致。每张表最多只能建一个聚簇索引。合理地创建聚簇索引，可以十分显著地提高系统性能。当

27、在一个表格中建立了聚簇索引后，在执行插入、修改、删除等操作时，系统要维护聚簇结构，开销比较大。 2022/8/26数据库系统原理452、非聚簇索引 与表中数据行的实际存储结构无关，在创建与使用时不会改变数据表中记录的实际存储顺序。非聚簇索引保存的是行指针，而不是数据页，因此检索速度不如聚簇索引快。在SQL Server 2000中每个表最多能创建249个非聚簇索引。 2022/8/26数据库系统原理463、复合索引 对多个字段创建的索引。 2022/8/26数据库系统原理474、唯一索引 该索引要求被索引的列不能有相同值出现，可用来限制表中某些字段值的唯一性。 2022/8/26数据库系统原理

28、48建索引时应遵循的原则 经常做为条件查询的字段加上索引，如果几个字段同时做为查询时就建复合索引。在表的主键、外键上创建索引。在经常用于表间连接的字段上建立索引。2022/8/26数据库系统原理49建立聚簇索引若满足下列情况之一，可考虑建立聚簇索引，否则应建立非聚簇索引：（1）检索数据时，常以某个属性（组）作为排序、分组条件。（2）检索数据时，常以某个属性（组）作为检索限制条件，并返回大量数据。（3）表格中某个属性（组）的值重复性较大。2022/8/26数据库系统原理50二、创建索引 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name

29、 ON table_name ( column ASC | DESC ,n ) 语法说明：索引的建立和删除，必须要有DBA权限或是表的拥有者。UNIQUE表示建立唯一性的索引。CLUSTERED | NONCLUSTERED 表示聚簇索引或非聚簇索引。ON table表示为哪个表创建索引。column ASC | DESC ,n 用于指定索引基于的列和索引排序方式。 2022/8/26数据库系统原理51例子CREATE CLUSTERED INDEX CName ON Customer(CName)CREATE INDEX Customer_address ON Customer(CStree

30、t, CCity)CREATE INDEX Account_BName ON Account(BName)CREATE INDEX Account_balance ON Account(balance)2022/8/26数据库系统原理52三、删除索引 DROP INDEX index_name语法说明：只有索引的拥有者和具有DBA权限的用户，可以删除索引。索引删除后，有关索引的定义将从系统的数据字典（DD）中删除，并且包含在索引中的全部索引项将被清除。索引的删除，不会影响其他表和索引的正常使用，只会在某种程度上影响系统的性能。在数据定义语言中，没有索引的修改功能。要修改索引，可先删除要修改的索

31、引，然后创建一个同名的索引。 2022/8/26数据库系统原理533.3 数据查询 3.3.1 基本语法格式3.3.2 单表查询3.3.3 聚集函数3.3.4 多表查询 2022/8/26数据库系统原理543.3.1 基本语法格式 SELECT select_listFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 2022/8/26数据库系统原理55语法说明：select_list：

32、描述进入结果集的列，是由逗号分隔的表达式的列表。table_source：用于指定产生查询结果的数据源。search_condition：用于从指定数据源中筛选元组。group_by_expression：分组的依据。search_condition：用于筛选分组。order_expression：排序的依据。 2022/8/26数据库系统原理56一、从单表中读取指定列 例 查询全体客户表中的客户标识和客户姓名。SELECT CId,CName FROM Customer 例 查询全体客户的全部信息。SELECT * FROM Customer 例 查询有贷款记录的支行名称。SELECT DI

33、STINCT BName FROM Loan 2022/8/26数据库系统原理57一、从单表中读取指定列（续）例 以万元为单位显示支行的资产额。格式一：SELECT BName,BCity,Assets/10 000 FROM Branch格式二：SELECT BName,BCity,Assets/10 000 AS Assets_ tenthousand FROM Branch格式三：SELECT BName name, BCity city, Assets/10 000 Assets_tenthousand FROM Branch2022/8/26数据库系统原理58二、从单表中读取指定行

34、例 查询客户姓名为“刘伟”的客户信息。SELECT * FROM Customer WHERE CName=刘伟 2022/8/26数据库系统原理59常用的条件表达式运算符 运算类型运 算 符比较=、=、=、!=范围BETWEEN AND，NOT BETWEEN AND集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL逻辑AND，OR，NOT2022/8/26数据库系统原理60例子例 查询贷款金额在10 000元以上的贷款信息。SELECT * FROM Loan WHERE amount10000例 查询存款金额在5000元至7000元之间(含

35、5 000元和7 000元)的账号。格式一：SELECT * FROM Account WHERE balance BETWEEN 5000 AND 7000格式二：SELECT * FROM Account WHERE balance=5000 AND balance=7000 2022/8/26数据库系统原理61例子例 查询存款金额不在5 000元至7 000元之间的账号。格式一：SELECT * FROM Account WHERE balance NOT BETWEEN 5000 AND 7000格式二：SELECT * FROM Account WHERE balance7000 例

36、 查询“解放支行”和“铁路支行”的账户信息。格式一：SELECT * FROM Account WHERE BName IN (解放支行, 铁路支行)格式二：SELECT * FROM Account WHERE BName=解放支行 OR BName=铁路支行 2022/8/26数据库系统原理62例子例 查询不是“解放支行”和“铁路支行”的账户信息。格式一：SELECT * FROM Account WHERE BName NOT IN (解放支行, 铁路支行)格式二：SELECT * FROM Account WHERE BName解放支行AND BName!= 铁路支行 例 查询没有登记

37、所在街道的客户信息。SELECT * FROM Customer WHERE CStreet IS NULL 2022/8/26数据库系统原理63例子例 查询所有登记了所在街道的客户信息。SELECT * FROM Customer WHERE CStreet IS NOT NULL 例 查询所有姓“刘”的客户信息。SELECT * FROM Customer WHERE CName LIKE 刘% 例 查询所有不姓“刘”的客户信息。SELECT * FROM Customer WHERE CName NOT LIKE 刘% 例 查询姓名中第二个字为“大”的顾客信息。SELECT * FROM

38、 Customer WHERE CName LIKE _ _大% 例 查询支行名称以“青岛_”打头的支行信息。SELECT * FROM Branch WHERE BName LIKE 青岛_% ESCAPE 2022/8/26数据库系统原理64三、从单表中读取指定行和指定列 例 查询记录所在街道的客户姓名。SELECT CName FROM Customer WHERE CStreet IS NOT NULL 例 查询“市中支行”的所有账号。SELECT ANo FROM Account WHERE BName=市中支行 2022/8/26数据库系统原理65四、对查询结果进行排序 例 按资产

39、额从小到大的顺序显示支行信息。SELECT * FROM Branch ORDER BY assets 例 查询全部账户信息，查询结果按开户支行升序排列，相同开户支行情况下按账户余额降序排列。SELECT * FROM Account ORDER BY BName ASC,balance DESC 2022/8/26数据库系统原理663.3.3 聚集函数 COUNT(ALL|DISTINCT expression|*)统计记录个数SUM (ALL|DISTINCT expression) 计算指定表达式的总和AVG(ALL|DISTINCT expression) 计算指定表达式的平均值MAX

40、(ALL|DISTINCT expression) 计算指定表达式中的最大值MIN(ALL|DISTINCT expression) 计算指定表达式中的最小值 注意：如果在聚集函数中指定DISTINCT关键词，则表示在计算时要取消指定表达式中的重复值，如果不指定DISTINCT关键词或ALL关键词(ALL为缺省值)，则表示不取消重表达式值。COUNT（*）返回所有记录的行数，其中包括NULL值的行，而COUNT（ALL|DISTINCT expression）返回的是表达式值不为NULL值的行数。 2022/8/26数据库系统原理67一、使用聚集函数例 查询全部客户记录个数。SELECT CO

41、UNT(*) FROM Customer例 查询登记了所在街道的客户人数。SELECT COUNT(CStreet) FROM Customer例 查询支行所在城市的个数。SELECT COUNT(DISTINCT BCity) FROM Branch例 查询开户行是“市中支行”的账户余额总额。SELECT SUM(balance) FROM Account WHERE BName=市中支行2022/8/26数据库系统原理68例子例 查询所有支行的平均资产额。SELECT AVG(assets) FROM Branch例 查询所有支行中资产额最大的支行。SELECT MAX(assets) F

42、ROM Branch例 查询所有支行中资产额最小的支行。SELECT MIN(assets) FROM Branch 2022/8/26数据库系统原理69二、查询结果分组 例 查询不同所在城市中支行的个数。SELECT BCity,COUNT(*) FROM Branch GROUP BY BCity 例 查询不同所在城市支行的资产总额。SELECT BCity,SUM(assets) FROM Branch GROUP BY BCity例 查询所在城市中有两个以上支行的城市。SELECT BCity FROM Branch GROUP BY BCity HAVING count(*)=2 2

43、022/8/26数据库系统原理703.3.4 多表查询 多表查询方法包括：嵌套子查询、相关子查询、集合查询、连接查询等。 2022/8/26数据库系统原理71一、嵌套子查询 嵌套子查询概念：一个查询语句的查询结果作为另一个查询语句的条件，则前者为“子查询”，亦称“嵌套子查询”（nested sub-query），而后者为“主查询”，或称“外查询”。2022/8/26数据库系统原理72例子例 查询有储蓄账户的客户姓名。SELECT CName FROM Customer WHERE CId IN (SELECT CId FROM Deposits) 例 查询比所在城市是“济南”的所有支行资产额都

44、大的支行名称。格式一：SELECT BName FROM Branch WHERE assetsALL (SELECT assets FROM Branch WHERE BCity=济南)格式二：SELECT BName FROM Branch WHERE assets(SELECT MAX(assets) FROM Branch WHERE BCity=济南) 2022/8/26数据库系统原理73比较运算符 ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值ANY小于子查询结果中的某个值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值=ANY小于等于子查询结

45、果中的某个值=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或) ANY不等于子查询结果中的某个值!=(或) ALL不等于子查询结果中的所有值2022/8/26数据库系统原理74带有ANY或ALL的子查询可用集函数来实现 =!=或=ANYINMAXMIN=MINALLNOT INMINMAX=MAX2022/8/26数据库系统原理75例子例 查询比所在城市是“济南”的某个支行的资产额小的支行名称。格式一：SELECT BName FROM Branch WHERE assetsANY (SELECT assets F

46、ROM Branch WHERE BCity=济南) AND BCity济南格式二：SELECT BName FROM Branch WHERE assets(SELECT MAX(assets) FROM Branch WHERE BCity=济南) and BCity济南 2022/8/26数据库系统原理76二、相关子查询 相关子查询概念：是一种特殊的嵌套子查询，即子查询与来自主查询的列有关。首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。2022

47、/8/26数据库系统原理77例子例 查询有储蓄账户的客户姓名。SELECT CName FROM Customer WHERE EXISTS (SELECT * FROM Deposits WHERE Deposits.CId=Customer.CId) 例 查询比所在城市是“济南”的所有支行资产额都大的支行名称。SELECT BName FROM Branch AS A WHERE NOT EXISTS (SELECT * FROM Branch AS B WHERE B.BCity=济南 and B.assetsA.assets) 2022/8/26数据库系统原理78三、集合查询 SELE

48、CT语句的查询结果是记录的集合，所以多个SELECT语句的结果可进行集合操作。不过在SQL Server中只支持UNION集合的并操作（有些产品实现的SQL中支持交INTERSECT和差EXCEPT运算）。交和差操作可以使用变通（使用EXISTS）的方法进行处理 2022/8/26数据库系统原理79例子例 查询有储蓄或者有贷款的客户标识。SELECT CId FROM Deposits UNION SELECT CId FROM Borrows 例 查询既有储蓄又有贷款的客户标识（Deposits和Borrows的交）。格式一：SELECT CId FROM Deposits WHERE EX

49、ISTS (SELECT * FROM Borrows WHERE Borrows.CId = Deposits.CId)格式二：SELECT CId FROM Deposits WHERE CId IN (SELECT CId FROM Borrows)2022/8/26数据库系统原理80例子例 查询只有储蓄没有贷款的客户标识（Deposits和Borrows的差）。格式一：SELECT CId FROM Deposits WHERE NOT EXISTS (SELECT * FROM Borrows WHERE Borrows.CId = Deposits.CId)格式二：SELECT C

50、Id FROM Deposits WHERE CId NOT IN (SELECT CId FROM Borrows)2022/8/26数据库系统原理81四、连接查询 From子句同时涉及多个表的查询称为连接查询。用来连接两个表的条件称为连接条件或连接谓词，一般格式：. . 比较运算符：=、=、=、!=. BETWEEN . AND .2022/8/26数据库系统原理82连接查询 （续）连接字段连接条件中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的2022/8/26数据库系统原理83连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然

51、后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕 2022/8/26数据库系统原理84排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字

52、段值的元组时，对表2的查询不再继续2022/8/26数据库系统原理85排序合并法找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止 2022/8/26数据库系统原理86索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组 2022/

53、8/26数据库系统原理87例子例 查询储蓄账号及对应的客户姓名。格式一：SELECT ANo,CName FROM Deposits, Customer WHERE Deposits.CId = Customer.CId格式二：SELECT ANo,CName FROM Deposits A,Customer B WHERE A.CId = B.CId格式三：SELECT ANo,CName FROM Deposits AS A INNER JOIN Customer AS B ON A.CId = B.CId 2022/8/26数据库系统原理88例子例 查询储蓄账号、对应的客户姓名以及储蓄金

54、额。SELECT A.ANo,CName,balance FROM Deposits A,Customer B,Account C WHERE A.CId = B.CId AND A.ANo = C.ANo 例 查询全部客户信息，同时显示贷款号(如果有的话)。格式一：SELECT customer.*,Borrows.LNo FROM Customer LEFT OUTER JOIN Borrows ON Customer.Cid = Borrows.CId格式二：SELECT customer.*,Borrows.LNo FROM Borrows RIGHT OUTER JOIN Custo

55、mer ON Customer.Cid = Borrows.CId 2022/8/26数据库系统原理893.4 数据操纵 3.4.1 数据插入 3.4.2 数据修改 3.4.3 数据删除 2022/8/26数据库系统原理903.4.1 数据插入 格式一：INSERT INTOtable_name|viewname (column_list) VALUES( data_values)格式二：INSERT INTO table_name|viewname (column_list) SELECT statement 2022/8/26数据库系统原理91语法说明 如果有列名表“column_list

56、”，则要求其后的数据在顺序、个数和类型上，应与其一一对应；如果没有列名表，则要求插入的数据在顺序、个数和类型上，应与表或视图定义中的列一一对应。插入数据时，对于允许为空的列，可使用NULL插入空值。格式一为单行数据插入方式，一次只能插入一行记录。格式二为集合数据插入方式，一次可插入一行或多行记录。2022/8/26数据库系统原理92例子例 在银行数据库表中插入数据。INSERT INTO Branch(BName,BCity,assets) VALUES(栈桥支行, 青岛,7100000)INSERT INTO Branch VALUES(市中支行, 青岛,9000000)INSERT INT

57、O Customer VALUES(0503001, 李勇, 迎春路, 威海)INSERT INTO Account VALUES(A101, 市中支行,5000) 2022/8/26数据库系统原理93例子例 将客户姓名、存款账号和存款余额信息插入到一个临时表中。CREATE TABLE Customer_Deposits(CName CHAR(8),ANo CHAR(4),balance DECIMAL(10,2)INSERT INTO Customer_Deposits SELECT ame, B.ANo, B.balance FROM Customer A,Account B,Depos

58、its C WHERE CId=C.CId AND B.ANo=C.ANo 2022/8/26数据库系统原理943.4.2 数据修改 UPDATE table_name|viewname SET columnname =expression | NULL, n WHERE 语法说明：UPDATE后的表名和视图名，是指要进行修改操作的表或视图。如果没有WHERE子句，则将修改表中的所有数据，否则只修改满足search_condition查询条件的记录。 2022/8/26数据库系统原理95例子例 修改Customer表中的数据。UPDATE Customer SET CStreet=和平路 WH

59、ERE CId =0503001UPDATE Customer SET CStreet=新华路, customer_city=济南WHERE CName=钱小平 2022/8/26数据库系统原理963.4.3 数据删除 DELETE FROM table_name|viewname WHERE 语法说明：DELETE后的表名和视图名，是指要进行删除操作的表或视图。如果没有WHERE子句，则将删除表中的所有数据，否则只删除满足search_condition查询条件的记录。 2022/8/26数据库系统原理97例子例 删除Borrows借贷表中的所有数据。DELETE FROM Borrows例 删除Customer客户表中的指定数据。DELETE FROM Customer WHERE CId=0