Как вывести данные таблицы в sql

Команда SQL для удаления и обновление данных в базе (DELETE, UPDATE)

В базу данных можно не только добавлять данные, но и удалять их оттуда. Ещё существует возможность обновить данные в базе. Рассмотрим оба случая.

Удаление данных из базы (DELETE)

Удаление из базы данных происходит с помощью команды «DELETE» (переводится с английского как «УДАЛИТЬ»). Функция удаляет не одну строку, а несколько, при этом выбирает для удаления строки по логике функции «SELECT». То есть чтобы удалить данные из базы, необходимо точно определить их. Приведём пример SQL команды для удаления одной строчки:

DELETE FROM `USERS` WHERE `ID` = 2 LIMIT 1;

Благодаря этому запросу из таблицы «USERS» будет удалена одна запись, у которой в столбце «ID» стоит значение «2».

Обратите внимание, что в конце запроса стоит лимит на выборку «LIMIT 1;» размером в 1 строку. Его можно было не ставить, если поле «ID» является «PRIMARY KEY» (первичный ключ, то есть содержит только уникальные значения). Но всё-таки рекомендуем ставить ограничение «LIMIT 1;» в любом случае, если вы намерены удалить только одну строчку.

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

DELETE FROM `USERS` WHERE `ID` >= 5;

Этот запрос удалит все строки в таблицы, у которых в столбце «ID» стоит значение меньше 5. Если не поставить никакого условия «WHERE» и лимита «LIMIT», то будут удалены абсолютно все строки в таблице:

DELETE FROM `USERS`;

На некоторых версиях MySQL способ удаления всех строк через «DELETE FROM _;» может работать медленнее, чем «TRUNCATE _;«; Поэтому для очистки всей таблицы лучше всё-таки использовать «TRUNCATE».

Обновление данных в базе (UPDATE)

Функция обновления «UPDATE» (переводится с английского как «ОБНОВИТЬ») довольно часто используется в проектах сайтов. Как и в случае с функцией «DELETE», фкнция обновления не успокоится до тех пор, пока не обновит все поля, которые подходят под условия, если нет лимита на выборку. Поэтому необходимо задавать однозначные условия, чтобы вместо одной строки нечаянно не обновить половину таблицы. Приведём пример использования команды «UPDATE»:

UPDATE `USERS` SET `NAME` = 'Мышь' WHERE `ID` = 3 LIMIT 1;

В этом примере, в таблие «USERS» будет установлено значение «Мышь» в столбец «NAME» у строки, в столбце «ID» которой стоит значение «3». Можно обновить сразу несколько столбцов у одной записи, передав значения через запятую. Попробуем обновить не только значение с толбце «NAME», но и значение в столбце «FOOD» используя один запрос:

UPDATE `USERS` SET `NAME` = 'Мышь', `FOOD` = 'Сыр' WHERE `ID` = 3 LIMIT 1;

Если не поставить никаких лимитов LIMIT и условий WHERE, то все записи таблицы будут обновлены без исключений.

Как вывести данные таблицы в sql

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

mysql> SELECT 1 + 1; -> 2

Все используемые ключевые слова должны быть даны точно в порядке, показанном выше. Например, предложение HAVING должно прийти после любого предложения GROUP BY и перед любым предложением ORDER BY .

Выражение SELECT может применять псевдоним, используя AS . Псевдоним используется при задании в выражении имени столбца и может быть указан с предложениями ORDER BY или HAVING . Пример:

mysql> select concat(last_name,', ',first_name) AS full_name from mytable ORDER BY full_name;

mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; mysql> select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;

mysql> select college, region, seed from tournament ORDER BY region, seed; mysql> select college, region AS r, seed AS s from tournament ORDER BY r, s; mysql> select college, region, seed from tournament ORDER BY 2, 3;

mysql> select col_name from tbl_name HAVING col_name > 0;

Пишите это взамен:

mysql> select col_name from tbl_name WHERE col_name > 0;

В MySQL Version 3.22.5 или позже Вы можете также писать запросы подобно этому:

mysql> select user,max(salary) from users group by user HAVING max(salary)>10;

В старых версиях MySQL Вы можете писать взамен это:

mysql> select user,max(salary) AS sum from users group by user HAVING sum>10;

SELECT a,COUNT(b) FROM test_table GROUP BY a DESC

mysql> select * from table LIMIT 5,10; # Вернуть строки 6-15

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

mysql> select * from table LIMIT 5; # Вернуть первые 5 строк

• Сам символ ESCAPED BY
• Первый символ в FIELDS TERMINATED BY
• Первый символ в LINES TERMINATED BY

SELECT a,b,a+b INTO OUTFILE "/tmp/result.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM test_table;

MySQL поддерживает следующие синтаксисы JOIN для применения в операторе выбора SELECT :

table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference LEFT [OUTER] JOIN table_reference table_reference NATURAL [LEFT [OUTER]] JOIN table_reference < oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr >table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Здесь table_reference определен как:

table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

а join_condition как:

ON conditional_expr | USING (column_list)

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

Обратите внимание, что в версиях до Version 3.23.17 INNER JOIN не обрабатывал join_condition !

Последний синтаксис LEFT OUTER JOIN , показанный выше, существует только для совместимости с ODBC:

Ссылка таблицы может быть оснащена псевдонимом с использованием tbl_name AS alias_name или tbl_name alias_name :

mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;

mysql> select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL;

A LEFT JOIN B USING (C1,C2,C3. )

Определено, чтобы быть семантически идентичным выражению ON :

A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3.

Вот некоторые примеры:

mysql> select * from table1,table2 where table1.id=table2.id; mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> select * from table1 LEFT JOIN table2 USING (id); mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; mysql> select * from table1 USE INDEX (key1,key2) WHERE key1=1 and key2=2 AND key3=3; mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND key3=3;

SELECT . UNION [ALL] SELECT . [UNION SELECT . ]

UNION реализован только в версии MySQL 4.0.0 и выше.

UNION используется, чтобы объединить результаты из многих инструкций SELECT в один набор результатов.

Команды SELECT представляют собой нормальные команды выбора, но со следующими ограничениями:

• Только последняя команда SELECT может иметь INTO OUTFILE .
• Только последняя команда SELECT может иметь ORDER BY .

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

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] SELECT . INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, .

INSERT вставляет новые строки в существующую таблицу. Форма INSERT . VALUES вставляет строки, основываясь на явно определенных значениях. INSERT . SELECT вставляет строки, выбранные из другой таблицы или таблиц. Форма INSERT . VALUES с несколькими списками значений поддержана в MySQL Version 3.22.5 или позже. Синтаксис col_name=expression введен, начиная с MySQL Version 3.22.10 или выше.

tbl_name таблица, в которую строки должны быть вставлены. Список имен столбцов или предложение SET указывает, для которых столбцов инструкция определяет значения:

• Если Вы не определяете никакого списка столбцов для INSERT . VALUES или INSERT . SELECT , значения для всех столбцов нужно обеспечить в списке VALUES() или через SELECT . Если Вы не знаете порядка столбцов в таблице, используйте DESCRIBE tbl_name , чтобы выяснить.
• Любой столбец, для которого значение явно не задано, будет установлен в значение по умолчанию. Например, если Вы определяете список столбцов, который не называет все столбцы в таблице, неназванные столбцы будут установлены в их значения по умолчанию. Задание значений по умолчанию описано в разделе «7.3 Синтаксис CREATE TABLE «.
• Выражение expression может обратиться к любому столбцу, который был установлен ранее в списке значения. Например, можно задать:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

Но нельзя скомандовать:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

Если Вы используете INSERT . SELECT или INSERT . VALUES со многими списками значений, Вы можете использовать функцию C API mysql_info() , чтобы получить информацию относительно запроса. Формат информационной строки показан ниже:

Records: 100 Duplicates: 0 Warnings: 0

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

• Вставка NULL в столбец, который был объявлен как NOT NULL . Столбец установлен в значение по умолчанию.
• Установка числового столбца в значение, которое находится вне диапазона столбца. Значение отсечено к соответствующей границе диапазона.
• Установка числового столбца к значению типа 10.34 a . Конечный будет мусор удален, и остающаяся числовая часть будет вставлена. Если значение не имеет смысла как число вообще, столбец установлен в 0 .
• Вставка строки, которая превышает максимальную длину столбца, в столбец типов CHAR , VARCHAR , TEXT или BLOB . Значение усечено к максимальной длине столбца.
• Вставка значения, которое является запрещенным для этого типа столбца, в столбец даты или времени. Столбец установлен к соответствующему нулевому значению для типа.

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT .

Командой INSERT . SELECT Вы можете быстро вставлять много строк в таблицу из одной или нескольких таблиц.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

Следующие условия работают для команды INSERT . SELECT :

• Целевая таблица инструкции INSERT не может появляться в предложении FROM части SELECT запроса потому, что в ANSI SQL запрещено выполнять SELECT над той же самой таблицей, в которую Вы вставляете. Проблема состоит в том, что SELECT , возможно, нашел бы записи, которые были вставлены ранее в течение той же самой операции. При использовании предложений sub-select, ситуация могла бы очень легко запутаться!
• Столбцы AUTO_INCREMENT работают как обычной.
• Вы можете использовать функцию C API mysql_info() , чтобы получить информацию относительно запроса. Подробности в разделе «8.3 Синтаксис INSERT «.
• Чтобы гарантировать, что модификации регистрируются и могут использоваться, чтобы пересоздать первоначальные таблицы, MySQL не будет позволять параллельные вставки в течение выполнения INSERT . SELECT .

Вы можете, конечно, также использовать REPLACE вместо INSERT , чтобы перезаписать старые строки.

INSERT DELAYED .

Опция DELAYED для инструкции INSERT специфична для MySQL и является очень полезной, если Вы имеете клиентуру, которая не может ждать завершения INSERT . Это общая проблема, когда Вы используете MySQL для регистрации и также периодически выполняете инструкции SELECT и UPDATE , которые занимают много времени. DELAYED введен в MySQL Version 3.22.15. Это MySQL-расширение для стандарта ANSI SQL92.

INSERT DELAYED работает только с таблицами ISAM и MyISAM . Обратите внимание, что таблицы MyISAM поддерживают параллельные SELECT и INSERT , если не имеется никаких свободных блоков в середине файла данных, Вы очень редко должны использовать INSERT DELAYED с таблицами типа MyISAM .

Когда Вы используете INSERT DELAYED , клиент получит сообщение OK сразу, а вот строка будет вставлена, когда таблица не будет использоваться любым другим потоком.

Другая польза от применения INSERT DELAYED в том, что вставки многих клиентов будут связаны вместе и записаны в одном блоке. Это намного быстрее, чем выполнение многих отдельных вставок.

Обратите внимание, что в настоящее время поставленные в очередь строки сохранены только в памяти, пока они не вставлены в таблицу. Это означает, что, если Вы уничтожаете mysqld крутыми мерами ( kill -9 ), или если mysqld рухнет неожиданно, любые поставленные в очередь строки, которые не были записаны на диск, потеряются!

Следующее описывает подробно, что случается, когда Вы используете опцию DELAYED вместе с командами INSERT или REPLACE . В этом описании «поток» представляет собой поток, который получил команду INSERT DELAYED , и «драйвер» обозначает поток, который обрабатывает все инструкции INSERT DELAYED для специфической таблицы.

• Когда поток выполняет инструкцию DELAYED для таблицы, будет создан драйвер: чтобы обработать все инструкции DELAYED для этой таблицы, если никакого такого драйвера еще не существует.
• Поток проверяет, приобрел или нет драйвер блокировку DELAYED . Если нет, он сообщает, чтобы драйвер это сделал. Блокировка DELAYED может быть получена, даже если другие потоки имеют блокировку READ или WRITE на таблице. Однако, драйвер будет ждать ALTER TABLE или FLUSH TABLES , чтобы гарантировать, что текущая структура таблицы современна.
• Поток выполняет инструкцию INSERT , но вместо того, чтобы записать строку в таблицу, он помещает копию конечной строки в очередь, которая управляется драйвером. Любые ошибки синтаксиса будут отмечены потоком и сообщены программе пользователя.
• Пользователь не может сообщать число дубликатов или значения AUTO_INCREMENT для возникающей в результате строки. Он не может получить эти данные с сервера, поскольку возвраты INSERT перед операцией вставки был завершены.
• Файл регистрации модификаций обновляется драйвером, когда строка будет реально вставлена в таблицу. В случае вставки нескольких строк файл регистрации модификаций меняется, когда вставлена первая строка.
• После того, как каждые delayed_insert_limit строк написаны, драйвер проверяет, являются или нет любые инструкции SELECT ждущими своей очереди. Если это так, драйвер позволяет им выполниться перед продолжением своей работы.
• Когда драйвер больше не имеет строк в очереди, он разблокирует таблицу. Если никакая новая команда INSERT DELAYED не появилась в течение последних delayed_insert_timeout секунд, драйвер завершается.
• Если больше, чем delayed_queue_size строк, ждущих обработки, уже в специфической очереди драйверов, поток, запрашивающий INSERT DELAYED ждет до появления участка памяти в очереди. Это выполнено, чтобы гарантировать, что сервер mysqld не использует всю память для отсроченной очереди.
• Поток драйвера обнаружится в списке процесса MySQL с меткой delayed_insert в столбце Command . Это будет уничтожаться, если Вы выполняете команду FLUSH TABLES или уничтожаете поток с помощью KILL thread_id . Однако, драйвер сначала сохранит все поставленные в очередь строки в таблицу перед выходом. В это время система не будет принимать никакие новые команды INSERT от других потоков. Если Вы выполняете команду INSERT DELAYED после того, как это было выполнено, новый поток драйвера будет создан.
• Обратите внимание, что вышеупомянутое означает, что команды INSERT DELAYED имеют более высокий приоритет, чем нормальные команды INSERT , если уже имеется драйвер INSERT DELAYED ! Другие команды модификации должны будут ждать до опустошения очереди INSERT DELAYED , уничтожения драйвера (через KILL thread_id ) или выполнения FLUSH TABLES .
• Следующие переменные состояния обеспечивают информацию относительно выполнения команд INSERT DELAYED :

  Переменная	Значение
  Delayed_insert_threads	Число обработчиков потоков
  Delayed_writes	Число строк, вставленных с помощью INSERT DELAYED
  Not_flushed_delayed_rows	Число строк, ждущих записи

  Вы можете рассматривать эти переменные, выдавая инструкцию SHOW STATUS или выполняя команду mysqladmin extended-status .

Обратите внимание, что INSERT DELAYED медленнее, чем нормальная INSERT, если таблица сейчас не используется. Имеется также дополнительная нагрузка для сервера, чтобы обработать отдельный поток для каждой таблицы, на которой Вы используете INSERT DELAYED . Это означает, что Вы должны использовать INSERT DELAYED только тогда, когда Вы, действительно, уверены, что нуждаетесь в этом!

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, . ] [WHERE where_definition] [LIMIT #]

UPDATE модифицирует столбцы в существующих строках таблиц с новыми значениями. Предложение SET указывает, какие столбцы изменять, и значения, которые им должны быть заданы. Предложение WHERE , если задано, определяет, какие строки должны модифицироваться. Иначе модифицируются все строки. Если определено предложение ORDER BY , строки будут модифицироваться в том порядке, который определен.

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение UPDATE будет отсрочено до наступления момента, когда никакие клиенты не читают из таблицы.

Если Вы определяете ключевое слово IGNORE , инструкция модификации не будет прерываться, даже если мы получаем ошибки дублирования ключа в течение модификации. Строки, которые могли бы вызвать конфликты, не будут модифицироваться вообще.

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

mysql> UPDATE persondata SET age=age+1;

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

mysql> UPDATE persondata SET age=age*2, age=age+1;

Если Вы устанавливаете столбец к значению, которое он в настоящее время имеет, MySQL не обращает внимания на это и не модифицирует столбец!

UPDATE возвращает число строк, которые были фактически изменены. В MySQL Version 3.22 или позже функция C API mysql_info() возвращает число строк, которые были согласованы и модифицировались, и число предупреждений, которые произошли в течение UPDATE .

В MySQL Version 3.23 Вы можете использовать LIMIT # , чтобы гарантировать, что только заданное число строк будет изменено.

DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY . ] [LIMIT rows] DELETE [LOW_PRIORITY | QUICK] table_name[.*] [table_name[.*] . ] FROM table-references [WHERE where_definition]

DELETE удаляет строки, которые удовлетворяют условию, заданному в where_definition из таблицы table_name и возвращает число удаленных записей.

Если Вы выдаете DELETE без предложения WHERE , все строки будут удалены. Если Вы делаете это в режиме AUTOCOMMIT , это сработает аналогично TRUNCATE . Подробности в разделе «8.7 Синтаксис TRUNCATE «. В MySQL 3.23 DELETE без предложения WHERE возвратит ноль как число обработанных записей.

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

mysql> DELETE FROM table_name WHERE 1>0;

Обратите внимание, что это НАМНОГО медленнее, чем DELETE FROM table_name без предложения WHERE потому, что эта команда удаляет строки по одной.

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение DELETE будет отсрочено до наступления момента, когда никакие клиенты не читают из таблицы.

Если Вы определяете слово QUICK , то драйвер таблицы не будет объединять индексные листья в течение процесса удаления, что может ускорять некоторые виды удаления.

В MyISAM-таблицах удаленные записи поддерживаются в связанном списке, и старые позиции записей повторно используются вызовом INSERT . Чтобы освободить неиспользуемое место и уменьшить размеры файла, используйте команду OPTIMIZE TABLE или утилиту myisamchk , чтобы реорганизовать таблицы. OPTIMIZE TABLE проще, но myisamchk быстрее. Подробности в разделе «4.6 Синтаксис OPTIMIZE TABLE «.

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

Символ .* после имени таблицы появился для совместимости с Access :

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

В вышеупомянутом случае мы удаляем строки соответствий только из таблиц t1 и t2 .

ORDER BY и использование нескольких таблиц в DELETE реализовано, начиная с MySQL 4.0.

Если предложение ORDER BY используется, строки будут удалены в заданном порядке. Это удобно в сочетании с LIMIT . Например:

DELETE FROM somelog WHERE user='jcole' ORDER BY timestamp LIMIT 1

Это удалит самую старую запись (по timestamp ), где строка соответствует предложению WHERE .

MySQL-специфичная опция LIMIT rows функции DELETE сообщает серверу максимальное число строк, которые будут удалены прежде, чем управление будет возвращено пользователю. Это может использоваться, чтобы гарантировать, что специфическая команда DELETE не займет слишком много времени. Вы можете просто повторять команду DELETE , пока число обрабатываемых строк не меньше, чем значение LIMIT .

TRUNCATE TABLE table_name

В версии 3.23 TRUNCATE TABLE отображен на вызов COMMIT; DELETE FROM table_name . Подробности в разделе «8.6 Синтаксис DELETE «.

Разница между TRUNCATE TABLE и DELETE FROM .. :

• Truncate удаляет и пересоздает таблицу, что намного быстрее, чем удаление строк одну за другой.
• Не транзакционно-безопасно: Вы получите ошибку, если имеете активную транзакцию или активную блокировку таблицы.
• Не возвращает число удаленных строк.
• Пока файл определения таблицы table_name.frm корректен, таблица может быть пересоздана, даже если данные или индексный файл повреждены.

TRUNCATE представляет собой Oracle-расширение языка SQL.

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name. )] SELECT . REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expression, col_name=expression.

REPLACE работает в точности подобно INSERT , за исключением того, что, если старая запись в таблице имеет то же самое значение, что и новая запись на уникальном индексе, старая запись будет удалена прежде, чем новая запись вставлена в таблицу. Подробности в разделе «8.3 Синтаксис INSERT «.

Другими словами, Вы не можете обращаться к значениям старой строки из инструкции REPLACE . В некоторых старых версиях MySQL могли, но это было ошибкой, которая исправлена.

При использовании вызова REPLACE mysql_affected_rows() возвратит 2, если новая строка заменила старую. Это потому, что в этом случае одна строка была вставлена, а затем дубликат был удален.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name. )]

Команда LOAD DATA INFILE читает строки из текстового файла в таблицу. Если ключевое слово LOCAL определено, файл читается с компьютера пользователя. Если LOCAL не определено, файл должен быть размещен на сервере. LOCAL доступен в MySQL Version 3.22.6 или более поздней.

По соображениям безопасности, при чтении текстовых файлов, размещенных на сервере, файлы должны находиться в каталоге баз данных или быть читаемыми всеми. Также, чтобы использовать LOAD DATA INFILE на файлах сервера, Вы должны иметь привилегию file . Подробности в разделе «10.6 Привилегии, предоставляемые MySQL».

Если Вы определяете ключевое слово LOW_PRIORITY , выполнение LOAD DATA будет отложено до тех пор, пока другая клиентура не прекратит читать из таблицы.

Если Вы определяете ключевое слово CONCURRENT с таблицей типа MyISAM , то другие потоки могут получать данные из таблицы в то время, как выполняется LOAD DATA . Использование этой опции будет, конечно, воздействовать на эффективность LOAD DATA , даже если никакой другой поток не использует таблицу в то же самое время.

Использование LOCAL будет немного медленнее, чем разрешение серверу обращаться к файлам непосредственно потому, что содержание файла должно пропутешествовать с компьютера пользователя на сервер. С другой стороны, Вы не нуждаетесь в привилегии file , чтобы загрузить свои локальные файлы.

Если Вы используете MySQL ниже 3.23.24, Вы не можете читать из FIFO с помощью LOAD DATA INFILE . Если Вы должны читать из FIFO (например, вывод из gunzip), используйте вместо этого команду LOAD DATA LOCAL INFILE .

Вы можете также загружать файлы данных, используя полезность mysqlimport . Она функционирует, посылая команду LOAD DATA INFILE на сервер. Опция —local заставляет mysqlimport читать файлы данных с компьютера пользователя. Вы можете определять опцию —compress , чтобы получить лучшую эффективность при работе с медленными сетями, если пользователь и сервер поддерживают сжатый протокол.

При размещении файлов на компьютере сервера, он использует правила:

• Если абсолютное имя пути задано, сервер использует его как есть.
• Если относительное имя пути с одним или более ведущими компонентами задано, поиск файла выполняется относительно каталога данных сервера.
• Если имя файла без ведущих компонентов задано, сервер ищет файл в каталоге текущей базы данных.

Обратите внимание, что эти правила означают, что файл, заданный как ./myfile.txt , читается из каталога данных сервера в то время, как файл myfile.txt читается из каталога текущей базы данных. Например, следующая инструкция LOAD DATA читает файл data.txt из каталога базы данных для db1 потому, что db1 текущая база данных, даже при том, что инструкция явно загружает файл в таблицу в базе данных db2 :

mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

Ключевые слова REPLACE и IGNORE контролируют обработку записей, которые дублируют существующие записи на уникальных значениях ключа. Если Вы определяете REPLACE , новые строки заменяют существующие, которые имеют то же самое уникальное значение ключа. Если Вы определяете IGNORE , строки, которые дублируют существующие на уникальном значении ключа, будут пропущены. Если Вы не определяете никакую опцию, ошибка происходит, когда будет найдено двойное значение ключа, и остальная часть текстового файла игнорируется.

Если Вы загружаете данные из локального файла, используя ключевое слово LOCAL , сервер не имеет никакого способа остановить передачу файла в середине операции, так что значение по умолчанию такое же, как если определена опция IGNORE .

Если Вы используете LOAD DATA INFILE на пустой таблице MyISAM , все неуникальные индексы будут созданы в отдельном пакете (подобно REPAIR ). Это обычно делает LOAD DATA INFILE намного быстрее, когда Вы имеете много индексов.

LOAD DATA INFILE представляет собой дополнение к SELECT . INTO OUTFILE . Подробности в разделе «8.1 Синтаксис SELECT «. Чтобы писать данные из базы данных в файл, используйте SELECT . INTO OUTFILE . Чтобы читать файл обратно в базу данных, используйте LOAD DATA INFILE . Синтаксис предложений FIELDS и LINES тот же самый для обеих команд. Оба предложения факультативные, но FIELDS должно предшествовать LINES , если оба определены.

Если Вы определяете предложение FIELDS , каждое из подпредложений ( TERMINATED BY , [OPTIONALLY] ENCLOSED BY и ESCAPED BY ) также факультативны за исключением того, что Вы должны определить по крайней мере одно из них.

Если Вы не определяете предложение FIELDS , значения по умолчанию были бы такие же, как если бы Вы написали это:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Если Вы не определяете предложение LINES , значения по умолчанию были бы такие же, как если бы Вы написали это:

LINES TERMINATED BY '\n'

Другими словами, значения по умолчанию заставляют LOAD DATA INFILE действовать следующим образом при чтении ввода:

• Найти границы строки по newlines.
• Разделить строки на поля через символы табуляции.
• Не ожидать, что поля будут заключены внутрь любых символов цитирования.
• Интерпретировать местонахождения позиций табуляции, newline или \ предшествующего \ как литеральные символы, которые являются частью значений поля.

SELECT . INTO OUTFILE по умолчанию должен действовать следующим образом при записи вывода:

• Записать позиции табуляции между полями.
• Не включать поля внутрь любых символов цитирования.
• Использовать \ , чтобы выйти из позиций табуляции, newline или символов \ , которые происходят внутри значений поля.
• Записать newlines в конце строк.

Обратите внимание, что, чтобы писать FIELDS ESCAPED BY ‘\\’ , Вы должны определить две наклонных черты влево для любого значения, которое нужно читать как одиночную наклонную черту влево.

Опция IGNORE number LINES может использоваться, чтобы игнорировать заголовок столбца в начале файла:

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

Когда Вы используете SELECT . INTO OUTFILE в тандеме с LOAD DATA INFILE , чтобы записать данные из базы данных в файл, а затем читаете файл обратно в базу данных позже, параметры обработки поля и строк для обеих команд должны соответствовать. Иначе LOAD DATA INFILE не будут интерпретировать содержание файла правильно. Предположите, что Вы используете SELECT . INTO OUTFILE , чтобы записать файл с полями, разграниченными запятыми:

mysql> SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM . ;

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

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';

Если взамен Вы пробовали читать файл инструкцией, показанной ниже, это не будет работать потому, что это инструктирует LOAD DATA INFILE искать позиции табуляции между полями:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';

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

LOAD DATA INFILE может использоваться, чтобы читать файлы, полученные из внешних источников. Например, файл в формате dBASE будет иметь поля, разделяемые запятыми и заключенные в двойные кавычки. Если строки в файле завершены newlines, команда, показанная ниже, иллюстрирует параметры обработки, которые Вы использовали бы, чтобы загрузить файл:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Любое из поля или параметров обработки может определять пустую строку ( » ). Если она не пустая, значения FIELDS [OPTIONALLY] ENCLOSED BY и FIELDS ESCAPED BY должны быть одиночным символом. Значения FIELDS TERMINATED BY и LINES TERMINATED BY могут быть больше, чем одним символом. Например, чтобы писать строки, которые завершены парами carriage return-linefeed, или читать файл, содержащий такие строки, определите LINES TERMINATED BY ‘\r\n’ .

Например, чтобы читать файл записей, которые отделяются строкой %% , в таблицу SQL, Вы можете сделать:

create table jokes (a int not null auto_increment primary key, joke text not null); load data infile "/tmp/jokes.txt" into table jokes fields terminated by "" lines terminated by "\n%%\n" (joke);

FIELDS [OPTIONALLY] ENCLOSED BY управляет цитированием полей. Для вывода ( SELECT . INTO OUTFILE ), если Вы опускаете слово OPTIONALLY , все поля включены символом ENCLOSED BY . Пример такого вывода (использование запятой как разделителя поля) ниже:

"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20"

Если Вы определяете OPTIONALLY , символ ENCLOSED BY используется только для того, чтобы включить поля типов CHAR и VARCHAR :

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20

Обратите внимание, что местонахождение символа ENCLOSED BY внутри значения поля экранируется приписыванием символа ESCAPED BY . Также обратите внимание, что, если Вы определяете пустое значение ESCAPED BY , возможно генерировать вывод, который не может читаться правильно средствами LOAD DATA INFILE . Например, вывод, показанный выше, появился бы как показано ниже, если бы символ ESC был пустым. Заметьте, что второе поле в четвертой строке содержит запятую после кавычки, которая ошибочно появляется и завершает поле:

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20

Для ввода символ ENCLOSED BY , если он вообще задан, будет удален с концов значений поля. Это истинно, определен или нет OPTIONALLY . OPTIONALLY не имеет никакого эффекта на входной интерпретации. Местонахождения символа ENCLOSED BY , которому предшествует символ ESCAPED BY , интерпретируются как часть текущего значения поля. Кроме того, дублированные символы ENCLOSED BY , встречающиеся внутри полей, интерпретируются как одиночные символы ENCLOSED BY , если поле непосредственно начинается этим символом. Например, если определено ENCLOSED BY ‘»‘ , кавычки будут обработаны как показано ниже:

"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss

• Символ FIELDS ESCAPED BY .
• Символ FIELDS [OPTIONALLY] ENCLOSED BY .
• Первый символ значений FIELDS TERMINATED BY и LINES TERMINATED BY .
• ASCII 0 (что фактически написано после символа ASCII 0 , не нулевой байт).

Если символ FIELDS ESCAPED BY пуст, никакие символы не экранируются вовсе. Не стоит определять пустой символ ESC, особенно, если значения полей в Ваших данных содержат любой из символов в списке выше.

Для ввода, если символ FIELDS ESCAPED BY не пуст, местонахождения этого символа будут удалены, и следующий символ принимается буквально как часть значения поля. Исключительные ситуации: 0 или N (например, \0 или \N , если символ ESC задан как \ ). Эти последовательности интерпретируются как 0 и NULL соответственно. Ниже изложены правила для обработки NULL .

За подробностями относительно синтаксиса экранировки с символом \ отсылаю Вас к разделу «3.1 Литералы: как писать строки и числа».

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

• Если LINES TERMINATED BY пустая строка, а FIELDS TERMINATED BY не пустая, строки также будут завершены FIELDS TERMINATED BY .
• Если обе строки FIELDS TERMINATED BY и FIELDS ENCLOSED BY пусты ( » ), используется фиксированный формат. С этим форматом никакие разделители не используются между полями. Вместо этого, значения столбцов пишутся и читаются, используя «экранную» ширину. Например, если столбец объявлен как INT(7) , значения для столбца будут написаны, используя 7-символьные поля. На вводе значения для столбца будут получены, читая 7 символов. Формат фиксированной строки также воздействует на обработку значений NULL (см. ниже). Обратите внимание, что этот формат не будет работать, если Вы используете многобайтный набор символов.

Обработка значений NULL изменяется, в зависимости от параметров FIELDS и LINES , которые Вы используете:

• Для заданных по умолчанию значений FIELDS и LINES NULL будет написан как \N для вывода, а \N читается как NULL для ввода (подразумевается, что символ ESCAPED BY определен как \ ).
• Если FIELDS ENCLOSED BY не пуст, поле, содержащее литеральное слово NULL , читается как значение NULL (это отличается от слова NULL , заключенного внутри символов FIELDS ENCLOSED BY , которое читается как строка ‘NULL’ ).
• Если FIELDS ESCAPED BY пустое, NULL будет написан как слово NULL .
• С форматом фиксированной строки (когда FIELDS TERMINATED BY и FIELDS ENCLOSED BY оба пусты), NULL будет записан как пустая строка. Обратите внимание, что это делает неразличимыми значения NULL и пустые строки в таблице, когда они записаны в файл потому, что и то и другое пишется как как пустые строки. Если Вы должны при чтении файла иметь возможность разобраться что есть что, не следует применять фиксированный формат строки.

• Строки фиксированной длины ( FIELDS TERMINATED BY и FIELDS ENCLOSED BY вместе пустые) в сочетании со столбцами BLOB или TEXT .
• Если Вы определяете один разделитель, который является префиксом другого, команда LOAD DATA INFILE не будет способна интерпретировать ввод правильно. Например, следующее предложение FIELDS проблемное:

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

Следующий пример загружает все столбцы таблицы persondata :

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Никакой список полей не определен, так что LOAD DATA INFILE ожидает строки, содержащие поле для каждого столбца таблицы. Используются заданные по умолчанию значения FIELDS и LINES .

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

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2. );

Вы должны также определить список полей, если порядок полей во входном файл отличается от порядка столбцов в таблице. Иначе MySQL не может выяснить, как совмещать входные поля со столбцами таблицы.

Если строка имеет слишком мало полей, столбцы, для которых никакого входного поля не присутствует, будут установлены в значения по умолчанию. Задание этих значений по умолчанию подробно описано в разделе «7.3 Синтаксис CREATE TABLE «.

Пустое значение поля интерпретируется не так, как если бы оно отсутствовало вовсе, а именно:

• Для строковых типов, столбец установлен в пустую строку.
• Для числовых типов, столбец установлен в 0 .
• Для типов даты и времени, столбец установлен к соответствующему нулю для типа. Подробности в разделе «5.2 Типы Date и Time».

Обратите внимание, что здесь все происходит точно так же, как если Вы назначаете пустую стргоку явно как значение соответствующего поля с помощью вызовов INSERT или UPDATE .

Столбцы TIMESTAMP будут установлены только к текущей дате и времени, если для столбца имеется значение NULL , или (только для первого столбца TIMESTAMP ) если столбец типа TIMESTAMP не учтен в списке полей, когда такой список определен. Если входная строка имеет слишком много полей, лишние поля игнорируются.

LOAD DATA INFILE расценивает весь ввод как строки, так что Вы не можете использовать числовые значения для столбцов типов ENUM или SET , как Вы можете это делать инструкциями INSERT . Все значения ENUM и SET должны быть определены как строки!

Если Вы используете C API, Вы можете получать информацию относительно запроса, вызывая функцию mysql_info() когда запрос LOAD DATA INFILE обработан. Формат информационной строки показывается ниже:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Предупреждения происходят при тех же самых обстоятельствах, что и с командой INSERT (подробности в разделе «8.3 Синтаксис INSERT «), за исключением того, что LOAD DATA INFILE также генерирует предупреждения, когда имеется слишком мало или слишком много полей во входной строке. Предупреждения не сохранены где-нибудь. Число предупреждений может использоваться только как индикация, если все пошло нормально. Если Вы получаете предупреждения и хотите знать точно, почему Вы их получили, один способ сделать это состоит в том, чтобы использовать SELECT . INTO OUTFILE в другой файл и сравнить его с первоначальным входным файлом.

Если Вы нуждаетесь в LOAD DATA , чтобы читать из канала, Вы можете использовать следующий прием:

mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 >/nt/mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Если Вы используете версию MySQL до 3.23.25, Вы можете делать вышеупомянутое только с помощью LOAD DATA LOCAL INFILE .

Связи между таблицами MS SQL: обзор основных отношений и типов соединения

Связи между таблицами в базе данных — основа хранения данных в СУБД.

Связи в базе данных MS SQL позволяют нормализировать БД, настроить отношение между данными таблиц и сделать эффективные выборки данных. Главное — понять, как настраивать и использовать связи между таблицами MS SQL. Это необходимое условие для работы с любой БД.

Ниже рассмотрим основные концепции связей: Foreign Key и JOINs.

Foreign Key

Создание связей MS SQL между таблицами происходит через внешний ключ (foreign key). Данный ключ связывает поле (значение) исходной таблицы с Primary Key внешней таблицы. Через внешний ключ можно не только производить выборку данных, но и контролировать удаление данных в главной таблице:

• NO ACTION — не производит никаких действий;
• SET NULL — зависимые данные установятся в NULL при удалении записи из главной таблицы (primary table);
• CASCADE — удаляются зависимые данные. Опасная операция. В реальной жизни используется редко.

Подробнее об этом — на курсе «Аналитик данных». За 12 месяцев разберетесь в основных данных, научитесь делать таблицы, соединять и обрабатывать несколько сразу, использовать оконные функции. Станете настоящим экспертом в аналитике!

ALTER TABLE ADD CONSTRAINT FK__ FOREIGN KEY () REFERENCES () ON UPDATE ON UPDATE

Типы соединения JOINs

Когда отношения между таблицами установлены, можно делать выборки данных из этих связанных таблиц.

Существует несколько механизмов соединения двух таблиц в запросе: это основные типы JOINs для MS SQL SERVER. И они практически всегда совпадают для всех реляционных СУБД.

Рассмотрим основные типы JOINs. Будем считать, что у нас есть левая и правая таблицы, которые соединяем через JOIN. *Левая и правая относительно слова JOIN.

Есть несколько механизмов соединения двух таблиц в запросе. Например, Oracle содержит Natural Join, который соединяет колонки с одинаковыми именами в таблицах. Используется крайне редко.

Обо всех способах соединения — на курсе «Аналитик данных». Уделяйте учебе всего два часа в день — и научитесь обновлять, удалять и добавлять записи, использовать разные соединения, чтобы решать реальные задачи.

LEFT (OUTER) JOIN

Всегда выводите данные по левой таблице. Если правая таблица не содержит связанных данных, то выводите NULL для этих значений.

select * from A left join B on A.ID = B.A_ID

RIGHT (OUTER) JOIN

Обратное от Left Join. Используют редко. Всегда можно переписать на Left Join — тогда запрос легче читать. В частных случаях бывает, что Right Join дает лучшую статистику выполнения и оптимизирует запрос.
A left join B = B right join A:

select * from B right join A on A.ID = B.A_ID

Аналитик данных: новая работа через 5 месяцев
Получится, даже если у вас нет опыта в IT

INNER JOIN

Выводите значения для строки из левой таблицы, только если есть связанные данные в правой таблице. Часто используют, чтобы отфильтровать данные левой таблицы и выводить только те записи, по которым есть значения в правой.

На курсе «Аналитик данных» вы не только изучите теорию, но и выполните практические задания. Если возникнут сложности — помогут кураторы с реальным опытом в аналитике. Благодаря их помощи подготовите интересные проекты для портфолио.

select * from A inner join B on A.ID = B.A_ID -- аналог запроса на left join select * from A left join B on A.ID = B.A_ID where B.ID is not null

CROSS JOIN

Это пересечение всех строк из левой таблицы со всеми строками правой таблицы.

select * from A cross join B

FULL JOIN

Представьте, что это смешанное сочетание Left Join и Right Join. Вначале выводятся значения левой таблицы, а правой заполняются NULL, затем — наоборот.

Запрос выводит пересечение значений. Если нет пересечений, то выводит значения по A и B c NULL:

select * from A full join B on A.ID = B.A_ID

Типы отношений между таблицами

Используем Foreign Key и JOINs и создадим реальный пример бизнес-задачи. А еще рассмотрим настройку связей между таблицами.

Введем сущности Clinics, Doctors, Patients и Appointments.

• Доктор работает или не работает только в одной клинике.
• У доктора может быть вышестоящий менеджер.
• Пациент может обращаться в разные клиники к разным докторам.

Создадим таблицы БД, пока без связей с сурогатными Primary Keys:

CREATE TABLE dbo.Clinics ( ID int, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT Clinics_PK PRIMARY KEY (ID) ); CREATE TABLE dbo.Patients ( ID int, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT Patients_PK PRIMARY KEY (ID) ); CREATE TABLE dbo.Doctors ( ID int, Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, Clinic_ID int NULL, Manager_ID int NULL, CONSTRAINT Doctors_PK PRIMARY KEY (ID) ); CREATE TABLE dbo.Appointments ( ID int, [Date] datetime2(0) NOT NULL, Patient_ID int NOT NULL, Doctor_ID int NOT NULL, CONSTRAINT Appointments_PK PRIMARY KEY (ID) );

insert into dbo.Clinics values (1, 'First Clinic'), (2, 'Second Clinic') insert into dbo.Doctors values (10, 'Doctor', 1, NULL), (11, 'Assistent Doctor', 1, 10), (12, 'Another Doctor', 2, NULL), (13, 'Retired Doctor', NULL, NULL), (15, 'Assist 2 Doctor', 1, 11) insert into dbo.Patients values (100, 'First Patient'), (101, 'Second Patient') insert into dbo.Appointments values (1000, GETDATE(), 100, 10), (1001, GETDATE(), 101, 10), (1002, GETDATE(), 100, 12)

Отношения «один к одному»

Используйте данную связь, когда значению из таблицы соответствует только одна запись из внешней таблицы. «Доктор может работать только в одной клинике». Можем предположить, что связь между Clinics и Doctors будет «один к одному»:

ALTER TABLE dbo.Doctors ADD CONSTRAINT FK_Doctors_ClinicID FOREIGN KEY (Clinic_ID) REFERENCES dbo.Clinics(ID);

select d.Name, c.Name from dbo.Doctors d left join dbo.Clinics c on c.ID = d.Clinic_ID;

Отношение «один ко многим»

Одной записи из таблицы соответствуют несколько записей из внешней. Данный тип связи очень распространен при построении схемы БД.

«Хотя доктор может принадлежать только одной клинике, клиники, в свою очередь, содержат штат докторов». Это отношение «один ко многим»:

select c.Name, d.Name from dbo.Clinics c inner join dbo.Doctors d on d.Clinic_ID = c.ID;

Отношение «многие ко многим»

Организуется через промежуточную таблицу, в которой есть внешние ключи на разные таблицы.

О временных таблицах вы подробнее узнаете на курсе «Аналитик данных». Научитесь работать с временными таблицами, использовать оконные функции и соединять таблицы в SQL, применять их для сложных запросов.

В таблице Appointments есть связь на таблицы Doctors и Patients. Таким образом, организована связь между пациентами и докторами: пациент может посещать нескольких докторов, а доктора — принимать нескольких пациентов.

ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_DoctorID FOREIGN KEY (Doctor_ID) REFERENCES dbo.Doctors(ID); ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_PatientID FOREIGN KEY (Patient_ID) REFERENCES dbo.Patients(ID);

Это довольно распространенный вопрос на собеседовании для SQL-разработчика. Если программист может на примере объяснить, как строится связь «многие ко многим», — это уже хороший показатель для интервьюера.

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

select p.Name, a.[Date], d.Name, c.Name from dbo.Patients p inner join dbo.Appointments a on a.Patient_ID = p.ID inner join dbo.Doctors d on d.ID = a.Doctor_ID inner join dbo.Clinics c on c.ID = d.Clinic_ID where p.ID = 100;

Связь с самим собой

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

Таблица Doctors содержит колонку Manager, в которой указано, кто из докторов является менеджером текущего доктора. Здесь связь на строку из той же таблицы докторов.

Как рекурсивно получить список докторов, у которых определенный доктор является вышестоящим менеджером:

with cte as ( select d.ID, d.Name from dbo.Doctors d where d.ID = 10 union all select d2.ID, d2.Name from dbo.Doctors d2 inner join cte on cte.ID = d2.Manager_ID ) select * from cte

Памятка/шпаргалка по SQL

Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

Почему SQL?

• получать доступ к данным в системах управления РБД
• описывать данные (их структуру)
• определять данные в БД и управлять ими
• взаимодействовать с другими языками через модули SQL, библиотеки и предваритальные компиляторы
• создавать и удалять БД и таблицы
• создавать представления, хранимые процедуры (stored procedures) и функции в БД
• устанавливать разрешения на доступ к таблицам, процедурам и представлениям

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

• диспетчер запросов (Query Dispatcher)
• движок оптимизации (Optimization Engines)
• классический движок запросов (Classic Query Engine)
• движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды SQL

Стандартными командами для взаимодействия с РБД являются CREATE , SELECT , INSERT , UPDATE , DELETE и DROP . Эти команды могут быть классифицированы следующим образом:

• DDL — язык определения данных (Data Definition Language)

• DML — язык изменения данных (Data Manipulation Language)

• DCL — язык управления данными (Data Control Language)

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Обратите внимание: вместо userId и userName можно было бы использовать id и name , соответственно. Но при работе с несколькими объектами, содержащими свойство id , бывает сложно понять, какому объекту принадлежит идентификатор, особенно, если вы, как и я, часто прибегаете к деструктуризации. Что касается слова name , то оно часто оказывается зарезервизованным, т.е. уже используется в среде, в которой выполняется код, поэтому я стараюсь его не использовать.

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

Что такое колонка?

Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем. В таблице users одной из колонок является city , которая содержит названия городов, в которых проживают пользователи.

Что такое нулевое значение?

Нулевое значение (NULL) — это значение поля, которое является пустым, т.е. нулевое значение — это значение поля, не имеющего значения. Важно понимать, что нулевое значение отличается от значения 0 и от значения поля, содержащего пробелы ( `). Поле с нулевым значением — это такое поля, которое осталось пустым при создании записи. Также, следует учитывать, что в некоторых СУБД пустая строка ( » ) — это NULL`, а в некоторых — это разные значения.

Ограничения

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

• NOT NULL — колонка не может иметь нулевое значение
• DEFAULT — значение колонки по умолчанию
• UNIQUE — все значения колонки должны быть уникальными
• PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
• FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
• CHECK — все значения в колонке должны удовлетворять определенному условию
• INDEX — быстрая запись и извлечение данных

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

• целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
• целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
• целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
• целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила

Нормализация БД

Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:

• предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
• обеспечение «оправданной» связи между данными

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Синтаксис SQL

Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT , INSERT , UPDATE , DELETE , ALTER , DROP , CREATE , USE , SHOW и т.п. и заканчиваться точкой с запятой ( ; ) (точка с запятой не входит в синтаксис SQL , но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т.е. SELECT , select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL , где учитывается регистр в названии таблицы.

Примеры синтаксиса

-- выборка SELECT col1, col2, . colN FROM tableName; SELECT DISTINCT col1, col2, . colN FROM tableName; SELECT col1, col2, . colN FROM tableName WHERE condition; SELECT col1, col2, . colN FROM tableName WHERE condition1 AND|OR condition2; SELECT col2, col2, . colN FROM tableName WHERE colName IN (val1, val2, . valN); SELECT col1, col2, . colN FROM tableName WHERE colName BETWEEN val1 AND val2; SELECT col1, col2, . colN FROM tableName WHERE colName LIKE pattern; SELECT col1, col2, . colN FROM tableName WHERE condition ORDER BY colName [ASC|DESC]; SELECT SUM(colName) FROM tableName WHERE condition GROUP BY colName; SELECT COUNT(colName) FROM tableName WHERE condition; SELECT SUM(colName) FROM tableName WHERE condition GROUP BY colName HAVING (function condition); -- создание таблицы CREATE TABLE tableName ( col1 datatype, col2 datatype, . colN datatype, PRIMARY KEY (одна или более колонка) ); -- удаление таблицы DROP TABLE tableName; -- создание индекса CREATE UNIQUE INDEX indexName ON tableName (col1, col2, . colN); -- удаление индекса ALTER TABLE tableName DROP INDEX indexName; -- получение описания структуры таблицы DESC tableName; -- очистка таблицы TRUNCATE TABLE tableName; -- добавление/удаление/модификация колонок ALTER TABLE tableName ADD|DROP|MODIFY colName [datatype]; -- переименование таблицы ALTER TABLE tableName RENAME TO newTableName; -- вставка значений INSERT INTO tableName (col1, col2, . colN) VALUES (val1, val2, . valN) -- обновление записей UPDATE tableName SET col1 = val1, col2 = val2, . colN = valN [WHERE condition]; -- удаление записей DELETE FROM tableName WHERE condition; -- создание БД CREATE DATABASE [IF NOT EXISTS] dbName; -- удаление БД DROP DATABASE [IF EXISTS] dbName; -- выбор БД USE dbName; -- завершения транзакции COMMIT; -- отмена изменений ROLLBACK;

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10 , а b — 20 .

Арифметические

Операторы сравнения

Логические операторы

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

SELECT col1, col2, . colN FROM tableName WHERE [condition|expression];

Существуют различные типы выражений: логические, числовые и выражения для работы с датами.

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

SELECT col1, col2, . colN FROM tableName WHERE выражение для поиска совпадения с единичным значением;

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

SELECT * FROM users WHERE status = active;

Числовые

Используются для выполнения арифметических операций в запросе.

SELECT numericalExpression as operationName [FROM tableName WHERE condition];

Простой пример использования числового выражения:

SELECT (10 + 5) AS addition;

Существует несколько встроенных функций, таких как count() , sum() , avg() , min() , max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

SELECT COUNT(*) AS records FROM users;

• AVG — вычисляет среднее значение
• SUM — вычисляет сумму значений
• MIN — вычисляет наименьшее значение
• MAX — вычисляет наибольшее значение
• COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

• CONCAT — объединение строк
• LENGTH — возвращает количество символов в строке
• TRIM — удаляет пробелы в начале и конце строки
• SUBSTRING — извлекает подстроку из строки
• REPLACE — заменяет подстроку в строке
• LOWER — переводит символы строки в нижний регистр
• UPPER — переводит символы строки в верхний регистр и т.д.

• ROUND — округляет число
• TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
• CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
• FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
• POWER — возводит число в указанную степень
• SQRT — возвращает квадратный корень числа
• RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP — это и выражение, и функция ( CURRENT_TIMESTAMP() ). Другая функция для получения текущей даты и времени — NOW() .

Другие функции для получения текущей даты и времени:

• CURDATE / CURRENT_DATE — возвращает текущую дату
• CURTIME / CURRENT_TIME — возвращает текущее время и т.д.

Функции для разбора даты и времени:

• DAYOFMONTH(date) — возвращает день месяца в виде числа
• DAYOFWEEK(date) — возвращает день недели в виде числа
• DAYOFYEAR(date) — возвращает номер дня в году
• MONTH(date) — возвращает месяц
• YEAR(date) — возвращает год
• LAST_DAY(date) — возвращает последний день месяца в виде даты
• HOUR(time) — возвращает час
• MINUTE(time) — возвращает минуты
• SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

• DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
• DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
• DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
• TO_DAYS(date) — возвращает количество дней с 0-го дня года
• TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format) , соответственно.

Создание БД

Для создания БД используется инструкция CREATE DATABASE .

CREATE DATABASE dbName; -- или CREATE DATABASE IF NOT EXISTS dbName;

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Создаем БД testDB :

CREATE DATABASE testDB;

Получаем список БД:

SHOW DATABASES;

Удаление БД

Для удаления БД используется инструкция DROP DATABASE .

DROP DATABASE dbName; -- или DROP DATABASE IF EXISTS dbName;

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

DROP DATABASE testDB;

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

Проверяем, что БД удалена:

SHOW DATABASES;

Для получения списка таблиц используется инструкция SHOW TABLES .

Выбор БД

При наличии нескольких БД, перед выполнением каких-либо операций, необходимо выбрать БД. Для этого используется инструкция USE .

USE dbName;

Предположим, что мы не удаляли testDB . Тогда мы можем выбрать ее так:

USE testDB;

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

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE .

CREATE TABLE tableName ( col1 datatype, col2 datatype, . colN datatype, PRIMARY KEY (хотя бы одна колонка) );

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT .

Пример создания таблицы users , где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

CREATE TABLE users ( userId INT, userName VARCHAR(20) NOT NULL, age INT NOT NULL, city VARCHAR(20), status VARCHAR(8), PRIMARY KEY (id) );

Проверяем, что таблица была создана:

DESC users;

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

Для удаления таблицы используется инструкция DROP TABLE .

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

Удаляем таблицу users :

DROP TABLE users;

Теперь, если мы попытаемся получить описание users , то получим ошибку:

DESC users; -- ERROR 1146 (42S02): Table 'testDB.users' doesn't exist

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO .

INSERT INTO tableName (col1, col2, . colN) VALUES (val1, val2, . valN);

Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.

INSERT INTO tableName VALUES (val1, val2, . valN);

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

Предположим, что мы не удаляли таблицу users . Заполним ее пользователями:

INSERT INTO users (userId, userName, age, city, status) VALUES (1, 'Igor', 25, 'Moscow', 'active'); INSERT INTO users (userId, userName, age, city, status) VALUES (2, 'Vika', 26, 'Ekaterinburg', 'inactive'); INSERT INTO users (userId, userName, age, city, status) VALUES (3, 'Elena', 27, 'Ekaterinburg', 'active');

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

INSERT INTO users (userId, userName, age, city, status) VALUES (1, 'Igor', 25, 'Moscow', 'active'), (2, 'Vika', 26, 'Ekaterinburg', 'inactive'), (3, 'Elena', 27, 'Ekaterinburg', 'active');

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

INSERT INTO users VALUES (4, 'Oleg', 28, 'Moscow', 'inactive');

Заполнение таблицы с помощью другой таблицы

INSERT INTO tableName [(col1, col2, . colN)] SELECT col1, col2, . colN FROM anotherTable [WHERE condition];

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT . Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

SELECT col1, col2, . colN FROM tableName;

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

SELECT * FROM tableName;

Произведем выборку полей userId , userName и age из таблицы users :

SELECT userId, userName, age FROM users;

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT , UPDATE , DELETE и другими инструкциями.

SELECT col1, col2, . col2 FROM tableName WHERE condition;

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа > , < , = , NOT , LIKE и т.д.

Сделаем выборку полей userId , userName и age активных пользователей:

SELECT userId, userName, age FROM users WHERE status = 'active';

Сделаем выборку полей userId , age и city пользователя с именем Vika .

SELECT userId, age, city FROM users WHERE userName = 'Vika';

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

SELECT col1, col2, . colN FROM tableName WHERE condition1 AND condition2 . AND conditionN;

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

Сделаем выборку полей userId , userName и age активных пользователей старше 26 лет:

SELECT userId, userName, age FROM users WHERE status = active AND age > 26;

OR

SELECT col1, col2, . colN FROM tableName WHERE condition1 OR condition2 . OR conditionN;

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

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

SELECT userId, userName, age FROM users WHERE status = inactive OR age < 27;

Обновление полей

Для обновления полей используется инструкция UPDATE . SET . Эта инструкция, обычно, используется в сочетании с предложением WHERE .

UPDATE tableName SET col1 = val1, col2 = val2, . colN = valN [WHERE condition];

Обновим возраст пользователя с именем Igor :

UPDATE users SET age = 30 WHERE username = 'Igor';

Если в данном случае опустить WHERE , то будет обновлен возраст всех пользователей.

Удаление записей

Для удаления записей используется инструкция DELETE . Эта инструкция также, как правило, используется в сочетании с предложением WHERE .

DELETE FROM tableName [WHERE condition];

Удалим неактивных пользователей:

DELETE FROM users WHERE status = 'inactive';

Если в данном случае опустить WHERE , то из таблицы users будут удалены все записи.

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

• проценты ( % )
• нижнее подчеркивание ( _ )

% означает 0, 1 или более символов. _ означает точно 1 символ.

SELECT col1, col2, . colN FROM tableName WHERE col LIKE 'xxx%' -- или WHERE col LIKE '%xxx%' -- или WHERE col LIKE '%xxx' -- или WHERE col LIKE 'xxx_' -- и т.д.

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

SELECT * FROM users WHERE status LIKE 'in%';

Сделаем выборку пользователей 30 лет и старше:

SELECT * FROM users WHERE age LIKE '3_';

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

SELECT col1, col2, . colN FROM tableName WHERE colName REGEXP регулярное выражение;

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

• ^ — начало строки
• $ — конец строки
• . — любой символ
• [символы] — любой из указанных в скобках символов
• [начало-конец] — любой символ из диапазона
• | — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika :

SELECT * FROM users WHERE userName REGEXP 'Igor|Vika';

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

SELECT TOP number|percent col1, col2, . colN FROM tableName [WHERE condition];

Сделаем выборку первых трех пользователей:

SELECT TOP 3 * FROM users;

SELECT * FROM users LIMIT 3, [offset];

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

SELECT * FROM users LIMIT 2, 2;

SELECT * FROM users WHERE ROWNUM 

SELECT col1, col2, . colN FROM tableName [WHERE condition] [ORDER BY col1, col2, . colN] [ASC | DESC];

SELECT * FROM users ORDER BY city, age;

SELECT * FROM users ORDER BY city, age DESC;

SELECT * FROM users ORDER BY (CASE city WHEN 'Ekaterinburg' THEN 1 WHEN 'Moscow' THEN 2 ELSE 100 END) ASC, city DESC;

SELECT col1, col2, . colN FROM tableName WHERE condition GROUP BY col1, col2, . colN ORDER BY col1, col2, . colN;

SELECT city, COUNT(city) AS amount FROM users WHERE status = active GROUP BY city ORDER BY city;

SELECT DISTINCT col1, col2, . colN FROM tableName [WHERE condition];

SELECT DISTINCT city FROM users;

SELECT userId, userName, age, amount FROM users, orders WHERE users.userId = orders.userId;

SELECT col1, col2, . colN FROM table1 [WHERE condition] UNION SELECT col1, col2, . colN FROM table2 [WHERE condition];

 SELECT userId, userName, amount, date FROM users LEFT JOIN orders ON users.useId = orders.userId UNION SELECT userId, userName, amount, date FROM users RIGHT JOIN orders ON users.userId = orders.userId;

SELECT col1, col2, . colN FROM table1 [WHERE condition] UNION ALL SELECT col1, col2, . colN FROM table2 [WHERE condition];

SELECT col1, col2, . colN FROM tableName AS aliasName [WHERE condition];

SELECT colName AS aliasName FROM tableName [WHERE condition];

SELECT U.userId, U.userName, U.age, O.amount FROM users AS U, orders AS O WHERE U.userId = O.userId;

SELECT userId AS user_id, userName AS user_name, age AS user_age FROM users WHERE status = active;

CREATE INDEX indexName ON tableName;

CREATE INDEX indexName ON tableName (colName);

CREATE UNIQUE INDEX indexName ON tableName (colName);

CREATE INDEX indexName ON tableName (col1, col2, . colN);

DROP INDEX indexName;

-- добавление новой колонки ALTER TABLE tableName ADD colName datatype; -- удаление колонки ALTER TABLE tableName DROP COLUMN colName; -- изменение типа данных колонки ALTER TABLE tableName MODIFY COLUMN colName newDatatype; -- добавление ограничения `NOT NULL` ALTER TABLE tableName MODIFY colName datatype NOT NULL; -- добавление ограничения `UNIQUE` ALTER TABLE tableName ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, . colN); -- добавление ограничения `CHECK` ALTER TABLE tableName ADD CONSTRAINT myUniqueConstraint CHECK (condition); -- добавление первичного ключа ALTER TABLE tableName ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, . colN); -- удаление ограничения ALTER TABLE tableName DROP CONSTRAINT myUniqueContsraint; -- mysql ALTER TABLE tableName DROP INDEX myUniqueContsraint; -- удаление первичного ключа ALTER TABLE tableName DROP CONSTRAINT myPrimaryKey; -- mysql ALTER TABLE tableName DROP PRIMARY KEY;

ALTER TABLE users ADD sex char(1);

ALTER TABLE users DROP sex;

TRUNCATE TABLE tableName;

TRUNCATE TABLE users;

SELECT * FROM users; -- Empty set (0.00 sec)

CREATE VIEW viewName AS SELECT col1, col2, . colN FROM tableName [WHERE condition];

CREATE VIEW usersView AS SELECT userName, age FROM users;

SELECT * FROM usersView;

CREATE VIEW usersView AS SELECT userName, age FROM users WHERE age IS NOT NULL WITH CHECK OPTION;

UPDATE usersView SET age = 31 WHERE userName = 'Igor';

DELETE FROM usersView WHERE age = 26;

DROP VIEW viewName;

DROP VIEW usersView;

SELECT col1, col2, . colN FROM table1, table2, . tableN [WHERE condition] GROUP BY col1, col2, . colN HAVING condition ORDER BY col1, col2, . colN;

BEGIN TRANSACTION DELETE FROM users WHERE age = 26; COMMIT;

BEGIN DELETE FROM users WHERE username = 'Oleg'; ROLLBACK;

SAVEPOINT savepointName;

ROLLBACK TO savepointName;

START TRANSACTION SAVEPOINT sp1; DELETE FROM users WHERE age = 26; SAVEPOINT sp2; DELETE FROM users WHERE userName = 'Oleg'; SAVEPOINT sp3; DELETE FROM users WHERE status = 'inactive';

ROLLBACK TO sp2;

SELECT * FROM users;

SET TRANSACTION [READ WRITE | READ ONLY];

SELECT col1, col2, . colN FROM table1, table2, . tableN WHERE colName operator (SELECT col1, col2, . colN FROM table1, table2, tableN [WHERE condition]);

SELECT * FROM users WHERE userId IN ( SELECT userId FROM users WHERE status = 'active' );

INSERT INTO tableName col1, col2, . colN SELECT col1, col2, . colN FROM table1, table2, . tableN [WHERE operator [value]];

UPDATE tableName SET col = newVal [WHERE operator [value] ( SELECT colName FROM tableName [WHERE condition] ) ];

DELETE FROM tableName [WHERE operator [value] ( SELECT colName FROM tableName [WHERE condition] ) ];

CREATE TABLE tableName ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), -- другие строки );

ALTER TABLE tableName DROP id; ALTER TABLE tableName ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);