ORACLE SQL查询语句

ORACLE SQL查询语句

SQL语句分为以下三种类型：
DML：Data Manipulation Language 数据操纵语言
DDL：Data Definition Language 数据定义语言
DCL：Data Control Language 数据控制语言

DML：用于查询与修改数据记录，包括四项SQL语句
insert（新增）:添加数据到数据库中；
update（修改）：修改数据库中的数据；
delete（删除）：删除数据库中的数据；
*select（查询）：选择（查询）数据（主要基础）

insert语句语法
-使用insert语句向表中插入数据。
-使用这种语法一次只能向表中插入一条数据。
-为每一列添加一个新值。
-按 列的默认顺序列出各个列的值。
-在insert子句中随意列出列名和他们的值。
-字符和日期型数据应包含在单引号中。
例：insert into dept(name,salary,id,hire_date)
values(‘lisanwan’,‘888888’,‘1’,‘11-3月-19’)
select * from dept;
在 insert中使用子查询
insert into dept1
select *
from employees
where department_id=90
select * from dept1;
-不必书写 values 子句
-子查询中的值列表应与 insert 子句中的列名对应

update语句语法
-使用update语句更新数据
-可以一次更新多条数据
-使用where子句指定需要更新的数据
例：update dept1
set department_id=100
where department_id =90;
-如果省略where子句，则表中的所有数据都将被更新
在update中使用子查询
在 update 中使用子查询，使更新基于另一个表中的数据。
题目：调整与employee_id 为100的员工job_id相同的员工的department_id为employee_id
为202的员工的department_id
update dept1
set department_id = (select department_id
from employees
where employee_id = 202)
where job_id = (select job_id
from employees
where employee_id = 100);

delete语句语法
-使用delete语句从表中删除数据
-使用where子句删除指定的记录
delete from dept1
where id=5;
-如果省略where子句，则表中的全部数据都将被删除
在delete中使用子查询
在 delete 中使用子查询，使删除基于另一个表中的数据
题目：从emp1表中删除dept1部门名称中含Public字符的部门id
delete from emp1
where department_id = (select department_id
from dept1
where department_name like ‘%Public%’);

select语句语法
基本select语句
select标识选择哪些列。from标识从哪个表中选择。
select department_id, location_id
from departments;

DDL：用于定义数据库的结构，比如创建、修改或删除数据库对象，包括五项SQL语句
*create table：创建数据库表；
*alter table：更改表结构、添加、删除、修改列长度；
*drop table：删除表；
create index：在表上建立索引；
drop index：删除索引

命名规则
表名和列名:
• 必须以字母开头
• 必须在 1–30 个字符之间
• 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle 的保留字

create table语句语法
-必须具备：create table权限，存储空间
-必须指定：表名，列名，数据类型，尺寸
创建表：
例：create table dept --创建表dept
(name varchar2(20),
salary number(8,2)default 1000,
id number(4),
hire_date date);
使用子查询创建表
-使用as subquery 选项，将创建表和插入数据结合起来
-指定的列和子查询中的列要一一对应
-通过列名和默认值定义列
例：create table dept80
as select employee_id,last_name,salary*12 annsal,hire_date
from employees
where department_id = 80

alter table语句语法
-add 追加新的列
-modify 修改现有的列
-为新追加的列定义默认值
-drop column 删除一个列
-rename column 重命名表的一个列名
追加一个新列
alter table dept80
add (job_id varchar2(9));
修改一个列（可以修改列的数据类型，尺寸和默认值。对默认值的修改只影响今后对表的修改）
alter table dept80
modify (last_name varchar2(30));
删除一个列
alter table dept80
drop column job_id;
重命名一个列
alter table dept80
rename column job_id to job2_id;

drop table语句语法
-数据和结构都被删除
-所有正在运行的相关事务被提交
-所有相关索引被删除
-drop table 语句不能回滚
例：drop table dept80;

create index语句语法
创建索引
-在一个或多个列上创建索引
-在表 EMPLOYEES的列 LAST_NAME 上创建索引
例： create index emp_last_name_idx
on employees(last_name);

什么时候创建索引
• 列中数据值分布范围很广
• 列经常在 WHERE 子句或连接条件中出现
• 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引
• 表很小
• 列不经常作为连接条件或出现在WHERE子句中
• 查询的数据大于2%到4%
• 表经常更新

drop index语句语法
删除索引
-使用drop index 命令删除索引
drop index index；
-删除索引UPPER_LAST_NAME_IDX
drop index upper_last_name_idx;
• 只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引
• 删除操作是不可回滚

DCL：用来控制数据库的访问，包括六项SQL语句
grant：授予访问权限；
revoke ：撤销访问权限；
*commit：提交事务处理；
*rollback：事务处理回退；
*savepoint：设置保存点；
lock：对数据库的特定部分进行锁定
grant语句语法
用户的系统权限
-用户创建之后，DBA 会赋予用户一些系统权限
grant 授予
-以应用程序开发者为例，一般具有下列系统权限：
-create session（创建会话）
-create table （创建表）
-create sequence（创建序列）
-create view（创建视图）
-create procedure（创建过程）
赋予系统权限
DBA 可以赋予用户特定的权限
grant create session,
create table,
create sequence,
create view to scott;

revoke语句语法

收回对象权限
-使用revoke语句收回权限
-使用WITH GRANT OPTION 子句所分配的权限同样被收回
例： revoke select, insert
on departments
from scott;

例： revoke select, insert
on departments
to scott
WITH GRANT OPTION;

commit语句语法

commit 和 rollback 语句的优点
-确保数据完整性
-数据改变被提交之前预览
-将逻辑上相关的操作分组

提交数据
-改变数据
delete from dept1
where id=5; --删除表中的id 5
insert into dept1
values(‘ss’,‘100’,3,null) --向表中插入数据
-提交改变
commit；
commit complete.

rollback语句语法

数据回滚后的状态
使用 rollback 语句可使数据变化失效：
-数据改变被取消
-修改前的数据状态被恢复
-锁被释放
delete from dept; --删除所有数据，rollback 回滚
rollback;
rollback complete

savepoint语句语法

回滚到保留点
使用 savepoint 语句在当前事务中创建保存点
使用 rollback to savepoint 语句回滚到创建的保存点
update…
savepoint update_done;
Savepoint created.
insert…
rollback TO update_done;
Rollback complete.

提交或回滚前的数据状态
-改变前的数据状态是可以恢复的
-执行 DML 操作的用户可以通过 select 语句查询之前的修正
-其他用户不能看到当前用户所做的改变，直到当前用户结束事务
-DML 语句所涉及到的行被锁定，其他用户不能操作

提交后的数据状态
-数据的改变已经被保存到数据库中
-改变前的数据已经丢失
-所有用户可以看到结果
-锁被释放，其他用户可以操作涉及到的数据
-所有保存点被释放