sql基础

USE sql_store;

SELECT *
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name;
-- 注意sql子句的使用顺序必须是：SELECT ----> FROM----> WHERE -----> ORDER BY
-- 如果使用顺序写错了就会报语法错误

1.1 选择子句select

1.1.1 为列起别名AS

SELECT 
	last_name,
    first_name,
    points,
    (points + 10) * 5 AS 'discontNum'
FROM customers;

1.1.2 去重distinct

查询到的city内容可能有重复，使用distinct去重

SELECT DISTINCT city
FROM customers;

1.2 where 子句

SELECT *
FROM customers
WHERE points > 3000;

sql的比较运算符：

>

>=

<

<=

=

!=或者<> ——> 都表示不等于

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'; 
-- 对于日期sql标准需要加引号，标准格式就是1990-01-01

1.3 and，or，not运算符

and的优先级大于or

and前后的条件都满足的数据

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

满足or前后任意一个条件即可

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR points > 3000;

not运算符

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000);

1.4 in运算符

SELECT *
FROM customers
WHERE city IN ('Arlington', 'Chicago', 'Orlando');

-- 也可以用or达到筛选的效果，不过看着有点呆
SELECT *
FROM customers
WHERE city = 'Arlington' OR city = 'Chicago' OR city = 'Orlando'

SELECT *
FROM customers
WHERE city NOT IN ('Arlington', 'Chicago', 'Orlando');

1.5 between ...and...运算符

SELECT *
FROM customers
WHERE  points >= 1000 AND points <= 3000;

-- 可以写成between and的形式

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;

1.6 like 运算符

SELECT *
FROM customers
WHERE last_name LIKE '%y';

-- % 可以表示任意数量的字符

SELECT *
FROM customers
WHERE last_name LIKE '%b%';

SELECT *
FROM customers
WHERE last_name LIKE 'br%';

-- _代表一个字符

SELECT *
FROM customers
WHERE last_name LIKE 'b____y';


SELECT *
FROM customers
WHERE phone NOT LIKE '%9';

1.7 regexp 运算符

SELECT *
FROM customers
WHERE last_name LIKE '%y';
-- 以y结尾的名字

-- % 可以表示任意数量的字符

SELECT *
FROM customers
WHERE last_name LIKE '%b%';
-- 中间含b的名字

SELECT *
FROM customers
WHERE last_name LIKE 'br%';
-- 以br开头的名字


-- 可以使用正则表达式regexp来实现以上3种筛选
SELECT *
FROM customers
WHERE last_name REGEXP 'y$';
-- 以y结尾的名字

SELECT *
FROM customers
WHERE last_name REGEXP 'b';
-- 中间含b的名字

SELECT *
FROM customers
WHERE last_name REGEXP '^br';
-- 以br开头的名字


-- 查询名字以field开头或者  含mac或者   含rose的顾客信息
--  | 表示多个搜索模式
SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose';



-- 查询名字里含有ge   ie   se 的顾客信息
SELECT *
FROM customers
WHERE last_name REGEXP '[gis]e';
-- WHERE last_name REGEXP 'ge|ie|se';


-- 查询名字里含 el ey  eb的顾客信息
SELECT *
FROM customers
WHERE last_name REGEXP 'e[lyb]';


-- 查询名字第一个字符是a-h，且第二个字符是o的顾客的信息
SELECT *
FROM customers
WHERE last_name REGEXP '^[a-h]o';

1.8 is null 运算符

SELECT *
FROM customers
WHERE phone IS NULL;

SELECT *
FROM customers
WHERE phone IS NOT NULL;

1.9 order by 子句

SELECT *
FROM customers
ORDER BY first_name;
-- 默认按照firstName升序排列
-- 改成order by first_name DESC  就是降序排列了

SELECT first_name, last_name, points, points + 10 AS discountNum
FROM customers
ORDER BY city, first_name DESC;
-- 先按照city升序 排序，city相同的按照first_name降序排序
-- 即使select查询字段里没有city，order by后面也可以使用这个列，这就是mysql的特别指之处

1.10 limit 子句

SELECT *
FROM customers
LIMIT 3;
-- 按照limit指定的n返回前n条数据，如果n比全部查询的结果数量都大，那就返回全部查询结果即可

-- 如果想获取前7~9位的顾客信息，使用偏移量 limit 6, 3
SELECT *
FROM customers
LIMIT 6, 3;
-- 偏移量是6，获取3条数据


-- 获取积分最高的前3位顾客信息
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;

二、链接

2.1 内连接inner join

2.1.1 同数据库内连接查询

USE sql_store;
-- 操作的表都在同一个数据库中

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

-- 可以写成inner join  也可以写成 join


-- 如果要查询的字段是在多个表中都存在的，需要在字段前面加上表名称 orders.customer_id, 不然会报语法错误
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
INNER JOIN customers
	ON orders.customer_id = customers.customer_id;


-- 可以给表起别名，避免多次写不方便
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c
	ON o.customer_id = c.customer_id;

2.1.2 跨数据库内连接查询

USE sql_store;

-- 查询数据库sql_store里order_items和数据库sql_inventory里products的数据

-- 需要给不在当前数据库的表名前面加上它的数据库名称sql_inventory.products

SELECT order_id, p.name, quantity, oi.unit_price
FROM order_items oi
INNER JOIN sql_inventory.products p
    ON oi.product_id = p.product_id

2.1.3 表的自内连接

USE sql_hr;


-- 数据库表自连接
-- 查找员工表中员工id，first_name，上级的first_name
SELECT e.employee_id, e.first_name, m.first_name AS manager
FROM employees e
INNER JOIN employees m
    ON e.reports_to = m.employee_id;

-- 但是表的自内连接会查不到经理这条数据，因为经理的reports_to是null

2.1.4 多表连接

USE sql_store;

-- 从orders  customers   order_statuses查询订单id、下单日期、客户名、客户电话、客户所在城市、客户详细地址、订单状态
SELECT 
	o.order_id,
    o.order_date,
    c.first_name AS customer,
    c.phone,
    c.city,
    c.address,
    os.name AS status
FROM orders o
INNER JOIN customers c
	ON o.customer_id = c.customer_id
INNER JOIN order_statuses os
	ON o.status = os.order_status_id;

USE sql_invoicing;

-- 查询支付表payments  下单客户信息表clients   支付方式表payment_methods
-- 获取支付id、支付日期、发票id、支付金额、客户名称、支付方式
SELECT
	p.payment_id,
    p.date,
    p.invoice_id,
    p.amount,
    c.name,
    pm.name AS payment_method
FROM payments p
INNER JOIN clients c
	ON p.client_id = c.client_id
INNER JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

2.1.5 复合连接条件

有的表需要多个主键才能定位一条数据，如订单详情表order_items

订单id+产品id才能唯一定位一条记录

订单注释表order_item_notes内有每个订单的客户备注信息

USE sql_store;

-- 要查询订单详情表order_items 和订单备注表order_item_notes 
-- 查询条件匹配时需要使用and连接order_items 的多个主键字段
SELECT oi.order_id, oi.product_id, quantity, unit_price, note
FROM order_items oi
INNER JOIN order_item_notes oin
	ON oi.order_id = oin.order_Id
    AND oi.product_id = oin.product_id

2.1.6 隐式连接语法

上面学习的表连接方式都是显式的，即使用的是inner join.... on的方式

-- 先写一个显式的表连接
USE sql_store;

SELECT *
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 下面使用隐式表连接的方式
SELECT *
FROM customers c, orders o
    WHERE c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 需要注意的是隐式表连接一定不能忘了where子句，不然查询到的数据就不对了
-- 尽量不要使用隐式表连接的方式，仅仅了解即可

2.2 外连接

外连接包括left join 和right join

2.2.1 两个表的外连接

USE sql_store;

-- 外连接包括left join 和right join

-- left join是把位于left join左侧的customers表里全部数据都查询出来，不管是否满足on后面的条件
SELECT 
    c.customer_id, 
    c.first_name, 
    order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 同样的，right join是把位于right join右侧的orders表里全部数据都查询出来，不管是否满足on后面的条件
-- 但是这里建议最好不要使用right join ，特别是多表外连接的时候，太容易出错了

2.2.2 多表外连接

USE sql_store;

-- 查询订单表orders、 客户表customers、订单状态表order_statuses、 承运方表shippers
-- 获取订单时间order_date, 订单id ，下单客户名、承运人、订单状态
-- 并按照订单状态，订单id升序排列,
SELECT 
    o.order_date, 
    o.order_id, 
    c.first_name, 
    s.name AS shipper, 
    os.name AS status
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers s
    ON o.shipper_id = s.shipper_id
JOIN order_statuses os
    ON o.status = os.order_status_id
ORDER BY os.name, o.order_id;
-- order by后面也可以使用上面给os.name起的别名 status，但是这样对别人不太好理解，因为orders里面也有status这个列，所以还是用os.name比较好
-- ORDER BY status, o.order_id;

2.2.3 表的自外连接

USE sql_hr;

SELECT 
    e.employee_id, 
    e.first_name, 
    e.job_title, 
    e.reports_to as manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id;

-- 表的自外连接就可以把经理这条数据查询出来，因为会把left join左侧的表内全部数据输出

2.3 using子句

使用场景：当进行表连接查询时，可以使用using子句替代on 条件判断，代码编写更清新，

使用前提：如果判断条件里的2个表的列名称是完全一样的，就可以使用using

USE sql_store;

SELECT 
	o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
INNER JOIN customers c
	-- ON o.customer_id = c.customer_id
	USING(customer_id)
LEFT JOIN shippers sh
	-- ON o.shipper_id = sh.shipper_id;
		USING(shipper_id);

USE sql_store;

SELECT 
	oi.order_id, 
    oi.product_id, 
    oin.note
FROM order_items oi
INNER JOIN order_item_notes oin
	-- ON oi.order_id = oin.order_id
    -- AND oi.product_id = oin.product_id;
    USING(order_id, product_id);
-- 复合条件查询时，当条件内的列名称一样时使用using子句

注意：当on后面条件里的列名称不一样时就不能使用using子句

USE sql_invoicing;

SELECT 
	p.date,
    c.name AS client,
    p.amount,
    pm.name AS payment_method
FROM payments p
INNER JOIN clients c
	-- ON p.client_id = c.client_id
    USING(client_id)
INNER JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

2.4 自然连接natural join

建议不要使用这种方式，了解即可

使用自然连接，搜索引擎自己会根据2个表拥有的相同名称的列进行查询，得到你需要的查询结果，不需要你写查询条件，但同时这种查询也是我们不能控制的

USE sql_store;

SELECT *
FROM orders o
NATURAL JOIN customers c;

2.5 交叉连接cross join

让左侧的表中的每一条数据和右侧表中的每一条数据组合，不写查询条件

2.5.1 交叉连接的显式语法

使用关键字cross join

USE sql_store;
	
SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;

2.5.2 交叉连接的隐式语法

不使用关键字cross join ，直接在from后面写需要用到的表名称

SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c, products p
ORDER BY c.first_name;

得到的结果和上面显式语法一样

2.6 联合查询 union

查询结果的列名称会以第一个sql查询里面的字段为准。

如果union前后2个查询语句获取的字段个数不一致，就会报错——重要！。

union前后2个查询语句内操作的表可以是同一个表也可以不同表

USE sql_store;


-- 给查询到的数据加一个标签
-- 2019年下单的订单标签为active
-- 2019年之前下单的订单标签为archived
SELECT 
	order_id AS order_num,
    order_date AS 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';

SELECT
	customer_id,
    first_name,
    points,
    '青铜' AS type
FROM customers
WHERE points < 2000
UNION 
SELECT
	customer_id,
    first_name,
    points,
    '白银' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
	customer_id,
    first_name,
    points,
    '黄金' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name

三、数据库表数据操作

3.1 插入单行insert into...values...

插入单行有2种方式：

第一种插入就是每一个字段都会传递要插入的值，并且需要和数据库表中字段的先后顺序进行赋值。

USE sql_store;

INSERT INTO customers
VALUES (
	DEFAULT,
    'John',
    'Smith',
    '1990-01-05',
	NULL,
    'address',
	'city',
	'CA',
    DEFAULT);
-- 主键是自增的，插入数据时没办法指定，可以是的default让数据库自己去生成
-- 有默认之的字段也可以使用default让数据库自己填充为默认值，也可以自己写

第二种单行插入是指定要插入的字段值，对于有默认值的或者数据库自动会生成的字段就不用传递了。

只要字段名和赋值对应上即可，不用非要按照数据库表里字段的顺序。


INSERT INTO customers (
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
	'Lisa',
    'Smith',
    '1990-01-05',
    'address',
    'city',
    'CA');

3.2 插入多行

USE sql_store;

-- 在shippers中插入多行数据
INSERT INTO shippers (name)
VALUES ('京东'),
	('菜鸟裹裹'),
    ('中通'),
    ('韵达');

3.3 插入分层行（操作父子表）

对多张表操作，orders表和order_items表，其中orders里面一条数据对应order_items里面1或n条数据，orders和order_items是父子表。

可以看到orders表中有一个主键order_id

order_items表中有2个主键order_id和product_id

要插入一个订单时需要在orders表插入下单信息，在order_items中添加具体购买的商品信息

last_insert_id()获取当前连接下的自增值，也就是上一条sql语句对应的自增值（上一条sql是向orders表插入订单记录，获取最后插入的那条订单记录对应的子增值---也就是订单记录的主键order_id）

USE sql_store;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-08-16', 1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 1.95),
	(LAST_INSERT_ID(), 2, 2, 2.15);

3.4 创建表复制create table... as...

USE sql_invoicing;

-- 创建一个新表test
CREATE TABLE `test`(
	`test_id` tinyint not null auto_increment,
    `name` varchar(50) not null,
    primary key(`test_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into test (name)
values ('test1'),
	('test2'),
    ('test3');

如果你想快速创建一张和orders表一模一样的表，可以使用表复制:

create table orders_archived as select * from orders

但是注意新建的表orders_archived没有设置主键和主键的自增属性

USE sql_store;

CREATE TABLE orders_archived AS
SELECT * FROM orders;
-- 这个sql语句会把orders里面的全部数据复制到orders_archived里面
-- 但是orders_archived里面没有设置主键和主键的自增属性



-- 如果只是想复制orders表里面的部分数据到新表orders_archived中
CREATE TABLE orders_archived AS
SELECT * FROM orders
WHERE order_date < '2019-01-01';


-- 如果想把orders表里面特定数据复制到orders_archived里面
-- 直接使用insert into即可，因为orders_archived和orders表的字段名完全一样，
-- 所以不需要指定插入的字段名称
INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date = '2019-08-16';

-- 切换到发票数据库
USE sql_invoicing;

-- 新建一个表invoices_archived，复制invoices表的数据，但是不要client_id这一列，
-- 把这一列变成客户名，并且只要已完成支付操作的发票信息
CREATE TABLE invoices_archived AS
SELECT 
	i.invoice_id,
    i.number,
    c.name AS client,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
INNER JOIN clients c
	USING(client_id)
WHERE payment_date IS NOT NULL;

3.5 更新单行update... set ... where...

USE sql_invoicing;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE invoice_id = 1;

UPDATE invoices
SET payment_total = DEFAULT,
	payment_date = NULL
WHERE invoice_id = 1;
-- 使用default前提是知道表设计时这个字段有默认值
-- 使用null前提是知道这个字段可以为nul

3.6 更新多行

MySQL工作台workbench要求一次只能更新一条数据，所以如果你要更新多条数据时会有报错信息。

设置方法：Edit--->Preferences--->SQL Editor，取消页面底部的勾选

USE sql_invoicing;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id = 3;


UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id IN (1, 5);


SELECT * FROM invoices
WHERE client_id IN (1, 3, 5)
ORDER BY client_id;

3.7 在update中用子查询

USE sql_invoicing;

UPDATE invoices
SET 
	payment_total = invoice_total * 0.1,
    payment_date = due_date
WHERE client_id = 
	(SELECT client_id
	FROM clients
	WHERE name = 'Yadel');
--在UPDATE里面的WHERE后面的条件子句中插入子查询
    
SELECT * FROM invoices
WHERE client_id = 3;

USE sql_store;
UPDATE orders
SET comments = '金牌客户'
WHERE customer_id IN (
	SELECT customer_id
	FROM customers
	WHERE points > 3000);
    
SELECT * FROM orders
WHERE customer_id IN (
	SELECT customer_id
	FROM customers
	WHERE points > 3000)

3.8 删除行delete from... where...

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id = (
	SELECT client_id
	FROM clients
    WHERE name = 'QQ');
-- 同样有删除一行和删除多行的操作，子查询得到的时多个结果时上面用in就行了
    
SELECT * 
FROM invoices
ORDER BY client_id;

四、汇总数据

4.1 聚合函数

聚合函数只能对非null的列进行计算。

MAX(column_name)

—— 求最大值/距今最近的日期

MIN(column_name)

—— 求最小值/距今最久的日期

AVG(column_name)

—— 求平均值

SUM(column_name)

—— 求和

COUNT(DISTINCT column_name)

—— 只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(*)可以返回表内数据记录的总数，不会计较某个列值为null。

count(column_name)不加distinct时可以把列值相同的记录也进行统计。

USE sql_invoicing;

SELECT 
	MAX(invoice_total) AS Highest,
    MIN(invoice_total) AS Lowest,
    AVG(invoice_total) AS Average,
    SUM(invoice_total) AS Total,
    COUNT(*) AS Total_records
FROM invoices
WHERE invoice_date > '2019-07-01';


SELECT COUNT(payment_date)
FROM invoices
WHERE invoice_total > 180;

USE sql_invoicing;

SELECT 
	'First half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT 
	'Second half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT 
	'Total' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-01-01' AND '2019-12-31';

4.2 group by子句

需要注意的是sql子句的顺序：

select ---> from ---> where ---> group by ---> order by ---> limit
顺序错了就会报语法错误

group by后面的列名可以是表中原来存在的，也可以是select 子句中AS 后面的新列名

USE sql_invoicing;

-- 从invoices表中获取2019年下半年每个客户的消费额度，
-- 并按照消费额度降序排列
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC;
-- 需要注意的是sql子句的顺序：select ---> from ---> where ---> group by ---> order by ---> limit
-- 顺序错了就会报语法错误

USE sql_invoicing;

-- 从invoices表中获取2019年下半年每个客户的消费额度，
-- 并按照消费额度降序排列
SELECT 
	client_id AS customer_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY customer_id
ORDER BY total_sales DESC;
-- 需要注意的是sql子句的顺序：select ---> from ---> where ---> group by ---> order by ---> limit
-- 顺序错了就会报语法错误

USE sql_invoicing;

-- 按州和城市获取对应的销售额
SELECT 
	c.state,
    c.city,
    SUM(invoice_total) AS total_sales
FROM invoices i
INNER JOIN clients c
	USING (client_id)
GROUP BY c.state, c.city;

-- 获取每种支付方式每天的支付总额
SELECT 
	date,
    pm.name AS payment_method,
    SUM(amount) AS total_payment
FROM payments p
INNER JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method
ORDER BY date

4.3 having子句

-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
-- where子句中可以使用表中任何已存在的列名进行判断筛选
-- having子句中使用的列名必须在select筛选子句里，如果使用了select子句中没有，having子句就使用某个列名就会报错，报错信息如下：
-- Error Code: 1054. Unknown column 'payment_date' in 'having clause'

USE sql_invoicing;

-- 获取销售额大于500的用户和其销售额
SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE total_sales > 500
-- 使用where会有报错，内容如下：
-- 报错具体信息：Unknown column 'total_sales' in 'where clause'
-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
GROUP BY client_id
HAVING total_sales > 500;

USE sql_invoicing;

-- 获取销售额大于500的用户和其销售额，以及发票数量大于5并获取发票数
SELECT 
	client_id,
    -- 一条数据就是一个发票记录，使用count(*)即可计算发票数量
    COUNT(*) AS number_of_invoice,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE total_sales > 500
-- 使用where会有报错，内容如下：
-- 报错具体信息：Unknown column 'total_sales' in 'where clause'
-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
-- where子句中可以使用表中任何已存在的列名进行判断筛选
-- having子句中使用的列名必须在select筛选子句里，如果使用了select子句中没有，
-- having子句就使用某个列名就会报错，报错信息如下：
-- Error Code: 1054. Unknown column 'payment_date' in 'having clause'
GROUP BY client_id
-- having payment_date > '2019-01-01'
HAVING total_sales > 500 AND number_of_invoice > 5;

USE sql_store;

-- 获取state为VA，消费超过100的用户信息
SELECT 
	c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.quantity *oi.unit_price) AS total_spend
FROM customers c
INNER JOIN orders o USING (customer_id)
INNER JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY c.customer_id
HAVING total_spend > 100;

4.4 数据汇总with rollup运算符

在group by...后面使用with rollup可以按照分组列名进行数据汇总，还会汇总整个数据

使用with rollup汇总的是select子句中使用聚合函数得到的那个数据

需要注意：当在group by后面使用with rollup运算符时，group by后面不能跟列别名

USE sql_invoicing;

-- 查询每个州 的每个城市的销售额
-- 并汇总所有每个州、每个城市以及销售总额
SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
INNER JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP
-- with rollup汇总的是SUM函数计算后得到的数据，即total_sales的值

USE sql_invoicing;

-- 计算不同支付方式的支付金额，并汇总支付总额
SELECT 
	pm.name AS payment_method,
    SUM(p.amount) AS total
FROM payments p
INNER JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
-- GROUP BY payment_method WITH ROLLUP;
-- 需要注意：当在group by后面使用with rollup运算符时，group by后面不能跟列别名
GROUP BY pm.name WITH ROLLUP;

五、复杂查询

5.1 子查询

where子句中添加子查询

子查询返回的是一个单一的值：平均工资、指定商品的价格

USE sql_store;

-- 查询价格大于生菜价格的商品，生菜的product_id = 3
SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price
    FROM products
    WHERE product_id = 3
);

USE sql_hr;

-- 查询工资大于平均工资的员工信息
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
)

5.2 in运算符+子查询

子查询返回的是某个列字段的值集合

USE sql_store;

-- 查询没有被订购过的商品信息
-- order_items表中是用户订单内具体商品信息
-- products表里面是全部商品的信息集合

SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
	FROM order_items
)

USE sql_invoicing;

-- 查询没有发票的客户
-- clients表存储全部客户信息
-- invoices表内存储发票信息
SELECT *
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices
)

5.3 子查询 vs 连接

USE sql_store;

-- 查询购买了生菜的用户，生菜的商品id=3
-- 获取customer_id,first_name,last_name
-- 方法一：使用表连接
SELECT 
	DISTINCT c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
INNER JOIN orders o
	USING (customer_id)
INNER JOIN order_items oi
	USING (order_id)
WHERE product_id = 3;


-- 方法二：使用子查询
SELECT 
	c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
WHERE customer_id IN (
	SELECT customer_id
    FROM orders
    WHERE order_id IN (
		SELECT order_id 
        FROM order_items
        WHERE product_id = 3
    )
)

5.4 all关键字

> (select MAX(列1) ......) ——》 > all (select 列1 .....)

< (select MIN(列1) .......) ——》 < all (select 列1 ......)

USE sql_invoicing;

-- 获取发票信息，发票的面值大于用户client_id = 3最大值发票
-- 使用聚合函数 max()
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
);


-- 使用all关键字
SELECT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);

5.5 any关键字

IN 可以替换为 = ANY

USE sql_invoicing;

-- 获取至少有2张发票的用户信息
SELECT client_id, COUNT(*) AS invoice_number
FROM invoices
GROUP BY client_id
HAVING invoice_number >= 2;

-- 使用in关键字
SELECT *
FROM clients
WHERE client_id IN (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
);

-- 使用any关键字
SELECT * 
FROM clients
WHERE client_id = ANY (
	SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);

5.6 相关子查询

上面讲过的子查询都是非相关子查询，也就是执行完子查询后把结果给主查询，之后主查询根据自己的条件进行再次查询，子查询完全不依赖主查询。

相关子查询是指在子查询的筛选条件中会有和主查询表内字段的判断。

USE sql_hr;

-- 查询工资高于所属部门平均工资的员工信息
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
);


USE sql_invoicing;

-- 获取面值大于客户自己发票平均面值的发票信息
SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
);

5.7 exists运算符

exists关键字后面的要加一个相关子查询。

USE sql_invoicing;

-- 获取有发票的客户信息
-- 使用in关键字
-- in关键字后面的子查询会返回一个查询结果集给到主查询，
-- 如果有上万上亿的数据那使用in就会效率很低，比较耗机器性能
SELECT *
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
    FROM invoices
);


-- 使用exists关键字
-- exists 后面的相关子查询如果返回True，
-- 主查询就会从当前进行判断的这条客户数据获取需要的字段值
-- exists 后面的子查询并没有返回数据量庞大的结果集，这样就效率高，也不耗机器性能
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
	WHERE client_id = c.client_id
);

USE sql_store;

-- 查询没有被订购过的商品信息
-- 方法一：使用not in
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
    FROM order_items
);


-- 方法二： 使用not exists
SELECT *
FROM products p
WHERE NOT EXISTS (
	SELECT product_id
    FROM order_i