ORACLE数据库操作总结大全

Oracle 数据库操作总结目 录1.登录操作22.常用工具23.SQL Plus常用命令24.常用数据类型:35.SQL分类36.数据操作（DML）36.1.查询语句36.2.插入语句66.3.修改记录76.4.删除记录76.5.ROLLBACK （回滚）76.6.左连接与右连接77.标量函数的使用98.对象管理（DDL）128.1.表128.2.视图138.3.序列148.4.表空间159.约束1510.用户管理1611.权限管理1712.角色管理1813.表空间与段1813.1.表空间1813.2.段191. 登录操作Oracle安装好后，系统默认有超级管理员“sys”和普通用户“scott”。 scott的密码是tiger。如果要使用scott用户登录，则要做以下工作。(1) 新建一个服务别名myorcl（名字可以自己命名），使服务别名指向要登录的数据库。(2) 用sys超级管理员给scott用户解锁（因为scott这个用户被锁住了，不能登录）。解锁命令：ALTER USER scott ACCOUNT UNLOCK。(3) 用户scott用户登录，登录命令：sysplus scott/tigermyorcl。SQLPlus工具登录命令：系统管理员登录，DOS中执行命令：sqlplus username/passwordserviceName as sysdba 普通用户登录，DOS中执行命令：sqlplus username/passwordserviceName 2. 常用工具SQLPlus系统管理员登录，DOS中执行命令：sqlplus username/passwordserviceName as sysdba 普通用户登录，DOS中执行命令：sqlplus username/passwordserviceName SQLPLusW 可视化窗口 DOS中执行命令：sqlpluswiSQLPlus(网页形式): 访问网页地址：:5560/isqlplus/Enterprise Manager(网页形式): 访问网页地址：:5500/em/说明：访问iSQLPlus(网页形式)和Enterprise Manager(网页形式)时需要网址端口号，如果忘记，可在此文件中查：ORACL_HOME/product/10.2.0/db_1/install/portlist.ini。3. SQL Plus常用命令SQL Plus中的常用命令：select * from tab; 列出当前用户下的所有表。show user; 察看当前连接的用户connect scott/password 采用scott的用户名/密码连接数据库desc table_name; 察看tableName表结构quit | exit ; 退出disconnect; 断开连接clear screen; 清屏，相当于Windows下的cls命令 path 执行path指定的脚本文件4. 常用数据类型:varchar2变长字符串char 定长字符串Integer 整型number ( m, n) 数字型smallint 短整型float 浮点数decimal 十进制数字date 日期型5. SQL分类 DML(Data Manipulation Language,数据操作语言) 用于检索或修改数据 DDL(Data Definition Language，数据定义语言)用于定义数据的结构，如创建修改或者删除数据库对象 DCL(Data Control Language，数据控制语言)用于定义数据库用户的权限6. 数据操作（DML）6.1. 查询语句基本数据查询select 内容 from 表名；select distinct job from emp；去除重复行限定查询（查询满足条件的行）SELECT * | 列名FROM 表名WHERE 条件; SELECT empno,ename,commFROM empWHERE comm IS NOT NULL; 限定查询-IS NULL和IS NOT NULL的使用SELECT empno,ename,commFROM empWHERE comm IS NULL查询谁没有奖金限定查询-AND 的使用 查询工资大于1500，并且可以领取奖金的雇员 SELECT empno,ename,sal,commFROM EMPWHERE sal 1500 AND comm IS NOT NULL;限定查询-OR 的使用 查询工资大于1500和可以领取奖金的雇员 SELECT empno,ename,sal,commFROM EMPWHERE sal 1500 OR comm IS NOT NULL;限定查询-使用NOT对条件整体取反 查询工资不大于1500并且不能领取资金的雇员 SELECT empno,ename,sal,commFROM empWHERE NOT (sal1500 AND comm IS NOT NULL);限定查询-BETWEEN . AND.的使用 查询基本工资大于等于1500并且小于等于3000的雇员SELECT empno,ename,sal,commFROM empWHERE sal BETWEEN 1500 AND 3000限定查询-IN 的使用 查询出雇员编号是7369，7499，7521的雇员的具体信息 SELECT *FROM empWHERE empno IN(7369,7499,7521);限定查询-NOT IN 的使用 查询出雇员编号不是7369，7499，7521的雇员的具体信息 SELECT *FROM empWHERE empno NOT IN(7369,7499,7521);限定查询-LIKE 的使用 查询中雇员的名字第二个字符是M的雇员信息 SELECT empno,ename,comm,salFROM empWHERE ename LIKE _M%; 说明： _ 匹配一个字符，% 匹配0个或多个字符限定查询-LIKE 匹配日期 查询1982年入职的所有雇员的信息 SELECT *FROM empWHERE hiredateLIKE %82%限定查询-LIKE 匹配数字 查询工资中包含 5 的雇员信息 SELECT *FROM empWHERE salLIKE %5%对结果排序-ORDER BY带有ORDER BY 子句的SQL语句基本格式SELECT 列. FROM 表 WHERE 条件 ORDER BY 列 查询员工资大于1500的信息，按工资排序 SELECT * FROM emp WHERE sal1500 ORDER BY sal查询工资大于1500员工的信息，按工资降序,按雇佣日期升序排序 SELECT * FROM emp WHERE sal1500ORDER BY salDESC,hiredate ASC 说明: ASC 排序，DESC降序，默认ASC左、右外连接查询员工编号，姓名，所在部门号，部门名称，将没有员工的部门也显示出来SELECT e.ename,d.deptno,d.dnameFROM emp e,dept dWHERE e.deptno(+)=d.deptno;注：（+）在左边，表示右连接，会列出右表中出现但是没有在左表中出现的行交叉连接（CROSS JOIN）：用来产生笛卡尔积的SELECT * FROM emp CROSS JOIN dept;自然连接（NATURAL JOIN）：自动进行关联字段的匹配SELECT * FROM emp NATURAL JOIN dept;USING子句：直接指定操作关联列SELECT * FROM emp JOIN dept USING(deptno);ON 子句：用户自己编写连接条件SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno;RIGHT JOIN：右外连接SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;子查询-IN 的使用 查询和SMITH或JONES在同一部门，同一职位工作的员工SELECT * FROM emp WHERE (deptno,job) IN(SELECT deptno,job FROM emp WHERE enameIN(SMITH,JONES);子查询-ANY 的使用 =ANY ： 与 IN操作符的效果一致查询和SMITH或JONES在同一部门，同一职位工作的员工SELECT * FROM emp WHERE (deptno,job) =ANY (SELECT deptno,jopFROM emp WHERE ename IN(SMITH,JONES);ANY ： 只要大于子查询中的任何一个值即可SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM EMP GROUP BY deptno);ALL：比最大的值大SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno);ANY ： 只要小于子查询中的任何一个值即可SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);ALL：比最小的值小SELECT * FROM emp WHERE sal、=、=、!和。 （3）自然连接：自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。等值连接与自然连接的区别：（1）等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。 （2）等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。 2、外连接: （1）左外连接 (左边的表不加限制) （2）右外连接(右边的表不加限制) （3）全外连接(左右两表都不加限制) 3、自连接(连接发生在一张表内) SQL标准连接语法：select table1.column, table2.columnfrom table1 inner | left | right | full join table2 on table1.column1 = table2.column2;例如：select a.*,b.* from a left join b on a.编号=b.编号INNER JOIN表示内连接；LEFT JOIN 或 LEFT OUTER JOIN表示左外连接；RIGHT JOIN 或 RIGHT OUTER JOIN表示右外连接；FULL JOIN 或 FULL OUTER JOIN表示完全外连接；不管匹配ON子句用于指定连接条件。如果主表的主键列和从表的外部键列名称相同，那么可以使用自然连接 (natural join) 关键字自动匹配内连接操作。使用“(+)”符号的左右连接：右连接：select a.studentno, a.studentname, b.classname from students a, classes b where a.classid(+) = b.classid;左连接：select a.studentno, a.studentname, b.classname from students a, classes b where a.classid = b.classid(+);(+)写在主表的另一侧，主表的所有记录均会被显示。(+)所在位置的另一侧为连接的方向。右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配。左连接相反。注意：如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；如果使用（+）操作符指定外连接，则必须使用where子句指定连接条件。7. 标量函数的使用转化大小写 select upper(sddfa) from dual select lower(sddfa) from dual将首字母大写 其余小写 select initcap （ename）from emp；连接字符串：select count（deptno）from dept；select contact (hello,woeld) from dual;select abc| jkh| sdf from dual;求子串：select substr (字符串，起始位，结束位) from dual; 起始位写 0或者1 都可以代表第一位select substr (字符串，起始位) from dual; 从起始位开始往后截取求字符串长度：select length（ename ）from emp;字符串替换select replace （原始字符串，想要被替换的字符串，替换字符串 ）from dual；输出字符串的后三位select ename ，substr（ename，length（ename）-2） from dual ；select ename substr（ename，-3）from dual ；四舍五入select round（132.78454）from dual； 四舍五入取整select round（132.78345，2）from dual； 四舍五入并且指定保留小数点的位数select round（1323.7858416，-2）from dual；小数不要 并且 整数位两位取整 本代码得1300截断小数位select trunc （546541.151） from dual 不管四舍五入 直接截断小数 5465541select trunc（789.536，2） from dual 截断小数 指定小数的数位 789.53select trunc（789.536，-2） from dual 截断小数 并且对整数进行截断 700取余select mod（10，3）from dual ； 取余数 1运算规律：日期+ 数字=日期日期-数字=日期日期日期 = 数字显示系统时间select sysdate from dual；select to_char(sysdate,yyyy-mm-dd,hh24:mi:ss) from dual;显示某本门员工进入公司星期数：select empno，ename ，round（sysdate-hiredate）/7）from emp where deptno=10；在指定的日期加上指定的月数后的日期：select add_months(sysdate,2) from dual;求出给定日期范围的月数：select empno ename month_between(sysdate,hiredate) from emp;下一个给定的星期是那个日期：select next_day(sysdate,星期一) from dual;求出给定日期所在的 月份的最后一天的日期select last_day(sysdate) from dual; 转换函数转换成字符串(TO_CHAR)查询雇员号，姓名，以及入职的年份SELECT empno,ename,TO_CHAR(hiredate,yyyy)FROM emp;说明：yyyy 匹配年份，mm 匹配月份，dd 匹配日使用TO_CHAR设置日期的显示格式 SELECT empno,ename,TO_CHAR(hiredate,yyyy-mm-dd)FROM emp;使用fm去掉前导 0 SELECT empno,ename,TO_CHAR(hiredate,fmyyyy-mm-dd)FROM emp;通过TO_CHAR设置数值的格式 SELECT empno,ename,TO_CHAR(sal,99,999) FROM emp; 说明：9 代表 1 位数字显示金额 SELECT empno,ename,TO_CHAR(sal,$99,999) FROM emp; 说明：$代表美元，L代表本地转换成数字(TO_NUMBER) SELECT TO_NUMBER(123) +TO_NUMBER(123) FROM dual;转换成日期(TO_DATE) SELECT TO_DATE(2009-07-31 ,yyyy-mm-dd) FROM dual;通用函数查询每个员工的年收入SELECT empno,ename,(sal+NVL(comm,0)*12 incomeFROM emp;说明：(COMM,0) 当comm的值为null时，用 0 替换常用的列函数 SUM(expression) 求和 MAX(expression) 求最大值 MIN(expression) 求最小值 COUNT(expression) 统计记录数 COUNT(DISTINCT COLNAME) 统计去除重复行记录数SQL语句的执行顺序FROMWHEREGROUP BYHAVINGSELECTORDER BY8. 对象管理（DDL）8.1. 表创建表create table 表名（字段1 数值类型 defult xx， 字段2 数值类型，）;create table CUSTOMER( CUSTOMER_ID int not null, CUSTOMER_NO varchar(100), CUSTOMER_NAME varchar(200), TYPE INT, - 客户类型 SOURCE_TYPE_ID int, - 来源 INDUSTRY_TYPE_ID int, - 行业 LEVEL_TYPE_ID int, - 等级 REGION_TYPE_ID int, - 区域 CREDIT_TYPE_ID int, - 信用 primary key (CUSTOMER_ID);defult 是默认值 可写 可不写 create table 表名2 as 表名1 复制表2 到 表1 中create table 表名 as （子查询） 复制子查询结果这张表，当子查询结果没有记录时 这时 只会复制 表结构（数据类型） 而不会增加数据（有时目的只是复制表结构 那么就使用这种方法 比如查询语句中 使用 select 1=2 from 表名 ）删除表drop table 表名; 注意：delete 是删除表里面的数据修改表结构增加一列：alter table 表名add（列名称 列数据类型 defult 默认值）注意：defult 和默认值 可写 可不写删除列：alter table 表名 drop column 列名称 注意：不管有没有数据都会删除修改列的数据类型：alter table 表名 modify （列名称 数据类型 defult 默认值）注意：修改的时候（和原来相同数据类型） 长度 要求不能小于数据中 数据的最小长度，但是能增大，要想 改变数据类型的话，必须将字段下的数据全部删除 才可以更改数据类型表名重命名rename 表名（旧） to 表名（新）；截断表truncate 表名；注意：清空表中的所有数据 立即释放资源，该操作不可回滚ROWNUM 表示行号， 是一个伪列，可以在每一张表中出现应用：查询表中记录的前五行SELECT *FROM EMPWHERE ROWNUM BETWEEN =5;现在要查询从6 到 10条记录，使用子查询SELECT *FROM(SELECT ROWNUM num,empno,ename,salFROM emp) eeWHERE rownum BETWEEN 6 AND 10;8.2. 视图创建视图语法：CREATE VIEW 视图名称 AS 子查询例如：创建部门20员工的雇员信息，包含EMPNO,ENAME,SAL,DEPTNOCREATE VIEW empv20 ASSELECT empno,ename,sal,deptno FROM emp;注:当创建视图以后，可以像操作表一样的操作视图注：当创建视图时，视图的操作会影响到对表的操作，这是不安全的，我们可以采用如下选项来创建视图 WITH CHECK OPTION 不能更新创建视图的条件 WITH READ ONLY 创建只读视图删除视图语法：DROP VIEW 视图名称DROP VIEW empv20; 删除视图 empv20;注:当删除视图所在的表时，则视图也不能再被使用8.3. 序列创建序列语法：CREATE SEQUENCE 序列名称MAXVALUE num | NOMAXVALUEMINVALUE num | NOMINVALUEINCREMENT BY num START WITH 10CACHE num | NOCACHE;CYCLE | NOCYCLE创建序列CREATE SEQUENCE myseq;序列的操作 nextVal ：取得序列的下一个内容 currVal：取得当前序列的内容select 序列名称.nextval from dual.创建表验证序列的操作 CREATE TABLE testseq(next NUMBER;CURR NUMBER;) INSERT INTO testseq VALUES(myseq.nextVal,myseq.currVal); 将以上插入语句，执行5次，观察效果，不难得出结论，nextVal每次都会加1，而currVal都是取当前值创建序列指定每次增长的增量 CREATE SEQUENCE myseq INCREMENT BY 2;创建序列指定开始的序列，默认的序列从1开始。 CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;创建一个循环序列 1，3，5，7，9。 CREATE SEQUENCE myseq MAXVALUE 9 INCREMENT BY 2START WITH 1 CACHE 2 CYCLE;8.4. 表空间创建表空间CREATE TABLESPACE 表空间名称 DATAFILE 数据文件的全路径 SIZE表空间的大小; 删除表空间 DROP TABLESPACE 表空间名称; 注：在进行相应操作时，应该具有相应的操作权限9. 约束constraint 约束名 约束 字段主键约束（PRIMARY KEY）CONSTRAINT persion_pid_pk PRIMARY KEY(pid);非空约束：name VARCHAR2(30) NOT NULL, 唯一约束：name VARCHAR2(30) UNIQUE NOT NULL, 在创建表时声明CONSTRAINT person_name_uk UNIQUE(pid) 在表的结尾声明检查约束：（check）age NUMBER(3) NOT NULL CHECK(age between 1 and 150),主外键约束：（如一本书要属于一个人）CREATE TABLE person(pid VARCHAR2(18),name VARCHAR2(30) NOT NULL,age NUMBER(3) NOT NULL,birthdate DATE,sex VARCHAR2(2) DEFAULT 男CONSTRAINT person_pid_pk PRIMARY KEY(pid),CONSTRAINT person_name_uk UNIQUE(name),CONSTRAINT person_age_ck CHECK(age BETWEEN 1 AND 150),CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);CREATE TABLE book(bid NUMBER PRIMARY KEY NOT NULL,bname VARCHAR(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid);增加约束ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型（约束字段） 例：分别增加主键约束和外键约束给book表ALTER TABLE book ADD CONSTRAINT book_bik_pk PRIMARY KEY(bik);ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGNKEY(pid) REFERENCES person(pid) ON DELETE CASCADE;约束的命名规范（建议） PRIMARY KEY ：表名称_主键名称_pk; UNIQUE ：表名称_字段名称_uk CHECK ：表名称_字段名称_ck 删除约束 ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;10. 用户管理 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码 ACCOUNT UNLOCK; 为用户授予连接的权限 GRANT CONNECT TO 用户名; 为用户授权为查询SCOTT下emp表的权限 GRANT SELECT ON SCOTT.emp TO 用户名; 删除用户 DROP USER 用户名; 修改用户的默认表空间 ALTER USER 用户名 DEFAULT TABLESPACE 表空间名; 修改用户的密码 ALTER USER 用户名 IDENTIFIED BY 新密码; 锁定用户 ALTER USER 用户名 ACCOUNT LOCK; 解锁用户 ALTER USER 用户名 ACCOUNT UNLOCK;11. 权限管理 级联授权（对象权限，WITH GRANT OPTION） GRANT 权限 TO 用户名 WITH GRANT OPTION; GRANT ALL ON SCOTT.DEPT TO 用户名 WITH GRANT OPTION; 为用户授权操作表空间的权限 GRANT UNLIMITED TABLESPACE TO 用户名; 给用户授予创建用户的操作 GRANT CREATE USER TO 用户名; 给用户授予删除用户的操作 GRANT DROP USER TO 用户名; 级联授权（系统权限，WITH ADMIN OPTION） GRANT 系统权限 TO 用户名 WITH ADMIN OPTION; GRANT CREATE USER TO 用户名 WITH ADMIN OPTION; 解除用户的权限 REVOKE 权限 FROM 用户名; REVOKE SELECT ON SOCTT.EMP FROM 用户名; REVOKE CREATE USER FROM 用户名12. 角色管理 创建角色 CREATE ROLE 角色名称; 为角色授权 GRANT SELECT ON SCOTT.EMP TO 角色名称; GRANT SEL