数据库软件Visual Foxpro课件第七章 结构化查询语言SQL

1、1第七章第七章 结构化查询语言结构化查询语言sql sql sql语言功能包括数据定义、数据查询、数据操纵、和数据控语言功能包括数据定义、数据查询、数据操纵、和数据控制四个方面。制四个方面。7.1 sql7.1 sql的数据定义功能的数据定义功能 sqlsql的数据定义功能用于定义数据表的结构，如创建、修改、的数据定义功能用于定义数据表的结构，如创建、修改、删除数据表。删除数据表。一一 创建数据表创建数据表 命令：命令：create table ( ,), ) 功能：创建数据表结构功能：创建数据表结构 例例7-1： create table stud(学号学号 c(8),姓名姓名 c(8),性

2、别性别 c(2),出生日期出生日期 d,; 专业号专业号 c(2),入校总分入校总分 n(6,2),团员团员 l,简历简历 m,照片照片 g)2二二 修改数据表修改数据表 命令命令1：alter table add/alter column ,小数位小数位 命令命令2： alter table drop column rename column to 功能：修改表的结构功能：修改表的结构例例7-2：在：在stud中增加一个中增加一个“电话电话”字段字段 alter table stud add column 电话电话 c(8)例例7-3：将：将stud中电话字段的宽度该为中电话字段的宽度该为1

3、0 alter table stud alter 电话电话 c(10) 例例7-4：删除：删除stud中的电话字段中的电话字段 alter table stud drop 电话电话例例7-5：将：将stud中中“入校总分入校总分”字段该名为字段该名为“高考成绩高考成绩” alter table stud rename 入校总分入校总分 to 高考成绩高考成绩37.2 sql7.2 sql的数据查询功能的数据查询功能一一 select命令的格式命令的格式 格式：格式：select all/distinct top percent. as , as from , inner/left/right/

4、full join on into /to file/to printer/to screen where and and/orand/or group by , having order by asc/desc, asc/desc 功能：实现数据查询。功能：实现数据查询。select语句的执行过程为：根据语句的执行过程为：根据where子子句的连接和检索条件，从句的连接和检索条件，从from子句指定的基本表或视图中选取子句指定的基本表或视图中选取满足条件的元组，再按照满足条件的元组，再按照select子句中指定的列表达式，选出子句中指定的列表达式，选出元组中的属性值形成结果表。元组中的属性值

5、形成结果表。4说明：说明：(1)from , : 指明要查询的所有数据表指明要查询的所有数据表(2) . as ：可以可以是字段名是字段名,也可是表达式，也可是表达式， 而而是字段所在的表名；是字段所在的表名； as表示可以给输出时显示的列标题重新命名表示可以给输出时显示的列标题重新命名二二 投影查询投影查询 例例7-7：查询查询stud表中前表中前5列的信息列的信息 select 学号学号,姓名姓名,入校总分入校总分,性别性别,出生日期出生日期 from stud 例例7-8：查询查询stud表中入校总分最低的五名学生基本情况，按入校表中入校总分最低的五名学生基本情况，按入校总分由低到高进行

6、排列，同时指定表中的字段在查询结果中的总分由低到高进行排列，同时指定表中的字段在查询结果中的列标题列标题 sele top 5 学号学号 as 学生编号学生编号,姓名姓名 as 学生姓名学生姓名,性别性别 as 学生性别学生性别,; 入校总分入校总分 as 高考分数高考分数 from stud order by 入校总分入校总分 例例7-9：在在xk中查询选修了课程的学号中查询选修了课程的学号 sele distinct 学号学号 from xk5三三 条件查询条件查询 当要在数据表中找出满足某些条件的行时，则需使用当要在数据表中找出满足某些条件的行时，则需使用where子句指定查询条件子句指

7、定查询条件(格式：格式：where ) 1. 比较大小比较大小 例例7-11：在在stud.dbf中查询专业号为中查询专业号为“01”的学生记录的学生记录 例例7-12：在：在stud.dbf中查询入学总分大于等于中查询入学总分大于等于550分的学生学号、分的学生学号、姓名、性别、出生日期和入学总分姓名、性别、出生日期和入学总分 sele 学号学号,姓名姓名,性别性别,出生日期出生日期,入校总分入校总分 from stud where; 入校总分入校总分=550sele * from stud where 专业号专业号=012. 多重条件查询多重条件查询 当当where子句需要指定一个以上的查

8、询条件时，则需要使用逻子句需要指定一个以上的查询条件时，则需要使用逻辑运算符辑运算符and，or和和not将其连接成复合的逻辑表达式。将其连接成复合的逻辑表达式。例例7-13：在在stud.dbf中查询男团员的学号、姓名、性别和入校总分。中查询男团员的学号、姓名、性别和入校总分。sele 学号学号,姓名姓名,性别性别,入校总分入校总分 from stud where 性别性别=男男 and 团员团员63. 确定范围确定范围 例例7-14：在在js中查询工资在中查询工资在1500至至2000之间的教师的教师号、姓之间的教师的教师号、姓名及职称。名及职称。 sele 教师号教师号,姓名姓名,职称职

9、称 from js where 工资工资=1500 and 工资工资=80sele 学号学号,课程号课程号,成绩成绩 from xk where 课程号课程号 in (c140 ,c160); and 成绩成绩=807例例7-17：在：在xk中查询没有选修中查询没有选修c140，也没有选修，也没有选修c160的课程并且的课程并且成绩在成绩在85和和90之间的学生的学号、课程号和成绩之间的学生的学号、课程号和成绩 sele 学号学号,课程号课程号,成绩成绩 from xk where 课程号课程号 not in; (c140 ,c160) and 成绩成绩 between 85 and 905.

10、 部分匹配查询部分匹配查询 也称模糊查询，使用也称模糊查询，使用like或或not like子句定义，格式：子句定义，格式： like %：表示任意长度的字符串：表示任意长度的字符串 _：表示任意一个字符或汉字：表示任意一个字符或汉字例例7-18：查询查询stud表中所有姓表中所有姓“刘刘”的学生的学号和姓名的学生的学号和姓名 例例7-19：查询查询stud表中第二个汉字是表中第二个汉字是“敏敏”的学生的学号和姓名的学生的学号和姓名 sele 学号学号,姓名姓名 from stud where 姓名姓名 like 刘刘%sele 学号学号,姓名姓名 from stud where 姓名姓名 l

11、ike _敏敏%8四四. 统计查询统计查询常用函数及功能：常用函数及功能： avg sum count max min说明：在以上函数中，可使用说明：在以上函数中，可使用all或或distinct distinct：表示在计算时取消指定列中的重复值：表示在计算时取消指定列中的重复值 all(默认值默认值)：表示不取消重复值：表示不取消重复值例例7-20：求学号为求学号为“s0401109”学生的总分和平均分并显示如图学生的总分和平均分并显示如图 例例7-21：求选修课程号为：求选修课程号为c140学生的最高分、最低分及之间相差学生的最高分、最低分及之间相差的分数的分数(显示课程号显示课程号)

12、sele 学号学号,sum(成绩成绩) as 总分总分,avg(成绩成绩) as 平均分平均分 from xk; where 学号学号=s0401109sele 课程号课程号, max(成绩成绩) as 最高分最高分,min(成绩成绩) as 最低分最低分,max(成成; 绩绩)-min(成绩成绩) as 相差分数相差分数 from xk where 课程号课程号=c1409例例7-22：求入校总分在：求入校总分在580分以上的学生的人数分以上的学生的人数 sele count(学号学号) as 入校总分在入校总分在580分以上的学生的人数分以上的学生的人数 from; stud where

13、入校总分入校总分=580例例7-23：统计：统计xk表中有多少门课表中有多少门课 sele count(distinct 课程号课程号) as 选课表中课程数选课表中课程数 from xk例例7-24：利用特殊函数：利用特殊函数count(*)求求js表中表中“教授教授”和和“副教授副教授”的人的人数数 sele count(*) as 教授和副教授的人数教授和副教授的人数 from js ; where 职称职称 in(“教授教授”,“副教授副教授”)五五. 分组查询分组查询 将查询结果按照指定字段相同的值进行分组，使用将查询结果按照指定字段相同的值进行分组，使用group by子句子句例例

14、7-25：查询各位教师的教师号及其任课的门数查询各位教师的教师号及其任课的门数 sele 教师号教师号,count(*) as 任课门数任课门数 from sk group by 教师号教师号10例例7-26：查询选修两门以上课程的学生学号和选课门数：查询选修两门以上课程的学生学号和选课门数 例例7-27：在课程：在课程c120、c140、c150、c160中查询学生平均成绩在中查询学生平均成绩在80分以上课程的学生的平均分（显示课程号）分以上课程的学生的平均分（显示课程号） select 课程号课程号,avg(成绩成绩) as 平均分平均分 from xk group by 课程号课程号;

15、having 课程号课程号 in (c120,c140,c150,c160) and avg(成绩成绩)=80六六. 查询的排序查询的排序 当需要对查询结果排序时，可以使用当需要对查询结果排序时，可以使用order by 子句对查询的结子句对查询的结果按一个或多个属性列进行升序果按一个或多个属性列进行升序(asc)或降序或降序(desc)排列排列例例7-28：查询选修了课程查询选修了课程“c140”的学生学号和成绩，并按成绩降序的学生学号和成绩，并按成绩降序 排列排列select 学号学号 as 选课门数在两门以上学生的学号选课门数在两门以上学生的学号,count(*) as; 选课门数选课门

16、数 from xk group by 学号学号 having count(*)=211 select 学号学号,成绩成绩 from xk where 课程号课程号=c140 order by 成绩成绩 desc例例7-29：查询选修：查询选修c120、c130和和c150课程学生的学号、课程号和成课程学生的学号、课程号和成绩，查询结果按课程号升序排列，课程号相同再按成绩降序排绩，查询结果按课程号升序排列，课程号相同再按成绩降序排列列 select 学号学号,课程号课程号,成绩成绩 from xk where 课程号课程号; in(c120,c130,c150) order by 课程号课程号,

17、成绩成绩 desc例例7-30：求选课在三门以上且各门课程均及格的学生的学号及平均：求选课在三门以上且各门课程均及格的学生的学号及平均成绩，查询结果按学号降序列出成绩，查询结果按学号降序列出 select 学号学号,avg(成绩成绩) as 平均成绩平均成绩 from xk group by 学号学号 having; count(*)=3 and 成绩成绩=60 order by 学号学号 desc七七 . 连接查询连接查询 通过加在连接字段上的条件将多个表连接起来，以便从多个表通过加在连接字段上的条件将多个表连接起来，以便从多个表 中查询数据中查询数据121. 等值连接与非等值连接等值连接与

18、非等值连接例例7-31：查询陈静老师所讲授课程的课程号，教师号和姓名查询陈静老师所讲授课程的课程号，教师号和姓名 inner join : 内连接，显示符合条件的记录内连接，显示符合条件的记录 例例7-32：查询选修了课程：查询选修了课程“数据库数据库”或或“电子商务电子商务”学生的学号、学生的学号、姓名选课名和成绩姓名选课名和成绩 select stud.学号学号,姓名姓名,课程名课程名,成绩成绩 from stud,xk ,kc where stud.学号学号; =xk.学号学号 and xk.课程号课程号=kc.课程号课程号 and 课程名课程名 in(数据库数据库,; 电子商务电子商务

19、)select js.教师号教师号,js.姓名姓名,sk.课程号课程号 from js inner join sk on js.教师号教师号; =sk.教师号教师号 and 姓名姓名=陈静陈静select js.教师号教师号,js.姓名姓名,sk.课程号课程号 from js,sk; where js.姓名姓名=陈静陈静 and js.教师号教师号=sk.教师号教师号 132. 自身连接自身连接例例7-33：查询所有比查询所有比“陈静陈静”工资高的教师姓名、职称、工资和工资高的教师姓名、职称、工资和“陈静陈静”工资工资 例例7-34:查询与查询与“周密周密”职称相同的教师姓名、性别和职称职称相

20、同的教师姓名、性别和职称 select x.姓名姓名,x.性别性别,x.职称职称 from js as x,js as y; where x.职称职称=y.职称职称 and y.姓名姓名=周密周密select x.姓名姓名,x.工资工资, x.职称职称,y.工资工资 as 陈静工资陈静工资 from js as x,js as; y where x.工资工资 y.工资工资 and y.姓名姓名=陈静陈静xy3. 外连接外连接例例7-35：查询教师姓名、职称及所授课程的课程名查询教师姓名、职称及所授课程的课程名 select 姓名姓名,职称职称,课程名课程名 from js,sk,kc wher

21、e js.教师号教师号=sk.教师号教师号; and sk.课程号课程号=kc.课程号课程号14说明：外连接中，参与连接的表有主从之分，以主表中的每行数据说明：外连接中，参与连接的表有主从之分，以主表中的每行数据去匹配从表中的数据列，符合连接条件的数据将直接显示，对去匹配从表中的数据列，符合连接条件的数据将直接显示，对于那些不符合条件的列，将填上于那些不符合条件的列，将填上null值显示。左连接的主表在值显示。左连接的主表在关键字关键字left join的左方，右连接的主表在关键字的左方，右连接的主表在关键字right join的右的右方方例例7-36：在在js表中添加一条记录，以外连接的方式

22、查询教师姓名、表中添加一条记录，以外连接的方式查询教师姓名、职称及所授课程的课程号职称及所授课程的课程号 select 姓名姓名,职称职称,课程号课程号 from js left join sk on js.教师号教师号=sk.教师号教师号 select 姓名姓名,职称职称,课程号课程号 from sk left join js on js.教师号教师号=sk.教师号教师号select 姓名姓名,职称职称,课程号课程号 from js right join sk on js.教师号教师号=sk.教师号教师号select 姓名姓名,职称职称,课程号课程号 from sk right join j

23、s on js.教师号教师号=sk.教师号教师号select 姓名姓名,职称职称,课程号课程号 from js full join sk on js.教师号教师号=sk.教师号教师号select 姓名姓名,职称职称,课程号课程号 from sk full join js on js.教师号教师号=sk.教师号教师号15八八. 嵌套查询嵌套查询 在在where子句中包含一个形如子句中包含一个形如select-from-where的查询块，的查询块，此查询块称为嵌套查询或子查询，包含子查询的语句称为外部此查询块称为嵌套查询或子查询，包含子查询的语句称为外部查询或父查询。查询或父查询。1. 返回一个

24、值的子查询返回一个值的子查询 当子查询的返回值只有一个时，可以使用比较运算符将父当子查询的返回值只有一个时，可以使用比较运算符将父查询和子查询连接起来查询和子查询连接起来 例例7-37：查询与查询与“陈静陈静”教师职称相同的教师姓名和职称教师职称相同的教师姓名和职称 2. 返回一组值的子查询返回一组值的子查询 当子查询的返回值不止一个时，则不能直接使用比较运算当子查询的返回值不止一个时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入符，可以在比较运算符和子查询之间插入any或或allselect 姓名姓名,职称职称 from js where 职称职称=; (select 职称职称

25、 from js where 姓名姓名=陈静陈静)16(1) 使用使用any（any的含义为任意一个）的含义为任意一个）例例7-38：查询讲授课程号为查询讲授课程号为“c140”的教师姓名的教师姓名 例例7-39：查询比男生入校总分最低分高的女生姓名和入校总分：查询比男生入校总分最低分高的女生姓名和入校总分 select 姓名姓名,入校总分入校总分 from stud where 性别性别=女女 and 入校总分入校总分any; (sele 入校总分入校总分 from stud where 性别性别=男男)说明：可以使用说明：可以使用in 代替代替“=any”select 姓名姓名 from

26、js where 教师号教师号=any; (sele 教师号教师号 from sk where 课程号课程号=c140)17(2) 使用使用all（all含义为全部）含义为全部）例例7-41：查询比女生入校总分最高分高的男生姓名和入校总分查询比女生入校总分最高分高的男生姓名和入校总分select 姓名姓名,入校总分入校总分 from stud where 性别性别=男男 and 入校总分入校总分all; (sele 入校总分入校总分 from stud where 性别性别=女女)18(4) 使用使用exists exists用于判断查询结果是否存在。当用于判断查询结果是否存在。当exists

27、的子查询结果存在的子查询结果存在即即非空时，则外层的非空时，则外层的where子句返回真值，否则返回假值子句返回真值，否则返回假值例例7-42：查询讲授课程号为：查询讲授课程号为“c140”的教师姓名的教师姓名 select 姓名姓名 from js where exists; (select * from sk ; where js.教师号教师号=sk.教师号教师号 and 课程号课程号=c140) 19九九 查询结果的输出查询结果的输出 into table ：输出到数据表；：输出到数据表； into cursor ：输出到临时表。临时表只存储在内存中，：输出到临时表。临时表只存储在内存中

28、，不作为永久的数据表存储在磁盘中不作为永久的数据表存储在磁盘中例例7-43：查询陈静老师所讲授课程的课程号，教师号和姓名，输出：查询陈静老师所讲授课程的课程号，教师号和姓名，输出到临时表到临时表,然后显示该表的内容然后显示该表的内容 7.3 sql7.3 sql的数据更新功能的数据更新功能一一 插入数据记录插入数据记录1. 插入一行新记录：插入一行新记录： insert into 表名表名(字段名字段名1,字段名字段名2, ) values(表达式表达式1,表达表达式式2 )select js.教师号教师号,js.姓名姓名,sk.课程号课程号 from js,sk where js.姓名姓名=

29、陈静陈静 ; and js.教师号教师号=sk.教师号教师号 into cursor cx1brow202. 插入多行记录插入多行记录 将一个表中的数据抽取数行插入另一表中，可以通过子查询将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现来实现例例7-45：求出各学生选修课的平均成绩，把结果存放在新表学生平：求出各学生选修课的平均成绩，把结果存放在新表学生平 均成绩中均成绩中 create table 学生平均成绩学生平均成绩(姓名姓名 c(8),平均成绩平均成绩 n(3) insert into 学生平均成绩学生平均成绩 sele 姓名姓名,avg(成绩成绩) from stud,xk; where stud.学号学