关闭

数据库基础知识点一:Oracle入门,数据库基本操作,函数

129人阅读 评论(0) 收藏 举报
分类:

一、Oracle入门知识:

1.Oracle:

      常见数据库:

               Oracle(*)  IBM db2   sybase   MS

    SQLServer  Postgre  IBM   Informix

    mysql(*): 灵活的 Access

  

Oracle简化版的:XE     

 

Oracle完整版: 9i  10g 11g  12g

   

2.多个数据库实例

           实例:(Database Instance):指的是oracle内存结构和后台进程,被称为数据库实例。

默认数据库: orcl  新建数据库实例

          

 

 

3.oracle数据库的常用字段类型:

   数值:number   number(7,2)

   字符串:varchar2(20)  varchar(20)  char(2)

   日期:  date(年月日)  timestamp(年月日时分秒

   大的二进制数据类型: blob  clob

 

 

 

4.建表:powerdesigner:建表模型工具   建模工具

        生成物理模型(mysql)

  概念模型/逻辑模型---------------->物理模型()

  概念模型:实体对象 connectualdata model       *.cdm

  逻辑模型:实体对象  logic data model          *.ldm

  物理模型:  pysal data model                 *.pdm


check  model:检查模型是否正确.

 概念模型(connectual data model)-----check model(检查概念模型是否正确 ok)-------Tools----生成物理模型 ()-------数据库(database)-------生成数据库建表脚本.

5.连接oracle的工具:  sqldevloerper



二、数据库基本操作

 创建表空间:

create tablespace 名称

 datafile '文件路径'

 size 10M;

 

create user 用户名

IDENTIFIED by 密码

default  tablespace 表空间名称;

 

create  role 角色名称;

 

grant to ...

revoke from ....

 

  注意:所有上面操作 可以通过工具完成.

 

 

-- sql语句

--创建表空间 tablespace,实际存储的是表的数据

 -- oracle中 关键字 大小不敏感的

 -- 但是数据是敏感的

 -- oracle自带表空间(users  temp)

 

 

 

 create tablespace  tablespace_5b

datafile 'D:\oracle\oradata\XE\5b.DBF'

SIZE 10M;

 

 

 

 --为用户创建表的时候  存放对应用户表数据

 --  创建用户

create user user_4b

IDENTIFIED by  user_4b

default tablespace tablespace_4b;

 

 

  --给用户 分配 角色 或者权限

  -- 角色==权限的集合 

  -- oracle自带角色(dba(所有权限),resource(正式用户),connect(临时用户))

grant connectto   user_4b;

grant RESOURCE to   user_4b;

grant DBA to   user_4b;

grant select on system.emp  to user_4b;

   


  --撤销权限或者角色

 

    revoke dba from user_4b;

    revoke RESOURCE from user_4b;

    revoke select on system.emp   from user_4b;

 


-- 修改表结构

     create table test_4b(

     id number primary key,

     name varchar2(20),

     age number(3),

     sex char(5),

     birthday date );

 

 

-- DDL(数据定义语言 create alter drop ....)

 alter table test_4b add(jobvarchar2(20));  

 alter table test_4b add(dno number);  

 alter table test_4b modify(job varchar2(50));

 alter table test_4b drop column job;

 

 

 

-- 添加约束

  -- 主键约束==唯一+非空  pk

    alter table test_4b add constraint  system_test_4b_pk_id primary key(id);

 

  --外键约束==   references 父表(字段)  fk

  alter table test_4b add

  constraint system_test_4b_fk_dno  foreign key(dno)  references dept(dno);

 

  -- 唯一约束==  unique uk

   alter table test_4b add constraintsystem_test_4b_uk_name unique(name);

 

  --非空约束== not null  nk 只能在建表的时候 添加

  --检查约束==  范围限制  值限制 

  alter table test_4b add constraintsystem_test_4b_ck_age  check(age between1 and 150);

  alter table test_4b addconstraint system_test_4b_ck_sex check(sex in('',''));

  insert into test_4bvalues(2,'bbb',20,'',sysdate,10);


三、函数:

-- oracle中的表:

  -- 复制表

   create table emp_copy

    as

    select * from emp where 1=1;

  -- 根据多列 排序 

   select * from emp order by age desc,empnoasc; 

  --oracle中常用函数或者短语

   -- in() between  and  

  select * from emp where job='老板'or  job='程序员';

  select * from emp where job in('老板','程序员');

  select * from emp where salary between  2000  and  20000;

  --  sysdate :当前日期

   select distinct sysdate   当前日期  from emp;

  -- dual: 一行

  select sysdate from  dual;

  -- to_char():转换成字符串显示  日期--字符串

  select to_char(sysdate,'yyyy-MM-dd') now  from dual;

 -- to_date(): 将字符串 转换成日期

  insert intoemp(empno,ename,job,salary,hiredate)

  values(1003,'张飞','程序员',23456.75,to_date('2012-12-12','yyyy-MM-dd')); 

 --   commit; 提交

  -- to_number(); 转换成数值

--case  when  

 --  10部门   20%

 -- 20部门     10%

 ---其他部门   不变

   select ename,dno,salary,case

          when  dno=10 then salary*1.2

          when  dno=20  then salary*1.1

          else  salary*1 

          end new_salary

            from emp;

 --空值转换函数

  

   selectsum(nvl(bonus,0)),count(*),avg(nvl(bonus,0)) from emp;        

  select nvl(bonus,0) from emp;

   -- null出现在 单行表达式中进行运算 结果还是null 

  selectename,salary,bonus,(salary+nvl(bonus,0))*12 year_salary from emp;

-- 单行函数:表中有一行数据  单行函数执行一次

 

-- 组函数: 一组才执行一次 count(*)    sum()  avg() max()  min()   分组中使用比较多

 -- null做是否相等比较 用 is   否定形式is  not

  selectsum(salary),avg(salary),max(salary),min(salary),count(empno) from emp wherebonus is not null;

 

-- oracle 中的过滤查询条件  where  and  or

-- oracle中的分组  group  by  having

 -- where:行过滤 

 -- having:过滤的是分组后结果  having前面一定有group  by分组

 -- 薪水大于2000的员工中  每个职位的人数多少  按照人数降序排序

  select job,count(*) from emp where  salary>=2000 group by job  having count(*)>1  order by count(*)  desc;

-- 子查询

-- 非关联子查询    子查询 只执行一次

  --  一次将子查询全部执行完毕  带入到主查询  继续执行 

    --  谁的薪水最大?

    select ename from emp where  salary=(select  max(salary) from emp);

 -- 谁的薪水 等于他所在部门的平均薪水?

     select ename

       from emp where (dno,salary)

       in(select dno,avg(salary) from emp groupby dno);

    select ename from emp where salary=

     (select  salary from emp where ename='张无忌')

      and job=(

     select job from emp whereename='张无忌') and ename!='张无忌';

  -- 那些部门的人数 大于 10部门的人数?

   select dno

    from emp group by  dno  having count(*)>

   (select count(*) from emp where dno=20);

 

 

-- 关联子查询子查询执行次数 取决于 主查询条数

  -- -----------------..........

  -- 先从主查询的第一条记录开始 代入到子查询计算出一个结果

  --  然后将子查询结果传回到主查询继续执行

    

 -- 谁的薪水 大于所在部门的平均薪水?

    select e.ename

      from emp e where salary>

      (select avg(salary) from emp wheredno=20); 

  -- 子查询可以出现 where having之后 

  ----  可以出现在select 后面 

  ---- 可以出现在from后面

  --谁的薪水和职位 和张无忌 相同  但是不能是张无忌本人?

  -- exists:存在

  -- not  exists: 不存在

   -- 那些部门有员工(部门号 部门名称)?

    -- 子查询中where条件成立性 存在

     select d.dno,d.dname

    from dept d where exists(select 1111 fromemp e where e.dno=d.dno);

  -- 那些部门没有员工? --  子查询中where条件成立性 不存在

         select d.dno,d.dname

    from dept d where not  exists(select 1111 from emp e wheree.dno=d.dno);

--  多表的内外连接?

  -- 内连接inner  join

    --  在多个表中每个表都找到匹配 才能查询出来

   select  dname,count(*),sum(salary)

     from emp e inner  join dept d

       on e.dno=d.dno group by  dname having count(*)>=1 order by count(*)desc; 

  -- 权限管理:

     create table user2

     (id number primary key,

     username varchar2(20),

     pwd varchar2(20));

    

     insert into user2

      values(1,'张三','12345');

     

       insert into user2

      values(2,'李四','34567');

     

    

    

    

     create table role2

     (rid number primary key,

     rname varchar2(20),

     rdesc varchar2(20));

    

    insert into role2

     values(10,'开发工程师','写代码');

    

     insert into role2

     values(20,'项目经理','把控项目进度');

 

    insert into role2

     values(30,'项目总监','负责成本预算');

    

     insert into role2

     values(40,'超级管理员','管理所有权限');

      

   

     -- 一个用户多个角色

     -- 一个角色 有多个用户

    create table u_r(

       id number,

       rid number,

       foreign key(id)references user2(id),

       foreign key(rid)references role2(rid),

       primary key(id,rid)

     )

      insert into  u_r values(1,10);

      insert into  u_r values(2,20);

      insert into  u_r values(2,30);

      insert into  u_r values(2,40);

 

 

 

 

   

     create table privalige(

       pid number primary key,

       pname varchar2(20),

       url varchar2(50),

       image varchar2(50),

       parent_id  number

     );

    

      insert into privaligevalues(101,'查看需求','select',null,null);

      insert into privaligevalues(102,'修改需求','update',null,null);

      insert into privaligevalues(103,'添加用户','addUser',null,null);

      insert into privaligevalues(104,'删除用户','deleteUser',null,null);

      insert into privaligevalues(105,'成更新本','update',null,null);

      insert into privaligevalues(106,'查看bug','selectBug',null,null);

    

     create table r_p(

       rid number,

       pid number,

       foreign key(rid)references role2(rid),

       foreign key(pid)referencesprivalige(pid),

       primary key(rid,pid)

     );

    

      insert into r_p values(40,101);

      insert into r_p values(40,102);

      insert into r_p values(40,103);

      insert into r_p values(40,104);

      insert into r_p values(40,105);

      insert into r_p values(40,106);

     

      insert into r_p values(10,101);

      insert into r_p values(10,106);

  

     

      insert into r_p values(20,101);

      insert into r_p values(20,102);

      insert into r_p values(20,106);

    

    

      insert into r_p values(30,101);

      insert into r_p values(30,102);

      insert into r_p values(30,105);

      insert into r_p values(30,106);

    

 -- 显示 李四这个用户的 的所有权限 ?

   select distinct u.username,r.rname,p.pname

    from user2 u join u_r  ur 

     on u.id=ur.id andu.username='张三' join role2 r on

       ur.rid=r.rid join  r_p rp on

         rp.rid=r.rid join privalige p on

         rp.pid=p.pid; 

 

-- 外链接

  -- 左外left  outer join    右外right  join      全外 full join=左外 +右外

-- 左别的表全部都会查询出来

 

 -- 查询所有的部门的人数:

   select dname,count(*)

     from emp e join dept d on e.dno=d.dno

     group by dname;

 -- 外连接

 select dname,count(e.empno)

   from dept  d left join emp e

     on e.dno=d.dno group by dname;

 

selectdname,count(e.empno)

   from emp  e right join dept d

     on e.dno=d.dno group by dname;

 

selectdname,count(e.empno)

   from emp  e full join dept d

     on e.dno=d.dno group by dname;

 

-- oracle集合并集(union all/union) 交集(interect )  差集(minus)

  select * from emp where dno in(10)

  union all

   select * from emp where dno in(10,20);

 

   select * from emp where dno in(10)

  union

   select * from emp where dno in(10,20);

 

 

   select * from emp where dno in(20)

  intersect

   select * from emp where dno in(10,20);

 

   select * from emp where dno in(10,20)

   minus

   select * from emp where dno in(10);

-- oracle的俩个伪列

   --  rowid: 每条记录具体物理地址  通过rowid查询最快

   -- 数据库(XE)-----表空间(tablespace)--------------------(block)-----rowid

   select rowid,e.* from emp e;

    select * from emp whererowid='AAADVHAABAAAKU6AAB';

 --rownum: 行号   1开始

   -- 前俩行数据

  select rownum,e.* from emp e whererownum<=2;

  -- 第二 到第三行?  rownum---->rn  固化

     select * from  

     (select rownum rn,e.* from emp e)

     where rn>=2 and rn<=3;

  -- 按照薪水降序排序后 的第2到第3?

       select * from(

       select rownum rn,e.*from(

       select * from emp order by salary desc)e)

       where rn>=2 and rn<=3;       

   -- 一个角色有多个权限

   -- 一个权限可以属于多个角色





 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:11119次
    • 积分:396
    • 等级:
    • 排名:千里之外
    • 原创:28篇
    • 转载:4篇
    • 译文:1篇
    • 评论:0条
    文章分类