【Oracle数据库02】SQL(一)

SQL语句基础

本文承接Oracle数据库01_数据库简介及Oracle安装，主要记录SQL语句基础及数据定义语言DDL、数据操作语言DML、数据控制语言DCL相关知识点。

一、概述

1. 表

数据库就是数据的仓库。

数据库中包含表、关系以及操作对象。数据存放在表中。

表中存储的是格式相同的实体。表的行叫做记录，列叫做字段。

2. 数据类型

创建表时，必须为各个列指定数据类型。Oracle数据库的数据类型主要有以下几种。

• 字符串

  • char：定长字符串类型。声明长度后，如果存储不满，会使用空格补足。

  • varchar2：变长字符串类型。最多可以存储4000个byte。声明长度后，如果存储不满，则自动缩小长度。

• 数字

  • number：数字类型，类似于Java中的double。
    • number(2) - 两位数。
    • number(4,2) - 数字连小数点一共四位，小数点占两位。
    • number - 默认38位。
    • number(3,-1) - 三位数，忽略最后一位，还原为0。
  • int：是number类型的子类，相当于number(22)。

• 日期

  • date：日期类型。如2020/11/18 14:32。两个日期类型可以比大小。

• LOB（大对象）

  • CLOB：字符大对象类型。varchar2存储不下时可以考虑使用。
  • BLOB：二进制大对象类型。

注：varchar和varchar2的区别。
	1. varchar是标准sql里的。varchar2是oracle提供的独有的数据类型。
	2. varchar对于汉字占2个字节，英文占1个字节，占的内存小。varchar2均占2个字节，但是varchar2可以伸缩长度。
	3. varchar对空串不处理，即遇到空串会报错。varchar2将空串当做空对象null来处理。
	4. varchar存放固定长度的字符串，最大长度是2000。varchar2存放可变长度的字符串，最大长度是4000。

3. 运算符

运算符是一种符号，用来进行列间或者变量之间的比较和数学运算。主要有以下几种。

• 算术运算符

  运算符	说明
  +	加运算。求两个数或表达式相加的和
  -	减运算。求两个数或表达式相减的差
  *	乘运算。求两个数或表达式相乘的积
  /	除运算。求两个数或表达式相除的商（取整数部分）
  %	取模运算。求两个数或表达式相除的余数（取余）

• 逻辑运算符

  运算符	说明
  and	当且仅当两个布尔表达式都为true时，返回TRUE
  or	当且仅当两个布尔表达式都为false时，返回FALSE
  not	布尔表达式的值取反

• 比较运算符

  运算符	说明
  =	等于
  >	大于
  <	小于
  <>	不等于（SQL-92标准，所有数据库通用）
  >=	大于等于
  <=	小于等于
  !=	不等于（非SQL-92标准，仅Oracle数据库使用）

4. 语句分类

结构化查询语言SQL主要包括3个部分。

SQL语句	动词	作用
数据定义语言（DDL）	CREATE，ALTER，DROP	用于表、视图或索引的创建、修改和删除
数据操作语言（DML）	SELECT，INSERT，UPDATE，DELETE	用于数据的查询、添加、修改和删除
数据控制语言（DCL）	GRANT，REVOKE，DENY	实现权限控制，确定单个用户和用户组对数据库对象的访问

有的资料把 SELECT 从DML中分离出来，称作数据查询语言（DQL）。因为查询涉及的要点较多，故笔者照此分类，把查询相关记录在Oracle数据库03_SQL(二)。

最新的百度百科中，还额外增加了2个分类，用来表征数据库特性。笔者记录在了Oracle数据库05_数据库特性。

一是事务控制语言（TCL），它能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT、SAVEPOINT和ROLLBACK。

二是指针控制语言（CCL），它的语句DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

注：在Oracle数据库中，关键字、表名、列名都忽略大小写。字符串值是大小写敏感的，比较时严格区分大小写。

二、数据定义语言DDL

DDL即Data Define Language，数据定义语言。

1. 创建表

create table 表名(
	列名 数据类型,
	……
	列名n 数据类型n
);

注：
1. 最后一列末尾不加逗号；
2. 有时在右括号后加";",可加可不加。

2. 删除表

在表格删除时，同时表格中的数据也会被清空。

drop table 表名;

3. 修改表

添加列(为已存在的表添加一列)

alter table 表名 add 列名 数据类型;

删除列(为已存在的表删除一列)

alter table 表名 drop column 列名;

修改表名/列名(将已存在的表/列重命名)

alter table 旧表名 rename to 新表名;
alter table 表名 rename column 旧列名 to 新列名;

需要特别注意的是：
修改表结构的流程是先将表格中的数据备份，再将表格清空，修改结构，最后将备份的数据依次插入。
但是在实际开发中，当数据量很大时，这样的操作会极大地消耗数据库的性能，所以我们应尽量避免表格被重构。
正确的做法是，在设计表格时，应尽量的让表格中的字段完善，并预留出未来可能会修改的列或其它表结构，作为保留字段，留待以后添加新的业务。

三、数据操作语言DML

DML即Data Manipulation Language，数据操作语言。

1. 添加数据

全部添加数据：
insert into 表名 (所有列名) values (值列表);
insert into 表名 values (值列表);

添加部分数据：
insert into 表名 (列1,列2…) values (值1,值2…);

注：

1. 列个数要与值个数对应。
2. 字符串值需要用单引号引住，number值写数字。
3. 如果向所有列添加数据，可以直接省略列名。当向部分列添加数据时，列名是不可以省略的。

2. 删除数据

delete [from] 表名 [where 删除条件];

注：

1. []表示可有可无的语句;
2. 没有where条件，删除表中全部数据。有where条件，只删除匹配的数据。

3. 修改数据

修改单个记录：
update 表名 set 列名 = 更新值 [where 更新条件];

修改多个记录：
update 表名 set 列1 = 更新值1, …, 列n = 更新值n [where 更新条件];

注：

1. 不论修改几个记录，set只出现一次。

2. 没有where条件，修改表中全部数据。有where条件，只修改匹配的数据。

四、数据控制语言DCL

主要指对语句或对象权限的控制。

1. 授权

把语句权限或者对象权限授予给其他用户和角色。关键字是GRANT。

1.语句权限
语法：
GRANT privilege [, ...] ON object [, ...]
    TO { PUBLIC | GROUP group | username }

参数：
privilege : 权限。包括SELECT,INSERT,UPDATE,DELETE,RULE(表或视图上定义规则),ALL(赋予所有权限)等。
object    : 赋予权限的对象。包括table,view,sequence,index等。
PUBLIC    : 所有用户。
group     : 将要赋予权限的显示创建的组。
username  : 将要赋予权限的用户名。

2.对象权限
GRANT{ ALL [ PRIVILEGES ] | permission [ ,...n ] }{[ ( column [ ,...n ] ) ]
ON { table | view }| 
ON { table | view } [ ( column [ ,...n ] ) ]| 
ON {stored_procedure | extended_procedure }| 
ON { user_defined_function } } 
TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role} ]

2. 拒绝授权

用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

1.语句权限
语法：
DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

参数：
ALL              : 拒绝所有适用的权限。对于语句权限,只有sysadmin可以使用。对于对象权限,sysadmin和db_owner都可以使用。
statement        : 被拒绝权限的语句。
			       包括create database/default/function/procedure/rule/table/view, backup database/log等。
security_account : 当前数据库内受拒绝权限影响的安全帐户名称。

2.对象权限
DENY{ ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] )]
ON { table | view } |
ON { table | view } [ ( column [ ,...n ] ) ] |
ON { stored_procedure | extended_procedure } |
ON {user_defined_function } }
TO security_account [ ,...n ] [ CASCADE ]

3. 删除权限设置

将以前在当前数据库内的用户或者角色上授予或拒绝的权限删除，不影响用户或者角色从其他角色中作为成员继承过来的权限。

1.语句权限
语法：
REVOKE privilege [, ...] ON object [, ...]
	FROM { PUBLIC | GROUP group | username }

参数同GRANT

2.对象权限
REVOKE[ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission [ ,...n ] } { [( column [ ,...n ] ) ]
ON { table | view } |
ON { table | view } [ (column [ ,...n ] ) ] |
ON { stored_procedure | extended_procedure } |
ON { user_defined_function } }
{ TO | FROM } security_account [ ,...n ][ CASCADE ] [ AS { group | role } ]

特别鸣谢：@孙国安

参考资料：

1. 数据库系统概论(第5版)
2. 结构化查询语言
3. 数据控制语言

注：以上说明均为个人经验，如有错误欢迎指出，笔者虚心改正。如果有更好的方法或者别的问题，也欢迎大家扫文末的微信平台二维码，共同交流，共同进步。