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 |
||||||||
