mysql hql查询语句_常用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 = CASE

WHEN THEN

WHEN THEN

ELSE

END

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

3、在mybatis中如果传递多个参数，参数值类型parameterType可以不写，然后SQL语句：

update t_pak_used set sp_call = #{0} where phone_no = #{1} //传递参数的顺序必须正确

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

4、mybatis中forEach的用法：

select * from G_Dept where a in

#{i} //等价于(#{i1},#{i2},#{i3}...)

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

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 user = query.list();

如果要查找个别字段：

String hql = "select user.userName form UserBean user";

**如果查询多个字段，则返回的是个Object[]，可使用以下方法取得数值：

for(int i = 0; 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();

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