最常用MySQL语句的写法

        最近项目上要测试统计类的功能，用到较多的sql查询语句，熟练使用SQL语句，能够提高我们在测试中的工作效率，特别是针对统计图表类的功能，在版本迭代以后，回归该模块的功能，能够很快的知道是否统计正确。

一、删除表或清空表数据

 1、删除表数据+表结构（慎用）

语法：drop table 表名称；

使用场景：不需要该表时，删除多余表或重复的表。（慎用，一般不删表，只清表数据）

例子：DROP TABLE biz_time_table;

2、清空表内的数据

语法1：truncate table 表名称;

使用场景：将表内的数据，进行清空。效率较高，推荐使用

例子：TRUNCATE TABLE biz_time_table;

语法2：delete table 表名称

使用场景：将表内的数据，进行清空。效率较低，一行行删除，不推荐使用

例子：DELETE TABLE biz_time_table;

二、查询表内数据，单表查询、多表查询、联合查询

Left join    左连接

Right join  右连接

Inner join   内连接

Count        统计行数

Sum           求和

Exists/not exists  EXISTS用于检查子查询是否至少会返回一行数据

MAX  返回最大值

in/not in    在或者不在数据范围内

group by           按什么字段分组

Having              过滤条件

order by            排序

Concat （）       字符拼接

date_format（）时间格式转换

Now（）            系统当前时间

Case when else end 判断

入参： set @courseId=1340845286179819521;在sql脚本多次复用的时候可用参数代替，减少修改脚本工作量

1、单表查询具体条件的数据

语法1：select 字段1，字段2,... from 表名称 where 条件

例子1：SELECT * FROM org_classroom WHERE id=1352857098347106305

例子2：# 正在上课的教室数 num1
SELECT COUNT(DISTINCT classroomId) 正在上课的教室数 FROM biz_course_list WHERE del=0 AND endTime IS NULL AND classroomId IS NOT NULL;

2、多表查询：常用语法

# 本学年原型智慧课堂开课数
SELECT bc.collegeId,uoc.`name`,COUNT(bc.collegeId) 本学年院系智慧课堂开课数 FROM biz_course_list bcl 
INNER JOIN biz_attend_code bac ON bcl.id= bac.courseListId 
INNER JOIN biz_teach_class_course tcc ON tcc.teachClassId = bcl.teachClassId
INNER JOIN biz_course bc ON bc.id = tcc.courseId
INNER JOIN userorg.org_college uoc ON uoc.id = bc.collegeId
INNER JOIN biz_term bt ON bt.id=bc.termId
WHERE bcl.del=0 AND bt.id IN (SELECT id FROM biz_term WHERE schoolYear IN (SELECT schoolYear FROM biz_term WHERE del =0 AND currentTerm=1)) AND bcl.classroomId IS NOT NULL
GROUP BY bc.collegeId ORDER BY 本学年院系智慧课堂开课数 DESC,bc.collegeId ASC LIMIT 8;

 三、删除具体条件的数据

语法：delete from 表名 where 条件

例子1：#删除当天的直播相关日志

SET @today="2021-07-01 00:00:00";
DELETE FROM biz_classroom_live_log WHERE createTime > @today;

四、插入数据

语法：insert into 表名称 values(字段1,字段2...)

例子：INSERT INTO biz_teach_class VALUES(2323, 0,'测试插入',NOW(),111,100,123,'ABCD');

插入多条数据：插入的数据需要从其他表查出来，然后再插入进去

INSERT INTO biz_teach_class_user(id,del,createTime,teachClassId,userId,top,property,canBeRemove) 
SELECT CONCAT(res.userId,04),0,NOW(),1460885953102053377,res.userId,0,0,1 FROM (SELECT * FROM userorg.user_information inf WHERE inf.userId >=570 AND inf.userId <= 575) res

CONCAT语法：CONCAT(userid,04)，假设学号是202112，则得到结果是：20211204

INSERT INTO biz_course_list (`id`, `del`, `name`, `createTime`, `endTime`, `teachClassId`) 
select CONCAT('11',LPAD(CAST(@n:=@n+1 as char),8,0)) id,0 del,aa.name,bb.createTime,DATE_ADD(bb.createTime,INTERVAL 1 HOUR) endTime,aa.teachClassid from 
(
select c.id teachClassid,c.name from biz_course a, biz_teach_class_course b,biz_teach_class c
where a.id=b.courseId and b.teachClassid=c.id
and c.id<203001
)aa,
biz_course_list_lhy2 bb,
(SELECT @n:=0) as i 
where 1=1

拼在CONCAT('11',LPAD(CAST(@n:=@n+1 as char),8,0))，LPAD表示拼接在左边，RPAD表示拼接在右边，‘11’表示被拼接的字符，LPAD(CAST(@n:=@n+1 as char)，表示在‘11’的左边拼接自然数n+1,8表示最大位数8位，0，表示不够8位，中间用0替代。例如n+1=2，则得到结果是00000211。

SELECT
    max( CASE contentName WHEN '作品展示' THEN num ELSE 0 END ) '作品展示',
    max( CASE contentName WHEN '资料分享' THEN num ELSE 0 END ) '资料分享',
    max( CASE contentName WHEN '投票' THEN num ELSE 0 END ) '投票',
    ( max( CASE contentName WHEN '广播到学生' THEN num ELSE 0 END ) + max( CASE contentName WHEN '广播到小组' THEN num ELSE 0 END ) ) '屏幕广播'
FROM
    (
    SELECT
        COUNT( * ) num,
        pdc.contentName 
    FROM
        biz_course_list_action cla
        INNER JOIN pub_dict_content pdc ON pdc.contentValue = ABS( cla.type ) 
    WHERE
        cla.del = 0 
        AND cla.type < 0 AND cla.type >= - 17 
        AND pdc.typeId = 18 
        AND cla.createTime BETWEEN ( SELECT startTime FROM biz_term WHERE currentTerm = 1 AND del = 0 ) 
        AND ( SELECT endTime AS maxTime FROM biz_term WHERE currentTerm = 1 AND del = 0 ) 
    GROUP BY
        cla.type 
    ) a;

五、更新数据

语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例子：UPDATE biz_teach_class SET invitationCode='EFGH' WHERE name='测试插入';

以上，基本就是一些常用的语法，需要手动去操作，才能深刻地了解语法得到的效果。