1. SQL 简介

SQL (Structured Query Language,结构化查询语言)支持如下类别命令:

  • 数据定义语言(Data Definition Language, DDL): CREATE(创建)、ALTER(更改)、TRUNCATE(截断)、DROP(删除)命令。
  • 数据操纵语言(Data Manipulation Language, DML): INSERT(插入)、SELECT(选择)、DELETE(删除)、UPDATE(修改)命令。
  • 事务控制语言(Transaction Control Language, TCL):COMMIT(提交)、SAVEPOINT(保存点)、ROLLBACK(回滚)命令。
  • 数据控制语言(Data Control Language, DCL):GRANT(授予)、REVOKE(回收)命令。

2. 数据定义语言(Data Definition Language, DDL)

用于改变数据库结构,包括创建、修改和删除数据库对象。

2.1. CREATE

创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等。

#创建表
CREATE TABLE tablename
(columname datetype);
#创建视图
CREATE VIEW VIEW_NAME AS SQL 语句;
#创建索引
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME);

ORACLE常用的字段类型:

类型说明
CHAR固定长度的字符串
VARCHAR2可变长度的字符串
NUMBER(M,N)数字型M是位数总长度, N是小数的长度
DATE日期类型

命名规则:

  • 表名的最大长度为30个字符;
  • 表名首字母为字母,可以用下划线、数字和字母,但不能使用空格和单引号;
  • 同一用户模式下的不同表不能有相同的名称;
  • 表名、列名、用户名、和其他对象名不区分大小写,系统会自动转换成大写。

习惯:

  • 创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
  • 创建表时可以用中文的字段名, 但最好还是用英文的字段名
  • 创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE,这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
  • 创建表时可以给字段加上约束条件,例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY

2.2. ALTER

修改表, 索引, 视图等

ALTER TABLE <tablename>
    MODIFY (column definition);
    ADD (column definition);
    DROP COLUMN column;
#改变表的名称
ALTER TABLE 表名1  TO 表名2;

#在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;

#修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;

#给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

#把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;

2.3. TRUNCATE

仅删除表格中的数据,保留表结构。

TRUNCATE TABLE <tablename>; #快速删除记录并释放空间,不使用事务处理,无法回滚,效率高。

DESC <tablename>  #查看表结构

2.4. DROP

删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等。

DROP TABLE|VIEW|INDEX|DATABASE <name>

3. 数据操纵语言(Data Manipulation Language, DML)

  • 插入:INSERT
  • 更新:UPDATE
  • 删除:DELETE
  • 查询:SELECT(有的地方把SELECT单独分为‘数据查询语言,DQL’)

4. 事务控制语言(Transaction Control Language, TCL)

事务(Transaction)是由一系列相关的SQL语句组成的最小逻辑工作单元,在程序更新数据库时事务事关重要,因为必须维护数据的完整性。事务由数据操作语言完成,是对数据库所做的一个或多个修改。

事务的特征:

  • 所有的事务都有开始和结束;
  • 事务可以被保存或撤销;
  • 如果事务在中途失败,事务中的任何部分都不会被记录到数据库。

4.1. COMMIT

COMMIT 命令用于把事务所做的修改保存到数据库,表明该事务对数据库所做的操作将永久记录到数据库。 自动提交:SET AUTOCOMMIT ON;

4.2. SAVEPOINT

保存点是事务过程中的一个逻辑点,我们可以把事务回退到这个点,而不必回退整个事务。

4.3. ROLLBACK

ROLLBACK 命令用于撤销还没有被保存到(未提交的事务)数据库的命令,它只能用于撤销上一个COMMIT或ROLLBACK命令之后的事务。

  • 更新scott.emp表中的sal字段,然后执行ROLLBACK操作。
UPDATE scott.emp SET sal=sal*2; 
ROLLBACK; #UPDATE语句等于并没有执行
  • 更新scott.emp表中的sal字段,然后执行COMMIT操作。
UPDATE scott.emp SET sal=sal*2; 
COMMIT;  #UPDATE真正提交到数据库
  • 向表中插入员工编号为1111的记录,设置一个保存点,然后用UPDATE命令将该记录的员工姓名修改为李明,然后用ROLLBACK命令回滚到保存点。
INSERT INTO scott.emp(empno) VALUES (1111);
SAVEPOINT p1; #设置保存点p1 
UPDATE scott.emp SET ename="李明" WHERE empno=1111; 
ROLLBACK TO p1;  #回退到p1。

4.4. 事务控制与数据库性能

  • 当出现COMMIT命令时,回退事务信息被写入到目标表里,临时存储区域里的回退信息被清除
  • 当出现ROLLBACK命令时,修改不会作用于数据库,而临时存储区域里的回退信息被清除
  • 如果一直没有出现COMMIT 或 ROLLBACK 命令,临时存储区域里的回退信息就会不断增长,直至没有剩余空间,导致数据库停止全部进程,直至空间被释放。

5. 数据控制语言(Data Control Language, DCL)

为用户提供权限控制命令。

常用的系统权限集合:CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

常用的数据对象权限:ALL,SELECT,UPDATE,DELETE,INSERT,ALTER。

5.1. GRANT

授予对象权限

GRANT SELECT,UPDATE ON 表名 TO 用户名;

5.2. REVOKE

回收对象权限

REVOKE SELECT,UPDATE ON 表名 FROM 用户名;

6. Oracle 函数

函数接受一个或多个参数并返回一个值。

6.1. 日期函数

对日期值进行运算,根据用途产生日期/数值类型的结果。

函数说明
ADD_MONTHS(d, n)返回指定日期加上月数后的日期值
MONTHS_BETWEEN(d1, d2)返回两个日期间的月数
LAST_DAY(d)返回 指定日期当前的最后一天的日期值
RONUD(d,[fmt])返回 指定日期四舍五入格式(YEAR、MONTH、DAY)后的 日期值
NEXT_DAY(d,day)返回指定日期下一个星期几的日期值
TRUNC(d,[fnt])返回指定日期截断为格式后的日期值
EXTRACT(fmt FROM d)返回指定日期提取的格式的值

6.2. 字符函数

字符函数接受字符输入,并返回字符或数值。

函数说明
INITCAP(char)首字母大写
LOWER(char)转换为小写
UPPER(char)转换为大写
LTRIM(char, set)左裁切
RTRIM(char, set)右裁切
TRANSLATE(char, from, to)按字母翻译
REPLACE(char, search_str, replace_str)字符串替换
INSTR(char, substr[,pos])查找子串位置
SUBSTR(char, pos, len)取子字符串
CONCAT(char1, char2)连接字符串
CHR(ascii)根据 ASCII 码返回对应字符串
LPAD / RPAD左/右填充LPAD ('aa', 5 , '=') 返回 '===aa'
TRAM开头或结尾(或 开头和结尾)裁剪特定的字符,默认裁剪空格。<br>TRIM ([LEADING / TRAILING] trim_char)
LENGTH(char)返回字符串长度
DECODE逐个值进行字符串替换<br>DECODE (expr, search1, result1, search2, result2, [ ,default])<br>DECODE (ostalus, 'p', '准备处理', 'c', '已完成')

6.3. 数字函数

数字函数接受数字输入并返回数值作为输出结果。

函数说明
ABS(n)取绝对值
CEIL(n)向上取值
FLOOR(n)向下去整
SIN(n)正弦值
COS(n)余弦值
POWER(m, n)指数函数
SQRT(n)平方根
MOD(m, n)取余
ROUND(m, n)小数点后精度四舍五入
TRUNC(m, n)小数点后精度截断

6.4. 转换函数

转换函数将一种数据类型转换为另一种数据类型。

函数说明
TO_CHAR (d/n, [,fmt])格式化 日期 / 数值
TO_DATE (char [,fmt])将 fmt模型格式的字符串 转换为日期型
TO_NUMBER (char)将 包含数字的的字符串转换为 数值型

6.5. 其他函数

函数说明
NVL (exp, exp2)如果 exp 为空返回 exp2;如果非空返回 exp
NVL2 (exp, exp2, exp3)如果 exp 为空返回 exp3;如果非空返回 exp2
NULLIF (exp1, exp2)比较两表达式,相等返回空值,不等则返回 exp1

6.6. 分析函数

分析函数根据一组行来计算聚合值。这些函数通常用来完成对聚集的累积排名、移动平均数和报表计算。分析函数与聚合函数不同的是他们为每组记录返回多个行。

ROW_NUMBER () OVER ([PARTITION BY colum] ORDER BY colum)

为有序组中的每一行返回一个唯一的排序值,序号由 ORDER BY 子句指定,从 1 开始,即使具有相等的值,排位也不同。 PARTITION BY colum 按列值进行区分,各分组内在进行排序。

RANK () OVER ([PARTITION BY colum] ORDER BY colum)

计算一个值在一个组中的地位,由 1 开头,具有相等值得行排位相同,序数随后跳跃相应的数值。

DENSE_RANK () OVER ([PARTITION BY colum] ORDER BY colum)

计算一个值在一个组中的地位,由 1 开头,具有相等值得行排位相同,并且排位是连续的。