结构查询语言SQL课件

第四章结构化查询语言SQLVisualfoxpro程序设计第四章结构化查询语言SQLVisualfoxpro程本章主要内容4.1数据查询4.2数据操作4.3数据定义本章主要内容4.1数据查询1.SQL的中英文名称是什么?

2.SQL的语言功能是什么?

----结构化查询语言----StructuredQueryLanguage数据查询:select数据定义:create、drop、alter数据操纵:insert、update、delete数据控制:grant、revokeSQL语言概述1.SQL的中英文名称是什么?----结构化查询语言数据查询表4-1SQL语言的9个命令动词

SQL功能命令动词数据查询Select数据操作插入记录Insert更新记录Update删除记录Delete数据定义定义基本表或索引Create删除基本表或索引Drop修改基本表或索引Alter表4-1SQL语言的9个命令动词SQL功能命令动词数据本节主要内容1、基本查询2、排序查询3、带特殊运算符的条件查询4、计算与分组查询5、嵌套查询6、利用空值查询7、查询中的特殊选项4.1数据查询本节主要内容4.1数据查询select…from…where…字段名表名查询条件一、基本查询格式：字段名表名查询条件一、基本查询格式：1、无条件查询：SELECT…FROM…Eg1：将student表中的专业信息检索出来。Eg2：将student表中的专业信息检索出来，并去掉重复元组。Eg3：从student表中检索出学生的学号、姓名、专业信息。Eg4：查询score表中的所有信息。方法一：方法二：

select专业fromstudent

selectdistinct专业fromstudentselect学号,姓名,专业fromstudentselect*fromscoreselect学号,课程编号,成绩fromscore1、无条件查询：SELECT…FROM…select注意：去掉重复元组distinct所有属性*，Select后多字段名之间去掉重复元组distinct所有属性*，Select后多字段2、条件查询格式：SELECT…FROM…WHERE…Eg1：查询成绩大于80分的学号。Eg2：从score表中检索出选修了课程编号为“02”的，并且成绩大于80分的成绩信息。selectdistinct学号fromscore;where成绩>80select*fromscore;where课程编号="02"and成绩>802、条件查询selectdistinct学号fromEg3：检索出选修了课程编号为“02”或“05”的，并且成绩大于80分的成绩信息。select*fromscore;where课程编号="02"or课程编号="05")and成绩>70Eg3：检索出选修了课程编号为“02”或“05”的，并且成绩Eg4：从student表中检索出性别为“男”且入学时间为1996年9月7日的学生的学号、姓名、性别、专业和年龄信息。select学号,姓名,性别,专业,;year(date())-year(出生日期)as年龄;fromstudent;where入学时间={^1996-09-07}and性别=“男”Eg4：从student表中检索出性别为“男”且入学时间为12、单表条件查询：

（1）条件中，涉及到字符型字段值，加定界符(“”)（2）多个条件之间用and/or相连（3）一行书写不下，除最后一行，各行结尾加分号（;）注意2、单表条件查询：注3、多表联接查询：格式:SELECT…FROM表1,表2,…；WHERE查询条件and连接条件<表名1>.<公共字段名>=<表名2>.<公共字段名>连接条件3、多表联接查询：<表名1>.<公共字段名>=<表名2>.<Eg1:检索出成绩大于80分的学号、姓名、性别和成绩。

Eg2:检索选修了课程的学生信息。

selectdistinctstudent.*;fromstudent,score;wherestudent.学号=score.学号selectstudent.学号,姓名,性别,成绩;fromstudent,score;where成绩>80andstudent.学号=score.学号selectdistinctstudent.*;selEg3:检索学号为“199648101”的学生姓名以及所选修的课程名称和授课教师姓名。

select姓名,课程名称,教师姓名;fromstudent,course,score,teacher;wherestudent.学号=score.学号and;course.课程编号=score.课程编号and;course.教师编号=teacher.教师编号and;student.学号="1996468101"Eg3:检索学号为“199648101”的学生姓名以及所选修说明：From短语：后面接多个表时，两个表之间用逗号隔开。联接条件：两个表进行联接查询的前提是一定有相同的字段名。联接的方法：表名1.字段名=表名2.字段名如：“student.学号=score.学号”查询条件：成绩>80”为查询条件联接条件和查询条件用AND联接，并且都放在where短语中。Selects查询项，若为多个表的公共字段，则指明来源的表。格式为：表名.字段名作队selectstudent.学号,姓名,性别,成绩;fromstudent,score;where成绩>80andstudent.学号=score.学号说明：selectstudent.学号,姓名,格式：ORDERBY<字段名>[ASC|DESC][,<字段名>]ASC|DESC]……select…from…[where…]orderby字段名1[asc|desc],字段名2[asc|desc]…二、排序查询：格式：ORDERBY<字段名>[ASC|DESC]sEg1：按学生的入学时间升序检索出全部学生信息。Eg2：检索学生信息，按学生的入学时间升序排序，如果入学时间相同则按学生的出生日期降序进行排序。

select*fromstudentorderby入学时间select*fromstudent;orderby入学时间asc,出生日期descEg1：按学生的入学时间升序检索出全部学生信息。select1）orderby对最终结果进行排序，位置在最后2）asc/desc在排序的字段名后，默认为升序3）按多列排序时，各字段名之间用“，”相隔注意1）orderby对最终结果进行排序，位置在最后注1、确定范围Between…and…2、确定集合in3、部分匹配查询like字符串匹配运算4、不等于!=用在where子句中三、带特殊运算符的条件查询1、确定范围用在where子句中三、带特殊运算符的条件查询1、确定范围格式：BETWEEN……AND……

表示在…和…之间包含界值NOTBETWEEN……AND……

表示不在…和…之间注意1、确定范围注1、确定范围Eg1：查询入学时间在1997年1月1日至1999年12月30日之间的学生的信息。select*fromstudentwhere;入学时间between{^1997-01-01}and{^1999-12-30}select*fromstudentwhere;入学时间>={^1997-01-01}and入学时间<={^1999-12-30}1、确定范围select*fromstudent2、确定集合格式：字段名[NOT]IN(表达式1,表达式2,…)Eg1：查询选修了课程编号为“02”或“05”或“04”，并且成绩在80分以上的学生的学号、课程编号和成绩。select学号,课程编号,成绩;fromscore;where课程编号in("02","04","05")and成绩>=80

2、确定集合select学号,课程编号,成绩;Eg2：查询没有选修课程编号为“04”或“03”并且成绩不及格的学生的学号、课程编号和成绩。

select*fromscore;where课程编号notin("04","03")and成绩<60

Eg2：查询没有选修课程编号为“04”或“03”并且成绩不及3、部分匹配查询格式：字段名like字符串常量通配符：%：0个或多个字符_：一个字符3、部分匹配查询Eg1：查询所有姓“李”的学生的学号、姓名、性别、专业。Eg2：查询第二个汉字是“海”的学生的学号、姓名、性别、专业。

select学号,姓名,性别,专业fromstudent;where姓名like"李%"select学号,姓名,性别,专业fromstudent;where姓名like"_海%"Eg1：查询所有姓“李”的学生的学号、姓名、性别、专业。se4、不等于（!=）Eg1:查询家庭住址在哈尔滨并且不是学计算机科学与技术专业的学生的信息。

select*fromstudent;where家庭住址="哈尔滨"and专业!="计算机科学与技术"4、不等于（!=）select*fromstudent1、简单的计算查询(用在select中，对查询结果中数值型字段值进行计算)注:

as新字段名：给结果指定字段别名（1）count(distinct字段名)：统计数目count(＊):统计元组个数（2）sum(字段名)：求总和（3）avg(字段名)：求平均值（4）max(字段名)：求最大值（5）min(字段名)：求最小值四、计算与分组查询1、简单的计算查询(用在select中，对查询结果中数值型字Eg1：统计授课教师的数目。Eg2：统计student表中有多少个学生记录。selectcount(distinct教师编号)as人数fromcourseselectcount(*)as人数fromstudentselectcount(distinct教师编号)asEg3：求学号为1996468001的学生平均成绩。Eg4：求score表中的最高分和最低分。selectmax(成绩)as最高分,min(成绩)as最低分;fromscoreselectavg(成绩)as平均分fromscore；where学号="1996468001"Eg3：求学号为1996468001的学生平均成绩。sele①用在select后②count(*)：计算关系中所含元组个数③as新字段名：给计算结果指定字段名注意①用在select后注2、分组与计算查询格式：GROUPBY<字段名>[HAVING<分组条件表达式>]Eg1：检索出每个学生的学号、总分、平均分、最高分和最低分。select学号,sum(成绩)as总分,;avg(成绩)as平均分,;max(成绩)as最高分,;min(成绩)as最低分;fromscore；groupby学号2、分组与计算查询select学号,sum(成绩)as学号课程编号成绩XS0401C00296XS0402C00884XS0401C00367XS0403C01055XS0402C00782XS0402C00365XS0402C00989XS0401C00775学号课程编号成绩XS0401C00296XS0401C00367XS0401C00775XS0402C00884XS0402C00782XS0402C00365XS0402C00989XS0403C01055学号总分平均分最高分最低分XS040116381.29667XS0402320808965XS040355555555学号课程编号成绩XS0401C00296XS0402C008Eg2：检索出“计算机科学与技术系”的每个学生的学号、姓名、总分、平均分、最高分和最低分。select

student.学号,姓名,;

sum(成绩)as总分,;

avg(成绩)as平均分,;

max(成绩)as最高分,;

min(成绩)as最低分;from

student,score;where

student.学号=score.学号and;

专业=“计算机科学与技术”；groupby

student.学号Eg2：检索出“计算机科学与技术系”的每个学生的学号、姓名、学号课程编号成绩XS0401C00296XS0402C00884XS0401C00367XS0403C01055XS0402C00782XS0402C00365XS0402C00989XS0401C00775Eg3：检索选修课程在3门以上（含3门）的每个学生的学号和平均成绩，并按平均成绩升序排序注意：orderby后只能接字段名，不能接函数select学号,avg(成绩)as平均成绩fromscore;groupby学号havingcount(*)>=3;orderby平均成绩分组条件学号课程编号成绩XS0401C00296XS0402C008学号课程编号成绩XS0401C00296XS0401C00367XS0401C00775XS0402C00884XS0402C00782XS0402C00365XS0402C00989XS0403C01055学号平均分XS040181.2XS040280学号平均分XS040280XS040181.2select学号,avg(成绩)as平均成绩fromscore;groupby学号havingcount(*)>=3;orderby平均成绩学号课程编号成绩XS0401C00296XS0401C003学号课程编号成绩XS0401C00296XS0402C00884XS0401C00367XS0403C01055XS0402C00782XS0402C00365XS0402C00989XS0401C00775Eg4：检索计算机科学与技术系选修课程在3门以上（含3门）的每个学生的学号和平均成绩，并按平均成绩降序排序selectstudent.学号,avg(成绩)as平均成绩fromstudent,score;wherestudent.学号=score.学号and;专业=“计算机科学与技术”;

groupbystudent.学号havingcount(*)>=3;orderby平均成绩desc查询条件分组条件连接条件注意：先用where选择满足条件的元组，然后分组，再计算，最后去掉不满足分组条件的记录学号课程编号成绩XS0401C00296XS0402C008Eg5：检索除“商务英语”专业之外的，选修课程在3门以上（含3门）的每个学生的学号、姓名和平均成绩，并按将结果按平均成绩降序排序，保存到表aa.dbf中selectstudent.学号,姓名,avg(成绩)as平均成绩;fromstudent,score;wherestudent.学号=score.学号and专业!=”商务英语”;groupbystudent.学号havingcount(*)>=3;orderby平均成绩descintotableaaEg5：检索除“商务英语”专业之外的，选修课程在3门以上（含Eg6：求至少有两名学生选修的课程的平均分。select课程编号,avg(成绩)as平均分fromscore;groupby课程编号havingcount(*)>=2Eg6：求至少有两名学生选修的课程的平均分。select课Eg7：检索出平均分在80分以上的每个学生的学号、总分和平均分，并将结果按学号升序排列，保存到表xx.dbf中。select学号,sum(成绩)as总分,;

avg(成绩)as平均分;fromscore;groupby学号havingavg(成绩)>=80;orderby学号intotablexxEg7：检索出平均分在80分以上的每个学生的学号、总分和平均（1）位置：where后，orderby前（2）having子句总是跟在groupby子句之后,不可以单独使用（3）先where,再groupby,然后计算,最后having.注意（1）位置：where后，orderby前注（查询结果出自一个表，条件涉及多个表）格式：

SELECT…FROM表1WHERE字段名in；(SELECT字段名FROM表2WHERE…)五、嵌套查询（查询结果出自一个表，条件涉及多个表）五、嵌套查询Eg1：检索没有授课的教师信息。Eg2:检索选修了课程的学生信息。Eg3:找出没有选课的学生的信息。select*fromteacher;where教师编号notin(select教师编号fromcourse)select*fromstudent;where学号notin(select学号fromscore)select*fromstudent;where学号in(select学号fromscore)Eg1：检索没有授课的教师信息。select*from格式：<字段名>ISNULL<字段名>ISNOTNULL六、利用空值查询格式：六、利用空值查询Eg1：查询出还没有确定联系方式的学生记录。Eg2：查询已经确定了联系方式的学生信息。select*fromstudentwhere联系方式isnullselect*fromstudentwhere联系方式isnotnull不能写成“=NULL”或“!=NULL”

注意Eg1：查询出还没有确定联系方式的学生记录。select*1、显示部分结果2、将结果存放在临时文件中※3、将结果存放在永久表中七、查询中的特殊选项1、显示部分结果七、查询中的特殊选项1、显示部分结果（显示前几项）格式：topn[percent]①位置：紧跟select后②top短语要与orderby同时使用1、显示部分结果（显示前几项）Eg1：显示成绩最低的两名学生成绩信息。Eg2：显示成绩最高的那40%的学生成绩信息select*top2fromscoreorderby成绩ascselect*top40percentfromscoreorderby成绩descselect*top2fromscoreorde2、将结果存放在临时文件中格式：intocursor临时文件名注意:（1）文件关闭后，自动删除（2）位置：from短语后或最后2、将结果存放在临时文件中3、将结果存放在临时文件中Eg1：将teacher表中的信息保存在临时文件“教师”中。select*fromteacherintocursor教师3、将结果存放在临时文件中select*fromte4、将结果存放在永久表中

格式：intotable表名注意：（1）查看结果可以使用下面的命令操作use表名browse（2）通过该子句可实现表的复制。

4、将结果存放在永久表中Eg1:将所有课程信息按学分降序保存永久表“课程”中。select*fromcourseorderby学分desc；intotable课程Eg1:将所有课程信息按学分降序保存永久表“课程”中。seSelect…..From表名[where查询条件[and联接条件]][groupby….[having….]][orderby…..][intocursor|intotable]字段名,as…,函数说明查询内容来自哪个些表说明条件按某列分组,分组条件对查询结果排序保存查询结果SQL查询语句的总格式字段名,as…,函数说明查询内容来自哪个些表说数据操作插入记录Insert更新记录Update删除记录Delete4.2数据操作本节主要内容数据操作插入记录Insert更新记录Update删除记录De格式1：insertinto表名;values(表达式1,表达式2,…,表达式n)格式2：insertinto表名(字段名1,字段名2,…);values(表达式1,表达式2,…)一、插入记录格式1：insertinto表名;一、插入记录Eg1：向score表中插入一条记录("1996468006","05",98)Eg2:向score表中插入一条记录("05",98)insertintoscore(课程编号,成绩);values("05",98)insertintoscore;values("1996468006","05",98)insertintoscore(学号,课程编号,成绩);values("1996468006“,"05",98)Eg1：向score表中插入一条记录("1996468006格式：update表名;set字段名1=表达式1，字段名2=表达式2….;[where…]说明：（1）当不使用where时，则更新全部记录二、更新记录格式：二、更新记录eg1:给score表中所有成绩加10分eg2:给score表中所有不及格的成绩加10分updatescoreset成绩=成绩+10updatescoreset成绩=成绩+10;where成绩<60eg1:给score表中所有成绩加10分updatescoEg3:将course表中课程编号为“01”的课程的学分加2分。Eg4:将成绩大于70分的的所有课程的成绩提高10%。updatecourseset学分=学分+2where课程编号="01"updatescoreset成绩=成绩*1.1;Where成绩>70Eg3:将course表中课程编号为“01”的课程的学分加2格式:deletefrom表名[where…]

说明:（1）当不使用where时，逻辑删除表中全部记录（2）物理删除：Pack逻辑删除三、删除记录逻辑删除三、删除记录Eg1：将teacher表中职称为助教的教师信息删除。deletefromteacherwhere职称="助教"Eg1：将teacher表中职称为助教的教师信息删除。deleg2：物理删除student表中年龄为20的学生记录deletefromstudent;whereyear(date())-year(出生日期)=20packeg2：物理删除student表中年龄为20的学生记录del本节主要内容1、定义表2、删除表3、修改表4、定义视图4.3数据定义本节主要内容4.3数据定义格式：createtable|dbf表名(

字段名1类型(宽度[,小数位数])[null|notnull][check规则|[error信息]][default表达式][primarykey|unique],字段名2

…,[foreignkey表达式tag表达式references表名1])一、表的定义格式：一、表的定义Eg1:用SQLCREATE命令建立“仓库”表createtable仓库(;

仓库号C(5)primarykey,;

面积N(20),;

城市C(20))Eg1:用SQLCREATE命令建立“仓库”表Eg2:用SQLCREATE命令建立“职工”表createtable职工(;仓库号C(5),;职工号C(5)primarykey,;工资Icheck(工资>=1000and工资<=5000);error"工资值的范围在1000-5000!";default1200,;foreignkey仓库号tag仓库号references仓库)Eg2:用SQLCREATE命令建立“职工”表格式：droptable表名eg1:删除订货管理数据库中的仓库表droptable仓库一、表的删除格式：一、表的删除格式一：添加新字段altertable表名；add字段名类型(宽度)……

eg1:为订购单表增加一个货币类型的总金额字段altertable订购单;add总金额Ycheck总金额>0;error“总金额应该大于0!”[check规则|[error信息]]