一些简单sql

SELECT
count(0) as totalNum,
sum(CASE WHEN learn_status = '1' AND subject_type = '3' AND STATUS = '1' THEN 1 ELSE 0 END) AS km1,
sum(CASE WHEN learn_status = '1' AND subject_type = '4' AND STATUS = '1' THEN 1 ELSE 0 END) AS km2,
sum(CASE WHEN learn_status = '1' AND subject_type = '5' AND STATUS = '1' THEN 1 ELSE 0 END) AS km3,
sum(CASE WHEN learn_status = '1' AND subject_type = '6' AND STATUS = '1' THEN 1 ELSE 0 END) AS km4
FROM
sm_student t
WHERE 1 = 1 AND school_id =1

 

按照学校找到科目3,4,5,6汇总的人数

 

 

sql之left join、right join、inner join的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

 

三、update

UPDATE sm_student SET card_type = 1 and card_no = "123456789" and last_updated_date=CURDATE() and created_by=22 where id=89

set改变的值用and链接，是与的判断，不能改变值。用,隔开

 

四、SHOW TABLE STATUS;

这是显示mysql中所有的表的状态

这个是mysql的序列

 

五、show OPEN TABLES where In_use > 0;

mysql查询所有被锁住的表

 

六，拼接查询 用逗号拼接查询的字段

SELECT
GROUP_CONCAT(t1.name)
FROM
sm_coach_vehicle_kind t
INNER JOIN sm_vehicle_kind t1 on t.vehicle_kind_id=t1.id
where t.coach_id=#{coachId}