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:回收权利;***mit:提交;  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 ***info(pno, pname, sex, birthday) values(1004, '李四', '男', '1998/1/4');
或
insert into ***info values(1004, '李四', '男', '1998/1/4');

(11)  update更新

修改语句

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

(12)  delete删除

删除语句 

delete from 表名 where 条件
示例:
删除编号为1009的同学
delete from ***info 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 - 博客园 (***blogs.***)

转载请说明出处内容投诉
CSS教程_站长资源网 » Oracle/PL/SQL数据库基础操作(持续更新)

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买