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

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),空值是无效的,未指定的
• 别名的创建：(方式三)

• 使用双引号”“,如:将别名原封不动的显示(尤其是别名由多个单词构成)

  <code>select employee_id "id",last_name "Name",12*salary "annual_sal"from employees;</code>

• 连接符：

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

  • 如：

    <code>select first_name || ' ' || last_name from employees;</code>

• 字符串：
  • 字符串可以是select列表中的一个字符、数字、日期
  • 日期和字符只能在【单引号】中出现
  • 每当返回一行时，字符串被输出一次

• 使用distinct能够过滤掉重复的数据，如：

  <code>select distinct dept_id from empt;</code>

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

• 数据库操作函数：

  • 单行函数

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

      <code>单行函数：①操作数据对象；        ②接收参数返回一个结果        ③只对一行进行变换        ④每行返回一个结果        ⑤可以嵌套与转换数据类型        ⑥参数可以是一列或一个值</code>

    • 字符函数：

      • 字符大小控制函数，如：

        <code>LOWER('HELLOWORLD')             //helloworldUPPER('hello')                  //HELLOinitcap('helloWorld you')       //Helloworld You,首个字母大写</code>

      • 字符控制函数，如：

        <code>CONCAT('Hello', 'World')        //HelloWorldSUBSTR('HelloWorld',1,5)        //HelloLENGTH(('HelloWorld')           //10INSTR('HelloWorld','W')     //6LPAD(24000,10,'*')              //*****24000RPAD(24000,10,'*')              //24000*****TRIM('H' FROM 'HelloHWorldH')   //elloHWorldREPLACE('abcd','b','m')     //amcd</code>

    • 数值函数：

      • round：四射五入

        <code>round(45.926,2)     //45.93round(45.926,-1)    //50</code>

      • trunc：截断

        <code>trunc(45.926,2)     //45.92</code>

      • 求余：

        <code>MOD(1600,300)       //100</code>

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

    • 转换函数：

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

        <code>VARCHAR2 or CHAR ---------------------> NUMBERVARCHAR2 or CHAR ---------------------> DATENUMBER  ------------------------------> VARCHAR2DATE    ------------------------------> VARCHAR2</code>

      • 显性

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

          <code>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;</code>

        • NUMBER→to_char()→CHARACTER→to_number()→NUMBER

          <code>//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;</code>

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

          <code> - 9：数字 - 0：零 - $：美元符 - L：本地货币符号 - .：表示小数点 - ,：表示千位符</code>

    • 通用函数：

      • 该类函数适用于任何数据类型,同时也适用于空值

      • NVL(expr1,expr2)：

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

        • 函数的形式：

          <code>NVL(commission_pct, 0)  //表示为当commission_pct为null时其值为0,否则等于commission_pctNVL(hire_date,'01-JAN-97')</code>

      • 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表达式：

          • 格式：

            <code>case expr when comparison_expr1 then return_expr1    [when comparison_expr2 then return_expr2    ...]    else else_exprend </code>

          • 如：

            <code>select department_id,case department_id when 10 then salary*1.1                   when 20 then salary*1.2                   else salary*1.3endfrom employeeswhere department_id in(10,20,30);</code>

      • decode函数：

        • 如：

          <code>select department_id,decode(department_id, 10, 1.1*salary,                      20,1.2*salary,                      30,1.3*salary)from employees;</code>

• 嵌套组函数：

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

    <code>select max(avg(salary))from employeesgroup by department_id;</code>

• Oracle数据库子查询：

  • 子查询语法：

    <code>select select_listfrom tablewhere expr operator(    select select_list2    from table2;        //table与table2可以是同一个表);</code>

  • 子查询在主查询之前一次执行完成，子查询的结果被主查询使用

  • 注意：

    <code>1.子查询要包括在括号内2.将子查询放在比较条件的右侧3.子查询中可以使用组函数4.子查询中要留意空值</code>

  • 单行子查询使用的单行比较操作符：

    <code>=、>、>=、<、<=、(不等于)</code>

    • 如：

      <code>select salary,last_namefrom employeeswhere salary > (select salary                from employees                where last_name='Abel');</code>

  • 多行子查询使用的多行操作符：

    <code>如:in、any(和子查询返回的某一个值比较)、all(和子查询返回的所有值比较)</code>

  • 综合子查询嵌套

    <code>            //查询平均工资最低的部门信息select *from departmentswhere department_id = (select department_id                       from employees                       group by department_id                       having avg(salary) = (select min(avg(salary))                                             from employees                                             group by department_id<mark style="color:transparent">本文来源gaodaimacom搞#^代%!码网@</mark>                                            )                       )</code>

• 创建和管理表(DDL)(create、alter、drop、rename、truncate)→操作皆不可回滚

  • Oracle数据库中的表

    • 用户定义的表

      • 用户自己创建并维护的一组表
      • 包含了用户所需的信息

      • 常用的查询字典命令:

        <code>select * from user_tables;  //查看用户创建的表有哪些select table_name from user_tables; //查看用户创建的表的表名select distinct object_type from user_objects;  //查看用户定义的各种数据库对象select * from user_catalog;     //查看用户定义的表,视图,同义词和序列</code>

    • 数据字典
      • 由Oracle Server自动创建的一组表
      • 包含数据库信息

  • 创建表的方式：

    • 方式一：直接创建空表

      <code>create table myemploy(    id number(5),    name varchar2(20),          //注意：定义字符串使用的是varchar2    salary number(10,2),        //表示10位数据,其中两位是小数    hire_date date);</code>

    • 方式二 ：使用子查询创建表

      • 指定的列和子查询中的列要一一对应

      • 通过列名和默认值定义列

        <code>//方式二:从其他的表中抽取部分列生成新表create table emptas select employee_id,last_name,salary,hire_datefrom employees;//注意,上表生成的信息会把原来的表中的数据也复制过来,若想不生成数据,则可加上不等的过滤条件,如:    create table employee    as     select employee_id,last_name,salary,hire_date    from employees    where 1=3;              //因为2不可能等于3,故创建的表为有相应结构而无数据的空表</code>

• 数据处理(DML)(insert into、delete from、upddate、select)→操作可回滚

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

    <code>insert into emp(employee_id,last_name,salary,hire_date)select employee_id,last_ame,salary,hire_datefrom employeeswhere employee_id < 20;     //加上限制条件表示仅复制部分数据</code>

  • 创建脚本:在SQL语句中使用&变量指定列值,&变量放在values子句中，如：

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

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

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

    <code>    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                    );</code>

  • 数据增删改查相关的操作
    • commit(提交):类似于文件保存
    • rollback(回滚):当操作数据不当时,可以通过回滚回到修改前的数据

  • 数据库事务：

    • 以第一个DML语句的执行作为开始

    • 以下面的其中之一作为结束:

      • commit或rollback语句

        <code>commit与rollback语句的优点:    1.确保数据完整性    2.数据改变被提交之前预览    3.将逻辑上相关的操作分组在进行增删改操作时可以设置保存点,如:        savepoint A;在未commit之前,进行多步(增删改)操作后,可以直接通过保存点回滚到保存点处,如:    rollback to savepoint A;注意：回滚只能在commit之前进行,在commit之后是无效的</code>

      • DDL语句(自动提交)
      • 用户会话正常结束
      • 系统异常终止
    • 提交或回滚前的数据状态
      • 改变前的数据状态是可以恢复的
      • 执行DML操作的用户可以通过delect语句查询之前的修正
      • 其他用户不能看到当前用户所做的改变,直到当前用户结束事物
      • DML语句所涉及到的行被锁定,其他用户不能操作
  • 数据库操作(增删改查)完成后最好执行commit。

• Oracle数据库创建约束：

  • 非空约束(not null)，如：

    <code>create table emp(    id number(10) constraint emp_id_nn not null,    //emp_id_nn表示约束名    name varchar(20),    salary number(10,2))</code>

  • 唯一约束(unique)，如：

    <code>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)       //括号内指明约束列)</code>

  • 主键约束(primary key)，如：

    <code>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))</code>

  • 外键约束(foreign key):连接两个表,将两个表进行关联

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

    • 如：

      <code>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))</code>

  • 删除数据时，父表中的列被删除时，子表(外键关联的)中对应的列也被删除

  • 添加约束的语法：

    • 使用alter table语句:

      <code>1.添加或删除约束,但是不能修改约束2.有效化或无效化约束3.添加not null约束要使用modify语句</code>

    • 格式：

      <code>alter table table_nameadd(drop...) [constraint constraint_name] type (column);</code>

    • 如：

      <code>//添加不为空alter table empmodify salary number(8,2) not null;//添加其他的约束alter table empadd constraint emp_uq unique(name);//删除约束alter table empdrop constraint emp_uq;//无效化约束alter table empdisable constraint emp_uq;//有效化(注意,enable的前提是约束所在的列不存在对应的约束问题)alter table empenable constraint emp_uq;</code>

  • 查询约束：

    • user_constraints

    • 如：

      <code>//查询约束名、约束类型、约束条件select constraint_name,constraint_type,search_condition from user_constraintswhere table_name='employees';   //''其内的表示表名</code>

    • 查询定义约束的列:

      • user_cons_columns

      • 如:

        <code>select constraint_name,column_namefrom user_cons_columnswhere table_name='employees';</code>

• 视图：

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

  • 视图中使用DML的规定

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

    • 如:

      <code>create or replace view emp_viewas selectavg(salary) avg_sal from employeesgroup by department_id;</code>

• 序列：可供多个用户用来产生唯一数值的数据库对象

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

  • 创建格式：

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

  • 如：

    <code>create sequence emp_seqincrement by 5start with 2maxvalue 50cyclenochche;</code>

  • 序列的执行方法：

    <code>1.select emp_sql.nextval from dual; 2.select emp_sql.curval from dual;</code>

  • 注意：首次调用方法时，需要闲滴啊用nextval.

  • 序列的修改(不能修改初始值)

    <code>alter sequence seq_nameincrement by newvalnomaxvalue...;    </code>

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

喜欢 (0)赏

[搞代码]

分享 (0)