关系数据库标准语言.ppt

2019/11/29,3.1SQL概述3.2数据定义3.3数据查询3.4数据更新3.5视图3.6案例2：活期储蓄管理系统数据库上的查询,2019/11/29,3.1SQL概述,SQL(StructuredQueryLanguage)的字面意思是结构化的查询语言，但它的功能并不仅仅是查询，它是关系数据库管理系统的标准语言。3.1.1SQL的主要功能SQL语言按照功能可以分为3大类：DDL（DataDefinitionLanguage）数据定义语言用于定义关系数据库的模式、外模式和内模式，以实现对数据库基本表、视图及索引文件的定义、修改和删除等操作。最常用的DDL语句是CREATE、DROP和ALTER命令。DML（DataManipulationLanguage）数据操纵语言用于完成数据查询和数据更新操作。其中数据更新指对数据进行插入、删除和修改操作。最常使用的DML语句是SELECT、INSERT、UPDATE和DELETE命令。,2019/11/29,DCL（DataControlLanguage）数据控制语言用于控制对数据库的访问，服务器的关闭、启动等操作。常使用的DCL命令有：GRANT、REVOKE等。3.1.2SQL的特点语言简洁，风格统一，易学易懂SQL语言接近英语，只用几个英文单词的组合就能完成所有功能，因此初学者非常容易上手。既是自含式语言，又是嵌入式语言作为自含式语言，SQL能够独立地用于联机交互的使用方式。当作为嵌入式语言使用时，SQL语句几乎可以不加修改地嵌入到如VB、PB这样的前端开发平台上，利用前端工具的计算能力和SQL的数据库操纵能力，可以快速地建立数据库应用程序。高度非过程化用SQL语言进行数据操作，只要提出“做什么”，而无须知道“怎么做”，SQL语句的实现过程由系统自动完成。,2019/11/29,3.1.3SQL对象命名约定SQL对象包括数据库、表、视图、属性名等。这些对象名必须符合一定规则或约定，一般应遵守下列规则。数据对象名可以为1-30个字符（在MSAccess为64个字符），但有些DBMS限制为8个字符，例如：Oracle数据库就是如此。数据对象名应以字母开头，其余字符可以由字母、数字、下划线组成。3.1.4SQL语句结构和书写准则在SQL语句语法格式中的一些约定符号：尖括号“”中的内容为实际语义。中括号“”中的内容为任选项。，.意思是“等等”，即前面的项可以重复。,2019/11/29,大括号“”与竖线“|”表明此处为选择项，在所列出的各项中仅需选择一项。例如：A|B|C|D意思是A、B、C、D中取其一。SQL中的数据项（包括列项、表和视图）分隔符为“，”；其字符串常数的定界符用单引号“”表示。在编写SQL语句时，遵从某种准则以提高语句的可读性，使其易于编辑，是很有好处的。以下是一些通常的准则：SQL语句对大小写不敏感为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式。SQL语句可写成一行或多行，习惯上每个子句占用一行。关键字不能在行与行之间分开，并且很少采用缩写形式。SQL语句的结束符为分号“；”，分号必须放在语句中的最后一个子句后面，但可以不在同一行。,2019/11/29,3.2数据定义,SQL语言的数据定义功能包括定义数据库、定义基本表、定义索引和定义视图。其基本语句如表3-1所示。表3-1SQL的数据定义语句,2019/11/29,基本表是独立存储在数据库中的表在SQL中一个关系对应一个基本表，一个（或多个）基本表对应一个存储文件，基本表对应的数据必须在数据库中存放。存储文件的物理结构对用户而言是透明的，用户无需关心。一个基本表可以根据需要带一个或多个索引，索引也存放在存储文件中。视图是由一个或几个基本表导出的，它的外部形式也是一个表，是由基本表中选取的行和列组成的视图本身不独立，它依附于基本表，在数据库中不存放视图对应的数据，而只存放关于视图的定义，因此视图只是一个虚表。,2019/11/29,3.2.1数据库的创建与删除创建数据库SQL使用命令CREATEDATABASE创建数据库，其一般语法如下：CREATEDATABASE；【例3-1】创建一个简单数据库。CREATEDATABASEMyDb；删除数据库SQL使用命令DROPDATABASE删除一个或多个数据库，其一般语法如下：DROPDATABASE，,；【例3-2】删除数据库MyDb。DROPDATABASEMyDb；,2019/11/29,3.2.2表的创建与删除创建基本表创建基本表的结构是建立数据库最重要的一步，其一般语法如下：CREATETABLE（，,)；说明：是指要创建的基本表的名称，该名称应符合具体DBMS的标识符的命名规则。指的是表的属性名称。,2019/11/29,定义表的各个属性时应指出其相应的数据类型和长度，不同DBMS支持的数据类型不完全相同，表3-2列出的是IBMDB2SQL支持的主要数据类型：,2019/11/29,关于完整性约束条件，需要说明以下几点：完整性约束条件分为列级完整性约束条件和表级完整性约束条件，它们之间的区别在于：列级完整性约束条件只能用于列，而表级完整性约束条件能够用于一张表中的多列。SQL的完整性约束条件有以下几种：NOTNULL或NULL约束这个约束条件为列级完整性约束条件。NOTNULL为不允许该列存在空值，而NULL为允许该列存在空值。UNIQUE约束UNIQUE约束是惟一性约束。即不允许表中的某一列或者某几列有重复的属性值。DEFAULT约束DEFAULT约束为默认值约束，是列级完整性约束条件。当向表中插入一个新行时，如果对于特定列没有指定数值，则使用DEFAULT子句指定的默认值。,2019/11/29,CHECK约束CHECK约束是检验约束，为插入列中的数据指定约束条件。PRIMARYKEY约束PRIMARYKEY约束即主键约束，是表级完整性约束条件。表中的主键可以是一列或列组，PRIMARYKEY约束可以使得主键的数值在每一行中各不相同。注意：PRIMARYKEY约束类似于UNIQUE约束，差别在于PRIMARYKEY约束中的列不可以为空。FOREIGNKEY约束FOREIGNKEY约束是参照完整性约束，是用于约束外键的，也是表级完整性约束条件。,2019/11/29,【例3-3】创建“学生表”student，它由学号id、姓名name、性别sex、班级号class、出生日期birthday等5个属性组成。其中学号不能为空，值必须惟一，且姓名也必须惟一和非空。CREATETABLEstudent(idCHAR(8)NOTNULLUNIQUE,nameVARCHAR(20)NOTNULLUNIQUE,sexCHAR(1)DEFAULT男NOTNULL,classCHAR(4),birthdayDATE,sumintconstraintc2checksum1800;,2019/11/29,【例3-14】查询员工号为1001的员工姓名及部门号。SELECTename,dept_idFROMemployeeWHEREemp_id=1001;投影运算的实现查询表的全部列【例3-15】查询部门表中部门号为11的全部内容。SELECTdept_id,dname,telFROMdeptWHEREdept_id=11;在SQL语言中，可以用星号“*”代表所有列名，列的显示顺序与基本表中列的顺序一致。【例3-16】下面语句的结果等价于【例3-15】的语句。SELECT*FROMdeptWHEREdept_id=11;,2019/11/29,查询表的部分列【例3-17】查询全体员工的员工号，姓名和参加工作时间。SELECTemp_id,ename,workdateFROMemployee;查询经过计算的值SELECT子句的不仅可以是基本表的属性，也可以是表达式，包括算术表达式、字符串常量和函数等。【例3-18】查询全体员工的姓名及年薪。SELECTename,sal*12FROMemployee;结果:enamesal*12吴伟15600岳玲13200王斌18000徐欢9600,2019/11/29,【例3-19】上例查询如果在中使用字符串常量，结果会更清晰。SELECTename,年薪：,sal*12FROMemployee;查询结果如下：ename年薪：sal*12吴伟年薪：15600岳玲年薪：13200王斌年薪：18000徐欢年薪：9600,2019/11/29,【例3-20】对【例3-18】查询可以通过指定别名来改变查询结果的列标题，这样也可以使结果更清晰。SELECTenameAS姓名,sal*12AS年薪FROMemployee;查询结果如下：姓名年薪吴伟15600岳玲13200王斌18000徐欢9600,2019/11/29,3.3.3SQL的运算符SQL语言使用的运算符包括算术运算符、比较运算符、逻辑运算符等。算术运算符算术运算符有4种：+、-、*、/比较运算符基本比较运算符共9种：=、=、！=或、！（不大于）、！1500ANDjob=部门主管;【例3-30】查询月薪超过1500的员工和所有部门主管的姓名及月薪。SELECTename,salFROMemployeeWHEREsal1500ORjob=部门主管;,2019/11/29,【例3-31】查询领有岗位津贴的员工姓名。SELECTenameFROMemployeeWHEREcommISNOTNULL;【例3-32】查找部门号不属于11和21的员工姓名及部门号。SELECTename,dept_idFROMemployeeWHEREdept_idNOTIN(11,21);,2019/11/29,3.3.4对查询结果排序SQL语言中用ORDERBY子句实现对查询结果的排序，可以根据包含的一列或者多列的表达式进行ASC（升序）或DESC（降序）的排列，默认值是ASC。【例3-33】查询所有员工的姓名及月薪，结果按月薪的降序排列。SELECTename,salFROMemployeeORDERBYsalDESC;ORDERBY子句指定的排序列可以不只一个。,2019/11/29,【例3-34】查询所有员工的姓名、部门号及月薪，结果按部门号升序排列，同一部门按月薪降序排列。SELECTename,dept_id,salFROMemployeeORDERBYdept_id,salDESC;说明：上例中dept_id称为主排序关键字，sal成为次排序关键字。注意：(1)ORDERBY子句不改变基本表中行或列的顺序，只改变查询显示的顺序。(2)ORDERBY子句指定排序的列必须出现在SELECT子句的列表达式中。(3)排序是查询语句的最后一步工作，所以ORDERBY子句一般放在查询语句的最后。,2019/11/29,3.3.5消除重复行基本表中不相同的行，经过对某些指定列进行投影运算后，可能会变成完全相同的行，显示结果不直观，这时需要用DISTINCT选项消除重复的行。【例3-35】查询表employee中的所有职位。SELECTDISTINCTjobFROMemployee;注意：在一个SELECT语句中DISTINCT只能出现一次，并且DISTINCT必须在所有列名之前，否则会发生语法错误。与DISTINCT选项含义相反的是ALL选项，在SELECT语句中使用ALL选项，表示结果重复的行也将显示。ALL选项是默认选项。,2019/11/29,3.3.6SQL的统计函数SQL语言提供了许多统计函数，主要的统计函数见表3-3。,2019/11/29,【例3-36】统计员工总人数。SELECTCOUNT（*）FROMemployee;也可以写成：SELECTCOUNT（emp_id）FROMemployee;【例3-37】统计部门号“11”的部门领取岗位津贴的人数。SELECTCOUNT(comm)FROMemployeeWHEREdept_id=11;这里统计的是属性列comm不为空值的行数。,2019/11/29,【例3-38】统计部门号“11”的部门全体员工人数。SELECTCOUNT(*)FROMemployeeWHEREdept_id=11;【例3-39】查询最早参加工作时间和最晚参加工作时间。SELECTMIN(workdate),MAX(workdate)FROMemployee;【例3-39】统计所有员工的岗位津贴总数及平均岗位津贴。SELECTSUM(comm),AVG(comm)FROMemployee;注意：除COUNT（*）外，所有的统计函数都不包括取值为空值的行。,2019/11/29,3.3.7数据分组利用GROUPBY子句可以将查询结果按照一列或者多列分组，值相等的为一组。基于单列的分组【例3-40】按部门号查询各部门的平均月薪。SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;基于多列的分组GROUPBY子句还可以作用于多列上，此时的数据分组意义是分大组之后再分小组。【例3-41】分各部门各职位统计月薪总额。SELECTdept_id,job,sum(sal)FROMemployeeGROUPBYdept_id,job;说明：先按照部门号分组，部门号相同的组再按职位细分，最后部门号和职位完全相同的行才会分在一组，然后每组统计一个月薪总额。,2019/11/29,HAVING子句如果分组后还要根据一定条件对这些组进行筛选，则使用HAVING子句来实现。【例3-42】按部门号查询各部门的平均月薪，要求只显示平均月薪在1000以上的部门编号和平均月薪。SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_idHAVINGAVG(sal)1000;注意:WHERE子句和HAVING子句有相似之处，即后面都跟指定条件；但是它们又有区别：前者直接用于SELECT子句中，作用于基本表或视图；而后者一定跟在GROUPBY子句后面，作用于分组。,2019/11/29,3.3.8连接查询连接查询的概念如果一个查询需要从两个或两个以上的数据表中获取数据时，则称之为连接查询。连接查询包括广义笛卡尔积、等值连接、自然连接、外连接、内连接、左连接、右连接和自连接等。广义笛卡尔积广义笛卡尔积是不带连接条件的连接操作。两个表的广义笛卡尔积即是两个表中所有记录的交叉组合，其形成的结果集是所有连接种类中最大的。比如：表1有3条记录，表2有5条记录，则广义笛卡尔积产生3*5=15条记录。由于这种连接操作是不带条件的表的拼接，因此实际意义不大。,2019/11/29,等值连接.=.等值连接又称为内连接。若将查询结果的目标列中重复的列去掉，则称为自然连接，在实际中等值连接一般以自然连接的形式出现。【例3-43】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。SELECTemp_id,ename,dept.dept_id,dname,telFROMemployee,deptWHEREemployee.dept_id=dept.dept_id;说明：(1)如果属性列名在参加连接的各表中是惟一的，可以省略表名前缀；如果属性列名是两个表共同的属性，则一定要加表名前缀。(2)在书写连接查询时，为了简化，可以为表名取别名，别名应该简单。别名只在本次查询有效。,2019/11/29,【例3-44】查询每位员工的员工号，姓名，部门号、部门名称及部门电话。SELECTemp_id,ename,d.dept_id,dname,telFROMemployeee,deptdWHEREe.dept_id=d.dept_id;不等连接当连接条件中的比较运算符不为“=”时，此时的连接查询称为不等连接。,2019/11/29,假设有JOB表（职位表），它包含两个属性：职位job_level和标准月薪std_sal，该表包含记录如下：job_levelstd_sal部门经理2200出纳1200【例3-45】列出可提供给employee表中每位员工比现在薪水高的职位。SELECTename,sal,job_level,std_salFROMemployee,jobWHEREstd_salsal查询结果如下：enamesaljob_levelstd_sal吴伟1300部门经理2200岳玲1100部门经理2200王斌1500部门经理2200徐欢800部门经理2200岳玲1100出纳1200徐欢800出纳1200,2019/11/29,3.4数据更新,3.4.1插入记录插入单条记录INSERTINTO(,)VALUES(,);注意：（1）属性列的个数与常量的个数要相等，且顺序一致，否则会产生语法错误。（2）在表结构定义中未说明为NOTNULL的属性列，如果没有出现在INTO子句后，这些列将取空值。已经说明为NOTNULL的属性列，则必须出现在INTO子句后。（3）如果INTO子句后没有指定任何列，则VALUES子句后面的常量个数必须与基本表中列的个数相等，且类型、顺序一致，否则会出语法错误或导致赋值不正确。,2019/11/29,【例3-47】插入一条部门新记录。INSERTINTOdept(dept_id,dname,tel)VALUES(31,产品开发部,08667864532);该语句等价于：INSERTINTOdeptVALUES(31,产品开发部,08667864532);【例3-48】插入一条员工新记录。INSERTINTOemployeeVALUES(1311,淳,NULL,NULL,to_date(2004/08/15),800,11);,2019/11/29,插入子查询的结果SELECT语句可以作为子查询嵌套在INSERT语句中，用以插入批量记录。其语句格式一般为：INSERTINTO(,)子查询;【例3-49】求出每个部门平均月薪，将部门号和平均月薪放入一张新表dept_sal中。先创建新表的结构：CREATETABLEdept_sal(dept_idCHAR(2),avg_salSMALLINT);然后将子查询求出的数据批量插入新表中：INSERTINTOdept_sal(dept_id,avg_sal)SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;,2019/11/29,3.4.2修改记录SQL语言修改记录的语句为UPDATE。该语句有3种形式：修改单条记录、修改多条记录以及使用子查询修改记录。其一般语句格式为：UPDATESET=,=,WHERE;SQL语言的修改语句功能是将表中符合WHERE子句条件的记录找出，以表达式的值替代相应属性列的值。,2019/11/29,修改单条记录【例3-50】修改1311号员工的月系薪为1700。UPDATEemployeeSETsal=1700WHEREemp_id=1311;修改多条记录【例3-51】所有员工月薪上调5%。UPDATEemployeeSETsal=sal*1.05;【例3-52】21号部门的所有员工取消岗位津贴。UPDATEemployeeSETcomm=0WHEREdept_id=21;,2019/11/29,用子查询修改记录UPDATE语句可以和SELECT语句联合使用。后者作为子查询嵌套。这种情况也属于批量修改。【例3-53】将产品开发部的部门主管的岗位津贴加500。UPDATEemployeeSETcomm=comm+500WHEREjob=部门主管ANDdept_id=(SELECTdept_idFROMdeptWHEREdept.dname=产品开发部);说明：(1)因为对应产品开发部的部门号只有一个，所以子查询检索出的记录只有一条，因此子查询前面可以用“=”。(2)这条UPDATE语句的WHERE子句有两个条件，它们是逻辑与关系。根据这两个条件查询找出的记录可能是多条。,2019/11/29,3.4.3删除记录DELETE语句一般格式：DELETEFROMWHERE;DELETE语句的功能是：先按照WHERE子句中指定的条件范围将记录找出来，然后进行删除。删除单条记录【例3-54】删除员工号为1045的记录。DELETEFROMemployeeWHEREemp_id=1045;,2019/11/29,删除多条记录【例3-55】删除部门号为31的所有记录。DELETEFROMemployeeWHEREdept_id=31;【例3-56】删除所有员工记录。DELETEFROMemployee;删除所有记录，是清空表中数据，因此不加WHERE子句。这类操作的执行应非常小心。,2019/11/29,使用子查询删除SELECT语句同样也可以和DELETE语句联合使用。【例3-57】删除产品开发部和事业推广部的所有员工的记录。DELETEFROMemployeeWHEREdept_idIN(SELECTdept_idFROMdeptWHEREdept.dname=产品开发部ORdept.dname=事业推广部);因为SELECT子查询得到的记录一般不止一条，所以子查询前面的运算符不能为“=”，而应该用表示取值范围的“IN”。,2019/11/29,3.5视图,3.5.1视图的概念视图不包含任何数据，只是定义在一个或多个基表上或其他视图上，并且提供一种访问基表数据的方法。在物理磁盘上存储的有关视图的信息是：视图的名称和视图的定义。视图的所有数据来自基本表。因此，当基本表的数据发生变化时，对应视图中的查询出的数据也会随之变化。视图一经定义，就可以象基本表一样执行查询、删除等操作，也可以在视图上定义新的视图，但对视图的更新操作则有一定限制。3.5.2视图的作用可以满足不同用户的需求不同的用户对数据库操作有不同的需求，即使相同的数据也可能有不同的操作要求。一张基本表可能有很多属性列，利用视图，用户可以把自己感兴趣的属性列集中起来，放在一个视图中，此后用户可以将视图作为一张表来对待。,2019/11/29,可以简化数据读取查询数据时，通常要用SELECT语句编写复杂的连接、统计、函数等，以产生所需要的结果。使用视图，可以隐蔽这种复杂性。可以将经常用到的复杂查询的语句定义为视图，不必每次查询都写上复杂查询条件，这样就简化了用户的查询操作。保证了基本表数据和应用程序的逻辑独立性当应用程序通过视图来访问数据时，视图实际上成为应用程序和基本表数据之间的桥梁。如果应用程序直接调用基本表，则一旦基本表的数据发生变化时，应用程序必须随之改动。而通过视图访问数据，则可以通过改变视图来适应基本表的变化，使应用程序不必作改变，保证了基本表数据和应用程序的逻辑独立性。可以提供安全机制利用视图可以限制数据访问。如果某个用户需要访问表中的某些列，但另一些属性列必须对该用户保密，则可以利用视图达到此目的，将视图建立在该用户需要访问的那些列上。,2019/11/29,3.5.3视图的建立SQL语言用CREATEVIEW语句来建立视图，其一般格式为：CREATEVIEW(，)ASWITHCHECKOPTION；说明：(1)选项WITHCHECKOPTION确保用户只能查询和修改他们所看到的数据，强制所有在视图上使用的数据修改语句满足定义视图时的条件。(2)组成视图的各属性列可以显式指定，也可以省略。如果省略不写，则组成视图的各属性列由子查询中SELECT子句的各目标列组成。下列情形建立视图时必须显式指定属性列：1)视图的某列不是原属性列，而是统计函数或者表达式。2)多表连接时选出了两个或者多个同名列作为视图的属性列。3)需要对视图中的某些列重新命名。,2019/11/29,【例3-58】建立仅包含部门主管视图。CREATEVIEWmgr_vuASSELECT*FROMemployeeWHEREJOB=部门主管;【例3-59】为21号部门的所有员工的员工号、姓名及月薪建立视图。CREATEVIEWsal21_vu(eno,ename,salary)ASSELECTemp_id,ename,salFROMemployeeWHEREdept_id=21;,2019/11/29,【例3-60】为21号部门月薪超过1500的员工的员工号、姓名及月薪建立视图。CREATEVIEWsal21_vu1(eno,ename,salary)ASSELECTemp_id,ename,salFROMemployeeWHEREdept_id=21ANDsal1500;该题也可以对【例3-55】定义的视图进行进一步的筛选。由于视图不仅可以建立在基本表之上，还可以建立在视图上，因此该语句也可写作：CREATEVIEWsal21_vu1ASSELECT*FROMsal21_vuWHEREsal1500;,2019/11/29,【例3-61】为各部门的平均月薪建立视图CREATEVIEWsalavg_vu(dno,sal_avg)ASSELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;本例中因使用了统计函数，因而对视图的列作