SQL语言及T-SQL的应用.ppt

第三章SQL语言及T-SQL的应用 (1),本章学习重点之一: 1、关系数据库的标准语言SQL基本知识。 2、SQL的数据定义语句、数据操作语句、数据控制语句。 3、视图、索引的概念与操作。,内容概要（之一）: 3.1 关系数据库的标准语言SQL 3.2 数据定义 3.3 数据查询语言,3.1关系数据库标准语言SQL 3.1.1 SQL的发展史 1974年IBM圣约瑟实验室(San Jose Research Laboratory)的Boyce和Chamberlin为关系数据库管理系统System-R设计的一种查询语言，当时称为SEQUEL语言（Structured English Query Language），后简称为SQL。 1981年IBM推出关系数据库系统SQL/DS后，SQL得到了广泛应用。,1986年美国国家标准协会（American National Standard Institute 简称ANSI）公布了第一个SQL标准SQL86。 1987年，国际标准化组织（International Organization for Standardization 简称ISO ）通过SQL86标准。 1989年，ISO制定SQL89标准，SQL89标准在SQL86基础上增补了完整性描述。 1990年，我国制定等同SQL89的国家标准。 1992年，ISO制定SQL92标准，即SQL2。 1999年，ANSI制定SQL3标准。 注: Server 2000中使用Transact-SQL语言。,3.1.2 SQL的特点,SQL(Structured Query Language）的特点： 综合统一 a、SQL集各种数据语言的功能于一体，语言风格统一。 b、实体和实体之间的联系均用关系表示。 2. 高度非过程化 只要提出“做什么”。 有利于各种数据库之间交换数据、有利于程序的移植、有利于实现程序和数据间的独立性；有利于实施标准化。,3. 面向集合的操作方式 操作数是集合，操作结果也是集合。 4.以同一种语法结构提供两种使用方法 自含式语言：联机交互的使用方式。 嵌入式语言：嵌入到高级语言中。 5. 语言简洁，易学易用,3.1.3 SQL的分类 1、数据定义语言（DDL：Data Definition Language） 创建、修改或删除数据库中各种对象，包括表、视图、索引等。 2、查询语言（QL：Query Language） 按照指定的组合、条件表达式或排序检索。 3、数据操纵语言（DML：Data Manipulation Language ） 插入、删除、修改等操作。,4、数据控制语言（DCL：Data Control Language） 授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。 注意：在书写各种SQL 命令时，命令中所涉及的标点符号，如括号、逗号、分号、圆点（英文句号）等都应是英文半角，如果写成中文全角符号，则会在执行命令时出错。,3.2 数据定义命令 数据定义命令用于建立数据库和建立、修改、删除基本表。,3.2.1 建立数据库 ANSI标准SQL建立数据库（SCHEMA：模式）的命令是： CREATE SCHEMA AUTHORIZATION ; 例如：创建者是duck，则上面命令写作： CREATE SCHEMA AUTHORIZATION duck; 注：RDBMS更常用下面命令格式： CREATE DATABASE ; 例如：建立学费管理数据库的命令 CREATE DATABASE xfgl;, Server 语言,SQL SERVER中建立数据库补充,SQL Server 2000中常用的数据类型：, Server 中的数据类型,3.2.2 建立数据表（标准） 建立基本表的命令格式为： CREATE TABLE （ ， ， ）； ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件,如: NOT NULL：指该列的值不能为空值。 UNIQUE:指该列的值是唯一的。 ：涉及一个或多个属性列的完整性约束条件 注：完整性约束被存入系统的数据字典中。,创建表语句中常用完整性约束 、主码约束： PRIMARY KEY 、唯一性约束：UNIQUE 、非空值约束：NOT NULL 、参照完整性约束: FOREIGN KEY REFERENCES 引用表名（引用列） 、检查约束：CHECK(检查表达式) 、默认值约束：DEFAULT 默认值,例1 建立一个”学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 (非空约束、唯一性约束实例) CREATE TABLE Student (Sno CHAR(8) NOT NULL UNIQUE， Sname VARCHAR(10) UNIQUE， Ssex CHAR(2) ， Sage tinyint， Sdept CHAR(2)； Not null、unique组合等价于主键约束。,例2主键约束、默认约束: CREATE TABLE student (Sno CHAR(8) PRIMARY KEY, Sname VARCHAR(10) UNIQUE, Ssex CHAR(2) default 男, Sage TINYINT, Sdept CHAR(2);,列级约束,PRIMARY KEY与 UNIQUE 的区别？ （）主键 具有以下特征： 每个表只能定义一个主键 主键值不能为空。 主键列可以由一个或多个列组成。 主键值不重复。 （）惟一键 通常限定的是没有被选为主键的关键字，它列或多列值的不重复，惟一性 与主键的区别如下： 每个表可以有多个惟一键。 惟一键的列值可以为空，但只能有一个空。,例3建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。 (CHECK约束实例) CREATE TABLE SC( Sno CHAR(8) , Cno CHAR(3) , Grade int check(grade=0 and grade=100), Primary key (Sno, Cno);,表级约束,例4参照完整性约束与check约束。 CREATE TABLE SC( Sno CHAR(8) FOREIGN KEY REFERENCES student(sno), Cno CHAR(3) FOREIGN KEY REFERENCES course(cno), Grade int check(grade=0 and grade=100), primary key (Sno, Cno);,3.2.3 修改基本表,ALTER TABLE ADD 完整性约束 DROP MODIFY ； ：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的完整性约束条件 MODIFY子句：用于修改列名和数据类型 注：标准SQL无删除列的语句。,1、添加新列命令： ALTER TABLE ADD 列名 数据类型; 例向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATETIME； 不论基本表中原来是否已有数据，新增加的列一律为空值。 2、删除指定的完整性约束条件的命令： ALTER TABLE DROP 完整性约束名； 例删除学生表中姓名列必须取唯一值的约束。 ALTER TABLE Student DROP UNIQUE(Sname);,注：-SQL中删除约束命令： ALTER TABLE DROP CONSTRAINT 约束名; 例7将Student表中的删除主键约束删除。 ALTER TABLE STUDENT DROP CONSTRAINT PK_student_21B6055D 注：在创建时若未给所设置的约束取名，系统将自动取名字。 查看约束信息）可以用存储过程：sp_help 数据表名 ）可以在sysobjects中查到。,如： IF EXISTS(SELECT * FROM sysobjects WHERE NAME= PK_student_21B6055D ) ,、修改列命令： ALTER TABLE MODIFY 列名 数据类型; 例将年龄的数据类型改为占2字节整数。 ALTER TABLE Student MODIFY Sage SMALLINT； 注：修改原有的列定义有可能会破坏已有数据。 T-SQL: ALTER TABLE Student alter column Sage SMALLINT； 、-SQL中删除列命令： ALTER TABLE DROP COLUMN 列名; 例将Student表中新增加的“入学时间”列删除。 ALTER TABLE Student DROP COLUMN Scome； 注：在删除列时，必须先删除该列的索引和约束后，才能删除该列。,back,3.2.4 数据表的删除,DROP TABLE ; 基本表删除 数据、表上的索引都删除 表上的视图往往仍然保留， 但无法引用。 删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述 。 例10 删除Student表 DROP TABLE Student ;,须使用的基本操纵、查询语句: 1、查看表数据： （1）使用企业管理器 （2）SQL语句格式 简单格式： Select */属性列组 from 数据表名 2、向数据表中插入数据 （）使用企业管理插入数据 （ 2 ）SQL语句格式 INSERT INTO ( , , ) VALUES ( , )；,例1、向学生基本情况表（student）中插入一条记录(学号：95020；姓名：陈林；性别：女；所在系：MA；年龄：19岁)。 INSERT INTO student VALUES(95020,陈林,女, 19 ,MA);,3、删除数据 （1）使用企业管理器： （2）SQL删除语句； DELETE FROM WHERE ； 例1、删除学号为95010的学生记录。 DELETE FROM student WHERE sno = 95010;,4、修改数据 （1）使用企业管理器 （2）修改数据SQL语句 UPDATE SET = , = WHERE ; 例1、将课程表(course)中所有课程的学时(credit)减少5学时。 UPDATE course SET credit = credit - 5;,SQL SERVER中建立数据表、数据完整性约束补充之一：,、企业管理器创建数据表、数据完整性约束方法（演示） 创建数据表 数据完整性约束 、语句 ）用select into以复制创建数据表 格式： Select */属性列组 into 新数据表名 from 数据表名 如复制表结构： Select * into stu from student where 2=1,SQLSERVER 课后阅读,如复制表的结构和内容： Select * into stu from student 思考：如何实现不同数据库间数据表的复制？如何实现不同数据服务器间数据表的复制？ 提示：服务器实例名.数据库名.拥有者.数据表名 ）创建数据表时自定义约束名 例（）：主键约束 create table course( cno char(3) constraint pk_course primary key, cname varchar(10), credit_hour tinyint),例（）：check约束 如：要求课程表中学分（credit_hour）限制在（，范围内。 create table course( cno char(3) constraint pk_course primary key, cname varchar(10), credit_hour tinyint constraint ch_course check(credit_hour0 and credit_hour=5),例（）：外键约束 CREATE TABLE SC( Sno CHAR(5) CONSTRAIT FK_SNO FOREIGN KEY REFERENCES student(sno), Cno CHAR(3) CONSTRAIT FK_CNO FOREIGN KEY REFERENCES course(cno), Grade int check(grade=0 and grade=100), Primary key (Sno, Cno);,表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。 在SQL Server 2000中，一个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段。,小知识:,3) 对计算列使用表达式 例（）下例显示如何使用表达式 (low + high)/2) 计算 myavg 计算列。 (假设low:最低分、high:最高分、myavg:平均分) CREATE TABLE mytable ( low int, high int, myavg AS (low + high)/2 ),例（）对计算列使用系统函数 下例在 myuser_name 列中使用系统函数 USER_NAME 函数。 CREATE TABLE mylogintable ( date_in datetime, user_id int, myuser_name AS USER_NAME() ) 思考：可以用自定义函数吗？ 可以！,4) 创建临时表 (1)局部临时表 例：Create table #productTemp(productID int primary key, productname char(10) (2)全局临时表 例：Create table #tempWork(pk int, coll int),5) 在列中使用 uniqueidentifier 数据类型 下例创建含有 uniqueidentifier 列的表。该表使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值，并在DEFAULT 约束中使用 NEWID() 函数为新行提供值。 CREATE TABLE Globally_Unique_Data ( guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWID(), Employee_Name varchar(60), CONSTRAINT Guid_PK PRIMARY KEY (Guid) ),6)修改数据库 添加一个或多个属性列,ALTER TABLE 表名 ADD 列名 数据类型(长度)null|not null 实例:修改数据库abab中表a，添加两个属性列cc、dd，使cc为整型，且不为空，取默认值为5，dd类型为字符型，最多占20字节。 use abab alter table a add cc int not null default 5, dd char(20),7)修改字段的属性 ALTER TABLE 表名 ALTER COLUMN 列名 数据类型(长度) 约束 注:将一个原来允许为空的列改为不允许为空时,必须满足列中没有存放空值的记录,以及在该列上没有创建索引。,8)删除字段(列)（T-SQL）,ALTER TABLE 表名 DROP COLUMN 列名 实例: 例1 例2 删除表a中的cc属性列。 alter table a drop column cc 注：须先删除属性列上的约束，再删除该列。,查询数据表信息。 sp_help a,9)修改表名,调用存储过程： Sp_rename 原表名，新表名 实例:将学生信息库student中studentx表更名为student 。 Use student sp_rename studentx,student 注：修改会影响调用该表的存储过程、触发器、视图等，使它们无效。,待续！,3.2. 索引的建立与删除 索引是对数据库表中一个或多个列的值进行排序的结构。 1、建立索引语句 CREATE UNIQUE CLUSTERED INDEX 索引名 ON 基本表名(列名次序,列名次序); 说明：索引可以建立在一列和多列之上，索引顺序可以是ASC（升序）或DESC（降序），缺省值是升序。 UNIQUE表示每一个索引值对应唯一的数据记录。 CLUSTERED表示要建立的索引是聚簇索引。,a、唯一值索引 对于已含重复值的属性列不能建UNIQUE索引。 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。 例1 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。 CREATE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX Coucno ON Course(Cno ASC)； CREATE UNIQUE INDEX SCno ON SC(Sno ，Cno DESC)；,b、聚簇索引 聚簇索引是指索引项顺序与表中记录的物理顺序一致。 在一个基本表上最多只能建立一个聚簇索引。 聚簇索引用途：对于某些类型的查询，可以提高查询效率。 聚簇索引的适用范围： （）很少对基表进行增删操作。 （） 很少对其中的变长列进行修改操作 。 （）该列为最常查询列。,例3在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。 CREATE CLUSTERED INDEX Stusname ON Student(Sname)； 例4在学生基本情况表jbqk之上建立一个关于学生表的索引文件。索引文件名为“学生索引”，索引建立在学号之上，按学号降序排序。 Create index 学生索引 on jbqk(sno desc);,2删除索引语句 DROP INDEX .; 注意：该命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键和唯一性约束索引，也不能删除系统表中的索引。 删除索引时，系统会从数据字典中删去有关该索引的描述。 例12删除例5创建的索引“学生索引”。程序如下： DROP INDEX JBQK.学生索引 例13 删除Student表的Stusname索引。 DROP INDEX Student.Stusname；,返回,3.3 数据查询语言,3.3.1 查询语句格式 SQL语言的查询语句一般格式是： SELECT ALL|DISTINCT, FROM , WHERE GROUP BY HAVING , ORDER BY ASC|DESC,;,下面对该命令进行一些说明： SELECT子句：指定显示的属性列，选出元组中的属性值形成结果表。 FROM子句：指定查询对象(基本表或视图)。 WHERE子句：指定查询条件，筛选出满足条件的元组。 GROUP BY子句：对查询结果按指定列（列1）的按值分组，该属性列值相等的元组为一个组。 通常会在每组中作用集函数。 HAVING短语：筛选出分组中满足指定条件的元组，和GROUP BY子句一起用。 ORDER BY子句：对查询结果表按指定列（列2）值的升序或降序排序。,注、目标列表达式几种表示： 列表达式可以是“列名1,列名2”的形式；如果FROM子句指定了多个表，则列名应是“表名.列名”的形式。 也可以是全部列,指定为*。 列表达式可以使用函数形成表达式 DISTINCT参数：表示在结果集中，查询出的内容相同的记录只留下一条。,示例数据库,学生-课程数据库 学生表： Student(Sno，Sname，Ssex，birthday，Sdept) 课程表： Course(Cno，Cname，Cpno，Ccredit) 学生选课表： SC(Sno，Cno，Grade),back,3.3.2 单表查询 单表查询是指仅涉及一个表的查询。 一、选择表中的若干列 （1）查询指定列,例1 查询全体学生的学号与姓名、性别。 SELECT Sno,Sname,Ssex FROM Student； 例2 查询全体学生的学号、姓名、所在系。 SELECT Sno ，Sname，Sdept FROM Student；,（2）、查询全部列 例3 查询全体学生的详细记录。 SELECT Sno，Sname，Ssex，birthday，Sdept FROM Student； 或 SELECT * FROM Student；,（3）、查询经过计算的值 SELECT子句的不仅可以是表中的属性列，也可以是表达式。（如算术表达式、字符串常量、函数、列别名等）,例4 查全体学生的姓名及其出生年份。 SELECT Sname，year(birthday) FROM Student； 输出结果： Sname 2004-Sage - - 李勇 1976 刘晨 1977 王名 1978 张立 1978,思考：如何通过出生年月信息求出年龄？,例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 SELECT Sname，Year of Birth: ，year(birthday)，ISLOWER(Sdept) FROM Student；,输出结果： Sname Year of Birth: year(birthday) ISLOWER(Sdept) - - - - 李勇 Year of Birth: 1980 cs 刘晨 Year of Birth: 1981 is 王名 Year of Birth: 1982 ma 张立 Year of Birth: 1981 is,例 使用列别名改变查询结果的列标题,SELECT Sname NAME，Year of Birth: BIRTH, BirthYear=year(birthday)，ISLOWER(Sdept) as DEPARTMENT FROM Student； 输出结果： NAME BIRTH BirthYear DEPARTMENT - - - - 李勇 Year of Birth: 1980 cs 刘晨 Year of Birth: 1981 is 王名 Year of Birth: 1982 ma 张立 Year of Birth: 1981 is,用：空格或AS,二、选择表中的若干元组,消除取值重复的行 在SELECT子句中使用DISTINCT短语。 假设SC表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80,例7 查询选修了课程的学生学号。 (1) SELECT Sno FROM SC; 或(默认 ALL) SELECT ALL Sno FROM SC; 结果： Sno - 95001 95001 95001 95002 95002,(2) SELECT DISTINCT Sno FROM SC; 结果： Sno - 95001 95002,注意: DISTINCT短语的作用范围是所有目标列。,查询满足条件的元组 WHERE子句常用的查询条件：,(1) 比较大小,在WHERE子句的中使用比较运算符 =，=，!，!= 30；,或 Select Sname,Sage From Student Where Sage!=30,(2) 确定范围,使用谓词： BETWEEN AND NOT BETWEEN AND 例1查询年龄在2029岁（包括20岁和29岁）之间的学生的姓名、年龄和所在系 SELECT Sname，year(getdate()-year(birthday)，Sdept FROM Student WHERE year(getdate()-year(birthday) BETWEEN 20 AND 29；,例1 查询年龄不在2029岁之间的学生姓名、年龄和系别。 SELECT Sname，year(getdate()-year(birthday) ， Sdept FROM Student WHERE year(getdate()-year(birthday) NOT BETWEEN 20 AND 29；,(3) 确定集合,使用谓词 IN , NOT IN ：用逗号分隔的一组取值 例1查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的学号、姓名和性别。 SELECT Sno，Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS );,例1查询既不是信息系、数学系，也不是计算 机科学系的学生的学号、姓名和性别。 SELECT Sno，Sname，Ssex FROM Student WHERE Sdept NOT IN ( IS，MA，CS ); 其它实例： 、查询考试成绩不及格学生的学号。 、查询学分在24之间的课程名称和学分。,case,(4) 字符串匹配,NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时： 可以用 = 运算符取代 LIKE 谓词 用 != 或 运算符取代 NOT LIKE 谓词,通配符,% (百分号) 代表任意长度（长度可以为0）的字符串。 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串。 _ (下横线) 代表任意单个字符。 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。,case,ESCAPE 短语：,当查询的字符串本身就含有 % 或 _ 时，需用ESCAPE 短语对通配符进行转义。 LIKE DB_Design ESCAPE ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符_不再具有通配符的含义，转义为普通的_字符。,例题:,1) 匹配模板为固定字符串 例1 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001； 等价于： SELECT * FROM Student WHERE Sno = 95001；,case,2) 匹配模板为含通配符的字符串 例1 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%； 例1 查询姓“欧阳”且全名为三个汉字的学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sname LIKE 欧阳_ _；,case,例18 查询名字中第2个字为“阳”字的学生的学号、姓名。 SELECT Sno，Sname FROM Student WHERE Sname LIKE _ _阳%； 例19查询所有不姓刘的学生学号、姓名、性别。 SELECT Sno， Sname，Ssex FROM Student WHERE Sname NOT LIKE 刘%；,case,3) 使用换码字符将通配符转义为普通字符 例20查询DB_sql课程的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_sqlESCAPE 例21 查询以“DB_“开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；,case,(5) 涉及空值的查询,使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例22 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。（该列允许为空，不是零值，即无成绩） SELECT Sno，Cno FROM SC WHERE Grade IS NULL；,case,case,例22 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；,(6) 多重条件查询,用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级 可用来实现多种其他谓词 NOT IN NOT BETWEEN AND ,case,例24查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND year(getdate()-year(birthday)20；,原例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ) 可改写为： SELECT Sname，Ssex FROM Student WHERE Sdept= IS OR Sdept= MA OR Sdept= CS ；,case,思考：？ 原例10 查询年龄在2029岁（包括20岁和29岁）之间的学生的姓名、系别和年龄。,case,（7）T-SQL：Top子句（选择） Top n子句：在查询结果中输出前面的n条记录； Top n percent子句，则在查询结果中输出前面占结果记录总数的n%条记录。 注：top 子句不能和Distinct关键字同时使用。 1）显示student表中，最前面的两条记录： select top 2 * from student 2）显示student表中，占总数10%条记录： select top 10 percent * from student,三、对查询结果排序,使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序 当排序列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示,例2 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；,case,SQLserver中执行后： Sno Grade - - 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55 95010 95024,例26查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按姓名降序排列。 SELECT * FROM Student ORDER BY Sdept，Sname DESC；,case,查询中集函数（又称为聚合函数）的使用 说明：DISTINCT：取消列中的重复值 ALL：为默认状态，表示不取消重复值。,例27 查询学生总人数。 SELECT COUNT(*) FROM Student； 例28 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC； 注：用DISTINCT以避免重复计算学生人数。,例29计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= 1； 例30查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHER Cno= 1；,四、对查询结果分组,使用GROUP BY子句分组 细化集函数的作用对象: 未对查询结果分组，集函数将作用于整个查询结果。 对查询结果分组后，集函数将分别作用于每个组 。,例31 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno； 结果 Cno COUNT(Sno） 1 22 2 34 3 44 4 33 5 48,GROUP BY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数,例32按学号查询选修了课程的学生的总平均成绩和总成绩。 SELECT Sno,avg(grade),sum(grade) FROM SC GROUP BY Sno,使用HAVING短语筛选最终输出结果,例33查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； 例34查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。 SELECT Sno, COUNT(*) FROM SC WHERE Grade90 GROUP BY Sno HAVING COUNT(*)3;,只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别： (1)作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。 ()集函数的使用 WHERE子句的条件表达式中不能有集函数。 HAVING短语的条件中可以使用集函数。,当WHERE子句、 GROUP BY子句、 HAVING子句和集函数同时出现在一个查询中时，SELECT命令的执行顺序如下： 执行WHERE子句，从表中选取行。 由GROUP BY对选取的行进行分组。 执行集函数。 执行HAVING子句选取满足条件的分组。,8、查询结果的分组与排序 例4：查询选修了2门以上课程的学生学号,并且查询结果按照学号升序排列。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 2 ORDER BY Sno ASC; 思考：order by 的作用范围？ 应是对order by 之前语句所查询到的结果集的排序。,该语句一定放置在最后！,9、T-SQL: computer子句: 能获得统计数据及相应统计的明细数据。 注：使用compute by子句时必须使用order by对compute by中的列进行排序。 Compute子句中的集函数中的列一定要出现在select子句的选择列表中。 语法格式： COMPUTE AVG | COUNT | MAX | MIN | STDEV | STDEVP| VAR | VARP | SUM ( expression ) ,.n BY expression ,.n ,Compute 集合函数（列名） 与 compute 集合函数 by 列名 的区别： Compute 集合函数（列名）：获得该列的统计值，及该列的详细信息（未分组）。 例： select sno,grade from sc compute avg(grade),Compute 集合函数（列名）by 列名：获得该列的统计值，及该列的详细分组信息。 如： select sno,grade from sc order by sno -须先按分类的字段排序 compute avg(grade) by sno,待续！,