数据库原理PPT教学课件-第3章(2) 关系数据标准语言SQL.ppt

第3章关系数据标准语言sql,本章目录,3.1 sql 概述 3.2 数据定义 3.3 数据查询 3.4 数据更新 3.5 视图 3.6 查询优化,熟练掌握sql的数据定义功能； 熟练掌握sql的单表、多表的查询功能； 熟练掌握sql的数据更新功能； 理解索引的作用及索引的管理； 掌握视图的概念及视图的管理； 理解查询优化的意义、方法及步骤。,学习目标,3.1 sql概述,sql（structured query language） 结构化查询语言，是关系数据库的标准语言 sql是一个通用的、功能极强的关系数据库 语言,sql标准的进展过程,标准 大致页数 发布日期 sql/86 1986.10 sql/89(fips 127-1) 120页 1989年 sql/92 622页 1992年 sql99 1700页 1999年 sql2003 3600页 2003年,3.1 sql 概述,sql语言支持数据库三级模式结构,3.2 数据定义,3.2.1 基本数据类型 3.2.2 基本表的创建、删除与修改 3.2.3 索引的创建与删除,数据类型,系统定义数据类型,char varchar nchar nvarchar text ntext,money smallmoney,binary varbinary image,uniqueidentifier (guid),timestamp,sql_variant,int smallint tinyint bigint bit,numeric decimal float real,sql server 2000 数据类型,二进制,table,其他数据类型,货币,数字,字符,整数,datetime smalldatetime,日期时间,1. 整数数据类型,bigint,int,smallint,tinyint，bit。 存储精确的整数值。由数字09、正负号组成。 bigint（长整型） ：8个字节。 int (整型) ：4个字节。 smallint (短整型) :2个字节。 tinyint(微整型) :1个字节。 bit（位）：只能取0、1 或 null。,2.浮点数据类型（近似数值型）,二种：float,real。大小范围不同。 存储实数值。由数字09、正负号、小数点组成。,说明方法：float (n) :,real ：相当于float(1-24)。,3.精确数值数据类型,二种：decimal,numeric。 等价。 存储实数值。由数字09、正负号、小数点组成。最大精度38位。 说明方法： decimal（p,s) 或 numeric（p,s) p指数据中的位数总和，包括整数部分和小数部分，不包括小数点。 s指小数点右边能存储的数字位数的最大值,4. 字符数据类型,char, nchar 、varchar, nvarchar、 text、 ntext 常量用单引号括起来。 char：8000个字符，固定长度。 varchar:可变长度。其大小为输入数据的字 节的实际长度。（不包括尾随空格） text:固定长度16个字节。 n开头的是unicode类型。,5.货币型数据类型,二种：money,smallmoney。 大小范围不同。 美元、英镑、日元、欧元。 货币常量前加币符。,6. 二进制数据类型,三种：binary,varbinary、image。 存储位串。需要存储十六进制数时使用。 表示方法： binary （n) 存储定长的位串。 varbinary （n) 存储可变长的位串。 image实际是可变长度的二进制数据类型。,7. 时间日期型数据类型,二种：datetime,smalldatetime。 说明方法： datetime用8个字节存储日期时间。日期范围1753/1/19999/12/31。精度到3/100秒。 smalldatetime用4个字节存储。范围1900/1/12079/12/31。精度到分。 常量用单引号引起，有多种格式。分隔符可为-或/,8. 其他几种数据类型,timestamp时间标识类型：它提供数据库范围内的唯一值，反应数据库中数据修改的相对顺序。一个表只能有一个timestamp类型，其内容会自动更新 sql_variant可变数据类型 可以在单独的字段、变量中存储不同类型的数据。但text,ntext,image,timestamp,以及sql_variant类型的值除外。最长8016字节。,其他几种数据类型,uniqueidentifier全局性唯一标识数据类型：用于存储一个由16个字节组成的二进制数字，其数值格式类似于1c1ae361-7f2c-11d6-97ad-00e03c68608e，该识别码称为全局唯一标识符（guid :globally unique identfier)。 可以用newid函数产生guid的值。,table数据类型类似于一个临时表，用于存储一个结果集。这种数据类型只能用于定义局部变量和用于用户定义函数的返回值，不能在表中定义字段。,其他几种数据类型,3.2.2基本表的创建、删除与修改,sql的数据定义语句,数据库的物理实现(数据库文件) ,数据库的逻辑组件(数据库对象) ,sql server数据库概述,sql server中的数据库,sales数据库,sql server,表,视图,salesdat1.mdf,salesdat2. ndf,saleslog1. ldf,主数据文件,次数据文件,日志文件,索引,存储过程,用户视图,物理视图,数据库的用户视图和物理视图,视图,sql server 数 据 库 概 述,1. sql server中的数据库对象 sql server提供了很多逻辑组件，这些逻辑组件通常被称为数据库对象。,sql server数据库概述,2. sql server中的数据库文件,主数据文件*.mdf 仅有一个,事务日志文件*.ldf 一到多个,次数据文件*.ndf 零到多个,次数据文件*.ndf 零到多个,(仅有一个) 主文件组,(零到多个) 次文件组,事务日志,一个数据库的文件集,数据库的文件组成,sql server数据库概述,(1)数据文件 数据文件是存放数据和数据库对象的文件。有一个文件被定义为主数据文件(primary database file)，扩展名为mdf，用来存储数据库的启动信息和部分或全部数据。 其他数据文件被称为次数据文件(secondary database file) 扩展名为ndf，用来存储主数据文件没存储的其他数据。,sql server数据库概述,(2)事务日志 事务日志文件是用来记录数据库更新信息的文件。这些更新信息(日志)可用来恢复数据库。 事务日志文件最小为 512 kb，扩展名为ldf。 每个数据库可以有一个或多个事务日志文件。,sql server数据库概述,(3)文件组 sql server允许对文件进行分组，以便于管理和数据的分配放置。所有数据库都至少包含一个主文件组，所有系统表都分配在主文件组中。用户可以定义额外的文件组。,数据库的创建,使用transact-sql语句创建数据库，命令格式： create database database_name on ,.n ,.n log on ,.n,命令行中符号的含义,| （竖线）分隔括号或大括号内的语法项目。只能选择一个项目。 （方括号）可选语法项目。不必键入方括号。 （大括号）必选语法项。不要键入大括号。 ,.n 表示前面的项可重复 n 次。 :=语法块的名称。此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组和标记。适合使用语法块的每个位置由括在尖括号内的标签表示：。,= primary ( name=逻辑名 , filename=物理文件名和路径 ,size=文件初始容量 ,maxsize=最大容量|unlimited ,filegrowth=递增值 ) 1,n,数据库的创建,= filegroup filegroup_name ,4.2 数据库的创建,数据库的创建,例1 最简形式的创建数据库(不指定文件)语句。 create database sales 例2 不指定size创建数据库。 create database sales2 on ( name=sales2_dat, filename=c:databasesales2.mdf),数据库的创建,例3 创建简单的数据库。 create database sales3 on ( name=sales3_dat, filename=c:databasesales3.mdf, size=4, maxsize=10, filegrowth=1 ),数据库的创建,例4-4 创建指定数据文件和事务日志文件的数据库。 create database sales4 on ( name=sales4_dat, filename=c:databasesales4dat.mdf, size=10000kb, maxsize=500000kb, filegrowth=5 ) log on ( name=sales4_log, filename=c:databasesales4log.ldf, size=5, maxsize=25, filegrowth=5 ),使用transact-sql建立表,create table （ | 计算列定义 | ，n | ）,表名：database_name. owner. table_name database_name：用于指定在其中创建表的数据 库名称。 owner：用于指定新建表的所有者的用户名 。 table_name：用于指定新建的表的名称。,使用transact-sql建立表,:= 列名 数据类型 collate default 常量表达式 | identity ( 种子,种子增量) rowguidcol .n ,使用transact-sql建立表,列定义选项说明,数据类型：定义字段数据类型和长度，长度需要使用括号( )括起来 collate ：指定排序规则，默认为数据库的默认值。只有字符型字段才能指定排序规则。 default 常量表达式：定义字段的默认值。 identity ( 种子 ,增量)：定义字段标识属性，允许字段自动编号。种子值和增量默认为1。一个表中只允许有一个标识字段。,列定义,create table test1 ( 编号 int identtity, 学号 char(8) 成绩 numeric(3,1) default 0),create table test2 ( 编号 int identtity（1，2）, 学号 char(8)， 成绩 numeric(3,1)， default 0 ),rowguidcol：设置字段由sql server自动产生一个全局惟一值，只有字段数据类型为uniqueidentifier时，才可设置该选项。,列定义选项说明,:= 列名 数据类型 collate default 常量表达式 | identity ( 种子,种子增量) rowguidcol .n ,使用transact-sql建立表,约束 (1) 非空值约束(not null) (2) 默认约束(default) (3) 惟一性约束(unique ） (4) 主键约束(primary key) (5) 外键约束(foreign key) (6) 检查约束（check）,sql server表概述,:=constraint 约束名 null | not null | primary key | unique | clustered | nonclustered | foreign key references 参考表 ( 参考列 ) | check not for replication ( logical_conditions ) ,列约束,建立职工表并包含一个约束名为salary,create table 职工 ( 仓库号 char(5) primary key, 职工号 char (5), 工资 int default 1200 constraint salary check(工资=1000 and 工资=5000) ),create table 职工 ( 仓库号 char(5) foreign key (仓库号) references 仓库(仓库号), 职工号 char(5) primary key, 工资 int constraint salary check (工资=1000 and 工资=5000) default 1200 ),建立职工表,使用transact-sql建立表,create table （ | 计算列定义 | ，n | ）,表的创建与维护,例 对计算列使用表达式。 create table salarys ( 姓名 varchar(10), 基本工资 money, 奖金 money, 总计 as 基本工资+奖金),表的创建与维护,例 定义表autouser自动获取用户名称。 create table autouser ( 编号 int identity(1,1) not null, 用户代码 varchar(18), 登录时间 as getdate( ), 用户名 as user_name( ) ),使用transact-sql建立表,create table （ | 计算列定义 | ，n | ）,:= constraint 约束名 | primary key | unique | clustered|nonclustered | foreign key references 参考表 ( 参考列 ) | check not for replication ( logical_conditions ) ,表级完整性约束,表级完整性约束,create table 职工 ( 仓库号 char(5) foreign key (仓库号) references 仓库(仓库号), 职工号 char(5), 工资 int constraint salary check(工资=1000 and 工资=5000) default 1200, primary key (仓库号,职工号) ),表的创建与维护,例 创建临时表。 create table #students ( 学号 varchar(8), 姓名 varchar(10), 性别 varchar(2), 班级 varchar(10) ),使用transact-sql建立表,create table （ | 计算列定义 | ，n | ）,练习,建立一个“学生选课”表sc，它由学号sno、课程号cno，修课成绩grade组成，其中(sno, cno)为主码。 create table sc ( sno char(5) , cno char(3) , grade int, primary key (sno, cno) ),订购单,create table 订购单 ( 职工号 char(5) foreign key references 职工 not null, 供应商名 char(5) foreign key references 供应商 not null, 订购单号 char(5) primary key, 订购日期 datetime default getdate( ) ),提 问,主键约束和惟一约束有何不同？ 如何定义某列为标识列？ 如何定义外键约束？ 表级完整性和列级完整性有何区别？,修改表结构alter table语句,alter table alter column |add |add |drop |drop column ,修改字段定义,alter table table alter column column_name new_data_type ( precision , scale ) null | not null | add | drop rowguidcol ,注： alter column 一次只能修改一个字段,例如: 下面的语句可以将“成绩”数据表中的“学号”字段修改为char(10)和not null alter table 成绩 alter column 学号 char(10) not null,修改字段,续,| add | 列名 as 计算列表达式 , . n | with check | with nocheck add , . n,with check | with nocheck用于指定已经存在于表中的数据是否需要使用新添加的或者刚启用的 foreign key 或 check 约束进行验证。,添加字段,例 向student表增加“入学时间”列，其数据类型为日期型。 alter table student add scome datetime 不论表中原来是否已有数据，新增加的列一律为空值。,添加字段,如果新字段不允许空（not null），又没有设置默认值，则不能添加新字段。 例如，下面的语句可以为“成绩”数据表添加一个字段。 alter table 成绩 add 班级 varchar(20) not null default 04级计算机应用,create table xs ( a int) alter table xs add b varchar(20) null constraint b unique,向表中添加具有 unique 约束的新列。,向表中的现有列上添加约束。,create table xs( a int) alter table xs with nocheck add constraint a_check check ( a 1) 利用 with nocheck 来防止对现有行验证约束，从而允许该约束的添加,删除表字段定义,| drop constraint constraint_name | column column ,.n | check | nocheck constraint all | constraint_name ,.n , check | nocheck constraint：用于指定启用或禁用foreign key或者check约束。 all ：用于指定使用 nocheck 选项禁用所有的约束，或者使用 check 选项启用所有约束。,表的创建与维护,例 更改表以添加新列，然后再删除该列。 alter table employee add email varchar(20) null alter table employee drop column email,例如：,alter table 学生 add 英语 int constraint encj default 80,现要删除英语列，如何做？,alter table 学生 drop constraint encj alter table 学生 drop column 英语,订购单,create table 订购单 ( 职工号 char(5) foreign key references 职工 not null, 供应商名 char(5) foreign key references 供应商 not null, 订购单号 char(5) primary key, 订购日期 datetime default getdate( ) ),表的创建与维护,使用transact-sql语句删除表 语句格式如下： drop table table_name 其中，table_name是要删除的表名。 注意： (1) 定义有外键约束的表必须先删除外键约束，才 能删除。 (2) 系统表不能使用drop table语句删除。,表的创建与维护,例 删除当前数据库内的表。 use sales go drop table employee 例 删除另外一个数据库内的表。 drop table sales.dbo.employee 本例删除sales数据库内的employee表。可以在任何数据库内执行此操作。,索引概述 索引的操作,3.2.3 索引的管理,索引概述,索引的基本概念 索引是对数据库表中一个或多个列的值进行排序的结构，可以利用索引快速访问数据库表中的特定信息。,建立索引的目的有以下几点： (1)加速数据检索 (2)加速连接、order by和group by等操作 (3)查询优化器依赖于索引起作用 (4)强制实行的惟一性,索引概述,惟一索引和非惟一索引 聚集索引和非聚集索引 单列索引和复合索引,索引的分类,索引概述,索引的分类 1惟一性索引 在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。,2主键索引 表中通常有一个字段或一些字段的组合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键。不能有空值null 表中设主键后将自动创建主键索引 ，它是 唯一索引的特殊类型,索引概述,索引概述,3聚集索引 在聚集索引中，表中各记录的物理顺序与键值的逻辑(索引)顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。 由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。,聚集索引的适用范围 在最经常查询的列上建立聚簇索引以提高查询效率 一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引,索引概述,非聚集索引适用于以下情况：,包含大量非重复值的列，如姓名列。如果只有很少的非重复值，则大多数查询将不使用索引，因为此时使用表扫描查询数据更有效。 不返回大型结果集的查询。 搜索条件（where子句）要求精确匹配的查询中经常使用的列。 经常需要联接和分组查询语句。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。,索引的操作,3使用transact-sql语句创建索引 create unique clustered| nonclustered index on 表名(列名asc|desc,n),说明：,unique、clustered、nonclustered用来指定索引的类型，无这些选项时系统默认创建的是非唯一索引、非聚集索引 由 ntext、text 或 image 数据类型组成的列不能指定为索引列。 asc/desc 确定索引列的排序方法，默认为asc,索引的操作,例 按employee表的employee_name 列建立非聚集索引。 create nonclustered index name_idx on employee(employee_name),例14: sc表按学号升序和课程号降序建 唯一索引。 create unique index scno on sc(sno asc，cno desc),索引的操作,索引的操作,删除索引 索引会减慢insert、update和delete语句的执行速度。如果发现索引阻碍整体性能或不再需要索引，则可将其删除。,索引的操作,2使用transact-sql语句删除索引语法格式： drop index 表名.索引名 ,.n 各选项的含义： (1)表名：索引列所在的表。 (2)索引名：要删除的索引名称。索引名必须符合标识符的规则。,索引的操作,例 删除employee表内名为index_2的索引。 use sales if exists (select name from sysindexes where name = index_2) drop index employee. index_2 go,执行drop index后，将自动释放索引占据的空间 如果在create table中创建的索引，只能用alter table进行删除，如果用create index创建的可以用drop index删除 删除一个表时，表的索引也被删除 不能在由primary key约束或unique约束创建的索引上使用drop index，要删除索引必须先删除约束。 drop index不能用于系统表,删除索引时注意,3.3数据查询,3.3.1语句格式 select all|distinct ， from ， where group by having order by asc|desc ；,3.3 数据查询,3.3.1 单表查询 3.3.2 连接查询 3.3.3 嵌套查询 3.3.4 集合查询 3.3.5 select语句的一般形式,3.3.1 单表查询,查询仅涉及一个表： 一、 选择表中的若干列 二、 选择表中的若干元组 三、 order by子句 四、 聚集函数 五、 group by子句,一、 选择表中的若干列,查询指定列 例1 查询全体学生的学号与姓名。 select sno，sname from student； 例2 查询全体学生的姓名、学号、所在系。 select sname，sno，sdept from student；,2. 查询全部列,选出所有属性列： 在select关键字后面列出所有列名 将指定为 * 例3 查询全体学生的详细记录。 select * from student；,3. 查询经过计算的值,select子句的可以为： 算术表达式 字符串常量 函数 列别名,例4 查全体学生的姓名及其出生年份。 select sname，2004-sage from student； 输出结果： sname 无列名 李勇 1984 刘晨 1985 王敏 1986 张立 1985,查询经过计算的值（续）,查询经过计算的值（续）,例5查询全体学生的姓名、出生年份和所有系，要求用小写字母 表示所有系名 select sname,year of birth: ，2004-sage， lower(sdept) from student； 输出结果： sname 无列名 无列名 lower(sdept) 李勇 year of birth: 1984 cs 刘晨 year of birth: 1985 is 王敏 year of birth: 1986 ma 张立 year of birth: 1985 is,操作查询的列名,用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时的标题。 对列名进行操作有3种方式： （1）采用符合ansi规则的标准方法，在列表达式后面给出列名。 （2）用“=”来连接列表达式。 （3）用as关键字来连接列表达式和指定的列名,查询经过计算的值（续）,使用列别名改变查询结果的列标题: select sname name，year of birth: birth， 2000-sage birthday，lower(sdept) department from student； 输出结果： name birth birthday department - - - - 李勇 year of birth: 1984 cs 刘晨 year of birth: 1985 is 王敏 year of birth: 1986 ma 张立 year of birth: 1985 is,select 图书代号=title_id, 原价=price, 现价= price-price*0.3 from titles,操作查询的列名,select title_id as 图书代号, price as 原价, price-price*0.3 as 现价 from titles,操作查询的列名,select title_id 图书代号, price 原价, price-price*0.3 现价 from titles,操作查询的列名,3.3.1 单表查询,查询仅涉及一个表： 一、 选择表中的若干列 二、 选择表中的若干元组 三、 order by子句 四、 聚集函数 五、 group by子句,数据查询,语句格式 select all|distinct ， from ， where group by having order by asc|desc ；,二、选择表中的若干元组,1. 消除取值重复的行 如果没有指定distinct关键词，则缺省为all 例6 查询选修了课程的学生学号。 select sno from sc； 等价于： select all sno from sc；,sno 200215121 200215121 200215121 200215122 200215122,执行上面的select语句后，结果为：,消除取值重复的行（续）,指定distinct关键词，去掉表中重复的行 select distinct sno from sc； 执行结果： sno 200215121 200215122,2.查询满足条件的元组,表3.4 常用的查询条件,(1) 比较大小,例7查询计算机科学系全体学生的名单。 select sname from student where sdept=cs； 例8 查询所有年龄在20岁以下的学生姓名及其年龄。 select sname,sage from student where sage 20；,例9 查询考试成绩有不及格的学生的学号。 select distinct sno from sc where grade60；,(1) 比较大小,（2）确定范围,谓词: between and not between and 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 select sname，sdept，sage from student where sage between 20 and 23；,例11 查询年龄不在2023岁之间的学生姓名、系别和年龄 select sname，sdept，sage from student where sage not between 20 and 23；,(3) 确定集合,谓词：in , not in 例12查询信息系（is）、数学系（ma）和计算机科学系（cs）学生的姓名和性别。 select sname，ssex from student where sdept in ( is，ma，cs );,例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。 select sname，ssex from student where sdept not in ( is，ma，cs ),(4)字符匹配,谓词:not like escape 匹配串为固定字符串 例14 查询学号为200215121的学生的详细情况。 select * from student where sno like 200215121；,等价于： select * from student where sno = 200215121 ；,2) 匹配串为含通配符的字符串,字符匹配（续）,2) 匹配串为含通配符的字符串 例15 查询所有姓刘学生的姓名、学号和性别。 select sname，sno，ssex from student where sname like 刘%； 例16 查询姓“欧阳“且全名为三个汉字的学生的姓名。 select sname from student where sname like 欧阳_；,字符匹配（续）,例17 查询名字中第2个字为“阳“字的学生的姓名和学号。 select sname，sno from student where sname like _阳%； 例18 查询所有不姓刘的学生姓名。 select sname，sno，ssex from student where sname not like 刘%；,例：查询借阅信息表（borrowinf）中图书编号以a到f的字符开头的所有借阅者信息。,select * from borrowinf where 图书编号 like a-f%,字符匹配（续）,3) 使用换码字符将通配符转义为普通字符 例19 查询db_design课程的课程号和学分。 select cno，ccredit from course where cname like db_design escape ,例20 查询以“db_“开头，且倒数第3个字符为 i的课程的详细情况。 select * from course where cname like db_%i_ _ escape escape 表示“ ” 为换码字符,(5) 涉及空值的查询,谓词： is null 或 is not null “is” 不能用 “=” 代替 例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 select sno，cno from sc where grade is null,(6) 多重条件查询,逻辑运算符：and和 or来联结多个查询条件 and的优先级高于or 可以用括号改变优先级 可用来实现多种其他谓词 not in not between and ,多重条件查询（续）,改写例12 例12 查询信息系（is）、数学系（ma）和计算机科学系（cs）学生的姓名和性别。 可改写为： select sname，ssex from student where sdept= is or sdept= ma or sdept= cs ；,3.3.1 单表查询,查询仅涉及一个表： 一、 选择表中的若干列 二、 选择表中的若干元组 三、 order by子句 四、 聚集函数 五、 group by子句,三、order by子句,order by子句 可以按一个或多个属性列排序 升序：asc；降序：desc；缺省值为升序 当排序列含空值时 asc：排序列为空值的元组最先显示 desc：排序列为空值的元组最后显示,例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 select sno，grade from sc where cno= 3 order by grade desc；,order by子句 （续）,例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 select * from student order by sdept，sage desc；,3.4.1 单表查询,查询仅涉及一个表： 一、 选择表中的若干列 二、 选择表中的若干元组 三、 order by子句 四、 聚集函数 五、 group by子句,五、group by子句,group by子句分组： 细化聚集函数的作用对象 未对查询结果分组，聚集函数将作用于整个查询结果 对查询结果分组后，聚集函数将分别作用于每个组 作用对象是查询的中间结果表 按指定的一列或多列值分组，值相等的为一组,group by子句（续）,例31 求各个课程号及相应的选课人数。 select cno，count(sno) 选课人数 from sc group by cno； 查询结果： cno 选课人数 1 22 2 34 3 44 4 33 5 48,group by子句（续）,例32 查询选修了3门以上课程的学生学号。 select sno from sc group by sno having count(*) 3；,提 问,1、什么是索引？如何创建索引？ 2、什么是聚集索引？ 3、查询语句的一般格式？ 4、查询语句中的*和all有何区别？ 5、like有何作用？有哪些通配符？各有什么作用?,group by子句（续）,having短语与where子句的区别： 作用对象不同 where子句作用于基表或视图，从中选择满足条件的元组 having短语作用于组，从中选择满足条件的组。,select sno, count(*) as num, sum(score),max(score), min(score) from sc group by sno having avg(score)=60,查询平均成绩及格的学生所选课程的数量、总分及最高和最低分。,3.3.1 单表查询,查询仅涉及一个表： 一、 选择表中的若干列 二、 选择表中的若干元组 三、 order by子句 四、 聚集函数 五、 group by子句,四、聚集函数,count函数,count函数返回列中的记录个数。 函数格式： count ( all | distinct 列名 | *) 说明： （1）count函数默认使用all参数，它将重复计算相同的值，但不包含值为null的行。 （2）使用distinct时，相同的值只计数一次，且不包含null值。,（3）在count函数中只引用一个列名时，将返回该列中行记录的个数，但不包含值为null的行。 （4）在count函数中可以使用 * 做参数，它表示返回表中的所有行数，包括含有null值的行。,count函数,例： select count(*) as 人数 from 教师 select count(民族) 民族数 from 教师 select count(distinct 民族) 民族数 from 教师,集合函数,聚集函数 （续）,例28 计算1号课程的学生平均成绩。 select avg(grade) from sc where cno= 1 ；,聚集函数 （续）,例30查询学生200215012选修课程的总学分数。 select sum(ccredit) from sc， course wher sno=200215012 and sc.cno=course.cno;,注意： where子句中不能使用聚集函数 聚集函数除count(*)外，都不处理空值,transact-sql提供了连接操作符join，用于从两张或多张数据表的连接中获取数据。两张表之间的连接可以有以下几种不同的方式： 内连接 inner join 外连接 left outer join right outer join full outer join 自连接 inner join 交叉连接 cross join,3.3.3 连接查询,3.3.3连接查询（续）,一、内连接（等值与非等值连接查询） 二、自身连接 三、外连接 四、复合条件连接,（2）join和on关键字建立连接的命令格式： select select_list from table1 inner join table2 on table1.column1 table2. column2,select 列名列表 from table1, table2 where table1.column1 table2.column2,（1）一般格式：,一、内连接（等值与非等值连接查询）,提 问,1、having与where子句的区别？ 2、如何进行两个表的连接（两种方法）？ 3、查询平均成绩及格的学生所选课程的数量、总分及最高和最低分。,一、内连接（等值与非等值连接查询 ),等值连接：连接运算符为= 例33 查询每个学生及其选修课程的情况 select student.*，sc.* from student，sc where student.sno = sc.sno；,等值与非等值连接查询（续）,等值与非等值连接查询（续）,自然连接： 例34对例33用自然连接完成。 select student.sno，sname，ssex，sage， sdept，cno，grade from student，sc where student.sno = sc.sno；,连接查询（续）,一、内连接(等值与非等值连接查询 ) 二、自身连接 三、外连接 四、复合条件连接,二、自身连接,自身连接：一个表与其自己进行连接 需要给表起别名以示区别 由于所有属性名都是同名属性，因此必须使用别名前缀 例35查询每一门课的间接先修课（即先修课的先修课） select first.cno，second.cpno from course first，course second where first.cpno = second.cno；,first,second,查询books表中至少有两本相同书名的所有图书的信息，包括编号、书名和作者。,select distinct a.