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

前言

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

分类	命令
DDL	create：创建；drop：删除；alter：修改；rename：重命名; truncate：截断；
DML	insert：插入；delete：删除；update：更新；select：查询;
DCL	grant：授权；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)