SQL查询技巧：DISTINCT、JOIN、过滤与聚合函数应用,-优快云博客

本文链接：https://blog.youkuaiyun.com/seanbill/article/details/134507550

1.唯一
DISTINCT
2.排序
ORDER BY xxx; 升序
ORDER BY xxx desc; 降序
题目描述

select DISTINCT author_id  as id from Views where author_id = viewer_id ORDER BY author_id;

3.length();
字符串长度

select tweet_id from Tweets where length(content) > 15

4.左连接
a LEFT JOIN b ON …
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a)的记录将会全部表示出来，而右表(b)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL
右连接相反
a RIGHT JOIN b ON

SELECT 
    EmployeeUNI.unique_id, Employees.name
FROM 
    Employees
LEFT JOIN 
    EmployeeUNI 
ON 
    Employees.id = EmployeeUNI.id;

//统计学生们参加各科测试的次数题目链接

select 
    Students.student_id ,Students.student_name ,Subjects.subject_name,
    (select
        count(e.subject_name)  
    from
        Examinations as e
    where e.student_id = Students.student_id and e.subject_name = Subjects.subject_name)  as  attended_exams

from 
    Students 
join
    Subjects
group by student_id,subject_name
order by student_id,student_name,subject_name;

WHERE子句和HAVING子句在SQL查询中都用于设置条件进行筛选，但它们在过滤数据和使用场景上存在显著的区别。
	1.执行顺序:在查询过程中，首先执行FROM操作，然后是WHERE过滤，接着进行分组(GROUP BY) 和聚合函数计算之后是HAVING过滤，然后是排序 (ORDER) ，最后选择 (SELECT) 结果。
	2.过滤时机:WHERE子句在数据分组前进行过滤，即它在聚合函数计算之前对数据行进行筛选。这意味着WHERE过滤掉的数据不包含在分组中。相反，HAVING子句在数据分组后才进行过滤，即它作用于聚合函数计算后的数据行。
	3.使用条件:由于WHERE子句在聚合函数之前执行，因此它不能使用聚合函数作为过滤条件。而HAVING子句可以与聚合函数 (如SUM、MIN、MAX、AVG、COUNT等) 一起使用，对分组后的数据进行进一步的筛选

5.UNION
组合两个或更多SELECT语句的结果集

SELECT 
    employee_id , department_id 
FROM 
    Employee 
GROUP BY employee_id
HAVING COUNT(primary_flag) = 1
UNION
SELECT 
    employee_id , department_id 
FROM 
    Employee 
WHERE primary_flag = 'Y'

6.聚合函数

sum()
count()
min()
max()
avg()

7.清理数据

PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 0 DAY);
OPTIMIZE TABLE `account`;
RESET QUERY CACHE;

8.高级字符串函数
concat() 连接
upper() 字符串变大写
lower() 字符串变小写
substring() 字符串截取子串
例子: 将所有名字改为首字母大写

select 
    user_id,
    concat(upper(substring(name,1,1)),lower(substring(name,2))) as name  
from
    Users
order by user_id

9.正则表达式

select 
    patient_id,
    patient_name,
    conditions   
from
    Patients
where 
    conditions  regexp '\\bDIAB1.*'

10.limit
查询第二高薪水

select DISTINCT
    salary as SecondHighestSalary 
from Employee 
order by salary desc 
limit 1,1

11.GROUP_CONCAT
将组中的字符串连接成为具有各种选项的单个字符串

SELECT 
    sell_date,
    COUNT(DISTINCT(product)) AS num_sold, 
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM 
    Activities
GROUP BY 
    sell_date
ORDER BY 
    sell_date ASC

12.sum函数 + over函数

例子:最后一个能进入巴士的人

select 
    person_name
from 
(
    select 
        *,
        sum(weight) over(order by turn) as sumWeight
    from 
        Queue
) as t
where 
    sumWeight <= 1000
order by 
    sumWeight desc
limit 
    1

//向前6行

sum(amount) over(order by visited_on rows 6 preceding)
sum(amount) over(partition by tag order by visited_on rows between 6 preceding and CURRENT row)

//向后6行

sum(amount) over(partition by tag order by visited_on desc rows between CURRENT row and 6 following)

13.case
交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (
        SELECT
            COUNT(*) AS counts
        FROM
            seat
    ) AS seat_counts
ORDER BY id ASC;

其他

PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 0 DAY);

OPTIMIZE TABLE `xxxxx`;

RESET QUERY CACHE;

select 
	a.`id`,a.`account`,a.`name`,a.`level`
FROM
	characters as a
left JOIN
	item_bank_instance as b
ON
	a.guid = b.owner_guid
WHERE b.guid is NULL and a.`level` > 0
ORDER BY a.`level` desc;

TRUNCATE TABLE xxxxx

show ENGINEs;

show variables like 'innodb_file_per_table';

SHOW GLOBAL STATUS LIKE 'Com_______';

show variables like 'slow_query_log';

SELECT @@have_profiling;

SELECT @@profiling;

show profiles;

show profile for query 52;

show profile cpu for query 52;

#直接在selec语句之前加上关键字explain/desc
EXPLAIN SELECT account FROM characters WHERE name = 'xxx';

索引失效的五种情况：

1. 索引列上进行函数运算；

2.字符串不加引号引起隐式类型转换导致索引失效；

3.模糊查询时在前面加了%设定前面进行模糊匹配；

4.or连接的条件，一侧有索引一侧没有索引；

5.mysql自己评估发现走全表扫描比走索引快，此时索引失效；

https://dbeaver.io/

https://github.com/Light-City/CPlusPlusThings

https://github.com/changkun/modern-cpp-tutorial

https://github.com/wuye9036/CppTemplateTutorial

https://github.com/Alinshans/MyTinySTL

https://github.com/microsoft/calculator

mysql常用函数