SQL查询语句大全

第4章查询与视图4.1SQL语言简介4.2SELECT数据访问基本方法4.3条件检索的SELECT语句4.4从多张表检索的SELECT语句4.5Union操作和子查询语句4.6SQL常用函数及其使用方法4.1SQL语言简介1.SQL(StructuredQueryLanguage):结构化查询语言，是一种介于关系代数与关系运算之间的语言，主要功能包括查询、操纵、定义和控制等方面，是一个通用的、功能极强的关系数据库语言。2.Transact-SQL的组成1）数据定义语言(DDLDataDefinitionLanguage)：用来建立数据库、数据库对象。如Createtable、view等。2）数据操纵语言(DMLDataManipulationLanguage):用来操纵数据库中的数据的命令。如select、insert、update、delete等。3）数据控制语言(DCLDataControlLanguage):用来控制数据库组建的存取权限等。如Grant、Revoke等。4）流程控制语言(FCLFlowControlLanguage):用来设计应用程序的语句。如if、while、case等。5）其他语言要素(ALEAdditionallanguageElement):包括变量、运算符、函数和注解等。4.2SELECT数据查询SELECT语句的基本格式如下：SELECTselect_listFROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]][INTOnew_table]SELECT子句SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_listselect_list::={*|{table_name|view_name|table_alias}.*|{column_name|expression|}[[AS]column_alias]|column_alias=expression}[,...n]4.2.1表中列的使用方法1.选择所有字段SELECT*FROM表名如：usepubsselect*fromauthors(显示authors中的所有信息，全表查询）2.选择部分字段SELECT列名1[,列名2,…列名n]FROM表名如：usestudentselect学号,姓名,性别from学生基本情况(显示学生基本情况中学号,姓名,性别字段的信息）SELECT语句的使用方式3.为字段设置别名SELECT列名1as新名1[,列名2as新名2,…列名nas新名n]FROM表名(将选择字段的标题按新的名称显示）注意：新标题的名称可以有下列方式：1)在列表达式后面给出列名selectxh学号2)用“=”来连接列表达式select学号=xh3)新标题的名称用单引号、双引号括起来；4)用AS关键字来连接列表达式和指定的列名SELECT语句例如：查询authors中编号、姓名、电话、地址的信息，可以采用以下方式：1.selectau_id编号,au_lname姓,au_fname名,phone电话,address地址fromauthors2.select编号=au_id,姓=au_lname,名=au_fname,电话=phone,地址=addressfromauthors3.selectau_id'编号',au_lname'姓',au_fname'名',phone'电话',address'地址'fromauthors4.selectau_id编号,au_lname姓,au_fname名,phone电话,address地址fromauthors5.selectau_idas编号,au_lnameas姓,au_fnameas名,phoneas电话,addressas地址fromauthorsSELECT语句4.在选择列表中使用表达式在查询数据时，可以通过运算操作来控制从一个表中的返回值。例如：查询每个学生的总成绩、平均成绩。select学号,姓名,数学成绩+语文成绩+英语成绩as总成绩,(数学成绩+语文成绩+英语成绩)/3as平均成绩from学生基本情况SELECT语句4.消除字段数据的重复值在查询数据时，可能会有许多重复的数据。SQL提供的Distinct关键字，可以从select语句的结果集中消除重复的数据。例如：1)查询学生来至哪些院系的信息。selectdistinct院系名称from学生基本情况2)查询有哪些专业的学生。selectdistinct所学专业from学生基本情况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学生基本情况4.2条件子句的使用方法1.条件子句最常用的条件子句是where和having，用它们来指定一系列条件，执行操作时只返回满足条件的记录。Having通常与Groupby一起使用，用来说明返回分组的条件。例如：1）显示男生的相关信息Select*from学生基本情况where性别=‘男’2）显示男生人数超过10人的院系信息select院系名称,count(*)as男生人数from学生基本情况where性别='男'groupby院系名称havingcount(*)104.2SELECT语句的查询条件2．算术表达式、比较运算符算术运算符有：+、-、*、/、%使用算术表达式的一般形式为：expressionoperatorexpression比较运算符：是最为常见的一种条件限制方式，用于测试两个表达式是否相同，返回值为True或False。WHERE子句中允许出现的比较运算符有：=（等于）、（大于）、=（对于等于）、（小于）、=（小于等于）、（不等于）、!=（不等于）、!（不大于）、!（不小于）4.2SELECT语句的查询条件3．逻辑表达式在Transact-SQL中可以使用的逻辑运算符有三个：NOT：逻辑反，对指定的布尔表达式求反。AND：逻辑与，只有当两个条件都是TRUE时取值为TRUE。OR：逻辑或，当两个条件中任何一个条件是TRUE时，取值为TRUE。优先顺序：先Not，再And，后Or4.2SELECT语句的查询条件4．BETWEEN条件BETWEEN用于搜索介于两个指定值之间的所有信息，且包括两个指定的值。格式为：条件字段[NOT]BETWEENbegin_expressionANDend_expression例如：查询数学成绩在80到90之间的信息。Select*from学生基本情况where数学成绩between80and90Between是AND的简化用法，上面的语句等价于：Select*from学生基本情况where数学成绩=80and数学成绩=904.2SELECT语句的查询条件4.IN列表搜索条件IN列表搜索条件用于返回与给定的列表中任意一个值相匹配的记录。格式为：条件字段[NOT]IN(列表选项)例如：查询数学成绩为70、80、90的信息。select*from学生基本情况where数学成绩in(70,80,90)In列表条件是OR的简化形式，上面语句等价于：select*from学生基本情况where数学成绩=70OR数学成绩=80OR数学成绩=904.2SELECT语句的查询条件6．LIKE匹配模式LIKE匹配模式是确定条件字符串是否与指定的模式匹配。使用格式：条件字段[NOT]LIKE匹配模式SQL中的有效模式包括：%：可匹配任意类型和长度的字符串。Like‘李%’_（下划线）：可匹配任何单个字符。Like‘71005_’[]：指定范围或集合中的任何单个字符。Like[a-d][^]：不属于指定范围或集合的任何单个字符例如：1）查询所有姓“张”的学生信息select*from学生基本情况where姓名like'张%'4.2SELECT语句的查询条件6．NULL搜索条件空值比较的关键字是ISNULL或ISNOTNULL。其中NULL表示字段的数据未知或不确定。格式为：表达式ISNULL或表达式ISNOTNULL例如：查询缺少数学成绩的学生信息。select*from学生基本情况where数学成绩isnull查询示例表练习讨论1.按10%的比例显示本专业的男生信息；2.查询“管理学院”专业分布情况；3.查询本专业学生来自哪些省份；4.查询英语不及格学生的信息，显示学号、姓名、专业、英语原成绩、英语+10、英语*1.1等字段信息；5.显示管理学院学生中，不姓“刘”和“张”的学生信息；6.查询“数学”在65-75之间，而“英语”不在70-90之间的学生信息；7.查询“管理学院、能源学院、机械学院、人文学院”学号在01-07之间的学生信息。8.将学生的“学号、姓名、性别、身份证号”作为一列、“院系名称、专业”作为一列，各数据之间用“，”分隔；列名分别为“基本信息”、“隶属关系”进行显示；9.查询总成绩210、语文成绩在75-90间、且不属于“人文学院”的学生信息。排序和分组的使用1.排序使用Orderby对查询结果中的一个或多个字段进行排序。排序可以升序(ASC),也可以是降序(DESC)。若未指定排序方式，默认升序。例如：1）按姓名升序查询学生信息。select*from学生基本情况orderby姓名2)查询数学成绩前5名的学生信息selecttop5*from学生基本情况orderby数学成绩desc思考：1）如何按院系升序、姓名降序查询？2）如何查询总成绩前10名的学生信息？3）如何按性别降序、学号升序、平均成绩升序查询？4）查询本专业女生中年龄最小的10个学生信息。数据记录的分组使用GroupBy进行分组，返回的结果中，每一行都产生聚合值。常用的聚合函数包括：Sum():返回一个数字列或计算列的总和Avg():返回一个数字列或计算列的平均值Min()：返回一个数字列或计算列的最小值Max()：返回一个数字列或计算列的最大值Count()：返回非NULL值的记录个数count(*)：返回符合条件的记录个数排序与分组例如：1)统计各个院系的学生人数。select院系名称,count(*)as人数from学生基本情况groupby院系名称2)统计各个院系的男生、女生的人数。select院系名称,性别,count(*)as人数from学生基本情况groupby院系名称,性别思考：1）查询各个专业、不同省份的学生人数。2）查询数据表中所有男生、女生的人数。3）查询本专业数学成绩的总分、平均分、最高分、最低分。4）按性别查询本专业英语的平均分、最高分、最低分和人数。4.3数据连接与子查询1.数据连接查询根据各个表之间的逻辑关系，从两个或多个表中检索数据，并把所有检索到的数据通过一个数据集返回。1）用WHERE进行数据连接使用WHERE子句可以实现多个表间的数据连接。例如：从学生基本情况表与成绩表中查询学生的学号、姓名、专业、各门课程成绩。SELECT学生基本情况.学号,姓名,所学专业,成绩表.高等数学,成绩表.大学英语,计算机基础,管理学FROM学生基本情况,成绩表where学生基本情况.学号=成绩表.学号WHERE数据连接如果数据表名称太长，造成书写麻烦，可以给数据表另外取一个简洁的名称，即别名。例如:将上面语句中的基本情况表改名为jbqk,将成绩表改名为cjb,则语句变成：SELECTjbqk.学号