Самым популярным способом доступа к реляционным базам данных является язык запросов 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 колонки 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];
Очень часто программисты стараются создавать таблицы и колонки без пробелов, поэтому не так часто можно увидеть запросы, в которых используются символы, которыми окружаются имена объектов.
Отлично, мы научились выбирать все данные из таблицы или определенные колонки, а теперь хорошо бы научиться еще и выбирать только определенные строки.
Формат команды выборки начинает усложняться и уже начинает выглядеть так:
ВЫБРАТЬ колонки ИЗ таблица ГДЕ фильтр
В качестве фильтра можно указывать имя колонки, по которой мы хотим фильтровать и значение, которое мы ищем. Например, если мы хотим найти все записи из нашего телефонного справочника, где фамилия владельца это 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 'Фл_нов’
Подчеркивание означает один и только один символ. Недостаток именно этого запроса – он возвращает не только Фленов и Флёнов, но, возможно, и какие-то другие вариации, если они существуют Фланов, Флонов и т.д. Но возможно именно это нам и нужно.
Если выбрать все содержимое таблицы 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;
На этом пока с основами получения данных закончим. В процессе рассмотрения дальнейшего материала мы познакомимся с еще более сложными запросами на практике.
Когда мы выполняем запрос, то база данных может вернуть данные в любой последовательности, хотя чаще всего возвращает строки в том порядке, в котором они хранятся и чаще всего это будет совпадать со значением ключевой колонки, в нашем случае это 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);
Она выполниться успешно только если в таблице нет колонок с обязательными полями без значения по умолчанию.
Бывают такие случаи, когда данные вставил в таблицу и они больше никогда не меняются. Но в реальной жизни нередко данные подвержены изменениям и у нас должна быть возможность сделать это.
Минимальная команда изменения данных:
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.
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.