数据库学习总结

Mysql学习总结

汇总数据

聚集函数：

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

例：

AVG函数：

select avg(grade) from topic;

COUNT函数:

select count(grade) from topic;

MAX函数：

select max(grade) from topic;

MIN函数：

select min(grade) from topic;

SUM函数：

select sum(grade) from topic;

以上的五个函数都有以下用法

• 对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
• 只包含不同的值，指定DISTINCT参数

如果指定列，那么distinct只能用于count函数，且distinct只能用于列名。

例：

select count(distinct *) from topic; (X)

select count(distinct grade) from topoc; (√)

分组数据

创建分组

例：

select homeworkid,count(*) as num from topic group by homeworkid;

注意：

1.GROUP BY 子句可以包含任意数目的列，使得能对分组进行嵌套，为数据分组提供更细致的控制。

2.如果GROUP BY子句中中嵌套了分组，数据将在最后规定的分组上进行汇总。

3.GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

4.如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

5.GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

举例：

假设我们有以下员工表格（employees）：

employee_id	department_id	salary
1	1	50000
2	1	60000
3	2	45000
4	2	55000

现在，我们想要按部门计算平均工资和每个部门的员工数目。如果我们使用以下查询：

sqlCopy CodeSELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

结果将是：

department_id	avg_salary
1	55000
2	50000

这个查询计算了每个部门的平均工资，但没有计算每个部门的员工数目。现在，如果我们想要同时显示平均工资和员工数目，我们需要修改查询：

sqlCopy CodeSELECT department_id, COUNT(employee_id) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

结果将是：

department_id	num_employees	avg_salary
1	2	55000
2	2	50000

在第二个查询中，我们不仅计算了每个部门的平均工资，还计算了每个部门的员工数目。这就是在SELECT语句中引入了另一个列（num_employees）的区别。

过滤分组

过滤分组可以只显示满足条件的组，过滤分组使用的关键字是having。

例：（过滤掉分数大于5的行，然后按照homeworkid对挑选出来的数据进行分组，最后过滤掉组中数据小于3的组）

select homeworkid,sum(grade) as sum from topic where grade<6 group by homeworkid having count(*)>2;

SELECT子句顺序

SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	要查找数据的表	是
WHERE	要查找的数据的特征	仅在从表选择数据时使用
GROUP BY	分组说明	尽在按组计算聚集是使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

数据库运行顺序：FROM->WHERE->GROUP UP->HAVING->SELECT->ORDER BY->LIMIT;

子查询

在主查询的每一行中再次执行查询（允许查询不同的表中的信息）。

注意：

当使用子查询需要查询其他表中的数据时，为了区分列所在的表，子查询中的列名有格式上的要求（表名.列名）

例：

select id,homeworkid,(select studenttopic.grade from studenttopic where studenttopic.topicid=topic.id) as grade from topic;

联结表

表的连结是通过共享表中的一个或多个列的值，来将两个或多个表关联起来的过程。

外键：某一个表里的一列，是另一个表的主键，表示了两个表的关系。

联接类型

内联结：返回两个表中满足联结条件的数据

左联结：返回左边表中的所有数据和右边表中满足联结条件的数据。

右联结：返回右边表中的所有数据和左边表中满足联结条件的数据。

外连接：返回两个表中的所有数据。

自联结：是指在单个表内进行联结操作的情况，即将表中的一部分与该表的另一部分进行联结。自联结通常用于需要比较同一表中不同行之间的数据的情况。

自然联结：是在两个表中具有相同名称的列之间进行联结的操作。

例：

假设我们有一个名为 "Students" 的表，其中包含学生的信息，以及一个名为 "Grades" 的表，其中包含学生的考试成绩。我们希望获取所有学生的信息，以及他们可能的考试成绩。

Students 表：

Student_ID	Name	Age
1	John	20
2	Alice	22
3	Bob	21

Grades 表：

Student_ID	Subject	Grade
1	Math	A
2	Science	B
4	English	A

内联结：

select students.name,grades.subject,grades.grade

from students

inner join grades on grades.student_id=students.student_id;

name	subject	grade
John	Math	A
Alice	Science	B

左联结：

select students.name,grades.subject,grades.grade

from students

left join grades on grades.student_id=students.student_id;

name	subject	grade
John	Math	A
Alice	Science	B
Bob	NULL	NULL

右联结：

select students.name,grades.subject,grades.grade

from students

right join grades on grades.student_id=students.student_id;

name	subject	grade
John	Math	A
Alice	Science	B
NULL	English	A

外联结：

select students.name,grades.subject,grades.grade

from students

full join grades on grades.student_id=students.student_id;

name	subject	grade
John	Math	A
Alice	Science	B
Bob	NULL	NULL
NULL	English	A

两个表可以用where语句进行联结，但不建议使用。

例：

SELECT vend_name, prod_name, prod_price

FROM vendors, products

WHERE vendors.vend_id = products.vend_id

ORDER BY vend_name, prod_name;