《数据库应用基础与实训教程——Visual_FoxPro》第6章：SQL语句

第6章 SQL语句,（时间：8次课，16学时）,第6章 SQL语句,学习目的与要求： SQL是结构化查询语言（Stuctured Query Language）的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。SQL语言拥有使用方式灵活、语句简洁、功能齐全等特点，深受用户欢迎，已成为目前应用最广泛的关系数据库语言，几乎所有的关系型数据库型系统都支持它。 Visual FoxPro并不支持所有的SQL语句，它只支持其中的子集。本章将介绍常用的SQL语句使用，主要包括数据查询、数据定义和数据操作功能。要求熟练掌握这些命令的应用。,第6章 SQL语句,6.1 数据库的操作语句 6.2 表的创建与删除语句 6.3 SQL的数据操作功能 6.4 SQL的数据查询功能 6.5 本章实训 6.6 本章习题,6.1 数据库的操作语句,6.1.1 数据库的创建 6.1.2 数据库的删除,6.1.1 数据库的创建,SQL创建数据库的命令和第三章讲的命令一样，这里只做简单介绍。 【命令格式】 CREATE DATABASE DatabaseName 【命令说明】 DatabaseName指的是所要创建的数据名。 具体应用见【例6-1】。,【例6-1】创建名为CJGL的数据库。命令如下： CREATE DATABASE CJGL &在当前路径下创建了名为CJGL的数据库。 MODIFY DATABASE &打开名为CJGL的数据库设计器，如图6.1所示。,图6.1,6.1.2 数据库的删除,【命令格式】 DELETE DATABASE DatabaseName 【命令说明】 DatabaseName指的是所要删除的数据名。但在删除数据库之前，必须先关闭该数据库。 具体应用见【例6-2】,【例6-2】删除名为CJGL的数据库。命令如下： CLOSE DATABASE &关闭例6-1所建立的数据库。 DELETE DATABASE CJGL &删除已经关闭的数据库，弹出如图6.2所示，单击【是】按钮就删除了该数据库。,图6.2,6.2 表的创建和删除语句,6.2.1 表的创建 6.2.2 表结构的修改 6.2.3 表的删除 6.2.4 将表移出数据库 6.2.5 将自由表添加到数据库,6.2.1 表的创建,【一般常用命令格式】 CREATE TABLE|DBF TableName FREE (FieldName1 FieldType (nFieldWidth,nPrecision) NULL|NOT NULL CKECK lExpression1 ERROR cMessagetext1 DEFAULT eExpression1 PRIMARY KEY|UNIQUE ,FieldName2 【命令说明】 （1）TableName指的是所要创建的表名。 （2）FREE指的是要创建一个自由表。 （3）FieldName1指的是新建表的字段名。,（4）FieldType指的是字段的数据类型，数据类型得用英文表示，具体见第三章所介绍的。 （5）nFieldWidth指的是字段的宽度。 （6）nPrecision指的是数值型数据的小数位数。 （7）NULL|NOT NULL指的字段中是否允许保存空值。 （8）CKECK lExpression1 ERROR cMessagetext1指的是验证字段的值是否符合指定的表达式，不符合可以有错误提示信息。 （9）DEFAULT eExpression1指的是给指定字段设置默认值。 （10）PRIMARY KEY|UNIQUE指的是给指定字段设置主索引或唯一索引。,【例6-3】在数据库“成绩管理”中创建数据库表XS。命令如下： OPEN DATABASE 成绩管理 CREATE TABLE XS (XH C(2),XM C(10), XB C(2),CSRQ D, SFTY L,XBBH C(2) &在成绩管理数据库中建立表XS，如图6.3所示。,图6.3,【例6-4】创建数据库表XS1，要求给XB字段定义规则：XB的值为“男”或“女”，出错提示信息为“性别必须为男或者女”；XH为主关键字，不允许为空值；SFTY默认值为.T.。命令如下： CREATE TABLE XS1 (XH C(2) NOT NULL PRIMARY KEY,XM C(10), XB C(2) CHECK XB=”男” OR XB=”女”,CSRQ D, SFTY L DEFAULT .T.,XBBH C(2) 然后查看表XS1的结构，如图6.4所示。,图6.4,6.2.2 表结构的修改,1. 添加字段 【命令格式】 ALTER TABLE TabelName1 ADD COLUNMN FieldName1 FieldType (nFieldWidth,nPrecision) NULL|NOT NULL CHECK lExpression1 ERROR cMessagetext1 DEFAULT eExpression1 PRIMARY KEY|UNIQUE 【命令说明】 这里的参数的含义和创建表的含义一致，参照执行。,【例6-5】为XS表添加NL字段，NL大于零。命令如下： ALTER TABLE XS ADD NL N(2) CHECK NL0 ERROR ”年龄必须大于零” 命令执行后进入表XS的表设计器，如图6.5所示。,图6.5,2. 修改字段 【命令格式】 ALTER TABLE TabelName1 ALTER COLUNMN FieldName1 FieldType (nFieldWidth,nPrecision) NULL|NOT NULL SET CHECK lExpression1 ERROR cMessagetext1 SET DEFAULT eExpression1 DROP CHECK DROP DEFAULT RENAME COLUMN FieldName1 TO FieldName2,【命令说明】 （1）SET CHECK lExpression1 ERROR cMessagetext1指给指定字段增加规则或修改规则。 （2）SET DEFAULT eExpression1指给指定字段增加默认值或修改默认值。 （3）DROP CHECK指删除指定字段的规则。 （4）DROP DEFAULT指删除指定字段的默认值。 （5）RENAME COLUMN FieldName1 TO FieldName2指给字段FieldName1重新命名为FieldName2。 其他的参数的含义和创建表的含义一致，参照执行。,【例6-6】为XS表修改NL字段有效性规则，NL16。命令如下： ATLER TABLE XS ALTER NL N(2) SET CHECK NL16 ERROR ”年龄必须大于16周岁！” 【例6-7】把SP1表中NL字段更改为RXNL。命令如下： ATLER TABLE XS RENAME COLUMN NL TO RXNL 【例6-8】删除XS表RXNL字段的有效性规则。命令如下： ATLER TABLE XS ALTER RXNL DROP CHECK,6.2.3 表的删除,【命令格式】 DROP TABLE TableName 【命令说明】 该命令是直接从磁盘上删除指定的表名的DBF文件。但该表如果是数据库表，就必须先打开该表所在的数据库，然后使用该命令删除表，否则数据库中将还保留有该表的信息，会有错误提示。 【例6-10】删除表SP1。 DROP TABLE SP1,6.2.4 将表移出数据库,【命令格式】 REMOVE TABLE TableName|？DELETE 【命令说明】 （1）该命令是把指定数据库表移出数据库。 （2）？指调出【移去】对话框，从中选择要移去的表。 （3）DELETE指把指定表移出后，并从磁盘上删除。 【例6-11】将表XS移出数据库CJGL，但不删除。命令和操作如下：,【例6-11】将表XS移出数据库CJGL，但不删除。命令和操作如下： OPEN DATABASE CJGL MODIFY DATABASE REMOVE TABLE XS &该命令执行后出现如图6.6所示的对话框。 在图6.6中单击【是】按钮，就把表XS移出数据库CJGL，但并没有删除表XS，数据库设计器如图6.7所示。,图6.6,图6.7,6.2.5 将自由表添加到数据库,【命令格式】 ADD TABLE TableName|？NAME LongTableName 【命令说明】 （1）该命令是把指定自由表添加到指定的数据库中，成为数据库表。 （2）？：指调出【打开】对话框，从中选择要添加的自由表。 （3）NAME LongTableName：指为添加的数据库表指定一个长名。,【例6-12】将表XS添加到数据库CJGL中。命令如下： OPEN DATABASE CJGL MODIFY DATABASE ADD TABLE XS 命令执行后数据库设计器如图6.8所示。,图6.8,6.3 SQL的数据操作功能,6.3.1 数据的插入 6.3.2 数据的更新 6.3.3 数据的删除 SQL的数据操作功能只要是对已有表中数据的操作，主要包括记录的插入、更新和删除三大方面的操作。,从本节开始将要使用“成绩管理”数据库中的表的数据，其具体数据如图6.9所示：,图6.9,6.3.1 数据的插入,Visual FoxPro支持两种SQL数据的插入格式，第一种是标准格式，第二种是特殊格式。一般使用第一种格式。 【第一种命令格式】 INSERT INTO TableName(FieldName1,FieldName2,) VALUES(eExpression1,eExpression2,) 【命令说明】 （1）TableName指所要插入记录的表名。 （2）FieldName1,FieldName2,指插入记录所要用到的字段。该参数可以不选，默认为对所有字段都需要插入值。 （3）eExpression1,eExpression2,指插入记录对应字段的值。 （4）所插入字段的值必须和该对应的字段的数据要求一致，包括数据类型、数据长度的范围、数据小数位数。 （5）所插入的记录字段的值必须和字段的个数保持一致，不能多也不能少，否则会出错。,【例6-13】给课程表插入一条记录。命令如下： INSERT INTO 课程(课程号,课程名称,学分) VALUES(C6,桌面程序设计,5) 由于该命令是对表中所有字段都插入一个值，还可以使用下面命令进行操作： INSERT INTO 课程 VALUES(C6,桌面程序设计,5) 命令执行结果如图6.10所示。,图6.10,【第二种命令格式】 INSERT INTO TableName FROM ARRAY AraayName 【命令说明】 （1）该命令是从指定的数据组中插入数据成为记录。 （2）AraayName指数组名。该数组必须先定义，而且各元素的赋值符合指定表相应字段的定义，这样才可以使用数组插入记录。,【例6-14】给课程表插入一条记录。命令序列如下： DIME X(3) &定义一个一维数组X，有3个元素。 X(1)=C7 X(2)=专业英语 X(3)=3 INSERT INTO 课程 FROM ARRAY X 命令执行结果如图6.11所示。,图6.11,6.3.2 数据的更新,【命令格式】 UPDATE TableName SET ColumnName1=eExpression1,ColumnName2=eExpression2) WHERE Conditon 【命令说明】 （1）ColumnName1、ColumnName2指对所要修改的字段名。 （2）eExpression1、eExpression2指对所要修改字段更新的数据值。可以是常量和表达式。 （3）WHERE Conditon 指对满足条件的数据进行更新。 （4）所更新字段和新的值必须保持一致。,【例6-15】把课程表中政治课程的课程名更改为“思想道德”，学分改为4。命令如下： UPDATE 课程; SET 课程名称=思想道德,学分=4; WHERE 课程名称=政治 命令执行结果如图6.12所示。,图6.12,6.3.3 数据的删除,【命令格式】 DELETE FROM TableName WHERE Conditon 【命令说明】 （1）该命令是对指定表的记录进行逻辑删除，需要物理删除还要借助PACK命令。 （2）WHERE Conditon 指对满足条件的数据进行删除。,【例6-16】删除课程表中课程号为“C6”课程记录。命令如下： DELETE FROM 课程 WHERE 课程号=C6 命令执行结果如图6.13所示。图中的黑色方块表示作了删除标记，但记录并没有真正删除，还保留在表中。如果要去掉删除标记和物理删除该条记录，可以参见第三章所讲解的表数据删除方法。,图6.13,6.4 SQL的数据查询功能,6.4.1 数据库的简单查询 6.4.2 数据库的复杂查询 SQL的数据查询功能是数据库中最常用、最重要的数据操作，它可以在指定的表中查询满足条件的数据，该命令应用广泛，但格式比较复杂。,【命令格式】 SELECT ALL|DISTINCE TOP nExpr PERCENT Alias.Select_Item AS Column_Name, Alias.Select_Item AS Column_Name FROM FORCE DatabaseName!Table AS Local_Alias INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN DatabaseName!Table AS Local_AliasON JoinCondition INTO Destination| TO FILE FileName ADDITIVE | TO PRINTER PROMPT | TO SCREEN PREFERENCE PreferenceName NOCONSOLEPLAINNOWAIT WHERE JoinCondition AND JoinCondition .AND | OR FilterCondition AND | OR FilterCondition . GROUP BY GroupColumn , GroupColumn .HAVING FilterCondition UNION ALL SELECT命令 ORDER BY Order_Item ASC | DESC , Order_Item ASC | DESC . 由于该命令格式比较复杂，下面将分别在各种应用中进行说明和介绍：,6.4.1 数据库的简单查询,数据列查询 条件查询 简单计算查询 分组查询 查询结果排序 查询结果保存,【命令格式】 SELECT ALL|DISTINCE Alias.Select_Item1 AS Column1_Name, Alias.Select_Item2 AS Column2_Name FROM DatabaseName!Table AS Local_Alias 【命令说明】 （1）ALL|DISTINCE：指查询表中所有数据或去掉重复的记录。 （2）Alias.Select_Item1 AS Column1_Name：从指定的表中（可以使用别名）引用相关的字段或着表达式，AS Column1_Name对查询结果的列名重新命名。 （3）FROM DatabaseName!Table AS Local_Alias：指从指定的表（可以是数据库中的表）查询数据，这里可以给表指定一个别名，在所有字句中都可以应用。,1 .数据列查询,（4）该命令是从指定表中查询表中的所有或者部分列的数据。 【例6-17】查询学生表中所有数据。命令如下： SELECT * FROM 学生 &这里*代表所有字段 结果如图6.14所示。 【例6-18】查询学生表的学号和姓名。命令如下： SELECT 学号 AS 学生的编号,姓名 AS XM FROM 学生 结果如图6.15所示。,图6.14,图6.15,【例6-19】查询选课表中有选课的学生的学号。命令如下： SELECT DISTINCT 学号 FROM 选课 结果如图6.16所示。,图6.16,【主要命令格式】 SELECT FROM WHERE Condition 【命令说明】 该命令是查询满足条件的数据，Condition为条件表达式。具体应用如下： （1）使用关系和逻辑表达式的条件查询。 【例6-20】查询成绩不及格的情况。命令如下： SELECT * FROM 选课 WHERE 成绩60 结果如图6.17所示。,2 .条件查询,图6.17,(2) 使用范围查询BETWEENAND或者NOT BETWEEN AND。 【例6-21】查询成绩在80到100之间的情况。命令如下： SELECT * FROM 选课 WHERE 成绩 BETWEEN 80 AND 100 结果如图6.18所示。,图6.18,（3）使用字符匹配查询LIKE或NOT LIKE 。 %：代表任意长度的字符串。 _：代表任意单个字符。 【例6-22】查询所以姓王的学生情况。命令如下： SELECT * FROM 学生 WHERE 姓名 LIKE 王% 结果如图6.19所示。,图6.19,（4）空值查询IS NULL或IS NOT NULL。 【例6-23】查询学生表系部编号不为NULL的情况。命令如下： SELECT * FROM 学生 WHERE 系部编号 IS NOT NULL 结果如图6.20所示。,图6.20,（5）确定集合查询IN或NOT IN。 【例6-24】查询系部编号为”2”或”3”的学生情况。命令如下： SELECT * FROM 学生 WHERE 系部编号 IN (2,3) 结果如图6.21所示。,图6.21,【主要命令格式】 SELECT . FROM WHERE Condition 【命令说明】 （1）SELECT短语后主要书写计算表达式和函数。 （2）计算检索的函数主要有： COUNT(DISTINCT|ALL)*)统计记录个数。 SUM(DISTINCT|ALL)计算一数值列的总和。 AVG(DISTINCT|ALL)计算一数值列的平均值。 MAX(DISTINCT|ALL)求一列的最大值。 MIN(DISTINCT|ALL)求一列的最小值。,3 .简单计算查询,【例6-25】统计学生表中的记录个数。命令如下： SELECT COUNT(*) AS 学生人数 FROM 学生 结果如图6.22所示。 【例6-26】计算选课表的成绩总和、平均分、最大分、最小分。命令如下： SELECT SUM(成绩) AS 总和,AVG(成绩) AS 平均分,MAX(成绩) AS最大分,MIN(成绩) AS 最小分 FROM 选课 结果如图6.23所示。,图6.22,图6.23,【主要命令格式】 SELECT FROM . GROUP BY GroupColumn , GroupColumn .HAVING FilterCondition 【命令说明】 （1）GroupColumn是指分组的标志列。 （2）HAVING FilterCondition是指限制分组的条件。 【例6-27】分别统计学生表中的男女生人数。命令如下： SELECT 性别,COUNT(*) AS 人数 FROM 学生 GROUP BY 性别 结果如图6.24所示。,4 .分组查询,【例6-28】统计成绩有两门以上在70分的情况。命令如下： SELECT 学号,COUNT(课程号) AS 课程门数 FROM 选课; WHERE 成绩=70; GROUP BY 学号 HAVING COUNT(*)=2 结果如图6.25所示。,图6.24,图6.25,【主要命令格式】、 SELECT TOP nExpr PERCENT FROM . ORDER BY Order_Item ASC | DESC , Order_Item ASC | DESC . 【命令说明】 （1）Order_Item：指的选定的对查询结果排序的列。 （2）ASC | DESC：指的是排序的顺序。ASC指升序，默认为升序。DESC指的是降序。 （3）TOP nExpr PERCENT：指查询结果显示特定数目的记录。TOP nExpr表示现在最前面的N条记录。TOP nExpr PERCENT表示显示查询结果记录数目的百分比。这里必须和排序结合使用，不能单独使用。,5 .查询结果排序,【例6-29】对学生表数据按出生日期的升序降序排序。命令如下： SELECT * FROM 学生; ORDER BY 出生日期 DESC ORDER BY 成绩 DESC &结果如图6.27所示。,图6.26,图6.27,【主要命令格式】 SELECT FROM . INTO TABLE|CURSOR Destination| TO FILE FileName ADDITIVE | TO PRINTER PROMPT | TO SCREEN 【命令说明】 （1）INTO TABLE|CURSOR Destination：指查询结果保存到新表。将结果永久保存在表中则使用INTO TABLE；如果接查询结果保存到临时表中则使用INTO CURSOR，临时表的不永久保存，关闭后不在存在。 （2）TO FILE FileName： ADDITIVE指查询结果保存到文本文件。 （3）TO PRINTER PROMPT：指查询结果输出到打印机。 （4）TO SCREEN：指查询结果到屏幕。,6 .查询结果保存,【例6-31】查询成绩不及格的情况，将结果保存到表BJGQK。命令如下： SELECT * FROM 选课 ; WHERE 成绩60 ; INTO TABLE BJGQK SELECT * FROM BJGQK 表BJGQK的记录数据如图6.28所示。,图6.28,6.4.2 数据的复杂查询,联接查询 子查询,1）自然联接查询 自然联接查询也叫等值联接查询或者内联接查询，一般用于查询结果须从两个表或两个以上表中去获取。 【一般命令格式】 SELECT FROM Table1 INNER JOIN Table2 ON Table1.Column= Table2.Column 或者 SELECT FROM Table1,Table2 WHERE Table1.Column= Table2.Column AND ,1 .联接查询,【命令说明】 （1）命令格式中我们一般使用第二种。指的是从多个表中查询满足条件的数据。要求多个表中互相之间有联系保证有同名字段。 （2）Table1.Column= Table2.Column指两个有联系的表中具有的相同字段名。这是建立联接查询的基本条件。 （3）该命令给多表建立连接后，可以和前面所有讲的简单查询一起结合使用，查询满足条件的数据。 【例6-32】查询学生李小明所选课的情况。命令如下： SELECT A.学号,姓名,课程名称,成绩 FROM 学生 A,选课 B,课程 C; WHERE A.学号=B.学号 AND C.课程号=B.课程号 AND 姓名=”李小明” 结果如图6.29所示。,【例6-33】统计每门课程选课学生的人数。命令如下： SELECT 课程.课程号,课程名称,COUNT(学号) AS 学生人数 FROM 课程,选课; WHERE 课程.课程号=选课.课程号 ; GROUP BY 1 结果如图6.30所示。命令中的1指的是查询结果所包含的第一列，也就是课程.课程号。,图6.29,图6.30,2）左联接查询 【一般命令格式】 SELECT FROM Table1 LEFT JOIN Table2 ON Table1.Column= Table2.Column 【命令说明】 （1）该命令指除满足联接条件的记录出现在查询结果外，第一个表不满足联接条件的记录也出现在查询结果中。 （2）查询结果的记录个数以第一个表的记录个数为准，没有匹配的字段值，自动填写NULL。,【例6-34】为学生和选课表建立左联接。命令如下： SELECT * FROM 学生 LEFT JOIN 选课; ON 学生.学号=选课.学号 部分结果如图6.31所示。,图6.31,3）右联接查询 【一般命令格式】 SELECT FROM Table1 RIGHT JOIN Table2 ON Table1.Column= Table2.Column 【命令说明】 （1）该命令指除满足联接条件的记录出现在查询结果外，第二个表不满足联接条件的记录也出现在查询结果中。 （2）查询结果的记录个数以第二个表的记录个数为准，没有匹配的字段值，自动填写NULL。,【例6-35】为学生和选课表建立右联接。命令如下： SELECT * FROM 学生 RIGHT JOIN 选课; ON 学生.学号=选课.学号 部分结果如图6.32所示。,图6.32,4）完全联接查询 【一般命令格式】 SELECT FROM Table1 FULL JOIN Table2 ON Table1.Column= Table2.Column 【命令说明】 该命令指除满足联接条件的记录出现在查询结果外，两个表不满足联接条件的记录也出现在查询结果中。,【例6-36】为学生和选课表建立完全联接。命令如下： SELECT * FROM 学生 FULL JOIN 选课; ON 学生.学号=选课.学号 部分结果如图6.33所示。,图6.33,2. 子查询,【一般命令格式】 SELECT FROM WHERE （SELECT FROM ） 【命令说明】 （1）子查询适合用于查询结果必须先从一个查询中来得来。 （2）子查询可以使用IN、NOT IN、关系运算符等。 【例6-37】查询没有选课的学生情况。命令如下： SELECT * FROM 学生 WHERE 学号 NOT IN; (SELECT DIST 学号 FROM 选课) 结果如图6.34所示。,【例6-38】统计学生表中的记录个数。命令如下： SELECT 姓名 FROM 学生 WHERE 学号 IN; (SELECT 学号 FROM 选课 GROUP BY 1 HAVING COUNT(课程号)4) 结果如图6.35所示。,图6.34,图6.35,6.5 本 章 实 训,实验目的： 熟悉各语句的命令格式和功能 熟练掌握数据定义语句的使用 熟练掌握数据操作语句对表中数据的灵活操作 重点掌握查询语句的灵活使用,根据下面提供的数据，用SQL完成以下的操作。 1创建数据库TSGL，并打开。建立程序61.prg，并输入以下命令： create database tsgl open database tsgl 2在数据库TSGL中创建以下表。表的结构如下： TS（SSH C(10),SM C(30),ZZ C(6),TSDJH C(10),CBS C(10),JG N(6.2)） JYZ（JSZH C(5),XM C(10),XBM C(8),BJ C(4)） JYDJ（JSZH C(5),TSDJH C(6),JSRQ D） 建立程序62.prg，并输入以下命令： create table ts(ssh c(2),sm c(30),zz c(6),tsdjh c(10),cbs c(10),jg n(6.2) create table jyz(jszh c(5) primary key,xm c(10),xbm c(8),bj c(4) create table jydj(jszh c(5),tsdjh c(6),jsrq d),实验内容：,3给数据库TSGL中所创建表输入数据，具体数据如下： 表TS的数据如下表：,表JYZ的数据如下表：,表jydj的数据如下表：,建立程序63.prg，并输入以下命令： open database tsgl *给表TS插入数据* insert into ts values(“TP311.138“,“Visual FoxPro程序设计教程“,“匡松“,“T00101“,“川大“,29) insert into ts values(“TP311.138“,“Visual FoxPro程序设计教程“,“匡松“,“T00102“,“川大“,29) insert into ts values(“TP311.138“,“数据库应用基础“,“周察金“,“T00201“,“高等“,20.90) insert into ts values(“TP311.138“,“ SQL Server2000实例教程“,“杨学全“,“T00301“,“电子“,25) insert into ts values(“C931.6“,“管理信息系统“,“李永平“,“C00101“,“科学“,18) *代码接下页,*给表JYZ插入数据* insert into jyz values(“01001“,“扬扬“,“电子系“,“01-1“) insert into jyz values(“01201“,“田田“,“计算机系“,“02