Основные команды SQL, которые должен знать каждый программист

Last Spark

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.

Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

mysql -u root -p

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

Команды для работы с базами данных

1. Просмотр доступных баз данных

SHOW DATABASES;

2. Создание новой базы данных

CREATE DATABASE;

3. Выбор базы данных для использования

USE <database_name>; 

4. Импорт SQL-команд из файла .sql

SOURCE <path_of_.sql_file>; 

5. Удаление базы данных

DROP DATABASE <database_name>; 

Работа с таблицами

6. Просмотр таблиц, доступных в базе данных

SHOW TABLES; 

7. Создание новой таблицы

CREATE TABLE <table_name1> (
  <col_name1> <col_type1>,
  <col_name2> <col_type2>,
  <col_name3> <col_type3>
  PRIMARY KEY (<col_name1>),
  FOREIGN KEY (<col_name2>) REFERENCES <table_name2>(<col_name2>)
); 

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

• ячейка таблицы не может иметь значение NULL;
• первичный ключ — PRIMARY KEY (col_name1, col_name2, …);
• внешний ключ — FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

CREATE TABLE instructor (
  ID CHAR(5),
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  salary NUMERIC(8,2),
  PRIMARY KEY (ID),
  FOREIGN KEY (dept_name) REFERENCES department(dept_name)
); 

8. Сведения о таблице

Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой:

DESCRIBE <table_name>; 

9. Добавление данных в таблицу

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
  VALUES (<value1>, <value2>, <value3>, …); 

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

INSERT INTO <table_name>
  VALUES (<value1>, <value2>, <value3>, …); 

10. Обновление данных таблицы

UPDATE <table_name>
  SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  WHERE <condition>; 

11. Удаление всех данных из таблицы

DELETE FROM <table_name>; 

12. Удаление таблицы

DROP TABLE <table_name>; 

Команды для создания запросов

13. SELECT

SELECT используется для получения данных из определённой таблицы:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>; 

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM <table_name>; 

14. SELECT DISTINCT

В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.

SELECT DISTINCT <col_name1>, <col_name2>, …
  FROM <table_name>; 

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>; 

В запросе можно задавать следующие условия:

• сравнение текста;
• сравнение численных значений;
• логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; 

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT, MAX, MIN, SUM и AVG, для группировки выходных значений.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  GROUP BY <col_namex>; 

Пример

Выведем количество курсов для каждого факультета:

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name; 

17. HAVING

Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями.

SELECT <col_name1>, <col_name2>, ...
  FROM <table_name>
  GROUP BY <column_namex>
  HAVING <condition> 

Пример

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

SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name
  HAVING COUNT(course_id)>1; 

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  ORDER BY <col_name1>, <col_name2>, … ASC|DESC; 

Пример

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

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC; 

19. BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> BETWEEN <value1> AND <value2>; 

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor
  WHERE salary BETWEEN 50000 AND 100000; 

20. LIKE

Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE:

• % (ни одного, один или несколько символов);
• _ (один символ).

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> LIKE <pattern>; 

Пример

Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___'; 

21. IN

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namen> IN (<value1>, <value2>, …); 

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

SELECT * FROM student
  WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’); 

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SELECT <col_name1>, <col_name2>, …
  FROM <table_name1>
  JOIN <table_name2>
  ON <table_name1.col_namex> = <table2.col_namex>; 

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

SELECT * FROM course 
    JOIN department 
    ON course.dept_name=department.dept_name;

Пример 2

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

SELECT prereq.course_id, title, dept_name, credits, prereq_id
  FROM prereq
  LEFT OUTER JOIN course
  ON prereq.course_id=course.course_id; 

Пример 3

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

SELECT course.course_id, title, dept_name, credits, prereq_id
  FROM prereq
  RIGHT OUTER JOIN course
  ON prereq.course_id=course.course_id; 

23. View

View — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных.

Создание

CREATE VIEW <view_name> AS
  SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>; 

Удаление

DROP VIEW <view_name>; 

Пример

Создадим view, состоящую из курсов с 3 кредитами:

24. Агрегатные функции

Эти функции используются для получения совокупного результата, относящегося к рассматриваемым данным. Ниже приведены общеупотребительные агрегированные функции:

• COUNT (col_name) — возвращает количество строк;
• SUM (col_name) — возвращает сумму значений в данном столбце;
• AVG (col_name) — возвращает среднее значение данного столбца;
• MIN (col_name) — возвращает наименьшее значение данного столбца;
• MAX (col_name) — возвращает наибольшее значение данного столбца.

25. Вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT, FROM и WHERE, вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id
  FROM section
  WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
    SELECT course_id
    FROM section
    WHERE semester = ‘Spring’ AND year= 2010
  ); 

Смотрите также: Подборка книг по SQL и теории баз данных

Транзакции в PostgreSQL версии 8.0

Транзакции в PostgreSQL версии 8.0

Автор: Джошуа Д. Дрейк

Несомненно, транзакции очень хороши, но транзакции в предыдущих версиях PostgreSQL пропагандировали лозунг – “все, или ничего”, останавливая транзакцию, если ошибка произошла в ее пределах. К счастью, новая версия PostgreSQL 8 позволяет взглянуть на это подругому, добавляя “savepoints” (точки сохранения), позволяя откатить назад только часть транзакции и восстановиться от ошибки изящно.

Одна из очень хороших особенностей PostgreSQL – транзакции. Они предотвращают случайную потерю данных или их искажение.

Например, скажем, вы хотите удалить записи в таблице. В PostgreSQL команда выглядит так:

template1=# DELETE FROM foo;

Однако, данная команда удаляет все записи в таблице. Это, вероятно, вовсе не то, чего вы хотите, и, если вы не использовали транзакцию, восстановление из резервной копии будет единственным способом восстановить базу. Используя транзакции, вернуть данные очень просто:

BEGIN;

DELETE FROM foo;

DELETE 50

Параметр BEGIN заставляет базу начать транзакцию. Поскольку, скоро вы понимаете, что забыли включить параметр WHERE, и удалили все столбцы, то вам необходимо будет сделать обратную перемотку и восстановить данные:

BEGIN;

DELETE FROM foo;

DELETE 50

ROLLBACK;

Есть один недостаток в такой реализации транзакций – если внутри транзакции произошла ошибка, вы вынуждены сделать перемотку. Команда перемотки должна будет выполнена в пределах транзакции, до того, как произведены какие-либо команды в пределах соединения. Как только перемотка будет выполнена, вы должны будете повторить действия снова в том виде, который не будет вызывать ошибку. Это правило включает в себя и ошибки пользователей: типа удаления всех отчетов в таблице; синтаксические ошибки: типа попытки выбрать из столбца, который не существует. Например:

BEGIN;

UPDATE foo SET bar = (SELECT count(*) FROM baz));

INSERT INTO foo (column1) SELECT column2 FROM bar;

ОШИБКА: отношение "bar" не существует

CREATE TABLE bar (column1 text, column2 float);

ОШИБКА: текущая транзакция прервана

команды, игнорируемые до конца блока

Из-за ошибки вы будете делать перемотку и вся ваша текущая работа будет потеряна. Этот специфический момент транзакций PostgreSQL особенно раздражает в период отладки и тестирования.

Точки сохранения – путь к спасению

Новая версия PostgreSQL 8 обращается к этой проблеме с помощью точек сохранения - 'savepoints'. Точки сохранения - это именованные метки, которые разбивают транзакцию на этапы, заставляя базу сохранять данные в рамках каждого этапа. В случае ошибки, мы можем определить этап на котором она возникла, и сделать перемотку только до предыдущей точки сохранения, не теряя при этом работу, которая лежит перед savepoint с ошибкой. Поскольку кажная savepoint имеет свое имя, то и обращаться к ней необходимо по ее имени.

Чтобы инициализировать savepoint, вы должны быть в пределах операционного блока:

template1=# BEGIN;

BEGIN

template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);

INSERT 17231 1

template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);

INSERT 17232 1

template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);

INSERT 17233 1

template1=# SELECT * FROM foo;

column1 | column2 | column3

--------+---------+--------

    1   |   2     |   0  

    1   |   2     |   0

    1   |   2     |   0

(3 rows)

template1=# SAVEPOINT main_values_inserted;

SAVEPOINT

template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,1/0);

ОШИБКА: деление на ноль

ОШИБКА: деление на ноль

В предыдущей версии PostgreSQL, вы потеряли бы все INSERT данные в предыдущем коде (после возникновения ошибки деления на ноль в последнем утверждении INSERT), но в версии 8 вы можете сделать перемотку до определенного savepoint. Обратите внимание, что код содержит savepoint с именем 'main_values_inserted'. Для обратной перемотки до этой savepoint вы можете написать:

template1=# ROLLBACK TO main_values_inserted;

ROLLBACK

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

template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);

INSERT 17234 1

template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);

INSERT 17235 1

template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);

INSERT 17236 1

template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);

INSERT 17237 1

template1=# SAVEPOINT secondary_values_inserted;

SAVEPOINT

template1=# SELECT * FROM foo;

column1 | column2 | column3

--------+---------+--------

   1    |    2    |   0

   1    |    2    |   0

   1    |    2    |   0

   5    |    9    |   10

   5    |    9    |   10

   5    |    9    |   10

   5    |    9    |   10

(7 rows)

template1=# SAVEPOINT all_values_inserted;

SAVEPOINT

template1=# DELETE FROM foo;

DELETE 7

template1=# SELECT * FROM foo;

column1 | column2 | column3

---------+--------+--------

(0 rows)

Ой. Также, как и в первом примере этой статьи, вы фактически хотели удалить только одну строку где 'column = 1'. Но вы можете сделать перемотку ко второй savepoint 'all_values_inserted' в коде выше.

template1=# ROLLBACK TO all_values_inserted;

ROLLBACK

template1=# SELECT * FROM foo;

column1 | column2 | column3

--------+---------+--------

   1    |    2    |    0

   1    |    2    |    0

   1    |    2    |    0

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

(7 rows)

Обратите внимание, это восстановило все ваши данные. Теперь вы можете запустить корректные команды для удаления.

template1=# DELETE FROM foo WHERE column1 = 1;

DELETE 3

template1=# SELECT * FROM foo;

column1 | column2 | column3

--------+---------+--------

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

(4 rows)

Наконец, вы можете завершить вашу транзакцию. Последняя команда SELECT показывает, что целостность данных после всех этих вставок и обратных перемоток не повреждена.

template1=# COMMIT;

COMMIT

template1=# select * from foo;

column1 | column2 | column3

--------+---------+--------

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

   5    |    9    |    10

(4 rows)

Джошуа Д. Дрейк – Президент Command Prompt, Inc. Компания занимается поддержкой PostgreSQL программированием. Он также соавтор 'Практического PostgreSQL' от издательства O'reilly и Партнеров.