第五章关系数据库标准语言SQL(一)

第五章关系数据库标准语言SQL一、SQL简介SQL（StructuredQueryLanguage）是结构化查询语言的缩写。SQL功能命令动词数据查询SELECT数据定义(表的定义、删除和表结构的修改)CREATE、DROP、ALTER数据操纵(数据的插入、更新和删除)INSERT、UPDATE、DELETE数据控制例1：SQL是哪几个英文单词的缩写？()A）StandardQueryLanguageB）StructuredQueryLanguageC）SelectQueryLanguageD）以上都不是二、数据查询1、简单查询格式：SELECT——FROMSELECT：给出要在查询结果中显示的字段名、表达式或常量FROM：给出了查询的数据来源于哪个表例1：从student中查询所有学生的姓名、出生日期、入学分数例2：查询所有学生的详细信息备注：*表示显示表中所有字段练习1：从职工中检索所有的工资值练习2：检索仓库关系中的所有元组例3：查询显示所有学生的姓名，年龄，院系号，入学分数和增加了5％后的入学分数，并将增加了5%的入学分数显示为“新分数”SELECT姓名,INT((DATE()-出生日期)/365)年龄,院系号,入学分数,;入学分数*1.05AS新分数FROMstudent备注：AS的用法2、条件查询格式：SELECT——FROM——WHEREWHERE：给出查询的条件（1）WHERE条件中常见的关系运算例：查询显示入学分数在500分以上的学生的姓名和入学分数SELECT姓名,入学分数FROMstudentWHERE入学分数578例：查询2系和4系中所有82年以后出生的非党员的学生的姓名，出生日期，入学分数SELECT姓名,出生日期,入学分数FROMstudentWHERE;(院系号=2OR院系号=4)AND出生日期={^1982/1/1}and党员=.F.备注：WHERE的用法练习：检索工资多于2220元的职工号（2）WHERE条件中特殊运算符的应用（p118）1、NOT，！=，例：查询所有不是83年出生的学生2、LIKE：字符匹配运算符。%表示0个或多个连续的字符；_（下划线）表示一个任意字符例：查询STUDENT表所有姓王的学生信息例：查询COURSE表中所有不是基础课的课程例：查询STUDENT表所有学号第2位为“0”的学生的信息3、between……and：在什么之间，并包括边界值例：查询SC表中成绩在60—80分之间的学生的信息4、不确定值的查询：ISNULL|ISNOTNULL例：查询SC表中成绩不确定的学生的学号5、IN：在一个值列表中进行查询例：查询STUDENT表中所有院系号为2或3或4的学生的信息3、计算与分组查询（1）计算函数（P120）COUNT([DISTINCT]字段表达式)——统计记录个数例:统计选修了课程的学生人数。SELECTCOUNT(DISTINCT学号)FROMsc例:在student表中统计83年以后（含83年）出生的学生人数。SELECTCOUNT(*)FROMSTUDENTWHEREYEAR(出生日期)=1983SUM([DISTINCT]字段表达式)——求和例:计算所有学生的入学分数的总和SELECTSUM(入学分数)AS总入学分数FROMstudentAVG([DISTINCT]字段表达式)——求平均值例:计算选修了c2课程的学生的成绩的平均分SELECTAVG(成绩)AS平均成绩FROMscWHERE课程号=c2MAX(字段表达式)——求最大值例：查询所有学生的最高入学分数SELECTMAX(入学分数)AS最高分数FROMstudentMIN(字段表达式)——求最小值例：查询02级学生所选修课程的最低分SELECTMIN(成绩)AS最低分FROMscWHERELEFT(学号,2)=02说明：所有查询函数的计算结果都只有唯一的值SUM和AVG函数中字段表达式必须是数值型MAX和MIN函数的参数中不能有DISTINCT计算函数不能嵌套使用如果使用了计算函数，在SELECT子句中的其他字段没有意义。(2)分组查询：GROUPBY……HAVINGGROUPBY:查询结果依据什么进行分组例：查询每个学生选修的课程门数select学号,count(*)as选课门数FROMSCGROUPBY学号练习：查询每个学生最高成绩例：查询每门课的选课人数SELECT课程号,COUNT(*)AS选课人数FROMSCGROUPBY课程号HAVING:给出分组结果的筛选条件，即只有符合条件的分组项才会在查询结果中出现例：查询至少选修了3门课的学生的学号，选课门数select学号,count(*)as选课门数FROMSCGROUPBY学号HAVINGcount(*)=3练习：查询至少选修了2门课的学生的学号和总成绩select学号,SUM(成绩)as总分FROMSCGROUPBY学号HAVINGcount(*)=2例：查询每个年级的学生的人数SELECTLEFT(学号,4)AS年级,COUNT(*)AS学生人数FROMSTUDENTGROUPBY年级练习：查询每个年级的学生入学分数的平均值WHERE与HAVING的关系例：查询统计至少有2人及格的课程的总成绩SELECT课程号,SUM(成绩)AS总成绩FROMSCWHERE成绩=60GROUPBY课程号HAVINGCOUNT(*)=2说明：1)一般在查询要求中含有“各”、“每”字时要用到分组子句2)在使用分组子句时，SELECT子句中一般只能有分组字段和统计函数3)分组字段可以是数据源或查询结果中的字段名或者字段表达式，也可以是查询结果中的列号4)WHERE条件和HAVING条件都是对条件的限定，但它们所限定的范围不同，WHERE条件是对整个查询的条件限定，限定哪些记录参与查询。HAVIING条件是对分组条件的限定，在使用时必须有GROUPBY语句，是对分组结果进行筛选5)WHERE条件中不能有统计函数，而HAVING条件中通常都有统计函数。例：使用SQL语句进行分组检索时，为了去掉不满足条件的分组，应当（）。A）使用WHERE子句B）在GROUPBY后面使用HAVING子句C）先使用WHERE子句，再使用HAVING子句D）先使用HAVING子句，再使用WHERE子句例：下面有关HAVING子句描述错误的是（）。A）HAVING子句必须与GROUPBY子句同时使用，不能单独使用B）使用HAVING子句的同时不能使用WHERE子句C）使用HAVING子句的同时可以使用WHERE子句D）使用HAVING子句的作用是限定分组的条件4、排序查询格式：SELECT—FROM—WHERE—GROUPBY—HAVING—ORDERBY—（1）范围参数：ALL、DISTINCTALL:显示查询结果中的全部记录DISTINCT：在查询结果中剔除重复行例：查询所有的学生的姓名，院系号和党员信息SELECTALL姓名,院系号,党员FROMstudent例：查询哪些学生选修了C1或C2课程SELECTDISTINCT学号FROMscwhere课程号=c1OR课程号=c2（2）排序短语：ORDERBY字段名1,字段名2ASCORDERBY:对最终的查询结果进行排序,ASC表示升序，是默认值，DESC表示降序。例：按入学分数的降序查询显示所有学生的信息SELECT*FROMstudentORDERBY入学分数DESC例：查询所有学生的信息，要求按院系号升序排序，院系号相同按出生日期的降序排序SELECT*FROMstudentORDERBY院系号,出生日期DESC例：查询所有学生的姓名，年龄，入学分数和党员情况，查询结果按年龄的降序排序SELECT姓名,YEAR(DATE()-YEAR(出生日期)年龄,入学分数,党员FROMstudent;ORDERBY年龄DESC例：查询所有党员的入学分数增加11％后的学生信息，并按增加后的新入学分数升序显示SELECT*,入学分数*1.11FROMstudentwhere党员=.T.ORDERBY10说明：字段名是指查询中字段的标题名,只能是SELECT后的字段名；ORDERBY是对最终的查询结果进行排序,只能在主查询中使用.（3）显示部分结果：TOPN、TOPNPERCENTTOPN：显示排序结果中的前N条记录TOPNPERCENT:显示排序结果中的前N%条记录例：查询显示入学分数最高的3个学生的姓名，入学分数以及出生日期SELECTTOP3姓名,入学分数,出生日期FROMstudent;ORDERBY入学分数DESC例：查询显示成绩最低的40％的学生的学号、课程号和成绩SELECTTOP40PERCENT学号,课程号,成绩FROMscORDERBY成绩说明：只有使用ORDERBY排序之后，才能用TOPn和TOPnPERCENT显示部分记录5、多表的等值联接查询SELECT……FROM……；WHERE查询条件AND联接条件例：查询所有学生的学号，姓名，出生日期，院系号以及学生所在相关院系的全部信息SELECT学号,姓名,出生日期,DEP.院系号,DEP.*FROMSTUDENT,DEP;WHERESTUDENT.院系号=DEP.院系号例：查询所有男生的姓名和他们的所选修的课程的课程号，课程名以及成绩SELECT姓名,SC.课程号,课程名,成绩FROMSTUDENT,SC,COURSE;WHERESTUDENT.学号=SC.学号ANDSC.课程号=COURSE.课程号AND性别=男例：查询平均成绩在70分以上并且总成绩最高的课程的课程名与总成绩SELECTTOP1课程名,sum(成绩)FROMsc,course;WHEREsc.课程号=course.课程号;GROUPBY课程名HAVINGAVG(成绩)70ORDERBY2desc说明（P124）：（1）在SELECT子句中，如果涉及到的两个表有的相同表达式，则需要注明结果中的该表达式所从属的表名。说明格式为“表名.字段名”。（2）FROM表1,表2,……：指定为查询提供数据源的各个表。（3）[WHERE联接条件及查询条件]：给出表与表之间的联接条件和查询条件。联接条件一般是不同表的相同字段名或者相同值域的字段名相等。（4）联接时的执行过程是：从表1中的第1条记录开始，用指定字段名的值与表2中每条记录的指定字段名的值依次比较，如果相等就组成一条新记录放在查询结果中，直到表1中的所有记录比较完为止。6、嵌套查询（P128）查询时可能查询的条件不能直接给出，而须先执行另一个查询，得到结果后才能给出条件，这时就要用到嵌套查询。嵌套查询是指WHERE子句中包含另一个SELECT–FROM–WHERE语句的查询。嵌套查询的运行机理是先执行内层查询，然后把获得的结果作为外层查询的条件。（1）=、!=、、、=、=连接内外层查询功能：当子查询的返回结果唯一时，主查询WHERE子句中指定的字段可与内层查询的查询结果进行比较运算。例：查询与“赵丽”同一个院系的学生的信息例：查询成绩高于所有课程的平均成绩的学生的学号、课程号和成绩例：查询选修了“数据结构”的学生的学号和成绩（2）IN、NOTIN联接内外层查询功能：当子查询的返回结果有多个时，可以用IN和NOTIN联接内外层查询。例：查询与“王芳”，“赵丽”不是同一个院系的学生信息。例：查询选修了c1课程的学生的姓名，院系号和党员情况（3）用量词ANY，SOME，ALL联接内外层查询功能：当子查询的返回结果有多条时，可以使用量词联接内外层查询。ANY,SOME：在进行比较运算时，只要主查询条件与任意一个子查询结果比较时值为真，就符合主查询的条件；ALL:在进行比较运算时，只有主查询条件与所有子查询结果比较时值为真，就符合主查询的条件。例：查询哪几门课程的成绩比c3课程的最低成绩要高例：查询哪几门课程的成绩比c3课程的最高成绩要高7、查询结果