Mysql笔记（全）

选择语句

返回的是行数据，返回行的特征数取决于select的个数

可在select中执行数学计算，符合数学运算律

如果想加空格，就要加’’

lSELECT
	first_name,
	
	
	last_name,
	points,
	points + 10,
	points * 10+100 as 'discount factor'
FROM
	customers;
	
SELECT distinct state
	FROM customers;

Where 字句

不等于 ！=

Sql 中日期要加“

select * 
from customers
where points>3000;
select * 
from customers
where state != 'VA';

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'

And , OR , Not 运算符

and 运算符优先级高于OR

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' and points >1000

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' or (points >1000 and state = 'VA')

IN运算符

适用于多个条件并列的时候

SELECT *
FROM customers
WHERE state = 'VA' or state = 'GA' or state = 'FL'

SELECT *
FROM customers
WHERE state in ('VA','FL','GA')

Between运算符

SELECT * 
from  customers 
where points BETWEEN 1000 and 3000
SELECT * 
from  customers 
where points BETWEEN 1000 and 3000a

Like运算符

SELECT *
from customers
where last_name like 'b%'

SELECT *
from customers
where last_name like 'b%'

Regexp运算符(regular expression)

^符号代表字符串开头

$符号代表字符串结尾

|符号代表字符串包含|前的字符或者|后的字符

'[gim]e’对应了包含ge，ie，me的字符

同理’e[gim]’

'[a-h]e’包含了a到h的ae， be …

select *
from customers
where last_name REGEXP 'field'

select *
from customers
where last_name REGEXP '^field'

select *
from customers
where last_name REGEXP '^field$'

select *
from customers
where last_name REGEXP 'field|mac|rose'

select *
from customers
where last_name REGEXP '^field|mac|rose'
///表示寻找字符一field开头或者包含mac或者rose

select *
from customers
where last_name REGEXP '[gim]e'

select * 
from customers
where first_name REGEXP 'ELKA|AMBUR'

select * 
from customers
where last_name REGEXP 'EY$|ON$'

select * 
from customers
where last_name REGEXP '^MY|SE'

select * 
from customers
where last_name REGEXP 'b[RU]'

IS NULL 运算符

null代表缺失值

SELECT *
FROM customers
WHERE phone is not NULL

Order by字句

可以改变表格的排列顺序

DESC(降序字符)

在mysql的表中可以用任意列排序比如

SELECT * , quantity
FROM order_items
where order_id = 2
order by quantity*unit_price DESC

SELECT *
FROM customers
order by first_name DESC

limit子句

返回限定的行数，但如果限定的行数大于满足条件的行数，那么就会返回满足条件的全部行

limit子句永远要写在最后不然会报错

SELECT *
FROM customers
LIMIT 3
///跳过前6个数据然后开始取连续的三个数据
SELECT *
FROM customers
LIMIT 6,3 

内连接（inner join）

可以直接写join不用把inner打出来

SELECT
	order_id,
	o.customer_id,
	first_name,
	last_name 
FROM
	orders o
	INNER JOIN customers c ON o.customer_id = c.customer_id

自连接

SELECT
	e.employee_id,
	e.first_name,
	m.first_name
FROM
	employees e
	JOIN employees m ON e.reports_to = m.employee_id

33391 D’arcy Yovonnda
37851 Sayer Yovonnda
40448 Mindy Yovonnda
56274 Keriann Yovonnda
63196 Alaster Yovonnda
67009 North Yovonnda
67370 Elladine Yovonnda
68249 Nisse Yovonnda
72540 Guthrey Yovonnda
72913 Kass Yovonnda
75900 Virge Yovonnda
76196 Mirilla Yovonnda
80529 Lynde Yovonnda
80679 Mildrid Yovonnda
84791 Hazel Yovonnda
95213 Cole Yovonnda
96513 Theresa Yovonnda
98374 Estrellita Yovonnda
115357 Ivy Yovonnda

多表连接

多表连接就是找相同，只有表里有相同的列，才能连起来，然后就是一张大表，然后从大表里边选择自己想要的信息

每次写之前可以先select*等写完全部之后再根据每个表的特性选取相应的列

select c.client_id ,c.NAME, c.address, c.phone , p.date , pm.name as paymentmethod
FROM clients c
JOIN payments p
ON c.client_id= p.client_id
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kCvN0RPG-1677031702684)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230216231442794.png)]

复合条件

select *
from order_items oi
join order_item_notes oin
ON oi.order_id = oin.order_id
and oi.product_id = oin.product_id

隐式连接语法

select * 
from orders o
join customers c
on o.customer_id = c.customer_id

--Implpicit join syntax
select *
from orders o , customers c 
where o.customer_id = c.customer_id
///这两个从效果上是一样的，如果忘记输入where字句会产生笛卡尔积消耗大量算力

外连接

SELECT
 c.customer_id, c.first_name, o.order_id 
FROM
	customers c
left	JOIN orders o ON c.customer_id = o.customer_id 
ORDER BY
	c.customer_id
	
	
	SELECT
 c.customer_id, c.first_name, o.order_id 
FROM
	customers c
right	JOIN orders o ON c.customer_id = o.customer_id 
ORDER BY
	c.customer_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4GUcLZeR-1677031702684)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230216235429822.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kKZchses-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230216235453145.png)]

select p.product_id , p.name , o.quantity 
from products p 
left join order_items o 
on p.product_id = o.product_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kepGCcGv-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230217001747121.png)]

多表外连接

SELECT
	o.order_date,
	o.order_id,
	c.first_name,
	sh.name as shipper,
	os.name as status 
FROM
	orders AS o
	JOIN customers AS c ON o.customer_id = c.customer_id
	left JOIN shippers AS sh ON sh.shipper_id = o.shipper_id
	JOIN order_statuses AS os ON o.STATUS = os.order_status_id
order by  order_id
	

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZJ31lFxH-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230217154729501.png)]

自外连接

在工作表中可以用自连接或者自外连接的方式快速导出组织架构图

SELECT
	e.employee_id,
	e.first_name,
	m.first_name AS manager 
FROM
	employees e
	left JOIN employees m ON e.reports_to = m.employee_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EVv6UOvL-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230217161921473.png)]

using字句

当用连接语句 on 之后的条件一样时，比如

join customers c 
o.customer_id = c.customer_id

--就可以写成
join customers c
using(customer_id)

SELECT
	O.ORDER_id,
	c.first_name,
	sh.NAME AS shipper 
FROM
	orders o
	JOIN customers c USING ( customer_id )
	LEFT JOIN shippers sh USING (
	shipper_id 
	)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DNaJGNNb-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230217162614266.png)]

复合主键的情况下

select * 
from order_items oi 
join order_item_notes oin
on oi.order_id = oin.order_id and 
oi.product_id = oin.product_id

---用using语句就可以这样子改
select * 
from order_items oi 
join order_item_notes oin
using(order_id,product_id)

自然连接

通过自然连接我们就不用写列名了，数据库引擎会自己看着办，基于共同的列连接

select *
from orders o 
natural join customers c

一般不太推荐用，因为会产生意料之外的结果

交叉连接

显式交叉连接

两个集合的所有集合，其实就是个笛卡尔积

select 
c.first_name as customer,
p.name as product
from customers c
cross join products p 
order by c.first_name

隐式交叉连接

select 
c.first_name as customer,
p.name as product
from customers c , products p 
order by c.first_name

这样子也会得到笛卡尔积

联合 | Unions

通过union我们可以合并多个查询结果，这些结果可以基于一张表也可以基于多张表

但要切记查询的列要在数目上保持一致，因为union的本质是合并列

第一段被查询的列名将会被用来当做整个列表的列名

select 
order_id,
order_date,
'Active' as status
from orders 
where order_date >= '2019-01-01'
UNION 
select 
order_id,
order_date,
'Archived' as status
from orders 
where order_date < '2019-01-01'
---可以将两个查询的结果结合到一起---

利用union语句可以做一个关于积分的排序，非常方便

select customer_id , 
first_name,
 points ,
 'Bronze' as type 
 from customers
 where points<=2000
 UNION
 select customer_id , 
first_name,
 points ,
 'Silver' as type 
 from customers
 where points BETWEEN 2000 and 3000
UNION
select customer_id , 
first_name,
 points ,
 'Gold' as type 
 from customers
 where points>3000

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xCWmEdRq-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230217181847645.png)]

聚合函数

SELECT
	max( invoice_total ) AS hightest ,
	min(invoice_total) as lowest,
	avg(invoice_total) as average,
	sum(invoice_total) as total,
	count(invoice_total) as number_of_invoices,
	count(payment_date) as count_of_payments,
	count(DISTINCT client_id) as total_records
FROM 
	invoices
	where invoice_date > '2019-07-01'

Group By语句

Group by 永远在where子句之后，order子句之前

order by 是进行排序，group by 是进行分组

select 
client_id ,
sum(invoice_total) as total_sales
from invoices i
join clients  using(client_id)
where invoice_date >= '20 19-07-01'
group by client_id
order by total_sales DESC

SELECT
	p.DATE AS DATE,
	pm.NAME AS payment_method,
	sum(p.amount) AS total_payment 
FROM
	payments AS p
	JOIN payment_methods AS pm ON p.payment_method = pm.payment_method_id 
GROUP BY p.date , pm.name 
order by p.date

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f1TpcgmR-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230220102059814.png)]

Having子句

where可以让我们在分组之前筛选数据，而having用于在group by 分组之后筛选数据

select 
client_id ,
sum(invoice_total) as total_sales
from invoices 
group by client_id
having total_sales >500

和where子句一样，我们可以在having后打上多个条件，但用到的列一定的得是在select语句中存在的

一般选择group by 的时候可以根据select子句里的所有列来进行分组

--Get the customers
--			located IN Virginia
--who have spent more than $100
SELECT
	customer_id , first_name , last_name , sum(quantity * unit_price) as cost
FROM
	customers
	JOIN orders USING ( customer_id )
	JOIN order_items USING ( order_id ) 
WHERE
	state = 'VA'
group by customer_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WeBmE6DU-1677031702685)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230220112244021.png)]

//一个小技巧就是在写select语句时可以边写边运行，边观察数据表格的变化，在进行编写，效率和准确率会比较高

Roll up运算符

//相当于在增加了一个合集功能，rollup运算符只能应用于聚合值的列
select 
client_id,
sum(invoice_total) as total_sales 
from invoices 
group by client_id with ROLLUP

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5b2VnWTq-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230220113033549.png)]

子查询

sql会先运行内查询里的内容之后将它发送给外查询

--in sql_hr database
--find employees whose earn more than average

SELECT
	* 
FROM
	employees 
WHERE
	salary > ( SELECT AVG( salary ) FROM employees )

IN运算符

-- find the products that have never been ordered

SELECT
	* 
FROM
	products 
WHERE
	product_id NOT IN ( SELECT DISTINCT product_id FROM order_items )
	
	
--find the client without invoices
SELECT
	* 
FROM
	clients 
WHERE
	client_id NOT IN ( SELECT DISTINCT client_id FROM invoices )

子查询VS连接

--find customers who have ordered lettuce (id= 3)
SELECT * 
from customers
where customer_id in(
SELECT o.customer_id
from order_items oi 
join orders o USING(order_id)
where product_id = 3)

All关键字

SELECT
	* 
FROM
	invoices 
WHERE
	invoice_total > (
	SELECT
		max( invoice_total ) 
	FROM
		invoices 
	WHERE
	client_id = 3 
    
SELECT * 
from invoices
where invoice_total > all (select invoice_total
from invoices
where client_id = 3)
//这两者在结果上是等效的，all关键字就是会将括号前的的和括号后select的每一个结果进行比较只有比括号后的每个结果都大才会被筛选出来

ANY关键字

in 关键字和 = any是等效的

//select clients with at least two invoices
SELECT
	* 
FROM
	clients 
WHERE
	client_id IN (
	SELECT
		client_id 
	FROM
		invoices 
	GROUP BY
		client_id 
	HAVING
	COUNT(*)>= 2 
	)
	
	SELECT
	* 
FROM
	clients 
WHERE
	client_id  = ANY (
	SELECT
		client_id 
	FROM
		invoices 
	GROUP BY
		client_id 
	HAVING
	COUNT(*)>= 2 
	)
	
	

相关子查询

相关子查询会在主查询每一行的层面执行，因此子查询执行的很慢

相关子查询也可以用在select语句之中作为子查询

-- select employees whose salary is 
-- above the average in their office

select * 
from employees e 
where salary > (
select avg(salary)
from employees 
where office_id = e.office_id
 
)

-- get invoices that are larger than the 
-- client's average invoice amount

select *
from invoices i
where invoice_total > (SELECT
avg(invoice_total) from invoices
where  client_id = i.client_id
)
order by 
client_id

EXISTS运算符

-- select clients that have an invoice
-- exist 的好处在于子查询的结果不会被存储，而是会返回T/F返回T在主查询就会返回当前行,并开始查询下一个id所以不会出现重复的情况
SELECT *
from clients 
where client_id in (
SELECT DISTINCT client_id
from invoices)

-- find the product that have never been ordered 
select * 
from clients c
where exists (
select
client_id
from invoices 
where client_id = c.client_id 
)


-- Find the products that have never been ordered 
select * 
from products p
where not EXISTS (
SELECT product_id from order_items
where product_id = p.product_id
)

Select 子句中的子查询

select 
invoice_id,
invoice_total,
(SELECT avg(invoice_total)
from invoices) as invoice_average,
invoice_total - (SELECT invoice_average) as difference 
from invoices

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WhHhV29r-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221133118879.png)]

	-- 我写的
	SELECT
	client_id,
	NAME,
	sum( invoice_total ) AS total_sales,
	( SELECT avg( invoice_total ) FROM invoices ) AS average,
	 (select total_sales) as difference
FROM
	invoices
	RIGHT JOIN clients USING ( client_id ) 
GROUP BY
	client_id 
ORDER BY
	client_id
	
	-- 老师写的
	SELECT
	client_id,
	NAME,
	( SELECT sum( invoice_total )
	 FROM invoices
		WHERE  client_id = c.client_id ) AS total_sales,
	( SELECT avg( invoice_total ) FROM invoices ) AS average,
	( SELECT total_sales - average ) AS difference 
FROM
	clients c 
-- 我写的之所以在最后一句select报错是因为group by 语句的没有办法单独作为一列去提取，要反复运行很多遍才能运行结束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yAYGTvO2-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221143749135.png)]

From子句中的子查询

SELECT
	* 
FROM
	(
	SELECT
		client_id,
		NAME,
		( SELECT sum( invoice_total ) FROM invoices WHERE client_id = c.client_id ) AS total_sales,
		( SELECT avg( invoice_total ) FROM invoices ) AS average,
		( SELECT total_sales - average ) AS difference 
	FROM
		clients c 
	) AS sale_summry -- 这是必须项，没有这项是跑不出来的
WHERE
	total_sales IS NOT NULL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VXRwKDaR-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221145308394.png)]

数值函数

select TRUNCATE(5.7345,2);-- 截断小数两位
select round(5.7345,2) -- 四舍五入到小数的第二位
select CEILING(5.7) -- 返回大于或者等于这个数字的最小整数
select FLOOR(5.2) -- 返回小于或者等于这个数字的最大整数
select  ABS(5.2) -- 返回绝对值
select  rand() -- 返回0-1之间的随机浮点数
-- 更多数值函数可以搜索：mysql numeric functions

字符串函数

select length('sky') -- 显示字符串的长度
select upper('sky') -- 将字符串转化为大写
select lower('sky')-- 将字符串转化为小写
select ltrim(' sky') -- 移除字符串左侧的空白字符或者其他预定义字符
select rtrim('Sky   ') -- 移除字符串右侧的空白字符或者其他预定义字符
select left('Kindergarten',4) -- 返回字符串前四个字符
select right('Kindergarten' ,5) -- 返回字符串后四个字符串
select substring('Kindergarten',6) -- 返回字符串中任意位置的字符(是从1开始计数的python是0)
select locate('n','Kindergarten') -- 返回第一个字符或者说是一串字符所匹配的位置
select replace('Kindergarten','garten','garden')
-- 会替换字符串里的固定字符 三个字符的位置分别是，选中字符，字符中需要替换的部分，此部分的替换字符
select concat ('first' ,'last')-- 合并字符
例如：
select CONCAT(first_name,'·',last_name) as full_name
from customers

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZHMfQIJd-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221160449945.png)]

日期函数

select now() -- 返回当前电脑上的日期和时间 2023-02-21 16:07:30
select curdate() -- 会把时间去掉，只返回当前的日期 2023-02-21
select CURTIME() -- 返回当前时间 16:08:03
select YEAR(NOW()) -- 用于提取当前日期下的年份
select month(now()) -- 用于提取当前日期下的月份
select minute(now()) -- 用于提取当前日期下的分钟
select second(now()) -- 用于提取当前日期下的秒
select dayname(now()) -- 会返回例如"monday"这类结果
select monthname(now()) -- 得到字符串格式的月份
select extract(year from now()) -- 返回响应的时间格式

格式化时间和日期

select DATE_FORMAT(NOW(),'%M %d %Y')
-- February 21 2023

计算日期和时间

select DATE_ADD(NOW(),INTERVAl  1 DAY)
-- 2023-02-22 16:42:59
select DATE_ADD(NOW(),INTERVAl  -1 DAY)
-- 2023-02-20 16:43:36
select datediff('2019-01-05','2019-01-01')
-- 计算两个日期间相隔的天数
select time_to_sec('09:00')
-- 返回零点计数的秒数
select time_to_sec('09:00') - TIME_TO_SEC('09:02')
-- 计算时间间隔

IFNULL 和 COALESCE函数

SELECT 
order_id,
IFNULL(shipper_id,'not assigned') as shipper 
from orders

SELECT 
order_id,
coalesce(shipper_id, comments ,'not assigned') as shipper 
from orders
-- 会从一堆列里向后依次填入，如果comment为空就填入 not assigned

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cBbgupx8-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221165115574.png)]

IF函数

select 
order_id,
order_date,
if(year(order_date) = year(now()),
   'Active',
   'Archived'
)
如果第一个条件为T则返回第一个值
如果为F则返回第二个值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E34qEbtL-1677031702686)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221170532551.png)]

-- 通过相关子查询的方式解决
SELECT
 product_id, 
 NAME, 
(select count(product_id)
from order_items
where product_id = p.product_id) as orders ,
IF((select orders)>1,'Many time','Once') as frequency
from products p

-- 通过group by的方式解决
select product_id,
name ,
count(*) as orders,
IF(count(*)>1,'Many time','Once')
from products
join order_items USING(product_id)
GROUP BY product_id

case运算

select CONCAT(first_name,'·',last_name),
points,
CASE 
	WHEN points < 2000  THEN 'Bronze'
	when points >2000 and points <3000 then 'Silver'
	ELSE 'Gold'
END  as 'category'
from customers
order by points desc

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hmspmNK2-1677031702687)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230221202243296.png)]

窗口函数

可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

+----------------+-------------+--------+
| sales_employee | fiscal_year | sale   |
+----------------+-------------+--------+
| Alice          |        2016 | 150.00 |
| Alice          |        2017 | 100.00 |
| Alice          |        2018 | 200.00 |
| Bob            |        2016 | 100.00 |
| Bob            |        2017 | 150.00 |
| Bob            |        2018 | 200.00 |
| John           |        2016 | 200.00 |
| John           |        2017 | 150.00 |
| John           |        2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.01 sec)

理解窗口函数可能更容易从聚合函数开始。聚合函数将来自多行的数据汇总到单个结果行中。例如，以下SUM()函数返回记录年份中所有员工的总销售额：

SELECT 
    SUM(sale)
FROM
    sales; 

+-----------+
| SUM(sale) |
+-----------+
|   1500.00 |
+-----------+
1 row in set (0.01 sec)

GROUP BY子句允许您将聚合函数应用于行的子集。例如，您可能希望按会计年度计算总销售额：

SELECT 
    fiscal_year, 
    SUM(sale)
FROM
    sales
GROUP BY 
    fiscal_year; 

+-------------+-----------+
| fiscal_year | SUM(sale) |
+-------------+-----------+
|        2016 |    450.00 |
|        2017 |    400.00 |
|        2018 |    650.00 |
+-------------+-----------+
3 rows in set (0.01 sec)

在这两个示例中，聚合函数都会减少查询返回的行数。

与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减少查询返回的行数。

例如，以下查询返回每个员工的销售额，以及按会计年度计算的员工总销售额：

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales; 

+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      450.00 |
|        2016 | Bob            | 100.00 |      450.00 |
|        2016 | John           | 200.00 |      450.00 |
|        2017 | Alice          | 100.00 |      400.00 |
|        2017 | Bob            | 150.00 |      400.00 |
|        2017 | John           | 150.00 |      400.00 |
|        2018 | Alice          | 200.00 |      650.00 |
|        2018 | Bob            | 200.00 |      650.00 |
|        2018 | John           | 250.00 |      650.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.02 sec)

在此示例中，SUM()函数用作窗口函数，函数对由OVER子句内容定义的一组行进行操作。SUM()应用函数的一组行称为窗口。

SUM()窗口函数由财政年度像它与查询报告不仅总销量GROUP BY子句，而且结果中的每一行中，而不是行的总数返回。

需要注意的是窗函数的结果集毕竟进行JOIN，WHERE，GROUP BY，以及HAVING子句和前ORDER BY，LIMIT和SELECT DISTINCT。

窗口函数语法

调用窗口函数的一般语法如下：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    ) 

在这个语法中：

• 首先，指定窗口函数名称，后跟表达式。
• 其次，指定OVER具有三个可能元素的子句：分区定义，顺序定义和帧定义。

OVER子句后面的开括号和右括号是强制性的，即使没有表达式，例如：

window_function_name(expression) OVER()

partition_clause 句法

将partition_clause行分成块或分区。两个分区由分区边界分隔。

窗口函数在分区内执行，并在跨越分区边界时重新初始化。

partition_clause语法如下所示：

PARTITION BY <expression>[{,<expression>...}] 

您可以在PARTITION BY子句中指定一个或多个表达式。多个表达式用逗号分隔。

order_by_clause 句法

order_by_clause语法如下：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}] 

ORDER BY子句指定行在分区中的排序方式。可以在多个键上的分区内对数据进行排序，每个键由表达式指定。多个表达式也用逗号分隔。

与PARTITION BY子句类似ORDER BY，所有窗口函数也支持子句。但是，仅对ORDER BY顺序敏感的窗口函数使用子句才有意义。

frame_clause 句法

帧是当前分区的子集。要定义子集，请使用frame子句，如下所示：

frame_unit {<frame_start>|<frame_between>} 

相对于当前行定义帧，这允许帧根据其分区内当前行的位置在分区内移动。

帧单位指定当前行和帧行之间的关系类型。它可以是ROWS或RANGE。当前行和帧行的偏移量是行号，如果帧单位是ROWS行值，则行值是帧单位RANGE。

所述frame_start和frame_between定义帧边界。

将frame_start包含下列之一：

• UNBOUNDED PRECEDING：frame从分区的第一行开始。
• N PRECEDING：第一个当前行之前的物理N行。N可以是文字数字或计算结果的表达式。
• CURRENT ROW：当前计算的行

frame_between如下：

BETWEEN frame_boundary_1 AND frame_boundary_2 

frame_boundary_1和frame_boundary_2可各自含有下列之一：

• frame_start：如前所述。
• UNBOUNDED FOLLOWING：框架结束于分区的最后一行。
• N FOLLOWING：当前行之后的物理N行。

如果未frame_definition在OVER子句中指定，则MySQL默认使用以下帧：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

MySQL窗口函数列表

下面显示了MySQL中的窗口函数：

排序类

rank, denserank, row_number

-- rank 按班级分组后打上序号，不考虑并列
select * , row_number() over (partition by cid order by score desc) as '不可并列排名' from SQL_5;

-- denserank 按班级分组后跳跃排名 考虑并列
select * , rank() over (partition by cid order by score desc) as '跳跃可并列排名' from SQL_5;

-- row_number 按班级分组后连续排名 考虑并列
select * , dense_rank() over (partition by cid order by score desc) as '连续可并列排名' from SQL_5;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qzntra8F-1677031702687)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230222092224783.png)]

聚合类

Sum ,avg ,count , max , min

跨行类

Lag , lead

-- lag/lead 函数 参数1 ：比较的列 参数2：偏移量 参数3:找不到的默认值
-- 同一班级内，成绩比自己低一名的分数是多少
select * , lag(score,1) over (partition by cid order by score ) as '低一名的分数' from SQL_5;
-- 或者写成
select * , lag(score , 1, 0) over(partition by cid order by score) as '低一名的分数' from SQL_5;

-- 同一班级内， 成绩比自己高两名的分数是多少
select * , lead(score ,2 ) over (partition by cid order by score) as '高两名的分数' from SQL_5;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8qj9MNzf-1677031702687)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230222092931380.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lbdSkE9s-1677031702687)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230222093949486.png)]

-- 求出每个班级，每门课程的平均分
select cid , course , avg(score) as avg from SQL_6 group by  cid , course;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9bJFBWMX-1677031702688)(/Users/bahadir/Library/Application Support/typora-user-images/image-20230222095822763.png)]