常用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();

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

 

转载于:https://www.cnblogs.com/monkey200804/p/7140589.html