Akina

-- рабочая таблица
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT,
  project_number INT
  );

-- опорная таблица для генерации номеров в разрезе проектов
CREATE TABLE generate_project_number (
  project_id INT,
  project_number INT AUTO_INCREMENT,
  PRIMARY KEY (project_id, project_number)
  ) ENGINE = MyISAM;

-- триггер-генератор
CREATE TRIGGER generate_project_number
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- вставить хапись в опорную таблицу для генерации номера
  INSERT INTO generate_project_number (project_id) VALUES (NEW.project_id);
  -- вставить сгенерированный номер в рабочую таблицу
  SET NEW.project_number = LAST_INSERT_ID();
  -- почистить опорную таблицу от лишних записей
  DELETE FROM generate_project_number 
  WHERE project_id = NEW.project_id
    AND project_number < NEW.project_number;
END

SELECT * 
FROM `table1` 
JOIN `table2` ON `title`=`sometitle` 
WHERE NOT EXISTS ( SELECT NULL
                   FROM table3
                   WHERE table1.id = table3.id )
ORDER BY `date`

SELECT * 
FROM `table1` 
JOIN `table2` ON `title`=`sometitle` 
LEFT JOIN table3 ON table1.id = table3.id
WHERE table3.id IS NULL
ORDER BY `date`

INSERT INTO test (a,b,c,d)
SELECT a,b,c,d
FROM JSON_TABLE(
  CONCAT('[', REPLACE(REPLACE(@list, '(', '['), ')', ']'), ']'),
  '$[*]' COLUMNS (
    a INT PATH '$[0]',
    b INT PATH '$[1]',
    c INT PATH '$[2]',
    d INT PATH '$[3]'
    )                   
  ) jsontable

SELECT name, `text`
FROM product
JOIN product_attribute USING (product_id)
JOIN attribute_description USING (attribute_id)
-- JOIN attribute USING (attribute_id)   -- таблица в запросе не нужна
GROUP BY 1, 2
HAVING COUNT(*) > 1;

SELECT `id`, 
       (SELECT COUNT(*) FROM `table2` WHERE `id` = table1.id)  AS `count` 
FROM `table1` 
WHERE ....;

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() /* либо 'database_name' */
  AND TABLE_NAME = 'table_name'

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'table_name'

SELECT categories.id, 
       categories.slug,  
       ct_ru.title title_ru,  
       ct_en.title title_en,  
       ct_de.title title_de
FROM categories
LEFT JOIN category_translations ct_ru ON category_translations.category_id=categories.id 
                                     AND category_translations.locale = 'ru'
LEFT JOIN category_translations ct_en ON category_translations.category_id=categories.id 
                                     AND category_translations.locale = 'en'
LEFT JOIN category_translations ct_de ON category_translations.category_id=categories.id 
                                     AND category_translations.locale = 'de';

CREATE TRIGGER trigger_name
EVENT ACTION ON table_name
FOR EACH ROW
BEGIN
    SET @trigger_name_table_name = CONCAT('executed, ', 'column_value=', NEW.column);
-- trigger action
    SET @trigger_name_table_name = NULL;
END;