Oracle语句以及各种知识点

用户和权限

1创建用户

管理员才有权限创建用户 sys/system

--管理员登录

conn sys/oracle@orcl as sysdba;

--创建用户方案---必须管理员身份才能操作  

create user username identified by password;

create user wangwu  identified by w123;

--删除用户方案

drop user username cascade;

---修改密码

alter user username identified by newpassword;

2 用户解锁上锁

--用户解锁和上锁---必须管理员身份才能操作

alter user username account lock;

alter user scott  account lock;

alter user username account unlock;

alter user scott account unlock;

3授权

一般开发只要给用户授予connect 和resource 角色就够了

3个默认 角色

4回收权限

---回收权限 （要在管理员身份下才能回收）

revoke privilegename  from username;

revoke connect from wangwu;

5 权限传递

--授权（对象权限）

grant select on scott.emp to ken;

grant update on scott.emp to wangwu;

--回收权限(对象权限)

revoke select on scott.emp from ken;

revoke  update on scott.emp from wangwu;

---权限的传递 with admin option传递系统权限  with grant option 传递对象权限

create user userone  identified by one;

create user usertwo  identified by two;

grant  create session to userone with admin option;

--with admin option  在userone下面可以 将 create session 系统权限授权给其他用户

grant  select on scott.emp  to userone with grant option ;

--with grant option  在userone下面可以 将 select 对象权限授权给其他用户

6 规则

---规则 profile

create profile notLoginDay limit  failed_login_attempts  3  password_lock_time  100;

--添加规则

alter user scott profile notLoginDay;

7字符函数

/*字符函数*/

--当没有表可以用个的时候oracle自带一个虚表dual

-- || 表示连接符号  将字符串连接到一起

Lower(char):将字符串转化为小写格式

?将所有员工的名字按小写的方式显示

select  lower(ename),lower(job) from emp;

Upper(char):将字符转化为大写的格式

select  upper(‘abcd‘)  from dual;

Length(char):返回字符串的长度

?显示正好为5个字符员工的姓名

select * from emp   where  length(ename)=5;

Substr(char,m,n):取字符串的子串  m 表示开始的位置 n 字符个数

?显示所有员工姓名的前三个字符

select ename ,substr(ename,1,3) from emp;

Trim,Ltrim,Rtrim:去掉空格

select   ‘==‘||  trim(‘    abc   ‘)  ||‘==‘ from dual;

?以首字母大写的方式显示所有员工

select ename, substr(ename,0,1)||  lower( substr(ename,2,length(ename)-1) )   from emp;

Replace (char1,search_string,replace_string)

 ? 显示所有的员工姓名将A替换成“我是A”

select  ename, replace(ename,‘A‘,‘我是A‘)as result from emp;

Instr(char1,char2,[,n[,m]])取字符串的位置  n 开始位置 m 第几个char2

 ? 从第二个字符开始找到员工姓名中A所在的位置

select ename,  Instr(ename,‘A‘,1,2) from emp;

8 数学函数

/*数学函数*/

Round(n,[m])

四舍五入，如果省掉m，则四舍五入到整数，如果m是整数，则四舍五入到小数点的m位置后，如果是m负数，则  四舍五入到小数点m的位前

?  73.2564  四舍五入到小数点后2位

select  round(73.2564,2)    from dual;

trunc(n,[m])

用户截取数字，如果省略m，就截去小数部分，如果m是整数就截取到小数点后m位后，如果m是负数，则截取到小数点的前m位

? 73.2564  截取到小数点后3位

select  trunc(73.2564,3)    from dual;

Mod(n,[m])取模

?   8%3=?

select mod(8,3) from dual;

Floor(n)

 向下取整

? 7.5846 向下取整

Ceil(n)

向上取整

?7.5846 向上取整

select  floor(7.1289) ,Ceil(7.1289) from dual;

9/*日期函数*/

1 sysdate :返回系统时间

select  sysdate from dual;

2 current_date 返回当前时间

select  current_date from dual;

3 Next_day(date,week) 给定时间的下一个星期是几号   /*week 数字和英文*/

 select  next_day(sysdate,‘MONDAY‘) from dual;   

4 add_months(d,n)：返回在时间d上加n个月的时间

select add_months( sysdate,12 ) from dual;

5 last_day(d):返回指定日期所在月份的最后一天

 select last_day( sysdate) from dual;

？查找已经入职362个月多的员工

？显示满30年份服务年限的员工的姓名和受雇日期

？每个员工加入公司的天数

？下个星期一是多少号

？找出各月倒数第三天受雇的所有员工

转换函数

/*

转换函数

时间---->字符

字符---->数字

字符---->时间

*/

/*to_char( 时间,格式)*/

---------------------时间格式----------------------

格式：

yyyy:显示年份

yy :显示年份后2位

mm:月份

dd:号

hh /hh24:小时

mi分

ss秒

select to_char(sysdate,‘yy-mm-dd hh24:mi:ss‘) from dual;

-----------------货币格式--------------------

9 ：显示数字

0 ：不足则补0

.  ：显示小数点

，;  显示分隔符

$ :  显示美元符号

L :  显示本地货币

C : 显示国际货币符号

G : 制定位置显示分割符号

D : 制定位置显示小数点符号

？薪水指定货币符号

8756984.555

$8,756,984.555

$9,999,999.99

L9,999,999.99

select to_char(8756984.5,‘L9,999,999.99‘) from dual;

select to_char(8756984.5,‘$9G999G999D99‘) from dual;

---------------字符转日期  to_date(‘2010-10-10‘,‘yyyy-mm-dd‘)-------------------------

select to_date(‘2017-2-27‘,‘yyyy-mm-dd‘) from dual;

insert into  emp values( 9900, ‘ZHANGSAN‘,‘CLERK‘,7698,to_date(‘2010-10-10‘,‘yyyy-mm-dd‘),2500.00 ,0.00,30);

---------------字符转数字  To_number ---------------------------------------------------

select to_number(‘001‘)*8 from dual;

-------------------------聚集函数  count,sum,avg,max,min --------------------------------------------------

---count ---统计总行数

select count(*) from emp;

select count(empno) from emp; --统计员工总人数

---sum-----统计总和

select sum(sal) as total from emp;  --统计所有人员的工资总和

--avg------统计平均数

select avg(sal) from emp; --统计所有人员的工资平均值

--max--最大值---min最小值

select max(sal) as maxSal ,min(sal) as minSal from emp;

---聚合函数不能放到where 作为条件

select * from emp where sal  avg(sal)--错;

select * from emp where sal>(select avg(sal) from emp)；---对

--显示当前用户-

 select user from dual;

---decode 布尔判断函数 ------

---查询出CLERK 的人员数量

 select  sum( decode(job,‘CLERK‘,1,0) ) from emp;

 ---查询各个工种有多少人

 select sum( decode(job,‘CLERK‘,1,0) ) as CLERK,

        sum( decode(job,‘SALESMAN‘,1,0) ) as  SALESMAN,

        sum( decode(job,‘MANAGER‘,1,0) ) as  MANAGER,

        sum( decode(job,‘ANALYST‘,1,0) ) as  ANALYST,

        sum( decode(job,‘PRESIDENT‘,1,0) ) as  PRESIDENT,count(*) as total

        from emp;

-nvl(column,default) 非空

----------nvl(column,default) 非空--------------------------------------

---------------------emp表中所有员工在元奖金的基础上增加50元奖金

update emp set comm=nvl(comm ,0 ) +50 ;

数据导出备份和导入

--------------------------数据导出备份和导入

-------------------------注意 导出和导入 必须是CMD 命令行下操作，而不是SQL编辑器中

---导出表  exp 导出关键字   userid用户权限  file 保存的位置  tables 表

exp  userid=scott/tiger@orcl  tables=(dept,emp)  file=c:/scottTables.dmp   

--导出方案

exp  userid=scott/tiger@orcl  owner=scott  file=d:/scott.dmp

--管理员帮忙导出scott方案

exp  userid=sys/oracle@orcl  owner=scott  file=d:/scott.dmp

--导入表  imp导入关键字

imp  userid=scott/tiger@orcl   file=c:/scottTables.dmp  

--导入方案

imp userid=scott/tiger@orcl   file=d:/scott.dmp

--管理员导入  管理员导出过的方案

imp userid=sys/oracle@orcl  file=d:/scott.dmp fromuser=sys  touser=scott