关系数据库标准语言SQL

1、关系数据库标准语言SQL一、 SQL概述与查询功能1、 SQL概述SQL是Structured Query Language（结构化查询语言）的缩写。可以说查询是SQL语言的重要组成部分，但不是全部，SQL还包括数据定义、数据操纵和数据控制功能等部分。Visual FoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体表现方面也存在一些差异，由于安全控制方面的缺陷，没有提供数据控制功能。2、 查询功能Visual FoxPro的SQL SELECT命令格式如下：SELECTFROMWHEREGROUP BYHAVINGUNIONALLORDER BYSELECT ALL|DIS

2、TINCT<别名>.<选项>AS<显示列名>,<别名>.<选项>AS<显示列名>FROM <数据库名>!<表名>AS<本地别名>INNER|LEFT|OUTER|RIGHTOUTER|FULLOUTERJION<数据库名>!<表名>AS<本地别名>ON<连接条件>INTO<目标>|TO FILE<文件名>ADDITIVE|TO PRINTER PROMPT|TO SCREENPREFERENCE<参照名>

3、NOCONSOLEPLAINNOWAITWHERE<连接条件1>AND <连接条件2>AND|OR <过滤条件1>AND|OR <过滤条件2>GROUP BY <分组列名1>,<分组列名2>HAVING<过滤条件>UNIONALLSELECT命令ORDER BY <排序选项1>ASC|DESC,<排序选项2>ASC|DESCSELECT说明要查询的数据。FROM 说明要查询的数据来自哪个（些）表。WHERE 说明查询条件，即选择元组的条件。GROUP BY 用于对查询结果进行分组，可以利

4、用它进行分类汇总。HAVING 必须跟随GROUP BY使用，客观存它用来限定分组必须满足的条件。ORDER BY 用来对查询的结果进行排序。数据查询语句SQL数据查询命令是SELECT命令。该命令的基本框架是SELECTFROMWHERE，它包含输出字段、数据来源、查询条件等基本子句。在这种固定格式中，可以不用WHERE，但是SELECT和FROM是必备的。基本查询所谓基本查询是指无条件查询，其常用格式是： SELECT ALLDISTINCT别名.选项AS 显示列名,别名.选项AS 显示列名FROM 表名1 别名1,表名2 别名2 其中ALL表示输出所有记录，包括重复记录。DISTINCT

5、表示输出无重复结果的记录。 基本查询示例写出对学生表进行如下操作的命令。(1) 列出全部学生信息。(2) 列出全部学生的姓名和年龄(去掉重名的)。 操作1： SELECT * FROM 学生 操作2： SELECT DISTINCT 姓名 AS 学生名单，YEAR(DATE()-YEAR(出生日期)AS年龄 FROM 学生带条件查询WHERE 子句用于指定查询条件，其格式是：WHERE 条件表达式其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的比较符为：=(等于)、<>、！=,#(不等于)、=(精确等于)、>(大于)、>=(大于等于)、&l

6、t;(小于)、<=(小于等于)。 带条件查询示例写出对学生表进行如下操作的命令。 (1) 列出入学成绩在560分以上的学生记录。 (2) 求出湖南学生入学成绩平均分。 操作1： SELECT * FROM 学生 WHERE 入学成绩560 操作2：SELECT 籍贯,AVG(入学成绩) AS 入学成绩平均分 FROM 学生 WHERE 籍贯=湖南 写出对教学管理数据库进行如下操作的命令。（1） 列出非湖南籍的学生名单（2） 列出江苏籍和贵州籍的学生名单（3） 列出入学成绩在560分到650分之间的学生名单（4） 列出所有姓赵的学生名单（5） 列出所有成绩为空值的学生学号和课程号操作1：S

7、ELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯<>”湖南”或者SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯!=”湖南”或者SELECT 学号,姓名,籍贯 FROM 学生 WHERE NOT(籍贯=”湖南”)操作2:SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯 IN(”江苏”,”贵州”)或者SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯 =”江苏” OR 籍贯=”贵州”操作3:SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 650或者SELECT 学号,

8、姓名,入学成绩 FROM 学生 WHERE 入学成绩>= 560 AND 入学成绩<=650操作4:SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE “赵%”或者SELECT 学号,姓名 FROM 学生 WHERE 姓名=“赵”或者SELECT 学号,姓名 FROM 学生 WHERE AT(“赵”,姓名)=1或者SELECT 学号,姓名 FROM 学生 LEFT(姓名,2)=“赵”操作5:SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL嵌套查询有时候一个SELECT命令无法完成查询任务，而需要一个子SELECT的结果作为查询的条件，即

9、需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。Visual FoxPro只支持单层嵌套查询。1. 返回单值的子查询 例:对教学管理数据库,列出选修”数据库原理”的所有学生的学号SELECT 学号 FROM 选课 WHERE 课程号=( SELECT 课程号 FROM 课程 WHERE 课程名=”数据库原理”)2. 返回一组值的子查询 (1) ANY运算符的用法例:对教学管理数据库,列出选修”W01”课的学生中成绩比选修”K02”的最低成绩高的学生

10、的学号和成绩.SELECT 学号,成绩 FROM 选课 WHERE 课程号=”W01” AND 成绩>ANY(SELECT 成绩 FROM 选课 WHERE 课程号=”K02”)(2) ALL运算符的用法例: 对教学管理数据库,列出选修”W01”课的学生中成绩比选修”K02”的最高成绩还要高的学生的学号和成绩.SELECT 学号,成绩 FROM 选课 WHERE 课程号=”W01” AND 成绩>ALL(SELECT 成绩 FROM 选课 WHERE 课程号=”K02”)(3) IN运算符的用法(等价于”=ANY”)例:对教学管理数据库,列出选修”数据库原理”或”软件工程”的所有学

11、生的学号.SELECT 学号 FROM 选课 WHERE 课程号 IN( SELECT 课程号 FROM 课程 WHERE 课程名=”数据库原理” OR 课程名=”软件工程”)多表查询等值连接等值连接是按对应字段的共同值将一个表中的记录与另一个表中的记录相连接。例:写出对教学管理数据库进行如下操作的命令(1) 输出所有学生的成绩单,要求给出学号,姓名,课程号,课程名和成绩.(2) 列出男生的选课情况,要求列出学号,姓名,课程号,课程名,授课教师和学分数.(3) 列出至少选修”W01”课和”K02”课的学生学号.操作1:SELECT a.学号,姓名,b.课程号,课程名,成绩 FROM 学生 a,

12、选课 b,课程 c WHERE a.学号=b.学号 AND b.课程号=c.课程号操作2:SELECT a.学号,a.姓名 AS 学生姓名,b.课程号,课程名, e.姓名 AS教师姓名,学分 FROM 学生 a,选课 b,课程 c,授课 d,教师 e WHERE a.学号=b.学号 AND b.课程号=c.课程号 AND c.课程号=d.课程号 AND d.教师号=e.教师号 AND 性别=”男”操作3:SELECT a.学号 FROM 选课 a,课程b WHERE a.学号=b.学号 AND b.课程号=”W01” AND a.课程号=”K02”非等值连接非等值连接按对应字段的记录的某种不等

13、值关系进行连接例：对教学管理数据库,列出选修”K02”课的学生中,成绩大于学号为”200109”的学生该门课程成绩的那些学生的学号及其成绩.SELECT a.学号,a.成绩 FROM 选课 a,课程b WHERE a.成绩>b.成绩 AND a.课程号=b.课程号 AND b.课程号=”K02” AND b.学号=”200109”连接查询内部连接(Inner Join) 所谓内部连接是指包括符合条件的每个表中的记录。也就是说是所有满足连接条件的记录都包含在查询结果中。 例:对教学管理数据库,列出少数民族学生的学号,课程号及成绩.SELECT a.学号,b.课程号 FROM 选课 a,课程

14、b WHERE a.学号=b.学号 AND 少数民族否若采用内部连接则为:SELECT a.学号,b.课程号 FROM 选课 a INNER JOIN 课程b ON a.学号=b.学号 WHERE 少数民族否 外部连接左外连接 左外连接也叫左连接(Left Outer Join)，其系统执行过程是将左表的某条记录与右表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。接着，左表的下一记录与右表的所有记录依次比较字段值，重复上述过程，直到左表所有记录都比较完为止。连接结果的记录个数与左表的记录个数一致。 右外连接 右外连接也叫右连接(Rig

15、ht Outer Join)，其系统执行过程是右表的某条记录与左表的所有记录依次比较，若有满足连接条件的，则产生一个真实值记录；若都不满足，则产生一个含有NULL值的记录。接着，右表的下一记录与左表的所有记录依次比较字段值，重复上述过程，直到左表的所有记录都比较完为止。连接结果的记录个数与右表的记录个数一致。 全外连接 全外连接也叫完全连接(Full Join)，其系统执行过程是先按右连接比较字段值，然后按左连接比较字段值，重复记录不记入查询结果中。 查询结果处理排序输出(ORDER BY) 格式 ：ORDER BY 排序选项1ASC DESC,排序选项2ASC DESC 例:对教学管理数据库

16、按性别顺序列出学生的学号,姓名,性别,课程名及成绩,性别相同的先按课程后按成绩同高到低排序.SELECT a.学号,姓名,性别,课程名,成绩 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 AND b.课程号=c.课程号 ORDER BY 性别,课程名,成绩DESC重定向输出(INTO) 格式 ：INTO 目标 TO FILE文件名ADDITIVE TO PRINTER <目标>有三种形式:ARRAY<数组名>:将查询结果存到指定的数组中.CURSOR<临时表>:将查询结果存到一个临时表中,这个表的操作与其它表一样,不同的是,它一旦被

17、关闭就会被删除.DBF<表>|TABLE<表>:将查询结果存到一个表中.例:写出对教学管理数据库进行如下操作的命令.(1) 将上题的结果保存到test1.txt文本文件中.(2) 查询学生所学课程和成绩,输出学号,姓名课程名和成绩,并将查询结果存入testtable表中.操作1:SELECT a.学号,姓名,性别,课程名,成绩 FROM 学生 a,选课 b,课程 c WHERE a.学号=b.学号 AND b.课程号=c.课程号 ORDER BY 性别,课程名,成绩DESC操作2:SELECT a.学号,姓名,b.课程号,成绩 FROM 学生 a,选课 b WHERE

18、a.学号=b.学号 INTO CURSOR testSELECT a.学号,姓名,课程号,成绩 test a,课程 b WHERE a.课程号=b.课程号 INTO TABLE testable ORDER BY a.学号输出合并(UNION) 子句格式 ：UNION ALLSELECT命令其中,ALL表示结果全部合并,若没有ALL,则重复的记录被自动取掉.(1) 不能合并子查询的结果(2) 两个SELECT命令必须输出同样的列数(3) 两个表各相应列出的数据类型必须相同,数字和字符不能合并.(4) 仅最后一个SELECT命令中可以用ORDER BY子句,且排序选项必须用数字说明.例:对教学管

19、理数据库,列出选修”W01”或”K02”课程的所有学生的学号.SELECT 学号 FROM 选课 WHERE 课程号=”W01” UNION SELECT 学号 FROM 选课 WHERE 课程号=”K02”分组统计(GROUP BY)与筛选(HAVING) 格式 ：GROUP BY 分组选项1,分组选项2 格式 ：HAVING 筛选条件表达式例:写出对教学管理数据库进行如下操作的命令(1)分别统计男女生人数(2) 分别统计男女生中少数民族学生的人数(3)列出成绩平均分大于80分的课程号操作1:SELECT 性别,COUNT(性别) FROM 学生 GROUP BY 性别操作2:SELECT

20、性别,COUNT(性别) FROM 学生 GROUP BY 性别 WHERE 少数民族否操作3:SELECT 课程号,AVG(成绩) FROM 选课 GROUP BY 课程号 HAVING AVG(成绩)>=80二、数据定义有关数据定义的SQL命令分为3组，它们是建立(CREATE)数据库对象、修改(ALTER)数据库对象和删除(DROP)数据库对象。每一组命令针对不同的数据库对象(如数据库、查询、视图等)分别有3个命令。建立表结构CREATE TABLEDBF 表名1 NAME 长表名 FREE(字段名1 类型(宽度,小数位数) NULLNOT NULL CHECK 条件表达式1 ERROR 出错显示信息 功能：建立表可以完成用表设计器完成的所有功能。例：建立一个自由表：人事档案(编号，姓名，性别，基本工资，出生年月)，其中允许出生年月为空值。 CREATE TABLE D:人事管理人事档案FREE (编号 C(7),姓名 C(8),性别 C(2),基本工资 N(7，2),出生年月 D NULL) 删除表命令格式：DROP TABLE 表名 功能：DROP TABLE命令直接从磁盘上删除所指定的表文件。如果指定的表文件是数据库中的表并且相