Продолжим знакомиться с SQL. Здесь я собрал некоторые возможности языка, которые я не стал включать в базовый SQL и оставил на будущее.
Для сортировки мы раньше использовали имена колонок и это в принципе работает и очень даже наглядно, но иногда бывает удобно просто сказать номер колонки – первая, вторая или любая другая. Это можно делать, просто вместо имени колонки в order by указываем порядковый номер колонки:
select * from phone order by 2;
Я иногда использую это в своих тестах или когда просто нужно выполнить запрос, но при программировании в коде я чаще все же использую нормальные имена. Порядковый номер не самое надежное решение.
В таблице может находиться сразу миллионы записей и если выполнить команду
SELECT * FROM phone
то в результате просто чтение всех записей займет очень много времени. Если каждая запись занимает 100 байт (это не так много), и мы будем вытаскивать миллион таких записей, то придется скопировать с сервера примерно 100 мегабайт. Примерно, потому что в мегабайте чуть больше миллиона.
Мы можем ограничить количество записей с помощью слова limit, после которого можно указать количество записей:
SELECT * FROM phone LIMIT 5;
В данном случае я попросил ограничить результат 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 | | 5 | Alice | Cooper | 414254234 | 2 | +---------+-----------+-----------+-----------+--------+
Если limit указать два числа, то первое число будет указывать на то, сколько записей нужно пропустить, а потом сколько отобразить. То есть чтобы отобразить пять строк, начиная с третей, мы должны указать 2 и 5 – пропустить 2 и потом отобразить 5:
select * from phone limit 2, 5;
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 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 | +---------+-----------+-----------+-----------+--------+
С помощью limit у меня на сайте реализованы страницы. На каждой странице у меня на блоге отображается 10 записей. Чтобы отобразить 5-ю страницу, нужно пропустить 40 строк и отобразить начиная с 41-й по 50-ю. Чтобы сделать это нужно выполнить запрос:
SELECT * FROM blog LIMIT 40, 10;
Иногда бывает удобно отображать уникальные данные. Давайте добавим в таблице две одинаковые записи:
INSERT INTO phone (firstname, phonenumber)
VALUES ('Mike', '1492823');
Теперь посмотрим на содержимое таблицы:
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 | | 12 | Mike | NULL | 1492823 | NULL | | 13 | Mike | NULL | 1492823 | NULL | +---------+-----------+-----------+-----------+--------+ 12 rows in set (0.00 sec)
Две последних строки практически одинаковы и очень часто это не то, что вы хотите видеть в результате. Допустим, что мы хотим увидеть уникальные записи и избавиться от дубликатов. Для этого существует слово distinct, которое нужно добавить после SELECT, но если просто его добавить, то оно не сработает. Попробуем:
SELECT DISTINCT * FROM phone;
Дело в том, что уникальность ищется по всем отображаемым колонкам, а первая колонка у обоих Майков разная, поэтому от дубликата мы не избавились. А вот если перечислить только колонки имени, фамилии, телефона, то мы получим нужный результат:
SELECT DISTINCT firstname, lastname, phone FROM phone;
Результат:
+-----------+-----------+-----------+ | firstname | lastname | phone | +-----------+-----------+-----------+ | John | Doe | 4144122 | | Steve | Doe | 414124 | | Johnatan | Something | 4142947 | | Donald | Trump | 414251123 | | Alice | Cooper | 414254234 | | Michael | Jackson | 4142544 | | John | Abama | 414254422 | | Andre | Jackson | 414254422 | | Mark | Oh | 414254422 | | Charly | Lownoise | 414254422 | | Mike | NULL | 1492823 | +-----------+-----------+-----------+ 11 rows in set (0.00 sec)
У нас теперь только один Mike и на одну строку меньше. А если вы хотите увидеть только уникальные имена:
SELECT DISTINCT firstname phone FROM phone;
Результат:
+----------+ | phone | +----------+ | John | | Steve | | Johnatan | | Donald | | Alice | | Michael | | Andre | | Mark | | Charly | | Mike | +----------+ 10 rows in set (0.00 sec)
Результат сократился еще на одну строку, потому что теперь у нас только один Mike и только один John. У Джонов разные фамилии, поэтому в прошлый раз мы видели их обоих.
К сожалению, у меня нет хороших колонок с числами в таблице телефонов, есть только phoneid и cityid. Это идентификаторы, но они все же числа, поэтому мы можем их сложить математически с помощью + записав это как phoneid + cityid:
select phoneid, CityId, phoneid + cityid from Phone;
Результат
+---------+--------+------------------+ | phoneid | CityId | phoneid + cityid | +---------+--------+------------------+ | 1 | 1 | 2 | | 2 | 1 | 3 | | 3 | 2 | 5 | | 4 | 2 | 6 | | 5 | 2 | 7 | | 6 | 3 | 9 | | 7 | 3 | 10 | | 8 | 3 | 11 | | 9 | NULL | NULL | | 10 | NULL | NULL | +---------+--------+------------------+
Смысла особо в третей колонке нет, это просто числа, но результат все же виден.
Вы можете выполнять и другие математические операции, такие как минус -, умножить * или разделить /.
Намного интереснее было бы объединить Имя и Фамилию в одну колонку. В записимости от базы данных это можно сделать плюсом, как в математике:
SELECT FirstName + ' ' + LastName FROM phone;
Когда все колонки строковые, то SQL Server будет объединять строки в одну. MySQL предпочитает вместо этого функцию CONCAT(колонка1, колонка2...). В круглых скобках через запятую перечисляются колонки, которые вы захотите объединить. Тут могут быть не только колонки, но и какие-то строки, ведь чтобы объединить имя и фамилию между ними еще нужно добавить пробел concat(FirstName, ' ', LastName), здесь в скобках у CONCAT три значения – колонка FirstName, пробел, и LastName.
Полный SQL запрос будет выглядеть так:
SELECT concat(FirstName, ' ', LastName) FROM phone;
А результат будет таким:
+----------------------------------+ | concat(FirstName, ' ', LastName) | +----------------------------------+ | John Doe | | Steve Doe | | Johnatan Something | | Donald Trump | | Alice Cooper | | Michael Jackson | | John Abama | | Andre Jackson | | Mark Oh | | Charly Lownoise | +----------------------------------+
Всего одна колонка и в ней имя и фамилия разделены пробелом.
Возможно, вы пока не ощутите всего кайфа от псевдонимов, но уже в следующей части вы увидите, что это действительно необходимо.
Вы можете создавать псевдонимы для колонок. Когда мы объединили имя и фамилию в одну колонку, то была создана как бы виртуальная колонка, которой не существует в базе данных. Какое имя у этой виртуальной колонки? Его нет и поэтому в заголовке мы видим математическую операцию, которую выполняли:
+----------------------------------+ | concat(FirstName, ' ', LastName) | +----------------------------------+
Далеко не самое лучшее имя, поэтому было бы круто иметь возможность задать своё имя и это возможно. После имени можно указать слово as и потом псевдоним, который вы хотите увидеть в заголовке колонки. Так как мы отображаем полное имя, то этой колонке можно дать псевдоним FullName:
SELECT concat(FirstName, ' ', LastName) as FullName FROM phone;
Две магические буквы as указывать не обязательно, но рекомендуется, просто с эстетической точки зрения красивее.
Обратите внимание, что изменилось имя в заголовке:
+--------------------+ | FullName | +--------------------+ | John Doe | | Steve Doe | | Johnatan Something | | Donald Trump | | Alice Cooper | | Michael Jackson | | John Abama | | Andre Jackson | | Mark Oh | | Charly Lownoise | +--------------------+
Когда мы перечисляем колонки после слова SELECT, мы просто указывали имена, но если быть боле хе аккуратным, то мы должны указывать перед именем колонки имя таблицы, из которой мы хотим увидеть данные. Так что перед именем колонки мы теоретически должны были бы указывать имя таблица phone:
SELECT phone.FirstName, phone.LastName FROM phone;
У нас одна таблица в запросе, поэтому MySQL и любая другая база данных без проблем могут догадаться, что колонки имени и фамилии будут как раз из таблицы phone.
В тех случаях (а мы их рассмотрим скоро), когда мы обязаны указать имя таблицы перед именами колонок мы можем использовать псевдонимы. После имени таблицы в секции FROM можно дать таблице phone псевдоним в виде одной буквы p, и теперь использовать псевдоним и перед именами колонок ставить именно его:
SELECT p.FirstName, p.LastName FROM phone p;
Одна буква – это на много проще, чем писать все имя таблицы.
Всегда ли мы должны использовать именно букву P? Нет, вы можете выбирать любую букву, просто чаще всего берут именно ту, с которой начинается имя таблицы. Для таблицы городов можно было бы выбрать псевдоним – букву С, а можете любую другую.
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.