1.desc(描述） emp 描述emp这张表2.desc dept 部门表3.desc salgrade 薪水等级4.select *from table 查找表中的元素5.dual 是系统中的一张空表6.select *from dual 7.select sysdate from dual 取出系统时间8.select ename,sal*12 "annul sal"(取的别名） from emp; 查找用户姓名和用户的年薪9.任何含有空值的数学表达式的值都是空值select ename,sal*12+comm from emp;10.select ename||sal from emp 其中的||相当于将sal全部转化为字符串11.表示字符串的方法select ename ||'ajjf' from emp;12.如果其中有一个单引号就用2个单引号来代替他select ename||'sakj' 'lds'from emp;13.select distinct deptno from emp (去除部门字段中重复的部分,关键字distinct)14.select distinct deptno,job from emp;(去除这2个字段中重复的组合)15.select *from dept where deptno=10; 取出条件(取出部门编号为10的记录)16.select * from emp where ename='CLIRK'; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)17.select ename,sal from emp where sal>1500; 取出部门中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno<> 10 取出部门中的部门号不等于10的19.select ename,sal,deptno from emp where ename>'CBA' 取出部门中员工名字大于CBA的员工(实际比较的是ACIIS码)20.select ename,sal from emp where sal between 800 and 1500 select ename,sal from emp where sal>=800 and sal<=1500; (取出800和1500之间的数)21.select ename,sal,comm from emp where comm is null (选出其中的空值) select enmae,sal,comm from emp where comm is not null(选出其中的非空值)22.select ename,sal,comm from emp where sal in (800,1500,2000);取出这3者之中的 select ename,sal,comm from emp where ename in('simth');23.select ename,sal,hiredate from emp where hiredata>'3-04月-81';宣传符合条件的日期24.select ename,sal,from emp where sal>1000 or deptno=10; 找出工资薪水大于1000或者部门号等于10的员工25.select ename,sal from emp where sal not in(500,1000); 查找薪水不在500到1000的员工姓名和月薪26.select ename,sal from emp where ename like '%ALL%'; select ename,sal from emp where ename like '_%A%'; 查找姓名中含有ALL的客户信息，一个横线代表一个通配符27.select ename,sal from emp where ename like '_%$%%' escape '$'; 自己指定转易字符 select ename,sal from emp where ename like '_%/%%'; 查找中间含有%相匹配的客户信息，运用转易字符28.select * from dept order by deptno 对表中元素按部门号排序 select *from dept order by deptno desc 默认为升序，可以用desc按降序29.select ename,sal from emp where sal <>1000 order by sal desc 按照查询条件来查询，并排序（asc升序排列）30.select ename,sal*12 from emp where ename not like '_%A%' and sal>800 order by sal desc 31.select lower(ename) from emp 将ename都转化为小写 lower是函数能将字母转化为小写32.select ename from emp where lower(ename) like '_%a%'; 找出ename 中所有的含有a的字符33.select substr(ename,2,3) form emp 从第2个字符开始截取3个字符34.select chr(65) from dual; 将65转化为字符35.select ascii('A') from dual 将ACSII码转化为字符串36.select round(23.565)from dual 四舍五入36.select round(23,4565,2)from dual 四舍五入到第二位37.select to_char(sal,'$99.999.9999') from emp 按指定格式输出 select to_char(sal,'L99,999,9999') form emp L代表本地字符38.select hiredate from emp select to_char(hiredate,'YYYY-MM-DD HH:MI:SS) from emp; 时间格式的显示 select to_char(sysdate,'YYYY-MM-DD HH:MI:ss) from dual; 十二小时制显示系统时间 select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS) from dual 二四小时制显示系统时间39.select ename,hiredate from emp where hiredate > to_date('2005-2-3 12:32:23','YYYY-MM-DD HH:MI:SS');40 select sal from emp where sal>to_number('$1,250.00','$9,999.99'); 取出比它大的一切字符串（把特定格式的数字转化成字符）41 select ename,sal+nvl(comm,0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条42.select Max(sal) from emp; select Min(sal) from emp; select avg(sal) from emp; select sum(sal) from emp; select count(*) from emp; 查看表中一共有多少条记录 select count(*) from emp where deptno=10; 查找部门10一共有多少人；43.select avg(sal),deptno from emp group by deptno; 按部门号进行分组 select deptno,job,max(sal) from emp group by job,deptno; 按工作和部门号进行分组；44.select ename from emp where sal=(select max(sal) from emp); 子查询，查找部门中薪水最高的员工姓名45.group by 注意：出现在select列表中的字段，如果没有出现在组函数中必须出现在group by子句中46.select avg(sal),deptno from emp group by deptno having avg(sal)>2000; 选出部门中平均薪水大于2000的部门，47.select * from emp where sal>100 group by deptno having ..........order by........ 先取数据--过滤数据------分组----对分组限制-------排序48.select avg(sal) from emp where sal>2000 group by deptno having avg(sal)>1500 order by avg(sal) desc; 查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列49.select ename from emp where sal>(select avg(sal) from emp); 查找出员工中薪水位于部门平均薪水之上的所有员工50.select ename,sal from emp join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno); 查找每个部门中薪水最高的51.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 表的自连接52.select dname,ename from emp cross join dept 交叉连接，笛卡尔 SQL99中的新语法53.select ename,dname from emp join dept on(emp.deptno=dept.deptno); 54.select ename,dname from emp join dept using(deptno); 查找emp和dept表中deptno相同的部分。55.select ename,dname,grade from emp e join dept d on(e.deptno=d.depno) join salgrade s(e.sal between s.losal and s.hisal) (三表查找） where ename not like '_%A%';56.select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.deptno); 表的自连接57.select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.deptno) 左外表连接 select ename,dname from emp e right join dept d on(e.deptno=d.deptno)右外连接 select ename,dname from emp e full join dept d on(e.deptno=d.deptno)全连接58.求部门中薪水最高的 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);59.求部门中薪水等级的平均值 select deptno,avg(grade) from(select deptno,ename,grade,from emp join salgrade s on(emp.sal between s.losal and s.hisal))t group by deptno;60.查找雇员中哪些是经理人 select ename from emp where empno in(select mgr from emp);61.select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal); 自连接(不用组函数求出最高薪水） select distinct sal from emp where not in (select ename from e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));62.select deptno from (select avg(sal) max_sal deptno from emp group by deptno) where max_sal=(select max(avg_sal) from (select avg(sal) avg_sal deptno from emp group by deptno)); 查找部门中部门薪水最大的部门号63.求平均薪水最大的部门的部门编号 select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal=(select max(avg(sal)) from emp group by deptno);DML语句：更、删、改、查创建权限， conn sys/admin as sysdba grant create table,create view to scott;首先在C:下面建个文件夹备份文件1.createNewUser方法1.--backup scott exp2.create user(创建用户）用超级管理员模式进入 create user yun identified by kang1234 default tablespace users quota 10M on users; grant create session,create table,create view to kafei（给kafei这个用户授予权限）3.import the data(导入备份数据） imp2.insertinsert into dept values (50,'game','bj') 插入一条记录insert into dept2 (deptno,dname) values (78,'games'); 插入指定的几条记录insert into dept2 select *from dept 插入指定的表（表结构要一样）rollback; 回退create table emp2 as select * from emp; 创建数据库表2来备份emp这张表3.update emp2 set sal=sal*12 where deptno=10; update的用法4.delete from dept2 where deptno<25 ; 删除语句的用法DDL语言1.创建表:create table t(a varchar2(10));2.drop table t 删除表3.commit 所有的提交，所有修改都结束了。对于rollback无效，一个事务开始于第1条DML语句碰到执行DDL DCL语句事务自动提交 对于rollback无效建表语句建学生信息表：create table stu(id number(6),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),email varchar2(50) unique (唯一约束));
非空 唯一 主键 外键 chick
create table stu(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),email varchar2(50),constraint stu_name_uui unique(email,name) 组合性约束); 主键约束方法二create table stu(id number(6),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),(参考class 这张表，参考字段)email varchar2(50), constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 组合性约束); 外键约束create table class(id number(4) primary key,(id为被参考字段,被参考的字段必须是主键)name varchar2(20) not null)
create table stu(id number(6),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) email varchar2(50), constraint stu_class_fk foreign key(class) references class(id),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 组合性约束);
像外键中插入关键字，1.insert into class values(1000,'c1');2.insert into stu(id,name,class,email) values(1,'a',1000,'a');3.alter table stu add(addr varchar(20));添加表的结构4.alter table stu drop(addr); 删除表结构5.alter table stu modify(addr varchar2(150));修改精度6.alter table stu drop constraint stu_class_fk; 删除约束条件7.alter table stu add constraint stu_class_fk forengn key(class) references class(id),添加约束条件查找当前用户下有哪些表和哪些视图及哪些约束8.select table_name from user_names9.select view_name from view_names10.select constraint_name,table_name from user_constraints;desc dictionary数据字典表desc user_tables当前用户下面有多少张表select table_name from user_tables; 查找当前用户有多少张表索引：创建索引create index idx_stu_email on stu(email);drop index idx_stu_email;查找索引select index_name from user_indexes;索引读的速度快了，插入速度变慢view 视图视图赠加了维护的量序列：create table arcticle(id number,title varchar2(1024),cont long);序列的创建sequence产生独一无二的序列，而且是oracle独有的create sequence seq;select seq.nextval from dual; 查找序列号insert into arcticle values(seq.nextval,'a','b');往表中插入序列数据库设计的3范式第一范式： 设计任何表都要有主键，列不可分第二范式： 如果有2个主键的话，不能存在部分依赖第三范式， 不能存在传递依赖
PL-sql例子1：SQL> set serveroutput on;SQL> begin(必要的--程序开始执行)2 dbms_output.put_line('hello world');3 end;(结束)4 /例子2：SQL> declare2 v_name varchar2(20);3 begin4 v_name:='myname';5 dbms_output.put_line(v_name);6 end;7 /myname
例子3：SQL> declare2 v_num number:=0;3 begin4 v_num:=2/v_num;5 dbms_output.put_line(v_num);6 end;7 /declare*ERROR 位于第 1 行:ORA-01476: 除数为 0ORA-06512: 在line 4
例子4：declarev_num number:=0;begin v_num:=2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_output.put_line('error');end;/变量声明的规则1.变量名不能够使用保留字，如from,select等2.第一字符必须是字母。3.变量名最多包含30个字符4.不要与数据库的表或者列同名5.每一行只能声明一个变量
常用变量类型1. binary_interger,整数，主要用来计数，而不是用来表示字段类型2. number 数字类型3. char 定长字符串4. varchar2 变长字符串5. date 日期6.long 长字符串，最长2GB7.boolean 布尔类型，可以取true false 和null的值
例5：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; v_valid boolean:=false; v_name varchar2(20) not null:='myname';begin dbms_output.put_line('v_temp value:'||v_temp);end;用--可以注释一行例6：declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type;begin dbms_output.put_line('test');end;例7table变量类型set serveroutput on;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):=7369; v_empnos(2):=7869; v_empnos(-1):=9999; dbms_output.put_line(v_empnos(-1));end;例8Record 变量类型set serveroutput on;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:='aaaa'; v_temp.loc:='bj'; dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end;例9：使用%rowtype声明record变量（表结构的变化同时也能代理储存过程的变化）set serveroutput on;declare v_temp dept%rowtype;begin v_temp.deptno:=50; v_temp.loc:='aaaa'; v_temp.loc:='bj'; dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end;例10；
declarev_name emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_name,v_sal from emp where empno=7369;（将ename和sal的值放在v_name和v_sal里面）
例11：declarev_name emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_name,v_sal from emp where empno=7369;dbms_output.put_line(v_name||' '||v_sal);end;dbms_output.put_line(v_name||' '||v_sal);end;
例12：declarev_deptno dept.deptno%type:=50;v_dname dept.dname%type:='aaaa';v_loc dept.loc%type:='bj';begininsert into dept2 values(v_deptno,v_dname,v_loc);commit;end;
例13：declarev_deptno emp2.deptno%type:=50;v_count number;beginupdate emp2 set sal=sal/2 where deptno=v_deptno;dbms_output.put_line(sql%rowcount ||'条记录被影响');(sql为关键字，代表上一条语句commit;end;/
例14：declarev_deptno emp2.deptno%type:=50;v_count number;begin--update emp2 set sal=sal/2 where deptno=v_deptno; select deptno into v_deptno from emp2 where empno=7369;dbms_output.put_line(sql%rowcount ||'条记录被影响');(sql为关键字，代表上一条语句commit;end;/
例15declarev_deptno emp2.deptno%type:=50;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; （select必须和into一起使用）dbms_output.put_line(sql%rowcount ||'条记录被影响');commit;end;/PL/SQL里面执行DDL语句beginexecute immediate 'create table T(nnn varchar2(20) default ''aaa'')';end;
PL/SQL的分支语句：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;
pL/Sql循环declare i binary_integer:=1;begin loop dbms_output.put_line(i); i:=i+1; exit when(i>=11); end loop;end;
PL/SQL for循环beginfor k in 1..10 loop dbms_output.put_line(k);end loop;for k in reverse 1..10 loop dbms_output.put_line(k);end loop;end;
exception 捕获异常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;
没有数据错误declare v_temp number(4);begin select empno into v_temp from emp where deptno=2222;exception when no_data_found then dbms_output.put_line('没数据');when others then dbms_output.put_line('error');end;/错误处理create table errorlog(id number primary key,errcode number,errmsg varchar2(1024),errdate date);create sequence seq_errorlog_id start with 1 increment by 1;
declarev_deptno dept.deptno%type:=10;v_errcode number;v_errmsg varchar2(1024);begindelete from dept where deptno=v_deptno;exceptionwhen others then rollback; v_errcode:=SQLCODE; v_errmsg:=SQLERRN; insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit;end;
游标declare cursor c is select * from emp; v_emp c%rowtype; begin open c; fetch c into v_emp; --(取游标的第一个值插入v_emp，在不断的循环) dbms_output.put_line(v_emp.ename); close c; end;
例子declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp; exit when(c%notfound); dbms_output.put_line(v_emp.ename); end loop; close c;end;
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;
declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop;end;
带参数的游标declare cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is select ename,sal from emp where deptno=v_deptno and job=v_job;begin for v_temp in c(30,'chick') loop dbms_output.put_line(v_temp.ename); end loop;end;
可更新的游标
存储过程create or replace procedure pis cursor c is select * from emp2 for update;begin for v_emp in c loop if (v_emp.deptno=10) then update emp2 set sal=sal+10 where current of c; elsif(v_emp.deptno=20) then update emp2 set sal=sal+20 where current of c; else update emp2 set sal=sal+50 where current of c; end if; end loop; commit;end;
exec p执行存储过程begin p:end;
带参数的存储过程create or replace procedure p(v_a in number,v_b number,v_ret out number, v_temp in out number)isbeginif(v_a>v_b) then v_ret:=v_a; else v_ret:=v_b;end if; v_temp:=v_temp+1;end;
调用存储过程declarev_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.put_line(v_ret); dbms_output.put_line(v_temp);end;
show error返回错误信息
删除存储过程
存储过程中的函数create or replace function sal_tax (v_sal number) return numberisbegin if(v_sal<2000) then return 0.10;elsif(v_sal<2750) then return 0.5;else return 0.20;end if;end;/
触发器
create or replace trigger trigafter update on deptfor each rowbeginupdate emp set deptno=:NEW.deptno where deptno=:OLD.deptno;end;/
