SQL 笔记

字符串拼接

SELECT CONCAT(id, name, age) as result_column from user;   -- 12grig18

SELECT CONCAT_WS(',','1','2','3','4');  -- 指定分隔符拼接，1,2,3,4

group_concat(distinct concat_ws(':',date_format(start_time,'%Y-%m-%d'),tag) order by start_time separator ';') -- 返回一个字符串结果，该结果由分组中的值连接而成，2021-07-02:SQL;2021-07-05:SQL;2021-09-01:算法;2021-09-02:SQL;2021-09-05:SQL

字符串截取

SELECT MID(column_name,start[,length]) FROM table_name;   -- start 起始值是1

left(string,length)  -- 如：left('abcd',3),输入abc
right(string,length)  -- 如：right('abcd',3),输入bcd

limit

select * from table limit num   -- num指返回的条数
select * from table limit start,num   -- start起始值是0，num指返回的条数

将字母转为大写

SELECT UPPER('Allah-hus-w3cschool');    -- ALLAH-HUS-W3CSCHOOL 
select upper(column_name) from table_name;

-- 或者
SELECT UCASE(column_name) FROM table_name;

将字母转为小写

 SELECT LOWER('W3CSCHOOL');   -- w3cschool
 select lower(column_name) from table_name;
 
-- 或者
SELECT LCASE(column_name) FROM table_name;

去除字符串头尾空格

SELECT TRIM('   Sample   ');    -- 'Sample'

SELECT LTRIM('   Sample   ');   -- 'Sample   '

SELECT RTRIM('   Sample   ');   -- '   Sample'

字符串替换函数

update `article` set title=replace(title,'w3cschool','hello');    -- 把数据库表article中的所有title字段里的w3cschool字符串替换成hello。

文本字段值的长度

SELECT LEN(column_name) FROM table_name;

指定小数位数

-- decimals，规定要返回的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name;

日期格式化

SELECT FORMAT(column_name,format) FROM table_name;  

SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM Products;

求余数

select student_id,student_name,score,mod(score,3) as '余数' from student_score_info WHERE id = 8
-- 结果：20210103   黄虎    65.00   2.00

随机数

SELECT * FROM employee_tbl ORDER BY RAND();   -- 对一组记录进行随机化排列

-- 当以某个整数值作为参数来调用的时候，RAND() 会将该值作为随机数发生器的种子。对于每一个给定的种子，RAND() 函数都会产生一列可以复现的数字：
SELECT RAND(1) -- 种子都为1时，多次调用产生的随机数一样

聚合查询，聚合group by、having、order by顺序如下

-- 聚合查询group by中不能使用where条件语句，使用having语句
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

union连接

-- union，不允许重复值
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

-- union all，允许重复值
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

-- 子查询如果使用排序，会失效。但子查询的子查询有效
select
    *
from
    (
        select
            any_value (exam_id) as tid,
            any_value (count(distinct uid)) as uv,
            any_value (count(start_time)) as pv
        from
            exam_record
        group by
            exam_id
        order by
            uv desc,
            pv desc
    ) a
union all
select
    *
from
    (
        select
            any_value (question_id) as tid,
            any_value (count(distinct uid)) as uv,
            any_value (count(submit_time)) as pv
        from
            practice_record
        group by
            question_id
        order by
            uv desc,
            pv desc
    ) b

强制插入记录(唯一索引记录已经存在，更新记录)

replace into examination_info values (null,9003,'SQL','hard',90,'2021-01-01 00:00:00 ');

datetime字段时间的加减

-- 日期相加
delete from exam_record where adddate(start_time,interval 5 minute) > submit_time and score < 60  -- 加5分钟
delete from exam_record where adddate(start_time,interval 1 day) > submit_time and score < 60  -- 加1天
delete from exam_record where adddate(start_time,interval 1 hour) > submit_time and score < 60  -- 加1小时
delete from exam_record where adddate(start_time,interval 1 second) > submit_time and score < 60  -- 加1秒
delete from exam_record where adddate(start_time,interval 1 microsecond) > submit_time and score < 60  -- 加1毫秒
delete from exam_record where adddate(start_time,interval 1 week) > submit_time and score < 60  -- 加1周
delete from exam_record where adddate(start_time,interval 1 month) > submit_time and score < 60  -- 加1月
delete from exam_record where adddate(start_time,interval 1 quarter) > submit_time and score < 60  -- 加1季度
delete from exam_record where adddate(start_time,interval 1 year) > submit_time and score < 60  -- 加1年

--时间相加
select addtime('13:05','02:57')  -- 16:02:00
select addtime('11:05','02:57')  -- 14:02:00


-- 日期相减
select datediff('20191010','20191001')    -- 9

-- 时间相减
select timediff('2019-06-03 12:30:00', '2019-06-03 12:29:30')    -- 00:00:30
等价于
select timediff('12:30:00', '12:29:30')    -- 00:00:30

索引

create index idx_duration on examination_info (duration); -- 普通索引
create unique index uniq_idx_exam_id on examination_info (exam_id); -- 唯一索引
create fulltext index full_idx_tag on examination_info (tag); -- 全文索引

-- 查询索引
SHOW INDEX FROM examination_info

-- 删除索引
ALTER TABLE table_name DROP INDEX index_name  -- mysql
DROP INDEX table_name.index_name  --sql server


-- 全文索引（可重复、可为空，生成全文索引非常耗时和磁盘空间，仅支持char、varchar、text列，普通索引使用模糊匹配%xx索引失效，全文索引可以避免这个问题）
CREATE FULLTEXT INDEX index_info ON tb_student(info);

-- 主键索引（不重复、不为空、不能在已存在表中创建主键索引），建表语句中使用PRIMARY KEY 关键字创建

-- 唯一索引（不重复、可为空）
CREATE UNIQUE INDEX index_id ON tb_student(id);

-- 普通索引（可重复、可为空）
CREATE INDEX index_id ON tb_student(id);

-- 复合索引（左前缀匹配原则,在前面索引查找的基础之上再进行索引查找，这就是复合索引的作用本质）
CREATE INDEX index_na ON tb_student(C1,C2,C3);
-- WHERE c1 = x AND c2 = y AND c3 = z;  组合索引最大化
-- WHERE c1 = x AND c3 = z; C1生效，C3跳过C2所以C3未生效
-- WHERE c1 > x AND c2 = y AND c3 = z; C1是范围查找，所以仅C1生效
-- WHERE c1 = x AND c2 < y AND c3 = z; C2是范围查找，所以仅C1、C2生效
-- WHERE c1 = x AND c3 = z ORDER BY c2 ; 根据左前缀原则，C1生效，C2、C3未生效

join 连接

-- 内连接，INNER JOIN 与 JOIN 是相同的，如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;


-- 左连接，LEFT JOIN 关键字返回左表（table1）中的所有行，即使在右边表（table2）中没有匹配。
SELECT column_name(s)                
FROM table1                
LEFT JOIN table2                
ON table1.column_name=table2.column_name; 

-- 右连接，RIGHT JOIN 关键字返回右表（table2）的所有行，即使在左表（table1）中没有匹配。
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

-- 外部连接， FULL OUTER JOIN关键字返回左表（table1）中的所有行，以及右表（table2）中的所有行
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

去掉一个最大值和一个最小值后，求平均值

select ei.tag,ei.difficulty,format((sum(er.score)-max(er.score)-min(er.score))/(count(*)-2),1) from examination_info ei left join exam_record er on ei.exam_id = er.exam_id where ei.tag = 'SQL' and ei.difficulty='hard' and er.submit_time is not null

小数位处理

SELECT FORMAT(12562.6655,2); -- 12,562.67
SELECT FORMAT(12332.1,4);  -- 12,332.1000
select truncate(4545.1366,2);  -- 4545.13（直接截取，不会四舍五入）
select convert(4545.1366,decimal(10,2));  -- 4545.14
ROUND(748.58, -1);  -- 750
ROUND(748.58, -2);  -- 700
ROUND(748.58, -3);  -- 1000
select ROUND(4545.1366,2);   -- 4545.15

计数count的用法

select 
    count(*) as total_pv,  -- 统计所有记录数量
    count(submit_time) as complete_exam_cnt,  -- 统计submit_time不为空null的数量，统计已完成数据
    count(distinct exam_id,submit_time is not null or NULL)   -- 聚合函数里面加条件，统计已完成的试卷种类数
from exam_record

取出比平均分大的最小值

select
    score as min_score_over_avg
from
    examination_info ei
    left join exam_record er on ei.exam_id = er.exam_id
where
    ei.tag = 'SQL'
    and score >= (
        select
            avg(er.score)
        from
            examination_info ei
            left join exam_record er on ei.exam_id = er.exam_id
        where
            ei.tag = 'SQL'
    )
order by
    score
limit
    1

月总和日均统计

select
    date_format(submit_time,'%Y%m') as submit_month,
    count(question_id) as month_q_cnt,
    any_value(round(count(question_id)/day(LAST_DAY(submit_time)),3)) as avg_day_q_cnt
from
    practice_record
where
    year(submit_time) = 2021
group by submit_month
union all
select 
    '2021汇总' as submit_month,
    count(*) as month_q_cnt,    -- count(*) 总数
    round(count(1)/31,3) as avg_day_q_cnt   -- count(1) 总数
from
    practice_record
where
    year(submit_time) = 2021
order by submit_month

日期格式、日期处理函数

%Y 年，4 位，如：2017 %y 年，2 位，如：17 %M 月名，如：July %m 月名，数字，00-12 %D 带有英文前缀的月中的天：2nd %d 月的天，数值(00-31) %H 小时 (00-23) %h 小时 (01-12)

date_format(submit_time,'%Y%m') 获取年月，如：202307

year(submit_time) 获取年，如：2023

month(submit_time) 获取月，如：7

day(submit_time) 获取天，如：5

last_day(submit_time) 获取时期月份最后一天，如：2023-07-31

case when then else

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

select
    device_id,
    gender,
    case
        when age < 20 then '20岁以下'
        when age between 20 and 24  then '20-24岁'
        when age >= 25 then '25岁及以上'
        else '其他'
    end as age_cut
from
    user_profile

IF( expr1 , expr2 , expr3 )

expr1 的值为 TRUE，则返回值为 expr2 expr1 的值为FALSE，则返回值为 expr3

SELECT IF(TRUE,1+1,1+2);
-> 2
 
SELECT IF(FALSE,1+1,1+2);
-> 3

-- 方式一
select
    if (
        age < 25
        or age is null,
        '25岁以下',
        '25岁及以上'
    ) as age_cut,
    count(*) as number
from
    user_profile
group by
    age_cut


-- 方式二  
select
    '25岁以下' as age_cut,
    count(device_id) as number
from
    user_profile
where
    age < 25
    or age is null
union
select
    '25岁及以上' as age_cut,
    count(device_id) as number
from
    user_profile
where
    age >= 25;

IFNULL 表达式

IFNULL( expr1 , expr2 )

如果expr1不为空，直接返回expr1；

如果expr1为空，返回第二个参数 expr2

NULLIF 表达式

NULLIF(expr1,expr2)

如果两个参数相等则返回NULL，否则返回第一个参数的值expr1

视图

-- 创建视图
CREATE VIEW student_backup as 
select student_name,student_id,course from student_score_info WHERE score>60

-- 从视图中查询
SELECT * FROM student_backup

-- 删除视图
DROP VIEW student_backup

插入查询结果

INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

INSERT INTO xiaoshu_backup SELECT * FROM xiaoshu

字符串拼接

字符串截取

limit

将字母转为大写

将字母转为小写

去除字符串头尾空格

字符串替换函数

文本字段值的长度

指定小数位数

日期格式化

求余数

随机数

聚合查询，聚合group by、having、order by顺序如下

强制插入记录(唯一索引记录已经存在，更新记录)

datetime字段时间的加减

索引

join 连接

去掉一个最大值和一个最小值后，求平均值

小数位处理

计数count的用法

取出比平均分大的最小值

月总和日均统计

日期格式、日期处理函数

case when then else

if

视图

插入查询结果