第4章数据查询

1、南昌工程学院计算机系第第4章章 数据查询数据查询报告报告 人：谭德坤人：谭德坤Email:4.1 主要内容主要内容SQL的概念Select语句基本查询高级查询 SQL的全称为Structured Query Language（结构化查询语言），它利用一些简单的句子构成基本的语法，来存取数据库的内容。 4.1 SQL 4.1.1 SQL概述 SQL是应用于数据库的语言，本身是不能独立存在的。4.1.2 SQL的分类的分类 SQL按照用途可以分为如下3类。 1数据定义语言（Data Definition Language，DDL） 类似于这一类定义数据库对象的SQL叙述即为DDL语言。例如，数据库

2、创建语句（CREATE DATA）和表创建语句（CREATE TABLE）等。2数据处理语言（数据处理语言（Data Manipulation Language，DML） SQL语法中处理数据语言称为DML。例如，使用SELECT（数据查询语句）查询表中的内容，或者使用INSERT（插入语句）、DELETE（删除语句）和UPDATE（更新语句）插入、修改和更新一笔记录等，这些语句属于DML。3数据控制语言（数据控制语言（Data Control Language，DCL） 在某些情况下，可能需要一次处理好几个SQL语句，而且希望它们必须全部执行成功，如果其中一个执行失败，则这一批SQL语句都不

3、要执行，而且已经执行的应该恢复到开始的状态。这种情况需要用到数据控制语言。4.2 SELECT查询语句查询语句 SQL查询分析器是一个图形用户界面，用以交互地设计和测试Transact-SQL语句、批处理和脚本。 4.2.1 查询分析器查询分析器 启动SQL查询分析器可采用两种方法： 在“开始”菜单中，通过Microsoft SQL Server下拉菜单的“查询分析器”命令来打开查询分析器。 在SQL Server企业管理器中通过工具菜单启动。 启动SQL查询分析器并执行相应语句的操作步骤如下。 （1）在“开始”菜单中，单击“程序”级联菜单，选择“Microsoft SQL Server”程序

4、组中的“查询分析器”选项，即可启动SQL查询分析器。或在企业管理器中，单击“工具”，选择“SQL查询分析器”选项，也可启动SQL查询分析器。 （2）在启动SQL查询分析器时，系统首先打开“连接到SQL Server”对话框。使用该对话框可以指定连接到哪个SQL Server服务器。如图4-1所示。图图4-1 “4-1 “连接到连接到SQL Server”SQL Server”对话框对话框 （3）单击SQL Server文本框后面的按钮，可打开“选择服务器”对话框。可以在此对话框中选择要连接到的SQL Server服务器。如图4-2所示。图图4-2 “4-2 “选择服务器选择服务器”对话框对话框

5、 （4）在图4-1所示的“连接到SQL Server”对话框中设置正确的验证模式，即可连接到SQL Server服务器，并打开SQL查询分析器。 （5）进入“SQL查询分析器”界面，在工具栏中，单击 master所在的下拉框，即可选择待查询的数据库，例如选择“学生信息数据库”。 （6）在SQL语句录入框中录入SQL语句，然后单击工具栏中的按钮“ ”，执行该“SELECT”查询语句，即可得到操作结果。4.2.2 查询语句格式查询语句格式 1基本格式 SELECT ALL | DISTICT ， ， FROM ， ， WHERE GROUP BY HAVING ORDER BY ASC | DES

6、C 2语句说明语句说明 SELECT语句的基本格式是由SELECT子句、FROM子句和WHERE子句组成的查询块。 整个SELECT语句的含义是：根据WHERE子句的筛选条件表达式，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的字段次序，筛选出记录中的字段值构造一个显示结果表。 如果有GROUP子句，则将结果按的值进行分组，该值相等的记录为一个组。 如果GROUP子句带HAVING短语，则只有满足指定条件的组才会显示输出。 4.3 基于单表的查询基于单表的查询 4.3.1 查询表中指定的字段 SELECT子句后的各个字段的先后顺序可以与原表中的顺序不一致，但在结果表中，

7、字段是按照SELECT子句后的各个字段的顺序显示。 SELECT student_name, student_sex, address FROM student_info图图4-4 4-4 【例例4-14-1】的显示结果的显示结果使用使用TOP n PERCENT关键字关键字 如果只需要显示前n行，可以在SELECT语句中使用TOP n PERCENT关键字。【例8.2】查询表student_info 中的前3个学生的记录，具体语句如下： USE HrSystemGOSELECT TOP 3 student_name FROM student_infoGO4.3.2 通配符通配符“*”的使用的

8、使用 在SELECT语句中，可以使用通配符“*”显示所有的字段。 SELECT * FROM student_info4.3.3 使用单引号加入字符串使用单引号加入字符串 在SELECT语句中，可以在一个字段的前面加上一个单引号字符串，对后面的字段起说明作用。图图4-5 4-5 【例例4-24-2】的显示结果的显示结果 SELECT student_name,家庭住址, address FROM student_info图图4-6 4-6 【例例4-34-3】的显示结果的显示结果4.3.4 使用别名使用别名 在显示结果时，可以指定以别名代替原来的字段名称，共有3种方法： 采用“字段名称 AS

9、别名”的格式； 采用“字段名称 别名”的格式； 采用“别名=字段名称”的格式。 SELECT student_name AS 姓名，address AS家庭住址FROM student_info或者SELECT student_name姓名，address家庭住址 FROM student_info或者SELECT 姓名=student_name, 家庭住址=address FROM student_info执行结果如图4-7所示。图图4-7 4-7 【例例4-44-4】的显示结果的显示结果4.3.5 显示表达式的值显示表达式的值 SELECT student_name, year(getda

10、te()-year(born_date) as 年龄 FROM student_info4.3.6 使用使用DISTINCT短语消除重短语消除重复的记录复的记录 DISTINCT短语能够从结果表中去掉重复的记录。 SELECT DISTINCT class_no FROM student_info4.3.7 使用使用WHERE子句查询特定子句查询特定的记录的记录 1关系表达式 用关系运算符将两个表达式连接在一起的式子即为关系表达式，关系表达式的返回值为逻辑值（TRUE、 FALSE），关系表达式的格式为： 在关系表达式字符型数据之间的比较是对字符的ASCII值进行比较。所有字符都有一个ASCI

11、I值与之对应。例如，字母“A”、字母“B”、字母“C”，它们的ASCII值分别是65、66、67。字符串的比较是从左向右依次进行的。在SQL Server 2000中，日期字符串可以按照“年月日”的格式书写。运 算 符 号意 义=等于小于大于=大于或等于!= 或 不等于表表4-14-1关系运算符关系运算符2逻辑表达式逻辑表达式 用逻辑运算符将两个表达式连接在一起的式子即为逻辑表达式，逻辑表达式的返回值为逻辑值（TRUE、FALSE），逻辑表达式的格式为： 运 算 符 号意 义OR或（或者）AND与（并且）NOT非（否）表表4-24-2逻辑运算符逻辑运算符3特殊表达式特殊表达式运 算 符 号意

12、义%通配符，包含零个或更多字符的任意字符串通配符，表示任意单个字符指定范围（a-f）或集合（abcdef）中的任何单个字符不 属 于 指 定 范 围 （ a - f ） 或 集 合 （abcdef）中的任何单个字符BDTWEENAND 定义一个区间范围IS NULL 测试字段值是否为空值LIKE模式匹配，字符串匹配操作符IN检查一个字段值是否属于一组值之中EXISTS检查某一个字段值是否有值，实际上，EXISTS是IS NULL的反义词表表4-34-3特殊运算符特殊运算符4有关关系运算符的使用有关关系运算符的使用 SELECT student_name, address FROM studen

13、t_info WHERE student_sex=女 SELECT * FROM student_info WHERE born_date 1980-12-314.有关逻辑运算符的使用有关逻辑运算符的使用 SELECT student_name,tele_number,student_sex,address FROM student_info WHERE substring(address,1,3)=武汉市 AND student_sex=男 SELECT student_name,student_sex,tele_number FROM student_info WHERE class_no

14、=200001 OR SUBSTRING(tele_number,1,2)=87 SELECT student_name,student_sex, born_date FROM student_info WHERE not (year(getdate()-year(born_date)=23) 或 SELECT student_name,student_sex, born_date FROM student_info WHERE ( year(getdate()-year(born_date) !=236有关特殊运算符的使用有关特殊运算符的使用 使用“BETWEENAND”的作用是定义表达式在

15、两数之间。 格式为： 表达式 NOT BETWEEN 表达式1 AND 表达式2 SELECT * FROM student_info WHERE ( year(getdate()-year(born_date) BETWEEN 18 AND 20 当需要判断一个表达式的值是否为空值时，可使用关键字“IS NULL”。 使用“IS NULL”短语的格式为： 表达式 IS NOT NULL 当使用NOT 关键字，表示不为空。 SELECT student_name, tele_number FROM student_info WHERE comment IS NULL 使用IN关键字可以指定一个

16、值表，值表中指定列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回TRUE,否则返回FALSE。使用IN关键字指定值表的格式为： 表达式 IN （表达式1，n） SELECT student_id,student_name, student_sex FROM student_info WHERE class_no IN (200001,200002) SELECT student_id,student_name, student_sex FROM student_info WHERE class_no NOT IN (200001,200002) LIKE关键字用于指出一个字符串是否与

17、指定的字符串相匹配，其运算对象可以是char、text、datetime和smalldatetime等类型的数据，返回逻辑值TRUE或FALSE。 LIKE关键字表达式的格式为： 字符表达式1 NOT LIKE 字符表达式2 SELECT student_id,student_name FROM student_info WHERE student_name LIKE 刘_4.3.8 使用使用ORDER BY子句对查子句对查询结果排序询结果排序 1“ORDER BY”子句的基本格式 基本格式： SELECT FROM WHERE ORDER BY ASC|DESC， “子句表达式1”可以是一个

18、列名、列的别名、表达式或非零的整数值，而非零的整数值则表示字段、别名或表达式在选择列表中的位置。ASC表示升序，为默认值；DESC表示降序，排序时空值（NULL）被认为是最小值。2“ORDER BY”子句应用实例子句应用实例 SELECT student_id,student_name, born_date FROM student_info ORDER BY born_date SELECT student_id,student_name, ru_date FROM student_info WHERE ru_date 19 AND student_sex=女2MAX和和MIN函数的应用函数

19、的应用 MAX和MIN函数分别用于求表达式中所有值项的最大值与最小值，语句格式为： MAX / MIN （ALL|DISTINCT 表达式） 其中表达式是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。MAX/MIN忽略NULL值。 SELECT MIN (born_date) 年龄最大 , MAX (born_date) 年龄最小 FROM student_info3AVG函数的应用函数的应用 AVG函数的表达式格式与MAX和MIN的表达式格式一样。求表达式中所有项的平均值。其格式为： AVG（ALL

20、|DISTINCT 表达式） 其中表达式是常量、列、函数或表达式，其数据类型只能是int，smallint，tinyint，bigint，decimal，numeric，float，real，money和smallmoney。 ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。AVG忽略NULL值。 SELECT AVG (course_time) 平均课时, AVG (course_score) 平均学分 FROM course_info WHERE course_start =14SUM函数的应用函数的应用 SUM函数用于求表达式中所有项的总和。其格式为： SUM（

21、ALL|DISTINCT 表达式） 其中表达式是常量、列、函数或表达式，其数据类型只能是int、smallint、tinyint、bigint、decimal，numeric，float、real，money和smallmoney。 ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。SUM忽略NULL值。 SELECT SUM (course_time) 总课时, SUM (course_score) 总学分 FROM course_info WHERE course_start =14.3.10 使用使用GROUP BY子句对查子句对查询结果进行分组询结果进行分组 利

22、用SQL的GROUP BY子句，能够快速而简便地将查询结果表按照指定的字段进行分组，值相等的记录分为一组。 GROUP BY子句一般和子句一般和SQL的聚合函的聚合函数一起使用。数一起使用。 SELECT FROM GROUP BY SELECT class_no,COUNT( student_id) 学生人数 FROM student_info GROUP BY class_no4.3.11 使用使用HAVING子句筛选结子句筛选结果表果表 在实际使用中，往往还要对分组后的结果按某种条件再进行筛选，而只输出满足用户指定条件的记录。在SQL中，HAVING子句能完成此功能。 “WHERE”子句

23、与“HAVING”子句的区别在于作用对象不同： WHERE子句的作用对象是表，是从表中选择出满足筛选条件的记录。 HAVING子句的作用对象是组，是从组中选择出满足筛选条件的记录。 SELECT class_no 班级名,COUNT(student_id) 总人数 FROM student_info GROUP BY class_no HAVING COUNT(student_id)24.4 基于多表的连接查询基于多表的连接查询 在关系型数据库中，将一个查询同时涉及两个或两个以上的表，称为连接查询。 基于多表的查询是通过所谓的连接查询来完成的。连接是根据各个表之间的逻辑关系从两个或多个表中查询

24、数据，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。 在T_SQL中，连接查询有两大类表示形式，一是符合SQL标准连接谓词表示形式，二是T_SQL扩展使用关键字JOIN的表示形式。 指定每个表中要用于连接的列。典型的连接条件在一个表中指定外键，在另一个表中指定与其关联的键。 指定比较各列的值时要使用的逻辑运算符，如“=、”等。4.4.1 连接谓词连接谓词 可以在SELECT语句的WHERE子句中使用比较运算符，给出连接条件对表进行连接，对这种表示形式称为连接谓词表示形式。其基本格式为： 连接谓词中的比较符可以是、=、!=、等。当比较符为“=”时，就是等值连接；若在等值连接中去除结

25、果表中相同的字段名，则为自然连接；若有多个连接条件，则为复合条件连接。若一个表与自身进行连接，称为自连接。 SELECT student_info.*,class_info.* FROM student_info, class_info WHERE student_info.class_no= class_info.class_no SELECT student_id, student_name,student_sex,born_date, student_info.class_no, tele_number,ru_date,address, comment,class_name,direct

26、or,profession FROM student_info, class_info WHERE student_info.class_no= class_info.class_no 若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。 SELECT a.student_id,a.course_no,b.course_no,a.result FROM result_info a,result_info b WHERE a.result=b.result AND a.student_id=b.student_id AND a.c

27、ourse_nob.course_no 本例主要学习有关复合条件连接的表达式书写格式。在“WHERE”子句中，若有多个连接条件，则称为复合条件连接。在多表操作中，复合条件连接的使用最为广泛。 书写“WHERE”子句中的条件表达式一般先写自然连接的条件表达式，然后通过逻辑运算符，再写出其他的附加限定条件。 SELECT student_info.student_id,student_name, course_info.course_name,result_info.result FROM student_info,course_info,result_info WHERE student_inf

28、o.student_id= result_info.student_id AND result_info.course_no=course_info.course_no AND course_info.course_name=汇编语言 AND result_info.result704.5 子子 查查 询询 子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。 SELECT class_name FROM class_info WHERE class_no = (SELECT class_no FROM student_i

29、nfo WHERE student_name=张小强) 要注意连接与子查询的区别：连接可以合并两个或多个表中数据，而带子查询的SELECT语句的结果只能来自一个表，子查询的结果是用来作为选择结果数据时进行参照的。 可以在许多地方指定子查询，例如：使用别名时；使用IN或NOT IN时；在UPDATE、DELETE和INSERT语句中；使用比较运算符时；使用ANY、SOME或ALL时；使用EXISTS或NOT EXISTS时；在有表达式的地方。4.4.1 使用使用IN或或NOT IN的子查询的子查询 IN 子查询用于进行一个给定值是否在子查询结果集中的判断，格式为： 表达式 NOT IN（子查询）

30、 当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE，若使用了NOT，则返回的值刚好相反。 SELECT * FROM student_info WHERE student_id IN (SELECT student_id FROM result_info WHERE course_no=13) SELECT * FROM student_info WHERE class_no=200001 AND student_id NOT IN (SELECT student_id FROM result_info WHERE course_no=13)4.4.2 INS

31、ERT、DELETE和和UPDATE语句中的子查询语句中的子查询 子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。1插入子查询结果插入子查询结果 基本格式： INSERT INTO （ ， ） SELECT （ ， ） FROM WHERE CREATE TABLE stu(学号 int NOT NULL PRIMARY KEY, 姓名 char(10),性别 char(2),家庭住址 char(50) ) go INSERT INTO stu (学号,姓名,性别,家庭住址) SELECT student_id,student_name,student_se

32、x,address FROM student_info go SELECT * FROM stu go2带子查询的修改语句带子查询的修改语句 与INSERT相同，在SQL中，子查询也可以嵌套在UPDATE语句中，用来指定修改的条件。 UPDATE result_info SET result=result+10 WHERE student_id IN (SELECT student_id FROM student_info WHERE class_no=200001)3带子查询的删除语句带子查询的删除语句 在SQL中，同样子查询也可以嵌套在DELETE语句中，用来指定删除的条件。 DELETE student_info WHERE student_id IN (SELECT DISTINCT student_id FROM result_info)4.4.3 使用比较运算符的子查询使用比较运算符的子查询 子查询可由一个比较运算符（=、=、，!或=）引入。这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为： 表达式 | | = | != | | ! ALL | ANY （子查询） ALL指定表达式要与子查询的结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。 ANY表示表达式只