Category Archives: Databases

SQL coding style

Цель — стараться сделать код как можно более вертикально компактным, но читаемым.

SQL Server: Получить время в формате (hh:mm) из datetime

Для SQL Server 2005

CONVERT( VARCHAR( 8 ), GETDATE(), 108 ) AS HourMinuteSecond, -- hh:mm:ss
CONVERT( VARCHAR( 5 ), GETDATE(), 108 ) AS HourMinute, -- hh:mm
CONVERT( VARCHAR( 8 ), GETDATE(), 101 ) AS DateOnly -- mm/dd/yyyy

CONVERT( VARCHAR( 8 ), GETDATE(), 108 ) AS HourMinuteSecond, -- hh:mm:ss

CONVERT( VARCHAR( 5 ), GETDATE(), 108 ) AS HourMinute, -- hh:mm

CONVERT( VARCHAR( 8 ), GETDATE(), 101 ) AS DateOnly -- mm/dd/yyyy

http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/

MySQL. Восстановление из бэкапа

Понадобилось восстановить бэкап mysql размером примерно в гигабайт. Всякие phpMyAdmin даже сжатый файл кушать отакзываются — ограничение по размеру, а доступа к настройкам нет. Нашёл хороший способ — в командной строке

mysql

mysql>use database <>;
mysql>source path/to/backup_file.sql;

mysql

mysql>use database <>;

mysql>source path/to/backup_file.sql;

Очень быстро и хорошо) http://forums.mysql.com/read.php?104,145923,148081#msg-148081

Аналог ON DUPLICATE KEY UPDATE SQL Server

Есть таблица для записи статистики за день

CREATE TABLE mailStats (
  dt DATE NOT NULL CONSTRAINT PK_mailStats PRIMARY KEY,
  total INT NOT NULL,
  failed INT NOT NULL,
);

CREATE TABLE mailStats (

dt DATE NOT NULL CONSTRAINT PK_mailStats PRIMARY KEY,

total INT NOT NULL,

failed INT NOT NULL,

);

Нужно записать в неё общее количество действий и количество фэйлов скриптом, запускающимся по крону к примеру раз в час. При этом мы не знаем, создана ли запись на сегодняшний день, а выполнить всё хочется за один запрос. Тут бы на помощь пришёл «ON DUPLICATE KEY UPDATE» от …

PhpStorm DB Navigator

Чтобы работать напрямую с MySQL-базой из PhpStorm: Установить плагин Database Navigator (перезапустить IDE — появится пункт DB Navigator). Скачать jdbc-драйвер (http://www.mysql.com/downloads/connector/j/) DB Navigator → Settings → add connection: driver library — …mysql-connector-java-5.1.16-bin.jar url — jdbc:mysql://127.0.0.1:3306 user — root

MySQL: что быстрее, многоколоночный индекс или пересечение индексов (index merge)

Вкратце: если в условии WHERE разные столбцы соединяются условием OR, то лучше иметь отдельные индексы по этим полям, во всех остальных случаях нужно использовать многоколоночный индекс (данные по MySQL 5.2.4). Подробнее.

PostgreSQL — генерирование повторяющихся значений для неупорядоченных данных

Была задача: есть некоторое количество заказов, которым оказались не назначены менеджеры, есть 3 менеджера, между которыми нужно распределить эти заказы равномерно. Легче всё сделать на php или python, но задача была сделать всё при помощи SQL-запроса без хранимых процедур и желательно одним запросом.

Postgresql-аналоги myslq-евской «ON DUPLICATE KEY UPDATE»

В mysql есть очень удобная конструкция

INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2

1	INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2

Это очень удобно тогда, когда нужно вставить только те данные, которых в таблице нет (при этом в апдейте указывается уже существующее значение поля). В postgresql такого, к сожалению, нет. Но подобное поведение можно сэмулировать несколькими способами. Два самых интересных:

PostgreSQL EXPLAIN

При работе с MySql’овским EXPLAIN’ОМ всё просто (а может просто привычно). Postgres предоставляет больше информации, но с ним сложнее разобраться. Далее — вольный перевод статьи про PostgreSQL EXPLAIN (ссылка на оригинал в конце статьи).

Генерирование данных в PostgreSQL

Задача: Рекурсивно поднимаем статистику по количеству аккаунтов на указанную дату. При этом у аккаунта есть только период с-по. Основная задача — сгенерировать даты.