大学数据库教学课件第七章关系数据库标准语言SQL

第七章关系数据库标准语言SQL,SQL结构化查询语言(StructuredQueryLanguage)的功能十分强大，所有表的建立、查询、索引等功能都可以用几条语句完成。本章简要介绍SQL语言的常用语句。只要求掌握这些语句的基本语法及使用，重点掌握SELECT查询语句的一般使用。,7.1SQL概述,SQL是一个功能强大且十分庞大的语言，其标准文档就有600多页。在Access中由用户输入SQL语句，能够实现各种的查询的功能，甚至有些功能只能通过SQL查询完成。,SQL语言的特点：1.高度综合SQL语言可用于表示用户对数据库的所有操作，而所有对数据库的操作是由几个命令来完成的。,7.1SQL概述,2.非过程化用户只需要描述要做什么，而不必告诉系统如何去做。,3.采用面向集合的操作方式该语言的操作对象和操作结果都是元组的集合，也就是表。,现在几乎所有数据库应用开发工具都将SQL直接融入自身中，Access也不例外。,SQL语言的功能：SQL语言提供数据定义、数据查询与数据控制三大类功能。下表为Access所支持的功能。,定义,查询,7.2SQL语言的数据定义,SQL语言的数据类型：注意：不同数据库管理系统所提供的SQL数据类型不完全相同。(教材表7.2中有些类型在Access中不能使用。,掌握以下数据类型：,数据定义：SQL语言的数据定义主要包括表和索引的创建、修改和删除操作。(针对表结构的操作),1.建立数据表CREATETABLE(,(长度),)；功能：创建一个表，指定列属性定义的表结构。,说明：(1)可选项；名词不可拆分项；,前面的项可重复多次。,(2)表的完整性约束一般分列级和表级两种，定义格式如下：NOTNULL列级约束。输入数据时不允许字段值为空。系统默认值为NULL。其作用与Access表的设计视图中“必填字段”相同。例如，CREATETABLE职工(工号CHAR(6),姓名CHAR(6)NOTNULL,工作时间DATE,年龄SMALLINT,工资REAL);,PRIMARYKEY表或列级约束。如果单个字段做主健，可在“”的后面写“PRIMARYKEY。如果多个字段做主键，则需在所有字段定义完成后写“PRIMARYKEY(,)”。其作用与Access表的设计视图中“主键”相同。例如，CREATETABLE系(系编号CHAR(6)NOTNULLPRIMARYKEY,系名称CHAR(40),系主任CHAR(8),学院编号CHAR(1);或CREATETABLE系(系编号CHAR(6)NOTNULL,系名称CHAR(40),系主任CHAR(8),学院编号CHAR(1),PRIMARYKEY(系编号);,FOREIGNKEY外键约束。该约束可在列级或表级定义。如果外键只有一列，可定义为列级约束。如果外键包含一到多个列，可定义为表级约束。例如，CREATETABLE系(系编号CHAR(6)NOTNULLPRIMARYKEY,系名称CHAR(40),系主任CHAR(8),学院编号CHAR(1)REFERENCES学院(学院编号);或CREATETABLE系(系编号CHAR(6)NOTNULL,系名称CHAR(40),系主任CHAR(8),学院编号CHAR(1),PRIMARYKEY(系编号),FOREIGNKEY(学院编号)REFERENCES学院(学院编号);说明：系编号是主键，NOTNULL可省略。注意在参照表（表名由REFERENCES后面的定义）中，这些对应字段必须是已建立了唯一性索引的，否则该建表语句无法执行。,CHECK是域完整性约束，用于输入列值时对输入数据进行有效性检查。其作用与Access表的设计视图中“有效性规则”相同。(无效)UNIQUE唯一性约束，要求不同记录在此字段上取值不能相等。其作用与Access表的设计视图中“索引（无重复）”相同。例如，CREATETABLE系(系编号CHAR(6)NOTNULL,系名称CHAR(40)NOTNULLUNIQUE,系主任CHAR(8),学院编号CHAR(1),PRIMARYKEY(系编号),FOREIGNKEY(学院编号)REFERENCES学院(学院编号);说明：UNIQUE唯一性约束则要求姓名不能有重名。另外，完整性约束主要掌握前2种。,2.修改数据表ALTERTABLEADD,DROPCONSTRAINT|COLUMN,ALTER,;功能：修改表的结构。,说明：(1)是要修改结构的表的名字(2)ADD用于增加新列和新的完整性约束条件(3)DROP用于删除指定列或完整性约束条件(4)ALTER用于修改表的列宽度、数据类型等。注:参数CONSTRAINT无效，参数COLUMN可省略。,例7.2在学生表上，给出完成下列操作的SQL语句。(1)增加“入学时间”列，其数据类型为日期类型(2)将“入学时间”字段改为整数类型，(3)删除“入学时间”字段。完成上述操作的SQL语句依次为：ALTERTABLE学生ADD入学时间DATE;ALTERTABLE学生ALTER入学时间INT;ALTERTABLE学生DROP入学时间;说明，该语句主要掌握增加一个字段、删除一个字段的简单命令。(注：不能对主键操作),3.删除数据表格式：DROPTABLE功能：删除表。说明：表一旦被删除，表中的数据、此表上建立的索引和查询等结构都将自动被删除，并且无法恢复，因此一定要格外小心。,例7.3删除学生和班级两张表。DROPTABLE班级;DROPTABLE学生;注：如果学生表与班级表有关联，需选删除学生表，再删除班级表。,4.索引操作语句(1)建立索引CREATEUNIQUECLUSTEREDINDEXON(ASCDESC,ASCDESC,)功能：为表创建索引。,例7.4(1)为系表建立系编号字段上的唯一索引(2)为系表建立学院编号字段上的一般索引SQL语句如下：CREATEUNIQUEINDEX系表的系编号ON系(系编号);CREATEINDEX系表的学院编号ON系(学院编号);注:按设计视图打开表,点击视图/索引,可看到已创建的索引。,(2)删除索引格式：DROPINDEX索引名ON功能：删除索引。,例7.5给出删除例7.4建立的两个索引的SQL语句。DROPINDEX系表的系编号ON系;DROPINDEX系表的学院编号ON系;,7.3SQL的数据查询,SELECT语句：(该命令掌握程度以教材例题为准)SELECTALL|DISTINCT|,FROM,WHEREGROUPBYHAVINGORDERBYASCDESC功能：创建一个由指定范围内、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集。(1)WHERE：符合条件的记录集。(2)：查询计算函数。(3)GROUPBY：查询结果按指定字段分组。(4)HAVING：只对满足条件的查询结果分组。(5)ORDERBYASCDESC：按指定字段排序(6)ALL|DISTINCT的含义：ALL是指显示所有满足条件的记录(默认)，DISTINCT则指只显示不完全相同结果的记录，可用于过滤重复。,简单查询语句：是指数据来源是一个表或一个视图的查询操作，它是最简单的查询操作。,1.检索表中所有的行和列例7.6查询所有学院的各项信息。,SELECT学院编号,学院名称,院长姓名,电话,地址FROM学院;或SELECT*FROM学院;注：如果查询包括所有列，可用*号代替。,2.检索表中指定的列例7.7查询所有学院的名称和联系方式。,SELECT学院名称,电话,地址FROM学院;,3.检索表中指定的列和产生新列例7.8查询员工工资表，输出员工编号、姓名、时间、扣款、税款和实发工资。假设员工工资表结构为：工资(员工编号CHAR(10),姓名CHAR(10),时间DATE,应发工资REAL,扣款REAL,税款REAL)。,SELECT员工编号,姓名,时间,扣款,税款,应发工资-扣款-税款AS实发工资FROM工资;,例7.9在学生表中，显示学号、姓名和年龄三个字段，且将学号字段的标题显示为“学生号码”。,SELECT学号AS学生号码,姓名,year(now()-year(出生年月)AS年龄FROM学生;,4.检索表中指定的列和指定的行查询满足条件的记录可以通过WHERE子句来实现。,例7.10查询信息学院的名称和联系方式。,例7.11查询有不及格成绩的学生的学号、课程编号和成绩。,SELECT学院名称,电话,地址FROM学院WHERE学院名称=信息学院;,SELECT学号,课程编号,成绩FROM选课WHERE成绩=20)AND(year(now()-year(出生年月)=23);,5.字符串匹配关键词“LIKE”用以进行字符串的匹配，其格式如下：NOTLIKEESCAPE表示查找指定属性列的值与相匹配的元组。通常是含有通配符的字符串。Access中通配符目前只支持“*”和“?”。,例7.13查询叫张玲玲的学生的详细信息。,SELECT*FROM学生WHERE姓名=张玲玲;,例7.14查询姓张且姓名只有两个字的同学的姓名、学号、出生年月、籍贯信息。,SELECT姓名,学号,出生年月,籍贯FROM学生WHERE姓名LIKE张?;,例7.15查询姓名中包含“丽”字的学生的姓名、学号、出生年月、籍贯信息。,SELECT姓名,学号,出生年月,籍贯FROM学生WHERE姓名LIKE*丽*;,例7.16统计每门课程的平均分。,6.检索表中分组统计结果SQL中常用的统计函数与Access中使用的函数相同，利用这些函数可完成简单的数据汇总工作。,例7.17按性别统计学生人数。,SELECT课程编号,AVG(成绩)AS平均分FROM选课GROUPBY课程编号;,SELECT性别,COUNT(学号)AS人数FROM学生GROUPBY性别;,例7.19计算课程编号为03001的课程的平均成绩。,例7.18统计选课表中各门课程的最高分。,SELECT课程编号,MAX(成绩)AS最高分FROM选课GROUPBY课程编号;,SELECTAVG(成绩)AS平均分FROM选课WHERE课程编号=03001;,SELECT*FROM学生ORDERBY出生年月;,7.检索表中排序结果利用ORDERBY子句可以对查询的结果进行排序。,如果按由大到小进行排序，则：SELECT*FROM学生ORDERBY出生年月DESC;,例7.20将学生按出生年月由小到大进行排序。,对例7.16中的查询结果，按平均分由大到小进行排序。,8.空值问题例7.21查询课程中还没有给出所有学生成绩的课程编号。,SELECT课程编号,AVG(成绩)AS平均分FROM选课GROUPBY课程编号ORDERBY平均分DESC;,SELECT课程编号FROM选课WHERE成绩ISNULL;,连接查询：把多个表的信息集中在一起输出，就要用到“连接”操作，可通过在FROM子句中使用多个表名来实现。多个表名在FROM子句中通过逗号隔开。另外还可以用WHERE子句给定表的连接条件。,1.两表连接例7.22查询每个学生的姓名、所在班级名称和专业。本例中，连接条件为“学生.班级编号=班级.班级编号”。,SELECT学号,姓名,班级名称,专业FROM学生,班级WHERE学生.班级编号=班级.班级编号;,2.多表连接连接操作可以是两个以上表之间进行的，此时连接条件要求必须是两两之间给出。,例7.24查询学生的学号和姓名，及所在学院、系和班级的名称。,SELECT学院.学院名称,系.系名称,班级.班级名称,学生.学号,学生.姓名FROM学院,系,班级,学生WHERE(学院.学院编号=系.学院编号)AND(系.系编号=班级.系编号)AND(班级.班级编号=学生.班级编号);注：学院.学院名称可直接写成学院名称，因为只有一个表有此字段，其它项也如此。,其它查询：1.嵌套查询在SQL语言中，一个SELECTFROMWHERE语句产生一个新的数据集，一个查询语句完全嵌套到另一个查询语句中的WHERE或HAVING的“条件”短语中，这种查询称为嵌套查询。,例如：SELECT*FROM学生WHERE学号IN(SELECT学号FROM选课WHERE课程编号=03001),该例中内层查询的含义是查找所有选修了“03001”号课程的学生的学号，这些学号形成一个集合，在此称该集合为S。外层循环的含义是查找学号在S中的学生的详细信息。因此,此嵌套查询的含义是查询选修了“03001”号课程的学生的详细信息。,例7.25查询计算机系、地理系和外语系的所有学生的姓名和专业。,SELECT姓名,专业FROM学生,班级WHERE学生.班级编号=班级.班级编号AND班级.系编号IN(SELECT系编号FROM系WHERE系名称IN(计算机系,地理系,外语系);,2.集合查询SQL提供的集合操作主要包括并、交和差三类，分别用UNION，INTERSECT和EXCEPT表示，其含义与集合操作相同。注：Access目前不支持INTERSECT和EXCEPT操作。,例7.26输出所有教师和学生的姓名和性别字段。,SELECT姓名,性别FROM教师UNIONSELECT姓名,性别FROM学生;,7.4SQL的数据更新,SQL数据更新操作有三类，向表中插入、修改或删除表中若干行数据，其对应的SQL命令分别是INSERT、UPDA