第4章-SQL

1计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】第4章关系数据库标准语言SQL4.1SQL概述SQL(StructuredQueryLanguage):结构化查询语言。一、SQL语言的特点：1、SQL是一种一体化的语言。包括：数据定义：数据查询（数据查询是SQL的核心）数据操纵数据控制（VFP不提供数据控制功能）2、SQL是一种高度非过程化的语言。3、SQL语言非常简洁。4、SQL语言用交互方式和程序方式都可以用。SQL功能命令动词格式数据查询SELECTselect……from……where数据定义CREATE、DROP、ALTERcreatetable……数据操纵INSERT、UPDATE、DELETEinsertinto……数据控制GRANT、REVOKE注：VFP不提供数据控制功能注意：数据定义一般是对数据表操作，数据操纵一般是对表中的记录进行操作4.2查询功能格式：select字段名……&&说明要查询的数据from数据表名（有联系的多个数据表名，有公共域）&&数据来源哪些表on联接的字段名&&多表查询需要建立联接，on和join配合使用where连接条件……and/or条件……&&说明查询条件groupby分组（字段名）having分组条件&&查询结果分组、分组汇总orderby排序字段&&查询结果排序：asc升序；desc降序intotable表名&&查询去向为表intocursor临时表名&&查询去向为临时表intoarray数组名&&查询去向为数组tofile文件名&&查询去向为文本文件toprinter&&查询去向为打印机（即将查询打印出来）特别注意：查询去向为表、临时表和数组时用into，而查询去向为文本文件时用to。查询去向2计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】假设有课程管理数据库有，以下学生、选课、课程三个数据库表：本章的例题都是依据以下三个表完成。学生（学号C3，姓名C8，性别C2，籍贯C10）学号姓名性别籍贯101张三男山东102李小伟女山西103刘余女湖南104蒋小菜男山东105余小波男山西106李斌女湖北选课（学号C2，课程号C2，成绩I）学号课程号成绩10101851010278101039510201751020368103047710305951040267104049210501771050391106017810603691060477课程（课程号C2，课程名C10，任课教师C8）课程号课程名任课教师01计算机基础刘远军02大学语文王丽娜03数理逻辑张才华04美术学王强05音乐鉴赏许三多06世界地理王睿4.2.1简单查询单表查询：Select…from…where…Select后面接要查询输出的字段名，多个用逗号分隔，如果全部选择可以用*代替3计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】From查询的数据表Where查询条件例1：从学生表中查询籍贯是山东的学生的姓名、性别和籍贯，查询结果存放在表table01中。Select姓名,性别,籍贯from学生where籍贯=”山东”intotabletable01例2：从学生表中查询籍贯是山东并且性别是男的学生的所有信息，结果存放在临时表lsb中。Select*from学生where籍贯=”山东”and性别=”男”intocursorlsb注意：WHERE后面的条件可以是任意复杂的条件表达式。And或or连接。例3：从选课表中查询所有的学号（不显示重复记录），结果存放在数组a中。Selectdistinct学号from选课intoarraya注意：DISTINCT短语消除结果中重复值4.2.2简单的连接查询基于多个数据表（两个或三个），这些数据表肯定有联系（有连接字段），而且查询输出的字段可能在多个不同的数据表中。例4：查询男生的的学号，姓名，成绩分析：学号和姓名在学生表中，而成绩在选课表中。同时注意在两个表中都有学号字段，该字段也就是两个表的联接字段。Select学生.学号,姓名,成绩from学生,选课;Where学生.学号=选课.学号and性别=”男”注意：1、“；”是续行符号（写续行符之前最好先敲一个空格）2、学号是同名字段（连接条件），因此前面必须加表名，指明是哪个数据表的字段Where学生.学号=选课.学号：这两个数据表的学号字段有公共域（相同值域）例5：查询男生的学号，姓名，课程名，任课教师，成绩，结果输出到表table02中。分析：学号和姓名属于学生表；课程名和任课教师属于课程表；成绩属于选课表。学生表和选课表有公共字段“学号”，所以将学号作为这两个表的联接条件；选课表和课程表有公共字段“课程号”，所以将课程号作为这两个表的联接条件。Select学生.学号,姓名,课程名,任课教师,成绩;from学生,选课,课程where学生.学号=选课.学号;and选课.课程号=课程.课程号and性别=”男”intotabletable02注意：学号是两个表都有的公共字段，所以要指明学生.学号。Where后面“学生.学号=选课.学号AND选课.课程号=课程.课程号”这句话一定不能少。它表示的是表之间的联接条件。4计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】4.2.3嵌套查询（考试很少考到，但是要求能理解、看懂）查询结果出自一个关系，但是相关条件却涉及多个关系例6：查询至少有一门成绩是80分以上的同学的姓名。分析：从学生表中查询“姓名”，而条件是学生表中的学号，要在选课表中80分以上的成绩对应的学号中。子查询select学号from选课where成绩=80的作用就是在选课表中查询出所有成绩=80分对应的学号。select姓名from学生where学号in(select学号from;选课where成绩=80)分析：为什么不可以写成select姓名from学生,选课where学生.学号=选课.学号and成绩=80×如果这样些，那就成了：查询每一个=80的成绩所对应的姓名，张三有两门课=80,那么就会显示两个张三，如下图（左）所示。而题目的意思是任一个学生，如果有任一门课=80分，就显示该学生的姓名。结果应该如下（右图所示）注意：IN相当于集合运算“属于”，并不是“=”的意思。例7：输出至少有一名学生选修了的课程名称Select课程名from课程where课程号in(select课程号from选课)4.2.4几个特殊运算符1、between……and……在什么范围内（包含两头）（成绩在80-99之间的学生学号）2、like：字符匹配运算符。3、not：不等于，相当于!=特别注意：SQL的通配符与Windows默认的通配符不一样。SQL中，用”%”表示0个或多个字符，用”_”表示一个字符。例8：查询成绩在80-99之间的学生的学号。Selectdistinct学号from选课where成绩between80and99例9：查询姓“张”的同学的姓名。Select姓名from学生where姓名like张%5计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】Select姓名from学生whereleft(姓名,2)=”张”Select姓名from学生wheresubstr(姓名,1,2)=”张”例10：查询至少有一门课不大于70分的学生的姓名select姓名from学生where学号in(select学号from;选课wherenot成绩=70)4.2.5排序短语：orderby排序字段1asc|desc,排序字段2asc|desc说明：ASC（升序），DESC（降序）。默认是升序ASC。例11：查询学生的姓名，按姓名降序排序，只显示前面的3条记录。selecttop3姓名from学生orderby姓名descTOPN:前N项,要与ORDERBY短语同时使用才有效TOPNPERCENT:前面百分之几十,其他同TOPN例12：查询籍贯为山东的学生的成绩，输出字段为姓名，籍贯，成绩，先按姓名升序再按成绩降序。select姓名,籍贯,成绩from学生,选课where学生.学号=选课.学号;and籍贯=山东orderby姓名,成绩desc注意：排序字段可以用一个数字代表以该数字为顺序的输出字段。比如说姓名是第1列，成绩是第3列，所以上面的命令也可以写成select姓名,籍贯,成绩from学生,选课where学生.学号=选课.学号;and籍贯=山东orderby1,3desc4.2.6简单计算注意：以下函数计算都是纵向，对表的某一个字段的值的进行计算1、COUNT（计数）——可以用COUNT(*)，或任意某个具体字段。例13：查询男生数量：selectcount(*)as男生数量from学生where性别=男例14：在选课表中统计选课的学生人数（选修多门功课，只计算一次）selectcount(*)as选课人数from学生where;学号in(select学号from选课)2、SUM（求和）——可以对N,I,Y类型的数据求和。例15：计算张三的所有选修课的总成绩，输出姓名和总成绩字段。select姓名,sum(成绩)as总成绩from学生,选课where;6计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】学生.学号=选课.学号and姓名=张三3、AVG（计算平均值）——可以对N,I,Y类型的数据求平均值。例16：计算张三的所有选修课的平均成绩，输出姓名和平均成绩字段。select姓名,avg(成绩)as平均成绩from学生,选课where;学生.学号=选课.学号and姓名=张三4、MAX（字段名）例17：计算张三的所有选修课的最高成绩，输出姓名和最高成绩字段。select姓名,max(成绩)as最高成绩from学生,选课where;学生.学号=选课.学号and姓名=张三5、MIN（字段名）例18：计算张三的所有选修课的最低成绩，输出姓名和最低成绩字段。select姓名,min(成绩)as最低成绩from学生,选课where;学生.学号=选课.学号and姓名=张三4.2.7分组与计算可以按照某个字段或者字段组合分组，一般用于统计。按某字段分好组后，不同字段值为不同的组，每一组内的统计操作跟其他组互不干涉。例19：显示每个同学的平均成绩，输出字段为学号，平均成绩。select学号,avg(成绩)as平均成绩from选课groupby学号分析：因为每个同学有几门功课成绩，因此对成绩用函数AVG求平均值时要以学号分组，相当于每一个同学是一组，查询结果就是该同学的平均成绩，结果一个同学就只有一条记录如果去掉“groupby学号”语句，那么查询出来的平均分是所有同学的所有课程的平均分。例20：查询男女生的平均成绩，输出性别，平均成绩，查询去向为文本文件temp_1。Select性别,avg(成绩)as平均成绩from选课,学生;where选课.学号=学生.学号groupby性别tofiletemp_1注意：查询去向为文本文件时，语句为tofile文件名，不能够写成intofile×。**在分组的基础上，要对分组条件进一步进行限制后面加HAVING例21：查询平均成绩80分以上（含）的学生的学号和平均成绩。select学号,avg(成绩)as平均成绩from选课;groupby学号having平均成绩=80注意：HAVING一定是跟在GROUPBY字句后面，限定分组条件，(不能单独使用)4.2.8利用空值查询7计算机国二VF培训资料．SQL．刘远军整理看准目标，耐住性子，一步一个脚印走下去。【骆驼的成功哲学】WHERE后面条件要用ISNULL√不能用=NULL×4.2.9别名与自连接查询别名的使用（多表查询比较方便