oracle基本操作

一.对用户的操作
1.用户创建
  create user 用户名 identified by 口令
  [default tablespace 表空间名]--用户对象保存的表空间
  [temporary tablespace 表空间名];--用户对象临时保存的表空间
  CREATE USER PQ IDENTIFIED BY PQ;
2.修改密码
  alter user 用户名 identified by 口令
  [default tablespace 表空间名]
  [temporary tablespace 表空间名];
  ALTER USER PQ IDENTIFIED BY PQ1;
3.用户管理
  ①修改用户默认表空间
    alter user 用户名 default tablespace 表空间名;
  ②修改密码
    alter user 用户名 identified by 新密码;
  ③锁定用户
    alter user 用户名 account lock;
  ④解锁用户
    alter user 用户名 account unlock;
4.删除用户
  drop user 用户名 [cascade];
  注：如果要删除的用户下存在对象，如表，视图等，如果没有cascade，系统会提示不能删除此用户，一般都是用cascade
  DROP USER PQ CASCADE;
二.权限
1.权限分类
  系统权限：系统规定用户使用数据库的权限（对用户而言）如：
  create session;create table;create sequence;create view;create procedure等
  对象权限：某种权限用户对其他用户的表或视图的存取权限（对表或视图而言）
  如：alter,select,insert,update,delete等
可以修改和查询所有表的权限
  alter any table
  select any table
2.给用户分配权限
  ①系统权限 grant 权限 to 用户名;
  ②对象权限 grant 权限 on schema.对象名(例:scott.emp) to 用户名;  
              将(scott.emp)中emp表中的权限赋给用户
3.查看当前用户所具有的权限
  ①系统权限 select * from user_sys_privs;
  ②对象权限 select * from user_tab_privs;
4.权限名
  ①系统权限
    create session        用户可以连接到数据库
    create table          用户可以创建表
    insert any table      用户可以插入表
    update any table      用户可以修改表
    unlimited tablespace  用户可以使用表空间
    create user           授权可以创建用户
    drop user             授权可以删除用户
  ②对象权限
    grant select/insert/all on 对象名 to 用户名;   将对象的查询权限/添加权限/所有权限赋给用户
    grant insert on 对象名 to public;  将插入权限赋给所有用户(all权限不包括drop)
5.将权限控制在表列级别上
  grant update(列名) on 表名 to 用户名;--只能修改表中固定列
  grant insert(列名) on 表名 to 用户名;--只能插入表中固定列
  select/delete 不能控制到列级别
6.将自己拥有的权限分配给别人
  ①系统权限 grant 权限 to 用户名 with admin option;
  ②对象权限 grant 权限 on schma.表名(例:scott.emp) to 用户名 with admin option;
7.收回权限
  ①系统权限 revoke 权限 from 用户名;
  ②对象权限 revoke 权限 on 对象名(例:emp) from 用户名;


三.对表的操作
1.建表
  ①create table 表名(
   字段名1 字段类型 [default 默认值],
   字段名2 字段类型 [default 默认值],
   ......
);
  ②create table 字段名 as select * from 表名 where 条件;
  ③tables --右键--新建表
2.插入数据
  ①insert into 表名(列名) values();
  ②insert into 表名(列名1,列名2) values('','');
  ③insert into 表名 values('','','','','','','',);(表名有几列,就插入几个值)
  ④insert into 表名 select * from emp;
3.更新数据（修改数据）
  update 表名 set 要修改的字段=新值 where 条件;
  例: update emp set comm=100 where empno=7896;
      update emp set empno=1000,sal=10 where empno=7896;
关联更新 update emp set dname=(select dname from dept where emp.deptno=dept.deptno); 
4.删除表
  drop table 表名;
5.修改表结构
  ①增加列
    alter table 表名 add(列名 列数据类型 [default 默认值]);
    例: alter table st add(address varchar(4) [default '中国']);
  ②删除列
    alter table 表名 drop column 列名;
    例: alter table emp drop column empno;
  ③修改数据类型
    alter table 表名 modify (列名 列类型 [default 默认值]);
    Ⅰ更改数据长度:大改小可以，但必须大于已有数值长度的最大值
    Ⅱ更改数据类型:要修改的列必须为空
  ④重命名
    rename 旧名 to 新名;    (oracle 特有操作)
  ⑤截断表（清空表中所有数据）
    truncate table 表名;(不能回滚,不可加where条件,删除表中所有数据)
  ⑥删除表中数据
    delete 表名 where 条件;(不添加条件会将表中数据全部删除，但是只是删除数据)
四.约束
1.五种约束
  ①主键约束--primary key(不能插入空值,不能插入重复值)
    Ⅰ.create table st(
       s_id varchar2(18) primary key,
       s_name varchar2(18),
       age number(3)
      );--创建约束
    Ⅱ. select * from st;
    Ⅲ. insert into st(age) values(27);--无法将null插入s_id
    Ⅳ. insert into st values ('9527',27);--插入值
    Ⅴ. insert into st values ('9527',27);--违反唯一约束条件
    Ⅵ. insert into st values ('9528');--没有足够的值
    Ⅶ. create table st(
        s_id varchar2(18) primery key,
        age number(3) primary key
        );--表只能具有一个主键 
    Ⅷ.使用constraint
       create table st1(
       s_id varchar2(18),
       age number(3),
       constraint st1_s_id_pk primary key(s_id)
      );--可以给约束取名字
    Ⅸ. create table st1(
       s_id varchar2(18),
       age number(3),
       primary key(s_id)
      );--主键约束由系统默认 
    
  ②唯一约束--unique(可以有多个,字段内容不允许重复)
    Ⅰ. create table st(
        s_id varchar2(18),
        age number(3) unique,
        sex varchar2(3)
        ); 
    Ⅱ. insert into st values('1','','');
    Ⅲ. insert into st values('2','','');
    Ⅳ. select * from st;--null<>null(插入空值可以）
    Ⅴ. insert into st values('3',1,'');
        insert into st values('4',1,'')--违反唯一性约束
    Ⅵ. create table st(
        s_id varchar2(18),
        age number(3),
        sex varchar2(3),
        primary key(s_id),
        constraint st_age_uk unique(age)
        );


  ③检查约束--check(判断一列中数据内容是否合法)
    Ⅰ. create table st(
        s_id varchar2(18),
        age number(3),
        sex varchar2(3) check(sex in('男','女'))
        );
    Ⅱ. insert into st(sex) values('中');--违反检查约束条件
    Ⅲ. insert into st(sex) values('女');
    Ⅳ. create table st(
        s_id varchar2(18),
        age number(3) check(age between 1 and 150),
        sex varchar2(3)
        );
    Ⅴ. insert into st(age) values(151);--违反检查约束条件
    Ⅵ. create table st(
        s_id varchar2(18),
        age number(3),
        sex varchar2(3),
        constraint st_age_ck check(age between 1 and 150)
        );
    Ⅶ. create table st(
        s_id varchar2(18),
        age number(3),
        sex varchar2(3)
        constraint st_age_ck check(age between 1 and 150)
        );--列检查约束条件无法引用其他列




  ④非空约束--not null(插入数据时必须插入,可以有多个,不能使用constraint)
    Ⅰ. create table st(
        s_id varchar2(18),  
        age number(3) not null,
        primary key(s_id)
        );
    Ⅱ. insert into st values('12345','');--无法将null插入age
    Ⅲ. insert into st values('12345',50);
    Ⅳ. create table st(
        s_id varchar2(18),
        age number(3) not null,
        sex varchar2(3) not null,
        primary key(s_id)
       );--可以多个非空
    Ⅴ. create table st(
        s_id varchar2(18),
        age number(3) not null,
        sex varchar2(3),
        primary key(s_id),
        constraint st_sex_nk not null(sex)
        );--标识符无效(非空约束不可以写在后面)




  ⑤主外键约束--foreign key
    Ⅰ. create table p(
        pid varchar2(18) primary key,
        pname varchar2(30) not null,
        age number(3) check(age between 1 and 150),
        brithdate date,
        sex varchar2(2) default '男' not null
        );
    Ⅱ. create table b(
        bid number(10) primary key,
        bname varchar(30) not null
        );
    Ⅲ. create table pb(
        pid varchar2(18),
        bid number(10),
        pb_id varchar2(18) primary key,
        constraint p_b_pid_fk foreign key(pid) references p(pid),
        constraint p_b_bid_fk foreign key(bid) references b(bid)
        ); 
    Ⅳ. select * from pb;
    Ⅴ. insert into pb values('1234567890','','',)
    Ⅵ. insert into pb values('12345678901','1',1)--未找到父项关键字
    Ⅶ. insert into p values('0001','lishi',10,'20-7月 2017','女');
        select * from p;
    Ⅷ. insert into b values(10001,'国库论');
        select * from b;
    Ⅸ. insert into pb values('201707201454','0001',10001);
        select * from pb;
        如果一列存在着检查约束和外键约束条件,需同时满足才能进行操作,
        若外键和检查没有交集,则无法对该字段进行操作.
        注:在字表设置外键必须是父表中的主键
           删除时先删除字表后删除父表
           级联删除删除的是数据--constraint p_b_bid_fk foreign key(bid) references b(bid) on delete cascade;


2.对约束的操作
  ①增加约束 alter table 表名 add constraint 约束名称 约束类型(约束字段)
            例:alter table b add constraint b_bid_pk primary key(bid);
  ②删除约束 alter table 表名 drop constraint 约束名称


ROWID 和ROWNUM
  rowid--物理地址
  rownum--排序序号
  select emp.*,rowid from emp;


  ①查询前五行
    select rownum,s.* from (select emp.* from emp order by sal desc) s where rownum<=5;
  ②查询10行以后的
    select rownum,s.* from (select emp.* from emp order by empno desc) s
    minus
    select rownum,s.* from (select emp.* from emp order by empno desc) s where rownum<=10;


    select * from (select rownum q,e.* from emp e) m where m.q between 11 and (select count(*) from emp)
    where rownum between 1 and 10--(对)
    where rownum between 2 and 10--(错)


五.对表中的内容进行操作
upper--全部大写
lower--全部小写
initcap--首字母大写
concat或||--连接符
substr()--截取字符串
length()--长度
replace()--替换
round()--四舍五入
trunc()--截取
mod()--取余
nvl()--为null设置默认值
decode()--条件判断函数
minus--求差集
union--合并
union all--合并所有
<any  小于最大值
>any  大于最小值
<all  小于最小值
>all  大于最大值


六.视图
  1.创建视图  
    create view 视图名 as 子查询
    例: create view p as select * from emp e,dept d where e.deptno=d.deptno and e.deptno=20;--重复的列名
        create view p as select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;--权限不足(赋予权限)
  2.删除视图 
    drop view 视图名称
-当删除视图所在表时,则视图也不能在使用
七.序列
1.创建序列
  create sequence 序列名称
  [maxvalue num/nomaxvalue]
  [minvalue num/nominvalue]
  [increment by 步长 start with 开始位]
  [cache num/nocache]
  [cycle/nocycle]
  空值分割
  如果没有start with默认从最小值开始,如果没有最小值则从1开始


2.序列参数
  maxvalue num--序列最大值
  nomaxvalue--不设置最大值
  minvalue num--序列最小值
  nominvalue--没有最小值
  increment by 1--每次加10个
  start with 1--从1开始计数
  cache 10--设置缓存10个序列
  nocache--不设置缓存
  cycle--到头循环
  nocycle--一直累加,不循环
  
例题  create sequence seq minvalue 1 increment by 3 start with 1;
      select seq.currval,seq.nextval from dual;
      create table stab(id number(10))
      insert into stab values(seq.nextval);--执行六次
      select * from stab;
      drop table stab;
      create table stab(id varchar2(40) primary key);
      insert into stab values(to_char(sysdate,'yyyymmdd')||seq.nextval);--执行六次
      先执行select seq.nextval from dual;
      才能执行select seq.nextval from dual;
     例题:创建学员注册表,学号编号,长度为16位,通过时间戳和序列产生
            create sequence stu minvalue 1;
            create table stude(id varchar2(16) primary key);
     insert into stude values
         (to_char(sysdate,'yyyymmddhhss')||trim(to_char(stu.nextval,'0000')));
            select * from stude;
     insert into stude values
(to_char(sysdate,'yyyymmddhhss')||to_char(to_date(stu.nextval,'yyyy'),'yyyy'))
  select trim('     qwq     ') from dual;--去掉两边空格
  select ltrim('     qwq     ') from dual;--去掉左边空格
  select rtrim('     qwq     ') from dual;--去掉右边空格
八.索引
使用场景
  B树索引:distinct 的值接近count(*)的时候
  位图索引:重复值较多的情况
1.创建索引(默认B树索引)
  create [unique] index 索引名 on 表名(列名1[asc/desc],列名2[asc/desc]......)
  例: create index demo_index on demo(sal);
      select * from demo where sal=9291;
      unique定义的是唯一索引确保在定义索引的列中没有重复的值
      默认asc,oracle自动为主键和唯一键列创建唯一索引
      创建索引尽量在数据插入以后,最好用于查询比较频繁的数据
      create bitmap index demo_dit on demo1(sal);--位图索引
2.修改索引
  alter index [schema.]索引名
  [initrans integer]
  [maxtrans integer]
  rebuild
  [storage storage_clause]
  可以通过上述语句重建索引,也可以先删除索引再建立新的索引.
3.删除索引
  drop index 索引名