Выведите всю информацию о пользователе из таблицы Users, кто является владельцем самого дорого жилья (таблица Rooms)
Пытаюсь выполнить такую задачу со скалярным подзапросом, тренажер sql-academy ругается, говорит, что неверно
Отслеживать
задан 17 мар в 21:25
Константин Рожков Константин Рожков
31 1 1 серебряный знак 2 2 бронзовых знака
Скорее всего, у вас владелец может иметь несколько комнат, и соответственно нужно найти суммарную стоимость для каждого владельца.
17 мар в 21:34
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
select * from Users where Users.id = (select owner_id from Rooms order by price desc limit 1) справился с задачей таким образом
Отслеживать
47.8k 17 17 золотых знаков 56 56 серебряных знаков 100 100 бронзовых знаков
ответ дан 17 мар в 22:10
Константин Рожков Константин Рожков
31 1 1 серебряный знак 2 2 бронзовых знака
Вижу, что уже разобрались, но внесу дополнительно еще и понимание. В условиях задачи делается акцент на то, что надо вывести всю информацию именно о пользователе, но когда мы выбираем в SELECT все столбцы и потом делаем JOIN, в выдачу попадает вся информация и из другой таблицы. Следовательно надо исключить ее из финального результата. Решить можно следующим образом:
SELECT u.* FROM Users u JOIN Rooms r ON u.id = r.owner_id WHERE price = (SELECT MAX(price) FROM Rooms) Отслеживать
ответ дан 24 мар в 20:33
11 3 3 бронзовых знака
Час думал, что я делаю не так. Сэкономлю время трудягам, которые в будущем столкнутся с данным вопросом. Постараюсь подробно ответить. Финальный код:
Select Users.* FROM users JOIN Rooms ON Rooms.owner_id = Users.id WHERE price = (SELECT MAX(price) FROM Rooms)
Пояснения: Во-первых, ставим «звёздочку», тем самым мы хотим получить все записи. Для выбора конкретной таблицы ставим «Users.» Точку не забываем) Получается все значения из таблицы Users. Если просто «звёздочку» поставите без указания конкретной таблицы, то он ещё вам выдаст результаты из Rooms, а это по условиям задачи не требуется, поэтому может выдавать сообщение о неверном решении. Во-вторых, делаем подзапрос на ценник для таблицы Rooms. Таким образом получится скалярное значение, удовлетворяющие критерии запроса. В-третьих, связываем обе таблицы через многотабличный запрос JOIN. По умолчанию, будет являться внутренним. Надеюсь, поможет кому-нибудь 🙂
Как вывести данные из MySQL – руководство для не шаманов
От автора: что вы мобильник так трясете? Письмо пришло на почтовый ящик, а вы его прочитать не можете? Понятно! Вы бы еще, чтобы вывести данные из MySQL, с бубном возле ПК побегали. После «изъятия» письма этим и собирались заняться, и даже бубен прихватили? Ну ладно, не буду мешать. А для остальных «не шаманов» расскажу, как «вынуть» данные из MySQL без бубна.
Средства вывода phpMyAdmin
Отложите пока в сторону бубен, глаза ползучего питона и ожерелье из мухоморов. Опробуем для получения информации из БД менее «магические» способы. Начнем с рассмотрение возможностей, которые предоставляет для этого оболочка phpMyAdmin. Запускаем программу, слева в списке выбираем нужную базу. Чтобы вывести данные из таблицы MySQL, в основном верхнем меню переходим в раздел «Обзор». После этого получаем содержимое выбранной таблицы.
В результате нам удалось в три щелчка получить доступ к содержимому нужной базы данных. Но что-то выбранная для экспериментов БД уж слишком приелась. Конечно, все мы любим «зверюшек», но от наших «танцев с бубнами» они все быстро разбегутся. Нелегкое это дело «шаманство» ��
Чтоб не мучатся с созданием новой БД и не тратить понапрасну драгоценное время, скачаем готовую базу с официального ресурса MySQL. А сэкономленные таким образом минуты потратим на обучение «волшебству» администрирования СУБД. Установка скачанной базы происходит в phpMyAdmin через вкладку «Импорт».
Импортированная БД «World» содержит намного больше информации, и поэтому более интересна для обучения (на случай, если мы заскучаем во время освоения материала). Ее «потенциал» можно увидеть, открыв любую из ее таблиц. Например, «City»:
Окунаемся в язык структурированных запросов
Все это интересно, но что делать, если нужно вывести строку MySQL, а не полностью таблицу? Визуально найти среди сотни строк искомую практически невозможно. Для этого нужно воспользоваться средствами SQL.
В языке структурированных запросов для вывода отсортированных данных используется оператор SELECT. Его синтаксис:
2.8. Связанные таблицы
На данный момент мы писали достаточно простые запросы, потому что использовали только одну таблицу. Но мы же создали в прошлой главе 4-е таблицы, и хотелось бы научиться связывать их в одно целое. Я бы не стал усложнять базу данных, создавая справочники, если бы связь не была возможной.
Давайте попробуем связать две таблицы на примере должностей. В таблице Peoples у нас есть поле «idPosition». В этом поле содержится идентификатор (первичный ключ) строки, с которой связана запись со строкой из таблицы «tbPosition». Следующий пример показывает, как можно связать эти таблицы:
SELECT * FROM tbPeoples, tbPosition WHERE tbPeoples.idPosition=tbPosition.idPosition
Первая строка, как всегда говорит, что надо вывести все поля (SELECT *). Вторая строка говорит, из каких таблиц надо получать данные (FROM tbPeoples, tbPosition). На этот раз у нас здесь указано сразу две таблицы – работники и должности. Третья строка показывает связь:
tbPeoples.idPosition=tbPosition.idPosition
Для того, чтобы указать к какой таблице относиться поле «idPosition» (поле с таким именем есть в обеих таблицах, которые мы используем) мы записываем полное имя поля как ИмяБазы.ИмяПоля. Если имя поля уникально для обеих таблиц (как «vcFamil», которое есть только в таблице tbPeolpes), то можно имя таблицы опускать. Именно поэтому мы раньше опускали имя таблицы, когда использовали поля в секции SELECT и WHERE, ведь мы работали только с одной таблицей, и никаких конфликтов не могло быть. Как только мы указали две таблицы в секции FROM, сразу возникает вероятность встретиться с конфликтами имен полей.
Итак, в секции WHERE мы указываем, что поле «idPosition» из таблицы tbPeoples равно полю «idPosition» из таблицы tbPosition.
Связь необходима. Если ее не указать, то результат будет совершенно другим. Посмотрим, что произойдет, если не указывать связь, а просто выбрать данные из двух таблиц:
SELECT * FROM tbPeoples, tbPosition
Результат выполнения этого запроса показан на рисунке 2.4. На рисунке я немного изменил результат, чтобы поле «idPosition» из таблицы tbPeoples находилось рядом с одноименным полем (с которым происходит связь) из таблицы tbPosition. Выделенный фрагмент содержит поля, которые принадлежат таблице должностей.
Теперь посмотрим на строку Иванова. Обратите внимание, что их много. Иванов связался со всеми существующими должностями. Потом идут строки Петрова, который так же связался со всеми возможными должностями. Таким образом, количество строк в результате равно количеству строк из первой таблицы умноженное на количество строк из второй таблицы. Такое объединение называется ортогональным или декартовым.
Теперь посмотрим, что означает связь:
tbPeoples.idPosition=tbPosition.idPosition
Будем смотреть на эту команду не как на связь, а на как простое ограничение WHERE, которое говорит, что в результате поле «idPosition» в обоих, таблицах должны быть равны. Посмотрите на результат работы запроса без связи. Где значения этих полей равны? Для Иванова это та строка, где связь произошла с должностью генерального директора. Для Петрова это связь с коммерческим директором и т.д. Таким образом, все лишние записи отбрасываются, и мы получаем в результате только те строки, которые связаны по правильному ключу.
Почему база данных сама не указывает связь? Просто среди таблиц может быть несколько ключей, в том числе, две таблицы могут связываться разными способами и сервер просто не может знать, какая именно связь нам нужна в данный момент. Например, исходя из практики, фамилия, имя, отчество и дата рождения образуют уникальное сочетание. Вы можете попытаться связать две таблицы по этим данным, не обращая внимания на внешние ключи, чтобы определить для себя какие-то дополнительные сведения.
При написании связи нам пришлось писать полные имена таблиц. Чтобы этого не приходилось делать, в запросе можно создавать псевдонимы. Псевдонимы создаются в секции FROM, ставятся через пробел после имени таблицы, и действуют только внутри этого запроса:
SELECT * FROM tbPeoples pl, tbPosition ps WHERE pl.idPosition=ps.idPosition
В данном запросе у нас используется две таблицы и для каждой из них указывается псевдоним. Для таблицы tbPeoples это псевдоним pl, а для таблицы tbPosition это ps. В качестве псевдонима может выступать любое имя из любого количества букв. Я чаще всего использую первую букву, если она не будет конфликтовать с другими именами. В данном случае имена двух таблиц начинается с буквы p, вот и приходиться использовать две буквы.
В секции WHERE теперь не надо писать полное имя таблицы. Достаточно только указывать псевдоним:
pl.idPosition=ps.idPosition
Удобство от использования псевдонимов очень хорошо заметно, когда вы будете связывать несколько таблиц. Давайте посмотрим, как связывать три таблицы:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples=pn.idPeoples
В секции FROM перечислены уже три таблицы, а в секции WHERE наведены две связи. Таблица tbPeoples связана с таблицей должностей, а вторая связь связывает таблицу tbPeoples c таблицей телефонов.
У нас в таблице работников 19 записей, а в таблице телефонов 18 строк. Проанализируйте результат и вы увидите, что некоторые работники имеют по несколько номеров телефонов. Например, строка Иванова встречается 3 раза, но с разными номерами. Те работники, которые не имеют телефонов, в результат не попали. Почему? Просто нет связи, а значит условие pl.idPeoples=pn.idPeoples не срабатывает.
Использование жесткого объединение с помощью знака равенства называют внутренним объединением. Чтобы увидеть записи, которые не связаны, нужно использовать внешнее объединение, которое бывает левым или правым.
Как же тогда увидеть всех работников, и при этом наладить связь? Для этого используются левые объединения:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Самое интересное кроется как раз в последнем условии:
pl.idPeoples*=pn.idPeoples
Обратите внимание, что слева от знака равно стоит знак умножения или проще – звездочка. Это значит, что из таблицы работников (tbPeoples, которая находиться со стороны звездочки) нужно взять все строки, а если есть связь с таблицей, указанной справа, то отобразить ее.
Выполните этот запрос, и вы увидите всех работников. У тех, у кого нет номера телефона, поля из таблицы tbPhoneNumbers будут содержать нулевые значения NULL.
Использование знака * для не жесткого объединения описано в стандарте SQL, но я говорил, что не все базы данных поддерживают этот стандарт полностью. Например, MS Access позволяет создавать левые объединения, но здесь это делается совершенно по-другому. Мы рассмотрим этот метод в главе 2.8.
Когда мы связываем таблицы жестко с помощью знака равно, такое объединение называют внутренним. Когда мы используем не жесткое объединение со звездочкой, то оно бывает правым или левым. Чтобы вам проще было понять, где какое направление – посмотрите на звездочку. Мы ее поставили слева, значит, объединение было левым. Если бы звездочка была справа от знака равенства, то объединение стало бы правым.
Чтобы получить правое объединение, достаточно поменять поля местами:
SELECT * FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pn.idPeoples=*pl.idPeoples
Вот теперь знак звездочки находиться справа. Как видите, разница в них небольшая, но она значительна при использовании объединения таблиц по методу MS.
Псевдонимы можно использовать в любой секции, даже в секции SELECT:
SELECT pl.vcFamil, pl.vcName, pl.vcSurname, ps.vcPositionName, pn.vcPhoneNumber FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Если быть более точным, то бывают случаи, когда использовать псевдонимы необходимо. Если есть имя поля, которое присутствует одновременно в обеих таблицах, то для его объявления в секции SELECT необходимо явно указать таблицу. Например, попробуйте добавить в список SELECT поле «idPeoples», без указания имени таблицы или псевдонима. В ответ на этот запрос, сервер выдаст ошибку: Ambiguous column name ‘idPeoples’ (двусмысленное имя колонки «idPeoples»). Сервер не знает, значение колонки «idPeoples», из какой таблицы нужно вернуть пользователю. Это вы знаете, что благодаря сравнению pl.idPeoples*=pn.idPeoples в результате все равно обе колонки будут содержать одно и то же значение, но сервер на это не надеется и даже не пытается понять, а просто выдает ошибку о двусмысленности.
При использовании связанных таблиц очень часто бывает необходимость выбрать одну таблицу полностью, а остальные могут выбираться частично. Например, давайте выберем из таблицы «tbPeoples» только ФИО, а из таблиц должностей и телефонов все поля:
SELECT vcFamil, vcName, vcSurname, ps.*, pn.* FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples*=pn.idPeoples
Обратите внимание, что поля, которые нам нужны из таблицы tbPeoples — перечисляются, а чтобы не перечислять все поля остальных таблиц, мы просто пишем ps.* или pn.*. То есть знак звездочки, означающий вывод всех полей относится не ко всем таблицам, а только к перечисленным.
В главе 1.2.6 мы рассматривали пример создания таблицы, в которой внешний ключ был связан с первичным ключом той же самой таблицы. В нашей тестовой базе данных такой таблицей является tbPosition, где хранятся должности работников. В этой таблице поле «idParentPosition» связано с первичным ключом «idPosition» этой же таблицы и предназначено для указания названия должности, которая является главной. Таким образом, можно построить дерево главный-подчиненный.
Давайте попробуем вывести табличку из двух полей, где первое поле будет отображать название должности, а второе – главную должность. Вот как это будет выглядеть в виде SQL запроса:
SELECT p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Главная должность' FROM tbPosition p1, tbPosition p2 WHERE p1.idParentPosition*=p2.idPosition
Прежде чем мы разберем этот запрос, давайте посмотрим на результат его работы:
ДОЛЖНОСТЬ ГЛАВНАЯ ДОЛЖНОСТЬ Генеральный директор NULL Коммерческий директор Генеральный директор Директор по общим вопросам Генеральный директор Начальник отдела снабжения Коммерческий директор Начальник отдела сбыта Коммерческий директор Начальник отдела кадров Директор по общим вопросам ОТиЗ Директор по общим вопросам Бухгалтерия Коммерческий директор Менеджер по снабжению Начальник отдела снабжения Менеджер по продажам Начальник отдела сбыта
Первая строка соответствует должности генерального директора. Это самый главный человек в компании, поэтому для нее во второй колонке указан NULL, т.е. главной должности нет.
Следующая строка в первой колонке содержит должность коммерческого директора. Вполне логично, что она подчиняется генеральному директору, что и отображено во второй колонке результата.
Теперь посмотрим на SQL запрос, с помощью которого мы получили эти данные. Для начала посмотрим на секцию FROM, где дважды указана одна и та же таблица «tbPosition», но с разными псевдонимами p1 и p2. В секции WHERE мы наводим связь между псевдонимами одной и той же таблицы:
p1.idParentPosition*=p2.idPosition
Таким образом, через псевдонимы мы указали связь внутри одной и той же таблицы. В секции SELECT мы отображаем имя должности из первого псевдонима и имя из второго.
Теперь посмотрите на следующий запрос:
SELECT p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Главная должность', p3.vcPositionName AS 'Главная для главной' FROM tbPosition p1, tbPosition p2, tbPosition p3 WHERE p1.idParentPosition=p2.idPosition AND p2.idParentPosition*=p3.idPosition
Здесь мы дважды использовали связь таблицы саму на себя. Результат работы этого запроса:
Должность Главная должность Главная для главной Коммерческий директор Генеральный директор NULL Директор по общим вопросам Генеральный директор NULL Начальник отдела снабжения Коммерческий директор ГенеральныйДиректор Начальник отдела сбыта Коммерческий директор ГенеральныйДиректор
Давайте теперь напишем запрос, который отобразит все записи из всех связанных таблиц нашей тестовой базы данных. А таблиц у нас всего 4, но в нашей секции FROM будет пять таблиц, потому что дважды будет ссылка на таблицу должностей, чтобы отобразить должность текущего работника и должность начальника:
SELECT pl.vcFamil, pl.vcName, pl.vcSurname, dDateBirthDay, p1.vcPositionName AS 'Должность', p2.vcPositionName AS 'Начальник', pn.vcPhoneNumber, pt.vcTypeName FROM tbPeoples pl, tbPosition p1, tbPosition p2, tbPhoneNumbers pn, tbPhoneType pt WHERE pl.idPosition=p1.idPosition AND p1.idParentPosition*=p2.idPosition AND pn.idPeoples=pl.idPeoples AND pt.idPhoneType=pn.idPhoneType
Попробуйте разобраться в этом запросе. Если вы поймете его, то можно считать, что тема связанных таблиц усвоена удачно.
Саязанные таблицы в стиле MS
Объединение по стандарту SQL, который мы рассматривали в главе 2.7, описывает условие связи в секции WHERE. В MS зачем-то связи перенесли в секцию FROM. На мой взгляд, это как минимум не удобно для создания и для чтения связей. Стандартный вариант намного проще и удобнее. И все же, метод MS мы рассмотрим, ведь только с его помощью в MS Access можно создать левое или правое объединение, и этот же метод поддерживается в MS SQL Server.
Ортогональное объединение по методу MS, т.е. без указания связи:
SELECT * FROM tbPeoples CROSS JOIN tbPosition
Внутреннее объединение (эквивалентно знаку равенства) по методу MS описывается следующим образом:
SELECT * FROM tbPeoples pl INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Как видите, для этого метода не нужна секция WHERE, но зато намного больше всего нужно писать. Вначале мы описываем, что нам нужно внутреннее объединение (INNER JOIN). Слева и справа от этого оператора указываются таблицы, которые нужно связать. После этого ставиться ключевое слово ON, и только теперь наводим связь между полями связанных таблиц. Таким образом, этот запрос эквивалентен следующему:
SELECT * FROM tbPeoples pl, tbPosition ps WHERE pl.idPosition=ps.idPosition
Самая большая путаница начинается, когда нужно объединить три таблицы в одно целое. Посмотрите на следующий запрос:
SELECT * FROM tbPeoples pl LEFT OUTER JOIN tbPhoneNumbers pn ON pl.idPeoples=pn.idPeoples INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Сначала объединяются таблицы tbPeoples и tbPhoneNumbers через внешнее левое объединение (LEFT OUTER JOIN). Затем указывается связь между этими таблицами. А вот теперь результат объединение, связываем внутренним объединением (INNER JOIN) с таблицей tbPosition. Внимательно осмотрите запрос, чтобы понять его формат, и что в нем происходит.
Чтобы получить правое объединение, необходимо просто поменять перечисление таблиц местами:
SELECT * FROM tbPhoneNumbers pn RIGHT OUTER JOIN tbPeoples pl ON pl.idPeoples=pn.idPeoples INNER JOIN tbPosition ps ON pl.idPosition=ps.idPosition
Меняется местами перечисление таблиц, а вот порядок указания связанных полей не имеет особого значения и здесь ничего не меняется.
Если честно, то мне не очень нравиться объединение по методу Microsoft. Какое-то оно неудобное и громоздкое. Даже не знаю, зачем его придумали, когда в стандарте есть все то же самое, только намного проще и нагляднее.
MySQL для пользователя
Во всемирной Сети можно найти много информации о том, как настроить MySQL-сервер, но практически нет информации о том, как его использовать обыкновенному пользователю. Возможно, это из-за того, что приложения, которые базируются на MySQL, пишутся квалифицированными программистами, а самому пользователю и не нужно знать, что и как работает. Но знать это все же не помешает, например, вы хотите знать, как выполняется тот или иной SQL-запрос.
Удобной программой для просмотра структуры базы данных является mysqlshow. Введите следующую команду:
mysqlshow -p mysql
Вы увидите список таблиц, которые находятся в базе данных mysql.
Database: mysql +--------+ | Tables | +--------+ | db | | host | | user | +--------+
Программа mysqlshow может вызываться с дополнительными параметрами, указанными в таблице 1.
Таблица 1. Параметры программы mysqlshow
| Параметр | Описание |
| —host=hostname | Задает имя хоста, к которому вы хотите подключиться |
| —port=port_number | Определяет номер порта для сервера MySQL |
| —socket=socket | Указывает сокет |
| —user=username | С помощью этого параметра можно указать нужное имя пользователя |
| -p | Запрашивает ввода пароля |
Для самих же операций с данными используется программа mysql. Она и является клиентом сервера. В этой программе можно использовать те же опции, что и mysqlshow. Среди многочисленных параметров программа mysql имеет один очень важный параметр «-s». Я рекомендую вам всегда его использовать. Этот параметр подавляет большинство ненужных сообщений, выводимых клиентом. На медленных линиях связи это должно повысить производительность. Да и наблюдать за всеми рамочками и ненужными сообщениями особо не хочется.
Создать базу данных можно с помощью программы mysqladmin. Естественно, что использовать эту прогамму может только администратор, например,
mysqladmin -u admin -p create my_db
Как работает сервер SQL? Клиент посылает запрос, в котором он указывает, какую информацию он хочет получить от сервера, или какую операцию с данными он хочется выполнить, сервер посылает клиенту ответ, в котором указывается, выполнил ли сервер запрос клиента, и, если сервер выполнил запрос, результат запроса.
- Cоздавать базы данных и таблицы
- Добавлять информацию в таблицы
- Удалять информацию
- Модифицировать информацию
- Получать нужные вам данные
Естественно, пользователь admin, кроме того, что должен существовать, должен обладать соответствующими правами. Каждый запрос MySQL должен заканчиваться точкой с запятой. Если вы введете SELECT * FROM test клиент mysql будет ждать ввода точки с запятой:
Давайте договоримся, что будем писать запросы согласно стандарту SQL, то есть для улучшения восприятия будем разбивать их на части. Программа mysql допускает ввод запроса во всю строчку. Например, запрос, записанный в стандарте SQL,
SELECT * FROM S WHERE Q > 10
в программе mysql можно записать так:
SELECT * FROM S WHERE Q > 10
Теперь создадим три таблицы — Товар, Клиенты и Заказы.
CREATE TABLE CLIENTS ( C_NO int NOT NULL, FIO char(40) NOT NULL, ADDRESS char(30) NOT NULL, CITY char(15) NOT NULL, PHONE char(11) NOT NULL );
Таблица CLIENTS содержит поля C_NO (номер клиента), FIO (Фамилия, Имя, Отчество), Адрес, Город и Телефон. Все эти поля не могут содержать пустого значения (NOT NULL).
CREATE TABLE TOVAR ( T_NO int NOT NULL, DSEC char(40) NOT NULL, PRICE numeric(9,2) NOT NULL, QTY numeric(9,2) NOT NULL );
Эта таблица будет содержать данные о товарах. Тип numeric(9,2) означает, что 9 знаков относим под целую часть, и два — под дробную. QTY — это количество товара на складе.
CREATE TABLE ORDERS ( O_NO int NOT NULL, DATE date NOT NULL, C_NO int NOT NULL, T_NO int NOT NULL, QUANTITY numeric(9,2) NOT NULL, AMOUNT numeric(9,2) NOT NULL );
Данная таблица содержит сведения о заказах — номер заказа (O_No), дату заказа (DATE), номер клиента (C_NO), номер товара (T_NO), количество (QUANTITY) и сумму всего заказа AMOUNT (то есть AMOUNT = T_NO * TOVAR.PRICE)
Теперь добавим данные в наши таблицы. Добавить данные можно с помощью оператора INSERT. Рассмотрим использование оператора INSERT:
INSERT INTO CLIENTS VALUES (1,'Иванов И.П.', 'Ленина 6', 'Кировоград','80522111111');
Добавляемые значения должны соответствовать тому порядку, в котором поля перечислены в операторе CREATE. Если вы хотите добавлять информацию в другом порядке, то вы должны указать этот порядок в операторе INSERT:
INSERT INTO CLIENTS (FIO,ADDRESS,C_NO,PHONE,CITY) VALUES ('Петров', 'Пушкина 9',2,'-','Кировоград');
С помощью INSERT мы можем добавлять данные в определенные поля, например, C_NO и FIO:
INSERT INTO CLIENTS (C_NO, FIO) VALUES (1,'Петров');
Но сервер не выполнит наш запрос, поскольку все остальные поля равны NULL (пустое значение), а наша таблица не принимает пустые значения. Аналогично можно добавить данные в другие таблицы. Добавим данные в таблицу TOVAR:
INSERT INTO TOVAR VALUES (1,'Монитор LG',550.74);
Обратите внимание, что мы пока еще не указали первичные ключи таблицы, поэтому нам никто не мешает добавить в таблицу одинаковые записи. Добавить дату в поле DATE можно с помощью функции TO_DATE:
INSERT INTO ORDERS VALUES (1,TO_DATE('01/01/02,'DD/MM/YY'),1,1,1,550.74);
Данная запись означает, что первого января 2002 года Иванов И.П. (C_NO=1) заказал один (QUANTITY=1) Монитор LG (T_NO=1).
Предположим, что нам нужно обновить запись, например, клиент Иванов переехал в другой город. Это делается так:
UPDATE CLIENTS SET CITY = 'Киев' WHERE C_NO = 1;
Теперь удалим всех клиентов, номера которых превышают 10:
DELETE FROM CLIENTS WHERE C_NO > 10;
С помощью команды DELETE можно удалить все записи таблицы, указав условие, которое подойдет для всех записей, например
DELETE FROM CLIETNS;
Если вторая часть оператора DELETE — WHERE — не указана, значит, действие оператора распространяется на все записи сразу.
Добавление, изменение и удаление записей — это, безусловно, очень важные команды, но чаще всего вы будете использовать оператор SELECT, который выбирает данные из таблицы. Например, для вывода всех записей из таблицы CLIENS, введите:
SELECT * FROM CLIENTS;
В результате вы получите такой ответ от сервера:
C_NO FIO ADDRESS CITY PHONE 1 Иванов И.П. Ленина 6 Кировоград 80522111111 1 Иванов И.П. Ленина 6 Кировоград 80522111111 2 Петров В.К. Пушкина 9 Кировоград 80522112111
Обратите внимание на первые две записи — они одинаковые. Теоретически, добавление одинаковых записей возможно — мы ведь не указали первичный ключ таблицы. Если вы хотите исключить одинаковые записи из ответа сервера (но не из таблицы!), введите запрос:
SELECT DISTINCT * FROM CLIENTS;
Предположим, что вы хотите вывести только фамилию и номер телефона клиента, тогда введите такой запрос:
SELECT DISTINCT FIO, PHONE FROM CLIENTS;
Теперь займемся усложнением наших запросов. Выведем все товары, цена которых превышает 500 рублей.
SELECT * FROM TOVAR WHERE PRICE > 500;
Вы можете использовать другие знаки отношений: ,=,<>,=.
Если ваша компания обслуживает несколько однофамильцев, и вы хотите вывести информацию обо всех Ивановых, используйте шаблон LIKE:
SELECT * FROM CLIENTS WHERE FIO LIKE '%Иванов%';
Запрос читается так: вывести всю информацию о клиентах, фамилия которых похожа на ‘Иванов’. Если вы хотите выбрать данные из разных таблиц, перед именем поля нужно указывать имя таблицы. Следующий запрос выведет имена всех клиентов, которые хотя бы раз покупали у нас товар:
SELECT DISTINCT CLIENTS.FIO FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ODREDS.C_NO;
Оператор SELECT позволяет использовать вложенные запросы. Следующий оператор аналогичен предыдущему:
SELECT DISTINCT CLIENTS.FIO FROM CLIENTS WHERE CLIENTS.C_NO IN (SELECT C_NO FROM ORDERS);
При работе с оператором SELECT вам доступно несколько полезных функций, вычисляющих количество элементов (COUNT), сумму элементов (SUM), максимальное и минимальное значение (MAX и MIN), а также среднее значение (AVG).
Следующие операторы выведут, соответственно, количество записей в таблице CLIENTS, самый дорогой товар и сумму всех товаров на складе.
SELECT COUNT(*) FROM CLIENTS; SELECT MAX(PRICE) FROM TOVAR; SELECT SUM(PRICE) FROM TOVAR;
Оператор SELECT позволяет группировать возвращаемые значения. Например, клиент Иванов (C_NO=1) несколько раз заказывал у нас какой-то товар. Значит, его номер встречается в таблице ORDERS несколько раз.
Выведем имена всех клиентов, а также сумму заказа каждого клиента.
SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ORDERS.C_NO GROUP BY ORDERS.C_NO;
Группировку выполняет оператор GROUP BY, который является частью оператора SELECT. Оператор GROUP BY можно ограничить с помощью HAVING. Этот оператор используется для отбора строк, возвращаемых GROUP BY. HAVING можно считать аналогом WHERE, но только для GROUP BY:
HAVING
Например, нас интересуют только клиенты, которые заказали товаров на общую сумму, превышающую 1000.
SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM FROM CLIENTS, ORDERS WHERE CLIENTS.C_NO = ORDERS.C_NO GROUP BY ORDERS.C_NO HAVING TOTALSUM > 1000;
В этом запросе мы использовали псевдоним столбца TOTALSUM. В некоторых сервера SQL для определения псевдонима не нужно писать служебное слово AS, а некоторые требуют применение знака равенства: SUM(ORDERS.AMOUNT) TOTALSUM или TOTALSUM = SUM(ORDERS.AMOUNT).
Пока мы не установили первичный ключ, сортировка нашей таблицы не выполняется. Данные будут отображены в порядке занесения их в таблицу. Для сортировки по полю C_NO результата вывода таблицы CLIENTS используется следующий оператор (сама таблица при этом не сортируется):
SELECT * FROM CLIENTS ORDER BY C_NO;
Предположим, что кто-то добавил в таблицу CLIENTS запись
1 Сидоров Егорова 11 Кировоград 80522345111
У на получилось, что один и тот же номер сопоставлен разным клиентам. Тогда кто из них заказал монитор LG? Чтобы избежать подобной путаницы, нужно использовать первичные ключи:
ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_NO);
После этого запроса поле C_NO может содержать только уникальные значения. В качестве первичного ключа нельзя использовать поле, допускающее значение NULL. Создать первичный ключ можно проще — при создании таблицы. Это делается так:
CREATE TABLE CLIENTS ( C_NO int NOT NULL, FIO char(40) NOT NULL, ADDRESS char(30) NOT NULL, CITY char(15) NOT NULL, PHONE char(11) NOT NULL, PRIMARY KEY (C_NO); );
Таблица ORDERS содержит сведения о заказах. По полю C_NO этой таблицы идентифицируется заказчик. Предположим, что в таблицу ORDERS кто-то ввел значение, которого нет в таблице CLIENTS. Кто же заказал товар? Нам нужно не допустить подобной ситуации, поэтому введите следующий запрос:
ALTER TABLE ORDERS ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS;
Введенные в таблицу ORDERS номера клиентов C_NO должны существовать в таблице CLIENTS. Аналогично нужно добавить внешний ключ по полю T_NO. Эта возможность называется декларативной целостностью.
Команда ALTER используется не только для добавления ключей. Она предназначена для реорганизации таблицы в целом. Вы хотите добавить еще одно поле? Или установить список допустимых значений для каждого из полей. Все это можно сделать с помощью команды ALTER:
ALTER TABLE CLIENTS ADD ZIP char(6) NULL;
Этот оператор добавляет в таблицу CLIENTS новое поле ZIP типа char. Обратите внимание, что вы не можете добавить новое поле со значением NOT NULL в таблицу, в которой уже есть данные. Наша компания работает с клиентами только из Киева и Кировограда, поэтому целесообразно ввести список допустимых значений для таблицы CLIENTS:
ALTER TABLE CLIENTS ADD CONSTRAINT INVALID_STATE SHECK (CITY IN ('Кировоград','Киев'));
Вам уже надоело работать с этой базой данных? Тогда с помощью запроса DISCONNECT отключитесь от нее, и, используя запрос CONNECT, подключитесь к другой базе данных. В некоторых серверах SQL запрос DISCONNECT не работает, а вместо CONNECT нужно использовать оператор USE.
Теперь, когда вы уже знакомы с основами SQL, немного углубимся. Мы уже знаем, как добавлять первичный ключ, теперь добавим внешний ключ при создании таблицы:
CREATE TABLE T ( /* Описания полей таблицы */ FOREIGN KEY KEY_NAME (LIST) REFERENCES ANOTHER_TABLE [(LIST2)] [ON DELETE OPTION] [ON UPDATE OPTION] );
Здесь KEY_NAME — это имя ключа. Имя не является обязательным, но я очень рекомендую всегда указывать имя ключа — если вы не укажете имя ключа, вы потом не сможете его удалить. А мало ли что может случиться, возможно, он вам больше будет не нужен? LIST — это список полей, входящих во внешний ключ. Список разделяется запятыми. ANOTHER_TABLE — это другая таблица, по которой устанавливается внешний ключ, а необязательный элемент LIST2 — это список полей этой таблицы. Типы полей в списке LIST должны совпадать с типами полей в списке LIST2. Предположим, что в первой таблице у нас есть два поля — NO и NAME — целого и символьного типов соответственно. Во второй таблице у нас есть поля с одинаковыми именами и типами. Определение внешнего ключа
FOREIGN KEY KEY_NAME (NO, NAME) REFERENCES ANOTHER_TABLE (NAME, NO)
Некорректно, потому что типы полей NO и NAME не совпадают. Нужно использовать такое определение:
FOREIGN KEY KEY_NAME (NO, NAME) REFERENCES ANOTHER_TABLE (NO, NAME)
Если же поля имеют одинаковые имена, как в нашем случае, список LIST2 лучше вообще не указывать, чтобы не создавать себе лишних проблем.
Необязательные параметры ON DELETE и ON UPDATE определяют действие по обновлению информации в базе данных, при удалении информации из таблицы и при ее обновлении. Помните наш пример с клиентами? Я о том, что в таблице заказов есть поле C_NO (Client NO), значения которого должны быть в таблице клиентов. И в самом деле, как мы узнаем имя и прочие данные клиента с номером 99999, которого нет в таблице клиентов? Установив внешний ключ, мы связываем две таблицы по полю C_NO. Можно спокойно спать (я хотел сказать администрировать базу данных), до одного прекрасного момента, когда девушка-оператор удалит какого-нибудь клиента из таблицы клиентов. Что делать с записями в таблице заказов? С помощью параметра ON DELETE мы можем указать серверу реакцию на удаление таких данных:
ON DELETE OPTION
Параметр OPTION может принимать одно их четырех значений: CASCADE, NO ACTION, SET DEFAULT, SET NULL.
Параметр CASCADE означает, что номер удаляемого клиента будет удален из всех связанных таблиц. Например, если вы удалите клиента с номером 10 из таблицы клиентов, то из таблицы заказов будут удалены все заказы этого клиента.
Параметр NO ACTION не разрешает удаление клиента до тех пор, пока но есть в связанной таблице. Это означает, что девушка-оператор должны сперва удалить всю информацию о заказах из таблицы заказов
С помощью параметра SET_DEFAULT вы можете указать значение по умолчанию. Например, если вы укажите SET DEFAULT 1, то при удалении клиента с любым номеров его заказы будут приписываться клиенту с номером 1, который, разумеется, всегда есть в таблице CLIENTS.
Параметр SET NULL устанавливает значение NULL в качестве номера клиента, если тот удален из таблицы CLIENTS. Помните, что в нашем случае поле C_NO не допускает значения NULL! А как удалить поле? Стандартом SQL не предусмотрено удаление столбцов, но в MySQL мы все же можем это сделать:
ALTER TABLE CLIENTS DROP ZIP;
Удалить таблицу еще проще:
DROP ORDERS;
Во второй части этой статьи мы рассмотрим функции языка PHP для работы с сервером MySQL. Ваши вопросы и комментарии буду рад выслушать по адресу dhsilabs@mail.ru.
linux samba mail postfix FreeBSD Unix doc linux howto ALTLinux PHP faq bind sendmail apache iptables firewall kernel rpm apt-get Slackware openssh Cisco debian vmware GNU oracle sun awk /etc/ passwd linux установка учебник книга скачать