posts - 18, comments - 1, trackbacks - 0

2007年3月11日

再shortcut 的target 后面加上-data workspace path

如"D:\Program Files\IBM\Rational\SDP\6.0\rationalsdp.exe" -data E:\EE-workspace\1600

posted @ 2007-09-13 10:55 sunny 阅读(230) | 评论 (0) | 编辑收藏

log4j的配置

实际编程时，要使Log4j真正在系统中运行事先还要对配置文件进行定义。定义步骤就是对Logger、Appender及Layout的分别使用。 Log4j支持两种配置文件格式，一种是XML格式的文件，一种是java properties（key=value）【Java特性文件（键=值）】。下面我们介绍使用Java特性文件做为配置文件的方法具体如下：　　　　1、配置根Logger，其语法为：　　log4j.rootLogger = [ level ] , appenderName1, appenderName2, … level : 是日志记录的优先级，分为OFF、FATAL、ERROR、WARN、INFO、DEBUG、ALL或者您定义的级别。Log4j建议只使用四个级别，优先级从高到低分别是ERROR、WARN、INFO、DEBUG。通过在这里定义的级别，您可以控制到应用程序中相应级别的日志信息的开关。比如在这里定义了INFO级别，则应用程序中所有DEBUG级别的日志信息将不被打印出来。　　 appenderName:就是指定日志信息输出到哪个地方。您可以同时指定多个输出目的地。例如：log4j.rootLogger＝info,A1,B2,C3 　　　　2、配置日志信息输出目的地，其语法为：　　log4j.appender.appenderName = fully.qualified.name.of.appender.class // 　　 "fully.qualified.name.of.appender.class" 可以指定下面五个目的地中的一个： 1.org.apache.log4j.ConsoleAppender（控制台） 2.org.apache.log4j.FileAppender（文件） 3.org.apache.log4j.DailyRollingFileAppender（每天产生一个日志文件） 4.org.apache.log4j.RollingFileAppender（文件大小到达指定尺寸的时候产生一个新的文件） 5.org.apache.log4j.WriterAppender（将日志信息以流格式发送到任意指定的地方） 1.ConsoleAppender选项 Threshold=WARN:指定日志消息的输出最低层次。 ImmediateFlush=true:默认值是true,意谓着所有的消息都会被立即输出。 Target=System.err：默认情况下是：System.out,指定输出控制台 2.FileAppender 选项 Threshold=WARN:指定日志消息的输出最低层次。 ImmediateFlush=true:默认值是true,意谓着所有的消息都会被立即输出。 File=mylog.txt:指定消息输出到mylog.txt文件。 Append=false:默认值是true,即将消息增加到指定文件中，false指将消息覆盖指定的文件内容。 3.DailyRollingFileAppender 选项 Threshold=WARN:指定日志消息的输出最低层次。 ImmediateFlush=true:默认值是true,意谓着所有的消息都会被立即输出。 File=mylog.txt:指定消息输出到mylog.txt文件。 Append=false:默认值是true,即将消息增加到指定文件中，false指将消息覆盖指定的文件内容。 DatePattern='.'yyyy-ww:每周滚动一次文件，即每周产生一个新的文件。当然也可以指定按月、周、天、时和分。即对应的格式如下： 1)'.'yyyy-MM: 每月 2)'.'yyyy-ww: 每周 3)'.'yyyy-MM-dd: 每天 4)'.'yyyy-MM-dd-a: 每天两次 5)'.'yyyy-MM-dd-HH: 每小时 6)'.'yyyy-MM-dd-HH-mm: 每分钟 4.RollingFileAppender 选项 Threshold=WARN:指定日志消息的输出最低层次。 ImmediateFlush=true:默认值是true,意谓着所有的消息都会被立即输出。 File=mylog.txt:指定消息输出到mylog.txt文件。 Append=false:默认值是true,即将消息增加到指定文件中，false指将消息覆盖指定的文件内容。 MaxFileSize=100KB: 后缀可以是KB, MB 或者是 GB. 在日志文件到达该大小时，将会自动滚动，即将原来的内容移到mylog.log.1文件。 MaxBackupIndex=2:指定可以产生的滚动文件的最大数。实际应用：　　log4j.appender.A1=org.apache.log4j.ConsoleAppender //这里指定了日志输出的第一个位置A1是控制台ConsoleAppender 　　　　3、配置日志信息的格式，其语法为：　　A.log4j.appender.appenderName.layout = fully.qualified.name.of.layout.class "fully.qualified.name.of.layout.class" 可以指定下面4个格式中的一个： 1.org.apache.log4j.HTMLLayout（以HTML表格形式布局），　　 2.org.apache.log4j.PatternLayout（可以灵活地指定布局模式），　　 3.org.apache.log4j.SimpleLayout（包含日志信息的级别和信息字符串），　　 4.org.apache.log4j.TTCCLayout（包含日志产生的时间、线程、类别等等信息） 1.HTMLLayout 选项 LocationInfo=true:默认值是false,输出java文件名称和行号 Title=my app file: 默认值是 Log4J Log Messages. 2.PatternLayout 选项 ConversionPattern=%m%n :指定怎样格式化指定的消息。 3.XMLLayout 选项 LocationInfo=true:默认值是false,输出java文件和行号实际应用：　　log4j.appender.A1.layout=org.apache.log4j.PatternLayout B. log4j.appender.A1.layout.ConversionPattern=%-4r %-5p %d{yyyy-MM-dd HH:mm:ssS} %c %m%n 这里需要说明的就是日志信息格式中几个符号所代表的含义：　　－X号: X信息输出时左对齐； %p: 输出日志信息优先级，即DEBUG，INFO，WARN，ERROR，FATAL, %d: 输出日志时间点的日期或时间，默认格式为ISO8601，也可以在其后指定格式，比如：%d{yyy MMM dd HH:mm:ss,SSS}，输出类似：2002年10月18日 22：10：28，921 %r: 输出自应用启动到输出该log信息耗费的毫秒数 %c: 输出日志信息所属的类目，通常就是所在类的全名 %t: 输出产生该日志事件的线程名 %l: 输出日志事件的发生位置，相当于%C.%M(%F:%L)的组合,包括类目名、发生的线程，以及在代码中的行数。举例：Testlog4.main(TestLog4.java:10) %x: 输出和当前线程相关联的NDC(嵌套诊断环境),尤其用到像java servlets这样的多客户多线程的应用中。 %%: 输出一个"%"字符 %F: 输出日志消息产生时所在的文件名称 %L: 输出代码中的行号 %m: 输出代码中指定的消息,产生的日志具体信息 %n: 输出一个回车换行符，Windows平台为"\r\n"，Unix平台为"\n"输出日志信息换行可以在%与模式字符之间加上修饰符来控制其最小宽度、最大宽度、和文本的对齐方式。如： 1)%20c：指定输出category的名称，最小的宽度是20，如果category的名称小于20的话，默认的情况下右对齐。 2)%-20c:指定输出category的名称，最小的宽度是20，如果category的名称小于20的话，"-"号指定左对齐。 3)%.30c:指定输出category的名称，最大的宽度是30，如果category的名称大于30的话，就会将左边多出的字符截掉，但小于30的话也不会有空格。 4)%20.30c:如果category的名称小于20就补空格，并且右对齐，如果其名称长于30字符，就从左边交远销出的字符截掉。　　这里上面三个步骤是对前面Log4j组件说明的一个简化；下面给出一个具体配置例子，在程序中可以参照执行：　　log4j.rootLogger=INFO,A1，B2 　　log4j.appender.A1=org.apache.log4j.ConsoleAppender 　　log4j.appender.A1.layout=org.apache.log4j.PatternLayout 　　log4j.appender.A1.layout.ConversionPattern=%-4r %-5p %d{yyyy-MM-dd HH:mm:ssS} %c %m%n 　　根据上面的日志格式，某一个程序的输出结果如下：　　0　　INFO　2003-06-13 13:23:46968 ClientWithLog4j Client socket: Socket[addr=localhost/127.0.0.1,port=8002,localport=2014] 16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j Server says: 'Java server with log4j, Fri Jun 13 13:23:46 CST 2003' 　　16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j GOOD 　　16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j Server responds: 'Command 'HELLO' not understood.' 　　16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j HELP 　　16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j Server responds: 'Vocabulary: HELP QUIT' 　　16　 DEBUG 2003-06-13 13:23:46984 ClientWithLog4j QUIT 4. # 当输出信息于回滚文件时 log4j.appender.ROLLING_FILE=org.apache.log4j.RollingFileAppender //指定以文件的方式输出日志 log4j.appender.ROLLING_FILE.Threshold=ERROR log4j.appender.ROLLING_FILE.File=rolling.log //文件位置,也可以用变量${java.home}、rolling.log log4j.appender.ROLLING_FILE.Append=true log4j.appender.ROLLING_FILE.MaxFileSize=10KB //文件最大尺寸 log4j.appender.ROLLING_FILE.MaxBackupIndex=1 //备份数 log4j.appender.ROLLING_FILE.layout=org.apache.log4j.PatternLayout log4j.appender.ROLLING_FILE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n 　　 ×××××××××××××××××××××××××××××××××××××××××××××××× Log4j比较全面的配置 LOG4J的配置之简单使它遍及于越来越多的应用中了：Log4J配置文件实现了输出到控制台、文件、回滚文件、发送日志邮件、输出到数据库日志表、自定义标签等全套功能。择其一二使用就够用了， log4j.rootLogger=DEBUG,CONSOLE,A1,im log4j.addivity.org.apache=true # 应用于控制台 log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.Threshold=DEBUG log4j.appender.CONSOLE.Target=System.out log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.CONSOLE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n #log4j.appender.CONSOLE.layout.ConversionPattern=[start]%d{DATE}[DATE]%n%p[PRIORITY]%n%x[NDC]%n%t[THREAD] n%c[CATEGORY]%n%m[MESSAGE]%n%n #应用于文件 log4j.appender.FILE=org.apache.log4j.FileAppender log4j.appender.FILE.File=file.log log4j.appender.FILE.Append=false log4j.appender.FILE.layout=org.apache.log4j.PatternLayout log4j.appender.FILE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n # Use this layout for LogFactor 5 analysis # 应用于文件回滚 log4j.appender.ROLLING_FILE=org.apache.log4j.RollingFileAppender log4j.appender.ROLLING_FILE.Threshold=ERROR log4j.appender.ROLLING_FILE.File=rolling.log //文件位置,也可以用变量${java.home}、rolling.log log4j.appender.ROLLING_FILE.Append=true //true:添加 false:覆盖 log4j.appender.ROLLING_FILE.MaxFileSize=10KB //文件最大尺寸 log4j.appender.ROLLING_FILE.MaxBackupIndex=1 //备份数 log4j.appender.ROLLING_FILE.layout=org.apache.log4j.PatternLayout log4j.appender.ROLLING_FILE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n #应用于socket log4j.appender.SOCKET=org.apache.log4j.RollingFileAppender log4j.appender.SOCKET.RemoteHost=localhost log4j.appender.SOCKET.Port=5001 log4j.appender.SOCKET.LocationInfo=true # Set up for Log Facter 5 log4j.appender.SOCKET.layout=org.apache.log4j.PatternLayout log4j.appender.SOCET.layout.ConversionPattern=[start]%d{DATE}[DATE]%n%p[PRIORITY]%n%x[NDC]%n%t[THREAD]%n%c[CATEGORY]%n%m[MESSAGE]%n%n # Log Factor 5 Appender log4j.appender.LF5_APPENDER=org.apache.log4j.lf5.LF5Appender log4j.appender.LF5_APPENDER.MaxNumberOfRecords=2000 # 发送日志给邮件 log4j.appender.MAIL=org.apache.log4j.net.SMTPAppender log4j.appender.MAIL.Threshold=FATAL log4j.appender.MAIL.BufferSize=10 log4j.appender.MAIL.From=web@www.wuset.com log4j.appender.MAIL.SMTPHost=www.wusetu.com log4j.appender.MAIL.Subject=Log4J Message log4j.appender.MAIL.To=web@www.wusetu.com log4j.appender.MAIL.layout=org.apache.log4j.PatternLayout log4j.appender.MAIL.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n # 用于数据库 log4j.appender.DATABASE=org.apache.log4j.jdbc.JDBCAppender log4j.appender.DATABASE.URL=jdbc:mysql://localhost:3306/test log4j.appender.DATABASE.driver=com.mysql.jdbc.Driver log4j.appender.DATABASE.user=root log4j.appender.DATABASE.password= log4j.appender.DATABASE.sql=INSERT INTO LOG4J (Message) VALUES ('[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n') log4j.appender.DATABASE.layout=org.apache.log4j.PatternLayout log4j.appender.DATABASE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n log4j.appender.A1=org.apache.log4j.DailyRollingFileAppender log4j.appender.A1.File=SampleMessages.log4j log4j.appender.A1.DatePattern=yyyyMMdd-HH'.log4j' log4j.appender.A1.layout=org.apache.log4j.xml.XMLLayout #自定义Appender log4j.appender.im = net.cybercorlin.util.logger.appender.IMAppender log4j.appender.im.host = mail.cybercorlin.net log4j.appender.im.username = username log4j.appender.im.password = password log4j.appender.im.recipient = corlin@cybercorlin.net log4j.appender.im.layout=org.apache.log4j.PatternLayout log4j.appender.im.layout.ConversionPattern =[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n

posted @ 2007-06-05 19:37 sunny 阅读(487) | 评论 (1) | 编辑收藏

struts-configure 的配置和对应页面

<%@page contentType="text/html;charset=gbk" %>
<%@taglib uri="http://struts.apache.org/tags-bean" prefix="bean"%>

<h3 align="center">Dispatch Action</h3>
<hr>
<pre>
    根据请求中的某个（1）参数的值来调用Action的方法（2）。
    （1）参数名字在<action parameter="method" />
    （2）方法名字为 method 参数的值。
</pre>
<center>
<a href="${pageContext.request.contextPath}/dispatch.do?method=login">login</a>   
<a href="${pageContext.request.contextPath}/dispatch.do?method=find">find</a>   
<a href="${pageContext.request.contextPath}/dispatch.do?method=findById">findById</a>   
<a href="${pageContext.request.contextPath}/dispatch.do?method=register">register</a>   
<a href="${pageContext.request.contextPath}/dispatch.do?method=remove">remove</a>   
<a href="${pageContext.request.contextPath}/dispatch.do?method=modify">modify</a>
</center>
<hr>
<h3 align="center">LookupDispatch Action</h3>
<hr>
<pre>
</pre>
<center>
<form method="post" action="${pageContext.request.contextPath}/lookup.do">
    <input type="submit" value="<bean:message key="submit.login"/>" name="method">
    <input type="submit" value="<bean:message key="submit.find"/>" name="method">
    <input type="submit" value="<bean:message key="submit.findById"/>" name="method">
    <input type="submit" value="<bean:message key="submit.register"/>" name="method">
    <input type="submit" value="<bean:message key="submit.remove"/>" name="method">
    <input type="submit" value="<bean:message key="submit.modify"/>" name="method">
</form>
</center>

<hr>
<h3 align="center">MappingDispatch Action</h3>
<hr>
<pre>
    根据<action>标记的parameter属性的值来调用Action的方法（1）。
    （1）方法名字为 parameter属性的值。
</pre>
<center>
<a href="${pageContext.request.contextPath}/mapping/login.do">login</a>   
<a href="${pageContext.request.contextPath}/mapping/find.do">find</a>   
<a href="${pageContext.request.contextPath}/mapping/findById.do?id=1">findById</a>   
<a href="${pageContext.request.contextPath}/mapping/register.do">register</a>   
<a href="${pageContext.request.contextPath}/mapping/remove.do?id=2">remove</a>   
<a href="${pageContext.request.contextPath}/mapping/modify.do">modify</a>
</center>

=====================================================

<?xml version="1.0" encoding="gb2312" ?>

<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.2//EN"
"http://jakarta.apache.org/struts/dtds/struts-config_1_2.dtd">

<struts-config>

    <form-beans>
        <form-bean name="loginForm" type="com.allanlxf.action.LoginForm"/>
        <form-bean name="userForm" type="org.apache.struts.action.DynaActionForm">
            <form-property name="userName" type="java.lang.String" />
            <form-property name="password" type="java.lang.String" />
        </form-bean>
    </form-beans>

    <action-mappings>

        <action   path="/core/login" type="com.allanlxf.action.LoginAction"
                   name="loginForm" input="/core/login.jsp" validate="true" scope="request">
          <exception key="errors.root"
                       path="/core/error.jsp"
                       type="com.allanlxf.biz.IllegalUserException" />
          <forward name="success" path="/core/success.jsp" redirect="true"/>
          <forward name="fail" path="/core/fail.jsp" />
        </action>

        <action   path="/dispatch" type="com.allanlxf.struts.actions.UserAction" parameter="method">
          <forward name="login" path="/actions/pages/login_next.jsp" />
          <forward name="find" path="/actions/pages/find_next.jsp" />
          <forward name="findById" path="/actions/pages/findById_next.jsp" />
          <forward name="remove" path="/actions/pages/remove_next.jsp" />
          <forward name="modify" path="/actions/pages/modify_next.jsp" />
          <forward name="register" path="/actions/pages/register_next.jsp" />
        </action>

        <action   path="/mapping/login" type="com.allanlxf.struts.actions.UserMappingAction" parameter="login">
          <forward name="next" path="/actions/pages/login_next.jsp" />
        </action>

        <action   path="/mapping/find" type="com.allanlxf.struts.actions.UserMappingAction" parameter="find">
          <forward name="next" path="/actions/pages/find_next.jsp" />
        </action>

        <action   path="/mapping/findById" type="com.allanlxf.struts.actions.UserMappingAction" parameter="findById">
          <forward name="next" path="/actions/pages/findById_next.jsp" />
        </action>

        <action   path="/mapping/remove" type="com.allanlxf.struts.actions.UserMappingAction" parameter="remove">
          <forward name="next" path="/actions/pages/remove_next.jsp" />
        </action>

        <action   path="/mapping/modify" type="com.allanlxf.struts.actions.UserMappingAction" parameter="modify">
          <forward name="next" path="/actions/pages/modify_next.jsp" />
        </action>

        <action   path="/mapping/register" type="com.allanlxf.struts.actions.UserMappingAction" parameter="register">
          <forward name="next" path="/actions/pages/register_next.jsp" />
        </action>

        <action   path="/lookup" type="com.allanlxf.struts.actions.UserLookupAction" parameter="method">
          <forward name="login" path="/actions/pages/login_next.jsp" />
          <forward name="find" path="/actions/pages/find_next.jsp" />
          <forward name="findById" path="/actions/pages/findById_next.jsp" />
          <forward name="remove" path="/actions/pages/remove_next.jsp" />
          <forward name="modify" path="/actions/pages/modify_next.jsp" />
          <forward name="register" path="/actions/pages/register_next.jsp" />
        </action>

        <action   path="/dyna/register" type="com.allanlxf.action.RegisterAction"
                   name="userForm">
          <forward name="success" path="/dyna/success.jsp"/>
        </action>

    </action-mappings>

    <message-resources parameter="com.allanlxf.MessageResources" />

</struts-config>

posted @ 2007-04-25 16:12 sunny 阅读(653) | 评论 (0) | 编辑收藏

HttpFilter

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public abstract class HttpFilter implements Filter
{
    private FilterConfig config;

    //////////////////////////////////
    public void init(FilterConfig config) throws ServletException
    {
        this.config = config;
        init();
    }

    public void init() throws ServletException
    {
    }
    ///////////////////////////////
    public FilterConfig getFilterConfig()
    {
        return config;
    }

    public String getInitParameter(String name)
    {
        return config.getInitParameter(name);
    }

    public ServletContext getServletContext()
    {
        return config.getServletContext();
    }

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
                                                throws IOException, ServletException
    {
        doFilter((HttpServletRequest)request, (HttpServletResponse)response, chain);
    }

    public abstract void doFilter(HttpServletRequest request, HttpServletResponse response, FilterChain chain)
                                                throws IOException, ServletException;

    public void destroy()
    {
    }
}

posted @ 2007-04-06 16:31 sunny 阅读(301) | 评论 (0) | 编辑收藏

一个工厂方法

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
public class ConnectionFactory
{

/**

oracle 分页

select * from (
select a.*, ROWNUM rn from ( select * from user_info ) a
where ROWNUM<=40 )
where rn >=21

*/
    private static Properties config = new Properties();

    static
    {
        try
        {
            InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            config.load(in);
            in.close();
        }catch(IOException e)
        {
            e.printStackTrace();
            throw new ExceptionInInitializerError(e.getMessage());
        }
    }

    public static Connection getConnection()
    {
        if(config.getProperty("jndi-name") != null)
        {
            return getJndiConnection();
        }

        return getDirectConnection();
    }

    public static Connection getJndiConnection()
    {
        Connection con = null;
        try
        {
/**context.xml

数据源的配置
<Context>
      <Resource name="jdbc/oracle" auth="Container" type="javax.sql.DataSource" maxActive="2" maxIdle="1" maxWait="-1"
      username="openlab" password="open123" driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@192.168.0.20:1521:tarena"/>
</Context
*/
            Context ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/" + config.getProperty("jndi-name"));
            con = ds.getConnection();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        return con;
    }

    public static Connection getDirectConnection()
    {
        Connection con = null;
        try
        {
            Class.forName(config.getProperty("driver"));
            con = DriverManager.getConnection(config.getProperty("dburl") ,config.getProperty("user"), config.getProperty("password"));
        }catch(ClassNotFoundException cne)
        {
            cne.printStackTrace();
        }catch(SQLException sqle)
        {
            sqle.printStackTrace();
        }

        return con;
    }

    public static void close(ResultSet rs, Statement st, Connection con)
    {
        try
        {
            rs.close();
        }catch(Exception e)
        {
        }

        try
        {
            st.close();
        }catch(Exception e)
        {
        }

        try
        {
            con.close();
        }catch(Exception e)
        {
        }
    }

    public static void main(String[] args) throws Exception
    {
        Connection con = ConnectionFactory.getConnection();
        System.out.println(con);
        con.close();
    }
}

posted @ 2007-04-05 15:30 sunny 阅读(151) | 评论 (0) | 编辑收藏

jdbc 笔记

上午:

一.JDBC原理概述

1，JDBC是一套协议，是JAVA开发人员和数据库厂商达成的协议，也就是由Sun定义一组接口，由数据库厂商来实现，并规定了JAVA开发人员访问数据库所使用的方法的调用规范。

2，JDBC的实现是由数据库厂商提供，以驱动程序形式提供。

3，JDBC在使用前要先加载驱动。

JDBC对于使用者要有一致性，对不同的数据库其使用方法都是相同的。

驱动开发必须要实现Driver接口。

数据库驱动的实现方式

JDBC-ODBC桥接式

JDBC网络驱动，这种方式是通过中间服务器的协议转换来实现的

JDBC+本地驱动，这种方式的安全性比较差。

JDBC驱动，由数据库厂商实现。

二.JDBC的API

java.sql包和javax.sql包

Driver接口(驱动),在加载某一 Driver 类时，它应该创建自己的实例并向 DriverManager 注册该实例。这意味着用户可以通过调用以下程序加载和注册一个驱动程序

Class.forName("oracle.jdbc.driver.OracleDriver")

DriverManager类（驱动管理器），它可以创建连接，它本身就是一个创建Connection的工厂(Factory)。

Connection接口，会根据不同的驱动产生不同的连接

Statement接口，发送sql语句

ResultSet接口（结果集），是用来接收select语句返回的查询结果的。其实质类似于集合。

下午:

三.JDBC应用步骤

1，注册加载一个driver驱动

2，创建数据库连接（Connection）

3，创建一个Statement（发送sql）

4，执行sql语句

5，处理sql结果（select语句）

6，关闭Statement

7，关闭连接Connection。

注意：6，7两个步骤势必须要做的，因为这些资源是不会自动释放的，必须要自己关闭

访问Oracle的数据库的驱动名字叫ojdbc14.jar,要使用这个驱动程序，要先将他加到环境变量CLASSPATH中。

注册加载驱动driver，也就是强制类加载

Class.forName(Driver包名.Driver类名)。

Driver d=new Driver类();//注意:这个方法不能用参数来构造

DriverManager.registerDriver(d);

Oracle的Driver的全名oracle.jdbc.driver.OracleDriver

mysql的Driver的全名com.mysql.jdbc.Driver

SQLServer的Driver的全名com.microsoft.jdbc.sqlserver.SQLServerDriver

创建连接

DriverManager.getConnection(String url,String username,String password);

Connection连接是通过DriverManager的静态方法getConnection(.....)来得到的，这个方法的实质是把参数传到实际的Driver中的connect()方法中来获得数据库连接的。

Oracle的URL值是由连接数据库的协议和数据库的IP地址及端口号还有要连接的数据库的库名（DatebaseName）

Oracle URL的格式

jdbc:oracle:thin:（协议）@XXX.XXX.X.XXX:XXXX（IP地址及端口号）:XXXXXXX（所使用的库名）

例：jdbc:oracle:thin:@192.168.0.20:1521:tarenadb

MySql URL的写法

例： jdbc:mysql://localhost:3306/tarena

SQLServer URL的写法

例：jdbc:microsoft:sqlserver://localhost:1433/test

java -Djdbc.drivers=驱动的完整类名

使用虚拟机参数,加载驱动 -D表示为虚拟机参数赋值

java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver:com.mysql.jdbc.Driver

四.JDBC基本方法

DriverManager:如果有多个驱动可用的话,DriverManager会选择其中一个.

Driver:可以选择固定的驱动

Driver driver = new oracle.jdbc.driver.OracleDriver();

String user = "sd0613";

String password = "sd0613";

Properties prop = new Properties();

prop.setProperty("user",user);

prop.setProperty("password",password);

driver.connect(url,properties);

executeQuery(sqlString);//返回结果集

executeUpdate(sqlString);//返回值为该次操作影响的记录条数,create table返回0

execute(sqlString);

//适用于不知道具体的操作是什么,返回值是boolean类型的

//如果返回值是true,代表执行查询操作;否则代表执行更新操作.

ResultSet

next()方法:

1.判断是否存在下一条记录

2.将游标移向下一条记录

getXXX(字段名或字段序号)//注意:字段序号从1开始

关闭问题:

使用Connection对象获得一个Statement，Statement中的executeQuery(String sql) 方法可以使用select语句查询，并且返回一个结果集 ResultSet通过遍历这个结果集，可以获得select语句的查询结果，ResultSet的next()方法会操作一个游标从第一条记录的前边开始读取，直到最后一条记录。executeUpdate(String sql) 方法用于执行DDL和DML语句，可以update，delete操作。

注意：要按先ResultSet结果集，后Statement，最后Connection的顺序关闭资源，因为Statement和ResultSet是需要连接时才可以使用的，所以在使用结束之后有可能其他的Statement还需要连接，所以不能先关闭Connection。

1.回忆下昨天的一些JDBC的配置
(1) 驱动：
  ojdbc14.jar (Oracle)
  mysql-connector-java-3.1.11-bin.jar(MySql)
(2) 实现了Driver接口的驱动类(程序中要加载的类)：
  jdbc.oracle.driver.OracleDriver (Oracle)
  com.mysql.jdbc.Driver (MySql)
(3)连接数据库的URL
  jdbc:oracle:thin:@192.168.0.24:1521:tarena   (Oracle)
  jdbc:mysql://192.168.0.24:3306/test    (MySql)

2.PreparedStatement概述
SQL语句传到数据库后，数据库会先对其编译再执行。在使用Statement时，如果要执行一组类似的SQL操作时，这样做效率很低，而且把不同类型的数据直接写在SQL语句中是比较麻烦的。这时应该用PreparedStatement来代替Statement，PreparedStatement 接口继承 Statement，并和他在两方面有所不同：
(1)PreparedStatement 实例包含已编译的 SQL 语句。这就是使语句先“准备好”。包含于 PreparedStatement对象中的SQL 语句可具有一个或多个 IN 参数。IN参数的值在 SQL 语句创建时未被指定。相反的，该语句为每个 IN 参数保留一个问号（“?”）作为占位符。每个问号的值必须在该语句执行之前，通过适当的setXXX 方法来提供。
(2)由于 PreparedStatement 对象已预编译过，所以其执行速度要快于 Statement 对象。因此，多次执行的 SQL 语句经常创建为 PreparedStatement 对象，以提高效率。

作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。另外它还添加了一整套方法，用于设置发送给数据库以取代 IN 参数占位符的值。同时，三种方法 execute()、 executeQuery() 和 executeUpdate() 已被更改以使之不再需要参数。这些方法的 Statement 形式（接受 SQL 语句参数的形式）不应该用于 PreparedStatement 对象。

3.创建 PreparedStatement 对象
以下的代码段（其中 con 是 Connection 对象）创建包含带两个 IN 参数占位符的 SQL 语句的 PreparedStatement 对象：
PreparedStatement pstmt = con.prepareStatement("UPDATE table4 SET m = ? WHERE x = ?");
pstmt 对象包含语句 "UPDATE table4 SET m = ? WHERE x = ?"，它已发送给DBMS，并编译好为执行作好了准备。

4.传递 IN 参数
在执行 PreparedStatement 对象之前，必须设置每个 ? 参数的值。这可通过调用 setXXX 方法来完成，其中 XXX 是与该参数相应的类型。例如，如果参数具有Java 类型 long，则使用的方法就是 setLong。setXXX 方法的第一个参数是要设置的参数的序数位置(从1开始)，第二个参数是设置给该参数的值。例如，以下代码将第一个参数设为 123456789，第二个参数设为 100000000：
pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);

5.ResultSetMetaData
元数据是用来描述数据的数据，ResultSetMetaData就是来描述结果集的列的类型和属性信息，比如可以通过它得到结果集的列数，列名等。具体可在API中查阅java.sql.ResultSetMetaData。
ResultSetMetaData对象可以通过ResultSet对象的getMetaData()来得到。
ResultSetMetaData对象有以下三个方法比较常用：
getColumnCount()：获得实际列数
getColumnName(int colnum)：获得指定列的列名
getColumnType(int colnum)：获得指定列的数据类型(Types里面的类型,存放的是整数)

6.JDBC是持久层的技术，是JAVA连接数据库目前最通用的手段。其他的持久层技术，比如接下来我们要学的Hibernate，底层也是由JDBC实现的。持久层是与业务无关的，具体的业务由业务层完成，当业务层需要和数据库进行交互时，就需要通过持久层来操作。

7.BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
由于System.in是字节流，我们需要把他转成字符流。并用BufferedReader包装后方便我们的操作。

8.为了区分表中不同的数据，我们要给放入表中持久化的每个对象都加上一个唯一的标识，这就是ID，ID是与业务无关的。ID的生成方法有很多，在Oracle数据库中我们一半利用Sequence来生成。

9.读取配置文件时，我们采用Properties对象。它是HashTable的子类，它有个load(InputStream inStream) 的方法可以直接从输入流中读取属性列表(键值对)。getProperty(String key) 方法用指定的键在此属性列表中搜索值。

1.Registering a driver
2.Establishing a connection to the datebase
3.Creating a statement
4.Executing a SQL
5.Processing the results
6.Closing down JDBC objects

JDBC第三天
上午:
一.事务（Transaction）
原子操作:不可再分的操作,一个操作不能再分成比它更细小的操作.
事务是针对原子操作的，要求原子操作不可再分，并且必须同时成功同时失败。
事务就是把一些非原子操作,变成原子操作,由应用服务器来提出要求,由数据库服务器来执行操作.

在JDBC中默认是自动提交的,如果要想使用事务,需要按以下步骤执行:
1.要调用con.setAutoCommite(false)方法(打开事务边界)，把自动提交（commit）置为false。
2.进行正常的数据库操作
3.如果操作成功了可以选择con.commit(),或者操作失败时选择con.roolback()------
（回滚：数据恢复到之前的情况）

注意:打开事务就要关闭自动提交,当不需要再使用事务的时候调用
setAutoCommite(true).

事务性资源（监控完整性）

二.事务并发产生的问题
三种并发产生的后果:
1，脏读:一个事务读取到了另外一个事务没有提交的数据。（Dirty Read）
2，重复读:一个事务读取到了另外一个事务提交的数据。它是要保持在同一时间点上读取到的数据相同，希望在一段时间内的数据是不变的。
3，幻读:一个事务读取到了另外一个事务提交的数据。用同样的操作读取两次,得到的记录数不相同。

三.事务隔离级别
五种控制级别:
TRANSACTION_NONE不使用事务。
TRANSACTION_READ_UNCOMMITTED 允许脏读。
TRANSACTION_READ_COMMITTED防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别----------------------
TRANSACTION_REPEATABLE_READ可以防止脏读和不可重复读，
TRANSACTION_SERIALIZABLE可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection类中定义的静态常量，使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

下午:
四.JDBC2.0新特性
1.可滚动特性和可更新特性
JDBC1.0中是指游标的移动的方向和方式是单向,单步(相对)移动,功能比较简单.
JDBC2.0中游标可以双向,相对或者绝对移动.
可滚动结果集:这种结果集不但可以双向滚动，相对定位，绝对定位，并且还可以修改数据信息。

1)滚动特性
定位函数:aaa
boolean absolute(int row)，定位到指定的记录位置。定位成功返回true，不成功返回false。
void afterLast() ，把游标移动到最后一条记录的后面(逻辑位置)。一定会有的
void beforeFirst() ，把游标移动到第一条记录的前面(逻辑位置)。
//由于第一条记录的前面和最后一条记录的后面这两个位置肯定存在,所以无需判断是否存在,返回值设为void.

boolean first()，把游标定位到第一条记录，相对定位；
boolean last()，把游标定位到最后一条记录也是相对的概念。

//当结果集为空的时候,这两个方法会返回false.
boolean next()，此方法是使游标向下一条记录移动。
boolean previous() ，此方法可以使游标向上一条记录移动，前提是前面还有记录。

boolean relative(int rows) ，相对定位方法，参数值可正可负，参数为正，游标从当前位置向后移动指定值条记录，参数为负，游标从当前位置向前移动指定值条记录。

判断函数:
ifBeforeFirst()判断是否在在第一条记录之前.
ifAfterLast()判断是否在在最后一条记录之后.
ifFirst()判断是否为第一条记录.
ifLast()判断是否为最后一条记录.

要使用可滚动结果集时，需要一次设置更新特性与滚动特性,不能分开.

1.更新特性常量:
CONCUR_READ_ONLY 只读结果集 (默认的)
CONCUR_UPDATABLE 可更新结果集

2.滚动特性常量:
TYPE_FORWARD_ONLY ，该常量表示指针只能向前移动的 ResultSet 对象的类型。(默认)
双向滚动：
不敏感：TYPE_SCROLL_INSENSITIVE ，该常量指示可滚动但通常不受其他更改影响的 ResultSet 对象的类型。
敏感的：TYPE_SCROLL_SENSITIVE ，该常量指示可滚动并且通常受其他更改影响的 ResultSet 对象的类型。
//敏感:数据库改变,结果集改变.
语法:
Statement st=null;
st=con.createStatement(ReusltSet.TYPE_SCROLL_INSENSITIVE,ResuleSet.CONCUR_UPDATABLE)
在创建Statement的时候就要指定这两个参数,使用Statement,第一个参数代表滚动特性常量,第二个代表更新特性常量

----------------------------------------

2)可更新特性
a.moveToInsertRow();记录当前游标位置,将游标移到和结果集结构类似的缓冲区;
b.使用updateXxx(int column,columnType value)方法来更新指定列数据;
c.使用insertRow() 方法插入记录，加信结果集，更新
d.将游标指回原位，moveToCurrentRow() 。

2，3步，可循环
-----------------------------------------------------
能否使用JDBC2.0 ResultSet的新特性，要看使用的数据库驱动是否支持.
还有只能用于单表且表中有主键字段（可能会是联合主键），不能够有表连接，会取
可更新操作必须满足以下条件:
a.查询只能引用一张表.
b.不能包含任何连接操作.
c.必须把完整的主键查到结果集里面;
d.保证所有字段为非空字段并且没有默认值。

五.数据库元数据:
DatabaseMetaData dbmd = con.getMetaData();//得到数据库元数据
dbmd.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);//判断是否支持可更新操作

六.批量更新
优势:
1.节省传递时间
2.并发处理

PreparedStatement:
1.addBatch() 将一组参数添加到 PreparedStatement对象内部
2.executeBatch() 将一批参数提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。

Statement:
addBatch(String sql)方法会在批处理缓存中加入一条sql语句
executeBatch()执行批处理缓存中的所有sql语句。

注意：PreparedStatement中使用批量更新时，要先设置好参数后再使用addBatch()方法加入缓存。
批量更新中只能使用更新或插入语句

//
Statement stm=con.createStatement(int resultSetType,int resultSetConcurrency);创建的时候就要指明要什么样的结果集。
先可滚，后可更新

boolean absolute (int row)绝对定位，

afterLast()定位到最后一条记录的后面

posted @ 2007-03-21 16:14 sunny| 编辑收藏

oracle 3days

*********************************
** oracle 学习笔记第一天      **
** author Ice Xu   (XuBin)    **
** date 2006-10-30           **
***********************************
初始化表的位置：
cd $ORACLE_HOME/rdbms   cd demo     summit2.sql
这个角本可以初始化练习用的表
set LANG = AMERICAN_AMERICA.US7ASCII
*********************************
我们目前使用的是oralce 9i   9201 版本

恢复练习表命令：
sqlplus openlab/open123 @summit2.sql

登陆oracle的命令：
sqlplus 用户名/密码

show   user        显示当前登陆的身份.
set    pause on
set    pause off   分页显示.

oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在

edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行 c /旧串/新串

执行出错时，利用错误号来查错误:
!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错

误的具体信息)

想在sql中执行unix命令时，把所有的命令前加一个!就可以，或者host( 用

于从sql从切换至unix环境中去)

/*** 初次使用时注意 ****
运行角本时的命令：
先切换到unix环境下，cd $oracle_home cd sqlplus cd demo 下面有两

个角本建表语句。
@demobld.sql
sqlplus nanjing/nanjing @demobid.sql 直接运行角本，后面跟当前目录或

者是绝对路径

保存刚才的sql语句： save 命令第二次保存时要替换之前的角本

save 文件名 replace
把刚才保的sql重新放入 buffer中

spool 文件名
此命令会把所有的操作存在某个文件中去
spool off

练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;

给列起别名的命令：
利用关键字 as 或者用空格 "别名" 双引号内大小写敏感保持引号内容

原样输出，如果不加双引号时，默认为大写

拼接字段：
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接

一个空格)

查看当前用户所有的表：
练习2：(常用于批量更改数据)
set echo off
spool selecttab.sql;
select 'select * from ' || table_name ||' ; ' "table name " from

user_tables;
spool off;
set head off(去除第一行)
set feed off(去除最后一行)
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) "

year salary " from s_emp;
nvl函数专用于处理空值的影响.

*******************************************************************

***************************************************************

下午：
column 定义格式化输出
column last_name Heading format a15;
column last_name;
column salary justify left format $99,999.00 ( 定义工资的显示形式

)

$ echo $LANG
zh_CN.hp15CN
$ echo $NLS_LANG
simplified chinese_china.zhs16cgbk

ORDER BY 排序升序和降序 ASC 升序(默认) DESC 降序
select * from s_emp order by dept_id , salary desc 部门号升序，工

资降序
关键字distinct也会触发排序操作。

过滤操作： where 子句
select * from s_emp where dept_id=42; 查看部门号为42的所有员工
select * from s_emp where salary>1000 查看工资高于1000的所有员工
select salary from s_emp where first_name='Geroge' 找出名字为

Geroge的员工的工资数
select table_name from user_tables where table_name='S_EMP'; 查

某个具体表名时，表名的字符串必须要为大写

日期的默认的格式 DD-MON-RR(天-月-年)
BETWEEN AND 在什么之间 NOT BETWEEN AND

注意区间：[ ]是一个闭区间
IN( LIST) 在某个集合中 NOT IN (list)

空值会有影响 (等于list其中任何一个就行，为提高效率常把比例

高的放在前面)
LIKE 模糊配置 NOT LIKE

通配比较
IS NULL 是空
AND
OR
NOT

练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义,

并不是一定要用\，escape后面定义是什么字符为转义字符，那就用哪个字符
select table_name from user_tables where table_name like 'S\_%'

escape '\';

当有多个条件时，要用逻辑运算符：AND OR
写对where语句:正确的数据类型判断、逻辑运算符

sql函数的作用：
sql函数的分类：单行函数、多行函数
单行函数： (dual 哑表 )
字符函数：
lower 转小写 select lower('SQLPLUS') from dual;-->

对纯字符串处理的时候
upper      转大写          select upper('sqlplus') from dual;
initcap    首字符大写      select initcap('tarena') from dual;
concat     连接字符串      select concat(first_name , last_name)

from s_emp;等效于||
substr 求子串 select substr('tarenasd0603' ,1,6) from

dual; (取前六个字符) select substr('tarenasd0603',-2) from dual;

(取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数两个参数的类型要匹配，统一的，表示：如果有，

则返回前面的参数，如果没有就返回后面的参数
eg:select first_name,salary from s_emp where lower(first_name)

='george';
select first_name , substr(first_name , -2 ) from s_emp; (查出

s_emp表中所有用户名字的最后两个字符)
默认的是从左向右，如果是-2则表示从右向左数
练习5： select first_name , salary from s_emp where lower

(first_name)='george';

数值函数：
round 函数(四舍五入) select round(45.935, 2) from dual; 不带参

数时默认为0位小数
trunc 函数(截取，不管后面的数字) select trunc(45.995, 1) from

dual;
日期函数：DD-MON-RR ,默认不显示世纪、时、分、秒       日期格式敏感
世纪、年、月、日
sysdate 返回当前系统时间       select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意

单位是以天为单位，也可以得到多少小时、多少分钟之后的时间
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select round(last_day(sysdate),'month') from dual;
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个

FRIDAY
last_day 求月的最后一天

round 函数: select round(sysdate, 'MONTH') from dual; 参

数可以为： MONTH YEAR(看上半年还是下半年)
select trunc(last_day(sysdate)+1) from dual;
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
关于日期的两种形式：

转换函数：
to_char显示日期：
从数字转化为char to_char(date,'格式')
从日期转化为char to_char(date, 'fmt' )

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

select to_char(sysdate, 'fmyyyy mm

dd hh24:mi:ss') from dual;去掉前导名
select to_char(sysdate ,'YEAR MONTH

dy
eg:查出三月分入职的员工：select first_name,start_date from s_emp

where to_char(start_date,'mm')='03';

to_date表达日期：
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ')

from dual;
select round(to_date('10-OCT-06'

,'dd-mon-RR') ) from   dual;
to_number
      字符转数字
                            select to_number('10') from dual ;

day2

where 条件一定是根据某个字段来进行过滤操作.

多表连接操作：
两表没有任何关联时会产生迪卡尔机：
select   first_name , name from    s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select   first_name ,   name from s_emp e, s_dept d where e.dept_id=d.id;同时起了别名
select   first_name ,   name from s_emp e, s_dept d where e.dept_id=d.id and e.first_name='George';具体到哪个人所在的部门

练习二：每个员工所在的部门和部门所在的地区
select first_name , name   from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
eg:select first_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
等值连接：
练习三:找出每个员工和每个员工的工资级别
select    a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select    a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接：当一个表的插入行之间有了关系时就发生了(又名：内连接)
select   first_name   , manager_id   from s_emp;
查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name   from s_emp e , s_emp m where   e.manager_id=m.id;
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name   from s_emp e , s_emp m where   e.manager_id=m.id(+);
+号放在哪边就表示在哪边补空，来跟对方来匹配，使得数据一个都不会漏掉，这个例子中的领导有可能会没有(最高领导就再没有领导了，所以就
方法领导的那边)
标准写法：select e.deptno,d.name from emp e,dept d where e.deptno(+)=d.depton and e.depton is null;
查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式）
第一步：
select     e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：
select     e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno   and   e.deptno is null;

组函数（group function)：
group by 分组子句对分组后的子句进行过滤还可以用having 条件对分组后的条件进行过滤 where 是对记录进行过滤

avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ）求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(所有组函数会忽略空值 , avg   sum只能作用于数字类型）
求有提成员工的提成的平均值；
select    avg(nvl(commission_pct ,0 ) ) from s_emp;
有多少人有提成：
select count( commission_pct ) from    s_emp ;
count(*) 用于统计记录数：
select   sum(commission_pct)/ count(*)   from     s_emp;
员工分部在多少个不同的部门：count 默认为作all的动作
select   count(dept_id) from s_emp;
select   count(distinct dept_id) from   s_emp;
求各个部门的平均工资：group by 子句也会触发排序
select dept_id , avg(salary) aa    from    s_emp    group by   dept_id   order by aa ;
select dept_id , avg(salary) aa    from    s_emp    group by   dept_id    ;
注意：group by 子句后面跟有条件只能是查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段
select   region_id , count(*) from s_dept 此句会有错
select   max(region_id) , count(*) from       s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
求各个部门不同工种的平均工资：
select     dept_id , title, avg(salary) from s_emp   group   by dept_id , title ;
哪些部门的平均工资比2000高:
select    dept_id, avg(salary) aa from s_emp   group by (dept_id)    having      avg(salary)>2000;
除了42部门以外的部门的平均工资:
select   dept_id , avg(salary)   from s_emp group by (dept_id ) having    dept_id!=42;
select   dept_id , avg(salary)   from s_emp   where   dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
where       单行函数。
having      组函数。
求各个部门的平均工资:
// 这样统计不详细
select    max(d.name) , avg (s.salary)   from   s_emp s, s_dept d where    s.dept_id=d.id   group by    d.name;
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name) ***
select   max(d.name) , avg(e.salary) , max(r.name) from s_emp e,   s_dept d ,   s_region r where e.dept_id = d.id and d.region_id=r.id group by   d.id ;

下午：
关于子查询: Subqueries
找出所有员工中，工资最低的那个员工：( 利用子查询）
select    first_name, salary    from s_emp   where   salary = ( select min(salary) from s_emp)    ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;(利用子查询可以解决)
子查询运行的顺序：先运行子查询再运行主查询    子查询一般出现在运算符的右边
单值运算符：运算后面只能跟一个值
多值运算符：可以对两个以上的值进行操作
查询谁跟Smith干一样的活：
select   last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp   where title =( select   title from s_emp where last_name='Smith' )    and last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp   where title   in   ( select   title from s_emp where last_name='Smith' )    and last_name <> 'Smith' ;
使用子查询时应注意：单行子查询返回多个结果时会有错误    single-row subquery returns more than one value
查出哪些员工的工资比平均工资低：
select    * from s_emp where     salary   < ( select avg(salary) from   s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from   s_emp   group by dept_id;
第二步再查出哪个部门的工资是最低的:
select    dept_id, avg(salary) from s_emp   group by dept_id   having   avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;

哪个部门里没有员工：
select deptno from dept where deptno not in ( select deptno from emp );
哪些人是普通员工：（用子查询形式来做）
select * from s_emp where id not in ( select manager_id from s_emp);

E--R图实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计

第一范式：最简单的一种建方式，一张表只有一个主键。
第二范式：表的自连接存在原因，一张表，学生表中也有班级的信息。
第三范式：表连接存在的原因，两张表，其中一张表引用其它一张表。

约束：
为了保证数据的一致性，
primary key   (pk) 主键约束       不允许有重复和空值(唯一且非空）
foregin key   (fk)   外键约束       两张表parent table      child   table
unique key   (uk) 唯一可以为空
not   null
数据库设计时的注意：
索引：为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系：利用FK+PK实现，多的一方引用外键
一对一关系：可以利用FK+UK实现，
多对多关系：通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。

DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）

创建表语句：
create    table    [schema].表名   ( 字段名，   字段类型   约束条件);                    schema   默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则：首字母为字母，不得超过30个字符
char(size)                定长不管是否达到最大宽度，都会点最大的宽度。
varchar2(size)         可变长   按实际的字节占用空间
number            所有的数字类型都称为number
number(n, m ) n------n位宽度   m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位    values(0.0099) 这样可以   values(0.01)这样出错
LONG    2GB   大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB    大对象形式存放(在表里只存一个指针)
BLOB     存二进制大对象(声音，图像之类）

default 作用演示：
create table test(c1 number default 10, c2 number);

约束的演示：
主键约束的定义：
create table   test(c   number primary key );     列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table   test( c1 number constraints   pkc1 primary key );   此约束有名字: pkc1
create table   test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键

外键约束的定义：(先定义父表，再定义子表）
carete   table     parent(c1 number primary key );
create   table    child (c number primary key ,   c2 number references parent(c1));
或表级约束定义:
create   table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

on delete   cascade (及联删除，删除父表时子表也跟着删除）
on delete   set   null (及联删除父表时子表中引用的字段为null）

day3

不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束      表级约束

非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点

primary key约束：
主键约束的定义：
第一种定义形式：
create table   test(c   number primary key );     列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table   test( c1 number constraints   pkc1 primary key );   此约束有名字: pkc1
create table   test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键

foregin key   (fk)   外键约束:
(先定义父表，再定义子表）
create   table     parent(c1 number primary key );
create   table    child (c number primary key ,   c2 number references parent(c1));
或表级约束定义:
create   table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .

怎么创建一个角本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus nanjing/nanjing @filename.sql

创建表的语法：
create    table    表名 (   字段名    字段类型     约束类型（可选））；
利用已知表建一张新表：注会把非空约束带过来，其它约束要自己添加
create table s_emp_42    as select   * from   s_emp     where   dept_id = 42;
只取要表结构，不想要表中数据的建表方式：
create table s_emp_copy    as   select *    from s_emp   where   1=2;
(这是一个小技巧,在JDBC的学习中会用到 where 1=1 的形式,注意体会）

查看一张表的约束：( 查数据字典示图)
desc user_constraints;(这个数据字典中会查到相应的信息）
select    constraint_name, constraint_type    from   user_constraints where   table_name='S_EMP';
P   pk
R   fk
C   check
U    UK
V    这种只定义在示图中(with check option 相当于组示图加了一个约束）
O    也是出现在示图中
非空约束和CHECK都是用C来表示

查看字段约束的方法：
desc    user_cons_columns;
select   column_name, position from    user_cons_columns    where   constraint_name='S_EMP_ID_PK' ;
position 的含义：联合主键，约束名一样。
user_constraints    user_cons_columns   两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。

select constraint_name , r_constraint_name from user_constraints where constraint_type='R' and table_name='S_EMP' ;
数据库建立时，数据字典就会建好。
user_constraints; 自己拥有的
all_constraints; 你自己拥有的加上你可以访问的
dba_constraints 所有的

查看当前数据库数据字典的字典（这个示图很重要）
desc dict;
select table_name form dict where table_name like '%cons%;

示图：
user_objects; user_tables;
select distinct object_type from user_objects;

介绍事务的概念：
commit 提交，此时说明前面所有语句都成功执行
rollback　回退操作，此时会恢复至上一次提交时的状态。
savepoint 设置保存点

注意 insert into 后面可以跟子查询
insert into s_emp_42 select * from s_emp where dept_id =42;

UPDATE 修改字段值：
update s_emp set dept_id =10 where id =2 ;
update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值.
注意：如有外键引用时常会出现外键引用值没有找到等错误？

delete 删除记录命令语法：
delete from   s_emp where dept_id=42;
delete form   s_emp ;      没有where条件时说明删除表中所有的值
注意：如有外键引用时，删除一张表时常会出现不能删除的情况，
原因一   是因为此时正在有人操作表中记录
原因二   此表有其他的表引用，没能设及联删除：
delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。
truncate table 表名 (删除表中记录时释放表空间)

DML 语句：
表级共享锁：对于操作一张表中的不同记录时，互不影响
行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作
wait() 等到行级锁被释放，才进行数据操作
drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表

alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add   constraint 　约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter tbale   father   drop primary key    cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint   约束名;
(怎样取一个约束名：1、人为的违反约束规定根据错误信息获取!
                                 2、查询示图获取约束名!)

alter table 表名 disable from primary key ; (相当于把一个表的主键禁用)
alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）

*******************************************************************

增加字段：
alter table 　　表名　　　add(字段字字段类型)
删除字段：
alter table 　　表名     drop(字段)
alter tbale         表名    drop    column   字段 ; (8i 以后才支持）
给列改名:920才支持
alter table   表名   rename   column   旧字段名    to     新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)
alter table   　表名     modify( 字段，类型)
更改表中的字段：
update 表名   set     字段     ＝      值     where       条件
更改表名
rename       旧表名           to 　　新表名           ；
删除表：
trucate   table    表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表）

关于oralce中产生序列(sequence):
create sequence   序列名alter system flush   shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值,    start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看   sequence 示图：
desc    user_sequences ;
select   sequence_name , cache_size , last_number from user_sequences   where   sequence_name like 's_';
select 序列名.currval from   dual    查看当前的序列数
select 序列名.nextval from   dual    查看下一个序列数，它会自动给当前的序列加１
为列：nextval          currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
alter system flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名修改项；
删除序列sequence
drop sequence 序列名;

创建示图： creating      views（属于了解知识）
desc user_views;
select   text   from user_views    where   view_name='TEST1_V1' ;
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV   物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，主要应用在数据仓库中用要用于聚合表）
使用示图的好处：控制数据访问权限.
如何创建一个示图：
create   or replace   views   test_vi    as       select       *   from    test1   where c1=1;
此时往表test1（base   table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
关于rownum：
rownum 有个特点要么等于1 要么小于某个值，不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：
select   first_name , rnum    from   ( select   rownum   rnum    , first_name   from    s_emp where rownum <=10 )     where rnum between 5 and 10 ;

分面显示：
SELECT * FROM (SELECT a.*, rownum r FROM S_EMP a WHERE r between 5 AND 10 )；

练习：哪些员工的工资比本部门的平均工资高？
select   first_name , salary   , avgsal     from s_emp   e , ( select   dept_id , avg (salary )   avgsal from   s_emp group by dept_id ) a   where   e.dept_id =a.dept_id and e.salary > a.avgsal;
在示图上加一个 with check   option 就相当于给示图加上了约束
create    view    test_v as select   * from   test where c =1 with check option ;
同义词:相当于别名的作用(***只需了解***）系统自建的同义词：    user_tables
create synonym    asd_s_emp   for    asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public     synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym    同义词名称

创建索引： Creating    indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full   table    scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例）
查看表的rowid：
select     rowid , first_name    from s_emp;
rowid 定义的信息有： object   block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的，
创建一个索引：
create index     索引名     on   表名 ( 字段名）;
create   insex testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.

替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
set    verify on
set    verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select   id ,last_name ,salary   from s_emp where title='&job_title';
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define     p_dname='abc';

分页的实现语句：(可以正常运行)
select   *   from ( select   rownum   rnum , a.*   from   (select * from s_emp) a )     where rnum between 5 and 10 ;

-------------------------------------------------------------------------------------------------------------------------
1、关于约束的知识：
primary key约束：
主键约束的定义：
第一种定义形式：
create table   test(c   number primary key );     列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table   test( c1 number constraints   pkc1 primary key );   此约束有名字: pkc1
create table   test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键

foregin key   (fk)   外键约束:
(先定义父表，再定义子表）
carete   table     parent(c1 number primary key );
create   table    child (c number primary key ,   c2 number references parent(c1));
或表级约束定义:
create   table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

check 约束：
create   table   test(c1   number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
****************************************************************************
2、关于针对表操作的语法知识：
创建表：
   create    table 表名   (    字段名1    类型   约束条件，   字段名2    类型    约束条件）;

插入数据命令：
方式一：(指定字段名插入数据）
insert into   表名 ( 字段名 )    values ( 数据）；
方式二：
insert into 表名   values(数据1，数据2）；

修改数据：
update   table   表名 set ( 字段名   数据，字段名数据）；
****************************************************************************
3、关于alter table 命令知识:
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add   constraint 　约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter tbale   father   drop primary key    cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint   约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)
alter table   表名 disable    from   primary key ; (相当于把一个表的主键禁用)
alter table   表名 enable    primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
增加字段：
alter table 　　表名　　　add(字段字，字段类型)
删除字段：
alter table 　　表名     drop(字段)
alter tbale         表名    drop    column   字段 ; (8i 以后才支持）
给列改名:920才支持
alter table   表名   rename   column   旧字段名    to     新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)
alter table   　表名     modify( 字段，类型)
更改表中的字段：
update 表名   set     字段     ＝      值     where       条件
更改表名
rename       旧表名           to 　　新表名           ；
删除表：
trucate   table    表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表）
****************************************************************************
4、关于oralce中产生序列(sequence)
create sequence   序列名alter system flush   shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值,    start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看   sequence 示图：
desc    user_sequences ;
select   sequence_name , cache_size , last_number from user_sequences   where   sequence_name like 's_';
select 序列名.currval from   dual    查看当前的序列数
select 序列名.nextval from   dual    查看下一个序列数，它会自动给当前的序列加１
为列：nextval          currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
alter system flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名修改项；
删除序列sequence
drop sequence 序列名;
****************************************************************************
5、创建示图： creating      views（属于了解知识）
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV   物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，主要应用在数据仓库中用要用于聚合表）
使用示图的好处：控制数据访问权限.
如何创建一个示图：
create   or replace   views   test_vi    as       select       *   from    test1   where c1=1;
此时往表test1（base   table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
关于rownum：
rownum 有个特点要么等于1 要么小于某个值，不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：
select   first_name , rnum    from   ( select   rownum   rnum    , first_name   from   s_emp    where rownum <=10 )    where rnum between 5 and 10;
练习：哪些员工的工资比本部门的平均工资高？
select   first_name , salary   , avgsal     from s_emp   e , ( select   dept_id , avg (salary )   avgsal from   s_emp group by dept_id ) a   where   e.dept_id =a.dept_id and e.salary > a.avgsal;
关于同义词：
同义词:相当于别名的作用(***只需了解***）系统自建的同义词：    user_tables
create synonym    asd_s_emp   for    asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public     synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym    同义词名称

****************************************************************************
6、创建索引： Creating    indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full   table    scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例）
查看表的rowid：
select     rowid , first_name    from s_emp;
rowid 定义的信息有： object   block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的，
创建一个索引：
create index     索引名     on   表名 ( 字段名）;
create   insex testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.

posted @ 2007-03-20 12:57 sunny 阅读(751) | 评论 (0) | 编辑收藏

oracle

两表没有任何关联时会产生迪卡尔机：
select first_name , name from s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
　
练习二：每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;

非等值连接
练习三:查出每个员工和每个员工的工资级别)
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;

自连接：
select first_name , manager_id from s_emp;
练习四:查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;7fg
　
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);

练习五：查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式，用子查询也可以实现）
第一步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：(!L_=N
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;

练习六：查询员工有多少人有提成：
select count( commission_pct ) from s_emp ;
select sum(commission_pct)/ count(*) from s_emp;
　　
练习七：员工分部在多少个不同的部门：
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
　
练习八：求各个部门的平均工资：
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
//体会下句sql
select region_id , count(*) from s_dept ****此句会有错,请多体会********
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）

练习九：求各个部门不同工种的平均工资：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;

练习十：查询哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;

练习十一：除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）

练习十二：求各个部门的平均工资:
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name)
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and

d.region_id=r.id group by d.id ;

关于子查询: Subqueries
练习十三：找出所有员工中，工资最低的那个员工：( 利用子查询）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;

练习十四：查询谁跟Smith的工种一样：
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' )and

last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and

last_name <> 'Smith' ;
使用子查询时应注意：单行子查询返回多个结果时会有错误 single-row subquery returns more than one value(
练习十五：查出哪些员工的工资比平均工资低：
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from

s_emp group by dept_id ) ;
练习十六：哪个部门里没有员工(用子查询的方式来实现）：
select deptno from dept where deptno not in ( select deptno from emp );

posted @ 2007-03-20 12:48 sunny 阅读(425) | 评论 (0) | 编辑收藏

plsql命令大全

SQL*PLUS命令的使用大全

                               SQL*PLUS命令的使用大全

       Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为
sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。
   除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
   下面就介绍一下一些常用的sql*plus命令：

1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。

2. 对当前的输入进行编辑
SQL>edit

3. 重新运行上一次运行的sql语句
SQL>/

4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
   在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。

5. 关闭spool输出
SQL> SPOOL OFF
   只有关闭spool输出，才会在输出文件中看到输出的内容。

6．显示一个表的结构
SQL> desc table_name

7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
     DEPTNO DNAME                        LOC
---------- ---------------------------- ---------
         10 ACCOUNTING                   NEW YORK
sql>col LOC heading location
sql>select * from dept;
    DEPTNO DNAME                        location
--------- ---------------------------- -----------
        10 ACCOUNTING                   NEW YORK

2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department name           Salary
---------- ---------- ----------
         10 aaa                11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
            Employee
Department name           Salary
---------- ---------- ----------
         10 aaa                11
note: the col heading turn into two lines from one line.

3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
      EMPNO ENAME      JOB
---------- ----------     ---------
       7369 SMITH      CLERK
       7499 ALLEN      SALESMAN
7521 WARD       SALESMAN
Sql> col ename format a40
      EMPNO ENAME                                    JOB
----------   ----------------------------------------         ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
       7521 WARD                                    SALESMAN

4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
      EMPNO           ENAME                   JOB
----------   ----------------------------------------       ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
7521 WARD                                     SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边

5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
      EMPNO           ENAME
----------     ----------------------------------------
       7369 SMITH
       7499 ALLEN
7521 WARD

6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name        Salary    Commission
---------- ---------- --------- ----------
30          ALLEN        $1,600    300

7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text

8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
        COL1
--------------------
HOW ARE YOU?

SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A

9). 显示列的当前的显示属性值
SQL> COLUMN column_name

10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS

8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE SAL < 2500
ORDER BY DEPTNO;
DEPTNO      ENAME         SAL
---------- ----------- ---------
10           CLARK        2450
MILLER      1300
20            SMITH       800
ADAMS       1100

9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n

SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300

20 SMITH 800
ADAMS 1100

10. 显示对BREAK的设置
SQL> BREAK

11. 删除6、7的设置
SQL> CLEAR BREAKS

12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}

1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}

2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}

3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
   默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数

4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替

5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
   如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。

6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。

7).显示时，用text值代替NULL值
SQL> SET NULL text

8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题

9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF}
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。

10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL> SET WRA[P] {ON|OFF}
   当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。

11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL> SET TERM[OUT] {ON|OFF}
   在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。

12).将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF}

13)显示每个sql语句花费的执行时间
set TIMING {ON|OFF}

14．修改sql buffer中的当前行中，第一个出现的字符串
C[HANGE] /old_value/new_value
SQL> l
   1* select * from dept
SQL> c/dept/emp
   1* select * from emp

15．编辑sql buffer中的sql语句
EDI[T]

16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行
L[IST] [n]

17．在sql buffer的当前行下面加一行或多行
I[NPUT]

18．将指定的文本加到sql buffer的当前行后面
A[PPEND]
SQL> select deptno,
   2 dname
   3 from dept;
     DEPTNO DNAME
---------- --------------
         10 ACCOUNTING
         20 RESEARCH
         30 SALES
         40 OPERATIONS

SQL> L 2
   2* dname
SQL> a ,loc
   2* dname,loc
SQL> L
   1 select deptno,
   2 dname,loc
   3* from dept
SQL> /

     DEPTNO DNAME          LOC
---------- -------------- -------------
         10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

19．将sql buffer中的sql语句保存到一个文件中
SAVE file_name

20．将一个文件中的sql语句导入到sql buffer中
GET file_name

21．再次执行刚才已经执行的sql语句
RUN
or
/

22．执行一个存储过程
EXECUTE procedure_name

23．在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@db_alias

24．设置每个报表的顶部标题
TTITLE

25．设置每个报表的尾部标题
BTITLE

26．写一个注释
REMARK [text]

27．将指定的信息或一个空行输出到屏幕上
PROMPT [text]

28．将执行的过程暂停，等待用户响应后继续执行
PAUSE [text]

Sql>PAUSE Adjust paper and press RETURN to continue.

29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query

sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST
create emp_temp
USING SELECT * FROM EMP

30．不退出sql*plus，在sql*plus中执行一个操作系统命令：
HOST

Sql> host hostname
该命令在windows下可能被支持。

31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：
!

sql>!
$hostname
$exit
sql>

该命令在windows下不被支持。

32．显示sql*plus命令的帮助
HELP
如何安装帮助文件：
Sql>@ ?\sqlplus\admin\help\hlpbld.sql ?\sqlplus\admin\help\helpus.sql
Sql>help index

33．显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER

1) . 显示当前环境变量的值：
Show all

2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。

3) . 显示初始化参数的值：
show PARAMETERS [parameter_name]

4) . 显示数据库的版本：
show REL[EASE]

5) . 显示SGA的大小
show SGA

6). 显示当前的用户名
show user