MySQL 系统学习系列 - SQL 语句 DQL 语句的使用（2）《MySQL系列篇-04》

最新推荐文章于 2024-11-07 11:56:55 发布

小孔_H

最新推荐文章于 2024-11-07 11:56:55 发布

阅读量277

点赞数 9

分类专栏： MySQL 文章标签： mysql 学习 sql

本文链接：https://blog.csdn.net/qq_73220073/article/details/141503701

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

SQL 语句 DQL

聚合函数

1. 聚合

聚合查询：指的是一个函数 [ 聚合函数对一组执行计算并返回单一的值 ]
聚合的目的：为了快速得到统计数据

聚合函数	说明
count(*)	表示计总行数，括号中写*与列名，结果相同
max(列)	表示求此列最大值
min(列)	表示求此列最小值
sum(列)	求此列的和
avg(列)	求此列的平均值
group_concat(列)	按组进行来接数据▲ 【分组查询】

# count函数-通常配合组合一起使用
  # count(*) < count(1) < count(列名【索引列】) --> [执行效率]
    SELECT count(*) FROM stu;

# 其它函数使用基本无异 [略]

# 分组查询[group by 列名]🔺
    SELECT count(*) FROM stu GROUP by age;
    SELECT age,count(*) FROM stu GROUP by age;

2. 外键

使用外键目的： 保证数据参照性完整。通过定义外键约束，关系数据库可以保证无法插入无效的数据
语法：foreign key(列名) references 表名(列)

# 外键定义
	CREATE TABLE IF NOT EXISTS grade ( # 定义一个班级表
		g_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
		g_name VARCHAR(25) NOT NULL
	);
	# 因为一个班级可有多名学生，而一名学生只能有一个班级（所以班级表与学生表是一个一对多的关系，即需要再学生表中加上班级表的主键作为外键）
	CREATE TABLE IF NOT EXISTS students ( # 定义一个学生表
		id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(11) NOT NULL,
		grade_id int, # 定义外键列
		# 定义外键语句
		CONSTRAINT fk_students_grade FOREIGN KEY(grade_id) REFERENCES grade(g_id)
	);

  # 其中 CONSTRAINT fk_students_grade FOREIGN KEY(grade_id) REFERENCES grade(g_id) 语句就为定义外键的语句
      /**其中
          CONSTRAINT(constraint) ：表示定义的意思
          fk_students_grade ：为定义外键的名称（自定）
          FOREIGN KEY(grade_id) ：中的grade_id，表示以该表中grade_id列作为外键
          REFERENCES grade(g_id) ：再REFERENCES后定义与哪一种表的那一列（主键）进行联系（即作为stu表的外键）

          整体意思：
              定义一个名为fk_students_grade的外键，其中以学生表中的grade_id列作为外键列
              以班级表中（grade）的g_id(主键)作为学生表的外键
       */

设置外键的级联关系：on update cascade on delete cascade
- on update：设置数据更新时 <> on delete：设置数据删除时
- cascade该值表示会外键表跟着主键表变化而变化

练习与巩固

# 表结构定义
   CREATE TABLE IF NOT EXISTS grade ( # 定义一个班级表
       g_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       g_name VARCHAR(25) NOT NULL
   );
  # 因为一个班级可有多名学生，而一名学生只能有一个班级（所以班级表与学生表是一个一对多的关系，即需要再学生表中加上班级表的主键作为外键）
   CREATE TABLE IF NOT EXISTS students ( # 定义一个学生表
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(11) NOT NULL,
       grade_id int, # 定义外键列               
       CONSTRAINT fk_students_grade FOREIGN KEY(grade_id) REFERENCES grade(g_id) # 定义外键
   );
  
# 数据插入
   INSERT INTO grade VALUES(0,'JAVA班'),(0,'PYTHON班'),(0,'JS班'),(0,'HTML班'); # 插入班级信息
   INSERT INTO students VALUES(0,"li",4),(0,"hello",4),(0,"wang",4),(0,"kong",1),(0,"deng",3),(0,"2kong",2); # 插入学生信息
   
# 问题解决 【问题：查询班级人数大于等于2的班级所有信息】
   # 非最终语句
      SELECT grade_id,count(*) FROM students GROUP BY grade_id; # 对学生表根据班级id来进行分组查询 [获取每个班级的学生个数]
      SELECT grade_id FROM students GROUP BY grade_id HAVING count(grade_id) >= 2; # 改进上面语句获取班级人数大于等于2的班级
   # 最终语句，可以利用子查询来通过班级id来获取班级人数大于等于2的班级所有信息
      SELECT * FROM grade WHERE g_id IN (SELECT grade_id FROM students GROUP BY grade_id HAVING count(grade_id) >= 2);

3. [扩充知识] having语句▲

having 与 where 语句用法一样
不过 where 是对from后面指定的表进行数据筛选（对原始数据进行筛选）[where中不支持函数的使用]
而 having 是对group by的结果进行筛选（即分组查询后的结果进行筛选）[having中可以支持函数的使用]

需要非常注意的一点：having是对select查询后的数据再进行筛选，所以它只能找到select查找后的结果列

-- 报错，因为找不到name列
 SELECT grade_id FROM students HAVING LENGTH(name)>5;
  
-- 因为having只能找到select结果列，所以必须再select的结果列加上having要使用的对应列，如下
 SELECT grade_id,name FROM students HAVING LENGTH(name)>5; -- 再select查找中加上name列，此时having就能够找到对应的列