第 5 章 SQL(结构化查询语言).ppt

1、第 5 章 SQL（结构化查询语言）,SQL的全称为Structured Query Language（结构化查询语言），它利用一些简单的句子构成基本的语法，来存取数据库的内容。,5.1 SQL,5.1.1 SQL概述 SQL是应用于数据库的语言，本身是不能独立存在的。,5.1.2 SQL的分类,SQL按照用途可以分为如下3类。 1数据定义语言（Data Definition Language，DDL） 类似于这一类定义数据库对象的SQL叙述即为DDL语言。例如，数据库创建语句（CREATE DATA）和表创建语句（CREATE TABLE）等。,2数据处理语言（Data Manipulati

2、on Language，DML）,SQL语法中处理数据语言称为DML。例如，使用SELECT（数据查询语句）查询表中的内容，或者使用INSERT（插入语句）、DELETE（删除语句）和UPDATE（更新语句）插入、修改和更新一笔记录等，这些语句属于DML。,3数据控制语言（Data Control Language，DCL）,在某些情况下，可能需要一次处理好几个SQL语句，而且希望它们必须全部执行成功，如果其中一个执行失败，则这一批SQL语句都不要执行，而且已经执行的应该恢复到开始的状态。这种情况需要用到数据控制语言。,5.2 SELECT查询语句,SQL查询分析器是一个图形用户界面，用以交互

3、地设计和测试Transact-SQL语句、批处理和脚本。,5.2.1 查询分析器,启动SQL查询分析器可采用两种方法： 在“开始”菜单中，通过Microsoft SQL Server下拉菜单的“查询分析器”命令来打开查询分析器。 在SQL Server企业管理器中通过工具菜单启动。,启动SQL查询分析器并执行相应语句的操作步骤如下。 （1）在“开始”菜单中，单击“程序”级联菜单，选择“Microsoft SQL Server”程序组中的“查询分析器”选项，即可启动SQL查询分析器。或在企业管理器中，单击“工具”，选择“SQL查询分析器”选项，也可启动SQL查询分析器。,（2）在启动SQL查询分

4、析器时，系统首先打开“连接到SQL Server”对话框。使用该对话框可以指定连接到哪个SQL Server服务器。如图5-1所示。,图5-1 “连接到SQL Server”对话框,（3）单击SQL Server文本框后面的按钮，可打开“选择服务器”对话框。可以在此对话框中选择要连接到的SQL Server服务器。如图5-2所示。,图5-2 “选择服务器”对话框,（4）在图5-1所示的“连接到SQL Server”对话框中设置正确的验证模式，即可连接到SQL Server服务器，并打开SQL查询分析器。,（5）进入“SQL查询分析器”界面，在工具栏中，单击 master所在的下拉框，即可选择待

5、查询的数据库，例如选择“学生信息数据库”。 （6）在SQL语句录入框中录入SQL语句，然后单击工具栏中的按钮“”，执行该“SELECT”查询语句，即可得到操作结果。,5.2.2 查询语句格式,1基本格式 SELECT ALL | DISTICT ， ， FROM ， ， WHERE GROUP BY HAVING ORDER BY ASC | DESC ,2语句说明, SELECT语句的基本格式是由SELECT子句、FROM子句和WHERE子句组成的查询块。 整个SELECT语句的含义是：根据WHERE子句的筛选条件表达式，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的

6、字段次序，筛选出记录中的字段值构造一个显示结果表。, 如果有GROUP子句，则将结果按的值进行分组，该值相等的记录为一个组。 如果GROUP子句带HAVING短语，则只有满足指定条件的组才会显示输出。,5.3 基于单表的查询,5.3.1 查询表中指定的字段 SELECT子句后的各个字段的先后顺序可以与原表中的顺序不一致，但在结果表中，字段是按照SELECT子句后的各个字段的顺序显示。 SELECT student_name, student_sex, address FROM student_info,图5-4 【例5-1】的显示结果,5.3.2 通配符“*”的使用,在SELECT语句中，可以

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

8、_name AS 姓名，address AS家庭住址FROM student_info或者SELECT student_name姓名，address家庭住址 FROM student_info或者SELECT 姓名=student_name, 家庭住址=address FROM student_info执行结果如图5-7所示。,图5-7 【例5-4】的显示结果,5.3.5 显示表达式的值,SELECT student_name, year(getdate()-year(born_date) as 年龄 FROM student_info,5.3.6 使用DISTINCT短语消除重复的记录,DI

9、STINCT短语能够从结果表中去掉重复的记录。 SELECT DISTINCT class_no FROM student_info,5.3.7 使用WHERE子句查询特定的记录,1关系表达式 用关系运算符将两个表达式连接在一起的式子即为关系表达式，关系表达式的返回值为逻辑值（TRUE、 FALSE），关系表达式的格式为： ,在关系表达式字符型数据之间的比较是对字符的ASCII值进行比较。所有字符都有一个ASCII值与之对应。例如，字母“A”、字母“B”、字母“C”，它们的ASCII值分别是65、66、67。 字符串的比较是从左向右依次进行的。 在SQL Server 2000中，日期字符串可

10、以按照“年月日”的格式书写。,表5-1关系运算符,2逻辑表达式,用逻辑运算符将两个表达式连接在一起的式子即为逻辑表达式，逻辑表达式的返回值为逻辑值（TRUE、FALSE），逻辑表达式的格式为： ,表5-2逻辑运算符,3特殊表达式,表5-3特殊运算符,4有关关系运算符的使用,SELECT student_name, address FROM student_info WHERE student_sex=女 SELECT * FROM student_info WHERE born_date 1980-12-31,5有关逻辑运算符的使用,SELECT student_name,tele_numbe

11、r,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=200001 OR SUBSTRING(tele_number,1,2)=87,SELECT student_name,student_sex, born_date FROM student_info WHERE not (year(getdate()-year(

12、born_date)=23) 或 SELECT student_name,student_sex, born_date FROM student_info WHERE ( year(getdate()-year(born_date) !=23,6有关特殊运算符的使用,使用“BETWEENAND”的作用是定义表达式在两数之间。 格式为： 表达式 NOT BETWEEN 表达式1 AND 表达式2,SELECT * FROM student_info WHERE ( year(getdate()-year(born_date) BETWEEN 18 AND 20,当需要判断一个表达式的值是否为空值

13、时，可使用关键字“IS NULL”。 使用“IS NULL”短语的格式为： 表达式 IS NOT NULL 当使用NOT 关键字，表示不为空。 SELECT student_name, tele_number,FROM student_info WHERE comment IS NULL,使用IN关键字可以指定一个值表，值表中指定列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回TRUE,否则返回FALSE。使用IN关键字指定值表的格式为： 表达式 IN （表达式1，n）,SELECT student_id,student_name, student_sex FROM student_

14、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关键字用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、text、datetime和smalldatetime等类型的数据，返回逻辑值TRUE或FALSE。 LIKE关键字表达式的格式为： 字符表达式1 NOT LIKE 字符表达式2,SELECT student_id,student_nam

15、e FROM student_info WHERE student_name LIKE 刘_,5.3.8 使用ORDER BY子句对查询结果排序,1“ORDER BY”子句的基本格式 基本格式： SELECT FROM WHERE ORDER BY ASC|DESC，,“子句表达式1”可以是一个列名、列的别名、表达式或非零的整数值，而非零的整数值则表示字段、别名或表达式在选择列表中的位置。ASC表示升序，为默认值；DESC表示降序，排序时空值（NULL）被认为是最小值。,2“ORDER BY”子句应用实例,SELECT student_id,student_name, born_date FR

16、OM student_info ORDER BY born_date,SELECT student_id,student_name, ru_date FROM student_info WHERE ru_date 2000-01-01 ORDER BY ru_date DESC,student_id,5.3.9 SQL的聚合函数,表5-4常用的几个聚合函数,1COUNT函数的应用,COUNT函数在使用时，主要作用是统计满足某种条件的行数。 SELECT COUNT(*) 学生人数 FROM student_info WHERE year(getdate()-year(born_date)19

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

18、达式中所有项的平均值。其格式为： AVG（ALL|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 =1,4SUM函数的应用,SUM函数用于求表

19、达式中所有项的总和。其格式为： SUM（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 =1,5.3.10 使用GROUP BY子句对查询

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

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

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

23、件，则为复合条件连接。若一个表与自身进行连接，称为自连接。,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,director,profession FROM stude

24、nt_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.course_nob.course_no,本例主要

25、学习有关复合条件连接的表达式书写格式。在“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_info.student_id= result_inf

26、o.student_id AND result_info.course_no=course_info.course_no AND course_info.course_name=汇编语言 AND result_info.result70,5.4.2 以JOIN关键字指定的连接,T-SQL扩展了以JOIN关键字连接的表示方式，使表的连接运算能力有了增强，可以将多个表连接起来，FOR关键字后面的连接格式为： ON | CROSS JOIN ,其中first_table，second_table为需连接的表，join_type表示连接类型，ON用于指定连接条件。 Join_type的格式为： INN

27、ER| LEFT | RIGHT | FULL OUTER JOIN,其中INNER表示内连接，OUTER表示外连接，join_hint是连接提示。JOIN表示交叉连接。因此，以JOIN关键字指定的连接有三种类型。,1内连接,内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。 SELECT * FROM student_info INNER JOIN class_info ON student_info.class_no= class_info.class_no,SELECT student_info.*,class_name,director,profession FROM stu

28、dent_info INNER JOIN class_info ON student_info.class_no= class_info.class_no,内连接是系统默认的，可以省略INNER关键字，使用内连接后仍可使用WHERE子句指定条件。,SELECT student_name,result FROM student_info JOIN result_info ON student_info.student_id= result_info.student_id WHERE result_info.course_no=2 AND result_info.result60 内连接还可用于多

29、个表的连接。,SELECT student_info.student_id,student_name, course_info.course_name,result_info.result FROM student_info JOIN result_info ON student_info.student_id= result_info.student_id JOIN course_info ON result_info.course_no=course_info.course_no WHERE course_info.course_name=汇编语言 AND result_info.resu

30、lt70,SELECT a.student_id,a.course_no,b.course_no,a.result FROM result_info a JOIN result_info b ON a.result=b.result AND a.student_id=b.student_id AND a.course_nob.course_no,2外连接,外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括 三种。 左向外连接（LEFT OUTER JOIN）或（LEFT JOIN） 结果表中除了满足连接条件的行，还包括左表中的所有行。,右向外连接（RIGHT OUTER

31、 JOIN）或（RIGHT JOIN） 结果表中除了满足连接条件的行，还包括右表中的所有行。,完整外部连接（FULL OUTER JOIN）或（FULL JOIN） 结果表中除了满足连接条件的行，还包括两表中的所有行。,SELECT student_info.student_id, student_name,result_info.course_no FROM student_info LEFT OUTER JOIN result_info ON student_info.student_id= result_info.student_id WHERE class_no=200001,SELE

32、CT student_info.student_id, student_name, result_info.course_no FROM result_info RIGHT OUTER JOIN student_info ON student_info.student_id= result_info.student_id,3交叉连接,在这类连接的结果表内，是由第一个表的每行与第二个表的每行拼接后形成的表。交叉连接不使用WHERE子句。,SELECT student_id,student_name,course_no,course_name FROM student_info CROSS JOI

33、N course_info,5.5 子 查 询,子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。,SELECT class_name FROM class_info WHERE class_no = (SELECT class_no FROM student_info WHERE student_name=张小强),要注意连接与子查询的区别：连接可以合并两个或多个表中数据，而带子查询的SELECT语句的结果只能来自一个表，子查询的结果是用来作为选择结果数据时进行参照的。,可以在许多地方指定子查询，例如： 使用别名时；

34、使用IN或NOT IN时； 在UPDATE、DELETE和INSERT语句中； 使用比较运算符时； 使用ANY、SOME或ALL时； 使用EXISTS或NOT EXISTS时； 在有表达式的地方。,5.5.1 使用IN或NOT IN的子查询,IN 子查询用于进行一个给定值是否在子查询结果集中的判断，格式为： 表达式 NOT IN（子查询） 当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE，若使用了NOT，则返回的值刚好相反。,SELECT * FROM student_info WHERE student_id IN (SELECT student_id FR

35、OM 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),5.5.2 INSERT、DELETE和UPDATE语句中的子查询,子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。,1插入子查询结果,基本格式： INSERT INTO （ ， ） SELECT （ ， ） FROM WHERE ,CRE

36、ATE TABLE stu(学号 int NOT NULL PRIMARY KEY, 姓名 char(10),性别 char(2),家庭住址 char(50) ) go INSERT INTO stu (学号,姓名,性别,家庭住址) SELECT student_id,student_name,student_sex,address FROM student_info go SELECT * FROM stu go,2带子查询的修改语句,与INSERT相同，在SQL中，子查询也可以嵌套在UPDATE语句中，用来指定修改的条件。,UPDATE result_info SET result=res

37、ult+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),5.5.3 使用比较运算符的子查询,子查询可由一个比较运算符（=、=、，! | = | != | | ! ALL | ANY （子查询）,ALL指定表达式要与子查询的结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。 ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE。