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;