oracle常用语句和一些函数

oracle常用语句和一些函数

oracle的简介

oracle数据库的特点

持大数据量、多用户的高性能的事务处理
Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准
实施安全性控制和完整性控制
支持分布式数据库和分布处理
具有可移植性、可兼容性和可连接性
全球化、跨平台的数据库

oracle版本

oracle9i internet 互联网技术
oracle10g grid 网格技术
oracle11g grid 网格技术
oracle12c cloud 云计算

简单使用oracle数据库

Oracle数据库

• Oracle数据库：位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑整体，即为Oracle数据库。
  因此，在Oracle看来，数据库是只硬盘上文件的集合，必须要与内存中的实例合作，才能对外提供数据管理服务。

Oracle实例

• 位于物理内存中的数据结构。它由一共享的内存池和多个后台进程组成，共享的内存池可以被所有的进程访问，
  用户如果要存储数据库（也就是硬盘上的文件）里的数据，必须通过实例才能实现，不能直接读取硬盘上的文件。

使用oracle先登录
双击安装后sqlplus

登录

用户名和密码

管理员用户:
用户名sys(最大的权限)密码在安装时设置的密码；还具有创建新的数据库的权限。
用户名system(次之)密码在安装时设置的密码
普通用户:
用户名scott密码tiger

登陆数据库：
cmd中输入： sqlplus sys as sysdba
密码：
sqlplus窗口： 输入用户名
密码：

sqlplus sys as sysdba

sys as sysdba

如下在sqlplus中输入

切换用户：
conn 用户名/密码
例如：conn system/123456

conn system/123456

权限管理

在为一个Oracle数据库系统创建用户之后,这些用户既不能与数据库服务器连接,也不能做任何事情,除非他们具有执行特定数据库操作的权限.
Oracle中的数据库访问权限类型共有两种:

• 系统权限: 允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等
• 对象权限: 允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等

查oracle内置权限

select * from system_privilege_map;

常用系统权限

CREATE SESSION	创建会话
CREATE SEQUENCE	创建序列
CREATE SYNONYM	创建同名对象
CREATE TABLE	在用户模式中创建表
CREATE ANY TABLE	在任何模式中创建表
DROP TABLE	在用户模式中删除表
DROP ANY TABLE	在任何模式中删除表
CREATE PROCEDURE	创建存储过程
EXECUTE ANY PROCEDURE	执行任何模式的存储过程
CREATE USER	创建用户
DROP USER	删除用户
CREATE VIEW	创建视图

创建用户
创建一个root用户

create user root identified by 123456;

赋权
赋予登录权限

grant create session to root;

赋予创建删除修改表权限

 grant create table,drop table,alter table to root;

赋予dba权限，dba权限就是数据库管理员，但跟sysdba比不了

grant dba to root;

回收权限
取消dba权限

revoke dba from root;

取消创建表的权限

 revoke create table from root;

权限分为两种：

• 系统权限 create alter drop
• 对象权限 insert update delete

像对表的增删改查跟mysql就一样，说说不一样的部分吧！

mysql和oracle区别

语法

	语法	数据类型
oracle	varchar2	char类型
mysql	varchar	char类型

表名不能是关键字
例如创建一个user表就创建不了跟user关键字重复

create table users(
		id int primary key,
		name varchar2(16)
	)

序列

序列：是一数据库对象，利用它可生成唯一的整数。oracle没有主键自增
一般使用序列自动地生成表主键值或唯一键值,不直接连接到数据库中的任何表
创建序列语法

CREATE SEQUENCE [user.]sequence_name
			[increment by n] 
             [start with n]
    		 [maxvalue n|]
			[minvalue n ];
			[cache]
[NOCYCLE|CYCLE]

INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。
START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。
对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。
MAXVALUE：指定序列可生成的最大值。
MINVALUE：指定序列的最小值
NOCYCLE：一直累加，不循环
CYCLE：累加到最大值循环
cache:默认值是20

创建序列 作为自增主键

create sequence seq_users_id
	increment by 1
	start with 1
	maxvalue 9999;

删除序列

drop sequence users_id;

利用序列获取主键
–currval :返回序列的当前值
–nextval 返回序列首次引用时的起始值，以后使用nextval的引用将使用increment by 子句增加序列值，并返回新值

insert into users(id,name) values (seq_users_id.nextval,'徐凤年');

查询序列当前值

select seq_users_id.currval from dual;

dual表

Oracle提供的最小的工作表，只有一行一列，具有某些特殊功用途
一行一列 不能删 是辅助查询用的

select * from dual;
select 2*3 from dual;

查看当前用户

select user from dual;

查询中常用的函数

字符函数

大小写转换函数
LOWER (strexp) 返回字符串,并将所有的字符小写
UPPER (strexp) 返回字符串,并将所有的字符大写
INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写,后面的小写;

小写

select lower('zhangSAN') from dual;

大写

select upper('zhangsan') from dual;

首字母大写

select initcap('zhangSAN') from dual;

字符处理函数

scott 用户下有几张表
emp 员工表
dept 部门表

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;

CONCAT(strexp, strexp)	连接两个字符串
Substr(str,start_index,length)	从指定的位置截取指定长度的字符串
LENGTH(strexp)	返回字符串的长度
LPAD( string1, padded_length, [ pad_string ] )	在列的左边粘贴字符
	string1是需要粘贴字符的字符串
	padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length
	pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格
RPAD(粘贴字符)	RPAD 在列的右边粘贴字符
Trim()	截取字符串两端特殊字符

select concat(dname,loc) from dept;

select substr(dname,0,5) from dept;

 select dname,lpad(dname,10,'*'),rpad(dname,10,'*') from dept;

select trim('S' from dname) from dept;

数字函数

Round	传回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。
ROUND( number, decimal_places )
TRUNC	函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
mod	两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作

select round(111.123,2) from dual;

select trunc(45.926, 2) from dual;

select mod(1600,300) from dual;

转换函数

隐含的类型转换
显式的类型转换

显式的类型转换

TO_CHAR函数处理数字
	TO_CHAR(number, ‘fmt’)	是字符类型的函数，转化数字为字符 使用TO_CHAR函数将数字作为字符显示
	数字格式控制符	描述
	9	代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示）
	0	强制显示该位，如果当前位有数字，显示数字，否则显示0
	$
	L	增加本地货币符号显示
	.	小数点符号
	,	千分位符号 3,000,000,000.00
TO_NUMBER函数
	TO_NUMBER(char [, ‘fmt’]))	使用TO_NUMBER函数将字符转换为数字
TO_CHAR 函数操作日期
	TO_CHAR(date, ‘fmt’)	是字符类型的函数，转化日期为字符
	格式(‘fmt’)
		必须用单引号括起来,并且是大小写敏感
		可包含任何有效的日期格式
		fmt值的宽度正好能容纳所有的有效数字
	格式控制符	描述
	YYYY YYY YY	以数字表示全年(分别代表4位、三位、两位)的数字年
	YEAR	年的拼写
	MM(mm）	两位数字月
	MONTH	月的全拼
	MON	月名称的缩写
	DD	数字日
	DAY	星期的全拼
	D	星期中的第几天
	DY	表示三位缩写的星期
TO_DATE函数
	TO_DATE(char [, ‘fmt’]))	使用TO_DATE函数将字符转换为日期
	格式控制符	描述
	HH，HH12	一天中的第几个小时，12进制表示法
	HH24	一天中的第几个小时，取值为00~23
	MI	一小时中的分钟
	SS	一分钟中的秒
	AM	显示上午或下午

sal 数值 —> char

select to_char(sal,'$999,999,999.99') from emp;
select to_char(sal,'$000,000,000.00') from emp;
select to_char(sal,'L999,999,999.99') from emp;

日期—>char

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

日期函数
add_months(date_value,number_of_months):用于从一个日期值增加或减少一些月份

select add_months(sysdate,12) "明年今日" from dual;
select add_months(sysdate,-12) "去年今日" from dual;

current_date:返回当前会话时区中的当前日期

select sessiontimezone,current_date from dual;
alter session set time_zone='-11:00';//修改当前会话时区

extract(date_field from datetime_value):找出日期或间隔值的字段值

select extract(month from sysdate) "当前月份" from dual;

last_day(date_value):返回指定日期中的月份的最后一天的日期

select last_day('2000-02-01') "润月" from dual;

next_day( date, weekday ):返回指定时间的下一个星期的指定星期对应的日期

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

months_between(f,s) 日期f和s间相差月数

select months_between(sysdate,'04-5月-09')from dual;