【数据库笔记】MySQL 关键字 & 函数

---

0 基础数据

以下为本次应用的三张表，其结构和内容如下：

-- 表1 : T_Score 分数表
create table T_Score
(stu_id int,
lesson_id varchar(255),
score int);

-- 表2 : T_Stu_Profile 学生表
create table T_Stu_Profile
(stu_id int,
stu_name varchar(255),
gender varchar(255),
age int,
class_id varchar(255));

-- 表3 : T_Lesson 课程表
create table T_Lesson
(lesson_id varchar(255),
lesson_name varchar(255));

insert into T_Score values (1,'L001',90),(1,'L002',86),(2,'L001',84),(2,'L004',75),(3,'L003',85),(4,'L005',98);
insert into T_Stu_Profile values (1,'郭东','F',16,'0611'),(2,'李西','M',18,'0612'),(3,'张北','F',16,'0613'),(4,'钱南','M',17,'0611'),(5,'王五','F',17,'0614'),(6,'赵七','F',16,'0615');
insert into T_Lesson values ('L001','语文'),('L002','数学'),('L003','英语'),('L004','物理'),('L005','化学');

1 关键字：EXISTS & NOT EXISTS

• EXISTS关键字：当EXISTS里的条件语句能够返回记录行时，条件为真，返回当前loop到的这条记录；反之，当前loop到的这条记录被丢弃。
• NOT EXISTS关键字：NOT EXISTS与EXISTS相反，也就是当EXISTS条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集。

例1：统计没有参加所有考试的学生

SELECT
	* 
FROM
	t_stu_profile b 
WHERE
	NOT EXISTS ( SELECT * FROM t_score a WHERE a.stu_id = b.stu_id )

2 函数

2.1 count()：计数

• COUNT(*)：统计总行数，不管表列中包含的是空值（NULL）还是非空值；
• COUNT(col_name)：对特定列中具有值的行进行计数，忽略NULL值；
• COUNT(col_order)：效果同上，当col_order=1，则表明对第1列进行计数。

例2：查找各门课程的记录数量

select lesson_id,
	   count(lesson_id) as lesson_nums
from t_score
group by lesson_id
order by lesson_id

2.2 group_concat()：分组聚合

GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ‘_’)

例3：聚合每科的分数

SELECT
	lesson_id,
	group_concat( score ORDER BY score DESC SEPARATOR '-' ) AS scores 
FROM
	t_score
group by lesson_id

2.3 substring_index()：切分

SUBSTRING_INDEX(str, delim, count)

• str：被截取字段
• delim：分隔符
• count：计数，count为正，从左往右数，取第n个分隔符的左边的全部内容；count为负，从右往左数，取第n个分隔符的右边的全部内容

例4：查找每科的最高分（结合例3）

SELECT
	lesson_id,
	substring_index( scores, '-', 1 ) AS max_score 
FROM
	( 
		SELECT 
			lesson_id, 
			group_concat( score ORDER BY score DESC SEPARATOR '-' ) AS scores 
		FROM t_score 
		GROUP BY lesson_id 
	) t

源自：MySQL操作实战（一）：关键字 & 函数