DAY3
什么是关键字NULLs?
在数据库中,NULL代表无的意思。
如果某个字段没有添加到数据库,则会出现NULL。常见的解决方式是给字段设置默认值,比如数字的默认值设置为0,字符的默认值设置为""字符串。但在一些场景需要注意到底是使用默认值还是NULL,比如求平均值的时候如果使用默认值则,则会将默认值0加入计算,影响结果;如果是NULL则不会加入计算。
在多表连接中,再出现数据不匹配时,会使用NULL来填充。
可以使用 IS NULL 或者 IS NOT NULL来表示是否使用null。
语法:
SELECT column,another_column ...
FROM table
WHERE column IS/IS NOT NULL
AND/OR another_condition
例子:
找到雇员里还没有分配办公室的(列出名字和角色就可以) ?
SELECT Name,Role
FROM employees
WHERE Building IS NULL;
找到还没有雇员的办公室 ?
SELECT building_name
FROM buildings
LEFT JOIN employees
ON building_name = employees.building
WHERE name is null;
表达式和AS的使用
在SQL中,我们可以用表达式来指定对属性进行一定的计算和处理。表达式计算可以是数字的、字符串的运算,也可以是常数的运算。当表达式运算比较复杂时,也可以使用AS来取别名,表明能够代表表达式的内容
语法:
SELECT particle_speed / 2 AS half_particle
FROM table
WHERE ABS(half_particle) *10.0>500
(条件是这个属性的绝对值乘10大于500)
例子:
列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
SELECT id,title, (Domestic_sales+International_sales)/1000000
FROM movies
LEFT JOIN Boxoffice
ON id = Boxoffice.Movie_id;
列出所有偶数年份的电影,需要电影ID,名字和年份
SELECT movie_id,title,year
FROM movies
LEFT JOIN Boxoffice
ON id = Boxoffice.Movie_id
WHERE year%2==0;
John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT title,(Domestic_sales+International_sales)/Length_minutes as movie_value
FROM movies
LEFT JOIN Boxoffice
ON id = Boxoffice.Movie_id
WHERE director
LIKE "%John Lasseter%"
ORDER BY movie_value DESC
LIMIT 3;
统计查询:
几个常用的统计函数:
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结合,那统计结果就是分组内的数据统计
语法:
SELECT AGG_FUNC AS desription
FROM table
WHERE constraint_expression
GROUP BY column;
例子:
找出就职年份最高的雇员(列出雇员名字+年份)
SELECT name,MAX(Years_employed)
FROM employees;
按角色(Role
)统计一下每个角色的平均就职年份
SELECT role,AVG(Years_employed)
FROM employees
GROUP BY role;
每栋办公室按人数排名,不要统计无办公室的雇员
SELECT Building,count(Building)AS count
FROM employees
GROUP BY Building
order by count DESC limit 2;
或者这样解决也可以:
SELECT building,count(*) as count
FROM employees
where building is NOT null
group by building
HAVING筛选
语法:
SELECT group_by_column,AGG_FUNC AS aggregate_result,...
FROM table
WHERE condition
GROUP BY column
HAVING group_condition;
HAVING和WHERE语法一样,但是结果集不一样,当数据量成千上万时,HAVING的效果能够体现。
例子:
统计一下Artist角色的雇员数量
SELECT count(*)
FROM employees
WHERE Role = 'Artist';
按角色统计一下每个角色的雇员数量
SELECT role,count(*)
FROM employees
GROUP BY Role;
算出Engineer角色的就职年份总计
SELECT SUM(Years_employed)
FROM employees
GROUP BY Role
HAVING role = 'Engineer';
※按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
SELECT count(*) as count,Role,building is not null as bn
FROM employees
group by Role,bn
一个完整的查询语句语法是如何的呢?
语法:
SELECT AGG_funs AS express_name,...
FROM table
JOIN another_table
ON table.column = another_table.column
WHERE constraint
GROUP BY column
HAVING constraint
ORDER BY column ASC/DESC
LIMIT count OFFSET count;
查询执行的顺序如何?
1、FROM 和JOIN
FROM或JOIN会第一个执行,确定一个整体的数据范围。确定一个数据源表(含临时表)
2、WHERE
在数据源中筛选数据,丢弃不符合要求的数据行,注意AS别名不能在这个阶段使用,因为可能别名仍是一个没执行的表达式
3、GROUP BY
对数据进行统计分组
4、HAVING
对分组后的结果集再次筛选
5、SELECT
确定结果后,SELECT用来对结果col简单筛选或计算,决定输出什么数据
6、DISTINCT
对重复数据行排重
7、ORDER BY
在确定结果集的情况下,对结果进行排序。因为SELECT中表达式已经执行完了,此时可以使用AS别名。
8、LIMIT/OFFSET
从排序的结果中截取部分数据
例题:
统计出每一个导演的电影数量(列出导演名字和数量)
SELECT director,count(*) as num
FROM movies
GROUP BY director;
统计一下每个导演的销售总额(列出导演名字和销售总额)
SELECT director, count()
FROM movies
GROUP BY director;
统计一下每个导演的销售总额(列出导演名字和销售总额)
SELECT director ,sum(Domestic_sales)+sum(International_sales) AS total
FROM movies
INNER JOIN BoxOffice
ON id = BoxOffice.movie_id
GROUP BY director;
按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量)
SELECT director ,sum(Domestic_sales)+sum(International_sales) AS total_sales,count() as films_num,(sum(Domestic_sales)+sum(International_sales))/count() as avg_sales
FROM movies
INNER JOIN BoxOffice
ON id = BoxOffice.movie_id
GROUP BY director
HAVING films_num>1
ORDER BY avg_sales DESC
LIMIT 1;
找出每部电影和单部电影销售冠军之间的销售差,列出电影名,销售额差额
SELECT
(SELECT (Domestic_sales+International_sales) as total_sale
FROM movies
left join boxoffice on movies.id = boxoffice.movie_id
order by total_sale desc
limit 1) - (Domestic_sales+International_sales) as sale_diff,title
FROM movies
left join boxoffice on movies.id = boxoffice.movie_id
order by sale_diff desc;