Oracle SQL

                              Oracle SQL day1

Oracle SQL(Oracle 9i 9.2.0.1.0)

一、DataBase
         保存数据，以表的形式表现数据
二、SQL

 SQL(structure query language  结构化查询语言)，是操作关系型数据库中的对象

 DDL（data definition language 数据定义语言），用于建表或删表操作，以及对表约束进行修改。
  create table , alter table , drop table 对表结构的增、删、改操作。

 DML（Data manipulation language 数据操作语言），向表中插入纪录，修改纪录
         insert , update , delete

    transaction,事务控制语言，由DML语句组成的，commit; ,rollback，savepoint   
                    
    select 查询语句

    dcl 数据控制语言，授权语句 grant,revoke

 alter session alter system

三、Oracle
 DB (database)
 DBMS 数据库管理系统(database management system)
    DBA 数据库管理员
 数据库管理系统有：ms微软的sql server,
            IBM的db2，imformix,
            mysql,
                          oracle的oracle920,
                          sybase的ase

四、相关操作 
 
 echo $PATH 配置环境变量，加/oracledata/oracle/product/9.2.0/bin,把oracle命令加上

 echo $ORACLE_SID  查看Oracle数据库的实例，实例是访问数据库的方法。缺省端口号是：1521
 

 1、sqlplus 访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境
     sqlplus 用户名/密码

 2、show user 显示当前用户的用户名

     改变身份可以直接connect 用户名/密码   --- 这个是sqlplus命令
     在sqlplus中可以使用 ! 可以在shell和sqlplus间切换，!shell命令 可以在sqlplus中使用shell命令。
     实际上是sqlplus开了子进程来执行shell命令。

 3、Oracle数据库中的表分两类：用户表（用户使用操作的表），系统表（数据库系统维护的表，也叫数据字典）
     对用户表的DDL操作触发了对系统表的DML操作！

五、基本语法

1、select查询语句
 
 对一个表进行查询操作首先要熟悉表结构。

 select table_name from user_tables;（查询系统表）
 以上的查询语句就是查询本用户下所拥有的所有表的表名。

 投影操作，只查看选择的字段的信息。  select
 选择操作，查看字段中的特定某些信息。 where
 联接操作，多表查询，通过表间连接，查寻出多表中的信息

（1）select table_name from user_tables;（查询系统表）
     以上的查询语句就是查询本用户下所拥有的所有表的表名。
    
（2）sqlplus的buffer中会缓存最后一条sql语句，可以使用"/"来执行这最后一条sql语句，也可以使用
     edit命令来编辑最后一条sql语句。
     l命令（list）（sqlplus命令）可以显示buffer中最后一条命令。  
    
（3）desc [表名]
     这是一条sqlplus命令，注意他不是sql语句，这条命令用于查看表的结构。describe的缩写
     [字段名] [字段的类型]，这是使用完desc命令后显示的表结构。
 
 例：desc s_emp  员工表  
 
 显示结果：

 Name                                      Null       Type
        ------------------------------------------------------------------
 ID                                        NOT NULL NUMBER(7)
  LAST_NAME                                 NOT NULL VARCHAR2(25)
  FIRST_NAME                                         VARCHAR2(25)
  USERID                                             VARCHAR2(8)
  START_DATE                                         DATE
  COMMENTS                                           VARCHAR2(255)
  MANAGER_ID                                         NUMBER(7)
  TITLE                                              VARCHAR2(25)
  DEPT_ID                                            NUMBER(7)
  SALARY                                             NUMBER(11,2)
 COMMISSION_PCT                                     NUMBER(4,2)

 Name是字段名，Null是此字段是否允许为空，not null是不允许为空。
 Type是类型，number是数字类型、varchar2是字符类型，date是日期类型。

 表中字段的含义：ID              ID
   LAST_NAME       姓
    FIRST_NAME      名
    USERID          此字段咱们不使用，忽略                                
    START_DATE      入职日期                        
    COMMENTS        说明                               
    MANAGER_ID      经理ID                                
    TITLE           职务                           
    DEPT_ID         部门ID                            
    SALARY          月薪
   COMMISSION_PCT   提成
  
      desc s_dept   部门表

  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
   ID                                        NOT NULL NUMBER(7)
   NAME                                      NOT NULL VARCHAR2(25)
   REGION_ID (地区号)                                 NUMBER(7)

  
 desc s_region   地区表

（4）select [表的字段名1],[表的字段名2], ... from 表名;
     select * from 表名; 查询表中所有字段的信息 
  
    
（5）关键字不能拆分，sql语句，以及表名，字段名是大小写不敏感的。
     sql语句要以";"结尾，来表示sql语句结束，如果不加";"系统不会执行此条sql语句，并提示。    
     在Oracle中字符显示是左对齐，数值右对齐。
    
（6）在select 语句中可以使用数学表达式。
     select [表达式（必须包含本表字段名）],[...],.... from 表名；
     运算的优先级的先乘除后加减，同级自左向右运算，括号改变优先级。

 select first_name,salary*12 from s_emp; 找出每个人的年薪

（7）别名
     select [字段名或表达式] ["别名"]，[...] ["..."],.... from 表名；
     可以通过在字段名或表达式后加空格"别名"，可以给列，或者表达式结果其别名。    
     表达式别名若有空格必须加双引号。
 
 select first_name,salary*12 annual_salary from s_emp;
    
（8）字符串拼接使用||符号
     select 目标字段名||' '||目标字段名 from 表名；

     注意：在Oracle中的字符或字符串要用单引号，双引号用来起别名
     别名中需要使用空格，或是大小写敏感时需要用".."包含。    
    
练习：
自己写一条SQL语句，执行的结果是select * from ...;
其中...是每张表的表名
即在每张表的表名前加“select * from”  ，后加“;”

select 'select * from '||table_name||';' from user_tables; 
    
    
2、处理错误
 
 执行查询时
  无效标识一般表示字段名错误
  表或视图不存在表示表名错误

（1）!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
     实际上是在编辑缓存文件中的最后一条sql语句。
     也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
     只有在Linux平台使用
     ! 相当于 host ，没有断连接，只是切换了一下，执行shell命令
（2）edit命令来编辑最后一条sql语句。

3、sqlplus设置
 set pause on 回车响应，分屏显示，只在本会话中有效
 set pause off 关闭分屏显示。
 set pause "..."  设置分屏显示的提示信息。
 set pause on 先输出提示信息，回车响应，分屏显示
 set head off 提头输出关闭
 set feed off 结尾输出关闭
 set echo off 回写关闭
 spool 文件名.sql   写入指定文件
 spool off 关闭写入。   

4、sql脚本
   也就是在文件中写有sql语句的文件，可以在sqlplus中运行。
   引入sql脚本
   sqlplus 用户名/密码 @sql脚本 （注意：在用户名密码输入结束后一定要加空格然后再写@sql脚本）
   在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上
  
  
5、Oracle中的空值
 
 空值会当无穷大处理，其实空值根本就不会存储，只是看作是无穷大。

Oracle中空值处理函数 NVL(字段名，值)，如果这个字段为空，替换为第二个参数的指定值，如果不为空，则会返回其原值。

例：select first_name,salary*12*(1+NVL(commission_pct,0)/100) "total salary" from s_emp;   查询每个员工一年的总收入

6、distinct关键字，去掉重复行（这个关键字会触发排序操作）

例： select distinct dept_id from s_emp; 查询员工都分配在哪些不同的部门

 select distinct dept_id,title from s_emp;  查询部门都有哪些不同的职位

         distinct 可以使dept_id与title的联合唯一

 注意：distinct，关键字之后会对from之前的字段进行排重操作。

六、选择操作
 
1、order by  排序是按照存储顺序输出的，order by后面可以跟字段名，别名，位置，也可以有多个字段，当第一个字段的值一样时，按第二个字段排序
   排序子句 ASC（默认，升序） DESC（降序）
   order by 目标列名（别名） 排序顺序（不写排序顺序，会默认为升序排序）

   例：select first_name from s_emp order by first_name; 等价于 select first_name from s_emp order by 1; 此处的1表示first_name。
       select first_name from s_emp order by first_name desc;

 select first_name,salary from s_emp order by salary;   按照工资的升序排序
 select first_name,salary from s_emp order by salary desc;  按照工资的降序排序
 select first_name,salary,commission_pct from s_emp order by salary,commission_pct;   先按工资排，工资一样的再按提成排序，升序显示 

    注意：升序空值在结果的末尾，降序空值在结果的前面。
  
2、where子句（选择操作）

 where子句使用在 select ... from ... 后面，用来选择所需（符合条件的）的记录

 例：select first_name,salary from s_emp where salary>1500;  找出工资比1500高的员工
                此处不能用where 2>1500

 查询Carmen的工资：
     select first_name,salary from s_emp where first_name='Carmen';
  注意:字符需要加引号，数字不需要。引号之内的内容是大小写敏感的
 
 where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X  ，XXX in（X，X，X）

 between ... and ... ,表示结果在这之间，between and是一个闭区间，也就相当于... <= ... and ... >= ... 。
 
 找出工资在1500到2000之间的： select first_name,salary from s_emp where salary between 1500 and 2000;
   等价于：select first_name,salary from s_emp where salary >=1500 and salary<=2000;

 !=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。

 ... in (va1,val2,...) 判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any
 in中集合的数据是有顺序的，概率高的数据放在前面可提高效率
 
 查找这三个部门里任意一个部门的员工
  select first_name from s_emp where dept_id in(41,42,32); 等价于select first_name from s_emp where dept_id=any(41,42,32);
    应该把出现频率较高的数据放在集合的前面会提高效率
 
 not in<>all 没有顺序，not in 不等于 all
 空值会对not in造成影响，也就是不等于任何值，但是空值例外。

 like '...' 字符串通配查询，'%'表示0或任意多个字符，'_'，表示任意一个字符。
 注意：转义的用法：like ‘S/_%’ escape ‘/’
 表名都是以大写存储的
 
 例：找出表名是以S_开头的表
  select table_name from user_tables where table_name like 'S/_%' escape '/';  把/定义为转义字符

 ... is null 使用来判断值是否为空。is not null 判断值不为空
 例：查看哪些人没有提成
     select first_name from s_emp where commission_pct is null;

 找出42部门每个员工的年薪并且按年薪从大到小的顺序显示：
   select first_name, salary*12*(1+nvl(commission_pct,0)/100) total_salary from s_emp where dept_id =42 order by 2 desc;
 
      ... and ... 表示只有两个条件同时满足
 ... or ... 表示条件只要满足其中只一就可以
 all ... 是要求都满足条件。

 注意：Oracle中的字符串是严格区分大小写的。

 （1）注意数据类型，数字类型直接写，字符用'......' ，缺省格式的Date可以用'......'，只有别名才用" "包含。
 （2）选择合适的运算符  

2、单行函数（一个输入，一个输出）
   多行函数又叫组函数（多个输入，一个输出）例平均值

 1.字符函数

    字符是大小写敏感的
    转小写 lower(字段名)      ---  其中的参数可以是一个字符串常量或是一个字段名
  例：select lower(first_name) from s_emp;把每一个字符都变成小写。
      select lower('Sql Course') from dual;  这个dual表可以用来测试。
      select first_name,salary from s_emp where lower(first_name)='carmen';   通用性好
      select first_name,salary from s_emp where first_name='Carmen';   效率高， 平时用的时候尽量写这种，当实在不知道数据是什么样时才用上面的。
  
    转大写 upper(字段名)
    首字母大写 initcap(字段名)
    字符串拼接 concat(字段1, 字段2)
    截取子串 substr(字段名, 起始位置，取字符个数)
 返回字符串长度length(字符串)

    dual表，是专门用于函数测试和运算的，他只有一条记录 

    可以使用"-"表示从右向左数，取的时候可以从左往右取。
     例：select first_name,substr(first_name,length(first_name)-1,2) from s_emp;
     select substr(first_name,-2,2) sub from s_emp;（取后两个）
     select substr(first_name,2,2) sub from s_emp;（取前两个）
  
 nvl是一般单行函数，支持任何数据类型
      
 2.数值函数

    四舍五入 round(数据,保留小数点后几位)
    可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）。
    例：select round(15.36,1) from dual;

    截取数字函数 trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0
    例：select trunc(123.456,1) from dual; 
 
 otn.oracle.com这个网上有函数的参考文档
  
 3.日期函数
 
    日期格式，
    全日期格式 世纪信息，年月日，时分秒。
    缺省日期格式，日-月-年 dd-mon-rr
    修改当前会话的日期格式，会按照指定的格式输出日期
    alter session set nls_date_format='yyyy mm dd hh24:mi:ss';

    返回当前日期 sysdate
    例：select sysdate from dual;  显示今天的日期
         select sysdate+1 from dual;  获得明天的日期，加1，单位是天
     
 例: 10分钟以后的时间  select sysdate,sysdate+1/144 from dual;
    
    日期是格式敏感的
    求两个日期间相隔了多少个月 months_between(date1,date2)
    加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
    从date日期开始的第一个星期五 next_day(date,FriDay)
    返回月末的日期 last_day(date)
 round 不能使用缺省的日期格式
 例：select round(to_date('02-JAN-07','DD-MM-RR'),'MONTH') from dual;

    截取日期 trunc(date,'年或月或日或时分秒')
    例：select next_day(sysdate,2) from dual;
    例：select trunc(add_months(sysdate,1),'month') from dual;  
  
    练习：
    返回下个月的第一天的日期
    select round(last_day(sysdate),'MONTH') from dual;
 select trunc(last_day(sysdate)+1) from dual;
    select add_months(trunc(sysdate,'MONTH'),1) from dual;
 select trunc(add_months(sysdate,1),'MONTH') from dual;
    
 4.不同数据类型间转换函数

    将日期转成字符 to_char(date,'日期格式')
    日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss'（完整的日期可格式）
    year'(全拼的年),'mm'(2位数字表示的月) 'month'(全拼的月)，'dy'(3位字母表示的星期) ，'day'(星期的全拼)，'ddspth' (日期的序数词)

    例：显示完整的系统日期
     select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
 
     显示42号部门员工的入职日期：select first_name,to_char(start_date,'yyyy mm dd hh24:mi:ss') from s_emp where dept_id=42;
 
    找出3月份入职的员工：select first_name,start_date from s_emp where to_char(start_date,'mm')='03';

    查询3号入职的员工
    select first_name,start_date from s_emp where to_char(start_date,'dd')='03';
    select first_name,start_date from s_emp where to_char(start_date,'fmdd')='3';     'fm'是去掉前导0

    创建表：create table hiloo(c1 date);
 
    向表中插入日期(hiloo是表名)
    insert into hiloo values('01-JAN-07');
    insert into hiloo values(to_date('2007 01 01 10:10:10','yyyy mm dd hh24:mi:ss'));

    将字符转换成数字 to_number('...') ,缺省是按十进制来算，字符串只能是0---9的数字
  select to_number('ab','xx') from dual; 把ab转成十六进制
  
    将数字转字符to_char(number，'fmt') fmt是数字格式
 select to_char(salary,'$99,999.99') from s_emp;
 select to_char(salary,'$00,000.00') from s_emp;    ##########表示越界，大于显示宽度
 select to_char(salary,'L00,000.00') from s_emp;

    将字符串转成日期 to_date('...','日期格式')
    例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；

 5、对字段的显示格式化
  column命令 --- sqlplus命令
  column命令 列格式的定义
  column last_name clear;清除已定义的格式
  
      column 目标列名 查看这个类是否定义了格式

      column 目标列名 format A.. 设置列宽。
      column last_name heading 'Employee|Name'  FORMAT A15   ----format 是显示的宽度.heading 设置题头 ，这其中的'|'是换行符

      column salary justify left format $99,990.00
      定义数字显示格式
      注意：如果不满足显示的格式，就会把数据显示为"#"
   
      column salary justify left format $00,000.00  ----会出现$00,928.00 ,用0补齐

      column 列名 clear （清除列格式定义）

      注意：只有sqlplus命令才有简写，并且在使用sqlplus命令时结尾也不能加分号。

 

                              oracle sql day2

一、多表查询
 
 先熟悉s_emp,s_dept,s_region这三张表的结构。

 表连接（关联查寻）

 如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配（组合），产生很多无效的结果（笛卡尔积）。

 注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系

 查找每个员工所在的部门名称：
 select first_name,name from s_emp,s_dept;   会有300条记录
 select first_name,dept_id,d.id,name from s_emp e,s_dept d;   //s_emp e 这是给表起别名，这种形式得出的记录结果叫做笛卡尔积。
      故要进行过虑，找到符合条件的

 当做多表查询时一定要用where子句

 1、等值连接
 
 当表与表中有描述共同字段时，可以使用此连接。

   select [表别名1.字段名1]，[表别名2.字段名2],...
   from 表1 表别名1 ，表2 表别名2
   where 表别名1.字段名3=表别名2.字段名4;

   表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
   表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。

   注意：当为表起了别名，就不能再使用表名.字段名。

 查找每个员工所在的部门名称：select first_name,dept_id,d.id,name from s_emp,s_dept d where dept_id=d.id;   有25条记录，这种连接又叫内连接

  即查出的结果一定是小于等于原表的记录数。

 查询部门号，部门名称，地区名称：select d.id,d.name,r.name region_name
            from s_dept d,s_region r
            where d.region_id=r.id;

 查询Carmen所在的地区：select e.first_name,r.name
    from s_emp e,s_dept d,s_region r
    where e.dept_id=d.id and d.region_id=r.id and e.first_name='Carmen';
 
 2、非等值连接
 
 select [表别名1.字段名1]，[表别名2.字段名2],...
   from 表1 表别名1 ，表2 表别名2
   where 表别名1.字段名3 ..... 表别名2.字段名4

   ....可以使比较运算符，也可以使其他的除了'='的运算符
 
 列出每个员工的工资级别：select ename,sal,grade
    from emp e,salgrade s
    where sal between losal and hisal;
    
 把工资级别在5级的员工列出来：select ename,sal,grade
     from emp e,salgrade s
     where sal between losal and hisal and grade=5;    
 
 select id,first_name,manager_id from s_emp;

 3、自连接

   用别名把一张表中的数据分成两部分，然后在使用条件过滤。
   select [表别名1.字段名1]，[表别名2.字段名2],...
   from 表1 表别名1 ，表1 表别名2
   where 表别名1.字段名3=表别名2.字段名4;

 列出每个员工对应的领导：
   例：select a.first_name ename,b.first_name mname
        from s_emp a,s_emp b
        where a.manager_id=b.id;

 列出所有的领导：select distinct m.first_name
   from s_emp e,s_emp m
   where e.manager_id=m.id;

   以上所提到的表连接，都叫做内连接，严格匹配两表的记录。         
 
 4、外连接

   会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。

   例：select a.first_name enamei,a.id,b.first_name cname,b.id
        from s_emp a,s_emp b
        where a.manager_id=b.id(+);
       即用a表中的数据去匹配b表的，若b表中有null，系统模拟纪录与其匹配

   注意：要把哪一方的记录全部都显示出来，条件(+)就跟在要全部选出的对端。              
 
   外连接的应用：
    列出哪个部门没有员工
    select e.deptno,d.deptno
    from emp e,dept d
    where e.deptno(+)=d.deptno
    and e.deptno is null;
 

 

                              oracle day3 笔记

外连接：
 查找每个员工的经理ID
 select e.first_name "Employee",m.first_name "Manager"
 from s_emp e,s_emp m
 where e.manager_id=m.id(+);

一、组函数

 group 组
 group by 分组子句，按指定的分组规则分组 .
 group by子句也会出发排序操作，会按分组字段排序。

 select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；

 例：求多少人有提成：select count(commission_pct) from s_emp;
     求所有人提成的平均值：select sum(commission_pct)/count(*) from s_emp;
      select avg(nvl(commission_pct,0)) from s_emp;
  
     求这些员工分布在多少个不同的部门：select count(distinct dept_id) from s_emp;
 
 注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where 后只能跟单行函数，不能有组函数。

 avg(..),求平均值，sum(..),求和 这两个函数的参数只能是number型的。

 以下所提到的函数可以使用任意类型做参数。
  count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
  max(..),min(..)求最大值和最小值，
  count(*),统计表中记录数。

 求各个部门的平均工资：select dept_id,avg(salary)
      from s_emp;
        group by dept_id;
 
 求各个部门不同职位的平均工资：select dept_id,title,avg(salary)
     from s_emp
     group by dept_id,title;
    
 求42部门的平均工资（只显示平均工资）：select avg(salary) from s_emp where dept_id=42;
 求42部门的平均工资（显示部门号，平均工资）: select dept_id,avg(salary) from s_emp where dept_id=42 group by dept_id;
            第二种写法：select max(dept_id),avg(salary) from s_emp where dept_id=42;

 having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
 
 求各个部门平均工资大于2000的：select dept_id,avg(salary)
          from s_emp
          group by dept_id
          having avg(salary)>2000;

 找出除了42部门以外的各个部门的平均工资：select dept_id,avg(salary)
          from s_emp
          where dept_id!=42
          group by dept_id;

 注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。
 
     所有子句的执行：select
       from
       where
       group by
              having
       order by

 找出来哪个人的工资是最低的：select first_name,salary
               from s_emp
        where salary=(select min(salary)
           from s_emp);
二、子查询

 子查询，就是可以嵌在任何的sql语句中的select语句。

 在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。

 注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。
  配合使用子查询返回的结果必须符合运算符的用法。

 子查询中的where条件不会与主查询中的where关联叫做不关联子查询

 例: 哪些员工的职位与smith是一样的：

  select last_name,title
  from s_emp
  where title=(select title                       确保子查询只会返回一个值时才能用=号
    from s_emp
                  where last_name='Smith');
   
  应写成:  
  select last_name,title
  from s_emp
  where title=any(select title
    from s_emp
                  where last_name='Smith');

  去掉smith的写法：
  select last_name,title
  from s_emp
  where title=any(select title
    from s_emp
                  where last_name='Smith')
        and last_name<>'Smith';

  或
  select last_name,title
  from s_emp
  where title in (select title
    from s_emp
                  where last_name='Smith')
        and last_name<>'SMITH';
 
   哪些人是领导：select first_name,id
        from s_emp
        where id in(select manager_id
      from s_emp);

 哪些人是员工：select first_name
        from s_emp
        where id not in(select distinct manager_id
       from s_emp
     where manager_id is not null);
   
 找出来哪个部门的平均工资比32部门的平均工资还要高：select dept_id,avg(salary)
                from s_emp
         group by dept_id
         having avg(salary) > (select avg(salary)
          from s_emp
                 where dept_id=32);
三、将业务需求转换成可操作的表

 要经过的阶段：
  1、 需求分析  （业务）
  2、 做逻辑设计---针对数据去做（依靠数据模型---E-R图）
  3、 物理设计---把E-R图转换成表关系  
  4、 割接（新老系统交接）
  5、 生产库

 实体----把有共同属性的一类对象抽象出来就是实体了。具体的某一个学生是实体的一个实例。
 
 E-R图属性: E（Entity)--R(Relationship)

  * （Mandatory marked 强制的）  强制的非空属性
  o (Optional marked 可选的)     可选属性(可以有值也可以没有)
  #* (Primary marked )           表示此属性唯一且非空 

 实体关系:
  mastbean maybean

 数量关系:    多对一关系
       一对多关系 
      一对一关系
       多对多关系  

 第一范式，每一个属性说一件事情。所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）
 第二范式，最少有一个属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。
 第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）

 一般情况会做到第三范式。

 四、约束

 约束是针对表中的字段进行定义的。

 primary key （主键约束 PK，PK=UK+NN）保证实体的完整性，保证记录的唯一
 主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键(Composite Primary Key)。

 foreign key （外建约束 FK）保证引用的完整性，
 外键约束，外键的取值是受另外一张表中的主键或唯一值的约束，不能够取其他值，
 只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表）;
 child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，
 要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。

 unuque key（唯一键 UK），值为唯一

 index（索引）是数据库特有的一类对象，实际应用中一定要考虑索引，view（示图）

 典型的一对多 class 对应多个学生。

  student table                      class table
  ______________________________     _________________________
 | id | name | address| class_id|   | id |class_desc|class_num|
 |(PK)|______|________|___(FK)__|   |(pk)|__________|_________|
 |    |      |        |         |   |    |          |         |

 一对一

  student tabel             shenfenzheng table
  ____________________     _________________________________
 | id | name | address|   |  s_id  |shenfen_desc|shenfen_num|
 |(PK)|______|________|   |(PK，FK)|____________|___________|
 |    |      |        |   |        |            |           |

 多对多

       student tabel             zhongjian table                  kecheng table
  ____________________     _________________________________    __________________
 | id | name | address|   |  s_id  |shenfen_desc|shenfen_num|  | kid | kechengname|
 |(PK)|______|________|   |(FK，FK)|____________|___________|  | (PK)|____________|
 |    |      |        |   |联合主键|            |           |  |     |            |

 引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建

 建表和其他相关操作：

 DDL语句

 创建表：
    create table 表名(字段名1 类型(数据长度)(default ...) 约束条件,字段名2  类型(数据长度) 约束条件 );

 Oracle数据库中的数据类型

 varchar2(长度)，可变长字符串，
 char(长度) 定长，不够长度时会补空。
 date 日期类型
 number(..,..),number 表示浮点数，或者是整数
 long 大对象，clog 字符的大对象，相当于文本文件在表中只存放一个相当于只针对值
                     blog 二进制的大对象，也是以相当于指针的形式存放的。

 例：create table test(c1 varchar2(10),c2 char(10) );

 例：create table test10(c1 number(3),c2 number(4,2),c3 number(2,4));
  
 练习：要求用到的数据类型有char,varchar2,number,date，插入数据，并查询出来
  建表：create table hiloo(c1 number(2),varchar2(10),c3,char(10),c4 date);
  插入：insert into hiloo values(99,'ab','a',to_date('2007 01 01 10:10:10','yyyy mm dd hh24:mi:ss'));
  查询：select * from hiloo;

 select count(*) form sd0708.s_emp;  查看sd0708用户下的s_emp表
 connect sd0708/sd0708    连接sd0708用户
 grant select on s_emp to sd0709;  把s_emp表的select权限授权给sd0709用户。

 alter session set current_schema=sd0708;  等于把当前用户改成了sd0708
 select count(*) from s_emp;

 
 create table hiloo(c1 number default 10,c2 number);   //default 10 就是给这个字段一个默认值
 insert into hiloo(c2) values(20);   //c1=10,c2=20
 insert into hiloo(c1) valuses(30);  //c1=30,c2为null

 clear screen  清屏

 primary key约束：
  主键约束的定义：
   第一种定义形式：
    create table test(c1 number primary key );    //这种约束叫列级约束
    insert into test values(1);   //第二次执行这条语句时会报错，因为不能插入重复值。这就是主键约束。
    不同表约束名也不同。
    约束名可以自己定义。
     create table test(c1 number constraints test_c1_pk primary key);  //给约束起名
  
   第二种定义形式：
    create table test(c1 number, primary key(c1) )  ; 表级约束
    create table test( c1  number  constraints   pkc1  primary key );   此约束有名字:  pkc1
    create table test(c1 number, c2 number, primary key (c1 ,c2) )  ; 用表级约束可以实现联合主键

 

       oracle day4 笔记

从emp和dept表中求出各个部门的平均工资,要求显示部门名称和平均工资: select max(d.dname),avg(e.sal)
          from emp e,dept d
          where e.deptno=d.deptno
          group by d.deptno;

     !oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
     实际上是在编辑缓存文件中的最后一条sql语句。
     也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
     只有在Linux平台使用,windows上没有。
     ! 相当于 host ，没有断连接，只是切换了一下，执行shell命令

 foregin  key   (fk)   外键约束:

  (先定义父表，再定义子表）
   carete table parent(c1 number primary key );
   create table child(c1 number primary key , c2 number  references parent(c1));
  或表级约束定义:
   create table  child( c1 number primary key , c2  number , foreign key(c2)  references  parent(c1) );
  
  级联删除：create table child(c1 number primary key , c2 number  references parent(c1) on delete cascade ); 
   //on delete cascade这个外键约束支持级联删除。 // on delete set null 删除所引用的字段时把此字段设为空

  建表时应先建父表，再建子表，插入数据时也应是先父后子，删除时应是先删子表，再删父表。

 多对对多关系,一般都通过一张中间表来分解成两个一对多的表。
 建一个多对多关系：create

 NOT NULL （NN）非空约束
 
  只有这一个约束是可以用desc看到的。对于pk一定要求非空。

  number未指定宽度时最大到38位。

  create table test(c1 number primary key, c2 number not null); //c2有非空约束
  非空约束只有列级约束，没有表级约束
 
 unique  (UK) 唯一约束
 
  create table test(c1 number primary key,c2 number unique);  //UK是允许为空的。UK的列级约束的写法。

  create table test(c1 number primary key,c2 number，unique(c2) );  //UK的表级约束的写法。

 
 两个字段都要实现唯一且非空的约束如何定义：
  
  create table test(c1 number primary key,c2 number not null unique);
 
 
 给表增加约束时系统会自动创建一个唯一性索引。
 
 
 check约束：create table test(c1 number check(c1>100));  // 要求c1字段的取值要比100大
 
            create table test(c1 number, check( c1 is not null));   //这样也可以实现非空约束，但check写的非空约束用desc是查看不到的。
 

 sql脚本：也就是在文件中写有sql语句的文件，可以在sqlplus中运行。

    引入sql脚本：sqlplus 用户名/密码 @sql脚本 （注意：在用户名密码输入结束后一定要加空格然后再写@sql脚本）

  例：%sqlplus sd0709/sd0709 @test.sql
  
   若是SQL> @test.sql      //找脚本只到当前路径去找

    在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上
 
 
 设置中文：在.cshrc文件中setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'    然后用source .cshrc 生效
 
 
 create table emp_42 as select * from s_emp where dept_id=42; //根据查询创建新表，把表结构和数据都带过了，只有非空约束带过来了，其它约束没有带过来

 根据一个查询创建新表，只要结构不要数据：     where 1=2; //叫做永假式，这样就可以实现只要结构不要数据

     select * from s_emp where 1=2;  这样选不出纪录，方便察看表结构
 
DML语言： 

 1、insert操作，插入记录，一次只能插入一条记录。
      
  insert into 表名 values(值1，值2，......);

   注意这种方法插入记录时，要对所有字段进行插入，没有非空约束时，又不想插入值时，要用空值替代，
   并且要按照字段的顺序插值（要清楚表结构），且要注意数据类型一致。

  insert into 表名(字段名1，字段名2，.....) values(值1，值2，......);
    这种方法可以对指定的字段进行插入，不想插值的就可以不写，前提是该字段没有非空约束。

  例：insert into student value(1,'xxx','xxx');
       insert into student(id,name,address) value(1,'xxx','xxx');
   
 注意：有空值的话:
        隐式插入
            INSERT INTO s_dept (id, name) VALUES(12, 'MIS');
            不往想为空的字段中插数据，系统默认为NULL
        显示插入
            INSERT INTO s_dept VALUES(13, 'Administration', NULL);
  
     select * from s_emp where 1=2;  这样选不出纪录，方便察看表结构
 
 这样可以一次插入多条记录：
  INSERT INTO history(id, last_name, salary,title, start_date)
  SELECT id, last_name, salary,title, start_date
      FROM s_emp
      WHERE start_date < '01-JAN-94';

  
 2、update修改操作

  update table 表名 set 字段名1=数据1或表达式1，字段名2=数据2或表达式2  [where ....=....];

  例：update shenfenzhen set num=99 where sid=2; 

 3、delete删除操作

  delete from 表名 [where ...=...];
  
  有where子句时删除符合where条件的记录，无where条件时删除整张表的记录。

  例：DELETE FROM s_emp WHERE start_date > TO_DATE('01.01.1996', 'DD.MM.YYYY');

  用delete 删除一张大表会花很长的时间。
  用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
  delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。
  delete操作会占用大量的系统资源。

事务

 OLTP （ on_line transaction procession ） 联机事务处理
 数据库中操作的应是事务，而不是DML语句
 事务是有生命周期的，commit；事务结束
 系统中充满了并发的transation,每个连接是一个session，每个操作都是一个transaction
 DDL、DCL语句是自动提交的
 sqlplus正常退出（exit），系统自动提交
 上个事务的结束就是下个事务的开始
 事务保证数据的一致性，保证原子操作的安全
 一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
 缺省的事务隔离级别是read committed，只可以读取已经做提交操作的数据和本会话正在修改却没有提交的数据。

 在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，
 只允许一个用户对该条记录进行DML操作，只有提交操作commit;或回滚操作rollback;时，才可让其他用户操作对该记录进行DML操作，也就是释放了该条
 记录的行级排它锁。如果没有提交操作或回滚操作，那么该用户就不能对该条记录加锁，该用户的DML操作就会进入等待状态，但是在对表作drop操作（DDL操作）时，
 如果还有用户在操作该表，也就是没有释放表级共享锁，就会直接报错。

 事务越大，就会消耗更多的资源，并长时间持有事务会造成无法进行其他的操作，事物提交太频繁的话，会对I/O造成很大的负担，所以要合理确定事务的大小。
 commit;提交操作，事物的结束
 rollback;回滚操作，回滚整个事务，会将先前的活动事务中的操作（DML操作）的结果进行回滚，撤销全部操作，恢复成事务开始时的数据，
    也就是恢复成事务开始时的状态。

 savepoint:保留点

 DML语句：如update语句会加两把锁：1、表级共享锁（共享锁表示在一把共享锁上面还可以再加共享锁）2、行级排它锁（排它锁只允许一个会话加锁）
  DML语句会等待

 DDL语句会加DDL排它锁，DDL语句不会等待

 alter table命令

 alter table 命令用于修改表的结构(这些命令不会经常用)：

 增加字段：
  alter  table 表名　add(字段名 字段类型)

 删除字段：
  alter tbale 表名 drop column 字段; (8i 以后才支持) ，不允许删除表中的最后一个字段。

 给列改名: 9.2.0以后才支持
  alter table 表名 rename column 旧字段名 to 新字段名;  //不支持以前采用先新建一个字段，把数据复制过去，然后再把旧字段删掉。

 修改字段
  alter table 表名 modify( 字段 类型)
  (此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)

  not null约束是使用alter table .. modify (.. not null)，来加上的。

  alter table test modify(c11 null);

 增加约束：
  alter table 表名 add constraint [约束名] 约束类型(字段);
  只能够增加表级约束。

 解除约束：(删除约束)

  alter table 表名 drop 约束;
  alter table 表名 drop primary key;
  （对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）

  alter table father drop primary key cascade; 
  (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）

 删除重复记录可以使用rowid, rowid是一条记录的物理位置。任何一个表的rowid是不会重复的。

 给表重命令：rename 旧表名 to 新表名

 删除一个表里所有的数据：TRUNCATE TABLE 表名;   truncate是不能回滚的，所以使用时要慎重。但适合删大表，效率高

sequence序列：

 sequence 是另外一个数据库对象
 
 关于oralce中产生序列(sequence) 可以自动产生唯一值，主要用在产生主键值。

 创建sequence:  create sequence  序列名;

 (不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，
 序列会出现不连续的动作 回退操作不会影响序列取值)
 
 多个表可以使用一个sequence。

 sequence 的参数：
 
 increment by n   递增量
 start with n     起始值
 maxvalue n       最大值  nomaxvalue  定义的最大值
 minvalue n       最小值
 cycle|nocycle   轮回 ，循环
 cache n          缓存(第一次取时会一次取多少个id存起来)

 查看sequence 示图：
 desc    user_sequences ;   查看当前用户所有的sequence
 select   sequence_name , cache_size , last_number  from  user_sequences   where   sequence_name  like 's_';
 select  序列名.currval  from   dual    查看当前的序列数
 select  序列名.nextval  from   dual    查看下一个序列数，它会自动给当前的序列加1

 伪列：nextval, currval
  (开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)

 清空当前会话的内存：
  alter system  flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错）

 修改序列：(此命令不常用，只需了解就行不必深究)
  alter  sequence  序列名  修改项；

 删除序列sequence
  drop sequence 序列名;