TiDB tour

0.create.sql

create table products (
  id bigint(20) NOT NULL AUTO_RANDOM,
  name varchar(20),
  price int(10),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id));

create table users (
  id bigint(20) NOT NULL AUTO_RANDOM,
  name varchar(20),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id));

create table orders (
  id bigint(20) NOT NULL AUTO_RANDOM,
  user_id bigint(20) NOT NULL ,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY (user_id),
  PRIMARY KEY (id));

create table order_items (
  id bigint(20) NOT NULL AUTO_RANDOM,
  order_id bigint(20) NOT NULL,
  product_id bigint(20) NOT NULL,
  price int(10) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY (order_id),
  PRIMARY KEY (id));

1.insert.sql

-- 注意仅仅为了演示方便，这里指定了id, 下同
set @@allow_auto_random_explicit_insert = true;

insert into products (id, name, price)
  values
  (10001, '电视机', 1000),
  (20001, '计算机', 5000),
  (30001, '洗衣机', 2000);

select * from products;


insert into users (id, name) values (1, '张三'),(2, '李四'),(3, '王五');

select * from users;


insert into orders (id, user_id) values (101, 1), (201, 2), (301, 3);

insert into order_items (order_id, product_id, price)
  values
  (101, 10001, 1000),
  (101, 20001, 5000),
  (101, 30001, 2000),

  (201, 10001, 1000),
  (201, 20001, 5000),
  (201, 30001, 2000),

  (301, 10001, 1000),
  (301, 20001, 5000),
  (301, 30001, 2000);

3.select.sql

select * 
  from order_items
  where order_id = 101 ;

select o.user_id, u.name, i.order_id, o.created_at, i.product_id, p.name, i.price 
  from
    order_items as i,
    products as p,
    users as u,
    orders as o 
  where 
    o.id=i.order_id 
    and u.id = o.user_id 
    and i.product_id = p.id;

explain ...;

explain analyze ...;