Oracle 笔记（二）

Oracle的sql语言：

Sql全称：struct query language 结构化查询语言

五大类：

DDL：数据定义语言 create alter drop

DQL：数据查询语言select

DML：数据操作语言 insert update delete

DCL：数据控制语言 grant revoke

TCL(TPL)：事务控制语言（DML可以操作） savepoint commit rollback to

事务保存点：savepoint 保存点的名

事务回滚：rollback to 保存点的名

事务手动提交： commit

DML语句提交的情况（DQL不会造成自动提交）：

l 一条显式的commit语句

l 一条DDL语句或者DCL语句（相当于commit）

l 用户退出第三方工具SQL Plus（相当于rollback）和PLSQL developer（相当于commit）

l 第三方工具崩溃（相当于rollback）

l 系统崩溃（相当于rollback）

案例账户：scott

知识点一：DQL基本内容

查询员工名称和工资？	select ename,sal from emp; =, >, < ,>=, <=,!=或者<>
查询奖金为空的员工信息？	select * from emp where comm isnull;
查询首字母为大写’S’的员工姓名的信息？-模糊查询	--查询首字母为大写’S’的员工姓名的信息 selectfrom emp where ename like'S%' selectfrom emp where ename notlike'_C%' ‘_’代表1个字符，’%’代表0到多个
查询所有job为经理或办事员的员工信息？	select * from emp where job = 'CLERK' or job ='MANAGER'; and，or 注意：由于and操作的优先级大于or，所以务必加上() select * from emp where job in ('CLERK','ANALYST','MANAGER'); select*from emp where job not in('CLERK','ANALYST','MANAGER')
按照参加工作的日期，降序排列员工信息？	select * from emp order by hiredate desc； select ename,hiredate from emp order by hiredate；-默认是升序asc select*from emp orderby deptno,sal desc
查询emp表前三个员工的员工姓名	--非通用SQL表示方法，而是Oracle数据库特有的表示方法 rownum select * from emp where rownum <=3;
利用现有的表创建一张新表做现有表的备份表？并利用备份后的表完成查询工资前三名的员工	createtable empsal as selectfrom emp orderby sal desc selectfrom empsal where rownum <=3 selectfrom(selectfrom emp orderby sal desc) where rownum <=3
选择无重复行job,deptno?	selectdistinct job,deptno from emp orderby deptno;
如何对查询列启别名?	select ename 员工姓名,sal as 员工工资 from emp;

语法：

Select *|列名（字段名）

From 表名

Where 条件【or|in|like】

Group by 分组 Having 分组条件

Order by 排序desc asc

知识点二：DML语句：insert update delete语句 - 数据库的更新

2-1、语法 insert into 表名(字段名1,字段名2) values (值1，值2) //to_date(‘日期字符串’,’格式’)

技巧：批量插入

--将岗位不是‘MANAGER’和‘CLERK’的员工信息插入到备用表emp_bak中

insertinto emp_bak

select*from emp where job notin('CLERK','MANAGER')

2-2、更新语法：update 表名 set 字段名1=值1，字段名2=值2 where 条件

--update 表名 set 列名1 = 值1,列名2=值2，……where 列名=筛选值

update emp_bak set sal =2000,comm=500where ename='ALLEN';

技巧：批量更新

--10号部门与allen的工资和奖金平齐

update emp_bak

set(sal,comm)=(select sal,comm from emp_bak where ename='ALLEN')

where deptno =10

--步骤1：得到结果2000,500

select sal,comm from emp_bak where ename='ALLEN'

--步骤2：将2000,500放入括号中

update emp_bak

set(sal,comm)=(2000,500)

where deptno =10

2-3、删除语法：delete from 表 where 条件

--删除10号部门

delete from emp_bak where deptno = 10;

delete from emp_bak;和truncate table emp_bak;-区别？著名的面试题

知识点三：Oracle中的预定义(内置)函数 - 效率高

函数的分类：

单值函数：有且只有返回唯一的一个值的函数

|- 字符函数：处理字符串

|- 数学函数：处理数字

|- 日期函数：时间进行处理

|- 转换函数：类型间转换

|- 混合函数：特定功能

分组函数：类似聚合函数count,sum,avg

1：字符函数

问题1: 把员工姓名和工作类型连接在一起，中间用“-”分割显示

函数：字符串连接函数 concat

语法：concat(字符串1,字符串2)

--查询员工的姓名和岗位并显示为ename-job的格式

select concat(concat(ename,'-'),job)from emp

--查询员工的姓名和岗位并显示为ename-job的格式

select ename||'-'||job from emp

问题2：让员工姓名右对齐显示

函数：左填充函数 lpad

语法：lpad(待处理的字符串,预留位置大小,填充符号)

--查询员工的姓名并显示为右对齐

select lpad(ename,10,' ')from emp

问题3：截取员工姓名的前3个字符和字符4以后的内容显示

函数：字符串截取函数 substr

语法：substr(待处理的字符串,截取的起始位置,截取的个数) AAABBCCDDEFFFFFFFFFFF......

注意：Oracle的下标是从1开始

--查询员工的姓名并显示名字前三个字符……和字符四以后

select substr(ename,1,3)||'……'||substr(ename,4)from emp

问题4：员工姓名包含字母'T'的员工信息

函数：字符定位函数 instr

语法：instr(待处理的字符串,需要查找的字符,开始查找的起始位置,第n次出现)

返回值：字母所在的位置，没有返回 0

--员工姓名包含字母'T'的员工信息

select*from emp where instr(ename,'T',1,1)<>0

--查询员工姓名中有两个T字符的员工信息

????

问题5：字母'T'在员工姓名中第一次和第二次出现的位置？？？？

????

问题6：将用户姓名小写显示

函数：字符串小写转换 lower / upper

语法：lower(待处理的字符串)

--将用户姓名小写显示

select lower(ename)from emp

--使用小写形式查询allen这个员工

select*from emp where lower(ename)='allen'

问题7：将工作种类首字母大写显示

函数：首字母大写函数 initcap

语法：initcap(待处理的字符串)

??????

问题8：将用户姓名中的'T'替换成'O'显示 --论坛，

函数：替换函数 replace

语法：replace(待处理的字符串,需要修改的字符,修改后的字符)

--将用户姓名中的'T'替换成'O'显示

selectreplace(ename,'T','O')from emp

问题9：显示姓名只有5个字母组成的员工信息

函数：长度函数 length

语法：length(待处理的函数)

--显示姓名只有5个字母组成的员工信息

select*from emp where length(ename)=5

问题10：查找员工姓名以S开头Y结尾的员工信息

函数：从右删除字符函数RTRIM

语法：RTRIM (待处理的字符串，[被删除的字符（默认是空格）])

insertinto emp_bak0919(empno,ename,job,sal)

values(4900,'SUNNY ','CLERK',5000)

select*from emp_bak where rtrim(ename)like'S%Y'

2．日期或时间函数运算规则：日期相减=天数日期-日期=天数

问题1：查询显示系统时间select sysdate from dual;

函数：时间函数 sysdate

语法：sysdate

--查询员工smith入职的天数

select ename,sysdate-hiredate from emp where ename='SMITH';

问题2：查询在35年前参加工作的员工

函数：月份差函数 months_between

语法: months_between(时间1,时间2)

--查询在35年前参加工作的员工

select*from emp where months_between(sysdate,hiredate)>35*12

问题3：查询在当月倒数第三天入职的员工信息

函数：last_day

语法：last_day(时间) 自动返回实践中该月份的最后一天日期

--查询在当月倒数第三天入职的员工信息

select*from emp where hiredate = last_day(hiredate)-2

问题4：查询每个员工的工作天数

规则：Oracle中时间类型数据相减代表两个时间的天数差。

??????

问题5：查询距今天26个月后的时间。

函数：add_months

语法: add_months(待处理的时间，添加月份的数量)??

--查询距今天26个月后的时间

select add_months(sysdate,26)from dual

3 数字函数

问题1：按每月30天计算员工的每日工资，要求计算结果四舍五入到小数点后2位

函数：round

语法：round(数字,精度)

--按每月30天计算员工的每日工资，要求计算结果四舍五入到小数点后2位

select ename,round(sal/30,2)from emp

问题2：计算每个员工已经工作了多少个月，要求忽略小数部分

函数：整数截取 trunc

--计算每个员工已经工作了多少个月，要求忽略小数部分

select ename,trunc(months_between(sysdate,hiredate))from emp

ceil ->向上取 -> ceil(4.1) -> 5

floor ->向下取 -> floor(4.9) -> 4

power(m,n) -> m的n次方 -> power(2,3) -> 8

abs ->绝对值 -> abs(-1) -> 1

4 转换函数

问题1：向emp表添加一个员工，注意hiredate-> date to_date

?????

问题2：按照年月日、时分秒、星期几等信息显示系统当前时间

函数：to_char

语法：to_char(时间,格式)

--to_char数字格式化，员工工资

select ename,to_char(sal,'L9,999.99')from emp

--to_char日期格式化

--按照年月日、时分秒、星期几等信息显示系统当前时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day')from dual

5 混合函数查询每个雇员的年工资注意：nvl（）两个参数类型一致

nvl 替空函数 nvl(字段名称, 替换的值) – comm= ( comm == nul)?0:comm

--查询每个雇员的年工资

select ename,(sal)*12+(nvl(comm,0)*12)from emp

6 聚合函数sum求和、avg求平均数、count计数、max最大值、min最小值

问题1：显示部门30中所有员工的工资总和

函数： sum 求和函数

--显示部门30中所有员工的工资总和

--第一步：查询30部门的员工工资

--select sal from emp where deptno = 30

--第二步：求第一步结果的工资总和

selectsum(sal)from emp where deptno =30

select deptno,sum(sal) from emp group by deptno having deptno = 30;

问题2：显示部门30中员工的平均工资

函数：avg 求平均数

？？？？

问题3：工资高于3000的员工个数

函数：count 求个数

selectcount(*)from emp where sal >2000

问题4：显示工资最低的员工信息

函数: min 求最小值 / max 求最大值

select*from emp where sal = （selectmax(sal)from emp）;

--第一步：找出最高工资:5000

selectmax(sal)from emp;

--第二步：查询一个员工的信息，筛选条件是他的工资得等于5000

select*from emp where sal =5000;

Group by 分组

Having 分组条件

问题1：显示每个部门的平均工资和最高工资

--显示每个部门的平均工资和最高工资

select deptno, avg(sal),max(sal) from emp group by deptno;

问题2：显示平均工资高于2000元的每个部门的平均工资和最高工资

--显示每个部门的平均工资和最高工资

select deptno, avg(sal),max(sal) from emp group by deptno having avg(sal) > 2000;

语法：

Select(查询) *|列名（字段名）

From（从）表名

Where（筛选）条件【or|in|like】

Group by 分组

Having（筛选）分组条件

Order by 排序desc asc

知识点四：多表查询 emp dept salgrade

分为4种

1、等连接

2、不等连接

3、外连接

4、自连接

3-1、等连接

问题：显示员工姓名及所在部门的名称

分析：姓名-emp.ename

部门名称-dept.dname

关系：emp(deptno) ó dept(deptno)

--显示员工姓名及所在部门的名称

select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;

3-2、不等连接

问题：显示员工的编号,姓名,工资,以及工资所对应的级别。

分析:员工编号-empno,姓名-ename,工资-sal,工资等级-grade

关系:emp(sal) between salgrade(LOSAL) and salgrade(HISAL)

--显示员工的编号,姓名,工资,以及工资所对应的级别

select emp.empno,emp.ename,emp.sal,salgrade.grade from emp,salgrade

where emp.sal >= salgrade.losal and emp.sal <=salgrade.hisal

3-3、外连接

问题：查询所有部门名称和对应的员工姓名，若该部门没有员工，只显示部门名称

分析：部门名称 dept.dname

员工姓名 emp.ename

Oracle中使用(+)表示外连接，可以理解(+) 所在字段的对侧（以=分开左右两部分）为主要显示信息

--查询所有部门名称和对应的员工姓名-若该部门没有员工，只显示部门名称

select dept.dname,emp.ename from emp,dept where emp.deptno(+)=dept.deptno

select emp.ename,dept.dname from emp right outer join dept

on (emp.deptno = dept.deptno)

3-3、自连接（等连接）

问题：显示员工姓名及其上级员工的姓名

分析：员工姓名emp.ename

上级员工姓名 emp.name

--显示员工姓名及其上级员工的姓名

select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

--显示员工姓名及其上级员工的姓名-外连接

select e.ename 员工姓名,m.ename 上级姓名 from emp e,emp m where e.mgr = m.empno(+)

select e.ename 员工姓名,m.ename 上级姓名 from emp e left outer join emp m

on(e.mgr = m.empno)

注意：这种符号是Oracle数据库自己所独有的，其他数据库不能使用。

SQL:1999语法

除了以上的表连接操作之外，在SQL语法之中，也提供了另外一套用于表连接的操作SQL，格式如下：

SELECT table1.column,table2.column

FROM table1 [CROSS JOIN table2]|

[NATURAL JOIN table2]|

[JOIN table2 USING(column_name)]|

[JOIN table2 ON(table1.column_name=table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

以上实际上是属于多个语法的联合，下面分块说明语法的使用。

1、交叉连接（CROSS JOIN）：用于产生笛卡尔积

SELECT*FROM emp CROSSJOIN dept;

笛卡尔积本身并不是属于无用的内容，在某些情况下还是需要使用的。

2、自然连接（NATURAL JOIN）：自动找到匹配的关联字段，消除掉笛卡尔积

SELECT*FROM emp NATURAL JOIN dept;

但是并不是所有的字段都是关联字段，设置关联字段需要通过约束指定；

3、JOIN…USING子句：用户自己指定一个消除笛卡尔积的关联字段

SELECT*FROM emp JOIN dept USING(deptno);

4、JOIN…ON子句：用户自己指定一个可以消除笛卡尔积的关联条件

SELECT*FROM emp JOIN dept ON(emp.deptno=dept.deptno);

select emp.ename,dept.dname,salgrade.grade

from emp join dept on(emp.deptno = dept.deptno)

join salgrade on(emp.sal between salgrade.losal and salgrade.hisal)

5、连接方向的改变：

左（外）连接：LEFT OUTER JOIN…ON;
右（外）连接：RIGHT OUTER JOIN…ON;
全（外）连接：FULL OUTER JOIN…ON; -->把两张表中没有的数据都显示

SELECT*FROM emp RIGHTOUTERJOIN dept ON(emp.deptno=dept.deptno);

在Oracle之外的数据库都使用以上的SQL:1999语法操作，所以这个语法还必须会一些（如果你一直使用的都是Oracle就可以不会了）。Oracle9i以后也都支持以上写法了。

再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能够发现。

知识点五:子查询

问题：查询工资高于公司平均工资的所有员工

分析：公司的平均工资

select avg(sal) from emp;

--查询工资高于公司平均工资的所有员工

?????

--查询选择了王萍老师教的课的学生姓名

select sname from student

where sno in(

select sno from score

where cno in(

select cno from course

where tno =(

select tno from teacher

where tname ='王萍'

)))

补充内容：

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

select*from emp_england

union

select*from emp_china

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

select*from emp_england

unionall

select*from emp_china

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

MINUS关键字(差集)

INTERSECT (交集)

2017-10-31 18:33:07