Форумы портала PHP.SU » Разное » Обсуждение статей » Синтаксис SQL

Страниц (1): [1]
 

1. DeepVarvar - 25 Мая, 2012 - 23:44:32 - перейти к сообщению
Предполагается, что читатель умеет составлять простые запросы выборки из БД
с использованием операций сравнения (<, >, =, <>, !=) и вхождений IN( ... list ... ).
Также предполагается, что читатель умеет составлять простые разовые INSERT/UPDATE запросы.

Примеры будут приводиться для следующих таблиц:
CODE (SQL):
скопировать код в буфер обмена
  1. CREATE TABLE users (
  2.   id BIGINT NOT NULL AUTO_INCREMENT,
  3.   name CHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  4.   birthday DATE NOT NULL,
  5.   sex TINYINT(1) NOT NULL DEFAULT '0',
  6.   PRIMARY KEY (id)
  7.   ) ENGINE = MYISAM;

CODE (SQL):
скопировать код в буфер обмена
  1. CREATE TABLE messages (
  2.   id BIGINT NOT NULL AUTO_INCREMENT,
  3.   sender BIGINT NOT NULL,
  4.   receiver BIGINT NOT NULL,
  5.   message MEDIUMTEXT NOT NULL,
  6.   PRIMARY KEY (id)
  7.   ) ENGINE = MYISAM;


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

Основные правила:

0) Всегда именуйте таблицы и их поля в нижнем регистре используя в качестве смыслового разделителя для слов нижнее подчеркивание.
CODE (SQL):
скопировать код в буфер обмена
  1. shops_barcode, partial_delivery, message_from_world

1) Всегда пишите все команды SQL-языка и его зарезервированные слова в верхнем регистре.
Так в будущем, составляя какой-либо хитрый и длинный запрос, спокойно разберетесь в том, что написано.
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT id, name FROM users WHERE id IN(2, 4, 61, 33, 1);

2) Обрамляйте апострофами имена таблиц и столбцов только в том случае, если они конфликтуют с зарезервированными словами SQL.
Например это date, time, table и др... Почему не писать апострофы всегда?
Да потому, что в большом запросе среди апострофов уже не так-то просто сориентироваться.
К тому же полный, не сокращенный запрос будет выглядеть вот так:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT `users`.`id`, `users`.`name` FROM `users` WHERE `users`.`id` IN(2, 4, 61, 33, 1);

А если вы будете выбирать из трех таблиц сразу? Это уже будет выглядеть "по-аццки".

3) Используйте пояснительные комментарии в тексте запросов там, где вам кажется это нужным.

4) Используйте переносы строк для структурированного разделения "смысловых" частей запроса.
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT id, name /* на этой строке мы перечислим поля, которые нам нужны */
  2.   FROM users /* на этой укажем, откуда мы будем брать данные */
  3.   WHERE id IN(2, 4, 61, 33, 1); /* на этой будут условия выборки */

5) Используйте сокращения названий (имен) с помощью алиасов (альясов), т.е. псевдонимов.
Давать псевдонимы можно с помощью ключевого слова AS. Причем давать псевдонимы можно и полям, и таблицам, и даже выражениям.
Более того, само ключевое слова AS можно вообще опускать.
Вот несколько примеров:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT id AS user_id FROM users;

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT users.id AS user_id FROM users;

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT u.id, (22/10) AS floater FROM users AS u;

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT u.id, ((1+8)/3) sid FROM users u;

Использование этих нехитрых приемов и правил позволит упорядочить смысл того, что Вы пишете.
Более того, некоторые из приемов становятся необхоимыми при одновременной выборке из двух и более таблиц.
Например, Вам необходимо сделать выборку сообщений для пользователя с id равном единице.
Кроме того, Вам нужно включить в выборку еще и id всех сообщений и id пользователей.
В этом случае без алиаса (псевдонима) не обойтись.
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT m.id AS mid, u.id AS uid, u.name
  2.   FROM messages AS m, users AS u
  3.   WHERE u.id = 1 AND m.receiver = u.id

Это не очень сложный пример, но смысл перекрытия конфлита имен полей он отражает.
Этот пример можно еще более сократить и сделать аккуратным.
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT m.id mid, u.id uid, u.name
  2.   FROM messages m, users u
  3.   WHERE u.id = 1 AND m.receiver = u.id

Это была обычная выборка из двух таблиц.
Сейчас мы рассмотрим способ выборки из двух таблиц с помощью JOIN (присоединение).
JOIN бывает разный. Мы рассмотрим только 1 тип.
Это INNER JOIN или просто JOIN, т.к. слово "INNER" можно опускать, и писать только JOIN.

Перепишем наш последний запрос с использованием JOIN.
CODE (SQL):
скопировать код в буфер обмена
  1. /* как видно в примере, порядок полей и таблиц в выводе не важен */
  2. SELECT m.id mid, u.id uid, u.name, m.message, (2*60) rating
  3.   /* тут укажем выборку только из основной таблицы
  4.       и не важно что там написано в формировании вывода результата */
  5.   FROM users u
  6.   /* присоединим вторую таблицу к выборке
  7.       "приклеив" её по внешнему ключу
  8.       с помощью специальной конструкции ON,
  9.       она в этом месте очень похожа на условие WHERE
  10.       главное что мы вынесли логику сравнения ключа между двух таблиц
  11.       в отдельную смысловую строку */
  12.   JOIN messages m ON m.receiver = u.id
  13.   /* а тут будет чистое внешнее входное условие для выборки
  14.       от которого не зависят внутренние связи между таблиц */
  15.   WHERE u.id = 1

Очень надеюсь что вас заинтересуют типы JOIN,
а их несколько и все они по разному себя ведут при соединении таблиц.
Надеюсь что немногим позже, продолжение, пусть и не от меня,
появится либо прямо в этой теме, либо в новой теме рядом с этой.
(Добавление)
Хех, кстати подсветка синтаксиса на форуме доказывает эффективность "правописания" запросов. Закатив глазки
2. EuGen - 26 Мая, 2012 - 00:43:27 - перейти к сообщению
DeepVarvar пишет:
Этот пример можно еще более сократить и сделать аккуратным.
CODE (SQL):
скопировать код в буфер обмена

SELECT m.id mid, u.id uid, u.name
FROM messages m, users u
WHERE u.id = 1 AND m.receiver = u.id


Это была обычная выборка из двух таблиц.

От себя я бы не рекомендовал так делать. Вариант с альясингом через AS сработает и в MySQL, и в другой СУБД по ANSI-стандарту, а вот без AS - не факт.

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

я бы не стал выдавать такой неймспейсинг за единственно правильный. longTableName тоже допустимо, важно - чтобы стиль был единообразен.
По поводу неймспейсинга же еще бы добавил такую рекомендацию - а именно:
0. Именование ключей. Уникальные ключи именуются как ukField0Field1{..}FieldN, где Field0..FieldN - имена полей, по которым создается индекс. Внешний ключ имеет префикс fk
1. Именование первичных и внешних ключей - дополнительно. Как правило, таблица имеет первичный ключ по одному полю и оно - целочисленного типа. Всегда такое поле следует называть id. Для внешних же ключей вступает в силу правило - образования имени от имени главной таблицы и дополнения его справа символами "_id".
Простой пример. Есть таблица goods (id, name) и есть таблица store. Первая - список доступных наименования товаров (справочник), вторая - позиции конкретного товара на складе. Для связи будет использовано наименование good_id - по единственному числу наименования, указанного в таблице goods.
2. (Уже фигурировало в предыдущем пункте). Грамматическое наименование. Таблица - это сущность. Она почти никогда не должна содержать в имени действия, а всегда - название сущности. И название почти всегда во множественном числе. Поля же, как правило, указываются в единственном числе (но могут быть редкие исключения).
3. DeepVarvar - 26 Мая, 2012 - 00:49:55 - перейти к сообщению
Поддерживаю дополнения.
Добавлю что в PostgreSQL допустимо опускать AS.
Возможно это каким-то образом и зависит от версии ПО, но в последних (MySQL, PostgreSQL) однозначно разрешается.
4. EuGen - 26 Мая, 2012 - 00:53:04 - перейти к сообщению
DeepVarvar
Но есть и другие версии СУБД. Oracle, Firebird и т.п. В любом случае то, что гласит стандарт - поддерживается везде, а отступления от него - порождают риск непереносимости SQL-кода, чего следует избегать (даже если предполагается, что код будет использоваться только внутри одной-двух СУБД, это поддерживающих).
Дополню еще одним примером - а именно с JOIN
MySQL разрешает использовать USING, тогда как стандарт регламентирует использование ON. Ровно потому я, к примеру, всегда использую ON а не USING.
5. DeepVarvar - 26 Мая, 2012 - 00:57:14 - перейти к сообщению
Верно (и я кой-чего намотаю на ус из ваших дополнений).
6. Panoptik - 26 Мая, 2012 - 01:14:42 - перейти к сообщению
на счет JOINов хотелось бы получить сущность вариаций
в мануале читал что есть INNER/OUTER/RIGHT/LEFT смутно представляю себе, но на практике использую только LEFT. так вот на счет остальных было бы интересно узнать их предназначение
7. DeepVarvar - 26 Мая, 2012 - 01:24:21 - перейти к сообщению
LEFT - если в присоединяемой таблице не будет результата, то в вывод попадут все поля из главной + ассоциативные имена полей из присоединяемой, но они будут иметь значение NULL, что в php может интерпретироваться как пустота, с его "мягким" приведением типов. т.е. по умолчанию без сокращений выглядит как: LEFT OUTER JOIN

RIGHT - делает противоположное лефту, присоединяемая таблица становится приоритетнее и выборка производится сперва в ней. Это просто инвертор.

FULL - комбинированно заполняет NULL'ами там где не нашло данных для той или иной таблиц.

CROSS - это та же шляпа что и FROM tbl1, tbl2

OUTER - загуглил, это то что дает добро на заполнение значениями NULL, говорят что можно опускать как и AS.

INNER - антипод оутера, если в присоединяемой таблице не будет результата, то и строка из "главной" таблицы в вывод не попадает.
(Добавление)
Пруф:
CODE ():
скопировать код в буфер обмена
  1. http://ru.wikipedia.org/wiki/Join_%28SQL%29
8. Panoptik - 26 Мая, 2012 - 02:41:18 - перейти к сообщению
думаю стоило бы еще отметить такое маленькое правило как выборка столбцов
есть 2 варианта: простой и непростой
0. указать вместо полей звездочку *
1. указывать все имена полей

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

так что в продакшине нужно использовать именно второй метод. выбирать только то что необходимо и не более того
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT name, sex FROM users
9. DeepVarvar - 26 Мая, 2012 - 12:05:05 - перейти к сообщению
Panoptik пишет:
выбирать только то что необходимо и не более того
Да, мысль крутилась, но написать забыл. )))
А так не только в продакшне - так всегда делать надо.

 

Powered by ExBB FM 1.0 RC1