oracle常用sql语句大全

[b] 1.解锁用户
	请输入用户名：sys
	输入口令：sys as sysdba 
 	alter user scott accout unlock;
	    用户已更改.
	SQL> commit;
	SQL> conn scott/tiger
	更改scott口令
	新口令：tiger
	重新键入新口令：tiger
		增加权限 用sys登陆完 grant create table, create view to scott; conn scott/root 


 2.查看表结构
 	desc tbname


 3.从表中查询数据
	select * from tbname;


 4.字段运算后再查询
	select ename, sal*12 from emp;


 5.纯数字运算查询
	select 2*3 from dual;  dual是oracle无意义的表


 6.查询当前系统的日期
	select sysdate from dual;


 7.双引号保持原来的格式
	select ename, sak*12 "anuual sal" from emp;


 8.查询数字时，把为空值的换为0，oracle中空值运算结果都为null
 	select ename, sal*12 + nvl(comm, 0) from;  nvl(,) 如果字段comm为空值时，用0代替
	select ename, sal, comm from emp where comm is null;(is not null)(选出comm为空的数据)

	
 9.字符串连接（把两个字段查询出来的数据作为一条字符串输出）两个单引号代替一个
	select ename||sal from emp; select ename || 'ds''fsdf' from emp; 


10.去掉重复的值(也会去掉多个字段组合重复的值)

	select distinct ziduan from tbname;


11.条件 where
	select * from tbname where ziduan > 'CBA' ; =, <, 不等于号<>，


12.条件 between and (包含800和1500)
	select ename, sal from emp where sal between 800 and 1500;
	select ename, sal from emp where sal >= 800 and sal <= 1500;



13.条件 in (谁的薪水值=800或1500或2000)
	select ename, sal comm from where sal in (800, 1500, 2000); 也可以not in ('df', 'dfsd')


14.条件 or
	select ename, sal from emp where deptno = 10 or sal > 1000;
15. 模糊查询 %零个或多个，下横线_代表一个
	select ename from emp where ename like '%All%';



16. 转义字符 \ 可以制定转义字符 escape
	select ename from emp where ename like '%\%%';  like '%$%%' escape '$';


17.排序 order by 默认升序asc

	select empno, ename from emp order by deptno asc, ename desc;先按deptno,再按ename


18.函数 转化为小写lower()
	select lower(ename) from emp;
19.函数 截子串substr(ename,2,3) 从字符串ename中第2个开始截，一个截3个字符


		select substr(ename,2,3) from emp;
20.函数 把数字转化为相应的字母,相反 ascii('A')
	select chr(65) from dual;   



21.函数 四舍五入 round(23.652)
	select round(23.652) from dual;  24
	select round(23.652, 2) from dual;  23.65  2代表舍到小数点后2位
	select round(23.652，-1) from dual; 20     可以是负数


22.函数 把数字或字母或日期转化为特定的格式 to_char(sal,'$99,999.9999'), $换成L,显示￥
	select to_char(sal, '$99,999.9999') from emp; 百千等位没有的不显示
	select to_char(sal, '$00,000.0000') from emp; 没有的位用0补齐
	select to_char(hircdate, 'YYY-MM-DD HH24:MI:SS') from emp; 转化为特定的日期，24位24进制



23.函数 把特定的字符转化为日期 to_date('', '')
	select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');


24.函数 把特定的字符转化为数字 to_number('$1,250.00', '$9，999.99')
	select sal from emp where sal > to_number('$1,250.00', '$9，999.99');



25.组函数 取最大max()，最小min()，平均avg(),函数可以组合使用
	select to_char(avg(sal), '99999999.99') from emp;


26.组函数 总和 sum()
	select sum(sal) from emp;


27.组函数 求出总共多少条数据 count(*)，count(ename), 凡是不是空值的字段一共有几个
	select count(*) from emp;
	select count(distinct ziduan) from tbname; distinct去掉重复



28.函数 分组查询 group by
	select avg(sal), deptno from emp group by deptno;
	select deptno, job, max(sal) from emp group by deptno, job;


29.分组查询，多条输入，一条输出
	查询出薪水最高的人的名字（可能不止一个人）
	select ename from emp where sal = (select max(sal) from emp);
	查询出每个组中薪水最高的人的名字
	select ename from emp where sal in (select max(sal) from emp group by deptno);
	group by使用规则，要查询的字段如果没出现在组函数中则必须出现在group by中，否则出错


30.取出按部门编号分组后每个部门的平均薪水
	select avg(sal) from emp group by deptno;



31.where语句是处理单条语句，有此语句先执行where语句再进行分组（有group by的话）
	having 用来对分组进行限制 此处代替where
	查询出部门平均薪水大于2000的平均薪水和部门编号
	select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;



32.完整的select语句，按此顺序执行

	select * from emp
	where sal > 1000
	group by deptno
	having avg(sal) > 2000
	order by


33.查询出薪水大于平均薪水人的名字
	select ename, sal from emp where sal > (select avg(sal) from emp);




34.查出按部门分组后，每个部门中薪水最高的人的名字，部门编号 join 表连接，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);
	join表连接，on后面是连接条件，此语句等于如下语句
	select ename, sal from emp where (deptno,sal) in (select deptno, max(sal) from emp group by deptno);



35.把自己的名字和他经理人的名字取出来(自链接)
	select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;


36.SQL1999 cross join 交叉连接
	select ename,dname from emp, dept; 1992版的
	select ename,dname from emp cross join dept;1999版的



36.SQL1999等值连接 老版用where难分辨哪个是过滤条件哪个是表连接条件，用新版的on，后可加where过滤
	select ename,dname from emp,dept where emp.deptno = dept.deptno; 1992版
	select ename,dname from emp join dept on (emp.deptno = dept.deptno); 1999版
	select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);



37.sql1999三表链接加where过滤
	select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal aand s.hisal) where ename not like '_A%';



38.外链接 左外链接 left join 会把左边这张表多余的数据显示出来（和另外一张表对应不上的数据）同理right join右外连接，full join 全外连接
	select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);



39.部门平均薪水的的等级
	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);



40.部门平均的薪水等级
	select deptno, avg(grade) from(select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno;


41.雇员中哪些人是经理人
	select ename from emp where empno in (select mgr from emp);



42.不用组函数求最高薪水
	select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 left join emp e2 on (e1.sal < e2.sal));



43.平均薪水最高的部门编号与名称
	select dname from dept where deptno = (
	select deptno from (select deptno avg(sal) avg_sal from emp group by deptno) where avg_sal = (sel	ect max(avg_sal) from (select deptno avg(sal) avg_sal from emp group by deptno)));



44.平均薪水的等级最低的部门的部门名称
	select dname from dept where deptno = (
	select deptno 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) where	 grade = (
	select min(grade) from (select grade, deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal betwe	en s.losal and s.hisal))));




45.创建视图 create view v$name as 重复使用的语句. 视图就是一个子查询，就是一张表
	create view v$name as select * from emp; 以v$开头
	select * from v$name;



46.插入语句
	insert into tbname values (50, 'ganbe', 'bj');
	insert into tbanme (zd1, zd2) values (50, 60);
	insert into dept2 select * from dept;



47.备份表
	create table emp2 as select * from emp;



48.伪字段 rownum 默认从第一行往后排列序号1，2，3等，必须< = 号；
	select empno, ename from emp where rownum <=5;
	select roenum r, ename from emp where r > 10; 这样可 〉 于号；



49.薪水最高的前5人
	select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;



50.部门为10的员工薪水翻一倍
	update emp set sal = sal*2, name ename = ename||"-" where deptno = 10;


51.还原没提交的修改 rollback
	rollback;



52.提交 commit 遇到ddl语句事物自动执行 commit，正常断开连接时自动提交
	commit;




53.Oracle有事物回滚机制
	事物 transaction



54.约束 自定义名字 非空约束
	create table stu (id number(6), name varchar2(20) constraint stu_name_ nn not null);


55.唯一 约束 unique 可以插空置
	标级约束 几个字段的组合唯一约束 不在字段后面，另起一行
	constraint syu_name)email_uni unique(email,name)


56.主键约束 非空唯一 primary key，可以组合 主键
	id number(4),
	另起一行的话 primary key(id)



57.外键约束 牵扯到两张表、两个字段references(参考),被参考的字段必须是主键
	表级别：constraint ysname foreign key (benziduan) references ckbiao(waizd)



58.修改现有表的表结构
	alter table tbname add(ziduan varchar2(100)) 添加
	alter table tbname drop(ziduan) 删除
	alter table tbname modify(ziduan varchar2(100)) 修改 字段类型容量不能改小


59.去掉约束
	alter table tbname drop constraint yueshuname;


60.添加约束
	alter table tbname add constraint yueshuname foreign key (class) references class (id);


61.删除表
	delete from tbname;


62.oracle默认的一个表user_tables 装的当前用户下有多少表，（数字字典表）
	select table_name from user_tables


63.oracle有多少个数字字典表都放在表 dictionary 中
	select table_name from dictionary;


64.索引 index
	create index syname on tbname(ziduan1,ziduan2);


65.删除索引
	drop index syname;


66.序列 oracle独特的 自动递增
	create sequence sename;
	drop sequence sename;删粗序列
	select sename.nextval from dual;查询的结果会递增


67.三范式
	不存在冗余数据
	第一范式要求：要有主键，列不可分
	第二范式要求：不能存在部分依赖 （分割为n张表）
	第三范式要求：属性不能依赖其它属性


68.PL_SQL 语言   斜杠/执行
	set serveroutput on;
	begin
		dbms_output.put_line('HelloWorld');
	end;


69.PL_SQL 语言 declare 声明变量 以v_开头
	declare
		v_name varchar(20);
	begin
		v_name := 'myname';   := 赋值符号
		dbms_output.put_line(v_name);
	end;
	/

69.PL_SQL 语言 异常
		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;

70.PL_SQL 语言 常用变量的类型
	binary_integer: 整数，主要用来计数而不是用来表示字段类型
	number：数字类型
	char: 定长字符串
	varchar2: 变长字符串
	date: 日期
	long: 长字符串，最长2GB
	boolean: 布尔类型，可以取值为 true、false和null值，默认null


71.PL_SQL 语言 constant
	相当于java中的 fianl；


72.PL_SQL 语言 -- 单行注释


73.PL_SQL 语言 %type 属性
	v_empno emp.empno%type; 变量v_empno的类型随表emp中字段empno的类型变化而变化


74.Table变量类型 相当于java中的数组，type表示定义了一种新的数据类型
	declare
		type type_table_emp_emono is table of emp.empno%type index by binary_integrt;
		v_empnos type_table_emp_empno;
	begin
		v_empnos(0) := 7369;
		v_empnos(2) := 7339;
		v_empnos(-1) := 9999;
		dbms_output.put_line(v_empnos(-1));
	end;


75.Record变量类型 相当于java中的类
	declare
		type type_recors_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 := 'bj';
		dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
	end;



76.使用%rowtype声明record变量
	declare
		v_temp dept%rowtype;
	begin
		v_temp.deptno := 50;
		v_temp.dname := 'aaaa';
		v_temp.loc := 'bj';
		dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
	end;



77.PL_SQL语句的运用,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;
		dbms_output.put_line(v_ename || ' ' || v_sal);
	end;
	同上
	declare
		v_emp emp%rowtype;
	begin
		select * into v_emp from emp where empno = 7369;
		dbms_output.put_line(v_emp.ename || ' ' || v_emp.sal);
	end;


87.PL_SQL语句的运用 insert
		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;


88.PL_SQL语句的运用
	dbms_output.put_line(sql%rowcount || '条记录被影响')


89,PL_SQL语句的运 ddl语句在PL_SQL语句中前加 excute immediate
	begin
		execute immediate 'create table tbname (nnn varchar2(20) default ''aaa'')';


90.PL_SQL语句 if语句 取出7369的薪水，如果<1200,则输出'low',如果<2000则输出'middle',否则输出'high'
	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;



91.PL_SQL语句 循环语句
	declare
		i binary_integer := 1;
	begin
		loop
			dbms_output.put_line(i)
				i := i + 1;
				exit when (i >= 11);
		end loop;
	end;

	declare
		j binary_integer := 1;
	begin
		where j < 11 loop
			dbms_output.put_line(j);
			J := J + 1;
		end loop;
	end;

	begin
		for k in 1..10 loop
			dbms_output.putline(k);
		end loop;
		for k in reverse 1..10 loop
			dbms_output.put_line(k);
		end loop;
	end;


92.PL_SQL语句 异常
	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;

	no_data_found  没找到数据




93.PL_SQL语句 游标（指针） cursor
	declare
		cursor c is
			select * from emp;
		v_emp c%rowtype;
	begin
		open c; --开始执行select语句
			fetch c into v_emp; --fetch提取游标数据
			dbms_output.put_line(v_emp.eename);
		close c;
	end;

	循环
		loop
			fetch c into v_emp;
			exit when (c%notfound);
			........;
		end loop;
	for循环
		declare
			cusor c is
				select * from emp;
		begin
			for v_emp in c loop
				dbms_output.put_line(v_emp.ename);
			end loop;
		end;



94.PL_SQL语句 带参数的游标
	declare
		cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
		is
			select ename, sal from emp where deptno = v_deprno and job = v_job;
		--v_temp c%rowtype;
	begin
		for v_temp in c(30, 'CLERK') loop
		 dbms_output.put_line(v_temp.ename);
		end loop
	end;




95.PL_SQL语句 可更新的游标
	declare
		cursor c
		is
			select * from emp2 for update;
			--v_temp c%type;
			begin
				for v_temp in c loop
					if(v_temp.sal < 2000) then
					 update emp2 set sal = sal * 2 where current of c; --current当前的
					elsif(v_temp.sal = 5000) then
						delect from emp2 where current of c;
					end if;
				end loop;
		commit;
	end;






96.存储过程procedure
	创建存储过程
	create or replace procedure p
	is
		cursor c
		is
			select * from emp2 for update;
			--v_temp c%type;
			begin
				for v_temp in c loop
					if(v_temp.sal < 2000) then
					 update emp2 set sal = sal * 2 where current of c; --current当前的
					elsif(v_temp.sal = 5000) then
						delect from emp2 where current of c;
					end if;
				end loop;
		commit;
	end;
	执行此存储过程
	exec p;










97.带参数的存储过程 默认in
	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.outline(v_ret);
		dbms_output.putline(v_temp);
	end;       答案 4，6





98.函数 调用方式和系统函数调用方式一样
	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.15;
		else if;
	end;




99.触发器 必须在表上，在什么时间，等 for each row 每处理一行触发一次。删除触发器 drop trigger trig;
	create or replace trigger trig
		after 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;






100. update 执行会有前后两个状态 NEW, OLD
	create or replace trigger trig
		after update on dept
		for each row
	begin
		update emp set deptno =: NEW.deptno where deptno =: OLD.deptno;
	end;
[/b]