常用SQL语句和HQL语句写法
1、左(右)外连接:
select b.phone_no,a.pak_no from t_phone a left(right) join t_pak_used b on (a.phone_no=b.phone_no) where a.phone_num = '13045977553'
--------------------------------------------------------------------------------------------------------------------------------------------------
2、外连接:
select b.phone_no,a.pak_no from t_phone a join t_pak_used b on (a.phone_no=b.phone_no) where a.phone_num = '13045977553'
--------------------------------------------------------------------------------------------------------------------------------------------------
3、SQL中类似JAVA的 switch-case语句:
SELECT <myColumnSpec> = CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END
--------------------------------------------------------------------------------------------------------------------------------------------------
3、在mybatis中如果传递多个参数,参数值类型parameterType可以不写,然后SQL语句:
update t_pak_used set sp_call = #{0} where phone_no = #{1} //传递参数的顺序必须正确
--------------------------------------------------------------------------------------------------------------------------------------------------
4、mybatis中forEach的用法:
<select id="" parameterType="list">
select * from G_Dept where a in
<foreach collection="list" item="i" open="(" separator="," close=")" >
#{i} //等价于(#{i1},#{i2},#{i3}...)
</foreach>
</select>
--------------------------------------------------------------------------------------------------------------------------------------------------
5、oracle中的分页查询语句:
//此语句查询出第11到第60条数据,rowno为rownum的别名
select * from (select rownum rowno, t.* from t_phone t where rownum <= 60) where rowno >10
--------------------------------------------------------------------------------------------------------------------------------------------------
6、MySQL中的分页查询语句:
select * from table_name limit 10,5 //取第11到15条数据,后一个参数代表返回行的最大数目
--------------------------------------------------------------------------------------------------------------------------------------------------
7、oracle中随机获取20条数据:
select * from(select * from t_phone order by dbms_random.value) where rownum <=20;
--------------------------------------------------------------------------------------------------------------------------------------------------
==================================================================================================================================================
==================================================================================================================================================
********HQL语句********
1、查询:
这里需要注意的是,from后面跟的不是表名而是实体类名,如果要查找所有select后不跟*,而是跟类名的别名
String hql = "select user from UserBean user";
查找后放入集合中:
Query query = session.createQuery(hql);
List<UserBean> user = query.list();
如果要查找个别字段:
String hql = "select user.userName form UserBean user";
**如果查询多个字段,则返回的是个Object[],可使用以下方法取得数值:
for(int i = 0; i<user.size(); i++){
Object [] a = (Object[]) user.get(i);
}
**查询中也可以使用?来占位,参数的索引一样从0开始:
String hql = "select user from UserBean user where user.userName like ?";
Query query = session.createQuery(hql);
query.setParameter(0, "peter");
--------------------------------------------------------------------------------------------------------------------------------------------------
2、插入增加:
直接调用Hibernate中save()方法:
UserBean user = new UserBean();
session.save(user);
--------------------------------------------------------------------------------------------------------------------------------------------------
3、修改:
update后面跟的是实体类名,注意要executeUpdate()
String hql = "update UserBean user set user.id=10 where user.id=5";
Query query = session.createQuery(hql);
int ret = query.executeUpdate();
--------------------------------------------------------------------------------------------------------------------------------------------------
4、删除:
这里需要注意的是from后是实体类名
String hql = " DELETE FROM UserBean user WHERE user.id = 1 ";
Query queryupdate = session.createQuery(hql);
int ret = queryupdate.executeUpdate();
--------------------------------------------------------------------------------------------------------------------------------------------------