ourday

JPA是什么

定义 Java Persistence API JPA通过JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中。 起源 Sun引入新的JPA ORM规范出于两个原因：其一，简化现有Java EE和Java SE应用的对象持久化的开发工作；其二，Sun希望整合对ORM技术，实现天下归一。 JPA由EJB 3.0软件专家组开发，作为JSR-220实现的一部分。但它不囿于EJB 3.0，你可以在Web应用、甚至桌面应用中使用。JPA的宗旨是为POJO提供持久化标准规范，由此可见，经过这几年的实践探索，能够脱离容器独立运 行，方便开发和测试的理念已经深入人心了。目前Hibernate 3.2、TopLink 10.1.3以及OpenJPA都提供了JPA的实现。 JPA的总体思想和现有Hibernate、TopLink，JDO等ORM框架大体一致。总的来说，JPA包括以下3方面的技术： ORM映射元数据 JPA支持XML和JDK 5.0注解两种元数据的形式，元数据描述对象和表之间的映射关系，框架据此将实体对象持久化到数据库表中； JPA 的API 用来操作实体对象，执行CRUD操作，框架在后台替我们完成所有的事情，开发者从繁琐的JDBC和SQL代码中解脱出来。 查询语言 这是持久化操作中很重要的一个方面，通过面向对象而非面向数据库的查询语言查询数据，避免程序的SQL语句紧密耦合。 JPA的优势 1 标准化 JPA 是 JCP 组织发布的 Java EE 标准之一，因此任何声称符合 JPA 标准的框架都遵循同样的架构，提供相同的访问 API，这保证了基于JPA开发的企业应用能够经过少量的修改就能够在不同的JPA框架下运行。 2 对容器级特性的支持 JPA 框架中支持大数据集、事务、并发等容器级事务，这使得 JPA 超越了简单持久化框架的局限，在企业应用发挥更大的作用。 3 简单易用，集成方便 JPA的主要目标之一就是提供更加简单的编程模型：在JPA框架下创建实体和创建Java 类一样简单，没有任何的约束和限制，只需要使用 javax.persistence.Entity进行注释；JPA的框架和接口也都非常简单，没有太多特别的规则和设计模式的要求，开发者可以很容易的 掌握。JPA基于非侵入式原则设计，因此可以很容易的和其它框架或者容器集成。 4 可媲美JDBC的查询能力 JPA的查询语言是面向对象而非面向数据库的，它以面向对象的自然语法构造查询语句，可以看成 是Hibernate HQL的等价物。JPA定义了独特的JPQL（Java Persistence Query Language），JPQL是EJB QL的一种扩展，它是针对实体的一种查询语言，操作对象是实体，而不是关系数据库的表，而且能够支持批量更新和修改、JOIN、GROUP BY、HAVING 等通常只有 SQL 才能够提供的高级查询特性，甚至还能够支持子查询。 5 支持面向对象的高级特性 JPA 中能够支持面向对象的高级特性，如类之间的继承、多态和类之间的复杂关系，这样的支持能够让开发者最大限度的使用面向对象的模型设计企业应用，而不需要自行处理这些特性在关系数据库的持久化。 JPA的供应商 JPA 的目标之一是制定一个可以由很多供应商实现的API，并且开发人员可以编码来实现该API，而不是使用私有供应商特有的API。因此开发人员只需使用供应 商特有的API来获得JPA规范没有解决但应用程序中需要的功能。尽可能地使用JPA API，但是当需要供应商公开但是规范中没有提供的功能时，则使用供应商特有的API。 1 Hibernate JPA是需要Provider来实现其功能的，Hibernate就是JPA Provider中很强的一个，目前来说应该无人能出其右。从功能上来说，JPA现在就是Hibernate功能的一个子集。Hibernate 从3.2开始，就开始兼容JPA。Hibernate3.2获得了Sun TCK的JPA(Java Persistence API) 兼容认证。 只要熟悉Hibernate或者其他ORM框架，在使用JPA时会发现其实非常容易上手。例如 实体对象的状态，在Hibernate有自由、持久、游离三种，JPA里有new，managed，detached，removed，明眼人一看就知 道，这些状态都是一一对应的。再如flush方法，都是对应的，而其他的再如说Query query = manager.createQuery(sql)，它在Hibernate里写法上是session，而在JPA中变成了manager，所以从 Hibernate到JPA的代价应该是非常小的 同样，JDO，也开始兼容JPA。在ORM的领域中，看来JPA已经是王道，规范就是规范。在各大厂商的支持下，JPA的使用开始变得广泛。 2 Spring Spring + Hibernate 常常被称为 Java Web 应用人气最旺的框架组合。而在 JCP 通过的 Web Beans JSR ，却欲将JSF + EJB + JPA 、来自 JBoss Seam（Spring 除外）的一些组件和EJB 3（目前能够提供有基本拦截和依赖注入功能的简化 Session Bean 框架）的一个 Web 组合进行标准化。如今的 Spring 2.0 为 JPA 提供了完整的 EJB 容器契约，允许 JPA在任何环境内可以在 Spring 管理的服务层使用（包括 Spring 的所有 AOP 和 DI 增强）。同时，关于下一个Web应用组合会是 EJB、Spring + Hibernate 还是 Spring + JPA 的论战，早已充斥于耳。 在Spring 2.0.1中，正式提供对JPA的支持，这也促成了JPA的发展，要知道JPA的好处在于可以分离于容器运行，变得更加的简洁。 3 OpenJPA OpenJPA 是 Apache 组织提供的开源项目，它实现了 EJB 3.0 中的 JPA 标准，为开发者提供功能强大、使用简单的持久化数据管理框架。OpenJPA 封装了和关系型数据库交互的操作，让开发者把注意力集中在编写业务逻辑上。OpenJPA 可以作为独立的持久层框架发挥作用，也可以轻松的与其它 Java EE 应用框架或者符合 EJB 3.0 标准的容器集成。 4 其它 目前支持的实现包括Toplink、Hibernate Entitymanager等。TopLink以前需要收费，如今开源了。OpenJPA虽然免费，但功能、性能、普及性等方面更加需要加大力度。 对于EJB来说，实体Bean一直是被批评的对象，由于其太复杂和庞大。JPA的出现，很大程度的分离了复杂性。这让EJB的推广也变得容易。 总而言之，JPA规范主要关注的仅是API的行为方面，而由各种实现完成大多数性能有关的调优。尽管如此，所有可靠的实现都应该拥有某种数据缓存，以作为选择。但愿不久的将来，JPA能成为真正的标准。 小结 EJB 3.0和JPA 毫无疑问将是Java EE 5的主要卖点。在某些领域中，它们给Java社区带来了竞争优势，并使Java 在其他领域与竞争对手不分伯仲（因为，不可否认，目前某些领域尚不存在基于标准的方法）。 过去数年来，Spring Framework一直是EJB在企业领域的主要竞争对手。EJB3.0规范解决了很多促进Spring兴起的问题。随着它的出现，EJB3.0毫无疑问比Spring提供了更好的开发体验——最引人注目的优势是它不需要配置文件。 JPA提供一种标准的OR映射解决方案，该解决方案完全集成到EJB3。0兼容的容器中。JPA的前辈将会继续稳定发展，但是业务应用程序中的 raw 使用将可能会减少。实现 JPA 兼容的实体管理器似乎很可能是此类技术的发展方向。 Java EE系列规范的较大问题与JPA没有任何关系。Java EE 系列规范的问题涉及到 Web和EJB容器之间的集成。Spring在此领域仍然具有主要竞争优势。JBoss的Seam项目尝试使用自定义的方法来解决这一问题。Caucho Resin应用服务器试图扩展容器边界并支持在Web容器中使用@EJB注释。我们希望Java EE 5.1将解决层集成的问题，为我们提供一个全面而标准的依赖性注入方法。 在不久的将来，Sun可能会将JPA作为一个单独的JSR对待，同时JPA还可能作为Java SE的一部分。不过这些都不太重要，重要的是，我们现在已经可以在脱离容器的情况下、在Java SE应用中使用JPA了。 JPA已经作为一项对象持久化的标准，不但可以获得Java EE应用服务器的支持，还可以直接在Java SE中使用。开发者将无需在现有多种ORM框架中艰难地选择，按照Sun的预想，现有ORM框架头顶的光环将渐渐暗淡，不再具有以往的吸引力。 值得注意的是Java Persistence API并不是J2EE环境 专用，而是在java中的通用API。意味着我们可以在任何需要访问关系数据库的地方使用JPA， 甚至包括面应用。JPA也不要求一定在J2EE容器中才能运行，而是任何有JVM的环境都可以运用。 这就使得我们可以很容易的把JPA作为一个持久化组件自由的和各种容器／框架（EJB3容器, Spring等等）组合。    JPA最主要的是一个统一的persistence.xml配置文件，可以放到每一个jar的META-INF/目录下面，配置内容主要有 （以hibernate为例） 1、数据库连接的配置文件 2、数据库方言、连接URL、用户名、密码 3、ORM映射的列表（Class） 4、配置其它的Hibernate属性（其他的Provider属性） 5、cache 主要内容，详细内容请看hibernate的reference xml 代码 xml version="1.0" encoding="UTF-8"?>   <persistence>      <persistence-unit name="TestEntityManager" transaction-type="RESOURCE_LOCAL">         <provider>org.hibernate.ejb.HibernatePersistenceprovider>            <class>com.jl.sub1.mapping.Productclass>         <class>com.jl.sub1.mapping.ProductItemclass>                  <properties>             <property name="hibernate.dialect" value="org.hibernate.dialect.DB2Dialect" />             <property name="hibernate.connection.driver_class" value="com.ibm.db2.jcc.DB2Driver" />             <property name="hibernate.connection.password" value="db2admin" />             <property name="hibernate.connection.url" value="jdbc:db2://10.1.1.10:50000/dbjl" />             <property name="hibernate.connection.username" value="db2admin" />             <property name="hibernate.ejb.autodetection" value="class" />       properties>                   persistence-unit>   persistence>               <property name="kodo.ConnectionDriverName" value="..."/>   <property name="kodo.ConnectionURL" value="..."/>   <property name="kodo.ConnectionUserName" value="..."/>   <property name="kodo.ConnectionPassword" value="..."/>   <property name="kodo.Log" value="DefaultLevel=INFO, Runtime=DEBUG, Tool=INFO"/>                   spring配置 xml 代码 <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean">       <property name="entityManagerName"         value="TestEntityManager" />          <property name="jpaProperties">           <props></props>       </property>          </bean>   <!-- 此处的名字要和persistence.xml中定义的name一致-->   <!-- jpaProperties此属性必须要加，hibernate3.1.2的一个bug,否则会抛NullPointerException，springframework论坛告诉我的：）-->      <bean class=       "org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator"/>       <!--貌似这个部分所有spring配置文件还是仅允许一个，可以配置到公共的xml中-->          <!--引用上面建立的entityManagerFactory-->   <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">       <property name="entityManagerFactory“ ref="entityManagerFactory" />              </bean>      <bean id="transactionInterceptor"       class="org.springframework.transaction.interceptor.TransactionInterceptor">       <property name="transactionManager" ref="transactionManager" />       <property name="transactionAttributeSource">       <bean class=       "org.springframework.transaction.annotation.AnnotationTransactionAttributeSource" />       </property>   </bean>      <bean class="org.springframework.transaction.interceptor.TransactionAttributeSourceAdvisor">       <property name="transactionInterceptor" ref="transactionInterceptor" />   </bean>      <!--自定义DAO-->   <bean id="numberDao" class="dao.JpaNumberDAO">       <property name="entityManagerFactory“ ref="entityManagerFactory" />   </bean>   <!--DAO需继承自org.springframework.orm.jpa.support.JpaDaoSupport-->   <!--DAO中可以通过getJpaTemplate()的各种方法进行持久化操作-->     这样就好了，可以使用entitymanager，每个jar里面可以相互不用影响，而且这个也符合我们新过程改进推进的每个子系统用jar发布的原则，当然这里的还有作service层，对于子系统之间的交互，我觉得，原则上应该仅允许他们在service的层面上进行，在DAO之间不应该有相互的交叉，不过隐约觉 得还有很多东西要做，嗯，很晚了，肚子饿，明天再想，呵呵 哦，对了，Spring真是好东西，封装了这么多东西给我们用，真是期待更好的版本出来啊， 还有，在j2se环境里，也可以通过代码的方式取得JPA的api java 代码 EntityManagerFactory emf =   Peristence.createEntityManagerFactory(“TestEntityManager”) ;   //此处的名字要和persistence.xml中      //定义的name一致         EntityManager em = emf.createEntityManager();   ...//使用   em.close();   ...   emf.close();   EntityManagerFactory emf =   Peristence.createEntityManagerFactory(“TestEntityManager”) ;   EntityManager em = emf.createEntityManager();   ...//使用   em.close();   ...   emf.close();

使用注解元数据
基本注解：
例子：
@Entity(name = "T_TOPIC") ①
public class Topic implements Serializable ...{

@Id ② -1

@GeneratedValue(strategy = GenerationType.TABLE) ② -2

@Column(name = "TOPIC_ID") ② -3

private int topicId;

@Column(name = "TOPIC_TITLE", length = 100) ③
private String topicTitle;

@Column(name = "TOPIC_TIME") @Temporal(TemporalType.DATE) ④
private Date topicTime;

@Column(name = "TOPIC_VIEWS")

private int topicViews;

...

}



解释：
① Entity 标明该类 (Topic) 为一个实体类，它对应数据库中的表表名是 T_TOPIC ，这里也可以写成： @Entity

@Table(name = "T_TOPIC") 其作用都是一样的
② -1 Id 标明该属性对应数据表中的主键
② -2 GeneratedValue 通过 strategy 属性指明主键生成策略，默认情况下， JPA 自动选择一个最适合底层数据库的主键生成策略。在 javax.persistence.GenerationType 中定义了以下几种可供选择的策略：
1) IDENTITY ：表自增键字段， Oracle 不支持这种方式；
2) AUTO ： JPA 自动选择合适的策略，是默认选项；
3) SEQUENCE ：通过序列产生主键，通过 @SequenceGenerator 注解指定序列名， MySql 不支持这种方式；
4) TABLE ：通过表产生主键，框架借由表模拟序列产生主键，使用该策略可以使应用更易于数据库移植。
② -3 Column 标明这个属性是数据表中的一列，该列的名字是 TOPIC_ID

③ Column 的一个属性 length 指明的是该属性的允许的长度。 ( 个人认为设定该属性只是对于程序中操作该属性时增加了一验证过程，对数据库中该列原来的设置并没有影响，但是 length 属性指定的值必须不能大于数据库创建表时给该列限制的最大长度否则会出错 )

④ Temporal(TemporalType.DATE) ：如果属性是时间类型，因为数据表对时间类型有更严格的划分，所以必须指定具体时间类型。在 javax.persistence.TemporalType 枚举中定义了 3 种时间类型：
1) DATE ：等于 java.sql.Date

2) TIME ：等于 java.sql.Time

3) TIMESTAMP ：等于 java.sql.Timestamp



继承关系注解：
对继承关系进行注解，必须在 父类 中声明继承实体的映射策略。
例子：
@Entity(name = "T_TOPIC")

@Inheritance(strategy = InheritanceType.SINGLE_TABLE) ① @DiscriminatorColumn(name = "TOPIC_TYPE", discriminatorType =

DiscriminatorType.INTEGER, length = 1) ②
@DiscriminatorValue(value="1") ③
public class Topic implements Serializable ...{ … }



解释：
① Inheritance 通过 strategy 属性指明实体的继承策略。
在 javax.persistence.InheritanceType 定义了 3 种映射策略：
1) SINGLE_TABLE ：父子类都保存到同一个表中，通过字段值进行区分。
2) JOINED ：父子类相同的部分保存在同一个表中，不同的部分分开存放，通过表连接获取完整数据；
3) TABLE_PER_CLASS ：每一个类对应自己的表，一般不推荐采用这种方式。
② DiscriminatorColumn 如果继承策略采用第一种继承策略，则需要指明区分父子类的字段， DiscriminatorColumn 就是用来指明区分字段的注解。
③ DiscriminatorValue 同样的采用第一种继承策略通过字段区分父子类，则用这个注解给该实体的区分字段赋值在这里赋的值为 ”1”.



关联关系注解：
例子：
@Entity @DiscriminatorValue(value="2") ①
public class PollTopic extends Topic ...{ ②继承于 Topic 实体
private boolean multiple; ③
@Column(name = "MAX_CHOICES")

private int maxChoices; @OneToMany(mappedBy="pollTopic",cascade=CascadeType.ALL) ④
private Set options = new HashSet();

// 省略 get/setter 方法
}

解释 :

① 通过 @DiscriminatorValue 将区分字段 TOPIC_TYPE 的值为 2 。由于 PollTopic 实体继承于 Topic 实体，其它的元数据信息直接从 Topic 获得。
④ OneToMany 指定了一个一对多的关联关系， mappedBy 属性指定 “Many” 方类引用 “One” 方类 的属性名； cascade 属性指明了级联方式（如果这里不指定为 CascadeType.ALL 的话，那么有关联关系的两个对象在做保存和删除操作时要分别来进行） 建议 ：尽可能使用 cascade=CascadeType.ALL 来减少持久化操作的复杂性和代码量
注意 ： JPA 规范规定任何属性都默认映射到表中，所以虽然我们没有给③处的 multiple 属性提供注解信息，但 JPA 将按照 默认的规则对该字段进行映射：字段名和属性名相同，类型相同。如果我们不希望将某个属性持久化到数据表中，则可以通过 @Transient 注解显式指定：
@Transient

private boolean tempProp1;





@Entity(name="T_POLL_OPTION")

Public class PollOption implements Serializable ...{

@Id

@GeneratedValue(strategy = GenerationType.TABLE)

@Column(name = "OPTION_ID")

private int optionId;

@Column(name = "OPTION_ITEM")

private String optionItem;

@ManyToOne ①
@JoinColumn(name="TOPIC_ID", nullable=false) ②
private PollTopic pollTopic;

}



解释：
① ManyToOne 描述了多对一的关联关系，他是对该类引用的 ”One” 类 (PollTopic) 的属性（ pollTopic ）进行注解的。
② JoinColumn 指定关联 ”One”(PollTopic) 实体所对应表的 “ 外键 ” 。


Lob 字段的注解：
在 JPA 中 Lob 类型类型的持久化很简单，仅需要通过特殊的 Lob 注解就可以达到目的。
例子：


@Lob ① -1

@Basic(fetch = FetchType.EAGER) ① -2

@Column(name = "POST_TEXT", columnDefinition = "LONGTEXT NOT NULL") ① -3

private String postText;



@Lob

@Basic(fetch = FetchType. LAZY) ② -2

@Column(name = "POST_ATTACH", columnDefinition = "BLOB") ② -3

private byte[] postAttach;



解释：
① -1 JPA 通过 @Lob 将属性标注为 Lob 类型 ；
① -2 通过 @Basic 指定 Lob 类型数据的获取策略， FetchType.EAGER 表示非延迟 加载，而 FetchType. LAZY 表示延迟加载 ；
① -3 通过 @Column 的 columnDefinition 属性指定数据表对应的 Lob 字段类型。






使用 XML 元数据


除了使用注解提供元数据信息外， JPA 也允许我们通过 XML 提供元数据信息。按照 JPA 的规范， 如果你提供了 XML 元数据描述信息，它将覆盖实体类中的注解元数据信息 。 XML 元数据信息以 orm.xml 命名，放置在类路径的 META-INF 目录下。


<?xml version="1.0" encoding="UTF-8"?>

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"

version="1.0">

①实体对象所在的包
<package>com.baobaotao.domain</package>

<entity class="Topic">

② Topic 实体配置

<table name="T_TOPIC" />

<attributes>

<id name="topicId">

<column name="TOPIC_ID"/>

<generated-value strategy="TABLE" />

</id>

<basic name="topicTitle">

<column name="TOPIC_TITLE" length="30" />

</basic>

<basic name="topicTime">

<column name="TOPIC_TIME" />

<temporal>DATE</temporal>

</basic>

<basic name="topicViews">

<column name="TOPIC_VIEWS" />

</basic>

</attributes>

</entity>

<entity class="PollTopic">

② PollTopic 实体配置

<discriminator-value>2</discriminator-value>

<attributes>

<basic name="maxChoices">

<column name="MAX_CHOICES" />

</basic>

<one-to-many name="options" mapped-by="pollTopic">

<cascade>

<cascade-all/>

</cascade>

</one-to-many>

</attributes>

</entity>

<entity class="PollOption">

② PollOption 实体配置

<table name="T_POLL_OPTION" />

<attributes>

<id name="optionId">

<column name="OPTION_ID" />

<generated-value strategy="TABLE" />

</id>

<basic name="optionItem">

<column name="OPTION_ITEM"/>

</basic>

<many-to-one name="pollTopic" >

<join-column name="TOPIC_ID" nullable="false"/>

</many-to-one>

</attributes>

</entity>

<entity class="Post">

② Post 实体配置

<table name="T_POST" />

<attributes>

<id name="postId">

<column name="POST_ID" />

<generated-value strategy="TABLE" />

</id>

<basic name="postText" fetch="EAGER">

<column name="POST_TEXT" column-definition="LONGTEXT NOT NULL"/>

<lob/>

</basic>

<basic name="postAttach" fetch="LAZY">

<column name="POST_ATTACH" column-definition="BLOB"/>

<lob/>

</basic>

</attributes>

</entity>

</entity-mappings>



使用这个 orm.xml 来描述实体信息的话，这里并没有标明两个继承类之间的关系，其继承信息将从实体类反射信息获取。


到这里我们的实体描述结束了，当然我们只是做了比较简单的描述，对于那些复杂的信息描述并没有进行讲述。实体描述结束了，有人会问如果我要来操作这些实体该怎么操作？这就是我们接下来要讲述的问题。


EntityManager 介绍

实体对象由实体管理器进行管理， JPA 使用 javax.persistence.EntityManager 代表实体管理器。实体管理器和持久化上下文关联，持久化上下文是一系列实体的管理环境，我们通过 EntityManager 和持久化上下文进

通过dateadd和case..when...then.else函数来比较开始考试日期,考试结束日期与当前时间来确定是否可以参加考试.
select   name,test_num,test_paper,begin_date,end_date,
      valid=case   when   begin_date>dateadd(dd,0,getdate()) or end_date<dateadd(dd,0,getdate()) then   0   else   1   end    
from test where id=1
或者
SELECT    'valid '=
      CASE         
       WHEN datediff(dd,begin_date,getdate())<0 THEN 0
       WHEN datediff(dd,end_date,getdate())>0 THEN 0
else 1     
      END,
name,test_num,test_paper,begin_date,end_date
from test where id=1

Mysql中的获取最后一条插入ID的函数是Last_INSERT_ID(),而MSSql2000的函数有以下三个.
IDENT_CURRENT() 返回为任何会话和任何作用域中的特定表最后生成的标识值。  
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。   
推荐使用:SCOPE_IDENTITY () 返回为当前会话和当前作用域中的任何表最后生成的标识值。(防止返回触发器中的insert的IDENTITY值)  

时间转换函数
SELECT convert(varchar(10),mn_test.begin_date,23)+'至'+convert(varchar(10),mn_test.end_date,23) as testtime FROM test LEFT OUTER JOIN grade ON (test.grade_note = grade.note)

对查询结果随机排序
SELECT * FROM Northwind..Orders ORDER BY NEWID()
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()

附1:SQLServer和Oracle的常用函数对比

　　1.绝对值

　　S:select abs(-1) value

　　O:select abs(-1) value from dual

　　2.取整(大)

　　S:select ceiling(-1.001) value

　　O:select ceil(-1.001) value from dual

　　3.取整（小）

　　S:select floor(-1.001) value

　　O:select floor(-1.001) value from dual

　　4.取整（截取）

　　S:select cast(-1.002 as int) value

　　O:select trunc(-1.002) value from dual

　　5.四舍五入

　　S:select round(1.23456,4) value 1.23460

　　O:select round(1.23456,4) value from dual 1.2346

　　6.e为底的幂

　　S:select Exp(1) value 2.7182818284590451

　　O:select Exp(1) value from dual 2.71828182

　　7.取e为底的对数

　　S:select log(2.7182818284590451) value 1

　　O:select ln(2.7182818284590451) value from dual; 1

　　8.取10为底对数

　　S:select log10(10) value 1

　　O:select log(10,10) value from dual; 1

　　9.取平方

　　S:select SQUARE(4) value 16

　　O:select power(4,2) value from dual 16

　　10.取平方根

　　S:select SQRT(4) value 2

　　O:select SQRT(4) value from dual 2

　　11.求任意数为底的幂

　　S:select power(3,4) value 81

　　O:select power(3,4) value from dual 81

　　12.取随机数

　　S:select rand() value

　　O:select sys.dbms_random.value(0,1) value from dual;

　　13.取符号

　　S:select sign(-8) value -1

　　O:select sign(-8) value from dual -1

　　----------数学函数

　　14.圆周率

　　S:SELECT PI() value 3.1415926535897931

　　O:不知道

　　15.sin,cos,tan 参数都以弧度为单位

　　例如：select sin(PI()/2) value 得到1（SQLServer）

　　16.Asin,Acos,Atan,Atan2 返回弧度

　　17.弧度角度互换(SQLServer，Oracle不知道)

　　DEGREES：弧度-〉角度

　　RADIANS：角度-〉弧度

　　---------数值间比较

　　18. 求集合最大值

　　S:select max(value) value from

　　(select 1 value

　　union

　　select -2 value

　　union

　　select 4 value

　　union

　　select 3 value)a

　　O:select greatest(1,-2,4,3) value from dual

　　19. 求集合最小值

　　S:select min(value) value from

　　(select 1 value

　　union

　　select -2 value

　　union

　　select 4 value

　　union

　　select 3 value)a

　　O:select least(1,-2,4,3) value from dual

　　20.如何处理null值(F2中的null以10代替)

　　S:select F1,IsNull(F2,10) value from Tbl

　　O:select F1,nvl(F2,10) value from Tbl

--------数值间比较

　　21.求字符序号

　　S:select ascii('a') value

　　O:select ascii('a') value from dual

　　22.从序号求字符

　　S:select char(97) value

　　O:select chr(97) value from dual

　　23.连接

　　S:select '11'+'22'+'33' value

　　O:select CONCAT('11','22')||33 value from dual

　　23.子串位置 --返回3

　　S:select CHARINDEX('s','sdsq',2) value

　　O:select INSTR('sdsq','s',2) value from dual

　　23.模糊子串的位置 --返回2,参数去掉中间%则返回7

　　S:select patindex('%d%q%','sdsfasdqe') value

　　O:oracle没发现，但是instr可以通过第四霾问 刂瞥鱿执问?BR>　　select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6

　　24.求子串

　　S:select substring('abcd',2,2) value

　　O:select substr('abcd',2,2) value from dual

　　25.子串代替 返回aijklmnef

　　S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value

　　O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual

　　26.子串全部替换

　　S:没发现

　　O:select Translate('fasdbfasegas','fa','我' ) value from dual

　　27.长度

　　S:len,datalength

　　O:length

　　28.大小写转换 lower,upper

　　29.单词首字母大写

　　S:没发现

　　O:select INITCAP('abcd dsaf df') value from dual

　　30.左补空格（LPAD的第一个参数为空格则同space函数）

　　S:select space(10)+'abcd' value

　　O:select LPAD('abcd',14) value from dual

　　31.右补空格（RPAD的第一个参数为空格则同space函数）

　　S:select 'abcd'+space(10) value

　　O:select RPAD('abcd',14) value from dual

　　32.删除空格

　　S:ltrim,rtrim

　　O:ltrim,rtrim,trim

　　33. 重复字符串

　　S:select REPLICATE('abcd',2) value

　　O:没发现

　　34.发音相似性比较(这两个单词返回值一样，发音相同)

　　S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')

　　O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual

　　SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差

　　返回0-4，4为同音，1最高

　　--------------日期函数

　　35.系统时间

　　S:select getdate() value

　　O:select sysdate value from dual

　　36.前后几日

　　直接与整数相加减

　　37.求日期

　　S:select convert(char(10),getdate(),20) value

　　O:select trunc(sysdate) value from dual

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

　　38.求时间

　　S:select convert(char(8),getdate(),108) value

　　O:select to_char(sysdate,'hh24:mm:ss') value from dual

　　39.取日期时间的其他部分

　　S:DATEPART 和 DATENAME 函数 （第一个参数决定）

　　O:to_char函数 第二个参数决定

参数---------------------------------下表需要补充

　　year yy, yyyy

　　quarter qq, q (季度)

　　month mm, m (m O无效)

　　dayofyear dy, y (O表星期)

　　day dd, d (d O无效)

　　week wk, ww (wk O无效)

　　weekday dw (O不清楚)

　　Hour hh,hh12,hh24 (hh12,hh24 S无效)

　　minute mi, n (n O无效)

　　second ss, s (s O无效)

　　millisecond ms (O无效)

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

　　40.当月最后一天

　　S:不知道

　　O:select LAST_DAY(sysdate) value from dual

　　41.本星期的某一天（比如星期日）

　　S:不知道

　　O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

　　42.字符串转时间

　　S:可以直接转或者select cast('2004-09-08'as datetime) value

　　O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

　　43.求两日期某一部分的差（比如秒）

　　S:select datediff(ss,getdate(),getdate()+12.3) value

　　O:直接用两个日期相减（比如d1-d2=12.3）

　　SELECT (d1-d2)*24*60*60 vaule FROM DUAL;

　　44.根据差值求新的日期（比如分钟）

　　S:select dateadd(mi,8,getdate()) value

　　O:SELECT sysdate+8/60/24 vaule FROM DUAL;

　　45.求不同时区时间

　　S:不知道

　　O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;

　　-----时区参数,北京在东8区应该是Ydt-------

　　AST ADT 大西洋标准时间

　　BST BDT 白令海标准时间

　　CST CDT 中部标准时间

　　EST EDT 东部标准时间

　　GMT 格林尼治标准时间

　　HST HDT 阿拉斯加—夏威夷标准时间

　　MST MDT 山区标准时间

　　NST 纽芬兰标准时间

　　PST PDT 太平洋标准时间

　　YST YDT YUKON标准时间

Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:

函数 Oracle Microsoft SQL　Server

把字符转换为ASCII ASCII ASCII

字串连接 CONCAT (expression + expression)

把ASCII转换为字符 CHR CHAR

返回字符串中的开始字符（左起） INSTR CHARINDEX

把字符转换为小写 LOWER LOWER

把字符转换为大写 UPPER UPPER

填充字符串的左边 LPAD N/A

清除开始的空白 LTRIM LTRIM

清除尾部的空白 RTRIM RTRIM

字符串中的起始模式（pattern） INSTR PATINDEX

多次重复字符串 RPAD REPLICATE

字符串的语音表示 SOUNDEX SOUNDEX

重复空格的字串 RPAD SPACE

从数字数据转换为字符数据 TO_CHAR STR

子串 SUBSTR SUBSTRING

替换字符 REPLACE STUFF

将字串中的每个词首字母大写 INITCAP N/A

翻译字符串 TRANSLATE N/A

字符串长度 LENGTH DATELENGTH or LEN

列表中最大的字符串 GREATEST N/A

列表中最小的字符串 LEAST N/A

如果为NULL则转换字串 NVL ISNULL

　

日期函数

函数 Oracle Microsoft SQL　Server

日期相加 (date column +/- value) or

ADD_MONTHS DATEADD

两个日期的差 (date column +/- value) or

MONTHS_BETWEEN DATEDIFF

当前日期和时间 SYSDATE GETDATE()

一个月的最后一天 LAST_DAY N/A

时区转换 NEW_TIME N/A

日期后的第一个周日 NEXT_DAY N/A

代表日期的字符串 TO_CHAR DATENAME

代表日期的整数 TO_NUMBER

(TO_CHAR)) DATEPART

日期舍入 ROUND CONVERT

日期截断 TRUNC CONVERT

字符串转换为日期 TO_DATE CONVERT

如果为NULL则转换日期 NVL ISNULL

　

转换函数

函数 Oracle Microsoft SQL　Server

数字转换为字符 TO_CHAR CONVERT

字符转换为数字 TO_NUMBER CONVERT

日期转换为字符 TO_CHAR CONVERT

字符转换为日期 TO_DATE CONVERT

16进制转换为2进制 HEX_TO_RAW CONVERT

2进制转换为16进制 RAW_TO_HEX CONVERT

　

其它行级别的函数

函数 Oracle Microsoft SQL　Server

返回第一个非空表达式 DECODE COALESCE

当前序列值 CURRVAL N/A

下一个序列值 NEXTVAL N/A

如果exp1 = exp2, 返回null DECODE NULLIF

用户登录账号ID数字 UID SUSER_ID

用户登录名 USER SUSER_NAME

用户数据库ID数字 UID USER_ID

用户数据库名 USER USER_NAME

当前用户 CURRENT_USER CURRENT_USER

用户环境(audit trail) USERENV N/A

在CONNECT BY子句中的级别 LEVEL N/A

　

合计函数

函数 Oracle Microsoft SQL　Server

Average AVG AVG

Count COUNT COUNT

Maximum MAX MAX

Minimum MIN MIN

Standard deviation STDDEV STDEV or STDEVP

Summation SUM SUM

Variance VARIANCE VAR or VARP

Oracle还有一个有用的函数EXTRACT,提取并且返回日期时间或时间间隔表达式中特定的时间域:

EXTRACT(YEAR FROM 日期)

T_SQL命令大全

--语 句 功 能

--数据操作

SELECT --从数据库表中检索数据行和列

INSERT --向数据库表添加新数据行

DELETE --从数据库表中删除数据行

UPDATE --更新数据库表中的数据

--数据定义

CREATE TABLE --创建一个数据库表

DROP TABLE --从数据库中删除表

ALTER TABLE --修改数据库表结构

CREATE VIEW --创建一个视图

DROP VIEW --从数据库中删除视图

CREATE INDEX --为数据库表创建一个索引

DROP INDEX --从数据库中删除索引

CREATE PROCEDURE --创建一个存储过程

DROP PROCEDURE --从数据库中删除存储过程

CREATE TRIGGER --创建一个触发器

DROP TRIGGER --从数据库中删除触发器

CREATE SCHEMA --向数据库添加一个新模式

DROP SCHEMA --从数据库中删除一个模式

CREATE DOMAIN --创建一个数据值域

ALTER DOMAIN --改变域定义

DROP DOMAIN --从数据库中删除一个域

--数据控制

GRANT --授予用户访问权限

DENY --拒绝用户访问

REVOKE --解除用户访问权限

--事务控制

COMMIT --结束当前事务

ROLLBACK --中止当前事务

SET TRANSACTION --定义当前事务数据访问特征

--程序化SQL

DECLARE --为查询设定游标

EXPLAN --为查询描述数据访问计划

OPEN --检索查询结果打开一个游标

FETCH --检索一行查询结果

CLOSE --关闭游标

PREPARE --为动态执行准备SQL 语句

EXECUTE --动态地执行SQL 语句

DESCRIBE --描述准备好的查询

 

---局部变量

declare @id char(10)

--set @id = '10010001'

select @id = '10010001'

---全局变量

---必须以@@开头

--IF ELSE

declare @x int @y int @z int

select @x = 1 @y = 2 @z=3

if @x > @y

print 'x > y' --打印字符串'x > y'

else if @y > @z

print 'y > z'

else print 'z > y'

--CASE

use pangu

update employee

set e_wage =

case

when job_level = ’1’ then e_wage*1.08

when job_level = ’2’ then e_wage*1.07

when job_level = ’3’ then e_wage*1.06

else e_wage*1.05

end

--WHILE CONTINUE BREAK

declare @x int @y int @c int

select @x = 1 @y=1

while @x < 3

begin

print @x --打印变量x 的值

while @y < 3

begin

select @c = 100*@x + @y

print @c --打印变量c 的值

select @y = @y + 1

end

select @x = @x + 1

select @y = 1

end

--WAITFOR

--例 等待1 小时2 分零3 秒后才执行SELECT 语句

waitfor delay ’01:02:03’

select * from employee

--例 等到晚上11 点零8 分后才执行SELECT 语句

waitfor time ’23:08:00’

select * from employee

 

***SELECT***

select *(列名) from table_name(表名) where column_name operator value

ex 宿主)

select * from stock_information where stockid = str(nid)

stockname = 'str_name'

stockname like '% find this %'

stockname like '[a-zA-Z]%' --------- ([]指定值的范围)

stockname like '[^F-M]%' --------- (^排除指定范围)

--------- 只能在使用like关键字的where子句中使用通配符)

or stockpath = 'stock_path'

or stocknumber < 1000

and stockindex = 24

not stocksex = 'man'

stocknumber between 20 and 100

stocknumber in(10,20,30)

order by stockid desc(asc) --------- 排序，desc-降序，asc-升序

order by 1,2 --------- by列号

stockname = (select stockname from stock_information where stockid = 4)

--------- 子查询

--------- 除非能确保内层select只返回一个行的值，

--------- 否则应在外层where子句中用一个in限定符

select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复

select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name

select stockname , "stocknumber" = count(*) from table_name group by stockname

--------- group by 将表按行分组,指定列中有相同的值

having count(*) = 2 --------- having选定指定的组

select *

from table1, table2

where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示

table1.id =* table2.id -------- 右外部连接

select stockname from table1

union [all] ----- union合并查询结果集，all-保留重复行

select stockname from table2

--STDEV()

--STDEV()函数返回表达式中所有数据的标准差

--STDEVP()

--STDEVP()函数返回总体标准差

--VAR()

--VAR()函数返回表达式中所有值的统计变异数

--VARP()

--VARP()函数返回总体变异数

----算术函数----

/***三角函数***/

SIN(float_expression) --返回以弧度表示的角的正弦

COS(float_expression) --返回以弧度表示的角的余弦

TAN(float_expression) --返回以弧度表示的角的正切

COT(float_expression) --返回以弧度表示的角的余切

/***反三角函数***/

ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角

ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角

ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角

ATAN2(float_expression1,float_expression2)

--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角

DEGREES(numeric_expression)

--把弧度转换为角度返回与表达式相同的数据类型可为

--INTEGER/MONEY/REAL/FLOAT 类型

RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为

--INTEGER/MONEY/REAL/FLOAT 类型

EXP(float_expression) --返回表达式的指数值

LOG(float_expression) --返回表达式的自然对数值

LOG10(float_expression)--返回表达式的以10 为底的对数值

SQRT(float_expression) --返回表达式的平方根

/***取近似值函数***/

CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为

--INTEGER/MONEY/REAL/FLOAT 类型

FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为

--INTEGER/MONEY/REAL/FLOAT 类型

ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据

--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型

ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为

--INTEGER/MONEY/REAL/FLOAT 类型

SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型

--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型

PI() --返回值为π 即3.1415926535897936

RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数

----字符串函数----

ASCII() --函数返回字符表达式最左端字符的ASCII 码值

CHAR() --函数用于将ASCII 码转换为字符

--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值

LOWER() --函数把字符串全部转换为小写

UPPER() --函数把字符串全部转换为大写

STR() --函数把数值型数据转换为字符型数据

LTRIM() --函数把字符串头部的空格去掉

RTRIM() --函数把字符串尾部的空格去掉

LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串

CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置

SOUNDEX() --函数返回一个四位字符码

--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值

DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异

--0 两个SOUNDEX 函数返回值的第一个字符不同

--1 两个SOUNDEX 函数返回值的第一个字符相同

--2 两个SOUNDEX 函数返回值的第一二个字符相同

--3 两个SOUNDEX 函数返回值的第一二三个字符相同

--4 两个SOUNDEX 函数返回值完全相同

QUOTENAME() --函数返回被特定字符括起来的字符串

/*select quotename('abc', '{') quotename('abc')

运行结果如下

----------------------------------{

{abc} [abc]*/

REPLICATE() --函数返回一个重复character_expression 指定次数的字符串

/*select replicate('abc', 3) replicate( 'abc', -2)

运行结果如下

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

abcabcabc NULL*/

REVERSE() --函数将指定的字符串的字符排列顺序颠倒

REPLACE() --函数返回被替换了指定子串的字符串

/*select replace('abc123g', '123', 'def')

运行结果如下

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

abcdefg*/

SPACE() --函数返回一个有指定长度的空白字符串

STUFF() --函数用另一子串替换字符串指定位置长度的子串

----数据类型转换函数----

CAST() 函数语法如下

CAST() (<expression> AS <data_ type>[ length ])

CONVERT() 函数语法如下

CONVERT() (<data_ type>[ length ], <expression> [, style])

select cast(100+99 as char) convert(varchar(12), getdate())

运行结果如下

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

199 Jan 15 2000

----日期函数----

DAY() --函数返回date_expression 中的日期值

MONTH() --函数返回date_expression 中的月份值

YEAR() --函数返回date_expression 中的年份值

DATEADD(<datepart> ,<number> ,<date> )

--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期

DATEDIFF(<datepart> ,<number> ,<date> )

--函数返回两个指定日期在datepart 方面的不同之处

DATENAME(<datepart> , <date> ) --函数以字符串的形式返回日期的指定部分

DATEPART(<datepart> , <date> ) --函数以整数值的形式返回日期的指定部分

GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间

----系统函数----

APP_NAME() --函数返回当前执行的应用程序的名称

COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值

COL_LENGTH(<'table_name'>, <'column_name'> ) --函数返回表中指定字段的长度值

COL_NAME(<table_id>, <column_id> ) --函数返回表中指定字段的名称即列名

DATALENGTH() --函数返回数据表达式的数据的实际长度

DB_ID(['database_name']) --函数返回数据库的编号

DB_NAME(database_id) --函数返回数据库的名称

HOST_ID() --函数返回服务器端计算机的名称

HOST_NAME() --函数返回服务器端计算机的名称

IDENTITY(<data_type>[, seed increment]) [AS column_name]}

--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中

/*select identity(int, 1, 1) as column_name

into newtable

from oldtable*/

ISDATE() --函数判断所给定的表达式是否为合理日期

ISNULL(<check_expression>, <replacement_value> ) --函数将表达式中的NULL 值用指定值替换

ISNUMERIC() --函数判断所给定的表达式是否为合理的数值

NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值

NULLIF(<expression1>, <expression2> )

--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值

附2:时间转换函数 convert(char,date,N)输出的各中样式 convert(varchar(10),字段名,转换格式) 例： select id,convert(varchar(10),date,11) as date from sb N 日期样式 0 04 2 2005 9:06AM ..... 20 2005-04-02 09:06:18 21 2005-04-02 09:06:18.857 22 04/02/05 9:06:18 AM 23 2005-04-02 24 09:06:18 25 2005-04-02 09:06:18.857 100 04 2 2005 9:06AM 101 04/02/2005 102 2005.04.02 103 02/04/2005 104 02.04.2005 105 02-04-2005 108 09:06:18 109 04 2 2005 9:06:18:857AM 110 04-02-2005 114 09:06:18:857 120 2005-04-02 09:06:18 121 2005-04-02 09:06:18.857 126 2005-04-02T09:06:18.857