SQL-операторы
Работать с данными помогают операторы — определенные слова или символы, которые используются для выполнения конкретной операции — например, для выбора из множества по конкретному параметру. Если нам нужно из всех видов пиццы отсортировать те, в которых есть пармезан, — нужно использовать оператор SELECT (выбор в соответствии с условием).
Операторы в SQL делятся на несколько групп в соответствии с задачами, которые они решают.
DDL (Data Definition Language) — операторы определения данных. Они работают с объектами, то есть с целыми таблицами. Если базу нужно дополнить таблицей с новыми данными или, наоборот, убрать одну из таблиц с ошибочными данными — используется этот набор операторов.
- CREATE — создание объекта в базе данных
- ALTER — изменение объекта
- DROP — удаление объекта
DML (Data Manipulation Language) — операторы манипуляции данными. Эти операторы уже работают с содержимым таблиц — строками, атрибутами и значениями. С их помощью можно вносить изменения в конкретное значение. Например, заменить поле в колонке «Фамилия» в строке с данными сотрудницы компании посте того, как она вышла замуж. Или удалить строку с данными уволенного сотрудника.
- SELECT — выбор данных в соответствии с условием
- INSERT — добавление новых данных
- UPDATE — изменение существующих данных
- DELETE — удаление данных
DCL (Data Control Language) — оператор определения доступа к данным. Он определяет, кто из пользователей может отправлять запросы к базе, менять объекты и значения. Например, можно отозвать доступ у сотрудника, перешедшего в другой отдел, а также открыть доступ к базе новому маркетологу или разработчику.
- GRANT — предоставление доступа к объекту
- REVOKE — отзыв ранее выданного разрешения
- DENY — запрет, который является приоритетным над разрешением
TCL (Transaction Control Language) — язык управления транзакциями. Транзакции — это набор команд, которые выполняются поочередно. Если все команды выполнены, транзакция считается успешной, а если где-то произошла ошибка — транзакция откатывается назад, отменяя все выполненные команды. Наглядный пример такой транзакции — оплата онлайн, когда банк просит сначала ввести сумму и получателя, затем проверить и подтвердить операцию, а после ввести одноразовый код. На каждом из этих этапов оплату можно отменить и транзакция откатится назад.
- BEGIN TRANSACTION — обозначение начала транзакции
- COMMIT TRANSACTION — изменение команд внутри транзакции
- ROLLBACK TRANSACTION — откат транзакции
- SAVE TRANSACTION — указание промежуточной точки сохранения внутри транзакции
Задачка про лайки
С полученными знаниями ты легко сможешь решить эту задачу: есть пользователи (id, имя) и они могут ставить друг другу лайки. Сделай таблицы для хранения всей этой информации и напиши запрос, который выведет такую таблицу:
- ид пользователя
- имя
- лайков получено
- лайков поставлено
- взаимных лайков
Далее, выведи список всех пользователей, которые лайкнули пользователей A и B, но при этом не лайкнули пользователя C. Тут есть несколько вариантов решения.
Сложно? Ну ок, давай начнем с более простой задачи: просто выведи 5 самых популярных пользователей.
- Если ты используешь несколько связанных друг с другом таблиц, связи необходимо пометить с помощью внешних ключей
- Желательно на уровне БД запретить возможность ставить пользователю лайк другому пользователю дважды
- Подсказка: эта задача решается без подзапросов
- Подсказка: достаточно использовать всего 2 джойна и группировку
Усложненная (но более жизненная) задача про лайки
В воображаемой социальной сети есть Пользователи (id, имя), Фото (id, название, автор) и Комментарии К Фото (id, текст, автор, к какому Фото относится). Необходимо добавить возможность для Пользователей ставить лайки другим Пользователям, Фото или Комментариям К Фото. Нужно реализовать такие возможности:
- пользователь не может поставить 2 лайка одной и той же сущности (например одному и тому же Фото)
- пользователь может отозвать лайк
- необходимо иметь возможность посчитать число полученных сущностью лайков и вывести список Пользователей, поставивших лайки
- в будущем могут появиться новые виды сущностей которые можно лайкать
Для начала, нужно решить задачу без оглядки на производительность. Очень желательно следовать принципам нормализации и помечать связи внешними ключами (а также на уровне Бд предотвратить возможность повторной отправки лайка). Далее, можно дополнить решение комментариями по поводу оптимизаций производительности.
Тут есть несколько вариантов решения.
Что такое MySQL. Установка сервера
Последнее обновление: 03.06.2021
MySQL представляет систему управления реляционными базами данных (СУБД). На сегодняшний день это одна из самых популярных систем управления базами данных.
Изначальным разработчиком данной СУБД была шведская компания MySQL AB. В 1995 году она выпустила первый релиз MySQL.
В 2008 году компания MySQL AB была куплена компанией Sun Microsystems, а в 2010 году уже компания Oracle поглотила Sun и
тем самым приобрела права на торговую марку MySQL. Поэтому MySQL на сегодняшний день развивается под эгидой Oracle.
Текущей актуальной версией СУДБ является версия 8.0, которая вышла в январе 2018 года, но для которой постоянно выходят подверсии.
MySQL обладает кроссплатформенностью, имеются дистрибутивы под самые различные ОС, в том числе наиболее популярные версии Linux, Windows, MacOS.
Установка MySQL
После выбора версии нажмем на кнопку «Go to Download Page», и нас перенаправит на страницу загрузки дистрибутива. Здесь можно выбрать либо онлайн-загрузчик, либо полный пакет инсталятора. Можно выбрать любой:
Дальше может быть предложено залогиниться с помощью учетной записи Oracle. Можно пропустить всю эту байду и без какого-либо логина нажать на ссылку
«No thanks, just start my download.», и начнется загрузка:
Сначала будет предложено выбрать тип установки. Выберем тип Developer Default, которого вполне хватит для базовых нужд, и нажмем на кнопку Next:
Затем на этапе установки инсталлятор отобразит весь список устанавливаемых компонентов. У меня он выглядит так:
Чтобы выполнить установку всех компонентов, нажмем кнопку Execute.
После того, как все компоненты будут установлены, нажмем кнопку Next.
Далее отобразится окно с перечнем продуктов, готовых к конфигурации
Нажмем на кнопку Next и далее будет предложено установить ряд конфигурационных настроек сервера MySQL. В частности, здесь мы видим, что для
подключения будет применяться протокол TCP/IP и порт 3306. Оставим все эти настройки соединения и порта по умолчанию:
На следующем шаге будет предложено установить метод аутентификации. Оставим настройки по умолчанию:
Затем на следующем окне прогаммы установки укажем какой-нибудь пароль, и запомним его, так как он потом потребуется при подключении к серверу
MySQL:
И на следующем экране необходимо применить все ранее установленные конфигурационные настройки, нажав на кнопку Execute:
После применения конфигурационных настроек сервер MySQL будет полностью установлен и сконфигурирован, нажмем на кнопку «Finish».
Далее опять отобразится окно с перечнем продуктов, готовых к конфигурации. Нажмем на кнопку «Next»
И нам будет предложено установить конфигурацию для второго продукта — MySQL Router:
Ничего не будем менять, оставив все настройки по умолчанию, и нажмем на кнопку «Finish».
Далее опять отобразится окно с перечнем продуктов, готовых к конфигурации. Нажмем на кнопку «Next»
И далее нам будет предложено установить конфигурацию для третьего родукта — Samples and Examples (Примеры работы с MySQL).
В частности, надо будет указать экземпляр сервера MySQL для получения примеров для работы с MySql. Установленный экземпляр будет автоматически отмечен в списке.
Кроме того, предлагает протестировать подключения. В поле Password введем ранее указанный пароль и нажмем на кнопку Check:
Пи успешном подлючении к MySQL отобразится выделенная зеленым цветом надпись Connection succeeded. Нажмем на кнопку Next.
И на последнем окне необходимо будет применить конфигурацию для
Далее мы опять увидим окно с перечнем установленных и сконфигурированных продуктов. И нажмем на кнопку Next.
На последнем экране мы увидим два отмеченных поля: Start MySQL Workbench after setup и Start MySQL Shell after setup.
Эти поля позволяют запустить графический и консольный клиенты для управления сервером MySQL. Снимем отметки с этих полей, поскольку пока мы не собираемся запускать соответствующие программы.
И нажмем на кнопку Finish. Все! MySQL полностью установлен, сконфигурирован и запущен. И мы сможем с ним работать.
НазадВперед
Установка MySQL сервера на Windows 10
Для админа от админа
3 минуты чтения
Всем привет! Сегодня мы покажем процесс установки MySQL сервера версии 8.0 на Windows 10 с помощью автоматического установщика. До этого мы уже рассказывали как установить MySQL сервер на CentOS 7, теперь мы решили коснуться несколько более известной всем платформы.
Установка
Далее выбираем тип установки, коих есть несколько — установка готового «набора разработчика», установка только сервера, только клиента, полная установка (первая опция + дополнительные инструменты) и кастомная. В нашем случае мы выбираем установку сервера.
Далее кликаем Execute и ждем завершения установки.
Затем нажимаем Next.
Переходим на этап настройки — нажимаем Next.
Так как мы показываем самую простую установку, выбираем первую опцию, также как на скриншоте — отдельный MySQL сервер и кликаем Next.
Настраиваем сетевые параметры — для демонстрационных целей мы все оставили по умолчанию.
Затем настраиваем параметры аутентификации — выбираем первую опцию и нажимаем Next.
Устанавливаем рутовый пароль для сервера — чем сложнее, тем лучше. Мы рекомендуем использовать по меньшей мере пароль из 12 символов, содержащий буквы, цифры и специальные символы. Также на этом этапе можно добавить пользователей — мы, к примеру, добавили пользователя asterisk.
Далее настраиваем свойства службы MySQL — указываем имя службы, параметры автозапуска и из под какой учетной записи необходимо запускать данную службу.
Далее настраиваем плагины и расширения — мы на данном этапе оставили все по умолчанию, т.к демонстрируем базовую установку сервера.
Далее необходимо применить настройки — кликаем Execute и ждем.
Завершение установки и проверка работоспособности
Готово! Теперь осталось нажать Finish два раза — поздравляем! Вы установили MySQL сервер.
Теперь давайте проверим его работоспособность. Для этого необходимо открыть приложение, которое было установлено вместе с сервером — MySQL 8.0 Command Line Client. Необходимо будет ввести рутовый пароль, который был указан вами во время установки и, затем, выполнить команду show databases;
Результатом вы должны увидеть несколько созданных по умолчанию баз данных — mysql, performance_schema, information_schema и sys. Для выхода введите команду exit .
Пожалуйста, расскажите почему?
Нам жаль, что статья не была полезна для вас Пожалуйста, если не затруднит, укажите по какой причине? Мы будем очень благодарны за подробный ответ. Спасибо, что помогаете нам стать лучше!
Подпишитесь на нашу еженедельную рассылку, и мы будем присылать самые интересные публикации Просто оставьте свои данные в форме ниже.
СУБД Mysql
Работать с Mysql можно не только в текстовом режиме, но и в графическом. Существует очень популярный визуальный интерфейс (кстати, написанный на PHP) для работы с этой СУБД. Называется он PhpMyAdmin. Этот интерфейс позволяет значительно упростить работу с базами данных в Mysql.
PhpMyAdmin позволяет пользоваться всеми достоинствами браузера, включая прокрутку изображения, если оно не умещается на экран. Многие из базовых SQLфункций работы с данными в PhpMyAdmin сведены к интуитивно понятным интерфейсам и действиям, напоминающим переход по ссылкам в Internet. Но, тем не менее, стоит все же поработать и в текстовом режиме.
Перед тем как переходить к детальному изучению языка SQL, несколько слов об установке Mysql и подготовке к работе. Если вы не собираетесь заниматься администрированием сервера, то информация, приведенная ниже, пригодится вам только для общего развития. Итак, устанавливается Mysql очень просто – автоматически, пару раз нажмите OK, и все. После этого вы можете зайти в директорию, где лежат файлы типа Mysql.exe, Mysqld.exe и т.п. (у нас под Windows XP это c:\Mysql\bin) Последний файл запускает Mysql-сервер. В некоторых системах сервер запускается в виде сервиса. После запуска сервера следует запустить Mysql-клиент, запустив программу Mysql.exe. Здесь даже пароля не спросят. Более того, если вы наберете shell> Mysql.exe -u root или shell>Mysql -u root Mysql то получите все права администратора Mysql сервера. Кстати, выполнять эти команды надо, находясь в той директории, где лежат файлы Mysql.exe.
Для начала, не вдаваясь в подробности команд, исправим эти два недочета (отсутствие пароля у администратора и возможность входа анонимным пользователям):
Все данные о пользователях Mysql хранит в таблице user в специальной базе данных Mysql, доступ к которой имеет только администратор сервера. Поэтому, чтобы изменить какой-либо пароль, нужно изменить эту таблицу. Пароль задается с помощью функции PASSWORD, которая кодирует введенные данные. Кроме изменения пароля администратора, нужно еще удалить всех пользователей, не имеющих логина (команда DELETE). Команда Flush Privileges заставляет вступить в действие изменения, произошедшие в системной базе данных (Mysql).
Теперь создадим базу данных, с которой будем работать (мы все еще работаем как администратор сервера):
Mysql>create database book;
Как можно заметить, все команды в Mysql заканчиваются точкой с запятой. Если вы забыли поставить этот знак, то выдается приглашение его поставить до тех пор, пока это не будет сделано:
Mysql> show tables
->
->
Теперь последнее действие – создадим простого пользователя, предоставим ему доступ к созданной базе данных, и начнем работать.
Команда GRANT наделяет пользователя nina, зашедшего на сервер с этой же машины (c localhost) и идентифицируемого паролем «123», определенными правами (в данном случае всеми) на все таблицы базы данных book. Теперь мы можем выйти и зайти как пользователь nina с соответствующим паролем:
shell>Mysql -u nina -p
Enter password
: ***
Welcome to
the Mysql monitor!…
Mysql>
Если вы собираетесь пользоваться базой данных на чужом сервере, то его администратор проделает все описанные выше действия за вас, т.е. все настроит и создаст пользователя и базу данных. В следующей главе описаны команды языка SQL, которые пригодятся для работы с данными, хранящимися в СУБД Mysql.
Базовая структура базы данных
Прежде чем мы начнем, вы должны понять иерархию базы данных.
База данных SQL — это набор связанной информации, хранящейся в таблицах. В каждой таблице есть столбцы, описывающие данные в них, и строки, содержащие фактические данные. Поле — это отдельный фрагмент данных в строке.
Например, удаленная компания может иметь несколько баз данных. Чтобы увидеть полный список их баз данных, мы можем ввести SHOW DATABASES; и мы можем подключиться к базе данных сотрудников.
Результат будет выглядеть примерно так:
В одной базе данных может быть несколько таблиц. Взяв пример из вышеупомянутого, чтобы увидеть различные таблицы в базе данных сотрудников, мы можем сделать запрос SHOW TABLES in employees;. Это запрос выведет список всех существующих таблиц в базе данных employees.
Каждая таблица состоит из разных наборов столбцов, описывающих данные.
INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN (верно для MYSQL, в стандарте SQL INNER JOIN не эквивалентен синтаксически CROSS JOIN, т.к. используется с выражением ON).
SELECT id_person, name, id_pos, title
FROM `persons`
INNER JOIN `positions` ON id_pos = position_ref
|
1 |
SELECT id_person,name,id_pos,title FROM`persons` INNER JOIN`positions`ON id_pos=position_ref
|
Такое присоединение покажет нам данные из таблиц, только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.
Если поменять порядок соединения таблиц — получим тот же результат.
Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:
Далее проследим как получить разные части (подмножества) данного множества.
Задачка про кинотеатр
Вот дополнительная, более сложная задачка. Есть кинотеатр, в нем идут фильмы. У фильма есть название, длительность (пусть для простоты будет 60, 90 или 120 минут), цена билета (в разное время и дни может быть разная), время начала сеанса (один фильм может быть показан несколько раз в разное время за разную цену). Также, есть информация о купленных билетах (номер билета, на какой сеанс).
Задания:
составь грамотную нормализованную схему хранения этих данных в БД. Внеси в нее 4-5 фильмов, расписание на один день и несколько проданных билетов.
Сделай запросы, считающие и выводящие в понятном виде:
- ошибки в расписании (фильмы накладываются друг на друга), отсортированные по возрастанию времени. Выводить надо колонки «фильм 1», «время начала», «длительность», «фильм 2», «время начала», «длительность».
- перерывы больше или равные 30 минут между фильмами, выводятся по уменьшению длительности перерыва. Выводить надо колонки «фильм 1», «время начала», «длительность», «время начала второго фильма», «длительность перерыва».
- список фильмов, для каждого указано общее число посетителей за все время, среднее число зрителей за сеанс и общая сумма сбора по каждому, отсортированные по убыванию прибыли. Внизу таблицы должна быть строчка «итого», содержащая данные по всем фильмам сразу.
- число посетителей и кассовые сборы, сгруппированные по времени начала фильма: с 9 до 15, с 15 до 18, с 18 до 21, с 21 до 00:00. (то есть сколько посетителей пришло с 9 до 15 часов, сколько с 15 до 18 и т.д.).
Создание таблицы
publications
USE publications;
CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM;
Я не буду останавливаться на типах данных. О них много написано и вы всегда можете посмотреть их в справочниках или книгах. Замечу, что использование типов данных будет влиять на производительность и возможности поиска. Поэтому, лучше все спланировать заранее.
Тип данных AUTO_INCREMENT
Добавление столбца id с автоприращением
- NOT NULL — обеспечивает наличие значения в каждой записи столбца. Многие
программисты используют его в поле NULL, чтобы показать отсутствие в нем
какого-либо значения. Но тогда могут появляться дубликаты, противоречащие
самому смыслу существования этого столбца. Поэтому появление в нем значения NULL запрещено. - AUTO_INCREMENT — заставляет MySQL установить для этого столбца уникальное
значение в каждой строке, как было описано ранее. Фактически мы не управляем
значением, которое будет появляться в каждой строке этого столбца, но это и не
нужно: все, о чем мы беспокоимся, — гарантия уникальности этого значения. - KEY — столбец с автоприращением полезно использовать в качестве ключа, поскольку вы будете стремиться искать строки на основе значений этого столбца.
CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4), id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;
