sql

select a.*, b.*
from LeftTable a
join RightTable b

for a in LeftTable:
    for b in RightTable
        result = concat([a, b])

  -- Get the database free space
  SELECT table_schema "Data Base Name",
  sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
  sum( data_free )/ 1024 / 1024 "Free Space in MB"
  FROM information_schema.TABLES
  GROUP BY table_schema;
  -- Get the database last update ordered by update time then by create time.
  SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
  FROM `TABLES`
  GROUP BY TABLE_SCHEMA
  ORDER BY 1, 2;

  SHOW PROCESSLIST;
  KILL 38239;
  KILL 38240;
  -- Para hacerlo más automático, se puede hacer lo sigiente:
  -- También matchea el propio proceso que ejecuta la query,
  -- por lo que siempre devuelve un resultado al menos
  select concat('KILL ',id,';') from information_schema.processlist -- where INFO like "%FaultyTable%" -- Filtro opcional

  select * from INFORMATION_SCHEMA.PROCESSLIST where db = 'somedb';

  CREATE TABLE db.backup LIKE db.mytable;
  INSERT db.backup SELECT * FROM db.mytable;

  DROP PROCEDURE IF EXISTS filldates;
  DELIMITER |
  CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
  BEGIN
    WHILE dateStart <= dateEnd DO
      INSERT INTO calendar (calendar_date) VALUES (dateStart);
      SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
    END WHILE;
  END;
  |
  DELIMITER ;
  DROP TABLE IF EXISTS calendar
  CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY);
  CALL filldates('2021-01-01','2021-09-06');
  select * from calendar

  DROP PROCEDURE IF EXISTS filldates;
  DELIMITER |
  CREATE PROCEDURE filldates(dateStart TIMESTAMP, dateEnd TIMESTAMP)
  BEGIN
    WHILE dateStart <= dateEnd DO
      INSERT INTO calendar (calendar_date) VALUES (dateStart);
      SET dateStart = adddate(dateStart, INTERVAL 15 MINUTE);
    END WHILE;
  END;
  |
  DELIMITER ;
  DROP TABLE IF EXISTS calendar
  CREATE TABLE IF NOT EXISTS calendar(calendar_date TIMESTAMP );
  CALL filldates('2021-01-01','2021-01-02');

  docker run -it --name some-mysql -v /media/julian/volume/mysql:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw mysql
  mysql -u root -p mysql -h 127.0.0.1  < out.sql

  select *
  from mytable
  join (
    select max(created_at)
    from mytable
    group by id
  ) latest
  on mytable.id = latest.id

  timestamp > lag(timestamp) -- OJO! Esto excluye registros con timestamp nulo!
  timestamp > coalesce(lag(timestamp), min_timestamp)

  -- Hacer un backup de la tabla de table_name_1
  create table table_name_backup as (select * from table_name_1 mg)
  -- Comprobar que el backup se ha creado
  select * from table_name_backup
  -- Tirar la tabla de table_name_1
  drop table table_name_1
  -- Copiar de vuelta los valores en la nueva tabla
  insert into table_name_1 (grid_id, region_id, inside, min_lon, max_lon, min_lat, max_lat)  select grid_id, region_id, inside, min_lon, max_lon, min_lat, max_lat from table_name_backup
  -- Comprobar que la tabla se ha copiado bien desde el backup
  select * from table_name_1
  -- Tirar la tabla de table_name_backup
  drop table table_name_backup

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

  select
  c.name->>"$.translations.en" as "Ciudad",
  coalesce(created_at, 0) as "Día",
  coalesce(sum(created_at >= '2020-04-01 02:00:00' and created_at < '2020-05-01 02:00:00'), 0) as "Registrados",
  coalesce(sum(accept_date >= '2020-04-01 02:00:00' and accept_date < '2020-05-01 02:00:00'), 0) as "Validados"
  from motit.iterable i
  left join motit.`User` u
  on i.num = date(u.created_at)
  left join motit.City c
  on u.city_id = c.id
  where created_at >= '2020-04-01 02:00:00'
  and created_at < '2020-05-01 02:00:00'
  group by 1, 2
  order by c.name->>"$.translations.en", date(created_at)
  Básicamente haces un
  select
  sum(condición 1 que iria en el where) as cuenta1, sum(condición 2 que iria en el where) as cuenta2

  select count(distinct user_id) as users_without_payments
  from user_list ul
  left join Payments p
  on ul.user_id = p.user_id and
  p.payment_at >= '2020-10-01 04:00:00' and p.payment_at < '2021-10-04 22:00:00'
  where p.id is null

  SELECT * FROM t1
  LEFT JOIN t2 ON t1.id = t2.id
  UNION
  SELECT * FROM t1
  RIGHT JOIN t2 ON t1.id = t2.id

  -- Esto para sacar los registros que hay de cada tipo
  -- Luego calibramos las probabilidades aprox con esto
  select cond1, cond2, count(*)
  from table1
  group by cond1, cond2
  select *
  from
  table1
  join ...
  where table1.id = (
  -- Las probabilidades las aproximamos aquí en función del número de registros que queremos
  select id where cond1=1 and cond2=1 and rand() < 0.01
  union
  select id where cond1=0 and cond2=1 and rand() < 0.1
  union
  select id where cond1=1 and cond2=1 and rand() < 0.9
  -- Ponemos rand() < <fraccion> porque asi es más fácil de calcular

LAG(expression [,offset [,default_value]])
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)