一、sqlplus小知识
1、显示当前用户
show user;
2、修改oracle密码
passw 或者 passw + 用户名
(可以带用户名,也可以不带用户名,但是给别人带用户名,必须带用户名)
3、切换用户
conn + 用户名
4、查询所有的用户名
select username from dba_users;
select * from dba_users;
二、用户管理
1、创建用户
create user 用户名 identified by 密码
创建用户必须要创建用户的权限
刚刚创建的用户不能登录,因为没有登录的权限:
2、给新用户赋予登录权限
给用户赋值登录的权限:
grant create session to 用户名
3、系统管理员给用户修改密码
如果忘记了密码,需要找系统管理员重置秘密:
4、综合案例(创建用户和权限赋值)
①创建用户 gg
②给gg赋予登录的权限
③gg创建一张表
发现没有权限,所以需要分配角色
④分配角色
grant resource to gg;
⑤再次创建表
connect resource 介绍:
⑥回收权限
revoke 权限名 from 用户名
⑦删除用户
drop user gg;
在删除用户的时候,如果该用户已经创建过数据对象,需要加上一个cascade,表示和数据对象一并删除。
drop user gg cascade;
5、使用profile文件对口令进行管理
举例,如果一个用户连续输入密码错误3次,则锁定该用户3天,三天之后才可以登录?
--创建profile文件
create profile lock_account limit failed_login_attempts 3 password_lock_time 3;
--使用profile文件
alter user 用户名 profile lock_account;
当用户输入错误3次之后,再一次登录,不管密码是不是正确,都会显示:
ORA-28000: the account is locked
② 可以使用有权限的管理员给用户解锁
alter user 用户 account unlock;
alert user lwb account unlock;
③ 终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作.
例子:给前面创建的用户lwb创建一个profile文件,要求该用户每隔10天要修改自家的登陆密码,宽限期为2天。
create profile myprofile limit password_life_time 10 password_grace_time 2
alter user tea profile myprofile
④ 口令历史
概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码
create profile password_history limit password_life_time 10 password_grace_time 2
password_reuse_time 10
password_reuse_time //指定口令可重用时间即10天后就需要修改
alter user tea profile myprofile
⑤ 删除profile
概述:当不需要某个profile文件时,可以删除该文件
三、关于用户的方案问题
如果有两个用户,如果 一个用户创建了一个表,但是另一个用户却不能访问这个表,这是为什么呢?
没错,oracle为每个用户创建了各自的方案,所以一个用户不能看见别的用户的表(数据对象)
那么在上图中,怎么让新建的lwb用户可以查询scott的emp表呢? 操作如下:
① scott登录
conn scott
② 赋予一个对象权限给lwb
grant select或者update或者delete或者insert或者all on emp to lwb;
例如:grant select on emp to lwb;
登录到lwb查询的时候:select * from scott.emp; 这样就可以查询到数据了。
四、数据库的启动流程
① oracle启动流程-windows下
1、lsnrctl start (启动监听)
2、oradim –startup –sid 数据库实例名
② oracle启动流程-linux下
1、lsnrctl start (启动监听)
2、sqlplus sys/change_on_install as sysdba (以sysdba 身份登录,在oracle10g后可以这样写)
sqlplus /nolog
conn sys/change_on_install as sysdba
3、startup
五、oracle的登录认证
在我的windows系统中,安装了一个oracle数据库,当我用 as sysdba的方式登录的时候,发现如下一个问题:
即不论使用什么样的用户名和密码,只要是加上 as sysdba 那么都可以登录到sys用户,如上图。
这是什么原因呢?原来,当我们在安装oracle的时候,会在我们的系统上创建一个用户组,如下图:
该用户组是安装oracle的时候,自动创建的用户组,点开这个用户组可以发现:
在该用户组中,只要是包含的用户,那么在使用as sysdba登录的时候,默认不会去校验用户名和密码。而我的windows系统当前登录的用户正好是Administrator用户,所以在用as sysdba模式登录的时候,不会校验用户名和密码。当然,这是oracle的默认方式,该方式是可以修改的。
总结如下:
① 对于普通用户,oracle默认使用数据库验证。
② 对于特权用户(比如sys用户),oracle默认使用操作系统认证,
③如果验证不通过,再到数据库验证(密码文件验证)。通过配置sqlnet.ora文件,可以修改oracle登录认证方式
SQLNET.AUTHENTICATION_SERVICES= (NTS)是基于操作系统验证;
SQLNET.AUTHENTICATION_SERVICES= (NONE)是基于Oracle验证;
SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS)是二者共存。
④ oracle登录认证方式-linux下
默认情况下linux下的oracle数据库sqlnet.ora文件没有SQLNET.AUTHENTICATION_SERVICES参数,
此时是基于操作系统认证和oracle密码验证共存的,加上SQLNET.AUTHENTICATION_SERVICES参数后,
不管SQLNET.AUTHENTICATION_SERVICES设置为NONE或者NTS,都是基于oracle密码验证的。
六、丢失管理员密码怎么
恢复办法:把原有密码文件删除,生成一个新的密码文件。
恢复步骤如下:
① 搜索名为 PWD数据库实例名.ora 文件 (比如我的:PWDorcl.ora)
② 删除该文件,为预防万一,建议大家备份
③ 生成新的密码文件,在dos控制台下输入命令:
orapwd file=原来密码文件的全路径\密码文件名.ora password=新密码 entries=10;//entries:允许几个特权用户。密码文件名 一定要和原来的密码文件名一样。
七、oracle数据类型
① char(size) 定长字符串,最多可以放2000个字符。
name char(32) 如果插入的数据少于32,那么就用空格来填充,如果多余32个,就报错。(一个中文字,占用两个字符空间),一个字符空间是使用一个字节来设计的。
② varchar2(size) 变长,最多可以存放4000个字符。
name(32) 如果插入数据少于32个,那么实际是多长就占用多长,剩余的空间就被回收了。如果超过32个字符,那么会自动增加存储空间。
如果以后要存储的数据是定长的,比如商品编号,身份证号,那么就使用char,不但约束了存储的长度,并且char的存储速度比varchar2快,如果存储的字符是变长的,那么就使用varchar来存储。
③ nchar(size) 定长,最长可以存储2000个字符
nchar采用的是unicode的编码方式。(一个中文字,占一个字符空间),因为一个字符空间是使用两个字节来设计的。
④ nvarchar2(size) 变长,最长可以存储4000个字符
采用unicode编码方式。
⑤ clob 变长,字符型大对象,最多可存储8TB
⑥ blob 变长,二进制数据,可以存放图片和视屏等
⑦ number(p,s) ,变长
可以存放整数和小数。
p:代表有效位 1 <= p <= 38
s:代表小数位 -84 <= s <= 127
保存数据范围:-1.0e-130 <= number < 1.0e+126
⑧ date 日期类型
按照日-月-年的方式存储的
insert into users vaues(‘12-11月-2017’);
八、基础sql
以下的表为演示sql语句的基础表:
小知识:
单引号作用: 表示字符串类型的数据
双引号作用: 主要用在别名、关键字作为名字
oracle中的不等于: <>
如果输出的文本中有单引号则使用两个单引号:select 'he''s is a boy' from dual;
①字符串的连接
1.使用concat运算符号
select concat(e.last_name,e.first_name) from employees e;
2.使用||运算符
select last_name||first_name from employees;
②like运算符查询特殊字符
首先在employees中插入两条数据:
Insert into departments values(300,’cd_lwb’,205,1700);
Insert into departments values(310,’cd%lwb’,205,1700);
由于在oracle中_代表一个字符,%代表任意字符,所以查询上面这样的特殊数据的时候
需要用到转移符关键字 escape ,如下:
select * from departments d where d.department_name like 'cd\_lwb' escape '\';
select * from departments d where d.department_name like 'cd\%lwb' escape '\';