MySQL Tutorial

select 1, 2;

select * from customers where state = 'va';

select *
  from customers
 where birth_date > '1990-01-01';

insert into customers
values (default, 'babb', 'chen', default, default, '18896763538', 'henan', 'xy', default);

update customers
  set points = default
where customer_id = 1;

insert into shippers (name)
values ('Shipper1'),
       ('Shipper2'),
       ('Shipper3');

insert into orders (customer_id, order_date, status)
values (1, '2010-02-02', 1);

insert into order_items
values (last_insert_id(), 1, 1, 2.95), -- last_insert_id() 插入 orders 中的 order_id
       (last_insert_id(), 2, 1, 3.95);

select *
  from customers
 where last_name regexp 'field|mac|rose';

select *
  from customers
 where last_name regexp '[gim]e';

select *
  from customers
 where last_name regexp '^b.*?[gim]e';

select *
  from customers
 limit 6, 3;

select client_id, sum(invoice_total) as total_sales
  from invoices
 group by client_id;

use sql_invoicing;

select *
  from sql_invoicing.invoices;

select max(invoice_total) as highest,
       min(invoice_total) as lowest,
       avg(invoice_total) as average,
       sum(invoice_total) as total,
       count(invoice_total) as number_of_invoices,
       count(payment_date) as number_of_payments,
       count(*) as total_records,
       count(client_id) as number_of_clients,
       count(distinct client_id) as number_of_distinct_clients
from invoices;

select client_id,
       sum(invoice_total) as total_sales
  from invoices
 group by client_id with rollup;

select state,
       city,
       sum(invoice_total) as total_sales
  from invoices i
  join clients using (client_id)    --using (client_id) <=> invoices.client_id = clients.client_id
 group by state, city with rollup;

SELECT
    ROUND(5.73),
    ROUND(5.73, 1),
    TRUNCATE(5.73, 1),
    CEILING(5.73),
    FLOOR(5.73),
    RAND();

select upper('sky') a, lower('Sky') b,
       ltrim(' sky') c, rtrim('sky ') d, trim(' sky ') e,
       left('babb chen', 4) f, right('babb chen', 4) g, -- left, right 返回前,后几个字符
       substring('babb chen', 6, 4) h,
       locate('c', 'babb chen') i, locate('chen', 'babb chen') j,  -- locate 返回字符或字符串索引
       replace('babb chen', 'babb', 'bob') k,
       concat('babb', 'chen') l;

select now(), curdate(), curtime();

select year(now()), month(now()), day(now()), hour(now()), minute(now()), second(now());

select extract(year from now()) as year,
       extract(month from now()) as month,
       extract(day from now()) as day,
       extract(hour from now()) as hour,
       extract(minute from now()) as minute,
       extract(second from now()) as second;

select date_format(now(), '%Y-%m-%d %H:%i %p') as date;

date
-----
2020-09-12 16:14 PM

select date_add(now(), interval 1 day) as tomorrow,
       date_sub(now(), interval 1 day) as yesterday,
       (select datediff(tomorrow, yesterday)) as datediff,
       time_to_sec('09:00') - time_to_sec('09:02') as secdiff;

select ifnull(null, 'unknown') column_a,
       ifnull('a', 'unknown') column_b,
       coalesce(null, null, null, 'b') column_c,
       coalesce(null, 'a', 'b') column_d;

column_a    column_b    column_c    column_d
-------     --------    -------     --------
unknown        a            b            a

select order_id,
       if(year(order_date) = year(now()),
          'Active',
          'Archived') as category
  from orders;

select order_id,
       case
          when year(order_date) = year(now()) then 'Active'
          when year(order_date) = year(now()) - 1 then 'Last Year'
          when year(order_date) < year(now()) - 1 then 'Archived'
          else 'Future'
       end as category
  from orders;

create or replace view invices_with_balance as
select invoice_id,
       number,
       client_id,
       invoice_total,
       payment_total,
       invoice_total - payment_total as balance,
       invoice_date,
       due_date,
       payment_date
  from invoices
 where (invoice_total - payment_total) > 0
  with check option;

select * from invices_with_balance;

update invices_with_balance
   set payment_total = invoice_total
 where invoice_id = 1;

delimiter $$

drop procedure if exists get_unpaid_invoices_for_client;

create procedure get_unpaid_invoices_for_client(
    client_id int,
    out invoices_count int,
    out invoices_total decimal
)
begin
    select count(*), sum(invoice_total)
      into invoices_count, invoices_total
      from invoices i
     where i.client_id = client_id
       and i.payment_total = 0;
end $$

delimiter ;

set @invoices_count = 0;
set @invoices_total = 0;
call get_unpaid_invoices_for_client(5, @invoices_count,  @invoices_total);
select @invoices_count,  @invoices_total;

@invoices_count   @invoices_total
---------------  ----------------
3                 490

delimiter $$

drop function if exists get_risk_factor_for_client;

create function get_risk_factor_for_client(client_id int)
returns integer
-- deterministic -- 确定性，给定函数参数一组值永远返回相同的值
-- modifies sql data -- 修改 sql 数据
reads sql data -- 读取 sql 数据
begin
    declare risk_factor decimal(9, 2) default 0;
    declare invoices_total decimal(9, 2);
    declare invoices_count int;

    select count(*), sum(invoice_total)
      into invoices_count, invoices_total
      from invoices
     where client_id = client_id;

    set risk_factor = invoices_total / invoices_count * 5;

    return ifnull(risk_factor, 0);

end $$

delimiter ;


select
   client_id,
   name,
   get_risk_factor_for_client(client_id) as risk_factor
 from clients;

show variables like 'event%';
set global event_scheduler = on;

delimiter $$

drop event if exists yearly_delete_stale_audit_rows;

create event yearly_delete_stale_audit_rows
on schedule
    -- at '2019-05-01'
    every 1 year starts '2019-01-01' ends '2029-01-01'
do
begin
    delete from payment_audit
    where action_date < now() - interval 1 year;  -- <=> datesub(now(), interval 1 year)
end $$

delimiter ;

show events;
show events like 'yearly%';

alter event yearly_delete_stale_audit_rows disable;
alter event yearly_delete_stale_audit_rows enable;

show variables like 'autocommit';

Variable_name    Value
------------    -----
autocommit        ON

start transaction; -- 1

insert into orders(customer_id, order_date, status)
values (1, '2020-01-01', 1); -- 2

insert into order_items
values (last_insert_id(), 1, 1, 1); -- 3

commit;

select * from customers where customer_id = 1;

show variables like 'transaction_isolation';

Variable_name            Value
--------------           ------------
transaction_isolation    REPEATABLE-READ

set session transaction isolation level serializable;
set global transaction isolation level serializable;

update products set properties  = '
{
    "dimensions": [1, 2, 3],
    "weight": 10,
    "manufactor": { "name": "sony" }
}
'
where product_id = 1;

select *
  from products
 where product_id = 1;

update products set properties  =  json_object(
    'weight', 10,
    'dimensions', json_array(1, 2, 3),
    'manufactor', json_object('name', 'sony')
)
where product_id = 1;

select json_extract(properties, '$.weight') as extract_weight,
       properties -> '$.weight' as weight,
       properties -> '$.dimensions[0]' as x,
       properties -> '$.dimensions[1]' as y,
       properties -> '$.dimensions[2]' as z,
       properties -> '$.manufactor.name' as manufactor_in_quotes,
       properties ->> '$.manufactor.name' as manufactor
  from products
 where properties ->> '$.manufactor.name' = 'sony';

update products
   set properties = json_set(
        properties,
        '$.weight', 20,
        '$.age', 10
    )
 where product_id = 1;

update products
   set properties = json_remove(
        properties,
        '$.age'
    )
 where product_id = 1;