小伙伴们知道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常见问题，就请持续关注我们网站吧。