这里会记录一些Mysql的语句的关键字等信息,以备自己复习
select ,distinct ,order by
Distinct 作用是去重 在select之后使用
使用通配符筛选数据要用like,如果不用通配符就可以用=或者!=
Order by用来排序,其中desc是降序,asc是升序。然后order by之后可以用limit来设定显示的条目数量,limit后可跟offset来设定从第几个数据开始显示。
例子:
SELECT distinct title FROM movies where director = “John Lasseter” order by Length_minutes desc limit 3 offset 2;
内连接inner join
左连接,右连接left join,right join
is null ,is not null
AS,函数
例子
列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
SELECT id, title, (Domestic_sales + International_sales) / 1000000
from movies
left join boxoffice
on id = Movie_id;
列出所有偶数年份的电影,需要电影ID,名字和年份
SELECT id, title, year
from movies
left join boxoffice on id = Movie_id
where year % 2 = 0;
John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT title,((Domestic_sales + International_sales) / Length_minutes) as sale_value
from movies
left join boxoffice on id = Movie_id
where director = "John Lasseter"
order by sale_value desc
limit 3;
按角色(Role)统计一下每个角色的平均就职年份
SELECT name, Years_employed
FROM employees
where Years_employed = (select Max(Years_employed) from employees);
或者
SELECT name,Years_employed FROM employees
order by Years_employed desc
limit 1
按角色(Role)统计一下每个角色的平均就职年份
select role,AVG(Years_employed)
from employees
GROUP BY role;
按办公室名字总计一下就职年份总和
select Building,SUM(Years_employed)
from employees
GROUP BY Building;
每栋办公室按人数排名,不要统计无办公室的雇员
select Building,COUNT(name)
from employees
GROUP BY Building
HAVING building IS NOT NULL
ORDER BY COUNT(name) DESC;
或者
select Building,COUNT(name)
from employees
WHERE building IS NOT NULL
GROUP BY Building
ORDER BY COUNT(name) DESC;
having
这里需要知道的是having是用在分组后的,where是在分组前
例子
按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
SELECT role,COUNT(name), building is not null
FROM employees
GROUP BY role, building is not null;
顺序
查询执行顺序
统计一下每个导演的销售总额(列出导演名字和销售总额)
SELECT director, SUM(International_sales + Domestic_sales)
FROM movies
left join boxoffice on id = Movie_id
GROUP BY director;
按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量)
SELECT director, SUM(International_sales + Domestic_sales) , COUNT(title), SUM(International_sales + Domestic_sales) / COUNT(title) AS avg_sale
FROM movies
left join boxoffice on id = Movie_id
GROUP BY director
HAVING COUNT(title) > 1
ORDER BY avg_sale desc
limit 1;
找出每部电影和单部电影销售冠军之间的销售差,列出电影名,销售额差额
SELECT title, (SELECT (Domestic_sales + International_sales) AS saleall FROM movies LEFT JOIN boxoffice ON id = Movie_id ORDER BY saleall desc limit 1) - (International_sales + Domestic_sales)
FROM movies
left join boxoffice on id = Movie_id;