聚合与进阶
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);