MySQL----语法篇

IFNULL

SELECT 
  a.`id`,
  a.`name`,
  b.`id`,
  IFNULL(b.`name`,'/')    //如果查询为null，可以设置一个默认值
FROM
  tb_content_category AS a 
  LEFT JOIN tb_content_category AS b 
    ON a.`parent_id` = b.`id`

EXISTS和select配饰使用

（执行student.length次）对于下面的sql语句首先执行的语句是select * from student s，在根据表的每一条记录，执行以下语句（EXISTS(select stuid from score ss where ss.stuid = s.stuid)），依次去判断where后面的条件是否成立：如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回

select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)

EXISTS和update配饰使用

update actor set name='1' where id=1 and exists(1=1);

EXISTS和Insert配合使用

有问题？参考：https://www.cnblogs.com/chucklu/p/10482311.html

IF NOT EXISTS (SELECT * FROM logininfo WHERE username="stdf")
BEGIN
	INSERT INTO logininfo(username,PASSWORD) VALUES("xx","xx")
END

<insert id="insertSelection" parameterType="net.microcarystal.sport.pojo.master.CourseStudentSelection">
        <selectKey keyProperty="id" order="AFTER" resultType="long">
            select LAST_INSERT_ID();
        </selectKey>
        insert into course_student_selection
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="schoolId != null">
                schoolId,
            </if>
            <if test="stuId != null">
                stuId,
            </if>
            <if test="yearId != null">
                yearId,
            </if>
            <if test="courseId != null">
                courseId,
            </if>
            <if test="scheduleId != null">
                scheduleId,
            </if>
            <if test="sportStageId != null">
                sportStageId,
            </if>
            <if test="sportItemId != null">
                sportItemId,
            </if>
            <if test="updateTime != null">
                updateTime,
            </if>
            <if test="createTime != null">
                createTime,
            </if>
            <if test="createUser != null">
                createUser,
            </if>
            <if test="updateUser != null">
                updateUser,
            </if>
        </trim>
        select
        <trim  suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=BIGINT},
            </if>
            <if test="status != null">
                #{status,jdbcType=INTEGER},
            </if>
            <if test="schoolId != null">
                #{schoolId,jdbcType=BIGINT},
            </if>
            <if test="stuId != null">
                #{stuId,jdbcType=BIGINT},
            </if>
            <if test="yearId != null">
                #{yearId,jdbcType=BIGINT},
            </if>
            <if test="courseId != null">
                #{courseId,jdbcType=BIGINT},
            </if>
            <if test="scheduleId != null">
                #{scheduleId,jdbcType=BIGINT},
            </if>
            <if test="sportStageId != null">
                #{sportStageId,jdbcType=BIGINT},
            </if>
            <if test="sportItemId != null">
                #{sportItemId,jdbcType=BIGINT},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="createUser != null">
                #{createUser,jdbcType=BIGINT},
            </if>
            <if test="updateUser != null">
                #{updateUser,jdbcType=BIGINT},
            </if>
        </trim>
        from DUAL where 1=(SELECT count(1) FROM course_schedule  a
        left join user_info b on b.id=#{stuId}
        left join course_student_selection c on c.stuId=b.id and c.scheduleId = a.id and c.`status`=1
        WHERE c.id is null and a.id = #{scheduleId} and a.lastLim>0 and (a.genderLim!=3 or (a.genderLim=3 and b.userSex=0 AND (femaleScale-femaleCount)>0) or (genderLim=3 AND b.userSex=1 AND (maleScale-maleCount)>0)))
    </insert>

插入加where条件

INSERT INTO f (id,name, num) VALUES (4,'1', 10) WHERE 1=1;#报错
INSERT INTO f (id,name, num) select 5,'1', 10 from dual WHERE 0=(select count(id) from f where id=5);#不报错

上面直接使用where条件1=1来判断，如果使用not exists

mysql> 
    ->INSERT INTO  test_book1
    ->   SELECT 1, 'TEST', 'ABC' FROM dual #从前台传递进来的值 select #{id}，#{xx},#{xx} from dual
    ->   WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 1);

IN

　　（只执行一次），in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

首先会执行from语句找出student表，然后执行 in 里面的子查询，再然后将查询到的结果和原有的user表做一个笛卡尔积，再根据我们的student.stuid IN score.stuid的条件，将结果进行筛选（既比较stuid列的值是否相等，将不相等的删除）。最后，得到符合条件的数据。

select * from student s where s.stuid in(select stuid from score ss where ss.stuid <1005)

使用UNION替代IN

select id from user where id in （1,2）；
#替换成
select id from user where id = 1
union
select id from user where id=2;

EXISTS和In选择

select * from A where id in （select id from B）

如果B查询结果集较少，A有索引，使用in

select * from student A where EXISTS(select id from score B where B.id = A.d)

如果A没有索引，B有索引，并且A的结果集少，使用exists

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

Unique

　　首先建表的时候给name设置为unique

　　不存在则插入，存在则更新（on duplicate key update）

　　　　如果数据库中有一条数据name = “jack”（因为name设置了unique，插入数据会造成冲突），那么我们继续update操作，更新年龄是19，否则就正常插入操作

INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19) ON DUPLICATE KEY UPDATE `age`=19;

　　先删除旧数据再插入最新的数据（replace into）

REPLACE INTO `student`(`name`, `age`) VALUES('Jack', 18);

　　避免重复插入（insert ignore into）

INSERT IGNORE INTO `student`(`name`, `age`) VALUES('Jack', 18);

Group By

　　group By 和聚合函数：https://blog.csdn.net/u014717572/article/details/80687042

　　当使用group By的时候,sql首先执行Group 在进行条件判断,如果希望group By的时候对某些字段不进行group By可以在条件中进行判断

比如:这种startYear=2019的数据就不会查出来

SELECT  * FROM school_year WHERE STATUS = 1 AND schoolId = 1 AND (startYear != 2019) GROUP BY startYear ORDER BY startYear DESC

我们就可以使用or方法并添加条件来将startYear的数据查询出来

SELECT  * FROM school_year WHERE STATUS = 1 AND schoolId = 1 AND (startYear != 2019 OR isThisYear=1) GROUP BY startYear ORDER BY startYear DESC

Order by

如果order by的字段包括多个表
select * from （select * from xx,xx） order by $(xx) desc

Case

　　如果字段中没有#{},比如genderLim,就会从数据库中的字段来查

And (CASE genderLim WHEN 1 THEN #{userSex} = 1 WHEN 2 THEN #{userSex} = 0 ELSE 1=1 END)

  select
        a.id as id,
        d.courseName as courseName,
        (case b.weekTimes when 1 then '星期一'
            when 2 then '星期二'
            when 3 then '星期三'
            when 4 then '星期四'
            when 5 then '星期五'
            when 6 then '星期六'
            when 7 then '星期日'
            else null end)
        as weekTimes,
        b.beginTimes as beginTimes,
        from attend_calendar

IF

　　IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false

and if((`v`.`rangeTYPE`= '2'),(`v`.`rangeID`= `s`.`SCHOOL_ID`),(`v`.`parentId`= `s`.`SCHOOL_ID`))

代替IF

#性别是1名字是liming，性别是2，名字是xx
and (sex='1' and name="liming") or (sex='2' and name="xx")

concat

　　可以将返回的字段进行拼接

SELECT CONCAT(user_info.`userCode`,'-',user_info.`userName`) AS userCodeAndUserName FROM user_info WHERE
user_info.`status`=1 AND user_info.`userType`=2 AND schoolId=1 LIMIT 0,20

update关联

        update course_schedule
        set totalCount = totalCount+1, lastLim = lastLim-1,
        maleCount = maleCount + (select ifnull(count(*),0) as maleCount from user_info where id=#{stuId} and userSex=1),
        femaleCount = femaleCount + (select ifnull(count(*),0) as maleCount from user_info where id=#{stuId} and userSex=0)
        where id = #{scheduleId,jdbcType=BIGINT}

正则

select * from tb where name regexp ’z’;

on和where的用法

无论LEFT JOIN ON后面什么条件，都不影响主表数据被查询数来（只是关联表没有数据而已）

参考：https://www.cnblogs.com/FENGXUUEILIN/p/10040025.html

SELECT
    stu.*
FROM
    student stu
 LEFT JOIN grade gra on stu.id = gra.c_stuId
WHERE gra.c_fs is NOT null

执行顺序　　

Where, Group By, Having, Order by

Insert into

https://www.w3school.com.cn/sql/sql_select_into.asp

把某些字段插入新表

SELECT colum1,colum2 INTO new_table FROM SYS_BUSINESS_SEQNUM

如果在函数里面，可以查询的数据赋值到另一个数据

iseqnum :=1;
SELECT SEQNUM  INTO iseqnum FROM SYS_BUSINESS_SEQNUM

distinct

select distinct name from xx;
建立临时表，如果name是索引就不会建立临时表，直接在索引树去重，如果要优化，对name建立索引是非要有必要的。

date()

　　省略时间

　　更多参考：https://www.w3school.com.cn/sql/sql_dates.asp

select * from DATE(date),date where Date(date)='2020-01-01';

UNION和UNION ALL

注意UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
每条 SELECT 语句中的列的顺序必须相同。
如果教师1和教师2中的name有重复值，union会去重，而union all不去重

示例

SELECT name FROM Teacher1
UNION ALL
SELECT name FROM Teacher2

For update

select 一般情况下不产生锁。如果需要对select加锁，需要在select后面加锁for update。

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

for update的使用场景

如果遇到存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。

比如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是1，而立马另一个进程进行了update将库存更新为0了，而事务还没有结束，会将错的数据一直执行下去，就会有问题。所以需要for upate 进行数据加锁防止高并发时候数据出错。

session1：select * from f where id in （1,2） for update；
session2：select * from f where id in （1,2） for update；等待，select * from f where id in （1,2）；不等待

参考：https://zhuanlan.zhihu.com/p/143866444

四大排名函数

ROW_NUMBER()

　　定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询，
比如查询前10个查询10-100个学生。

　　实例：

　　1.1对学生成绩排序

这里写图片描述
这里number就是每个学生的序号根据studentScore(分数)进行desc倒序

1.2获取第二个同学的成绩信息

这里写图片描述
这里用到的思想就是分页查询的思想在原sql外再套一层select
where t.number>=1 and t.number<=10 是不是就是获取前十个学生的成绩信息纳。

RANK()

　　定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样出现相同的，他们的排名是一样的。下面看例子:

这里写图片描述

当出现两个学生成绩相同是里面出现变化。RANK()是 1 2 2，而ROW_NUMBER()则还是1 2 3，这就是RANK()和ROW_NUMBER()的区别了

DENSE_RANK()

这里写图片描述

NTILE()

　　定义：将数据进行分组

　　一共13行数据，分三组，第一组就是5；

posted @ 2019-03-14 23:03 小名的同学阅读(190) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

小名的同学

MySQL----语法篇

IFNULL

EXISTS和select配饰使用

EXISTS和update配饰使用

EXISTS和Insert配合使用

IN

使用UNION替代IN

EXISTS和In选择

Unique

不存在则插入，存在则更新（on duplicate key update）

先删除旧数据再插入最新的数据（replace into）

避免重复插入（insert ignore into）

Group By

Order by

Case

IF

代替IF

concat

update关联

正则

on和where的用法

执行顺序

Insert into

distinct

date()

UNION和UNION ALL

For update

四大排名函数

公告

　　不存在则插入，存在则更新（on duplicate key update）

　　先删除旧数据再插入最新的数据（replace into）

　　避免重复插入（insert ignore into）

执行顺序　　