Oracle常用知识点(1)

前端之家收集整理的这篇文章主要介绍了Oracle常用知识点(1)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
05.avi 解锁与设置普通用户
创建数据库 在Oracle-OraDb11g_home1/配置和移植工具/DatabaseConfiguration Assistant
在12c版本中 没有scott账户,但出现了hr用户
sys超级用户,dba角色。
解锁用户:alter user scott account unlock;
设置密码:alter user scott identified by tiger;
sqlplus窗口登陆:sys为:sqlplus sys/sys as sysdba
scott登陆为:sqlplus scott/tiger
退出登陆:exit
如果scott账户密码遗忘可以使用sys账户登陆,设置scott账户密码即可。
不显示密码登陆:sqlplus,输入账户,输入密码。
在Oracle-OraDb11g_home1 /应用程序开发中/sql Plus登陆。

06.avi Oraclesql
sql92/99规则分为四大类
(A)DML(数据操纵语言):select,insert,update,delete
(B)DDL(数据定义语言):create table,alter table,drop table,truncate table 。。。
(C)DCL(数据控制语言):grant 权限 to scott,revoke 权限 from scott 。。。
(D)TCL(事务控制语言):commit,rollback,rollback to savepoint 。。。

07.avi select子句在sql Plus工具中:
使用scott账户修改密码命令:
password
查看当前用户是谁命令:
show user;
查询scott用户下的所有对象命令:
select * from tab;
查询scott用户下emp表结构命令:
desc emp;
查询scott用户下emp表内容命令:
select * from emp;
设置查询显示的列宽(数值型number),9表示数字型,一个9表示个一个数字为,只能用9,命令:
column empno format 9999;
设置查询显示的列宽(字符型varchar2、日期型data),10个位宽度,a表示字符型,大小写均可,命令:
column ename format a12;
使用/,执行最近一次的sql命令:
/
设置一页显示80记录高度,命令:
set pagesize 80;
注:格式调整仅对一次登陆会话有效,退出登陆更改的格式就会消失。
清屏命令:
host cls;
注:列明大小写不敏感,但推荐大写。
查询时使用别名,关键字as,大小写均可,as后的别名可加双引号,也可不加,不加双引号要求别命中不能有空格,命令:
select empno "编号",ename "姓名" from emp;
注:单引号用于字符串和日期类型
查询emp表不重复的工作命令:
select distinct job from emp;
字段中有月薪,查询年薪,年收入(年薪+奖金)命令:
select ename,sal,sal*12,sal*12+comm from emp;
select ename,sal*12+NVL(comm,0) from emp;
注:如果结果为null,在sqlplus客户端工具中,是不显示null这个值的。
解决null问题:NVL(a,b)如果a为null,用b代替,如果a非null,就不用b代替,直接返回a的值。
使用dual哑表(伪表),其只有一行,使用字符连接符号||完成hello world的输出,命令:
select 'hello' || 'world' from dual;
使用sysdate,显示系统当前时间,格式:18-11月-16命令:
select sysdate from dual;

08.avi select子句。
sql Plus命令和查询结果保存起来,命令:
spool e:save.sql;
spool off;
读取硬盘上的sql命令,命令:
@ e:/test.sql
注释,命令:
单行--
多行/* */
sql语句的特点
1)是sql92/99的ANSI官方标准,只要按照该标准来写,在任何的关系型数据库中都可以直接执行
2)sql语句的关健字不能简写,例如:select,where,from
3)大小写不敏感,提倡大写
4)能够对表数据进行增删改查操作
5)必须以分号结束
6)通常称做语句
sqlPLUS命令的特点
1)是oracle自带的一款工具,在该工具中执行的命令叫sqlPLUS命令
2)sqlPLUS工具的命令中的关健字可以简写,也可以不简写,例如:col ename for a10;
3)大小写不敏感,提倡大写
4)不能够对表数据进行增删改查操作,只能完成显示格式控制,例如:设置显示列宽,清屏,记录执行结果
5)可以不用分号结束,也可以用分号结束,个人提倡不管sqlsqlPLUS,都以分号结束
6)通常称做命令,是sqlPLUS工具中的命令
注意:sqlPLUS命令是sqlPLUS工具中特有的语句
单引号出现的地方如下:
1)字符串型,例如:'hello' || ' world'
2)日期型,例如'25-4月-15'
双引号出现的地方如下:
1)列别名,例如:sal*12 "年 薪",或 sal*12 年薪,个人提倡用""双引号作列别名

09.avi where子句
查询员工信息(编号,姓名,月薪,年薪),按月薪升序排序,默认升序,如果月薪相同,按oracle内置的校验规则排序
select empno,ename,sal*12
from emp
order by sal asc;

查询员工信息(编号,姓名,月薪,年薪),按月薪降序排序
select empno,sal*12
from emp
order by sal desc;

查询员工信息,按入职日期降序排序,使用列名
select empno,hiredate,sal*12 "年薪"
from emp
order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号(从1开始,在select子句中的列号)
列名:
select empno,sal*12 "年薪"
from emp
order by hiredate desc;

别名:
select empno,sal*12 "年薪"
from emp
order by "年薪" desc;

表达式:
select empno,sal*12 "年薪"
from emp
order by sal*12 desc;

列号,从1开始:
select empno,sal*12 "年薪"
from emp
order by 5 desc;

查询员工信息,按佣金升序或降序排列,null值看成最大值
select * from emp order by comm desc;

查询员工信息,对有佣金的员工,按佣金降序排列,当order by 和 where 同时出现时,order by 在最后
select *
from emp
where comm is not null
order by comm desc;

查询员工信息,按工资降序排列,相同工资的员工再按入职时间降序排列
select *
from emp
order by sal desc,hiredate desc;

select *
from emp
order by sal desc,hiredate asc;
注意:只有当sal相同的情况下,hiredate排序才有作用

查询20号部门,且工资大于1500,按入职时间降序排列
select *
from emp
where (deptno=20) and (sal>1500)
order by hiredate desc;

-------------------------------------------------------------------------------------单行函数

单行函数:只有一个参数输入,只有一个结果输出
多行函数或分组函数:可有多个参数输入,只有一个结果输出

测试lower/upper/initcap函数,使用dual哑表
select lower('www.BAIdu.COM') from dual;
select upper('www.BAIdu.COM') from dual;
select initcap('www.BAIdu.COM') from dual;

测试concat/substr函数,从1开始,表示字符,不论中英文
select concat('hello','你好') from dual;正确
select concat('hello','你好','世界') from dual;错误
select 'hello' || '你好' || '世界' from dual;正确
select concat('hello',concat('你好','世界')) from dual;正确
select substr('hello你好',5,3) from dual;
5表示从第几个字符开始算,第一个字符为1,中英文统一处理
3表示连续取几个字符

测试length/lengthb函数,编码方式为UTF8/GBK(赵君),一个中文占3/2个字节长度,一个英文一个字节
select length('hello你好') from dual;
select lengthb('hello你好') from dual;

测试instr/lpad/rpad函数,从左向右找第一次出现的位置,从1开始
select instr('helloworld','o') from dual;
注意:找不到返回0
大小写敏感
select LPAD('hello',10,'#') from dual;
select RPAD('hello','#') from dual;

测试trim/replace函数
select trim(' ' from ' he ll ') from dual;
select replace('hello','l','L') from dual;

测试round/trunc/mod函数作用于数值型
select round(3.1415,3) from dual;
select trunc(3.1415,3) from dual;
select mod(10,3) from dual;

当前日期:sysdate = 26-4月-15

测试round作用于日期型(month)
select round(sysdate,'month') from dual;

测试round作用于日期型(year)
select round(sysdate,'year') from dual;

测试trunc作用于日期型(month)
select trunc(sysdate,'month') from dual;

测试trunc作用于日期型(year)
select trunc(sysdate,'year') from dual;

显示昨天,今天,明天的日期,日期类型 +- 数值 = 日期类型
select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄,日期-日期=数值,假设:一年以365天计算,一月以30天计算
select ename "姓名",round(sysdate-hiredate,0)/365 "天数" from emp;

使用months_between函数,精确计算到年底还有多少个月
select months_between('31-12月-15',sysdate) from dual;

使用months_between函数,以精确月形式显示员工工龄
select ename "姓名",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数,下个月今天是多少号
select add_months(sysdate,1) from dual;

测试add_months函数,上个月今天是多少号
select add_months(sysdate,-1) from dual;

测试next_day函数,从今天开始算,下一个星期三是多少号【中文平台】
select next_day(sysdate,'星期三') from dual;

测试next_day函数,从今天开始算,下下一个星期三是多少号【中文平台】
select next_day(next_day(sysdate,'星期三'),'星期三') from dual;

测试next_day函数,从今天开始算,下一个星期三的下一个星期日是多少号【中文平台】
select next_day(next_day(sysdate,'星期日') from dual;

测试last_day函数,本月最后一天是多少号
select last_day(sysdate) from dual;

测试last_day函数,本月倒数第二天是多少号
select last_day(sysdate)-1 from dual;

测试last_day函数,下一个月最后一天是多少号
select last_day(add_months(sysdate,1)) from dual;

测试last_day函数,上一个月最后一天是多少号
select last_day(add_months(sysdate,-1)) from dual;

注意:
1)日期-日期=天数
2)日期+-天数=日期

-------------------------------------------------------------------------------------三大类型转换

oracle中三大类型与隐式数据类型转换
(1)varchar2变长/char定长-->number,例如:'123'->123
(2)varchar2/char-->date,例如:'25-4月-15'->'25-4月-15'
(3)number---->varchar2/char,例如:123->'123'
(4)date------>varchar2/char,例如:'25-4月-15'->'25-4月-15'

oracle如何隐式转换:
1)=号二边的类型是否相同
2)如果=号二边的类型不同,尝试的去做转换
3)在转换时,要确保合法合理,否则转换会失败,例如:12月不会有32天,一年中不会有13月

查询1980年12月17日入职的员工(方式一:日期隐示式转换)
select * from emp where hiredate = '17-12月-80';

使用to_char(日期,'格"常量"式')函数将日期转成字符串,显示如下格式:2015 年 04 月 25 日 星期六
select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;

使用to_char(日期,'格式')函数将日期转成字符串,显示如格式:2015-04-25今天是星期六 15:15:15
select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:$1,234
select to_char(1234,'$9,999') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:¥1,234select to_char(1234,999') from dual;
select to_char(1234,'L9,999') from dual;

使用to_date('字符串','格式')函数查询1980年12月17日入职的员工(方式二:日期显式转换)
select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

使用to_number('字符串')函数将字符串‘123’转成数字123
select to_number('123') from dual;


注意:
select '123' + 123 from dual;246
select '123' || 123 from dual;123123


-----------------------------------



使用NVL(a,b)通用函数统计员工年收入,NVL()作用于任何类型,即(number/varchar2/date)
通用函数:参数类型可以是number或varchar2或date类型

使用NVL2(a,b,c)通用函数,如果a不为NULL,取b值,否则取c值,统计员工年收入

使用NULLIF(a,b)通用函数,在类型一致的情况下,如果a与b相同,返回NULL,否则返回a,比较10和10.0是否相同

使用sql99标准通用语法中的case表达式,将职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400
case 字段
when 条件 then 表达式1
when 条件 then 表达式2
else 表达式n
end

使用oracle专用语法中的decode()函数,职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400
decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)

单引号出现的地方如下:
1)字符串,例如:'hello'
2)日期型,例如:'17-12月-80'
3)to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')

双引号出现的地方如下:
1)列别名,例如:select ename "姓 名" from emp
2)to_char/to_date(日期,'YYYY"年"MM"月"DD"日" HH24:MI:SS')

注:感谢船只!
原文链接:https://www.f2er.com/oracle/211731.html

猜你在找的Oracle相关文章