SQL
在线练习网:xuesql.cn
在线练习: LeetCode
查询执行顺序
1.完整的select语句
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT index,number;
详解:
http://xuesql.cn/lesson/select_queries_order_of_execution#
查询中使用表达式
1.理论简介
我们可以使用计算表达式,也可以使用函数操作表中的数据。
聚集统计函数:
- AVG(column)
- count 计数!COUNT(*) 统计数据行数,COUNT(column) 统计column非NULL的行数.
- MAX(column)
- MIN(column)
- SUM(column) 对column所有行求和.
- row_number() 依次递增排名 无重复排名
- rank():相同分数有重复排名,但是重复后下一个人按照实际排名
- dense_rank():分数一致排名一致,分数不一致排名+1
- NTILE(4):分组排名,里面的数字是几,最多排名就是几,里面的数字是4,最多的排名就是4
字符串处理:
- 合并字符串函数:concat(str1,str2,str3…)
- 比较字符串大小函数:strcmp(str1,str2)
- 获取字符串字节数函数:length(str)
- 获取字符串字符数函数:char_length(str)
- 字母大小写转换函数:大写:upper(x),ucase(x);小写lower(x),lcase(x)
日期处理:
- 获取当前日期:curdate(),current_date()
- 获取当前时间:curtime(),current_time()
- 获取当前日期时间:now()
- 从日期中选择出月份数:month(date),monthname(date)
- 从日期中选择出周数:week(date)
- 从日期中选择出周数:year(date)
- 从时间中选择出小时数:hour(time)
- 从时间中选择出分钟数:minute(time)
- 从时间中选择出今天是周几:weekday(date),dayname(date)
2.思路
3.实战演练
3.1.找到John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值。
http://xuesql.cn/lesson/select_queries_with_expressions#
思路:
1)从电影名和价值 知道 要多表查询
-
条件1 导演名字叫John Lasster
-
价值 要用(国外+国内)/时长
4)排序降序
5)最高3个 使用LIMIT 0,3
--请输入sql
SELECT mv.Title, (Domestic_sales+International_sales)/Length_minutes AS avg_money
FROM Movies AS mv
INNER JOIN Boxoffice AS bff
on mv.Id=bff.Movie_id
WHERE Director like "John%"
ORDER BY avg_money desc
LIMIT 0,3
3.2统计排名函数使用(dense_rank() OVER )
178题 分数排名
根据分数排名倒序,分数相同就相同名次
https://leetcode-cn.com/problems/rank-scores/
思路:
dense_rank() OVER 排序函数,相同分数相同排名。
SELECT Score,
dense_rank() OVER
(ORDER BY Score) AS "Rank"
FROM Scores
查询结果去重(DISTINCT)和排序(ORDER BY)
1.理论简介
DISTINCT 用于排重,比如找到所有的电影名,但有很多电影名重复的,需要去重,就使用DISTINCT。
结果排序(ORDER BY)是在我们查询结果出来之后执行的语句。
通过LIMIT对已经查询的结果进行截取,可用于分页。 LIMIT 1,20 从下标1开始获取20个值。
2.思路
1.先通过WHERE语句查询
2.使用LIMIT 、ORDER BY等处理结果。
3.实战演练
在线练习:http://xuesql.cn/lesson/filtering_sorting_query_results#
3.1 ORDER BY 和 LIMIT
1.【结果排序】如果按片长排列,John Lasseter导演导过片长第3长的电影是哪部,列出名字即可
--请输入sql
SELECT Title
FROM movies
WHERE Director="John Lasseter"
ORDER BY Length_minutes desc
LIMIT 2,1 -- 从下标2开始,获取1个。
3.2 DISTINCT 去重
【去重】从movies表中查询所有的电影导演。
--请输入sql
SELECT DISTINCT Director FROM movies;
4.分页(LIMIT)
网站数据博客系统常见文章分页,使用的就是LIMIT 分页。
分页公式: (n-1)*pageSize,pageSize
n是第几页,pageSize是页面数据大小。
总页数=数据总数/ 页面大小
分组查询(GROUP BY)
1.理论简介
WHERE语句是先从数据库中获取数据之后,再对查询结果分组。
对分组之后的结果 条件语句用HAVING。
2.格式
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM table
WHERE constraint_expression
GROUP BY column
HAVING group_condition;
3.实战
1.按角色Role统计一下每个角色的平均就职年份
http://xuesql.cn/lesson/select_queries_with_aggregates#
思路:
1)按照ROLE分组 GROUP BY Role
2)平均就职年份 AVG(Years_employed)
--请输入sql
SELECT ROLE ,AVG(Years_employed)
FROM employees
GROUP BY ROLE
2.按照办公室名总计一下就职年份总和
http://xuesql.cn/lesson/select_queries_with_aggregates#
思路:同上
1)分组GROUP BY 得到分组的数据
2)SUM求和每个分组的年份。
--请输入sql
SELECT Building ,sum(Years_employed)
FROM employees
GROUP BY Building
3.统计一下Artist角色的雇员数量
http://xuesql.cn/lesson/select_queries_with_aggregates_pt_2#
思路:
1)先按照角色Role 分组 得到每个组的数量
2)分组之后HAVING条件 Role=“Artist”
--请输入sql
SELECT COUNT(*)
FROM employees
GROUP BY Role
HAVING Role="Artist"
3.4【最难多个分组】
按照角色分组算出每个角色并按有无办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色吐过部分有办公室,部分没有需要分开统计)
http://xuesql.cn/lesson/select_queries_with_aggregates_pt_2#
思路:
1)按Role,btn(有无办公室)分组
2)需要设置值 有办公室为1 无为0
Select Role,
case when Building is NULL then "0"
else "1" END AS "有无办公室",
COUNT(*)
FROM Employees
GROUP BY Role,"有无办公室"
联表查询
1.理论简介
2.思路
1.分析需求 需要从哪些表中查询
2.确定使用哪种连接方式。(通常是内连接 INNER JOIN)
3.确定交叉点 学生表中的studentNo=成绩表studentNo
3.实战
在线练习网站:http://xuesql.cn/lesson/select_queries_with_joins
基本语句:
SELECT *
FROM Employees AS em
LEFT/INNER/RIGHT JOIN Buildings AS bl
ON em.id=bl.id
WHERE
3.1内连接Inner JOIN
1.[连表]每部电影按照国际销售额比较排名靠前的导演是谁,国际销量是多少?
思路:
确定表
确定连接方式
确定交叉点
确定条件 order by desc 排序降序
确定个数 limit 0,1 从下标0开始获得1个。
SELECT bf.International_sales,mv.Director
FROM Movies AS mv
INNER JOIN Boxoffice AS bf
WHERE mv.Id=bf.Movie_id
order by bf.International_sales desc
limit 0,1
3.2外连接OUTER JOIN
外连接有 左连接 右连接 和 full 连接
1.找到所有办公室李的所有角色(包含没有雇员的)并作出唯一输出。
http://xuesql.cn/lesson/select_queries_with_outer_joins
DISTINCT去重。
分析:
1).办公室里所有角儿,Buildings表里面有所有办公室,所以应该把Buildings表放左边。
- 去重。会出现很多相同的Building_name 和ROLE 要用DISTINCT去重。
--请输入sql
SELECT DISTINCT ROLE,bld.Building_name
FROM Buildings AS bld
LEFT JOIN employees AS em
on bld.Building_name=em.Building
子查询(WHERE中嵌套)
1.理论简介
WHERE语句中再嵌套一个WHERE语句。
不过还是建议使用多表连接查询。
执行顺序由里及外,里面SELECT到外面的SELECT。
NULL的使用
简介
尽量少使用null,
条件判断语句IFNULL 当返回值为空时,可以设置为null
1.IFNULL函数
SELECT IFNULL(
SELECT Salary FROM Salarys
ORDER BY Salary DESC
LIMIT 1,1)
,NULL) AS SecondHeightSalarysql
常见需求SQL
1.分组排序多表综合应用 xuesql.cn题目
1.统计一下每个导演的销售总额(列出导演名字和销售总额)
http://xuesql.cn/lesson/select_queries_order_of_execution#
思路:
1)首先要求销售总额以及导演名字 要联表
2)求每个导演 需要分组GROUP BY
3)销售总额 需要相加(国内+国外票房)
SELECT DISTINCT Director, SUM(Domestic_sales+International_sales)
FROM Movies AS em
INNER JOIN Boxoffice AS bff
ON em.Id=bff.Movie_id
GROUP BY Director
2.【难题】按导演分组计算销售总额,求出平均销售额冠军(统计结果过略叼单部电影的导演,列出道衍明,总销售量,电影数量,平均销量)
http://xuesql.cn/lesson/select_queries_order_of_execution#
思路:
1) 按照要求的字段 导演名 总销量 等知道要联表 通常用内联
2) 按导演分组 GROUP BY Director
3)写出总销量SUM(国内+国外的销量)
4)写出平均销量 AVG(国内+国外)
5)分组条件 用HAVING 过滤单部电影 num>1
6)求冠军 先根据平均销量 排序降序
7)降序的结果再处理 LIMIT 0,1
--请输入sql
SELECT DISTINCT Director,
SUM(Domestic_sales+International_sales) AS sum_money,
COUNT(Title) AS num,
AVG(Domestic_sales+International_sales) AS avg_money
FROM Movies AS em
INNER JOIN Boxoffice AS bff
ON em.Id=bff.Movie_id
GROUP BY Director
HAVING num>1
ORDER BY avg_money desc
LIMIT 0,1
3.【变态难】找出每部电影和单部电影销售冠军之间的销售差,列出电影名销售差额。
http://xuesql.cn/lesson/select_queries_order_of_execution#
最大销量1063171911:
SELECT Title,SUM(Domestic_sales+International_sales) sum_money
FROM Movies AS mv
INNER JOIN Boxoffice AS bff
ON mv.Id=bff.Movie_id
GROUP BY Title
ORDER BY sum_money desc
LIMIT 0,1
最终sql:
SELECT Title,1063171911- SUM(Domestic_sales+International_sales) AS diff_money
FROM Movies AS mv
INNER JOIN Boxoffice AS bff
ON mv.Id=bff.Movie_id
GROUP BY Title
2. LeetCode SQL
2.1 连续性出现问题
题目:180连续出现的数字
找到连续出现至少3次的数字。
题解:
2.2 分组排序
找到每个部门最高工资的员工id,部门id,Salary
题解:
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
3 题目:求每个组前几的问题
题解:
核心在于中间WHERE子语句。
StuddentGrade自连接。
<=1 就是控制每个组前2.
select
*
from
StudentGrade a
WHERE (
SELECT count(1) FROM StudentGrade b WHERE b.subId=a.subId AND b.grade>a.grade
)<=1
ORDER BY
a.subId,a.grade desc;
4.分页问题
网站数据博客系统常见文章分页,使用的就是LIMIT 分页。
分页公式: (n-1)*pageSize,pageSize
n是第几页,pageSize是页面数据大小。
总页数=数据总数/ 页面大小
SELECT
*
FROM Table
LIMIT (n-1)*pageSize,pageSize