Oracle基础语法汇总（四）

既往文章请浏览上一篇：17认证网👉Oracle认证👉技术资料

二．SQL查询和SQL函数

1、SQL支持的命令：

数据定义语言（DDL）:create,alter,drop,truncate 数据操纵语言（DML）:insert,delete,update,select

数据控制语言（DCL）:grant,revoke

事务控制语言（TCL）:commit,savepoint,rollback

2、Oracle使用的常见数据类型

字符，数值，日期，RAWID，LOB字符型

char:1-2000字节的定长字符，当存入数据内容的长度不够时，Oracle将在数据内容后面自动填充空格以达到其固定的长度。varchar2:1-4000字节的变长字符，从Oracle 12c版本开始，可以存储32767字节的内容。与char类型不同，Oracle不会在数据内容后面填充任何内容。long:2GB的变长字符注意：一个表中最多可有一列为long型

Long列不能定义唯一约束或主键约束

long列上不能创建索引

过程或存储过程不能接受long类型的参数。注意：汉字的存储

每个汉字占多少字节要看具体的编码方式，如UTF-8（1-3字节）、GB2312（2字节）、GBK（2字节）、GB18030（1、2、4字节）。

数值型

number:最高精度38位，number( m,n)，m表示总长度，n表示小数位的精度，如果存入的数据的小数位的精度超过了n，则取四舍五入后的值。

日期时间型

date:精确到ss

timestamp:秒值精确到小数点后6位

函数

sysdate,systimestamp返回系统当前日期，时间和时区。

更改时间的显示

alter session set nls_date_language=’american’;
alter session set nls_date_format=’yyyy-mm-dd’;

Oracle中的伪列

像一个表列，但没有存储在表中

伪列可以查询，但不能插入、更新和修改它们的值

常用的伪列：rowid和rownum

rowid:表中行的存储地址，可唯一标示数据库中的某一行，可以使用该列快速定位表中的行，它有10字节长。rownum:查询返回结果集中的行的序号，可以使用它来限制查询返回的行数。

3、数据定义语言（DDL）

用于操作表的命令

create table

CREATE TABLE stu_table (
ID NUMBER(6) NOT NULL,
STU_NAME varchar2(20) NOT NULL,
STU_AGE varchar2(20) NOT NULL,
);

alter table

truncate table

drop table

修改表的命令

alter table stu_table rename to stu_tbl;–修改表名
alter table stu_tbl rename column stu_age to age;–修改列名
alter table stu_tbl add (sex varchar2(20));–添加新列
alter table stu_tbl add (classno varchar2(20));–添加新列
alter table stu_tbl drop(classno );–删除列
alter table stu_tbl modify(age NUMBER(6));–更改列的数据类型(注意：要修改数据类型，则要修改的列必须为空)
alter table stu_tbl add constraint pk_stu_tbl primary key(id);–添加约束

4、数据操纵语言（DML）

select,update,delete,insert

插入数据insert

INSERT INTO stu_tbl VALUES(1000,’李华’,’20’);
INSERT INTO stu_tbl VALUES(1001,’accp’,’20’);
INSERT INTO stu_tbl VALUES(1003,’nimda’,’30’);

利用现有的表创建表

create table stu_tbl_log as select id,stu_name,age from stu_tbl where 1=2;–根据已有数据表创建新表,仅包含表结构

选择无重复的行

select distinct stu_name from stu_tbl;–过滤拥有stu_name相同名字的行

插入来自其他表中的记录

insert into stu_tbl_log select id,stu_name,age from stu_tbl;
修改现有的行数据update stu_tbl set id=1002,stu_name=’admin’,sex=’男’ where id=1003
update stu_tbl set sex=’男’ where id in (1000,1001);

5、数据控制语言（DCL）

grant：GRANT语句用于授予用户权限。GRANT语句的基本语法如下：GRANT privilege [, …] TO user [, …] [WITH GRANT OPTION];

其中：

privilege 是要授予的权限名称。
user 是要授予权限的用户名称。
WITH GRANT OPTION 是可选的，表示授予用户可以将这些权限授予其他用户的能力。

示例代码：

授予用户查询（SELECT）、插入（INSERT）和删除（DELETE）权限：

GRANT SELECT, INSERT, DELETE ON my_table TO user_name;

注意：执行GRANT语句的用户必须拥有授权权限本身，或者具有管理员权限。

revoke：REVOKE语句用于撤销用户或角色的权限。以下是REVOKE语句的基本语法：

REVOKE privilege | role
FROM user | role | PUBLIC;
privilege 是要撤销的权限名称。
role 是要撤销的角色。
user 是要从中撤销权限的用户或角色。
PUBLIC 关键字意味着撤销的权限将会应用于所有用户。

示例代码：

–撤销特定用户的权限：这条语句会从用户john撤销在employees表上的SELECT权限。
REVOKE SELECT ON employees FROM john;
–撤销角色的权限：这条语句会从角色hr_role撤销在employees表上的SELECT权限。
REVOKE SELECT ON employees FROM hr_role;
–撤销所有用户的权限：这条语句会从所有用户撤销在employees表上的SELECT权限。
REVOKE SELECT ON employees FROM PUBLIC;

注意：确保在执行REVOKE操作前，你有足够的权限去执行该操作，并且已经确认了REVOKE操作的影响。

6、事务控制语言（TCL）

commit（COMMIT命令用于把事务所做的修改保存到数据库，它把上一个COMMIT或ROLLBACK命令之后的全部事务都保存到数据库。）savepoint（通过创建检查点我们可以在事务提交前回退到任意已创建检查点的事务。）rollback

7、SQL操作符

算术操作符:用于执行数值计算，包括加(+)、减(-)、乘(*)、除(/)。比较操作符:用于比较两个表达式的值。包括 =、!=、<、>、<=、>=、between…and、in、not in、like 和 is null等。逻辑操作符:包括与(AND)、或(OR)和非(NOT)。集合操作符:将两个查询的结果组合成一个结果。包括 union、union all、intersect、minus。连接操作符:用于将多个字符串或数据值合并成一个字符串。包括 ||

示例中stu_tbl_log中的数据如下：

stu_tbl中的数据如下：

示例：

select (3+2)/2 from dual;–算术操作符，结果：2.5
select * from stu_tbl where age>=20;–比较操作符
select * from stu_tbl where stu_name like ‘%a%’;–比较操作符：like
select * from stu_tbl where stu_name like ‘a___’;–比较操作符：like
select * from stu_tbl where age in(20,30);–比较操作符：in
select * from stu_tbl where age between 20 and 30;–比较操作符：between…and…(包括边界值，边界值是指定范围的开始值（BETWEEN后面的值）和结束值（AND后面的值）)

集合操作符测试具体如下：