Oracle常用知识点（1）
发布时间：2020-12-12 15:42:45 所属栏目：百科 来源：网络整理
导读：05.avi 解锁与设置普通用户 创建数据库 在Oracle-OraDb11g_home1/配置和移植工具/DatabaseConfiguration Assistant 在12c版本中 没有scott账户，但出现了hr用户。 sys超级用户，dba角色。 解锁用户：alter user scott account unlock; 设置密码：alter user
|
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）可以不用分号结束，也可以用分号结束，个人提倡不管SQL或SQLPLUS，都以分号结束 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') 注：感谢船只！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |