SQL 语言与 Oracle 数据库

#SQL 语言包括的内容

DDL，数据库定义语言：定义关系模式、删除关系、建立索引以及修改关系模式。
DML，数据库操纵语言：查询（select）、插入（insert）、删除（delete）与修改（update）。
嵌入式 DML。嵌入在 Pascal、C 等宿主语言中。
视图定义。
权限管理。（grant、revoke、deny等）。
完整性约束。
事务控制。（commit、rollback、savepoint）。

#数据库提交事务的几种情况

一条显式的COMMIT或者Rollback命令
一条DDL或者DCL语句
非正常退出sql plus，或者使用exit或quit退出sql plus
系统崩溃

后三条为隐式提交。

#SQL 语句

#建表

1
2
3
4
5
6


create table table_name(
    col_name data_type [column level constraint],
    col_name data_type [column level constraint],
    ...,
    [table level constraint]
);

完整性约束条件：

非空，not null。
主键，primary key。
参照完整性约束（外键），foreign key。
唯一性约束，unique。
检查约束，check。

1
2
3
4
5
6
7
8


-- 表级约束实例
create table test(
    testColOne varchar2(10),
    testColTwo number(4),
    testColThree char(2) check(testColThree in ('好','坏')),
    primary key (testColOne, testColTwo),
    foreign key (testColThree) references test2(testCol)
);

#修改表

#删除基本表

1
2
3


drop table table_name [where condition]
-- 或者使用truncate
truncate table table_name

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。
如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。

#修改基本表

1
2
3
4
5
6
7
8


-- 添加列
alter table table_name add col_name data_type;

-- 修改列的数据类型
alter table table_name modify col_name data_type;

-- 删除完整性约束
alter table table_name drop constraint_name(col_name);

#插入数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 1.插入的数据数与表列数相同且数据类型相同
insert into table_name values(v1, v2, v3, v4);

-- 2.在特定列上插入数据并且其他列允许空值
insert into table_name(col1, col2, col3) values(v1, v2, v3);

-- 3.从一个表中查询数据插入到另一个表中
insert <distinct|all> into table_name
values(col1, col2, col3)
select col1, col2, col3 from table_name2 where conditions;

#更新数据

1
2
3


update table_name
set col_name = 值或者查询语句结果
where conditions;

#查询

1
2
3
4
5
6
7


-- 基于文本过滤的查询中包含'_'或者'%'的情况
-- 例：查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。
select *
from course
where cname like 'DB\_%i__' escape '\';

-- 集合查询 union [all]、minus、intersect

#表间数据转换

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


-- 同时插入到多表 无条件
INSERT ALL
INTO first_table_name
    VALUES(first_column_name,...last_column_name)
...
INTO last_table_name
    VALUES(first_column_name,...last_column_name)
SELECT statement;

-- 同时插入到多表 有条件
INSERT <ALL|FIRST>
WHEN first_condition THEN INTO first_table_name
    VALUES(first_column_name,...last_column_name)
...
WHEN last_condition THEN INTO last_table_name
    VALUES(first_column_name,...last_column_name)
[ELSE INTO defaul_table_name
    VALUES(first_column_name,...last_column_name)]
SELECT statement;

-- 多表合并
MERGE INTO main_table
USING change_table
ON (main_table.primary_key=change_table.primary_key)
WHEN MATCHED THEN
UPDATE
SET
main_table.first_column=change_table.first_column
...
main_table.last_column=change_table.last_column
WHEN NOT MATCHED THEN
INSERT(first_column,...last_column)
VALUES(change_table.first_column,...last_column);

#SQL 函数

#字符函数

LOWER(列名|表达式)：将大写或大小写混合的字符转换成小写
UPPER(列名|表达式) ：将小写或大小写混合的字符转换成大写
INITCAP(列名|表达式) ：将每个单词的第一个字母转换成大写，其余的字母都转换成小写
CONCAT:连接两个值,等同于 ||

格式：CONCAT(column1|expression1,column2|expression2)
SUBSTR:返回第一个参数中从 n1 字符开始长度为 n2 的子串，如果 n1 是负值，表示从后向前数的 abs(n1) 位，如果 n2 省略,取 n1 之后的所有字符

格式：SUBSTR(column | expression,n1[,n2])
LENGTH:取字符长度

格式:LENGTH(column | expression)
INSTR:返回 s1 中，子串 s2 从 n1 开始，第 n2 次出现的位置。n1,n2 默认值为 1

格式:INSTR(s1,s2,[,n1],[n2])
LPAD:返回 s1 被 s2 从左面填充到 n1 长度。

格式：LPAD(s1,n1,s2)
RPAD:返回 s1 被 s2 从右面填充到 n1 长度。

格式：RPAD(s1,n1,s2)
REPLACE:把 s1 中的 s2 用 s3 替换。

格式:REPLACE(s1,s2,s3)

#日期函数

SYSDATE()：返回系统日期
MONTHS_BETWEEN(date1,date2)：返回两个日期间隔的月数
ADD_MONTHS(date1,n)：在指定日期基础上加上相应的月数
NEXT_DAY()：返回某一日期的下一个指定日期，例如：NEXT_DAY('02-2月-06','星期一')
LAST_DAY：返回指定日期当月最后一天的日期，例如：LAST_DAY('02-2月-2006')
ROUND(date[,'fmt'])将 date 按照 fmt 指定的格式进行四舍五入，fmt 为可选项，如果没有指定 fmt，则默认为 ‘DD’，将 date 四舍五入为最近的天。

格式码：世纪 CC,年 YY，月 MM,日 DD，小时 HH24，分 MI，秒 S 例如：ROUND(hire_date, 'MONTH')
TRUNC(date[,'fmt'])将 date 按照 fmt 指定的格式进行截取，fmt 为可选项，如果没有指定 fmt，则默认为 ‘DD’，将 date 截取为最近的天。TRUNC(hire_date, 'MONTH')
EXTRACT：返回从日期类型中取出指定年、月、日，例如：EXTRACT (MONTH FROM HIRE_DATE)