《OracleSQL入门》PPT课件.ppt

1、SQL 入门,目标,学习完本课之后，你将掌握以下内容: Oracle数据类型和单行函数 简单查询和复杂查询 DML、DDL语句,ORACLE SQL,常用Oracle 数据类型：,ORACLE SQL,常用Oracle 数据类型：,ORACLE SQL,常用Oracle 数据类型：,ORACLE SQL,SQL语句种类： DQL-Data Query Language Select 语句 DML-Data Manipulation Language Insert、Update、Delete语句 DDL- Data Definition Language Create、Alter、Drop、Re

2、name、Truncate语句,ORACLE SQL,SQL语句种类： DCL-Data Control Language Grant、Revoke语句 TCL-Transaction Control Commit、Rollback、Savepoint语句,ORACLE SQL,DQL Statement(select)： SQL操作符 LIKE 匹配字符串中的模式 模式由普通字符和”%”、”_”通配符组成 例： select * from scott.emp where ename like “%s_%” escape 以下名字是否匹配？ scott ascott sys_system,OR

3、ACLE SQL,DQL Statement(select)： SQL操作符 IN 匹配值列表 下面语句将返回什么结果？ 1. select * from emp where deptno in (10,20,30) 2. select * from scott.emp where deptno not in (10,20,30) 3. select * from scott.emp where deptno not in (20,10,NULL),ORACLE SQL,DQL Statement(select)： SQL操作符 BETWEEN 匹配值范围 下面语句将返回什么结果？ selec

4、t * from scott.emp where sal between 1100 and 1600,ORACLE SQL,DQL Statement(select)： SQL操作符 IS NULL 匹配NULL值 IS NAN 匹配NaN这个特殊值，意思是非数字 IS INFINITE 匹配无穷binary_float和binary_double值,ORACLE SQL,DQL Statement(select)： 逻辑运算符 NOT NOT x, x为false则返回true，否则反之 AND x AND y,当x和y都为true时返回ture OR x OR y,当x和y中有一个为tru

5、e时返回ture 优先级：NOT AND OR,ORACLE SQL,DQL Statement(select)： 逻辑运算符 以下语句返回什么值？ select * from scott.emp where hiredate 20-2月-81 or sal 3000 and empno = 7782,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 内连接 连接的多表必须有公共列(相等连接) 通过INNER JOIN语法实现 语法： SELECT tab_name1.col_name, tab_name2.col_name. FROM tab_na

6、me1 INNER JOIN tab_name2 ON tab_name1.col = tab_name2.col 其中col是两表的公共列,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 内连接 以下语句输出内容： select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 内连接 上面语句等同于(SQL 86语法)： select e.ename, d.dnam

7、e from emp e , dept d where e.deptno = d.deptno,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 以下语句输出结果： select e.ename, d.dname, s.grade from emp e inner join dept d on e.deptno = d.deptno inner join salgrade s on e.sal between s.losal and s.hisal,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 外连接

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

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

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

11、回不满足连接条件的所有行 语法： SELECT tab_name1.col_name, tab_name2.col_name. FROM tab_name1 FULL JOIN tab_name2 ON tab_name1.col = tab_name2.col,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 外连接 3.完全外连接 Select d.dname, e.ename from dept d full join emp e on d.deptno = e.deptno and d.deptno = 10; 结果？,ORACLE SQL,

12、DQL Statement(select)： 多表查询(SQL 99) 外连接 3.完全外连接,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 自然连接 同一张表之间的连接查询 主要用于在参照表上显示上下级关系或层次关系 语法： select t1.col_name, t2.col_name from table1 t1 inner join table1 t2 on t1.col_name = t2.col_name,ORACLE SQL,DQL Statement(select)： 多表查询(SQL 99) 自然连接 预测以下语句结果： SEL

13、ECT M.ENAME FROM EMP M INNER JOIN EMP W ON M.EMPNO = W.MGR AND W.ENAME=BLAKE;,ORACLE SQL,DQL Statement(select)： 函数 单行函数 只能对一行进行操作，对输入的每一行返回一个结果 聚合函数 可以对多行操作，并返回一行结果,ORACLE SQL,DQL Statement(select)： 单行函数 字符函数 对字符串进行操作 数字函数 对数字进行计算 转换函数 将一种数据类型转换成另一种数据类型 日期函数 对日期和时间进行处理,ORACLE SQL,DQL Statement(selec

14、t)： 单行函数 正则表达式函数 使用正则表达式查询,ORACLE SQL,DQL Statement(select)： 单行函数 字符函数(c是被操作的字符串) ASCII(c) CHR(c) CONCAT(c) INSTR(c, find_string ,start ,occurrence) LENGTH(c) LOWER(c),UPPER(c) NVL(c,value) NVL2(c,value1,value2) REPLACE(c, search_string, replace_string) SUBSTRING(c, start , length) TRIM(trim_string,

15、 c),ORACLE SQL,DQL Statement(select)： 单行函数 select ASCII(a), CHR(97) from dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select instr(morning, n), instr(morning, n, -1) from dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select substr(morning,1,3), substr(morning,-4,3) from dual;,ORACLE SQL,DQL Statement

16、(select)： 单行函数 select concat(hello, world) from dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select concat(hello, world) from dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select hello | world from dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select length(helloworld), replace(helloworld,hello,Hi ) f

17、rom dual;,ORACLE SQL,DQL Statement(select)： 单行函数 select trim( good ) | morning, ltrim(morning,m) from dual;,ORACLE SQL,DQL Statement(select)： 日期函数(d为特定日期) SYSDATE ADD_MONTHS(d, 2) LAST_DAY(d) MONTHS_BETWEEN(d1,d2) NEXT_DAY(d,char) CURRENT_DATE,ORACLE SQL,DQL Statement(select)： 日期函数 查看和设置默认日期格式 selec

18、t * from nls_session_parameters where parameter = NLS_DATE_LANGUAGE 设置格式： 1.修改当前会话的日期格式 alter session set nls_date_language = AMERICAN; (改为美国语言格式，如20-JUL-06) alter session set nls_date_language=SIMPLIFIED CHINESE (改为中国语言格式，如20-7月-06)(缺省格式),ORACLE SQL,DQL Statement(select)： 日期函数 2.修改系统日期格式 只需把上面语句的se

19、ssion改为system就可以了,ORACLE SQL,DQL Statement(select)： 日期函数,ORACLE SQL,DQL Statement(select)： 日期函数 select add_months(sysdate, 4) from dual;,ORACLE SQL,DQL Statement(select)： 日期函数 select last_day(sysdate) from dual;,ORACLE SQL,DQL Statement(select)： 日期函数 select months_between(sysdate, 30-5月-2006) from d

20、ual;,ORACLE SQL,DQL Statement(select)： 日期函数 select next_day(sysdate, 星期六) from dual;,ORACLE SQL,DQL Statement(select)： 日期函数 select to_char(sysdate,ymd) from dual;,ORACLE SQL,DQL Statement(select)： 数字函数(num为特定数字) ABS(num) CEIL(num) FLOOR(num) MOD(num1,num2) -num1%num2 POWER(num, y) -numy ROUND(num, s

21、cal) SIGN(num),ORACLE SQL,DQL Statement(select)： 数字函数(num为特定数字) TRUNC(num, y),ORACLE SQL,DQL Statement(select)： 数字函数 select abs(-10),mod(8,3), power(2,3) from dual;,ORACLE SQL,DQL Statement(select)： 数字函数 select round(5.75), round(5.75,1), round(5.75,-1) from dual;,ORACLE SQL,DQL Statement(select)： 数

22、字函数 select ceil(5.8), ceil(-5.2), floor(5.8), floor(-5.2) from dual;,ORACLE SQL,DQL Statement(select)： 数字函数 select round(5.75), round(5.75,1), round(5.75,-1) from dual;,ORACLE SQL,DQL Statement(select)： 数字函数 select sign(10), sign(-10), sign(0) from dual;,ORACLE SQL,DQL Statement(select)： 数字函数 select

23、 trunc(5.75), trunc(-5.75,1), trunc(5.75,-1) from dual,ORACLE SQL,DQL Statement(select)： 转换函数(x是被用于转换操作的值) CAST(x AS type_name) DECODE(x,search,result,default) TO_CHAR(x, format) TO_DATE(x, format) TO_XXX,ORACLE SQL,DQL Statement(select)： 转换函数 select to_char(12345.67) | 0 from dual;,ORACLE SQL,DQL S

24、tatement(select)： 转换函数 select to_char(123456.7, 99,9999.99) from dual;,ORACLE SQL,DQL Statement(select)： 转换函数 select to_char(123456.7, 99,9999.99) from dual;,ORACLE SQL,DQL Statement(select)： 转换函数 select to_char(sysdate,month dd, yyyy) from dual;,ORACLE SQL,DQL Statement(select)： 转换函数 select to_date

25、(7月 17, 2006, month dd, yyyy) from dual;,ORACLE SQL,DQL Statement(select)： 转换函数 select to_char(to_date(17-7月-2006 12:54:36, dd-month-yyyy hh12:mi:ss), hh12:mi:ss) from dual;,ORACLE SQL,DQL Statement(select)： 转换函数 DECODE(expr,search1, result,search2,result2,default) 类似于IFTHENELSE 如果expr匹配search1，返回re

26、sult；如果expr匹配search2，返回result2，依此类推。都不匹配则返回default值 Select deptno,ename,sal, Decode(deptno,10,sal*1.2,20,sal*1.5,sal) From emp order by deptno;,ORACLE SQL,DQL Statement： 聚合函数 AVG 计算某一列或者某一表达式的平均值 例：计算所有员工的平均工资 select avg(sal) from emp where depno=10;,ORACLE SQL,DQL Statement： 聚合函数 COUNT 计算一个查询所返回的函数

27、 例：计算所有员工的人数 select count(empno) from emp; 注意：还有一种格式count(*),和count(col_name)的区别在于，该格式函数会计算null值,ORACLE SQL,DQL Statement： 聚合函数 SUM 计算某一列的所有值的总和 例：计算所有员工的工资总额 select sum(sal) from emp;,ORACLE SQL,DQL Statement： 聚合函数 MAX和MIN 分别获取表达式或列的最大值和最小值 例：查看工资额最大的员工信息 select * from emp where sal = (select max(s

28、al) from emp),ORACLE SQL,DQL Statement： GROUP BY子句 对查询结果进行分组统计 如果在选择列表中同时包含列、表达式、集合函数，那么这些列和表达式必须出现在GROUP BY子句中 一般结合ORDER BY 和HAVING子句使用 单列分组和多列分组,ORACLE SQL,DQL Statement： GROUP BY子句 单列分组 基于列的每个不同值生成一个数据统计结果 例： 显示每个部门的平均工资和最高工资 select deptno,ename,avg(sal), max(sal) from emp group by deptno having deptno10;,ORACLE SQL,DQL Statement： GROUP BY子句 单列分组,ORACLE SQL,DQL Statement： GROUP BY子句 多列分组 基于两个或两个以上的列生成分组统计结果 基于多个列组合成的不同值分组 显示每个部门(deptno)每种职位(job)的平均工资和最高工资。 应该怎么编写该语句？,ORACLE SQL,DQL Statement： GROUP BY子句 Select deptno,job, avg(sal), max(sal) from emp group by deptno,job,ORACLE SQL,D