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

第6章SQL语言基础,第6章SQL语言基础,目前，无论是像Oracle、SQLServer、DB2、Sybase等这些大型的数据库管理系统，还是像VisualFoxPro、MySQL、Access等中小型的数据库管理系统，都支持SQL作为数据查询语言。,6.1SQL语言概述,6.1.1SQL语言的功能SQL语言主要有数据定义、数据操纵和数据控制等功能。1.数据定义2.数据操纵3.数据控制4.嵌入功能,6.1SQL语言概述,6.1.2SQL语言的特点1.综合统一2.高度非过程化3.统一的语法结构4.面向集合的操作方式5.语言简洁，易学易用,6.1SQL语言概述,6.1.3PL/SQL与SQL的关系PL/SQL（ProceduralLanguage/SQL，过程化SQL）也是一种程序设计语言，是Oracle公司对标准SQL语言的过程化扩展，PL/SQL在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就可以把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL主要用于在Oracle数据库系统上进行数据操作和开发应用。,6.2数据定义,数据定义功能是针对数据对象进行定义，主要包括表、视图和索引、触发器、存储过程和程序包等。,6.3数据操纵,数据操纵包括数据查询和数据更新两大类操作，是通过数据操纵语言DML来实现。数据查询操作是通过SQL的SELECT语句来完成。数据更新包括数据插入、删除和修改操作，对应SQL的INSERT、DELETE、UPDATE语句。在Oracle11g中，数据操纵语句除了上述语句外，还包括TRUNCATE、CALL、EXPLAINPLAN、LOCKTABLE语句等。,6.4数据控制,数据控制功能主要包括数据库的事务管理功能和数据保护功能等，即对数据库的恢复、并发控制、安全性和完整性控制等，而这些功能都是通过对各种数据库对象的各种操作权限进行管理而实现的。,6.5基本数据查询,6.5.1数据查询基本结构SELECT语句的基本格式为：SELECTALL|DISTINCTTOPnPENCERT,INTOFROM,数据源表名或视图名WHEREGROUPBYHAVINGORDERBYASC|DESC,6.5基本数据查询,6.5.2简单查询1.查询所有列【例6.1】查询表scott.emp表中所有列，可以使用如下语句：SELECT*FROMscott.emp;2.查询指定列【例6.2】查询表scott.emp表中的ename列，语句如下：说明SELECTenameFROMscott.emp;,6.5基本数据查询,6.5.2简单查询3.改变列标题其语法格式如下：SELECTAS，AS，FROM【例6.3】查询表scott.emp表中的ename列将其列名改为雇员姓名，语句如下：SELECTenameAS雇员姓名FROMscott.emp;,6.5基本数据查询,6.5.2简单查询4.查询经过计算的值【例6.4】下列查询语句就是进行运算的结果，查询emp表中的两项工资的总和，执行结果如图6-2所示。SELECTsal+commFROMscott.emp;5.利用DISTINCT关键字消除取值重复的行【例6.5】消除emp表中job列的重复值的语句如下，执行结果如下图。SELECTDISTINCTjobFROMscott.emp;,6.5基本数据查询,6.5.3使用WHERE子句进行筛选1.比较运算符【例6.6】查看scott.emp表中在10号部门的所有员工具体情况。SELECT*FROMscott.empWHEREdeptno=10;2.确定范围【例6.7】查询scott.emp表中工资在1500和3000之间的雇员编号。SELECTempnoFROMscott.empWHEREsalBETWEEN1500and3000;,6.5基本数据查询,3.确定集合【例6.8】查询scott.emp表中在10，20或30号部门工作的雇员编号。SELECT*FROMscott.empWHEREdeptnoIN(10,20,30);4.字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：列表NOTLIKE【例6.9】查询名字中含有K字母的雇员姓名。SELECTenameFROMscott.empWHEREenameLIKE%K%;,6.5基本数据查询,5.涉及空值NULL的查询【例6.10】查询comm列为NULL的雇员信息。SELECT*FROMscott.empWHEREcommISNULL;6.多重条件查询多重条件查询也称为复合条件查询，是指WHERE子句后有多个查询条件，使用逻辑运算符（AND、OR、NOT）将多个条件联接起来，组成复合查询条件。【例6.11】查询10号部门工资在2000元以上的雇员信息。SELECT*FROMscott.empWHEREdeptno=10andsal2000;,6.5基本数据查询,6.5.4使用ORDERBY子句进行查询的排序使用ORDERBY子句的语法格式如下：SELECTALL|DISTINCTTOPnPENCERT,FROMORDERBYASC|DESC【例6.12】请将emp表中元组按照部门号升序、员工编号降序排列。SELECT*FROMscott.empORDERBYdeptnoASC,empnoDESC;,6.5基本数据查询,6.5.5使用GROUPBY子句进行分组查询GROUPBY子句可以将查询结果集的各行按一列或多列取值相等的原则进行分组。分组的目的是为了能为每个分组生成其汇总信息，即细化聚合函数的作用对象为每一组。例6.13统计emp表中各个部门的人数。selectdeptno,count(*)fromscott.empGroupbydeptno;,6.5基本数据查询,6.5.6使用HAVING子句对分组进行筛选HAVING子句通常与GROUPBY子句一起使用，其作用是在在完成对分组结果统计后，再使用HAVING子句对分组结果进行进一步的筛选。【6.14】统计各个部门人数，将多于5人的部门编号输出。Selectdeptnofromscott.empgroupbydeptnoHavingcount(*)5;,6.5基本数据查询,6.5.7使用INTO子句将查询结果存储到表中在SELECT语句中使用INTO子句可以将查询的结果存储到一个新建的数据表或临时表中。例6.15将emp表中的内容复制到newemp中。Createtablenewemp(newenonumber(4);Insertintonewemp1selectempnofromscott.emp;,6.5基本数据查询,6.5.8常用函数在数据库查询中，经常要对查询的数据进行统计计算，如求平均值、总和、平方差等，本节介绍Oracle数据库系统提供的主要函数。1字符函数下面介绍常用的几个字符函数的用法。（1）ASCII()和CHR()ASCII()和CHR()是两个基于ASCII的函数，ASCII(x)函数用于获得字符串x最左边字符的ASCII码，CHR(x)函数用于获得ASCII码值为x的对应的字符。,6.5基本数据查询,6.5.5常用函数【例6.13】查看A的ASCII码。SELECTASCII(A)FROMdual;结果是65，即函数返回字符“A”的ASCII码值。【例6.14】查看ASCII码为65的字符。SELECTCHR(65)FROMdual;结果是字母A。,6.5基本数据查询,6.5.5常用函数（2）LENGTH()LENGTH(x)函数用于获得x字符串中字符的个数，即返回字符串的长度。【例6.15】使用LENGTH函数求字符串ThisisOracle11g。SELECTlength(ThisisOracle11g)FROMdual;,6.5基本数据查询,6.5.5常用函数（3）CONCAT()CONCAT(x,y)函数用于将字符串y添加在字符串x之后，即将两个字符串连接起来形成一个字符串。【例6.16】使用CONCAT函数将EMP表中各元组的ename添加在job之后。SELECTCONCAT(job,ename)FROMscott.emp;,6.5基本数据查询,6.5.5常用函数（4）LOWER()和UPPER()LOWER()将字符串全部转为小写，而UPPER()将字符串全部转为大写。（5）LTRIM()、RTRIM()和TRIM()LTRIM(x,trim_string)函数用于从x的左边截去一些字符，该函数还可以使用可选的参数trim_string来指定要截去的字符；如果没有指定trim_string参数，默认情况下会截去空格。同理，RTRIM函数用于从x的右边截去一些字符，TRIM函数用于从x的左边和右边各截去一些字符。,6.5基本数据查询,6.5.5常用函数（6）SUBSTR()SUBSTRING(，length)返回从字符串expression左边第starting_position个字符起length个字符的部分。Selectsubstr(xiaomingisagoodman,16,8)Fromdual;,6.5基本数据查询,6.5.5常用函数2数字函数下面介绍几个常用的数字函数的具体用法。（1）ABS()ABS(x)用于得到x的绝对值。（2）MOD()MOD(x,y)函数返回x除以y所得的余数。（3）CEIL()CEIL(x)返回大于或等于x的最小整数，即将参数x向上取整。,6.5基本数据查询,6.5.5常用函数2数字函数（4）FLOOR()FLOOR(x)用于获得小于或等于x的最大整数，即将参数x向下取整。（5）POWER()POWER(x,y)用于计算x的y次幂。（6）ROUND()ROUND(x,y)用于计算对x取整的结果，即对x进行四舍五入计算，并返回；其中y为可选参数，说明对第几位小数取整。,6.5基本数据查询,6.5.5常用函数2数字函数（7）SQRT()SQRT(x)函数用于计算x的平方根，负数无意义。,6.5基本数据查询,6.5.5常用函数3聚合函数在实际应用中，常常需要对表中的数据进行分类、统计、汇总等操作，如统计公司的人数、平均工资、最高工资等，这些都需要聚合函数来实现。聚合函数也被称为聚集函数、分组函数、统计函数。,6.5基本数据查询,6.5.5常用函数（1）AVG()函数AVG()函数用于返回一组数值中所有非空数值的平均值。（2）MAX()和MIN()函数MIN()函数用于返回一个列范围内的最小非空值；MAX()函数用于返回最大值。（3）COUNT()函数COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。,6.5基本数据查询,6.5.5常用函数（4）SUM()函数SUM()函数是最常用的聚合函数之一，其功能是返回一个列范围内所有非空值的总和。和AVG()函数一样，它用于数值数据类型。4日期时间函数Oracle数据库系统提供了丰富的日期时间函数来处理日期类型数据，有些函数可用于解析日期值的日期与时间部分，有些函数可用于比较、操纵日期/时间值。,6.5基本数据查询,4日期时间函数（1）LAST_DAY()LAST_DAY()返回指定日期所在月最后一天的日期，通常被用来确定当前月中还剩下多少天。【例6.21】执行下列语句求当前月份的最后一天。SELECTLAST_DAY(SYSDATE)FROMDUAL;（2）MONTHS_BETWEEN()【例6.22】下列例子求当前日期与2012年12月20日之间相隔的月数。SELECTMONTHS_BETWEEN(SYSDATE,20-12月-2012)FROMDUAL;,6.5基本数据查询,4日期时间函数（3）NEXT_DAY()【例6.23】下列例子求下一个星期一的日期。SELECTNEXT_DAY(SYSDATE,星期一)FROMDUAL;(4)获取系统时间Selectsysdatefromdual;Selectcurrent_datefromdual;(5)设置日期格式Altersessionsetnls_date_format=yyyy-mm-ddhh:mi:ss;,6.5基本数据查询,6.5.5常用函数5转换函数（1）CAST()CAST(xAStype)用来将x转换为由type指定的兼容数据库类型。【例6.27】下面查询展示了使用CAST()将字面值转换为指定的类型：SELECTCAST(12345.67ASVARCHAR2(10),CAST(9A4FASRAW(2),CAST(12345.678ASNUMBER(10,2)FROMdual;,6.5基本数据查询,6.5.5常用函数（2）CONVERT()对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。【例6.28】下面的例子演示CONVERT()函数的使用：Selectconvert(搴旇鏄彲浠殑,ZHS16GBK,UTF8)Fromdual;,6.5基本数据查询,6.5.5常用函数（3）TO_CHAR()TO_CHAR(x,format)函数用于将x转换为一个字符串，可选参数format是格式化控制符，用来指定转换后x的格式。结构format取决于x是数字还是日期。Selectto_char(12345.66)fromdual;Selectto_char(sysdate)fromdual;,6.5基本数据查询,6.5.5常用函数（4）TO_NUMBER()TO_NUMBER(x,format)函数用于将x转换为一个数字，可选参数format字符串用来指定转换后的格式。【例6.27】下面这个查询使用TO_NUMBER函数将字符串970.13转换为一个数字，然后再在其基础上加上25.5：SELECTTO_NUMBER(970.13)+25.5FROMdual;,6.5基本数据查询,6.5.5常用函数6正则表达式函数使用正则表达式函数可以在字符串中搜索字符模式。下面介绍几个常用正则表达式函数的具体用法。（1）REGEXP_LIKE()REGEXP_LIKE(x,pattern,match_option)函数用于在x中查找pattern参数中定义的正则表达式。,6.5基本数据查询,6.5.5常用函数（2）REGEXP_INSTR()REGEXP_INSTR(x,pattern,start,occurrence,return_option,match_option)用于在x中查找pattern；REGEXP_INSTR()返回pattern出现的位置，匹配位置从1开始。（3）REGEXP_REPLACE()REGEXP_REPLACE(x,pattern,replace_string,start,occurrence,match_option)用于在x中查找pattern，并将其替换为replace_string。（4）REGEXP_COUNT()REGEXP_COUNT(x,pattern,start,match_option)用于在x中查找pattern，并返回pattern在x中出现的次数。,6.6高级数据查询,6.6.1多表连接查询1定义连接的两种形式定义连接有两种形式，一种是在WHERE子句中定义，另一种是在FROM子句中定义。（1）在WHERE子句中定义连接在WHERE子句中定义连接的查询语句基本格式为：SELECT表名.列名1,表名.列名2,FROM表名1,表名2WHERE表名1.列名表名1.列名;,6.6高级数据查询,6.6.1多表连接查询（2）在FROM子句中定义连接在FROM子句中定义连接的查询语句基本格式为：SELECT表名.列名1,表名.列名2,FROM表名1表名2ON（连接条件）WHERE其中：连接类型可以是：INNERJOIN（内连接）、OUTERJOIN（外连接）、CROSSJOIN（交叉连接）。,6.6高级数据查询,6.6.1多表连接查询2内连接内连接使用INNERJOIN连接关键字，其连接格式为：FROM表名1INNERJOIN表名2ON（连接条件）（1）等值连接在连接条件中使用“=”连接运算符时，称为等值连接。（2）非等值连接在连接条件中使用除“=”以外的其它运算符（、=、）来比较被连接列的列值时，称为非等值连接。,6.6高级数据查询,6.6.1多表连接查询3外连接如果查询结果集包含来自一个表的所有行和另一个表中的匹配行，那么这种连接称为外连接。外连接又分为以下三类：1）左外连接（LEFTOUTERJOIN）：结果集包含左表中所有行和右表中匹配行。2）右外连接（RIGHTOUTERJOIN）：结果集包含右表中所有行和左表中匹配行。3）全外连接（FULLOUTERJOIN）：结果集包含左、右两个表中的所有行。,6.6高级数据查询,6.6.1多表连接查询4交叉连接交叉连接使用CROSSJOIN关键字来连接多个表，不能使用WHERE子句，返回的结果集包含所连接的表中所有行的全部组合，即结果集是所连接的各表数据行的笛卡尔积。5自身连接连接操作不仅可以在两个不同的表之间进行，也可以是一个表与其自己进行连接，这种连接称为自身连接。此时相同表要取不同的别名。,6.6高级数据查询,6.6.2嵌套查询在SQL语句中，一个SQL-FROM-WHERE语句称为一个查询块。有时一个查询块无法完成查询任务，需要将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中，这种将一个查询块嵌套在另一个查询块的条件子句中的查询称为嵌套查询。1使用IN谓词的子查询使用IN谓词的子查询是指父查询和子查询之间用IN关键字进行连接，判断原表中某个列值是否在子查询的结果中。2使用比较运算符的子查询使用比较运算符的子查询是指父查询和子查询之间用比较运算符进行连接。,6.6高级数据查询,6.6.2嵌套查询3使用ANY或ALL谓词的子查询当子查询返回结果为多个值时，父查询还可以通过将比较运算符与ANY或ALL结合来和子查询建立连接。4使用EXISTS谓词的子查询EXISTS代表存在量词彐。有时侯只需要考虑子查询是否有返回结果，而并不考虑结果的具体数据，此时可以使用EXISTS谓词来定义子查询。,6.6高级数据查询,6.6.3集合操作集合操作就是将两个或多个SQL查询结果集合并，形成复合查询，所以集合操作的查询也称为联合查询。,