04SQL结构化查询语言.pps

全国计算机等级考试(NCRE) 二级 Visual FoxPro,第4讲 关系数据库标准语言SQL,上节内容回顾,表和数据库的基本操作：新建、打开、关闭、删除、修改等creatuse(open)closedelete(recall,pack,zap)modify copy to *; copy structure to *; list(display) * for * replace * with * for * 表记录的基本操作：添加、删除、修改、显示 索引：四种类型，建立、打开、关闭、删除; .idx与.cdx 数据完整性：字段、域、表之间的参照完整性 工作区：命名规则及使用方法,主要内容,SQL语言的特点组成 SQL数据查询 SQL格式和简单查询； 特殊运算符、分组查询； 多表连接查询; SQL数据操作 INSERT、UPDATE、DELETE SQL数据定义 表的创建creat、修改alter和删除drop,SQL语句为必考点，考试中分值在20分以上，是重重之重。,结构化查询语言SQL P109,什么是SQL？ SQL (Structured Query Language) SQL被作为关系型数据库管理系统的标准语言。 1974年由Boyce和Chamberlin提出, 1986年10月由美国ANSI公布了最早的SQL标准， 1987年6月,ISO也正式采纳它为国际标准, 1989年4月,ISO提出了具有完整性特征的SQL,并称之为SQL89 为什么需要学习SQL？ 目前，绝大多数流行的关系型数据库管理系统，如Oracle, Microsoft SQL Server等都采用了SQL语言标准。,较少考点，了解,结构化查询语言SQL,1、SQL语言主要特点：了解即可 P109-110 1）一体化的语言：包括了数据定义、数据查询、数据操作和数据控制等方面的功能，它可以独立完成数据库的全部操作。 2）非过程化的语言（面向对象的语言，但非完全面向对象）：对应的是过程化语言 3）SQL的执行方式多样，既能以交互命令的方式直接使用，也能嵌入到各种高级语言中使用。几乎所有的数据库管理系统或数据库应用开发工具都已将SQL融入自身语言中。 4）SQL语言具有完备的查询功能、能对视图进行各种操作、具有良好的可移植性 VFP根据本身的特点对标准SQL进行了修改和扩充，增强了数据处理和有关方面的功能。,SQL语句 P110,SQL语言包含4个部分（一体化的语言）：P109 表5-1 （要求掌握相应SQL命令属于哪一类） 数据定义语言(DDL)，例如：建立CREATE、修改 ALTER 、删除DROP等语句。 数据操作语言(DML)，例如：表中，INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 数据查询语言(DQL)，例如：SELECT语句，核心内容，进行查询、排序、汇总和表联接等。 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。（VFP不支持） 在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的。,一般考点,数据查询SQL语言的核心 P110,SELECT命令格式： 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 FILE ADDITIVE|TO PRINT WHERE AND GROUP BY HAVING ORDER BY , UNION SELECT ,必考点,数据查询SQL语言的核心 P110,1)SELECT：用于说明要查询的数据。 2)FROM：用于说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询。 3)WHERE：用于说明查询条件；如果是多表查询还可通过该子句指明表与表之间的联接条件，进行联接。 4)GROUP BY：用于对查询进行分组，可利用它进行分组汇总。 5)HAVING：跟随在GROUP BY 之后使用，它是用来限定分组，要求只有满足分组条件的记录才能进行分组查询。 6)ORDER BY：用于对查询的结果进行排序。 7)AS：用于指定查询结果中字段的新名称；AS前的表达式可以是一个字段名、表达式、函数等。 8)DISTINCT：用于说明该短语将在查询结果中去掉重复值。 9)INTO TABLE：用于说明查询结果保存何处。,数据查询SQL语言的核心 P110,常用的SQL SELECT 语句包含4个部分： SELECT ALL | DISTINCT； FROM ； WHERE AND； GROUP BY HAVING； ORDER BY ASC | DESC 说明： SQL查询命令通常都比较长，在分为多行书写或输入时，必须在每一行的末尾（最后一行除外）添加一个续行符号“；”。 SELECT-SQL命令可以实现对数据表的选择、投影和联接3种关系操作，SELECT短语对应投影操作，WHERE短语对应选择操作，而FROM短语和WHERE短语配合则可实现多表之间的联接操作。,必考点,SQL简单查询 P112,1.单表无条件查询（字段选择，理解as） 查看成绩表全部字段 Select * from 成绩表 & *表示全部字段 查看学生表部分字段 Select 学号,姓名,系别 from 学生表 Select 学号 as编号, 姓名,系别 from 学生表,As子句 查询结果字段别名,SQL简单查询,2.单表条件查询(where) 查询会计系的学生姓名 Select 姓名 from 学生表 where 系别= “会计“ 查询会计系王、张姓同学的姓名 Select 姓名 from 学生表 where (left(姓名,2)= “王” or left(姓名,2)= “张“) and 系别= “会计“ 查询计算机系学生姓氏（有重复） Select left(姓名,2) from 学生表 where 系别= “计算机“ 查询计算机系姓氏（无重复） Select distinct left(姓名,2) from 学生表 where 系别= “计算机“,Distinct 去掉查询结果中的重复记录,3.排序查询：Order By 子句 P115-116,Order By:查询结果排序 按升序（ASC）或降序（DESC）排列 格式：Order By 项1ASC|DESC,项2ASC|DESC 例:按课程号输出学生的各科成绩，同一门课按成绩降序排列： SELECT * FROM 成绩 ORDER BY 课程号,成绩 DESC,不指明则默认为 ASC升序,必考点,SELECT 学号, 姓名,性别,系别; FROM 学生表; WHERE 性别 = 女; ORDER BY 系别，学号 注意输入格式问题,问题：从学生表中，找出女生的学号、姓名、性别、系别字段值，并要求输出的记录按照系别和学号进行按照升序排列,4.几个特殊运算符查询 P112 表5-3,BETWEENAND子句 【例】查询成绩7585分的学生课程信息： SELECT * FROM 成绩表 WHERE 成绩 BETWEEN 75 AND 85 IN、 NOT IN 子句 【例】查询“计算机”、“管理”系的学生信息： SELECT * FROM 学生表 WHERE 系别 IN (“计算机“,“管理“) LIKE子句 通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符，类似于普通通配符*。 【例】查询姓李的学生信息： SELECT * FROM 学生表 WHERE 姓名 LIKE “李%“,几个特殊运算符,TOP 子句 必须要和Order by一起使用才有效 【例】查询学生表部分信息： SELECT TOP 5 * FROM 成绩表 Order by 成绩 SELECT TOP 20 Percent * FROM 成绩表 Order by 成绩 IS NULL子句 【例】查询成绩为空的学生课程信息： SELECT * FROM 成绩表 WHERE 成绩 IS NULL,限制返回数据前5行,限制返回行百分比,简单查询小结,复杂查询1：嵌套查询 P115,SELECT命令的WHERE子句中嵌套另一个SELECT 命令(即子查询)，子查询的结果必须是确定的值。 例:查询李明所学课程的姓名、课程名和成绩： SELECT 姓名,课程号,成绩 FROM 成绩表 WHERE ； 学号=(SELECT 学号 FROM 学生表 WHERE 姓名=“李明“) 或： SELECT 姓名,课程号,成绩 FROM 学生表,成绩表； Where 学生表.学号=成绩表.学号 and 姓名=李明,复杂查询2：统计查询计算函数 P116-117,必考点,复杂查询2：计算查询,例：查询学生表中的计算机系学生数 select 系别，count(*) from 学生表； where 系别=计算机 select 系别,count(*) as 人数 from 学生表 where 系别=“计算机“ 例：查询选修课程号为1001的平均分、最高分数 select 课程号，avg(成绩) ，max(成绩) ； from 成绩表 Where 课程号=1001,分组计算查询 P117,GROUP BY 分组列,分组列HAVING 过滤条件 GROUP BY子句可以实现分组查询，通常与计算查询（如计数、求和、求平均值等）结合使用，先按指定的数据项分组，再对各组汇总计算。 例：查询各门课程的平均成绩： SELECT 课程号,AVG(成绩) AS 平均成绩 FROM 成绩表 GROUP BY 课程号 例：查询系上人数多于或等于2人的系别： SELECT 系别,COUNT(系别) AS 各系人数 FROM 学生表 GROUP BY 系别 HAVING 各系人数=2,From短语中为关系定义别名，格式为： ，以后可用别名代替表名。用别名也可实现同一关系与自身的联接，称之为自联接即递归联接。例如:,Select s.类名,“先于”,x.类名 from kcm s,kcm x where s.类别=x.xxk,S,X,复杂查询3：自连接查询 P118,自连接查询例题,例：从“成绩表”中找出课程号“1001”的成绩高于课程号“1002”的学生的学号。 select 成绩表.学号 as 学号; from 成绩表 as a,成绩表 as b; where a.学号=b.学号; and a.课程号=“1001“; and b.课程号=“1002“; and a.成绩=b.成绩,不常考点，了解,复杂查询4：多表查询-简单连接查询 P114,多表查询时，表和表之间必须有联接关系，在WHERE子句中可以指定表间的联接关系。 格式为： . = . 【例】查询“李明”选修的课程号、姓名、成绩。 SELECT 姓名,课程号,成绩 FROM 学生表,成绩表； Where 学生表.学号=成绩表.学号 and 姓名=李明,多表查询：简单连接查询,例：查询选修“高等数学”的学生学号、姓名、成绩 Select 姓名,课程号,成绩 from 学生表,成绩表,课程表； Where 学生表.学号=成绩表.学号 ； and 课程表.课程号=成绩表.课程号； and 课程名=高等数学,注意：Select语句必须输入在命令窗口的同一行，如命令太长需分行输入，必须加续行符 ;（分号）,复杂查询5：多表查询-超连接查询 P121,连接运算：等值连接与自然连接，二者区别（了解）： 1. 等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。 2. 等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。 格式：Select from 表名1 inner|left|right|full join 表名2 on 联接条件 where Inner:普通联接或内部联接，满足条件的纪录才出现在结果中 Left:左联接。第一个表中不满足条件的也出现在查询结果中。 Right:右联接。第二个表中不满足条件的也出现在查询结果中。 Full:全联接。不论是否满足条件，两表记录都在结果中出现。 以上不满足条件的对应部分为null。,xs信息 xs成绩 Inner join Left join Right join Full join,超连接查询,例如，查询各门课程的平均成绩，要求输出课程名和成绩信息： SELECT 课程名,AVG(成绩) AS 平均成绩 FROM 课程表 as KC ; INNER JOIN 成绩表 as CJ ON KC.课程号=CJ.课程号 ; GROUP BY CJ.课程号 例如，查询刘天海的英语成绩，输出姓名、课程名和成绩信息： SELECT 姓名,课程名,成绩 FROM 学生 XS INNER JOIN 课程 KC INNER JOIN 成绩 CJ ON KC.课程号=CJ.课程号 ON XS.学号=CJ.学号 WHERE 姓名=“刘天海“ AND 课程名=“英语“,复杂查询6：查询去向 P123-124,存放到数组 使用“into array 数组名” 短语。 存放到临时文件中 使用“into cursor 临时文件名” 短语。 存放到永久表中 使用“into DBF|TABLE 表名” 短语。 存放到文本文件中 使用“to FILE 文件名additive”短语。 输出到打印机 使用“to printer prompt”短语,主要内容,SQL语言的特点组成 SQL数据查询 SQL格式和简单查询； 特殊运算符、分组查询； 多表连接查询; SQL数据操作 INSERT、UPDATE、DELETE SQL数据定义 表的创建、修改和删除,常考点,数据操作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,数据操作UPDATE更新 P125,更新记录 格式：UPDATE SET = WHERE 例如，将“成绩表”中“1001”课程的60分以下的成绩加5分 UPDATE 成绩表 SET 成绩=成绩+5; WHERE 课程号=“1001”and 成绩60 类比于表的命令replace * with * for *,数据操作DELETE删除 P126,删除记录 格式：DELETE FROM WHERE 说明：该命令执行的是逻辑删除操作。 例如，将“学生表”中所有男生的记录逻辑删除： DELETE FROM 学生表 WHERE 性别=“男“,主要内容,SQL语言的特点组成 SQL数据查询 SQL格式和简单查询； 特殊运算符、分组查询； 多表连接查询; SQL数据操作 INSERT、UPDATE、DELETE SQL数据定义 表的创建、修改和删除,数据定义 P126,标准SQL的数据定义功能非常广泛，包括数据库、表、视图、存储过程、规则及索引的定义等 数据定义语言由3个命令组成 定义表：CREATE TABLE 修改表结构：ALTER TABLE 删除表：DROP TABLE,常考点,数据定义Create建表,Create Table,CREATE TABLE 课程表 (课程号 C(4) PRIMARY KEY, 课程名 C(20) NOT NULL, 学时 N(3) NULL, 学分 N(2) NULL),数据定义Create建表 P126,CREATE TABLE FREE( ( ，小数位 ) NULL|NOT NULL CHECK ERROR DEFAULT PRIMARY KEY|UNIQUE ， ) 创建一个由TableName1所标识，且含有指定字段的基本表。 注意SQL命令与VFP命令的比较,数据定义Create建表 P126,字段类型：C，N，D，L，M，G，相应的字段宽度见P89 Null：字段值允许为空，Not Null：字段值不允许为空 FREE：指定创建自由表，当数据库没有打开时，不必指定该项.Reference：关系父表，Tag：父表索引标识 CHECK 用于指定字段的有效性规则。 ERROR 指定当输入的字段值违反有效性规则时，显示提示信息。 DEFAULT 用于设置字段的默认值。 PRIMARY KEY 用于设置字段为主索引，一个表中只能有一个主索引，UNIQUE 用于设置侯选索引，不允许出现NULL值，同一个字段不能既是主索引，又是侯选索引，也就是说，PRIMARY KEY和UNIQUE和不能同时出现在一个字段的定义中。,数据定义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),数据定义Create建表,掌握： （1）创建自由表与数据库表的区别 （2）表名后面跟字段用（），字段间用逗号隔开，各个字段的宽度要在字段名后的（）中说明。宽度固定的字段无需说明，数值型字段需要说明小数位数。 （3）每个列后面的完整性约束称为列级完整性约束，它给出对该列数据的完整性约束条件；列级完整性约束有六种。 （4）表级完整性约束在所有列定义后给出，它包括四种（主码约束PRIMARY KEY、单值约束UNIQUE、外码约束FOREIGN KEY REFERENCES 和检查约束CHECK）,数据定义Create建表,列级完整性约束对该列数据的完整性约束条件，有六种： 1、DEFAULT：默认值约束 2、NULL/NOT NULL：空值/非空值约束 3、PRIMARY KEY：主码约束 4、UNIQUE：单值约束/惟一值约束 5、REFERENCES()：外码约束 6、CHECK()：检查约束 当一个列规定为主码时，就隐含规定该列为非空和单值。当一个列虽然没有规定为主码，但若同时规定为非空和单值，则它是一个候选码。,数据定义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),数据定义ALTER表修改,修改或删除默认值、字段有效性规则 ALTER TABLE ALTER SET DEFAULT DROP DEFAULT SET CHECK DROP CHECK 例如，删除“成绩表”表中平时成绩字段的有效性规则： ALTER TABLE 成绩