-- 1. 创建 shop 库并切换
create database shop charset=utf8mb4;
use shop;

-- 2. 创建 users 表：user_id 主键自增，email 唯一
create table users (
  user_id  int primary key auto_increment,
  username varchar(50) not null,
  email    varchar(50) unique
) engine=innodb default charset=utf8mb4;

-- 3. 创建 products 表：product_id 主键自增
create table products (
  product_id int primary key auto_increment,
  name       varchar(50) not null,
  price      decimal(10,2) not null
) engine=innodb default charset=utf8mb4;

-- 插入多条 users
insert into users (username,email)
values ('tets1','3540@qq.com'),
       ('test2','test@gmail.com'),
       ('jjt','jjt@qq.com');

-- 插入多条 products
insert into products (name,price)
values ('牛奶',5.00),
       ('香蕉',2.00),
       ('苹果',6.00);

-- 查询全表
select * from users;
select * from products;

-- 更新：修改 user_id=1 的 email
update users
set email = 'test@qq.com'
where user_id = 1;

-- 删除：移除 product_id=3 的商品
delete from products
where product_id = 3;

-- 1. 条件过滤
select * from products
where price > 3.00;            -- 价格大于 3

select * from products
where name like '%牛%';         -- 名称包含“牛”

-- 2. 多字段排序
select * from products
order by price desc,            -- 先按价格降序
         name  asc;             -- 再按名称升序

-- 3. 分页：第 2 页，每页 2 条
select * from products
order by product_id asc
limit 2 offset 2;

-- 4. 批量更新：5–10 元之间打 5 折
update products
set price = price * 0.5
where price between 5.00 and 10.00;

-- 5. 批量删除：删除价格为 0 或 null
delete from products
where price = 0.00
   or price is null;

-- 1. 查看父表结构
desc users;
desc products;

-- 2. 创建 orders 表
create table orders (
  order_id   int primary key auto_increment,
  user_id    int not null,
  product_id int not null,
  order_date date not null,
  foreign key (user_id)
    references users(user_id)
    on delete cascade,          -- 删除用户时级联删除订单
  foreign key (product_id)
    references products(product_id)
    on delete restrict          -- 禁止删除有订单的商品
) engine=innodb default charset=utf8mb4;
-- 3. 插入合法记录
insert into orders (user_id,product_id,order_date)
values (1,1,'2025-05-16'),
       (2,2,'2025-05-16'),
       (3,1,'2025-05-15');

-- 4. 插入非法外键（观察错误）
insert into orders (user_id,product_id,order_date)
values (1,99,'2025-05-17');

-- 普通索引
create index idx_users_username on users(username);
create index idx_products_price on products(price);

-- 唯一索引
create unique index ux_products_name on products(name);

-- 复合索引：orders(user_id,order_date)
create index idx_orders_user_date on orders(user_id,order_date);
-- 对比：有索引 vs 无索引
explain analyze
select * from orders
where user_id = 1
  and order_date = '2025-05-16';

explain analyze
select * from orders
where order_date = '2025-05-16';

-- 1. inner join：查询每位用户及购买过的商品名
select
  u.user_id,
  u.username,
  p.name as product_name
from users u
inner join orders o on u.user_id = o.user_id
inner join products p on o.product_id = p.product_id;

-- 2. left join + 子查询：查询每位用户最近一次下单日期
select
  u.user_id,
  u.username,
  o.last_order_date
from users u
left join (
  select user_id, max(order_date) as last_order_date
  from orders
  group by user_id
) o on u.user_id = o.user_id;

-- 3. group by + count：按用户统计订单数，过滤 >1
select
  u.user_id,
  u.username,
  count(o.order_id) as order_count
from users u
join orders o on u.user_id = o.user_id
group by u.user_id,u.username
having order_count > 1;

-- 4. group by + sum：按商品统计销售总额
select
  p.product_id,
  p.name,
  sum(p.price) as total_sales
from products p
join orders o on p.product_id = o.product_id
group by p.product_id,p.name;

-- 1. 增加列：users.signup_date
alter table users
  add column signup_date date not null default current_date;

-- 2. 修改列类型：products.price → decimal(10,2)
alter table products
  modify column price decimal(10,2);

-- 3. 重命名列：orders.order_date → purchase_date
alter table orders
  rename column order_date to purchase_date;

-- 4. 删除列：orders.metrics
alter table orders
  drop column metrics;

-- 5. 重建复合索引：调整列顺序
alter table orders
  drop index idx_orders_user_date,
  add index idx_orders_date_user (purchase_date,user_id);
-- 验证：插入、更新、查询
insert into orders (user_id,product_id,purchase_date)
values (3,2,current_date());

update orders
set purchase_date = '2025-05-17'
where order_id = 3;

select * from orders
where user_id = 3
  and purchase_date between '2025-05-13' and '2025-05-20';