Oracle数据库初级常用操作总结.doc

此文档收集于网络，如有侵权，请联系网站删除查询语句基本数据查询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);Select * from 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 salALL(SELECT MIN(sal) FROM emp GROUP BY deptno);插入语句intsert into 表名 values(值1 值2 值3.) 插入的数值的字段类型要与定义的时候一样intsert into 表名 values（值1，值2，null，值3） 有不想写的可以用null 来标示intsert into 表名(字段1.字段2，字段3) values(值1 值2 值3) 类型对应insert into test (smallclass,bigclass) values (asp教程,好好学习吧)Insert into INSERT INTO SALFLDG (ACCNT_CODE,PERIOD) VALUES (SELECT ACCNT_CODE,PERIOD From SALFLDG GROUP BY ACCNT_CODE,PERIOD) 注意：数值的 类型 个数 顺序 不能超过最大值插入记录到表中INSERT 的语法INSERT INTO 表名(字段名1,字段名2,.)VALUES(值1,值2.);为MYEMP中增加一条记录INSERT INTO myemp(empno,ename,job)VALUES(7899,张三,清洁工)注：给定的值要和指定的字段数一致Insert into myemp(empno,ename,job) values (7899,张三,清洁工)当为表中的所有列都插入数据数，可以采用如下简单写法aINSERT INTO 表名VALUES(值1,值2.);INSERT INTO myemp VALUES(7991,李四,清洁工,7369,14-2月 -1995,9000,300,40)注：要求给定值的数目和表中列的数目一致，并且值的顺序和表中列的顺序一致。新增加一条记录到MYEMP中，但是该员工没有奖金也没有上级领导 第一种写法：INSERT INTO myemp (empno,ename,job,hiredate,sal,deptno)VALUES(7899,张三,清洁工,14-2月 -1995,9000,40) 第二种写法：(用null来指定没有值的列)INSERT INTO myemp VALUES(7898,王五,清洁工,null,14-2月 -1995,9000,null,40)新增加一条记录到MYEMP中，但日期格式按1988-09-09的方式指定 TO_DATE函数的使用INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno)VALUES(7896,赵六,清洁工,TO_DATE(1984-09-09,yyyy-mm-dd),9000,40);修改记录update 表名 set 字段= 值，字段 =值 ，字段= 值. where 条件;例子：Update customers Set credit=200000.00,state=021 Where ID in (80,90,100,120)注意：条件必须写Update customers set credit=2000,00 state=021 where id in (80,90,100,120)删除记录delete from 表名 where 条件；注意：条件必须写 ROLLBACK （回滚）在sqlplus 中如果使用 ROLLBACK的话那么将 撤销操作直到 上次执行 commit 的时候的状态标量函数的使用转化大小写 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 BY创建表create table 表名（字段1 数值类型 defult xx， 字段2 数值类型，）;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 （列名称 数据类型 default 默认值）注意：修改的时候（和原来相同数据类型） 长度 要求不能小于数据中 数据的最小长度，但是能增大，要想 改变数据类型的话，必须将字段下的数据全部删除 才可以更改数据类型表名重命名rename 表名（旧） to 表名（新）；截断表truncate 表名；注意：清空表中的所有数据 立即释放资源，该操作不可回滚ROWNUM 表示行号， 是一个伪列，可以在每一张表中出现应用：查询表中记录的前五行SELECT *FROM EMP WHERE ROWNUM BETWEEN =5;现在要查询从6 到 10条记录，使用子查询SELECT *FROM(SELECT ROWNUM num,empno,ename,salFROM emp) eeWHERE num BETWEEN 6 AND 10;视图创建视图语法：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;注:当删除视图所在的表时，则视图也不能再被使用序列创建序列语法：CREATE SEQUENCE 序列名称MAXVALUE num | NOMAXVALUEMINVALUE num | NOMINVALUEINCREMENT BY num START WITH 10CACHE num | NOCACHE;CYCLE | NOCYCLE创建序列CREATE SEQUENCE myseq;序列的操作 nextVal ：取得序列的下一个内容 currVal：取得当前序列的内容创建表验证序列的操作 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;数据库的常用操作语言：show user; 察看当前连接的用户connect scott/passwored 采用scott的用户名/密码连接数据库desc table_name; 察看tableName表结构quit | exit ; 退出disconnect; 断开连接clear screen; 清屏，相当于Windows下的cls命令select * from tab; 列出当前用户下的所有表。 path 执行path指定的脚本文件Oracle 常用基本数据类型:varchar2/varchar 变长字符串char 定长字符串Integer 整型number ( m, n) 数字型smallint 短整型float 浮点数decimal 十进制数字date 日期型SQL分类 DML(Data Manipulation Language,数据操作语言) 用于检索或修改数据 DDL(Data Definition Language，数据定义语言)用于定义数据的结构，如创建修改或者删除数据库对象 DCL(Data Control Language，数据控制语言)用于定义数据库用户的权限约束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 约束名称;表空间管理创建表空间CREATE TABLESPACE 表空间名称 DATAFILE 数据文件的全路径 SIZE表空间的大小; 删除表空间 DROP TABLESPACE 表空