SQL结构化查询语言课件

1、全国计算机等级考试(NCRE)二级 Visual FoxPro 第4讲 关系数据库标准语言SQL第1页，共45页。上节内容回顾表和数据库的基本操作：新建、打开、关闭、删除、修改等creatuse(open)closedelete(recall,pack,zap)modifycopy to *; copy structure to *;list(display) * for *replace * with * for *表记录的基本操作：添加、删除、修改、显示索引：四种类型，建立、打开、关闭、删除; .idx与.cdx数据完整性：字段、域、表之间的参照完整性工作区：命名规则及使用方法第2页，共4

2、5页。主要内容SQL语言的特点组成SQL数据查询SQL格式和简单查询；特殊运算符、分组查询；多表连接查询;SQL数据操作INSERT、UPDATE、DELETESQL数据定义表的创建creat、修改alter和删除dropSQL语句为必考点，考试中分值在20分以上，是重重之重。第3页，共45页。结构化查询语言SQL P109什么是SQL？SQL (Structured Query Language)SQL被作为关系型数据库管理系统的标准语言。1974年由Boyce和Chamberlin提出,1986年10月由美国ANSI公布了最早的SQL标准，1987年6月,ISO也正式采纳它为国际标准, 1

3、989年4月,ISO提出了具有完整性特征的SQL,并称之为SQL89为什么需要学习SQL？目前，绝大多数流行的关系型数据库管理系统，如Oracle, Microsoft SQL Server等都采用了SQL语言标准。较少考点，了解第4页，共45页。结构化查询语言SQL1、SQL语言主要特点：了解即可 P109-1101）一体化的语言：包括了数据定义、数据查询、数据操作和数据控制等方面的功能，它可以独立完成数据库的全部操作。2）非过程化的语言（面向对象的语言，但非完全面向对象）：对应的是过程化语言3）SQL的执行方式多样，既能以交互命令的方式直接使用，也能嵌入到各种高级语言中使用。几乎所有的数据

4、库管理系统或数据库应用开发工具都已将SQL融入自身语言中。4）SQL语言具有完备的查询功能、能对视图进行各种操作、具有良好的可移植性VFP根据本身的特点对标准SQL进行了修改和扩充，增强了数据处理和有关方面的功能。第5页，共45页。SQL语句 P110SQL语言包含4个部分（一体化的语言）：P109 表5-1 （要求掌握相应SQL命令属于哪一类） 数据定义语言(DDL)，例如：建立CREATE、修改 ALTER 、删除DROP等语句。 数据操作语言(DML)，例如：表中，INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 数据查询语言(DQL)，例如：SELECT语句，核心

5、内容，进行查询、排序、汇总和表联接等。 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。（VFP不支持） 在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的。一般考点第6页，共45页。数据查询SQL语言的核心 P110SELECT命令格式：SELECT DISTINCT TOP PERCENT. AS , . AS ,FROM AS ,AS ,|INNER|LEFT|RIGHT|FULL JOIN AS ON INTO TABLE |INTO CURSOR |TO SCREEN|INTO ARRAY |TO FIL

6、E ADDITIVE|TO PRINT WHERE AND GROUP BY HAVING ORDER BY , UNION SELECT 必考点第7页，共45页。数据查询SQL语言的核心 P1101)SELECT：用于说明要查询的数据。 2)FROM：用于说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询。3)WHERE：用于说明查询条件；如果是多表查询还可通过该子句指明表与表之间的联接条件，进行联接。4)GROUP BY：用于对查询进行分组，可利用它进行分组汇总。5)HAVING：跟随在GROUP BY 之后使用，它是用来限定分组，要求只有满足分组条件的记录才能进行分组查询。6)

7、ORDER BY：用于对查询的结果进行排序。7)AS：用于指定查询结果中字段的新名称；AS前的表达式可以是一个字段名、表达式、函数等。8)DISTINCT：用于说明该短语将在查询结果中去掉重复值。9)INTO TABLE：用于说明查询结果保存何处。第8页，共45页。数据查询SQL语言的核心 P110常用的SQL SELECT 语句包含4个部分：SELECT ALL | DISTINCT；FROM ；WHERE AND；GROUP BY HAVING；ORDER BY ASC | DESC说明：SQL查询命令通常都比较长，在分为多行书写或输入时，必须在每一行的末尾（最后一行除外）添加一个续行符号

8、“；”。SELECT-SQL命令可以实现对数据表的选择、投影和联接3种关系操作，SELECT短语对应投影操作，WHERE短语对应选择操作，而FROM短语和WHERE短语配合则可实现多表之间的联接操作。必考点第9页，共45页。SQL简单查询 P1121.单表无条件查询（字段选择，理解as）查看成绩表全部字段 Select * from 成绩表 & *表示全部字段 查看学生表部分字段Select 学号,姓名,系别 from 学生表Select 学号 as编号, 姓名,系别 from 学生表As子句 查询结果字段别名第10页，共45页。SQL简单查询2.单表条件查询(where) 查询会计系的学生姓

9、名Select 姓名 from 学生表 where 系别= 会计 查询会计系王、张姓同学的姓名Select 姓名 from 学生表 where (left(姓名,2)= “王” or left(姓名,2)= 张) and 系别= 会计查询计算机系学生姓氏（有重复）Select left(姓名,2) from 学生表 where 系别= 计算机查询计算机系姓氏（无重复）Select distinct left(姓名,2) from 学生表 where 系别= 计算机Distinct 去掉查询结果中的重复记录第11页，共45页。3.排序查询：Order By 子句 P115-116Order By

10、:查询结果排序按升序（ASC）或降序（DESC）排列格式：Order By 项1ASC|DESC,项2ASC|DESC例:按课程号输出学生的各科成绩，同一门课按成绩降序排列： SELECT * FROM 成绩 ORDER BY 课程号,成绩 DESC不指明则默认为 ASC升序必考点第12页，共45页。列名称SELECT 学号, 姓名, 性别, 系别表名FROM学生表过滤条件WHERE性别 = 女SELECT 学号, 姓名,性别,系别;FROM 学生表; WHERE 性别 = 女;ORDER BY 系别，学号 注意输入格式问题排序条件ORDER BY系别，学号问题：从学生表中，找出女生的学号、姓

11、名、性别、系别字段值，并要求输出的记录按照系别和学号进行按照升序排列第13页，共45页。4.几个特殊运算符查询 P112 表5-3BETWEENAND子句【例】查询成绩7585分的学生课程信息：SELECT * FROM 成绩表 WHERE 成绩 BETWEEN 75 AND 85IN、 NOT IN 子句【例】查询“计算机”、“管理”系的学生信息：SELECT * FROM 学生表 WHERE 系别 IN (计算机,管理)LIKE子句 通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符，类似于普通通配符*。【例】查询姓李的学生信息：SELECT * FROM 学生表 WHE

12、RE 姓名 LIKE 李%第14页，共45页。几个特殊运算符TOP 子句 必须要和Order by一起使用才有效【例】查询学生表部分信息：SELECT TOP 5 * FROM 成绩表 Order by 成绩SELECT TOP 20 Percent * FROM 成绩表 Order by 成绩IS NULL子句【例】查询成绩为空的学生课程信息：SELECT * FROM 成绩表 WHERE 成绩 IS NULL限制返回数据前5行限制返回行百分比第15页，共45页。简单查询小结第16页，共45页。复杂查询1：嵌套查询 P115SELECT命令的WHERE子句中嵌套另一个SELECT 命令(即子

13、查询)，子查询的结果必须是确定的值。例:查询李明所学课程的姓名、课程名和成绩：SELECT 姓名,课程号,成绩 FROM 成绩表 WHERE ；学号=(SELECT 学号 FROM 学生表 WHERE 姓名=“李明)或：SELECT 姓名,课程号,成绩 FROM 学生表,成绩表；Where 学生表.学号=成绩表.学号 and 姓名=李明第17页，共45页。复杂查询2：统计查询计算函数 P116-117函数名功 能COUNT（*） 计算表中元组的个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总和（列必须是数值型）AVG（列名）求某一列值的平均值(列必须是数值型）MAX（列名

14、）求某一列值的最大值MIN（列名） 求某一列值的最小值必考点第18页，共45页。复杂查询2：计算查询例：查询学生表中的计算机系学生数select 系别，count(*) from 学生表；where 系别=计算机select 系别,count(*) as 人数 from 学生表 where 系别=计算机例：查询选修课程号为1001的平均分、最高分数select 课程号，avg(成绩) ，max(成绩) ； from 成绩表 Where 课程号=1001第19页，共45页。分组计算查询 P117GROUP BY 分组列,分组列HAVING 过滤条件 GROUP BY子句可以实现分组查询，通常与计

15、算查询（如计数、求和、求平均值等）结合使用，先按指定的数据项分组，再对各组汇总计算。例：查询各门课程的平均成绩：SELECT 课程号,AVG(成绩) AS 平均成绩 FROM 成绩表 GROUP BY 课程号例：查询系上人数多于或等于2人的系别：SELECT 系别,COUNT(系别) AS 各系人数 FROM 学生表 GROUP BY 系别 HAVING 各系人数=2 第20页，共45页。From短语中为关系定义别名，格式为： ，以后可用别名代替表名。用别名也可实现同一关系与自身的联接，称之为自联接即递归联接。例如:Select s.类名,“先于”,x.类名 from kcm s,kcm x

16、where s.类别=x.xxkSX复杂查询3：自连接查询 P118第21页，共45页。自连接查询例题例：从“成绩表”中找出课程号“1001”的成绩高于课程号“1002”的学生的学号。select 成绩表.学号 as 学号;from 成绩表 as a,成绩表 as b;where a.学号=b.学号;and a.课程号=1001;and b.课程号=1002;and a.成绩=b.成绩不常考点，了解第22页，共45页。复杂查询4：多表查询-简单连接查询 P114多表查询时，表和表之间必须有联接关系，在WHERE子句中可以指定表间的联接关系。格式为：. = .【例】查询“李明”选修的课程号、姓名

17、、成绩。SELECT 姓名,课程号,成绩 FROM 学生表,成绩表；Where 学生表.学号=成绩表.学号 and 姓名=李明第23页，共45页。多表查询：简单连接查询例：查询选修“高等数学”的学生学号、姓名、成绩Select 姓名,课程号,成绩 from 学生表,成绩表,课程表；Where 学生表.学号=成绩表.学号 ； and 课程表.课程号=成绩表.课程号； and 课程名=高等数学注意：Select语句必须输入在命令窗口的同一行，如命令太长需分行输入，必须加续行符 ;（分号）第24页，共45页。复杂查询5：多表查询-超连接查询 P121连接运算：等值连接与自然连接，二者区别（了解）：1

18、. 等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。2. 等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。格式：Select from 表名1 inner|left|right|full join 表名2 on 联接条件 where Inner:普通联接或内部联接，满足条件的纪录才出现在结果中Left:左联接。第一个表中不满足条件的也出现在查询结果中。Right:右联接。第二个表中不满足条件的也出现在查询结果中。Full:全联接。不论是否满足条件，两表记录都在结果中出现。以上不

19、满足条件的对应部分为null。第25页，共45页。xs信息 xs成绩Inner join Left join Right join Full join第26页，共45页。超连接查询例如，查询各门课程的平均成绩，要求输出课程名和成绩信息：SELECT 课程名,AVG(成绩) AS 平均成绩 FROM 课程表 as KC ;INNER JOIN 成绩表 as CJ ON KC.课程号=CJ.课程号 ;GROUP BY CJ.课程号例如，查询刘天海的英语成绩，输出姓名、课程名和成绩信息：SELECT 姓名,课程名,成绩 FROM 学生 XS INNER JOIN 课程 KC INNER JOIN 成

20、绩 CJ ON KC.课程号=CJ.课程号 ON XS.学号=CJ.学号 WHERE 姓名=刘天海 AND 课程名=英语第27页，共45页。复杂查询6：查询去向 P123-124存放到数组使用“into array 数组名” 短语。存放到临时文件中使用“into cursor 临时文件名” 短语。存放到永久表中使用“into DBF|TABLE 表名” 短语。存放到文本文件中使用“to FILE 文件名additive”短语。输出到打印机使用“to printer prompt”短语第28页，共45页。主要内容SQL语言的特点组成SQL数据查询SQL格式和简单查询；特殊运算符、分组查询；多表连

21、接查询;SQL数据操作INSERT、UPDATE、DELETESQL数据定义表的创建、修改和删除常考点第29页，共45页。数据操作INSERT插入 P124格式1：在表的尾部尾添加一条新记录INSERT INTO （字段1,字段2,）VALUES (值1,值2,)说明：插入所有字段值时，可省略例如，向“学生1”表中添加两条记录：INSERT INTO 学生1 VALUES(“01020314”,“张红”,男,;1982/07/01,“计算机”,.T.,“北京”,null)格式2：记录值来自数组或同名的内存变量INSERT INTO FROM ARRAY |FROM EMVAR第30页，共45页

22、。数据操作UPDATE更新 P125更新记录格式：UPDATE SET = WHERE 例如，将“成绩表”中“1001”课程的60分以下的成绩加5分UPDATE 成绩表 SET 成绩=成绩+5;WHERE 课程号=“1001”and 成绩60 类比于表的命令replace * with * for * 第31页，共45页。数据操作DELETE删除 P126删除记录格式：DELETE FROM WHERE 说明：该命令执行的是逻辑删除操作。例如，将“学生表”中所有男生的记录逻辑删除：DELETE FROM 学生表 WHERE 性别=男第32页，共45页。主要内容SQL语言的特点组成SQL数据查询

23、SQL格式和简单查询；特殊运算符、分组查询；多表连接查询;SQL数据操作INSERT、UPDATE、DELETESQL数据定义表的创建、修改和删除第33页，共45页。数据定义 P126标准SQL的数据定义功能非常广泛，包括数据库、表、视图、存储过程、规则及索引的定义等数据定义语言由3个命令组成 定义表：CREATE TABLE 修改表结构：ALTER TABLE 删除表：DROP TABLE常考点第34页，共45页。数据定义Create建表列名称CREATE TABLE 课程表(课程号 课程名 学时 学分 数据类型C(4)C(20)N(3)N(2)空或非空PRIMARY KEY,NOT NUL

24、L,NULL,NULL)Create TableCREATE TABLE 课程表 (课程号C(4)PRIMARY KEY, 课程名 C(20) NOT NULL, 学时 N(3) NULL, 学分 N(2)NULL) 第35页，共45页。数据定义Create建表 P126CREATE TABLE FREE( ( ，小数位 ) NULL|NOT NULL CHECK ERROR DEFAULT PRIMARY KEY|UNIQUE， ) 创建一个由TableName1所标识，且含有指定字段的基本表。注意SQL命令与VFP命令的比较第36页，共45页。数据定义Create建表 P126字段类型：C

25、，N，D，L，M，G，相应的字段宽度见P89Null：字段值允许为空，Not Null：字段值不允许为空FREE：指定创建自由表，当数据库没有打开时，不必指定该项.Reference：关系父表，Tag：父表索引标识CHECK 用于指定字段的有效性规则。ERROR 指定当输入的字段值违反有效性规则时，显示提示信息。DEFAULT 用于设置字段的默认值。PRIMARY KEY 用于设置字段为主索引，一个表中只能有一个主索引，UNIQUE 用于设置侯选索引，不允许出现NULL值，同一个字段不能既是主索引，又是侯选索引，也就是说，PRIMARY KEY和UNIQUE和不能同时出现在一个字段的定义中。第

26、37页，共45页。数据定义Create建表CREATE TABLE 学生表(学号 C(8) PRIMARY KEY,姓名 C(8), ;性别 C(2) DEFAULT 男, 出生日期 D,系别 C(10),贷款否 L,简历 M,照片 G NULL)CREAT TABLE 成绩表(学号 C(8),课程号 C(3),成绩 N(5,1) CHECK ;(成绩=0 and 成绩=100) ERROR “成绩须在0-100之间”,;FOREIGN KEY 学号 TAG 学号 REFERENCES 学生1,;FOREIGN KEY 课程号 TAG 课程号 REFERENCES 课程1)第38页，共45页。

27、数据定义Create建表掌握：（1）创建自由表与数据库表的区别（2）表名后面跟字段用（），字段间用逗号隔开，各个字段的宽度要在字段名后的（）中说明。宽度固定的字段无需说明，数值型字段需要说明小数位数。（3）每个列后面的完整性约束称为列级完整性约束，它给出对该列数据的完整性约束条件；列级完整性约束有六种。（4）表级完整性约束在所有列定义后给出，它包括四种（主码约束PRIMARY KEY、单值约束UNIQUE、外码约束FOREIGN KEY REFERENCES 和检查约束CHECK）第39页，共45页。数据定义Create建表列级完整性约束对该列数据的完整性约束条件，有六种：1、DEFAULT：

28、默认值约束2、NULL/NOT NULL：空值/非空值约束3、PRIMARY KEY：主码约束4、UNIQUE：单值约束/惟一值约束5、REFERENCES()：外码约束6、CHECK()：检查约束当一个列规定为主码时，就隐含规定该列为非空和单值。当一个列虽然没有规定为主码，但若同时规定为非空和单值，则它是一个候选码。第40页，共45页。数据定义ALTER表修改 P129添加或修改字段ALTER TABLE ADD|ALTER (宽度,小数位数）CHECK ERROR PRIMARY KEY|UNIQUE例如，在“成绩表” 中添加一个字段：平时成绩 N(5)ALTER TABLE 成绩表 ADD 平时成绩 N(5)例如，将平时成绩字段修改为 N(5,1)ALTER TABLE 成绩表 ALTER 平时成绩 N(5,1)第41页，共45页。数据定义ALTER表修改修改或删除默认值、字段有效性规则 ALTER TABLE ALTER SET DEFAULT DROP DEFAULT SET CHECK DROP CHECK例如，删除“成绩表”表中