朝花夕拾3------SQL语句

目录

1. 第9课  包含表达式的查询

1.1  知识点

1.2 练习题

2. 第10课 使用聚合查询

2.1 知识点

2.2 练习题

3. 第11课 聚合查询---HAVING子句

3.1 知识点

3.2 练习题

---

1. 第9课  包含表达式的查询

1.1  知识点

除了使用SQL查询和使用原始数据之外，还可以使用表达式在查询中的列值上编写更复杂的逻辑，这些表达式可以使用数字和字符串函数以及基本算法在执行查询时转换值，如下所示：

带表达式的示例查询
SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

由于表达式会使查询更难阅读，因此SELECT在查询的一部分中使用表达式时，其会被赋予描述性别名，使用关键字“AS”，如下：

选择带有表达式别名的查询
SELECT col_expression AS expr_description, …
FROM mytable;

除了表达式之外，常规列甚至表还可以具有别名，使得他们更容易在输出中引用，并作为简化更复杂查询的一部分：

具有列和表名别名的示例查询
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

1.2 练习题

#1.列出所有电影及其合计销售额数百万美元
my:
SELECT Title,Domestic_sales,International_sales  FROM movies
RIGHT JOIN Boxoffice
      ON movie.Id = Boxoffice.Movie_id;

正确的：
SELECT title, (domestic_sales + international_sales) / 1000000 AS gross_sales_millions
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

出错源于审题不清

#2.列出所有电影及其评分百分比

SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

#3.列出偶数年份发布的所有电影

SELECT title, year
FROM movies
WHERE year % 2 = 0;

2. 第10课 使用聚合查询

2.1 知识点

聚合表达式（或函数）可以汇总关于一组行的信息，使用常用的皮克斯数据库，可以使用聚合函数来回答诸如“皮克斯制作了多少电影”？或者“皮克斯每年票房最高的电影是什么”，偏向于更实际化的问题。一组行上的命令如下：

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;

如果没有指定的分组，每个聚合函数将在整个结果行集上运行并返回单个值，与普通表达式一样，为聚合函数提供别名可确保结果便于阅读和处理。

• 下面是常见的聚合函数：

常见的聚合函数

Function	Description	解释
COUNT(*),  COUNT(column)	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.	如果未指定列名，则用于计算组中行数的常用函数。否则，使用指定列中的非NULL值计算组中的行数。
MIN(column)	Finds the smallest numerical value in the specified column for all rows in the group.	查找组中所有行的指定列中的最小数值。
MAX(column)	Finds the largest numerical value in the specified column for all rows in the group.	查找组中所有行的指定列中的最大数值。
AVG(column)	Finds the average numerical value in the specified column for all rows in the group.	查找组中所有行的指定列中的平均数值。
SUM(column)	Finds the sum of all numerical values in the specified column for the rows in the group.	查找指定列中组中行的所有数值的总和。
Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

• 分组聚合功能

除聚合所有行之外，还可以将聚合函数应用于该组中的各个数据组，这将创建于该group by 子句定义的唯一组一样多的结果。句式结构如下：

Select query with aggregate functions over groups
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

2.2 练习题

#1.找出员工在工作室工作的最长时间

SELECT MAX(years_employed) as Max_years_employed
FROM employees;

#2. 对于每个角色，查找该角色中员工雇用的平均年数

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

#3.查找每个建筑物中工作的员工年数

SELECT building, SUM(years_employed) as Total_years_employed
FROM employees
GROUP BY building;

3. 第11课 聚合查询---HAVING子句

如果要在GROUP BY子句在子句之后执行WHERE（过滤分组的行），该如何过滤分组的行呢？————SQL允许添加一个附加HAVING子句来实现这一点，该子句专门用于该GROUP BY子句，允许我们从结果中过滤分组行。

3.1 知识点

选择具有HAVING约束的查询
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

HAVING子句的约束编写方式和WHERE子句相同，可以应用于分组的行中。在示例中，可能不是一个特别有用的结构，但是如果有数百万的具有不同属性的行的数据时，通常可以应用额外的约束来快速理解数据

3.2 练习题

#1.找出工作室中的艺术家人数（没有HAVING子句）

MY:
SELECT Building,ROLE,SUM(Artists) AS SUM_ROLE_ARTISTS FROM employees
GROUP BY ROLE;

正确的：
SELECT role, COUNT(*) as Number_of_artists
FROM employees
WHERE role = "Artist";

#2.找出工作室中每个角色的员工人数

MY:
SELECT role, SUM(ROLE) as Number_of_ROLEs
FROM employees
WHERE IN(Artist,Engineer,Manager)
GROUP BY ROLE;

正确的：
SELECT role, COUNT(*)
FROM employees
GROUP BY role;

#3.查找所有工程师使用的总年数

SELECT role,SUM(YEARS_EMPLOYED) AS TOTLE_YEAR
FROM employees
WHERE role="Engineer";

或者：
SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";

需要思考的是在什么情况下用count，什么时候用sum函数