课件2 slq语言基础

1、SQL 语言基础1SQL 语言基础主要内容: SQL 语言简介 查询数据 维护数据 事务控制 SQL函数2SQL 语言基础SQL 语言简介*Structured English QUEry Language(被称为SEQUEL) SEQUEL语言后来简称SQL包括:DDL (Data Definition Language)DML (Data Manipulation Language)TC (Transaction Control)SC (Session Control),(System Control) 3SQL 语言基础查询数据查询数据是数据库的核心操作.查询数据是关系代数,关系演算在S

2、QL语言中的主要体现.查询数据时,即可以查询所有的列,也可以查询指定的列,所以必须要清楚表的结构及其所有列的概要.可以使用describe命令(简写为desc)来显示表结构.Oracle对SQL的重要改进之一就是增加了描述命令describe. 查看用户下所有的表:select * from tab;4SQL 语言基础查询数据 基本查询 分组查询 连接查询 合并查询 子查询5SQL 语言基础基本查询基本查询是针对一个表的查询,其基本语法是:SELECT *|column1 as bieming1,column2 as bieming2, FROM table_name WHERE condit

3、ion_expressionORDER BY column ASC|DESC6SQL 语言基础基本查询 查询所有列 查询指定列 取消重复行(DISTINCT) 使用表达式(|连接字符串,计算显示数据(+,-,*,/),TO_CHAR(date,yyyy_mm_dd)等) 使用WHERE子句* 使用ORDER BY 子句*7SQL 语言基础分组查询*数据分组是通过在SELECT语句中加入GROUP BY子句,组处理函数和HAVING子句来共同完成的.语法如下:SELECT col1 as bieming1,col2 as bieming2,aggregate_function FROM tabl

4、eWHERE condition_expressionGROUP BY group_by_expressionHAVING group_condition_expression8SQL 语言基础连接查询 笛卡儿连接* 相等连接(=) 不等连接(不用=) 自我连接(虚拟表连接的一种)9SQL 语言基础合并查询为了合并多个SELECT语句的查询结果,可以使用集合运算符(set operator)进行合并查询,其基本语法是SELECT 语句1UNION | UNION ALL | INTERSECT | MINUSSELECT 语句210SQL 语言基础合并查询UNION (两个结果集的并集,自动去

5、掉重复行) UNION ALL (并集,不去掉重复行)INTERSECT (交集)MINUS (差集)都会以第一列的结果进行排序,并且有的话只能有一个ORDER BY子句.在这个ORDER BY子句中可以使用列名或第一个查询中的列的别名.11SQL 语言基础子查询子查询(subquery)是指嵌入在其他SQL语句中的一个查询.子查询可以嵌套,嵌套子查询的求解次序是由里向外,即先处理子查询,然后将其查询的结果用于父查询.使用嵌套子查询的方法,可以用一系列简单的查询构成复杂的查询,从而明显增强SQL语句的功能,这正是SQL语言结构化(structure)的含义所在.12SQL 语言基础子查询 单行

6、子查询(single-row subqueries)使用的运算符号(=,=,=,) 多行子查询(multiple-row subqueries)使用的运算符号(in,not in,exists,not exits,all,any) 相关子查询(correlated subqueries)格式 select 列名,(select 语句) from 表名 标量子查询(scalar subqueries)子查询是返回单行单列,格式同上 多列子查询(multiple-column subqueries) 在DDL语句中使用子查询 在DML语句中使用子查询13ORDBMSEXISTS：与子查询结合使用检

7、查子查询所返回的行是否存在NOT EXISTS与其相反例：查询没有员工的部门14ORDBMSEXISTS：select * from dept where exists (select * from emp where dept.deptno = emp.deptno);15ORDBMSNOT EXISTS：显示没有员工的部门信息select * from dept where not exists (select 1 from emp where dept.deptno = emp.deptno); 16ORDBMSEXISTS 与 IN 的比较：EXISTS检查行的存在，而IN则检查实际值

8、的存在EXISTS性能比IN高当值列表包含一个NULL值时，NOT EXISTS返回true，而NOT IN返回false17ORDBMSEXISTS 与 IN 的比较：SQL select product_type_id, name 2 from product_types outer 3 where not exists 4 (select 1 from products inner where duct_type_id = duct_type_id);18ORDBMSEXISTS 与 IN 的比较：19ORDBMSEXISTS 与 IN 的比较：SQL

9、 select product_type_id, name 2 from product_types outer 3 where product_type_id not in 4 (select product_type_id from products);20ORDBMSEXISTS 与 IN 的比较：21ORACLE SQL高级查询 内连接 外连接 左外连接 右外连接 完全外连接 自然连接22ORACLE SQLDQL Statement(select)：多表查询(SQL 99)内连接连接的多表必须有公共列(相等连接)通过INNER JOIN语法实现语法：SELECT tab_name1.

10、col_name, tab_name2.col_name.FROM tab_name1 INNER JOIN tab_name2ON tab_name1.col = tab_name2.col其中col是两表的公共列23ORACLE SQLDQL Statement(select)：多表查询(SQL 99)内连接以下语句输出内容：select e.ename, d.dnamefrom emp e inner join dept don e.deptno = d.deptno24ORACLE SQLDQL Statement(select)：多表查询(SQL 99)内连接上面语句等同于(SQL

11、86语法)： select e.ename, d.dname from emp e , dept d where e.deptno = d.deptno 25ORACLE SQLDQL Statement(select)：多表查询(SQL 99)以下语句输出结果：select e.ename, d.dname, s.gradefrom emp e inner join dept don e.deptno = d.deptno inner join salgrade son e.sal between s.losal and s.hisal26ORACLE SQLDQL Statement(se

12、lect)：多表查询(SQL 99)外连接1.左外连接返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符的左边表的其他行语法： SELECT tab_name1.col_name, tab_name2.col_name.FROM tab_name1 LEFT JOIN tab_name2ON tab_name1.col = tab_name2.col27ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接1.左外连接Select d.dname, e.ename from dept d left join emp eon d.deptno

13、 = e.deptnoand d.deptno = 10;请问输出结果？28ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接1.左外连接29ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接2.右外连接返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符的左边表的其他行语法： SELECT tab_name1.col_name, tab_name2.col_name.FROM tab_name1 RIGHT JOIN tab_name2ON tab_name1.col = tab_name2.

14、col30ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接2.右外连接Select d.dname, e.ename from dept d right join emp eon d.deptno = e.deptnoand d.deptno = 10;输出结果？31ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接2.右外连接32ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接3.完全外连接不仅返回满足连接条件的所有行，而且还会返回不满足连接条件的所有行语法

15、： SELECT tab_name1.col_name, tab_name2.col_name.FROM tab_name1 FULL JOIN tab_name2ON tab_name1.col = tab_name2.col33ORACLE SQLDQL Statement(select)：多表查询(SQL 99)外连接3.完全外连接Select d.dname, e.ename from dept d full join emp eon d.deptno = e.deptnoand d.deptno = 10;结果？34ORACLE SQLDQL Statement(select)：多表

16、查询(SQL 99)外连接3.完全外连接35ORACLE SQLDQL Statement(select)：多表查询(SQL 99)自然连接同一张表之间的连接查询主要用于在参照表上显示上下级关系或层次关系语法：select t1.col_name, t2.col_namefrom table1 t1 inner join table1 t2on t1.col_name = t2.col_name36ORACLE SQLDQL Statement(select)：多表查询(SQL 99)自然连接预测以下语句结果：SELECT M.ENAME FROM EMP M INNER JOIN EMP W

17、ON M.EMPNO = W.MGRAND W.ENAME=BLAKE;37SQL 语言基础维护数据本节介绍有关数据维护的内容,包括如何插入数据,更改数据和删除数据.DML是SQL的一个子集,用于维护表或视图的数据,基本语句及功能如下: INSERT 向表或视图中添加新的数据行 UPDATE 更改表或视图中已经存在的数据 DELETE 删除表中的整行数据 SELECT FOR UPDATE 防止其他会话对选定的行执行DML 38SQL 语言基础事务控制: 事务的概念 事务的性质 原子性(atomicity) 一致性(consistency) 隔离性(isolation) 持久性(durabil

18、ity) 基本语句及功能 设置事务(set transaction) 提交事务(commit) 设置保留点(savepoint) 回退全部事务(rollback)/回退部分事务(rollback)39SQL 语言基础SQL函数 单行数学函数 单行字符函数 单行日期时间函数 单行转换函数 其他函数DUAL表是虚拟的数据库表40SQL 语言基础单行数学函数: ABS(n) 返回n的绝对值 ACOS(n) 返回n的反余弦值 ASIN(n) 返回n的反正弦值 ATAN(n) 返回n的反正切值 ATAN2(n,m) 返回数字n除以m的反正切值,m不能为0 CEIL(n) 返回大于等于n的最小整数 COS

19、(n) 返回n的余弦值 COSH(n) 返回n的双曲余弦值 EXP(n) 返回e的n次幂(e=2.71828183)41SQL 语言基础单行数学函数: FLOOR(n) 返回小于等于n的最大整数 LN(n) 返回n的自然对数,n 不能为0 LOG(m,n) 返回以m为底的n的对数,m不能为0 MOD(m,n) 返回m 除以n 之后的余数,如果n为0,则返回m POWER(m,n) 返回m 为底的n次幂.m和n可以为任意数字,但如果m为负数,则n必须为正数 ROUND(n,m) 执行四舍五入运算.如果省略m,则四舍五入到整数位;如果m是负数,则到小数点前m位;如果m 是正数,则到小数点后m位 S

20、IGN(n) 检测n的正负.如果n小于0,则返回-1;如果n 等于0,则返回0;如果n大于0,则返回1 42SQL 语言基础单行数学函数: SIN(n) 返回n的正弦值 SINH(n) 返回n的双曲正弦值 SQRT(n) 返回n的平方根,n 必须大于0 TAN(n) 返回n的正切值 TANH(n) 返回n的双曲正切值 TRUNC(n,m) 执行截取数字.如果省略m,则将n的小数部分截取;如果m是负数,则截取到小数点前m位;如果m是正数,则截取到小数点后m位43SQL 语言基础单行字符函数ASCII(c) 返回c的首字符在ASCII码中对应的十进制数 CHR(n) 返回十进制ASCII码n对应的

21、字符 CONCAT(c1,c2) 返回将c2添加到c1后面而形成的字符串.如果c1是NULL,那么返回c2;如果c2是NULL,那么返回c1;如果c1,c2都是NULL,那么返回NULLINITCAP(c) 返回将c的每个首字符都大写,其他字符都小写的字符串.单词之间以空格,控制字符和标点符号分界INSTR(c1,c2,n,m) 在c1中从n开始搜索c2第m次出现的位置,并返回该位置数字.如果n是负数,则搜索从右向左进行,但位置数字仍然从左向右计算.n和m默认都是1LOWER(c) 返回将c全部字符都小写的字符串44SQL 语言基础单行字符函数LENGTH(c) 返回c的长度,包括所有的后缀空

22、格.如果c是NULL,则返回NULLLPAD(c1,n,c2) 在c1的左边填充c2,直到字符串的总长度到达n.c2的默认值为空格.如果c1的长度大于n,则返回c1左边的n个字符LTRIM(c1,c2) 去掉c1左边所包含的c2中的任何字符,当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格RPAD(c1,n,c2) 在c1的右边填充c2,直到字符串的总长度到达n.c2的默认值为空格.如果c1的长度大于n,则返回c1右边的n个字符RTRIM(c1 ,c2) 去掉c1右边所包含的c2中的任何字符,当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格45SQL 语言基

23、础单行字符函数REPLACE(c1,c2 ,c3) 把c1中出现的c2都替换成c3,然后返回剩余的字符串.c3默认为NULL.如果c3为NULL,那么所有出现c2的字符都被删除;如果c2为NULL,则将返回c1;如果c1为NULL,则将返回NULLSUBSTR(c,m,n) 返回c的子串,其中m是子串开始的位置,n是子串的长度.如果m为0,则从c的首字符开始;如果m是负数,则从c的结尾开始.TRANSLATE(c1,c2,c3) 把所有在c2中出现的字符,用对应在c3中出现的字符代替,然后返回被替代之后的c1字符串.如果c1,c2,c3中有NULL,则返回NULL;如果c3中字符少于c2,则将

24、在c2中不匹配的字符从c1中删除;如果c2中的字符少于c3,则将在c3中不匹配的字符仍然保留在c1中46SQL 语言基础单行字符函数TRIM(c1 c2 FROM c3) c1是保留字,可以取如下字符串:LEADING,TRAILING,BOTH.从c3字符串的c1处开始,删除c2字符,然后返回剩余的c3字符串.c1默认为BOTH,c2默认为空格.如果c1,c2,c3中有任何一个为NULL,则返回NULLUPPER(c) 返回将c全部字符串都大写的字符串47SQL 语言基础单行日期时间函数ADD_MONTHS(d,n) 返回日期时间d加n月所对应的日期时间.n为正,则表示d之后;n为负,则表示

25、d 之前;n为小数,则自动先删除小数部分(如 3.9 会变成3)CURRENT_DATE 返回当前会话时区所对应的日期CURRENT_TIMESTAMP(p) 返回当前会话时区所对应的日期时间.P为精度,可以是0-9之间的一个整数,默认为6DBTIMEZONE 返回数据库所在的时区EXTRACT(c,FROM d) 返回日期时间d中指定的部分c. c的取值为YEAR,MONTH,DAY,MIN,SECOND,TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_REGION,TIMEZONE_ABBR.指定的c必须在d中存在48SQL 语言基础单行日期时间函数LAST_

26、DAY(d) 返回日期d所在月份的最后一天 LOCALTIMESTAMP(p) 返回当前会话时区的日期时间.P为精度,可以是0-9之间的一个整数,默认为6.与CURRENT_TIMESTAMP在返回值的数据类型上有所区别MONTHS_BETWEEN(d1,d2) 返回日期d1和d2之间相差的月数.如果d1小于d2,则返回负数;如果d1和d2的天数相同或都是月底,则返回整数;否则Oracle以每月31天为准来计算结果的小数部分NEXT_DAY(d,c) 返回日期d后的下一个c.c是一个字符串,表示用当前会话语言的一周的某一天的全称(如,星期一,星期二等)SYSTIMESTAMP 返回当前数据库的

27、一个TIMESTAMP WITH TIME ZONE 类型的日期时间49SQL 语言基础单行日期时间函数TRUNC(d , fmt) 返回根据fmt指定的精度截断后的日期d.其中,d是一个日期,fmt是一个指定日期精度的字符串.如果fmt为YEAR,则为本年的1月1日;如果fmt为MONTH,则为本月的1日;如果fmt为DAY,则天的各位为0(只有个位时不变)TZ_OFFSET(tz) 返回时区名tz指定的时区与格林尼治相比的时区偏差50SQL 语言基础单行日期时间函数LAST_DAY(d) 返回日期d所在月份的最后一天 LOCALTIMESTAMP(p) 返回当前会话时区的日期时间.P为精度,可以是0-9之间的一个整数,默认为6.与CURRENT_TIMESTAMP在返回值的数据类型上有所区别MONTHS_BETWEEN(d1,d2) 返回日期d1和d2之间相差的月数.如果d1小于d2,则返回负数;如果d1和d2的天数相同或都是月底,则返回整数;否则Oracle以每月31天为准来计算结果的小数部分NEXT_DAY(