oracle基础知识

一、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文件对口令进行管理

    ① 使用profile给用户限定输入密码的错误次数

        概述：profile是口令限制,资源限制的命令集合,当建立数据时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户。（ 有兴趣的同学，可以研究深入一点）

        举例，如果一个用户连续输入密码错误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 '\';