Умение быстро и грамотно создавать sql запросы может прийти только с большим количеством практики и, конечно, теорией. Эта статья может служить памяткой для генерации собственных запросов, т.к. в ней я выложу не только подробное описание «основных» видов sql запросов, но и несколько жизненных примеров к каждому из них. Так что давайте начнем разбираться в том, какие типы запросов бывают и как их строить.
SQL запросы обычно выполняют следующие задачи:
- создание, модификация и удаление таблиц базы данных;
- вставка информации (записей) в таблицы базы данных;
- редактирование информации (записей) в таблицах базы данных;
- выборка (извлечение) информации из таблиц базы данных;
- удаление информации (записей) из базы данных.
Для обзора всех этих функций sql запросов создадим и поэкспериментируем с базой данных пользователей. В этой базе данных будет 3 таблицы: пользователи (users), сообщения (messages) и заметки (notes). Эта база данных будет обслуживать сайт (вымышленный), на котором пользователи могут общаться между собой и оставлять для себя различные записи, вроде напоминаний. Данные о пользователе будут следующие: имя, фамилия, логин, пароль и электронный почтовый ящик.
Создание, модификация и удаление таблиц (и столбцов) базы данных.
Вначале, конечно, мы рассмотрим создание таблицы в базе данных, для этих целей используется sql запрос create. Вот его синтаксис:
Вначале, конечно, мы рассмотрим создание таблицы в базе данных, для этих целей используется sql запрос create. Вот его синтаксис:
1
| CREATE TABLE название_таблицы (столбец1 тип_столбца параметры_столбца, и т.д.) |
С помощью параметров столбца можно сделать его первичным или вторичным ключем, уникальным или неравным значению NULL, сделать автоинкремент (при добавлении следующей записи такой столбец будет увеличиваться на единицу) и т.д. Подробнее о параметрах и типах столбцов (типа данных, которые могут храниться в этих столбцах) вы можете прочесть по этой ссылке mysql.ru/docs/man/CREATE_TABLE.html.
Используем sql запрос create table для создания таблиц, соответствующих нашим требованиям:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| /*Таблица пользователей users*/create table users( /*Определяем user_id как первичный ключ (целое число) с автоинкрементом (+1), который никогда не будет равен NULL*/ user_id integer not null auto_increment primary key, user_name varchar(15), /*имя пользователя*/ user_surname varchar(20), /*фамилия пользователя*/ user_login varchar(20), /*логин пользователя в системе*/ user_passwd varchar(5), /*пароль пользователя в системе*/ user_email varchar(40) /*email пользователя в системе*/);/*Таблица сообщений messages*/create table messages( mess_id integer not null auto_increment primary key, /*создаем поле для первичного ключа пользователя, который отправил сообщение*/ user_id_author integer, /*создаем еще одно поле для первичного ключа пользователя, который получил сообщение*/ user_id_receiver integer, /*текст сообщения*/ mess_text varchar(300));/*Таблица заметок notes*/create table notes( note_id integer not null auto_increment primary key, user_id integer, note_text varchar(200)); |
Теперь давайте себе представим такую ситуацию: вам необходимо ввести правки в ранее созданные таблицы базы данных, есть два пути, первый — удалить старые таблицы и добавить новые (исправленные), второй — отредактировать таблицу sql запросом ALTER TABLE, который сохранит данные в таблицах (в отличие от первого метода) и выполнит нужную нам задачу. Для примера исправим таблицы messages и notes: 1 — добавим в них заголовок сообщения и название пометки соответственно, 2 — увеличим количество допустимых знаков в пароле пользователя до 20. Синтаксис команды ALTER TABLE выглядит следующим образом:
1
| ALTER TABLE [IGNORE] название_таблицы опции_изменения_таблицы |
[IGNORE] — эта опция не обязательна, она существует для защиты первичных ключей при редактировании таблицы, т.е. если при изменении таблицы возникает дублирование первичных ключей, то первая строка с этим первичным ключем остается в изменяемой таблице, а остальные ключи будут удалены. По умолчанию эта опция не указывается и если вы не совсем понимаете зачем она нужна, то лучше не применяйте ее в реальных проекта «на всякий случай».
Опций изменения таблиц очень много (более 25, поэтому я не буду здесь приводить описание каждой из них, а просто выполню требуемую задачу, подробнее об этих опциях можно узнать на сайте mysql.ru/docs/man/ALTER_TABLE.html), они позволяют сделать с таблицей все, что душе угодно: добавлять и удалять произвольное количество столбцов в любое место таблицы, изменять типы и название столбцов, переименовывать и удалять саму таблицу, преобразовывать наборы символов и т.д.
1. Добавляем дополнительные столбцы в таблицы messages и notes:
1
2
3
4
5
6
| //изменяем таблицу messages путем добавления в нее //столбца mess_title с типом данных внутри varchar(50), //новый столбец будет располагаться после user_id_receiverALTER TABLE messages add mess_title varchar(50) after user_id_receiver/*таким же образом редактируем таблицу notes*/ALTER TABLE notes add note_name varchar(100) after user_id |
2. Увеличиваем количество допустимых знаков в пароле пользователя до 20.
1
2
3
4
| //редактируем таблицу users путем изменения //поля user_passwd, заново прописываем тип //и размер поля varchar(20)ALTER TABLE users modify user_passwd varchar(20) |
Вставка информации (записей) в таблицы базы данных.
После того как мы создали и немного отредактировали нашу базу данных, ее нужно заполнить информацией. Нам нужны пользователи, сообщения этих пользователей, направленные друг к другу и несколько заметок для экспериментов. Вставить информацию в базу данных можно с помощью sql запроса INSERT INTO, вот его синтаксис:
1
2
3
| INSERT [LOW_PRIORITY или DELAYED] [IGNORE] [INTO] название_таблицы (в скобках можно указывать список_колонок в которые нужно вставлять данные) VALUES вставляемое_значение1, ... |
[IGNORE] — описание этой опции смотрите выше.
[LOW_PRIORITY | DELAYED] — это приоритеты добавления информации в базу данных, LOW_PRIORITY — означает, что СУБД подождет удобного момента (когда она будет свободна от операций) для добавления информации, а опция DELAYED — означает, что данные будут буферизироваться. Все эти три параметра (IGNORE, LOW_PRIORITY и DELAYED) являются необязательными.
[LOW_PRIORITY | DELAYED] — это приоритеты добавления информации в базу данных, LOW_PRIORITY — означает, что СУБД подождет удобного момента (когда она будет свободна от операций) для добавления информации, а опция DELAYED — означает, что данные будут буферизироваться. Все эти три параметра (IGNORE, LOW_PRIORITY и DELAYED) являются необязательными.
Для примера я заполню три наши таблицы разными способами, каким пользоваться, выбирать вам:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| /*заполняем таблицу users пользователями вставкой всех полей сразу*/insert into users values (0, "User1", "User surname1", "User login1", "248jslefgwsg", "user1@mail.ru"), (0, "User2", "User surname2", "User login2", "3hhsrgerghfd", "user2@mail.ru"), (0, "User3", "User surname3", "User login3", "34ysergsdgfd", "user3@mail.ru"), (0, "User4", "User surname4", "User login4", "sdfgsdghgfgs", "user4@mail.ru");//вставляем записи в таблицу только в выбранные //нами столбцы, здесь мы не указали столбец //mess title, его поле будет пустым//пользователь с user_id 1 (т.е. User1) отправляет //пользователю с user_id 2 (т.е. User2) сообщение "Hello my friend."insert into messages (mess_id, user_id_author, user_id_receiver, mess_text) values(0, 1, 2, "Hello my friend.");insert into messages (mess_id, user_id_author, user_id_receiver, mess_text) values(0, 2, 1, "Hi.");insert into messages (mess_id, user_id_author, user_id_receiver, mess_text) values(0, 1, 2, "Good by");//заполняем таблицу заметок, здесь мы тоже не //будем заполнять столбец note_name, как и в предыдущем примере/*добавляем три записи первому (User1) пользователю*/insert into notes (note_id, user_id, note_text) values (0, 1, "Notes 1");insert into notes (note_id, user_id, note_text) values (0, 1, "Notes 2");insert into notes (note_id, user_id, note_text) values (0, 1, "Notes 3"); |
Редактирование информации (записей) в таблицах базы данных.
Редактировать записи в базе данных можно с помощью sql запроса UPDATE, вот его синтаксис:
Редактировать записи в базе данных можно с помощью sql запроса UPDATE, вот его синтаксис:
1
2
3
4
| UPDATE [LOW_PRIORITY] [IGNORE] название_таблицы set имя_столбца = новое_значение, .....[WHERE - условная под которые подпадают изменяемые столбцы][ORDER BY сортировка по критериям][LIMIT число редактируемых записей] |
Описание опций LOW_PRIORITY и IGNORE смотрите выше. Как пользоваться параметрами WHERE, ORDER BY и LIMIT и для чего они нужны? Эти параметры указывают тот набор записей, в которых нужно произвести изменения.
Для примера изменим значения заголовка сообщений для всех записей в таблице messages:
1
2
3
4
| //изменяем значение поля note_name на 'New message', //т.к. мы не указали ограничительных параметров //(WHERE и LIMIT) этот запрос подействует на все записи в этой таблицеUPDATE messages set mess_title = 'New message' |
Второй пример обновления записи — мы хотим изменить значение заголовка одного сообщения для одной записи. Немного модифицируем предыдущий пример:
1
2
| /*изменяем значение поля mess_title на 'New message', */UPDATE messages set mess_title = 'Old message' limit 1 |
Чтобы испробовать опцию WHERE рассмотрим вот такой пример: нам нужно изменить логин пользователя с логином User login1 (такое часто приходится делать при управлении базой данных с пользователями):
1
2
3
| //изменяем поле user_login в таблице users //у пользователя с логином User login1 на значение 'User1'UPDATE users set user_login = 'User1_1' where user_login = 'User login1' |
Выборка (извлечение) информации из таблиц базы данных.
После того как мы создали таблицу, отредактировали нужные нам столбцы, заполнили и отредактировали кое-какую информацию, нам можно спокойно переходить к самому часто используемому (и наверно главному запросу, хотя без остальных тоже ничего не получится) sql запросу — SELECT. По запросу SELECT можно написать отдельную статью (я так позже и сделаю), а пока опишу лишь малую часть его функционала и поставлю ссылку на его хороший обзор с сайта mysql.ru/docs/man/SELECT.html.
После того как мы создали таблицу, отредактировали нужные нам столбцы, заполнили и отредактировали кое-какую информацию, нам можно спокойно переходить к самому часто используемому (и наверно главному запросу, хотя без остальных тоже ничего не получится) sql запросу — SELECT. По запросу SELECT можно написать отдельную статью (я так позже и сделаю), а пока опишу лишь малую часть его функционала и поставлю ссылку на его хороший обзор с сайта mysql.ru/docs/man/SELECT.html.
С помощью команды SELECT и ее параметров можно извлекать любые данные в нужной нам послодовательности и количестве. Синтаксис команды SELECT выглядит вот так:
1
2
3
4
5
6
| SELECT опции и поля FROM список_таблиц[WHERE] условие для "отсеивания" не нужных записей[GROUP BY] группировка полученных результатов по какому-нибудь столбцу[HAVING] используется также для фильтрации результата GROUP BY по заданным условиям, но только на другой стадии формирования ответа.[ORDER BY] сортировка результатов ответа.[LIMIT] количество требуемых записей в ответе |
Рассмотрим несколько примеров выборок:
- Найдем все сообщения системы.
- Найдем все сообщения пользователя с логином ‘User1_1′.
- Выведем имена трех пользователей системы обратном порядке по полю имени.
Думаю решения этих задач хватит для понимания основ работы sql запроса SELECT.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| //1. Выбираем значение поля mess_text из таблицы //messages, т.к. других условий не указано значения //mess_text возьмутся из каждой записи.SELECT mess_text FROM messages;//2. Для того чтобы найти все сообщения //пользователя с логином 'User1_1' нам нужно узнать //его первичный ключ (т.е. узнать user_id, т.к. с помощью него //таблицы связаны между собой), а уже по этому первичному //ключу можно без труда выбрать все сообщения через условие where.SELECT mess_text FROM messages where user_id_author = (select user_id from users where user_login = 'User1_1');//3. Выбираем столбец с именем пользователя, указываем //тип сортировки ORDER BY user_name desc, т.е. в обратном //порядке по имени (desc - это обратный порядок, а asc - стандартный)// и указываем количество нужных записей (LIMIT 3)SELECT user_name FROM users ORDER BY user_name desc LIMIT 3; |
4. Удаление информации (записей) из базы данных.
Удаление лишних записей из базы данных — это тоже очень частая операция, но с ней нужно быть намного осторожнее чем с SELECT’ом. Если вы работаете с реальной базой данных и вы не уверены что вы хорошо познакомились с конструкцией WHERE…, то лучше проверяйте каждый sql запрос на удаление на тестовой базы данных (правило простое, но им часто пренебрегают). Рассмотрим синтаксис команды DELETE (он очень схож с SELECT):
Удаление лишних записей из базы данных — это тоже очень частая операция, но с ней нужно быть намного осторожнее чем с SELECT’ом. Если вы работаете с реальной базой данных и вы не уверены что вы хорошо познакомились с конструкцией WHERE…, то лучше проверяйте каждый sql запрос на удаление на тестовой базы данных (правило простое, но им часто пренебрегают). Рассмотрим синтаксис команды DELETE (он очень схож с SELECT):
1
2
3
4
| DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT rows] |
Все параметры вам уже знакомы (смотрите выше), кроме QUICK — эта опция указывает СУБД на то, что нужно ускорить операцию удаления, если это возможно.
Рассмотрим пару примеров удаление записей из базы данных.
1
2
3
4
5
6
7
8
9
| //Удаляем из таблицы пользователей (users) все //записи в которых поле user_login равно 'User login4', если //бы мы не указали условия WHERE user_login = 'User login4', //то все пользователи были бы удалены.DELETE FROM users WHERE user_login = 'User login4';//удаляем все сообщения пользователей, т.к. //мы не указали никаких условий MySQL удалит все записи в таблице messagesDELETE FROM messages; |
На этом все.