Oracle
所有的sql语句粘到笔记本里再往命令行中粘，否则word中单引号不能用，有时候还会自动为你加标号，很是头疼。
- Oracle数据库语言的分类
DML:(数据操作语言)数据库查询及更新操作
DDL:(数据定义语言)数据对象的创建(表，用户…)
DCL:(数据控制语言)权限的管理操作
登录前确认服务开启了
登录方式：sqlplus /nolog
忘记密码：
1）sqlplus / as sysdba
2）alter user SCOTT identified by "tiger";
sqlplus SCOTT/tiger 此用户下有练习用的表
以下的练习基于该用户如果不小心删除了其中的表，可按以下方法进行恢复
1）在Oracle安装目录下搜索scott.sql
2）以sysdba方式登录，执行脚本。登录SCOTT，删除的表应该就恢复了
- 常用数据库命令
|-SELECT * FROM tab; 查询一个用户下所有的表
|-DESC 表名称; 显示表结构
|-set linesize 300; set pagesize 30;设置命令行数据显示行高及行宽。
- 基础查询
|-简单查询(查询全部数据)
| ②SELECT [DISTINCT] *|列名称 别名，列名称 别名…发 ①FORM 表名称 别名； |
消除重复数据查询
| SELECT DISTINCT 数据列 FROM 表名称； |
进行数据运算返回计算结果(查询列为空则什么也不显示)
| SELECT 数据列*12 别名FROM emp; |
以常量显示每一行数据
| SELECT ‘常量名’,数据列 FROM 表名称； |
将数据列进行连接显示
| SELECT 数据列||数据列 FROM 表名称; SELECT ‘常量名’||数据列||’常量名’||数据列 FROM 表名称；紧凑的显示表查询。 |
|-条件查询
| ③SELECT [DISTINCT] *|列名称 别名，列名称 别名…
|
运算符
| ·关系运算符：>,<,>=,<=,<>,!= ·逻辑运算符：AND,OR,NOT ·范围运算符：BETWEEN AND ·谓词范围：IN,NOT IN ·空判断：IS NULL,IS NOT NULL ·模糊查询：LIKE |
例子：BETWEEN … AND …
| SELECT * FROM emp WHERE ename BETWEEN ‘FORD’ AND ‘SMITH’;根据名字长度查，BETWEEN AND的两个条件必须由小到大，否则没有显示记录。 |
例子：日期数据
| SELECT * FROM emp WHERE HIREDATE BETWEEN ’01-4月-81’ AND ’30-8月-81’; |
注意：IN(可以使用null,表示查询条件为空，但是NOT IN()中不允许使用null，因为NOT IN(NULL):表示查询所有数据，与IN所示的条件查询相悖)
| SELECT * FROM emp WHERE empno NOT IN(NULL);显示未选定行 |
|-模糊查询：LIKE
|-“_”:匹配任意一个字符
|-“%”：匹配0到多个字符
| SELECT * FROM emp WHERE ename LIKE ‘%%’;查询全部数据 |
| ③SELECT [DISTINCT] *|列名称 别名，列名称 别名… ①FORM 表名称 别名； ②WHERE 满足约束的条件 ④ORDER BY [ASC升序|DESC降序] |
示例：根据薪水降序，薪水相同，根据日期升序
| SELECT * FROM emp ORDER BY sal DESC,HIREDATE ASC; |
示例：ORDER BY在SELECT之后执行，可以使用别名进行升序
| SELECT sal*12 income ORDER BY income; 默认升序,别名不区分大小写。 |
- 单行函数：
|-字符串操作（字符串表示只能用单引号，双引号会报错）
| 1 | 字符串 UPPER(列|字符串) | 将字符转换成大写 |
| 2 | 字符串 LOWER(列|字符串) | 将字符串转成小写 |
| 3 | 字符串 INITCAP(列|字符串) | 首字母转大写 |
| 4 | 数字 LENGTH(列|字符串) | 获得字符串长度 |
| 5 | 字符串 SUBSTR(列|字符串，开始索引，[长度]) | 截取指定长度的字符串 |
| 6 | 字符串 REPLACE(列|字符串，旧内容，新内容) | 替换指定字符串 |
示例：动态查询
| SELECT * FROM emp WHERE ename=’&随便取名’; |
| SELECT * FROM emp WHERE ename=UPPER(‘&随便取名’); |
示例：字符串的截取操作的下标是从0开始的,即使下标写0任以1开始查询,以下输出结果相同。
| SELECT ename,SUBSTR(ename,0,3) FROM emp WHERE … |
| SELECT ename,SUBSTR(ename,1,3) FROM emp WHERE … |
示例：截取字符串后三位字符串,此操作只限于Oracle
| SELECT ename,SUBSTR(ename,-3) FROM emp WHERE |
|-数字类型操作(“[]”代表可选项)
| 1 | 数字 ROUND(列|数字[,小数位]) | 实现数据的四舍五入 |
| 2 | 数字 TRUNC(列|数字[,小数位]) | 实现数字的截取操作 |
| 3 | 数字 MOD(列|数字，列|数字) | 求模（计算余数） |
示例：如果列后的保留位数为负数，进行整数位的四舍五入
| SELECT ROUND(23.56897,-2) FROM dual; 输出为0 |
|-时间函数
|-验证伪列
| SELECT SYSTIMESTAMP FROM dual; 时间戳：包含时分秒 |
|-时间操作
| ·日期+数字=日期（若干天之后的日期） ·日期-数字=日期（若干天之前的日期） ·日期-日期=数字（两个日期之间所差的天数） |
|-时间函数
| 1 | 日期 ADD_MONTHS(日期|列，月数) | 指定日期增加月数后返回 |
| 2 | 数字 MONTHS_BETWEEN(日期|列，日期|列) | 两个日期之间的月份差 |
| 3 | 日期 LAST_DAY(列|日期) | 日期所在月最后一天的日期 |
| 4 | 日期 NEXT_DAY(列|日期，星期X的数字) | 返回下一个指定星期几的日期 |
示例：查询所有雇佣月份为雇佣所在月月底前三天的全部信息
| SELECT * FROM emp e WHERE e.HIREDATE=LAST_DAY(e.HIREDATE)-2; |
示例：查询员工编号，员工姓名，入职日期，入职年、月、日信息
| 获得年：MONTHS_BETWEEN(SYSDATE ,hiredate)/12 |
| 获得月：MOD(MONTHS_BETWEEN(SYSDATE ,hiredate),12) |
| 获得日 SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate)) |
| 完整sql: SELECT e.empno,e.ename,e.hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE ,hiredate)/12) year, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE ,hiredate),12) ) month, TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day FROM emp e; |
|-转换函数(名称不太准确)
| 1 | 字符串 TO_CHAR(列|日期|数字，转换格式) | 指定数据转为字符串 |
| 2 | 日期 TO_DATE(列|字符串，转换格式) | 字符创转换为日期指定格式 |
| 3 | 数字 TO_NUMBER(列|字符串) | 指定数据转换为数字 |
示例：当前时间转为指定格式
| SELECT TO_CHAR(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) FROM dual; |
示例：TO_DATE
| SELECT TO_DATE(‘1966-3-8’,’yyyy-mm-dd’) FROM dual; |
|-通用函数：Oracle的特色函数
| 1 | 数字 NVL(列|NULL,默认值) | 传入值为NULL则为采用默认值 |
| 2 | 数据类型 DECODE(列|字符串|数字，比较内容1，显示内容1，比较内容2，显示内容2…) | 传入字符串与比较内容匹配，匹配成功则显示显示内容。 |
示例：显示所有员工的总工资(计算中遇到NULL值，则返回结果为NULL，查询不再显示)
| SELECT e.empno,e.ename,sal*12+NVL(comm,0) total FROM emp e; |
示例：如果报一个找不到FROM错误，可能是属性值少“，”
| SELECT ename,job,DECODE(job,’CLERK’,’办事员’,’SALESMAN’,’销售人员’) FROM emp; |
- 多表查询：多表查询性能差，尽量少使用。
示例：查询emp表和dept表的记录条数,记过为56条，
Emp有14条数据，dept有4条数据，也就是说，每查询到一条emp记录，dept表查询一遍，这种现象叫做笛卡尔积现象。
| SELECT COUNT(*) FROM emp,dept; |
示例：消除笛卡尔积现象
| SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; |
示例：查询每个雇员的编号，姓名，职位，工资，工资等级，部门名称。
|-使用到的表
·emp
·salgrade
·dept
|-关联字段
Emp.deptno=dept.deptno
Emp.sal BETWEEN salgrade.losal AND salgrade.hisal
| SELECT e.empno,e.ename,e.job,e.sal,s.grade,d.dname FROM emp e,dept d,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno; |
|-表的连接查询
|-内连接：等值连接，如上满足条件数据显示
|-外连接：左外连接(LEFT OUTER JOIN)，右外连接(RITHT OUTER JOIN)，全外连接(FULL OUTER JOIN)。控制表中不满足条件的数据显示。
|-左外连接：属性值=属性值（+）
|-右外连接：属性值（+）=属性值
|-交叉连接：CROSS JOIN,主要作用简单查询会产生笛卡尔积现象
|-自然连接：NATURAL JOIN,自动使用关联关键字消除笛卡尔积现象，一般使用外键属性。
|-USING子句：如果一张表有多个关联字段，可以使用，USING子句来指定关联属性
| SELECT * FROM emp JOIN dept USING(deptno); |
|-ON子句：没有关联字段可以使用ON子句设置条件
| SELECT * FROM emp JOIN salgrade ON(emp.sal BETWEEN salgrade.losal AND salgrade.hisal) |
|-集合查询：
|- UNION
示例：UNION:将两个查询结果一起显示出来,去除重复数据
| SELECT ename,deptno,job FROM emp WHERE deptno=10 UNION SELECT ename,deptno,job FROM emp; |
示例：UNION ALL：将所有查询集合结果显示，有重复数据，没有笛卡尔积，只是将所有的数据进行显示了。
| SELECT ename,deptno,job FROM emp WHERE deptno=10 UNION ALL SELECT ename,deptno,job FROM emp; |
|-INTERSECT：显示交集部分数据(第二个集合如果包含第一个集合中的所有数据，则返回第一个集合除此以外什么也不返回)
| SELECT ename,deptno,job FROM emp WHERE deptno=10 INTERSECT SELECT ename,deptno,job FROM emp; |
|-MINUS:利用第一个集合减去第二个集合返回一个新的集合(第二个集合中包含第一个集合中的数据则消除相同的部分，没有相同则全部返回第一个集合)
| SELECT ename,deptno,job FROM emp MINUS SELECT ename,deptno,job FROM emp WHERE deptno=10; |
- 分组统计查询
|-常用的函数：MAX(),MIN(),AVG(),COUNT(),SUM()
示例：函数的使用，统计雇员的平均服务年限
| SELECT AVG(TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) FROM emp; |
|-面试题：请解释COUNT(*),COUNT(字段),COUNT(DISTINCT 字段)
·COUNT(*):表中的记录数，最准确的
·COUNT(字段):该字段不为NULL，的记录数
·COUNT(DISTINCT 字段)：字段不为NULL,取消重复数据后的记录数。
|-分组查询
| ④SELECT [DISTINCT] *|列名称 别名，列名称 别名… ①FORM 表名称 别名； ②WHERE 满足约束的条件 ③GROUT BY 分组字段… ⑤ORDER BY [ASC升序|DESC降序] |
|-注意：1.分组字段必定有重复数据，所以以此来进行分组
2.SELECT查询字段只能是分组字段和统计函数，因为进行分组后，可以理解为分成了几个组，查出的一条记录，对应非分组字段的多个值无法显示。
3.分组查询可以使用统计函数的嵌套，但不能再有其他查询的字段
示例：查询出每个部门编号，部门人数，最高最低工资
| SELECT deptno,COUNT(empno),MAX(sal),MIN(sal) FROM emp GROUP BY deptno; |
问题：WHERE条件查询不允许使用统计函数，所以使用HAVING子句来实现分组后数据的筛选
| ⑤SELECT [DISTINCT] *|列名称 别名，列名称 别名… ①FORM 表名称 别名； ②WHERE 满足约束的条件 ③GROUT BY 分组字段… ④HAVING 统计函数判断条件 ⑥ORDER BY [ASC升序|DESC降序] |
示例：查询平均薪水大于2000的职位
| SELECT e.job,AVG(e.sal) FROM emp e GROUP BY e.job HAVING AVG(e.sal)>2000; |
注意：WHERE属于所查询表全部数据的筛选，在GROUP BY之前执行，而HAVING在GROUP BY之后执行，属于分组的再筛选操作。
示例：显示非销售人员工作名称以及从事同一工作雇员的工资总和并且总和要大于5000，输出结果按月工资合计升序排列
| SELECT e.job,SUM(e.sal) sum FROM emp e WHERE job<>‘SALESMAN’有些代码粘到命令行会有空格，单引号的问题 GROUP BY e.job HAVING SUM(e.sal)>5000 ORDER BY sum DESC; |
示例：查询出所有领取佣金和不领取佣金的雇员人数和平均工资
| SELECT COUNT(empno),AVG(sal) FROM emp WHERE COMM IS NULL UNION SELECT COUNT(empno),AVG(sal) FROM emp WHERE COMM IS NOT NULL; |
- 子查询：
|-子查询返回结果判断应用于那种子句
·WHERE子句：子查询返回单行单列，单行多列，多行单列
·HAVING子句：子查询返回单行单列同时要使用统计函数
·FROM子句：子查询返回多行多列（表结构）
·SELECT子句：单行单列使用，性能太低，一般不用
示例：查询大于平均工资的雇员信息
| SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp); |
示例：查询职位薪金与SCOTT相同的员工信息
| SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename=’SCOTT’) AND ename<>’SCOTT’; |
|-子查询使用操作符
1.示例：IN操作：指定字段与子查询返回结果相同
查询职位为MANAGER的全部信息
| SELECT * FROM emp WHERE sal IN(SELECT sal FROM emp WHERE job=’MANAGER’); |
2.ANY操作一共分为三种形式=ANY,>ANY,<ANY。
·=ANY:其功能与IN相同
·>ANY:比子查询返回最小值大的数据
·<ANY:比子查询返回最大值小的数据
3.ALL操作，一共分两种形式。
·>ALL:比子查询返回的最大值大
·<ALL:比子查询返回的最小值小
示例：查询职位平均工资大于公司平均工资的职位，人数，平均工资
| SELECT job,COUNT(empno),AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)>(SELECT AVG(sal) FROM emp); |
|-在SELECT中使用子句,了解，效率太低。
示例：查询每个雇员的编号，姓名，职位，部门名称
| SELECT e.empno,e.ename,e.job, (SELECT dname FROM dept WHERE deptno=e.deptno) FROM emp e; |
示例：查询每个部门名称，位置，人数
|-需要使用的表：dept(子查询实现),emp
|-表之间的关联关系：dept.deptno=emp.deptno
| SELECT COUNT(empno) 获得部门人数 FROM emp GROUP BY deptno; |
| SELECT dname,loc 查询部门名称地点 FROM dept; |
| SELECT d.dname,d.loc,count 使用子查询实现，较好 FROM dept d,( SELECT deptno no,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE temp.no(+)=d.deptno; |
| SELECT d.dname,d.loc,COUNT(empno) FROM emp e,dept d如果部门名称和地区存在重复数据！！！ WHERE e.deptno(+)=d.deptno GROUP BY d.dname,d.loc; |
| 性能比较：假设dept有400条数据，emp表有1400 多表查询：1400*400=56000 子查询：子查询1400条+400*(1400/4分组了)=16000 |
示例：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数
SELECT sal FROM emp WHERE deptno=30; |
SELECT e.ename,e.sal,d.dname,temp.count FROM emp e,dept d,(SELECT deptno no,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.deptno=d.deptno AND temp.no=e.deptno AND e.sal>ALL(SELECT sal FROM emp WHERE deptno=30); |
示例：列出与SCOTT从事相同工作的所有员工的员工信息、部门名称、部门人数、领导名字。
|-使用到的表：emp、dept、memp
|-关联信息：e.deptno=d.deptno e.mgr=memp.empno
SELECT job FROM emp WHERE ename=’SCOTT’; |
| SELECT e.*,d.dname,temp.count,m.ename FROM emp e,dept d,(SELECT deptno no,COUNT(empno) count FROM emp GROUP BY deptno)temp,emp m WHERE e.job=( SELECT job FROM emp WHERE ename='SCOTT') AND e.ename<>’SCOTT’ AND e.deptno=d.deptno AND temp.no=e.deptno AND m.empno=e.mgr; |
示例：列出薪金比SMITH或ALLEN多的所有员工的编号、姓名、部门名称、领导姓名、部门最高，低工资
|-emp,dept,emp,子查询
|-e.deptno=d.deptno e.mgr=m.empno temp.deptno=
e.deptno
| 1.查询出SMITH和ALLEN的工资 SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'); |
| SELECT e.empno,e.ename,e.sal,d.dname, m.ename,temp.min,temp.max要考虑条件中为空的情况 FROM emp e,dept d,emp m,(SELECT deptno no,MAX(sal) max,MIN(sal) min FROM emp GROUP BY deptno)temp WHERE e.sal>ANY(SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN')) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND temp.no=e.deptno; |
示例：列出受雇日期早于其直接上级的员工的编号，姓名，部门名称，部门位置
|-emp,dept
|-e.deptno=d.deptno
| SELECT e.empno,e.ename,d.dname,d.loc自己写的 FROM (SELECT mgr m,hiredate da,empno empsno FROM emp) em, (SELECT hiredate mdate,empno no FROM emp) m,emp e ,dept d WHERE em.m=m.no AND em.da<mdate AND e.empno=em.empsno AND e.deptno=d.deptno; |
| SELECT e.empno,e.ename,d.dname,d.loc FROM emp e,emp m,dept d呵呵别人写的 WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate AND d.deptno=e.deptno; |
| 表之间存在引用关系，比如此表中emp表的mgr,emp表empno，可以在FROM子句中设置两个emp表，通过关系来实现功能，自己写的命名不规范。。。 |
示例：列出所有CLERK(办事员)的姓名、及其部门名称、部门的人数、工资等级。
|-emp,dept,salgrade
|-e.deptno=e.deptno e.sal BETWEEN s.losal AND s.hisal
| SELECT e.ename,d.dname,temp.count,s.grade FROM emp e,dept d, (SELECT deptno no,COUNT(empno) count FROM emp GROUP BY deptno)temp,salgrade s WHERE e.job= 'CLERK' AND e.deptno=d.deptno AND temp.no=e.deptno AND e.sal BETWEEN s.losal AND s.hisal; |
- 数据更新与事务处理
示例：将emp表复制为myemp表
| CREATE TABLE myemp AS SELECT * FROM emp; |
示例：在myemp中插入数据
| INSERT INTO myemp(empno,ename,job,mgr, hiredate,sal,comm,deptno) VALUES(8888, '张三', '清洁工',7369,SYSDATE,3000.0,100.0,10); |
示例：将张三的工资修改为SCOTT的工资
| UPDATE myemp SET sal= (SELECT sal FROM myemp WHERE ename= 'SCOTT') WHERE ename= '张三'; |
示例：将低于公司平均工资的员工工资上涨20%
| UPDATE myemp SET sal=sal*1.2 WHERE sal<(SELECT AVG(sal) FROM emp); |
总结：只要是进行更新操作就必须带WHERE
示例：删除工资高于公司平均工资的人
| DELETE FROM myemp WHERE sal>(SELECT AVG(sal) FROM myemp); |
|-删除分两种
|-物理删除：直接执行DELETE FROM…,彻底从表中删除数据
|-逻辑删除：增加一个逻辑字段，如flag，如果真的执行了删除相当于修改了flag，在查询的时候加一个限定条件使flag改变的数据不显示。
|-删除数据是一个很危险的操作，执行时要小心
|-事务的处理：体现一件事不可分割性，比如打钱，A->B,账户A减少钱，B中加钱，B中没加钱，则A中钱回来。要么提交要么回退的操作。
|-认识死锁：不同的回话进行以下两部操作时会出现死锁，必须等待第一方commit之后才能够执行。
| UPDATE myemp SET sal=5000 WHERE empno=8888; |
| UPDATE myemp SET comm=4000 WHERE empno=8888; |
|-所有未提交的更新事务将会被自动提交，同一回话。
9.数据伪列
|-数据伪列ROWNUM：一个自动的编号机制，动态生成的，不属于真正的数据表，ROWNUM可以进行两类操作
|-查询第一行数据 只能查询第一行数据，可以理解为动态生成的序列所以无法从第二行查起。
| SELECT * FROM emp WHERE ROWNUM=1; |
|-通过伪列来实现查询符合条件的前几条数据
| SELECT * FROM(SELECT empno,ename,sal,job, ROWNUM m FROM emp WHERE ROWNUM<11)temp WHERE temp.m<6; |
示例：取出第十一条到第十五条数据，伪列的别名不能是row，否则报错。
| SELECT * FROM(SELECT empno,ename,sal,job, ROWNUM m FROM emp WHERE ROWNUM<16)temp WHERE temp.m>10; |
|-ROWID：唯一的作用就是描述一行记录的唯一编号
AAAR3s AAE AAAACX AAA的组成
数据的对象编号：AAAR3s
数据的保存文件编号：AAE
数据的保存块号：AAAACX
数据保存行号：AAA
- 表的创建和管理
|-表的数据类型
| CREATE TABLE member( mid NUMBER， mname VARCHAR2(50) DEFAULT ‘无名氏’, age NUMBER(3), birthday DATE, note CLOB ); |
|-数据字典的概念：Oracle为了能够记录下所有的对象信息，提供了数据字典这个“工具“
·用户级别：user_*开头，指的是用户可以操作的数据字典
·管理员级别：dba_*开头，指的是数据库管理员使用的数据字典
·全部级别：all_*开头，表示不管用户还是管理员都能够使用。
示例：查询当前用户下所有数据表
| SELECT * FROM user_tables; |
表的操作：表的重命名
| RENAME member TO person; |
表的截断：如果想清除一张表中的全部数据，第一想法就是使用delete，但是严格来讲表所占用的资源（索引、约束）并不会立刻被释放，此时可以使用表截断来删除表中的内容，释放空间。
| TRUNCATE TABLE person; |
表的操作：复制表(不属于标准SQL)
| CREATE TABLE 表名 AS 子查询 |
示例：复制表结构，但是不复制内容
| CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2; |
表的操作：表的删除
| DROP TABLE 表名； |
表执行drop操作后可以在user_recyclebin中查看
| COL ORIGINAL_NAME FOR A30;设置字段宽 COL DROPTIME FOR A30; SELECT ORIGINAL_NAME,ORIGINAL_NAME,DROPTIME FROM user_recyclebin; |
表的操作：闪回,回收站中删除表恢复
| FLASHBACK TABLE emp2 TO BEFORE DROP; |
表的操作：删除回收站
| PURGE TABLE emp2; 清除指定表 |
| PURGE RECYCLEBIN; 清除回收站所有表 |
表的操作：为表增加字段，设置默认值
| ALTER TABLE emp2 ADD(sex VARCHAR2(5) DEFAULT '男'); |
表的操作：修改表的数据列的语法
| ALTER TABLE member MODIFY(mname VARCHAR2(20) DEFAULT '无名氏'); |
表的操作：删除表中数据列
| ALTER TABLE member DROP COLUMN mname; |
10.约束的创建和管理(主键、外键、检查、唯一、非空)
约束操作：NK非空约束，唯一约束(NULL不影响唯一约束)
| CREATE TABLE member( mid VARCHAR2(20) NOT NULL, mname VARCHAR2(30) UNIQUE ); |
示例：通过数据字典查看约束
| COL owner FOR A10; COL constraint_name FOR A10; COL table_name FOR A10; SELECT owner,constraint_name,table_name FROM user_constraints; |
| COL owner FOR A10; 找到约束对应的列 COL constraint_name FOR A20; COL table_name FOR A20; COL column_name FOR A20; SELECT * FROM user_cons_columns; |
约束操作：添加唯一约束(UK常用，定义表时定义约束，异常好处理)
| DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20) NOT NULL, email VARCHAR2(30), CONSTRAINT uk_email UNIQUE(email) ); |
约束操作：主键约束(PK)
| DROP TABLE member; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20) NOT NULL, email VARCHAR2(30), CONSTRAINT pk_mid PRIMARY KEY(mid) ); |
示例：联合主键(非空+唯一：当两个属性都满足条件时完成操作)
| DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20), CONSTRAINT pk_mid_mname PRIMARY KEY(mid,mname) ); |
约束操作：检查约束(CK)
| DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20), age NUMBER, CONSTRAINT ck_age CHECK(age BETWEEN 20 AND 250) ); |
约束操作：外键（FK）字表中有一个字段是关于另一个父表的主键引用,模拟一个借书系统
键的约束有一个模式：CONSTRAINT 约束名 键的名称（引用的表的字段）
| DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20), CONSTRAINT pk_pid PRIMARY KEY(mid) ); CREATE TABLE book( bid NUMBER, bname VARCHAR2(20), mid NUMBER, CONSTRAINT pk_bid PRIMARY KEY(bid), CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ); |
知识点：具有引用的两张表，进行删除的时候必须，先删除字表再删除父表，这是正常的逻辑关系，鼓励如此操作，也有强制删除的语法。(表的删除操作无法回滚)
| DROP TABLE book PURGE; DROP TABLE member PURGE; |
示例：强制删除(英文部分：级联约束删除)
| DROP TABLE book CASCADE CONSTRAINT; |
约束操作：主键、外键级联删除，即删除主键表中数据的同时删除外键中引用该主键的数据（无法进行回滚）
| DROP TABLE book PURGE; DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20), CONSTRAINT pk_mid PRIMARY KEY(mid) ); CREATE TABLE book( bid NUMBER, bname VARCHAR2(20), mid NUMBER, CONSTRAINT pk_bid PRIMARY KEY(bid), CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE ); |
约束操作：主键删除的同时外键表引用的内容被清空，不显示
| DROP TABLE book PURGE; DROP TABLE member PURGE; CREATE TABLE member( mid NUMBER, mname VARCHAR2(20), CONSTRAINT pk_mid PRIMARY KEY(mid) ); CREATE TABLE book( bid NUMBER, bname VARCHAR2(20), mid NUMBER, CONSTRAINT pk_bid PRIMARY KEY(bid), CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ); 亲测子表中数据无法被清空，不知道怎么回事 |
约束操作：创建表后在再添加约束
| ALTER TABLE member ADD CONSTRAINT pk_pid PRIMARY KEY(mid); |
| 为表添加外键 ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键字段) REFERENCES 表名(主键字段)； |
|-表的约束分为五种：主键、外键、检查、唯一、非空。以上的语法不适用与定义非空约束。
示例：非空约束的语法
| ALTER TABLE member MODIFY(mname varchar2(20) NOT NULL); |
表的操作：为表删除约束
| ALTER TABLE member DROP CONSTRAINT pk_pid; |
- DML&DDL范例(产品、顾客、购物清单)
| CREATE TABLE customer( customerid VARCHAR2(3), cname VARCHAR2(20) NOT NULL, location VARCHAR2(50), CONSTRAINT pk_customerid PRIMARY KEY(customerid) ); CREATE TABLE product( productid VARCHAR2(3), productname VARCHAR2(20) NOT NULL, unitprice NUMBER, category VARCHAR2(20), provider VARCHAR2(20), CONSTRAINT pk_productid PRIMARY KEY(productid), CONSTRAINT ck_unitprice CHECK(unitprice>0) ); CREATE TABLE purcase( customerid VARCHAR2(3), productid VARCHAR2(3), quantity NUMBER, CONSTRAINT ck_quantity CHECK(quantity BETWEEN 0 AND 20), CONSTRAINT fk_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) ON DELETE CASCADE, CONSTRAINT fk_productid FOREIGN KEY(productid) REFERENCES product(productid) ON DELETE CASCADE ); |
|-往product表中添加数据
| INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M03','高露洁',6.50,'牙膏','高露洁'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M04','洁诺',5.00,'牙膏','联合利华'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M05','舒肤佳',3.00,'香皂','宝洁'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M06','夏士莲',5.00,'香皂','联合利华'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M07','雕牌',2.50,'洗衣粉','纳爱斯'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M08','中华',3.50,'牙膏','联合利华'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M09','汰渍',3.00,'洗衣粉','宝洁'); INSERT INTO product(productid,productname,unitprice,category,provider) VALUES('M02','碧浪',4.00,'洗衣粉','宝洁'); |
|-往purcase表中添加数据
| INSERT INTO purcase(customerid,productid,quantity) VALUES('C01','M01',3); INSERT INTO purcase(customerid,productid,quantity) VALUES('C01','M05',2); INSERT INTO purcase(customerid,productid,quantity) VALUES('C01','M08',2); INSERT INTO purcase(customerid,productid,quantity) VALUES('C02','M02',5); INSERT INTO purcase(customerid,productid,quantity) VALUES('C02','M06',4); INSERT INTO purcase(customerid,productid,quantity) VALUES('C03','M01',1); INSERT INTO purcase(customerid,productid,quantity) VALUES('C03','M05',1); INSERT INTO purcase(customerid,productid,quantity) VALUES('C03','M06',3); INSERT INTO purcase(customerid,productid,quantity) VALUES('C03','M08',1); INSERT INTO purcase(customerid,productid,quantity) VALUES('C04','M03',7); INSERT INTO purcase(customerid,productid,quantity) VALUES('C04','M04',3); INSERT INTO purcase(customerid,productid,quantity) VALUES('C05','M06',2); |
示例：查询出所有供应商为“宝洁”的所有顾客
|-使用到的表：product、customer、purcase
SELECT productid FROM product WHERE provider='宝洁'; |
SELECT customerid FROM PURCASE WHERE productid IN( SELECT productid FROM product WHERE provider='宝洁'); |
SELECT DISTINCT * FROM customer WHERE customerid IN( SELECT customerid FROM PURCASE WHERE productid IN( SELECT productid FROM product WHERE provider='宝洁') ); |
示例：求购买了包含“Demis”所购买的所有商品的顾客（姓名）
|-EXISTS:IN只要包含一个就符合要求，不符合此题的要求，而EXISTS表示子查询有返回结果就显示数据
SELECT customerid FROM customer WHERE cname= 'Demis'; |
SELECT productid FROM purcase WHERE customerid=( SELECT customerid FROM customer WHERE cname= 'Demis' ); |
SELECT productid FROM purcase WHERE customerid=( SELECT customerid FROM customer WHERE cname= 'Demis' ) MINUS SELECT productid FROM purcase WHERE productid<>( SELECT customerid FROM customer WHERE cname= 'Demis') GROUP BY productid; |
SELECT cname FROM customer cu WHERE NOT EXISTS( SELECT productid FROM purcase WHERE customerid=( SELECT customerid FROM customer WHERE cname= 'Demis' ) MINUS SELECT productid FROM purcase WHERE customerid=cu.customerid ) AND cname<>'Demis'; |
SELECT cname FROM customer cu WHERE EXISTS( SELECT productid FROM purcase WHERE customerid=( SELECT customerid FROM customer WHERE cname= 'Demis' ) INTERSECT SELECT productid FROM purcase WHERE customerid=cu.customerid ) AND cname<>'Demis'; |
示例：求牙膏卖出数量最高的供应商
SELECT productid FROM product WHERE category= '牙膏'; |
SELECT productid,SUM(quantity) FROM purcase WHERE productid IN(SELECT productid FROM product WHERE category= '牙膏') GROUP BY productid; |
SELECT provider FROM product p,( SELECT MAX(SUM(quantity)) sum FROM purcase WHERE productid IN(SELECT productid FROM product WHERE category= '牙膏') GROUP BY productid )temp WHERE p.productid=temp.pid AND temp.sum>ALL(temp.sum); 无法实现问题如下：查询到了销售数量和产品id值如图1，但是无法的到销售数量最大的id值。 用HAVING啊，二货 |
| SELECT provider FROM product p, ( SELECT productid pid,SUM(quantity) sum FROM purcase WHERE productid IN(SELECT productid FROM product WHERE category='牙膏') GROUP BY productid)temp WHERE temp.pid=p.productid AND temp.sum=(SELECT MAX(SUM(quantity)) sum FROM purcase WHERE productid IN(SELECT productid FROM product WHERE category='牙膏') GROUP BY productid ); |
| 另一种思路； |-使用到的表：product、purcase |-条件：pr.productid=pu.productid
SELECT pr.provider,SUM(pu.quantity) FROM product pr,purcase pu WHERE pr.productid=pu.productid AND pr.category= '牙膏' GROUP BY pr.provider HAVING SUM(pu.quantity)=( SELECT MAX(SUM(pu.quantity)) FROM product pr,purcase pu WHERE pr.productid=pu.productid AND pr.category= '牙膏' GROUP BY pr.provider ); |
示例：删除从未购买的商品.
| DELETE FROM product WHERE productid NOT IN(SELECT productid FROM purcase); |
- 常用的数据库对象
|-序列操作：
| 语法：CREATE SEQUENCE 序列名 [INCREMENT BY 步长][START WITH 开始值] [MAXVALUE 最大值|NOMAXVALUE] [MINVALUE 最小值|NOMINVALUE] [CYCLE|NOCYCLE]:是否循环 [CACHE 缓存数据|NOCACHE] |
|-查看序列：
| SELECT * FROM user_sequences; |
·是否循环(c)：是否循环
·缓存(cache_size):20存入内存的序列值个数。
·最后一次内容(last_number)：52
|-获得序列的值:nextval(获得下一个值，增加指定的值)、currval(获得当前值，新创建的序列，想获得执行currval，必须先执行过nextval之后才可以通过currval来获得当前值)
| CREATE SEQUENCE myseq; |
示例：设置一个循环序列1、3、5、7、9，设置一个循环序列要注意缓存中的数据不能超过循环的数字个数
| CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 1 MINVALUE 1 MAXVALUE 9 CYCLE CACHE 3; |
| ALTER SYSTEM SET deferred_segment_creation=false; = START WITH 1; |
|-同义词：dual表，此表是一张虚拟表，主要功能是进行数据伪列的查询，但是这张表其实是sys用户定义的，其他用户访问此表使用的dual其实是sys.dual的别名，这个就是同义词的应用(oracle自己的，了解就可以了)
| CREATE [PUBLIC] SYNONYM 同义词的名字 FOR 用户名.表名称 |
| 1.连接别的数据库：CONN sys/orcl AS SYSDBA; |
| 2.CREATE PUBLIC SYNONYM myemp FOR scott.emp; |
| 3.SELECT * FROM myemp; |
|-创建视图
| 语法：CREATE [OR REPLACE] VIEW 视图名称 AS 子查询 |
CREATE VIEW myview AS SELECT e.empno,e.ename,d.loc,temp.count,ROUND(temp.avg) ta FROM myemp e,dept d,(SELECT deptno no,COUNT(empno) count,avg(sal) avg FROM myemp GROUP BY deptno)temp WHERE e.deptno=temp.no AND d.deptno=e.deptno; |
示例：修改视图数据,此时无法更新数据，但是如果查询没有那么复杂，只涉及一张表的查询就可以进行值的更改。
| UPDATE myview SET ename= '测试' WHERE empno=7369; |
| 创建单表视图 CREATE OR REPLACE VIEW myview AS SELECT * FROM myemp WHERE deptno=20 [WITH CHECK OPTION]使视图WHERE子句条件不可更改; [WITH READ ONLY]时视图只读，不可更改 |
| 更新视图返回的数据,此时可以实现更新，默认情况下无法更新视图的WHERE子句的条件（有待研究） UPDATE myview SET deptno=40 WHERE empno=7369; |
|-索引的使用
CONN sys/orcl AS SYSDBA; SET AUTOTRACE ON; SELECT * FROM scott.emp WHERE sal>2000; |
|-解释：此时发现使用的是一个全表的查询方式，即字段和条件一一匹配那现在我们想象一下这种情况，现在有5W条记录，进行过了排序，查询只到1500条就没有我们想要的数据了，但是ORDER BY无法实现该功能，因为ORDER BY是最后执行的无法干预到查询结果，此时引出了索引的概念，使用树来进行查询就能满足功能（呵呵呵）
|-创建索引,加入索引之后使用索引针对sal字段查询
| CREATE INDEX emp_sal_ind ON scott.myemp(sal); |
|-关于索引的思考：索引可以提高检索的效率，但是如果数据量很大的情况下，拥有索引的字段被更改，那么索引（B*tree）就得进行自动的更新，太多的更新反而成为了拖累，矛盾在于：像提高索引就得使用索引，而数据量大二叉树的更新有太消耗内存，一般的解决策略是：时间换空间，空间换时间，鱼与熊掌不可兼得，减缓我们的更新效率，使用两张表，一张表用来保存数据，一张表用来保存更新的日志信息（牺牲实时性）。
|-创建用户：
| CREATE USER dog IDENTIFIED BY wangwang; |
|-授权用户登录权限
| CONN sys/orcl AS SYSDBA; GRANT CREATE SESSION TO dog; |
|-授权创建表的权限
| GRANT CREATE TABLE TO dog； |
|-为用户分配角色（一组权限的集合）
| GRANT CONNECT,RESOURCE TO dog; |
|-用户管理：
| 修改用户密码 ALTER USER dog IDENTIFIED BY miaomiao; 让密码失效：即登陆之后需要立即修改密码 ALTER USER dog PASSWORD EXPIRE; 锁定用户(锁定的用户无法链接)： ALTER USER dog ACCOUNT LOCK; 解锁用户 ALTER USER dog ACCOUNT UNLOCK; |
|-将scott用户的操作对象权限授予其他用户
| GRANT SELECT,INSERT,RESOURCE ON scott.emp TO dog; |
|-用户出现问题，回收权限
| REVOKE CONNECT,RESOURCE FROM dog; |
| REVOKE CREATE TABLE,CREATE SESSION FROM dog; |
|-删除用户
| DROP USER 用户名; |
12. 数据库备份
|-数据库导入和导出：针对某一用户进行的操作(基本上不用此备份，而是进行一些数据库转移操作)
|-用户名密码是区分大小写的
|-数据导入没什么可更改的，一路ENTER就可
|-数据导出只需要更改一项
|-数据库冷备份和热备份(太麻烦，dba的工作，此处显示数据库冷备份)
|-定义：数据库服务停止，专门去备份一下内容
|-步骤：找到文件->复制文件
|-数据库的设计范式
|-第一范式：数据表中的每个字段都不可再分
示例：联系方式，不可作为字段，因为联系方式有很多内容如:QQ,EMAIL…
|-第二范式：数据表之中不存在非关键字段对任意一候选关键字字段的部分依赖：理解为多对多关联映射就行。
|-第三范式：数据表之中不存在非关键字段对任意一候选关键字段的传递函数依赖：理解为一对多关联关系。
呵呵呵，了解就行
|-综合实战
|-创建表空间
| 语法： create tablespace 表空间名称 datafile '表空间的保存路径.dbf' size 10M(建立表空间快，自动扩展)； |
|-数据库误删文件修复方法
Oracle initialization or shutdown in progress的异常 解决方法：
1.win+r打开命令行窗口，输入sqlplus/nolog 2.SQL>connect system/hope; 此时会报出相应的异常信息 3.SQL>connect sys/hope as sysdba 以管理员方式登录 4. SQL>startup mount 使用归档日志 5. SQL>alter database datafile’异常中对应的文件路径，注意大小写’ offline;使损坏的数据文件脱机 6. SQL>recover datafile’异常中对应的文件路径，注意大小写’; 恢复受损坏的文件。 7.SQL>alter database datafile ’异常中对应的文件路径，注意大小写’ online; 使其联机。 8.alter database open;打开数据库。ok
|
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
