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’的员工姓名的信息 select*from emp where ename like'S%' select*from 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 select*from emp orderby sal desc select*from empsal where rownum <=3 select*from(select*from 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