Oracle数据库语言-DQL、DCL、DDL

目录

一、数据查询语言（DQL）：

二、数据控制语言（DCL）：

三、数据定义语言（DDL）：

3.1DDL-表

3.1.1创建表

3.1.2删除表

3.1.3查看回收站

3.1.4清空表数据

3.1.5修改表

3.2DDL-视图

3.2.1视图语法

3.2.2视图使用方式

3.2.3视图优缺点

3.2.4删除视图

3.3DDL-序列

3.3.1序列的使用方法：

3.3.2序列的使用实例

3.3.3删除序列

3.3.4序列适用场景

3.3.5序列的优缺点

---

SQL语言种类：

SQL（Structured Query Language）是一种用于访问和管理关系型数据库的语言。它具有一套基本的语法和结构，数据库语言种类包括：

一、数据查询语言（DQL）：

用于查询数据库中的数据，包括SELECT、FROM、WHERE等关键字。

简单查询、限定查询、树形查询、子查询、表连接、集合等都属于数据查询语言。

SQL查询语言的基本语法结构是由关键字、表名、字段名、条件表达式和操作符等组成的。例如，SELECT语句的基本语法结构如下：

SELECT column1, column2, ...

FROM table_name

WHERE condition;

其中，column1、column2等是要查询的字段名，table_name是要查询的表名，condition是查询条件。SQL查询语言的具体语法和用法可以根据不同的数据库产品而有所不同，需要根据具体的情况进行学习和使用。

二、数据控制语言（DCL）：

用于控制数据库中的访问权限，包括GRANT、REVOKE等关键字。

GRANT	赋权
REVOKE	回收权限

DCL的用处一般是给用户赋权和回收权限：

dba权限是数据库最高权限；dba权限或最高权限,只有数据库管理员(dba)可以赋权和回收权限

授权： GRANT 权限 TO 用户;

回收权限：REVOKE 权限 FROM 用户;

从命令窗口（win+R）连接数据库的语法 ：

Cmd打开命令窗口

sqlplus / as sysdba --sysdba是个用户(有dba权限的用户)

--我们现在是以sysdba的身份登录oracle

给scott用户赋dba权限，在scott用户下查询hr用户的表 ：

GRANT dba TO scott;

执行此命令后，Scott用户将被授予DBA角色，拥有数据库管理员的权限。注意，授予DBA角色需要具有足够的权限，因此需要使用具有足够权限的用户或管理员账户执行此命令。

收回scott用户赋dba权限，在scott用户下查询hr用户的表:

REVOKE dba FROM Scott;

区别：

①在连接数据库的情况下，更改权限是不会立即生效的，

②需要退出数据库重新登陆(切换用户再切回来)

三、数据定义语言（DDL）：

用于定义数据库中的结构，包括CREATE、ALTER、DROP等关键字。

创建	create
删除	drop
修改	alter

3.1DDL-表

可以使用DDL语言创建表、修改表（不是修改数据、是修改属性，比如表的名字、表的列数、表的列的格式；对表数据进行修改的是其他的语言-DML）

3.1.1创建表

Ⅰ、复制表(可以只复制表格式也可以复制表)语法：

CREATE TABLE 表名 AS select语句;

比如：

--创建一张表emp1和emp表完全一致(板书)(其实也不是完全一样，有约束的时候就能看出来)
create table emp1 as select * from emp;
select * from emp1;

--创建emp2数据同emp表工作为CLERK的员工信息(板书)
CREATE TABLE emp2 AS SELECT * FROM emp WHERE job='CLERK';
SELECT * FROM emp2;

--创建一张空的emp3表格式为emp表格式
create table emp3 as select * from emp where 1=2;
SELECT * FROM emp3;

--创建表emp3内容和emp表的ename,job,hiredate列一致
create table emp3 as select ename,job,hiredate from emp;

--创建emp10、emp20,emp30三张表、表数据分别为
--10部门、20部门、30部门
create table emp10 as select * from emp where deptno=10;
create table emp20 as select * from emp where deptno=20;
create table emp30 as select * from emp where deptno=30;
select * from emp10;
select * from emp20;
select * from emp30;

Ⅱ、手动创建表

①字段类型

数值型	number,int
字符型	char,varchar/varchar2
日期型	date
大数据类型	blob,clob,nclob,bfile,xml type

解释一下各种字段类型：

数值型：

NUMBER[(数1[,数2])]

数1是长度,数2是小数位数,数2不写默认没有小数,

number类型会四舍五入。

数1最大38，就是说number的精度最大是38位

数1不写,最多可以存126位,但是精度仍然是38

除了number之外还有int↓ 放整数的。

INT只能存整数,相当于number(22)。

字符型：

CHAR[(数)] 定长字符，最多存储2000个字节的定长字符,数不写,默认1，位数不足用空格补齐.

比如char(10)存了一个9位的字符,最后一位用空格补齐；

--读取快,可能会浪费空间。

VARCHAR/VARCHAR2(数) 可变长字符，varchar是定长字符，最多存储2000个字节,varchar2是可变长字符,最多存储4000个字节的可变长字符,数不能不写，位数不足,直接存储；

--读取比char略慢,不浪费空间。

--当列中值存在大量空的时候varchar2比char读取要快！！！

日期型：

DATE 我们用lengthb(日期)看emp表中日期的字节长度！由一个(9)个字节组成的定宽日期、时间类型。包含7个属性:世纪、年、月、日、时、分、秒不会浪费空间。

比如：2018-8-1 10:00:00

这就是20世纪 、18年、8月、1日、10时、0分、0秒

TIMESTAMP 时间戳 --stamp:邮票,邮戳；比date存储更精确的时间,还存储了时区，书写格式基本上和date一致。

SELECT SYSTIMESTAMP FROM dual;


select to_timestamp('2020-1-1 2:10:30.61561561','YYYY-MM-DD hh12:mi:ss.ff')

from dual;


select timestamp'2020-1-1 2:10:30.200'

from dual;

 

大数据类型：

• BLOB：二进制大对象。BLOB用于存储二进制数据，如图像、音频和视频等。
• CLOB：字符大对象。CLOB用于存储字符数据，如文本、HTML和XML等。
•  NCLOB：National Character Set（NCS）字符大对象。与CLOB类似，但支持国家字符集。
• BFILE：二进制文件。BFILE用于存储二进制文件，如图像、音频和视频等。BFILE数据存储在操作系统文件系统中，而不是数据库中。
• XMLType：XML类型。XMLType用于存储XML数据。

这些大数据类型可以存储大量的数据，支持高效的读写操作，并且可以通过SQL和PL/SQL等语言进行访问和处理。

②语法结构：

CREATE TABLE 表名 (列1 类型长度 [约束],列2 类型长度 [约束]...)

比如：

--创建学生表       
CREATE TABLE student(
 stu_id    NUMBER
,stu_name  VARCHAR2(4000)
,stu_sex   VARCHAR2(6)
,stu_class VARCHAR2(20)
,stu_bith  DATE
,stu_pic   BLOB 
);

SELECT * FROM student;

建议：

创建表的时候，建议把较小的，不为空的字段放在前面，可能为空的放到后面。表名和字段名可以使用中文，但是建议使用英文。一个表最大字段数是254

3.1.2删除表

drop table 表名 [purge];

备注：

加上 purge 是物理删除；

不加 purge 是逻辑删除,就是把表放进回收站(recyclebin)；

在Oracle数据库中，物理删除的表无法通过回收站进行恢复，回收站只能用于恢复被删除的对象（如表、视图、索引等）或者撤销对这些对象的删除操作。如果使用`DROP TABLE`命令对表进行了物理删除，那么该表的数据将被永久删除，无法恢复。

当然，如果在删除表之前启用了Oracle数据库的日志归档功能，那么您可以尝试通过数据库的日志归档来恢复被删除的表。但是，这需要一定的技术知识和经验，并且在某些情况下可能无法完全恢复表的数据。因此，建议在删除表之前先进行备份，以便在需要时可以快速恢复数据。

比如：

--删除学生表
drop table student;

3.1.3查看回收站

--查看回收站
select * from user_recyclebin;

--物理删除学生表1和逻辑删除学生表
drop table stu_1 purge; --物理删除stu_1

drop table stu; --逻辑删除stu_2

select * from stu;


--从回收站闪回删除的表

flashback table 表名 to before drop;

flashback table stu to before drop;

逻辑删除的学生表2是可以从回收站恢复的！！！

3.1.4清空表数据

Oracle数据库中的truncate用法是用于清空一个表中的所有数据，但保留表结构和约束等元数据信息。truncate是直接删除表中的所有行，而不是逐行删除，因此速度更快，但不能回滚操作。

语法：

truncate table 表名;

truncate table stu;

注意：

truncate操作不会改变表的结构，包括列名、数据类型、约束等元数据信息，因为这些信息是表的定义部分，与表中的数据无关。如果需要修改表结构，需要使用alter table语句来实现。

ddl的清空表之后数据无法找回！！！！！！！！！！！！只有dml操作时才能找回！！！

3.1.5修改表

Ⅰ、修改表名

rename 旧表名 to 新表名;

rename stu to stu_1;

Ⅱ、增加列的语法

alter table 表名 add(列1 类型长度,列2 类型长度...)

alter table stu_1 add(都叫啥 varchar2(20))

Ⅲ、修改列的类型

alter table 表名 modify 列名/*(修改后的)*/类型长度

alter table stu_1 modify 我叫啥 number

注意：

同类型修改变长，直接改；

同类型修改变短，不能短于已有数据的最长长度；

不同类型的修改，此列必须为空。

Ⅳ、删除列的语法

alter table 表名 drop column 列名 --删除单个列需要在drop后面加上column

alter table 表名 drop (列名1,列名2...)--删除多个列



alter table stu_1 drop column 帅哥

alter table stu_1 drop (全是帅哥)

Ⅴ、修改列名

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

alter table stu_1 rename column 张三 to 李四;

3.2DDL-视图

在Oracle数据库中，视图（View）是一种虚拟的表，它并不真正存储数据，而是根据查询语句动态生成的结果集。视图可以看作是一种“存储查询结果”的方式，它可以将多个表的数据组合在一起，或者对表中的数据进行过滤、排序、聚合等操作，方便用户进行数据查询和分析。

3.2.1视图语法

视图的创建语法如下：

create [or replace] view 视图名 as 
select语句 [with read only];
--比如：
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

其中，view_name是视图的名称，column1、column2等是视图中的列名，table_name是视图所基于的表名，condition是视图的过滤条件。

注意：

• 如果加上 or replace ,如果视图名已存在,会覆盖之前的视图；
• 如果不加 or replace ,如果视图名已存在,会报错(名称已由现有对象使用),不能创建。
• 如果加上 with read only 是创建只读视图,视图不能被修改；
• 如果不加 with read only 是创建普通视图,视图可以被修改。

实例如下：

--创建视图v_emp_20数据同emp表20部门的数据
create or replace view v_emp_20 as 
select * 
from emp 
where deptno=20;

--查询视图的数据
select * from v_emp_20;

--创建一张视图数据是emp和dept表连接后的数据
create or replace view v_emp_20 as 
select e.*,dname,loc ,d.deptno deptno1 
from emp e
right join dept d 
on e.deptno=d.deptno;

--创建一张只读视图数据是emp表和dept表各自独有的数据
create or replace view v_qwe as 
select e.*,dname,loc,d.deptno deptno1
from emp e
full join dept d
on e.deptno=d.deptno
where e.empno is null or d.deptno is null
with read only;

select * from v_qwe;

--可以用视图定义视图的数据
create or replace view v_1 as 
select * from v_qwe;

select * from v_1;
--如果视图的数据来源消失，视图还在，数据失效

总结：

• ·视图一般以v开头
• ·修改视图相当于修改原表
• ·工作中一般不允许通过视图修改数据

3.2.2视图使用方式

视图的使用方式与表类似，可以通过select语句查询视图中的数据，也可以在其他查询语句中使用视图。例如，我们可以通过以下语句查询名为“sales_view”的视图中的数据：

SELECT * FROM sales_view;

3.2.3视图优缺点

Oracle视图是一种虚拟表，它可以将多个表的数据组合在一起，或者对表中的数据进行过滤、排序、聚合等操作，方便用户进行数据查询和分析。视图的优缺点如下：

Ⅰ、优点：

①简化复杂的查询操作。视图可以将多个表的数据组合在一起，或者对表中的数据进行过滤、排序、聚合等操作，避免用户频繁编写重复的查询语句，提高查询效率。

②提高数据的安全性。视图可以对原始表进行过滤和隐藏，只暴露需要的数据给用户，避免用户误操作或者访问敏感数据。

③降低数据冗余。视图可以将多个表中的数据组合在一起，避免重复存储数据，降低数据冗余。

④提高数据的可读性。视图可以为数据表提供更加友好的名称或者更加容易理解的数据格式，提高数据的可读性。

Ⅱ、缺点：

①视图的性能受到影响。因为视图需要基于查询语句动态生成结果集，所以在查询大量数据或者复杂的查询操作时，视图的性能可能会较差。

②视图无法进行修改、删除等操作。视图只是基于查询语句动态生成的结果集，无法直接对视图进行修改、删除等操作，只能通过修改原始表来实现。

③视图的维护较为困难。如果数据表的结构发生变化，视图也需要相应地进行修改，否则可能会导致查询结果不准确。

④视图可能会造成歧义。如果视图中的列名与其他表或者视图的列名相同，可能会造成歧义，导致查询结果不准确。

总结：

• 优点：不占空间;安全。
• 缺点：性能差;修改限制。

3.2.4删除视图

DROP VIEW 视图名;
DROP VIEW v_ab;

执行此语句后，视图将被永久删除，无法恢复。

3.3DDL-序列

Oracle数据库中的序列是一个对象，它可以生成一系列唯一的数字，用于在表中生成唯一的标识符。序列可以在多个表中使用，每个表都可以使用序列生成唯一的标识符。序列可以自动递增，也可以手动指定初始值和步长。

3.3.1序列的使用方法：

Ⅰ、 创建序列：

使用CREATE SEQUENCE语句创建序列，可以指定序列的名称、初始值、递增步长、最小值、最大值、循环方式等参数。

CREATE SEQUENCE 序列名
[START WITH 数]--从几开始,默认1
[MAXVALUE 数]--到几结束,默认9999999999999999999999999999
[MINVALUE 数]--最小值,默认1(循环之后回到的最小值)
[INCREMENT BY 数]--步长/等差,默认1
[CACHE 数]--缓存值,默认20(缓存值必须小于循环值)
[CYCLE]--是否循环,默认不循环


CREATE SEQUENCE s_8
START WITH 5
MAXVALUE 101
MINVALUE 2
INCREMENT BY 10
CACHE 10
CYCLE

注意：

缓存值cache<=ceil((maxvalue-start with 的值)/abs(increment by 的 步长))

Ⅱ、使用序列：

在INSERT语句中使用序列的NEXTVAL函数来获取序列的下一个值，将其插入表中的某个列中。

序列.nextval --先执行(返回下一个值)

序列.currval --再执行(返回当前值)

Ⅲ、修改序列：

使用ALTER SEQUENCE语句修改序列的属性，如初始值、递增步长等。

Ⅳ、删除序列：

使用DROP SEQUENCE语句删除序列。

DROP SEQUENCE 序列名;
DROP SEQUENCE s_3;

注意：

使用序列插入一张表时,第一次运行会直接从第二个值开始,
需要删除序列然后重新创建插入（由于某种系统漏洞）

3.3.2序列的使用实例

Ⅰ、序列.nextval

--利用序列给表提供主键值
--创建一个表 test9
create table test9(id number,
                   name varchar2(20),
                   hiredate date);

select * from test9;  --查看表结构
create sequence seq_test9;  --创建序列

insert into test9 values(seq_test9.nextval,
                         'SCOTT'||seq_test9.nextval,
                         sysdate+seq_test9.nextval);
--向test9表中插入数据，并记录当前日期

用INSERT语句，将数据插入到名为test9的表中。具体分析如下：

①序列：seq_test9.nextval。

这是一个序列，用于生成唯一的标识符。每次调用seq_test9.nextval函数，都会返回序列的下一个值。

②列1：seq_test9.nextval。

该列的值是通过调用序列seq_test9生成的下一个值，用于作为test9表中的主键或唯一标识符。

③列2：'SCOTT'||seq_test9.nextval。

该列的值是字符串'SCOTT'和序列seq_test9生成的下一个值的组合。例如，如果序列的下一个值是100，则该列的值为'SCOTT100'。

④列3：sysdate+seq_test9.nextval。

该列的值是当前日期加上序列seq_test9生成的下一个值。例如，如果序列的下一个值是100，则该列的值为当前日期加上100天的日期。

通过使用序列生成唯一的标识符，并将其插入到表中的相应列中，可以确保表中的每一行都有唯一的标识符。这样可以帮助提高数据的管理和查询效率。

Ⅱ、序列.currval

--创建一个表包含三个列 empno ename hiredate
--分别向他们插入 1-20 king1-king20
--今天到19天后的日期
create sequence seq_test8; --创建序列
create table emp1 as select empno,ename,hiredate from emp where 1=2;--创建表

insert into emp1 values(seq_test8.currval,
                         'king'||seq_test8.currval,
                         sysdate+seq_test8.nextval-1); --向表中插入数据

select * from emp1;  --查看表

具体分析如下：

①序列：seq_test8.currval。

这是一个序列，用于生成唯一的标识符。seq_test8.currval函数返回序列的当前值，即上一次调用seq_test8.nextval函数生成的值。

②列1：seq_test8.currval。

该列的值是通过调用序列seq_test8生成的当前值，用于作为emp1表中的主键或唯一标识符。

③列2：'king'||seq_test8.currval。

该列的值是字符串'king'和序列seq_test8生成的当前值的组合。例如，如果序列的当前值是100，则该列的值为'king100'。

④列3：sysdate+seq_test8.nextval-1。

该列的值是当前日期加上序列seq_test8生成的下一个值减去1。例如，如果序列的下一个值是100，则该列的值为当前日期加上99天的日期。

通过使用序列生成唯一的标识符，并将其插入到表中的相应列中，可以确保表中的每一行都有唯一的标识符。这样可以帮助提高数据的管理和查询效率。

3.3.3删除序列

删除序列可以使用DROP SEQUENCE语句。具体操作步骤如下：

①在SQL命令行或者Oracle SQL Developer等工具中，使用管理员账号登录数据库。

②执行以下SQL语句，删除指定的序列：

DROP SEQUENCE 序列名;

   其中，序列名是要删除的序列的名称。

③执行完删除语句后，可以使用以下SQL查询语句，确认序列是否已经被删除：

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '序列名';

   如果查询结果为空，则说明序列已经被成功删除。

注意：

删除序列后，与该序列相关联的表的数据也会受到影响。如果在删除序列之前没有备份相关数据，可能会导致数据丢失。因此，在删除序列之前，需要进行充分的备份和确认操作。

3.3.4序列适用场景

①生成唯一的主键或者外键，确保数据库中每一行记录都有唯一的标识符。

②用于分配订单号、发票号等业务流水号，避免重复和冲突。

③在多个事务之间提供同步的计数器。

④在并发系统中，保证每次访问数据都是按照一定的顺序进行的。

⑤在一些应用场景中需要生成一些随机的数字或者字符串，可以使用序列来实现。

总之，序列可以帮助我们在数据库中生成唯一的标识符，避免重复和冲突，提高系统的并发性和可靠性。

3.3.5序列的优缺点

Ⅰ、优点：

①为表提供唯一的自增主键，避免了手动维护主键的繁琐工作。

②序列是独立于表的对象，可以被多个表使用，提高了代码的复用性。

③序列是基于数据库的，保证了数据的唯一性和正确性，避免了重复和冲突。

④序列可以提供高并发性能，多个用户同时请求序列值时也不会出现冲突。

⑤序列可以跨事务使用，保证了数据的一致性。

Ⅱ、缺点：

①序列是数据库对象，会占用一定的系统资源，如果序列数量过多，会影响系统性能。

②序列的值不是连续的，可能会存在空洞，导致一些查询操作变慢。

③序列的生成规则是固定的，无法自定义，不能满足一些特殊需求。

总结：

序列是Oracle数据库中非常有用的对象之一，可以提高数据的管理和查询效率，在实际应用中，需要根据具体情况进行合理的选择和使用。