又要继续学习了,卑
在查询中使用表达式
其实在SQL中可以用col_name的地方,都可以用表达式 来指定对属性进行一定的计算或处理。
表达式可以对 数字运算,对字符串运算,也可以在表达式中只包含常量不包含col_name(如:SELECT 1+1)
#包含表达式的例子
SELECT time-birth AS age
FROM data
WHERE ABS(age ) * 10.0 <60
(条件要求这个属性绝对值乘以10之后小于60);
其中:AS
关键字, 来给表达式取一个别名.
实际上AS不仅用在表达式别名上,普通的属性列甚至是表(table)都可以取一个别名,
例题:
Table: Boxoffice (Read-Only)
Movie_id | Rating | Domestic_sales | International_sales |
5 | 8.2 | 380843261 | 555900000 |
14 | 7.4 | 268492764 | 475066843 |
8 | 8 | 206445654 | 417277164 |
12 | 6.4 | 191452396 | 368400000 |
3 | 7.9 | 245852179 | 239163000 |
6 | 8 | 261441092 | 370001000 |
9 | 8.5 | 223808164 | 297503696 |
11 | 8.4 | 415004880 | 648167031 |
1 | 8.3 | 191796233 | 170162503 |
7 | 7.2 | 244082982 | 217900167 |
10 | 8.3 | 293004164 | 438338580 |
4 | 8.1 | 289916256 | 272900000 |
2 | 7.2 | 162798565 | 200600000 |
13 | 7.2 | 237283207 | 301700000 |
Table: Movies (Read-Only)
Id | Title | Director | Year | Length_minutes |
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug's Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
- 【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
- 【计算】列出所有的电影ID,名字和市场指数(
Rating
的10倍为市场指数) - 【计算】列出所有偶数年份的电影,需要电影ID,名字和年份
- 【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT id,Title,(Domestic_sales+International_sales)/1000000
FROM movies
join Boxoffice on id=Movie_id
;#注意括号
SELECT id,Title,Rating*10
FROM movies
join Boxoffice on id=Movie_id
;
SELECT id,Title,year
FROM movies
join Boxoffice on id=Movie_id
where year%2=0
;
SELECT Title,(Domestic_sales+International_sales)/Length_minutes AS price
FROM movies
join Boxoffice on id=Movie_id
where Director="John Lasseter"
order by price DESC
limit 3
;
统计:
常见统计函数
Function | Description |
COUNT(*), COUNT(column) | 计数!COUNT(*) 统计数据行数, COUNT(column) 统计column非NULL的行数. |
MIN(column) | 找column最小的一行. |
MAX(column) | 找column最大的一行. |
AVG(column) | 对column所有行取平均值. |
SUM(column) | 对column所有行求和. |
分组统计:
GROUP BY
数据分组语法可以按某个col_name对数据进行分组,如:GROUP BY Year
指对数据按年份分组, 相同年份的分到一个组里。如果把统计函数和GROUP BY
结合,那统计结果就是对分组内的数据统计了.GROUP BY
分组结果的数据条数,就是分组数量,比如:GROUP BY Year
,全部数据里有几年,就返回几条数据, 不管是否应用了统计函数.
例题:
Table(表): Employees
Role | Name | Building | Years_employed |
Engineer | Becky A. | 1e | 4 |
Engineer | Dan B. | 1e | 2 |
Engineer | Sharon F. | 1e | 6 |
Engineer | Dan M. | 1e | 4 |
Engineer | Malcom S. | 1e | 1 |
- 【统计】找出就职年份最高的雇员(列出雇员名字+年份)
- 【分组】按角色(
Role
)统计一下每个角色的平均就职年份 - 【分组】按办公室名字总计一下就职年份总和
- 【难题】每栋办公室按人数排名,不要统计无办公室的雇员
1.
SELECT name,Years_employed
FROM employees
//group by Years_employed (本句不写)
order by Years_employed DESC
limit 1
;#不可以直接对group降序排列
或者直接使用函数
SELECT name,max(Years_employed)
FROM employees
;
2.select role,avg(Years_employed)
from Employees
group by role
;
3.
select Building,sum(Years_employed)
from Employees
group by Building
;
4.
select Building,count(Name)
from Employees
where Building is not null
group by Building
;
在 GROUP BY
分组语法中,数据库是先对数据做WHERE
,然后对结果做分组.
如果我们要对分组完的数据再筛选出几条如何办?---》HAVING
(Group by后的where)
用HAVING进行筛选
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;
例题:
- 【统计】统计一下Artist角色的雇员数量
- 【分组】按角色统计一下每个角色的雇员数量
- 【分组】算出Engineer角色的就职年份总计
【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
1.
SELECT count(ROLE)
FROM employees
where Role="Artist"
;
2.
SELECT ROLE,count(ROLE)
FROM employees
group by ROLE
;
3.
SELECT sum(Years_employed)
FROM employees
group by ROLE
having ROLE="Engineer"
;
4.
SELECT ROLE,count(Name),Building is not NUll AS have
FROM employees
group by ROLE,have
;