结构化查询语言SQL.ppt

1、第5章 结构化查询语言SQL,5.1 SQL语言概述,SQL语言的主要优点如下： SQL语言是一种一体化的语言，提供了完整的数据定义和操纵功能。 SQL语言具有完备的查询功能。 SQL语言非常简洁，易学易用。 SQL语言是一种高度非过程化的语言。 SQL语言既能以交互方式直接使用，也能嵌入到各种高级语言中使用。 SQL语言不仅能对数据表进行各种操作，也可对视图进行操作。,SQL语言的9个命令动词,SQL数据查询中的常用运算符,说明：,在表达式中，字符型数据需要用定界符括起来；日期型数据也需要定界符括起来。如：“计算机”、2008-5-30 Between运算符的命令格式如下： Between

2、and Like运算符通常与通配符“_”、“%”一起用,5.1 SQL的数据查询功能,SQL的查询命令格式： SELECT ALL|DISTINCTTOP PERCENT; AS , AS ; FROM ! AS; INNER|LEFT|RIGHT|FULL JOIN ; INTO |TO FILE|TO PRINTER|TO SCREEN; WHERE AND ; AND|OR AND|OR ; GROUP BY ,HAVING ; UNION ALL SELECT语句; ORDER BY ASC|DESC,ORDER BY ASC|DESC; 功能：根据指定的条件从一个或多个表中检索并输出

3、数据。,常用的select语句包含四个部分：,SELECT ALL | DISTINCT AS ; FROM AS; WHERE AND ; GROUP BY HAVING ; ORDER BY ASC|DESC,说明：,在SQL select查询语句中，selectfrom两个命令动词是最基本的并且不可缺少的，其它命令动词根据需要选用。 SQL select查询语句的功能是将满足要求的数据查询出来，它不会更改数据库中的数据。,基本查询,单表无条件查询 格式：select distinctfrom 例1.检索student表中学生的学号、姓名、专业信息。 SELECT 学号,姓名,专业 FRO

4、M STUDENT 例2.检索score表中所有信息。 法1：SELECT 学号,课程编号,成绩 FROM SCORE 法2：SELECT * FROM SCORE,例3.检索teacher表中职称信息。 SELECT 职称 FROM teacher 例4.检索teacher表中职称信息（去掉重复值）。 SELECT DISTINCT 职称 FROM teacher 注意：distinct短语的位置，它位于select短语后面。,单表有条件查询 格式：select distinctfrom; where 例5.查询成绩大于80的学号、成绩 SELECT 学号,成绩 FROM SCORE WHE

5、RE 成绩80 例6.从score表中检索选修了课程编号为03的，并且成绩大于80分的学号、课程编号和成绩 法1.SELECT 学号,课程编号,成绩 FROM SCORE WHERE 课程编号=“03” AND 成绩80,例7.从score表中检索选修了课程编号为02或05的，并且成绩大于80分的学生成绩信息 SELECT * FROM SCORE WHERE (课程编号=“02” OR 课程编号=“05”) AND 成绩80 例8.从student表中检索性别为“男”且出生日期在1989年1月1日之前的学生学号、姓名、性别、出生日期和年龄 SELECT 学号,姓名,性别,出生日期, YEAR

6、(DATE()-YEAR(出生日期) AS 年龄 FROM STUDENT WHERE 出生日期1989-01-01 AND 性别=“男”,当查询设计多个表时，就要用到多表的联接查询。联接是关系的基本操作之一，联接查询是基于多个关系的查询。 多表无条件联接查询 格式：SELECT FROM WHERE 例9.检索出学号、姓名、性别、成绩 SELECT STUDENT.学号,姓名,性别,成绩 ; FROM STUDENT,SCORE; WHERE STUDENT.学号=SCORE.学号,说明,From短语后面接多个表名。当查询涉及多个表时，将所有表名都放在from短语后面，并用逗号隔开表。 两个

7、表能够进行联接查询的前提条件是两表一定要有相同字段名。当from短语之后的多个表中含有相同的字段时，必须用表名前缀直接指明字段名。 Select短语中的检索项如果不唯一，需要指明该检索项来自哪个表。,例10.检索选修了课程的学生信息。 SELECT DISTINCT STUDENT.* ; FROM STUDENT,SCORE; WHERE STUDENT.学号=SCORE.学号,多表有条件联接查询 格式：SELECT FROM WHERE AND 例11.检索男同学的成绩大于90的学号、姓名、性别、成绩 SELECT STUDENT.学号,姓名,性别,成绩 ; FROM STUDENT,SC

8、ORE; WHERE STUDENT.学号=SCORE.学号 AND (性别=“男” AND 成绩90) 例12.检索学号为200731001的学生学号、姓名、以及所选修的课程名称和授课教师姓名。 SELECT STUDENT.学号,姓名,课程名称,教师姓名 ; FROM STUDENT,SCORE,COURSE,TEACHER; WHERE STUDENT.学号=SCORE.学号 AND ; COURSE.课程编号=SCORE.课程编号 AND ;； COURSE. 教师编号= TEACHER.教师编号 AND ; STUDENT.学号 = “200731001”,排序查询,格式：SELEC

9、T FROM WHERE AND ; ORDER BY ASC | DESC, ASC | DESC 例13.按学生的出生日期升序检索出宣布学生信息。 SELECT * FROM STUDNENT ORDER BY 出生日期 例14.检索学生的选课信息，按课程编号的升序排序，如果课程编号相同则按成绩降序排序。 SELECT * FROM SCORE ORDER BY 出生日期 ASC,成绩 DESC 例15.检索女同学的学号、姓名、出生日期、课程编号和成绩，并按出生日期升序排序，再按成绩降序排序。 SELECT STUDENT.学号,姓名,出生日期,课程编号,成绩; FROM STUDENT,

10、SCORE WHERE STUDENT.学号 = SCORE.学号 AND 性别=“女” ; ORDER BY 出生日期 ASC,5 DESC,说明：,在一个查询语句中，可以按一列或多列进行排序。注意排序关键字之间用逗号隔开。 Order by 短语中的排序依据可以用列序号表示,计算与分组查询,常用库函数及功能,简单的计算查询 例16.统计student表中有多少学生记录 SELECT COUNT(*) AS 人数 FROM STUDNENT 说明： 如果要统计一个表中有多少元组，可以用count（*） 表达式“count（*） as 人数”不仅统计出表中的记录，而且为函数表达式命别名。,例1

11、7.统计score表中选修了课程编号为03的记录个数 SELECT COUNT(*) AS 人数 FROM SCORE WHERE课程编号 =“03” 例18.统计有多少学生选修了课程 SELECT COUNT(DISTINCT 学号) AS 人数 FROM SCORE 说明：在sql查询中，除非对表中的元组个数进行统计，一般情况下count函数应该使用distinct,例19.检索学号为200731001的学生平均成绩。 SELECT AVG(*) AS 平均分 FROM SCORE WHERE 学号=“200731001” 例20.检索score表中的最高分和最低分。 SELECT MAX

12、(成绩) AS 最高分, MIN(成绩) AS 最低分 FROM SCORE 例21.检索course表中所有课程的学分的总和。 SELECT SUM(学分) AS 学分总分 FROM COURSE,分组与计算查询 GROUP BY 短语用来对查询结果进行分组，把某一列的值相同的记录分在一组，通过计算每组产生一个结果。 格式： SELECT FROM WHERE AND ; GROUP BY HAVING ,说明： GROUP BY短语后面可以有多个字段名，分组时把在这些字段上值相同的记录分在一组。 HAVING子句总是跟在GROUP BY子句后，用来限定分组必须满足的条件，不可以单独使用。

13、都接条件表达式，但并不矛盾，当一个查询命令中既有where短语同时又有having短语时，先用where子句限定元组，然后进行分组，最后再用having子句限定分组。,例22.检索每个学生的平均成绩 SELECT 学号,AVG(成绩) AS 平均分 FROM SCORE GROUP BY 学号 例23.检索出每个学生的学号，姓名，总分，平均分，最高分，最低分。 SELECT SCORE.学号,姓名,SUM(成绩) AS 总分, AVG(成绩) AS 平均分,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分 FROM STUDENT,SCORE WHERE STUDENT.学号=SCO

14、RE. 学号 GROUP BY SCORE.学号 ORDER BY 总分 DESC,例24.检索出平均分在80分以上的学生的学号，姓名，平均分，并将结果按平均分升序排序。 SELECT SCORE.学号,姓名, AVG(成绩) AS 平均分 FROM STUDENT,SCORE WHERE STUDENT.学号=SCORE. 学号 GROUP BY SCORE.学号 HAVING AVG(成绩)=80 ORDER BY 平均分,带特殊运算符的条件查询,确定范围（betweenand） 例25：SELECT * FROM STUDENT WHERE 出生日期 BETWEEN 1987-12-30

15、 AND 1989-12-30 确定集合（selectfromwhere 字段名 not in（集合） 例26:查询选修了课程编号为02或04或05，并且成绩在80分以上的学生的学号、课程号和成绩 SELECT * FROM SCORE WHERE 课程编号 IN (“02”,“04”,“05”) AND 成绩=80,部分匹配查询（selectfromwhere 字段名 like ） “%”，“_” 例27：查询所有姓“孙”的学生的学号，姓名，性别，专业。 SELECT 学号,姓名,性别,专业 FROM STUDENT WHERE 姓名 LIKE “孙%”,嵌套查询,即允许在一个SELECT查

16、询命令的WHERE短语中，包含另一个SELECT查询命令。 例28：检索成绩在80分以上的学生信息。 SELECT * FROM 学生表; WHERE 学号 IN (SELECT 学号 FROM SCORE WHERE 成绩80),超联接查询,Sql提供超联接运算。原来的联接是只有满足联接条件，相应的结果才会出现在结果表中，而超联接是首先保证一个表中满足条件的元组都在结果中，然后将满足联接条件的元组与另一个表中的元组进行联接。 在一般sql中的超联接运算符是“*=”、“=*”,Vf不支持“*=”、“=*”两个超联接运算符，但有专门的联接运算格式，它们支持超联接查询。基本格式为： Select

17、； from inner | left | right | full join ； On ； Where ,例29：内部联接。即只有满足联接条件的记录才出现在查询结果中。 Select student.姓名，成绩； From student inner join score； On student.学号 =score.学号,例30.查询性别为男，且考试成绩在85分以上（含85）的学生的全部信息，并将结果按学号升序存入自由表nine.dbf文件。 Select student.*，score.成绩； From course inner join score； Inner join student

18、； On score.学号 = student.学号； On course.课程编号=score.课程编号； Where score.成绩=85; And 性别=“男”； Order by student.学号； Into table nine.dbf,查询中的几个特殊选项,显示部分结果： 格式：Top n percent 例. Select * top 2 from score order by 成绩 例. Select * top 40 percent from score order by 成绩 将结果存放在数组中 格式：into array 数值名 将结果存放在临时文件中 格式：int

19、o cursor cursorname,将结果存放在永久表中 格式：into table | dbf tablename 将结果存放在文本文件中 格式：to file filename additive 如果使用additive短语，则将结果追加到原文件尾部，否则将覆盖原有文件。 将结果直接输出到打印机 格式：to printer prompt 使用prompt项，在开始打印之前会打开打印机设置对话框,SQL数据定义功能,定义表 格式：CREATE TABLE|DBF NAME FREE ( (字段宽度,小数位数) NULLNOT NULL CHECK ERROR DEFAULT PRIMAR

20、Y KEY|UNIQUE REFERENCES TAG NOCPTRANS , ,PRIMARY KEY TAG |,UNIQUE TAG ,FOREIGN KEY TAG NODUP REFERENCES TAG ,CHECK ERROR) |FROM ARRAY ,表中常用的字段类型,例1.用sql命令创建“学生数据库”，并用create命令建立“学生表”。 Create database 学生数据库 Create table 学生表 (; 学号 c(4) primary key ,; 姓名 c(8),; 性别 l,; 年龄 i ,; 入学时间 d),例2.用sql命令创建 “教师表”。

21、Create table 教师表 (; 教师编号 c(3) primary key ,; 教师姓名 c(8),; 职称 c(8),; 工资 i check(工资1000 and 工资=6000 ) ; Error “工资的范围在1000-6000之间” ; Default 1500),例3.用sql命令创建 “课程表”。 Create table 课程表 (; 课程编号 c(2) primary key ,; 课程名称 c(8),; 学分 i,; 教师编号 c(3),; Foreign key 教师编号 tag 教师编号 reference 教师表),例4.用sql命令创建 “成绩表”。 Cr

22、eate table 成绩表 (; 学号 c(4),; 课程编号 c(2),; 成绩 n(6,2) check(成绩=0 and 成绩=100); error “成绩应该在0-100之间”; default 0,; Foreign key 学号 tag 学号 reference 学生表,; Foreign key 课程编号 tag 课程编号 reference 课程表),删除表,格式：drop table 例5.删除“学生表” Drop table 学生表,修改表结构,1命令格式1 格式：ALTER TABLE ADD|ALTER COLUMN (字段宽度,小数位数) NULLNOT NULL

23、 CHECK ERROR DEFAULT PRIMARY KEY|UNIQUE REFERENCES TAG NOCPTRANS 功能：为指定的表增加指定的字段，或者修改指定的字段。,例6.为score表增加一个长度为2的整型学分字段。 Alter table score add 学分 I 例7.为student表的家庭住址的长度由8改为20。 Alter table score alter 家庭住址 c(20),2命令格式2 格式：ALTER TABLE ALTER COLUMN NULLNOT NULL SET DEFAULT SET CHECK ERROR DROP DEFAULT DR

24、OP CHECK 功能：设置或删除指定表中指定字段的默认值和（或）约束条件。,例8.为score表中的“成绩”字段增加有效性规则：成绩在0-100之间且默认值为60。 Alter table score alter 成绩; Set default 60; Set check 成绩=0 and 成绩=100 error “成绩应该在0-100之间” 例9.为score表中的“成绩”字段删除有效性规则。 Alter table score alter 成绩; drop default ; drop check,3命令格式3 格式：ALTER TABLE DROP COLUMN SET CHECK

25、ERROR DROP CHECK ADD PRIMARY KEY TAG DROP PRIMARY KEY ADD UNIQUE TAG DROP UNIQUE TAG ADD FOREIGN KEY TAG REFERENCES TAG DROP FOREIGN KEY TAG SAVE RENAME COLUMN TO NOVALIDATE,功能：删除指定表中的指定字段、设置或删除指定表中指定字段的约束条件、增加或删除主索引、候选索引、外索引，以及对字段名重新命名等。,例10.将course表中的“教师编号”字段名改为“授课教师”。 Alter table course rename 教师编号 to 授课教师 例11.将score表中的“学分”字段删除。 Alter table score drop 学分 例12.将course表中的“课程名称”和“教师编号”字段定义为候选索引，索引名为htt_sp。 Alter table course add unique 课程名称+教师编号 tag htt_sp 例13.将teacher表中的主索引删除。 Alter table course drop primary key,SQL的数据操作功能,插入数据 1命令格式1 格式：INSERT INTO (,) VALUES(,) 功能：在指定表的尾部添加一条新记录，并将指定的