7.26实习培训日志-Oracle SQL(二)

Oracle SQL(二)

条件表达式

CASE 语句 或者DECODE 函数,两者均可实现 IF-THEN-ELSE 的逻辑,相比较而言,DECODE 更加简洁

“`sql
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

<pre><code> , 'ST_CLERK'
, 1.15 * salary

, 'SA_REP'
, 1.20 * salary
, salary) revised_salary
</code></pre>

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 定义的链接类型:

  1. 等于链接
  2. 不等链接
  3. 外连接(可细分为左外连接、右外连接)
  4. 自连接

工业标准定义(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使用using关键字

内连接

相当于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语句

  1. 写出表名+列名
  2. 仅写出表名
  3. 从其他表拷贝一行
  4. 使用子查询作为插入目标

update语句

  1. 更新符合条件的行中某些列为具体的值
  2. 使用子查询的结果作为更新后的值

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 动作:

  1. 数据定义语句被执行的时候,比如新建一张表:Create Table
  2. 数据控制语句被执行的时候,比如赋权 GRANT …( 或者 DENY)
  3. 正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句 。

当如下事件发生时,会隐式执行Rollback 动作:

  1. 非正常退出 iSQL*Plus , PLSQL DEVELOPER, 或者发生系统错误

读一致性: 对于有人正在修改过程中的一批数据,在其位提交前,其他用户读到的是
一致的内容。

Select … from TableA Where … For UPDATE NoWait:显示加锁,如果另一session在更新数据未提交,我们也更新数据,会造成死锁,但是我们不知情,只知道更新数据一直执行,但是用此语句可以得知是否死锁,会提醒我们有锁。

我们更新语句时最好用select语句查一下

某用户对某一批数据进行更改,而未提交之前,Oracle 会隐式的进行加锁。

MySQL的autocommit(自动提交)默认是开启,Oracle默认关闭

表的命名要求和表中列的命名要求:

  1. 必须以字母开头
  2. 长度不能超过30 个字符
  3. 只能包含 A–Z, a–z, 0–9, _, $, and #
  4. 不能与数据库中的已有对象重名
  5. 不能使用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区别

  1. 没有Rollback 机会
  2. 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,…);

外键约束类型:

  1. REFERENCES: 表示列中的值必须在父表中存在
  2. ON DELETE CASCADE: 当父表记录删除的时候自动删除子表中的相应记录.
  3. 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';

发表评论

电子邮件地址不会被公开。 必填项已用*标注