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;