【教学课件】第6章SQL语言的应用.ppt

第6章 SQL语言的应用,任课教师：乔奎贤,E-mail：,本章内容,6.1 SQL语言概述 6.2 数据定义 6.3 数据查询 6.4 数据操纵,6.1 SQL语言概述类型,结构化查询语言SQL：Structured Query LanguageSQL标准：20世纪80年代初由美国国家标准协会（ANSI）制定SQL语言的特点：SQL是一种一体化的语言SQL语言是一种高度非过程化的语言SQL语言非常简洁SQL语言可直接以命令方式交互使用，也可嵌入到程序设计语言中以程序方式使用,6.2 数据定义,6.2.1 建立表结构6.2.2 删除表6.2.3 修改表结构,6.2.1 建立表结构,格式：Create Table|Dbf Name Free(,)Null|Not Null Check Error Default Primary Key|Unique References Tag,)功能：创建表，以指定的字段属性定义表的结构例如：Create Table aa Free(Fd1 C(8),Fd2 L,Fd3 N(4),Fd4 N(7,2),Fd5 D Null),6.2.1 建立表结构,说明：Free：创建自由表 Check Error：完整性检查，检测字段值是否有效Default：指定字段默认值Primary Key：指定字段为关键字段，仅用于数据库表Unique：指定字段为候选关键字段References：为新建表指定永久性父表 Tag：父表中的关联字段,6.2.1 建立表结构,例6.1：创建表“学生”表，它由学号、姓名、性别、年龄、系别五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一Create Table 学生;(学号 char(6)not null unique,;姓名 char(10)unique,;性别 char(2),;年龄 int,;系别 char(20);),6.2.1 建立表结构,例6.2：创建创建“学生管理”数据库，其中包含3个表：学生表、选课表、课程表Create Database 学生管理Create Table 学生(学号 C(6)Primary Key,姓名 C(10),;入学成绩 N(5,1)Check(入学成绩 0)Error 成绩应大于0！)Create Table 课程(课程号 C(5)Primary Key,;课程名 C(20),学分 N(1)Create Table 选课(学号 C(6),课程号 C(5),;成绩 I Check(成绩=0 And 成绩=100);Error 成绩范围0100 Default 60,;Foreign Key 学号 Tag 学号 Reference 学生,;Foreign Key 课程号 Tag 课程号 Reference 课程),6.2.1 建立表结构,使用说明：SQL Create命令不仅可以创建表，还可创建表之间的联系SQL Create命令创建的表自动在最小可用工作区中打开，并可通过别名引用，新表以独占方式打开若建立自由表，则某些选项不可用，如Name、Check、Default、Foreign Key、Primary Key、References等,6.2.2 删除表,格式：Drop Table 功能：直接从磁盘上删除所指定的表文件 如果指定的表文件是数据库表且相应的数据库是当前数据库，则从数据库中删除了表；否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示,6.2.3 修改表结构,格式1：Alter Table Add|Alter Column(,)功能：可以添加(Add)新的字段或修改(Alter)已有的字段，其句法基本可与Create Table的句法相对应例6.3：为课程表增加一个整数类型的“学时”字段Open Database 学生管理Alter Table 课程 Add 学时 I Check(学时16);Error 学时应大于16！,6.2.3 修改表结构,格式2：Alter Table Alter Column Null|Not Null Set Default Set Check Error Drop Default Drop Check功能：定义、修改和删除有效性规则以及默认值定义例6.4：删除课程表中的“学时”字段的有效性规则Open Database 学生管理Alter Table 课程 Alter 学时 Drop Check,6.2.3 修改表结构,格式3：Alter Table Drop Column Set Check Error Drop CheckAdd Primary Key Tag For Drop Primary Key Rename Column To 功能：删除指定字段、修改字段名、修改指定表的完整性规则，包括主索引、外关键字、候选索引及表的合法值限定的添加与删除例6.5：删除课程表中的“学时”字段Open Database 学生管理Alter Table 课程 Drop Column 学时,6.3 数据查询,6.3.1 基本查询6.3.2 条件查询6.3.3 嵌套查询6.3.4 多表查询6.3.5 联接查询6.3.6 查询结果处理,6.3 数据查询,数据查询：数据库的核心操作格式：Select All|Distinct 字段选择 From子句Join子句 Where子句 Group子句 Order子句功能：从指定表或视图中，创建一个由指定范围内、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集说明：命令基本形式：Select From Where命令可从数据库中查询出各种数据 All：查询结果是表的全部记录 Distinct：查询结果是不包含重复记录的记录集,6.3.1 基本查询,基本查询：Select All|Distinct 字段选择 From子句字段选择：As,As From子句：From,例6.6：写出对stu表进行如下操作的命令(1)列出全部学生信息Select*From 学生(2)列出全部学生的学号、姓名和入学成绩Select 学号,姓名,入学成绩 From 学生(3)列出全部学生的姓名和年龄，去掉重名Select Distinct 姓名 As 学生名单,Year(Date()-Year(出生日期)As 年龄 From 学生,6.3.1 基本查询,基本查询：Select All|Distinct 字段选择 From子句：可以是字段、表达式、函数查询计算函数的格式及功能：,6.3.1 基本查询,基本查询：Select All|Distinct 字段选择 From子句例6.7：写出对stu表进行如下操作的命令(1)将所有学生入学成绩四舍五入，仅学号、姓名和入学成绩Select 学号,姓名,Round(入学成绩)As 入学成绩 From 学生(2)求全部学生入学成绩的平均分Select Avg(入学成绩)As 入学成绩平均分 From 学生(3)求入学成绩的最高分和最低分Select Max(入学成绩)As 最高成绩,;Min(入学成绩)As 最低成绩 From 学生,6.3.2 条件查询,Where 子句：用于指定查询条件，其格式是：Where 说明：条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的运算符为：比较运算符：=、!=、=、=、=逻辑运算符：And、Or、Not字符匹配运算符：Not Like确定范围运算符：Not Between And空值运算符：Is Not Null集合从属：In,6.3.2 条件查询,例6.8：写出对stu表进行如下操作的命令查询入学成绩在480分以上的学生记录Select*From 学生 Where 入学成绩480Select*From 学生 Where Not 入学成绩=420 And 入学成绩520)Select*From 学生 Where 入学成绩 Between 420 And 520,6.3.2 条件查询,例6.8：写出对stu表进行如下操作的命令查询女学生入学平均分Select Avg(入学成绩)As 入学平均分 From 学生;Where 性别=女性别!=男 性别 男 Not 性别=男 查询陕西籍学生记录Select*From 学生 Where 籍贯=陕西Select*From 学生 Where 籍贯 Like 陕西%通配符：%表示0个或多个字符表示1个字符,6.3.2 条件查询,例6.8：写出对stu表进行如下操作的命令查询陕西、四川和辽宁籍学生记录Select*From 学生 Where 籍贯=陕西 Or;籍贯=四川 Or 籍贯=辽宁Select*From 学生 Where 籍贯 In(陕西,四川,辽宁)查询所有宿舍电话为空值的学生Select*From 学生 Where 宿舍电话 Is Null注意：成绩 Is Null 不能写成成绩=Null,6.3.3 嵌套查询,嵌套查询：在Select命令的Where子句中包含另一个Select命令分类：单层嵌套查询、多层嵌套查询VFP只支持单层嵌套查询返回单值的子查询：例6.9：查询选修“数据库原理”的所有学生的学号Select 学号 From 选课 Where 课程号=;(Select 课程号 From 课程 Where 课程名=高等数学),6.3.3 嵌套查询,返回一组值的子查询：通常使用运算符Any、All、INAny 运算符例6.10：查询选修“10021”课的学生中成绩比选修“10011”课的最低成绩高的学号的学号和成绩Select 学号,成绩 From 选课 Where 课程号=10021Select 学号,成绩 From 选课 Where 课程号=10011Select 学号,成绩 From 选课 Where 课程号=10021 And;成绩 Any(Select 成绩 From 选课 Where 课程号=10011),6.3.3 嵌套查询,返回一组值的子查询：通常使用运算符Any、All、INAll 运算符例6.11：查询选修“10021”课的学生中成绩比选修“10011”课的最高成绩高的学号的学号和成绩Select 学号,成绩 From 选课 Where 课程号=10021Select 学号,成绩 From 选课 Where 课程号=10011Select 学号,成绩 From 选课 Where 课程号=10021 And;成绩 All(Select 成绩 From 选课 Where 课程号=10011),6.3.3 嵌套查询,返回一组值的子查询：通常使用运算符Any、All、ININ 运算符：=Any例6.12：查询选修“数据库原理”或“软件工程”课的所有学生学号Select 学号 From 选课 Where 课程号 IN;(Select 课程号 From 课程 Where 课程名=大学英语;Or 课程名=高等数学),6.3.4 多表查询,等值联接：按对应字段的共同值将两个表中的记录相联接例6.13：写出如下操作的命令列出所有教师姓名及所教课程Select a.教师编号,a.姓名 As 教师姓名,b.课程名;From 教师 a,课程 b Where a.课程编号=b.课程号列出教“高等数学”课程的所有教师姓名Select a.教师编号,a.姓名 As 教师姓名,b.课程名;From 教师 a,课程 b;Where a.课程编号=b.课程号 And b.课程名=高等数学,6.3.4 多表查询,等值联接：按对应字段的共同值将两个表中的记录相联接例6.13：写出如下操作的命令列出所有学生的成绩单Select a.学号,姓名,b.课程号,课程名,成绩;From 学生 a,课程 b,选课 c;Where a.学号=c.学号 And b.课程号=c.课程号列出至少选修“10011”和“10021”课的学生学号Select a.学号 From 选课 a,选课 b Where a.学号=b.学号;And b.课程号=10011 And a.课程号=10021,6.3.4 多表查询,非等值联接：例6.14：列出选修“10021”课的学生中，成绩大于学号为“075102”的学生该课程的学生的学号及其成绩Select a.学号,b.课程号,a.成绩 From 选课 a,选课 b;Where a.成绩 b.成绩 And a.课程号=b.课程号 And;b.课程号=10021 And b.学号=075103,6.3.5 联接查询,内部联接(Inner Join)：指包括符合条件的每个表中的记录。也就是说是所有满足联接条件的记录都包含在查询结果中 例6.15：列出少数民族学生的学号、课程号和成绩方法1：Select a.学号,b.课程号,成绩 From 学生 a,选课 b;Where a.学号=b.学号 And 少数民族否方法2：采用内联接方式Select a.学号,b.课程号,成绩 From 学生 a;Inner Join 选课 b On a.学号=b.学号 Where 少数民族否,6.3.5 联接查询,外部联接(Outer Join)：左外联接（Left Outer Join）：左表的的各条记录与右表的所有记录依次比较，若有满足联接条件，则产生一个记录；若都不满足，则产生一个含有NULL值的记录。右外联接（Right Outer Join）：右表的的各条记录与左表的所有记录依次比较，若有满足联接条件，则产生一个记录；若都不满足，则产生一个含有NULL值的记录。完全联接（Full Join），先按右联接比较字段值，然后按左联接比较字段值，重复记录不记入查询结果。,6.3.6 查询结果处理,查询结果排序：Order By子句Order By Asc|Desc,Asc|Desc例6.16：查询学生基本信息，按性别(升)、入学成绩(降)排序Select*From 学生 Order By 性别 Asc,入学成绩 Desc例6.17：查询学生的学号、姓名、性别、课程名和成绩，按课程名(升)、入学成绩(降)排序Select 学生.学号,姓名,课程名,成绩;From 学生 a,选课 b,课程 c;Where a.学号=b.学号 And b.课程号=c.课程号;Order By 课程名 Asc,成绩 Desc,6.3.6 查询结果处理,查询结果重定向：Into/To子句格式：Into|To File Additive|To Printer 说明：Array(数组)、Cursor(临时表)、Dbf|Table(表)临时表：一旦被关闭就被删除To File：将结果输出到指定的文本文件Additive：将结果追加到指定的文本文件尾To Printer：将结果输出到打印机,6.3.6 查询结果处理,例6.18：查询”大学英语”课所有学生成绩（按成绩降序排序），输出重定向Select 姓名,课程名,成绩 From 学生 a,选课 b,课程 c;Where a.学号=b.学号 And b.课程号=c.课程号 And;b.课程名=大学英语 Order By 成绩 Desc To t1.txtSelect 姓名,课程名,成绩 From 学生 a,选课 b,课程 c;Where a.学号=b.学号 And b.课程号=c.课程号 And;b.课程名=大学英语 Order By 成绩 Desc Into Cursor t2Select 姓名,课程名,成绩 From 学生 a,选课 b,课程 c;Where a.学号=b.学号 And b.课程号=c.课程号 And;b.课程名=大学英语 Order By 成绩 Desc Into Table t3,6.3.6 查询结果处理,查询结果合并：Union子句格式：Union All 说明：All：结果全部合并，无此项则重复记录自动取掉合并规则：不能合并子查询的结果两个查询必须有同样的列数两个查询各对应字段的数据类型必须相同仅最后一个查询可用排序，且排序选项必须用数字说明例6.19：查询选修”10011”和”10021”课程的学生的学号Select 学号 From 选课 Where 课程号=10011 Union;Select 学号 From 选课 Where 课程号=10021,6.3.6 查询结果处理,分组统计与筛选：Group By 子句和 Having 子句 Group By子句格式：Group By,功能：将查询结果按指定列进行分组，每组在列上具有相同的值，在分组后还可按一定条件筛选，即Having子句。Having子句格式：Having 功能：与Group By子句连用，用来指定每一分组内应满足的条件说明：在查询中先用Where子句选择记录，在进行分组，最后用Having子句选择记录,6.3.6 查询结果处理,分组统计与筛选：Group By 子句和 Having 子句 例6.20：写出操作命令分别统计男女生人数Select 性别,Count(性别)From 学生 Group By 性别分别统计男女生中少数民族学生人数Select 性别,Count(性别)From 学生 Group By 性别;Where 少数民族否查询成绩平均分大于75分的课程号Select 课程号,Avg(成绩)From 选课;Group By 课程号 Having Avg(成绩)=75,复习题：,查询全体学生的详细信息Select*From 学生查询全体学生的学号与姓名Select 学号,姓名 From 学生查询全体学生的学号、年龄Select 姓名,Year(date()-Year(出生日期)As 年龄 From 学生查询选修了课程的学生学号Select Distinct 学号 From 选课查询学生的总人数Select Count(*)As 学生总人数 From 学生,复习题：,查询选修10021号课程的学生最高分Select Max(成绩)As 最高分 From 选课;Where 课程号=10021 计算10021号课程的学生平均成绩Select Avg(成绩)As 平均分 From 选课;Where 课程号=10021 查询所有少数民族学生姓名Select 姓名 From 学生 Where 少数民族否=.t.查询考试成绩=80的学生的学号，课程名及成绩Select 学号,课程号,成绩 From 选课 Where 成绩=80,复习题：,查询所有年龄为18岁的学生姓名、性别和年龄Select 姓名,性别,Year(date()-Year(出生日期)As 年龄;From 学生 Where Year(date()-Year(出生日期)=18查询所有年龄为18岁的男学生姓名、性别和年龄Select 姓名,性别,Year(date()-Year(出生日期)As 年龄;From 学生 Where Year(date()-Year(出生日期)=18;And 性别=男查询”10021”课成绩不在7089分之间的学生Select*From 选课 Where 课程号=10021;And 成绩 Not Between 70 And 89,复习题：,查询赵姓学生的详细情况Select*From 学生 Where 姓名 Like 赵%查询非陕西籍学生的详细情况Select*From 学生 Where 籍贯 Not Like 陕西%查询名字中第二字为”二”的学生的学号、姓名Select 学号,姓名 From 学生 Where 姓名 Like _二%查询缺少成绩的学生的学号、成绩和相应的课程号Select 学号,课程号,成绩 From 选课 Where 成绩 Is Null,复习题：,查询“高等数学”和“大学英语”课的课程号Select 课程号,课程名 From 课程;Where 课程名=高等数学 Or 课程名=大学英语Select 课程号,课程名 From 课程;Where 课程名 IN(高等数学,大学英语)查询所有有成绩的学生的学号、成绩和相应的课程号Select 学号,课程号,成绩 From 选课;Where 成绩 Is Not Null查询即非陕西籍又非辽宁籍的学生Select 学号,姓名,性别,籍贯 From 学生;Where 籍贯 Not In(陕西,辽宁),复习题：,查询与赵二牛在同一个系学习的学生 确定 赵二牛所在系Select 所在系 From 学生 Where 姓名=赵二牛 查找所有在电子系的学生Select 学号,姓名,性别,所在系 From 学生;Where 所在系=计算机嵌套查询来完成Select 学号,姓名,性别,所在系 From 学生 Where 所在系=;(Select 所在系 From 学生 Where 姓名=赵二牛),复习题：,查询选修了10021号课程的学生的姓名、所在系Select 学号,姓名,所在系 From 学生 Where 学号 In;(Select 学号 From 选课 Where 课程号=10021)查询比学号“075106”选修的10021号课程成绩低的学生Select*From 选课 Where 成绩;(Select 成绩 From 选课;Where 学号=075106 and 课程号=10021),复习题：,查询其他系中比计算机系某一学生入学成绩低的学生Select 姓名,入学成绩,所在系 From 学生 Where;入学成绩 计算机查询其他系中比经济管理系所有学生入学成绩低的学生Select 姓名,入学成绩,所在系 From 学生 Where;入学成绩 经济管理,6.4 数据操纵,6.4.1 插入记录6.4.2 删除记录6.4.3 更新记录,6.4.1 插入记录,格式1：Insert Into(字段名1,)Values(,)功能：在指定表尾添加一条新记录，其值为Values后表达式的值例6-21：将一个新学生记录（学号:075111;姓名:陈东;性别:男;系别:法律系;年龄:19）插入到学生表Insert Into 学生 Values(075111,刘阿宝,男,;1988-5-12,.T.,555,四川省成都市,经济管理)例6-22：插入一条选课记录(学号:075111;课程号:10011)到选课表Insert Into 选课(学号,课程号)Values(075111,10011),6.4.1 插入记录,格式2：Insert Into From Array|From Memvar 功能：在指定的表尾添加一条新记录，其值来自于数组或对应的同名内存变量。例6-23：将数组内容作为一个新记录插入到教师表Dimension a(3)Store 1010 To a(1)Store 李幺妹 To a(2)Store 女 To a(3)Insert Into 教师 From Array a,6.4.2 删除记录,格式：Delete From!Where 功能：从指定表中，根据指定的条件逻辑删除记录说明：Delete语句删除的是表中的数据，而不是表的定义Where子句缺省则删除表中的全部数据例6-24：删除学号为075111的学生记录Delete From 学生 Where 学号=075111例6-25：将所有的学生选课记录Delete From 院系,6.4.3 更新记录,格式：Update!Set=,=Where 功能：用表达式的值更新满足条件的记录的字段值例6-26：将教师编号为1008的教师职称修改为高级工程师Update 教师 Set 职称=高级工程师;Where 教师编号=1008例6-27：将所有教师的年龄增加1岁Update 教师 Set 年龄=年龄+1,本章内容,6.1 SQL语言概述 6.2 数据定义 6.3 数据查询 6.4 数据操纵,第六章结束,