整合的ORACLE数据库方面的知识.doc

连接各数据库的连接字符串:sqlserver2000 jtds.jar class: net.sourceforge.jtds.jdbc.Driver url: jdbc:jtds:sqlserver:/localhost:1433/数据库-sqlserver2000 (microsoft mssqlserver2.jar)class: com.microsoft.jdbc.sqlserver.SQLServerDriverurl: jdbc:microsoft:sqlserver:/localhost:1433;DatabaseName=pubs-sqlserver2005 (microsoft sqljdbc.jar)class: com.microsoft.sqlserver.jdbc.SQLServerDriverurl: jdbc:sqlserver:/localhost:1433; databaseName=pubs user:sapassword:niit(也可以设置为空)-Oracle (ojdbc.jar) class: oracle.jdbc.driver.OracleDriver url: jdbc:oracle:thin:teacher:1521:SID-mysql (mysql-connector-java-5.1.7-bin.jar) class: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/数据库名1一些基本的操作-创建表-create table (.)create table student ( name varchar2(30), age number(2)select * from student;drop table qq1;-插入语句-insert into (.) values (.)insert into student (name,age) valus (小明,10);insert into student (name) values (小红);insert into student (age) values (20);insert into student values (李四,23);-这种形式插入数据的话必须将数据填写完整-提交，必须执行此命令才会将数据放到数据表中(提交和回滚-否则数据一直保存在缓存中)commit;-删掉表中的记录delete from student;truncate table student;-清空整张表的记录最好用这个，速度快select name from student where age=23;-2.利用现有的表创建新表的方式-create table as select from where .create table student_1 as select name from student;select * from student_1;-修改-update set =? where .update student set name=赵六 where age =23;commit;-必须有此命令来提交数据，才能真正达到更新的操作-删除-delete from |delete from student where (name=赵六 and age=23);-注：清空表不建议使用delete from 表名，使用truncate table -表中数据量很大，用delete 需要花费较长的时间所以不建议使用truncate table student;-用命令truncate的时候并不需要commit命令要记住哦-删除表(包括字段属性都会删除，而delete, truncate删除的只是记录而已)drop table student;select * from student ;-增加列-alter table add ( 默认值)alter table student add (sex varchar2(2) default 男);-修改列-alter table rename column to alter table student rename column sex to code;-修改列的数据类型-alter table modify （ 列类型 默认值）update student set code =null;commit;alter table student modify (code number(10) default 100001);update student set code=100001;select * from student;-修改表的名字-alter table rename to ;alter table student rename to teacher;select * from teacher;-删除列-alter table drop ()；alter table teacher drop (code);select * from teacher;drop table teacher;-alter 修改的东西都不用commit 命令的哦-数据类型-字符类型-char 固定长度的字符串,最大容量是2000 个字节(即1000 个汉字)-varchar2 可变长度的字符串,最大容量是4000 个字节(即2000 个汉字)-long 可变长度的字符串，最大容量是2GBcreate table qq1( name char(10)select * from qq1;insert into qq1 values (abcdef);insert into qq1 values (dddd);commit;-增加一列varchar2类型alter table qq1 add (code varchar2(10) default null);update qq1 set code =abdef where name=abcdef ;commit;-类型为字节，一个汉字代表2 个字节insert into qq1 (name) values (常会信息职);select length(name) from qq1;-函数返回的长度是根据字符个数来计算的-数值类型 number(p,s) p表示精度 s 小数点后的位数-增加一列 number 默认类型alter table qq1 add (account number default null);-默认情况下插入的是实数(包括整数和小数)select * from qq1;insert into qq1 (account) values (123456.789);commit;-增加一列number类型，带精度的(保存的只是一个整数，会把小数位按照四舍五入的方式去掉)alter table qq1 add(account1 number(6) default null ); insert into qq1 (account1) values (123456.523456);commit;-增加一列number 类型，(p= 精度+ 小数点位数)=38alter table qq1 add (account2 number(16,4) default null);-默认允许为空insert into qq1 (account2) values (1234.5468);commit;-时间类型-date-增加一列date 类型alter table student add (date1 date default null);-显示格式注意to_date 函数，年月日不能少insert into student (date1) values (to_date(2010-5-9,yyyy-mm-dd);-注意该格式的写法yyyy-mm-dd hh24:mi:ss会显示为24 进制的时间insert into student (date1) values (to_date(2012-12-9 11:20:26,yyyy-mm-dd hh:mi:ss);insert into student (date1) values (to_date(2012-12-9 23:20:26,yyyy-mm-dd hh24:mi:ss);commit;select * from student;drop table student;select cast(date1 as date) from student;select date1 from student;-精简的select cast(date1 as timestamp) from student;-具体的2关于创建表时的运算符等create table qq1( name varchar(10)truncate table qq1;insert into qq1 (name) values (nihao);commit;select * from qq1;-大对象数据类型 Lob(clob blob bfile)-增加3 列alter table qq1 add(cl clob default null);alter table qq1 add(bl blob default null);alter table qq1 add(bf bfile default null);-for update 锁表，修改( 必须有这个语句才能保证不被查出的数据可以被当前用户更改，其他用户只可以读取)select * from qq1 for update;-伪列rowid rownum-1.rowid 是指记录的物理序号，用于定位数据表中的某条数据的位置唯一不可变（系统自动生成的）-2.rownum 是记录的序号，表示查询某条记录在结果集中的位置，同一条记录查询条件不同，对应的rownum不同但是rowid 不会变-3.伪列只能查询，不能增删改-4.rownum可以限制返回的行数select t.*,rownum from qq1 t where rownum =5;select t.*, t.rowid,rownum from qq1 t;-选择无重复的行distinctselect distinct name from qq1;select distinct account,t.* from qq t;-对列起别名用as( 达到计算的目的)select account*2 as a from qq;-插入日期的两种方式insert into qq (date1) values (22-12月-2012);insert into qq (date1) values (to_date(2012-12-15,yyyy-mm-dd);-插入来自其他表中的数据insert into () select from -事务控制-commit savepoint rollbackinsert into qq1 (name) values (令狐冲);insert into qq1 (name) values (杨过);savepoint p1;insert into qq1 (name) values (郭靖);insert into qq1 (name) values (小龙女);savepoint p2;insert into qq1 (name) values (乔峰);insert into qq1 (name) values (韦小宝);rollback to savepoint p1;rollback to savepoint p2;rollback ;-返回到初始点commit;select name from qq1;-sql操作符-1.算术操作符（+，-，*，/）select * from emp;-雇员表（员工）-empno 雇员的编号-ename 雇员的名字-job 雇员的工作-mgr 上级的编号-hiredate 入职时间-sal 工资-comm 奖金 -deptno 部门编号select * from dept;-部门表-deptno 部门编号-dname 部门名称-loc 部门地址select * from salgrade;-工资等级表-grade 级别-losal 最低工资-hisal 最高工资-一年工资加奖金select * from emp;select t.*,sal*(12+1) as 年薪 from emp t;create table emp1 as select * from emp;select * from emp1;create table dept1 as select * from dept;select * from dept1;create table salgrade1 as select * from salgrade;select * from salgrade1;-更新emp1update emp1 set comm =0 where comm is null;-一年工资加奖金select t.*,(sal*(12+1)+comm*12) as 年薪 from emp1 t;select t.*,(hisal -losal) as 工资幅度 from salgrade1 t;select count(sal) from emp1 ;-个数函数select sum(sal)/count(sal) from emp1;-计算平均工资 -比较操作符-= ，！= ，,=, between.and ,in, like,is null/is not nullselect * from emp1;select * from emp1 where ename=SMITH;select * from emp1 where sal=800;select * from emp1 where sal !=800;select * from emp1 where deptno 10;-注意不能有多余的空格select * from emp1 where sal 2000;select * from emp1 where sal =1000 and sal 2000 and sal 3000;select * from dept;select * from emp1 where deptno=10 or deptno=20;select * from emp1 where not ename =SMITH;-集合操作符-union (返回两个表中特定字段的所有数据，相同的数据只显示一次)select deptno from emp1 union select deptno from dept;-union all ( 返回两个表中的特定字段的所有属性，不去除重复行 )select deptno from emp1 union all select deptno from dept;-intersect ( 返回两个表中特定字段相同的数据行,且相同的数据只显示一次-minus ( 返回从第一个查询结果中去除第二个查询结果中出现的行 )select deptno from emp1 minus select deptno from dept; -注：sql 优化 ：union 替换 or ( 适用于索引项 ) 避免全表扫描-注：用 union all 替换union 可以减少查询排序带来的消耗-连接操作符|select * from emp1;-select (comm|ename|.) as s from emp1;select (员工的提成：|comm|员工的姓名是：|ename) as s from emp1;-员工的编号是： empno,姓名是：enameselect (员工的编号是：|empno) as a from emp1;select (工作：|job) as b from emp1;select (员工的薪水：|sal) as c from emp1;select (员工的姓名是：|ename) as d from emp1;3约束及主外键create table student( sid number not null,-非空约束 name varchar2(20);select * from student;insert into student (name) values (nihao);commit;insert into student values (1,乔峰);insert into student values (1,短语);-unique唯一性约束不能插入重复的值，但是可以插入多个空值null-列级的 unique 只能对单列进行约束-表级的unique 可以对单列或者多列组合进行约束drop table student;create table student(-单列约束 sid number unique , name varchar2(20) , type varchar2(20);delete from student;select * from student;insert into student (sid,name,type) values (null,很好,风流笨蛋);insert into student (sid,name,type) values (1,不好,风流笨蛋);insert into student (sid,name,type) values (2,较好,风流笨蛋);insert into student (sid,name,type) values (3,差劲,风流笨蛋);insert into student (sid,name,type) values (3,不是很好,风流笨蛋);-违反约束了commit;-create table student1( sid number, name varchar2(20), type varchar2(20), constraint student1_sid_un unique(sid,name)-组合约束，即表级别的约束);-primary key 主键约束功能相当于 (非空且唯一),在字段级别和表级别都可以定义drop table student;create table student( sid number primary key, name varchar2(20), type varchar2(20);insert into student values (2,西门吹雪,冷酷型);commit;select * from student;create table student1 ( sid number , name varchar2(20), type varchar2(20), constraint student1_sid_pk primary key(sid,name)-联合主键,每个字段都不能为空，只能在表级别定义);insert into student1 values (1,你好,笨蛋);insert into student1 values (1,陆小凤2,也是笨蛋);commit;select * from student1;-外键约束foreign key -用于确保相关的两个字段之间的参照关系-实现参照完整性约束-注：如果字表字段中有主表主键相关联的记录，主表中的相关联记录不能被删除-字段末尾 constraint foreign key (相关的字段名)-references （主表字段名）drop table student ;create table student ( sid number , name varchar2(20), type varchar2(20), constraint student_sid_pk primary key(sid);create table information( sid number , address varchar2(50), tel varchar2(11), home varchar2(20), constraint student_sid_fk foreign key(sid) references student(sid);select * from student;select * from information;insert into student values(1,张三,学习型);insert into information values (1,常州,88888,sdfsdf);commit;delete from student where sid=1;-其中包含主键，所以删除的时候要注意是否含有外键约束等信息delete from information where sid=1;-按照顺序应该先删除包含外键约束的那张表，然后再删除主表中的响应的记录才行commit;-检查约束check-定义指定字段都必须满足的条件-注意：只能在字段级别定义drop table student ;drop table information;create table student ( sid number primary key, name varchar2(20) check (length(name)3 and length(name)=0 and age=120);insert into student values(1,tomy,16);insert into student values(2,tomy2,-1);-查看 check 检查性约束commit;select * from student;- 查看约束select * from user_constraints ;select * from user_cons_columns;-修改约束-建表后添加的约束-alter table add constraint 约束类型 ()-特例:not null 约束必须要用 modify 来修改drop table student1;create table student( sid number , name varchar2(20);alter table student add constraint sutdent_sid_pk primary key(sid);alter table student modify (constraint student_name name not null);-实际此处的名字student_name并没有真实效果-删除约束-alter table drop constraint ；-主键:也可用此删除 alter table drop primary key;-删除not null 约束（要先查找相应的约束然后再删除这是比较特殊的）alter table student drop constraint SYS_C009671;-student_name 对应not null的约束名字-删除级联约束 加上cascade-alter table drop primary key cascade; -禁用约束-alter table disable constraint cascade-启用的时候要注意:必须一个一个的启用约束 .( 即不能在加上cascade)alter table student disable constraint SUTDENT_SID_PK;alter table student enable constraint SUTDENT_SID_PK;alter table student disable constraint SYS_C009674 cascade; -级联禁用可以alter table student enable constraint SYS_C009674; -级联启用有问题则不行，必须一个一个的启用才行-视图-create or replace view 定义的列名 as create or replace view v_emp (员工编号,员工姓名,工资) as select empno,ename,sal from emp where sal2000;grant dba to scott;select * from v_emp;-删除视图drop view v_emp;-强制创建视图 force（如果表不存在）-create or replace force view 定义的列名-as create or replace force view v_emp(员工编号,姓名,工资)as select empno,ename,sal from emp2;select * from v_emp; -本身此表emp2 并不存在-复杂视图-1.函数的使用create or replace view v_emp1 (部门编号,部门最高工资,部门最低工资,部门平均工资)asselect deptno,max(sal),min(sal),avg(sal) from emp group by deptno;select * from v_emp1;-2.多表的使用create or replace view v_emp2(人员编号,人员姓名,部门编号,部门名称,人员年薪)asselect empno,ename,deptno,dname,sal*13 from emp right join dept using (deptno);select * from v_emp2;-select count(*) from emp1;select * from emp1;truncate table emp1;drop table emp1;create table emp1 as select * from emp;alter table emp1 modify(empno number(6);-索引-创建索引-create index on (列名.列名)create index i_emp1 on emp1 (empno);select * from user_indexes;-查看索引select * from user_ind_columns;-查看索引是建立在哪一列上的-删除索引drop index i_emp1;-索引查找数据 ( 一般在where 条件后的字段 上设置索引 来提高查询效率 数据量大的时候才能看出来区别)-基于函数 ( 表达式 )的索引-create index on (function)create index i1_emp1 on emp1 (lower(ename);select * from user_indexes;select * from emp1 where lower(ename) =jerry;-序列（独立的对象，与表没有关系）-创建序列-create sequence -increment by n 设置序列步长-start with n 序列初始值-maxvalue/nomaxvalue 有无序列最大值-minvalue/nominvalue 有无序列最小值-cycle/nocycle 是否循环-cache/nocache 是否设置缓存-order/noorder 是否排序create sequence seq_emp1 increment by 1start with 1maxvalue 200minvalue 1nocycle;-修改序列-alter sequence -increment by n 设置序列步长-start with n 序列初始值 -初始值可以修改但是必须要与最小值对应要注意理解-maxvalue/nomaxvalue 有无序列最大值 -maxvalue 可以更改，无限制-minvalue/nominvalue 有无序列最小值 -最小值不能大于等于初始值 ( 即必须小于初始值)，前提是修改的情况下-cycle/nocycle 是否循环-cache/nocache 是否设置缓存-order/noorder 是否排序-删除序列-drop sequence drop sequence seq_emp1;-查看序列 user_sequencesselect * from user_sequences;-表如何引用，使用该索引呢？-使用序列-获取当前序列值select seq_emp1.nextval from dual;select seq_emp1.currval from dual;-select count(*) from emp1;select * from emp1;create table emp2 as select * from emp1;select * from emp2;truncate table emp1;alter table emp1 add constraint emp_pk primary key(empno);alter table emp1 modify (empno number(6);insert into emp1 (empno,ename,sal,deptno)values (seq_emp1.nextval,jerry,3000,30);commit;-同义词-synonym 缩短对象名的长度-create synonym for create synonym e1 for emp1;select * from e1;-删除同义词 drop synonym drop synonym e1;-很重要必须掌握 导入/ 导出-导出（导出表 导出方案（对象） d:-cd bin目录所在的地址- 导出命令语句-A 导出表-导出命令：exp userid=scott/gaoorcl tables=(emp1) file=d:e.dmp;create table emp1 as select * from emp;select * from emp1;-2. 导出其他用户（方案）的表 ( 注意是谁导出的，还得是那个人进行导入)-注：导出其他用户的表需要 dba 权限或 exp_full_database 的权限-修改system用户的密码可以设置sys 超级管理员登陆用这个语句修改用户的密码alter user system identified by manager;-exp userid=system/managerorcl tables=(scott.emp1) file=d:e1.dmp-注意导出的是谁的表，要在表前面加上用户名-3.导出表结构，不导出表中的数据exp userid=scott/gaoorcl tables=(emp) file=d:e2.dmp rows=n-没有数据只有表的结构，导出后会很小-4. 直接导出方式（当数据量比较大的情况下）-注：速度比常规方式快，使用此法服务器上的字符集必须与客户端字符集一致exp userid=scott/gaoorcl tables=(emp) file=d:e3.dmp direct=y-B 导出方案(某用户拥有的对象)-1. 导出自己的方案（包括该用户下的表，同义词，索引，序列等信息）exp scott/gaoorcl owner=(scott) file=d:scott.dmp;-2. 导出其他用户方案exp system/managerorcl owner=(system,scott) file=d:sys.dmp-C 导出数据库 -导出自己的数据库 ( 内容好多哦 )-一般不会导出整个数据库中的内容的exp userid=system/managerorcl full=y file=d:sys1.dmpexp userid=scott/gaoorcl full=y file=d:scott1.dmp-此处会报错，必须是dba 才能执行完整数据库或表空间的导出操作-导入 （导入表 导入方案/对象 导入数据库）-imp 导入命令-userid 执行导入操作的用户名，口令，连接字符串-tables 执行导入操作的表-fromuser 指定源用户-touser 指定目标用户 -file 指定导入文件名-full=y 指定执行导入整个数据库-inctype 指定执行导入操作的增量类型-rows 指定是否导入表中的数据-ignore 如果表存在，只导入表中数据-A 导入表-1. 导入自己的表select count(*) from emp1;truncate table emp1;drop table emp1;imp userid=scott/gaoorcl tables=(emp1) file=d:e.dmp-2.导入表到其他用户drop table scott.emp1;select * from scott.emp1;