第6章--SQL语言基础

第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语句的基本格式为：SELECT[ALL|DISTINCT]TOPn[PENCERT]目标列表达式[,目标列表达式]…[INTO新表名]FROM数据源表名或视图名[,数据源表名或视图名]…[WHERE条件表达式][GROUPBY列名1…[HAVING条件表达式]][ORDERBY列名2[ASC|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.改变列标题其语法格式如下：SELECT列名1[AS]别名1，列名2[AS]别名2，…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可以用来进行字符串的匹配。其一般语法格式如下：列表[NOT]LIKE匹配串【例6.9】查询名字中含有K字母的雇员姓名。SELECTenameFROMscott.empWHEREenameLIKE‘%K%’6.5基本数据查询5.涉及空值NULL的查询【例6.10】查询comm列为NULL的雇员信息。SELECT*FROMscott.empWHEREcommISNULL6.多重条件查询多重条件查询也称为复合条件查询，是指WHERE子句后有多个查询条件，使用逻辑运算符（AND、OR、NOT）将多个条件联接起来，组成复合查询条件。【例6.11】查询10号部门工资在1000元以上的雇员信息。SELECT*FROMempWHEREdeptno=’10’andsal10006.5基本数据查询6.5.4使用ORDERBY子句进行查询的排序使用ORDERBY子句的语法格式如下：SELECT[ALL|DISTINCT]TOPn[PENCERT]目标列表达式[,目标列表达式]…FROM数据源表名或视图名ORDERBY[排序列名表[ASC|DESC]]【例6.12】请将emp表中元组按照部门号升序、员工编号降序排列。SELECT*FROMscott.empORDERBYdeptnoASC,empnoDESC6.5基本数据查询6.5.5使用GROUPBY子句进行分组查询GROUPBY子句可以将查询结果集的各行按一列或多列取值相等的原则进行分组。分组的目的是为了能为每个分组生成其汇总信息，即细化聚合函数的作用对象为每一组。6.5.6使用HAVING子句对分组进行筛选HAVING子句通常与GROUPBY子句一起使用，其作用是在在完成对分组结果统计后，再使用HAVING子句对分组结果进行进一步的筛选。6.5.7使用INTO子句将查询结果存储到表中在SELECT语句中使用INTO子句可以将查询的结果存储到一个新建的数据表或临时表中。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）SUBSTRING()SUBSTRING(expression，starting_position，length)返回从字符串expression左边第starting_position个字符起length个字符的部分。（7）SUBSTR()SUBSTR(x,start[,length])用于从x中取得从start位置开始的一个子字符串，如指定可选参数length，则返回start位置开始的length个字符的子字符串。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)设置日