第八章sql语言

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,8,章,SQL(,结构化查询语言,),基础,SQL Server 2005,数据库,河北旅游职业学院 信息技术系,SQL(,结构化查询语言,),概述,SQL,简介,：,SQL,是结构化查询语言,（,Structure Query Language,）,的缩写,，简单的说，,SQL,是一种关系数据库语言，它可以访问以逻辑集的形式有序地储存在数据库的数据，这些逻辑集称为表。实际上,SQL,是一种计算机编程语言，但它比传统的编程语言，如，,BASIC,，,FORTRAN,等简单的多。,SQL,是高级的非过程化编程语言,，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的,不同数据库系统,，可以,使用相同的,SQL,语言,作为数据输入与管理的,SQL,接口。,SQL,语句可以嵌套，这使它具有极大的灵活性和强大的功能，可以用,SQL,语言可以写出非常复杂的语句。,SQL(,结构化查询语言,),概述,SQL,语言概述：,1,、产生：前身是,SQUARE,语言，,20,世纪,70,年代末由,IBM,圣约瑟研究实验室为关系数据库管理系统,SYSTEM R,开发的一种查询语言。,2,、发展：,1981,年，,IBM,商业,SQL/DS,关系型数据库系统，,SQL,得到广泛应用。,3,、规范：,1986,年，,ANSI,宣布,SQL,作为数据库工业标准,ANSI SQL-86,，,1992,年，,ISO,和,IEC,发布了,SQL,国际标准，称为,SQL-92,。,4,、,SQL/2006,：定义了,SQL,与,XML(,包含,XQuery,),的关联应用,SUN,公司将,SQL,基础的数据库管理系统嵌入,Java V6,；,SQL,发展至今，已经脱离其“结构化查询语言”最初设想，成为功能强大的数据操作语言。,SQL(,结构化查询语言,),概述,SQL,扩展：,SQL,是一门,ANSI,的标准计算机语言，用来访问和管理数据库系统。,SQL,语句用于取回和更新数据库中的数据。,SQL,可与数据库程序协同工作，比如,MS Access,、,DB2,、,Informix,、,MS SQL Server,、,Oracle,、,Sybase,以及其他数据库系统。,不幸的是，目前存在着很多不同版本的,SQL,语言，但是为了与,ANSI,标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如,SELECT,、,UPDATE,、,DELETE,、,INSERT,、,WHERE,等等）。 注意：除了,SQL,标准之外，大部分,SQL,数据库程序都拥有它们自己的私有扩展！,SQL(,结构化查询语言,),概述,SQL,的安全性：,由于,SQL,指令在部份进阶使用时，语法会依照特定条件来变换，而且若是表格中的字段过多时，许多开发人员都会习惯以字串组立方式建立,SQL,指令，而且又使用系统管理员级的帐户连到数据库，因此让黑客有机会利用,SQL,的组立方式进行攻击，像是在指令中添加部份刺探性或破坏性的指令,(,例如,DROP TABLE,、,DROP DATABASE,或是,DELETE * FROM,myTable,等具破坏性的指令,),，让数据库的资料或实体服务器被破坏，导致服务中断或是系统瘫痪等后果，此种攻击手法称为,SQL,注入（,SQL Injection,）。目前实务上较有效的防御方法，就是全面改用参数化查询，或是检查输入数据，过滤掉可能的危险指令或数据来防范。,SQL(,结构化查询语言,),概述,SQL,语言构成：,SQL,语言包括三种主要程序设计语言类别的语句：,数据定义语言,(DDL),，,数据操作语言,(DML),及,数据控制语言,(DCL),。,SQL,语言包含,4,个部分：,数据定义语言,(DDL),，例如：,CREATE,、,DROP,、,ALTER,等语句。,数据操作语言,(DML),，例如：,INSERT,（插入）、,UPDATE,（修改）、,DELETE,（删除）语句。,数据查询语言,(DQL),，例如：,SELECT,语句,数据控制语言,(DCL),，例如：,GRANT,、,REVOKE,、,COMMIT,、,ROLLBACK,等语句。,SQL(,结构化查询语言,),概述,SQL,的主要功能：,1,、建立数据库及表,2,、改变数据库系统环境设置,3,、针对某个数据库或表，授予用户存取权限,4,、对表创建索引,5,、修改数据库、表结构,6,、对数据库、表进行数据的新、删除和修改操作,7,、对表进行数据的查询操作,用语句创建数据库,SQL2005,使用的,T-SQL,语方是标准,SQL,（结构化查询语言）的增强版本。使用它提拱的,create database,语句可以完成新建数据库的操作。,CREATE DATABASE,语法格式,Create database,database_name,on primary,(name=,logical_name,filename=path,size=,database_size,maxsize,=,database_maxsize,filegrowth,=,growth_increment,),Filegroup,filegroup_name,(,CREATE DATABASE,语法格式,(name=,logical_name,filename=path,size=,database_size,maxsize,=,database_maxsize,filegrowth,=,growth_increment,),Log on,(name=,logical_name,filename=path,size=,database_size,maxsize,=,database_maxsize,filegrowth,=,growth_increment,),创建教务管理系统,Create database,教务管理系统,on primary,(name=,教务管理系统,_data,filename=d:downloads,教务管理系统,_,data.mdf,size=5mb,maxsize,=20mb,filegrowth,=5%),Log on,(name=,教务管理系统,_log,filename=d:downloads,教务管理系统,_,log.ldf,size=5mb,maxsize,=20mb,filegrowth,=5%),使用三个,100 MB,的数据文件和两个,100 MB,的事务日志文件创建名为,Archive,的数据库。主文件是列表中的第一个文件，并使用,PRIMARY,关键字显式指定。事务日志文件在,LOG ON,关键字后指定。注意,FILENAME,选项中所用的文件扩展名：主要数据文件使用,.,mdf,，次要数据文件使用,.,ndf,，事务日志文件使用,.,ldf,。*,/,/*,程序清单如下：*,/,CREATE DATABASE Archive,ON,PRIMARY,(NAME=Arch1,FILENAME=c:program files,microsoft,sql,servermssql.1mssqldataarchdat1.mdf,SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),( NAME = Arch2,FILENAME = c:program files,microsoft,sql,servermssql.1mssqldataarchdat2.ndf,SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),( NAME = Arch3,FILENAME = c:program files,microsoft,sql,servermssql.1mssqldataarchdat3.ndf,SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),LOG ON,( NAME = Archlog1,FILENAME = c:program files,microsoft,sql,servermssql.1mssqldataarchlog1.ldf,SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),(NAME=Archlog2,FILENAME = c:program files,microsoft,sql,servermssql.1mssqldataarchlog2.ldf,SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),练习：,创建了一个,Test,数据库，该数据库的主数据文件逻辑名称为,Test_data,，物理文件名为,Test.mdf,，初始大小为,10MB,，最大尺寸为无限大，增长速度为,10%,；数据库的日志文件逻辑名称为,Test_log,，物理文件名为,Test.ldf,，初始大小为,1MB,，最大尺寸为,5MB,，增长速度为,1MB,。,修改数据库,1,、修改数据库名称,ALTER DATABASE,语句,语法如下：,ALTER DATABASE,databasename,modify name=,newdatabasename,例：,Alter database,教务管理系统,modify name=,school_mis,修改数据库,Sp_renamedb,存储过程,Exec,sp_dboption,教务管理系统，,single,true,Exec,sp_renamedb,教务管理系统,school_mis,Exec,sp_dboption,single,false,修改数据库大小,Alter database,database_name,Modify file,(name=filename,size=,newsize,),增加数据库文件,Alter database,教务管理系统,Add file,(name=,教务管理系统,_data1,filename=d:downloads,教务管理系统,_data1.ndf,size=3mb,maxsize,=10mb,filegrowth,=10%,),收缩和删除数据库,收缩数据库,Dbcc,shrinkdatabase,(,教务管理系统，,10),该语句表示将数据库教务管理系统，并保留数据库,10%,的可用空间。,注：可用空间省略，表示数据库缩减到最小容量,删除数据库,Drop database,databasename,n,其中，,database_name,为要删除的数据库名，,【,n】,表示可以有多个数据库名,例：,drop database,教务管理系统,定义表,表的创建,使用,create table,来创建表，其格式如下：,Create table,(,数据类型,列级完整性约束定义,数据类型,列级完整性约束定义,）,例,8-1,在数据库教务管理系统中创建一个数,student,它由学号,sno,、姓名、性别、年龄和系别,sdept,五个属性组成，其学号属性不能为空,use,教务管理系统,go,create table student,(,sno,char(5) not null,sname,char(6),sex char(2),sage,int,sdept,char(15),),练习：在教务管理系统，创建学生信息表,字段名,类型,长度,是否为空,学号,int,-,not null,姓名,varchar,10,null,班级编号,int,-,null,性别,varchar,5,null,年级,varchar,5,null,政治面貌,varchar,5,null,民族,varchar,10,null,籍贯,varchar,30,null,学籍,varchar,5,null,增加列,Use,教务管理系统,Go,Alter table,班级信息,Add,班级荣誉,varchar(200) null,修改列的属性,Use,教务管理系统,Go,Alter table,班级信息,Alter column,班级荣誉,varchar(100),删除列,Use,教务管理系统,Go,Alter table,班级信息,Drop column,班级荣誉,cascade,Cascade,选项表示将列和列中的数据删除，而不管其他对象是否引用这一列。,在创建表时定义主键约束,在教务管理系统数据库中，新建一个学生信息表，给学号列定义一个主键约束,Create table,学生信息,（学号,int,primary key,姓名,nchar(5) not null,家庭住址,nvarchar(30) null,固定电话,char(20) null,),这种定义主键约束的方法是在表的创建过程中完成的。主键是一个隐性约束。,在创建表的过程中，也可以显式地指定一个主键约束。,Create table,学生信息,（学号,int,姓名,nchar(5) not null,家庭住址,nvarchar(30) null,固定电话,char(20) null,constraint,pk,_,学号,primary key(,学号,),),可以将,primary key,约束应用于一个列，如果希望将,primary key,约束应用于多个列，也就是所谓的表级,primary key,约束。,Create table,学生信息,（学号,int,姓名,nchar(5) not null,家庭住址,nvarchar(30) null,固定电话,char(20) null,constraint,pk,_,学号,primary key(,学号,姓名,),),假设在创建学生信息表时没有定义主键，那么就可能使用以下语句给学生信息列添加主键约束。,Alter table,学生信息,Add,Contraint,pk,_,学号,Primary(,学号,),删除主键约束,Alter table,学生信息,Drop Constraint,pk,_,学号,创建外键约束,Create table,学生信息,（学号,int,not null,姓名,nchar(5) not null,班级编号,varchar(14) not null,家庭住址,nvarchar(30) null,固定电话,char(20) null,constraint,pk,_,学号,primary key(,学号,),，,constraint,fk,_,班级编号,foreign key(,班级编号,),references,班级信息（班级编号）,),Create table,学生信息,（学号,int,姓名,nchar(5) not null,班级编号,varchar(14) references,班级信息,家庭住址,nvarchar(30) null,固定电话,char(20) null,),添加和删除外键约束,Alter table,学生信息,Add,Constraint,fk,_,班级编号,Foreign key(,班级编号,),References,班级信息（班级编号）,Alter table,学生信息,Drop Constraint,fk,_,班级编号,Check,约束,例： 新建成绩表，要求成绩的值在,0,到,100,之间,Use,教务管理系统,Go,Create table,成绩,(,编号,int,primary key,课程编号,int,references,课程表，,学号,int,references,学生信息,姓名,int,not null,成绩,int,contraint,ch_ck,成绩,check(,成绩,between 0 and 100),SQL(,结构化查询语言,),概述,SQL,语言的特点：,1,、它是一种非过程化的交互式语言，它对数据的处理是以,集合,为单位的，即每次处理一个记录集而不是每次处理一个单个记录。,SQL,对数据提供导航,，这意味着用户在高层的数据结构上工作，而不必指定数据的存取方法。,2,、,SQL,是一种所有用户都可以使用的语言，这些用户包括系统管员、数据库管理员，程序开发人员，应用程序员及其它许多的终端用户，,SQL,可在任何数据库产品中使用,，它可以用于数据库的操作。如数据查询、修改和删除，对表进行插入、修改和删除行。控制对数据库和数据对象的存取，保证数据库的一致性和完整性。,3,、,SQL,是所有关系数据库的,公共语言,。它是世界公认的标准的关系数据库语言。用户可方便地移植用,SQL,语言编写的程序。在以后的学习中，如不特殊说明，,SQL,指,Transact-SQL.,SQL,查询基础,一、,SQL,查询语法结构：,SELECT ALL | DISTINCT ,TOP expression PERCENT WITH TIES , INTO,new_table, FROM ,.n , WHERE , GROUP BY ALL ,group_by_expression, ,.n , WITH CUBE | ROLLUP , HAVING ,ORDER BY,order_expression,ASC|DESC, COMPUTE AVG|COUNT|MAX|MIN|SUM (expression) ,.n , BY expression ,.n ,一、投影查询,投影查询：说明：投影查询是最基本的,SELECT,语句，包括两个部分：要返回的,列,，和这些列源于的,表,。,最简查询,1,、语法结构：,Select *|,列名列表, From ,2,、主要功能：,对指定数据表进行查询，返回所有列或指定列,3,、注意事项：,（,1,）,*|,列名列表,用来指定返回结果：其中，,*,表示查询结果返回表中所有列；列名列表，查询结果返回指定列,（,2,）,为查询的数据来源，即表对象,（,3,）特殊用法：可以在列名列表中用,AS,关键字来设定列别名,投影查询,例：从表学生信息表（教务管理系统）中，查询所有学生的学号、姓名、政治面貌和籍贯,Use,教务管理系统,Select,学号,姓名,政治面貌,籍贯,From,学生信息,例,2,查询表学生信息的所有数据,Use,教务管理系统,Select,*,From,学生信息,投影,查询,相异查询：,1、语法结构：,Select Distinct from ,2、主要功能：,消除返回查询结果中的重复行,3,、注意事项：,（,1,）,DISTINCT,关键字可从,SELECT,语句的结果中除去重复的行。如果没有指定,DISTINCT,关键字，那么将返回所有行，包括重复的行。,（,2,）,Distinct,关键字只对其后的指定列组合值的进行唯一性取值,（,3,）对于,DISTINCT,关键字来说，空值将被认为是相互重复的内容。不论遇到多少个空值，结果中只返回一个,NULL,。,投影查询,练习：查询学生信息中学生的籍贯,Use,教务管理系统,Select,籍贯,From,学生信息,例：查询学生的不同籍贯,Use,教务管理系统,Select distinct,籍贯,From,学生信息,TOP,查询,在某些情况下，我们希望查询结果中只返回部份行（可以是某一数量的行也可以是某一百分比数量的行），这时可以借助,TOP,子句来实现。,1,、语法格式：,Top (n) Percent,2,、注意事项：,Top (n):,从查询结果集中返回前,n,行,(n=,结果集总行数,).,Top (n) Percent:,从查询结果集中返回前,n%,行（,0=n=100,自动舍入法进行百分数计算）,.,Top,子句通常与,Order By,子句同时使用，控制排序输出的结果,.,TOP,子句用在,SELECT,列名列表的前面,.,为保证向后兼容性，支持在,SELECT,使用不包含括号的,TOP n,，但不推荐这种用法,.,例,:,Use,教务管理系统,Select distinct top 8,籍贯,From,学生信息,使用别名,使用,select,语句查询数据时，可以使用别名的方法根据需要对数据显示的标题进行修改，或者为没有标题的列增加临时的标题。,方法,1,：,Use,教务管理系统,Select,学号,N0.,姓名 ,Name,性别 ,Sex,年级 ,Grade,籍贯 出生所在地,From,学生信息,使用别名,方法,2,使用,SQL Server2005,支持的“,=”,符号连接表达式。,Use,教务管理系统,Select N0.=,学号,Name=,姓名,Sex =,性别,Grade=,年级,出生所在地,=,籍贯,From,学生信息,使用别名,方法,3,：在指定列标题时，使用,AS,关键字来连接列表达式的指定的列名。,Use,教务管理系统,Select,学号,as N0.,姓名,as Name,性别,as Sex,年级,as Grade,籍贯,as ,出生所在地,From,学生信息,对列名进行操作时，注意以下问题。,1,、当使用中文别名时，可以不写列号，但不能使用全角引号,2,、当使用的英文名超过两个单词时，必须使用引号将别括起来,计算列,在进行数据查询时，经常需要对查询的数据进行再次计算。这时可在,SELECT,语句中使用计算列完成。,USE,教务管理系统,SELECT,编号，学号，调整前成绩,=,成绩，成绩,-10 ,调整后成绩,From,成绩表,二、条件查询,条件查询,说明：SELECT 语句包括要返回的,列,，列源于的,表,以及查询结果应满足的,条件,。,1,、基本语法结构：,Select *|,列名列表, From ,Where ,2,、条件表达式：（,1,）定义：用来对操作的范围进行限定的关系或逻辑表达式形式。（,2,）,WHERE,子句指定查询条件。,条件查询,Use,教务管理系统,Select,学号,姓名,性别,民族,籍贯,From,学生信息,Where,籍贯,=,河南,Use,教务管理系统,Select,学号,姓名,性别,民族,籍贯,From,学生信息,Where,籍贯,=,河南,and,民族,=,汉,条件查询分类,区间查询说明：区间查询是一种特殊的条件查询，其查询条件是用区间表示的一种条件范围。,1,、语法结构：,Select *|,列名列表, From ,Where ,2,、主要功能：,查询结果返回满足指定区间条件的所有行,3,、注意事项：,（,1,）区间表达式用,Between and ,表述，其中,A,、,B,分别表示区间起始和终止值。,（,2,）,A,、,B,的数据类型与列名一致,（,3,）在,SQL SERVER,查询中,between and,是闭区间,条件查询分类,例：在“教务管理系统”数据库中查询出成绩在,70,与,80,之间的学生的编号、学号、课程编号和成绩信息。,Select,编号,学号,课程编号,成绩,From,成绩表,Where,成绩,between 70 and 80,练习：,1,、查询出成绩不在,70,与,80,之间的学生的编号、学号、课程编号和成绩信息。,2,、在“人事管理系统”数据库中查询从,2004-5-01,到,2007-01-01,的日期内入职的员工信息，包括员工编号、员工姓名、性别和入职时间,条件查询分类,IN,查询,(,组内查询,),IN,查询是一种特殊的条件查询，,IN,查询将查询符合指定,IN,列表中各值的所有行。,1,、语法结构：,Select *|,列名列表, From ,Where ,2,、主要功能：,查询结果返回满足指定,IN,列表中值的所有行,3,、注意事项：,（,1,）,IN,表达式结构为值列表形式：,In(Value1,Value2,),（,2,）列（列表达式）与,IN,表达式的数据类型一致,（,3,）可嵌套子查询（后继课程会重点讲述）,例：从“教务管理系统”数据库的“学生信息”表中，查询出“籍贯”是“湖南”，“湖北”和“江西”的学生的学号、姓名、性别、民族和籍贯信息。,Select,学号,姓名,性别,民族,籍贯,From,学生信息,Where,籍贯,IN,（ “湖南”，“湖北”，“江西” ）,条件查询分类,模式查询,（,匹配查询,）,模式查询是根据确定特定字符串是否与指定模式相匹配，而返回查询结果的一种查询。,1,、语法结构：,Select *|,列名列表, From ,Where Like Escape,2,、主要功能：,当不知道完全精确的値时，可以使用匹配模式表达式的方法查询符合条件的行,(,有时也称模糊查询,),3,、注意事项：,（,1,）模式表达式是由常规字符和通配符构成,（,2,）数据类型应为字符型（包括日期时间型）,条件查询分类,模式查询（匹配查询）,4,、模式表达式中的通配符号,通配符,意义,说明,%,通配任意字符串,汉字占用两个字符位，只用,_,代替,_,通配任意单字符,指定范围单字符,指定一个区间,a-d,或集合,abcd,连续,非范围内单字符,注意：如果我们在查询时，要将这些通配符号当成普通字符使用时，应该使用转义符号，将这些通配符号转为普通意义的字符,例如，要查询次品率是,15%,的所有记录时，,”%”,已定义为通配符号，不能直接使用，应该进行转义：,WHERE,次品率,LIKE %15!% ESCAPE !,模式查询,根据表中列出的通配符，下面给出一些常用的示例。,M%,表示查询以,M,开头的任意字符串，如,MILK,%M,表示查询以,M,结尾的任意字符串，如,ROOM,%M%,表示查询在任意位置包含字母,M,的所有字符串，如,SOME,、,MAN,_M,表示查询以任意一个字符开头，以,M,结尾的两位字符串，如,AM,、,PM,模式查询,MAI%,表示查询以,M,开头，第二个字符是,A,或,I,的所有字符串，如,MACHINE,、,MISS,MAI%,表示查询以,M,开头，第二个字符不是,A,或,I,的所有字符串，如,MACHINE,、,MISS,A-M%,表示查询以,A,到,M,之间的任意字符开头的字符串，如,JOB,、,MAIL,A-M%,表示查询以,A,到,M,之间的任意字符开头的字符串，如,NOT,、,ZOO,模式查询,例：在“教务管理系统”数据库的“学生信息”表中，查询姓名包含“红”的学生的学号、姓名、性别、民族、籍贯和班级编号信息,Select,学号,姓名,性别,民族,籍贯,班级编号,From,学生信息,Where,姓名,like %,红,%,条件查询,空值查询,某个字段没有值称之为具有空值（,NULL,）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。,1,、语法结构：,Select *|,列名列表, From ,Where Is null,2,、注意事项：,（,1,）在,SQL Server,中，表述空值的方法,（,2,）空值查询在实际工作中的意义,空值查询,例：在“教务管理系统”数据库中，查询还未分配班主任的班级信息,Select,班级编号,年级,班级名称,人数,班主任,From,班级信息,Where,班主任,is null,查询排序,查询排序：,将查询返回的结果，按指定要求进行升序或降序的排列。,1,、语法格式：,Order by ,升,|,降序,2,、参数说明：,排序依据列，可以是表中的一个列名，列别名，表达式。,可选项,升,|,降序,，指定排序方式，其中，升序：,Asc,；降序：,Desc,。,排序只是结果集中记录的逻辑顺序的改变，不会影响表中记录的物理顺序。,查询排序,3,、查询排序的注意事项：,(1),、排序时可指定多个排序列，排序列序列定义排序结果集的结构，多列间用“，”分开。,(2),、不能对,ntext,、,text,和,image,等列进行排序。,(3),、排序方式，可根据需要指定升,|,降序，空值被系统默认为最低的可能值。不指定方式时默认为升序。,(4),、,ORDER BY,子句可包括未出现在此选择列表中的项目。然而，如果指定,SELECT DISTINCT,，则排序列必定出现在选择列表中。,(5),、当,SELECT,语句包含,UNION,运算符时，列名或列的别名必须是在第一选择列表内指定的列名或列别名。,查询排序,例：从数据库“教务管理系统”的“班级信息”表中，按照班级的人数进行降序排列,Select,班级编号,年级,班级名称,人数,班主任,From,班级信息,Order by,人数,desc,查询排序,扩展：,如果用户对表比较熟悉，在对列进行排序时，可以直接指定列在表中的位置号，以方便操作。,例如，“人数”列在“班级信息”表的第,4,位，上列可改为,Select,班级编号,年级,班级名称,人数,班主任,From,班级信息,Order by 4 desc,查询排序,例：从数据库“教务管理系统”的“班级信息”表中，按照班级的人数进行降序排列,如果人数相同再按照年级过行升序排列,Select,班级编号,年级,班级名称,人数,班主任,From,班级信息,Order by,人数,desc,年级,asc,聚合函数,聚合函数的概念：,对,一组行中的某列,执行,计算,并返回,单一值,的函数。,聚合函数的特点：,1,、所有聚合函数都具有确定性 ，任何时候用一组给定的输入值调用它们时，都返回相同值,2,、除,COUNT(*),之外，聚合函数忽略空值,3,、用聚合函数，默认情况下，汇总信息包含所有行,4,、聚合函数可以在以下情况中作为表达式使用,（,1,）,SELECT,语句的选择列表,（,2,）,COMPUTE,或,COMPUTE BY,子句。,（,3,）,HAVING,子句,聚合函数,(,一,)Max(),函数,1,、格式：,Max( ALL|DISTINCT,列表达式,),由,Select,引导,2,、功能：返回表达式（指定列）的最大值。,3,、参数说明：,（,1,）,ALL,对所有的值进行聚合函数运算。,ALL,是默认设置。,（,2,）,DISTINCT,指定每个唯一值都被考虑。,DISTINCT,对于,MAX,无意义，使,用它仅仅是为了符合,SQL-92,兼容性。,（,3,）列表达式,常量、列名、函数以及算术运算符、按位运算符和字符串运算,符的任意组合，我们通常指定求最大值的列名。,聚合函数的分类：,在,SQL Server,系统中，常用的聚合函数：,聚合函数,聚合函数,函数功能描述,AVG(,列参数,),列值平均值。该列只能包含数字数据。,COUNT(,列参数,) COUNT(*),列值的计数（如果指定列名）忽略空值,表中所有行的计数（如果指定 *）,MAX(,列参数,),列中最大的值,MIN(,列参数,),列中最小的值,SUM(,列参数,),列值的合计 （求和）,聚合函数,4,、注意事项：,（,1,）,MAX,可用于数值列、字符列和,datetime,列，但不能用于,bit,列。不允许使用聚合函数和子查询。,（,2,）字符列的最大值，按字符（包括汉字）字母表的顺序递增。,（,3,）日期时间型列的日期时间越大值越大,（,4,）,Max,函数通常与,As,关键字同时使用，以标识最大值的含义。,（,5,）返回数据类型与,列表达式,的类型相同。,聚合函数,（二）,in(),函数,1,、格式：,in( ALL | DISTINCT ,列表达式,),2,、功能：返回表达式的最小值。,3,、参数说明,（,1,）,ALL,对所有的值进行聚合函数运算。,ALL,是默认设置。,（,2,）,DISTINCT,指定每个唯一值都被考虑。,DISTINCT,对于,Min,无意,义，使用它仅仅是为了符合,SQL-92,兼容性。,（,3,）列表达式,常量、列名、函数以及算术运算符、按位运算符和字符,串运算符的任意组合，我们通常指定求最小值的列名。,聚合函数,4,、注意事项：,（,1,）,Min,可用于数值列、字符列和,datetime,列，但不能用于,bit,列。不允许使用聚合函数和子查询。,（,2,）字符列的最小值，按字符（包括汉字）字母表的顺序递增,（,3,）日期时间型列的日期时间越小值越小,（,4,）,Min,函数通常与,As,关键字同时使用，以标识最小值的含义。,（,5,）返回数据类型与 列表达式的相同,（三）,Avg,函数,、格式,: AVG( ALL | DISTINCT ,列表达式,),、用途：返回组中值的平均值（空值将被忽略）,、参数说明：,（）,ALL,对所有的值进行聚合函数运算。,ALL,是默认设置。,（）,DISTINCT,指定,AVG,操作只使用每个值的唯一实例，而不管该值,出现了多少次。,（）列表达式,精确数字或近似数字数据类型类别的表达式（,bit,数据,类型除外）。不允许使用聚合函数和子查询,（四）,Sum,函数,、格式：,Sum( ALL | DISTINCT ,列表达式,),、功能：返回表达式中所有值的和，或只返回,DISTINCT,值。,SUM,只能用于数字列。空值将被忽略。,、参数说明：,（）,ALL,对所有的值进行聚合函数运算。,ALL,是默认设置。,（）,DISTINCT,指定,SUM,返回唯一值的和。,（）列表达式,是常量、列或函数，或者是算术、按位与字符串等运算符的任意组,合。列表达式是精确数字或近似数字数据类型分类（,bit,数据类型,除外）的表达式。不允许使用聚合函数和子查询。,（五）,Count,函数,1,、格式：,COUNT ( ALL | DISTINCT ,列表达式, | * ),2,、功能：返回组中项目的数量,3,、参数说明：,（）,ALL,对所有的值进行聚合函数运算。,ALL,是默认设置。,（）,DISTINCT,指定,COUNT,返回唯一非空值的数量。,（）列表达式,一个表达式，其类型是除,text,、,image,或,ntext,之外的任何类型。不允许使用聚合函数和子查询。,（）*,指定应该计算所有行以返回表中行的总数。,COUNT(*),不需要任何参数，而且不能与,DISTINCT,一起使用。,COUNT(*),根据定义，该函数不使用有关任何特定列的信息。,COUNT(*),返回指定表中行的数量而不消除副本。它对每行分别进行计数，包括含有空值的行。,Excel,表格实现小计和总计的实例,分组查询,一、分组查询的概念：,对结果集，按照指定的条件进行分组放置,二、语法格式：,Group by Having,条件子句,三、参数说明：,，是指定结果集按哪种分组条件（列）进行输出。,四、,Having,条件子句：,在分组查询时，我们通常用,Having,条件子句指定组或聚,合的搜索条件 。,HAVING,子句允许为每一个组指定条件，换句,话说，可以根据指定的条件来选择行。如果想使用,HAVING,子,句，它应该在,GROUPBY,子句之后。,例： 要在“教务管理系统,”,数据库的“班级信息”表中，按照年级查询出该年级各班的总人数及班级的数量。,Select,年级,sum(,人数,) as,年级总人数,count(,班级编号,) as,班级数量,From,班级信息,Group by,年级,分组查询,例：按照学号、课程编号和成绩分组，并列出学生成绩大于,80,的考试信息,Select,学号,课程编号,成绩,From,成绩表,Where,成绩,80,Group by,学号,课程编号,成绩,分组查询,五、注意事项：,1,、选择列表中任一非聚合表达式内的所有列都应包含在,GROUP BY,分组依据列表中,2,、选择列表内定义的列的别名不能用于指定分组列。,3,、,text,、,ntext,和,image,类型的列不能用于分组依据列。,4,、可指定多个分组列，列间用“，”分开,5,、,having,子句用来筛选满足条件的组,6,、访问远程表时不能进行分组。,例：从数据库“教务管理系统”的“学生信息”表中，按照“学号,”,、“姓名”、“年级”和“籍贯”进行分组，并筛选出,2001,、,2002,和,2003,年级的学生信息,Select,学号,姓名,年级,籍贯,From,学生信息,Group by,学号,姓名,年级,籍贯,Having,年级,in(2001,2002,2003),Into,子句,Into,子句的功能是将查询结果保存到新的基表中,Use,教务管理系统,go,Select,学号,姓名,年级,籍贯,Into,学生基本信息,From,学生信息,多表查询,多表查询基础,在实际查询应用中，用户所需要的数据并不全部都在一个表或视图中，而可能在多个表中，这时就要使用多表查询。多表查询用多个表中的数据来组合，再从中获取所需要的数据信息。多表查询实际上是通过各个表之间的共同更的相关性来查询数据的。多表查询首先要在各个表之间建立连接。,多表查询,在进行多表查询操作时，最简单的连接方式就是在,select,语句列表中引用多个表的字段，在,from,子句中用半角逗号将不同的基表隔开。,用户在进行基本连接操作时，可以遵循以下基本原则。,1,、,select,子句列表中，每个目标前都要加上基表名称。,2,、,from,子句应包括所有使用的基表,3,、,where,子句应定义一个同等连接。,多表查询,例：从“教务管理系统”数据库中的“学生信息”和“班级信息”两表中，查询学生和对应班级信息，要求返回包含学生的学号、姓名、性别和班级名称及年级。,Use,教务管理系统,go,select,学生信息,.,学号,学生信息,.,姓名,学生信息,.,性别,班级信息,.,班级名称,班级信息,.,年级,from,学生信息,班级信息,Where,学生信息,.,班级编号,=,班级信息,.,班级编号,多表查询,扩展练习：从“教务管理系统”数据库中的“学生信息”和“班级信息”两表中，查询学生和对应班级信息，要求返回包含学生的学号、姓名、性别和班级名称及年级，且所有年级中籍贯为“河南”并且读美术的学生和班级信息。,多表查询,Use,教务管理系统,go,select,学生信息,.,学号,学生信息,.,姓名,学生信息,.,性别,班级信息,.,班级名称,班级信息,.,年级,from,学生信息,班级信息,Where,学生信息,.,班级编号,=,班级信息,.,班级编号,And,学生信息,.,籍贯,=,河南,And,班级信息,.,班级名称,like %,美术,%,多表查询,当进行多于两个基表的连接操作时，如果需要引用多个目标列，而且每个列都要使用基表名称来限定，可以使用基表定义别名的方法来简化语句。,Use,教务管理系统,go,select s.,学号,s.,姓名,s.,性别,c.,班级名称,c.,年级,from,学生信息,as s,班级信息,as c,Where s.,班级编号,=c.,班级编号,内连接,等值连接,等值连接查询是在连接条件中使用比较运算符等于号（,=,）来比较连接列的列值，其查询结果中列出被连接表中的所有列。,例：,Select s.,学号, s.,姓名, s.,性别, s.,籍贯,c.,班级名称,c.,年级,From,学生信息,s inner join,班级信息,c,On s.,班级编号,=c.,班级编号,内连接,非等值连接,非等值连接查询就是在连接条件中使用除了等于号之外的比较运算符，来比较连接列的列值。,例：在“教务管理系统”数据库中的“学生信息”表和“成绩”表中，查询出所有考试及格的学生的成绩信息，包括学生的学号、姓名、性别、年级、班级编号及考试成绩。并且按照成绩进行降序排列。,非等值连接,Use,教务管理系统,go,Select s.,学号,s.,姓名,s.,性别,s.,年级,s.,班级编号,j.,成绩,From,学生信息,s inner join,成绩表,j,On s.,学号,=j.,学号,And j.,成绩,=60,Order by j.,成绩,desc,外连接,内连接只能返回满足连接条件的记录，外连接，不但返回所有满足连接条件的记录，还返回部分不满足条件的记录,在外连接中参与连接的表有主从之分，以主表中的每行数据去匹配从表中数据，如果符合连接条件，则直接返回查询结果中；如果主表中的行在从表中没有找到匹配的行，在内连接中将丢弃不匹配的行。在外连中的主表的行仍然保留，并且返回到查询结果中，相应的从表的数据被填上空值后也返回到查询结果中,左外连接,1,、左外连接,返回所有的匹配的行并从关键字,join,左边的表中返回不匹配的行。,A,B,C,A1,B1,C1,A2,B2,C2,A3,B3,C3,A4,B4,C4,C,D,C1,D1,C2,D2,C4,D3,C5,D4,R,S,A,B,C,C,D,A1,B1,C1,C1,D1,A2,B2,C2,C2,D2,A3,B3,C3,NULL,NULL,A4,B4,C4,C4,D3,R,左外连接,S ON R.C=S.C,左外连接,例：在“教务管理系统”数据库的“学生信息”表中，一个学号对应一个学生，在“成绩表”中保存了所有学生的考试成绩，而且在“学生信息”表中的学生并不都有考试成绩。所以可以使用这两个表做左外连接,USE,教务管理系统,GO,Select A.,学号, A.,姓名, B.,课程编号,B.,成绩,From,学生信息,A LEFT OUTER JOIN,成绩表,B,ON A.,学号,=B.,学号,右外连接,右外连接,返回所有的匹配的行并从关键字,JOIN,右边的表中返回所有不匹配的行,A,B,C,A1,B1,C1,A2,B2,C2,A3,B3,C3,A4,B4,C4,C,D,C1,D1,C2,D2,C4,D3,C5,D4,R,S,A,B,C,C,D,A1,B1,C1,C1,D1,A2,B2,C2,C2,D2,A4,B4,C4,C4,D3,null,null,null,C5,D4,R,右外连接,S ON R.C=S.C,右外连接,USE,教务管理系统,GO,Select A.,学号, A.,姓名, B.,课程编号,B.,成绩,From,学生信息,A RIGHT OUTER JOIN,成绩表,B,ON A.,学号,=B.,学号,全外连接,完全连接：返回两个表中所有匹配的行和不匹配的行,A,B,C,A1,B1,C1,A2,B2,C2,A3,B3,C3,A4,B4,C4,C,D,C1,D1,C2,D2,C4,D3,C5,D4,R,S,A,B,C,C,D,A1,B1,C1,C1,D1,A2,B2,C2,C2,D2,A3,B3,C3,null,null,A4,B4,C4,C4,D3,null,null,null,C5,D4,R,右外连接,S ON R.C=S.C,全外连接,USE,教务管理系统,GO,Select A.,学号, A.,姓名, B.,课程编号,B.,成绩,From,学生信息,A RIGHT OUTER JOIN,成绩表,B,ON A.,学号,=B.,学号,交叉连接查询,交叉连接指返回被连接的两个表所在数把的笛卡乘积，即返回的数据行数等于第一个表中符合查询条件的数据的行数，乘以第二个表中符合查询条件的数据的行数,从交叉连接的语句及其返回结果中可以看出，实院交叉连接和使用逗号的基本连接操作非常相似，唯一不同之处在于交叉连接使用,cross join,关键字，而基本连接使用逗号操作符,交叉连接查询,例：在“人事管理系统”数据库中，对“员工信息”表和“部门信息”表进行交叉查询，要求返所任职位为“职员”的员工编号、员工姓名、所任职位、文化程序和部门名称，并按照员工编号降序排列。,use,人事管理系统,select a.,员工编号, a.,员工姓名, a.,所任职位, a.,文化程度, b.,部门名称,from,员工信息,a ,部门信息,b,where a.,所在部门编号,= b.,部门编号,and a.,所任职位,=,职员,order by a.,员工编号,desc,自连接,连接操作不仅可以在两个或多个基表之间进行，一个表内也可以进行连接查询，这种连接方式称为自