MySQL----实操篇

 表内容操作： 

1、增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
INSERT INTO 表 VALUES (DEFAULT,"xsss",DEFAULT) //如果不填写字段，values后面的所有的字段都需要填写，不需要写的，可以填default

SELECT LAST_INSERT_ID(); //返回主键

2、删

删除数据一般不使用下面的sql语句,我们使用假性删除,给每一行数据加上一个是否删除字段,删除的时候把这个字段变成0,查询的时候判断条件为1

delete from 表      #删除表中的所有的数据
delete from 表 where id＝1 and name＝'alex'
DELETE FROM tbuser WHERE id IN (12,13)
DELETE FROM tbuser WHERE id BETWEEN 16 AND 20

3、改

UPDATE 表 set name ＝ 'alex' where id>1
UPDATE employee SET dept = dept+1 WHERE id = 1;  //对数据库中的dept字段自增1

4、查

select * from 表              
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

SELECT COUNT(*) FROM t_user WHERE username="yy";  //返回查询的数目
SELECT * FROM logininfo LIMIT 4,10;               //第5行数据开始一个10条数据 limit 4，-1（最后一行），如果数据不够10条，直接返回实际的数目

4.1子查询

SELECT 
  *,
  (SELECT 
    ordername 
  FROM
    u_order 
  WHERE t_user.`order_id` = u_order.`id`) AS ordername    //根据t_user.order_id 来关联查询的
FROM
  t_user ;

5、其他

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
 
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 0,5           - 钱5行(从第0行开始)，和limit 5 一样
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
    ORDER BY colum asc IF(ISNULL(colum),0,1) //null被强制放在最前，不为null的按声明顺序[asc|desc]进行排序
　　 ORDER BY colum asc IF(ISNULL(colum),1,0) //null被强制放在最后，不为null的按声明顺序[asc|desc]进行排序
 
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前
 
f、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

 

示例

       

　　计算每一个同学的总成绩从大到小排名（显示字段有name，总成绩）

　　　　不加DESC，默认从小到大排序(ASC)

SELECT NAME,SUM(score) AS t FROM u GROUP BY NAME ORDER BY t DESC

　　计算每一个同学的最高成绩（显示字段有name，总成绩）

SELECT NAME,MAX(score) AS t FROM u GROUP BY NAME

 

 

示例

　　需求，A表关联了B表，如果给A表插入数据

　　就按照正常的逻辑插入数据，但是如果插入的id在B表中找不到，就会报错

INSERT INTO employee(NAME,dept) VALUE("小明",1); //1是B表的id

 

示例

　　关联表的问题，如果from表的时候取了别名，后面只能使用 别名.id

SELECT * FROM realauth AS r  LEFT JOIN logininfo ON r.`applier_id` = logininfo.`id`

 

示例

　　一张表关联多张表

SELECT r.id,ap.id AS ap_id,ap.username,au.id AS au_id,au.username FROM realauth AS r LEFT JOIN logininfo AS ap ON r.`applier_id` = ap.`id` LEFT JOIN logininfo AS au ON r.`auditor_id` = au.`id`

 

示例

　　查出字段不为空和为空的数据

SELECT * FROM userfile WHERE fileType_id IS NOT NULL;
SELECT * FROM userfile WHERE fileType_id IS NULL;

 

示例

    <select id="getClientSchedules" resultMap="CourseScheduleMap">
        SELECT cs.id as scheduleId,ci.courseName,tch.userName teacherName,sv.venueName,sc.campusName,
        cs.weekTimes,cs.beginTimes,cs.endTimes,cs.rebuildLim,cs.genderLim,cs.totalLim,cs.lastLim,
        cs.totalCount,cs.maleScale,cs.maleCount,cs.femaleScale,cs.femaleCount,stime.`shortName` beginTimesShortName,etime.`shortName` endTimesShortName
        FROM (select * from course_schedule where status = 1 and schoolId = #{schoolId}
        <if test="courseId != null">
            and courseId = #{courseId}
        </if>
        <if test="teacherId != null">
            and teacherId = #{teacherId}
        </if>
        <if test="beginTimes != null">
            and beginTimes = #{beginTimes}
        </if>
        <if test="endTimes != null">
            and endTimes = #{endTimes}
        </if>
        <if test="weekTimes != null">
            and weekTimes = #{weekTimes}
        </if>
        <if test="rebuildLim != null and rebuildLim==2">
            and (rebuildLim = 0 or rebuildLim = 2)
        </if>
        <if test="rebuildLim != null and rebuildLim==1">
            and (rebuildLim =0 or rebuildLim = 1)
        </if>
        ) cs
        LEFT JOIN user_info tch ON cs.teacherId= tch.id
        LEFT JOIN course_info ci ON cs.courseId=ci.id
        LEFT JOIN school_venue sv ON cs.venueId = sv.id
        LEFT JOIN school_campus sc ON sv.campusId = sc.id
        LEFT JOIN school_year sy ON cs.yearId = sy.id
        LEFT JOIN school_times_info stime ON stime.`times` = cs.`beginTimes` and stime.status=1 and cs.schoolId= stime.schoolId
        LEFT JOIN school_times_info etime ON etime.`times` = cs.`endTimes` and etime.status=1 and cs.schoolId= etime.schoolId
        LEFT JOIN course_schedule_depart dep ON cs.id= dep.scheduleId
        LEFT JOIN course_schedule_major maj ON cs.id= maj.scheduleId
        LEFT JOIN (select * from user_info where id = #{stuId}) stu ON cs.schoolId= stu.schoolId
        LEFT JOIN school_class scl ON stu.classId= scl.id and scl.status=1
        LEFT JOIN course_class_selection ccs ON ccs.classId = stu.classId

        where sy.isThisYear=1 and (dep.departId is null or dep.departId=stu.departmentId)
        and (maj.majorId is null or maj.majorId=stu.majorId)
        and (ccs.id is null or (cs.weekTimes=ccs.weekTimes and cs.beginTimes>=ccs.beginTimes and ccs.endTimes>=cs.endTimes))
        and ((stu.userSex=0 and  genderLim!=1) or (stu.userSex=1 and  genderLim!=2))
    </select>

 

获取数据库中时间的后几天

语法结构

DATE_SUB(date,INTERVAL expr type)
DATE_ADD(date,INTERVAL expr type)
select * from table where date_sub('2019-04-03',INTERVAL  1 year);

SELECT DATE_ADD(weekBegin,INTERVAL 1 DAY) FROM school_week WHERE WEEK>=1 AND WEEK<=4 AND yearId=22
SELECT DATE_SUB(weekBegin,INTERVAL -1 DAY) FROM school_week WHERE WEEK>=1 AND WEEK<=4 AND yearId=22

比较时间(更多参考:https://www.cnblogs.com/Darkqueen/p/9264087.html)

https://blog.csdn.net/qq_23375733/article/details/88533006

    select
    *
    from attend_record
    where status = 1 and schoolId=#{schoolId} and stuId = #{stuId}  and unix_timestamp(#{signInTime})> unix_timestamp(signInTime)

 

 

查询重复数据

1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

select Email from Person group by Email having count(Email) > 1

 

查询id最大的一条数据

select * from table where id=(select MAX(id) from table where xx)

SELECT * FROM 表名 ORDER BY id DESC LIMIT 0,1

　　

 

分数排名

　　题目：letcode算法（https://leetcode-cn.com/problems/rank-scores/）

　　下面这个应该只能支持mysql8.x

SELECT Score,
dense_rank() over(order by Score desc) as 'Rank'
FROM Scores

　　方式2

select

a.score as Score,

count(DISTINCT b.score) AS Rank # 统计b表符合条件的不重复的分数的数量作为排名

FROM scores a join scores b

where b.score >= a.score # 条件是这个分数不小于我，因为a、b表数据相同，所以排名值最小是1

group by a.id # a表中每个数据都进行排名

order by a.score DESC # 最后按分数（跟排名一样）降序排列

 

获取薪水表中第二高的薪水

　　题目：letcode算法（https://leetcode-cn.com/problems/second-highest-salary/comments/）

　　思路1：从大到小排序，取第二

#方式1
select distinct salary from Employee order by salary desc limit 1,1
#优化[如果不存在结果，返回null]
select (select distinct salary from Employee order by salary desc limit 1,1) as SecondHighestSalary

　　思路2：不是最大数的最大数

SELECT max(Salary) SecondHighestSalary
 FROM Employee  
where Salary != (select max(Salary) from Employee ); 

 

换座位

　　题目：https://leetcode-cn.com/problems/exchange-seats/

　　思路：直接调换id，然后再进行排序【直接对查询出来的ID进行加减操作，变换ID的顺序】

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

 

查询一张表中状态0的数量和状态1的数量

select DJZT,count(1) as num from  XM_ZLWT_JCJLMX group by DJZT;

　　

树形结构表的查询

使用sql语句，构建树

　　缺点，无法构建不确定层级的树

<mapper namespace="com.aepcmis.ht.jc.mapper.BkdyMapper">
    <resultMap id="BaseResultMap" type="com.aepcmis.common.entity.Tree">
        <id column="BKDY_PKID" jdbcType="VARCHAR" property="id"/>
        <result column="BKDY_PPKID" jdbcType="VARCHAR" property="pid"/>
        <result column="BKMC" jdbcType="VARCHAR" property="text"/>
        <collection property="children" ofType="com.aepcmis.common.entity.Tree">
            <id column="BKDY_PKID2" jdbcType="VARCHAR" property="id"/>
            <result column="BKDY_PPKID2" jdbcType="VARCHAR" property="pid"/>
            <result column="BKMC2" jdbcType="VARCHAR" property="text"/>
        </collection>
    </resultMap>
    <select id="queryTree" resultMap="BaseResultMap">
SELECT
   b1.BKDY_PKID,
   b1.BKDY_PPKID,
   b1.BKMC,

   b2.BKDY_PKID as BKDY_PKID2,
   b2.BKDY_PPKID as BKDY_PPKID2,
   b2.BKMC as BKMC2
FROM
   HT_JC_BKDY b1 left join  HT_JC_BKDY b2 on b1.BKDY_PKID = b2.BKDY_PPKID
WHERE B1.BKDY_PPKID is null
</select> 
</mapper>

将list查询出来递归构建树

public List<TreeNode> transDepartment(List<Qydy> rootList){
        List<TreeNode> nodeList = new ArrayList<>();
        for (Qydy qydy : rootList) {
            TreeNode treeNode = new TreeNode();
            treeNode.setId(qydy.getQYDY_PKID());
            treeNode.setText(qydy.getQYMC());
            treeNode.setAttributes(qydy);
            //表明是一级父类
            if (1==qydy.getCJ()){
//                treeNode.setState("open");
                nodeList.add(treeNode);
            }
            treeNode.setChildren(setChild(treeNode.getId(),rootList));
        }
        return nodeList;
    }
    public List<TreeNode> setChild(String id, List<Qydy> list ){
        List<TreeNode> childList = new ArrayList<>();
        for (Qydy qydy : list) {
            TreeNode treeNode = new TreeNode();
            if (id!=null && id.equals(qydy.getQYDY_PPKID())){
                treeNode.setId(qydy.getQYDY_PKID());
                treeNode.setText(qydy.getQYMC());
                treeNode.setParentId(id);
                treeNode.setState("close");
                treeNode.setAttributes(qydy);
                childList.add(treeNode);
            }
        }
        for (TreeNode department : childList) {
            List<TreeNode> treeNodes = setChild(department.getId(), list);
            if (treeNodes.size()>=1){
                department.setState("close");
            }
            department.setChildren(treeNodes);
        }
        return childList;
    }

　　

删除树

@Override
    public void DelByPid(List<String> objects) {

        //1 创建list集合，用于封装所有删除菜单id值
        List<String> idList = new ArrayList<>();
        //2 向idList集合设置删除菜单id
        for (String id:objects){
            //把当前id封装到list里面
            idList.add(id);
            this.selectPermissionChildById(id,idList);
        }
        qydyMapper.deleteBatchIds(idList);
    }

    private void selectPermissionChildById(String id, List<String> idList) {
        //查询菜单里面子菜单id
        QueryWrapper<Qydy>  wrapper = new QueryWrapper<>();
        wrapper.eq("QYDY_PPKID",id);
        wrapper.select("QYDY_PKID");
        List<Qydy> childIdList = qydyMapper.selectList(wrapper);
        //把childIdList里面菜单id值获取出来，封装idList里面，做递归查询
        childIdList.stream().forEach(item -> {
            //封装idList里面
            idList.add(item.getQYDY_PKID());
            //递归查询
            this.selectPermissionChildById(item.getQYDY_PKID(),idList);
        });
    }

　　

统计各班级男女生人数

select banji,count(1) 班级人数,sum(case when xingbie='1' then 1 else 0 end ) 男生人数,sum(case when xingbie='2'  then 1 else 0 end ) 女生人数
from biyelunwen_xuesheng
group by banji