《第十七章：深入SQL当我们面对一个数据库的问题时,习惯上是先思 考. ..》由会员分享，可在线阅读，更多相关《第十七章：深入SQL当我们面对一个数据库的问题时,习惯上是先思 考. ..（23页珍藏版）》请在金锄头文库上搜索。
1、第十七章: 深入SQL 当我们面对一个数据库的问题时,习惯上是先思考是否可以使用SQL来解决它，若不能解决，才考虑使用ADO因为使用ADO难免要写上几行程序，而SQL则是给数据库的高级指令，通常一两行语句就可以让数据库做很多事。 1用select 完成统计 例:统计score01.mdb成绩单中语文、英文、数学平均分，并显示如下：(ch16ex1.vbp),SQL语句： select avg(语文) as 语文平均分，avg(英文) as 英文平均分， avg(数学) as 数学平均分 from 成绩单 SQL提供的统计函数： 函数 意义 count 统计数据记录条数 sum 计算数据记录的总
2、和 min 求取所有数据记录的最小值 max 求取所有数据记录最大值 avg 计算数据记录的平均值 first 求取所有数据记录第一条 last 求取所有数据记录最后一条,2。Group by 分组统计 按照某一个字段的值进行分组统计，相当于foxpro中分类统计。 例：在stato1.mdb中“销售记录”表中，分别统计每一个销售人员的销售金额。（ch16ex2.vbp) SQL语句： select 销售人员，sum(销售金额) as 销售总金额 from 销售记录 group by 销售人员 3。如何分组取决于希望得到怎样的信息 例：统计每一季度、每一个销售人员的销售总金额。 (ch16ex
3、2.vbp) SQL语句： select 季度，销售人员, sum(销售金额) as 销售总金额 from 销售记录 group by 季度，销售人员,例:统计第四季度各种产品销售总金额,且按产品排序(ch16ex2.vbp) SQL语句: select 产品,sum(销售金额) as 销售总金额 from 销售记录 where 季度=第四季 group by 产品 order by 产品 4. Having 表达式 对分组统计后的结果进行筛选必须使用“having 表达式”子句 而不是“where 表达式”子句。 例：显示每一个销售人员，每一个季度，销售金额大于5000的记录。(ch16ex
4、2.vbp) SQL语句： select 销售人员，季度，sum(销售金额) as 销售总金额 from 销售记录 group by 销售人员，季度 having sum(销售金额）5000,例：编写一个由用户指定哪一季度，各种产品销售总金额。 （ch16ex3.vbp) SQL 语句： select 产品，sum(销售金额） as 总金额 from 销售记录 where 季度=？ Group by 产品 order by 产品 5. 子集合查询SQL语句： select * from 数据表 where 字段 in 子集合 作用一：跨数据表的查询 例：要在score02.mdb中找出“上学期
5、成绩单”中数学不及格而本学期“成绩单”中数学及格的学生。（ch16ex4.vbp) SQL语句： select * from 成绩单 where 数学=60 and 学号 in (select 学号 from 上学期成绩单 where 数学60),例：编写一个由用户指定哪一季度，各种产品销售总金额。 （ch16ex3.vbp) SQL 语句： select 产品，sum(销售金额） as 总金额 from 销售记录 where 季度=？ Group by 产品 order by 产品 5. 子集合查询SQL语句： select * from 数据表 where 字段 in 子集合 作用一：跨数
6、据表的查询 例：要在score02.mdb中找出“上学期成绩单”中数学不及格而本学期“成绩单”中数学及格的学生。（ch16ex4.vbp) SQL语句： select * from 成绩单 where 数学=60 and 学号 in (select 学号 from 上学期成绩单 where 数学60),6. 建立交叉表 在stat01.mdb中有“销售记录”表如下: 为了更清楚地查阅到“每个销售人员每一季度”的销售状况,我们更喜欢看下面表。下面表的最大特点是把表中的“字段数据”显示在“字段标题”上。此类表称为交叉表。（ch16ex5.vbp) 建立交叉表的步骤： 1）利用group by 建立
7、“分组统计表” select 季度，销售人员，sum(销售金额) as 销售总金额 from 销售记录 group by 季度，销售人员 2）决定哪一个字段的数据要变成标题。 Pivot 季度 并且将这个字段由“select group by ”语句中掉去掉。 3）将统计的函数字段改成transform子句。即： transtorm sum(销售金额) as 销售总金额 而原来select 语句改成:,select 销售人员 from 销售记录 group by 销售人员 4)组合结果 transform子句 select 子句 pivot子句 即: transform sum(销售金额) a
8、s 销售总金额 select 销售人员 from 销售记录 group by 销售人员 pivot 季度 7. Union 联集查询 如果两个select语句所选取的字段均相同,那么sql允许我们将这两个select 语句的执行结果“联集”起来,语句: select 语句1 union select 语句2 例:将score02.mdb中上学期成绩单及成绩单联集起来,并以学号,学期排序(ch16ex6) SQL语句: select *, 1 as 学期 from 上学期成绩单 union select *, 2 as 学期 from 成绩单 order by 学号,学期 8. 动作查询 act
9、ion Query 用作更改、删除或新增数据的SQL语句，称为Action Query (动作查询）,前面我们已介绍利用ADO的recordset对象来进行数据的修改、删除或新增，现有必要介绍具有相同功能的SQL语句吗？ Action Query特别适合于对整批数据的修改。 例：把某一数据表所有数据记录的某一字段值全部加100。 若使用recordset编写程序： rs.movefirst while not rs.eof rs(“字段名”)= rs(“字段名”)+100 rs.movenext wend 但如果使用 action query则只要一行语句 update 数据表名 set 字段
10、名=字段名+100 action query整批更新数据方式比recordset逐条更新要快得多。,1）更新数据 update 数据表 set 字段名1=表达式1，字段名2=表达式2 where 例：将score02.mdb中成绩单数据表中的“数学”字段值全部“开根号乘以10”。（ch16ex7.vbp) SQL语句： update 成绩单 set 数学=数学0.5 * 10 例：将“数学”、“语文”、“英文”、字段值全部“开根号乘以10”。(ch16ex7.vbp) SQL语句： update 成绩单 set 语文=语文0.5 * 10，英文=英文0.5 * 10，数学=数学0.5 * 10
11、,例：仅将所有姓王的数学成绩开根号乘以10。（ch16ex7.vbp) SQL语句： update 成绩单 set 数学=数学0.5 *10 where 姓名 like 王% 例：对上学期数学成绩低于10分的学生，数学加10分。（ch16ex7.vbp) SQL语句： update 成绩单 set 数学=数学+10 where 学号 in (select 学号 from 上学期成绩单 where 数学10 ) 2）删除数据 delete from 数据表 where 表达式 作用：在所指定数据表中，删除符合表达式的数据记录。,例：删除在成绩单中“数学”成绩为0分的数据记录. 。（ch16ex7
12、.vbp) SQL语句： delete from 成绩单 where 数学=0 删除数据表中所有记录： delete from 成绩单 3）新增数据 格式一：insert into 数据表 （字段名1，字段名2，） value （字段值1，字段值2，） 例：要在score02.mdb中“成绩单”数据表中新增一条记录。（ch16ex7.vbp) SQL语句： insert into 成绩单 （学号，姓名，语文） values(850330,“张三”，85）,例：要在score02.mdb中“成绩单”数据表中新增一条记录，且各个字段均有值，数据的次序与数据表中字段 次序一致。 SQL可简写成： i
13、nsert into 成绩单 values (850331,“李四”，80,90,88) 格式二：insert into 数据表1 select from 数据表2 或： insert into 数据表1 （字段名1，字段名2) select 表达式1，表达式2from 数据表 作用：把select 语句所选取的数据记录新增到另一个数据 表1（必须是已存在表） 例：把“成绩单”数据表中所有记录新增到“成绩单复制本”中去。（ch16ex7.vbp) SQL语句： insert into 成绩单复制本 select * from 成绩单,4）建立新的数据表 select 字段名列表 into 新数
14、据表 from 源 数据表 where 注：字段名列表中字段是源数据表中字段，如果与源数据表字段个数顺序一样可用* 代替。 例：从“成绩单”数据表中生成一个三科全及格的数据表，名称为：全及格成绩单。（ch16ex7.vbp) SQL语句： select * into 全及格成绩单 from 成绩单 where 语文=60 and 数学=60 and 英文=60 5）执行Action Query的方法 调用connection对象的execute方法 设conn 为一个connection对象 conn.execute action query语句, 调用command对象的execute方法
15、设cmd为一个command对象 set cmd.activeconnection=conn mandtext=action query 语句 cmd.execute 使用dataenvironment设计器 mand名称 注意：只要command对象内含有数据记录集合，它就 不会重新执行SQL语句。 解决方法：先把数据记录集合关闭，然后执行SQL语句。 If dataenvironment1.rscommand1.stateadstateclose then dataenvironment1.rscommand1.close endif,）执行带有参数的动作查询SQL语句 例: 将“成绩单”
16、中各科成绩加分:(ch16练习12.vbp) SQL语句: update 成绩单 set 语文=语文+?, 英文=英文+?, 数学=数学+? 执行动作查询语句: mand1 参数值1, 参数值2, 参数值3,九、data environment的分组统计 这里的分组统计与前面所介绍的SQL语句中分组统计是 不一回事。 例如：在stat01.mdb “销售记录”表中,如果有一个按照“销 售人员分组统计“销售总金额”的sql语句 seleect 销售人员,sum(销售金额) as 销售总金额 group by 销售人员 结果:,例：使用data environment分组统计，为stat01.mdb编写一个销售统计表（具有每位销售人员销售明细表）(ch16ex11.vbp),例：以“销售记
