第6章 sql语言及应用

第六章 SQL语言的应用 2 6 1SQL语言概述6 2数据定义6 3数据查询6 4数据操纵 3 1 SQL语言简介SQL StructuredQueryLanguage的缩写 即结构化查询语言 是关系数据库的标准语言 SQL语言的主要特点 SQL语言非常简洁 功能很强 接近英语自然语言 SQL是一种一体化的语言 它包括了数据定义 数据查询 数据操纵和数据控制等方面的功能 它可以完成数据库活动中的全部工作 SQL语言是高度非过程化的语言 用户只需要描述清楚要 做什么 而不必说明 如何 去做 由计算机自动完成全部工作 SQL语言既可以直接以命令方式交互使用 也可以嵌入到程序设计语言中使用 具有强大的查询功能 6 1SQL语言概述 4 格式 CREATETABLE DBF NAME FREE NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG FROMARRAY 6 2数据定义 6 2 1建立表结构 5 参数说明 FREE 指明所创建的表为自由表 NULL NOTNULL 该字段是否允许 空值 其默认值为NULL CHECK 用来检测字段的值是否有效 这是实行数据库的一种完整性检查 ERROR 当完整性检查有错误 即条件表达式的值为假时的提示信息 DEFAULT 为一个字段指定的默认值 PRIMARYKEY 指定该字段为关键字段 它能保证关键字段的唯一性和非空性 非数据库表不能使用该参数 UNIQUE 指定该字段为一个侯选关键字段 注意 指定为关键或侯选关键的字段都不允许出现重复值 这称为对字段值的唯一性约束 REFERENCES 这里指定的表作为新建表的永久性父表 新建表作为子表 TAG 父表中的关联字段 若缺省该参数 则默认父表的主索引字段作为关联字段 FROMARRAY 根据指定数组的内容建立表 数组元素依次是字段名 类型等 6 表6 2数据类型说明 7 操作步骤如下 1 用CREATE命令建立数据库 CREATEDATABASED 学生管理 2 用CREATE命令建立学生表 CREATETABLE学生 学号C 5 PRIMARYKEY 姓名C 8 入学成绩N 5 1 CHECK 入学成绩 0 ERROR 成绩应该大于0 例6 1利SQL命令建立学生管理数据库 其中包含3个表 学生表 选课表和课程表 8 3 建立课程表 CREATETABLE课程 课程号C 5 PRIMARYKEY 课程名C 20 学分N 1 4 建立选课表 CREATETABLE选课 学号C 5 课程号C 5 成绩ICHECK 成绩 0AND成绩 100 ERROR 成绩值的范围0 100 DEFAULT60 FOREIGNKEY学号TAG学号REFERENCES学生 FOREIGNKEY课程号TAG课程号REFERENCES课程 注 FOREIGNKEYTAG FOREIGNKEY表示在该字段上建立外部索引 非主索引 并以此建立与父表的联系 TAG指定该索引的标识 9 注意 用SQLCREATE命令新建的表自动在最小可用工作区打开 并可以通过别名引用 新表的打开方式为独占方式 忽略SETEXCLUSIVE的当前设置 如果建立自由表 当前没有打开的数据库或使用了FREE 则很多选项在命令中不能使用 如NAME CHECK DEFAULT FOREIGNKEY PRIMARYKEY和REFERENCES等 10 删除表的SQL命令是 DROPTABLE 功能 该命令直接从磁盘上删除指定的dbf文件 说明 要删除数据库表时 最好应使所属数据库是当前打开的数据库 否则 虽然从磁盘上删除了dbf文件 但记录在dbc文件中的信息没有删除 以后会出现错误提示 例 删除 学生管理 数据库的课程表OPENDATABASE学生管理DROPTABLE课程 6 2 2删除表 11 修改表结构的命令是ALTERTABLE 该命令有3种格式 格式1 ALTERTABLEADD ALTER COLUMN NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE REFERENCES TAG 6 2 3修改表结构 例6 2 为课程表增加一个整数类型的学时字段 OPENDATABASE学生管理ALTERTABLE课程ALTER课程名 20 ALTERTABLE课程 ADD学时ICHECK 学时 16 ERROR 学时应该大于16 例6 3 将课程表中课程名字段的宽度由原来的10改为20 格式1命令可以修改字段的类型 宽度 有效性规则 错误信息 默认值 定义主关键字和联系等 但是不能修改字段名 不能删除字段 也不能删除已经定义的规则等 12 ALTERTABLE课程ALTER学时DROPCHECK 以上两种格式命令都不能删除字段 也不能更改字段名 所有修改是在字段一级 格式3是在这些方面对前两种格式的补充 格式2 ALTERTABLE ALTER COLUMN NULL NOTNULL SETDEFAULT SETCHECK ERROR DROPDEFAULT DROPCHECK 该格式命令主要用于定义 修改和删除有效性规则以及默认值定义 例6 4 为学生表的外语字段添加有效性规则 ALTERTABLE学生ALTER外语SETCHECK 外语 0AND外语 100 ERROR 外语成绩应在0 100之间 例6 5 删除学时字段的有效性规则 13 格式3 ALTERTABLE DROP COLUMN SETCHECK ERROR DROPCHECK ADDPRIMARYKEYTAG FOR DROPPRIMARYKEY ADDUNIQUE TAG FOR DROPUNIQUETAG ADDFOREIGNKEYTAG FOR REFERENCES TAG DROPFOREIGNKEYTAG SAVE RENAMECOLUMNTO 功能 删除字段 DROP 可以修改字段名 RENAME 可以定义或修改 SETCHECK 删除 DROPCHECK 表一级的有效性规则 可以建立 ADDPRIMARYKEY 或删除 DROPPRIMARYKEY 表的主索引 建立 ADDUNIQUE 或删除 DROPUNIQUE 表的候选索引 以及建立 ADDFOREIGNKEY 或删除 DROPFOREIGNKEY 表之间的联系等 14 ALTERTABLE学生DROPUNIQETAGRAN 例6 6 删除课程表中的学时字段 ALTERTABLE课程DROPCOLUMN学时 例6 7 将学生表中数学字段改为数学成绩字段 ALTERTABLE学生RENAMECOLUMN数学TO数学成绩 例6 8 在学生表中定义学号和姓名为候选索引 ALTERTABLE学生ADDUNIQE学号 姓名TAGRAN 例6 9 删除学生表的候选索引RAN 15 SQLSELECT命令的格式查询功能是SQL语言的核心功能 基本形式 SELECT FROM WHERE 查询块 多个查询块可以嵌套执行 6 3数据查询 16 VFP中SQLSELECT命令的格式如下 SELECT ALL DISTINCT TOP PERCENT 别名 AS列名1 别名 AS列名2 FROM FORCE 数据库名 AS INNER LEFT OUTER RIGHT OUTER FULL OUTER JOIN数据库名 AS ON INTO TOFILE ADDITIVE TOPRINTER PROMPT TOSCREEN PREFERENCE NOCONSOLE PLAIN NOWAIT WHERE AND AND OR AND OR GROUPBY HAVING UNION ALL ORDERBY ASC DESC ASC DESC 17 其中主要短语的含义如下 SELECT短语 说明查询的结果中要包含的数据 用 表示所有字段 否则指定具体字段或表达式 DISTINCT表示结果中不能包括重复值的记录 缺省为ALL TOP PERCENT 用来指定输出的行数或百分比 默认为ALL 使用短语TOP必须要排序 FROM短语 是必须的 用来指定提供数据的一个或多个表名称 多个表时用逗号分隔 数据库名和表名之间用 分隔 WHERE短语 说明查询元组的条件 GROUPBY短语 用于对查询结果进行分组 通常利用它进行分组求和 HAVING短语 必须跟随GROUPBY使用 用于限定分组必须满足的条件 ORDERBY短语 用来对查询的结果进行排序 缺省ORDERBY短语则按升序 18 最简单的查询基于单个表 由SELECT FROM WHERE 短语构成 6 3 1简单查询 例6 10 列出所有学生名单 SELECT FROM学生 是通配符 表示所有字段 例6 11 列出所有学生姓名 去掉重名 SELECTDISTINCT姓名AS学生名单FROM学生 例6 12 查询数学成绩在80 90分之间的学生 SELECT FROM学生WHERE数学 80AND数学 95 例6 13 将所有的学生数学成绩四舍五入 只显示学号 姓名和数学成绩 SELECT学号 姓名 ROUND 数学 0 AS 数学成绩 FROM学生 19 以上两例可见 直接使用VisualFoxPro提供的各种SQL函数在输出时进行计算 便可得到相应的输出结果 例6 14 求出所有学生的数学成绩平均分 最高分 最低分 SELECTAVG 数学 AS 数学成绩平均分 MAX 数学 AS 数学成绩最高分 MIN 数学 AS 数学成绩最低分 FROM学生 20 6 3 2带特殊运算符的条件查询 WHERE子句中的条件表达 除了使用关系比较运算和逻辑运算符号之外 还可以使用特殊运算符号 如IN BETWEEN AND 和LIKE等 例 查询数学成绩在80 90分之间的学生 要查询值在什么范围之内 可使用BETWEEN AND 命令如下 SELECT FROM学生WHERE数学BETWEEN80AND95 等价于 数学 80AND数学 95 例6 16 列出学生学号尾数为 2 的所有学生 注意学号字段的类型为字符型数据 SELECT FROM学生WHERE学号LIKE 4 LIKE 是字符串匹配运算符 通配符 代表0个或多个字符 通配符 代表一个任意字符 例6 18 列出所有姓赵的学生名单 SELECT姓名 学号FROM学生WHERE姓名 赵 21 联接查询是基于多个表的查询 表之间的联系是通过字段值来体现的 这种字段通常称为联接字段 1 等值联接与非等值联接查询当联接运算符为 时 称为等值联接 使用其他运算符 时称为非等值联接 6 3 3简单联接查询 其中 a 学号 b 学号ANDb 课程号 c 课程号 是联接条件注 短语FROM学生a 表示选择学生表 并将学生表的别名设为a 短语SELECTa 学号 表示取学生表的学号字段 说明 当FROM之后有多个关系时 各个关系相互之间肯定有一种联系 否则无法构成检索表达式 单位表和职工表之间是一对多的联系 例6 19 查询所有学生的成绩单 要求给出学号 姓名 课程号 课程名和成绩 SELECTa 学号 a 姓名 b 课程号 c 课程名 b 成绩FROM学生a 选课b 课程cWHEREa 学号 b 学号ANDb 课程号 c 课程号 22 当FORM指定的不同关系中含有相同的属性名时 必须加表名作为属性名的前缀 以区别属性所属的关系 例如 a 学号 b 学号 2 自联接查询一个表与其自已进行联接 称为自身联接 23 有时候一个SELECT命令无法完成查询任务 需要一个子SELECT的结果作为条件语句的条件 即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令 这种查询称为嵌套查询 通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询 把嵌入子查询多于一层的查询称为多层嵌套查询 VisualFoxPro只支持单层嵌套查询 6 3 4嵌套查询 1 返回单值的子查询 SELECT学号FROM选课WHERE课程号 SELECT课程号FROM课程WHERE课程名 数据库原理 例6 22列出选修 数据库原理 的所有学生的学号 24 1 ANY运算符的用法 2 返回一组值的子查询 若某个子查询返回值不止一个 则必须指明在WHERE子句中应怎样使用这些返回值 通常使用条件ANY 或SOME ALL和IN 例6 23列出选修 01101 课的学生中期末成绩比选修 01102 的最低成绩高的学生的学号和成绩 SELECT学号 成绩FROM选课WHERE课程号 01101 AND成绩 ANY SELECT成绩FROM选课WHERE课程号 01102 2 ALL运算符的用法 例6 24列出选修 01101 课的学生 这些学生的成绩比选修 01102 课的最高成绩还要高的学生的学号和成绩 SELECT学号 成绩FROM选课WHERE课程号 01101 AND成绩 ALL SELECT成绩FROM选课WHERE课程号 01102 25 3 IN运算符的用法 例6 25列出选修 数据库原理 或 软件工程 的所有学生的学号 SELECT学号FROM选课WHERE课程号IN SELECT课程号FROM课程WHERE课程名 数据库原理 OR课程名 软件工程 注 IN是属于的意思 等价于 ANY 即等于子查询中任何一个值 26 6 3 5联接查询 VisualFoxPro提供的SELECT命令 在FROM子句中提供一种称之为联接的子句 联接分为内部联接和外部联接 外部联接又分为左外联接 右外联接和全外联接 1 内部联接 InnerJoin 实际上 上面例子全部都是内部联接 所谓内部联接是指包括符合条件的每个表格中的记录 也就是说是所有满足联接条件的记录都包含在查询结果中 例6 26列出少数民族学生的学号 课程号及成绩 SELECTa 学号 b 课程号 b 成绩FROM学生a 选课b WHEREa 学号 b 学号ANDa 少数民族否 如采用内部联接方式 SELECTa 学号 b 课程号 a 成绩FROM学生aINNERJOIN选课b ONa 学号 b 学号WHEREa 少数民族否 所得到的结果完全相同 27 2 外部联接 OuterJoin 1 左外联接 也叫左联接 LeftOuterJoin 其系统执行过程是左表的某条记录与右表的所有记录依次比较 若有满足联接条件的 则产生一个真实值记录 若都不满足 则产生一个含有NULL值的记录 接着 左表的下一记录与右表的所有记录依次比较字段值 重复上述过程 直到左表所有记录都比较完为止 联接结果的记录个数与左表的记录个数一致 2 右外联接 也叫右联接 RightOuterJoin 其系统执行过程是右表的某条记录与左表的所有记录依次比较 若有满足联接条件的 则产生一个真实值记录 若都不满足 则产生一个含有NULL值的记录 接着 右表的下一记录与左表的所有记录依次比较字段值 重复上述过程 直到左表所有记录都比较完为止 联接结果的记录个数与右表的记录个数一致 3 全外联接 也叫完全联接 FullJoin 其系统执行过程是先按右联接比较字段值 然后按左联接比较字段值 重复记录不记入查询结果中 28 6 3 6排序 ORDERBY短语的格式如下 ORDERBY ASC DESC ASC DESC 可以按一列或多列排序 ASC 升序 DESC 降序 默认为ASC 注意 ORDERBY是对最终的查询结果进行排序 嵌套查询中的子查询不能使用该短语 例6 27 按性别顺序列出学生的学号 姓名 性别 课程名及成绩 性别相同的再先按课程名排序 最后按成绩由高到低排序 SELECTa 学号 a 姓名 a 性别 c 课程名 b 成绩FROM 学生a 选课b 课程cWHEREa 学号 b 学号ANDb 课程号 c 课程号ORDERBY性别 课程名 成绩DESC 例6 28对学生表 请输出数学成绩最高的前五名学生的信息 SELECT TOP5FROM学生ORDERBY数学DESC 29 各子句的含义 有如下3种形式 a ARRAY 将查询结果存到指定数组名的内存变量数组中 b CURSOS 将输出结果存到一个临时表 c DBF TABLE 将结果存到一个表 如该表已经打开 则系统自动关闭它 如执行SETSAFETYOFF 则重新打开 没有指定后缀 则默认为 dbf TOFILE ADDITIVE 将结果输出到指定文本文件 ADDITIVEG表示将结果添加到文件后面 TOPRINTER将结果送打印机输出 6 3 7查询结果输出 命令 INTO TOFILE ADDITIVE TOPRINTER 30 SELECTa 学号 a 姓名 a 性别 c 课程名 b 成绩FROM学生a 选课b 课程c WHEREa 学号 b 学号ANDb 课程号 c 课程号ORDERBYa 性别 c 课程名 b 成绩DESCTOFILEtest1 例6 29将例6 27的查询结果保存到test1 txt文本文件中 例6 30查询学生所学课程和成绩 输出学号 姓名 课程名和成绩 并将查询结果存入testtable表中 SELECTa 学号 a 姓名 b 课程号 b 成绩FROM学生a 选课b WHEREa 学号 b 学号INTOCURSORtest SELECTa 学号 a 姓名 b 课程名 a 成绩FROMtesta 课程b WHEREa 课程号 b 课程号INTOTABLEtesttableORDERBYa 学号 31 输出合并是指将两个查询结果进行集合并操作 其子句格式是 UNION ALL 其中ALL表示结果全部合并 若没有ALL 则重复的记录将被自动取掉 合并的规则是 1 不能合并子查询的结果 2 两个SELECT命令必须输出同样的列数 3 两个表各相应列出的数据类型必须相同 数字和字符不能合并 4 仅最后一个中可以用ORDERBY子句 且排序选项必须用数字说明 6 3 8输出合并 UNION 例6 31列出选修 01101 或 01102 课程的所有学生的学号 SELECT学号FROM选课WHERE课程号 01101 UNION SELECT学号FROM选课WHERE课程号 01102 32 查询结果可以分组 其格式是 GROUPBY 其中可以是字段名 SQL函数表达式 也可以是列序号 最左边为1 筛选条件格式是 HAVING HAVING子句与WHERE功能一样 只不过是与GROUPBY子句连用 用来指定每一分组内应满足的条件 6 3 9分组统计 GROUP 与筛选 HAVING 例6 32分别统计男女人数 SELECT性别 COUNT 性别 FROM学生GROUPBY性别 例6 33分别统计男女中少数民族学生人数 SELECT性别 COUNT 性别 FROM学生GROUPBY性别WHERE少数民族否 33 6 3 10空值查询 SQL支持空值 NULL 空值表示尚未确定的数据 判断取值为空的语句格式为 列名ISNULL 列名ISNOTNULL为取值不为空 例6 34查询所有有考试成绩的学号和课程号 SELECT FROM选课WHERE成绩ISNOTNULL 例6 35查询没有参加课程号为 01101 考试的学生的学号 SELECT学号FROM选课WHERE课程号 01101 AND成绩ISNULL 34 VisualFoxPro支持两种SQL插入命令 其格式是 格式1 INSERTINTO 字段名1 VALUES 该命令在指定的表尾添加一条新记录 其值为VALUES后面表达式的值 当需要插入表中所有字段的数据时 表名后面的字段名可以缺省 但插入数据的格式及顺序必须与表的结构完全吻合 若只需要插入表中某些字段的数据 就需要列出插入数据的字段名 当然相应表达式的数据位置应与之对应 6 4SQL的数据修改功