Mysql语句的书写规则

这里会记录一些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;