Oracle 操作语句大全
1。desc（描述） emp    描述emp这张表
2.desc    dept      部门表
3.desc salgrade      薪水等级
4。select ＊from table 查表中的元素
5.dual    是系统中的一张空表
6。select *from dual
7.select sysdate from dual 取出系统时间
8。select ename，sal*12 "annul sal”（取的别名） from emp； 查用户姓名和用户的年薪
9。任何含有空值的数学表达式的值都是空值
select ename,sal*12+comm from emp;
10.select ename｜|sal from emp 其中的|｜相当于将sal全部转化为字符串
11.表示字符串的方法
select ename ||’ajjf’ from emp；
12.如果其中有一个单引号就用2个单引号来代替他
select ename||'sakj’ 'lds'from emp;
13。select distinct deptno from emp    (去除部门字段中重复的部分，关键字distinct)
14。select distinct deptno,job from emp;(去除这2个字段中重复的组合)
15.select ＊from dept where deptno=10；    取出条件(取出部门编号为10的记录）
16。select * from emp where ename='CLIRK'; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)
17。select ename，sal from emp where sal>1500; 取出部门中薪水大于1500的人的姓名
18。select ename，sal,deptno from emp where deptno<〉 10 取出部门中的部门号不等于10的
19.select ename，sal，deptno from emp where ename>’CBA' 取出部门中员工名字大于CBA的员工（实际比较的是ACIIS码)
20。select ename，sal from emp where sal between 800 and 1500
  select ename,sal from emp where sal〉=800 and sal<=1500;  （取出800和1500之间的数)
21.select ename,sal,comm from emp where comm is null （选出其中的空值)
  select enmae，sal,comm from emp where comm is not null（选出其中的非空值）
22.select ename，sal,comm from emp where sal in （800，1500，2000);取出这3者之中的
  select ename,sal,comm from emp where ename in(’simth')；
23。select ename，sal,hiredate from emp where hiredata>'3-04月-81’；宣传符合条件的日期
24.select ename，sal,from emp where sal〉1000 or deptno=10；      出工资薪水大于1000或者部门号等于10的员工
25。select ename,sal from emp where sal not in（500,1000);        查薪水不在500到1000的员工姓名和月薪
26.select ename,sal from emp where ename like ’％ALL%’；
  select ename，sal from emp where ename like ’_％A％';        查姓名中含有ALL的客户信息，一个横线代表一个通配符
27。select ename，sal from emp where ename like '_%$％％’ escape '＄’; 自己指定转易字符
  select ename,sal from emp where ename like ’_%\％％'；            查中间含有％相匹配的客户信息，运用转易字符
28.select ＊ from dept order by deptno                            对表中元素按部门号排序
  select *from dept order by deptno desc                        默认为升序，可以用desc按降序
29.select ename，sal from emp where sal <〉1000 order by sal desc  按照查询条件来查询，并排序（asc升序排列）
30.select ename，sal*12 from emp where ename not like ’_％A%' and sal>800 order by sal desc
31。select lower(ename） from emp 将ename都转化为小写    lower是函数能将字母转化为小写
32。select ename from emp where lower(ename） like ’_％a％’; 出ename 中所有的含有a的字符
33.select substr(ename,2，3) form emp            从第2个字符开始截取3个字符
34.select chr（65） from dual;          将65转化为字符
35。select ascii('A’) from dual        将ACSII码转化为字符串
36。select round(23。565）from dual    四舍五入
36.select round(23,4565,2）from dual 四舍五入到第二位
37。select to_char(sal，'＄99。999.9999’） from emp 按指定格式输出
  select to_char(sal,’L99,999,9999’) form emp L代表本地字符
38。select hiredate 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        二四小时制显示系统时间
39。select ename，hiredate from emp where hiredate 〉 to_date('2005-2—3 12：32:23','YYYY—MM-DD HH：MI：SS'）;
40 select sal from emp where sal〉to_number(’$1，250.00’,’$9,999.99’)； 取出比它大的一切字符串（把特定格式的数字转化成字符）
41 select ename,sal+nvl(comm，0) from emp;    讲comm值为空的用0来替换，单行函数（以一条记录为条件)一条对一条
42。select Max（sal） from emp；
  select Min（sal） from emp；
  select avg（sal） from emp;
  select sum（sal) from emp;
  select count（*） from emp；                          查看表中一共有多少条记录
  select count（*) from emp where deptno=10；          查部门10一共有多少人；
43。select avg（sal)，deptno from emp group by deptno；  按部门号进行分组
  select deptno，job，max（sal) from emp group by job,deptno; 按工作和部门号进行分组；
44。select ename from emp where sal=（select max(sal) from emp)； 子查询，查部门中薪水最高的员工姓名
45.group by 注意：出现在select列表中的字段,如果没有出现在组函数中必须出现在group by子句中
46.select avg（sal）,deptno from emp group by deptno having avg(sal）>2000;    选出部门中平均薪水大于2000的部门，
47.select * from emp where sal〉100 group by deptno having ...。。.。。。。order by。。.。。.。.
  先取数据--过滤数据———--—分组-———对分组限制-—----—排序
48。select avg（sal) from emp where sal>2000 group by deptno having avg(sal）>1500 order by avg（sal) desc;
  查部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列
49.select ename from emp where sal〉（select avg(sal) from emp）；
  查出员工中薪水位于部门平均薪水之上的所有员工
50.select ename，sal from emp join（select max(sal） max_sal from emp group by deptno) t on（emp.sal=t,max_sal and emp.deptno=t.deptno）;
  查每个部门中薪水最高的
51。select e1。ename,e2。ename from emp e1,emp e2 where e1。pno;          表的自连接
52.select dname，ename from emp cross join dept        交叉连接，笛卡尔
SQL99中的新语法
53.select ename,dname from emp join dept on（emp.deptno=dept.deptno);
54.select ename,dname from emp join dept using(deptno);            查emp和dept表中deptno相同的部分。
55.select ename，dname,grade from emp e join dept d on(e.deptno=d。depno）
                                      join salgrade s(e。sal between s.losal and s.hisal）  （三表查）
                                      where ename not like '_％A%'；
56。select e1。ename，e2.ename from emp e1 join emp e2 on（e1。mgr=e2。deptno）; 表的自连接
57。select e1。ename,e2。ename from emp e1 left join emp e2 on（e1。mgr=e2.deptno） 左外表连接
  select ename,dname from emp e right join dept d on(e.deptno=d。deptno)右外连接
  select ename，dname from emp e full join dept d on(e.deptno=d.deptno)全连接
58。求部门中薪水最高的
  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)；
59.求部门中薪水等级的平均值
  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;
60。查雇员中哪些是经理人
  select ename from emp where empno in（select mgr from emp）；
61。select distinct e1.sal from emp e1 join emp e2 on(e1.sal〈e2。sal); 自连接(不用组函数求出最高薪水）
  select distinct sal from emp where not in (select ename from e1。sal from emp e1 join emp e2 on(e1。sal〈e2。sal））；
62.select deptno from (select avg（sal) max_sal deptno from emp group by deptno） where max_sal=（select max(avg_sal） from (select
avg（sal) avg_sal deptno from emp group by deptno)）；          查部门中部门薪水最大的部门号
63。求平均薪水最大的部门的部门编号
  select deptno，avg_sal from（select avg(sal） avg_sal,deptno from emp group by
  deptno）where avg_sal=(select max（avg(sal）） from emp group by deptno）;
DML语句：更、删、改、查
创建权限， conn sys/admin as sysdba
          grant create table,create view to scott;
首先在C：下面建个文件夹备份文件
1。createNewUser方法
1.—-backup scott
    exp
2.create user(创建用户）用超级管理员模式进入
  create user yun identified by kang1234 default tablespace users quota 10M on users;
  grant create session,create table,create view to kafei（给kafei这个用户授予权限）
3.import the data（导入备份数据）
  imp
2。insert
insert into dept values （50，'game'，'bj’) 插入一条记录
insert into dept2 （deptno，dname） values （78，’games’)； 插入指定的几条记录
insert into dept2 select ＊from dept      插入指定的表（表结构要一样)
rollback；        回退
create table emp2 as select * from emp;    创建数据库表2来备份emp这张表
3.update emp2 set sal=sal*12 where deptno=10；    update的用法
4.delete from dept2 where deptno〈25 ;          删除语句的用法
DDL语言
1.创建表：create table t(a varchar2(10)）;
2.drop table t    删除表
3。commit  所有的提交，所有修改都结束了.对于rollback无效，一个事务开始于第1条DML语句
碰到执行DDL DCL语句事务自动提交 对于rollback无效
建表语句
建学生信息表:
create table stu
（id number（6），
name varchar2（20） constraint stu_name_nn not null，
sex number（1），
age number(3),
sdate date,
grade number（2) default 1，
class number(4）,
email varchar2（50) unique    （唯一约束）
）;
非空 唯一 主键 外键 chick
create table stu
（id number（6) primary key，(主键约束）
name varchar2(20) constraint stu_name_nn not null，(非空约束)
sex number（1),
age number(3)，
sdate date，
grade number（2) default 1，
class number（4),
email varchar2(50），
constraint stu_name_uui unique(email,name) 组合性约束
)；
主键约束方法二
create table stu
(id number(6),
name varchar2(20) constraint stu_name_nn not null,(非空约束)
sex number(1)，
age number(3）,
sdate date，
grade number（2） default 1,
class number(4) references class(id）,（参考class 这张表,参考字段)
email varchar2(50）,
constraint stu_id_pk primary key(id），
constraint stu_name_uui unique(email，name） 组合性约束
);
外键约束
create table class
(id number（4) primary key,（id为被参考字段,被参考的字段必须是主键）
name varchar2（20） not null
）
create table stu
(
id number（6),
name varchar2（20) constraint stu_name_nn not null，(非空约束）
sex number（1）,
age number(3），
sdate date,
grade number（2） default 1,
class number(4)
email varchar2(50），
constraint stu_class_fk foreign key(class) references class(id）,
constraint stu_id_pk primary key（id),
constraint stu_name_uui unique(email，name) 组合性约束
)；
像外键中插入关键字，
1.insert into class values(1000，'c1'）；
2.insert into stu（id,name，class，email) values(1,'a',1000,’a’)；
3.alter table stu add（addr varchar（20));添加表的结构
4.alter table stu drop(addr)； 删除表结构
5。alter table stu modify(addr varchar2(150）);修改精度
6.alter table stu drop constraint stu_class_fk； 删除约束条件
7.alter table stu add constraint stu_class_fk forengn key（class) references class（id），添加约束条件
查当前用户下有哪些表和哪些视图及哪些约束
8.select table_name from user_names
9。select view_name from view_names
10。select constraint_name,table_name from user_constraints;
desc dictionary数据字典表
desc user_tables当前用户下面有多少张表
select table_name from user_tables; 查当前用户有多少张表
索引：
创建索引
create index idx_stu_email on stu(email）;
drop index idx_stu_email；
查索引
select index_name from user_indexes；
索引读的速度快了，插入速度变慢
view 视图
视图赠加了维护的量
序列：
create table arcticle
(id number，
title varchar2(1024），
cont long
)；
序列的创建sequence产生独一无二的序列,而且是oracle独有的
create sequence seq;
select seq。nextval from dual； 查序列号
insert into arcticle values(seq。nextval,'a’，'b'）;往表中插入序列
数据库设计的3范式
第一范式: 设计任何表都要有主键，列不可分
第二范式： 如果有2个主键的话,不能存在部分依赖
第三范式， 不能存在传递依赖
PL—sql
例子1：
SQL〉 set serveroutput on;
SQL> begin(必要的-—程序开始执行)
2 dbms_output.put_line('hello world’)；
3 end;（结束）
4 /
例子2：
SQL> declare
2 v_name varchar2(20）；
3 begin
4 v_name：=’myname’；
5 dbms_output。put_line(v_name);
6 end；
7 /
myname
例子3：
SQL〉 declare
2 v_num number：=0；
3 begin
4 v_num：=2/v_num；
5 dbms_output。put_line（v_num）；
6 end；
7 /
declare
*
ERROR 位于第 1 行:
ORA-01476： 除数为 0
ORA—06512: 在line 4
例子4：
declare
v_num number：=0;
begin
v_num:=2/v_num；
dbms_output。put_line(v_num)；
exception
when others then
dbms_output。put_line（'error’);
end；
/
变量声明的规则
1.变量名不能够使用保留字,如from,select等
2.第一字符必须是字母。
3。变量名最多包含30个字符
4.不要与数据库的表或者列同名
5。每一行只能声明一个变量
常用变量类型
1。 binary_interger,整数，主要用来计数，而不是用来表示字段类型
2. number 数字类型
3. char  定长字符串
4。 varchar2 变长字符串
5。 date    日期
6。long    长字符串，最长2GB
7。boolean  布尔类型，可以取true false 和null的值
例5：
declare
    v_temp number（1）;
    v_count binary_integer：=0；
    v_sal number（7,2)：=4000.00;
    v_date date:=sysdate；
    v_pi constant number（3,2):=3。14；
    v_valid boolean：=false;
    v_name varchar2（20) not null：='myname';
begin
    dbms_output.put_line('v_temp value：’｜｜v_temp);
end；
用-—可以注释一行
例6:
declare
    v_empno number(4）;
    v_pno%type;
    v_empno3 v_empno2%type;
begin
    dbms_output.put_line(’test’）;
end;
例7
table变量类型
set serveroutput on；
declare
    type type_table_emp_empno is table pno%type index by binary_integer;
    v_empnos type_table_emp_empno;
begin
    v_empnos(0)：=7369；
    v_empnos(2）:=7869；
    v_empnos（—1）：=9999；
    dbms_output.put_line（v_empnos（—1)）;
end;
例8
Record 变量类型
set serveroutput on；
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.loc：='aaaa’;
    v_temp。loc:=’bj';
    dbms_output.put_line（v_temp。deptno｜｜' '｜｜v_temp.dname)；
end；
例9：
使用％rowtype声明record变量(表结构的变化同时也能代理储存过程的变化）
set serveroutput on；
declare
    v_temp dept%rowtype；
begin
    v_temp。deptno：=50;
    v_temp。loc：='aaaa';
    v_temp.loc:=’bj';
    dbms_output。put_line(v_temp。deptno｜|' ’｜｜v_temp.dname)；
end；
例10;
declare
v_ame％type;
v_sal emp.sal%type;
begin
select ename，sal into v_name,v_sal from emp where empno=7369；(将ename和sal的值放在v_name和v_sal里面）
例11:
declare
v_name emp。ename%type;
v_sal emp.sal％type;
begin
select ename，sal into v_name，v_sal from emp where empno=7369；
dbms_output。put_line(v_name||' ’||v_sal）；
end;
dbms_output。put_line(v_name｜|' ’||v_sal）;
end；
例12:
declare
v_deptno dept.deptno%type:=50;
v_dname dept.dname％type:=’aaaa’；
v_loc    dept.loc％type:='bj';
begin
insert into dept2 values（v_deptno，v_dname,v_loc）；
commit;
end；
例13:
declare
v_deptno emp2。deptno%type：=50；
v_count number；
begin
update emp2 set sal=sal/2 where deptno=v_deptno;
dbms_output.put_line(sql％rowcount ||'条记录被影响’);（sql为关键字，代表上一条语句
commit；
end;
/
例14：
declare
v_deptno emp2。deptno%type：=50;
v_count number；
begin
—-update emp2 set sal=sal/2 where deptno=v_deptno;
  select deptno into v_deptno from emp2 where empno=7369；
dbms_output。put_line（sql％rowcount ｜｜’条记录被影响'）;(sql为关键字,代表上一条语句
commit;
end；
/sql约束条件大于0
例15
declare
v_deptno emp2。deptno％type:=50;
v_count number;
begin
--update emp2 set sal=sal/2 where deptno=v_deptno；
—-select deptno into v_deptno from emp2 where empno=7369；
select count（*） into v_count from emp2;                        （select必须和into一起使用）
dbms_output.put_line(sql％rowcount ||’条记录被影响'）;
commit;
end；
/
PL/SQL里面执行DDL语句
begin
execute immediate ’create table T(nnn varchar2(20） default ''aaa'’)’;
end;
PL/SQL的分支语句：
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp
    where empno=7369；
  if(v_sal〈1200) then
    dbms_output.put_line(’low')；
  elsif(v_sal<2000) then
    dbms_output.put_line（'middle’)；
  else
    dbms_output。put_line（'high'）；
  end if;
end;
pL/Sql循环
declare
  i binary_integer：=1；
begin
  loop
      dbms_output.put_line（i）；
      i:=i+1;
      exit when（i〉=11）；
  end loop;
end；
PL/SQL for循环
begin
for k in 1。。10 loop
    dbms_output.put_line（k);
end loop；
for k in reverse 1.。10 loop
    dbms_output.put_line(k);
end loop;
end;
exception 捕获异常
declare
  v_temp number（4)；
begin
  select empno into v_temp from emp where deptno=10;
exception
  when too_many_rows then
  dbms_output。put_line('太多记录了’);
when others then
  dbms_output.put_line(’error')；
end;
没有数据错误
declare
  v_temp number（4）;
begin
  select empno into v_temp from emp where deptno=2222；
exception
  when no_data_found then
  dbms_output。put_line(’没数据’);
when others then
  dbms_output。put_line(’error’)；
end;
/
错误处理
create table errorlog
（
id number primary key,
errcode number，
errmsg varchar2(1024),
errdate date
）；
create sequence seq_errorlog_id start with 1 increment by 1；
declare
v_deptno dept。deptno％type：=10；
v_errcode number；
v_errmsg varchar2（1024）；
begin
delete from dept where deptno=v_deptno;
exception
when others then
    rollback；
    v_errcode：=SQLCODE;
    v_errmsg：=SQLERRN;
    insert into errorlog values（seq_errorlog_id。nextval,v_errcode，v_errmsg，sysdate）；
    commit；
end；
游标
declare
  cursor c is
          select * from emp;
  v_emp c%rowtype；
  begin
      open c；
      fetch c into v_emp；          ——(取游标的第一个值插入v_emp,在不断的循环)
      dbms_output.put_line(v_emp。ename）；
      close c；
  end；
例子
declare
  cursor c is
      select * from emp;
      v_emp c％rowtype；
    begin
      open c;
    loop
      fetch c into v_emp;
      exit when(c％notfound);
      dbms_output.put_line（v_emp。ename);
    end loop；
    close c；
end；
declare
  cursor c is
      select ＊ from emp;
      v_emp c%rowtype;
    begin
      open c；
      fetch c into v_emp;
    while（c%found） loop
      dbms_output.put_line（v_emp。ename);
      fetch c into v_emp；
    end loop;
    close c;
end;
declare
  cursor c is
        select ＊ from emp；
  begin
        for v_emp in c loop
        dbms_output。put_line(v_emp。ename)；
    end loop；
end；
带参数的游标
declare
  cursor c（v_deptno emp。deptno％type，v_job emp.job%type)
  is
    select ename，sal from emp where deptno=v_deptno and job=v_job;
begin
  for v_temp in c（30，'chick') loop
        dbms_output.put_line(v_temp。ename）；
  end loop；
end;
可更新的游标
存储过程
create or replace procedure p
is
  cursor c is
        select ＊ from emp2 for update；
begin
  for v_emp in c loop
        if (v_emp。deptno=10) then
        update emp2 set sal=sal+10 where current of c;
      elsif(v_emp。deptno=20) then
        update emp2 set sal=sal+20 where current of c；
      else
        update emp2 set sal=sal+50 where current of c；
      end if;
    end loop；
    commit;
end;
exec p执行存储过程
begin
p:
end；
带参数的存储过程
create or replace procedure p
(v_a in number，v_b number,v_ret out number， v_temp in out number）
is
begin
if（v_a〉v_b) then
    v_ret:=v_a;
  else
    v_ret：=v_b;
end if;
    v_temp：=v_temp+1；
end;
调用存储过程
declare
v_a number：=3；
v_b number：=4;
v_ret number;
v_temp number:=5；
begin
  p(v_a，v_b，v_ret,v_temp）；
  dbms_output.put_line（v_ret);
  dbms_output。put_line(v_temp）；
end；
show error返回错误信息
删除存储过程
存储过程中的函数
create or replace function sal_tax
    （v_sal number）
    return number
is
begin
if(v_sal〈2000） then
    return 0.10;
elsif(v_sal〈2750） then
    return 0。5;
else
    return 0。20;
end if；
end;
/
触发器
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno=：NEW.deptno where deptno=：OLD。deptno;
end;
/

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。