SQL Snippets

SQL_Snippets.md

h1. Коллекция сниппетов SQL

h2. Сайты для тестирования SQL

• http://sqlfiddle.com/
• https://dbfiddle.uk/

h2. Создать таблицу с возможностью сохранять дату изменения строки

CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

h2. Добавить колонку, если не существует

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

h2. Вывести таблицы, измененные за последние 10 мин

SELECT *
FROM information_schema.tables
WHERE update_time >= (NOW() - INTERVAL 10 MINUTE);

h2. Получить количество ВУ не соответствующих правилам

Для РФ

SELECT COUNT(*) from pp_docs_drivers_license WHERE CHAR_LENGTH(series)!=4 OR CHAR_LENGTH(number_doc)!=6 OR series='0000' OR number_doc='000000';

Для иностранных

SELECT COUNT(*) from pp_docs_drivers_license_another WHERE CHAR_LENGTH(series)!=4 OR CHAR_LENGTH(number_doc)!=6 OR series='0000' OR number_doc='000000';

h2. Вывести 10 пользователей у которых есть прикрепленные pdf файлы

SELECT pp.last_name, pp.first_name, pp.father_name FROM files_in AS fi INNER JOIN private_person AS pp ON pp.id=fi.owner WHERE fi.file_link LIKE '%.pdf' GROUP BY pp.id LIMIT 10;

h2. Получить все типы документов

SELECT DISTINCT fi.type_files FROM files_in AS fi;

h2. Конвертировать дату в timestamp

SELECT UNIX_TIMESTAMP(STR_TO_DATE('01.5.2013','%d.%m.%Y'));

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

h2. Конвертировать timestamp в дату

SELECT FROM_UNIXTIME(19342800, '%d.%m.%Y');

h2. Получить данные клиента, статус и id заявки

SELECT pp.last_name, pp.first_name, pp.father_name, pp.gender, FROM_UNIXTIME(pp.birthdate) AS birthdate, pp.birthplace, ddl.series AS driver_license_series, ddl.number_doc AS driver_license_number_doc, FROM_UNIXTIME(ddl.issue_date) AS driver_license_issue_date, a.id AS application_id, a.status AS application_status 
FROM private_person AS pp 
LEFT JOIN pp_documents_to_person dtp ON dtp.pp_id=pp.id 
LEFT JOIN pp_docs_drivers_license AS ddl ON ddl.id=dtp.doc_id AND dtp.type_doc='drivers_license' 
LEFT JOIN application AS a ON a.user_pp_id=pp.id 
WHERE pp.last_name='Шчёьгфгвзф' AND pp.first_name='Дывккстпдш' AND pp.birthdate=UNIX_TIMESTAMP(STR_TO_DATE('13.08.1970', '%d.%m.%Y')) 
LIMIT 10;

h2. Получить количество запросов из лога сгруппированных по типу

SELECT t.type_change, COUNT(*) FROM (SELECT type_change, COUNT(*) AS c FROM log_sql GROUP BY type_change, record_id) AS t GROUP BY type_change;

h2. Сменить движок у таблицы на ARCHIVE

h3. Вариант 1

ALTER TABLE my_table AUTO_INCREMENT=0, ENGINE=ARCHIVE;

h3. Вариант 2

ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;

h3. Вариант 3

CREATE TABLE date_obook_new LIKE date_obook;
ALTER TABLE date_obook_new DROP PRIMARY KEY;
ALTER TABLE date_obook_new ENGINE=ARCHIVE;
INSERT INTO date_obook_new SELECT * FROM date_obook;
ALTER TABLE date_obook RENAME date_obook_old;
ALTER TABLE date_obook_new RENAME date_obook;
DROP TABLE date_obook_old;

h2. Удалить все таблицы в базе

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'database_name'; -- specify DB name here.

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

h2. Получить последние записи из групп

      SELECT 
        ut1.*
      FROM 
        underwriter_timebreaks ut1 
      LEFT JOIN 
        underwriter_timebreaks ut2
      ON 
        (ut1.user_id = ut2.user_id/*Признак группы*/ AND ut1.id < ut2.id)
      WHERE 
        ut2.id IS NULL;

h2. Получить всех пользователей авторизировавшихся сегодня.

          SELECT 
            COUNT(*) as count 
          FROM 
            sys_users 
          WHERE 
            first_login>DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')

h2. Подобрать заявки имеющие в истории статус effecting и approved

	(SELECT 
		l.date_change,
		l.new_value,
        l.line_db
	FROM
		ilogs l
	WHERE
		l.table_db = 'application'
		AND l.field_db = 'status'
		AND l.new_value = 'effecting'
		AND l.line_db = '2341880'
	LIMIT 100)
UNION
	(SELECT 
		l.date_change,
		l.new_value,
        l.line_db
	FROM
		ilogs l
	WHERE
		l.table_db = 'application'
		AND l.field_db = 'status'
		AND l.new_value = 'approved'
		AND l.line_db = '2341880'
	LIMIT 100)
ORDER BY
	date_change