176142998

 在Struts2中整合Spring的IoC支持是一件十分简单的事情。让我们一步一步来实现：
    1）复制struts2-spring-plugin-x-x-x.jar和相应的spring.jar到/WEB-INF/lib目录下。
    2）在struts.properties中设置struts.objectFactory属性值

    struts.properties
    struts.objectFactory = spring
    或者是在XML文件中进行常量配置

    struts.xml
    <struts>
        <constant name="struts.objectFactory" value="spring" />
    </struts>
    3）配置Spring监听器

    web.xml
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    通过Spring配置来注册对象

    applicationContext.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC
        "-//SPRING//DTD BEAN//EN"
        "http://www.springframework.org/dtd/spring-beans.dtd">
    <beans default-autowire="autodetect">
        <bean id="hello" class="hpfyeah.struts2.spring.HelloWorldAction"/>
    </beans>
    当然你也可以拥有更多的Spring配置文件。在web.xml中进行下列设置，从而使Spring的ApplicationContext通过匹配所给定模式的文件来初始化对象

    web.xml
    <!-- 用来定位Spring XML文件的上下文配置 -->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>
            /WEB-INF/applicationContext-*.xml,classpath*:applicationContext-*.xml
        </param-value>
    </context-param>
    4）修改你的Struts配置文件

    struts.xml
    <!DOCTYPE struts PUBLIC
        "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
        "http://struts.apache.org/dtds/struts-2.0.dtd">
    <struts>
        <include file="struts-default.xml"/>
        <package name="default" extends="struts-default">
            <action name="pureStruts" class="hpfyeah.struts2.spring.HelloWorldAction">
                <result>hello.jsp</result>
            </action>
            <action name="springStruts" class="hello">
                <result>hello.jsp</result>
            </action>
    </struts>
    默认情况下，Spring从上面显示的applicationContext.xml文件中寻找为hello所做的配置

5）好了，现在你的Struts2和Spring就能正常的一起工作了。有几个配置技术点需要详细说明下：
    装配模式。你可以通过设置修改struts.properties中下列属性的值来改变装配模式。

    name 按照你的action的属性的名字和Spring里的bean的名字匹配，如果匹配就自动装配。这是缺省的
    type 按照你的action的属性的类型，在Spring注册的bean中查找，如果相同就自动装配。这需要你在Spring中仅注册了一个此类型的bean
    auto Spring会试图自动监测来找到最好的方法自动装配你的action
    constructor Spring会自动装配bean的构造函数的参数

    是否使用类缓存。你可以通过设置修改struts.properties中下列属性的值来改变是否使用Spring自身的类缓存机制。可以设定的值为true或false，默认为true。

    struts.properties
    struts.objectFactory.spring.useClassCache = false


http://dev.rdxx.com/Java/Struts/2008/5/2313512756889_2.shtml

Spring 的快速开发， 说是快速开发， 其实能帮助的地方除了语法高亮和自动添加类库外也没多少东西了。

　　1. 新建普通 Java 项目 MySpringTest. 这个过程无需赘述了， 建议建项目的时候将 src 目录和 bin（或者classes）目录分开， 另外提示你切换透视图的时候一定要切换过去到 Java 透视图， 此时默认会在 Package Explorer 中选中刚才已经建好的 Java Project， 但是背景为灰色。

　　2. 首先单击一下左边的 Package Explorer 中新建的 MySpringTest 项目来使其高亮选中， 接着点击菜单项 MyEclipse -> Add Spring Capabilities……， 接着会弹出对话框 Add Spring Capabilities 提示你设置当前项目的 Spring 属性。

　　对话框的第一页可以选择全部的 Spring 框架， 这是最保险的做法， 不过我们的例子只需要选中Spring 2.0 Core Libraries 就可以了。 点击 "Next" 继续。

　　第二页是 Add Spring bean configuration file. 保持默认值不变就可以了。 最后点击 Finish.

　　3. Spring 的开发没法自动生成 Bean， 这里大家只好手工来写了， 也很简单。 分别复制下面的三个代码， 然后在 MyEclipse src 目录上点击右键后选择菜单项 Paste 就可以生成 Java 类文件了。

public interface Action { public String execute(String str); } public class UpperAction implements Action { private String message; public String getMessage() { return message; } public void setMessage(String string) { message = string; } public String execute(String str) { return (getMessage() + str).toUpperCase(); } } import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class TestAction { public static void main(String[] args) { ApplicationContext ctx = new ClassPathXmlApplicationContext( "applicationContext.xml"); Action bean = (Action) ctx.getBean("theAction"); System.out.println(bean.execute("Rod")); } }

　　4. 双击左侧在第2步生成的 applicationContext.xml， 然后选择菜单项 Window -> Show View -> Other……， 在弹出的对话框中选择 MyEclipse Enterprise Workbench 节点下的 Spring Beans 子节点打开视图 Spring Beans. 此视图讲出现在主界面的右下侧。

　　5. 展开此视图中的 MySpringTest 父节点， 并选中 src/applicationContext.xml 子节点， 在此节点上点击右键并选择弹出菜单项中的 New Bean 来打开 Create a new Spring bean 对话框， 并按照下图输入对应的内容。

　　Bean Id： [theAction]

　　Bean class： [UpperAction]

　　接下来请单击一下 Tab 面板 Properties 并点击其中的 Add…… 按钮， 在接下来弹出的 Property Wizard 对话框中按照下图输入/选择内容：

　　Name： [message]

　　Spring type： [value]

　　Type： [java.lang.String]

　　Value：[Hello_]

　　最后点击两次 Finish 按钮关闭所有向导对话框。 然后点击菜单 File -> Save. 此时可以看到 applicationContext.xml 的内容如下所示：

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"> <bean id="theAction" class="UpperAction" abstract="false" lazy-init="default" autowire="default" dependency-check="default"> <property name="message"> <value type="java.lang.String">Hello_</value> </property> </bean></beans>

　　然后双击 Package Explorer 下 MySpringTest/src/TestAction.java 打开源代码， 然后点击菜单 Run -> Run As -> 1. Java Application， 如果没有错误的话将会出现如下的输入， 您的第一个 Hello Spring 运行成功了：

log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext). log4j:WARN Please initialize the log4j system properly. HELLO_ROD

　　接着您就可以对着参考书继续创建类， 修改 applicationContext.xml 做更多的练习了。

　　开发整合 Hibernate 的关键操作点截图：

　　1. 在数据库浏览器中选择反向工程菜单；

　　2. 对话框的选项说明

http://java.chinaitlab.com/Spring/727426.html

《1》子查询和关联查询
建立表如下:

学生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键
[StudentName][char]NOT NULL

)
课程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key,       --主键
[SubjectName][char]NOT NULL
)
成绩表
CREATE Grade(
[Studentid][Int]NOT NULL,  --联合主键
[SubjectID][char]NOT NULL,  --联合主键
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)

1.将建表命令改为ORACLE语句在ORACLE中建表
create table student( --学生表
studentid number(3) primary key, --学生编号
studentname varchar2(20) --学生的姓名
);

create table subject( --课程表
subjectid char(3) primary key, --课程编号
subjectname varchar2(20)  --课程的名字
);

create table grade( --分数表
studentid number(3) references student(studentid), --学生id
subjectid char(3) references subject(subjectid), --课程id
mark      number(3), --分数
primary key (studentid,subjectid) --联合主键
);

 

insert into student values (101,'张三');
insert into student values (102,'李云');
insert into student values (103,'未');

insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');

insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);

insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);

insert into grade values (103,'A01',71);

 

2.作如下4题

第一问：查询出以下信息

学号 学生姓名 课程名称 成绩 （要全部学生信息）

关联查询 (多张表的)
别名

select a.studentid as "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid;

[select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c] 笛卡尔积

       3 * 3 * 6 = 54;

第二问：查询出以下信息

学号 学生姓名 课程名称 成绩（只显示每科最高分）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "课程名称",max(mark) "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)

(最高分---分数比我高的学生的人数=0)
select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0

第三问：查询出以下信息

学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",
decode(sign(mark-60),-1,'不及格','及格') "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid

第四问：查询出以下信息

学号 学生姓名 （查询出课程超过1门以上学生的信息）

select a.studentid "学  号",studentname "学生姓名",
count(subjectname)
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2

 

《2》复制一张表的结构
 --数据一起复制
  create table mydept as select * from dept;
 --只复制结构
  create table mydept1 as select * from dept where 1=2;
 --把数据从一个表复制到另一个结构相同的表
   insert into mydept1 select * from dept;
 --只复制部分数据
   insert into mydept1 select * from dept where deptno>20;
   insert into mydept1(deptno,loc) select deptno,loc
from dept;

 --表的约束是复制不过来的，需要自己去添加

 --列的别名 
   select ename "员工 姓名" from emp;

 a.没有主键的表如何消重复记录

create table test(
    id number(2),
    name varchar2(10));

   insert into test values (1,'aa');
   insert into test values (1,'aa');
   insert into test values (1,'aa');
   insert into test values (2,'bb');
   insert into test values (3,'cc');
 
  create table test1 as select distinct * from
  test;
  或者
  create table test1 as select id,name from test
  group by id,name;

 
  rename test to test2;
  rename test1 to test;

b.有主键消重复行
 
create table test(
    id number(2) primary key,
    name varchar2(10));

   insert into test values (1,'aa');
   insert into test values (2,'aa');
   insert into test values (3,'aa');
   insert into test values (4,'bb');
   insert into test values (5,'cc');

 create table test1 as select name from test
 group by name;

 alter table test1 add (id number(2));

 update test1 set id=(select max(id) from test
  where name=test1.name);

 create table test2 as select id,name from test1;

 rename test to testb;
 rename test2 to test;

 alter table test add primary key (id);

<<2>>SQL*PLUS中的环境设置命令
  不需要分号 就可以执行  ---  SQL*PLUS的环境命令
  需要分号结束 才能执行的 -- SQL命令 (和SQLserver是一样的)

   connect system/manager
   show user
   spool c:\aa.txt
   spool off
   clear screen
   set escape on     打开转义功能

   set linesize 1000  设置一行显示多少个字符
   set pagesize 20    设置一页显示多少行
-------
   define aa = 10  定义一个变量 aa = 10
   define bb='MANAGER' 定义一个字符 bb = 'MANAGER'
  
   prompt Hello World   原样输出prompt后的字符串
  
   set feedback off     关闭系统反馈的信息
   set heading off      列标题关闭
   set pagesize 0       不分页
   set autocommit on    设置自动提交打开
   set timing on        打开时间统计
   set time on          打开一个时钟

------
  a) 自动生成一个脚本
     利用SQL*PLUS环境命令  生成脚本文件
        set heading off   --关闭列的标题
        set feedback off  --关闭反馈信息
 
        spool c:\aa.txt   --缓冲写文件c:\aa.txt

        select 'insert into dept values ('||deptno||
 ','||''''||dname||''''||','||''''||loc||''''||');' from dept;   --执行select语句

spool off  --关闭缓冲

   用处:在备份 恢复 数据库管理等方面有用处

 <4>ORACLE中建立数据库对象
   表
   约束

   序列 sequence  ---  自动编号 ---- IDENTITY (SQLSERVER)
    <1>建立序列
     create sequence seq1; 从1开始 每次自动增加1 没有最大值
    <2>怎么使用
     select seq1.nextval from dual;
        nextval 伪列  下一个值
     select seq1.currval from dual;
        currval 伪列  当前值
     
      create sequence seq2
    start with 1000        --起始值1000
    increment by 2         --步长2
    maxvalue 9000          --最大值9000
    minvalue 900           --最小值900
    cycle                  --循环序列

   1000开始
    每次增加2 一直到9000
    回到900 重新开始

    <3>和表关联作为表的字段的值
     a) create table student(
          xh number(4) primary key, --学号
          xm varchar2(20) not null --姓名
        );

        要求:学号字段 从1000开始每次增加4 最大值9999
         --建立序列
           create sequence xh_seq
                  start with 1000 --从1000开始
                  increment by 4  --每次增加4
                  maxvalue 9999   --最大值 9999
                  ;
         --使用SQL语句关联
         insert into student values
             (xh_seq.nextval,'Mike');
         insert into student values
             (xh_seq.nextval,'John');
         insert into student values
             (xh_seq.nextval,'Rose');
    
      --特点:能产生唯一的值,但是不能保证值在表中的连续性
 
   b) create table teacher(
           teacherxh varchar2(10) primary key,
           teachername varchar2(20)
          );
     要求:教师的编号的格式是TH00001
                         TH00002
                          ....
     
   --建立序列
        create sequence teacher_seq
              maxvalue 99999;
   --
     insert into teacher values ('TH'||
       ltrim(to_char(teacher_seq.nextval,'00000')),
       '张三');
       insert into teacher values ('TH'||
       ltrim(to_char(teacher_seq.nextval,'00000')),
       '李');
      insert into teacher values ('TH'||
       ltrim(to_char(teacher_seq.nextval,'00000')),
       '王');

  视图 (view)
   --建立视图(用查询语句 select empno,ename,sal from emp建立了一个emp_v1的视图 )
     create or replace view emp_v1 as --select语句
         select empno,ename,sal from emp;

   视图的作用:隐藏数据,增加系统的安全性
      视图中是否存放了数据???  不存放数据,只存放查询
                             保证了 查询到的数据是和表
                             中的数据是一致的
   select * from emp_v1;
     <1>emp_v1先看是否是表
     <2>不是表 是否是视图
     <3> select * from  (select empno,ename,sal from emp);
     <4>执行得到结果

   能否通过视图去改表中的数据呢???
         有条件的可以:
           <1>建立视图的select语句必须是简单的select语句
               简单:不能是多表的查询
                    不能有分组函数
           <2>建立视图的时候不能带with readonly关键字
     create or replace view emp_v2 as --select语句
         select empno,ename,sal from emp
         with read only;
                  
  可以改的情况:
     1)create or replace view emp_v1 as --select语句
         select empno,ename,sal,comm from emp;
        
     update emp_v1 set comm = 1000
         where empno=7934;
 
     select * from emp;   --发现数据修改了

     2)create or replace view emp_v3
        as
          select empno,ename,hiredate,sal,deptno
          from emp
          where deptno = 10;

       update emp_v3 set deptno=20 where empno=7782;
 
       select * from emp_V3; --7782的数据没有了

    3)
 
 

                
      
     

 

 

  

   

 

 

 

回忆
    ORACLE安装 和 删除
    启动和关闭
    SQL*PLUS  windows下窗口版本
               超级用户 system/manager
                       sys/change_on_install
               普通用户
                       scott/tiger
              DOS下的SQLPLUS版本
    如何使用
      命令show user
      SQL语句
难点
    约束  如何给表加约束？？？

SQL*PLUS环境命令
  结束时可以写; 也可以不写
  desc dept
  show user
  connect scott/tiger
  set linesize 200
  set pagesize 100
  spool c:\aa.txt
 
SQL语句
  不需要分号结束的命令,sqlplus环境命令
     show user
     connect scott/tiger
     desc dept
     set linesize 200
     set pagesize 100
 
 SQL语句结束时候一定要 有分号或者换行加/

《1》DDL语句(数据定义语言) Data Define Language
   create
   alter
   drop
   truncate 开头的语句 truncate table <表名>
      特点:<1>建立和修改数据对象
           <2>建立和修改直接存入库中,直接生效

    建立表
       create table class(--班级表
          classid number(2) primary key,
          cname varchar2(20) not null);

       /*wdsadsadsad
         asdsadsadsadsad
         多行注释 
        */ 
       create table student( --学生表
         xh number(4) primary key, --学号
         name varchar2(10) not null, --姓名
         sex  char(2)  check (sex in ('男','女')),--性别
         birthday date,--生日
         sal number(7,2), --奖学金
         classid number(2) references class(classid) --班级         
       );

 外键引用的列一定是主键或有unique约束的列
 
  alter table student add (shengfenzheng number(18));

  drop table student;  删除结构
  delete from student; 只删除数据，速度慢，数据可以恢复
  truncate table student; 删除记录的 速度快 数据不能恢复

 《2》 DML语句(数据操作语言) Data Manupilate Language    
   select
   insert
   delete
   update
     特点:<1>对数据起作用的
         <2> 这些语句的修改是在内存中发生的
             要想改动存入库中必须要commit语句

 
  insert into student(xh,name,sex)
  values (&xh,'&n','&sd');
 
  insert into student(xh,name,sex)
  values (&学号,'&姓名','&性别');

  转义字符 \
  打开转义 set escape on
  
  insert into dept
  values (90,'JO&HI','北京');

  insert into dept
  values (92,'JO\&HI','大家');

 《3》 TCL(事务控制语句) Transaction Control Language
  commit;  提交  修改保存到数据库中
  rollback; 回滚  取消内存中的改动
  savepoint;保存点 分解事务的 把事务变小
       DDL语句 会自动提交以前未提交的事务
       关闭SQLplus工具 也会自动提交未提交的事务的
  事务 -- 就是一个完整的对数据的DML操作
  所有事务 都是要明确的提交和回滚的
  --转账
    update 账目表
    set 钱=钱-500
    where 帐号='A';
    update 账目表
    set 钱=钱+500
    where 帐号='B';
    commit;

    事务何时存在 DML语句中除select以外都会有事务
  
《《《《《《《注意》》》》》 / 重复运行上一条SQL语句  

  commit;    结束上一个事务 并且开始一个新的事务

  update student set sal = null where xh =1000;

  savepoint c111;
  
  insert into student(xh,name,sex) values (1004,'MIKE','男');

  rollback to c111; --撤销了插入的数据

  rollback;  --从c111这个点回滚到事务的开始点

《SQLPLUS规则》 
   a)DML语句后跟上DDL语句 DML语句的事务会被自动提交
   b)exit/quit命令 退出 SQLPLUS环境时也会自动提交事务
      点小叉子关闭sqlplus窗口 事务都自动回滚了
   c)非法操作是不能提交事务的 ，只能导致事务回滚
 
《4》 DCL语句(数据控制语句) Data Control Language                    grant 授予权限
     revoke 撤销权限
   权限 select ,insert,delete,update
        all (select ,insert,delete,update 总和)
   角色 connect （登陆数据库）,resource(建立表和对象)
  
   如何建一个自己的用户？
     必须是超级用户才能建用户
     --连接到超级用户
     connect system/manager
     --建立用户名zhangsan 密码m123
     create user zhangsan identified by m123;
     --授予必要的权限connect 你能够连接
                    resource 你能建表不受空间的限制,建立对象
     grant connect,resource to zhangsan;
     --这个普通用户就建好了 和scott用户的权限是一样的     
     grant DBA to zhangsan; --张三的权限和System一样
  
     --改张三的密码
    <<1>> 自己改自己的密码
        connect zhangsan/m123
        密码改为了mm1 
        alter user zhangsan identified by mm1;
    <<2>> 超级用户来改
        connect system/manager
        alter user zhangsan identified by mm1; 
 
 
    

   在scott/tiger这个用户下
     grant select on dept to zhangsan;
    在zhangsan下 可以使用select * from scott.dept;
         看到结果
   
 在scott/tiger这个用户下
       revoke select on dept from zhangsan;撤销授权
    在zhangsan下 可以使用select * from scott.dept;
         看不到结果
   

约束
   主键约束 --  每个表要有主键，唯一的标识一行数据
   非空约束
   唯一性约束
   外键约束
   检查约束
     脚本(SCRIPT)
        create table cla( --班级表
          id number(2) primary key, --班级编号
          cname varchar2(20) not null --班级名字
       );

        create table stu( --学生表
          xh number(4) primary key, --学号是主键
          xm varchar2(20) not null, --姓名非空
          age number(2) check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) unique, --身份证唯一 
          classid number(2) references cla(id) -- 班级编号外键
           --(引用的一定是主键或唯一性约束的字段)
         );
       
      <1>建立表的同时使用约束
  create table student( --学生表
           xh number(4) primary key, --学号主键
           xm varchar2(10) not null, --姓名不能为空
           sex char(2)  check (sex in ('男','女')), --性别
           birthday date unique, --日期
           sal number(7,2) check (sal between 500 and 1000),--奖学金 sal >=500 and sal <=1000
     classid number(2) references cla(id)
        );  --必须要先有cla表才对
            --一定先建立班级cla表
 
   主键约束 primary key
           not null
           check
           unique 唯一约束          

 create table student( --学生表
           xh number(4) constraint pk_stu primary key, --学号主键
           xm varchar2(10) constraint nn_stu not null, --姓名不能为空
           sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性别
           birthday date constraint uq_bir unique, --日期
           sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--奖学金 sal >=500 and sal <=1000
        );
      <2>建立约束的同时给约束指定名字，便于删除
        create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
     
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );
  
 
      <3>建完表后加约束
 
 学生表student
        create table student( --学生表
           xh number(4), --学号
           xm varchar2(10), --姓名
           sex char(2), --性别
           birthday date, --日期
           sal number(7,2) --奖学金
        );
 加约束
   加主键
    alter table student add constraint pk_stu
    primary key (xh);
   加非空
    alter table student modify (xm not null);
   检查约束
    alter table student add check(sex in ('男','女'));
    alter table student add constraint ck_sal check(sal between 500 and 1000));

 给student加班级字段
   alter table student add (classid number(2));

   班级表cla
    create table cla( --班级表
          id number(2), --班级编号
          cname varchar2(20) --班级名字
       );

添加 主键
 alter table cla add constraint pk_cla
       primary key (id);
加 not null
 alter table cla modify
       (cname not null);

               学生表student
      create table student( --学生表
          xh number(4) ,
          xm varchar2(20) , --姓名非空
          age number(2),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18), --身份证唯一 
          classid number(2) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );

加外键约束
alter table student add constraint fk_stu
    foreign key (classid) references cla(id);

加主键
alter table student add constraint pk_stu
 primary key (xh);

加not null
alter table student modify(xm not null);

加检查约束
alter table student add constraint cc_age
 check (age >= 10 and age <=90);

加唯一约束
  alter table student add constraint
      uq_sfz unique(shenfenzheng);

加外键约束
 alter table student add constraint
    fk_stu foreign key (classid)
     references cla(id);

如何删除约束
  
  alter table student drop constraint
        fk_stu;
可以用一个统一的格式来删除
  alter table 表名 drop constraint 约束名

  <4>如何查看约束?? 约束一定加在表上

    一个表上到底有哪些约束???
  select constraint_name,constraint_type
      from user_constraints
        where table_name = 'STUDENT'
--查看表上有什么约束
  select * from user_constraints;
--查看约束作用在什么字段上
  select * from user_cons_columns
   where CONSTRAINT_NAME='PK_STU';

user_constraints数据字典表

  <5>约束是如何起作用的??

       create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
     
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束unique的字段)
         );
  
   主键 = 非空 + 唯一
   非空
   唯一 = 有值的话  值要不同
          null的话  都是可以的
   外键 = 有值 一定要在被引用的表的数据中
          null的话  是可以的

ANSI SQL92 数据库的标准
 

 

  
  Oracle的约束

* 如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，
必须在表级定义约束
* 在定义约束时可以通过CONSTRAINT关键字为约束命名，如果没有指定，ORACLE将自动为约束建立默认的名称

定义primary key约束(单个字段）
create table employees (empno number(5) primary key,...)

指定约束名
create table employees (empno number(5) constraint emp_pk primary key,...)

定义primary key约束(多个字段,在表级定义约束）
create table employees
(empno number(5),
deptno number(3) not null,
constraint emp_pk primary key(empno,deptno)
using index tablespace indx
storage (initial 64K
next 64K
)
)

ORACLE自动会为具有PRIMARY KEY约束的字段(主码字段)建立一个唯一索引和一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引
指定存储位置和存储参数

alter table employees add primary key (empno)
alter table employees add constraint emp_pk primary key (empno)
alter table employees add constraint emp_pk primary key (empno,deptno)

not null约束(只能在字段级定义NOT NULL约束,在同一个表中可以定义多个NOT NULL约束)
alter table employees modify deptno not null/null

unique约束
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_ename_phone_uk unique (ename,phone)
)

alter table employees
add constraint emp_uk unique(ename,phone)
using index tablespace indx

定义了UNIQUE约束的字段中不能包含重复值，可以为一个或多个字段定义UNIQUE约束,因此，UNIQUE即可以在字段级也可以在表级定义，
在UNIQUED约束的字段上可以包含空值.

foreign key约束

* 定义为FOREIGN KEY约束的字段中只能包含相应的其它表中的引用码字段的值或者NULL值
* 可以为一个或者多个字段的组合定义FOREIGN KEY约束
* 定义了FOREIGN KEY约束的外部码字段和相应的引用码字段可以存在于同一个表中，这种情况称为"自引用"
* 对同一个字段可以同时定义FOREIGN KEY约束和NOT NULL约束

定义了FOREIGN KEY约束的字段称为"外部码字段",被FORGIEN KEY约束引用的字段称为"引用码字段",引用码必须是主码或唯一码,包含外部码的表称为子表，
包含引用码的表称为父表.

A:
create table employees
(.....,
deptno number(3) NOT NULL,
constraint emp_deptno_fk foreign key (deptno)
references dept (deptno)
)

如果子表中的外部码与主表中的引用码具有相同的名称，可以写成:
B:
create table employees
(.....,
deptno number(3) NOT NULL
constraint emp_deptno_fk references dept
)

注意：
上面的例子(B)中not null后面没有加逗号,因为这一句的contraint是跟在那一列deptno后面的，属于列定义，所以都无需指明列。而A例中的是表定义，需要指明那一列，所以要加逗号，不能在列后面定义，还可以写成：

create table employees
(empno char(4),
deptno char(2) not null constraint emp_deptno_fk references dept,
ename varchar2(10)
)
表定义contraint的只能写在最后，再看两个例子：

create table employees
(empno number(5),
ename varchar2(10),
deptno char(2) not null constraint emp_deptno_fk references dept,
constraint emp_pk primary key(empno,ename)
)

create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_pk primary key(empno,ename),
constraint emp_phone_uk unique (phone)
)

添加foreign key约束(多字段／表级）
alter table employees
add constraint emp_jobs_fk foreign key (job,deptno)
references jobs (jobid,deptno)
on delete cascade

更改foreign key约束定义的引用行为(delete cascade/delete set null/delete no action),默认是delete on action

引用行为(当主表中一条记录被删除时，确定如何处理字表中的外部码字段）：
delete cascade : 删除子表中所有的相关记录
delete set null :　将所有相关记录的外部码字段值设置为NULL
delete no action: 不做任何操作

先删除原来的外键约束,再添加约束
ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;
ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

check约束
* 在CHECK约束的表达式中必须引用到表中的一个或多个字段，并且表达式的计算结果必须是一个布尔值
* 可以在表级或字段级定义
* 对同一个字段可以定义多个CHECK约束，同时也可以定义NOT NULL约束
　
create table employees
(sal number(7,2)
constraint emp_sal_ck1 check (sal > 0)
)

alter table employees
add constraint emp_sal_ck2 check (sal < 20000)

删除约束

alter table dept drop unique (dname,loc) --指定约束的定义内容
alter table dept drop constraint dept_dname_loc_uk --指定约束名

删除约束时，默认将同时删除约束所对应的索引，如果要保留索引，用KEEP INDEX关键字
alter table employees drop primary key keep index

如果要删除的约束正在被其它约束引用，通过ALTER TABLE..DROP语句中指定CASCADE关键字能够同时删除引用它的约束

利用下面的语句在删除DEPT表中的PRIMARY KEY约束时，同时将删除其它表中引用这个约束的FOREIGN KEY约束:
alter table dept drop primary key cascade

禁用/激活约束(禁用/激活约束会引起删除和重建索引的操作)
alter table employees disable/enable unique email
alter table employees disable/enable constraint emp_ename_pk
alter tabel employees modify constraint emp_pk disable/enable
alter tabel employees modify constraint emp_ename_phone_uk disable/enable

如果有FOREIGN KEY约束正在引用UNIQUE或PRIMARY KEY约束，则无法禁用这些UNIQUE或PRIMARY KEY约束，
这时可以先禁用FOREIGN KEY约束，然后再禁用UNIQUE或PRIMARY KEY约束；或者可以在ALTER TABLE...DISABLE
语句中指定CASCADE关键字，这样将在禁用UNIQUE或PRIMARY KEY约束的同时禁用那些引用它们的FOREIGN KEY约束，如：
alter table employees disable primary key cascade

约束数据字典
all_constraints/dba_constraints/user_constraints 约束的基本信息，包括约束的名称，类型，状态
(约束类型：C(CHECK约束),P(主码约束),R(外部码约束),U(唯一码约束))
all_cons_columns/dba/user 约束对应的字段信息
Oracle的索引
    索引和对应的表应该位于不同的表空间中,oracle能够并行读取位于不同硬盘上的数据，可以避免产生I/O冲突
B树索引：在B树的叶节点中存储索引字段的值与ROWID。
唯一索引和不唯一索引都只是针对B树索引而言.
Oracle最多允许包含32个字段的复合索引

索引创建策略
1.导入数据后再创建索引
2.不需要为很小的表创建索引
3.对于取值范围很小的字段（比如性别字段）应当建立位图索引
4.限制表中的索引的数目
5.为索引设置合适的PCTFREE值
6.存储索引的表空间最好单独设定

创建不唯一索引
create index emp_ename on employees(ename)
tablespace users
storage(......)
pctfree 0;

创建唯一索引
create unique index emp_email on employees(email)
tablespace users;

创建位图索引
create bitmap index emp_sex on employees(sex)
tablespace users;

创建反序索引
create unique index order_reinx on orders(order_num,order_date)
tablespace users
reverse;

创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引)
create index emp_substr_empno
on employees(substr(empno,1,2))
tablespace users;

修改索引存储参数(与表类似，INITIAL和MINEXTENTS参数在索引建立以后不能再改变)
alter index emp_ename storage(pctincrease 50);

由于定义约束时由oracle自动建立的索引通常是不知道名称的，对这类索引的修改经常是利用alter table ..using index语句进行的,而不是alter index语句

利用下面的语句将employees表中primary key约束对应的索引的PCTFREE参数修改为5
alter table employees enable primary key using index pctfree 5;

清理索引碎片
1.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构）
alter index emp_pk coalesce;

2.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到其它的表空间中,重建索引
实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)
alter index emp_pk rebuild;

删除索引
drop index emp_ename;

如果索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除这个索引，然后再重建它.
如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引.
在删除一个表时,oracle会自动删除所有与该表相关的索引.

索引数据字典
all_indexes/dba_indexes/user_indexes 索引的基本信息
all_ind_columns/dba_ind_columns/user_ind_columns 索引对应的字段信息
    
 

课程
  java语言  java基本编程 -> j2EE编程
  数据库的能力 sqlserver -> ORACLE
  数据结构    训练编程思维方式 实际问题到计算机问题的转化
  linux      熟悉一种新的操作系统

  软件工厂    学到的知识 去做一个项目

  课程安排
  linux操作系统 4天
  数据结构    5天
  DOTNET介绍      4天

  ORACLE
    ORACLE数据库  10天
    XML语言       5天
  J2EE 
    JSP/servlet   12天
    EJB           3天
    struts        4天

  软件工厂        2周

找工作的技能
    j2EE   +  XML   + ORACLE/SQLSERVER
基础 java    html javascript   sql

ORACLE数据库
   甲骨文   英文中 意思是神寓 神说的话
      复杂性
      公司野心
1973年
    i  internet
    g  grid (网格)
   
    ERP  企业资源计划 Enterprise Resource Plan
    MIS  管理信息系统 Management Information System

    德国 SAP
    ORACLE application 11i
   

别的数据库
   DB2  IBM公司  IBM产品绑定紧密 (windows unix/linux)
   sybase  sybase公司  电信和银行 (windows unix/linux)
   sqlserver          windows平台 
  
小型
   access     office套件
   mysql      PHP(Personal HomePage)(linux) +
               mysql(不支持存储过程,触发器等)

      

学习方法
   动手能力
   做会的  不是书本学会的
  
   找问题的答案：
     问同学 问老师
     从网上 找答案
       搜索引擎  www.google.com
                百度
         错误号  错误文字放入关键字中
         提取关键字 多实验
          ORACLE安装
          ORACLE  +“安装”

ORACLE安装和删除
   ORACLE9i  9.0.1  空间1.76G
   ORACLE9i  9.0.2  空间2.86G
    事务处理 --  增 删除 修改频繁
    数据仓库 -- 查询 频繁

    GBK    --多200个字左右(偏僻字)
    GB2312 
 
 SETUP.exe 双击

 启动ORACLE数据库  OracleServiceACCP  服务  多启动几次
                  OracleOraHome90TNSListener 服务
    如果启动不了Listener(监听器)
     <1>改注册表 
           ImagePath  D:\oracle\ora90\BIN\TNSLSNR
     <2>用命令行启动
            cmd  ->   tnslsnr 最小化
            tNSlsnter  监听器  如果无法启动
            运行cmd 切换到DOS界面 敲tnslsnr 窗口不要关闭
     <3>D:\oracle\ora90\network\ADMIN
                listener.ora 文本文件
             HOST=localhost或机器名
              存盘
 
 
          用工具 ORACLE SQL*PLUS验证数据库是否启动
            用户名system   密码manager
            出现 连接到 和 SQL>符号 说明数据库已经可用了

        默认用户:
          超级用户: 用户名system/密码manager
                   用户名sys/密码change_on_install

          普通的用户: 用户名scott/密码tiger

 注意的问题:安装的源程序放在什么目录下??
        <1>目录中不要出现中文名字的目录
        <2>数据库的名字不要是中文,8个字符以内
        <3>字符集用默认的ZHS16GBK

删除:
   在硬盘上的一个隐含安装的目录Programm Files下的ORACLE  

操作ORACLE数据库的工具

   ORACLE SQL*PLUS 字符界面 ORACLE自带
     sqlplusw.exe windows窗口下的sqlplus
     sqlplus.exe   DOS下的sqlplus          
        
       用户名scott 密码tiger 普通用户
             system    manager   超级用户
             sys       change_on_install  超级用户 比system权限还要大

     SQLPLUS下的常用命令
       show user 察看用户
       connect 用户名/密码  连接到指定用户
            connect scott/tiger   
        select * from tab where tabtype='TABLE';  察看当前用户下的表      
       select count(*) from dept;  查询表dept中记录的行数
       desc dept             察看表dept的结构
       quit/exit             退出
       clear screen          清除屏幕
       set linesize 200      设置一行显示200个字符
       set pagesize 20       设置每页显示20行

       spool 文件名 (spool c:\abc.txt) 作日志文件命令        
       spool off
       
       修改D:\oracle\ora90\sqlplus\admin\glogin.sql文件
 可以设置SQLPLUS的环境
           set linesize 200
           set pagesize 20  

   
          

   ORACLE 企业管理器 (OEM)  图形界面

   PL/SQL Developer 第三方软件 ORACLE不带的

  SQLPLUS  /nolog    nolog是不登陆

  浏览器使用oracle (isqlplus)
     D:\oracle\ora90\Apache\Apache\conf\httpd.conf
           修改80端口   -> 8001
    http://localhost:8001/isqlplus

 

ORACLE中字段的数据类型
   字符型    char        范围  最大2000个字节 定长
               char(10)   '张三' 后添空格6个把10个字节补满  '张三      '
                 性别 char(2)   '男'
                
              varchar2    范围  最大4000个字节 变长
                 varchar2(10)  '张三'      在数据库中'张三'
                
             大对象 字符型大对象 >4000字节 最大4G
               CLOB (Character Large OBject)    
    
   数字     number     范围  10的-38次方 到10的38次方          
              可以表示小数 也可以表示整数  
             number(4)   最大表示4位整数  -9999 到 9999
             number(5,2)   表示5位有效数字 2位小数的 一个小数  -999.99 到 999.99
                  
            
   日期     date      包含年月日和时分秒  7个字节
   图片     blob      二进制大对象    图像/声音  4G

如何建表
   学生表student
        create table student( --学生表
           xh number(4), --学号
           xm varchar2(10), --姓名
           sex char(2), --性别
           birthday date, --日期
           sal number(7,2) --奖学金
        );
   班级class
       create table class( --班级表
          classid number(2), --班级编号
          cname varchar2(20) --班级名字
       );
  添加字段(学生所在班级classid)
      alter table student add (classid number(2));
  修改字段的长度
      alter table student modify (xm varchar2(12)) ;
  修改字段的类型(不能有记录的)
      alter table student modify (xh varchar2(5));
  删除一个字段
      alter table student drop column sal;
  删除表
      drop table student;
  表的名字修改
      rename student to stu;
  字段如何改名字
      --先删除
      a)alter table student drop column sal;        
      --再添加
      b)alter table student add (salary number(7,2));

如何插入数据
  插入数据 insert语句
      所有字段都插入
       insert into student values ('A001','张三','男','01-5月-05',10);
            ORACLE中默认的日期格式'DD-MON-YY'   dd 日子(天)  mon 月份 yy 2位的年
             '09-6月-99' 1999年6月9号
            改日期的默认格式
                 alter session set nls_date_format = 'yyyy-mm-dd';
    
       insert into student values ('A002','MIKE','男','1905-05-06',10);

           恢复ORACLE默认格式
               alter session set nls_date_format = 'dd-mon-yy';
           察看日期的格式
               set linesize 1000
               select * from nls_session_parameters
                 where parameter='NLS_DATE_FORMAT';
           永久设置日期格式
               改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd
     部分字段插入
       insert into student(xh,xm,sex) values ('A003','JOHN','女');
     插入空值
       insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);

  修改  update
      改一个字段 
       update student set sex='女' where xh='A001';
      改多个字段
       update student set sex='男',
                          birthday='1980-04-01'
              where xh='A001';
      改为空值 (修改为空时=null)
       update student set birthday=null where xh='A001';
      把生日为空的人的班级编号改为20(条件中的空是is null / is not null)
          update student set classid=20 where birthday is null;
         错误的没有达到要求
         update student set classid=20
           where birthday=null;
         不表示空值 表示xm是null的字符串        
         update student set classid=20 where xm='null';
 
   
  删除 delete
      delete from student;  删除所有记录,表结构还在,写日志,可以恢复的,速度慢
      drop table student;  删除表的结构和数据
      delete from student where xh='A001';  删除一条记录

      truncate table student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

  查询 select   
      select * from student;

      select xh,xm,sex from student;    

      select * from student where xh like 'A%1'; %任意多个字符
      select * from student where xh like 'A__1'; _1个字符
      select * from student where xh like '%A%';             select * from student where xh like 'A%';
select * from student where xh like '%A';                              

      select * from student where xh = 'A%';
                       
  
      select * from student
      order by birthday ;  升序 (order by birthday asc;)
 
      select * from student
      order by birthday desc; --降序
  
      select * from student
      order by birthday desc,xh asc; --按birthday 降序 按xh升序(asc/默认)
                
      select * from student
      where sex='女' or birthday='1999-02-01';

      select * from student
      where sex='女' and birthday='1999-02-01';

      select * from student
       where salary > 20 and xh <> 'B002'; (!=)

ORALCE的函数
   单行函数   返回值只有一个
     
   分组函数   返回值是多条记录
      group by
      sum
      avg           
     
单行函数
  字符函数
    concat 连接  ||
   <1>显示dname和loc中间用-分隔
     select deptno,dname||'----'||loc from dept;
    
     dual哑元表   没有表需要查询的时候 可以用它
         select 'Hello World' from dual;
         select 1+1 from dual;
         查询系统时间
          select sysdate from dual;
   <2>  initcap 首字母大写
       select ename,initcap(ename) from emp;
   <3>  lower   转换为小写字符
        select ename,lower(ename) from emp;
   <4> upper 转换为大写
        update dept set loc=lower(loc);
        update dept set loc=upper(loc);
   <5> LPAD 左填充
        select deptno,lpad(dname,10,' '),loc from dept;
   <6> RPAD 右填充
   <7> LTRIM 去除左边的空格
       RTRIM 去除右边的空格
       ALLTRIM  去除两边的空格
   <8>replace    替换
      translate  转换
       select ename,replace(ename,'S','s') from emp;
           用's'去替换ename中的'S'
        select ename,translate(ename,'S','a') from emp;
   <9> ASCII 求ASC码
       chr   asc码变字符
         select ascii('A') from dual;
         select chr(97) from dual;
         select 'Hello'||chr(9)||'World' from dual;
                           '\t' ascii码是 9
                           '\n' ascii码是 10

         select 'Hello'||'\t'||'World' from dual;    

   <10> substr 字符截取函数
           select ename,substr(ename,1,3) from emp;
               从第1个位置开始 显示3个字符
           select ename,substr(ename,4) from emp;
              从第4个位置开始显示后面所有的字符
   <11> instr 测试字符串出现的位置
          select ename,instr(ename,'S') from emp;
             'S'第1次出现的位置
  select ename,instr(ename,'T',1,2) from emp;                
             从第1个位置开始 测试'T'第2次出现的位置 
  <12> length 字符串的长度
        select ename,length(ename) from emp;
      
日期和 时间函数
   <1> sysdate 系统时间
        select sysdate from dual;
        select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
        select to_char(sysdate,'DDD') from dual  
      select to_char(sysdate,'D') from dual
      select to_char(sysdate,'DAY') from dual

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

  select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
 
 select '''' from dual;

select to_char(sysdate,'SSSSS') from dual;
  --从今天零点以后的秒数

   <2> ADD_MONTHS 添加月份 得到一个新的日期
        select add_months(sysdate,1) from dual;

       select add_months(sysdate,-1) from dual;

       select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
       select add_months(sysdate,12) from dual;
           一年以后的今天
select add_months(sysdate,-12) from dual;
           一年以前的今天

     trunc(sysdate) 截取年月日
 
 select sysdate+2 from dual;
        数字代表的是天数

     两个日期之间的差值代表天数

   <3> last_day  某月的最后一天
       select last_day(sysdate) from dual;
      
       select add_months(last_day(sysdate)+3,-1) from dual;
            本月第3天的日期
  <4>  months_between 两个日期之间的月数
         select months_between(sysdate,'2005-02-01') from dual;
                 方向 sysdate - '2005-02-01'
      select months_between('2005-02-01',sysdate) from dual;
    
 转换函数
   to_char   把日期或数字类型变为字符串
       select to_char(sysdate,'hh24:mi:ss') from dual;
       select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

       select sal,to_char(sal,'L9,999') from emp;
             L本地货币
  to_number   把字符串变成数字
      select to_number('19990801') from dual;
 
  to_date     把字符串变成日期
      select to_date('19800101','yyyymmdd') from dual;

      select to_char(to_date('19800101','yyyymmdd'),
        'yyyy"年"mm"月"dd"日"') from dual;     
 数学函数
    ceil(x)  不小于x的最小整数
        ceil（12.4)   13
        ceil(-12.4)   -12
    floor(x)  不大于x的最大整数
        floor(12.5)  12
        floor(-12.4)  -13

   round(x)  四舍五入
     round(12.5)   13
     round(12.456,2) 12.46

   trunc(x)  舍去尾数
     trunc(12.5)  12
     trunc(12.456,2)  12.45
    舍去日期的小时部分
     select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
   mod(x,n)  x除以n以后的余数
     mod(5,2) 1
     mod(4,2) 0

   power(x,y)  x的y次方
    select power(3,3) from dual;
    
 
混合函数
      求最大值
   select greatest(100,90,80,101,01,19) from dual;
  
      求最小值
   select least(100,0,-9,10) from dual;

      空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
   select comm,nvl(comm,0) from emp;
      comm 类型和 值的类型是 一致的
  
复杂的函数
   decode   选择结构  (if ... elseif .... elesif ... else结构)
   
要求:
   sal=800  显示低工资  
   sal=3000  正常工资
   sal=5000  高工资
    只能做等值比较

  select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
  from emp;
 表示如下的if  else 结构
     if sal=800 then
        '低工资'
     else if sal =3000 then
        '正常工资'
     else if  sal = 5000 then
         '高工资'
     else
        '没判断'
     end if
         

   sal > 800           sal -800 > 0  

 判断正负
  sign(x)   x是正  1
            x是负  -1
            x是0   0
    select sign(-5) from  dual;

 如何做大于小于的比较？？？？
  sal<1000  显示低工资   sal-1000<0   sign(sal-1000) = -1
   1000<=sal<=3000  正常工资
   3000<sal<=5000  高工资

   select sal,decode(
             sign(sal-1000),-1,'低工资',
            decode(sign(sal-3000),-1,'正常工资',
                            0,'正常工资',1,
            decode(sign(sal-5000),-1,'高工资','高工资')
             )) as 工资状态 from emp;
     
  一般的情况  decode(x,y1,z1,y2,z2,z3)
      if x= y1 then
          z1         
      else if x = y2 then
          z2
      else
         z3
      end if 

分组函数   返回值是多条记录 或计算后的结果
      group by
      sum
      avg

<1>  计算记录的条数 count

  select count(*) from emp;
  select count(1) from emp;

  select count(comm) from emp; 字段上count 会忽略空值
      comm不为空值的记录的条数

  统计emp表中不同工作的个数 ????
   select count(distinct job) from emp;

   select distinct job from emp;
   select distinct job,empno from emp;
   select job,empno from emp;
    得到的效果是一样的,distinct 是消去重复行
                       不是消去重复的列
 <2>group  by 分组统计
     --在没有分组函数的时候
     --相当于distinct 的功能
     select job from emp group by job;

     select distinct job from emp;

   --有分组函数的时候
   --分组统计的功能
   统计每种工作的工资总额是多少??
     select job,sum(sal) from emp
          group by job;       --行之间的数据相加

     select sum(sal) from emp;  --公司的工资总额

 统计每种工作的平均工资是多少?? 
     select job,avg(sal) from emp
          group by job;   

    select avg(saL) from emp; --整个公司的平均工资

 显示平均工资>2000的工作?？？
   <1>统计每种工作的平均工资是多少
   <2>塞选出平均工资>2000的工作     

    从分组的结果中筛选 having
   select job,avg(sal) from emp
          group by job
          having avg(sal) > 2000;
   group by 经常和having搭配来筛选

计算工资在2000以上的各种工作的平均工资？？？？
  select job,avg(sal) from emp
          where sal > 2000
          group by job
          having avg(sal) > 3000;

    一般group by  和 having搭配
        表示对分组后的结果的筛选
     where子句 --- 用于对表中数据的筛选  
 
  <3> max min
   select max(sal) from emp;
     公司的最高工资
   select min(sal) from emp ;
     公司的最低工资

找每个部门的最高和最低的工资??
  select deptno,max(sal),min(sal) from emp
     group by deptno;
找每个工作的最高和最低的工资??
  select job,max(sal),min(sal) from emp
     group by job;
找每个部门中每种工作的最高和最低的工资??
  select deptno,job,max(sal),min(sal)
   from emp
   group by deptno,job;

 select max(sal),min(sal)
   from emp
   group by deptno,job;

    单个字段如果没有被分组函数所包含，
       而其他字段又是分组函数的话     
      一定要把这个字段放到group by中去

 <4>关联查询
      多张表,而表与表之间是有联系的

       是通过字段中的数据的内在联系来发生
       而不是靠相同的字段名来联系的或者是否有主外键的联系是没有关系的
      select dname,ename from emp,dept;
         笛卡尔积  (无意义的)
      --当2个表作关联查询的时候一定要写关联的条件
      --N个表 关联条件一定有N-1个

      select dname,ename from mydept,myemp
       where mydept.no = myemp.deptno;

      多表查询的时候一定要有关联的条件
        --使用的表的全名
        select dname,ename from emp,dept
         where emp.deptno = dept.deptno ;
       
        --使用表的别名
         select dname,ename,a.deptno from emp a,dept b
         where a.deptno = b.deptno and a.deptno = 10;

       --等值连接(内连接-两个表的数据作匹配a.deptno = b.deptno )
         select dname,ename,a.deptno from
         emp a inner join dept b
         on a.deptno = b.deptno;
         where a.deptno = 10;

       --on写连接条件的
       --where中写别的条件

       --使用where/on
         select dname,ename,a.deptno from emp a,dept b
         where a.deptno = b.deptno and a.deptno=10;
        
          --on中写连接条件
          --where中写其他的条件
          select dname,ename,a.deptno from
         emp a inner join dept b
         on a.deptno = b.deptno
         where a.deptno = 10 ;

       --外连接
         左外连接 右外连接  全外连接
         (+)写法只有在ORACLE中有效
        select dname,ename,b.deptno
        from emp a,dept b
        where a.deptno(+) = b.deptno;
       --标准写法
         select dname,ename,b.deptno
        from emp a right outer join dept b
        on a.deptno = b.deptno;        

        select dname,ename,b.deptno
        from emp a,dept b
        where a.deptno = b.deptno(+);
    --标准写法
         select dname,ename,b.deptno
        from emp a left outer join dept b
        on a.deptno = b.deptno;        

    --标准写法(全外联) 
           select dname,ename,b.deptno
        from emp a full outer join dept b
        on a.deptno = b.deptno;    
  
    --自连接
        select a.ename as 员工姓名,b.ename as 经理名字 from emp a,emp b
        where a.mgr = b.empno(+);
              a.empno = b.mgr  ???????
 
 <5>子查询
    在select语句中嵌套了另一个select语句
     1)where 子句中嵌套子查询
     2)用子查询的结果 作为字段来出现

--1)where 子句中嵌套子查询,执行顺序是
      先执行子查询 再执行主查询
  找出工资高于公司平均工资的所有员工??
   select * from emp where sal+nvl(comm,0) > (select avg(sal+nvl(comm,0)) from emp);

   高于部门30中员工最高工资的其他员工???
    
  select * from emp where  sal+nvl(comm,0) > all (select sal+nvl(comm,0) from emp
               where deptno = 30);

   低于部门30中员工工资的其他员工???
  select * from emp where  sal+nvl(comm,0) < all (select sal+nvl(comm,0) from emp
               where deptno = 30);

  select * from emp where  sal+nvl(comm,0) < any (select sal+nvl(comm,0) from emp
               where deptno = 30);

--2）用子查询的结果 作为字段来出现 
      先执行主查询 再执行子查询

  <1>找员工姓名和直接上级的名字
   select ename as 员工姓名,(select ename from emp where empno = a.mgr) as 经理姓名
   from emp a;      
 <2>显示部门名称和人数
  select dname,(select count(*) from emp where deptno=a.deptno) as rs from dept a;
 <3>显示每个部门的最高工资的员工
    select * from emp a where (deptno, sal) in  (  select deptno,max(sal) from emp group by deptno);
 
 select a.* from emp a,(  select deptno,max(sal) as msal from emp group by deptno) c where a.deptno = c.deptno and
a.sal = c.msal;

--最大值和最小值的比较 转化为人数的比较
select * from emp a where (select count(*) from
 emp where deptno = a.deptno and
 sal > a.sal) = 0 and a.deptno is not null;

 <4>显示每个部门的工资前2名的员工
select * from emp a where (select count(*) from
 emp where deptno = a.deptno and
 sal > a.sal) <=1 and a.deptno is not null;

<6> 层次查询
--level 伪列 层次查询的时候可以使用的  层的编号

  select lpad('+',level,' ')||ename from emp
     connect by prior empno = mgr --父子关系 父结点中的empno = 子节点中的mgr
     start with mgr is null;--从 mgr is null的节点 开始遍历

select lpad('+',level,' ')||ename from emp
     connect by prior empno = mgr
     start with ename = 'BLAKE';

<7> TOP 前几行 (sqlserver)
    rownum  (oracle伪列)

 --emp表的前2行
 --rownum必须使用<=的关系比较运算符

 select * from emp where rownum <= 2;

 select top 2 * from emp; --sqlserver的写法

 select * from emp where rownum = 1;

 --公司工资最高的2个人
 /*select * from emp
 where rownum <= 2
order by sal desc;*/ 错误的

   select * from (select * from emp order by sal desc)
   where rownum <= 2;

 --分页查询
  --每页显示4行 一共有14行记录

  第1页    第1-4行
  第2页    第5-8行
  第3页    第9-12行
  第4页    第13-14行

 --希望每次页面显示的时候 都只查询回需要的记录
 
   select * from (select rownum as num,emp.* from emp)
   where num >= 9 and num <= 12;

   select * from (select rownum as num,emp.* from emp)
   where num >= 13 and num <= 14;

<例子>
建立表如下:

学生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键
[StudentName][char]NOT NULL

)
课程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key,       --主键
[SubjectName][char]NOT NULL
)
成绩表
CREATE Grade(
[Studentid][Int]NOT NULL,  --联合主键
[SubjectID][char]NOT NULL,  --联合主键
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)

1.将建表命令改为ORACLE语句在ORACLE中建表
create table student( --学生表
studentid number(3) primary key, --学生编号
studentname varchar2(20) --学生的姓名
);

create table subject( --课程表
subjectid char(3) primary key, --课程编号
subjectname varchar2(20)  --课程的名字
);

create table grade( --分数表
studentid number(3) references student(studentid), --学生id
subjectid char(3) references subject(subjectid), --课程id
mark      number(3), --分数
primary key (studentid,subjectid) --联合主键
);

 

insert into student values (101,'张三');
insert into student values (102,'李云');
insert into student values (103,'未');

insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');

insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);

insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);

insert into grade values (103,'A01',71);

 

2.作如下4题

第一问：查询出以下信息

学号 学生姓名 课程名称 成绩 （要全部学生信息）

关联查询 (多张表的)
别名

select a.studentid as "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid;

[select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c] 笛卡尔积

       3 * 3 * 6 = 54;

第二问：查询出以下信息

学号 学生姓名 课程名称 成绩（只显示每科最高分）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "课程名称",max(mark) "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)

(最高分---分数比我高的学生的人数=0)
select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0

第三问：查询出以下信息

学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",
decode(sign(mark-60),-1,'不及格','及格') "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid

第四问：查询出以下信息

学号 学生姓名 （查询出课程超过1门以上学生的信息）

select a.studentid "学  号",studentname "学生姓名",
count(subjectname)
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2

 

 

 

 

 

 

 

 

 

 

 

 

-- select * from emp where rownum > 2;   错误的