oracle数据库常用SQL语句

转载注明出处:https://www.cnblogs.com/zdf159/p/9635495.html 

SQL DML 和 DDL两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

-----------------------------------------------DML
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分：
select - 从数据库表中获取数据
update - 更新数据库表中的数据
delete - 从数据库表中删除数据
insert into - 向数据库表中插入数据

查询(DISTINCT--去重)
select 列名称 from 表名称
select distinct 列名称 from 表名称
select 列名称 from 表名称 where 列 运算符 值
添加
insert into 表名称 (列1, 列2,...) values (值1, 值2,....)
修改
update 表名称 SET 列名称 = 新值 where 列名称 = 值
删除
delete from 表名称 where 列名称 = 值

---------where条件

通配符 描述
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符

is null 为空条件
is not null 不为空条件

比较运算符
= 等于 != <> 不等于

逻辑运算符
优先级 比较 > not > and >or

模糊查询
like '%%'

顺序(空值最大)
升序 order by 列名称
降序 order by 列名称 desc

SQL In
select * from 表名称 where 列名称 in ('值1','值2')

SQL Between
select * from 表名称 where 列名称 between '值1' AND '值2'

SQL Alias
select 列名称 as 别名 from 表名称

----------------------------------------------------DDL
SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，
以及施加表间的约束。

SQL 中最重要的 DDL 语句:
create alter drop truncate(清空)

数据库
create database 数据库名- 创建新数据库
drop database 数据库名- 删除数据库

用户
create user 用户名 identified by 密码 -创建用户
alter user 用户名 identified by 密码 -修改密码
create user 用户名 profile default
identified by 用户名 default tablespace 表空间名称
account unlock;
grant connect to 用户名;--赋权
grant dba to 用户名;
revoke --撤权

表
create table 表名称 - 创建新表
alter table 表名称 - 变更（改变）数据库表
alert table 表名称 add 列名称 列类型 -创建新列
alert table 表名称 drop colnmn 列名称 - 删除列
alert table 表名称 drop (列名称) - 删除列
alter 表名称 modify 列名称 修改后的列类型 -修改列类型
alert table 表名称 alert column 列名称 列类型 -修改列类型
alter table 原表名称 rename to 新表名称 -修改表名
drop table 表名称 - 删除表
truncate table 表名称- 清空表
alter table 表名 move tablespace 表空间名称 -把表移植到别的表空间
flashback table 表名称 to before drop; -利用归档日志闪回技术恢复表
alter table 表名称 read only; -表处于只读转态 read write 可写状态

索引
create index 索引名称 on 表名称 (列名称) 不唯一索引
create unique index 索引名称 on 表名称 (列名称 desc(降序)) 唯一索引
drop index 索引名称 on 表名称(SQLJet Access)
drop index 表名称.索引名称(MS SQL Server )
drop index 索引名称(IBM DB2 和 Oracle)
alert table 表名称 drop index 索引名称(MySQL)

视图
create view 视图名称 as SQL语句 with read only (只读权限)
drop view 视图名称

同义词
create public synonym 同义词名称 for 表名称 -创建公有同义词
drop public synonym 同义词名称 -删除公有同义词
create synonym 同义词名称 for 表名称 -创建私有同义词
drop synonym 同义词名称 -删除私有同义词

序列
create sequence 序列名称
start whih 开始于几
increment by 增量于几
minvalue 最小为几
maxvalue 最大为几
cache 预分配为几 预分配个数存储在内存中,一般为20
cycle 循环复位;

创建表空间
CREATE TABLESPACE 表空间名称
DATAFILE 'E:/表空间名称/表空间名称.ora' SIZE 10M --文件放在oralce安装目录下
autoextend on next 20M maxsize 50M
UNIFORM SIZE 128k;

创建临时表空间
CREATE TEMPORARY TABLESPACE 表空间名称
TEMPFILE 'D:/表空间名称/表空间名称..dbf'
SIZE 512M
AUTOEXTEND ON
NEXT 100M MAXSIZE 2048M;

删除表空间
DROP TABLESPACE 表空间名称 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE 表空间名称 INCLUDING CONTENTS CASCADE CONSTRAINTS;

重命名表空间
alter tablespace tbs_name rename to tbs_name_new;

---------constraints约束
not null 不为空
unique 约束唯一标识数据库表中的每条记录
primary key 主键
foreign key 外键
check 特定的列中对值进行限制
default 约束用于向列中插入默认值
auto_increment 自动增长

create 表名称
(
Id_O int primary key,
Id_P int foreign key references 主表(主键),
Id_I int default 1,
check (Id_O>0)
)
create 主表
(
主键 int not null auto_increment,
primary key (主键) --MySQL
主键 int primary key identity --SQL Server
主键 int primary key autoincrement --Access
)

-------------------函数
avg()函数返回数值列的平均值
count() 函数返回匹配指定条件的行数
first() 函数返回指定的字段中第一个记录的值
last() 函数返回指定的字段中最后一个记录的值
max()函数返回一列中的最大值
min()函数返回一列中的最小值
sum()函数返回数值列的总数
ucase()函数返回值为大写
lcase()函数返回值为小写
len() 函数返回文本字段中值的长度
now() 函数返回当前的日期和时间

MID(column_name,start[,length]) 截取字符串
column_name 必需。要提取字符的字段
start 必需。规定开始位置（起始值是 1）
length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本

isnull()空值函数
isnull(列名称,0)--SQL Server / MS Access
nvl(列名称,0)--Oracle
ifnull(列名称,0)--MySQL
coalesce(列名称,0)

round(column_name,decimals) 数值字四舍五入
column_name 必需。要舍入的字段
decimals 必需。规定要返回的小数位数

format(column_name,format)
column_name 必需。要格式化的字段。
format 必需。规定格式。

group by 函数
select * from 表名称 group by 列名称1，列名称2

having 函数
select * from 表名称 group by 列名称1 having sum(列名称2)<值

case when 函数
case
when 条件1 then 返回值1
when 条件2 then 返回值2
else 返回值3 (无此信息，返回null)
end

编写顺序:select...from...where...group by...having...order by...
执行顺序:where>group by>having>select>order by

-------------------前N条 前N%条:N PERCENT
orcle: select * from 表名称 where rownum<=N
sql server: select top N * from 表名称
mysql: select * from 表名称 limit N
db2: select * from 表名称 fetch frist N rows only

 

-------------------连接语句:SQL JOIN

INNER JOIN:（内连接）select * from table1 a inner join table2 b on a.id = b.id
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

 

-------------------SQL UNION 操作符
UNION 操作符用于合并两个或多个 select 语句的结果集。
请注意，UNION 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
同时，每条 select 语句中的列的顺序必须相同。

select 列名称 from 表名称1 union select 列名称 from 表名称2
select 列名称 from 表名称1 union all select 列名称 from 表名称2

 

-------------------SQL SELECT INTO语句
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

1、SQL SELECT INTO 语法

您可以把所有的列插入新表：
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
或者只把希望的列插入新表：
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

2、SQL SELECT INTO 实例 - 制作备份复件

下面的例子会制作 "Persons" 表的备份复件：
SELECT * INTO Persons_backup FROM Persons
IN 子句可用于向另一个数据库中拷贝表：
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：
SELECT LastName,FirstName INTO Persons_backup FROM Persons

3、SQL SELECT INTO 实例 - 带有 WHERE 子句

我们也可以添加 WHERE 子句。
下面的例子通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：
SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'

4、SQL SELECT INTO 实例 - 被连接的表

从一个以上的表中选取数据也是可以做到的。
下面的例子会创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息：
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

 

-------------------备份数据:into(从table向table1备份 IN 子句可用于向des数据库中拷贝table表)
select * into table1 from 表名称

select * into 表名称 in des from 表名称

 

-------------------数据库密码无效
alter user 数据库名 identified by 密码
SELECT username,PROFILE FROM dba_users;
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

 

-------------------连接数据库

1、查询oracle的连接数
select count(*) from v$session;
2、查询oracle的并发连接数
select count(*) from v$session where status='ACTIVE';
3、查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
4、查看所有用户：
select * from all_users;
5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
6、查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
7、查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
8、查看所有角色：
select * from dba_roles;
9、查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
10、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;

修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;

查看游标数量
Select * from v$open_cursor Where user_name=''

查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
或者：show parameter processes;

查询数据库允许的最大游标数：
select value from v$parameter where name = 'open_cursors'

查看oracle版本
select banner from sys.v_$version;
按降序显示用户"SYSTEM"为每个会话打开的游标数
select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'SYSTEM' and o.sid=s.sid
group by o.sid, osuser, machine order by num_curs desc;

字符串转换
to_char(to_date(a.customfield5,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss')

 

查询表空间情况
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

 

查询容量大于1G的数据段
select segment_name,sum(bytes)/1024/1024 from dba_segments group by segment_name having sum(bytes)/1024/1024>1000;
LOB段查询该该lob段属于哪个表
select table_name,segment_name from dba_lobs where segment_name='SYS_LOB0000136091C00003$$';