当前位置:Gxlcms > mysql > MySQL/Oracle数据库的基础(二)

MySQL/Oracle数据库的基础(二)

时间:2021-07-01 10:21:17 帮助过:24人阅读

MySQL/Oracle数据库 Oracle数据库管理系统是管理数据库访问的计算机软件,由Oracle数据库与Oracle实例构成 Oracle数据库:一个相关的操作系统文件集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。Oracle数据库必须要与内存实例合作,才能对外提供

MySQL/Oracle数据库

  • Oracle数据库管理系统是管理数据库访问的计算机软件,由Oracle数据库与Oracle实例构成
    • Oracle数据库:一个相关的操作系统文件集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。Oracle数据库必须要与内存实例合作,才能对外提供数据管理服务。
    • Oracle实例:位于物理内存里的数据结构,它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被进程锁访问。
      • Oracle用它们来管理数据库访问
      • Oracle实例就是平常所说的数据库服务(service)
    • 实例可以操作数据库;在任何时刻一个实例只能与一个数据库关联,访问一个数据库;同一个数据库可由多个实例访问(RAC)
  • Oracle区别于MySQL的:
    • 在Oracle数据库中,使用NUMBER表示数字类型、DATE表示日期类型、VARCHAR2表示字符类型
  • 空值不同于0,凡是空值参与的运算,结果都为空(null),空值是无效的,未指定的
  • 别名的创建:(方式三)
  • 使用双引号”“,如:将别名原封不动的显示(尤其是别名由多个单词构成)

    select employee_id "id",last_name "Name",12*salary "annual_sal"
    from employees;
    
  • 连接符:

    • 把列与列,列与字符连接在一起
    • 用”||”来连接
    • 可以用来“合成”列
    • 如:

      select first_name || ' ' || last_name from employees;
      
  • 字符串:
    • 字符串可以是select列表中的一个字符、数字、日期
    • 日期和字符只能在【单引号】中出现
    • 每当返回一行时,字符串被输出一次
  • 使用distinct能够过滤掉重复的数据,如:

    select distinct dept_id from empt;
    
  • SQL语句与SQL*Plus命令:
    • SQL:一种语言、ANSI标准、关键字不能缩写、使用语句控制数据库中的表的定义和表中的数据。
    • SQL*Plus:一种环境、Oracle的特性之一、关键字可以缩写(如:edit(ed)、describe(desc))、命令不能改变数据库中的数据的值、集中运行
  • 数据库操作函数:

    • 单行函数

      • 单行函数分类:字符、数值、日期、转换、通用

        单行函数:①操作数据对象;
                ②接收参数返回一个结果
                ③只对一行进行变换
                ④每行返回一个结果
                ⑤可以嵌套与转换数据类型
                ⑥参数可以是一列或一个值
        
      • 字符函数:

        • 字符大小控制函数,如:

          LOWER('HELLOWORLD')             //helloworld
          UPPER('hello')                  //HELLO
          initcap('helloWorld you')       //Helloworld You,首个字母大写
          
        • 字符控制函数,如:

          CONCAT('Hello', 'World')        //HelloWorld
          SUBSTR('HelloWorld',1,5)        //Hello
          LENGTH(('HelloWorld')           //10
          INSTR('HelloWorld','W')     //6
          LPAD(24000,10,'*')              //*****24000
          RPAD(24000,10,'*')              //24000*****
          TRIM('H' FROM 'HelloHWorldH')   //elloHWorld
          REPLACE('abcd','b','m')     //amcd
          
      • 数值函数:

        • round:四射五入

          round(45.926,2)     //45.93
          round(45.926,-1)    //50
          
        • trunc:截断

          trunc(45.926,2)     //45.92
          
        • 求余:

          MOD(1600,300)       //100
          
      • 日期函数:
        • Oracle中的日期型数据实际含有两个值:日期和时间
          • 日期:函数sysdate返回
          • 两个日期相减返回日期之间相差的天数(但是不允许做加法运算)
        • MONTHS_BETWEEN:两个日期相差的月数
        • ADD_MONTHS:向指定日期中加上若干月数
        • NEXT_DAY:指定日期的下一个星期*对应的日期
        • LAST_DAY:本月的最后一天
        • ROUND:日期四舍五入
        • TRUNC:日期截断
      • 转换函数:

        • 隐性(Oracle自动完成下列转换)

          VARCHAR2 or CHAR ---------------------> NUMBER
          VARCHAR2 or CHAR ---------------------> DATE
          NUMBER  ------------------------------> VARCHAR2
          DATE    ------------------------------> VARCHAR2
          
        • 显性

          • DATA→to_char()→CHARACTER→to_date()→DATE

            select to_char(sysdate,'yyyy-mm-dd') from dual;
            select to_date('1995-05-23', 'yyyy-mm-dd') from dual;
            //在用到字符时使用双引号,如:
            select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
            
          • NUMBER→to_char()→CHARACTER→to_number()→NUMBER

            //1,234,567.890,'$999,999,999,999.999'表示美元,
            //'L999,999,999,999.999',表示当地货币符(L表示local)
            select to_char(1234567.89,'999,999,999,999.999') from dual;
            select to_number('Y001,234,567.89','L000,000,999.99') from dual;
            

            在数值的转化中,to_char()函数经常使用的集中格式:

             - 9:数字
             - 0:零
             - $:美元符
             - L:本地货币符号
             - .:表示小数点
             - ,:表示千位符
            
      • 通用函数:

        • 该类函数适用于任何数据类型,同时也适用于空值
        • NVL(expr1,expr2):

          • 将空值转换成一个已知的值
          • 可以使用的数据类型有日期、字符、数值
          • 函数的形式:

            NVL(commission_pct, 0)  //表示为当commission_pct为null时其值为0,否则等于commission_pct
            NVL(hire_date,'01-JAN-97')
            
        • NVL2(expr1,expr2,expr3):expr1不为NULL,返回expr2;为NULL,返回expr3
        • NULLIF(expr1,expr2):相等返回NULL,不等返回expr1
        • COALESCE(expr1,expr2,…,exprn):如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE.
          • COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值
      • 条件表达式:

        • 在SQL语句中使用IF-THEN-ELSE逻辑
        • 使用的两种方法:

          • case表达式:

            • 格式:

              case expr when comparison_expr1 then return_expr1
                  [when comparison_expr2 then return_expr2
                  ...]
                  else else_expr
              end 
              
            • 如:

              select department_id,
              case department_id when 10 then salary*1.1
                                 when 20 then salary*1.2
                                 else salary*1.3
              end
              from employees
              where department_id in(10,20,30);
              
        • decode函数:

          • 如:

            select department_id,
            decode(department_id, 10, 1.1*salary,
                                  20,1.2*salary,
                                  30,1.3*salary)
            from employees;
            
  • 嵌套组函数:

    • 如:显示各部门平均工资中的最大值

      select max(avg(salary))
      from employees
      group by department_id;
      
  • Oracle数据库子查询:

    • 子查询语法:

      select select_list
      from table
      where expr operator(
          select select_list2
          from table2;        //table与table2可以是同一个表
      );
      
    • 子查询在主查询之前一次执行完成,子查询的结果被主查询使用
    • 注意:

      1.子查询要包括在括号内
      2.将子查询放在比较条件的右侧
      3.子查询中可以使用组函数
      4.子查询中要留意空值
      
    • 单行子查询使用的单行比较操作符:

      =、>、>=、<、<=、<>(不等于)
      
      • 如:

        select salary,last_name
        from employees
        where salary > (select salary
                        from employees
                        where last_name='Abel');
        
    • 多行子查询使用的多行操作符:

      如:in、any(和子查询返回的某一个值比较)、all(和子查询返回的所有值比较)
      
    • 综合子查询嵌套

                  //查询平均工资最低的部门信息
      select *
      from departments
      where department_id = (select department_id
                             from employees
                             group by department_id
                             having avg(salary) = (select min(avg(salary))
                                                   from employees
                                                   group by department_id
                                                  )
                             )
      
  • 创建和管理表(DDL)(create、alter、drop、rename、truncate)→操作皆不可回滚

    • Oracle数据库中的表

      • 用户定义的表

        • 用户自己创建并维护的一组表
        • 包含了用户所需的信息
        • 常用的查询字典命令:

          select * from user_tables;  //查看用户创建的表有哪些
          select table_name from user_tables; //查看用户创建的表的表名
          select distinct object_type from user_objects;  //查看用户定义的各种数据库对象
          select * from user_catalog;     //查看用户定义的表,视图,同义词和序列
          
      • 数据字典
        • 由Oracle Server自动创建的一组表
        • 包含数据库信息
    • 创建表的方式:

      • 方式一:直接创建空表

        create table myemploy(
            id number(5),
            name varchar2(20),          //注意:定义字符串使用的是varchar2
            salary number(10,2),        //表示10位数据,其中两位是小数
            hire_date date
        );
        
      • 方式二 :使用子查询创建表

        • 指定的列和子查询中的列要一一对应
        • 通过列名和默认值定义列

          //方式二:从其他的表中抽取部分列生成新表
          create table empt
          as 
          select employee_id,last_name,salary,hire_date
          from employees;
          
          //注意,上表生成的信息会把原来的表中的数据也复制过来,若想不生成
          数据,则可加上不等的过滤条件,如:
              create table employee
              as 
              select employee_id,last_name,salary,hire_date
              from employees
              where 1=3;              //因为2不可能等于3,故创建的表为有相应结构而无数据的空表
          
  • 数据处理(DML)(insert into、delete from、upddate、select)→操作可回滚

    • 插入数据的特殊方式:从其他表中拷贝数据,如:

      insert into emp(employee_id,last_name,salary,hire_date)
      select employee_id,last_ame,salary,hire_date
      from employees
      where employee_id < 20;     //加上限制条件表示仅复制部分数据
      
    • 创建脚本:在SQL语句中使用&变量指定列值,&变量放在values子句中,如:

      //该插入方法是在执行语句后来添加数据的
      insert into emp(employee_id,last_name,salary,hire_date)
      values(&employee_id,'&last_name',&salary,&hire_date);
      

      -update:update不加限制条件更新的是整个列的数据

    • 在update中,可以使用子查询时更新基于另一个表中的数据,如:

          upadate emp_copy
          set departmen_id = (select departmen_id
                              from employees
                              where employee_id = 20
                              )
          where job_id = (select job_id
                          from employees
                          where employee_id = 10
                          );
      
    • 数据增删改查相关的操作
      • commit(提交):类似于文件保存
      • rollback(回滚):当操作数据不当时,可以通过回滚回到修改前的数据
    • 数据库事务:

      • 以第一个DML语句的执行作为开始
      • 以下面的其中之一作为结束:

        • commit或rollback语句

          commit与rollback语句的优点:
              1.确保数据完整性
              2.数据改变被提交之前预览
              3.将逻辑上相关的操作分组
          在进行增删改操作时可以设置保存点,如:
                  savepoint A;
          在未commit之前,进行多步(增删改)操作后,可以直接通过保存点回滚到保存点处,如:
              rollback to savepoint A;
          注意:回滚只能在commit之前进行,在commit之后是无效的
          
        • DDL语句(自动提交)
        • 用户会话正常结束
        • 系统异常终止
      • 提交或回滚前的数据状态
        • 改变前的数据状态是可以恢复的
        • 执行DML操作的用户可以通过delect语句查询之前的修正
        • 其他用户不能看到当前用户所做的改变,直到当前用户结束事物
        • DML语句所涉及到的行被锁定,其他用户不能操作
    • 数据库操作(增删改查)完成后最好执行commit。
  • Oracle数据库创建约束:

    • 非空约束(not null),如:

      create table emp(
          id number(10) constraint emp_id_nn not null,    //emp_id_nn表示约束名
          name varchar(20),
          salary number(10,2)
      )
      
    • 唯一约束(unique),如:

      create table emp(
          id number(10) constraint emp_id_uq unique,  //列级约束,emp_id_uq约束名
          name varchar(20) constraint,
          salary number(20),
          phone number(11),
          //表级约束
          constraint emp_phone_uq unique(phone)       //括号内指明约束列
      )
      
    • 主键约束(primary key),如:

      create table emp(
          id number(5) constraint emp_id_pk primary key,
          name varchar(20),
          salary number(9,2)
          //或者使用表级约束
          //constraint emp_id_pk primary key(id)
      )
      
    • 外键约束(foreign key):连接两个表,将两个表进行关联

      • foreign key:在表级指定子表中的列
      • references:标示在父表中的列
      • 如:

        create table empt(
            id number(5),
            name varchar(20),
            salary number(7,2),
            dept_id number(5),
            constraint emp_dept_fk foreign key(dept_id) references depts(dept_id) 
        )
        create table depts(
            dept_id number(5),
            dept_name varchar(10),
            job_id number(3)
        )
        
    • 删除数据时,父表中的列被删除时,子表(外键关联的)中对应的列也被删除
    • 添加约束的语法:

      • 使用alter table语句:

        1.添加或删除约束,但是不能修改约束
        2.有效化或无效化约束
        3.添加not null约束要使用modify语句
        
      • 格式:

        alter table table_name
        add(drop...) [constraint constraint_name] type (column);
        
      • 如:

        //添加不为空
        alter table emp
        modify salary number(8,2) not null;
        //添加其他的约束
        alter table emp
        add constraint emp_uq unique(name);
        //删除约束
        alter table emp
        drop constraint emp_uq;
        //无效化约束
        alter table emp
        disable constraint emp_uq;
        //有效化(注意,enable的前提是约束所在的列不存在对应的约束问题)
        alter table emp
        enable constraint emp_uq;
        
    • 查询约束:

      • user_constraints
      • 如:

        //查询约束名、约束类型、约束条件
        select constraint_name,constraint_type,search_condition 
        from user_constraints
        where table_name='employees';   //''其内的表示表名
        
      • 查询定义约束的列:

        • user_cons_columns
        • 如:

          select constraint_name,column_name
          from user_cons_columns
          where table_name='employees';
          
  • 视图:

    • 视图是一种虚表,视图建立在已有表的基础上
    • 向视图提供数据内容的语句为SELECT语句
    • 视图的优点:
      • 控制数据访问
      • 简化查询
      • 避免重复访问相同的数据
    • 视图中使用DML的规定

      • 可以在简单视图中执行DML操作
      • 当视图定义中包含以下元素之一时不能使用delete:
        • 组函数
        • group by子句
        • distinct关联字
        • rownum伪列(表示原来表中不存在的列)
      • 如:

        create or replace view emp_view
        as select
        avg(salary) avg_sal from employees
        group by department_id;
        
  • 序列:可供多个用户用来产生唯一数值的数据库对象

    • 自动提供唯一的数值
    • 共享对象
    • 主要用于提供主键值
    • 将序列值装入内存可以提高访问效率
    • 创建格式:

      create sequence seq_name
      [increment by n]    //每次增长的数值
      [start wiht n]      //从哪个值开始
      [{maxvalue n | nomaxvalue}]
      [{minvalue n | nominvalue}]
      [{cycle | nocycle}]     //是否需要循环
      [{cache n | nocache}]   //是否缓存登录
      
    • 如:

      create sequence emp_seq
      increment by 5
      start with 2
      maxvalue 50
      cycle
      nochche;
      
    • 序列的执行方法:

      1.select emp_sql.nextval from dual; 
      2.select emp_sql.curval from dual;
      
    • 注意:首次调用方法时,需要闲滴啊用nextval.
    • 序列的修改(不能修改初始值)

      alter sequence seq_name
      increment by newval
      nomaxvalue
      ...;    
      
    • 修改序列注意:
      • 1.必须是序列的拥有者或对序列有alter权限
      • 2.只有将来的序列值会被改变
      • 3.改变序列的’初始值’只能通过删除序列之后重新建序列的方法实现
      • 4.rollback是无法回滚序列的

人气教程排行