Oracle数据库常用sql

oracle数据库

1 简介

1.1 目前主流的关系型数据库

Oracle，IBM DB2，SQL/Server，MySQL，SyBase，IBM Informix

1.2 E-R模型（Entry-Relation）

E-R模型三要素：实体，关系，属性
实体间联系（1:1）（1:n）（n:m）

1.3Oracle用户

scott/tiger 普通用户
system/system 普通管理员
sys/sys 超级管理员

1.4 sql

结构化查询语言（Structured Query Language）
DML语句（数据操作语言）Insert，Update，Delete，Merge
DDL语句（数据定义语言）Create，Alter，Drop，Truruncate
DCL语句（数据控制语言）Grant，Revoke
事务控制语言 Commit，Rollback，Savepoint

2 sql入门

2.1 用户基本操作

2.1.1 建表空间、用户、分配权限

 CREATE TABLESPACE space_name DATAFILE 'space_name' SIZE 20 M AUTOEXTEND ON NEXT 2 M MAXSIZE UNLIMITED;
 create user user_name identified by pass default tablespace space_name ;
 grant connect,resource,dba to user_name;

2.1.2 查看用户和表空间

select username,default_tablespace from dba_users;

2.1.3 查看用户状态信息

select account_status,lock_date,profile from dba_users where username='TEST';

2.1.4 解锁

alter user test account unlock;

2.1.5 删除用户和表空间

drop user zwgk cascade;
drop tablespace zwgk including contents and datafiles;

2.1.6 查锁表，解决死锁

 select SESS.sid, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME,
      AO.OBJECT_NAME, LO.LOCKED_MODE
from V$LOCKED_OBJECT LO, DBA_OBJECTs AO, V$SESSION SESS
where AO.OBJECT_ID = LO.OBJECT_ID 
and lo.session_id = sess.sid;

alter system kill session 'sid,serial#'; //sid,serial#是上面查询出来的

2.2 单行函数

2.2.1 字符函数

upper，Lower，Initcap，Concat，Substr，length，replace，lpad，rpad，trim

select instr('Hello world','or') from dual;//输出 8

2.2.2 数值函数

round，mod，trunc

select trunc(412.13,-2) from dual;//输出 400

2.2.3 日期函数

months_between()，add_months()，next_day()，last_day()，

select next_day(sysdate,'星期一') from dual;
select last_day(sysdate) from dual;

2.2.4 转换函数

to_char，to_number，

select to_char(sysdate,'yyyy-mm-dd') from dual;//输出 2021-08-08
select to_char(sysdate,'fmyyyy-mm-dd') from dual;//输出 2021-8-8
 select to_date('20210808','yyyyMMdd') from dual;//输出 08-8月 -21

2.2.5 通用函数

nvl()，nullif()，nvl2()，coalesce()，case表达式，decode()，

2.3 分组函数

2.3.1 count，avg，max，min，sum，group by，having子句

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

2.4 多表查询

2.4.1 笛卡尔集

select * from emp,dept;

2.4.2 等值连接、非等值连接、自连接

2.4.3 左外连接、右外连接、满外连接

select empno,ename,dname from emp left outer join dept on emp.deptno=dept.deptno;

2.4.4 集合

union：并集，所有的内容都查询，重复的只显示一次；
union all：并集 ，所有内容都显示，包括重复的；
intersect：交集，只显示重复的；
minus：差集，只显示对方没有的，跟顺序有关系。

2.5 子查询