《SQL语言基础》PPT课件.ppt

2019/11/29,1,第三章SQL语言基础,SQL概述SQL(StructuredqueryLanguage,结构化查询语言)，功能包括查询、操纵、定义、控制。功能丰富、语言简洁、使用灵活，倍受欢迎。1974年由Boyce,Chamberlin提出，1975年在IBM公司的SystemR上首次实现。原型称为SEQUEL(StructuredEnglishQUEryLanguage).1986年ANSISQL-86标准，是第一个SQL标准。1987年成为国际标准。1992年SQL-92标准（简称SQL2）。,2019/11/29,2,1999年SQL-99标准（简称SQL3）。增加了对对象关系模型的支持。目前仅部分实现。最流行的国际标准数据库语言。主要特点：SQL是非结构化的语言，使用SQL查询数据库时，只需要告诉它做什么而不用告诉它如何去做。SQL本身不提供任何程序流程控制结构，而是通过PL/SQL提供SQL语言的过程化功能SQL提供相对固定的数据类型，一般不需要扩展SQL本身十分灵活，方便易学。,2019/11/29,3,标准SQL只包含9种语句：数据查询：select数据定义:create,drop,alter数据操纵:insert,update,delete数据控制:grant,revoke,2019/11/29,4,数据定义语言（DDL），用于定义数据结构数据操纵语言（DML），用于检索和修改数据结构数据控制语言（DCL），用于规定数据库用户的各种权限数据库事务处理，用来保证数据库的完整性,SQL语言分类,2019/11/29,5,SQL语言分类,数据定义语言（DDL）：用于定义数据结构能使用户完成下列任务创建数据库对象删除数据库对象更改数据库对象,2019/11/29,6,常用的数据定义语言DDL语句包括：CreateTable:创建数据库表CreateIndex:创建数据库表的索引DropTable:删除数据库表DropIndex:删除数据库表的索引Truncate：删除表中所有行AlterTable：增加表列，重定义表列，更改存储分配AlterTableADDCONSTRAINT:在已有的表上增加约束,2019/11/29,7,数据操纵语言(DML)：允许用户对数据库中的数据进行Insert,Update,Delete和Select等操作常用DML语句包括：Insert：增加数据行到表Delete：从表中删除数据行Update：更改表中数据Select：从表中或视图中检索数据行,2019/11/29,8,数据控制语言（DCL）：用于规定数据库用户的各种权限常用的数据控制语句包括：GRANT：将权限或角色授予用户或其它角色REVOKE：从用户或数据库角色回收权限SetRole：禁止或允许一个角色,2019/11/29,9,数据库事务控制:用来保证数据库的完整性常用的事务处理语句包括：COMMITWORK：把当前事务所作的更改永久化（写入磁盘）ROLLBACK:作废上次提交以来的所有更改,2019/11/29,10,事务：指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要具有原子性，一致性，隔离性和持久性四个属性。,2019/11/29,11,SQL语句的基本语法,基本语法如下：每条SQL语句必须以分号结束每条SQL语句可以单独写成一行，也可以分成若干行SQL语句对大小写不敏感，对于SQL语句的关键字（如Insert等），表名，列名等，可以大小写混合；但对列的内容则是大小写敏感的。,2019/11/29,12,对象命名约定,SQLServer用三段式名字标识对象：.前两者可以省略，系统自动有一个默认值。数据库名的默认值是当前数据库，所有者名的默认值是数据库的所有者（dbo）例如：在pubs数据库中的authors表的所有者是dbo，它可以在多个数据库中按如下方法引用：pubs.dbo.authorsPubs.authors,2019/11/29,13,别名：使用别名的主要目的是增加select语句的可读性。可使用如下语句指派数据表的别名：数据表名称as数据表别名,2019/11/29,14,例如：usesampleSelecte.员工编号,e.员工姓名From员工数据表ase注意：如果为数据表指定了别名，则在相应的T-SQL语句中，对该数据表的所有显式引用都必须使用别名，而不能使用数据表名。例如：Select员工数据表.员工编号,e.员工姓名From员工数据表ase,2019/11/29,15,SELECT语句,数据检索是数据库中最频繁执行的活动在SQL中，使用SELECT语句可以在需要的表单中检索数据在进行检索之前，必须知道需要的数据存储在哪里SELECT语句可以由多个查询子句组成,2019/11/29,16,SELECT语句,SELECT语句可用于检索：全部的行和列全部的行和特定的列限定范围的行与一组值匹配的行根据未知值检索的行隐藏有重复值的行根据多个搜索条件检索的行,2019/11/29,17,SELECT语句,Select语句的基本结构如下：SELECTALL|DISTINCTselect_listINTOnew_table_nameFROMtable_name|view_name,table_name2|view_name2.,table_name16|view_name16WHEREsearch_conditionsGROUPBYgroup_by_listHAVINGsearch_conditionsORDERBYorder_listASC|DESC,2019/11/29,18,SELECT子句,SELECT子句指定需要通过查询返回的表的列。其语法如下:SELECTALL|DISTINCTTOPn各参数说明如下：其中，select_list表示需要检索的字段的列表，字段名称之间用逗号分隔。这个列表中既可以包含数据源表或视图中的字段名称，还可以包含其它表达式。如用*，则系统将返回数据表中的所有字段。,2019/11/29,19,ALL：指明查询结果中可以显示值相同的列ALL是系统默认的DISTINCT：指明查询结果中如果有值相同的列，则只显示其中的一列。对DISTINCT选项来说，Null值被认为是相同的值TOPnPERCENT：指定返回查询结果的前n行数据，如果PERCENT关键字指定的话，则返回查询结果的前百分之n行数据。,2019/11/29,20,INTO子句,INTO子句用于把查询结果存放到一个新建的表中。SELECT.INTO句式不能与COMPUTE子句一起使用。其语法如下INTOnew_table参数new_table指定了新建的表的名称，新表的列由SELECT子句中指定的列构成。新表中的数据行是由WHERE子句指定的。但如果SELECT子句中指定了计算列，在新表中对应的列则不是计算列，而是一个实际存储在表中的列，其中的数据由执行SELECT.INTO语句时计算得出。,2019/11/29,21,FROM子句,FROM子句指定需要进行数据查询的表只要SELECT子句中有要查询的列就必须使用FROM子句其语法如下FROM,.ntable_source：指明SELECT语句要用到的表、视图等数据源，该列表中的数据表名和视图名之间使用逗号分隔。,2019/11/29,22,T-SQL可以支持在from子句中指定除了数据表或视图以外的其他对象，例如查询结果集构成的派生表。这些派生表实际上是from子句中select语句的查询结果集，这些结果集构成了外层select语句查询时所用的数据表。,2019/11/29,23,例如：,SelectEmp.员工编号，emp.员工姓名，sp.部门名称From员工数据表asemp，(select部门数据表.部门编号，部门数据表.部门名称From部门数据表Where部门数据表.部门编号2)asspWhereemp.部门编号sp.部门编号首先使用select语句检索高级部门，并用别名sp表示该派生表，然后从员工数据表和sp派生表中检索数据。,2019/11/29,24,Where子句,Where子句指定数据检索的条件，以限制返回的数据行。Where子句中的查询条件比较运算符：、=、=、!=、!范围说明：BetweenAandB、NotBetweenAandB可选值列表：IN、NOTIN模式匹配：LIKE，NOTLIKE是否空值：ISNULL、ISNOTNULL上述条件的逻辑组合：AND、OR、NOT,2019/11/29,25,比较查询条件：text、ntext和image数据类型不能与比较运算符组合成查询条件。列表查询条件：in关键字在大多数情况下应用于嵌套查询（又称为子查询）中，通常首先使用select语句选定一个范围，然后将选定的范围作为in关键字的符号条件的列表，从而得出最终的结果集。,2019/11/29,26,模式查询条件：Like、NotLike通配符*匹配任意字符串?匹配任意一个字符大小写敏感,2019/11/29,27,Like关键字中的通配符及其含义,2019/11/29,28,Like关键字举例,2019/11/29,29,空值判断查询条件：null值表示字段的数据值未知或不可用，它并不表示零（数字值或二进制值）、零长度的字符串或空白（字符值）,2019/11/29,30,如果用户要查找的数据中本身就包含了通配符，如SQL_Mail，就需要使用转义字符来区分通配符与实际存在的字符。其格式如下LIKE字符匹配串ESCAPE转义字符例：查找对象名称为SQL_M开头，il结尾，中间有一个不确定字符的对象select*fromobjectswhereobject_namelikeSQL#_M_ilescape#/*这里使用了两个下划线_符号，前一个下划线由于有逃逸字符在其前面作标识，因而被认为是实际存在的下划线字符；后面一个下划线没有逃逸字符在其前面作标识，因此将它作为通配符*/,2019/11/29,31,使用来将通配符指定为普通字符。例如：Select*fromobjectWherecolumn1like%54%将返回所有包含54%的字符串,2019/11/29,32,GROUPBY子句,GROUPBY子句指定查询结果的分组条件。其语法如下：GROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUP各参数说明如下：group_by_expression：指明分组条件group_by_expression通常是一个列名，但不能是列的别名。数据类型为TEXT、NTEXT、IMAGE或BIT类型的列不能作为分组条件ALL：返回所有可能的查询结果组合，即使此组合中没有任何满足WHERE子句的数据。分组的统计列如果不满足查询条件，则将由NULL值构成其数据。ALL选项不能与CUBE或ROLLUP选项同时使用,2019/11/29,33,CUBE：除了返回由GROUPBY子句指定的列外，还返回按组统计的行。返回的结果先按分组的第一个条件列排序显示，再按第二个条件列排序显示，以此类推。统计行包括了GROUPBY子句指定的列的各种组合的数据统计ROLLUP：与CUBE不同的是，此选项对GROUPBY子句中的列顺序敏感，它只返回第一个分组条件指定的列的统计行，改变列的顺序会使返回的结果的行数发生变化。,2019/11/29,34,例如：,例：查询工作级别为2的员工姓名，查询结果按部门分组selecte_name,dept_idfromemployeewherejob_level=2groupbydept_id,e_name,2019/11/29,35,CUBE：除了返回由GROUPBY子句指定的列外，还返回按组统计的行。返回的结果先按分组的第一个条件列排序显示，再按第二个条件列排序显示，以此类推。统计行包括了GROUPBY子句指定的列的各种组合的数据统计Select所属部门，性别，AVG(工资)FROM员工数据表Groupby所属部门，性别Withcube,2019/11/29,36,ROLLUP：与CUBE不同的是，此选项对GROUPBY子句中的列顺序敏感，它只返回第一个分组条件指定的列的统计行，改变列的顺序会使返回的结果的行数发生变化。Select所属部门，性别，AVG(工资)FROM员工数据表Groupby所属部门，性别Withrollup,2019/11/29,37,若希望在查询结果总仅包含按照性别分组后取得的平均值，则需要将groupby子句中的“所属部门”和“性别”字段的位置调换。Select所属部门，性别，AVG(工资)FROM员工数据表Groupby性别，所属部门Withrollup,2019/11/29,38,HAVING子句,HAVING子句指定分组搜索条件。HAVING子句通常与GROUPBY子句一起使用。TEXT、NTEXT和IMAGE数据类型不能用于HAVING子句。其语法如下HAVINGHAVING子句与WHERE子句很相似，其区别在于其作用的对象不同。WHERE子句作用于表和视图，HAVING子句作用于组,2019/11/29,39,例如：,例：查询有多个员工的工资不低于6000的部门编号selectdept_id,count(*)fromemployeewheree_wage=6000groupbydept_idhavingcount(*)1,2019/11/29,40,ORDERBY子句,ORDERBY子句指定查询结果的排序方式。其语法如下：ORDERBYorder_by_expressionASC|DESC,.n各参数说明如下：order_by_expression：指定排序的规则order_by_expression可以是表或视图的列的名称或别名，如果SELECT语句中没有使用DISTINCT选项或UNION操作符，那么ORDERBY子句中可以包含selectlist中没有出现的列名或别名。ORDERBY子句中也不能使用TEXT、NTEXT和IMAGE数据类型ASC：指明查询结果按升序排列这是系统默认值DESC：指明查询结果按降序排列,2019/11/29,41,COMPUTE子句,COMPUTE子句在查询结果的末尾生成一个汇总数据行，其语法如下：COMPUTEAVG|COUNT|MAX|MIN|SUM(expression),.nBYexpression,.n各参数说明如下：AVG|COUNT|MAX|MIN|SUM：以上参数与对应的函数有相同的含义，这些函数均会忽略NULL值且DISTINCT选项不能在此使用,2019/11/29,42,expression：指定需要统计的列的名称，此列必须包含于SELECT列表中，且不能用别名。COMPUTE子句中也不能使用TEXT、NTEXT和IMAGE数据类型BYexpression：在查询结果中生成分类统计的行。如果使用此选项，则必须同时使用ORDERBY子句。expression是对应的ORDERBY子句中的order_by_expression的子集或全集,2019/11/29,43,例如：,Select员工姓名，所属部门，工资From员工数据表Orderby所属部门Coumputesum(工资)by所属部门注意：在加入by关键字及其分组字段的同时必须将这个分组字段同时作为排序条件，否则查询会出错。,2019/11/29,44,UNION子句,UNION操作符将两个或两个以上的查询结果合并为一个结果集。它与使用连接查询合并两个表的列是不同的，使用UNION操作符合并查询结果需要遵循两个基本规则：列的数目和顺序在所有查询中必须是一致的；数据类型必须兼容其语法如下：Select语句UNIONALLSelect语句,2019/11/29,45,注意：使用union子句获得的结果集的字段名称与union运算符之前的select语句结果集中的字段名相同，union运算符之后的select语句结果集的字段名将被忽略。在默认情况下，union运算符将从最终结果集中删除重复的记录。如果希望最终结果集保留所有的记录，则必须使用all关键字。,2019/11/29,46,在使用union运算符时，单独的select语句中不能包含其自己的orderby或coumpute子句只能在最后一个select语句的后面使用一个orderby或compute子句，此时，该子句将适用于最终的组合结果集若需要对查询结果进行分组以及在分组后对结果使用having子句进行过滤，则必须在单独的select语句中指定groupby和having子句。,2019/11/29,47,联接查询,通过使用联接查询，可以根据各个数据表之间的逻辑关系从两个或多个数据表中检索数据。定义数据表之间的关联方式：1在数据表中指定用于联接的字段。典型的联接条件是在一个数据表中指定外键，同时在另一个数据表中指定与其关联的主键。2在select语句中指定比较各字段值时要使用的逻辑运算符。联接的类型：内连接外联接：左向外联接，右向外联接，完整外联接交叉联接,2019/11/29,48,内连接内联接的格式为：数据表1innerjoin数据表2on联接表达式指定返回两个表中所有匹配的行。Inner是缺省的连接方式例：select*from员工数据表Innerjoin项目数据表On员工数据表.员工编号项目数据表.负责人,2019/11/29,49,外联接：左向外联接，右向外联接和完整外联接。左向外联接的格式为：数据表1leftjoin数据表2on联接表达式或数据表1leftouterjoin数据表2on联接表达式注意:返回结果集中将包括数据表1中所有的记录，而不仅仅是联接字段所匹配的记录。如果数据表1的某条记录在数据表2中没有匹配的记录，则结果集相应记录的有关数据表2的所有字段将为空值。例如：select*from员工数据表leftjoin项目数据表On员工数据表.员工编号项目数据表.负责人则：检索员工数据表中的所有记录，并将项目数据表中负责人字段可以匹配的记录输出到结果集。,2019/11/29,50,右向外联接的格式为：数据表1rightjoin数据表2on联接表达式或数据表1rightouterjoin数据表2on联接表达式注意:返回结果集中将包括数据表2中所有的记录，而不仅仅是联接字段所匹配的记录。如果数据表2的某条记录在数据表1中没有匹配的记录，则结果集相应记录的有关数据表1的所有字段将为空值。例如：select*from员工数据表rightjoin项目数据表On员工数据表.员工编号项目数据表.负责人则：检索项目数据表中的所有记录，并将员工数据表中员工编号字段可以匹配的记录输出到结果集。,2019/11/29,51,完整外联接格式如下：数据表1fulljoin数据表2on联接表达式或：数据表1fullouterjoin数据表2on联接表达式结果集将包含两个数据表中的所有记录，当某条记录在另一个数据表中没有匹配记录时，则将另一个数据表的选择列表字段指定为空值,2019/11/29,52,交叉联接格式为：数据表1crossjoin数据表2如果在select语句中没有使用where子句，则交叉联接将返回数据表1和数据表2中记录的笛卡儿乘积，即交叉联接返回数据表1中的所有记录，以及数据表1中的每一条记录与数据表2中的所有记录的组合。例如：select*from员工数据表Crossjoin项目数据表将返回员工数据表中的所有记录，以及员工数据表中的每条记录与项目数据表中的所有记录的组合。,2019/11/29,53,嵌套查询,嵌套查询是指在一个外层查询中包含有另一个内层查询。其中，外层查询称为主查询，内层查询称为子查询。SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为主查询的查找条件可以用多个简单查询来构成复杂查询，以增强SQL的查询能力子查询中一般不使用OrderBy子句，OrderBy子句只能对最终查询结果进行排序,2019/11/29,54,子查询（Subquery）,包含子查询的格式通常采用如下格式：Where表达式notin（子查询）Where表达式比较运算符any|all子查询Wherenotexists(子查询),2019/11/29,55,返回单值的子查询，只返回一行一列主查询与单值子查询之间用比较运算符进行连接运算符：、=、=、例：找出与95001同龄的学生Select*FromStudentWheresage=(SelectsageFromStudentWheresno=95001),子查询单值比较,2019/11/29,56,例：查询选修C01课程的学生的学号、姓名。Selectsno,snameFromStudentWheresnoIN(SelectsnoFromSCWherecno=C01),子查询In,2019/11/29,57,多值比较：多行一列父查询与多值子查询之间的比较需用All来连接标量值s比子查询返回集R中的每个都大时，sAllR为TrueAll表示所有all、=all、allall等价于notin例：找出年龄最小的学生Select*FromStudentWheresageSomeR为True或sAnyR为TrueSome(早期用Any)表示某一个,只要有一个即返回真some、=some、some=some等价于in、some不等价于notin,子查询多值比较Some/Any,2019/11/29,59,例：找出不是最小年龄的学生Select*FromstudentWheresagesome(SelectsageFromStudent),子查询多值比较,2019/11/29,60,Exists子查询用来判断该子查询是否返回元组当子查询的结果集非空时，Exists为True当子查询的结果集为空时，Exists为False不关心子查询的具体内容，因此用Select*,子查询存在判断Exists,2019/11/29,61,例：列出选修了C01课程的学生的学号、姓名Selectsno,snameFromStudentWhereExists(Select*FromSCWhereSC.sno=Student.snoAndcno=C01),子查询Exists,2019/11/29,62,例：列出没有选C01课程的学生的学号、姓名Selectsno,snameFromStudentWhereNotExists(Select*FromSCWhereSC.sno=Student.snoAndcno=C01),子查询NotExists,2019/11/29,63,如何提高select语句的效率,使用exists关键字检查结果集：不要用count(*)来检查结果集中是否包含行。使用标准联接代替嵌套查询：在执行嵌套查询时，SQLServer将先执行内部的子查询，然后将查询结果返回给外部查询作为检索的数据源，最后执行外部的主查询。而在执行包含标准联接的查询时，SQLServer将要执行的仅仅是一个查询。有效避免整表扫描：使用索引；除了缺失索引外，可能导致整表扫描的另外一种常见的情况是在like子句的匹配条件的开始使用了%。若在like子句的匹配条件的开始使用了%，那么包含这个like分句的查询将会调用完整表扫描。,2019/11/29,64,数据插入,格式1：INSERTINTO(，)VALUES(,);insert语句把values后给出的各值按排列顺序依次赋到表名后列出的各列。每次只能插入一条记录。例：INSERTINTOstudentVALUES(95006,张三,男,21，cs）;例：insertintostudent(sname,sno,sdept)values(章小山,96008,IS);-在student中添加一条记