学Oracle小记
一.简介
1.小提示
2.Oracle中为管理员预置了两个用户分别是SYS和SYSTEM.同时Oracle为程序测试提供了一个普通用户SCOTT(默认密码:tiger 锁定状态).
默认用户中SYS和SYSTEM用户是没用锁定的,安装成功后可以直接使用,
SCOTT用户默认是锁定状态的,因此不能直接使用,需要把SCOTT用户设定为非锁定状态才能正常使用.
解除锁定的方法,使用SQL Plus工具(见3),命令为:
ALTER USER SCOTT ACCOUNT UNLOCK
UNLOKC为解除锁定,如果要锁定就换成LOCK
3.SQL Plus使用
a.打开SQL Plus(Oracle安装目录下)输入system用户名回车,再输入密码回车,显示如下界面,代表连接成功.就可以写命令与数据库进行交互了.
b.在命令行下输入 sqlplus/nolog 也可以启动这个工具,启动用要连接服务器,命令是:
conn 用户名/密码 as 连接身份@服务器连接字符串
连接身份有三种分别为:sysdba(数据库管理员),sysyoper(数据库操作员),normal(普通用户)
例:用户名:system 密码:123456 用管理员身份登陆,在只有一个数据库的情况下连接字符串可以缺省.
conn system/123456 as sysdba
或者 用户名:system 密码:123456 连接到orcl数据库
conn system/123456@orcl
在SQL Plus中进行Oracle的启动关闭
启用Oracle服务必须是sys用户 命令格式: startup open
关闭Oracle服务命令格式:shutdown immediate
4.用户和权限
创建用户语法:
CREATE USER 用户名 IDENTIFIED BY 口令 [ ACCOUNT LOCK | UNLOCK ]
LOCK|UNLOCK表示是否锁定,锁定用户无法正常登录进行数据库操作.
例:创建一个用户名:json 密码:123456 且无锁定
CREATE USER json IDENTIFIED BY 123456 ACCOUNT UNLOCK ;
分号(;) 在Oracle里边的意义:
创建完用户还不能正常登录使用,要可正常登录至少需要CREATE SESSON 系统权限.
权限分为:系统权限和数据库对象权限
系统权限比如:CREATE SESSION,CREATE TABLE等
数据库对象权限比如:对表中的数据进行增删改操作等
数据库角色
角色/权限 授权
语法GRANT 角色|权限 TO 用户(角色)
例:GRAN CONNECT TO json;
GRAN RESOURCE TO json
例2:将INFOS的查询权限赋于json,同时json可以将此此权限再赋于其他用户
GRANT SELECT ON INFOS TO json WITH GRANT OPTION
更多 WITH GRANT OPTION相关内容见
http://www.cnblogs.com/weiqt/articles/5416742.html
角色/权限 回收
REVOKE 角色|权限 FROM 用户(角色)
例: REVOKE CONNECT FROM json
REVOKE RESOURCE from json
修改用户密码
ALTER USER 用户名 IDENTIFIED BY 新密码
例:将json用户的密码改成123
ALTER USER json IDENTIFIED BY 123
修改用户处于锁定(非锁定状态)
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
例: 将json用户锁定
ALTER USER json ACCOUNT LOCK
二.SQL操作查询
1.数据类型
varchar varchar2 的区别
目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。
default的区别
在Oracle中default是一个值,而SQL Server中Default是一个约束,因此Oracle的Default设置可以在建表的时候创建
例:create table tmp(a varchar2(10) default '不填')
insert into tmp values(default)
select 的A字段结果是: 不填
dual表
Oracle的查询中,必须使用"select 列... from 表"的完整语句,当查询函数的时候,from 后面使用Dual表,dual表在系统中只有一行一列,该表在输出单行函数时为了select ...from的语法完整性而使用.
例:查询当前系统的日期时间
select sysdate from dual 返回 SYSDATE 2016-02-20 14:45:46
正斜杠(/)：
在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
例如：执行以下语句
CREATE TABLE TEMP
( AAA VARCHAR2(2 BYTE),
BBB VARCHAR2(2 BYTE)
)
Insert into TEMP (AAA,BBB) values ('TESTA','TESTB');
就会报错： TABLE OR VIEW DOES NOT EXIST。
在INSERT 语句之前加入正斜杠/即可。
根据查询的结果创建新表
语法:CREATE TABLE 表名 AS SELECT 语句
例:create table infos1 as select * from infos
此方法创建的新表中,不存在任何约束,并且把查询的数据一起插入到新表中.
日期转化
例:TO_DATE('2016-01-20 15:12:21','YYYY-MM-DD HH24:MI:SS')
操作符
Orcle中只有+ - * / 四种,除号(/)的结果是浮点数.求余只能用函数 MOD(x,y):返回x除以y的余数
字符串的连接用双竖线(||)表示
例: select 'abc'||'def ' from dual
结果是: 'abcdef '
Oracle中字符串可以用单引号,也可以用双引号,在别名(比如给表进行命名别名时)中存在空格时,必须用双引号.在表名/列名时用双引号
后边使用的示例表
外联接
select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno(+)=d.deptno
(+)为Oracle专用的联接符,在条件中出现在左边指右外联接,出现在右边指左外联接
ANY子查询
查询EMP表中比任意一个销售员("SALESMAN")工资低的员工姓名/工作/工资
SELECT ENMAE,JOB,SALE FROM EMP
WHERE SAL<ANY(SELECT SAL FROM EMP WHERE JOB='SALESMAN')
ALL子查询
查询出比所有销售员的工资都高的员工姓名/工作/工资
SELECT ENAME,JOB,SAL FROM EMP
WHERE SAL>ALL(SELECT SAL FROM EMP WHERE JOB='SALESMAN')
伪列
在Oracle的表的使用过程中,实际表中还有一些附加的列,称为伪列.伪列就像表中的列一样,但是在表中并不存储.伪列只能查询,不能进行增删改操作.
伪列有: ROWID和ROWNUM
ROWID
表中的每一行在数据文件中都有一个物理地址,ROWI伪列返回的就是该行的物理地址.使用ROWID可以快速的定位表中的某一行.ROWID值可以唯一的标识表中的一行.由于ROWID返回的是该行的物理地址,因此使用ROWID可以显示行是如何存储的.
ROWNUM
在查询的结果集中,ROWNUM为结果集中每一行标识一个行号,第一行返回1,第二行返回2,以此类推.通过ROWNUM伪列可以限制查询结果集返回的行数.
例:查询员工表中前5名员工的姓名/工作/工资
SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5
例2:查询出工资最高的前5名员工的姓名/工资
分析:生成ROWNUM操作比排序要早,排序时已经连同ROWNUM一起排序了,所以不能直接ORDBY BY.由于Oracle不存在像SqlServer 的TOP方法,所以也不能直接用order by然后top.
SELECT ROWNUM,T.* FROM(
SELECT ENAME,JOB,SAL
FROM EMP ORDER BY SAL DESC
) T WHERE ROWNUM<=5
例3:分页案例
查询出表EMP中第5条到第10条之间的记录
SELECT * FROM (
SELECT ROWNUM R,ENAME,JOB,SAL FRO EMP WHERE ROWNUM<=10)
WHERE R>5
)
解释:注意子查询中的rownum必须要有别名，否则仍然会查不到记录，这是因为rownum不是某个表的列。
如果不起别名的话，无法知道rownum是子查询的列，还是主查询的列。
rownum如果使用>1的任何数,查询不出来数据来.
具体原因参见以下链接:Oracle中的rownum不能使用大于>的问题
http://www.cnblogs.com/weiqt/articles/5420238.html
函数
日期函数
1. ADD_MONTHS(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期.d表示日期,n表示要加的月数
2.LAST_DAY(d),返回指定日期当月的最后一天
3.
转换函数
1.TO_CHAR(d|n,[,fmt])
把日期和数字转换为制定格式的字符串.fmt是格式化字符串,日期的格式化字符串有 YYYY-MM-DD HH24:MI:SS
例:
select to_char(-123123.45,'L9.9EEEEPR') from dual
输出： <￥1.2E+05>
2.TO_DATE(x[,fmt]) 把一个字符串以fmt格式转换为一个日期类型
3.TO_NUMBER(x,[,fmt]) 把一个字符串以fmt格式转换为一个数字.fmt格式字符参见上边 数字函数的表3.
其他函数
1.NVL(x,value) 如果x为空,返回value,否则返回x. 相当与sql server中的 isnull(x,value)
2.NVL2(x,value1,value2) 如果x非空,返回value1,否则返回value2.
聚合函数
AVG 平均值
SUM 求和
MIN 最小值 MAX最大值
COUNT 数据统计
