MySQL(3)-CSDN博客

本文链接：https://blog.csdn.net/qq_40764503/article/details/105180504

聚合与进阶

1. 聚合
2. where vs having
3. 聚合中的坑
4. 常见函数及操作总结

1. 聚合

1.count——不统计null

--5条记录
SELECT 
    COUNT(*) AS product_count
FROM
    products;

--4条记录（image_url有1条是null）
SELECT 
    COUNT(image_url) 
FROM
    products;

2. group by

SELECT 
    sales_status, COUNT(*)
FROM
    products
GROUP BY sales_status;

在这里插入图片描述

3. group_concat

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

结果对比2
在这里插入图片描述
加入分隔符“>>>”

SELECT 
    GROUP_CONCAT(DISTINCT `name`
        ORDER BY price
        SEPARATOR '>>>'),
    sales_status,
    COUNT(*)
FROM
    products
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 
    *
FROM
    t2;
    
SELECT 
    i, SUM(d1) AS a, SUM(d2) AS b
FROM
    t2
GROUP BY i;

结果如下
在这里插入图片描述

若加入having

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

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

若用where语句

SELECT 
    i, SUM(d1) AS a, SUM(d2) AS b
FROM
    t2
WHERE
    a <> b
GROUP BY i
;

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

3. 聚合中的坑

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

SELECT 
    `name`, MAX(price)
FROM
    products;

在这里插入图片描述
我们知道，实际价格88元对应的商品名称应该为“夏日缤纷桶”

更加合适的写法应该是

SELECT 
    ANY_VALUE(`name`), MAX(price)
FROM
    products;

即取出来的"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
FROM
    users
WHERE
    user_name = 'youzi'
        AND passwd = 'you123';

2. MD5()——密文存储

杂凑函数，结果不可逆

加密
完整性校验

SELECT MD5('test');

在这里插入图片描述
网络安全，多个层次加密
设计一个文件数据库=>名称不一样，内容一样，可通过MD5

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_no

select * 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
FROM
    orders,
    users,
    products
WHERE
    orders.user_id = users.user_id
        AND orders.product_id = products.product_id;
        
SELECT 
    user_name, product_name, unite_price, quantity
FROM
    orders
        JOIN
    (users, products) ON (orders.user_id = users.user_id
        AND orders.product_id = products.product_id);
        
   
-- left join
SELECT 
    products.product_id, product_name, order_id
FROM
    products
        LEFT JOIN
    orders ON products.product_id = orders.product_id;
    
SELECT 
    products.product_id, product_name, order_id
FROM
    orders
        RIGHT JOIN
    products 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
FROM
    products
WHERE
    price > 50 
UNION SELECT 
    price
FROM
    products
WHERE
    price < 36;
    
SELECT 
    price
FROM
    products
WHERE
    price > 50 
UNION ALL SELECT 
    price
FROM
    products
WHERE
    price < 36;
    
-- 哪款产品没人买过？
SELECT 
    products.product_id,
    product_name,
    order_id,
    orders.product_id
FROM
    products
        LEFT JOIN
    orders ON products.product_id = orders.product_id
WHERE
    orders.order_id IS NULL;

-- using 
SELECT 
    *
FROM
    orders
        LEFT JOIN
    products USING (product_id);

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

SELECT 
    products.product_id,
    products.product_name,
    SUM(unite_price * quantity) AS sales_amount,
    COUNT(orders.order_id),
    SUM(quantity)
FROM
    products
        LEFT JOIN
    orders 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
FROM
    users
        LEFT JOIN
    orders 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 
    *
FROM
    products
WHERE
    price = (SELECT 
            MIN(price)
        FROM
            products);

8. in，any，all

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

找出下架的商品信息

update products            
set sales_status = "DEPRECATED"
where product_name = "老北京鸡肉卷";

update products            
set sales_status = "DEPRECATED"
where product_name = "夏日缤纷桶";


SELECT 
    *
FROM
    products
WHERE
    sales_status IN (SELECT 
            sales_status
        FROM
            products
        WHERE
            sales_status = 'DEPRECATED');

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

SELECT 
    products.product_id, products.product_name, products.price
FROM
    products
WHERE
    price < ALL (SELECT 
            unite_price
        FROM
            orders,
            products
        WHERE
            products.product_id = orders.product_id);