第5章数据库的查询和视图

第5章数据库的查询和视图•5.1简单select查询•5.2select语句的统计功能•5.3select语句中的多表连接•5.4子查询•5.5数据库视图Page12019年12月18日星期三2•通过学习本章，读者应掌握以下内容：•掌握各种查询方法的语法格式和使用，包括单表条件查询、单表多条件查询、多表多条件查询、嵌套查询，并能对查询结果进行排序、分组和汇总操作。•掌握视图的建立、修改、使用和删除操作；并能通过视图查询数据，修改数据，更新数据和删除数据。Page22019年12月18日星期三35.1简单SELECT语句5.1.1SELECT语句概述•查询是SQL语言中最主要、最核心的部分。查询语言用来对已经存在于数据库的数据按照特定组合、条件表达式或者一定次序进行检索。•数据查询命令是SQL最常用的命令。Page32019年12月18日星期三45.1.2完整的SELECT语句的基本语法格式•SELECT语句的完整语法格式如下：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_list[INTOnew_table_name]FROMtable_name/view_name[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]][,…n]5•其中各子句说明如下：•(1)ALL。表示输出所有记录，包括重复记录；distinct表示去掉重复的记录。•(2)TOPn。查询结果只显示表中前n条记录，TOPnPERCENT关键字，则查询结果只显示前面n%条记录。•(3)select_list。所要查询的选项的集合，多个选项之间用逗号分开。•(4)FROMtable_name/view_name。结果集数据来源于哪些表或视图，FROM子句还可包含连接的定义。•(5)INTOnew_table_name。用于指定使用结果集来创建一个新表，new_table_name是新表名。•(6)WHEREsearch_condition。是一个条件筛选，只有符合条件的行才向结果集提供数据，不符合条件的行中的数据不会被使用。6•(7)GROUPBYgroup_by_expression。根据group_by_expression列中的值将查询结果进行分组。•(8)HAVINGsearch_condition。应用于结果集的附加筛选。逻辑上讲，HAVING子句从中间结果集对行进行筛选，这些中间结果集是用SELECT语句中的FROM、WHERE或GROUPBY子句创建的。HAVING子句通常与GROUPBY子句一起使用。•(9)ORDERBYorder_expression[ASC|DESC]。定义结果集中的行排列的顺序。order_expression指定组成排序列表的结果集的列。ASC指定行按升序排序，DESC指定行按降序排序。•SELECT语句可以完成以下工作：–投影。用来选择表中的列。–选择。用来选择表中的行。–连接。将两个关系拼接成一个关系。75.1.3基本的SELECT语句•SELECT语句的基本形式如下：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_listFROMtable_name/view_name[WHEREsearch_condition]•【例5.1】在“学生信息”表中查询学生的学号及姓名。USEjxglGOSELECTstu_id,stu_nameFROM学生信息GO82．查询表中全部列（*）•将表中所有属性都选出来，可以有两种方法。一种方法是在SELECT命令后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将字段列表简写为“*”。•【例5.2】查询“课程”表的所有信息。USEjxglGOSELECT*FROM课程GOPage82019年12月18日星期三9•3．设置字段别名•列表达式[as]别名•或：别名=列表达式•【例5.3】查询jxgl数据库的“课程”表，列出表中的所有记录，每个记录名称依次为课程编号，课程名称，课程学分及开课时间。USEjxglGOSELECTcourse_idAS课程编号,course_nameAS课程名称,course_creditAS课程学分,course_termAS开课时间FROM课程Page92019年12月18日星期三10•4．查询经过计算的值•SELECT子句的字段列表不仅可以是表中的属性列，也可以是表达式，包括字符串常量、函数等。其语法格式为：•计算字段名=表达式【例5.4】查询“学生信息”表中所有学生的学号、姓名及年龄。USEjxglGOSELECTstu_id,stu_name,age=DATEDIFF(YY,stu_birth,GETDATE())FROM学生信息GOPage102019年12月18日星期三11•5．返回全部记录（ALL）•要返回所有记录可在SELECT后使用ALL，ALL是默认设置，因此也可以省略。•【例5.5】查询“学生信息”表中所有学生的系别代码。USEjxglGOSELECTdept_idFROM学生信息GOPage112019年12月18日星期三12•6．过滤重复记录（DISTINCT）•在例5.5的执行结果集中显示重复行。如果让重复行只显示一次，需在SELECT子句中用DISTINCT指定在结果集中只能显示唯一行。•【例5.6】查询“学生信息”表中的学生所在系别有哪些(重复专业只显示一次)。USEjxglGOSELECTDISTINCTdept_idFROM学生信息GOPage122019年12月18日星期三13•7．仅返回前面若干条记录•其语法格式如下：SELECT[TOPn︱TOPnPERCENT]列名l[，．．．n]FROM表名•其中：TOPn表示返回最前面的n行，n表示返回的行数。TOPnPERCENT表示返回的最前面的n％行。•【例5.7】查询“学生信息”表中前5条记录。USEjxglGOSELECTTOP5*FROM学生信息GOPage132019年12月18日星期三14•【例5.8】查询xs表中前面10%行记录。USEjxglGOSELECTTOP10PERCENT*FROM学生信息GOPage142019年12月18日星期三155.1.4INTO子句•使用INTO子句允许用户定义一个新表，并且把SELECT子句的数据插入到新表中，其语法格式如下。SELECT字段列表INTO新表名FROM表名列表WHERE查询条件•使用INTO子句插入数据时，应注意以下几点：（1）新表不能存在，否则会产生错误信息。（2）新表中的列和行是基于查询结果集的。（3）使用该子句必须在目的数据库中具有CREATETABLE权限。（4）如果新表名称的开头为“#”，则生成的是临时表。•注意：使用INTO子句，通过在WHERE子句中FALSE条件，可以创建一个和源表结构相同的空表。Page152019年12月18日星期三16•【例5.9】创建一个和“学生信息”表结构相同的xs_new表。USEjxglGOSELECT*INTOxs_newFROM学生信息WHERE68•设置“WHERE68”这样一个明显为逻辑否的条件的目的是为了只保留“学生信息”表的结构，而不返回任何记录。Page162019年12月18日星期三17•【例5.10】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。USEjxglGOSELECT*INTO女生表FROM学生信息WHEREstu_sex='女'•【例5.11】查询所有男生的信息并将结果存入临时表中USEjxglSELECT*INTO#男生表FROM学生信息WHEREstu_sex='男'Page172019年12月18日星期三185.1.5使用WHERE子句•其语法格式如下：SELECT列名1[,…列名n]FROM表名WHERE条件表达式•使用WHERE子句可以限制查询的记录范围。在使用时，WHERE子句必须紧跟在FROM子句后面。WHERE子句中的条件是—个逻辑表达式，其中可以包含的运算符见表6-1。Page182019年12月18日星期三19Page192019年12月18日星期三运算符和连接谓词用途=，，，=，，=，!=比较大小AND、OR、NOT设置多重条件BETWEEN…AND…确定范围IN、NOTIN、ANY︱SOME、ALL确定集合LIKE字符匹配，用于模糊查询IS[NOT]NULL测试空值20•1．比较表达式作查询条件•比较表达式是逻辑表达式的一种，使用比较表达式作为查询条件的一般表达形式是：•表达式比较运算符表达式•其中：•表达式为：常量、变量和列表达式的任意有效组合。•比较运算符包括：=（等于）、（小于）、（大于）、（不等于）、!（不大于）、!（不小于）、=（大于等于）、=（小于等于）、!=（不等于）。Page202019年12月18日星期三21•【例5.12】查询年龄在20岁以下的学生。USEjxglGOSELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE())FROM学生信息WHEREDATEDIFF(YEAR,stu_birth,GETDATE())20GOPage212019年12月18日星期三22•2．逻辑表达式作查询条件•使用逻辑表达式作为查询条件的一般表达形式是：•表达式1AND|OR表达式2，或NOT表达式•【例5.13】查询年龄为20岁且性别为“女”的学生。USEjxglGOSELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE())FROM学生信息WHEREDATEDIFF(YEAR,stu_birth,GETDATE())=20ANDstu_sex='女'Page222019年12月18日星期三23•3．使用（NOT）BETWEEN…AND关键字•其语法格式为：•表达式[NOT]BETWEEN表达式lAND表达式2•谓词可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。使用BETWEEN限制查询数据范围时同时包括了边界值，而使用NOTBETWEEN进行查询时没有包括边界值。•【例5.14】查询年龄在19～20岁之间的女学生的学号、姓名和年龄。SELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE())FROM学生信息WHEREDATEDIFF(YEAR,stu_birth,GETDATE())BETWEEN19AND20ANDstu_sex='女'24•4．使用IN关键字•同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。•语法格式为：•表达式[NOT]IN（表达式1,表达式2[,…表达式n]）•如果“表达式”的值是谓词IN后面括号中列出的表达式1，表达式2,…表达式n的值之一，则条件为真。【例5.15】查询选修了112011和312010两门课程的学生的学号。SELECTDISTINCTstu_idFROM成绩WHEREcourse_idIN('112011','312010')Page242019年12月18日星期三25•5．使用LIKE关键字•语法格式：•表达式[NOT]LIKE匹配串•其含义是查找指定的属性列值与匹配串相匹配的元组。匹配串可以是一个完整的字符串，也可以含有通配符。SQLServer提供了以下4种通配符供用户灵活实现复杂的查询条件。–％（百分号）：表示从0到n个任意字符。–_（下划线）：表示单个的任意字