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=" xmlns:xsi=" xsi:schemaLocation="http://www.springframework.org/schema/beans <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;   錯誤的