oracle汇总.doc

环境设置SET LINESIZE 300;设置行长度SET PAGESIZE 30;设置页长度用户连接conn 用户名/密码 AS SYSDBA/SYSOPERshow user;显示当前用户SQL语句简单查询SELECT DISTINCT 去除重复* |字段 别名 FROM 表名；按列显示数据SELECT 随意字符|字段|随意字符|字段 FROM 表名；按行显示数据并显示随意字符SELECT 字段（+-*/）数字 别名 FROM 表名；-四则运算，最好写上别名，方便查看显示出的数据限定查询SELECT DISTINCT 去除重复* |字段 别名 FROM 表名 WHERE 条件（s）；字段 IS NOT NULL;查询非空；多条件连接AND OR NOT可以通过（）表示一组条件范围查找 字段 BETWEEN 最小值 AND 最大值 包含=相当于 = and 、=、=等计算符号不等于符号：在SQL中有两种不等于符号:、!=。例：WHERE 字段值；WHERE 字段!=值；使用ORDER BY 进行排序SELECT DISTINCT 去除重复* |字段 别名 FROM 表名WHERE 条件（s）ORDER BY 排序的字段1，排序的字段2 ASC升序|DESC降序；单行函数语法：function_name(column|expression,arg1,arg2.)参数说明：function_name：函数名称column:数据库列名expression:字符串或计算表达式arg1,arg2:在函数中使用参数 函数分类：字符、数值、日期、转换、通用字符函数：将小写转为大写UPPER（小写字符）SELECT UPPER(小写字符) FROM DUAL;将大写转为小写LOWER（大写字符）SELECT LOWER(大写字符) FROM DUAL;将单词的第一个字母大写 INITCAP(字符);SELECT INITCAP(字符) FROM DUAL;字符除了可以使用|连接外还可以使用CONCAT()函数进行连接操作字符串的截取：SUBSTR()字符串的长度：LENGTH()内容的替换：REPLACE()SELECT SUBSTR(hello,1,3) 截取字符串, LENGTH(hello)字符串长度, REPLACE(hello,l,x)字符串替换 FROM DUAL;SUBSTR的截取点从0和从1开始都是一样的。输入的位置是负数时，为倒着截取字符。数值函数：四舍五入：ROUND()ROUND(数字，正数,保留的N位的小数|负数，对小数点前的四舍五入)截断小数点：TRUNC()TRUNC(数字，正数,保留的N位的小数|负数，对小数点前截断)取余(取模)：MOD()MOD(数字，数字)取余日期函数：日期函数的规律日期-数字=日期日期+数字=日期日期-日期=数字（天数）使用SYSDATE获取当前日期ORACLE提供了以下几种日期函数MONTHS_BETWEEN():求出给定日期范围的月数MONTHS_BETWEEN(日期，日期)ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期ADD_MONTHS(日期，数值)NEXT_DAY()：下一个给定日期是哪一个日期NEXT_DAY(日期，星期一)LAST_DAY()：求出给定日期的月最后一天日期LAST_DAY(日期)转换函数：TO_CHAR():转换成字符串TO_NUMBER()：转换成数字TO_DATE()：转换成日期年为yyyy 月为mm 日为dd可以使用TO_CHAR(yyyy-mm-dd)转换月份不够两个使用0做为前导，如果不想使用可以使用FM去掉0例:TO_CHAR(sysdate,fmyyyy-mm-dd)数字转换为字符9表示一位数字例：to_char(sal,99,999)希望数字可以显示表示出区域，可以使用两个符号：$:表示美元L：local表示本地语言显示的金额例：to_char(sal,$99,999)to_char(sal,L99,999)TO_NUMBER(字符)TO_DATE(2009-11-13,yyyy-mm-dd)通用函数：NVL:如果为空按指定的数值计算，不为空按默认计算例:(sal+NVL(comm,0)*12 income计算年薪DECODE:类似IF.ELSEIF.ELSE语句例:SELECT DECODE(2,1,内容是1,2,内容是2,3,内容是3) from dual;多表查询：SELECT DISTINCT 去除重复* |字段 别名 FROM 表名1 别名1，表名2 别名2WHERE 条件（s）ORDER BY 排序的字段1，排序的字段2 ASC升序|DESC降序；使用where关系表之间关联的字段,去除笛卡尔积.左连接和右连接：默认为左连接右连接：select * from emp e,dept d where e.deptno(+)=d.deptno;左连接：select * from emp e,dept d where e.deptno=d.deptno(+);SQL1999：了解CROSS JOIN：交叉连接，产生笛卡尔积。例：SELECT * FROM emp CROSS JOIN dept;NATURAL JOIN:自然连接,自动进行关联字段的匹配。例：SELECT * FROM emp NATURAL JOIN dept;USING：直接关联操作列例：SELECT * FROM emp e JOIN dept d USING(deptno) ;ON:用户自己编写连接条件例：SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno) where e.deptno=30;LEFT JOIN:左连接RIGHT JOIN:右连接LEFT OUT JOIN:左外连接RIGHT OUT JOIN:右外连接组函数和分组统计组函数：COUNT():全部记录数MAX():最大值MIN():最小值AVG():平均值SUM():总和分组统计：SELECTDISTINCT*|查询列1 别名1,查询列2 别名2,.FROM 表名称1 别名1,表名称2 别名2.WHERE 条件(s)GROUP BY 分组条件 HAVING 分组条件ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC.HAVING中的条件为分组函数条件。例：SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING AVG(sal)2000;嵌套查询不能写入列。例:SELECT MAX(AVG(sal) FROM emp GROUP BY deptno;ORDER BY 中使用列别名进行排序例：SELECT deptno,avg(sal) sa FROM emp GROUP BY deptno HAVING AVG(sal)2000ORDER BY sa ASC;子查询SELECTDISTINCT*|查询列1 别名1,查询列2 别名2,.FROM 表名称1 别名1,表名称2 别名2(SELECTDISTINCT*|查询列1 别名1,查询列2 别名2,.FROM 表名称1 别名1,表名称2 别名2.WHERE 条件(s)GROUP BY 分组条件 HAVING 分组条件ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC.).WHERE 条件(s)(SELECTDISTINCT*|查询列1 别名1,查询列2 别名2,.FROM 表名称1 别名1,表名称2 别名2.WHERE 条件(s)GROUP BY 分组条件 HAVING 分组条件ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC.)GROUP BY 分组条件 HAVING 分组条件ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC.例：查出比7654工资高的人员信息select * from emp where sal(select sal from emp where empno=7654);单列子查询：返回的结果是一列的一个内容单行子查询：返回多个列，有可能是一条完整的记录多行子查询：返回多条记录例：要求查询出比7654工资高，同时与7788从事相同工作的人员信息select * from emp where sal(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);例：要求查询出工资最低的姓名，工作，工资select ename,job,sal from emp where sal=(select min(sal) from emp);例：部门名称，部门员工数，部门的平均工资，部门的最低收入雇员姓名select d.dname ,s.count,s.avg,s.min,e.enamefrom dept d,(select deptno,count(deptno) count,avg(sal) avg,min(sal) minfrom emp group by deptno) s,emp ewhere d.deptno=s.deptno and e.sal=s.min;注：dept表与临时表进行关联，在from中列出多个子查询子查询中使用的符号IN操作：指定一个查询的范围例：查找每个部门的最小工资select * from emp where sal in (select min(sal) from emp group by deptno);ANY操作：=ANY：与IN操作符完全一样ANY:比里面最小的值要大ANY：比里面最大的值要小例：select * from emp where sal ALL:比里面最大的值要大ALL (select min(sal) from emp group by deptno);多列子查询select * from empwhere (sal,nvl(comm,-1) IN (select sal,nvl(comm,-1)from emp where deptno=20);数据库的更新操作CREATE table myemp as select * from emp;复制表及表中的数据插入数据INSERT INTO 表名称（字段名称1，字段名称2.） VALUES（值1，值2.）数值字段不用加单引号,但字符及日期需加单引号如不写字段名称，在值中可以使用NULL排列。其中插入的日期格式可以使用TO_DATE函数，将字符转换成日期。例：INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,张三,老板,null,TO_DATE(1990-10-12,yyyy-mm-dd),1000,10,40);更新数据修改全部：UPDATE 表名称 SET 要修改的字段=新值，要修改的字段=新值；修改局部：UPDATE 表名称 SET 要修改的字段=新值，要修改的字段=新值 WHERE 条件；条件中可以使用in等函数，删除数据删除全部：DELETE FROM 表名称删除局部：DELETE FROM 表名称 WHERE 条件事务处理提交事务：commit回滚事务：rollback创建和管理表常用数据类型No数据类型描述1VARCHAR、VARCHAR2表示的是一个字符串，有长度限制，长度为2552NUMBERNUMBER（n）：表示一个整数，数字长度为n,可以使用INT代替NUMBER（m,n）：表示一个小数，小数长度为n,整数长度为m-n,可以使用float3DATE表示日期类型，日期要按照标准进行存放4CLOB大对象，表示大文本数据，一般可以存放4G文本5BLOB大对象，表示二进制数据，最大可以存放4G，例如，电影，歌曲表建立CREATE TABLE 表名称（字段名称1 字段类型 DEFAULT 默认值字段名称2 字段类型 DEFAULT 默认值字段名称n 字段类型 DEFAULT 默认值）表复制CREATE TABLE 表名称 AS SELECT * FROM 表名称 ；复制表结构及数据CREATE TABLE 表名称 AS SELECT * FROM 表名称 WHERE 1=2 ；复制表结构CREATE TABLE person(pid varchar2(18),name varchar2(200),age number(3),brithday date,sex varchar2(2) default 男);表删除DROP TABLE 表名称；表的修改：ALTER指令语法增加一个列ALTER TABLE 表名称 ADD（列的名称 列的类型 DEFAULT 默认值，列的名称 列的类型 DEFAULT 默认值，）;修改一个列ALTER TABLE 表名称 MODIFY(列名称 列类型 DEFAULT 默认值)如果列中有长数据,则无法修改长度.删除一个列ALTER TABLE schema_name.table_name DROP COLUMN column_name1|column_name2 CASCADE CONSTRAINT表的重命名-只能在ORACLE中使用RENAME 旧表名称 TO 新表名称截断表 立刻删除表中数据并提交 TRUNCATE TABLE 表名称约束主键约束：主键表示是一个唯一的标识，本身不能为空|例如身份证唯一约束：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则可以使用唯一约束。检查约束：检查一个列是否合法|-例如：年龄，只能在0150岁|-例如：性别，只能为男，女，中性非空约束：姓名这样的字段里面的内容就不能为空外键约束：在两张表中进行的约束操作。主键约束：PRIMARY KEY可以在建立表的时候指定。CONSTRAINT可以指定约束，为约束命名。例：CONSTRAINT person_pid_pk PRIMARY KEY (pid)唯一约束：UNIQUE非空约束：NOT NULL检查约束：CHECK例：drop table person;create table person(pid varchar2(32) primary key,name varchar2(30) not null,age number(3) not null check(age between 0 and 150),sex varchar(2) not null check(sex in(男,女),constraint person_name_un unique(name);主外键约束：针对于两张表的约束CONSTRAINT 约束名FOREIGN KEY (列名) REFERENCES 表名(列名) 级联删除: CONSTRAINT 约束名FOREIGN KEY (列名) REFERENCES 表名(列名) ON DELETE CASCADE当删除父表中的字段，子表中字段也将删除注意：在子表中指定的外键必须是父表中的主键删除时应先删除子表，再删除父表。在删除表时，也可以使用强制级联删除使用命令DROP TABLE 表名 CASCADE CONSTRAINT;但不建议使用。例1：constraint person_pid_pk foreign key (pid) references person(pid)例2：drop table person;create table person(pid varchar2(2) primary key,name varchar2(30) not null,age number(3) not null check(age between 0 and 150),sex varchar(2) not null check(sex in(男,女),constraint person_name_un unique(name);drop table book;create table book(bid varchar(2) primary key ,bname varchar2(30) not null,bprice number(3,2) not null,pid varchar (2) not null,foreign key(pid) references person(pid);insert into person(pid,name,age,sex) values(1,张三,20,男);insert into book(bid,bname,bprice,pid) values(1,JAVA_SE,3.2,1); 修改约束不建议使用添加约束：ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型（约束字段）；删除约束：ALTER TABLE表名称 DROP CONSTRAINT 约束名称;约束的命名：PRIMARY KEY：主键字段_PKUNIQUE:字段_UKCHECK:字段_CKFOREIGN KEY:父字段_子字段_FKROWNUM重要 表示行数,实际是一个列，是一个伪列，可以在所有表中建立。例：select ROWNUM,empno,ename,sal from emp;类似序号集合操作三种集合操作：并（UNION）、交（INTERSECT）、差（MINUS）UNION：将多个查询结果组合到一个查询结果之中，不含重复值UNION ALL：也是将多个查询结果组合到一个查询结果之中，包含重复值。INTERSECT：返回多个查询结果中相同的部分MINUS：返回两个查询结果的差异例：select * from emp MINUS select * from emp20;视图视图的功能：一个视图实际是封闭了一条复杂的查询语句创建视图的语法：CREATE VIEW 视图名称 AS 子查询实际上此时的子查询就表示一条非常复杂的语句。范例：建立一个视图，此视图包含了全部的20部门的雇员信息(雇员编号、姓名、工作、日期)。CREATE VIEW empv20 AS select empno,ename,job,hiredate from emp where deptno=20;范例：查询视图select * from empv20;删除视图：DROP VIEW 视图名称CREATE OR REPLACE 视图名称 AS 子查询此语法使用后，可以自动完成删除视图并重建的功能。WITH CHECK OPTION：不能更新视图的创建条件。WITH READ ONLY：创建的视图只读例：CREATE VIEW empv20 AS select empno,ename,job,hiredate from emp where deptno=20 WITH CHECK OPTION;序列在ORACLE中完成自动增加的功能，需要手工完成处理。CREATE SEQUENCE 序列名;nextVAL:取得序列的下一个内容。currVAL:取得序列当前内容。例：CREATE SEQUENCE squ;CREATE TABLE testsqu(next number,curr number);INSERT INTO testsqu(next,curr) values(squ.nextval,squ.currval);INSERT INTO testsqu(next,curr) values(squ.nextval,squ.currval);INCREMENT BY 长度：每次的增长的幅度例：CREATE SEQUENCE seq INCREMENT BY 2;INSERT INTO testsqu(next,curr) values(seq.nextval,seq.currval);START WITH 开始数：指定起始值。另还有最大值，最小值，循环等参数。同义词了解SELECTSYSDATE FROMDUAL;同义词语法：CREATE SYNONYM 同义词名称 FOR 用户名.表名称;范例：CREATE SYNONYM emp FOR scott.emp;删除语法：DROP SYNONYM emp;用户管理了解创建用户的语法：CREATE USER 用户名 IDENTIFIED BY 密码；例：CREATE USER test IDENTIFIED BY test123;要在管理员下创建权限使用如下格式：GRANT 权限1、权限2、。TO 用户；例：GRANTCREATE SESSION TO test;ORACLE中提供了两个主要角色CONNECT（连接操作）、RESOURCE（资源操作），包含了多个权限。在创建用户的时候可以直接把这两个角色赋予test这个用户。例：GRANT CONNECT,RESOURCE to test;更改密码语法：ALTER USER 用户名 IDENTIFIED BY 密码;例：ALTER USER test IDENTIFIED BY test;密码失效语法：ALTER USER 用户名 PASSWORD EXPIRE锁住一个用户：ALTER USER 用户名 ACCOUNT LOCK;解锁一个用户：ALTER USER 用户名 ACCOUNT UNLOCK;授权其他用户表的访问权限：例：GRANT SELECT,DELETE ON scott.emp TO test;回收权限REVOKE 权限 ON 用户.表名称 FROM 用户;数据库的备份与恢复（了解）数据库备份：exp数据库恢复：imp在D盘中建立一个文件夹，在此文件夹中保存所有的备份文件，如果要备份，则需要使用命令行方式，进入到data文件夹中。使用exp命令进行备份。进行到data文件夹中，可以使用imp恢复备份。嵌套表（了解）在一个表中还包含一个子表。先定义类型：例：CREATE TYPE project_ty AS OBJECT(proidnumber(4),pnamevarchar2(30),prodatedate);/必须要加加/此时类型不能使用，需要为类型指定名称。以下为类型指定名称,类似于VARCHAR2表示字符串是一样的：CREATE TYPE project_nt AS TABLE OF project_ty;/必须要加/建立嵌套