1、建表
create table sys_user (
user_id number(20) CONSTRAINT pk_employee PRIMARY KEY not null,
dept_id number(20) default null,
user_name varchar2(30) not null,
nick_name varchar2(30) default '',
user_type varchar2(2) default '00',
email varchar2(50) default '',
phonenumber varchar2(11) default '',
sex char(1) default '0',
avatar varchar2(100) default '',
password varchar2(100) default '',
status char(1) default '0',
del_flag char(1) default '0',
login_ip varchar2(128) default '',
login_date date,
create_by varchar2(64),
create_time date,
update_by varchar2(64) default '',
update_time date,
remark varchar2(500) default ''
);
# 主键自增序列
create sequence seq_sys_user
increment by 1
start with 100
nomaxvalue
nominvalue
cache 20;
1.1、主键序列
-- emp表， empno的自增序列：
create sequence seq_on_scott_emp_empno
increment by 1 -- 每次加几个
start with 8000 -- 从几开始
nomaxvalue -- 不设置最大值
nocycle -- 一直累加，不循环
nocache;
-- 记得提交：
commit;
-- currval 当前序列；nextval 下一个序列
select seq_on_scott_emp_empno.currval from dual;
-- dept表， deptno的自增序列：
create sequence seq_on_scott_dept_deptno
-- 每次加几个
increment by 10
-- 从几开始
start with 50
-- [{MAXVALUE/ MINVALUE n| NOMAXVALUE 不设置最大值}]
maxvalue 100
-- [{CYCLE | NOCYCLE 一直累加，不循环}]
NOCYCLE
-- [{CACHE n| NOCACHE}]
nocache;
-----
create sequence seq_on_scott_dept_deptno
increment by 10
start with 50
maxvalue 9999
NOCYCLE
nocache;
1.2、约束
-- 外键：
constraint fk_user_txt_user_id foreign key (txt_userid) references tb_user111(userid);
-- 主键
ALTER TABLE tb_user ADD CONSTRAINT pk_user_userid PRIMARY KEY (userid);
-- check
ALTER TABLE tb_user ck_user_username CHECK ( length( username ) BETWEEN 4 AND 20 );
-- 唯一
ALTER TABLE tb_user ADD CONSTRAINT uq_user_email UNIQUE ( email )
-- 非空
ALTER TABLE tb_user modify ( username CONSTRAINT nn_user_username NOT NULL );
-- 默认值
ALTER TABLE tb_user modify ( age DEFAULT ( 18 ) );
-- 外键
ALTER TABLE "SCOTT"."TB_TXT" ADD CONSTRAINT "SYS_C0011117" FOREIGN KEY ("TXT_USERID") REFERENCES "SCOTT"."TB_USER" ("USERID") ON DELETE SET NULL;
-- 删除约束
ALTER TABLE tb_user DROP constraint uq_user_email cascade;
2、字段属性
| 类型 | |
|---|---|
| varchar2(size) | 可变长度字符串，size=1~4000，必须指定size值。 |
| char(size) | 固定长度字符串，size=1~2000。 |
| long | 可变长字符，长度2G个字节。 |
| number(p,s) | 整数：number(5) 小数：number(7,2)，有效位数7位，整数5位，小数2位（p=1~38，s=-84~127）。 |
| date | 有效日期：公元前4712年1月1日，到公元后4712年1月1日。 |
| clob | 一个字符大型对象，可容纳单个字节的字符，不支持宽度不等的字符集，最大4G字节。 |
| nclob | 一个字符大型对象，可容纳单个字节的字符，不支持宽度不等的字符集，最大4G字节（存储国家字符集）。 |
| blob | 一个二进制大型对象，最大4G字节。 |
3、函数
3.1、字符串函数
-- 有空值
nvl(ename,'')
-- 字符串拼接
select ename, ename||'职位是'||job from emp;
SELECT ename,concat( concat( ename, '职位是' ), job ) 描述 FROM emp;
-- 字符串截取、替换
replace(str,old,new)
substr(str,start,length)
-- 查询
instr( 'helloworld', 'l' )
-- 这4表示起始位置; 这2表示字符若重复，查找第2个字符
instr( 'helloworld', 'l', 4, 2 )
-- 字符串转数字
to_number('11'+1)
3.2、日期函数
-- 系统当前时间：
sysdate
current_date
-- 系统最后一天：
sysdate+1
-- 3个月后转正时间：
add_months(hiredate,3)
-- 当前时间最后一天：
last_day(sysdate)
-- 当前时间最后一天：
last_day(sysdate)-- 查询入职时间到现在共几个月：
SELECT sysdate,hiredate,months_between ( sysdate, hiredate ) FROM emp;
-- 查询当前时间，到下周周一的时间：
select sysdate,next_day(sysdate,'Monday') from dual;
-- 查询入职时间大于1985年的
SELECT empno FROM emp WHERE HIREDATE > TO_DATE( '1985', 'yyyy' )
3.3、日期 | 字符串互转
-- publish_time使用NUMBER(15) 1577145600
select to_char(unix_to_date(publish_time), 'YYYY-MM-DD') from assi_sys_notice;
-- 日期转字符：
select hiredate,to_char(hiredate, 'yyyy/mm/dd') from emp;
-- 中文需要加上 "" 双引号
SELECT hiredate,to_char ( hiredate, 'yyyy"年"mm"月"dd"日"' ) FROM emp;
-- 字符转日期：
SELECT to_date ('1996-04-14', 'yyyy/mm/dd') FROM DUAL;
4、更新数据MERGE INTO
查询目标表和原表中是否有相同的数据，有就修改，没有就新增。
merge into 目标表 using 原表或者select查询的表 on 匹配相同字段如id，when matched then 有相同，when not matched then 没有相同。
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN
INSERT (t.id, t.col1, t.col2) VALUES (s.id, s.col1, s.col2);
-- scott数据：
MERGE INTO emp e1
USING (SELECT * FROM emp WHERE empno = 8000) e2
ON (e1.empno = e2.empno)
WHEN MATCHED THEN
UPDATE
SET e1.ename = e2.ename
,e1.hiredate = to_date(to_char(SYSDATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')
WHEN NOT MATCHED THEN
INSERT (e1.empno,e1.ename,e1.job,e1.mgr,e1.sal,e1.comm)
VALUES (e2.empno,e2.ename,e2.job,e2.mgr,e2.sal,e2.comm)
5、分页ROWNUM
oracle使用rownum分页，但是rownum不支持直接使用大于号。
-- rownum: 到第6行， rw：从第1行开始
SELECT *
FROM ( SELECT ROWNUM rw, a.* FROM ( SELECT * FROM emp ) a WHERE ROWNUM <= 6 ) b
WHERE b.rw > 0;
-- 表连接分页
SELECT * FROM
(SELECT ROWNUM rw, a.* FROM
( SELECT e.*, d.dname FROM emp e LEFT JOIN dept d ON e.DEPTNO = d.deptno ORDER BY e.empno )
a WHERE ROWNUM <= 10)
b WHERE b.rw > 5;
6、系统 | 用户 | 表空间管理
sysdba系统管理员：sys/system/sysdba。
普通用户：normal。
sqlplus sys/12345678 AS sysdba
-- 只进入sql服务端，不连接用户：
sqlplus /nologin
-- 查看当前用户：
SHOW USER;
-- 切换账号：
conn scott/123456;
conn 用户名/密码 AS sysdba;
-- 修改密码：
ALTER USER liubin IDENTIFIED BY 123456;
-- 账号解锁：
ALTER USER liubin account unlock;
表空间
是数据的逻辑划分。
永久表空间：
一个表空间只能属于一个数据库，所有的数据库，所有数据库对象都存放在指定的表空间中。
临时表空间：
主要来做查询和存放一些缓冲区数据；
临时表空间消耗的主要原因是：需要对查询的中间结果进行排序，重启数据库可以释放临时表空间。
-- 需要先登录上，system或sys
-- size 当空间存满了，以10M为单位，进行扩展
-- 永久表空间物理文件位置，系统的物理位置
create tablespace 永久表空间名称 datafile '永久表空间物理文件位置' size 15M autoextend on next 10M permanent online;
CREATE tablespace DATA datafile '/oracleData/oracle/oradata/orcl/DATA.DBF' SIZE 15M autoextend ON NEXT 10M permanent ONLINE;
-- 创建用户分配表空间：
create user jc identified by 123456 default tablespace DATA temporary tablespace TEMP profile DEFAULT password expire;
-- 授权：
GRANT dba TO jc;
ALTER USER jc IDENTIFIED BY 123456;
-- 创建用户分配表空间：
CREATE tablespace DATA datafile '/oracleData/oracle/oradata/orcl/DATA.DBF' SIZE 15M autoextend ON NEXT 10M permanent ONLINE;
启动数据库
## 启动数据库实例（管理员权限下）：
## 启动，不加载数据库
startup nomount
## 启动实例，加载数据并保持数据库关闭状态。
startup mount
## 启动实例，加载并打开数据库（默认）
startup open
## 终止实例，重启数据库
startup force
## 关闭数据库实例：
## 等待所用用户退出，正常关闭数据库。
shutdown normal
## 当前所有华东事务被提交后，关闭数据库。
shutdown transactional
## 不等待连接的所有用户退出，强行回滚当前所有事务，断开所有连接用户
shutdown immediate
## 终止，关闭数据库
shutdown abort
