（参考幻灯片）SQL复杂查询语句总结.ppt

第4章查询与视图,4.1SQL语言简介4.2SELECT数据访问基本方法4.3条件检索的SELECT语句4.4从多张表检索的SELECT语句4.5Union操作和子查询语句4.6SQL常用函数及其使用方法,1,4.1SQL语言简介,SQL(StructuredQueryLanguage):结构化查询语言，是一种介于关系代数与关系运算之间的语言，主要功能包括查询、操纵、定义和控制等方面，是一个通用的、功能极强的关系数据库语言。Transact-SQL的组成1）数据定义语言(DDLDataDefinitionLanguage)：用来建立数据库、数据库对象。如Createtable、view等。2）数据操纵语言(DMLDataManipulationLanguage):用来操纵数据库中的数据的命令。如select、insert、update、delete等。3）数据控制语言(DCLDataControlLanguage):用来控制数据库组建的存取权限等。如Grant、Revoke等。4）流程控制语言(FCLFlowControlLanguage):用来设计应用程序的语句。如if、while、case等。5）其他语言要素(ALEAdditionallanguageElement):包括变量、运算符、函数和注解等。,2,4.2SELECT数据查询,SELECT语句的基本格式如下：SELECTselect_listFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESCINTOnew_table,3,SELECT子句,SELECTALL|DISTINCTTOPnPERCENT:=*|table_name|view_name|table_alias.*|column_name|expression|AScolumn_alias|column_alias=expression,.n,4,4.2.1表中列的使用方法,1.选择所有字段SELECT*FROM表名如：usepubsselect*fromauthors(显示authors中的所有信息，全表查询）2.选择部分字段SELECT列名1,列名2,列名nFROM表名如：usestudentselect学号,姓名,性别from学生基本情况(显示学生基本情况中学号,姓名,性别字段的信息）,5,SELECT语句的使用方式,3.为字段设置别名SELECT列名1as新名1,列名2as新名2,列名nas新名nFROM表名(将选择字段的标题按新的名称显示）注意：新标题的名称可以有下列方式：1)在列表达式后面给出列名selectxh学号2)用“=”来连接列表达式select学号=xh3)新标题的名称用单引号、双引号括起来；4)用AS关键字来连接列表达式和指定的列名,6,SELECT语句,例如：查询authors中编号、姓名、电话、地址的信息，可以采用以下方式：selectau_id编号,au_lname姓,au_fname名,phone电话,address地址fromauthorsselect编号=au_id,姓=au_lname,名=au_fname,电话=phone,地址=addressfromauthorsselectau_id编号,au_lname姓,au_fname名,phone电话,address地址fromauthorsselectau_id编号,au_lname姓,au_fname名,phone电话,address地址fromauthorsselectau_idas编号,au_lnameas姓,au_fnameas名,phoneas电话,addressas地址fromauthors,7,SELECT语句,4.在选择列表中使用表达式在查询数据时，可以通过运算操作来控制从一个表中的返回值。例如：查询每个学生的总成绩、平均成绩。select学号,姓名,数学成绩+语文成绩+英语成绩as总成绩,(数学成绩+语文成绩+英语成绩)/3as平均成绩from学生基本情况,8,SELECT语句,4.消除字段数据的重复值在查询数据时，可能会有许多重复的数据。SQL提供的Distinct关键字，可以从select语句的结果集中消除重复的数据。例如：1)查询学生来至哪些院系的信息。selectdistinct院系名称from学生基本情况2)查询有哪些专业的学生。selectdistinct所学专业from学生基本情况,9,SELECT语句,6.限制记录的行数在限制查询记录的行数时，可以使用下列方式：1)使用topn:返回前n条记录；2)使用topnpercent:返回前n%条记录;3)使用setrowcountn:返回前n条记录。n=0关闭例如：1）显示前5条记录selecttop5*from学生基本情况2)显示20%学生的信息selecttop20percent*from学生基本情况3）对所有select语句，均显示5条记录。setrowcount5select*from学生基本情况,10,4.2条件子句的使用方法,1.条件子句最常用的条件子句是where和having，用它们来指定一系列条件，执行操作时只返回满足条件的记录。Having通常与Groupby一起使用，用来说明返回分组的条件。例如：1）显示男生的相关信息Select*from学生基本情况where性别=男2）显示男生人数超过10人的院系信息select院系名称,count(*)as男生人数from学生基本情况where性别=男groupby院系名称havingcount(*)10,11,4.2SELECT语句的查询条件,2算术表达式、比较运算符算术运算符有：+、-、*、/、%使用算术表达式的一般形式为：expressionoperatorexpression比较运算符：是最为常见的一种条件限制方式，用于测试两个表达式是否相同，返回值为True或False。WHERE子句中允许出现的比较运算符有：=（等于）、（大于）、=（对于等于）、（不等于）、!=（不等于）、!（不大于）、!=80and数学成绩=、(selectavg(数学成绩)FROM学生基本情况)思考：1）如何查询本专业大学英语成绩=60且低于平均分的学生信息？2)统计管理学院各专业高等数学低于平均分的人数。3）查询大学英语成绩最高分的学生信息。,39,Exists子查询,3）使用Exists的子查询使用Exists（或NotExists）引入子查询时，就相当于进行一次存在测试。外部查询的Where子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回True或False。例如：查询成绩表中女生的学习成绩。SELECT*FROM成绩表Whereexists(select*FROM学生基本情况where学号=成绩表.学号and性别=女)思考：1）如何查询管理学院的学生成绩？2）查询本专业高等数学不及格的学生信息？,40,练习讨论,数据表名：JBQK,数据表名：CJB,41,练习讨论,根据JBQK表、CJB表完成下列查询：1.查询高等数学成绩高于平均成绩的学生信息，显示学号、姓名、专业、高等数学等；2.查询信息管理专业男生的高等数学成绩、电子商务专业女生的高等数学成绩、能源学院所有学生的数学成绩，显示显示学号、姓名、性别、院系、专业、高等数学等，按院系、专业排序；3.查询与“刘文东”同专业的学生信息，显示显示学号、姓名、专业、各门课程成绩。,42,基于查询的数据表,1基于查询生成新的数据表如果需要将查询结果保存下来，使用INTO子句可以生成一个新表并将结果保存在这个新的数据表中。命令基本格式：Select选择字段表Into新的表名FROM已有的表where条件例如：1）将“学生基本情况”表复制成jbqkSelect*Intojbqkfrom学生基本情况,43,2)统计各个院系英语成绩的平均分、最高分、最低分、考试人数，结果放在Eng_tj表。SELECT学生基本情况.院系名称,AVG(成绩表.大学英语)AS英语平均,MAX(成绩表.大学英语)AS英语最高,MIN(成绩表.大学英语)AS英语最低,COUNT(*)AS考试人数INTOEng_tjFROM学生基本情况INNERJOIN成绩表ON学生基本情况.学号=成绩表.学号GROUPBY学生基本情况.院系名称思考：1）将管理学院学习成绩前10名学生的信息保存到数据表gl_10中，只保存学号、姓名、专业、各门课程成绩。2）如何将本专业不及格学生的信息保存在数据表bjg_xs?，保存学号、姓名、专业、各门课程成绩。3）将jbqk表的结构复制到jb_jg中。,44,将查询结果插入数据表,2向已有表插入数据用InsertIntoSelect语句，可以将一个子查询的结果添加到数据表中。命令格式：InsertInto数据表Select查询例如：创建临时数据表stu_1，存放从基本情况表、成绩表中查询出来的学号、姓名、性别、年龄、总成绩、平均成绩等信息。,45,createtable#stu_1(学号char(8),姓名nchar(4),性别nchar(1),年龄tinyint,总成绩int,平均成绩decimal(5,2)goinsertinto#stu_1selectjbqk.学号,jbqk.姓名,jbqk.性别,year(getdate()-year(出生日期),cjb.高等数学+cjb.大学英语+cjb.计算机基础+cjb.管理学,(cjb.高等数学+cjb.大学英语+cjb.计算机基础+cjb.管理学)/4from学生基本情况jbqkinnerjoin成绩表cjbonjbqk.学号=cjb.学号goselect*from#stu_1思考：如何将信息管理、电子商务专业学习成绩前5名的学生信息放到与基本情况表结构类似的数据表inf_ec中？,46,子查询修改记录,3用子查询修改记录Update语句中，Set子句、Where子句均可以使用子查询。例如：在基本情况表中添加字段“第1学期总分”、“第1学期平均”，并修改这些字段的值。altertable学生基本情况add第1学期总分smallint,第1学期平均decimal(5,2)goupdate学生基本情况set第1学期总分=(selectcjb.高等数学+cjb.大学英语+cjb.计算机基础+cjb.管理学from成绩表cjbwhere学生基本情况.学号=cjb.学号),第1学期平均=(select(cjb.高等数学+cjb.大学英语+cjb.计算机基础+cjb.管理学)/4from成绩表cjbwhere学生基本情况.学号=cjb.学号)goselect*from学生基本情况思考：1）对本专业高等数学不及格的学生，将数学成绩提高15%；2）将不是本专业学生的各门课程成绩清空；,47,子查询删除记录,4用子查询删除记录Delete命令的where子句可以使用子查询来从数据表中删除相关记录。例如：从基本情况表、成绩表中删除“男生”的相关记录。deletefrom成绩表whereexists(select*from学生基本情况jbqkwhere成绩表.学号=jbqk.学号andjbqk.性别=男)godeletefrom学生基本情况where性别=男goselect*from学生基本情况select*from成绩表思考：将本专业学生的成绩备份到dele_bak，并从cjb中删除；同时在jbqk表中添加字段Dele_flag、dele_table，并给dele_flag赋值1、给dele_table赋值备份表的名称。,48,SQL查询的增强功能,COMPUTE子句使用COMPUTE和COMPUTEBY子句既能浏览详细数据又可看到统计的结果。主要功能：1）生成合计作为附加的汇总放在结果集的最后。2）当与BY一起使用时，则在结果集生成控制中断与小计。3）同一查询内可同时用COMPUTE和COMPUTEBY子句。4）如果使用COMPUTEBY，则必须使用ORDERBY。,49,COMPUTE子句,其语法形式为：COMPUTE聚合函数(column_name),nBYcolumn_name,n注意：COMPUTEBY子句不能与SELECTINTO子句一起使用。COMPUTE子句中的列必须出现在SELECT子句的列表中。COMPUTEBY表示按指定的列进行明细汇总，使用BY关键字时必须同时使用ORDERBY子句，并且COMPUTEBY后出现的列必须具有与ORDERBY后出现的列相同的顺序，且不能跳过其中的列。例如：如果ORDERBY子句按照如下顺序指定排序列：ORDERBYa,b,c则COMPUTEBY后的列表只能是下面任一种形式：BYa,b,cBYa,bBYa,50,COMPUTE子句,应用举例：1）对管理学院学生的学号、姓名、高等数学、大学英语、数据库列出明细，并统计高等数学平均分、最高分及人数；2）对管理学院学生按专业列出学号、姓名、专业、高等数学的明细，并统计高等数学的平均、最高、最低成绩；3）按专业列出高等数学的明细，并统计各专业的平均、最高、最低成绩；然后计算全校的平均、最高、最低成绩。,51,WITHROLLUP,在GroupBy子句后使用withRollup，将对GroupBy指定的各列产生汇总行。例如：1）统计各专业人数及总人数。2）按专业统计男、女生人数，并统计专业总人数及学生的总人数。3）按学院、专业统计数据库的平均、最高分，同时统计各个学院以及全校的平均、最高成绩。,52,WITHCube,在GroupBy子句后使用withCube，将对GroupBy指定的各列的所有可能组合均产生汇总行。例如：按专业统计男、女生人数，并统计专业总人数及男、女生总人数、全部学生的总人数。Select所学专业,性别,count(*)as人数FromjbqkGroupby所学专业,性别withcube,53,常用字符串函数,1.ASCII(C):返回字符串最左端字符的ASCII值；例如：显示字符A、a、0的ASCII值selectascii(A)asA,ascii(a)asa,ascii(0)aszero2.CHAR(N):返回n(0-255)对应的字符；例如：显示十进制数65、97、48、32对应的字符selectchar(65),char(97),char(48),char(32)3.STR(FLOAT,LEN,DECIMAL):将数转换成字符串；FLOAT：表示要转换的数值；LEN:转换后的字符串的长度；DECIMAL:转换后的字符串中包含的小数位数。,54,常用字符串函数,说明：1）若没有给出长度，则对整数部分按默认长度10位进行转换；2）若给出的长度太短，则先满足整数部分，然后是小数部分；3）若给出的长度不能满足整数部分的长度，则显示n个*。例如：selectstr(123.45),str(123.45,6,2),str(123.45,5,2),str(123.45,3,2),str(123.45,2,2)思考：长度不够时，是否进行四舍五入？,55,常用字符串函数,4.SUBSTRING(C,START,LENGTH):截取子串；其中：C待截取的字符串；START截取字符的起始位置；LENGTH截取字符的长度。例如：SELECTSUBSTRING(12345ABCDEF,1,2),SUBSTRING(12345ABCDEF,5,5),SUBSTRING(12345ABCDEF,6,5),SUBSTRING(12345ABCDEF,10,5),SUBSTRING(12345ABCDEF,12,2)思考：1）位置、长度参数是否可以省略？2）显示基本情况表中，学号01开头的学生信息3）根据身份证号，显示1988年出生的学生信息,56,常用字符串函数,4.LEFT(C,LEN):从左边开始截取给定长度字符串；6.RIGHT(C,LEN):从右边开始截取给定长度字符串；7.LEN(C):返回字符串的字符个数（不包括尾部空格）8.LTRIM(C):删除字符串左端空格；9.RTRIM(C):删除字符串右端空格；10.CAST(表达式as类型):将表达式转换为SQL的某种类型。,57,字符函数应用举例,1显示姓名最后一个字为“强”的学生；select*from学生基本情况where姓名like%强%goselect*from学生基本情况whereright(rtrim(姓名),1)=强2显示身份证号带X的学生信息；select*from学生基本情况where身份证号like%X%goselect*from学生基本情况whereright(rtrim(upper(身份证号),1)=X3显示姓名为两个字的学生信息；select*from学生基本情况wherelen(rtrim(姓名)=24将学号、姓名、数学成绩组成一个显示内容；select学号+姓名+str(数学成绩,5)as组合显示from学生基本情况思考：1如何查询姓名仅一个或两个字的学生信息？2如何将学号、姓名、平均成绩进行组合显示？3如何显示“管院”学生的信息？,58,字符函数应用举例,写出下列语句的结果：1.selectascii(123)+ascii(char(65)+len(str(100+23)+len(ltrim(str(12*10+23+456/1000)aslen12.selectascii(str(456)+ascii(ltrim(str(456)+ascii(rtrim(str(456)aslen23.selectascii(str(456,3)+len(left(管理,2)+len(right(学院,2)+len(rtrim(str(789.123)aslen34.declares1char(100),s2char(50)sets1=西安科技大学管理学院sets2=信息管理与信息系统selectsubstring(s1,1,1)+substring(s1,3,1)+substring(s1,5,1)+substring(s2,1,1)+substring(s2,3,1)+char(48)+char(49)go,59,常用日期时间函数,Getdate():返回当前系统的日期和时间；Year(date):返回指定日期的年份；Month(date):返回指定日期的月份；Day(date):返回指定日期的某天；Dateadd(datepart,n,date):在指定日期上加上一段时间，返回新的日期时间；其中：Datepart:yyyy;mm;dd;ww/wk;hh;mi;ss;Datediff(datepart,startdate,enddate):返回两个日期的日期和时间的差值。Datename(datepart,date):返回指定日期部分的字符串。,60,日期时间函数举例,1）selectdateadd(yyyy,2,getdate()2）select学号,姓名,出生日期,datediff(yy,出生日期,getdate()as年龄from学生基本情况wheremonth(getdate()=month(出生日期)3)select2年后的今天是+datename(dw,dateadd(yyyy,2,getdate()as星期,61,日期时间函数举例,declareddatetime-声明局部变量setd=getdate()-将系统日期时间赋给变量select今天是+datename(yyyy,d)+年+datename(mm,d)+月+datename(dd,d)+日+datename(hh,d)+时+datename(mi,d)+分+datename(ss,d)+秒+datename(dw,d),62,日期时间函数举例,说明下列语句的作用：1.selecttop5姓名,datediff(yy,出生日期,getdate()as年龄from学生基本情况orderby2desc2.select学号,姓名,出生日期,year(getdate()-year(出生日期)asagefrom学生基本情况whereyear(出生日期)1982orderbyagedesc3.select学号,姓名,出生日期from学生基本情况whereyear(出生日期)between1982and1985orderby出生日期,63,日期时间函数举例,思考：1如何显示下一个月过生日的学生信息？2如何显示2008年年龄正好25岁的学生？3如何显示身份证出生日期与实际出生日期不