oracle sql语句

爱被打了一巴掌 2022-08-26 05:55 343阅读 0赞

、 http://127.0.0.1:5560/isqlplus/ 访问自己的或者别人的oracle。

2、 isqlplus不用装客户端也行。

3、 sqlplus sys/bjsxt as sysdba

4、 desc emp/dept/salgrade;//显示表结构

5、 select sysdate from dual;//显示日期

6、 select a\*12 "nihao Y" from dual;//别名中有空格用双引号

7、 select emname||'aa' from emp;//连接字符

8、 select 'wo'||'a''a' from emp;两个单引号代表一个一个点印单引号,返回结果为woa'a

9、 select distinct a from dual;查找唯一

10、 select name from dual where name is null//空值查找。

11、 select name from dual where name like '%a%'//包含a的所有name，like ‘%\\%%’包含%的name。 /为转义字符

12、自己定义转义字符

select name from dual where name like '%$%%' escape'$'//name里包含 %的纪录，并定义了$为转义字符。

13、 select name from dual order by id desc/asc ;--按降序/升序排列

14、 select name from dual order by id desc,age asc;--先按id降序排列再按age升序排列。

15、 select lower(name) from dual;--name 都小写

16、 select substr(name,2,3)from dual;--把name从第二个字母开始截取3个字符。

17、 select chr(65)from dual;--转换成A

18、 slect ascii（‘A’）from dual;--转换成ascii码65；

19、 select to\_char(sal,$99,999,9999)from emp

\--结果 $800.0000 ,$1,600.0000。如果把$换成L则输出￥800.0000 ,￥1,600.0000。如果把99，999，9999换成00，000，0000则输出为$00800.0000，$01,600.0000

20、 select to\_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual ;--格式化时间,hh为12进制小时，hh24为24进制小时。

21、 select hierdate where hierdate> to\_date('1981-2-30 12:34:51','yyyy-m-dd hh24:mi:ss');--格式化特定字符串为日期

22、 select sal from emp where sal >to\_number('$1,250.00','$9,999,00');

23、 select to\_char (12.356,'999,99') from dual;--12.35

select round(12.356,'999,99') from dual;--12.36

24、组函数： sum avg count max min

25、 select count(name)from emp;--统计name不为null的纪录数。

\----------------------------------

1、 select deptno,max(sal) from emp group by deptno where deptno=10；--不对，设计分组的条件必须用having而不能用where。

2、一条综合查询

select avg(sal)

from emp

where sal > 1200 --重点

group by deptno

having avg(sal) > 2000

order by avg(sal) desc

3、 select ename

from emp

where sal in (select max(sal) from emp group by deptno) ； \--条件返回多条值 ，肯定会出错，因为sal满足的是in后（）内的多条值中的一条即可。所以只出一个结果。

4、 sql1992不支持全外连接，sql1999支持

select ename,dname from emp e full join dept d on (e.deptno=d.deptno)； \--把全部记录输出，相当于同时用了左右连接。

5、笛卡尔积出现的例子

select ename,dname from emp,dept;--没有 where条件，输出笛卡尔积

6、不用组函数求最高工资

select sal

from emp

where sal not in

(select distinct (e1.sal) from emp e1, emp e2 where e1.sal < e2.sal)

7、可以把一个很长的 sql当作一个view

求部门平均工资等级最低的部门名称

select dname

from dept

where deptno =

(select deptno

from (select avg(grade) avgrade, deptno

from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno)

where avgrade =

(select min(avgrade) avgrade

from (select avg(grade) avgrade, deptno

from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno)))

可以创建视图  deptsal

create view deptsal as (select avg(grade) avgrade, deptno

from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno)

则 sql语句可以简写成

select dname

from dept

where deptno =

(select deptno

from deptsal

where avgrade = (select min(avgrade) avgrade from deptsal))

8、取工资前五名 用 rownum

select t.\* from (select ename from emp order by sal desc )t where rownum <5 ； \--rownum只能和<或<=使用

select t.\* from (select ename from emp order by sal desc )t where rownum >5； \--错误

要想取 5到8位只能这么取（论坛分页显示用）

select r, ename

from (select ename, rownum r

from (select ename from emp order by sal desc))

where r >= 5

and r <= 8

\---------------------------------

1、 conn sys/bjsxt as sysdba ;--系统管理员登陆

2、查看表所在的表空间

select TABLESPACE\_NAME from all\_tables where table\_name='EMP';

\------------------导出数据库 \-----------------------

2、导出某用户下的所有数据

进入一个空文件夹；

exp scott/tiger@fxjsbps\_test ； \--执行exp命令

3、创建用户

create user ynp identified by 507 --用户 ynp 密码507

default tablespace users --默认的表空间是  users

quota 10M on users; --分配 10M空间，也就是这个用户只能存10M的东东。

分配权限

grant create session, --授予连接数据库权限

create table, --授予创建表的权限

create view to ynp; --授予创建视图的权限；

4、导入数据库

进入数据库备份文件所在的文件夹中；

ynp/507 -- 要导入数据的用户名

scott --只导入 socct这个用户的数据（这些表从这个用户导出）

\--------------------------------------

1、备份数据

create table dept2 as select \* from dept;

2、插入数据  insert（三种方式）

insert into dept2 values(50,'jim','beijing')；

insert into dept2 (deptno,dname) values(60,'tom')；

insert into dept2 select \* from dept； \--注意没有values

delete from dept2 where deptno = 20; --删除一行或多行 .

3、 dml语句

select、 update、insert、delete 后直接跟表名

\--------------------------事务 \------------------------

1、事务的开始起始于第一条 dml语句；

\----------------------------------

1 、 varchar2的最大储存是4k

2、创建表

create table student (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)references class(id), --外键约束

email varchar2(50) unique,--创建唯一约束

//consraint student\_id\_pk primary key(id, name) --也可以这样创建主键 ,也可以两个主键

//constraint stu\_class\_fk foreign key(class)references class(id) --外键约束

consraint student\_name\_email\_uniq unique(name,email) --表级约束条 件  name和email字段同时不能重复

)

create table class (

id number(4) primary key, 被参考的必须是主键

name varchar2(10) not null

)

\--------------------------------------

1、 alter table student add (adddress varchar2(10)) ;--添加字段

2、 alter table student drop (adddress)；--删除某个字段

3、 alter table student modify (adddress varchar2(50))；--修改某个字段

4、 alter table student drop constraint stu\_class\_fk;--删除约束

5、 alter table student add constraint stu\_class\_fk foreign key(class)references class(id);--添加约束

6、几个数据字典表

desc user\_tables; --查看当前用户有多少张表 .

select table\_name from user\_tables; --查看当前用户有哪些张表 .

select view\_name from user\_views;--查看当前用户有哪些张视图 .

select constraint\_name from user\_constraints;--查看当前用户有哪些约束

select \* from dictionary --查看数据字典表

select \* from User\_Tables --用户表

select \* from user\_views --用户视图

select \* from user\_constraints --用户约束

\------------------------------------------------

1、 create index idx\_stu\_email on students(email);--创建索引

drop index idx\_stu\_email;--删除索引

当创建主键约束或唯一约束或着两者组合时索引自动创建。

索引提高查询速度，减慢修改效率；

\-------------------------视图 \------------

视图增加了维护难度（如修改了表结构），但简化了查询；

隐藏敏感信息；

create view v$\_sal as select sal from emp;--创建视图

\-------------------------序列 \---------------------

1、 create sequence seq ；--创建序列对象

create sequence seq start with 1 increment by 1; --从 1开始,每次递增1。

select seq.nextval from dual； \--取序列值

insert into student (id,name)values(seq.nextval,'zhangsan')； \--保证了id不重复

drop sequence seq； \--删除序列对象

\-------------------------- 三范式 \-----------------

1、三范式目的是尽量少的冗余数据；

2、要求：

第一范式：要有主键，列不可分；（不能像 xx\_xy\_zz进行设计）

第二范式：不能存在部分依赖（不是主键的字段不能部分依赖主键）；

第三范式：不能存在传递依赖（如：学号（主键） <-班级编号<-班级信息）

\--------------------------PL/SQL------------------------------------

1、一个简单的程序

set serveroutput on;

begin

dbms\_output.put\_line('HelloWorld!');

end;

/ --执行语句

2、一个简单的程序块

declare

v\_name varchar2(20);

begin

v\_name:='i am ynp';

dbms\_output.put\_line(v\_name);

end;

3、一个完整的简单程序

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.每一行只能声明一个变量

\-------------------------------------

4、变量声明尽量以 v\_ 开头

5、常用变量类型

binary\_integer ：整数，主要用来计数而不是用来表示字段类型

number :数字类型

char ：定长字符串类型

varchar2 ：变长字符串类型 最大 4K

date： 日期

long：长字符串，最长 2G

boolean：布尔类型，可以取值为  false，True，null。null参与运算值不确定，所以最好声明变量时就给它赋值。

6、变量声明

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; --constant相当 java里的final(常量)

v\_valid booolean := false; --不能打印出布尔值 .

v\_name varchar2(20) not null :='ynp';

begin

dbms\_output.put\_line('v\_temp is'||v\_temp); --为连接符

end;

7、变量声明，使用 %type属性

声明变量的数据对应表的某个字段的数据，当表结构改变时（如把 number(7,2)，改为number(8,3)时，必须修改相应程序），用%type属性可以解决该问题。

例：

declare

v\_empno number(4);

v\_empno2 emp.empno%type; --empno2数据类型于 emp表中的empno字段相同；

v\_empno3 v\_empno2%type;--v\_empno3数据类型于 v\_empno2 数据类型相同

begin

dbms\_output.put\_line('Test');

end;

\-----------------------自定义变量 \---------------------

1、 table 变量类型(相当于java中的数组)

declare

type type\_table\_emp\_empno is table of emp.empno%type index by binary\_integer;

v\_empnos type\_table\_emp\_empno;

begin

v\_empnos(0) := 356;

v\_empnos(-1):=12;

v\_empnos(2):=111;

dbms\_output.put\_line(v\_empnos(-1));

end;

说明： table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（empno字段）；

下标值可以为负值；

2、 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.loc:='bj' ;

v\_temp.dname:='aaa';

dbms\_output.put\_line(v\_temp.deptno||' '||v\_temp.dname);

end;

但上述 dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动：

使用 %rowtype声明record变量

declare

v\_temp dept%rowtype;

begin

v\_temp.deptno:=50;

v\_temp.loc:='bj' ;

v\_temp.dname:='aaa';

dbms\_output.put\_line(v\_temp.deptno||' '||v\_temp.dname);

end;

errcode := SQLCODE; --出错代码

errmsg := SQLERRM  \--出错信息

\-----------------------------pl/sql中的 sql语句----------------------

1、 select语句

有且只有一条返回值，且必须加 into

例子：

declare

v\_ename emp.ename%type;

v\_sal emp.sal%type;

begin

select ename,sal into v\_ename,v\_sal from emp where empno=7369; --有且只有一条返回值，  \--且必须加into

dbms\_output.put\_line(v\_ename||' '||v\_sal);

end;

1.2

declare

v\_emp emp%rowtype;

begin

select \* into v\_emp from emp where empno=7369; --有且只有一条返回值，  \--且必须加into

dbms\_output.put\_line(v\_emp.ename);

end;

2、 update 、delete、insert语句和sql中相同，可能和变量混用；

declare

v\_deptno emp2.deptnpo%type:=10;

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;

dbms\_output.put\_line(sql%rowcount||'条记录被修改 '); --sal%rowcount 返回记录数

commit;

end;

3、执行 ddl或DCL语句--（记住有execute immediate）

begin

execute immediate 'create table t(nn varchar(2) default ''a'')';

end;

4、 if语句

declare

v\_sal emp.sal%type;

begin

select sal into v\_sal from emp where empno =7839;

if v\_sal <= 800 then

dbms\_output.put\_line('low') ;

elsif v\_sal >= 2800 then --不是 elseif

dbms\_output.put\_line('high');

else

dbms\_output.put\_line('middle');

end if; --注意有这条语句

end;

5、 while语句

declare

k binary\_integer:=1;

begin

while ( k <11 ) loop

dbms\_output.put\_line(k) ;

k:=k+1;

end loop;

end;

6、 do ..while语句

declare

k binary\_integer:=1;

begin

loop

dbms\_output.put\_line(k) ;

k:=k+1;

exit when(k>=11);

end loop;

end;

7、 for循环

declare

k binary\_integer:=1;

begin

for k in 1..10 loop

dbms\_output.put\_line(k) ;

end loop;

end;

declare

k binary\_integer:=1;

begin

for k in reverse 1..10 loop --加上 reverse为逆序打印

dbms\_output.put\_line(k) ;

end loop;

end;

\-------------------游标（ pl/sql重点）-----------------

1、 plsql中select语句只能返回一条语句，要想返回多条语句，用游标。

declare

cursor c is

select \* from emp;

v\_emp c%rowtype;

begin

open c;

fetch c into v\_emp; --fetch完后自动定位到下条记录

dbms\_output.put\_line(v\_emp.ename) ; --不能放在 exit前，否则最后一条记录打印两次

close c;

end;

2、简单循环

declare

cursor c is

select \* from emp;

v\_emp c%rowtype;

begin

open c;

loop

fetch c into v\_emp;--fetch完后自动定位到下条记录

exit when(c%notfound);

dbms\_output.put\_line(v\_emp.ename) ; --不能放在 exit前，否则最后一条记录打印两次

end loop;

close c;

end;

3、 while循环

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;

4、 for循环 （循环时最简单）

declare

cursor c is

select \* from emp;

begin

for v\_emp in c loop --不用定义 v\_emp，不用打开关闭游标了

dbms\_output.put\_line(v\_emp.ename) ;

end loop;

end;

5、带参数的游标

declare

cursor c(v\_deptno emp.deptno%type,v\_job emp.job%type) is

select \* from emp where deptno = v\_deptno and job= v\_job;

begin

for v\_emp in c(30,'CLERK') loop

dbms\_output.put\_line(v\_emp.ename) ;

end loop;

end;

6、可更新的游标

游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标；

declare

cursor c is

select \* from emp2 for update;

begin

for v\_emp in c loop

if(v\_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ; --修改定位到的当前记录，注意形式

elsif(v\_emp.sal>=2000) then

delete from emp2 where current of c;

end if;

end loop;

commit; --提交

end;

\-------------------------存储过程 \--------------------

1、把过程的 declare变成 create or Replace produce p is 就行。

\--declare

create or replace procedure p

cursor c is

select \* from emp2 for update;

begin

for v\_emp in c loop

if(v\_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ;

elsif(v\_emp.sal>=2000) then

delete from emp2 where current of c;

end if;

end loop;

commit;

end;

创建了存储过程不代表运行了存储过程；

运行此存储过程 ：

方式一  exec p；

方式二

begin

end;

2、带参数的存储过程

in 相当于程序里的参数，供传入用，在存储过程不能改变其值；

out 相当于程序里的返回值，在存储过程中可以为其赋值传出；

in out 既可以当参数又可以当返回值用；

不带上述说明符默认为 in类型；

下例中 v\_a v\_b 为in类型

v\_c 为 out类型

v\_d 为 in out 类型

create or replace procedure p

(v\_a in number,v\_b number,v\_c out number,v\_d in out number)

begin

if(v\_a > v\_b) then

v\_c := v\_a;

else

v\_c := v\_b;

end if;

v\_d := v\_d+1;

end;

\---> 调试时：

可以在命令窗口调试，出错时 用 show errors 显示出错信息；

可以在 plDv中调试；

\---> 运行时：

可以在命令窗口运行：

declare

v\_a number:=3;

v\_b number:=4;

v\_c number;

v\_d number:=5;

begin

p(v\_a,v\_b,v\_c,v\_d);

dbms\_output.put\_line(v\_c);

dbms\_output.put\_line(v\_d);

end;

可以在 plDv中调试；

\------------------函数 \-------------------

1、它有返回值

create or replace function tax\_tag(sal number) return number --计算税率

begin

if(sal > 1000)then

return 0.1;

elsif(sal>=2000)then

return 0.15;

else

return 0.2;

end if;

end;

select ename, tax\_tag(sal) from emp ； \-- 直接用函数tax\_tag

\------------------------触发器 (trigger)------------------------------

1.create table emp2\_log

(

uname varchar2(20),

action varchar2(20),

atime date

);

create or replace trigger trig

after(before) insert or delete or update on emp2 for each row

begin

if inserting then

insert into emp2\_log values (USER, 'insert', sysdate);

elsif updating then

insert into emp2\_log values (USER, 'update', sysdate);

elsif deleting then

insert into emp2\_log values (USER, 'delete', sysdate);

end if;

end;

select \* from emp2\_log; --查询更改情况 .

2. create or replace trigger tig

after update on dept2 for each row

begin

update dept2 set deptno = :NEW.deptno where deptno = :OLD.deptno;

end;

\-------------------------recursion 通过递归写树 \--------------------

\-----》创建表并插入记录

create table article (

id number primary key,

cont varchar2(4000),--文章内容

pid number, --父类 id

isleaf number(1),--0代表非叶子节点， 1代表叶子节点

alevel number(2) --等级

)

insert into article values(1,'蚂蚁大战大象 ',0,0,0);

insert into article values(2,'大象被打趴下 ',1,0,1);

insert into article values(3,'蚂蚁也不好过 ',2,1,2);

insert into article values(4,'瞎说 ',2,0,2);

insert into article values(5,'没有瞎说 ',4,1,3);

insert into article values(6,'怎么可能 ',1,0,1);

insert into article values(7,'怎么没有可能 ',6,1,2);

insert into article values(8,'可能行很大的 ',6,1,2);

insert into article values(9,'大象进医院了 ',2,0,2);

insert into article values(10,'蚂蚁是护士 ',9,1,3);

\----------》存储过程

create or replace procedure p (v\_pid article.pid%type,v\_level binary\_integer) is

cursor c is select \* from article where pid = v\_pid;

v\_perStr varchar2(2000):=' ';

begin

for i in 1..v\_level loop

v\_perStr := v\_perStr||'\*\*\*';

end loop;

for v\_article in c loop

dbms\_output.put\_line(v\_perStr||v\_article.cont);

if (v\_article.isleaf = 0) then

p(v\_article.id,v\_level + 1);

end if;

end loop;

end;

\----------》输出结果

蚂蚁大战大象

\*\*\*大象被打趴下

\*\*\*\*\*\*蚂蚁也不好过

\*\*\*\*\*\*瞎说

\*\*\*\*\*\*\*\*\*没有瞎说

\*\*\*\*\*\*大象进医院了

\*\*\*\*\*\*\*\*\*蚂蚁是护士

\*\*\*怎么可能

\*\*\*\*\*\*怎么没有可能

\*\*\*\*\*\*可能行很大的

oracle sql语句

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle # Sql语句

相关 oracle sql语句

相关 Oracle SQL语句记录

相关 oracle sql语句

相关 oracle SQL语句优化

相关 oracle-sql语句优化

相关 oracle-sql语句练习

相关 Oracle sql语句练习

相关 oracle SQL语句优化

相关 Oracle sql语句摘记