Oracle SQL(二)
条件表达式
CASE 语句 或者DECODE 函数，两者均可实现 IF-THEN-ELSE 的逻辑，相比较而言，DECODE 更加简洁
SELECT last_name
,job_id
,salary
,CASE job_id
WHEN 'IT_PROG' THEN
1.10 * salary
WHEN 'ST_CLERK' THEN
1.15 * salary
WHEN 'SA_REP' THEN
1.20 * salary
ELSE
salary
END as "REVISED_SALARY"
FROM employees;
DECODE(col|expression, search1, result1 [, search2, result2,...,]
[, default])
SELECT last_name
,job_id
,salary
,decode(job_id
, 'IT_PROG'
, 1.10 * salary
, 'ST_CLERK'
, 1.15 * salary
, 'SA_REP'
, 1.20 * salary
, salary) revised_salary
FROM employees;多表关联
在执行多表查询时，若未指定链接条件，则结果返回是个笛卡尔乘积
--左外连接 ：左表不加限制，保留左表的数据，匹配右表，右表没有匹配到的行中的列显示为null。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
--右外连接 右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为null。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;Oracle 定义的链接类型：
- 等于链接
- 不等链接
- 外连接（可细分为左外连接、右外连接）
- 自连接
工业标准定义（SQL 1999) 的链接类型
交叉连接
select * from book as a cross join stu as b order by a.id相当于没有连接条件的多表关联查询，结果是个笛卡尔乘积
自然链接
自然链接：相当于Oracle 的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段作为） “等于连接”条件；（注意如果两个表中有相同的列名，但字段类型不一样，这会引发一个错误）
Using 子句
Using 子句可开着是 自然连接 的一种补充功能，我们知道自然连接会让系统自动查找两张表中的段 所有列名相同的字段，并试图建立“等于连接”；但有的时候我们不期望这么做，而只是期望某个特定的字段用 作为“等于连接”的条件，这种情况下可以使用Using 子句来做限制。
select emptno,ename,sal,deptno,dname from emp e inner join dept d using(deptno);内连接
相当于Oracle 的“等于链接”
隐式内连接
select a.id,b.name from a,b where a.id=b.pid
显示内连接
select a.id,b.name from a inner join b on a.id=b.pid
显示内连接比隐式内连接相同情况下更快。on是连接条件的限定
实际上一回事情了。只是内连接是由SQL 1999规则定的书写方式。
外连接（全外连接、左外连接、右外连接）
select * from book as a left join stu as b on a.sutid = b.stuid
左连接：是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL
分组计算函数和Group By子句
分组函数
| 函数 | 功能 |
|---|---|
| sum | 求和 |
| avg | 求平均值 |
| count | 计数 |
| stddev | 求标准差 |
| variance | 求方差 |
| max | 求最大值 |
| min | 求最小值 |
MIN, MAX 可用于任何数据类型，但AVG ， SUM ，STDDEV, VARIANCE 仅适用于数值型字段
| 函数 | 功能 |
|---|---|
| COUNT(*) | 返回满足选择条件的所有行的行数，包括值为空的行和重复的行 |
| COUNT(expr) | 返回满足选择条件的且表达式不为空行数。 |
| COUNT(DISTINCT expr) | 返回满足选择条件的且表达式不为空，且不重复的行数。 |
group by
Group By可以按照某一个字段分组，也可以按照多个字段的组合进行分组
SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ，其他字段必须出现在Group By 子
句中，否则不合法
SELECT department_id, count(last_name) FROM employees
GROUP BY department_id;--没有group by要报错不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。
SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;子查询
单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL 等
DML语句
DML: Data Manipulation Language , 数据操纵语言；简单的说就是SQL 中的增、删、改 等语句。
insert语句
- 写出表名+列名
- 仅写出表名
- 从其他表拷贝一行
- 使用子查询作为插入目标
update语句
- 更新符合条件的行中某些列为具体的值
- 使用子查询的结果作为更新后的值
delete语句
有约束可能删除失败
TRUNCATE TABLE copy_emp --TRUNCATE 语句无法回滚
merge语句
MERGE 语句： 比较整合语句
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);事务控制
当如下事件发生是，会隐式的执行Commit 动作：
- 数据定义语句被执行的时候，比如新建一张表：Create Table
- 数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)
- 正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句 。
当如下事件发生时，会隐式执行Rollback 动作：
- 非正常退出 iSQL*Plus , PLSQL DEVELOPER, 或者发生系统错误
读一致性: 对于有人正在修改过程中的一批数据，在其位提交前，其他用户读到的是
一致的内容。
锁
Select … from TableA Where … For UPDATE NoWait：显示加锁，如果另一session在更新数据未提交，我们也更新数据，会造成死锁，但是我们不知情，只知道更新数据一直执行，但是用此语句可以得知是否死锁，会提醒我们有锁。
我们更新语句时最好用select语句查一下
某用户对某一批数据进行更改，而未提交之前，Oracle 会隐式的进行加锁。
MySQL的autocommit(自动提交)默认是开启,Oracle默认关闭
表
表的命名要求和表中列的命名要求：
- 必须以字母开头
- 长度不能超过30 个字符
- 只能包含 A–Z, a–z, 0–9, _, $, and #
- 不能与数据库中的已有对象重名
- 不能使用Oracle 数据库的保留字
| 数据类型 | 描述 |
|---|---|
| VARCHAR2(size) | 可变长字符串 |
| CHAR(size) | 定长字符串 |
| NUMBER(p,s) | 可变长数值 |
| DATE | 日期时间 |
| LONG | 可变长大字符串，最大可到2G |
| CLOB | 可变长大字符串数据，最大可到4G |
| RAW and LONG RAW | 二进制数据 |
| BLOB | 大二进制数据，最大可到4G |
| BFILE | 存储于外部文件的二进制数据，最大可到4G |
| ROWID | 64进制18位长度的数据，用以标识行的地址 |
| TIMESTAMP | 精确到分秒级的日期类型（9i以后提供的增强数据类型） |
| INTERVAL YEAR TO MONTH | 表示几年几个月的间隔（9i以后提供的增强数据类型-极其少见） |
| INTERVAL DAY TO SECOND | 表示几天几小时几分几秒的间隔（9i以后提供的增强数据类型-极其 |
| 少见） |
CLOB：CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。
BLOB:BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用
创建表和普通的相同
从子查询快速建表
CREATE TABLEA as select * from tablename;--常用于复制表
CREATE TABLEA as select * from tablename where 1=2 --保留表结构，不复制数据
--添加列：
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
--更改列
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
--删除列：
ALTER TABLE table
DROP (column);
--删除表：表被删 除后，任何依赖于这张表的视图、Package等数据库对象都自动变为无效：
DROP TABLE tableName;
--更改 表名：
RENAME oldtablename to newtableName;
--一次性清空一张表中的所有内容，但保留表结构：
TRUNCATE TABLE tableName;注意TRUNCATE 与DELETE FROM table区别
- 没有Rollback 机会
- HWM 标记复位
约束
| 约束类型 | 中文名 |
|---|---|
| NOT NULL | （非空约束） |
| UNIQUE | （唯一性约束） |
| PRIMARY KEY | （主键约束） |
| FOREIGN KEY | （外键约束） |
| CHECK | （自定义约束） |
在创建表的时候同时创建 约束语法:CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID))或HIRE_DATE DATE CONSTRAINT emp_hire_date_nn NOT NULL
单独创建约束 语法:
ALTER TABLE tablename ADD CONSTRAINT constraintname
constrainttype (column1,…);
外键约束类型：
- REFERENCES: 表示列中的值必须在父表中存在
- ON DELETE CASCADE: 当父表记录删除的时候自动删除子表中的相应记录.
- ON DELETE SET NULL: 当父表记录删除的时候自动把子表中相应记录的值设为NULL
--删除约束
ALTER TABLE tablename DROP CONSTRAINT constraintname;
--失效约束
ALTER TABLE tablename disable CONSTRAINT constraintname;
--生效约束
ALTER TABLE tablename enable CONSTRAINT constraintname;
--级联删除
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
--查询约束
SELECT * FROM user_constraints WHERE table_name = 'employees';
扫一扫
375
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
