Sql как найти пустые ячейки

Прочитав название темы, можно задать вопрос: “А зачем вообще вводить в БД поля, не содержащие значений?” Ответ на этот вопрос кроется в тексте ниже.
Что такое база данных? Это информация, которая формируется и развивается на протяжении всего времени своего существования. Постоянно приходится что-то добавлять, что-то убирать. И так на протяжении всего жизненного цикла БД. Однако, есть такие периоды, точней моменты в жизни БД, когда есть часть информации об объекте и предполагается, что остальная часть будет добавлена по истечении какого-то периода времени.
Для примера возьмём таблицу должников.

Предположим, что появился очередной кандидат по долгу, но сумма его задолженности пока не сформирована, но известны остальные его данные – дата, до которой он должен погасить задолженность, его фамилия и адрес проживания. В этом случае, столбец Debt для данного должника останется пустым, а чтобы это подтвердить, поле будет назначено как пустое – NULL

Как говорилось выше, значение NULL подразумевает, что поле с таким значением не содержит каких-либо данных, которыми могли бы оперировать SQL-запросы. То есть, если проводить какие-либо отборы по используя данный оператор, результат будет таким же, как и при указании неверной неверной строки. Другими словами, любой запрос типа
SELECT *
FROM Debtors
WHERE любой столбец NULL;
Приведёт к тому, что программа работы с БД выдаст ошибку.
Для выхода из такой ситуации в SQL существует специальный оператор, который используется с ключевым словом NULL – это оператор IS. Теперь можно задать запрос, который смог бы отобрать строки, содержащие, точней не содержащие никаких значений
SELECT *
FROM Debtors
WHERE Debts IS NULL;
Результатом будет следующее

Если перевести ключевые слова строки запроса “WHERE Debts IS NULL” с английского, то получится буквально следующее: “ГДЕ Debts ЕСТЬ ПУСТОЕ”
Можно ли сделать, чтобы запрос с ключевым словом NULL игнорировался? Можно! Достаточно перед ним поставить Булево выражение NOT. 
Есть несколько вариантов размещения ключевого слова NOT в запросе, да бы игнорировалось значение NULL
SELECT *
FROM Debtors
WHERE Debt NOT IS NULL;
Или же использовать
SELECT *
FROM Debtors
WHERE NOT Debt IS NULL;
Результат обоих запросов будет один

То есть данные запросы попросту убрали из табличной части БД строки, содержащие в столбце Debt пустые ячейки.
Эта запись была посвящена тому, как ведёт себя БД, если в её состав входят ячейки с отсутствующими данными. Так же было сказано о том, как можно исключать такие ячейки при помощи операторов NULL и IS NULL.

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

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

Для тестирования нам понадобиться какая-то база данных, на которой мы будем тренироваться. Так как создание самой базы и таблиц я решил отложить на потом, я подготовил файл, который создаст для вас две таблицы.

Phone:

Phoneid	Firstname	Lastname	Phone	Cityid
1	John	Doe	4144122	1
2	Steve	Doe	414124	1
3	Johnatan	Something	4142947	2
4	Donald	Trump	414251123	2
5	Alice	Cooper	414254234	2
6	Michael	Jackson	4142544	3
7	John	Abama	414254422	3
8	Andre	Jackson	414254422	3
9	Mark	Oh	414254422
10	Charly	Lownoise	414254422

City

Cityid	cityname
1	Toronto
2	Vancouver
3	Montreal

Итак, скачайте файл testdb.sql

Если вы используете VS Code, то подключитесь к базе данных mysql, откройте новое окно для SQL запросов, скопируйте в него содержимое файла testdb.sql, и нажмите кнопку выполнения.

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

Теперь мы готовы к изучению SQL. Когда вы работаете с Excel таблицей, что вы можете с ней сделать? Искать данные в таблице поиском, добавлять новые строки, изменять существующие, удалять строки. То же самое можно делать и с базой данных, давайте начнем знакомиться с тем, как можно отображать содержимое таблицы и искать данные.

SELECT доступ к одной таблице

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

Начнем с самой простой версии:

SELECT колонки FROM базаданных.таблица

Большими буквами я выделил ключевые слова языка запросов SQL, а русскими маленькими буквами показано то, что мы должны заменить на реальные значения. Если перевести эту команду, то она будет звучать:

ВЫБРАТЬ колонки ИЗ база.данных.таблица

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

Колонки – это список имен колонок через запятую. Если вы хотите выбрать все колонки, то можно указать символ звездочки *.

У нас есть таблица City, давайте выберем из нее все записи и все колонки. Все колонки, значит нужно заменить слово “колонки” на символ звездочки, а на месте таблицы пишем city и в результате получаем

SELECT * FROM testdb.сity

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

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

+--------+-----------+
| cityid | cityname  |
+--------+-----------+
|      1 | Toronto   |
|      2 | Vancouver |
|      3 | Montreal  |
+--------+-----------+
3 rows in set (0.00 sec)

Если мы пишем множество запросов, неужели каждый раз придется писать имя базы данных перед именем таблицы? Нет, это не обязательно. Если вы работаете с определенной базой, то можно как бы перейти в нее, или можно еще сказать выбрать ее. Для этого выполняем команду:

USE базаданных

Слово USE означает «использовать». То есть мы просим сервер использовать определенную базу для всех последующих запросов, пока снова не выберем другую. В нашем случае база данных это testdb, так что выполняем команду:

USE testdb

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

SELECT * FROM сity

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

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

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

SELECT * FROM City
SELECT * FROM CITY

Потому что называние города написано в неверном регистре.

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

select * from city 

Или даже эта

SeLeCt * FrOm city

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

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

SELECT cityid, cityname FROM city

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

SELECT cityname, cityid FROM city

Или можно отобразить только имя города:

SELECT cityname FROM city

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

Пробелы в именах объектов базы данных

У тебя может возникнуть вопрос – а что, а можно создавать имена таблица или колонок из нескольких слов и как тогда MySQL будет работать с пробелами? Создавать объекты с пробелами можно, но в этом случае имя нужно окружить специальными символами, которые зависят от базы данных, в MySQL это символ ` который находится слева от цифры 1 на большинстве клавиш.

Так что теоретически наш запрос может выглядеть так:

SELECT `Adress id`, Name FROM `Address Table`

Обратите внимание, что колонка Address id содержит пробел, поэтому вначале и в конце стоит символ `. У колонки Name нет пробелов, поэтому ничего добавлять не нужно. У имени таблицы так же есть пробел.

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

SELECT `cityname` FROM `city`;
SELECT cityname FROM `city`;
SELECT `cityname` FROM city;
SELECT cityname FROM city;

Все они корректны и все будут работать.

Хотя все примеры мы рассматриваем и тестируем под MySQL, почти все они будут работать и в других базах данных, но вот разделитель в разных базах может отличаться. В MS SQL Server это квадратные скобки:

SELECT [cityname] FROM [city];

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

Фильтрация выборки WHERE

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

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

ВЫБРАТЬ колонки ИЗ таблица ГДЕ фильтр

В качестве фильтра можно указывать имя колонки, по которой мы хотим фильтровать и значение, которое мы ищем. Например, если мы хотим найти все записи из нашего телефонного справочника, где фамилия владельца это Doe, то мы должны в фильтре указать:

lastname = 'Doe'

Здесь lastname – это имя колонки, поэтому его просто указываем без каких-то дополнений. Когда сервер будет читать этот запрос, то он увидит слово lastname, попробует найти это имя среди известных ему имен и без проблем сможет найти его, так что вопросов нет.

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

Взглянем на следующий пример:

lastname = Mc Donald

Без одинарных кавычек MySQL не сможет понять этот фильтр, потому что он будет думать – нужно ли искать только по Mc или нужно искать по Mc Donald. А если все это объединить в одинарные кавычки, то фильтр станет корректным.

lastname = 'Mc Donald'

Если символы, которыми мы окружаем имена объектов являются НЕ обязательными, то одинарные кавычки являются обязательными и их опускать НЕЛЬЗЯ.

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

SELECT * FROM phone WHERE lastname = 'Doe'; 

В результате вы должны увидеть только две строки:

+---------+-----------+----------+---------+--------+
| phoneid | firstname | lastname | phone   | cityid |
+---------+-----------+----------+---------+--------+
|       1 | John      | Doe      | 4144122 |      1 |
|       2 | Steve     | Doe      | 414124  |      1 |
+---------+-----------+----------+---------+--------+
2 rows in set (0.01 sec)

В большом городе может оказаться слишком много людей с фамилией Doe и когда мы ищем телефон, то скорей всего мы знаем, что нужного нам человека зовут Steve. Мы можем искать сразу по двум колонкам – имени и фамилии, просто объединив обе проверки с помощью слова AND:

SELECT * FROM phone WHERE lastname = 'Doe' AND firstname = 'Steve'; 

В ответ должна быть отображена только одна строка:

+---------+-----------+----------+--------+--------+
| phoneid | firstname | lastname | phone  | cityid |
+---------+-----------+----------+--------+--------+
|       2 | Steve     | Doe      | 414124 |      1 |
+---------+-----------+----------+--------+--------+
1 row in set (0.00 sec)

Взглянем по-другому – мы ищем по фамилии и хотим увидеть всех, чья фамилия Doe или Jackson. Просто возможно человек поменял фамилию, и мы не знаем, под какой из них остался зарегистрирован телефон. Нам нужна записи, где колонка lastname равна Doe или Jackson. Именно так мы и должны писать наш запрос, объединив две проверки с помощью ИЛИ, в английском это OR:

SELECT * FROM phone WHERE lastname = 'Doe' OR lastname = 'Jackson'; 

Результат

+---------+-----------+----------+-----------+--------+
| phoneid | firstname | lastname | phone     | cityid |
+---------+-----------+----------+-----------+--------+
|       1 | John      | Doe      | 4144122   |      1 |
|       2 | Steve     | Doe      | 414124    |      1 |
|       6 | Michael   | Jackson  | 4142544   |      3 |
|       8 | Andre     | Jackson  | 414254422 |      3 |
+---------+-----------+----------+-----------+--------+
4 rows in set (0.00 sec)

Ok, фамилии меняют после свадьбы и хотя у меня в таблице все имена мужские (я только сейчас сообразил и это сделано не специально), допустим, что мы знаем имя и это Andre. Возможно вы захотите написать запрос так:

SELECT * 
FROM phone 
WHERE lastname = 'Doe' OR 
   lastname = 'Jackson' AND 
   firstname = 'Andre';

Может показаться, что в результате должна быть только одна запись – Andre Jackson, но это не так, мы увидим три записи:

+---------+-----------+----------+-----------+--------+
| phoneid | firstname | lastname | phone     | cityid |
+---------+-----------+----------+-----------+--------+
|       1 | John      | Doe      | 4144122   |      1 |
|       2 | Steve     | Doe      | 414124    |      1 |
|       8 | Andre     | Jackson  | 414254422 |      3 |
+---------+-----------+----------+-----------+--------+
3 rows in set (0.00 sec)

Дело в том, что наш запрос говорит, что мы хотим увидеть всех с фамилией Doe ИЛИ всех с фамилией Jackson и именем Andre. Чтобы проще было понять проблему я добавлю скобки, чтобы показать, как сгруппированы проверки:

lastname = 'Doe' 
OR 
(lastname = 'Jackson' AND firstname = 'Andre')

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

(lastname = 'Doe' OR lastname = 'Jackson')
AND 
firstname = 'Andre'

Здесь мы уже говорим, что у человека может быть фамилия Doe или Jackson, но имя обязательно должно быть Andre.

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

+---------+-----------+----------+-----------+--------+
| phoneid | firstname | lastname | phone     | cityid |
+---------+-----------+----------+-----------+--------+
|       8 | Andre     | Jackson  | 414254422 |      3 |
+---------+-----------+----------+-----------+--------+
1 row in set (0.00 sec)

Для подобных задач в SQL есть более красивый синтаксис – использовать слово IN, что можно перевести как одно из. Формат такой:

Колонка in (значения, перечисленные через запятую)

То есть запрос, где мы искали одну из двух фамилий, можно переписать так:

SELECT * FROM phone WHERE lastname IN ('Doe', 'Jackson'); 

На мой взгляд это читается на много проще. Если прочитать это предложение по-русски, то все будет звучать так:

ВЫБРАТЬ все ИЗ телефоны ГДЕ фамилия ОДНА ИЗ ('Doe', 'Jackson')

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

SELECT * 
FROM phone 
WHERE lastname IN ('Doe', 'Jackson') AND firstname = 'Andre';

Тоже достаточно просто читается и не нужно заморачиваться со скобками, чтобы указать на приоритет, как мы объединяем ИЛИ и И.

Когда мы ищем по числам, то их оборачивать в одинарные кавычки не нужно. Допустим, что мы хотим найти запись в справочнике под номером 1. Именно под номером, а не первую под счету. Такой запрос может выглядеть так:

SELECT * FROM phone WHERE phoneid = 1

В случае с числами еще очень часто может потребоваться искать числа больше или меньше какого-то значения. Допустим, что нужно найти все записи, где id телефона меньше 5. В нашем случае это будет первые 4 строки. Как и в математике, так и в программировании можно использовать символы:

• < меньше
• > больше
• >= больше или равно

В нашем случае можно использовать < 5 как в следующем примере:

SELECT * FROM phone WHERE phoneid < 5

Результат:

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       4 | Donald    | Trump     | 414251123 |      2 |
+---------+-----------+-----------+-----------+--------+

Если мы хотим включить в выборку и строку с phoneid равных 5, то можно увеличить число до 6 или использовать меньше или равно <=

SELECT * FROM phone WHERE phoneid

Результат:

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       4 | Donald    | Trump     | 414251123 |      2 |
|       5 | Alice     | Cooper    | 414254234 |      2 |
+---------+-----------+-----------+-----------+--------+

Усложняем задачу, ищем записи с id больше 3 и меньше 7. И снова мы можем воспользоваться AND, чтобы объединить две проверки:

SELECT * FROM phone WHERE phoneid > 3 and phoneid < 7

Чтобы проще читать и красивее все выглядело можно то же самое записать:

SELECT * FROM phone WHERE 3 < phoneid and phoneid < 7

Для этой задачи есть вариант решения проще, по крайней мере для некоторых – использовать between:

SELECT * FROM phone WHERE phoneid between 4 and 6;

Обратите внимание, что я использую числа 4 и 6, а не 3 и 7, потому что between включает граничные значение, это то же самое, что и:

SELECT * FROM phone WHERE phoneid >= 4 and phoneid

С точки зрения чтения это звучит лучше: выбрать все из телефонов, где id между 4 и 6. Звучит хорошо, но я почему-то почти не использую эту конструкцию. Мне больше нравится решать то же самое с помощью математических конструкций > или

Если работать со строками, то тут SQL предоставляет нам некую гибкость, мы можем искать по шаблону. Допустим, что мы хотим найти всех, у кого имя начинается с буквы J. Для этого используем новое слово LIKE. В английском это слово очень часто можно перевести как “нравиться” или “выглядеть как”, в зависимости от того, в качестве какой части речи использовать это слово. В данном случае это второй вариант. После этого мы можем использовать в качестве шаблона специальные символы:

% заменяет любое количество любых символов

_ заменяет один, но любой символ

Так как нам нужно найти всех, у кого первая бука J, а потом идет любое количество любых символов, то наш шаблон будет выглядеть как ‘J%’

SELECT * FROM phone WHERE firstname LIKE 'J%'

Результат:

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       1 | John      | Doe       | 4144122   |      1 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       7 | John      | Abama     | 414254422 |      3 |
+---------+-----------+-----------+-----------+--------+
3 rows in set (0.00 sec)

А что если мы хотим найти любую фамилию, в которой содержится хотя бы одна буква A. Для этого можно указать % перед и после буквы A:

SELECT * FROM phone WHERE lastname LIKE '%a%'

Символ % означает любое количество любых символов, значит до и после A может быть что угодно и в любом количестве.

Отлично, но что, если мы не знаем только одну букву. Например, моя фамилия Флёнов, но очень часто приходиться писать Фленов только потому, что буква ё не поддерживается. Очень часто это проблема печати – в паспорте, в бумажном журнале или в книге.

SELECT * FROM phone WHERE lastname LIKE 'Фл_нов’

Подчеркивание означает один и только один символ. Недостаток именно этого запроса – он возвращает не только Фленов и Флёнов, но, возможно, и какие-то другие вариации, если они существуют Фланов, Флонов и т.д. Но возможно именно это нам и нужно.

Пустые поля NULL

Если выбрать все содержимое таблицы phone, то в последних двух строках будет не число, а какое странное NULL:

SELECT * FROM phone;

Результат

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       4 | Donald    | Trump     | 414251123 |      2 |
|       5 | Alice     | Cooper    | 414254234 |      2 |
|       6 | Michael   | Jackson   | 4142544   |      3 |
|       7 | John      | Abama     | 414254422 |      3 |
|       8 | Andre     | Jackson   | 414254422 |      3 |
|       9 | Mark      | Oh        | 414254422 |   NULL |
|      10 | Charly    | Lownoise  | 414254422 |   NULL |
+---------+-----------+-----------+-----------+--------+

NULL – это не строка и не число, это отсутствующее значение, то есть в этих двух строках в колонке cityid отсутствует. NULL можно перевести как ноль, но правильнее все же переводить это слово как “несуществующий” или “недействительный”.

Если поле с числом равно 0, то это число, просто оно нулевое. А если поле с числом равно NULL, то это уже не число и не ноль, это значит, что там вообще числа нет, черная дыра, пробоина, все что угодно, но только не число.

Я только что ляпнул новое понятие – поле. Это пересечение колонки и строки. Это то, куда мы записываем значение какой-то колонки/строки.

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

Для базы данных есть огромная разница – мы храним пустую строку или в поле нет вовсе значения, потому что это разные вещи. Если строка пустая, то это все же строка, просто у нее нет длины, но если значения нет, то строки не существует.

Скорость у машины может быть нулевая, если машина стоит или какое-то число, если машина едет. А если машины нет? Скорости тоже не будет в принципе, и мы не можем сказать, что скорость нулевая у машины, которой просто нет.

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

select * from phone where cityid = null;

то ничего не вернется. Казалось бы, мы же сравниваем число символом сравнения с NULL, но это не работает. Дело в том, что сравнивать с помощью равенства нельзя, вместо этого нужно использовать слово is:

select * from phone where cityid is null;

А если мы хотим найти все строки, в которых поле не пустое, а имеет какое-то значение. Тут нужно использовать is not:

select * from phone where cityid is not null;

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

Сортировка данных в запросах SQL

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

Если вы хотите отсортировать по фамилии, то мы должны это явно сказать серверу. Для этого используется ORDER BY, который ставиться в конце запроса:

select * from phone order by lastname;

Результат

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       7 | John      | Abama     | 414254422 |      3 |
|       5 | Alice     | Cooper    | 414254234 |      2 |
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       6 | Michael   | Jackson   | 4142544   |      3 |
|       8 | Andre     | Jackson   | 414254422 |      3 |
|      10 | Charly    | Lownoise  | 414254422 |   NULL |
|       9 | Mark      | Oh        | 414254422 |   NULL |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       4 | Donald    | Trump     | 414251123 |      2 |
+---------+-----------+-----------+-----------+--------+

Обратите внимание, что первая колонка теперь не отсортирована, а вот в lastname все значения возрастают начиная с буквы A в сторону Z. Нет, это происходит не всегда. Если мы не указали направление сортировки, то используется ASC, возрастание, то есть это то же самое, что написать:

select * from phone order by lastname asc;

А теперь посмотрите на колонку имени – оно не по возрастающей. Мы попросили отсортировать по фамилии, а когда фамилия одинаковая, то сервер имеет право вернуть данные в любом порядке и в данном случае ему удобно вывести в соответствии с ключевой колонкой phoneid. У Michael Jackson первая колонка равна 6 и это меньше 8, что мы видим у Andre.

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

select * from phone order by lastname asc, firstname asc;

следующий запрос вернет то же самое, потому что не забываем, что ASC – возрастание это сортировка по умолчанию:

select * from phone order by lastname, firstname;

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

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

Если вы хотите отсортировать таблицу по фамилии, но в обратном порядке, то вместо ASC нужно указать DESC – убывание:

select * from phone order by lastname desc;

Результат:

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       4 | Donald    | Trump     | 414251123 |      2 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       9 | Mark      | Oh        | 414254422 |   NULL |
|      10 | Charly    | Lownoise  | 414254422 |   NULL |
|       6 | Michael   | Jackson   | 4142544   |      3 |
|       8 | Andre     | Jackson   | 414254422 |      3 |
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       5 | Alice     | Cooper    | 414254234 |      2 |
|       7 | John      | Abama     | 414254422 |      3 |
+---------+-----------+-----------+-----------+--------+

Добавить запись в таблицу

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

INSERT имя таблицы VALUES (значения колонок)

С именем таблицы вопросов нет. Если мы хотим вставить значения в таблицу телефонов, то пишем:

INSERT phone VALUES (значения колонок)

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

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

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

Итак, мы должны перечислить значения всех колонок, а их у нас в таблице 5, из которых первое и последние числа, значит их указываем без кавычек и указываем именно число. Первое поле ключ и его значение указывать не обязательно, но если вы сделаете это, то обязательно укажите уникальное число, которого до сих пор не было. Я создал таблицу с 10 строками, и первая колонка содержит значения от 1 до 10. Следующее значение 11, поэтому можно указать его.

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

INSERT phone VALUES (11, 'Anna', 'Koko', '41213213', 1);

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

INSERT phone VALUES (null, 'Elen', 'Rokoko', '41213183', 1);

Если мы показываем, что для первой колонки передано NULL, то есть мы не хотим указывать значение, то для автоматически увеличиваемых полей сервер сам найдет следующее свободное и будет использовать его. В нашем случае должно быть 12. Проверим:

mysql> select * from phone;

Результат:

+---------+-----------+-----------+-----------+--------+
| phoneid | firstname | lastname  | phone     | cityid |
+---------+-----------+-----------+-----------+--------+
|       1 | John      | Doe       | 4144122   |      1 |
|       2 | Steve     | Doe       | 414124    |      1 |
|       3 | Johnatan  | Something | 4142947   |      2 |
|       4 | Donald    | Trump     | 414251123 |      2 |
|       5 | Alice     | Cooper    | 414254234 |      2 |
|       6 | Michael   | Jackson   | 4142544   |      3 |
|       7 | John      | Abama     | 414254422 |      3 |
|       8 | Andre     | Jackson   | 414254422 |      3 |
|       9 | Mark      | Oh        | 414254422 |   NULL |
|      10 | Charly    | Lownoise  | 414254422 |   NULL |
|      11 | Anna      | Koko      | 41213213  |      1 |
|      12 | Elen      | Rokoko    | 41213183  |      1 |
+---------+-----------+-----------+-----------+--------+
12 rows in set (0.00 sec)

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

INSERT phone VALUES (null, null, null, null, null);

Проверим результат:

SELECT * FROM phone WHERE phoneid = 13;

И вот что, что мы получили

+---------+-----------+----------+-------+--------+
| phoneid | firstname | lastname | phone | cityid |
+---------+-----------+----------+-------+--------+
|      13 | NULL      | NULL     | NULL  |   NULL |
+---------+-----------+----------+-------+--------+
1 row in set (0.04 sec)

Указывать отсутствующее значение (NULL) для всех колонок, для которых мы не хотим указывать реальное значение – странно и глупо. Вместо этого после имени таблицы в скобках можно указать имена колонок, значения которых мы хотим указать:

INSERT phone (phoneid, phone, firstname) VALUES (14, ‘4184719’, ‘Mary’);

В этом запросе после имени таблицы в скобках указаны имена колонок phoneid, phone и firstname. Я намеренно указал имена не в том порядке, как они созданы в таблице, ведь реально имя находиться в таблице вторым, а здесь третьим.

Именно в таком же порядке должны быть предоставлены значения в круглых скобках после слова VALUES. Как видите значения тоже идут в таком же порядке – ID, номер телефона и только потом имя.

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

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

INSERT phone () VALUES ();

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

INSERT phone VALUES (null, null, null, null, null);

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

SQL – Обновление данных

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

Минимальная команда изменения данных:

UPDATE таблица
SET колонка1 = значение, колонка2 = значение . . .
WHERE фильтр

В секции WHERE мы можем писать такие же условия, как мы делали и при SELECT. В остальном в принципе все понятно.

Давайте посмотрим на содержимое строки с ID = 14:

SELECT * FROM phone WHERE phoneid = 14;

Результат:

+---------+-----------+----------+---------+--------+
| phoneid | firstname | lastname | phone   | cityid |
+---------+-----------+----------+---------+--------+
|      14 | Mary      | NULL     | 4184719 |   NULL |
+---------+-----------+----------+---------+--------+
1 row in set (0.00 sec)

Здесь у нас Мэри, но у нее не было указано фамилии. Давайте обновим эту строку и укажим фамилию.

UPDATE phone
SET lastname = 'Poppins'
WHERE 

Стоп, что указать в качестве фильтра WHERE? Можно указать имя, но если в базе данных будет несколько записей людей с именем Mary, то мы обновим их все. Не думаю, что мы этого хотим.

По номеру телефона. . . Возможно это сработает, если номер действительно уникальный.

Если у нас есть колонка с уникальными значениями, то лучше использовать ее, тогда мы точно будем знать, что обновлена именно нужная нам запись. Именно поэтому создают в базах данных ключевые поля, как я это сделал с phoneid и самый простой способ добиться уникальности – сделать колонку автоматически увеличиваемой или сохранять в ней что-то типа уникального GUID.

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

Если забыть про наличие phoneid, которую я заведомо и продуманно создал, то мы можем вставить в таблицу две записи с абсолютно одинаковыми значениями. Допустим, что у нас есть такая таблица:

+-----------+-----------+-----------+
| firstname | lastname  | cityid    |
+-----------+-----------+-----------+
| Mary      | NULL      | 4184719   |
| Mary      | NULL      | 4184719   |
| NULL      | NULL      | NULL      |
+-----------+-----------+-----------+

Как мы можем обновить вторую запись Mary, без уникального кода id? А первую? Да все равно какую из них! Записи идентичны и с обновлением проблема. Самый простой способ – удалить обе записи и вставить новые. Да, это решит проблему, но все же.

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

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

UPDATE phone
SET lastname = 'Poppins'
WHERE personid = 14

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

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

UPDATE phone
SET lastname = 'Poppins',
    phone = '48171738'
WHERE personid = 14

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

Самая простая тема – это удаление данных. Самый простой вариант удалить данные – выполнить оператор:

DELETE FROM имятаблицы

Что удалиться? Все!

Если мы не хотим удалять все, то мы можем добавить уже знакомую нам секцию WHERE:

DELETE FROM phone
WHERE firstname = 'Mary'

Здесь мы удаляем все записи, где телефон принадлежит человеку с именем Mary. Если их больше одного, то будут удалены все.

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

DELETE FROM phone
WHERE phoneid = 14

В этом примере мы удалим запись, где id телефона равен 14.