Oracle数据库常用命令

Oracle数据库常用命令
sqlplus scott/tiger as sysdba ：将scott账户当作dba登录进来
alter user scott account unlock; 用户解锁
|-账户解锁
1.conn / as sysdba
2.alter user scott account unlock;
3.alter user scott identified by tiger;
|-数据类型

1. binary_integer： 整数，主要用来计数而不是用来表示字段类型
2. number： 数字类型
3. char： 定长字符串
4. varchar2： 变长字符串
5. date： 日期
   6 long： 长字符串，最长2GB
   7 boolean： 布尔，可取true false null
   Number：
   NUMBER （ precision， scale）
   precision表示数字中的有效位。如果没有指定precision的话，Oracle将使用38作为精度。
   scale表示数字小数点右边的位数，scale默认设置为0.
   P是精度，s是刻度
   精度代表所要存的位数，s是指小数点后保留几位
   例如存储1234567.89序定义number(9,2)
   Char和varchar2 的区别
   CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc”，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。 CHAR的效率比VARCHAR2的效率稍高。

|-数据库的启动
sqlplus / as sysdba
shutdown immediate
sql> startup nomount
sql> alter database mount
sql> alter database open

1.在Dos窗口中: rman target / nocatalog
2.关闭数据库: shutdown immediate
3.启动数据库到第二阶段：startup mount
4.启动数据库到第三阶段：alter database open

|-创建表空间
create tablespace ts1 datafile ‘c:\ts1.dbf’ size 10M;

|-新建用户并授权
1.新建用户并制定密码 create user user1 identified by abc;
2.指定用户所在的表空间 alter user user1 default tablespace ts1;
3.授予连接和资源的权限 grant connect,resource to user1;
·显示当前用户 show user;
·授予最大权限 grant connect,dba to user1;

|-备份数据库

|-远程连接数据库
sqlplus sim/sim@ORCLCLCSKY101
sim/sim: 用户名/密码
ORCLCLCSKY101: 配置的监听

|-ORACLE默认表
desc 表名 ：查看表结构
·desc emp 员工表
·desc dept 部门表
·desc salgrade 薪水等级表
·desc dual 空表，一般用于计算，例如：select 2*3 from dual;
Scott账户的默认表
Emp:员工表
EMPNO（员工编号）
ENAME（员工姓名）
JOB（职位）
MGR（经理人编号）
HIREDATE（入职时间）
SAL（薪水）
COMM（津贴）
DEPTNO（部门编号）

Dept:部门表

Salgrade：等级表

Select语句

|-数学表达式
·select ename,sal*12 from emp; 薪水*12，年薪
·select ename,sal*12 year_sal from emp; 起别名
·select ename,sal*12 “year_sal” from emp; 加双引号保持字段的大小写原型
·select ename,sal*12+comm from emp; 所有人的年薪，薪水*12+津贴
|-sysdate
·select sysdate from dual; 当前系统时间
|-字符串连接符 ||
·SQL> select sal||’ksks’ from emp; ||字符串连接符,字符串用单引号引起

SAL||’KSKS’

800ksks
1600ksks
1250ksks
2975ksks
1250ksks
2850ksks
2450ksks
3000ksks
5000ksks
1500ksks
1100ksks
|-去掉重复信息 distinct
·select distinct deptno from emp; 去掉deptno字段的重复信息
·select distinct deptno,job from emp; 去掉deptno，job组合的重复的信息
|-过滤条件 where
·数学运算符：< > <> = not and or in
·select * from emp where deptno=10; deptno（部门）等于10的员工信息
·select ename,deptno from emp where deptno<>10; 部门不等于10的员工姓名和部门
·select ename,sal from emp where sal>=800 and sal<=1500; 薪水大于等于800并且小于等于1500
·select ename,sal from emp where sal>1000 and deptno=10; 薪水>10并且部门=10的
·select ename,sal from emp where sal>1000 or deptno=10; 薪水>10or部门=10的都取出来

|-空值处理 NULL
·select ename,sal from emp where comm is null; 奖金为空的员工的姓名和工资
·select ename,sal from emp where comm is not null; 奖金不为空的员工的姓名和工资
·select ename,sal from emp where sal in (800,1500,3000); 薪水是800、1500、3000的人的的姓名和薪水
·巧妙使用函数nvl（）
select ename,sal*12+nvl(comm,0) from emp;
如果comm是空值，则用0代替，如果不是null，则就是comm的值

|-模糊查询 Like
·%:通配一个或多个字母、 _：通配一个字母、 :转义字符
·select ename from emp where ename like ‘%A%’; 姓名中带’A’的人
·select ename from emp where ename like ‘_A%’; 姓名中第二个字母是A的人
·select ename from emp where ename like ‘%\%%’; 查询姓名中带%的人，
·select ename from emp where ename like ‘%’; $作为转义字符，查询出姓名中带%的人

|-排序 order by asc/desc
·select * from dept order by deptno desc;将部门表按照部门编号降序排列
·select ename,sal from emp order by sal asc;员工表按照薪水升序排列
·select ename,sal from emp where deptno<>10 order by sal asc;
部门不等于10的员工按照薪水升序排列
·select ename,sal,deptno from emp order by deptno asc,sal desc;
先按照部门编号升序排序，在部门编号相同的情况下再按照薪水降序排序

·select ename,sal*12 from emp
where
ename not like ‘_A%’ and sal>100
order by empno desc;
取出来emp的名字、年薪，名字的第二个字母不包含A并且薪水大于100按照员工编号降序排列

|-函数
|-lower(e):转换成小写 substr(字段，n，m)将字段从n开始截取，截取到m（包含第m和第n）
|-ascii(a)：求a的ascii码 chr(97)：将ascii转换为字符
|-round(m,s):四舍五入到s位
·select lower(ename) from emp; 将ename转换为小写
·select ename from emp where lower(ename) like ‘_a%’; 查询小写的姓名包含a的数据
·select substr(ename,1,3) from emp;将姓名从第一个开始截取，截取到第三个（包含第一个）
·select ascii(‘a’) from dual; 求a的ascii码
·select chr(97) from dual; 将ascii转换为字符，本条语句结果为a
·select round(23.567) from dual; 四舍五入到个位。结果为24
·select round(23.567,2) from dual; 四舍五入到小数点后两位，结果为23.57
·select round(23.567,-1) from dual; 四舍五入到小数点前1位，结果为20
|-to_char(字段a，格式)；将字段a转换为想要的格式显示出来
·select to_char(sal,’99,999.9999′)fromemp;⋅selecttochar(sal,′L99,999.9999′)fromemp;⋅selecttochar(sal,′L00,000.0000′)fromemp;L：本地货币|−todate将字符串转换为日期⋅selectename,hiredatefromempwherehiredate>todate(‘1981−2−2012:34:56′,‘YYYY−MM−DDHH−MI−SS′);将1981−2−2012:34:56转换为日期然后与日期类型的hiredate做比较|−tonumber将字符串转换为日期⋅selectename,salfromempwheresal>tonumber(‘888.88','999.99′);将‘888.88’转换为数字类型然后才能与sal比较

|-组函数 max()、min()、avg()、count() 、sum()，把N条数据作为输入，最后产生一个输出
select max(sal) from emp; 求最高工资
select sum(sal) from emp; 求薪水总和
select round(avg(sal),2) from emp; 求平均工资并四舍五入并保留小数点后两位
select to_char(avg(sal),’9999.99’) from emp; 同上
select count(ename) from emp where deptno=10; 部门编号是10的有名字的人有多少
select count(distinct deptno) from emp; 去除重复的部门编号后显出部门数

|-日期处理
·select to_char(hiredate,’YYYY-MM-DD HH:MI:SS’) from emp;

·select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;·select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; 24表示法

|-分组 group by、having
·select avg(sal) from emp group by deptno; 每个部门的平均工资
先按照部门进行分组，然后各组内部再按工资求平均数
·select deptno,max(sal) from emp group by deptno; 按部门分组，组内部分别求最大工资
·select avg(sal) from emp group by deptno having avg(sal)>2000;
按照部门进行分组，求各平均工资大于2000的薪水。
·select avg(sal) from emp where sal>1200
group by deptno
having avg(sal)>1500
order by avg(sal) desc;
求薪水大于1200的人的平均薪水，按照部门分组，每个部门平均薪水大于1500，并按照平均薪水倒序排序
·工资高于本部门平均工资的人的姓名和工资
Select avg(sal) from emp group by deptno;

|-子查询：理解子查询的条件：把它当成一张表
·select ename,max(sal) from emp;
求拥有最大工资的人的名字和工资，这句话显然错误，组函数是把N条数据作为输入，最后产生一个输出。很有可能最大工资数‘5000’为多个人共同所有。如果是多个人共同所有，那么就有了N多条记录，这显然和组函数的定义不符，这时就需要我们运用子查询来解决这个问题
·select ename,sal from emp where sal=(select max(sal) from emp);
求拥有最大工资的人的名字和工资

·select ename,sal from emp where sal>(select avg(sal) from emp);
工资高于平均工资的人的姓名和薪水

·select e.ename,e.sal,e.deptno,t.deptno t_deptno,t.avg_sal from emp e join (select deptno,avg(sal) avg_sal from emp group by deptno) t on e.deptno=t.deptno and e.sal>avg_sal;
工资高于本部门平均工资的人的姓名和薪水

·方法一：> select e.ename,t.ename from emp e join (select empno,ename,mgr from emp) t n e.mgr=t.empno;
方法二： select e.ename,t.ename from emp e,emp t where e.mgr=t.empno;
显示出员工和他的经理人的名字（表的自连接）

·SQL> select e.ename,e.sal,e.deptno from emp e join (select deptno,max(sal) max_s
al from emp group by deptno) t on e.deptno=t.deptno and sal=max_sal;
每一个部门里边挣钱最多的哪个人的名字、工资、部门

|-表连接
-1993年的普通连接
-1999年的join on 连接：join 表名 on 条件
-十字交叉连接：cross join 表名,结果为笛卡尔乘机

|-等值连接·select ename,dname from emp,dept where emp.deptno=dept.deptno;连接emp，dept，显示出每名员工所属的部门名称1993年原始表连接，将连接条件写在where条件后，这样读起来不方便。

·select ename,dname from emp e join dept d on (d.deptno=e.deptno);
连接emp，dept，显示出每名员工所属的部门名称
1999年join on连接，将连接条件写在on后，这样读起方便。

|-非等值连接 

·SQL> select ename,sal,grade,losal,hisal from emp e join salgrade s on (e.sal>s.l
osal and e.sal