sql语句百例之Oracle

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 sco

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

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; a

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 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 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 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的类型变化而变化

SpringMVC+mybatis HTML5 全新高大尚后台框架_集成代码生成器
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的薪水，如果 declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal dbms_output.put_line('low');
elsif (v_sal 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 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 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 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 return 0.10;
elsif(v_sal 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;