oracle数据库开发常用SQL语句总结

第一章 scott账号

• 启动与关闭服务

net stop OracleServiceOrcl：关闭oracle数据库服务
net start OracleServiceOrcl：开启oracle数据库服务
lsnrctl stop :关闭监听服务
lsnrctl start :开启监听服务 ---- lsnrctl status 查看监听服务的状态

• 清除命令行代码

host cls

• Oracle环境配制

1、sys或者system登录进入系统：
sqlplus sys/orcl as sysdba; | sqlplus system/orcl as sysdba;

2、sys/system以无密码进入系统：
sqlplus sys/nolog as sysdba;

3、普通账号登录：
sqlplus scott/huang; (sqlplus 账号/密码) 

4、切换账号：
conn sys/orcl as sysdba;

5、解锁一个账号并设置密码
解锁:alter user scott account unlock ;
加锁：alter user scott account lock;
修改密码： alter user scott identified by 123;

• Scott账号下的表操作

select * from tab;   查询SCOTT账号下的所有表对象

BONUS:工资表（此表为空表）
DEPT：部门表
EMP:雇员表
SALGRADE:工资等级表

3、SQLPLUS配制
set linesize 400 设置每行显示的字符数; 
set pagesize :40 设置每页显示的行数

• 关系型数据库端口号

Oracle (1521),MySql(3306) ,SQL Server(1433) 、DB2

第二章 升序和排序（SQL）

• 基础语法

DQL：Data Query Language         数据查询语言  - select
DML：Data Manipulation Language  数据操作语言 -insert,update,delete
DCL：Data Control Language 		 数据控制语言   grant| revoke
TCL：Transaction Control Language事务控制语言：commit|rollback
DDL：Data Definition Language 	 数据定义语言  create table|view|index| procedure|......  drop....

• 1、查询与排序

select ename,job,sal,comm from emp;

• 算术表达式

select ename ,(sal+nvl(comm,0))from emp;   nvl是comm为空也计算进去

• 连字运算符

select ename||job from emp;  合并员工姓名和工作

• 设置别名

 select ename as "名字" from emp; （方式一，加上as关键字）
  select ename  "名字" from emp;  （方式一，不加as关键字）

• 去重复行

select distinct deptno from emp;

• 2、过滤和排序数据
• 基本语法

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

• where的使用

select *from emp where deptno=10;
select *from emp where sal > 2000 and deptno =20;

• between … and的使用

select *from emp where sal between 2000 and 3000;   （选择[2000,3000]的数据）
select *from emp where deptno between 10 and 30;   （选择部门编号[10,30]间的数据）

• in条件使用

 select *from emp where empno in (7369,7788,7902); （方式一）
 select *from emp where empno not in (7369,7788,7902); （方式二）
 select *from emp where empno=7369 or empno=7788 or empno=7902; （or的用法同in）  （方式三）

• 模糊查询 like

% 为匹配0个或者多个字符 "_"为匹配任意单个字符
select *from emp where ename like '%A%'; （查询姓名中包含字母'A'所有雇员）
select *from emp where ename like '__A%'; （查询姓名第3个字符为'A'所有雇员）
select *from emp where ename not like '__A%'; （查询姓名第3个字符不为'A'所有雇员）

• NULL 空查值询

select *from emp where comm is null;  （查询佣金为空的雇员）
select *from emp where comm is not null; （查询佣金不为空的雇员）

• 排序功能

select *from emp order by sal asc;  (asc是升序，desc是降序)
select ename,sal,hiredate from emp   order by sal*12 asc;

第3章 单行函数和多表查询

• 字符函数

select ename,
lower('HELLO'), (转为小写字母)
upper('hello'), (转为大写字母)
initcap(ename), (首字母转为大写字母)
concat(ename,sal), (ename和sal拼接)
substr(ename,1,5), (取从第二个字符开始的后五个字符)
length(ename) ,   （获取ename的长度）
lpad(ename,10,'*'),  （从左边填充*字符，直到满足10个字符为止）
rpad(ename,10,'*'),  （从右边填充*字符，直到满足10个字符为止）
replace(ename,'a','tom')  （ename中的a替换成tom）
from emp;

• 数值函数

select
round(12.134,2),   (四舍五入，保留小数后两位)
trunc(123.2342,1), (截断，保留小数后一位)
mod(10,3)         （返回10除于3的余数）
from dual;		  （dual为测试表）

• 日期函数

<sysdate函数可以查看当前日期>
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
<得到结果为：2021-05-17 09:34:28>

select
to_char(next_day(hiredate,'星期日'))， (计算在指定日期之后的下一个"星期日"的日期)
last_day(hiredate),        <得出所在月的最后一天日期>
months_between(sysdate,hiredate)  <得到sysdate和hiredate之间间隔的月数>
from emp;

• 转换函数

三种转换格式：to_char,to_date,to_munber
select to_char(hiredate,'yyyy/mm/dd  hh-mi-ss') from emp;
select to_number('242','999') from dual; (前面的字符不能大于999)
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;

• 通用函数

select nvl(comm,0)from emp; （emp表中comm为空值时用0来代替）
select nvl2(comm,666,0)from emp; (emp表中comm为不为空时返回666，为空则返回0）
select coalesce(comm,0)from emp; (emp表中comm为不为空时返回原来的值，为空则返回0）

• 组函数

<分组查询与组函数是结合在一起的使用的-sum,avg,min,count>
select 
deptno,
sum(sal),  （求sal的和）
avg(sal),  （求sal的平均值）
max(sal),   （求sal的最大值）
min(sal),	（求sal的最小值）
count(*)   （求每个部门的数据，不包含空值）
from emp group by deptno
having sum(sal)>10000;  （having只能用于分组查询，且分组查询中不能用where）

<例2：各个部门中各个工作 的平均工资>
select deptno,job,avg(sal) from emp group by deptno ,job ;

• 命令窗口中修改sql语句

当写错成如下格式时：
select *form emp;

修改方式：
方式一：c /form/from  （之后下一行输出 "/"
方式二：ed （之后下一行输出 "/"

• 多表查询
• 等值查询

<定义：只显示满足条件的记录>
select e.ename,e.job from emp e,dept d where e.deptno=d.deptno;

• 内连接

<定义：多张表通过  相同字段进行匹配，只显示匹配成功的数据>
1.方式一：
select * from emp e ,dept d
   		where e.deptno = d.deptno ;

2.方式二：
select * from emp e
		inner join dept d
		on e.deptno = d.deptno 

• 外连接
• 左外连接

<左外连接：以左表为基准（左表数据全部显示），去匹配右表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)>
1.方式一：oracle独有的   ->理解（+）为加上null
select * from emp e ,dept d
		where e.deptno = d.deptno(+) ;
2.方式二：
select * from emp e
		left outer join dept d
		on e.deptno = d.deptno;

• 右外连接

<右外连接：以右表为基准（右表数据全部显示），去匹配左表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充)>

1.方式一：oracle独有的
select * from emp e ,dept d
where e.deptno(+) = d.deptno ;
2.方式二：
select * from emp e
		right outer join dept d
		on e.deptno = d.deptno;

• 全外连接

<全外连接 = 左外 + 右外连接 - 去重>

select 
d.*,e.empno,e.ename,e.sal
from emp e
full outer join dept d  on d.deptno = e.deptno;

• 拓展层次连接

select level ,empno, ename ,mgr from emp 
	connect by prior  empno=mgr
	start with mgr is null
	order by level ;

第4章 子查询

• 单行子查询与多行子查询

①子查询可以出现的位置：where、select、having、from ；不能写在group by 后面；
②主查询和子查询可以是同一张表也可以不是同一张表；
③子查询可以使用 单行操作符（=,<，>），多行操作符(in);
④子查询中的null ：子查询的结果中不要有NULL！！ (用"is null"不要用"=null")
1.例一：
select *from emp  where sal > (select SAL from emp where ename = 'SCOTT' );


2.例二：分组子查询
select deptno,min(sal) from emp  
group by deptno
having min(sal) < ( select min(sal) from emp where deptno =10  );

3.例三：不同表的子查询
select * from emp where deptno = (select deptno from dept where dname = 'SALES' );

4.例四：多行子查询
select *from emp where sal > (select min(sal) from emp) ;

select * from emp where deptno in
(select deptno from dept where dname = 'SALES' or dname='ACCOUNTING');

第5章 操作数据和管理表 DML

• insert语句

例一：字段名和字段值一一对应
insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(2222,'zhangsan','MANAGER',7788,'19-9月 -88',9998,1000,10);

例二：只填字段值
insert into emp
values(2223,'zhangsan','MANAGER',7788,'19-9月 -88',9998,1000,10);

例三：填入部分字段名和字段值
insert into emp(EMPNO,ENAME,JOB)
values(2224,'zhangsan','MANAGER');

例四：动态插入
insert into emp(EMPNO,ENAME,&otherJob)
values(&empno,'zhangsan','MANAGER');

• 拓展：批量插入数据

方式一：创建新表(批量插入之前不存在)，将全部emp数据复制到mytab表中
create table mytab 
as
select *from emp;

方式二：将全部emp数据复制到mytab2表中
create table mytab2 
as
select empno,ename,job,sal,comm from emp where sal>3000;

方式三：快速创建表mytab3的与emp表相同的结构，但不包含emp中的数据
create table mytab4 
as
select *from emp where 1=0 ;

方式四：在旧的表格中插入数据
insert into mytab4(empno,ename,sal)
select empno,ename ,sal from emp;

方式五：使用begin...and 方式同时插入多条数据
begin
	insert into emp
    values(1221,'LISI','MANAGER',7788,'19-9月 -88',9998,1000,10);
	insert into emp
    values(1223,'LISI','MANAGER',7788,'19-9月 -88',9998,1000,10);
end;

• delete语句

方式一：使用delete 删除，不会释放空间，是碎片化，可回退
delete from emp  ;

<处理碎片化的方式：>
1.alter table 表名 move ;  2.导出导入

方式二：使用truncate 清空表，不是碎片化，不可回退，效率更高
truncate table emp  ;

• 测试sql语句时间

开启时间：
set timing on
关闭时间：
set timing off

• update语句

例1：一般要添加上where条件来判断更新的位置，不然整个表对应字段都会全部更新
update mytab set ename='x',job ='y' where empno in(2222,2223);

• 事务

1.定义：是一个封装系列操作工作单 元，此工作单 元中的所有操作一起提交到数据库中执行；执行的结果要么都成功，要么失败；
2.事务特点：原子性、一致性、隔离性、持 久性

commit : 提交
set commit on: 开启自动提交
set commit off: 关闭自动提交
rollback: 回退

• DDL对表的操作，包含create、drop、truncate、alter

• 创建表操作

create table mytab6 
(
   id  number ,
   name varchar(10),
   age number
);

• 创建表的注意事项

1.权限和空间问题
2.表名的规定：
	a.必须以字母开头
	b.表名只能包含： 大小写字母、数字、_、$、#
	c.长度  1-30个字符
	d.不能与数据库中其他对象重名(表，视图、索引、触发器、存储过程....)
	e.不能与 保留字重名
	查看保留字：DBA账户
	sqlplus / as sysdba
	查看保留字:
		select *from v$reserved_words order by keyword asc ;

	设置显示的某个字段宽度：
	1.字符：col KEYWORD for a10
	2.数字：col  LENGTH for 9999

• 修改表的5种操作

1.在原表上添加新列
alter table mytab6 add myother varchar2(10) ;

2.修改列的长度
alter table mytab6 modify  myother varchar2(20) ;

3.修改列的类型
alter table mytab6 modify  myother number ;

4. 删除列
alter table mytab6 drop column myother2 ;

5.重命名列
alter table mytab6 rename column myother to myother3 ;

6.修改表注意事项：
blob和clob不能修改 ，想要修改必须先删除此列，重新追加
alter table mytab6 add myother2 blob ; （添加后，对其再修改会不成功）

• 删除表操作

方式一：会放在了回收站
drop table mytab6; 
 
方式二：删除表 并清空
drop table mytab6 purge ;

• 关于回收站的操作

1.查看回收站：		
show recyclebin;

2.清空回收站
purge recyclebin;

• 关于rownum、rowid

<rowid：根据插入的顺序依次递增，共18位>
<rownum：逻辑伪列,即不同的sql语句执行时会重新排序>

1.例一：
select rowid,rownum,e.*from emp e;

2.例二：用来删除重复数据
delete from mystudent where rowid not in (select min(rowid) from mystudent   group by stuno);

• 表约束

• 常见的6个约束

检查约束（check）           如设定：name > 4 
唯一约束（Unique）          id唯一也可以为null如1  2  3 4  null 
主键约束（Primary key）     类似唯一约束(唯一) 但是不能为null
外键约束（Foreign Key）     两张表 学生表   课程表(1 2 3)
非空约束(Not null)          不能为null
默认约束（Default）         设置默认值，如：adress:西安

• 主键和唯一的区别

唯一约束和主键约束都是唯一值，但唯一约束可以为null而主键约束不能为null

• 列级约束

注意事项：
	a.报错：违反唯一约束条件时可能主键报错也可能唯一约束报错
	b.如果有多个约束，default必须放在第一位
	c.check的编写和使用where完全相同
	d.唯一约束：可以是Null,但不适用于Null(可以有多个null)

create table student(
 stuno number(3) primary key  ,
 stuname varchar2(10) not null unique ,
 stuaddress varchar2(20) default '陕西' check(length(stuaddress)>2),
 stubid number(3)
);

• 约束命名规范

规范：约束类型_字段名
	1.主键：		PK_stuno
	2.检查约束：  CK_字段名
	3.唯一约束：  UQ_字段名
	4.非空约束：  NN_字段名
	5.外键约束：  FK_子表_父表
	6.默认约束： 一般不需要命名

例子：
create table student(
	 stuno number(3) constraint PK_stuno    primary key  ,
	 stuname varchar2(10) constraint NN_stuname  not null  constraint  UQ_stuname  unique ,
	 stuaddress varchar2(20) default '陕西' constraint CK_stuaddress  check(length(stuaddress)>2),
	 stubid number(3)
);

• 表级约束

create table student2(
	 stuno number(3) ,
	 stuname varchar2(10)  ,
	 stuaddress varchar2(20) ,
	 stubid number(3),
	 constraint PK_sno primary key(stuno) ,
	 constraint UQ_sname_subid unique(stuname,stubid),
	 constraint CK_saddress check( length(stuAddress)>2)
);

• 视图

• 创建视图语法

语法：
create  view 视图名
as 
	select ...语句

例子：
create  view  myempview
as 
	select d.deptno 部门编号,e.empno,e.ename,e.sal,e.comm ,d.dname from emp e ,dept d where e.deptno=d.deptno and  d.deptno=20; 

• 视图授权

通过sys 授予scott 创建视图的权限。
	撤销revoke create view  from scott;
	创建grant  xxxx to scott

• 修改视图

update myempview set ename = 'hello' where  empno=7934 ;

• 修改视图约束

1.with check option :限制对视图操作时，必须满足where子句
例如：
create view myempview
as
select empno ,ename ,deptno from emp where deptno =20   with check option;

2.with read only ：限定视图只能查看不能修改
例如：
create view myempview
as
select empno ,ename ,deptno from emp where deptno =20   with read only ;

• 事务

• 四大特性:ACID

Atomicity原子性：要么都成功，要么都失败。
Consistency:一致性 :事务执行前后 ，总量保持一致
Isolation：隔离性:各个事务并发执行时，彼此独立
Durability：持久性：持久化操作。

• 自动提交事务

set autocommit on：开启
set autocommit off ：关闭

• 事务的隔离级别

1.脏读：当一个事务正在访问数据，并对此数据进行了修改（1->2），但是这种修改【还没有提交到数据库(commit)】； 此时，另一个事务也在访问这个数据 。本质：某个事务（客户端）读取到的数据是过时的。

2.不可重复读： 在一个事务内（客户端）内，多次读取同一个数据，但结果不同。
			 本质：就是事务A拿到了 被其他事务B修改并提交后的数据 

3.幻读（虚读）：在一个事务内（客户端）内，多次读取同一批数据，但结果不同。