Oracle系列《一》：简单SQL与单行函数
使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 ＝ 薪金 ＋ 佣金
登录Oracle数据库
1、sqlplus scott/tiger
2、sqlplus /nolog
SQL>conn scott/tiger
若是使用SYS的账号进行登录的话，则使用以下语句
SQL>conn / as sysdba
【1】EMP表内容查询
SQL> SELECT * FROM emp;
出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了
【2】显示当前用户
SQL> show user
【3】查看当前用户的所有表
SQL> SELECT * FROM tab;
【4】若想重复执行上一条SQL语句，则在sqlplus命令行下输入"/"即可
【5】查询一张表的结构，例如dept表
SQL> desc dept
【6】在雇员表中查询雇员的编号、姓名、工作
SQL> SELECT empno,ename,job FROM emp;
【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写
SQL> SELECT empno 编号,ename 姓名,job 工作 FROM emp;
【8】查询所有的工作
SQL> SELECT DISTINCT job FROM emp;
工作可能会重复，加上DISTINCT关键字
【9】若要求按照以下的格式进行结果输出，如
NO:7469,Name:SMITH,Job:CLERK
SQL> SELECT 'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;
【10】要求列出每个雇员的姓名及年薪
SQL> SELECT ename,sal*12 income FROM emp;
这里年薪最好用别名进行标识，可以一眼就能明白
【11】查看每月可以得到奖金的雇员信息
SQL> SELECT * FROM emp WHERE comm is NOT NULL;
【12】要求基本工资大于1500，同时可以领取奖金的雇员信息
SQL> SELECT * FROM emp WHERE sal>1500 AND comm is NOT NULL; 如果是或的是关系，则使用 OR
【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息
SQL> SELECT * FROM emp WHERE NOT(sal>1500 AND comm is NOT NULL);
【14】查询在19xx年雇佣的全部雇员信息，BETWEEN .. AND 包含等于的情况
SQL> SELECT * FROM emp
WHERE hiredate BETWEEN '01-JAN-81' AND '31-DEC-81';
【15】Oracle对大小敏感，所以查询时名字要区分大小写
【16】要求查询出雇员编号不是 7369、7499的雇员信息
SQL> SELECT * FROM emp
WHERE empno NOT IN(7369,7499);
【17】SQL中LIKE语句要注意通配符 % 和 _
SQL> SELECT * FROM emp
WHERE hiredate LIKE '%81%';
【18】查看雇员编号不是7369的雇员信息，使用<>或!=
SQL> SELECT * FROM emp
WHERE empno<>7369;
【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC) SQL> SELECT * FROM emp
GROUP BY sal;
【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列
SQL> SELECT * FROM emp
WHERE deptno=10
ORDER BY sal DESC,hiredate ASC;
数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为
1、字符函数：接受字符输入并且返回字符或数值
2、数值函数：接受数值输入并返回数值
3、日期函数：对日期型数据进行操作
4、转换函数：将一种数据类型转换为另一种数据类型
5、通用函数：NVL、DECODE 函数
字符函数：
【1】大小写转换 UPPER 和 LOWER
SQL> SELECT UPPER('smith') FROM dual;
【2】将雇员姓名变为开头字母大写，INITCAP
SQL> SELECT INITCAP(ename) FROM emp;
字符函数中有连接函数CONCAT，但不如 || 好用，还有字符串处理的一些函数 字符串截取：substr()
字符串长度：length()
内容替换：replace()
SQL> SELECT
substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;
这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问
【3】要求显示所有雇员的姓名及姓名的后3个字符
SQL> SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp; 以上操作显得较为麻烦，substr()函数是可以倒着截取
SQL> SELECT ename,SUBSTR(ename,-3,3) FROM emp;
数值函数：
1、四舍五入：ROUND()
2、截断小数位：TRUNC()
3、取余(取模)：MOD
SQL> SELECT ROUND(789.536) FROM dual;
【1】保留2位小数,(如果是-2则对整数进行四舍五入，变为800了) SQL> SELECT ROUND(783.56,2) FROM dual;
【2】使用MOD()函数进行取余操作
SQL> SELECT MOD(10,3) FROM dual;
日期函数：
1、日期 - 数字 = 日期
2、日期 + 数字 = 日期
3、日期 - 日期 = 数字(天数)
【1】求出当前日期
SQL> SELECT SYSDATE FROM dual;
Oracle提供了以下的日期函数支持：
MONTHS_BETWEEN():求出给定日期范围的月数
ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期 NEXT_DAY():下一个的今天的日期
LAST_DAY():求出给定日期的最后一天日期
【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
SQL> SELECT
empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;
【3】验证 ADD_MONTHS()、NEXT_DAY()、LAST_DAY()
SQL> SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
SQL> SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
转换函数：
1、TO_CHAR()： 将日期或数值转换成字符串
2、TO_NUMBER()：将字符串转换成数字
3、TO_DATE()： 将字符串转换成日期
【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-dd
SQL> SELECT empno,ename,TO_CHAR(hiredate,'yyyy') datetime FROM emp;
【2】将薪水的数字进行格式化，'$99,999'表示美元，'L99,999'表示当地货币
SQL> SELECT empno,ename,TO_CHAR(sal,'99,999) salary FROM emp;
【3】TO_NUMBER()验证
SQL> SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM DUAL;
【4】TO_DATE()验证，如下例子执行后显示为 11-JUL-11
SQL> SELECT TO_DATE('2011-7-11','yyyy-mm-dd') FROM DUAL;
通用函数：
【1】求出每个雇员的年薪(应算上奖金)
SQL> SELECT empno,ename,(sal+comm)*12 FROM emp;
由于comm中有NULL,NULL值计算后还是NULL,正确如下：
SQL> SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;
NVL可以理解为将NULL值转换为具体的内容，这里是0
【2】DECODE()函数，该函数类似于 IF ... ELSEIF...ELSE
语法如下：
DECODE(col/expression,选择1,结果1[，选择2，结果2，...,默认]) 验证DECODE()函数
SQL> SELECT empno,ename,hiredate,
DECODE(job,'CLERK','业务员','SALESMAN','销售人员
',’MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位
FROM emp;
SQL简单语句练习：
【1】找出佣金高于薪金的60%的员工
SQL> SELECT * FROM emp WHERE comm>sal*0.6
【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
SQL> SELECT * FROM emp
WHERE (deptno=20 AND job='MANAGER')
OR (deptno=10 AND job='CLERK');
【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料
SQL> SELECT * FROM emp
WHERE job NOT IN('MANAGER','CLERK') AND sal >= 2000;
【4】找出有奖金的员工的不同国祚
SQL> SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;
【5】找出各月倒数第3天受雇的所有员工
SQL> SELECT * FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;
【6】找出早于12年前受雇的员工
SQL> SELECT * FROM emp
WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 > 12;
【7】显示刚好为5个字符的员工的姓名
SQL> SELECT ename FROM emp
WHERE length(ename)=5;
【8】显示不带有"R"的员工的姓名
SQL> SELECT ename FROM emp
WHERE ename NOT LIKE '%R%';
【9】显示员工的姓名和受雇日期，将最老的员工排在最前
SQL> SELECT * FROM emp
Order BY hiredate;
【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序
SQL> SELECT ename,TO_CHAR(hiredate,'yyyy')
year,TO_CHAR(hiredate,'mm') month FROM emp
ORDER BY month,year;
【11】找出在2月受聘的员工
SQL> SELECT * FROM emp
WHERE TO_CHAR(hiredate,'mm')=2;
【12】以年月日方式显示所有员工服务年限
SQL> SELECT
ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year, TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month, TRUNC(MOD(sysdate-hiredate,30)) day
FROM emp;
Oracle系列《二》：多表复杂查询和事务处理
多表查询应该注意去除笛卡尔积，一般多个表时会为表起个别名
【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置 SQL> SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d
WHERE e.deptno = d.deptno;
【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联) SQL> SELECT e.ename,e.job,m.ename
FROM emp e,emp m
WHERE e.mgr = m.empno;
【3】对【2】进行扩充，将雇员所在部门名称同时列出
SQL> SELECT e.ename,e.job,m.ename,d.dname
FROM emp e,emp m,dept d
WHERE e.mgr = m.empno AND e.deptno=d.deptno;
【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级(salgrade)，及其领导的姓名所在公司的等级
<1>先确定工资等级表的内容
SQL> SELECT * FROM salgrade;
<2>查询每个雇员的姓名、工资、部门名称和工资在公司的等级
SQL> SELECT e.ename,e.sal,d.dname,s.grade
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
<3>查询其领导姓名及工资所在公司的等级
SQL> SELECT
e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade ms
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal
AND e.mgr = m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;
【5】左连接与右连接的概念，"+"在等号左边表示右连接，反之，左连接 查询雇员的编号、姓名及其领导的编号、姓名
SQL> SELECT e.empno,e.ename,m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr = m.empno(+);
就发现将KING的那条记录也连过来了
SQL1999语法中有如下几种连接(了解)
1、交叉连接CROSS JOIN，产生笛卡尔积
SQL> SELECT * FROM emp CROSS JOIN dept;
2、自然连接NATURAL JOIN,自动进行关联字段的匹配
SQL> SELECT * FROM emp NATURAL JOIN dept;
3、使用USING子句，直接关联操作列
SQL> SELECT * FROM emp JOIN dept USING(deptno)
WHERE deptno=30;
4、使用ON子句，用户自己编写的条件
SQL> SELECT * FORM emp JOIN dept ON(emp.deptno = dept.deptno) WHERE deptno=30;
5、左连接(左外连接、LEFT (OUTER) JOIN)、右连接(右外连接、RIGHT (OUTER) JOIN)
组函数及分组统计
1、COUNT():求出全部记录数
2、MAX()：求出一组中最大值
3、MIN():求出最小值
4、AVG()：求出平均值
5、SUM()：求和
【1】求出每个部门的雇员数量
SQL> SELECT deptno,count(empno)
FROM emp
GROUP BY deptno;
【2】按部门分组，并显示部门的名称，及每个部门的员工数
SQL> SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
【3】要求显示平均工资大于2000的部门编号和平均工资
SQL> SELECT deptno,AVG(sal)
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno;
出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句 上述语句应该改为如下
SQL> SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000
【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000,
输出结果按月工资的合计升序排序
<1>按工作分组，求出非销售人员的月工资总和
SQL> SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;
<2>对分组条件进行限制，然后进行排序,HAVING子句不能使用别名 SQL> SELECT job,SUM(sal) totalSal
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY totalSal;
【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了
SQL> SELECT deptno,MAX(AVG(sal))
FROM emp
GROUP BY deptno;
出错！修改如下
SQL> SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno;
【4】查询出比7654工资要高的全部雇员的信息
<1>首先要查询雇员编号7654的工资
SQL> SELECT sal FROM emp WHERE empno=7654;
<2>以上述条件的结果最后后续查询的依据
SQL> SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654);
子查询在操作中分为以下三类：
1、单列子查询：返回的结果是一列的内容
2、单行子查询：返回多个列，也可能是一条记录
3、多行子查询：返回多个记录
【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员 SQL> SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654)
AND job=(SELECT job FROM emp WHERE empno=7788);
【2】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名
<1>查询部门员工数、部门平均工资
SQL> SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno;
<2>查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询) SQL> SELECT d.dname,ed.c,ed.a,e.ename
FROM dept d,(
SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp
GROUP BY deptno) ed, emp e
WHERE d.deptno=ed.deptno AND e.sal = ed.min;
若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号
IN：指定一个查询范围，例如查询每个部门的最低工资(返回值有多个) SQL> SELECT * FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);
ANY：=ANY(与IN操作一样)、>ANY(比最小大)、<ANY(比最大小) SQL> SELECT * FROM emp
WHERE sal <ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);
ALL: >ALL(比最大要大)、<ALL(比最小的小),SQL语句类似上面
SQL多列子查询示例如下
SQL> SELECT * FROM emp
WHERE (sal,NVL(comm,-1)) IN
(SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20);
数据库更新操作INSERT、UPDATE、DELETE
【1】复制一张表，例如复制EMP表为MYEMP
SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;
【2】将编号为7899的雇员的领导取消
SQL> UPDATE myemp SET mgr=null WHERE empno=7899;
【3】更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下
SQL> UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);
【4】删除掉全部领取奖金的雇员
SQL> DELECT FROM emp WHERE comm is NOT NULL;
事务处理 ACID
A：Atomicity 原子性：事务中的操作或者都完成，或者都取消
C：Consistency 一致性：事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况
I：Isolation 隔离性：当前的事务与其他未完成的事务是隔离的
D：Durability 持久性：在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚
验证事务过程：
<1>创建一张临时表，只包含部门10
SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10;
<2>删除emp10中的7782雇员
SQL> DELETE FROM emp10 WHERE empno=7782;
再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理
COMMIT 和 ROLLBACK 提交事务和回滚事务
SQL查询练习
【1】列出至少一个员工的所有部门
SQL> SELECT d.*,ed.cou FROM dept d,(
SELECT deptno,COUNT(empno) cou FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 1) ed
WHERE d.deptno=ed.deptno;
【2】列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 SQL> SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept d,emp e
WHERE d.deptno = e.deptno(+);
【3】列出所有"CLERK"(办事员)的姓名及其部门名称，部门的人数
<1>关联dept表
SQL> SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno and e.job='CLERK';
<2>使用GROUP BY 完成部门分组人数
SQL> SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,( SELECT deptno,COUNT(empno) cou FROM emp
GROUP BY deptno) ed
WHERE job='CLERK' AND e.deptno=d.deptno AND
ed.deptno=e.deptno;
Oracle系列《三》：表、（约束）索引、序列、视图的使用
一、创建、删除、修改表
建立表：Oracle中主要数据类型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)
如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如
SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1==2)
例如创建表Person如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30),
age NUMBER(3),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M'
);
如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令
SQL> ALTER TABLE person ADD(address VARCHAR2(20));
修改表中的列属性
SQL> ALTER TABLE person MODIFY(address VARCHAR2(30));
在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令
在Oracle中提供RENAME命令对表进行重命名
SQL> RENAME person to personer;
在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)
SQL> TRUNCATE TABLE personer;
（与DROP TABLE区别：前者删除内容，后者删除表）
二、表的约束
约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束
添加约束如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30) NOT NULL,
age NUMBER(3) CHECK(age BETWEEN 0 AND 150),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M' ,
CONSTRAINT person_pid_pk PRIMARY KEY(pid),
CONSTRAINT person_name_uk UNIQUE(name),
CONSTRAINT person_sex_ck CHECK(sex IN('M','F'))
);
以上约束可以采取自动命名和手动命名
现在要再添加一张表，使用主-外键约束
CREATE TABLE book(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
);
这时候如果要删除掉person表的话，就会出现错误，此时可以使用强制性的删除手段
DROP TABLE person CASCADE CONSTRAINT;
但是这种做法一般不用
如果在person 和 book 表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被
book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，
可以使用级联删除的操作
CREATE TABLE book(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
);
添加约束语法如下：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段); 约束类型命名规则：
PRIMARY KEY:主键字段_PK
UNIQUE：字段_UK
CHECK：字段_CK
FOREIGN KEY：父子段_子字段_FK
SQL> ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);
SQL> ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);
SQL> ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);
SQL> ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
ON DELETE CASCADE;
删除约束语法如下：
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;
SQL> ALTER TABLE person DROP CONSTRAINT person_age_CK; SQL> ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;
ROWNUM伪列
SQL> SELECT ROWNUM,empno,ename FROM emp;
ROWNUM采用自动编号的形式出现
加入只想显示前5条记录，那么ROWNUM<=5
SQL> SELECT ROWNUM,empno,ename FROM emp;
WHERE ROWNUM<=5;
但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式 SQL> SELECT * FROM (SELECT ROWNUM m,empno,ename
FROM emp
WHERE ROWNUM<=10) tmp
WHERE tmp.m>5;
不好的查询思路：
select ROWNUM,empno,job,mgr from emp t where rownum<=10 MINUS
select ROWNUM,empno,job,mgr from emp t where rownum<=5
集合操作：在Oracle中提供了3类集合操作:并、交、差
UNION：将多个查询结果组合到一个查询结果之中，没有重复值
UNION ALL：与UNICON不同的是包含重复值
INTERSECT：返回多个查询结果中相同的部分
MINUS：返回两个查询结果的差集
验证UNION和UNION ALL
SQL> CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)
SQL> SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录) SQL> SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)
验证MINUS 和 INTERSECT
SQL> SELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录) SQL> SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)
案例：
主键约束添加删除
1、创建表的同时创建主键约束
一、无命名 create table accounts ( accounts_number number primary key, accounts_balance number );
二、有命名 create table accounts ( accounts_number number primary key, accounts_balance number, constraint yy primary key(accounts_number) );
2、删除表中已有的主键约束
一、无命名 SELECT * FROM USER_CONS_COLUMNS WHERE
TALBE_NAME='accounts';
找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;
二、有命名 ALTER TABLE ACCOUNTS DROP CONTRAINT yy;
3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);
Oracle中视图的操作
1、创建视图
CREATE VIEW 视图名称 AS 子查询
这条子查询是非常复杂的语句
SQL> CREATE VIEW empv20 AS
SELECT empno,ename,job,hiredate
FROM emp
WHERE deptno=20;
2、查询视图
SQL> SELECT * FROM empv20;
3、删除视图
SQL> DROP VIEW empv20;
如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令
CREATE OR REPLACE 视图名称 AS 子查询
视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(
SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) min FROM emp
GROUP BY deptno) ed,emp e
WHERE d.deptno=ed.deptno AND e.sal=e.min;
在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图，以上红色部门
如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作 SQL> UPDATE empv20 SET deptno=30 WHERE empno=7369;
发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的
SQL提供了两个重要的参数
WITH CHECK OPTION：不能更新视图的创建条件
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION;
创建条件不能进行更新了，但其他字段仍然可以更新
SQL> UPDATE empv20 SET ename='wilson' WHERE empno=7369;
所以这时可以使用视图的第2个条件：创建只读视图
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY;
Oracle系列《四》：数据库的设计分析
一、序列的使用
在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成
序列的创建格式
CREATE SEQUENCE sequence
[INCREMENT BY n][START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]
创建一个myseq的序列
SQL> CREATE SEQUENCE myseq;
创建完该序列之后，所有的自动增长应该由用户自己处理
nextVal:取得序列的下一个内容
currVal:取得序列的当前内容
建立一张表验证序列
CREATE TABLE testseq(
next NUMBER,
curr NUMBER,
);
SQL> INSERT INTO testseq(next,curr)
VALUES(myseq.nextval,myseq.currval);
将这条SQL执行5次，然后进行查表操作
SQL> SELECT * FROM testseq;
可以发现,nextval的内容始终在自动增长，而curr使用取出当前操作的序列的结果，该序列增长幅度为1
如果要进行修改，则加上 INCREMENT BY 长度的语句
SQL> DROP SEQUENCE myseq;
SQL> CREATE SEQUENCE myseq INCREMENT BY 2;
发现每次取出的结果都是奇数 1,3,5,7,9，序列是从1开始的，我们可以指定序列的开始位置，例如
SQL> CREATE SEUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 2 CACHE 2 CYCLE;
二、同义词的概念(了解)
SQL> SELECT SYSDATE FROM dual;
dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到 SQL> SELECT * FROM tab WHERE TNAME='DUAL';
此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用"用户名.表名称"
该情况就是同义词的作用
创建同义词：
CREATE SYSNONYM 同义词名称 FOR 用户名.表名称';
例如，将scott.emp 定义 emp 的同义词
SQL> CREATE SYSNONYM emp FOR scott.emp;
删除同义词
SQL> DROP SYSNONYM emp;
同义词这种特性只适合于Oracle数据库
三、用户管理(*)
创建用户: CREATE USER 用户名 IDENTIFIED BY 密码;
SQL> CREATE USER test IDENTIFIED BY test123;
打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权
SQL> GRANT CREATE SESSION TO test;
将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限
Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可
在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户
SQL> GRANT CONNECT,RESOURCE TO test;
管理员对用户密码进行修改：
SQL> ALTER USER test IDENTIFIED BY hello;
在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式 ALTER USER 用户名 PASSWORD EXPIRE;
SQL> ALTER USER test PASSWORD EXPIRE;
这时会提示用户输入旧口令及新的密码
锁住用户和对用户解锁
SQL> ALTER USER test ACCOUNT LOCK;
SQL> ALTER USER test ACCOUNT UNLOCK;
此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可 SQL> GRANT SELECT,DELETE ON scott.emp TO test;
收回权限的命令：
SQL> REVOKE SELECT,DELETE ON scott.emp FROM test;
数据库的备份与恢复
数据库备份：exp
数据库恢复：imp
嵌套表的概念(了解)
嵌套表：在一个表中还包含另外一个子表
首先为嵌套表指定类型，该类型需要单独定义
SQL> CREATE TYPE project_ty AS OBJECT(
priod NUMBER(4),
proname VARCHAR2(30),
prodate DATE
);
/
该类型创建成后，不意味着此类型能够直接使用，要为此类型指定一个名称 SQL> CREATE TYPE project_nt AS TABLE OF project_ty /
这样就可以使用project_nt表示project_ty类型，现在根据此类型创建department表
SQL> CREATE TABLE department(
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(50) NOT NULL,
projects project_nt
)NESTED TABLE projects STORE AS project_nt_tab_temp;
如果要进行数据插入的话
SQL> INSERT INTO department(deptno,dname,projects) VALUES( 1,'tech',
project_nt(
project_ty(1001,'ERP',SYSDATE),
project_ty(1002,'CRM',SYSDATE)
)
);
查询部门表，可以返回多个项目
SQL> SELECT * FROM department;
如果需要查看一个部门的全部项目的话，查询嵌套表
SQL> SELECT * FROM TABLE
(SELECT projects FROM department WHERE deptno=1);
更新编号为1001的项目名称
SQL> UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro
SET VALUES(pro)=project_ty('1001','APR',SYSDATE) WHERE pro.proid=1001;
可变数组的概念：是嵌套表的升级版 ... (有用到再了解)
数据库范式的概念
第一范式：所有的信息都集中在一张表上，例如
CREATE TABLE person(
pid NUMBER(4) PRIMARY KEY,
name VARCHAR2(50),
info VARCHAR(200)
);
第一范式会出现问题，例如创建一张学生选课表
CREATE TABLE selectCourse(
stuno VARCHAR2(50),
stuname VARCHAR2(50),
stuage NUMBER,
cname VARCHAR2(50),
grade NUMBER,
credit NUMBER
);
以上不仅所有的课程信息冗余了，而且还存在以下的问题：
1、没有学生选该门课，那么该门课就消失了
2、课程本身有编号，按照以上设计，课程编号肯定重复
3、要更改课程信息，则要修改许多记录
使用第二范式进行修改
CREATE TABLE student(
stuno VARCHAR2(10) PRIMARY KEY,
stuname VARCHAR2(20),
stuage NUMBER
);
CREATE TABLE course(
cid NUMBER(5) PRIMARY KEY,
cname VARCHAR2(20),
credit NUMBER
);
CREATE TABLE selectCourse(
stuno VARCHAR2(50),
cid NUMBER(5),
grade NUMBER,
设置主-外键关系
);
以上设计解决了以下问题：
1、学生不选课，课程不会消失
2、更新课程的时候直接更新课程表
3、所有关联关系在关系表中体现
这里是完成了多-多关系
使用第三范式：
按照第二范式的设计一张学生表，包括学号、姓名、年龄、所在院校、学院地址、学院电话等 会出现一个学生同时在多个学院同时上课，正常应该是：一个学院包含多个学生，一个学生属于一个学院C
CREATE TABLE collage(
cid NUMBER(40) PRIMARY KEY,
cname VARCHAR2(50),
caddress VARCHAR2(20),
ctel VARCHAR2(20)
);
CREATE TABLE student(
stuno VARCHAR2(50) PRIMARY KEY,
stuname VARCHAR2(50),
stuage NUMBER,
cid NUMBER(4),
建立主-外键关联
);
以上是很明确的1对多的关系
Oracle系列《五》：SQL综合练习
【1】列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
<1>按工作分组，分组条件最低工资大于1500
SQL> SELECT job,MIN(sal) FROM emp
GROUP BY job
HAVING MIN(sal)>1500;
<2>根据求出来的工作来求全部的雇员工作
SQL> SELECT e.job,COUNT(e.empno) FROM emp e
WHERE e.job IN(SELECT job FROM emp
GROUP BY job
HAVING MIN(sal) > 1500)
GROUP BY e.job;
【2】列出在部门'SALES'工作的员工姓名
<1>先查询SALES的部门编号
SQL> SELECT deptno FROM dept WHERE dname='SALES';
<2>SELECT ename FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
【3】列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
<1>求出公司平均薪金
SQL> SELECT AVG(sal) FROM emp;
<2>列出薪金高于平均薪金的所有员工
SQL> SELECT * FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);
<3>查询所在部门信息
SQL> SELECT e.*,d.dname,d.loc FROM emp e,dept d
WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno;
<4>查询上级领导
SQL>
e.empno,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc FROM emp e,dept d,emp m
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr = m.empno(+);
<5>求出工资的工资等级
SQL> SELECT
e.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m,salgrade s
WHERE e.sal(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+)
AND e.sal BETWEEN s.losal AND s.hisal;
【4】列出与"SCOTT"从事相同工作的所有员工及部门名称
<1>找出与SCOTT相同工作的雇员，但不能包括自己
SQL> SELECT empno,ename,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
<2>与部门表关联，查询部门名称
SQL> SELECT e.empno,e.ename,e.job,d.dname
FROM emp e,dept d
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
AND e.deptno=d.deptno;
【5】列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
<1>列出部门30员工的薪金
SQL> SELECT sal FROM emp WHERE deptno=30;
<2>上述条件作为子查询，这里注意上述结果是返回多条记录的，所以要用IN SQL> SELECT ename,sal FROM emp
WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;
【6】列出在每个部门工作的员工数量、平均工资和平均服务期限
<1>每个部门的员工数量：可求出部门名称
SQL> SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno;
GROUP BY d.dname;
<2>求平均工资和服务年限
SQL> SELECT
d.dname,COUNT(e.empno),AVG(e.sal),AVG(MONTH_BETWEENS(sysdate,hiredate)/12) year
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
【7】列出所有部门的详细信息和部门人数
<1>列出所有部门人数
SQL> SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;
<2>将以上查询当做一张临时表
SQL> SELECT d.*,ed.cou
FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed
WHERE d.deptno = ed.deptno;
但是以上没有40部门的信息，则应该使用NVL和左连接操作 SQL> SELECT d.*,NVL(ed.cou,0)
FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed
WHERE d.deptno = ed.deptno(+);
【8】列出各种工作的最低工资及从事该工作的雇员姓名
<1>按照工作分组，使用MIN函数求出最低工资
SQL> SELECT job,MIN(sal) FROM emp
GROUP BY job;
<2>按照工资查询雇员信息
SQL> SELECT * FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp
GROUP BY job);
【9】列出各个部门 MANAGER的最低薪金
SQL> SELECT deptno,MIN(sal)
FROM emp
WHERE job='MANAGER'
GROUP BY deptno;
【10】列出所有员工的年工资，按年薪从低到高排序
SQL> SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDERY BY income;
【11】求出部门名称中，带'S'字符的部门员工，工资合计，部门人数
<1>使用模糊查询，获得部门编号
SQL> SELECT deptno FROM dept WHERE dname LIKE '%S%';
<2>上面作为子查询
SQL> SELECT deptno,SUM(sal),COUNT(empno)
FROM emp
WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%' GROUP BY deptno;
【12】给任职10年以上的人加薪10%
SQL> UPDATE emp SET sal=sal+(sal*0.1)
WHERE MONTH_BETWEENS(sysdate,hiredate)/12>10;
【综合题】有个学生运动会比赛信息的数据库，需要建立如下的表，结构如下 运动员sporter: (运动员编号 sporterid,运动员姓名 name,运动员性别 sex,所属系号 department)
项目item: (项目编号itemid,项目名称itemname,项目比赛地点 location)
成绩grade: (运动员编号 sporterid,项目编号itemid,积分mark)
1、建表要求
<1>定义各个表的主外键约束
<2>运动员姓名和所属系别不能为空
<3>积分要么控制，要么为6,4,2,0,
CREATE TABLE sporter(
sporterid NUMBER(4) PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
sex VARCHAR2(2) NOT NULL,
department VARCHAR2(20) NOT NULL,
CONSTRAINT sporter_sex_CK CHECK(sex IN('M','F'))
);
CREATE TABLE item(
itemid VARCHAR2(4)PRIMARY KEY,
itemname VARCHAR2(20) NOT NULL,
location VARCHAR2(20) NOT NULL
);
CREATE TABLE grade(
sporterid NUMBER(4),
itemid VARCHAR2(20),
mark NUMBER(2),
CONSTRAINT sporter_grade_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
CONSTRAINT item_grade_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
CONSTRAINT grade_mark_CK CHECK(mark IN(6,4,2,0))
);
记录可自己视情况插入，完成以上的查询语句
1、求出目前总积分最高的系名，及其积分
SQL> SELECT s.department,SUM(g.mark) sum
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department
ORDER BY sum DESC;
当然上述查出来的结果是排序的多条记录，使用ROWNUM最为简便
SQL> SELECT * FROM (
SELECT s.department,SUM(g.mark) sum
FROM sporter s,grade g
where s.sporterid = g.sporterid
GROUP BY s.department
ORDER BY sum DESC)
WHERE ROWNUM=1;
2、找出场地为'S1'，进行比赛的各项目名称及其冠军的姓名
<1>首先确定一操场中的全部项目和每个项目的最高成绩
SQL> SELECT i.itemname,s.name,g.mark
FROM item i,grade g,sporter s
WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid = g.sporterid;
<2>根据上述结果求出最高分
SQL> SELECT i.item,s.name,g.mark
FROM item i,grade g,sporter s
WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid AND g.mark=6;
3、找出参加了wilson所参加过的项目的其他同学的姓名
<1>找到wilson参加过的项目编号
SQL> SELECT g.itemid FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name='wislon';
<2>SELECT DISTINCT s.name
FROM sporter s, grade g
WHERE s.sporterid=g.sporterid AND s.name<>'wilson'; AND g.itemid IN(SELECT g.itemid FROM sporter s,grade g WHERE s.sporterid=g.sporterid AND s.name='wilson');
4、wilson使用了违禁药物，成绩记为0
SQL> UPDATE grade SET makr=0
WHERE sporterid=(SELECT sporterid FROM sporter WHERE name='wilson');
5、删除S2项目
SQL> DELETE FROM item WHERE itemid='S2'
其它补充：
Oracle数据库的创建有两种方式：
一种是使用CREATE DATABASE命令；
一种是使用DBCA (Database Configuration Assistant)，它是基于图形界面创建的，比较简单方便。而且，使用DBCA可以详细地对将要创建的数据库进行详细的配置。
数据库启动和关闭的几种方式
数据库的启动（STARTUP）
启动一个数据库需要三个步骤：
1、 创建一个Oracle实例（非安装阶段）
2、 由实例安装数据库（安装阶段）
3、 打开数据库（打开阶段）
在Startup命令中，能够通过不同的选项来控制数据库的不同启动步骤。
1、STARTUP NOMOUNT
NONOUNT选项仅仅创建一个Oracle实例。
2、STARTUP MOUNT
该命令创建实例并且安装数据库，但没有打开数据库。
3、STARTUP
该命令完成创建实例、安装实例和打开数据库的任何三个步骤。
假如采用STARTUP NOMOUNT或是STARTUP MOUNT的数据库打开命令方式，必须采用ALTER DATABASE命令来执行打开数据库的操作。例如，假如您以STARTUP NOMOUNT方式打开数据库，也就是说实例已创建，但是数据库没有安装和打开。这是必须运行下面的两条命令，数据库才能正确启动。 ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
而假如以STARTUP MOUNT方式启动数据库，只需要运行下面一条命令即能够打开数据库：
ALTER DATABASE OPEN.
数据库的关闭（SHUTDOWN）
1、SHUTDOWN NORMAL
这是数据库关闭SHUTDOWN命令的确省选项。
2、SHUTDOWN IMMEDIATE
这是我们常用的一种关闭数据库的方式，想很快地关闭数据库
3、SHUTDOWN TRANSACTIONAL
该选项仅在Oracle 8i后才能够使用。该命令常用来计划关闭数据库
4、SHUTDOWN ABORT
数据库处于一种非正常工作状态，需要立即关闭数据库；在启动数据库实例时碰到问题
Oracle数据库体系结构
Oracle服务器由 Oracle 实例和 Oracle 数据库组成，如图所示：
简易表示为下图所示：
每一个Oracle 数据库都关联着一个 Oracle 实例。在数据库服务器上启动数据库后，Oracle 软件会分配一个称为系统全局区(SGA) 的共享内
存区，还会启动若干个 Oracle 后台进程。这种 SGA 和Oracle 进程的组合就称为一个 Oracle 实例。
实例启动后，Oracle 软件会将实例与特定的数据库关联起来。这个过程称为装载数据库。接下来就可以打开数据库了。在同一台Oracle服务器上可以并发执行多个实例，每一个实 例只访问自己的物理数据库。Oracle 数据库使用内存结构和进程来管理、访问数据库。所有内存结构都存在于构成数据库服务器的计算机的主存中。
卸载Oracle
在卸载Oracle时删不干净，搞的要重装系统，本人在工作中总结出如下方法，希望对大家有所帮助。
1.以Administrators group身份登陆windows系统。
2.通过Oracle installer 卸载所有orcle产品及组件。
3.停掉Oracle Service服务。
4.删除%ORACLE_base%文件和SYSTEM_DRIVE:program files下的oracle文件。
5.删除SYSTEM_DRIVE:Documents and SettingsAll Users「开始」菜单程序中的oracle项。 6.打开注册表： 到HKEY_CLASSES_ROOT下，删除以Oracle，ORA，ORCL开头的键； 到HKEY_LOCAL_MACHINESOFTWARE下，删除ORACLE键和Apache Group键；
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices下，删除以Oracle开头的键；
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices EventlogApplication下，删除以Oracle开头的键；
到HKEY_CURRENT_USER下，删除Oracle键；
到HKEY_CURRENT_USERSOFTWAREORACLE下，删除以Oracle 或 Orcl开头的键（如果有的话）；关闭注册表，重启计算机。
7.打开系统系统环境变量（我的电脑－>右键－>系统属性－>高级－>环境变量），在PATH中删除所有以%ORACLE_HOME%开始的项。
8.重启计算机。
Oracle listener错误解决方法
Oracle listener错误，可以把安装目录下的listener.ora打开察看。如： ..\db1\network\admin\listener.ora。
打开该配置文件后发现如下一段配置信息：
LISTENER =
(DESCRTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (OTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IBMThinkPad)(PORT = 1521))
)
)
)
当看到这一行HOST=IBMThinkPad的时候，问题解决了。原来是由于几天装好Oracle后，又改了Windows的计算机名，而这个 配置文件中记录的监听主机名还是原来的名称，这也就怪不得 TNSListener服务无法启动了，马上修改为现在的计算 机名，再次启动OracleTNSListener服务成功，PL/SQL Developer连接Oracle成功，不能登陆故障排除。