MySQL 9.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


9.7.2


create table customer (id int primary key, name varchar(10) not null);
create table product (id int primary key, name varchar(10) not null, price decimal(6,2) not null);
create table orders (id int primary key, customer_id int not null references customer (id), ordered_on date not null);
create table order_line (order_id int references orders (id), product_id int references product (id), qty int not null, primary key (order_id, product_id));
insert into customer values (1,'ann'), (2,'bob'), (3,'cat');
insert into product values (1,'widget',2.50), (2,'gadget',7.00), (3,'gizmo',12.00);
insert into orders with recursive s (n) as (select 0 union all select n+1 from s where n < 51) select n, 1+n%3, date '2026-01-01' + interval n*7 day from s;
insert into order_line select o.id, p.id, 1+(o.id*p.id)%5 from orders o cross join product p where (o.id+p.id)%2=0 or p.id=1;

Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 52  Duplicates: 0  Warnings: 0



Records: 104  Duplicates: 0  Warnings: 0


-- new in MySQL 9.7: materialized views
create materialized view customer_revenue as
select c.name, sum(l.qty * p.price) as revenue
from customer c join orders o on o.customer_id = c.id join order_line l on l.order_id = o.id join product p on p.id = l.product_id
group by c.name;
select * from customer_revenue order by revenue desc;


name
revenue


ann
648.00

bob
639.00

cat
575.50


# python demo
import mysql.connector
c = mysql.connector.connect(unix_socket="/var/run/mysqld/mysqld.sock", user="root", database="fiddle")
cur = c.cursor()
cur.execute("select p.name, sum(l.qty) from product p join order_line l on l.product_id = p.id group by p.name order by 2 desc")
for name, qty in cur:
    print(f"{name:>6} {qty:4}  {chr(9608) * int(qty // 8)}")
widget  153  ███████████████████
 gizmo   79  █████████
gadget   76  █████████

-- echarts demo
with recursive
  mon (m) as (select date '2026-01-01' union all select m + interval 1 month from mon where m < date '2026-12-01')
, rev as
    ( select p.name, mon.m, coalesce(sum(l.qty*p.price),0) r
      from
        product p cross join
        mon left join
        (orders o join order_line l on l.order_id = o.id) on date_format(o.ordered_on, '%Y-%m-01') = mon.m and l.product_id = p.id
      group by p.name, mon.m )
select
  json_object
  ( 'tooltip', json_object('trigger','axis')
  , 'legend', json_object()
  , 'xAxis', json_object('type','category','data',(select cast(concat('[', group_concat(json_quote(date_format(m,'%b')) order by m), ']') as json) from mon))
  , 'yAxis', json_object('type','value')
  , 'series', (select cast(concat('[', group_concat(json_object('name',name,'type','bar','stack','revenue','data',data) order by name), ']') as json) from (select name, cast(concat('[', group_concat(r order by m), ']') as json) data from rev group by name) s)
  ) as `option`;


option


{"xAxis": {"data": ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"], "type": "category"}, "yAxis": {"type": "value"}, "legend": {}, "series": [{"data": [70.0, 35.0, 42.0, 49.0, 21.0, 63.0, 42.0, 63.0, 35.0, 42.0, 49.0, 21.0], "name": "gadget", "type": "bar", "stack": "revenue"}, {"data": [108.0, 36.0, 84.0, 96.0, 84.0, 72.0, 60.0, 108.0, 36.0, 144.0, 36.0, 84.0], "name": "gizmo", "type": "bar", "stack": "revenue"}, {"data": [37.5, 25.0, 27.5, 37.5, 30.0, 32.5, 37.5, 35.0, 25.0, 37.5, 27.5, 30.0], "name": "widget", "type": "bar", "stack": "revenue"}], "tooltip": {"trigger": "axis"}}


-- mermaid demo
select
  concat_ws
  ( '\n'
  , 'erDiagram'
  , ( select group_concat(concat('  ', t.table_name, ' {\n', x.cols, '\n  }') order by t.table_name separator '\n')
      from
        information_schema.tables t join
        ( select
            c.table_name
          , group_concat(concat_ws(' ', '   ', c.data_type, c.column_name, nullif(concat_ws(', ', if(c.column_key = 'PRI', 'PK', null), if(f.column_name is not null, 'FK', null)), '')) order by c.ordinal_position separator '\n') cols
          from
            information_schema.columns c left join
            information_schema.key_column_usage f on f.table_schema = c.table_schema and f.table_name = c.table_name and f.column_name = c.column_name and f.referenced_table_name is not null
          where c.table_schema = database()
          group by c.table_name
        ) x on x.table_name = t.table_name
      where t.table_schema = database() and t.table_type = 'BASE TABLE' )
  , ( select group_concat(concat('  ', table_name, ' }o--|| ', referenced_table_name, ' : "', column_name, '"') order by table_name, referenced_table_name separator '\n')
      from information_schema.key_column_usage
      where table_schema = database() and referenced_table_name is not null )
  ) as diagram;


diagram


erDiagram
  customer {
    int id PK
    varchar name
  }
  order_line {
    int order_id PK, FK
    int product_id PK, FK
    int qty
  }
  orders {
    int id PK
    int customer_id FK
    date ordered_on
  }
  product {
    int id PK
    varchar name
    decimal price
  }
  order_line }o--|| orders : "order_id"
  order_line }o--|| product : "product_id"
  orders }o--|| customer : "customer_id"