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;