第14章SQL语言基础

1、Oracle 数据库基础教程2007第第14章章 SQL语言基础语言基础Oracle 数据库基础教程2007本章内容本章内容pSQL语句分类p数据查询（SELECT）p数据操纵（INSERT、UPDATE、DELETE）p事务控制pSQL函数Oracle 数据库基础教程2007本章要求本章要求p掌握数据查询的各种应用p掌握数据操纵的各种应用p掌握事务处理p了解SQL函数应用Oracle 数据库基础教程200714.1SQL语言概述语言概述pSQL语言介绍 pSQL语言的分类 pSQL语言的特点 Oracle 数据库基础教程200714.1.1SQL语言介绍语言介绍pSQL（Structured

2、 Query Language）语言是1974年由Boyce 和Chamberlin提出的。 pSQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过SQL语言实现对数据库的访问和操作。 Oracle 数据库基础教程200714.1.2SQL语言的分类语言的分类p据定义语言（Data Definition Language，DDL）：用于定义、修改、删除数据库对象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。p数据操纵语言（Data Mani

3、pulation Language，DML）：用于改变数据库中的数据，包括数据插入（INSERT）、数据修改（UPDATE）和数据删除（DELETE）。p数据查询语言（Data Query Language，DQL）：用于数据检索，包括SELECT。p事务控制（Transaction Control）：用于将一组DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（COMMIT）、事务回滚（ROLLBACK）、设置保存点（SAVEPOINT）和设置事务状态（SET TRANSACTION）。p系统控制（System Control）：用于设置数据库系统参数，包括ALTER SYSTEM

4、。p会话控制（Session Control）：用于设置用户会话相关参数，包括ALTER SESSION。 Oracle 数据库基础教程200714.1.3SQL语言的特点语言的特点p功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。p高度的非过程化：在使用SQL语言操作数据库时，用户只需要说明“做什么”，而不需要说明“怎样做”。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。p面向集合的操作方式：SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式

5、极大地提高了对数据操作效率。p多种使用方式：SQL语句既是自含式语言，又是嵌入式语言。SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。p简洁、易学：SQL语言命令数量有限，语法简单，接近于自然语言（英语），因此容易学习和掌握。 Oracle 数据库基础教程200714.2数据查询数据查询p数据查询基础p基本查询p分组查询p连接查询p子查询p合并查询Oracle 数据库基础教程2007 14.2.1数据查询基础数据查询基础p基本语法： SELECT ALL|DISTINCTcolumn_name,expression FROM table1_name,table2

6、_name,view_name, WHERE condition GROUP BY column_name1,column_name2, HAVING group_condition ORDER BY column_name2 ASC|DESC,column_name2,; Oracle 数据库基础教程200714.2.2基本查询基本查询p无条件查询p有条件查询p查询排序p查询统计Oracle 数据库基础教程2007无条件查询无条件查询p查询所有列nselect * from dept;p查询指定列nselect deptno,dname from dept;nselect empno,sal

7、*0.18 from emp;p 使用算术表达式nSelect empno,sal*0.8 from emp;p使用字符常量nSelect empno,Name is:, ename from emp;p使用函数nSelect empno,UPPER（ ename） from emp;Oracle 数据库基础教程2007p改变列标题nSelect empno employeename,sal AS salary , job,dept from emp;p使用连接字符串nSelect ename | : | 1 | month salary= | sal as monthy from empp消

8、除重复行nSQL select ALL ename from emp;nSQL select DISTINCT ename from emp;Oracle 数据库基础教程2007有条件查询有条件查询p查询满足条件的元组可以通过WHERE 子句实现。pWHERE条件中常用的运算符号运算符号谓词比较大小=, , =, =, ，!=确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，OROracle 数据库基础教程2007p关系运算 nselect empno,ename,

9、sal from emp where deptno = 10;nSelect empno ,ename ,sal from emp where sal1500p确定范围n谓词谓词BETWEEN AND与与NOT BETWEEN AND。n select empno,ename,sal ,deptno from emp where deptno between 10 and 20nselect empno,ename,sal ,deptno from emp where sal not between 1000 and 2000Oracle 数据库基础教程2007p确定集合n谓词谓词IN可以用来

10、查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。nselect empno,ename,sal,deptno from emp where deptno in (10,30);p字符匹配n%（百分号）代表任意长（长度为（百分号）代表任意长（长度为0）字符串。）字符串。n_（下划线）代表任意单个字符。（下划线）代表任意单个字符。nESCAPE：转义字符：转义字符nselect empno,ename,sal,deptno from emp where ename like S%;Oracle 数据库基础教程2007p空值操作n涉及空值查询时使用涉及空值查询时使用IS NULL

11、或或 IS NOT NULL，这里的，这里的IS不能用不能用=替代。替代。nselect empno,ename,sal,deptno from emp where comm is not nullnselect empno,ename,sal,deptno from emp where comm is nullOracle 数据库基础教程2007p逻辑操作n用逻辑运算符用逻辑运算符NOT、AND和和OR来联结多个查询条件。来联结多个查询条件。n优先级：优先级：NOT、AND、OR（用户可以用括号改变优先（用户可以用括号改变优先级）。级）。 nIN谓词实际上是多个谓词实际上是多个OR运算的缩写

12、。运算的缩写。 nselect empno,ename,sal,deptno from emp where comm is not null and sal 1500;n select ename, job, sal from emp where ( job=SALESMAN or job= CLERK) and sal1500;Oracle 数据库基础教程2007查询排序查询排序p升序、降序排序p多列排序p表达式排序p使用别名排序p使用列位置编号排序Oracle 数据库基础教程2007p升序、降序排序nASC: 升序升序 （缺省）；（缺省）；DESC: 降序降序nSELECT empno,e

13、name,sal FROM emp ORDER BY sal;nSELECT empno,ename,sal FROM emp ORDER BY sal DESC; p多列排序 n首先按照第一个列或表达式进行排序；当第一个列或表达式的数首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类推据相同时，以第二个列或表达式进行排序，以此类推 。nSELECT * FROM emp ORDER BY deptno,sal DESC; Oracle 数据库基础教程2007p按表达式排序 n可以按特定的表达式进行排序。可以按特定的表达式进行排序。 nSELE

14、CT empno,ename,sal FROM emp ORDER BY sal*12; p使用别名排序 n可以使用目标列或表达式的别名进行排序。可以使用目标列或表达式的别名进行排序。：nSELECT empno,sal*12 salary FROM emp ORDER BY salary; p使用列位置编号排序 n如果列名或表达式名称很长，那么使用位置排序可以缩短排序语如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。句的长度。nSELECT empno,sal*12 salary FROM emp ORDER BY 2; Oracle 数据库基础教程2007查询统计查询统计

15、函数格式功能AVGAVG(DISTINCT|ALL )计算一列值的平均值（要求数值列）COUNTCOUNT(DISTINCT|ALL * )统计元组个数COUNTCOUNT(DISTINCT|ALL )统计一列中 非空值的个数MAXMAX(DISTINCT|ALL )求一列值中的最大值MINMIN(DISTINCT|ALL )求一列值中的最小值SUMSUM(DISTINCT|ALL )计算一列值的总和（要求数值列）STDDEVSTDDEV().计算一列值的标准差VARIANCEVARIANCE()计算一列值的方差Oracle 数据库基础教程2007p注意n除了除了COUNT(*)函数外，其他的

16、统计函数都不考虑返函数外，其他的统计函数都不考虑返回值或表达式为回值或表达式为NULL的情况。的情况。n聚集函数只能出现在目标列表达式、聚集函数只能出现在目标列表达式、ORDER BY子句、子句、HAVING子句中，不能出现在子句中，不能出现在WHERE子句和子句和GROUP BY子句中。子句中。n默认对所有的返回行进行统计，包括重复的行；如果默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用要统计不重复的行信息，则可以使用DISTINCT选项。选项。n如果对查询结果进行了分组，则聚集函数的作用范围如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数

17、作用于整个查询结果。为各个组，否则聚集函数作用于整个查询结果。Oracle 数据库基础教程2007pSELECT count(*),avg(sal),max(sal),min(sal) FROM emp WHERE deptno=10; pSELECT avg(comm),sum(comm) FROM emp ; pSELECT count(DISTINCT deptno) FROM emp; pSELECT variance(sal),stddev(sal) FROM emp; Oracle 数据库基础教程200714.2.3分组查询分组查询p基本语法p单列分组查询p多列分组查询p使用HAV

18、ING子句限制返回组p使用ROLLUP 和CUBEp合并分组查询Oracle 数据库基础教程2007 DEPTNO SAL- - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667将查询结果表按某一列或多列值分组，值相等的为一组。分组查询分组查询Oracle 数据库基础教程2007p语法语法nSELECT column,

19、group_function, nFROM tablenWHERE conditionnGROUP BY ROOLUP|CUBE|GROUPING SETS group_by_expressionnHAVING group_conditionnORDER BY columnASC|DESC; Oracle 数据库基础教程2007pGROUP BY子句用于指定分组列或分组表达式。p集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。pHAVING子句用于限制分组的返回结果。pWHERE子句是对

20、表中的记录进行过滤，而HAVING子句是对分组后形成的组进行过滤。p在分组查询中，SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是集合函数。 Oracle 数据库基础教程2007p单列分组查询n将查询出来的记录按照某一个指定的列进行分组将查询出来的记录按照某一个指定的列进行分组nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno; p多列分组查询n在在GROUP BY子句中指定了两个或多个分组列子句中指定了两个或多个分组列nSELECT deptno,job,count(*),avg(sal) FR

21、OM emp GROUP BY deptno,job; Oracle 数据库基础教程2007p使用HAVING子句限制返回组n可以使用可以使用HAVING子句，只有满足条件的组才会返回。子句，只有满足条件的组才会返回。nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)1500; p使用ROLLUP 和CUBEn如果在如果在GROUP BY子句中使用子句中使用ROLLUP选项，则还选项，则还可以生成横向统计和不分组统计；可以生成横向统计和不分组统计；n如果在如果在GROUP BY子句中使用子句中使用C

22、UBE选项，则还可以选项，则还可以生成横向统计、纵向统计和不分组统计。生成横向统计、纵向统计和不分组统计。 Oracle 数据库基础教程2007nSELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job);nSELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job); p合并分组查询n使用使用GROUPING SETS可以将几个单独的分组查询可以将几个单独的分组查询合并成一个分组查询合并成一个分组查询 nSELECT deptno,job,avg(sal) FRO

23、M emp GROUP BY GROUPING SETS(deptno,job); Oracle 数据库基础教程200714.2.4连接查询连接查询p交叉连接p内连接n等值连接等值连接n不等值连接不等值连接n自身连接自身连接p外连接n左外连接左外连接n右外连接右外连接n全外连接全外连接Oracle 数据库基础教程2007交叉连接交叉连接p以下情况可以出现交叉连接n连接条件省略连接条件省略n连接条件非法连接条件非法n一个表的所有行被连接到另一个表的所有行一个表的所有行被连接到另一个表的所有行p为了避免笛卡尔积，一定要在为了避免笛卡尔积，一定要在WHERE 从句中包从句中包含合法的连接条件。含合法

24、的连接条件。pSELECT ename,dname from emp,dept;Oracle 数据库基础教程2007内连接内连接p执行过程 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表1中的第2个元组，然后再从头扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组。重复执行，直到表1中的全部元组都处理完毕为止。Oracle 数据库基础教程2007p内连接语法内连接语法nSELECT FROM tab1 INNER JOIN ta

25、b2 ON conditionnSELECT FROM tab1,tab2 WHERE conditionOracle 数据库基础教程2007p相等内连接 nSELECT empno,ename,sal,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=10 AND emp.deptno=dept.deptno; nSELECT empno,ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno=10 AND emp.deptno=dept.deptno; Oracle 数据库基础教程

26、2007p自身内连接 nSELECT work.empno,work.ename,manager.empno,manager.enameFROM emp work JOIN emp manager ON work.mgr=manager.empno;nSELECT work.empno,work.ename,manager.empno,manager.ename FROM emp work,emp manager WHERE work.mgr=manager.empno;Oracle 数据库基础教程2007p不相等内连接nSELECT empno,ename,sal,grade FROM em

27、p JOIN salgrade ON sallosal AND sallosal AND sal(SELECT sal FROM emp WHERE empno=7934) p多行单列子查询n运算符号运算符号nSELECT empno,ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=10) Oracle 数据库基础教程2007运 算 符含 义IN与子查询返回结果中任何一个值相等NOT IN与子查询返回结果中任何一个值都不等ANY比子查询返回结果中某一个值大=ANY与子查询返回结果中某一个值相等ALL比子查询返回

28、结果中所有值都大2000ORDER BY deptno;Oracle 数据库基础教程2007pINTERSECTnSELECT empno,ename,sal,deptno FROM emp WHERE deptno=30 INTERSECT SELECT empno,ename,sal,deptno FROM EMP WHERE sal2000;pMINUSnSELECT empno,ename,job FROM emp WHERE deptno=30 MINUS SELECT empno,ename,job FROM EMP WHERE job=SALESMAN Oracle 数据库基础教

29、程200714.3数据操纵数据操纵p插入数据p修改数据p删除数据Oracle 数据库基础教程2007插入数据插入数据p插入单行记录p利用子查询插入数据p向多个表中插入数据 Oracle 数据库基础教程2007p插入单行记录n语法语法pINSERT INTO table_name|view_name (column1,column2) VALUES(value1,values, ) n注意注意p如果在INTO子句中没有指明任何列名，则VALUES子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。p如果在INTO子句中指定了列名，则VALUES子句中提供的列值的个数、顺序、类型必须

30、与指定列的个数、顺序、类型按位置对应。p向表或视图中插入的数据必须满足表的完整性约束。p字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用TO_DATE函数进行日期转换。 n示例示例pINSERT INTO emp(empno,ename,sal,hiredate) VALUES(1234,JOAN,2500,20-4月-2007);Oracle 数据库基础教程2007p利用子查询插入数据n语法语法pINSERT INTO table_name|view_name(column1,column2,) subquery;n示例示例pINSERT INTO emp_s

31、alary SELECT deptno,max(sal),min(sal) FROM emp GROUP BY deptno Oracle 数据库基础教程2007p向多个表中插入数据n语法语法pINSERT ALL|FIRST WHEN condition1 THEN INTO table1 WHEN condition2 THEN INTO table2 ELSE INTO tablen subquery;pALL表示一条记录可以同时插入到多个满足条件的表中pFIRST表示一条记录只会插入到第一个满足条件的表中Oracle 数据库基础教程2007p示例nINSERT FIRST WHEN d

32、eptno=10 THEN INTO emp10 WHEN deptno=20 THEN INTO emp20 WHEN deptno=30 THEN INTO emp30 ELSE INTO emp_other SELECT * FROM emp;Oracle 数据库基础教程2007p示例nINSERT ALL WHEN deptno=10 THEN INTO emp10 WHEN deptno=20 THEN INTO emp20 WHEN deptno=30 THEN INTO emp30 WHEN deptno=40 THEN INTO emp_other WHEN sal4000 T

33、HEN INTO highsal ELSE INTO middlesal SELECT * FROM emp;Oracle 数据库基础教程200714.3.2修改数据修改数据p基本语法p修改单行记录p修改多行记录p带有子查询的修改Oracle 数据库基础教程2007p基本语法nUPDATE table_name|view_name SET column1=value1,column2=value2 WHERE conditionp修改单条记录nUPDATE emp SET sal=sal+100,comm=200 WHERE empno=7844;p修改多条记录nUPDATE emp SET

34、sal=sal+150 WHERE deptno=20; p利用自查询修改记录nUPDATE emp SET sal=300+ (SELECT avg(sal) FROM emp WHERE deptno=10) WHERE deptno=30; Oracle 数据库基础教程200714.3.3删除数据删除数据p基本语法p删除单条记录p删除多条记录p带有子查询的删除操作p利用TRUNCATE删除数据pTRUNCATE与DELETE区别Oracle 数据库基础教程2007p基本语法n DELETE FROM table|view WHERE condition p删除单条记录nDELETE FR

35、OM emp WHERE empno=7844; p删除多条记录nDELETE FROM emp WHERE deptno=10;p带有子查询的删除操作nDELETE FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7900); Oracle 数据库基础教程2007p利用TRUNCATE删除数据n语法语法pTRUNCATE TABLE table_namepTRUNCATE与DELETE区别n释放存储空间释放存储空间n不写入日志文件，因此执行效率较高，但该操作不可不写入日志文件，因此执行效率较高，但该操作不可回滚。回滚。 Oracle 数据

36、库基础教程200714.4事务控制事务控制p事务概述pOracle事务的隔离级别 pOracle事务处理Oracle 数据库基础教程200714.4.1事务概述事务概述p原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。p一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。p隔离性（Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。p持久性（Durability）：一个事务一旦提交，则对数据库中数据的改变是永久

37、性的，以后的操作或故障不会对事务的操作结果产生任何影响。 Oracle 数据库基础教程200714.4.2Oracle事务的隔离级别事务的隔离级别 pREAD COMMITED（提交读）pSERIALIZABLE（串行化）nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;nALTER SESSION SET ISOLATION_LEVEL =SERIALIZABLE;nALTER SESSION SET ISOLATION_LEVEL =READ COMMI

38、TTED Oracle 数据库基础教程200714.4.3Oracle事务处理事务处理 p事务提交n用户显式执行用户显式执行COMMIT命令命令n执行特定操作时系统自动提交。执行特定操作时系统自动提交。 p事务回滚n事务全部回滚事务全部回滚 ：ROLLBACKn事务部分回滚事务部分回滚pSAVEPOINT XpROLLBACK TO XOracle 数据库基础教程2007 ROLLBACK ROLLBACK TO A ROLLBACK TO B 事务 INSERT INSERT UPDATE INSERT SAVEPOINT A SAVEPOINT B COMMIT Oracle 数据库基础教

39、程200714.5SQL函数函数pSQL函数分类p数值函数p字符函数p日期函数p转换函数p其他函数Oracle 数据库基础教程2007数值函数数值函数函数返回值ABS(n)返回n的绝对值CELL(n)返回大于或等于n的最小整数EXP(n)返回e的n次幂FLOOR(n)返回小于或等于n的最大整数LN(n)返回以E为底的n的对数LOG(m,n)返回以m为底的n的对数MOD(m,n)返回m除以n的余数PI( )返回常数3.1415926535897931POWER(m,n)返回m的n次方Oracle 数据库基础教程2007ROUND(m,n)对m进行四舍五入（n大于0时，将m四舍五入到小数点右边n位

40、，n等于零时，表示对m进行取整，n小于0时，则小数点左边的数字位置被圆整。SIGN(n)判断n的正负（n大于0返回，n等于0返回0，n小于0返回-1SQRT(n)返回n的平方根SQUARE(n)返回n的平方TRUNC(m,n)对m进行截断操作（n截断到小数点后第n位，如果n未给出，则系统默认为0,n也可以为负数，表示小数点左边的数字位置被删除成零。Oracle 数据库基础教程2007字符函数字符函数函数返回值ASCII(char)字符串首字符的ASC码值CHR(n)ASC码值为n的字符CONCAT把两个列值拼接起来；“|”操作符更通用INITCAP(char)将字符串中每个单词的首字母大写INSTR（char1,char2,a,b)INSTRB（char1,char2,a,b)返回指定字符的位置LENGTH(char)LENGTHB(char)计算字符串的长度SUBSTR(char,m,n)求子串。column中从起始位置m开始长度为n的子串Replace(str1,str2,str3)Oracle 数据库基础教程2007LOWER(char)、UPPER(char)将字符串中所有的大写字母变为小(大)写LPAD(char1,n