SQL常用语法（基础）

[[TOC]] SQL常用语法

1、查询结果去重

1.1、 distinct 去重

select distinct key from table; 

1.2、 group by 去重

select key from table group by key;

1.3、拓展(统计重复的数据)

select key from table group by key having count(*) > 1;

2、查询限制返回的行数

2.1、使用 Limit 关键字

# 查询前2条记录
select key from table limit 2;

2.2、使用 in 选择

# 查询前两条数据
select key from table where id in (1,2);

2.3、附加（将查询列重新命名返回）

select key as nickName from table;

3、查找某个范围内的值

3.1、使用between ... and...

# 查找在A和B之间的值（包含A和B）
select key1, key2 from table where key between A and B;

3.2、使用 and 进行连接

select key1, key2 from table where key >= ? and key <= ?;

#貌似这里用 && 替代 and 也是可以执行的
select key1, key2 from table where key >= ? && ket <= ?;

4、基础排序

对sql中的排序来说，无论进行升序还是降序，都需要使用order by

4.1、对多个字段进行升序排序

# 对关键字key1,key2进行升序
select * from table order by key1,key2;

4.2、先key1先进行升序,再对key2进行降序

select * from table order by key1, key2 desc;

4.3、对两个字段都进行降序

select * from table order by key1 desc,key2 desc;

5、基础操作符

5.1、查找除复旦大学的用户信息

可以使用三种排除的方式

1. not in ('XXXXX');
2. not like XXXXX;
3. != 'XXXXX';

select * from table where university not in ('复旦大学');
select * from table where university not like '复旦大学';
select * from table where university != '复旦大学';

5.2、查询某个值是否为空

最好不要去使用普通的比较运算符。

# 查询某个值不为空的情况
select * from table where key is not null;

#附加（若要查询某个值为空的情况,使用is null）
select * from table where key is null;

5.3、使用多重条件查询

一般用 OR 和 AND 比较多

# 只有所有的布尔表达式全为真的时候，才为真；有一个是假的话，都是假。
布尔表达式1 AND 布尔表达式2 AND … AND 布尔表达式n

# 只要有一个是真，那么其余的都是真
布尔表达式1 OR 布尔表达式2 OR … OR 布尔表达式n

5.4 查看学校名称中包含某个关键字的信息

一共两种方式：一个是用like 还有一个使用正则表达式 RegExp

# 用 like 进行匹配
select * from table where university like '%key%'

# 使用正则表达式
select * from table where university regexp 'key';

5.5 查找某个字段的最大值

有两种方式

1. 使用 max() 进行取最大值
2. 使用 降序排序， key desc,然后再取出第一条数据即可 limit 1

# 使用Max()函数
select max(key) from table;

# 使用降序排序，然后取出第一个
select key from table order by key desc limit 1;

5.6 保留小数点后一位并且计算平均值

保留最终的小数点后一位的话，可以使用 round(key,1)函数 计算平均值的话，使用avg函数 统计总数的话，使用count()函数

select count(gender),round(avg(gpa),1) from user_profile where gender = 'male';

5.7 按学校和性别分组

分组排序使用 group by

select gender,university,id from table group by gender,university;

5.8 分组过滤

分组过滤的时候，常常使用group by 搭配having 一起使用，在having子句中可以使用统计函数，但是在where子句中不能使用

select * from table group by university having avg(question) < 5;

5.9 分组过滤结合排序

先按照学校进行过滤，再按照各个学校中的发帖数进行顺序排序，对发帖数的的平均值取后4位小数。

select university,avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg(question_cnt);

6、多表查询

6.1、子查询

查询两种不同的表中的数据，通常使用table1 join table2 on 条件（如where order by 等等）进行连接

select xx(要查询的内容)xx
from table1 t1
join table2 t2
on t1.id = t2.uid
where university = '浙江大学'
order by t2.xxx asc;[

6.2 链接查询

统计每个学校的答过题的用户的平均答题数

select up.university,(count(qpd.question_id)/count(distinct(qpd.device_id)))
from user_profile up join question_practice_detail qpd
on up.device_id = qpd.device_id
group by university
order by university;

统计每个学校各难度的用户平均刷题数

# 1、首先是根据学校来进行分组
  2、然后根据难度来进行分组
  3、对没道题目的做题量来进行分组
  
SELECT
	university,
	difficult_level,
	COUNT(qpd.`question_id`) / COUNT(DISTINCT(qpd.device_id)) AS avg_answer_cnt
FROM
	question_detail qd
JOIN 
	question_practice_detail qpd
ON
	qpd.`question_id` = qd.`question_id`
JOIN 
	user_profile up
ON
	up.`device_id` = qpd.`device_id`
GROUP BY
	university,difficult_level;

统计是山东大学每个用户的平均刷题数量

1. 也是先进行分组，对学校和难度进行分组，使用group by
2. 只不过这个要找出特定的学校，所以使用group by 后面可以跟上having进行寻找

SELECT
	up.university,
	qd.difficult_level,
	COUNT(qpd.`question_id`) / COUNT(DISTINCT(qpd.device_id)) AS avg_answer_cnt
FROM
	user_profile up
JOIN
	question_practice_detail qpd
ON
	up.`device_id` = qpd.`device_id`
JOIN
	question_detail qd
ON
	qpd.`question_id` = qd.`question_id`
GROUP BY
	university,difficult_level
HAVING university = '山东大学';

6.3 组合查询

先查看学校为山东大学的或者性别为男的，结果不要求去重，但是输出顺序的时候，先输出山东大学的，再输出性别为男的

# 使用 union all 进行将两个查询结果合并起来

SELECT device_id,gender,age,gpa FROM user_profile WHERE university = '山东大学'
UNION all
SELECT device_id,gender,age,gpa FROM user_profile WHERE gender = 'male';

• 拓展：如果如果需要将两个查询出来的结果进行去重的话，可以使用 union

7、常用的函数

7.1 条件函数

用的比较多的有3个

1. if(判断表达式,判断成功,判断不成功的)
2. IFNULL(V1,V2) 函数，如果V1不为空，则直接返回V1
3. case when.. then... else...end

（例题1）计算25岁以上和25岁以下的用户数量

# 使用 if 来进行判断
SELECT IF(age >= 25,'25岁及以上','25岁以下') AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;

# 使用case ... when...then.. else... end 来判断
SELECT (CASE WHEN age >= 25 THEN '25岁及以上' ELSE '25岁以下' END) AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;

（例题2）查看不同年龄段的用户明细

SELECT
	device_id,gender,
	CASE
		WHEN age >= 25 THEN '25岁及以上'
		WHEN age BETWEEN 20 AND 24 THEN '20-24岁'
		WHEN age < 20 THEN '20岁以下'
		ELSE '其它' END AS age_cut
FROM
	user_profile;

7.2 日期函数

常用的几个 year(),month(),day()

例（1）、计算用户8月每天练习的数量

# 1、首先是每天对应不同的刷题数量，最后要用到group by
  2、统计年，月，日，使用对应的函数即可
SELECT 
	DAY(DATE) day,COUNT(question_id) question_cnt
FROM
	question_practice_detail
WHERE
	YEAR(DATE) = 2021
AND
	MONTH(DATE) = 8
GROUP BY
	DAY(DATE);

7.3 文本函数

substring_index(str,delim,count)

1. str表示的是字符串
2. delim表示的分割的符号
3. count表示的是位置，正数如1，从左边开始第一个；负数，从右边开始第一个

# 对一个文本中的男生和女生的数量进行筛选，选出有多少男生和女生

SELECT SUBSTRING_INDEX(PROFILE,',',-1) AS gender,COUNT(*) AS number
FROM user_submit
GROUP BY gender;

从文本框中截取出年龄（168cm,45kg,22,female），并按照年龄进行降序排序

# 使用两次 substring_index()进行截取即可
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(PROFILE,',',3),',',-1) AS age,COUNT(*) AS number
FROM
user_submit
GROUP BY age
ORDER BY age DESC;

7.4 窗口函数

窗口函数的功能

1. 可以同时进行分组和排序
2. 行数不会发生改变（可以用在某一组内进行排序）
3. 注：这个窗口函数只适用于MySQL8.0以上，MySQL5.6版本不支持

# 串口函数的语法 (over后面没有空格)
select 
	<串口函数> over(partition by <用户分组的列名>,
                	order by <用于分组的列名>) as 别名
from table;

# 可以放置两种函数
1、窗口函数：rank(),dense_rank(),row_number()，括号内不需要加额外参数
2、聚合函数：sum(列名),min(列名),max(列名),avg(列名),count(列名)，括号内需要加上参数

# 找出每个学校gpa最低的同学

select
    device_id,
    university,
    gpa
from
    (select device_id,
            university,
            gpa,
            row_number() over(partition by university
                              order by gpa asc) rk
    from
        user_profile)a
 where
     a.rk = 1;