1. 基本查询
- 字段(列)控制 a) 查询所有列 :
SELECT * FROM 表名;b) 查询指定列 :SELECT 列1 [,列2...列N] FROM 表名;c) 完全重复的记录只显示一次 :
- 当查询结果中的多行记录一模一样时,只显示一行. 一般查询所有列时很少会有这种情况, 但只查询一列(或几列)时,这种可能就大了!!(不会对源表产生影响, 只是在显示的时候, 不显示)SELECT DISTINCT 列1 [,列2, ... 列N] FROM 表名; - 列运算(源表没有变化, 查询不会修改数据库表记录) a) 数量型的列可以做加, 减, 乘, 除运算:
SELECT sal*1.5 FROM emp;b) 字符串类型的列可以做连接运算:SELECT CONCAT(ename, job) FROM emp;也可以使用连接符"||":SELECT ename ||'的工作是'|| job FROM emp;c) 转换 NULL 值:SELECT nvl(comm,0)+100 FROM emp;表示如果 comm 中存在 NULL 值, 那么当成 0 来运算. d) 给列起别名:SELECT nvl(comm,0)+1000 AS 奖金 FROM emp;其中 AS 可以省略. 别名也可以使用双引号,以便在别名中包含空格或特殊的字符并区分大小写;e) 如果查询的数据跟任何表没有关系,可以使用 dual 表(伪表):
SELECT 3+2 from dual; - 条件控制 a) 条件查询 - 字符和日期需要包含在单引号中 - 字符大小写敏感,日期格式敏感 - 默认的日期格式是 DD-MON-RR - 查询数据字典:
SELECT * FROM v$nls_parameters;b) 模糊查询
- 查询姓张, 并且姓名中包含""的员工,这时, ""需要转义SELECT * FROM emp WHERE ename LIKE '%\_%' escape '\'; - 排序
a) 降序 DESC
按照奖金降序排列,如果有空值,放在最后显示.SELECT * FROM emp order by comm desc nulls last;
2. 函数
2.1 字符函数
- 大小写控制函数
- 转为小写:
lower() - 转为大写:
upper() - 首字母大写:
initcap()
- 转为小写:
- 字符控制函数
substr(a,b,c): 从a字符中,第b位开始取,取c位length(): 字符数lengthb(): 字节数instr(a,b): 在a中查找是否存在b字符lpad(a,b,c): 左填充,a表示给谁填充,b表示填充至多少位,c表示以什么字符填充;rpad(a,b,c): 右填充;trim('H' from 'Hello WorldH'): 去除前后指定的字符;replace('Hello World','l','*'): 用"*"替换"hello world"中的 "l"
2.2 数值函数
round(45.926,2):四舍五入,结果为: 45.93;trunc(45.926,2):截取, 结果为: 45.92;
2.3 日期函数
- Oracle 中的日期型数据实际含有两个值,日期和时间; 默认的日期格式是: DD-MON-RR;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'): 格式化时间, 其中"sysdate"表示系统时间;- 在日期上加上或减去一个数字结果仍为日期;
- 两个日期相减返回日期之间相差的天数
MONTH_BETWEEN: 两个日期相差的月数;ADD_MONTHS: 向指定日期中加上若干月数;NEXT_DAY: 指定日期的下一个日期;例如,从今天开始,查看下一个周五是几号:NEXT_DAY(sysdate,'星期五')LAST_DAY: 本月的最后一天;ROUND: 日期四舍五入;例如,SYSDATE = '25-JUL-95'-ROUND(SYSDATE,'MONTH'), 结果为:01-AUG-95-ROUND(SYSDATE,'YEAR'), 结果为:01-JAN-96TRUNC: 日期截断; -TRUNC(SYSDATE,'MONTH'), 结果为:01-JUL-95-TRUNC(SYSDATE,'YEAR'), 结果为:01-JAN-95
2.4 转换函数
TO_CHAR(date,'format_model'): 将日期转换为字符 - 字符必须包含在单引号中而且大小写敏感; - 可以包含任意的有效的日期格式; - 日期之间用逗号隔开;TO_CHAR(number,'format_model'): 将数字转换成字符 - 查询员工薪水: 两位小数,千位符,本地货币代码 -SELECT TO_CHAR(sal,'L9,999.99') FROM emp;TO_NUMBER(char[, 'format_model']): 将字符转换成数字;TO_DATE(char[ , 'format_model']): 将字符转换成日期;
2.5 通用函数
- 这些函数适用于任何数据类型,同时也适用于空值;
NVL(expr1,expr2);NVL2(expr1,expr2,expr3);: 当 expr1=null 时,返回 expr3; 否则返回 expr2;NULLIF(expr1,expr2);: 当 expr1 = expr2 时,返回 null, 否则返回 expr1;COALESCE(expr1,expr2,...,exprn);: 从左到右,找到第一个不为 null 的值;
2.6 条件表达式
- 在 SQL 语句中使用 IF-THEN-ELSE 逻辑;
- CASE 表达式: SQL 99 的语法,类似 Basic, 比较繁琐;
- DECODE 函数: Oracle 自己的语法,类似 Java, 比较简洁;
// CASE 表达式 // 需求: 涨工资, 如果是总裁, 涨1000; 经理涨800; 其他涨400 SELECT empno,ename,job,sal 涨前, CASE job WHEN 'PRESIDENT' THEN sal+1000 WHEN 'MANAGER' THEN sal+800 ELSE SAL+400 END 涨后 FROM emp;// DECODE 表达式 SELECT empno,ename,job,sal 涨前, DECODE(job,'PRESIDENT',sal+1000, 'MANAGER',sal+800, sal+400) 涨后 FROM emp;
3. 组函数
- 概念
- 分组函数作用于一组数据,并对一组数据返回一个值;
- 常用的组函数
- 组函数会忽略空值;
- AVG
- COUNT
- MAX
- MIN
- SUM
- 分组数据
GROUP BY: 将表中的数据分成若干组;- 在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中;
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; - 包含在 GROUP BY 子句中的列不必包含在 SELECT 列表中;
4. 多表查询
- 连接类型
- 等值连接
- 不等值连接
- 左外连接:
WHERE e.deptno=d.deptno(+) - 右外连接:
WHERE e.deptno(+)=d.deptno - 自连接: 通过表的别名,将同一张表视为多张表;
- 层次查询
- 自连接不适合操作大表,因为笛卡尔积较大,例如有一亿条记录的大表,笛卡尔积的数量相当大;
- 可以使用层次查询(单表查询)
5. 子查询
5.1 概念
- 子查询: 在一个查询的内部还包括另一个查询.
- 可以在主查询的
where select having from后面使用子查询; - 单行子查询,返回一行;
- 多行子查询,返回多行; 多行比较操作符:
IN: 等于列表中的任何一个;ANY: 和子查询返回的任意一个值比较;ALL: 和子查询返回的所有值比较;
6. 分页
- rownum
- rownum 永远按照默认的顺序生成;
- rownum 只能使用 "< 或 <=", 不能使用"> 或 >=";
- rownum 永远从 1 开始;
- oracle 分页
SELECT * FROM (SELECT rownum r, el.* FROM (SELECT * FROM emp order by sal) el WHERE rownum <=8 ) WHERE r >= 5;
- 临时表
- 特点: 当事务或会话结束时,表中数据自动删除;
- 手动创建临时表:
CREATE GLOBAL TEMPORARY TABLE 表名; - 自动创建临时表: 在使用
order by时
// 基于事务的临时表, 在事务提交时,表自动删除 CREATE GLOBAL TEMPORARY table 表名 (列名 列类型, 列名2 列类型2) on COMMIT delete rows;// 基于会话的临时表, 在 exit 时,表自动删除 CREATE GLOBAL TEMPORARY table 表名 (列名 列类型, 列名2 列类型2) on COMMIT preserve rows;
6. 集合运算
- 并集:
UNION / UNION ALL - 交集:
INTERSECT - 差集:
MINUS - 参与集合运算的各个集合必须列数相同,且类型一致;
- 采用第一个集合作为最后查询出来的表头;
ORDER BY永远在最后;- 尽量不要使用集合运算,因为参与运算的集合数量越多,效率越低.
// 查询 10 和 20 号部门的员工 SELECT * FROM emp WHERE deptno=10 UNION SELECT * FROM emp WHERE deptno=20;// 备注: 如果 UNION 和 UNION ALL 相同的情况下, 尽量使用 UNION ALL// 因为 UNION = UNION ALL + DISTINCT
7. 插入数据
7.1 地址符 &
INSERT INTO emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);- 在 SQL 语句中使用 "&" 变量指定列值.
7.2 从其他表中拷贝数据
- 少量数据
- 一次性将 emp 中所有10号部门的员工插入到 emp10 中
INSERT INTO emp10 SELECT * FROM emp WHERE deptno=10;
- 插入海量数据
- 数据泵(PLSQL 程序: dbms_datapump);
- SQL*Loader 工具;
- 外部表;
- 如何去掉碎片
- 第一种方式:
ALTER table 表名 move; - 第二种方式: 数据的导出(exp 或 expdp)和导入(imp 或 impdp)
- 第一种方式:
8. 数据库事务
8.1 Oracle 中事务的标志
- 起始标志:事务中第一条 DML 语句;
- 结束标志(提交): 显示提交(COMMIT) 或 隐式提交(正常退出, exit, 或 DDL 或 DCL);
- 结束标志(回滚): 显示回滚(ROLLBACK) 或 隐式回滚(非正常退出, 掉电, 或宕机);
8.2 隔离级别
- Oracle 支持两种事务隔离级别:
READ COMMITED和SERIALIZABLE, 默认的事务隔离级别READ COMMITED; Oracle 还有一个自己独有的隔离级别:READ ONLY; - MySql 支持四种事务隔离级别;
8. DDL 创建和管理表
8.1 创建表
- 保存20号部门的员工
CREATE table emp20 as SELECT * FROM emp WHERE deptno=20; CREATE table emp20 as SELECT * FROM emp WHERE 1=2;"1=2"条件为假,表示只是将 emp 的表头复制到 emp20;
8.2 删除表
DROP table 表名;, 将表删除到 Oracle 回收站;- Oracle 回收站
- 查看回收站:
SHOW recyclebin; - 清空回收站:
PURGE recyclebin; - 从回收站中恢复(闪回删除):
flashback table 表名 to before drop; - 管理员没有回收站;
- 查看回收站:
8.3 约束
- 约束的类型
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK (检查性约束)
// 检查性约束 CREATE table test ( tid number, tname varchar2(20), gender varchar2(2) CONSTRAINT t_gender_ck(表示约束别名) check(gender in('男','女')), deptno number CONSTRAINT t_fk references dept(deptno) ON DELETE SET NULL ); 2. 外键约束
ON DELETE CASCADE: 当删除父表时, 级联删除子表记录;ON DELETE SET NULL: 当删除父表时, 将子表的相关依赖记录的外键值置为 null;
9 常见的数据库对象
9.1 概述
- 表: 基本的数据存储集合,由行和列组成;
- 视图: 从表中抽出的逻辑上相关的数据集合;
- 序列: 提供有规律的数值;
- 索引: 提高查询的效率;
- 同义词: 给对象起别名;
9.2 视图
9.2.1 概述
- 视图是一种虚表;
- 视图建立在已有表的基础上,视图赖以建立的这些表称为基表;
- 向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句;
- 视图向用户提供基表数据的另一种表现形式;
9.2.1 优点
- 简化复杂查询, 但视图不能提高性能;
// 创建视图 CREATE[OR REPLACE] VIEW 视图名 as SELECT e.empno,e.name,e.sal,e.sal*12 annsal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno WITH READ ONLY;
9.3 序列(sequence)
1. 概述
- 序列:可供多个用户用来产生唯一数值的数据库对象;
- 自动提供唯一的数值; (与 MySql 中的 auto_increment 类似)
- 主要用于提供主键值;
- 将序列值装入内存可以提高访问效率;
// 语法 CREATE SEQUENCE 序列名 [INCREMENT BY n] // 步长 [START WITH n] // 起始值 [{CACHE n | NOCACHE}] // 数组长度,默认为 20 (序列实际上是数组) 2. NEXTVAL 和 CURRVAL 伪列
- NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用;
- CURRVAL 中存放序列的当前值;
9.4 索引
9.4.1 概述
- 索引是一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中;
- 索引被删除或损坏,不会对表产生影响,其影响的只是查询的速度;
- 索引一旦建立,Oracle 管理系统会对其进行自动维护,而且由 Oracle 管理系统决定何时使用索引.
- 在删除一个表时,所有基于该表的索引会自动被删除;
- 索引通过指针加速 Oracle 服务器的查询速度;
- 索引通过快速定位数据的方法,减少磁盘 I/O;
9.4.2 什么时候创建索引
- 列中数据值分布范围很广;
- 列经常在 WHERE 子句或连接条件中出现;
- 表经常被访问而且数据量很大,访问的数据大概占数据总量的 2% 到 4%;
参考资料
