-- 创建 student 表，演示常见类型与约束
create table student (
  id int unsigned not null auto_increment,
  name varchar(50) not null,
  gender enum('m','f') not null default 'm',
  age tinyint unsigned check (age between 0 and 120),
  email varchar(100) unique,
  info json,
  primary key (id)
) engine=innodb default charset=utf8mb4;
show databases;
show tables;
desc student;

-- 查询
select * from student;

-- 插入
insert into student(name, gender, age, email)
values ('jjt','m',21,'3540@qq.com');

-- 批量插入
insert into student(name, gender)
values ('test','f'),('test2','f');

-- 指定列 & 别名
select id, name as student_name, age
from student;

-- 过滤
select *
from student
where age >= 18
  and gender is not null
  and email is not null;

-- 排序 + 分页
select *
from student
order by age desc, id asc
limit 10 offset 0;

-- 批量更新
update student
set age = age + 1
where email is not null or age is null;

-- 指定更新
update student
set age = 34
where id = 3;

-- 删除
delete from student
where id = 3;

select * from student
where name like 'a%';

select * from student
where id in (1,2)
  and age between 19 and 25;

insert into student(name, info)
values ('body','{"hobby":"swimming"}');

select * from student
where age is not null
  or info->>'$.hobby' is not null;

set autocommit = 0;
start transaction;
  insert into student(name) values('dava');
  update student set age = age + 1 where name = 'jjt';
commit;

-- 出错时可 rollback
rollback;
set autocommit = 1;

create table authors (
  author_id int unsigned not null auto_increment,
  name varchar(100) not null,
  email varchar(100) unique,
  primary key (author_id)
) engine=innodb default charset=utf8mb4;

create table book (
  book_id int unsigned not null auto_increment,
  name varchar(50) not null,
  isbn varchar(100) unique,
  status enum('available','loaned') not null default 'available',
  author_id int unsigned not null,
  primary key (book_id),
  foreign key (author_id) references authors(author_id)
) engine=innodb default charset=utf8mb4;

create table loans (
  loan_id int unsigned primary key auto_increment,
  book_id int unsigned not null,
  borrower varchar(100) not null,
  loan_date date not null,
  foreign key (book_id) references book(book_id)
) engine=innodb default charset=utf8mb4;

-- inner join：只返回有记录的行
select
  b.name   as book_name,
  a.name   as author_name
from book b
join authors a on b.author_id = a.author_id
where b.status = 'available';
-- 子查询：找出 jjt 借过的书
select name
from book
where book_id in (
  select book_id from loans where borrower = 'jjt'
);
-- 窗口函数：为每位借书人按时间倒序编号
select
  borrower,
  loan_date,
  row_number() over (
    partition by borrower
    order by loan_date desc
  ) as rn
from loans;

alter table student
  add column phone varchar(20) null after email;

alter table student
  modify column name varchar(100) not null;

alter table loans
  change column return_data return_date date null
  algorithm=copy, lock=exclusive;

alter table student
  drop column info;

create index idx_book_name on book(name);
create unique index ux_book_isbn on book(isbn);
create index idx_book_author_status on book(author_id, status);
show index from book;

create database shop charset=utf8mb4;
use shop;

create table users (
  user_id  int primary key auto_increment,
  username varchar(50),
  email    varchar(50) unique
);

create table products (
  product_id int primary key auto_increment,
  name       varchar(50),
  price      decimal(10,2)
);

create table orders (
  order_id    int primary key auto_increment,
  user_id     int not null,
  product_id  int not null,
  order_date  date,
  foreign key (user_id)    references users(user_id),
  foreign key (product_id) references products(product_id)
);

select
  b.book_id,
  b.name,
  max(l.loan_date) as last_loan_date
from book b
left join loans l on b.book_id = l.book_id
group by b.book_id, b.name;