VF第6章sql查询语言.ppt

《VF第6章sql查询语言.ppt》由会员分享，可在线阅读，更多相关《VF第6章sql查询语言.ppt（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第六章第六章 SQL查询语言的使用查询语言的使用学习目标：学习目标：（1）了解）了解SQL的基本知识的基本知识（2）掌握）掌握SQL的数据查询操作的数据查询操作（3）熟悉）熟悉SQL的数据定义功能的数据定义功能（4）熟悉）熟悉SQL的数据操纵功能的数据操纵功能6.1 SQL 基础知识基础知识 SQL（Structured Query Language）结构化查）结构化查询语言，是一种介于关系代数与关系运算之间的语询语言，是一种介于关系代数与关系运算之间的语言。言。SQL语言动词语言动词SQL功能功能动词动词数据查询数据查询select数据定义数据定义creat，drop，alter数据操纵数据

2、操纵insert，update，delete数据控制数据控制grant，revoke6.2 SQL 的数据定义功能的数据定义功能一、创建表一、创建表格式格式1：CREATE TABLE FREE(类型类型(长度长度),类型类型(长度长度)功能：创建一个以功能：创建一个以为表的名字，以指定为表的名字，以指定的字段属性定义数据表。的字段属性定义数据表。说明：定义表的各个属性时，需要指明其数据说明：定义表的各个属性时，需要指明其数据类型及长度。类型及长度。例例1：创建新表：创建新表stu2，其结构和学生表相同。，其结构和学生表相同。creat table stu2(学号学号 C(8)，姓名，姓名 C

3、(8)，性别，性别 C(2)，出生日期，出生日期 D，入校总分，入校总分 N(3，0)，三好，三好生生 L，特长，特长 M，照片，照片 G）格式格式2：CREATE TABLE FREE(类型类型(长度长度),类型类型(长度长度)NULL|NOT NULLCHECK ERROR提示信息提示信息 DEFAULTFREE：说明定义的表是自由表。：说明定义的表是自由表。NULL：允许一个字段为空值。如果一个或多个字：允许一个字段为空值。如果一个或多个字段允许包含空值，一个表最多可以定义段允许包含空值，一个表最多可以定义254个字段。个字段。NOT NULL：不允许字段为空值，即字段必须取：不允许字段

4、为空值，即字段必须取一个具体的值。一个具体的值。CHECK：定义字段级的有效性规则。：定义字段级的有效性规则。是逻辑型表达式。是逻辑型表达式。ERROR 提示信息提示信息：定义字段的错误信息。当：定义字段的错误信息。当字段中的数据违背了字段的完整性约束条件时，字段中的数据违背了字段的完整性约束条件时，Visual FoxPro就会显示就会显示“提示信息提示信息”定义的出定义的出错信息。错信息。DEFAULT：定义字段的默认值，：定义字段的默认值，的数据类型必须和字段类型一致。的数据类型必须和字段类型一致。例例2 2：在数据库：在数据库xsgl.DBCxsgl.DBC中，使用命令建立中，使用命令

5、建立“jsj.DBFjsj.DBF”表，表结构包括表，表结构包括(学号学号 C(8),C(8),姓名姓名 C(6),C(6),笔试笔试 N(5,1),N(5,1),上机上机 N(5,1)N(5,1)，并设置学号，并设置学号为主索引，笔试和上机的有效性规则是为主索引，笔试和上机的有效性规则是00，如，如果违反有效性规则，系统提示：果违反有效性规则，系统提示：“成绩必须大成绩必须大于于0 0”。creatcreat database database xsgl.DBCxsgl.DBCcreatcreat table table jsj.DBFjsj.DBF(学号学号 C(8)primary key

6、,C(8)primary key,姓名姓名 C(6),C(6),笔试笔试 N(5,1)check N(5,1)check 笔试笔试0 error 0 error“成绩必须大于成绩必须大于0 0”,上机上机N(5,1)check N(5,1)check 上机上机0 0 error error“成绩必须大于成绩必须大于0 0”)二、修改表的结构二、修改表的结构1、增加字段、增加字段格式：格式：ALTER TABLE ADD(,)；NULL|NOT NULLCHECK ERROR提示信息提示信息；PRIMARY KEY|UNIQUE功能：为指定的表的指定字段进行添加指定的功能：为指定的表的指定字段进

7、行添加指定的字段。字段。例例3 3：为：为xsdbxsdb表添加两个字段：表添加两个字段：“平均分平均分”字段字段N(5,1)N(5,1)和和“总分总分”字段字段N(5,1)N(5,1)。alter table alter table xsdb.dbfxsdb.dbf add add 平均分平均分 N(5,1)N(5,1)check check 平均分平均分80 error 80 error“平均分要大于平均分要大于80!80!”alter table alter table xsdb.dbfxsdb.dbf add add 总分总分N(5,1)N(5,1)注意：注意：check check

8、对非数据库表（对非数据库表（.dbcdbc）不可用。）不可用。2、修改字段、修改字段格式：格式：ALTER TABLE ALTER TABLE ALTER ALTER 类类型型(长度长度)NULL|NOT NULLSET CHECK)NULL|NOT NULLSET CHECK ERROR ERROR提示信息提示信息功能：为指定的表的指定字段进行修改指定的字功能：为指定的表的指定字段进行修改指定的字段。段。例例4 4：在：在xsdbxsdb表中，修改两个字段：表中，修改两个字段：“学号学号”字段字段C C（1010）和）和“英语英语”字段字段 N N（6,16,1）。）。alter table

9、 xsdb.DBF alter 学号学号 C(10)alter table xsdb.DBF alter 英语英语 N(6,1)例例5：修改或定义上机字段的有效性规则。：修改或定义上机字段的有效性规则。alter table jsj.dbf alter 上机上机 set check 上机上机0 error 上机应该大于上机应该大于0!3、删除字段、删除字段ALTER TABLE ALTER TABLE Drop Drop Drop Drop 2;功能：删除指定的表中指定字段。功能：删除指定的表中指定字段。例例6 6：删除：删除xsdbxsdb表中的平均分、奖学金和备注字段。表中的平均分、奖学金

10、和备注字段。alter table alter table xsdb.dbfxsdb.dbf drop drop 平均分平均分 drop drop 奖学金奖学金 drop drop 备注备注4、修改字段名、修改字段名格式：格式：alter table alter table renamecolumnrenamecolumn to to 2功能：将表中功能：将表中 1的名字修改为的名字修改为 2。例例7 7：将：将jsjjsj表的笔试字段名改为笔试成绩。表的笔试字段名改为笔试成绩。alter table alter table jsj.dbfjsj.dbf rename column renam

11、e column 笔试笔试 to to 笔试成绩笔试成绩三、删除表三、删除表格式：格式：DROP TABLEDROP TABLE 功能：功能：从数据库和磁盘上将表直接删除掉。从数据库和磁盘上将表直接删除掉。例例8 8：删除：删除jsjjsj表表drop table drop table jsj.dbfjsj.dbf四、视图的定义和删除四、视图的定义和删除 格式格式:CREATE VIEW AS SELECT-SQL命令命令。功能：按照功能：按照AS子句中的子句中的SELECT-SQL命令提命令提出的查询要求，创建一个本地或远程的出的查询要求，创建一个本地或远程的SQL视图。视图。视图的名称由命

12、令中视图的名称由命令中指定。指定。1、定义视图、定义视图 例例9：从：从“xsgl”数据库所含有的学生和选数据库所含有的学生和选课两个表中抽取学号、姓名、和课程号课两个表中抽取学号、姓名、和课程号3个字段，个字段，组成名称为组成名称为“myview”的的SQL视图。视图。open database xsgl creat view myview as select 学生学生.学学号，学生号，学生.姓名，选课姓名，选课.课程号课程号 from 学生学生.dbf，选课选课.dbf where 学生学生.学号学号=选课选课.学号学号2、查询视图、查询视图 例例10：查询：查询“xsgl”数据库中的数据

13、库中的“myview”视图，要求显示学生姓名和课程号视图，要求显示学生姓名和课程号 open database xsgl select 姓名，课程号姓名，课程号 from myview3、删除视图、删除视图格式：格式：drop view 功能：删除数据库中指定的视图。功能：删除数据库中指定的视图。例例11：删除数据库：删除数据库“xsgl”中的中的“myview”视视图图open database xsgldrop view myview6.3 SQL 的数据查询功能的数据查询功能一、一、SELECTSELECT语句格式语句格式SELECT ALL|DISTINCT SELECT ALL|DI

14、STINCT.ASAS;FROM FROM 联接方式联接方式JOINJOIN表名表名ONON联接条联接条件件;WHERE WHERE;ORDER BY ORDER BY ASC/DESC;ASC/DESC;GROUP BY GROUP BY;HAVINGHAVING分组筛选条件分组筛选条件;INTO INTO;功能：查询。功能：查询。二、投影查询二、投影查询1、查询部分字段、查询部分字段 在在select语句后列出要查询的字段，语句后列出要查询的字段，之间用之间用逗号隔开。逗号隔开。例例1：从学生表中查询学号，姓名，性别和：从学生表中查询学号，姓名，性别和出生日期。出生日期。select 学号

15、，姓名，性别，学号，姓名，性别，出生日期出生日期 from 学生学生2、查询全部字段、查询全部字段 可以在可以在select后面列出全部字段，后面列出全部字段，也可以用也可以用星号星号“*”来表示全部字段。来表示全部字段。例例2：查询教师表中的全部字段：查询教师表中的全部字段 select *from 教师教师 select 教师号，姓名，性别，职称，工资，政教师号，姓名，性别，职称，工资，政府津贴府津贴 from 教师教师 或者或者3、取消重复记录、取消重复记录 使用使用distinct取消查询结果中的重复记录取消查询结果中的重复记录 例例3：查询选课表中有成绩记录的学生学号。：查询选课表中

16、有成绩记录的学生学号。select distinct 学号学号 from 选课选课4、查询经过计算的表达式、查询经过计算的表达式查询的列可以是字段，也可以是计算后的表达式。查询的列可以是字段，也可以是计算后的表达式。例例4：从教师表中查询教师的姓名，工资和涨：从教师表中查询教师的姓名，工资和涨50%以后的工资。以后的工资。select 姓名，工资，工资姓名，工资，工资*1.5 from 教师教师 select 姓名，工资，工资姓名，工资，工资*1.5 as 涨后工资涨后工资from 教师教师 或者或者 as用来修改查询结果中指定列的列名。其中用来修改查询结果中指定列的列名。其中as可以省略可以

17、省略三、条件查询三、条件查询 使用使用where来指定查询条件，常用的比较运算来指定查询条件，常用的比较运算符如表符如表运算符运算符含义含义举例举例=、=、!=、比较大小比较大小入校总分入校总分600not、and 、or多重条件多重条件入校总分入校总分600 and 性别性别=“男男”between and、not between and确定范围确定范围入校总分入校总分 between 500 and 600运算符运算符含义含义举例举例in、not in确定集合确定集合学号学号 in（“s0803001”，“s0803002”）like、not like字符匹配字符匹配姓名姓名 like“王

18、王%”is null、not is null空值查询空值查询学号学号 is null1、简单条件查询、简单条件查询 例例5：从学生表中查询三好生的信息：从学生表中查询三好生的信息 select *from 学生学生 where 三好生三好生=.t.例例6：从学生中查询：从学生中查询7月份所生的学生的学号、月份所生的学生的学号、姓名、性别和出生日期姓名、性别和出生日期 select 学号，姓名，性别，出生日期学号，姓名，性别，出生日期 from 学生学生 where month（出生日期）（出生日期）=7 例例7：从学生表中查询入校总分大于：从学生表中查询入校总分大于600分的分的女生或者入校总

19、分小于女生或者入校总分小于600的男生的学号、姓名的男生的学号、姓名和入校总分。和入校总分。select 学号，姓名，入校总分学号，姓名，入校总分 from 学生学生 where（入校总分（入校总分600 and 性别性别=“女女”）or（入校总分（入校总分1990/01/01 and (入校总分入校总分=600 or 入入校总分校总分80六、查询的排序六、查询的排序 使用使用order by 可以按字段值排序，可以按字段值排序，asc为升为升序，序，desc为降序，默认为升序。为降序，默认为升序。1、单列排序、单列排序 例例21：查询学生表中的学生信息，查询学生表中的学生信息，并按照入并按照

20、入校总分的降序排列。校总分的降序排列。select*from 学生学生 order by 入校总分入校总分 desc 例例22：统计选课表中每门课程的平均成绩，并统计选课表中每门课程的平均成绩，并按照平均分数的降序排列按照平均分数的降序排列 select 课程号，课程号，avg（成绩）（成绩）as 平均成绩平均成绩 from 选课选课 group by 课程号课程号 order by 平均成绩平均成绩 desc select 课程号，课程号，avg（成绩）（成绩）as 平均成绩平均成绩 from 选课选课 group by 课程号课程号 order by 2 desc 或者或者 2表示以查询结

21、果的第表示以查询结果的第2列作为排序依据列作为排序依据2、多列排序、多列排序 例例23：查询学生表中的学生信息，查询学生表中的学生信息，并按照入并按照入校总分的降序排列，若入校总分相同，校总分的降序排列，若入校总分相同，按照学号按照学号的升序排列。的升序排列。select*from 学生学生 order by 入校总分入校总分 desc，学号学号七、内连接查询七、内连接查询 连接条件：两表的公共字段值相等，常用书写连接条件：两表的公共字段值相等，常用书写格式是：表格式是：表1.公共字段公共字段=表表2.公共字段公共字段 例例23：从授课表和课程表中，查询各个教师担从授课表和课程表中，查询各个教

22、师担任的课程的课程号及课程名。任的课程的课程号及课程名。select 授课授课.教师号，授课教师号，授课.课程号，课程课程号，课程.课课程名程名 from 授课授课，课程，课程 where 授课授课.课程号课程号=课课程程.课程号课程号 select 授课授课.教师号，授课教师号，授课.课程号，课程课程号，课程.课课程名程名 from 授课授课 join 课程课程 on 授课授课.课程号课程号=课课程程.课程号课程号 或者或者 例例23：学生表和选课表中查询学号为学生表和选课表中查询学号为s0803004和和s0803005的学生的选课情况，显示其的学生的选课情况，显示其学号、课程号和成绩。学

23、号、课程号和成绩。select 学生学生.学号，选课学号，选课.课程号，选课课程号，选课.成绩成绩 from 学生，选课学生，选课 where 学生学生.学号学号=选课选课.学号学号 and（学生学生.学号学号=“s08030004”or 学生学生.学号学号=“s08030005”）select 学生学生.学号，选课学号，选课.课程号，选课课程号，选课.成绩成绩 from 学生学生 join 选课选课 on 学生学生.学号学号=选课选课.学号学号 where（学生学生.学号学号=“s08030004”or 学生学生.学号学号=“s08030005”）或者或者八、自连接查询八、自连接查询 将同一

24、个表与其自身进行连接，称为自连接。将同一个表与其自身进行连接，称为自连接。在自连接查询涉及的字段前面，用别名加以限制。在自连接查询涉及的字段前面，用别名加以限制。定义表别名的语法是：定义表别名的语法是：例例24：查询入校总分大于谢小芳的学生姓名、查询入校总分大于谢小芳的学生姓名、入校总分。入校总分。select 学生学生.姓名，学生姓名，学生.入校总分入校总分 from 学学生，生，学生学生 b where 学生学生.入校总分入校总分 b.入校总分入校总分 and b.姓名姓名=“谢小芳谢小芳”九、修改查询去向九、修改查询去向1 1、into dbf into dbf into table i

25、nto table 将查询结果保存到以将查询结果保存到以 命名的永久表命名的永久表中，并打开该表作为当前文件中，并打开该表作为当前文件2 2、into cursor into cursor 将查询结果保存到以将查询结果保存到以 命名命名的临时表中，该文件为只读表，当关闭查询相的临时表中，该文件为只读表，当关闭查询相关的表文件时，该临时文件会被自动删除。关的表文件时，该临时文件会被自动删除。3 3、into file into file addtiveaddtive 将查询结果保存到以将查询结果保存到以 命名的文本文命名的文本文件中，件中，扩展名为扩展名为txttxt，如果有，如果有addtiv

26、eaddtive，查询结果追，查询结果追加到文本文件的尾部，否则覆盖该文本文件。加到文本文件的尾部，否则覆盖该文本文件。4 4、into array into array 将查询结果保存到将查询结果保存到 指定的数组中，一般指定的数组中，一般将存放查询结果的数组作为二维数组来使用。将存放查询结果的数组作为二维数组来使用。5 5、to printerto printer 将查询结果输出到打印机。将查询结果输出到打印机。十、嵌套查询十、嵌套查询1 1、带比较运算符的嵌套查询、带比较运算符的嵌套查询 例例25：查询入校总分大于谢小芳的学生姓名、查询入校总分大于谢小芳的学生姓名、入校总分。入校总分。（

27、1）查询谢小芳的入校总分）查询谢小芳的入校总分 select 入校总分入校总分 from 学生学生 where 姓名姓名=“谢小芳谢小芳”此结果的显示为：谢小芳入校总分为此结果的显示为：谢小芳入校总分为610 （2）查询入校总分大于）查询入校总分大于610分的学生姓名、入分的学生姓名、入校总分。校总分。select 姓名，入校总分姓名，入校总分 from 学生学生 where 入校总分入校总分（select 入校总分入校总分 from 学生学生 where 姓名姓名=“谢小芳谢小芳”）（3）将）将610用第（用第（1）步中的）步中的select语句替换语句替换 select 姓名，入校总分姓名

28、，入校总分 from 学生学生 where 入校总分入校总分6102 2、inin谓词嵌套查询谓词嵌套查询 例例26：查询有选课记录的学生信息。查询有选课记录的学生信息。（1）在选课表中查询选了课的学生学号）在选课表中查询选了课的学生学号 select (distinct 学号学号)from 选课选课 查询结果为查询结果为“s0803002”，“s0803003”，“s0803004”，“s0803005”（2）查询学号为）查询学号为“s0803002”，“s0803003”，“s0803004”，“s0803005”的学生信息。的学生信息。select *from 学生学生 where 学号

29、学号 in（“s08030002”，“s08030003”，“s08030004”，“s08030005”）（3）将）将“s0803002”，“s0803003”，“s0803004”，“s0803005”用第（用第（1）步中的）步中的select语句替换语句替换 select *from 学生学生 where 学号学号 in（select distinct 学号学号 from 选课选课）3 3、existsexists谓词嵌套查询谓词嵌套查询 例例27：查询学号为查询学号为s0803002的学生选修的的学生选修的课程名称课程名称 select 课程名课程名 from 课程课程 where e

30、xists（select*from 选课选课 where 选课选课.课程号课程号=课程课程.课程号课程号 and 学号学号=“s0803002”）例例28：查询学号为查询学号为s0803002的学生的学生没有没有选选修的课程名称修的课程名称 select 课程名课程名 from 课程课程 where not exists（select*from 选课选课 where 选课选课.课程号课程号=课程课程.课程号课程号 and 学号学号=“s0803002”）4 4、anyany、allall、somesome谓词嵌套查询谓词嵌套查询例例29：查询入校总分大于某个三好生的女生的信息。查询入校总分大于

31、某个三好生的女生的信息。select*from 学生学生 where 入校总分入校总分any(select 入校总分入校总分 from 学生学生 where 三好生三好生=.t.)and 性别性别=“女女”例例30：查询入校总分大于所有三好生的学生信息。查询入校总分大于所有三好生的学生信息。select*from 学生学生 where 入校总分入校总分all(select 入校总分入校总分 from 学生学生 where 三好生三好生=.t.)此处的此处的any也可以用也可以用some替换替换十一、集合查询十一、集合查询用用union可以进行集合查询可以进行集合查询 例例31：查询入校总分大于

32、查询入校总分大于600分或者小于分或者小于590分的学生姓名。要求使用集合查询。分的学生姓名。要求使用集合查询。select 姓名姓名 from 学生学生 where 入校总分入校总分600 union select 姓名姓名 from 学生学生 where 入校总入校总分分590 注意：使用注意：使用union集合查询时，会自动取消集合查询时，会自动取消重复记录。重复记录。6.4 SQL 的数据操纵功能的数据操纵功能一、插入数据一、插入数据 格式：格式：insert into (,)values(,)例例1、在课程表中插入一条新记录（、在课程表中插入一条新记录（“c170”，“操作系统操作系

33、统”，48）insert into 课程课程 values（“c170”，“操作操作系统系统”，48）二、更新数据二、更新数据 格式：格式：update！set=,=where 功能：用指定的新值更新记录。功能：用指定的新值更新记录。例例2、在课程表中课程号为、在课程表中课程号为“c170”的课时数改的课时数改为为32。update 课程课程 set 课时课时=32 where 课程号课程号=“c170”三、删除数据三、删除数据格式：格式：delete from where 功能：逻辑删除一条或多条记录功能：逻辑删除一条或多条记录说明：没有说明：没有where子句，表示逻辑删除所有记录。子句，表示逻辑删除所有记录。例例3、逻辑删除课程表中课程号为、逻辑删除课程表中课程号为“c170”的记的记录录 delete from 课程课程 where 课程号课程号=“c170”注意：若要物理删除必须使用注意：若要物理删除必须使用pack命令。命令。