sql 常用查询和优化笔记

一.返回多个带统计的结果

1.sum(if)

select  sum(if(status=1,1,0))as s1, sum(if(status=2,1,0))as s2   from vehicle;

2.count(if)

select  count(status=1 or null) as s1,count(status=2 or null) as s2 from vehicle;

注：不要少了后面的or null,因为count只有在遇见null时不计数，前者单引号内不管输入什么值都会统计出所有记录数。加上or NULL ，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。

二.统计结果添加条件判断

SELECT
    CASE WHEN 条件1
        THEN 结果1
    WHEN 条件2
        THEN 结果2
    ELSE 结果3
    END AS ISONLINE
FROM
    vehicle

三.in 和 exists

select * from student where stuid in (select stuid from score where ...);
in先执行里面的子查询，后执行外面的主查询，因此适用于主查询记录较大且有索引时

select * from student where exists (select stuid from score where ...);
exists先执行外面的主查询，后执行里面的子查询，因此适用于子查询记录较大时

四.#{}和${}

#：将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号；可以防止防止sql注入。

$:将传入的数据直接显示生成在sql中,方式一般用于传入数据库对象，例如传入表名，字段名。

五.分页优化

select * from tableName limit 10 offset 100;(全表扫描) ----->  select * from tableName where id >100 offset 10（根据指针定位）

六.有则更新，无则插入

Mysql中用：replace into 

Mybaits:   

<insert id="insertUser"  useGeneratedKeys="true" keyProperty="id" keyColumn="id"   parameterType="com.entity.User">
        insert INTO  t_user(openId, name, gender, phoneNumber)
        VALUES (#{openId},#{name},#{gender},#{phoneNumber})
        ON DUPLICATE KEY UPDATE
        name = #{name},
        gender = #{gender},
        phoneNumber = #{phoneNumber}
</insert>