ORACLE数据库 常用命令和Sql常用语句

ORACLE

账号相关

如何获取表及权限

1.COPY表空间
backup scott
exp
登录管理员账号system
2.创建用户
create user han identified(认证) by mima default tablespace users（默认的表空间） quota（配额）10M on users;创建账号
分配权限
grant creatr session,create table,create view to 账号
grant connect to han; 赋予账号han登录权限
3.import the date引入数据

过程全选YES

imp

alter user scott account unlock; 解锁账号

 

对表的操作 

建表

--新增2个字段
alter table stu
add(
stu_tel varchar2(11),--电话
stu_addr varchar2(50)--地址
);
alter table stu
modify (stuName varchar2(20), 
stu_tel varchar2(8) 
); 
删1段alter table stu
drop column stuId; 
删除多段alter table stu
drop (stu_tel,stu_addr);

 

增
insert into table_name values (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

改
update table_name set stuAge = 27 where stuno='1001'；

update table_name set stuAge = 27,stuname='abc' where stuno='1002';

删
delete from emp3;
delete from stu where stuno='1003';

*查
select * from stu where stuno='1001';
select * from stu where stuno='1002';
查询所有列
select * from stu t
重复的只显示一条
select distinct stuno from stu;

select * from stu where stuno='1006';
select stuno,stuage from stu;

select distinct stuno from stu;
select * from stu where stuno ='1006' and stuage=29;
select * from stu order by stuage desc;
select * from stu order by stuage desc,stuno asc;

备份表
create table stuBAK as
select * from stu;

 

desc emp;描述EMP表有什么字段

 

一单条select语句（必须熟练！）

查询语句组成
1.
select * from emp; 查询emp表的所有的内容
select ename，sal*12 from emp；
select ename，sal*12 anuual_sal（anuual_sal表名可以随便写2个单词用下划线隔开 不然显示from不存在 不要占from的位置 简而言之 名字只能占一个 如果非得占2个 必须带上双一号“anuual sal”包住 默认引号内的是名字 而且显示是小写 可以认为 “”可以保持你输入时的状态 当然 中文也行） from emp；
select enamel||'哈哈（随便写字符串）' from emp; ||+'字符' 表示字符串拼接
select enamel||' 哈'哈（随便写字符串）' from emp; 是错误的 如果字符串里面有单引号 需要转义
select distinct deptno from emp; 重复的只显示一次
select distinect deptno，job from emp； 这个有重复的 这个不是表里自己和自己比 而是 2个表 deptno和job 2个表如果有重复的 只显示一次

2.from 那张表？

3.where 出现在单行语句 作为筛选条件优先执行
select ename，sal from emp where sal=800；
select ename，sal from emp where sal!=800；
select ename，sal from emp where sal>=800 and sal<=1500； 包含800和1500
select ename，sal from emp where sal between and 1500； 包含800和1500
select ename,sal,deptno from emp where sal not in (800,1500); 薪水不是800和1500的
select ename，sal，comm from emp where comm is null； 查出ename，sal，comm里面有null的表 之所以不用=因为 null是空值就是没值的意思
select ename，sal，comm from emp where comm is not null；
select ename，sal from emp where sal in （800,1500,2000); 选出sal=800或者1500或者2000的表
select ename，sal from emp where ename in （'SMITH','KING','JONES'）; 选出'SMITH','KING','JONES'表
select ename,sal from emp where deptno=10 or sal>1500;
select ename,sal,deptno from emp where deptno=10 or sal>1500;

4.<--group by>（必须掌握）（需求 每个部门的平均薪资）
select avg(sal)from emp group by deptno;

select avg(sal)from emp group by deptno , job;

select deptno ,job,max(sal)from emp group by deptno , job;

select ename,max(sal)from emp;错误 组函数（一个都不能忘！）(多行输入 只有一行输出)
select ename from emp where sal=(select max(sal)from emp);

select ename ,max(sal) from emp group by deptno;错误 ename必须出现在 max（）和group by 2个中一个，只有这样才唯一
select ename ,max(sal)from emp group by ename; 正确
select deptno,max(sal)from emp group by deptno;正确

5.having（需求每个部门平均薪资大于2000的有哪些 不能用where 因为where只能由于一条语句或者说一个表的筛选）
select avg(sal),deptno form emp group by deptno having avg(sal)>2000;

6.order by

select * from dept order by deptno desc; 降序排列
select empno,ename from emp; 升序
select empno,ename from emp order by asc; 升序
select empno,ename from emp where deptno!=10 order by empno asc; 部门标号不是10的 按升序排列的表
select ename,sal,deptno from emp order by deptno asc; 按部门编号升序
select ename,sal,deptno from emp order by sal asc;按薪水升序
select ename,sal,deptno from emp order by sal asc,ename desc; 薪水升序的情况下 名字倒序
select ename,sal*12 anuual_sal from emp where ename like '_A%' and sal>800 order by sal desc;

总结 1 select avg（sal），字段名 2 from 表 3 where 4 group by 5 having 6 order by

select avg(sal)from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;

二,子查询 (把他当成一张表！）

select ename ,max(sal) form emp;错误 因为这个max（sal）可以对应很多ename 不是一一对应关系
select ename,sal from emp where sal=(select max(sal)from emp);

需求 有哪些人的工资在平均工资之上

select ename,sal from emp where sal > (select avg(sal)from emp);

表连接

<--内连接>
<--写法1 常用>
select d1.dname , d1.loc, e.ename,e.mgr 
from dept d1, emp e 
where d1.deptno = e.deptno;
<--写法2 >
select d1.dname , d1.loc, e.ename,e.mgr 
from dept d1
inner join emp e 
on d1.deptno = e.deptno;

<--左外链接>
select d1.dname , d1.loc, e.ename,e.mgr 
from dept d1
left join emp e 
on d1.deptno = e.deptno;
<--右外链接>
select d1.dname , d1.loc, e.ename,e.mgr 
from dept d1
right join emp e 
on d1.deptno = e.deptno;

<--联合查询>
<--联合查询>
<--相同和合并>
select*from emp
union select * from emp;
<--直接相加>
select*from emp;
union all select *from emp2;

需求：按照部门分组后 每个部门挣钱最多的那个人
select ename,sal,deptno from emp where sal = (select max(sal)from emp group by deptno); 错误（单行 子查询 返回 多个值）
select ename,sal,deptno from emp where sal in (select max(sal)from emp) group by deptno;错误 （返回的是 符合3个值（3个部门最大工资）之一的所有可能 那么很有可能别的部门不是最大值的是别的部门的最大值也会选出来 结果就不对了！）

<--错误示范2个>
select ename,sal,deptno from emp where sal=(select max(sal)from emp) group by deptno;
select ename,sal,deptno from emp where sal in (select max(sal)from emp) group by deptno;

<--正确>
<--正确 每个部门挣钱最多的那个人>
1.(select max(sal)max_sal,deptno from emp group by deptno)t 命名这俩表是t join是连接
2.on (emp.sal=t.max_sal and emp.deptno = t.deptno); on是限制条件

select ename,sal from emp

join(select max(sal)max_sal,deptno from emp group by deptno)t

on (emp.sal=t.max_sal and emp.deptno = t.deptno);

<-- 需求每个部门平均薪水的等级（表连接 1先写出每个部门的平均薪水等级 2 和薪水等级那个表做连接）>

select deptno,avg_sal,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno)t
join salgrade s
on (t.avg_sal between s.losal and s.hisal);

<--员工名字和经理人名字>
select empno,ename,mgr from emp;
select empno,ename,mgr from emp;
<-- 自连接 自身和自身链接 一个表看成2个一样表起2个别名做链接>
select e1.ename ,e2.ename from emp e1 ,emp e2 where e1.mgr=e2.empno;
<--92年老版本过滤筛选条件where里面写链接表的条件和过滤条件的写法，99年将连接条件和过滤条件分开写>
select ename ,dname,grade from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and
job<>'clerk';
<--过滤和链接条件 99年的新写法 推荐这个写！>
<--等值链接>
<--cross join 交叉连接>
select ename ,dname from emp cross join dept;

<--92语法>
select ename,dname from emp,dept where emp.deptno =dept.deptno;
<--99年语法>
select ename,dname from emp join dept on (emp.deptno=dept.deptno);

<--非等值链接>
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

<--3张表做链接>
select ename ,dname,grade from emp e
join dept d on (e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';

<--自连接 用99新语法>
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.empno);
<--外链接 左外链接和右外链接 全外链接>
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);
select e1.ename,e2.ename from emp e1 right join emp e2 on (e1.mgr=e2.empno);

select ename , dname from emp e
join dept d
on(e.deptno = d.deptno);

select ename , dname from emp e
right outer join dept d
on(e.deptno = d.deptno);
<--全外链接>
select ename , dname from emp e
full join dept d
on(e.deptno=d.deptno);

<--每个部门平均薪水等级>
select deptno,avg_sal,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno)t
join salgrade s1
on (t.avg_sal between s1.losal and s1.hisal);

<--每个人的薪水等级>
select deptno,ename,grade from emp
join salgrade s
on (emp.sal between s.losal and s.hisal);
<--每个部门（每个员工平均薪水等级的）平均薪水等级>
select deptno, avg(grade)from
(select deptno,ename,grade from emp
join salgrade s
on (emp.sal between s.losal and s.hisal))t
group by deptno;
<-- 那些雇员是经理？>
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);

select stuno num,stuage age from stu;

 

子查询
select deptno from emp where ename ='SCOTT';

in慎用 尤其结果大的
select * from emp where deptno in('10','20');
select * from emp where deptno not in('10','20');

select * from emp where( deptno='10' or deptno= '20') and sal>2000;

e1是emp的取名
select * from emp e1 where sal> (select avg(sal)from emp e2 where e1.deptno=e2.deptno);

select dname from dept where deptno where =(deptno from emp where sal>3000);

select dname from dept d where exists (select * from emp e where d.deptno = e.deptno and sal>3000);

<--不相关子查询>
select dname from dept where deptno =(select deptno from emp where sal>3000);
select dname from dept where deptno = (select deptno from emp where ename ='SMITH');

 

三，骚操作
select stuno "学号“stuage 年龄 from stu;
拼接字符
select 'test'||to_char(sysdate,'yyyy-mm-dd')from dual;
select 's'|| stuno "学 号“stuage 年龄 from stu;

select '测试'，'s'|| stuno "学 号“stuage 年龄 from stu;

select to_char(sysdate,'yyyy-mm-dd')from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日 "hh24:mi:ss')from dual;

 

四，单行函数
select to_number('234')from dual;
select to_date('2017-09-09','yyyy-mm-dd')from dual;

select distinct stuno from stu;

 

五，聚合函数

5个聚合函数
所以数字类型的数字相加用和这个
（1）计数
select count(*)from emp;
（2）求和
select sum(empno) from emp;

（3）最小
select min(sal)from emp;
（4）最大
select max(sal)from emp;
（5）平均
select avg(sal)from emp;

 

分析函数（略）

截取字符函数
select substr('sdsfds',2) from dual;

select substr('sdsfds',2,4) from dual;
select substr('20170909120910',1,8) from dual;

NVL函数
如果是空 就打印后边的4
select nvl(null,4)from dual;
打印5
select nvl(5,4)from dual;

select nvl(comm,0)*1.1 from emp; 工作经验
上面为啥这么写呢？因为如果comm是null的话 *1.1不合适 所以 我们这么写
如果是null就改成0

判断字符是否一样 1一样 0不一样
select decode('男','女',1,0)from dual; 0
select decode('男','男',1,0)from dual; 1
select decode('男','女',1,'男'，0，2)from dual;
select decode('男','女',1,'男'，0，2)from dual;
select decode('男','女',1,''，0，2)from dual;

 

<分页查询>
伪列
<--分页>
<--伪列>
<rowid rownum>
;
<--先抓取5个 然后排序>
select sal from emp where rownum<6
order by sal desc;
<--收入前五>
select *from(select * from emp order by sal desc) where rownum<6;
select *from(select * from emp order by sal desc) where rownum<16;

<--先找到需要排序的表排序 2然后 对这个表进行rownum固化 3.对固化后的表加限定条件>
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc)
e
)
where rn=5;

数据迁移
1、找到数据文件 执行select*from 表； 导出sql文件:点 绿色的按钮 导出查询结果 得到sql文件
2.建表 可以COPY→ 查看 sql(v) 里面有多余的部分 不要 只要建表操作
3.导入sql 新建命令窗口 @ 找到要导入的sql 文件

 

oracle 数据库编程语言 pl/sql 过程化语言和结构化语言的语言
存储过程

declare声明 变量 常量赋值 c_rate_iner number(7,2) comstant ：=1.10; 变量 select ename,sal*c_rate_iner into v_name,v_rate from emp where empno='7788';
begin sql语句
exception 异常
end；

loop循环
DBMS_OUTPUT.PUT_LINE(''+变量)

if 条件 then
goto ming

else null；

end if

end loop；
<<ming>>

 

--变量类型 %type属性 他的意思可以根据别的表的数据类型定义自己的数据类型，
--表的某个字段数据类型 绑定 自己的数据类型，表的某个字段类型变成什么，他就跟着变成什么 >
例子
declare
--v_num 的数据类型是emp表下面的empno这个字段的数据类型
v_num emp.empno%type :=5566;
begin
dbms_output.put_line(v_num);
end;

--table数据类型 相当于java里面的数组 自定义类型 先声明一种类型 再用这个类型 声明一个变量
declare
--标准写法 type表明 我要自定义一个类型 type_table_emp_empno是类型名字 is table of 是一张表
--emp.empno%type表的类型是emp这个表的empno这个字段的类型（绑定） index by索引下标 binary_integer索引下标的类型
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
--v_empnos 类型名 和定义好的数组类型
v_empnos type_table_emp_empno;
begin
v_empnos(0):=7369;
v_empnos(1):=7370;
v_empnos(2):=7860;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;

--record类型 相当于java里面的类
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);

v_temp type_record_dept;

begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;

--

使用 %rowtype属性 声明 record变量 （解决了原来的表dept改变了 record会跟着编号 解决了维护麻烦的问题）
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;