Oracle
登录数据库
登录数据库
sqlplus username/password@ip:port/name
如果被锁了登录不上，可以用管理员账户解锁
select LOCK_DATE,username from dba_users where username = 'MYUSERNAME';
alter user MYUSERNAME account unlock;
数据类型
字符型：
char 长度固定的字符串，char(10) 表示最长 10 字节，后面用空格补全，查询速度快，适合长度固定或频繁查找的数据
varchar2 长度可变，varchar2(10) 表示最长 10 字节，省空间
如需设置字符长度而不是字节长度，需要用 char ，例如 char(5 char) varchar2(10 char)
数字型：
number 整数或小数
number(5) 表示5位整数，-99999 到 99999
number(5,2) 表示 5 位有效数，包括 2 位小数，范围 -999.99 到 999.99
日期型：
date 年月日时分秒
timestamp 精确到毫秒
to_date('1999-01-31 23:24:25','yyyy-mm-dd hh24:mi:ss')
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
select DATE '2023-07-07' from dual;
select timestamp '2024-09-05 16:04:59' from dual;
Blob
blob 二进制数据，最大 4 G
表和数据操作
create table tab_name (
column1 tpye1 constraint1,
column2 type2 constraint2
)
示例
create table tab_name (
id number generated by default as identity,
name varchar2(10) not null,
age number(3),
sex char(1),
weight number(7,2),
birthday date
)
添加主键
alter table tab_name add constraint tb_pk primary key (deptno);
添加非空约束
alter table tab_name modify (col1 not null );
从其他表创建表
create table tab2 as select * from tab1;
create table tab2 as select * from tab1 where id < 100;
create table tab2 as select col1, col2 from tab1;
create table tab3 as select col1, col2 from tab1, tab2;
删除表
-- 删除表结构和数据
drop table tab_name;
-- 仅删除数据，保留表结构
truncate table tab_name;
查看表结构
DESC tab_name;
添加字段
-- 添加一列
alter table tab_name add col_name number(2);
-- 添加多列
alter table tab_name add(col1 number(5) not null, col2 varchar2(10));
添加字段是否会锁表？如果这个字段没有约束，没有默认值，不会锁
修改字段长度或类型（修改类型不能有数据）
实测 varchar2 扩长是瞬间的，缩短第一次较慢，1 千万数据 5 秒（不能有数据超长），再次缩短就只有零点几秒。varchar2 可以转 nvarchar ，1 千万数据耗时 300 s，而 nvarchar 无法转 varchar2。
alter table tab_name modify( col_name varchar2(5))
删除字段
alter table tab_name drop column col_name;
alter table tab_name drop (col1, col2);
示例：
create table employee (
id number(5),
name varchar2(20),
phone varchar2(10),
salary number(5),
age number(2));
alter table employee add constraint tb_pk primary key (id);
alter table employee add email varchar2(255) not null;
alter table employee modify salary number(5,2);
alter table employee drop (age, phone);
alter table employee rename column name to full_name;
desc employee;
名称 空值? 类型
------ -------- -------------
ID NOT NULL NUMBER(5)
FULL_NAME VARCHAR2(20)
SALARY NUMBER(5,2)
EMAIL NOT NULL VARCHAR2(255)
修改表名 rename
rename tab_name to tab_name_new；
alter table tab_name rename to tab_name_new;
复制表
insert select 插入数据非常非常快，比直接用代码批量插入快很多
-- 复制表结构和数据
create table tab_name as select * from tab_name_old;
-- 复制表结构
create table tab_name as select * from tab_name_old where 1=2;
-- 复制表数据（表结构一样）
insert into tab_name select * from tab_name_old;
-- 复制表数据（表结构不一样）
insert into tab_name(col1, col2) select col1, col2 from tab_name_old;
删除表
drop table tab_name
获取删除所有表的 sql
SELECT 'DROP table USERNAME.'||table_name||';' FROM all_tables WHERE owner='USERNAME';
插入数据
如果每列都插入数据，可以不指定列名，按顺序插入。如果指定列名，可以插入部分列
在 oracle 中，空字符串视为 null
insert into tab_name values('abc',1, null, 2.2);
insert into tab_name(name, age, weight) values('tom', 7, 11.1);
修改数据
通常需要用 where 语句指定修改的行，不然全部都会被修改
update tab_name set col1 = 'aaa', col2 = 11 where id = 1;
删除数据
通常需要用 where 语句指定删除的行，不然全部都会被删除
delete from tab_name where id = 1;
查询数据
select * from tab_name;
select col1, col2 from tab_name;
查询数据，去除重复行
select distinct col1, col2 from tab_name;
统计数量
select count(*) from tab_name;
可以使用 where 进行条件查询
可以使用 like 进行模糊查询，% 表示 0 个或多个任意字符
select * from tab_name where col1 = 1;
select * from tab_name where col1 is null;
select * from tab_name where col1 is not null;
select * from tab_name where col1 like '%abc%';
select * from tab_name where col1 in (1, 3, 5);
dual 是一个特殊的表，可以用于查询不属于任何表的数据或计算。
select 1+2 from dual;
select sysdate from dual;
查询时可以使用别名
select col1 col1_aliase from tab_name;
select col1 as col1_aliase from tab_name;
表连接
-- 默认是 inner join
select tab1.col1, tab2.col2 from tab1 join tab2 on tab1.id = tab2.id;
-- 内连接（两个表均不含为 null 的列）
select tab1.col1, tab2.col2 from tab1 inner join tab2 on tab1.id = tab2.id;
-- 左外连接（含第二个表为 null 的列）
select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.id = tab2.id;
-- 右外连接（含第一个表为 null 的列）
select tab1.col1, tab2.col2 from tab1 right join tab2 on tab1.id = tab2.id;
-- 全外连接（含两个表为 null 的列）
select tab1.col1, tab2.col2 from tab1 full join tab2 on tab1.id = tab2.id;
事务
commit 提交事务， savepoint 设置保存点， rollback 回滚事务
-- 创建一个保存点
savepoint s1;
update tab_name set col1='aa' where col2='bb';
savepoint s2;
update tab_name set col1='aaa' where col2='bb';
-- 回滚到保存点
rollback to s2;
-- 回滚全部
rollback;
-- 提交事务，（并删除所有保存点）
commit;
merge 语句
可用于将一个或多个源表合并
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
可以用 merge 语句实现 insert into on duplicate key update 语句，插入或更新数据
merge into tab_name m
using (select 1 as id from dual) n
on (m.id = n.id)
when matched then
update set name = 'abc'
when not matched then
insert(id, name) values(1, 'abc')
序列
当需要使用自增 id 时，可以使用 oracle 的序列。在每次插入前从序列获取下一个值。
create sequence seq_name
increment by n --递增，默认1
start with n --开始值，默认1
maxvalue n | nomaxvalue --最大值，默认9999999999999999999999999999
minvalue n | nominvalue --最小值，默认1
cycle | nocycle --是否循环，默认nocycle
cache n | nocache --缓存，默认20
示例：
-- 创建默认序列
CREATE SEQUENCE seq_name;
-- 创建序列
CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1 MAXVALUE 999999999999999999999999999 CACHE 10 ORDER NOCYCLE;
-- 创建循环序列
create sequence seq_name start with 1 increment by 1 maxvalue 5 minvalue 1 cycle nocache;
-- 读取下一个值
select seq_name.nextval from dual;
-- 读取当前值
select seq_name.currval from dual;
-- 删除序列
drop sequence seq_name;
可以通过 trigger 实现自动使用序列
CREATE TRIGGER tab_name_id_trigger BEFORE INSERT ON tab_name FOR EACH ROW
BEGIN
SELECT tab_name_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
获取建序列语句
SELECT 'drop SEQUENCE '||SEQUENCE_NAME|| ';CREATE SEQUENCE '||SEQUENCE_NAME|| ' INCREMENT BY '||INCREMENT_BY ||' START WITH '||LAST_NUMBER||' MAXVALUE '||MAX_VALUE ||' '||(case when CACHE_SIZE=0 then 'NOCACHE' else 'CACHE '||CACHE_SIZE end)||' ORDER NOCYCLE ;' FROM user_SEQUENCES;
查询所有序列
select * from user_sequences;
索引
查看所有索引
select * from user_ind_columns;
有一些索引列以 SYS_NC00 开头，这些是辅组列
创建索引
-- 创建 b-tree 索引
create index idx_name on tab_name(col);
-- 创建位图索引
create bitmap index idx_name on tab_name(col);
-- 创建多列索引
create index idx_name on tab_name(col1, col2);
查询索引信息
select * from user_indexes;
表的统计信息
all_tables user_tables 含有表的统计信息
有一些信息不是实时统计的，可以手动进行统计
-- 统计
analyze table tab_name compute statistics for table;
-- 获取所有表统计的 sql
select 'analyze table ' || table_name || ' compute statistics for table;' from user_tables;
函数
字符串处理函数
字符串拼接可以使用 || ，也可以使用 concat(str1, str2) ，concat 只能拼 2 个字符串，如需拼多个需要反复调用
字符串替换 replace(src, str1, str2) 将 src 中的 str1 替换成 str2
字符串截取 substr(string str, int a, int b) ，截取 str 从 a 开始长度为 b 的字符串，下标从 1 开始， 写 0 也是 1 ， substr(string str, int a) 截取从 a 到末尾
在为空时可以用 nvl(str, replace_with) 函数指定值
获取字符串长度 length(str) 。MySQL 中同名函数表示的是字节数，char_length(str) 才是字符数
select 'a' || 'b' from dual; -- ab
select concat('ab', 'cd') from dual; -- abcd
select replace('abcd', 'b' ,'bb') from dual; -- abbcd
select substr('abcdefg', 2, 3) from dual; -- bcd
select substr('abcdefg', 2) from dual; -- bcdefg
select nvl(col1, 'aa') from dual; -- aa
select length('abc') from dual; -- 3
时间处理函数
to_date(str, pattern) 将字符串转化为 date
to_char(date, pattern) 将 date 转换为字符串
select to_date('2000-01-01 01:01:01', 'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
导入导出
可以使用 exp 和 imp 进行导入导出，无需管理员权限，但需要两个数据库同一个用户名
导出表
rows 是否导出数据，默认 y ，为 n 则只导出表结构
file 导出文件名，默认 expdat.dmp
log 日志文件
buffer 缓冲区字节大小
tables 需要导出的表，逗号分隔
indexes 是否导出索引，默认 y
exp user/password@ip1:1521/db1 file=/path/to/expdat.dmp log=/path/to/exp.log tables=table1,table2 rows=n indexes=y buffer=1000000
rows 是否导出数据，默认 y ，为 n 则只导出表结构
file 导入文件名，默认 expdat.dmp
log 日志文件
buffer 缓冲区字节大小
indexes 是否导出索引，默认 y
commit 每插入一行进行提交，默认 n
imp user/password@ip2:1521/db2 file=/path/to/expdat.dmp log=/path/to/imp.log commit=yes buffer=1000000 indexes=y
查看帮助
> exp help=y
Export: Release 19.0.0.0.0 - Production on Wed Oct 23 16:32:55 2024
Version 19.3.0.0.0
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
...
> imp help=y
...
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input files (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output CONSTRAINTS import constraints (Y)
...