mysql ibatis count_[mysql] mysql-myibatis-整理

==================================== insert ==========================================语句1

insert intoecshop_new.ecs_users

(

reg_time,

mobile_phone,

is_validated

)values(

unix_timestamp(NOW())- 8*3600,

#{mobilePhone},'0')语句2

insert

intokjt.ecs_order_info

(

order_sn,user_id,

password2,emp_id)values(

#{orderSn,jdbcType=VARCHAR},

#{userId,jdbcType=DECIMAL},

#{password2,jdbcType=VARCHAR},#{empId})知识点：1、Mybatis主键自动生成：

在mysql、sql server等支持主键自动增长的数据库中,mybatis插入时，对自动生成的字段的写法：

insert into(uername, password, email )

vlaues

(#{username}, #{password}, #{email})

2、可以用控制语句控制是否插入某字段,emp_id

3、在插入时可以指定该字段的字段类型

#{orderSn,jdbcType=VARCHAR}4、参数类型一般是相应的bean5、mysql的日期函数

FROM_UNIXTIME(dateSerial,partten)

UNIX_TIMESTAMP(date)

NOW()

mysql> SELECTUNIX_TIMESTAMP() ; (获得当前时间序列)->1249524739mysql> SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d')->2007年11月20

mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ;->1249488000mysql> SELECT * FROM `student` WHERE regTime > UNIX_TIMESTAMP(curdate()) //今天所有学生注册记录。==================================== update ==========================================语句1

updateecshop_new.ecs_users usetu.kjt_open_id=#{openId}where(u.user_name = #{userName} or u.mobile_phone =#{userName})andu.password=#{password}语句2

updatekjt.ecs_order_info ogsetog.order_status='6',

og.password2=#{password},

og.store_receive_date=UNIX_TIMESTAMP()WHEREog.order_sn=#{orderSn}知识点：1或条件查询连接一定要加上括号，否则查询或更改就会出错where (u.user_name = #{userName} or u.mobile_phone = #{userName}) and u.password =#{password}where u.user_name = #{userName} or u.mobile_phone = #{userName} and u.password =#{password}

这两个语句的查询结果是不一样的2参数类型一般是 java.util.Map

parameterType="java.util.Map"==================================== select ==========================================语句1：

selecte.emp_idasempId,

e.emp_nameasempName,

e.sheng_codeasempShengCode,

e.shi_codeasempShiCode,

e.xian_codeasempXianCode,

(select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code) asempShengName,

(select r2.region_name from kjt.ecs_region r2 where r2.region_id = e.shi_code) asempShiName,

(select r3.region_name from kjt.ecs_region r3 where r3.region_id = e.xian_code) asempXianNamefromkjt.sale_employee_info ewhere 1 = 1

and e.emp_id =#{empId}

1两表联查(最外层单表)

(select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code) asempShengName

通过 r1.region_id=e.sheng_code 从 ecs_region 表获取省名称2resultType

查到的数据的类型asempXianName 的 empXianName 必须和 resultType 的数据类型的字段名一致

语句2：

selectu.user_id asuserId,

ifnull(e.emp_id,'') asempId,

ifnull(e.sheng_code,'') asempShengCode,

ifnull((select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code),'') asempShengNamefromecshop_new.ecs_users uleft joinkjt.emp_user_info euon u.user_id = eu.user_id

left joinkjt.sale_employee_info eon eu.emp_id =e.emp_idwhereu.is_validated= '0'

and (u.kjt_open_id = #{openId} or u.user_id =#{openId})

and (u.user_name = #{userName} or u.mobile_phone =#{userName})

and u.password =#{password}

1 外层多表联查LEFT JOIN内层两表联查

实例：当查询某销售本月的销售额的时候，如果不使用leftjoin 连接，那么查询结果不包含销售额为0/null的销售代表的记录

但如果使用leftjoin 则将销售代表个人信息表放在最前面，会得到全部销售的销售额，为0/null的就显示0/null语句3

selecto.order_idasorderId,CASE

WHEN o.pay_status = 2 THEN (o.money_paid + o.surplus + o.cash_money +o.post_money)ELSEo.order_amountEND asorderAmount,

o.surplusassurplus,

from_unixtime(o.add_time)asaddTime,

o.password2aspassword2fromkjt.ecs_order_info owhereo.order_sn=#{orderSn}知识点：

① SQL Select语句完整的执行顺序：1、from子句组装来自不同数据源的数据；2、where子句基于指定的条件对记录行进行筛选；3、groupby子句将数据划分为多个分组；4、使用聚集函数进行计算；5、使用having子句筛选分组；6、计算所有的表达式；7、使用order by对结果集进行排序。

举例：1.select 列列表 from 表列表名/视图列表名 where条件.2.select 列列表 from 表列表名/视图列表名 where 条件 group by (列列表) having条件3.select 列列表 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 order by列列表4.select 列列表 from 表1 join 表2 on 表1.列1=表2.列1...join 表n on 表n.列1=表(n-1).列1 where 表1.条件 and表2.条件...表n. 执行顺序：

分析：1. 先where 后select(先选出符合where子句的元组，再在元组中抽取指定的列组成二维表)2. 先where 再group 再having 后select3. 先where 再group 再having 再select 后order4. 先join 再where 后select

综上执行顺序为：join - where - group by - having - select - order by② 逻辑处理(多选择分支语句)CASE

WHEN o.pay_status = 1 THEN (o.money_paid +o.surplus)WHEN o.pay_status = 2 THEN (o.money_paid + o.surplus + o.cash_money +o.post_money)ELSEo.order_amountEND asorderAmount

对最终筛选出来的元组进行最后的逻辑处理，因此是在select里进行处理的

③ 时间函数

from_unixtime(o.add_time)asaddTime

语句4

selectgoods_idasgoodsId,

goods_numberasgoodsNumberfromkjt.ecs_goodswhere goods_id =#{goodsId}根据主键查询 参数类型为 BigDecimal

parameterType="java.math.BigDecimal"

语句5

selectc.idasid,

c.identify_noasidentifyNo,

c.real_nameasrealName,

c.consume_moneyasconsumeMoney,

c.taxastax,

c.consume_dateasconsumeDatefromecshop_new.ecs_user_consume_daily cwherec.identify_no=#{cardIdNo}andc.consume_date=curdate()参数类型：parameterType="java.lang.String"

“今天”：c.consume_date=curdate()

语句6

selectt1.order_idasorderId,

t1.order_snasorderSn,

t1.station_noasstationNo,

t1.station_nameasstationName,

t1.cabinet_no1ascabinetNo1,

t1.box_noasboxNofromkjt.ecs_order_info t1, ecshop_new.ecs_users t2wheret1.user_id = t2.user_id

andt1.user_id =#{userId}andt1.shipping_id= '9'

andt1.cabinet_no1is not null

order byt1.add_timedesc

注意：null 和 "" 和 0 是不一样的，is not null 用于查询不为 null的元组

语句7

SELECTg.GOODS_IDasgoodsID,

g.goods_snasgoodsSn,

g.taxastaxPrice,

g.MEASURE_UNITasgoodsUnit,

g.IS_WEIGHTasisWeight,

g.shop_priceasgoodsPrice,

g.GOODS_NAMEasgoodsName,

g.GOODS_IMGasgoodsImg,

g.CAT_IDascatId,

g.STORE_IDasstoreId,SUM(o.GOODS_NUMBER) asgoodsSumFROMkjt.ecs_order_goods o,kjt.ecs_goods gWHEREo.GOODS_ID=g.GOODS_IDGROUP BYg.GOODS_IDORDER BYgoodsSumDESC

sum() 聚集函数常用于统计金额和数量 有聚集函数必定有 group by 子句 且group by的字段最好使用不常改变的id类字段SUM(o.GOODS_NUMBER) asgoodsSumGROUP BYg.GOODS_ID

语句8

SELECTs.emp_idasempId,

s.emp_nameasempNameFROMkjt.sale_employee_info sWHEREs.status= '1'

and (s.mobile like CONCAT('%',#{empName},'%') or s.emp_name like CONCAT('%',#{empName},'%'))

order by s.emp_id desc

模糊查询

s.mobilelike CONCAT('%',#{empName},'%')

语句9

select

count(*)fromecshop_new.ecs_users u, kjt.emp_user_info ewhereu.user_id = e.user_id

ande.emp_id=#{empId}and

year(from_unixtime(u.reg_time + 3600*8)) = year(now())and

month(from_unixtime(u.reg_time + 3600*8)) = month(now())日期函数year()month()

语句10

select

sum(o.money_paid + o.surplus + o.cash_money +o.post_money)fromkjt.ecs_order_info owhere o.emp_id =#{empId}and year(from_unixtime(o.add_time)) = year(now())and month(from_unixtime(o.add_time)) = month(now())and o.pay_status = 2

语句11

select

count(*)fromecshop_new.ecs_users u, kjt.emp_user_info ewhere u.user_id = e.user_id

and e.emp_id =#{empId}

and from_unixtime(u.reg_time + 3600*8) >=#{startDate}

and from_unixtime(u.reg_time + 3600*8) <= date_add(#{endDate}, interval 1 day)]]>

1时间段查询2日期加减计算

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

date 是一个DATETIME或DATE值，用来指定起始时间。

expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。

type 为关键词，它指示了表达式被解释的方式。===============================================================mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1SECOND;-> '1998-01-01 00:00:00'mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1SECOND);-> '1998-01-01 00:00:00'

===============================================================mysql> SELECT INTERVAL 1 DAY + '1997-12-31';-> '1998-01-01'mysql> SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 DAY);-> '1998-01-01 23:59:59'

===============================================================mysql> SELECT '1998-01-01' - INTERVAL 1SECOND;-> '1997-12-31 23:59:59'

===============================================================语句12

SELECTROW_.*

FROM(selectt1.order_idasorderId,

t1.order_snasorderSn,CASE

WHEN t1.pay_status = 2

THEN (t1.money_paid + t1.surplus + t1.cash_money +t1.post_money)ELSEt1.order_amountEND asorderTaxPrice,

t1.goods_amountasorderPricefromkjt.ecs_order_info t1, ecshop_new.ecs_users t2wheret1.user_id = t2.user_id

and t1.user_id =#{userId}

order by t1.add_time desc

) ROW_

limit #{startRecord},#{pageSize}]]>

分页使用 limit 起始索引 每页条数

语句13 多表联合查询select

max(s.emp_id) asempId,max(s.emp_name) asempName,

ifnull(max(g.goods_name),'-') asgoodsName,

ifnull(SUM(g.goods_number*g.goods_price),'0') astotalMoneyfromkjt.sale_employee_info sLEFT JOINkjt.ecs_order_info oon s.emp_id =o.emp_idLEFT JOINkjt.ecs_order_goods gon o.order_id =g.order_idwhere 1=1

group byg.goods_id,s.emp_idorder by

SUM(g.goods_number*g.goods_price) DESC

============================ 拓展知识点 =========================① Mybatis主键自动生成：

在MYSQL、sql server等支持主键自动增长的数据库中！mybatis插入时，对自动生成的字段的写法：

insert into数据表名(uername, password, email )

vlaues

(#{username}, #{password}, #{email})对不支持自动生成功能的数据库，mybatis提供以下写法，不过，此写法生成的ID是随机的

SELECT LAST_INSERT_ID() ASid

insert into数据表名(id, username, password, email )values(#{id}，#{username}，#{password}，#{email})② Mybaits-SQL语句包含： select/update/delete/insert 等操作

select

from表名whereid=#{id}③ Mybatis动态SQL语句

a、if语句select字段名from表名where state=”1”

AND条件b、choose,when, otherwiseselect 字段名 from表名where state=”1”

And条件

And条件

And条件

c、trim,where , set

1, where

Select 字段名 from表名条件注 : 加 后则确保一定是 where开头2, set

Update表名

字段名=#{参数}

Where条件

d、foreach 通常构建在in条件中Select 字段名 from表名Where 字段名 in

#{参数名}e、作用例：批量删除

delete from tests where id in]]>

#{要删除的id}

f、模糊查询：select 字段名 from 表名 where 字段名 like "%" #{参数} "%"

④ sql元素

Sql元素用来定义一个可以复用的SQL 语句段，供其它语句调用。

SELECTST.STUDENT_ID,

ST.STUDENT_NAME,

ST.STUDENT_SEX,

ST.STUDENT_BIRTHDAY,

ST.CLASS_IDFROMSTUDENT_TBL ST这样，在select的语句中就可以直接引用使用了

WHERE ST.STUDENT_ID =#{studentID}⑤ parameters

MyBatis可以使用的基本数据类型和Java的复杂数据类型。

基本数据类型包括String，int，date等。但是使用基本数据类型，只能提供一个参数，所以需要使用Java实体类或Map类型做参数类型。

通过#{}可以直接得到其属性。