SQL Server数据库及PHP技术第4章 结构化查询语言

SQLServer数据库及PHP技术第4章结构化查询语言24.1SQL概述第3页SQL（StructureQuerylanguage）是结构化查询语言的英文缩写。SQL语言是一种标准的关系数据库查询语言，充分体现了关系数据语言的特点和优点。SQL的发展1974年，SQL语言由IBM公司的Boyce和Chamberlin将关系数据库的12条准则的数学定义以简单的关键字语法表现出来。1985年：微软和IBM开始合作。1986年10月，美国国家标准局(AmericanNationalStandardInstitute，简称ANSI)

批准了SQL语言的美国标准。1987年国际标准化组织(InternationalOrganizationforStandardization，简称ISO)也通过了这一标准。ANSI标准经过不断修改和完善，分别于1989年公布增强完整性的SQL-89标准，1992年公布了SQL-92标准。1999年ANSI和ISO合作发布了SQL-99标准。经过不断的发展，SQL语言已成为关系数据库的标准语言。第6页

关系数据库标准语言SQL

SQL命令动词

SQL功能命令动词数据查询SELECT

数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制 GRANT、REVOKE第7页4.2运算符第8页算术运算符说明+加法-减法*乘法/除法%取模，两个整数相除后的余数4.2.1算术运算符除法运算，两个整数相除的结果为整数。第9页4.2.2位运算符位运算符说明&(与、and)按位逻辑与运算|(或、OR)按位逻辑或运算~(非、NOT)按位逻辑非运算^(异或)按位异或运算第10页4.2.3关系运算符关系运算符说明>

大于=等于>=大于等于<

小于<=小于等于<>

不等于!=不等于!>不大于!<不小于关系运算符的两边可以是数值型、货币型、字符型、日期型、逻辑型的数据，其运算结果为逻辑型TRUE或FALSE。

第11页4.2.4逻辑运算符逻辑运算符说明AND对两个布尔表达式进行逻辑与运算OR对两个布尔表达式进行逻辑或运算NOT对两个布尔表达式进行逻辑非运算BETWEEN用于测试某一表达式的值是否在某个指定的范围内LIKE模式匹配运算符IN列表运算符，测试表达式的值在或不在某些列表值内ALL\SOME\ANY用于判断表达式和子查询之间的值的关系逻辑运算符和关系运算符一样，其运算结果为逻辑型TRUE或FALSE。

第12页4.2.5字符串连接运算符字符串连接运算符加号

(+)

是字符串连接运算符，用它将字符串连联起来。例如：?”That”+”is” &&表达式的值为”Thatis” ?”5”+”8” &&表达式的值为”58”第13页4.2.6赋值运算符赋值运算符只有"="一个，它用于给变量赋值。第14页4.2.7运算符的优先级优先级运算符说明1()小括号2+、-、~正、负、逻辑非3*、/、%乘除取模4+、-、+加、减、连接5=、>、<、>=、<=、<>、!=、!>、!<各种比较运算符6^、&、|位运算符7NOT逻辑非8AND逻辑与9ALL、ANY、BETWEEN、IN、LIKE、OR、SOM逻辑运算符10=赋值运算符当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。第15页4.3数据定义第16页4.3.1建立数据库基本语法格式为：CREATEDATABASE<数据库名>第17页4.3.2建立数据表基本语法格式为：CREATETABLE<表名>(<列定义>[{，<列定义>|<表约束>}])命令说明：（1）在创建数据表的命令中要定义每个字段的字段名、字段类型、字段宽度。（2）NULL表示字段允许为空值，NOTNULL不允许为空值。（3）CHECK(<字段有效性规则>)指定该字段取值的约束条件。

（4）DEFAULT<默认值>短语用来指定该字段默认的取值，<默认值>表达式的数据类型应与该字段的数据类型一致。（5）PRIMARYKEY指定该字段创建主索引，UNIQUE创建候选索引。CREATETABLE

表名(字段名1数据类型列的属性列约束,字段名2数据类型列的属性列约束,字段名3数据类型列的属性列约束,…)列属性的格式：[NULL|NOTNULL][IDENTITY(标识种子,标识增量)]列约束的格式：[CONSTRAINT约束名]PRIMARYKEY[(列名)][CONSTRAINT约束名]UNIQUE[(列名)][CONSTRAINT约束名][FOREIGENKEY[(外键列)]]REFERENCES主表名(引用列)[CONSTRAINT约束名]CHECK(检查表达式)[CONSTRAINT约束名]DEFAULT默认值第20页【例4-1】建立学生表，定义学号为主键。CREATETABLE学生(学号 CHAR(8)PRIMARYKEY，

姓名 VARCHAR(20)，

性别 CHAR(2)DEFAULT'男'，

出生日期 DATE，

班级 VARCHAR(20));其中，性别列的缺省值为”男”。第21页4.3.3建立索引建立索引可提高查询速度。索引的作用可归纳为：（1）加快查询速度；（2）保证行的唯一性。第22页建立索引的语法格式为：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][{，<列名>}][次序]…)说明：UNIQUE表明建立唯一索引。CLUSTER表示建立聚集索引。第23页【例4-11】为选课表在学号和课程号上建立唯一索引。USE学生选课CREATEUNIQUEINDEXSCION选课(学号，课程号)执行此命令后，为SC表建立一个索引名为SCI的唯一索引第24页4.4数据查询第25页SQL语言的核心是查询。SQL语言中实现查询的语句是SELECT语句，SELECT语句具有强大的查询功能，能实现各种查询，既能对单表查询，也能对多表查询，查询效率高，速度快。第26页4.3.1SELECT命令的基本格式命令格式：SELECT[ALL|DISTINCT]<查询项>FROM<数据表>[，<数据表>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<分组条件>]][ORDERBY<列名2>[ASC|DESC]]第27页命令中各短语的含义：（1）SELECT<查询项>：说明在查询结果中输出的内容，各查询项之间用逗号隔开。（2）FROM<数据表>：表明查询的数据来自哪些表，表与表之间要用逗号隔开。（3）WHERE<条件表达式>：查询数据表中满足指定条件的数据。（4）GROUPBY<列名1>：对查询结果按”列名1”进行分组（5）HAVING<分组条件>：用于限定分组必须满足的条件。（6）ORDERBY<列名2>：对查询的结果按<列名2>进行排序。第28页4.3.2简单查询1.查询全部字段例1:查询学生表的全部字段。SELE*FROM考试2.查询数据表中的部分字段例2.查询全体学生的学号、姓名。

SELECT学号,姓名FROM学生第29页3.使用distinct短语使用distinct短语去掉查询结果中的重复数据【例3】查询学生表中学生的籍贯SELECTdistinct籍贯FROM学生第30页4.3.3条件查询使用WHERE子句指定查询条件。【例4】查询学生表中来自湖南的学生SELECT学号，姓名，出生年月，籍贯 FROM学生WHERE籍贯='湖南'第31页4.3.4使用特殊运算符查询SQL语言中，使用BETWEEN、IN、LIKE特殊运算符进行查询可以帮助我们实行范围、是否在某个集合中和部分匹配等查询需求。第32页1.IN运算符IN运算意为属于集合，即等于集合中任一元素。【例5】查询学生表中来自山东、广西的学生SELECT学号，姓名，出生年月，籍贯FROM学生WHERE籍贯IN('山东'，'广西')2.BETWEEN…AND运算符【例6】查询学生表中入学成绩介入580-600的学生SELECT学号，姓名，性别，入学成绩

FROM学生

WHERE入学成绩BETWEEN580AND600第33页3．LIKE运算符LIKE是字符串匹配运算符，用于模糊查询。【例7】查询所有姓李的学生的学号和姓名。SELECT学号，姓名FROM学生WHERE姓名LIKE‘李%’4.NOT运算符NOT运算符即指不满足后面的条件。

【例8】查询学生表中来自除山东、广西以外的学生SELECT学号，姓名，出生年月，籍贯

FROM学生

WHERE籍贯NOTIN('山东'，'广西')第34页4.3.5统计查询函数名称函数功能COUNT计数COUNT(DISTINCT<列名>)计数（去掉重复值）MAX求最大值MIN求最小值SUM求和AVG求平均值在使用SELECT命令进行查询时，可以使用它内置的函数进行统计查询。常用于统计查询的数学函数如下：第35页【例9】查询学生表中湖北籍学生的人数SELECTCOUNT(*)as人数FROM学生

WHERE籍贯='湖北'【例10】查询学生表中有多少种籍贯SELECTCOUNT(distinct籍贯)as籍贯种数FROM学生【例11】查询学生表中入学成绩最高和最低的学生SELECTmax(入学成绩)as最高分，min(入学成绩)as最低分FROM学生第36页4.3.6分组查询在SELECT语言中，利用GROUPBY子句可以进行分组查询。在分组查询中使用HAVING短语，从中选出满足条件的组。第37页【例12】查询学生表中每种籍贯的人数SELECT籍贯，COUNT(*)as人数FROM学生GROUPBY籍贯【例13】查询学生表中人数在3人及以上的省份SELECT籍贯，COUNT(*)as人数

FROM学生

GROUPBY籍贯

HAVINGCOUNT(*)>=3第38页4.3.7查询的排序当需要对查询结果排序时，应该使用ORDERBY子句。排序可按升序或降序排列，使用ASC短语表示按升序排列，使用DESC短语表示按降序排列；没有指明，则默认按升序排列。第39页【例14】对学生表按入学成绩排序SELECT学号，姓名，性别，入学成绩

FROM学生

ORDERBY入学成绩【例15】查询学生表中入学成绩最高的前5名学生信息SELECTtop5学号，姓名，性别，入学成绩FROM学生ORDERBY入学成绩desc第40页4.3.8表的连接和连接查询表的连接方法有两种：方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。方法2：利用关键字JOIN进行连接。第41页1、利用相同字段进行等值连接【例16】从学生表、课程表和成绩表中查询学生的学号、姓名、所选课程编号、课程名称和成绩信息SELECT学生.学号，姓名，课程.课程编号，课程名称，成绩FROM学生，课程，成绩WHERE学生.学号=成绩.学号AND课程.课程编号=成绩.课程编号ORDERBY学号第42页2．用关键字join进行连接JOIN关键字有以下几种用法：INNERJOIN：显示符合条件的记录，此为默认值；这种查询一般称内连接。LEFT（OUTER）JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示，此称为左连接。RIGHT（OUTER）JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以NULL来显示，此称为右连接。第43页命令格式：SELECT<列名>FROM表1INNER|LEFT|RIGHTJOIN表2ON表1.列名=表2.列名[WHERE条件][ORDERBY列名]第44页【例17】查询学生所在的班级名称SELECT学号，姓名，性别，学生.班级编号，班级名称FROM学生INNERJOIN班级on学生.班级编号=班级.班级编号上述语句等价于：SELECT学号，姓名，性别，学生.班级编号，班级名称FROM学生，班级WHERE学生.班级编号=班级.班级编号第45页4.3.9嵌套查询在SELECT语句中，将一个SELECT…FROM…WHERE语句嵌套在另一个SELECT…FROM…WHERE的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。处于内层的查询称为子查询，包含子查询的语句称为父查询或外部查询。第46页【例18】查询选修了课程编号为1211课程的学生姓名SELECT姓名FROM学生WHERE学号in(select学号from选课where课程编号='1211')第47页4.5数据操纵第48页4.5.1插入记录在SQL语言中，使用INSERTINTO命令向指定的数据表中插入记录。命令格式1：INSERTINTO<表名>[(<字段1>[，<字段2>…])]VALUES(<表达式1>[，<表达式2>…])命令功能：向指定的数据表中插入一条记录。第49页【例19】在学生表中插入一条记录INSERTINTO学生VALUES('02020106'，'张小严'，'男'，'1996-10-3'，'团员'，'宁夏'，'010102'，607)【例20】在学生表中插入一条部分记录INSERTINTO学生(学号，姓名，性别，出生年月，政治面貌，籍贯，班级编号)VALUES('02020109'，'黄方'，'男'，'1997-1-3'，'团员'，'河北'，'02020202')

第50页4.5.2更新记录命令格式：UPDATE<表名>SET<字段1>=<表达式1>[，<字段2>=<表达式1>……][WHERE<条件>]命令功能：在指定的数据表中，对满足给定条件的记录的数据进行更新。第51页【例21】将成绩表中55-59分之间的成绩加5分。UPDATE成绩SET成绩=成绩+5WHREE成绩between55and59

第52页4.5.3删除记录命令格式：DELETEFROM<表名>[WHERE<条件>]命令功能：在指定的数据表中，删除满足条件的记录。【例22】删除学生表中学号为01010110的学生信息DELETEFROM学生WHERE学号='01010110'第53页4.6视图第54页视图是一个根据数据库表而定制的虚拟逻辑表，是观察表中