PLSQL高级查询.doc

数据库启动数据库Sqlplus/nolog登陆连接数据库Conn ygj/123orcl as sysdba;表空间创建表空间Create tablespace tbs_school2 datafile F:appRainboworadataorcl school_01.dbf3 size 20m autoextend on;修改表空间Alter database datafile F:appRainboworadataorcl school_01.dbf resize 30m;用户创建用户Create user 用户名 identified by 密码2 default tablespace 表空间名3 temporary tablespace temp;授权Grant 角色名 to 用户名;表创建表Create table 表名(字段名 字段类型(字段长度), 字段名 字段类型(字段长度)Create table school(id varchar2(5),name varchar2(40),studentCount number(3);查询表desc表名; 添加字段alter table 表名 add(字段名 字段类型(字段长度);修改字段alter table 表名 modify(字段名 字段类型(字段长度);删除字段Alter table 表名 drop column 字段名;删除表Drop table 表名约束增加主键约束Alter table 表名 add constraint约束名primary key(字段名);查看主键约束Select constraint_name from user_constraints where table_name = 表名(大写);增加唯一约束Alter table 表名 add constraint约束名unique(字段名);增加检查约束Alter table 表名 add constraint约束名check(字段名0);删除约束Alter table 表名 drop constraint约束名;表数据操作创建记录Insert into 表名 (字段名,字段名,字段名,createdate) values(字段值,字段值,字段值,to_date(2012-03-19,yyyy-mm-dd);创建备份表Create table school_bk(字段名 字段类型(字段长度), 字段名 字段类型(字段长度)备份数据Insert into school_bk select t.*,sysdate from school t;提交数据commit更新表数据Update 表名 set 字段名 = 字段值, 字段名 = 字段值 where id = 00002;删除表数据Delete from 表名 where id = 00002,恢复备份表Insert into 表名 select id,name,studentCount from school_bk截断表，不需要提交Truncate table 表名;查询字段部分查询Select 字段名,字段名 from 表名; 条件查询Select * from 表名 where 字段名 between 100 and 200; 升序排序查询Select * from school order by 字段名; 降序排序查询Select * from school order by 字段名 desc; 别名查询Select id as “编号”,name as “学校” from 表名限制查询行数Select id as “编号”,name as “学校” from 表名 where rownum 150;声明及赋值变量declare num number(16); name %type;begin num := 20; dbms_output.put_line(num= | num); select name into name from school s where s.id=00001; dbms_output.put_line(name);end;逻辑控制结构declare birthyear number;begin birthyear :=1990; if birthyear between 1980 and 1989 then dbms_output.put_line(80后); elsif birthyear between 1990 and 1999 then dbms_output.put_line(90后); else dbms_output.put_line(00后); end if;end;多分支条件结构(查询语句中使用)select (case when atk between 2000 and 2999 then 高级怪兽 when atk between 3000 and 3999 then 超级怪兽 when atk between 4000 and 4999 then 神之卡 else 没有找到匹配项 end)lv from card;多分支条件结构2declareatk number;begin atk := 4000; case when atk between 2000 and 2999 then dbms_output.put_line(高级卡); when atk between 3000 and 3999 then dbms_output.put_line(超级卡); when atk between 4000 and 4999 then dbms_output.put_line(神之卡); else dbms_output.put_line(没有找到匹配项); end case;end;LOOP循环declare i number := 0;begin dbms_output.put_line(循环之前i= | i); loop if i 4 then exit; end if; dbms_output.put_line(循环第 | (i + 1) | 次); i := i + 1; end loop; dbms_output.put_line(循环结束);end;FOR循环begin for i in 1.100 loop if(mod(i,3)=0)then dbms_output.put_line(i); end if; end loop;end;捕获异常declare salary number := 0 ; salary_exception exception ;begin salary := &salary; if salary 0 then raise salary_exception; end if; dbms_output.put_line(输入的工资是|salary); exception when salary_exception then dbms_output.put_line(输入的工资数据不合法!);end;内链接两种查询方式select , from card c,owner o where c.owner=o.id;select , from card c join owner o on c.owner=o.id;左外连接查询方式select , from card c left join owner o on c.owner=o.id;右外连接查询方式select , from card c right join owner o on c.owner=o.id;全外连接查询方式select , from card c full join owner o on c.owner=o.id;组合查询select id,name from ownerunion allselect id,name from owner2;简单子查询select name,owner from card where owner = (select id from owner where name = 游戏);相关子查询select name,owner from card where owner in (select id from owner where name = 游戏 or name=城之内);内嵌视图子查询select name,owner from card e where exists (select o.id from owner o where ( = 游戏or =城之内)and e.owner = o.id);创建序列create sequence seq_location_pk start with 0 increment by 1 maxvalue 3000 cycle cache 30;使用序列begin insert into location (id,name) values (seq_location_pk.nextval,北京); insert into location (id,name) values (seq_location_pk.nextval,上海); insert into location (id,name) values (seq_loca