?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;?? 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 < 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的类型变化而变化
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的薪水，如果<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;
SpringMVC+mybatis HTML5 全新高大尚后台框架_集成代码生成器
原文：http://qq-20921443.iteye.com/blog/2176341