1. 聚合

1.count——不统计null

--5条记录
SELECT COUNT(*) AS product_count
FROMproducts;--4条记录（image_url有1条是null）
SELECT COUNT(image_url) 
FROMproducts;

2. group by

SELECT sales_status, COUNT(*)
FROMproducts
GROUP BY sales_status;

3. group_concat

SELECT GROUP_CONCAT(DISTINCT `name`), sales_status, COUNT(*)
FROMproducts
GROUP BY sales_status;

结果对比2

加入分隔符“>>>”

SELECT GROUP_CONCAT(DISTINCT `name`ORDER BY priceSEPARATOR '>>>'),sales_status,COUNT(*)
FROMproducts
GROUP BY sales_status;

2. where vs having

常见查询语句

Select Column1, Column2,
From Table1, Table2,
(Where) Condition1 and Condition2 or Condition3(Group By) Column name
(Having) Condition
(order by) Sorting
(limit)

• having可以单独出现，一般和group by同时出现
• 单独使用也可以运行，但性能较差，一般更常用where
  再看一个例子

CREATE TABLE IF NOT EXISTS  t2 (i INT,d1 DECIMAL(60 , 30 ),d2 DECIMAL(60 , 30 )
);INSERT INTO t2 VALUES
(2,0.00,0.00),(2,-13.20,0.00),
(2,59.60,46.40),(2,30.40,30.40);SELECT *
FROMt2;SELECT i, SUM(d1) AS a, SUM(d2) AS b
FROMt2
GROUP BY i;

结果如下

若加入having

SELECT i, SUM(d1) AS a, SUM(d2) AS b
FROMt2
GROUP BY i
HAVING a <> b;

则返回的是一个空的查询结果，因为a==b

若用where语句

SELECT i, SUM(d1) AS a, SUM(d2) AS b
FROMt2
WHEREa <> b
GROUP BY i
;

则会报错，因为t2表中没有a,b

3. 聚合中的坑

• 在使用max(),min()这类函数时，想根据值得的大小输出相应的名称大小可能是不对的，名字和值可能对不上，例如

SELECT `name`, MAX(price)
FROMproducts;

我们知道，实际价格88元对应的商品名称应该为“夏日缤纷桶”

更加合适的写法应该是

SELECT ANY_VALUE(`name`), MAX(price)
FROMproducts;

即取出来的"name"为任意值

4. 常见函数及操作总结

-- 将字符串大写、小写
SELECT upper('hello');
SELECT lower('HesdDi');-- 从右边取2个
SELECT right('hello', 2);-- 从左边取2个
SELECT left('hello', 2);-- substring(字符串，第几个开始，取几个)
SELECT substring('important', 3, 5);-- 长度，包括空格
SELECT length('   ad f   ');-- 删除左边、右边空格
SELECT ltrim('   ad f   ');
SELECT rtrim('   ad f   ');

1. 明文存储

SELECT user_id
FROMusers
WHEREuser_name = 'youzi'AND passwd = 'you123';

2. MD5()——密文存储

杂凑函数，结果不可逆

1. 加密
2. 完整性校验

SELECT MD5('test');

网络安全，多个层次加密
设计一个文件数据库=>名称不一样，内容一样，可通过MD5

3. 关系分解

• 为什么要用多张表单存储信息？

1. 冗余
2. 修改异常
3. 删除异常

例子：创建orders表格，为orders表格添加列，将orders表格中的user_name作为users表格中的user_name，将orders表格中的product_name作为products表格中的product_name,将orders表格中的unit_price作为products表格中的price，整个过程中，我们需要关注3个表格：orders,products,users

1. 创建表格orders，并拆解为orders,products,users

use dal_learning;
CREATE TABLE orders (PRIMARY KEY (order_id),order_id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(100) DEFAULT NULL,order_no VARCHAR(100) DEFAULT NULL,user_name VARCHAR(100) NOT NULL,product_name VARCHAR(100) NOT NULL,order_status ENUM('CART', 'DRAFT', 'NEW', 'IN_PROCESS', 'COMPLETED', 'FAILED') DEFAULT 'CART',create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
);  select * from orders;-- orders中添加列
alter table orders 
add column 
unite_price 
decimal(9,2) null default 0.0 
after product_name;alter table orders 
add column 
quantity 
int not null default 0 after unite_price;select * from orders;insert into orders
(order_no,user_name,product_name,unite_price,quantity)
values
('KFC123','jiao','香辣鸡腿堡人气套餐A',36,1),
('KFC321','jiao','海苔岩烧大鸡腿饭套餐A2',37,2),
('KFC234','jane','夏日缤纷桶',88,1),
('KFC567','peizi','超值全家桶',85,3),
('KFC890','feng','新奥尔良烤鸡腿堡人气套餐B',36,2),
('KFC890','xiaowan','超级塔可午餐套餐',29,1);--  UPDATE order_noselect * from orders;create table users(
primary key(user_id),
user_id int(11) not null auto_increment,
user_name  varchar(255) default null
) select distinct user_name from orders;select * from users;create table product_info as(select product_name from orders);
select * from product_info;create table products(
PRIMARY KEY (product_id),
product_id int(11) NOT NULL AUTO_INCREMENT,name varchar(100) DEFAULT NULL, -- detail text DEFAULT NULL,image_url varchar(255) DEFAULT NULL,sales_status enum('NEW','DEPRECATED') DEFAULT 'NEW',price  DECIMAL(9,2)  DEFAULT NULL,create_date datetime
) select distinct product_name,unite_price as price from orders;select * from products;
select * from users;
select * from orders;

2. 表格之间创建连接

alter table orders drop column product_name;
alter table orders drop column user_name;SET SQL_SAFE_UPDATES = 0; -- 1175报错，取消主键保护
update orders,users
set orders.user_id = users.user_id
where orders.user_name = users.user_name;update orders,products
set orders.product_id = products.product_id
where orders.product_name = products.product_name;alter table orders add column product_id int(11) not null after product_name;
alter table orders add column user_id int(11) not null after user_name;alter table orders 
add constraint FK_ORDER_USER FOREIGN KEY(user_id)
REFERENCES users(user_id)
on DELETE CASCADE;alter table orders 
add constraint FK_ORDER_PRODUCT FOREIGN KEY(product_id)
REFERENCES products(product_id)
on DELETE CASCADE;DELETE from `dal_learning`.`products`
WHERE (product_id = 5);
select * from orders;insert into products(product_name,price) values("老北京鸡肉卷",15);
select * from products; 

3. inner join & outer join

-- inner join
SELECT user_name, product_name, unite_price, quantity
FROMorders,users,products
WHEREorders.user_id = users.user_idAND orders.product_id = products.product_id;SELECT user_name, product_name, unite_price, quantity
FROMordersJOIN(users, products) ON (orders.user_id = users.user_idAND orders.product_id = products.product_id);-- left join
SELECT products.product_id, product_name, order_id
FROMproductsLEFT JOINorders ON products.product_id = orders.product_id;SELECT products.product_id, product_name, order_id
FROMordersRIGHT JOINproducts ON products.product_id = orders.product_id;-- left join union right join --> full join
-- 先插入一条数据，区分union all 和 union
insert into products(product_name,price) values("炸酱面",15);SELECT price
FROMproducts
WHEREprice > 50 
UNION SELECT price
FROMproducts
WHEREprice < 36;SELECT price
FROMproducts
WHEREprice > 50 
UNION ALL SELECT price
FROMproducts
WHEREprice < 36;-- 哪款产品没人买过？
SELECT products.product_id,product_name,order_id,orders.product_id
FROMproductsLEFT JOINorders ON products.product_id = orders.product_id
WHEREorders.order_id IS NULL;-- using 
SELECT *
FROMordersLEFT JOINproducts USING (product_id);

4. 查看每单产品的总销售额及销量

SELECT products.product_id,products.product_name,SUM(unite_price * quantity) AS sales_amount,COUNT(orders.order_id),SUM(quantity)
FROMproductsLEFT JOINorders ON products.product_id = orders.product_id
GROUP BY products.product_name , products.product_id; 

5. 查看每个用户消费金额

SELECT user_name, SUM(unite_price * quantity) AS sales_amount
FROMusersLEFT JOINorders ON users.user_id = orders.user_id
GROUP BY user_name;

6. generate column

alter table orders 
add column sales_amount decimal(9,2) as (unite_price * quantity) 
after quantity;select * from orders;

7. 找出价格最便宜的产品对应的名字

SELECT *
FROMproducts
WHEREprice = (SELECT MIN(price)FROMproducts);

8. in，any，all

• in：定项内
• any：与子查询返回的任何值比较为True时，返回True
• some：any的别名，较少使用
• all：与子查询返回的所有值比较为True时，返回True

1. 找出下架的商品信息

update products            
set sales_status = "DEPRECATED"
where product_name = "老北京鸡肉卷";update products            
set sales_status = "DEPRECATED"
where product_name = "夏日缤纷桶";SELECT *
FROMproducts
WHEREsales_status IN (SELECT sales_statusFROMproductsWHEREsales_status = 'DEPRECATED');

2. 输出价格比定价高的商品相关信息

SELECT products.product_id, products.product_name, products.price
FROMproducts
WHEREprice < ALL (SELECT unite_priceFROMorders,productsWHEREproducts.product_id = orders.product_id);