Oracle/PL/SQL数据库基础操作(持续更新)

前言

    PL/SQL不是一个独立的编程语言；它是Oracle编程环境中的工具。 SQL* Plus是一个互动的工具，它可以在命令提示符下键入SQL和PL/SQL语句。这些命令发送到数据库进行处理。语句处理之后将结果发回，并在屏幕上显示出来。

分类命令DDLcreate：创建；drop：删除；alter：修改；rename：重命名; truncate：截断；DMLinsert：插入；delete：删除；update：更新；select：查询;DCLgrant：授权；revoke：回收权利；commit：提交； rollback：回滚事务；

一、基本操作

1.数据库操作

登录方式：第一种：命令行输入sqlplus再输入用户名和密码连接数据库；

              第二种：命令行输入sqlplus/NOLOG，再输入conn 用户名/密码 as sysdba;

可以输入show user查看当前用户(下图显示为SYS即为超级用户，超级用户拥有所有权限);

若不是SYS超级用户，可输入conn as sysdba;连接到超级用户；

启动数据库实例

startup;

卸载数据库

shutdown immediate

新建表空间

create tablespace 表名 datafile '创建表的路径 + 表名(后缀为.dbf)' size 空间大小;
例如:
create tablespace scott_tb_space datafile 'C:/Users/ZH/Desktop/demo/scott_tb_space.dbf' size 2m;

此时就创建了一个scott_tb_space.dbf的数据表空间

新建用户和密码

create user 用户名 identified by 密码 default tablespace 表空间名称;
示例(表空间scott_tb_space创建了用户'zmd' 密码是'123')
create user zmd identified by 123 default tablespace scott_tb_space;

提升普通用户权限

grant dba to 想授权的用户名;

切换用户

conn 用户名/密码 as sysdba;

查看哪些用户拥有sysdba、sysoper权限

select * from V_$PWFILE_USERS;

进入某个数据库

database 数据库名;

查看当前库的所有数据表

select * from all_tables;

**查看表结构 **

desc 表名;

2.数据表操作

---

(1) select查询

** 查询表的所有字段(PL/SQL按f8运行命令)**

select * from 表名;

基本查询

select  查询的信息  from  数据来源
查询demo表所有信息: 
select * from demo;(*号表示查询所有信息)
查询demo表的name和age :
select name, age from demo;

去重(不影响原数据，仅查询信息去重)

select  distinct  查询的信息  from  数据来源
查询demo表中name,lage但不显示重复信息:
select distinct name,age from demo;

取别名(不影响原数据，仅查询信息取别名)

select 查询信息 别名 from 数据来源
或
select 查询信息 as 别名 from 数据来源
示例:
select name 名称 from demo;

参考图:

排序(不影响原数据，仅查询信息排序)

升序
select 查询信息 from 数据来源 order by 查询信息 asc;
降序
select 查询信息 from 数据来源 order by 查询信息 desc;

参考图:

多字段排序

升序
select 查询信息 from 数据来源 order by 查询信息 asc, 查询信息 asc/desc;
降序
select 查询信息 from 数据来源 order by 查询信息 desc, 查询信息 asc/desc;

参考图:

(2) 伪列和表达式

伪列：查询不存在的列。当所需结果不能直接获取，需要通过计算时可以使用伪列。

select 查询信息,伪列 from 数据来源;

参考图:

nvl()运算遇空(null)

相加遇到空数据时结果为空。示例：

需要用**nvl()**函数, nvl(不为空数据，为空时数据返回0)。示例:

排序遇空(null)

nulls first空数据在最上面 / **nulls last **空数据在最下面。示例：

**字符串拼接 **

使用‘ || ’符号拼接数据。示例：

(3) 虚拟表

语法：dual 例如 select 999*666 from dual：

(4) 条件查询

语法：select 查询内容 from 数据来源 where 条件

例如查询性别男的同学： select name from emp where sex = ‘男’;

除 '=' 符号外，还有 '<' , '>' , '!=' 等。

between and 表示值在一定范围内

例如查询2000~4000内的数据：select * from emp where name between 2000 and 4000;

in 查询符合条件的数据

例如查询年龄18,20,30的人：select * from emp where sex in(18,20,30);

(5) 条件连接运算符

and 表示条件同时满足

例如查询年龄大于20，性别为女的数据：

select name from emp where sex = ‘女’ and age > 20;

or表示满足任意一个就行

例如查询年龄大于20，或者性别为女的数据：

select name from emp where sex = ‘女’ or age > 20;

not取反

例如查询年龄不为20的数据：

select name from emp where not age = 20;

is null 查询空数据

is not null 查询不为空的数据

(6) like模糊查询

语法：like '%查询数据%' (百分号表示模糊信息)

例如查询名称含 ‘Z’ 的数据：select * from emp like ‘%Z%’;

例如查询名称 ‘Z’ 开头的数据：select * from emp like ‘Z%’;

escape()特殊字符查询

例如查询包含%号的数据，直接写like '%%%' 无效；

正确写法：select * from emp like ‘%a%%’ escape('a'); 此时a后面的%代表普通字符；

where子句

例如查询一班所有女生：

select * from emp where age=(select age from emp where class='1');

---

(以下可应用上方select的去重排序等操作，用法与select一致)

(7) create创建

create table 表名(字段名 字段类型 可加not null表示该字段不能为空);
示例：
create table ceshi(name varchar2(20) not null);

(8) alter修改

修改表名

alter table 原表名 rename to 新表名

注意：修改后点击tables刷新一下

**添加字段 **

alter table 表名 add(字段名 字段类型);

**修改字段名 **

alter table 表名 rename column 旧字段名 to 新字段名

修改字段类型

alter table 表名 modify(字段名1 新类型1);

删除一个字段

alter table 表名 drop(字段名);

(9) drop删除

删除一张表

drop table 表名;

(10) insert插入

插入一条数据

insert into 表名(字段1,字段2,…）values(值1,值2,…);
示例：
insert into cominfo(pno, pname, sex, birthday) values(1004, '李四', '男', '1998/1/4');
或
insert into cominfo values(1004, '李四', '男', '1998/1/4');

(11) update更新

修改语句

update 表名 set 字段名 = ‘新值’ where 条件
示例：
将小月的性别改为女
update cominfo set psex = '女' where pname = '小月';

(12) delete删除

**删除语句 **

delete from 表名 where 条件
示例：
删除编号为1009的同学
delete from cominfo where pno = 1009;

---

3. 函数

各类函数(可应用所有增删改查) 例：

** 字符串转日期函数: to_date(‘日期字符串’,‘日期格式’)**

示例：
insert into 表名 values(to_date('1998-01-09 18:20:10','yyyy-mm-dd hh24:mi:ss');

**日期类型转字符串: **to_ char(字段名 ,日期的字符串格式)

示例：
select 字段名(birthday,'yyyy') from 表名;

(1) 字符函数

**concat(x,y) **连接字符串x和y；

**instr(x,str,start) ** 在x中查找str，可以指定从start开始，也可以指定从第n次开始；

**length(x) **返回x的长度；

**lower(x) **x转换为小写；

**upper(x) ** x转换为大写；

**ltrim(x,trim_str) ** 把x左边截去trim_str字符串，缺省截去空格；

**rtrim(x,trim_str) ** 把x右边截去trim_str字符串，缺省截去空格；

**replace(x,old,new) ** 在x中查找old，并替换为new；

**substr(x,start,length) ** 返回x的字符串，从start开始，截取length个字符串；

(2) 数字函数

**abs(x) ** x的绝对值；

ceil(x) 向上取整；

floor(x) 向下取整；

mod(x,y) 对x求y的余数；

(3) 日期函数

sysdate 当前系统时间；

current_date 返回当前系统日期；

add_months(d1,n1) 返回在日期d1基础上再加n1个月后的日期；

last_day(d1) 返回日期d1所在月份最后一天的日期；

months_between(d1,d2) 返回日期d1到日期d2之间的月数；

next_day(d1[,c1]) 返回日期d1在下周，星期几(参数c1)的日期；

(4) 转换函数

to_char(x,c) 将日期或数据安装c的格式转换为char数据类型

to_date(x,c) 将字符串x安装c的格式转换为日期；

to_number(x) 将字符串x转换为数字型

(5) 常用组函数

**avg() ** 平均值

**sum() **求和

**min() **最小值

**max() **最大值

**count() **统计

注意：null不参与计算

(6) group by分组

语法：select ... from ... group by ...

查询各部门的平均工资：select avg(工资) from emp group by 部门编号;

(7) having组信息过滤

语法：**select ... from ... group by ... having ... **

查询各部门平均工资只保留平均工资大于3000的组信息：

select avg(工资) from emp group by 部门编号 having avg(工资)>3000;

注意：where和having可以同时使用；

** where用来筛选行，只能出现行数据；**

** having用来过滤组，只能出现组数据；**

---

4. 分页和去重

(1) rownum分页

rownum对每行数据增加编号，从1开始；

查询前五条数据：select ename, sal, deptno from emp where rownum <= 5;

(2) rowid去重

    rowid是一个伪列，它并不实际存在表中，他是oracle在读取表的行数据时，根据每行数据的物理地址信息编码生成的一个伪列。数据库大多数操作都是通过rowid完成的，而且使用rowid进行单记录定位速度是最快的。

    有时数据相同，无法通过distinct和group by去重，rowid具有唯一性，就需要用到rowid了。

例如下图，圈内的数据相同，但rowid不同，此时可以使用rowid去重或进行其他操作。

---

5. 表连接查询

查询a和b表数据：select * from a , b;

(1) on连接

on可做等值连接，非等值连接，自连接，可以解决一切连接，关系列必须要区分查询信息。

(2) 92标准

select ... from table1,table2 where table1.xxx and table1.xxx=1;

(3) 99标准

select ... from table1 cross join table2 where ...; 等值连接

select ... from table1 natural join table2 where ...; 自等值连接

select ... from table1 join table2 using(同字段名) where ...; 同名字段等值连接

select ... from table1 join table2 on 连接条件; on连接

(4) 集合操作

Union 并集(去重) 对两个结果集进行并集操作，不包括重复行，进行默认规则排序；

Union All 全集(不去重) 对两个结果集进行全集操作，包括重复行，不进行排序；

Intersect 交集(找出重复) 对两个结果集进行交集操作，不包括重复行，进行默认规则排序；

Minus 差集(减去重复) 对两个结果集进行差集操作，不包括重复行，进行默认规则排序；

---

6. DDL操作

(1) 约束

**primary key(主键约束) ** 约束字段唯一且不能为空；

unique(唯一约束) 约束字段唯一，但可以为空；

not null(非空约束) 约束字段不能为空；

**foreign key(外键约束) ** 约束从表数据必须存在主表中；

**check(检查约束) ** 根据自定义条件进行约束，例如设置数据必须在0~100之间；

示例图(倒数第二个写错了，应该是约束唯一，不是非空)：

(2) 给出约束名称

语法：... constraint 约束名称 约束函数。例：

(3) 启用/禁用约束

**enable/disable(启用/禁用) **是否对新变更的数据启用约束；

validate/novalidate(验证/非验证) 是否对表中已客观存在的数据进行约束验证；

注意：这四种状态可任意组合，默认为enable validate

(4) 删除约束

语法：alter table 表名 drop constraint 约束名称;

---

7. 查看视图结构

查看数据库

show parameter name;

查看数据库(v$database)视图结构

查看数据库结构
desc v$database;
查看数据库结构的字段信息
select 数据结构字段 from v$database;

查看实例(v$instance)视图结构

查看实例结构
desc v$instance;
查看实例字段的信息
select 实例结构字段 from v$instance;

查看v$version视图结构(包含数据库版本信息)

desc v$version;
查看v$version视图结构(一般v$version里只有BANNER一个字段，BANNER包含数据版本信息)

查看数据库版本信息
select banner from v$version;

查看dba_objects视图结构

desc dba_objects;

数据库视图结构各字段含义链接：

Oracle体系结构之Oracle基本数据字典:v$database、v$instance、v$version、dba_objects - ChavinKing - 博客园 (cnblogs.com)