oracle

甲骨文公司，全称甲骨文股份有限公司(甲骨文软件系统有限公司)，是全球最大的企业级软件公司，总部位于美国加利福尼亚州的红木滩。1989年正式进入中国市场。2013年，甲骨文已超越 IBM ，成为继 Microsoft 后全球第二大软件公司。公司的数据库管理系统包括： Oracle数据库、Berkeley DB、MySQL、Java DB

oracle

• 课程目标
  • 掌握DQL语言
  • 掌握DML语言
  • 掌握DDL语言
  • 了解数据控制语言DCL
  • 了解数据库对象
  • Oracle课后习题(无答案版)
• 基本概念
  • 数据
    • 要存储的信息
      • 文字
      • 图形
      • 图像
      • 声音
      • 光
  • 数据库
    • 存储数据的仓库
      • 发展阶段
        • 人力手工整理存储数据
        • 使用磁盘文件存储数据
        • 关系型数据库
          • 优点
            • 有组织的存储
            • 可共享
            • 冗余小
            • 易扩展
            • 完整性
  • 数据库管理系统 Database Management System
    • DBMS
    • 科学地操纵和管理数据库，主要用于创建、使用、维护数据库
      • 普通用户可以通过管理系统访问数据库中的数据
      • 管理员可以通过管理系统对数据库进行维护
  • 数据库系统 Database System
    • DBS
    • 是数据库和数据库管理系统的的集合
      • DBMS
      • DB
      • Application Software
      • User
        • user/DBA
      • Database Manager
  • Oracle:甲骨文股份有限公司，全球最大的企业软件供应公司
  • Oracle是最大的数据库厂商提供的关系型数据库产品
  • Oracle 发展历程
    • 1979年，Oracle公司推出Oracle 2 1980年，Oracle 3 1992年，Oracle 7 1999年，Oracle 8i 2001年，Oracle 9i 2004年，Oracle 10g 2007年，Oracle11g
    • www.oracle.com/cn
• SQL
  • SQL(Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和 管理关系数据库系统。语句以分号结尾。
  • SQL语言四部分
    • 数据查询语言DQL第一部分
      • 第一部分基础
        • 查询全部数据：select * from 表名;
          • select * from user_tab;
        • 查询表中的指定列：select 列名1,列名2,列名3,... from 表名;
          • select user_id,name,phone,email from user_tab;
        • distinct去除某个字段中重复的记录
          • select distinct name from s_emp;
        • 列的别名使用：select 列名1 "别名1",列名2 "别名2" from 表名;
          • select user_id "用户编号",name "用户姓名",phone "电话",email "邮箱" from user_tab;
          • 别名的使用方法说明
            • 1)可用汉字做别名，如上举例
            • 2)也可用普通字符做别名：select name "user_name" from user_tab;
            • 3）可以用关键字做别名，但是一定要用双引号引上。 select name "select" from user_tab;
            • 4)不是关键字的可以不加双引号：select name user_name from user_tab; 但是为了规范，养成良好习惯，别名统一都用双引号引上；
            • 5)除了用空格隔开取别名，还提供了取别名的关键字as。 select name as "select" from user_tab;
            • 用一个例子概括用法； select user_id "用户编号",name user_name,phone as "电话",email "select" from user_tab;
          • 双引号标识：取别名，定义表时表名、列名
        • 连接符||，将多个列连接起来： select 列名1||列名2||... from 表名;
          • 连接姓名与电话： select name||phone from user_tab;
          • select name||'的电话是'||phone||';' from user_tab;
        • 根据条件查询，需要使用where关键字
          • where语句产生的结果为三种，真、假、空，但是只查询出为真的记录。
          • 空值(null)介绍
            • 空值是一种不确定状态，是无效的、不确定的、未知的或不可预知的值；注：空值不是空格，也不是0；
            • 空值做基本运算(加、减、乘、除、比较等)，其结果都为空值
          • 根据空值查询数据
            • 查询email为空的用户信息： select * from user_tab where email is null;
            • 查询email不为空的用户信息： select * from user_tab where email is not null;
            • select * from user_tab where email = null; 查询不出任何数据，因为与空值做比较运算结果为空值。
        • 根据单条件精确查询：select [*][列名1,列名2,......] from 表名 where 列名1='xxx' ......;
          • 查询条件中字符型的数据一定要用单引号括起来，并且区分大小写
          • select user_id,name,phone,email from user_tab where user_id='zf';
          • 查询条件中是区分大小写的； select user_id,name,phone,email from user_tab where user_id='ZF';
        • 模糊查询，通配符%、_
          • %表示0到多个字符
            • 前半部分内容确定，模糊匹配后半部分内容； select * from user_tab where user_id like 'z%';
            • 后半部分内容确定，模糊匹配前半部分； select * from user_tab where user_id like '%m';
            • 只知道部分内容，不确定完整信息是什么，模糊匹配查询包含有该内容的信息； select * from user_tab where user_id like '%m%'; --最常用，最实用
          • _表示一个字符
            • select * from user_tab where user_id like '_m'; 知道长度为两个字符，并且知道第二个是m
          • %和_的比较
            • _只匹配一个字符，而%匹配0到多个字符；
              • select * from user_tab where user_id like 'z%'; select * from user_tab where user_id like 'z_';
          • %和_的联合使用
            • 查询名字中第二个字母为z的行； select * from user_tab where user_id like '_z%';
        • 不等于操作符!=、<>、^=
          • 查询用户编号不是wm的用户信息： select * from user_tab where user_id!='wm';
          • 查询用户姓名不是zz的用户信息： select * from user_tab where name <> '张震';
        • 多条件查询，操作符
          • and：与，查询出多个条件同时满足的数据： select * from 表名 where 列名1='xx1' and 列名2='xx2' and ......
            • 查询用户编号为wm并且姓名为吴妹的用户信息：select * from user_tab where user_id='wm' and name='吴妹';
          • or：或者，查询出多个条件分别满足的数据集： select * from 表名 where 列名1='xx1' or 列名2='xx2' or ...
            • 查询用户编号为wm或者姓名为赵峰的用户信息： select * from user_tab where user_id='wm' or name='赵锋';
            • 加入or改为and结果是什么？
            • and和or结合使用，and的运算符优先级高于or
              • select * from emp where deptno='30' and mgr='7698' or job='CLERK'; select * from emp where deptno='30' or mgr='7698' and job='CLERK'; 查询结果分析比较；
          • in()：in操作符，查询在括号中列出的取值的数据信息
            • 查询姓名为“吴妹、赵峰、系统管理员”的用户信息： select * from user_tab where name in('吴妹','赵锋','系统管理员');
            • 等同于多个or并列在一起，如下： select * from user_tab where name='吴妹' or name='赵锋' or name='系统管理员';
            • not in()：查询不在括号内列出的取值信息； 查询姓名不为“吴妹、赵峰、系统管理员”的用户信息： select * from user_tab where name not in('吴妹','赵锋','系统管理员');
          • between ... and ...取两端闭区间的值， 用于数值类型的数据
            • 查询薪水在800到1500之间的职工信息：800和1500这两个值也会查询出来 select * from emp where sal between 800 and 1500;
            • 等同于>= and <=，如下： select * from emp where sal>=800 and sal<=1500;
          • not()：用于过滤条件外，取反的意思
            • 不加not是查询user_id以z开头的用户信息，加了not查询的是不是以z开头的用户信息： select * from user_tab where not( user_id like 'z%');
            • 没有not查询的是薪水在800与1500闭区间内的职工信息，加了not查询的是薪水小于800，大于1500的职工信息： select * from emp where not(sal>=800 and sal<=1500);
            • 其他任何查询条件外都可以加not操作，用于取反
        • 排序查询：select [*][列名...] from 表名 order by 列名 [asc][desc]; asc升序关键字，desc降序关键字，默认为升序
          • 升序： select * from user_tab order by user_id; select * from user_tab order by user_id asc;
          • 降序： select * from user_tab order by user_id desc;
          • 通过多列进行排序： select * from user_tab order by name,user_id; 先按name升序排，name排不出来，再按user_id升序排
          • 多列排序时不同列可选择不同的排序方式： select * from user_tab order by name asc,user_id desc; 先按name升序排，再按user_id降序排
          • 可根据列号排序： select * from user_tab order by 2; 根据第2列排序，就是name列。
        • where与order by结合使用，针对过滤条件查询出的结果进行排序： select [*][列名...] from 表名 where 条件 order by 列名 ...;
    • 数据查询语言DQL第二部分
      • rownum介绍
        • 是oracle系统顺序分配为从查询返回的行的编号，返回的第一行是1，第二行是2.
        • 特点： 1.rownum只支持<或者<=，不支持>、>=、=（其中=1是例外支持）； 2.rownum必须从1开始； 3.rownum可以限制查询返回的总行数； 4.rownum是虚拟的，伪列，不是真实存在的列；
          • 查询课程的前三行数据 select * from course where rownum<4;
          • select * from course where rownum=4; select * from course where rownum>1; 查不出数据。 =只支持等于1，因为从1开始，所以支持 select * from course where rownum=1;
      • 函数运用
        • 集合函数count(),avg(),max(),min(),sum() 注：除了count，avg、sum都是针对数值类型的列； 用于统计的，组函数不可用于where查询条件中，可以使用在having语句中
          • select count(*) from student;
          • select count(sage) from student; select max(sage) from student; select min(sage) from student; select avg(sage) from student; select sum(sage) from student; select sum(sage)/count(sage) from student;
        • 字符函数lower(),upper(),initcap(),substr(,,),length()，concat('字符串1','字符串2') 可用于查询条件中也可以用户查询返回的列中
          • select * from course where cid='sch01'; select * from course where cid=upper('sch01'); select * from course where lower(cid)='sch01';
          • select initcap(cid),cname,chour from course;
          • select length('skdfjkldsfj') from dual; select substr('skdfjkldsfj',1,3) from dual; --oracle下角标从1开始，不管是数组还是字符串
        • 数字函数trunc(,)截取,round(,)四舍五入,mod()取余
          • select round(123.456789,2) from dual; select round(123.456789,-2) from dual; select round(123.456789) from dual;
          • select trunc(123.456789,2) from dual; select trunc(123.456789,-2) from dual; select trunc(123.456789) from dual;
          • select mod(123.456789,2) from dual;
        • 空值置换函数：nvl(字段,'需要转换的值')
        • 数据转换函数to_char(number,'fmt'),to_date('string','DD-MON-YY'),to_number('number')
          • to_char()主要应用在将日期型数据转换成字符型 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
          • select to_char(字段,'FM$999,999,999,999.999,999')
          • to_date()将字符型数据转换成日期型 select to_date('1990-01-01','yyyy/mm/dd')+2 from dual;
      • 日期函数
        • months_between('日期1','日期2')
          • 日期间间隔的月份
        • add_months('日期',数值)
          • 日期只能加减、不能乘除
        • next_day('日期','星期几')
        • last_day('日期')
      • 子查询，嵌套查询
        • 查询条件中有查询语句，子查询分为单行子查询和多行子查询。 子查询使用原则： 1.子查询要包含在括号内； 2.将子查询放在比较条件的右侧； 3.除非进行Top-N分析，否则不要在子查询中使用order by子句； 4.单行比较操作符对应单行子查询，多行比较操作符对应多行子查询； 5.使用子查询时，数据类型要一致
        • 多行子查询的比较操作符： in：等于列表中的任意一个； any：和子查询返回的任意一个值比较（大于最小的，小于最大的）； all：和子查询返回的所有值比较（大于最大的，小于最小的）
        • 查询年龄比“张萍莉”大的学生 select * from student where sage>(select sage from student where sname='张萍莉'); 查询分数为80的学生信息 select * from student where sid in(select sid from score where grade=80); 查询年龄大于“王五”、“王丽君”、“华嘉”当中任意一人的学生信息 select * from student where sage>any(select sage from student where sname in('王五','王丽君','华嘉')); 查询年龄全部大于“王五”、“王丽君”、“华嘉”这三人的学生信息 select * from student where sage>all(select sage from student where sname in('王五','王丽君','华嘉'));
      • 设计表的要求
        • 数据库范式
          • 第一范式：无重复的列
            • 关系表中每一列都是不可分割的基本数据项。同一列中不能有多个值。
            • 基本类型：整型、实数、字符型、逻辑型、日期型等
            • 在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。 　　所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属 性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只 包含一个实例的信息。例如，对于图3-2 中的员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表 中只出现一次。简而言之，第一范式就是无重复的列。
            • 举例：联系人表中要存联系方式，将手机号、联系地址、邮编、邮箱全部存在一个列中就不正确，如果要检索手机号怎么检索？没法检索， 应该建立手机号、联系地址、邮编、邮箱这4列分别存放对应信息。
          • 第二范式：无重复的行， 依赖于主键
            • 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。 为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如 图3-2 员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。 　　第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这 一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二 范式就是非主属性非部分依赖于主关键字。
            • 需要有可以唯一标识一行的主键字段，比如每个班的学生信息，都有学号，就是用来唯一标识一个学生的，通过姓名是不能够的，因为有同名同姓的
          • 第三范式：主表与外表
            • 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2 的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。 如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。
      • 多表查询
        • 多表查询应用在有关联的表中
        • 连接就是用连接符号连接了两个或者多个表中的字段。 连接符号就是前面介绍的过滤条件中的各种操作符
        • 笛卡尔积 不加任何条件
          • select * from student,course; 将两表数据相乘,两两结合
          • select * from student s,department d where s.did=d.did;
          • select * from score s1,course c,student s2 where s1.sid=s2.sid and s1.cid=c.cid;
        • 连接n个表，至少需要n-1个有效的连接条件。例如：连接3个表至少需要2个条件；
        • 单行子查询可以转换成多表查询
          • select * from student where sage>(select sage from student where sname='张萍莉'); select s1.* from student s1,student s2 where s1.sage>s2.sage and s2.sname='张萍莉';
        • 内连接，是笛卡尔积的特殊形式； 可以得到与笛卡尔积相同的结果， 但是性能上优于笛卡尔积
          • select * from student s,department d where s.did=d.did; select * from student s inner join department d on s.did=d.did;
          • select s1.* from student s1,student s2 where s1.sage>s2.sage and s2.sname='张萍莉'; select s1.* from student s1 inner join student s2 on s1.sage>s2.sage and s2.sname='张萍莉';
          • select * from score s1,course c,student s2 where s1.sid=s2.sid and s1.cid=c.cid; select * from score s1 inner join course c on s1.cid=c.cid inner join student s2 on s1.sid=s2.sid;
        • 外链接
          • 等价连接
            • select 列 from 表1，表2，表3 where 表1PK=表2 FK or 表2 PK=表3FK
          • oracle独有的语法外连接
            • select 列 from 表1，表2 where 表1PK(+)=表2 FK
              • 尝试左右两边都加+
          • 内连接
            • select 表的别名.字段名 from 表1 别名 inner join 表2 别名 on 表1.pk=表2.fk
          • 左外连接
            • 将符合连接条件的数据和左表中不符合查询条件的数据都查询出来，如果左表的某行在右表中没有匹配行， 则在结果集行中右表的所有选择列表列均为控制
            • select * from student s left outer join department d on s.did=d.did;
          • 右外连接
            • 与左外连接相反，除了符合连接条件的数据，也会将右表中不符合查询条件的数据也查询出来
            • select * from student s right outer join department d on s.did=d.did;
          • 全连接
            • 除了符合连接条件的数据，把左右两张表中不符合条件的数据都查询出来；
            • select * from student s full outer join department d on s.did=d.did;
      • 查询工资排在4~6的员工 select * from (select * from (select * from emp where sal is not null order by sal desc) where rownum<=6 order by sal) where rownum<=3 order by sal desc;
      • 分组查询
        • select job,title,avg(salary) from emp group by job,title; select与from之间只能出现使用被by的字段和组函数。 按某个列分组统计
        • having与where作用基本相同，对结果集进行过滤，对组函数条件的过滤，紧跟group by子句。
    • 数据定义语言DDL
      • 数据定义语言，对于表结构进行操作
      • oracle常用数据类型
        • CHAR(size)：固定长度的字符数据，其长度为size个字节，size的最大值为2000字节。 最小值和默认值为1
        • VARCHAR2(size)：可变长度的字符串，其最大长度为size个字节，size的最大值是4000， 最小值为1；必须指定一个varchar2的size。
        • NUMBER(p,s)：精度为p并且数值范围为s的数值；精度p的范围从1到38；数值s的范围从-84 到127. P、S原理： 最高整数位=p-s s正数，小数点右边指定位置开始四舍五入； s负数，小数点左边指定位置开始四舍五入； s是0或者未指定，四舍五入到最近正数； 当p小于s时候，表示数值是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数
        • date：日期类型，日期型的数据只有加、减运算，没有乘除运算。
          • 取当前时间是通过sysdate关键字获取； select sysdate from dual; select sysdate+1 from dual;
        • 其他数据类型课后了解，参考PPT
      • 约束介绍
        • 约束就是对表中的数据进行限制，允许什么样的值，不允许什么样的值。 就是为了保持数据的实体完整性和参照完整性，避免出现脏数据。
        • 约束有五类
          • 主键约束primary key
          • 外键约束foreign key
          • 非空约束not null
          • 唯一约束unique
          • 检查约束check
        • 主键约束：“第二范式”要求行必须具有唯一性，主键就是来标识行的唯一性的关键。 设置为主键的列将会自动被创建索引； 主键不允许空值； 主键约束可以定义在一个列上，也可以定义在多个列的组合上。
          • 主键通常分为两类：自然主键和代理主键； 自然主键：主键所在的字段有确定的意义； 代理主键：像id一样的东西，没有确切的意义， 只用来标识一条记录。推荐使用
          • 主键一般为一个，不推荐使用联合主键
          • 创建一个学历表 create table province( pro_id varchar2(10), pro_name varchar2(30), primary key(pro_id));
        • 外键约束：为确保“参照完整性”，必须使用外键约束。
          • 外键可以有多个
        • 非空约束：约束列的值不允许为空。设计表时应尽量避免过空值，空值太多影响数据库性能。
        • 唯一约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。 否则就会违反约束条件。 不能包含重复值，但允许为空。 可以为一个列定义唯一约束，也可以为多列的组合定义唯一约束。 系统将自动为唯一约束的列创建索引。
        • 检查约束：用于用户自定义的约束，如对于学生性别，我们可以约束为只允许取“男”或“女”两个值， 对于学生年龄，可以约束为15~50岁之间的值等。 常用的检查约束： 算术运算：如 grade<=100，任何算术运算符均可使用 逻辑运算：sname is not null 指定值：如check(sex in('M','F')) 范围约束：如sage between 15 and 50
      • create创建
        • create table 表名( 字段1名 数据类型 约束条件, 字段2名 数据类型, ......);
          • create table girl( gname varchar2(20), age number(3), city varchar2(10));
        • 表名和列名的命名规则： 必须以字母开头； 必须在1~30个字符之间； 必须只能包含A~Z、a~z、0-9、_、$、#； 必须不能和用户定义的其他对象重名； 必须不能是oracle的关键字
      • 约束的使用方法： 在建表之时和建表之后都可创建约束， 一般在建表之时创建主键、非空、唯一、检查约束；外键约束一般放到建表之后
        • create table human( id varchar2(20), name varchar2(20), age number(3) not null, province varchar2(10), sex char(1), telephone varchar2(15), primary key(id), check (sex in('男','女')), unique (telephone), foreign key(province) references province(pro_id)); 建表时：非空约束、主键约束、检查约束、唯一约束、外键约束
        • 建表后添加约束
          • 先删除human表，再建不带约束的human表
          • --添加非空约束 alter table human modify(age not null); --添加主键约束 alter table human add(primary key(id)); --添加检查约束 alter table human add(check (sex in('男','女')),check (age between 1 and 150)); --添加唯一约束 alter table human add(unique(telephone)); --添加外键约束 alter table human add(foreign key(province) references province(pro_id)); key(当前表的字段名，即外键的字段名)
      • alter修改表
        • 添加列 alter table 表名 add(列1 数据类型,列2 数据类型,...);
          • 给girl表增加height和weight两列 alter table girl add(height number(5,2),weight number(5,2));
        • 修改列 alter table 表名 modify(列名 数据类型,...); 一般为修改列名数据类型的大小
          • 将girl表中gname字段长度改为30； alter table girl modify(gname varchar2(30));
        • 删除一列 alter table 表名 drop column 列名; 删除多列 alter table 表名 drop (列1名,列2名...);
          • 删除表girl的height列； alter table girl drop column height;
        • 修改列名 alter table 表名 rename column 旧列名 to 新列名;
          • alter table girl rename column gname to girl_name;
          • 修改表名 rename 旧表名 to 新表名； rename girl to girl_new;
      • drop删除
        • 删除表 drop table 表名;
          • 删除girl表 drop table girl;
      • 有了约束对于表的使用就有了限制
        • 插入、修改数据时五种约束限制，非空字段不能插入空值； 主键不能为空不能重复，取值要在检查约束内，唯一约束的列不能重复但可以为空， 外键列的值要在外键对应的表中有相应取值才可以；
        • 删除数据时不能删除正在被其他表使用的记录，只有当其他表不再使用这个记录才能被删除
    • 数据操作语言DML
      • Data Manipulation Language
      • 操作语言能够开启事务，一个事务中的操作要么全成功，要么全失败； 事务是由完成若干项工作的DML语句组成的逻辑单位。 事务的特点： 1.原子性（不可分性）：指所包含的操作要么全做，要么全不做； 2.隔离性：事务结束前对事务的操作只对当前操作窗口可见； 3.永久性：执行结束永久有效； 4.一致性：事务结束后，数据保持一致
      • 提交事务commit;
      • 回滚事务rollback;
        • 在事务中可以设置保存点。当回滚时从下往上进行回滚的，当指定回滚到保存点时，会将保存点后面的DML语句 被回滚。事务进行过程中状态丢失、恢复到事务开始前的状态、其他用户对这些记录进行更新 操作、系统失败或非正常的终止SQLPlus，都将会rollback
        • savepoint设置回滚点 通过命令rollback to回退到回滚点
      • 插入insert
        • 往表里的所有列写入数据： insert into 表名 values(列1值,列2值,列3值......);
          • insert into user_tab values('test1','testname','testname','18123451351','[e-mail]',sysdate);
          • commit;
        • 往表里的指定的列写入数据: insert into 表名(列1名,列2名,列3名......) values(列1值,列2值,列3值......);
          • insert into user_tab(user_id,name,passwd,create_date) values('test2','testname2','testname2',sysdate);
          • commit;
      • 修改update
        • update 表名 set 列1=列1新值,列2=列2新值...... [where 过滤条件];
          • update user_tab set passwd='haha' where user_id='test1'; 将user_id为test1的用户密码修改为haha；
          • commit;
      • 删除delete
        • delete from 表名 [where 过滤条件];
          • 删除刚刚插入的数据： delete from user_tab where user_id in('test1','test2');
          • commit;
        • 另外一种删除数据的方法， 通过truncate删除： truncate table 表名;
          • 备份表数据： select 'insert into user_tab values('''||user_id||''','''||name||''','''||passwd||''','''||phone||''','''||email||''',to_date('''||to_char(create_date, 'dd-mm-yyyy hh24:mi:ss')||''', ''dd-mm-yyyy hh24:mi:ss''));' from user_tab;
          • truncate table user_tab;
        • truncate与delete的区别：
          • delete会开启事务，可以回滚；truncate不可以回r滚， truncate是DDL语言，被隐式提交。
        • 注：不能删除被其他表使用的数据
      • 事务中的操作数据只对当前操作窗口可见， 只有提交后其他操作窗口才可见。
    • 数据库对象介绍； 表、视图、索引、 序列、同义词等
      • 介绍数据库自己的数据字典表
        • Oracle数据包括用户表和数据字典两部分 数据字典包含数据库的相关信息： 1.数据库用户的名字 2.授权给用户的权限信息 3.数据库对象的信息 4.表的约束信息
          • 根据权限分类： USER：用户所创建对象对应的数据字典表，如：USER_objects,user_tables等 ALL：所有用户所能访问对象，如：all_objects,all_tables DBA:所有对象对应的数据字典表，如：dba_objects,dba_tables V$:描述系统性能相关的数据字典表 DICTIONARY:一个特殊的数据字典表，用来描述数据字典表相关信息的数据字典表 TABLES_PRIVILEGES:数据表权限
          • dictionary:存放数据字典中所有表的信息
          • user_objects:存放了当前用户下所有的对象（包括表、视图、索引、序列和约束等）
          • user_constraints：存放当前用户下的所有约束信息
          • user_cons_columns：用来查看和约束相关的列
          • user_users:存放当前用户的信息
          • all_user:存放数据库中所有用户的信息
          • user_indexes:存放当前用户的所有索引信息
          • all_indexes:存放当前用户的所有表信息
          • all_tables:存放数据库中所有用户的表信息
          • user_view：存放数据库中所有用户的视图信息
          • user_synonyms:存放当前用户所有表的同义词
          • user_tab_privs_made带privs均是和权限相关的表
      • 表存放在哪里，放在表空间中
        • 表空间是一个数据结构，用于组合被相似地访问的数据。
        • 表空间是一个逻辑概念，用于存放某一个或多个用户的数据库对象（如表，索引，用户，存储过程等），如需要正常使用数据库， 必须首先为其创建表空间。系统安装时默认自带了几个表空间，如SYSAUX，SYSTEM，TEMP，UNDOTBS1，USERS等， 我们可以选择使用USERS这个表空间来保存我们自己的数据库对象。但是建议全新创建一个表空间， 一方面不破坏系统现有的配置，另一方面也需要了解如何创建表空间及一些注意事项。
        • 简单创建表空间语句 create tablespace 表空间名 datafile 'D:\xxx.dbf' size xxxm autoextend on next xxm[maxsizexxxxm][maxsizeunlimited] extent management local;
          • 以sys用户登录到数据库中 create tablespace wuwu datafile 'D:\wuwu.dbf' size 100m autoextend on next 10m maxsize 200m;
        • 创建表时可以指定表空间
          • create table t1( id varchar2(20)) tablespace wuwu;
          • 修改表所在的表空间 alter table t1 move tablespace users;
      • 表：存储数据的二元组，有行和列组成。
      • 视图：是虚拟表，是从多个表中通过查询语句引用过来的。视图带有行和列， 但是在数据库中并不存储数据，数据是通过查询语句从被引用的表中获取。 本质：一条有名字的SELECT语句 表现：一到多张表的部分内容
        • VIEW优点： 1.限制数据库的访问 2.简化查询 3.数据独立性，可以进行增删改查 4.对同一数据有不同的表现，不是原表
        • 创建：create view 视图名 as select查询语句
      • 索引：是以表中列为基础的数据库对象，数据库用其加快检索速度。它保存着表中排序的索引， 并且记录了索引列在数据表中的物理存储位置（ROWID），实现了表中数据的逻辑排序。
        • 自动创建索引：在创建主键约束、唯一键约束以及使主键约束、唯一键约束生效时会自动创建唯一索引 手动创建索引：用户可以在列上创建非唯一性的索引
        • 索引分类： 1.唯一性索引（自动创建） 2.非唯一性索引（手动创建） 3.单列索引 4.组合索引（多列）
        • 索引使用的场合： 1.列频繁用于WHERE子句或连接条件中 2.列的取值范围较广 3.表很大，记录数量较多 4.查询返回的结果占总记录的百分比在2%-4%内
        • create index 索引名 on 表名(列名1[,列名2...])
        • 两张索引表： 1.USER_INDEXES包含索引的名字和它的唯一性索引 2.USER_IND_COLUMNS包含索引名、表名和列名
      • 序列：是Oracle一个命名的顺序编号生成器，能够以串行方式生成一系列顺序整数。 序列可被设置为递增或递减，有界或无界，循环或不循环等方式。主要运用在主键中
        • 主键： 1.代理主键：流水账号，依次递增 2.自然主键：有字符、数字
        • create sequence 序列名 increment by n1 步长 start with n2 开始值 maxvalue n3 最大值 minvalue n4 最小值 cycle|nocycle 是否循环，到最大值后开始循环 cache n|nocache 是否产生n个值放在缓存中，可以提高性能，不加cache其值默认为20
        • 使用SEQUENCE:通过NEXTVAL和CURRVAL进行调用 1.NEXTVAL:每次获得不同的SEQUENCE值,每调用一次，序列号+1 2.CURRVAL:获得当前指向的SEQUENCE值
        • 删除SEQUENCE: DROP SEQUENCE seq_name
      • 同义词：是指数据库对象的一个别名，经常用于简化对象访问和提高对象访问的安全性； 可以创建同义词的数据库对象有：表、视图、同义词、序列、存储过程、函数、程序包、java类
        • create [public] synonym 同义词名 for 对象名;
        • 加一个public，是公共同义词,删除只能由DBA执行。其他用户 可以通过这个同义词来访问对应的对象
        • 删除同义词 DROP SYNONYM 同义词名;
    • 数据控制语言DCL
      • create user 用户名 identified by 密码;
      • 权限分为系统权限：对于数据库的权限 对象权限：操作数据库对象的权限；
      • 赋系统权限grant 权限1,权限2... to 用户名; create session;创建会话，及登录 create table; 创建表 create sequence; 创建序列 create view; 创建视图 create procedure; 创建过程
      • 在对象所有者下，赋对象权限grant select,insert,delete on 对象名 to 用户名;
        • grant select on emp to aa;
        • grant select on emp to aa with grant option; 被分配权限o的用户也可以将该权限分配给其他用户
      • connect：提供了登录和执行基本函数的能力。可以链接数据库 以及在这些表中进行对数据的查询、插入、修改、以及删除的权限
      • resource：建立对象的能力
      • revoke select,insert on s_dept from 用户名; 回收权限
• PL/SQL程序设计
  • SQL语言只是用来作为对数据库的一种访问语言，而不是程序设计语言， 但只有程序设计语言才能用于应用软件的开发。PL/SQL(Procedural Language/SQL)是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言。 PL/SQL之于SQL如同DOS批处理之于DOS命令，Linux Shell脚本之于Linux命令。它可以通过程序设计的方法来使用SQL语句，如分支， 循环，顺序结构，可用于构建由一系列SQL语句和程序结构组成的批处理， 来处理多个任务，主要用于Oracle中的存储过程和函数等。
    • declare /*declarative section*/声明变量; begin /*executable section*/ 程序执行的主体 exception /*exception section */异常处理部分 end;
  • 最简单的PL/SQL程序 declare A varchar2(100); begin A:='hello world'; DBMS_output.put_line(A); end;
    • 声明变量： 变量名 变量类型(取值范围); 变量名 表名.列名%type;变量类型和取值范围都与该列一致； 变量名 变量名%type;与前面定义的变量的类型和范围一致；
    • 给变量赋值: 变量名:=值; select 列 into 变量名 from 表名 where 查询条件;只有当检索一行时，select才能赋值成功；
  • if...else ...
    • if grade = 'A' then dbms_output.put_line('GOOD'); elsif grade = 'B' then dbms_output.put_line('OK'); else dbms_output.put_line('NO GRADE'); end if;
      • declare grade varchar2(100); begin grade:='v'; if grade = 'A' then dbms_output.put_line('GOOD'); elsif grade = 'B' then dbms_output.put_line('OK'); else dbms_output.put_line('NO GRADE'); end if; end;
  • case...when...
    • case when grade = 'A' then dbms_output.put_line('GOOD'); when grade = 'B' then dbms_output.put_line('OK'); else dbms_output.put_line('LOW'); end case;
      • declare grade varchar2(100); begin grade:='A'; case when grade = 'A' then dbms_output.put_line('GOOD'); when grade = 'B' then dbms_output.put_line('OK'); else dbms_output.put_line('LOW'); end case; end;
        • declare grade varchar2(100); begin grade:='B'; case grade when 'A' then dbms_output.put_line('GOOD'); when 'B' then dbms_output.put_line('OK'); else dbms_output.put_line('LOW'); end case; end;
  • 循环结构 create table item(id number(4),counter number(2));
    • loop 。。。 exit [when 条件] end loop;
      • declare v_ordid item.id%type; v_counter item.counter%type:=1; begin loop v_ordid:=v_counter; insert into item values(v_ordid,v_counter); v_counter:=v_counter+1; commit; exit when v_counter>10; end loop; end;
    • for counter in [reverse] lower_bond .. upper_bond loop ... end loop;
      • declare v_ordid item.id%type; v_counter item.counter%type; begin for i in 11..20 loop v_counter:=i; v_ordid:=v_counter; insert into item values(v_ordid,v_counter); commit; end loop; end;
        • declare v_ordid item.id%type; v_counter item.counter%type; begin for i in reverse 21..30 loop dbms_output.put_line(i); v_counter:=i; v_ordid:=v_counter; insert into item values(v_ordid,v_counter); commit; end loop; end; 30然后逐渐减1
    • while 条件 loop 。。。 exit [when 条件] end loop;
      • declare v_ordid item.id%type; v_counter item.counter%type; begin v_counter:=31; while v_counter<=40 loop v_ordid:=v_counter; insert into item values(v_ordid,v_counter); commit; v_counter:=v_counter+1; end loop; end;
  • 记录类型：复合类型；是pl/sql中除基本类型以外的特殊类型， 可以方便地针对表的列进行数据匹配。对记录类型的定义类似c语言中的结构体
    • declare type studentRecord is record ( sid char(5), sname varchar2(10), ssex char(1), sage number(3), sphone varchar2(12), did number(5) ); sr studentRecord; begin select * into sr from student where rownum<=1; dbms_output.put_line(sr.sname); end; end;
  • %rowtype 表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列， 类似于表里的一行数据，也可以是游标里的一行数据。 好处是任它数据表怎么改列和列类型，我这里的代码都不用改动
    • declare sr student%rowtype; begin select * into sr from student where rownum<=1; dbms_output.put_line(sr.sname); end;
    • 获取记录或者行数据中的列通过sr.列名;
    • 编写一个PL/SQL块，要求按照dept表，定义一个记录类型，记录类型 中的成员使用%rowtype和dept表中的各个字段类型保持一致。 给各个记录类型的各个成员赋值，并将其插入到dept表中。 declare v_deptInfo dept%rowtype; begin v_deptInfo.Deptno := '50'; v_deptInfo.Dname:='hr'; v_deptInfo.Loc:='CHINA'; insert into dept values v_deptInfo; commit; dbms_output.put_line('insert success!'); end;
  • 存储过程：是一个命名的PL/SQL块，被存储在数据库中， 并且可以被其他的PL/SQL块使用。 因此，可以将业务逻辑、企业规则写成过程或函数保存 到数据库中，以便其他PL/SQL块使用， 简化应用程序的开发和维护，提高效率和性能。 create [or replace] procedure 存储过程名称 ( [arg1 [IN|OUT|IN OUT]] 数据类型, [arg2 [IN|OUT|IN OUT]] 数据类型, ... ) is|as 声明部分; begin 执行部分; exception 异常处理部分; end;
    • 参数类型介绍： in 类型，表明传递到过程的参数。存储过程接受该值； out类型，表明该参数不接受值，存储过程往外传递的参数，传递给调用该存储过程的程序； in out类型：既可以接受输入值，也可以往调用该存储过程的程序传递参数；
    • create or replace procedure myproc(paramIn in varchar2,paramOut out varchar2,paramInOut in out varchar2) is myVar varchar2(50); begin myVar:=paramIn; paramOut:='This is Param for Out.'; paramInOut:='This is Param for In and Out'; dbms_output.put_line('过程内部变量myVar的值来自外部传递进来值，为:'||myVar); end myproc;
    • 调用存储过程 declare param_in varchar2(50):='传递该值给paramIn参数'; param_out varchar2(50); param_in_out varchar2(50); begin myproc(param_in,param_out,param_in_out); dbms_output.put_line('param_out的值来自于存储过程中的paramOut:'||param_out); dbms_output.put_line('param_in_out的值来自于存储过程中的paramInOut:'||param_in_out); end;
  • 函数：与存储过程类似，区别在于函数可以有返回值，但是存储过程没有 create [or replace] function 函数名( [arg1 [IN|OUT|IN OUT]] 数据类型, [arg2 [IN|OUT|IN OUT]] 数据类型, ... ) return 返回值类型 IS|AS 声明部分; begin 执行部分; exception 异常部分; end;
    • create or replace function myfunc ( paramIn in number, paramOut out varchar2, paramInOut in out number) return number is total number(5); begin total:=paramIn + paramInOut; paramOut:='这是输出值'; paramInOut:=200; return total; end ;
    • 调用函数的程序 declare param_in number(5):=50; param_out varchar2(30); param_in_out number(5):=40; returnValue number(6); begin returnValue:=myfunc(param_in,param_out,param_in_out); dbms_output.put_line('param_out的值来自于参数paramOut:'||param_out); dbms_output.put_line('param_in_out的值来自于参数paramInOut:'||param_in_out); dbms_output.put_line('函数的返回值为：'||returnValue); end;
  • 游标：在PL/SQL块中执行select、insert、update、delete语句时， oracle会在内存中为其分配上下文区。游标是指向该区的指针，它为应用程序 提供了一种对具有多行数据的查询结果集中每行数据进行单独处理的方法， 是设计交互式应用程序的常用编程接口。
    • 游标分为显示游标和隐式游标。 显示游标是由用户声明和操作的一种游标。 隐式游标是oracle为所有数据操作语句自动 声明和操作的一种游标。
    • 声明游标： cursor 游标名称(参数 参数类型) is select语句; 打开游标： open 游标名称; 提取游标中的数据信息 fetch 游标名称 into 变量; 关闭游标： close 游标名称;
    • declare c_sname student.sname%type; c_sage student.sage%type; cursor mycursor(param_sid student.sid%type) is select sname,sage from student where sid=param_sid; begin open mycursor('10005'); fetch mycursor into c_sname,c_sage; dbms_output.put_line('姓名：'||c_sname||'，年龄：'||c_sage); close mycursor; end;
    • 游标属性： %ISOPEN：当游标变量打开时，%ISOPEN属性返回true， 否则%ISOPEN属性为false，可以借助这个属性来判断一个游标是否被打开； %FOUND：当游标被打开并且在执行fetch语句之前，%FOUND的值为null； 执行fetch语句后，如果有返回记录，%FOUND的值为true，没有返回记录 %FOUND的值为false； %NOTFOUND：和%FOUND的值刚好相反。当游标被打开且执行fetch语句之前， %NOTFOUND的值也为null，但执行fetch语句后，如果有返回记录，%NOTFOUND 值为false；如果没有返回记录，则%NOTFOUND值为true； %ROWCOUNT：该属性用来返回迄今为止已经从游标中取出的记录数据。在游标被 打开而没有执行fetch语句前，%ROWCOUNT的值为0；执行fetch语句后，每返回一 个记录，%ROWCOUNT的值就增加1个。
    • declare c_sname student.sname%type; c_sage student.sage%type; cursor mycursor is select sname,sage from student; begin open mycursor; loop fetch mycursor into c_sname,c_sage; dbms_output.put_line('检索到'||mycursor%ROWCOUNT||'行'); dbms_output.put_line('姓名：'||c_sname||'，年龄：'||c_sage); exit when mycursor%NOTFOUND; end loop; close mycursor; end;
  • 触发器：定义触发器，当执行相关的DML语句时会触发触发器自动执行一些操作 create or replace trigger 触发器名称 {before|after }verb_list on 触发器作用的表名 [referencing {OLD as old}|{NEW as new}]{PARANT as parent}] [for each row] [when (condition)] PL/SQL块 new：SQL执行后用来引用字段值 old：SQL执行前应用字段值
    • create or replace trigger updatestudent after insert on student for each row declare begin insert into score values('20',:new.sid,'SCH01','0'); end;
    • insert into student values('10018','test','M',22,'18183929398','1001'); 查看触发器执行情况 select * from score; 执行commit再次查看