Oracle基础

Oracle 基础操作

#用户创建

sqlplus /nolog
sys/password as sysdba--进系统管理员
create user user_name identified by password--创建用户
grant dba to user_name--授予权限 
revoke 权限 from user_name --撤销权限
alter user user_name account (un)lock--解锁或锁定
alter user user_name identified by password --修改用户密码
drop user user_name --删除用户

#表创建

user_name/password --进用户
create table table_name (column1 modify 约束,column2 modify 约束,...)--创建表及结构
create table table_name as select * from 另一个表 （where 条件） --把另一个表的全部数据及表结构复制/某些数据
insert into table_name (column1,column2,...) values (值1,值2,...)---给表插入数据

#增删改

alter table table_name add (column modify 约束)--增加列
alter table table_name modify (column modify)--修改列类型
alter table table_name rename column (old_column to new_column)--更改列名
alter table table_name drop column column_name --删除列
update table_name set column_name=新值 （where 条件） --更新表的某行
delete from table_name （where 条件）--删除表里的数据

#函数

1、trunc（截段函数）
round（四舍五入）
round(a,b):
a:要进行操作（四舍五入）的对象
b:保留几位小数
select sal/30,round(sal/30) from emp 
select sal/30,round(sal/30,2) from emp 
select sal/30,round(sal/30,-2) from emp 
trunc（截段）
trunc(a,b):
a:要进行截段的对象
b:截取几位
select sal/30,trunc(sal/30) from emp 
select sal/30,trunc(sal/30,2) from emp 
select sal/30,trunc(sal/30,-1) from emp
select trunc(sysdate,'mm') from dual

2,substr 截取函数
instr(满足字符串返回值函数)
substr(a,b,c)
select substr('abcde',2,3) from dual
a:截取的对象
b:从第几位开始截
c:截取几位长度
select substr('abcde',2) from emp;
特例：c c为0，则不截取字符串长度，c没有内容，从起始位截取全部字符串
select substr（'abcde',2,-3） from dual
b: b为0 也为从第一位开始截取
select substr('abcde',0) from emp;
b<0 
select substr('abcdeefdghjbcdvs',-7,4) from dual;
instr 判断需求字符串在已知字符串中的位置
instr(a,b)
select instr('abcde','bd')from dual
select instr（ename,'S'） FROM EMP
a:已知字符串
b:需求字符串
select instr ('abcdcba','b',1) from dual
c;从第几位开始找
select instr('abcdcba','b',1,2) from dual
d;第几次找到需求字符串(只能取正整数)
select instr('abcdcba','b',1,-1) from dual
C:---2(表示从右往左找位置，并且从后往前找需求字符串)
select instr('abeccdcba','c',-4,2) from dual；
着重强调：
1，在substr中，若b为负数，从后往前数b的位置，从前往后截c的长度
select substr('aabbcc',-3,2) from dual
2，在instr中，若c为负数，从后往前数c的位置，在向前找b
select instr('aabbcc','b','-3') from dual
3，在instr中，无论什么情况，位置数总是从前向后数。

函数练习：

1 以首字母大写的方式显示所有员工的姓名
SELECT  INITCAP(ename) FROM emp

2 将员工的职位用小写显示
SELECT LOWER(job) FROM emp
3 将员工的名字分别用大写和小写显示 
SELECT UPPER(ename),LOWER(ename) FROM emp

4 将员工名字，首字母小写，其他字母大写的方式显示
SELECT LOWER(SUBSTR(ename,1,1))||UPPER(SUBSTR(ename,2)) FROM emp

5 显示员工姓名为5个字符的员工
SELECT * FROM emp WHERE LENGTH(ename)=5
6 显示所有员工姓名的前三个字符
SELECT substr(ename,1,3) FROM emp

7 显示所有员工姓名的后三个字符
SELECT SUBSTR(ename ,-3) FROM emp

8 以字符长度为10的方式显示员工职位，多余的位数在右边以*来填充
SELECT RPAD(job,10,'*') job FROM emp

9 找出字符串"oracle training"中第二个ra出现的位置
SELECT INSTR('oracle training','ra',1,2) FROM dual

10 去除字符串‘ aadde gf ’两边的空格
SELECT TRIM(' aadde gf') FROM dual

11 以指定格式显示员工的工资（格式：SMITH 的工资是 800）
SELECT ename||'的工资是：'||sal FROM  emp
12 显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ename,'A','a') FROM emp
13 显示员工姓名中包含‘H’的员工
SELECT ename FROM emp WHERE ename LIKE '%H%'

14 显示员工姓名中第二个字符是‘L’的员工
SELECT ENAME FROM EMP WHERE SUBSTR(ENAME,2,1)='L'

15 显示员工姓名中最后一个字符是‘T’的员工
SELECT ENAME FROM EMP WHERE SUBSTR(ENAME,-1,1)='T'


16 查询一个月前入职的员工
SELECT * FROM EMP WHERE hiredate = add_months(hiredate，-1）

17 显示在一个月为30天的情况所有员工的日薪,忽略余数
SELECT FLOOR(SAL/30) FROM EMP 

18 显示员工在此公司工作了几个月（要求结果是整数）
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) FROM EMP

19 显示所有12月份入职的员工
SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'MM')=12

20 显示员工的年薪（12个月的工资+补贴）
SELECT (SAL+NVL(COMM,0))*12 FROM EMP

21 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY') Y ,TO_CHAR(HIREDATE,'MM') M FROM EMP ORDER BY Y,M

22 显示每月倒数第3天入职的所有员工
SELECT ENAME FROM EMP WHERE HIREDATE = LAST_DAY(HIREDATE)-2

23 显示入职满10年的员工的姓名和受雇日期。 
SELECT ENAME,HIREDATE FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 >=10 

#简单查询（列名表名不过多赘述，以学习账户scott为例）

select  （column1，column2...）查询的列名   from  查询的表名  where 条件； --一般条件查询

#order by 、group by

select * from emp order by sal desc （降序） asc（升序） --以工资降序或者升序排列

select deptno,max(sal)  from emp group by deptno  --以部门分组查询部门最高工资

注意：用group by 分组的时候，查询的必须是分组的列，或者用组函数（max、min、count、avg 、sum）组合使用

#子查询

将一般查询作为一张表，用另一个查询查这张表，再用上一张表继续查询（多表查询）

例：求出工资排名6-10的员工

SELECT *
  FROM (SELECT E.*, ROWNUM R
          FROM (SELECT * FROM EMP ORDER BY SAL DESC) E
         WHERE ROWNUM <= 10)
 WHERE R >= 6