oracle数据库基本语句分类有哪些?

TheDisguiser 2020-06-19 13:31:46 java常见问答 5758

小伙伴们知道Oracle吗?你们知道Oracle的基本语句都有哪几类吗?这次我们就一起来聊聊吧。

一、oracle用户和常用命令

1、Oracle安装会自动生成sys用户和system用户

sys用户是超级用户,具有最高权限,具有sysdba角色,有create database的权限

system用户是管理操作员,权限也很大,具有sysoper角色,没有create database的权限一般讲,对数据库维护,使用system用户登录就可以啦

2、scott/tiger是普通用户(如果安装时未解锁,需要解锁才能使用)

3、sqlplus登录

使用oracle自带的sqlplus登录

管理员

本机登录:sqlplus sys/orcl as sysdba; ***
远程登录:sqlplus sys/orcl@192.168.56.102:1521/orcl as sysdba

普通用户(官方提供演示账号:scott)

本机登录:sqlplus scott/tiger ***
远程登录:sqlplus scott/tiger@192.168.56.102:1521/orcl

使用exit退出,但是这样sqlplus也退出了,可以使用disc,换用户登录可以使用connect

4、创建用户

create user 用户名 identified by 密码;

注意创建用户必须具有dba权限

create user xiaoming identified by xiaoming;

5、alter user 账号 identified by 新密码

改密码必须是系统管理员权限

远程管理员登录命令:

connect sys/orcl@192.168.56.102:1521/orcl as sysdba

本机管理员登录命令(可以省略conn)

connect sys/orcl as sysdba
alter user xiaoming identified by xiaohua;

6、drop删除用户

drop user 用户名 [cascade]

必须具有drop_user权限,自己不能删除自己

如果已经创建了表,那么就需要在删除时代一个级联的参数cascade

7、赋权限

Oralce有两种权限

一种是系统权限,一种是对象权限(select,update,delete,insert)。

系统权限允许执行某些数据库操作

主要为connect(连接),resource(创建表权限),

dba权限

对象权限是指用户对某一对象(表、视图、序列等)进行操作

要给用户授权必须满足下列条件之一:

用户拥有指定的对象

用户已经拥有了使用GRANT OPTION的对象权限

给xiaoming分配系统权限

给连接的权限

grant connect to xiaoming

给创建的权限

grant resource to xiaming

对象权限

允许用户查看scott的emp表中的记录

grant select on scott.emp to xiaoming

允许用户更新scottemp表中的记录

grant update on scott.emp to xiaoming

all表示emp表的所有权限

grant all on scott.emp to xiaoming

收回权限使用命令revoke

scott收回emp表的查询权限

revoke select on scott.emp from xiaoming

8、锁定用户

alter user xiaoming account lock

9、解锁用户

alter user xiaoming account unlock

10、记录sql语句

可以将sql*plus屏幕上的内容输出到指定的文件中去

spool d:	est.sql;
并输入spool off;

11、清屏

host cls

12、显示当前用户

show user

13、当前用户下的表

select * from tab

14、查看表结构

desc emp

15、设置显示的行宽

--先显示行宽
show linesize
--设置行宽
set linesize 150

16、设置列宽,表示ename这个字段 a代表字符串,固定写法,表示8个字符长度

column ename format a8

--可以简写,9代表一位数字

col sal for 9999

二、表操作命令

1、 建表

create table student(--表名
stuNo number(4),--学号
stuName varchar2(20),--姓名
sex char(2),--性别
birthday date,--出生日期
sal number(7,2) --奖金
);

2、修改表结构

添加一个字段

alter table student add(class_id number(2));

修改字段长度

alter table student modify(sex char(3));

修改字段名

alter table student rename column stuName to sname;

删除一个字段

alter table student drop column sal;

修改表名

rename student to stu;

删除表

drop table student;

3、oracle中的约束

(1)、约束包括:

not null、
unique、
primary key、
foreign key、
Check

三、SQL命令及函数

商店售货系统表案例,由三个表构成:

商品表:

表名: goods
字段 类型 描述 约束
goodsId char(8) 商品编号 主键
goodsName varchar2(40) 商品名
unitprice number(8,2) 单价 大于0
category varchar2(20) 商品类别
provider varchar2(40) 供应商
create table goods(
goodsId char(8) primary key,
goodsName varchar2(30),
unitprice number(10,2),check(unitprice>0),
category varchar2(20),
provide varchar2(30)
);

客户表

表名: customer
字段 类型 描述 约束
customerId char(8) 客户编号 主键
name varchar2(50) 姓名 非空
address varchar2(50) 住址
email varchar2(50) 邮箱 唯一
sex char(2) 性别 只能有男和女,默认男
cardId char(18) 身份证
create table customer(
customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(3) default '男' check(sex in('男','女')),
cardId char(18)
);

销售表

表名: purchase
字段 类型 描述 约束
p_customerId char(8) 客户编号 外键
p_goodsId char(8) 商品编号 外键
nums number(2) 销售数量 1-30之间
create table purchase(
p_customerId char(8) references customer(customerId),
p_goodsId char(8) references goods(goodsId),
nums number(2) check(nums between 1 and 30)
);

(3)、创建表后再添加约束:

注意如果是非空约束需要加modify

alter table goods modify goodsName not null;

默认约束

alter table goods modify goodsName default '商品';
给customer表的cardId增加唯一约束
alter table customer add constraint
card_unique unique(cardId);
给customer表的address地址添加检查约束
alter table customer add constraint
address_check check(length(address)>=10);

其余和SQLServer的一样。

(4)、删除约束

alter table 表名 drop constraint 约束名;

在删除主键约束的时候,可能有错误,比如:

alter table 表名 drop primary key;

这是因为如果两张表存在主从关系,那么,在删除主表的主键约束时,必须带上cascade选项

alter table 表名 drop primary key cascade;

4、插入数据:

insert into student values(2,'李四’,'男’,'1-1月-09',1100);

注意:oracle中默认的日期格式为 DD-MM-YYYY

该日期的默认格式:

alter session
set nls_date_format = ‘yyyy-mm-dd’;

修改后就可以按照熟悉的格式插入数据了

insert into student values(3,’李四’,’男’,’2000-10-19’,1100);

插入空值,字段值为null

insert into student values(2,’李四’,’男’,’2000-10-19’,1,null);

5、删除数据

l delete from student;

删除所有记录,表结构还在,写日志,可以恢复的,速度慢

l truncate table student;

删除表中的所有记录,表结构还在,

不写日志,无法找回删除的记录,速度快

l drop table student;

删除表的结构和数据

6、查询

set timing on;//打开操作时间开关

(1)、 查看表结构

desc 表名;

(2)、 取消重复行

select distinct deptno,job from emp;

(3)、 查询处理空值

nvl(expr1,expr2)如果expr1为空则返回expr2
select sal*13+nvl(comm,0) total from emp;
nvl2(expr1,expr2,expr3)
如果expr1不为空返回expr2,否则返回expr3
select empno,ename,nvl2(comm,'有奖金','没有奖金') from emp;
nullif(expr1,expr2)如果expr1和expr2相等返回空,否则返回expr1

(4)、 合并查询

为了合并多个select语句的结果,可以使用集合操作符号union,union all,intersect,minus

l union该操作符用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中重复行

查询工资大于2500与职位为MANAGER的并集

select ename, sal, job from emp where sal > 2500 union select ename, sal, job
from emp where job = ’MANAGER’;
l union all与union相似, 但是它不会取消重复行
select ename, sal, job from emp where sal > 2500 union all select
ename, sal, job from emp where job = ’MANAGER’;
l intersect使用该操作符用于取得两个结果集的交集
select ename, sal, job from emp where sal > 2500 intersect select
ename, sal, job from emp where job = ’MANAGER’;
l minus使用该操作符用于取得两个结果集的差集, 它只会显示在第一个集合中, 而不存在第二个集合中的数据
select ename, sal, job from emp where sal > 2500 minus select ename, sal, job
from emp where job = ’MANAGER’;

Oracle单行函数

1、字符函数

(1)lower转小写

select lower('Hello Word') from dual;

(2)upper转大写

select upper('Hello Word') from dual;

(3)initcap每个单词的首字母大写

select initcap('hello world') from dual;

(4)substr截取字符串

* substr(a,b) 从a中,第b位开始取(包含b 从1开始)
select substr('Hello World',3) from dual;
* substr(a,b,c) 从a中,第3位开始取,取4位
select substr('HelloWorld',3,4) from dual;

(5)length字符数,lengthb字节数

select length('湖南') 字符数,lengthb('湖南') 字节数 from dual;

(6)instr(a,b) 在a中,查询b,

返回找到的开始位置从1开始 找不到返回0

select instr('Hello World','ll') from dual;

(7)填充指定字符

* lpad左填充

select lpad('1',10,'0') from dual;

* rpad右填充

select rpad('abcd',10,'*') from dual;

(8)trim去除前后指定的字符

select trim('H' from 'Hello WorldH') from dual;

(9)replace替换

select replace('Hello World','l','*') from dual;

2、数学函数

(1)round 四舍五入,四舍五入是绝对值的四舍五入 0表示个位,正数表示保留的小数位数,负数表示小数点左边的位数

select round(45.8954,1) 一,round(45.8954,2) 二,round(45.8954,0) 
三,round(45.8954,-1) 四,round(45.8954,-2) 五 from dual;

(2)trunc 截断, 0表示个位,正数表示保留的小数位数,负数表示小数点左边的位数

select trunc(45.8954,1) 一,trunc(45.8954,2) 二,trunc(45.8954,0) 
三,trunc(45.8954,-1) 四,trunc(45.8954,-2) 五 from dual;

(3)mod 取余

select mod(10,3) from dual;

3、日期函数

(1) sysdate获取当前时间

select sysdate from dual;

(2)日期计算

加减的值是天数

* 昨天、今天、明天

select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;

* 计算员工工龄

select (sysdate-hiredate) 天,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 
月,(sysdate-hiredate)/365 年 from emp;

* 日期不允许相加日期

select sysdate+hiredate from emp;

(3)months_between两个日期相差的月数

select (sysdate-hiredate)/30 月,months_between(sysdate,hiredate) 月 from 
emp;

* 单行行数的嵌套使用

select round(months_between(sysdate,hiredate),0) from emp;

(4)add_months增加月数

select add_months(sysdate,12) from dual;

(5)last_day月份的最后一天的日期数据

select last_day(sysdate) from dual;

(6)next_day指定日期的下一个星期几的日期

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

(7)round日期四舍五入

select round(sysdate,'month'),round(sysdate,'year'),round(sysdate,'dd') from 
dual;

(8)trunc日期截断

select trunc(sysdate,'month') from dual;

4、转换函数

转换的前提:被转换对象是可以转换的

(1)to_char 将某种类型的数据转为字符串显示

12小时制显示

select to_char(sysdate,'YYYY-MM-DD HH12:mi:ss') from dual;

24小时制显示

select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;

按照年月日时分秒 星期几

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS"今天是"day') from dual;

(2)to_date 将字符串转为日期

select * from emp where hiredate<to_date('2017-1-1','yyyy-mm-dd');< p="">

(3)to_number将字符串转为数字

select to_number('2008')+1 from dual;

(4)数字转字符串

select 2008||'年' from dual;

5、通用函数

coalesce从左到右找到第一个不为null的值

select comm,sal,coalesce(comm,sal) from emp;

6、条件表达式

(1)case 字段

when 值 then 操作

when 值 then 操作

else 操作

end

涨工资,总裁1000 经理800 其他400

select ename,job,sal 涨前,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp;

(2)decode,oracle特有语法

select ename,job,sal 涨前,

decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后 from emp;

oracle多行函数

和单行函数相比,oracle提供了丰富的基于组的,多行的函数。

这些函数能在select或select的having子句中使用,

当用于select子串时常常都和GROUP BY一起使用。

(1)sum求总薪水

select sum(sal) from emp;

(2)count统计员工数

select count(*) from emp;

(3)avg求平均工资

select avg(sal) from emp;

(4)求平均工资和平均奖金

select avg(sal),avg(comm) from emp;

null 自动过滤

select avg(sal),avg(comm) from emp;

以上就是关于Oracle基本语句分类的所有内容了,如果对你有所帮助,还想要了解更多Oracle常见问题,就请持续关注我们网站吧。