IT блог Александра Солнышкова

Статьи об информационных технологиях.

Примеры SQL запросов

SELECT

Поиск, как часто встречается конкретное значение:

SELECT COUNT(*) FROM `table1` 
  WHERE `field1` = BINARY 'А';

Выбрать часть строки с начала до первого пробела и сгруппировать строки по полученному результату:

SELECT SUBSTRING(`address_field`, 1, INSTR(TRIM(`address_field`),' ')) a1 
  FROM `table1` GROUP BY a1;

Выбрать и показать значение и количество повторений поля `field`, если оно встречается 2 и более раз:

SELECT `field`, COUNT(`field`) FROM `table1` 
  GROUP BY `field` HAVING ( COUNT(`field`) > 1 );

Выборка строк, где текстовое поле string_field1 имеет неуникальное значение (встречается 2 и более повторений):

SELECT COUNT(*) FROM `table1` 
  WHERE `string_field1` in (
    SELECT `string_field1` FROM `table1` 
	  GROUP BY `string_field1` HAVING COUNT(*) > 1
  );

Выборка строк, где текстовое поле string_field1 имеет неуникальное значение, и даты в поле datetime_field2 позднее самой ранней для данного string_field1:

SELECT COUNT(*) FROM `table1` AS m 
  WHERE `string_field1` in (
    SELECT `string_field1` FROM `table1` 
	  GROUP BY `string_field1` HAVING COUNT(*) > 1
  ) 
  AND DATEDIFF(`datetime_field2`, (
    SELECT MIN(`datetime_field2`) FROM `table1` AS s 
      WHERE s.`string_field1` = m.`string_field1` 
  )) > 0;

Выборка по части поля и со связью текстового и числового полей двух таблиц:

SELECT * FROM `table1` LEFT JOIN `table2` 
  ON (`table1`.`int_field2` = cast(trim(`table2`.`string_field1`) as UNSIGNED)) 
  WHERE `table1`.`string_field4` like CONCAT(trim(`table2`.`string_field4`), ' %');

Найти максимальную длину строки в текстовом поле:

SELECT MAX(CHAR_LENGTH(`string_field1`)) FROM `table1`;

INSERT

Добавление данных из одной таблицы в другую:

INSERT INTO `table2` (`address`, `street`, `house`) 
  SELECT `address`, `street`, `house` FROM `table1` 
    GROUP BY `address`;

Добавление нескольких строк в таблицу:

INSERT INTO `table1` (`id`, `string_field1`, `int_field2`, `datetime_field2`) VALUES
(546, 'Piter', 0, '2014-05-13 11:12:30'),
(548, 'Alex', 0, '2014-05-20 12:36:49'),
(552, 'Jane', 1, '2014-06-24 04:01:55');

UPDATE

Модификация полей `string_field1` и `string_field2`, выбрав строку(строки) из таблицы `table1` по условию, что поле `id` = 1:

UPDATE `table1` 
  SET `string_field1` = 'example1', `string_field2` = 'example2' 
  WHERE `id` = 1;

Вариант по условию, что поле string_field3 начинается с 'abc':

UPDATE `table1` 
  SET `string_field1` = 'example1', `string_field2` = 'example2' 
  WHERE `string_field3` LIKE 'abc%';

Вариант, когда используется соединение с другой таблицей:

UPDATE `table1` t1 
  LEFT JOIN `table2` t2 
  ON (t1.`string_field1` = t2.`string_field1` 
    AND t1.`string_field2` = t2.`string_field2`
  ) 
  SET t1.`string_field3` = t2.`string_field3` 
  WHERE t2.`string_field3` IS NOT NULL;

Объединение значений полей в одном поле, добавляя разделитель:

UPDATE `table1` 
  SET `address_field` 
    = CONCAT_WS(',', `apartment`, `house`, `street`, `town`);

Модификация с выборкой из другой таблицы:

UPDATE `table1` 
  SET `string_field1` = (
    SELECT `string_field2` from `table2` 
	WHERE `table1`.`int_field2` = `table2`.`int_field2`
  );

нужно предварительно проиндексировать поля `int_field` в обоих таблицах.

DELETE

Обычная операция удаления с условием, что длина строки меньше 2 (т.е. один символ):

DELETE FROM `table1` 
  WHERE LENGTH(`field1`) < 2;

Теперь вообразим ситуцию, что при операции удаления из таблицы требуется выполнить подзапрос в ту же таблицу. Вообще MySQL не позволяет запросы вида:

DELETE FROM `table1` 
  WHERE table1.`string_field1` in (
    SELECT b.`string_field1` FROM `table1` AS b 
	GROUP BY b.`string_field1` 
	HAVING COUNT(*) > 1
  ) 
  AND DATEDIFF(table1.`datetime_field2`, (
    SELECT MIN(s.`datetime_field2`) FROM `table1` AS s 
    WHERE s.`string_field1` = table1.`string_field1` 
  )) > 0;

Т.е. SELECT запросы такие делать можно, а вот DELETE - нельзя. Но если очень хочется, можно попробовать использовать временные таблицы:

DELETE FROM `table1` WHERE id in
  SELECT temp.`id` FROM (
    SELECT `id` FROM `table1` AS m 
	WHERE m.`string_field1` in (
	  SELECT `string_field1` FROM `table1` 
	  GROUP BY `string_field1` HAVING COUNT(*) > 1
	) 
	AND DATEDIFF(m.`datetime_field2`, (
	  SELECT MIN(s.`datetime_field2`) FROM `table1` AS s 
	  WHERE s.`string_field1` = m.`string_field1` 
	)) > 0
  ) AS temp;

- такой запрос тоже не работает.

А вот следующий - работает:

DELETE FROM c
  USING 
    `table1` c
      INNER JOIN (
        SELECT `id` FROM `table1` AS m 
		WHERE m.`string_field1` in (
		  SELECT `string_field1` FROM `table1` 
		    GROUP BY `string_field1` HAVING COUNT(*) > 1
		) AND DATEDIFF(m.`datetime_field2`, (
		  SELECT MIN(s.`datetime_field2`) FROM `table1` AS s 
		    WHERE s.`string_field1` = m.`string_field1` 
		)) > 0
      ) d ON c.`id` LIKE d.`id`;

CREATE & ALTER

Создание таблицы:

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string_field1` varchar(100) NOT NULL DEFAULT '',
  `decimal_field2` decimal(10,2) NOT NULL DEFAULT '0.00',
  `datetime_field3` datetime NOT NULL DEFAULT '1900-01-01',
  `int_field4` int(11) NOT NULL DEFAULT '0',
  `tinyint_field5` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'some comments',
  `float_field6` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `string_field1` (`string_field1`),
  KEY `decimal_field2` (`decimal_field2`),
  KEY `int_field4` (`int_field4`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Добавление столбцов в таблице:

ALTER TABLE `table1` 
  ADD COLUMN `string_field1` varchar(254) DEFAULT NULL;
ALTER TABLE `table1` 
  ADD COLUMN `decimal_field2` decimal(3,2) DEFAULT NULL;
ALTER TABLE `table1` 
  ADD COLUMN `int_field3` int(1) DEFAULT NULL;

Изменение столбца в таблице:

ALTER TABLE `table1` 
  CHANGE `string_field1` `string_field1` VARCHAR( 14 ) 
  CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Аналогично:

ALTER TABLE `table1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

Удаление столбца в таблице:

ALTER TABLE `table1` DROP `string_field1`;

DATABASE

Экспорт базы данных в файл:

mysqldump –uUSERNAME –pPASSWORD DATABASE > DATABASE.sql

Импорт базы данных из файла:

mysql –uUSERNAME –pPASSWORD DATABASE < DATABASE.sql

Запуск MySQL запроса из консоли:

mysql -uUSERNAME -pPASSWORD -e "SHOW DATABASES;"

Скопировать всю таблицу вместе с данными:

CREATE TABLE `table2` AS SELECT * FROM `table1` WHERE 1;

Скопировать только структуру таблицы:

CREATE TABLE `table2` AS SELECT * FROM `table1` WHERE 0;

Переименование или перенос таблицы:

RENAME TABLE `database1`.`table1` TO `database2`.`table2`;

Удалить таблицу:

DROP TABLE `table2`;

GRANT

Первоначальная установка пароля root:

mysqladmin -u root password пароль

Смена пароля root:

mysqladmin -u root -p password новый_пароль

Создание локального пользователя с присвоением пароля и полномочий:

GRANT ALL PRIVILEGES ON *.* TO имя_пользователя@localhost IDENTIFIED BY 'пароль' WITH GRANT OPTION;