Oracle常用SQL语句

SQL对大小写不敏感
DDL(Data Definition Language 数据定义语言)如 CREATE, ALTER, DROP;
DQL(Data Query Language 数据查询语言)如 SELECT;
DML(Data Manipulation Language 数据操作语言)如 INSERT, UPDATE, DELETE;
DCL(Data Control Language 数据控制语言)如 GRANT, REVOKE;

1. DDL数据定义

-- 创建用户
create user testuser identified by password;

-- 删除用户
drop user testuser cascade;

-- 创建表空间
create tablespace user_space datafile '/opt/oracle/datafile/mytablespace.dbf' size 1G;

-- 创建临时表空间
create tablespace user_temp tempfile '/opt/oracle/datafile/mytablespace.dbf' size 1G;

-- 删除表空间(无数据)
drop tablespace user_space;

-- 删除表空间(包含数据)
drop tablespace user_space including contents and datafiles;

-- 创建表
create table mytable (
    userid number(11) primary key,
    username varchar(30) not null,
    userstate number(1) default 0
)
tablespace user_space;

-- 创建索引
create index idx_mytable_username on mytable (username) table space idx_space;

-- 删除索引
drop index idx_mytable_username;

-- 新增字段
alter table mytable add updatetime VARCHAR(14) not null;

-- 删除字段
alter table mytable drop updatetime;

-- 删除表
drop table mytable;

-- 清空表中数据
truncate table mytable;

-- 复制表结构和数据
create table mytable_bak as select * from mytable;

-- 复制表结构
create table mytable_bak as select * from mytable where 1=2;

-- 表重命名
alter table old_table_name rename to new_table_name;

2. DQL数据查询

-- 查询
select * from mytable;
select t.username from mytable t where t.userid='123';

-- 并行查询
select /*+ parallel(t,8) nologging */ count(*) from mytable t;

-- 分区查询
select count(*) from mytable partition(PART_01);

-- 指定别名
select t.username as name from mytable t;

-- 统计数量
select count(1) from mytable;
select count(*) from mytable;

-- 查询一行
select * from mytable where rownum<=1;

-- 正序 asc | 逆序 desc
select t.username,t.updatetime from mytable t order by t.updatetime desc;

-- 去重 distinc
select distinc t.userstate from mytable t;

-- 分组计数
select count(1),t.userstate from mytable t group by t.userstate;

-- 条件查询
-- in
select * from mytable where username in ('test1','test2');

-- like
-- 1. '%'：表示0个或多个字符
select * from mytable where username like '%test%';
-- 2. '_'：表示任意单个字符
select * from mytable where username like '_test_';
-- 3. '[]'：类似正则表达式，指定字符, 字符串或范围
select * from mytable where username like 'test[0-9]';
-- 4. '[^]'：匹配对象为指定字符以外的任一个字符
select * from mytable where username like '[^test]';

3. DML数据操作

-- 插入
insert into mytable (userid, username, userstate) values (12345,test,0);
commit;

-- 复制表数据(表结构相同)
insert into mytable_bak select * from mytable;

-- 更新
update mytable set userstate='1' where id='12345';
commit;
-- 手动更新
select * from mytable where id='12345' for update;

-- 删除
delete from mytable where userstate='1';
commit;

4. DCL数据控制

-- 赋权限
grant dba to testuser;

-- 收回权限
revoke dba from testuser;