При установке сервера MySQL также устанавливается консольный клиент для работы с базами данных. Например, в Windows в меню Пуск можно найти программу MySQL 8.0 Command Line Client . Это и есть собственно консольный клиент:
Причем клиент устанавливается сразу в двух вариантах — с поддержкой Unicode и без.
Запустим MySQL 8.0 Command Line Client — Unicode. Вначале нам отобразится предложение ввести пароль:
Здесь необходимо ввести пароль, который был установлен при установке MySQL для пользователя root. И после удачного подключения можно будет отправлять серверу команды через данный консольный клиент.
Для начала посмотрим, какие базы данные есть на сервере по умолчанию. Для этого введем команду show databases;
После выполнения этой команды мы увидим, что на сервере по умолчанию уже есть ряд баз данных, которые выполняют административные функции.
Теперь создадим базу данных с помощью следующей команды языка SQL:
Для создания базы данных применяется команда create database , после которой указывается название бд. То есть в данном случае база данных будет называться "test".
Чтобы обращаться к какой-нибудь определенной базе данных, вначале надо установить нужную базу данных в качестве текущей. Для этого нужно выполнить команду use , после которой указывается название базы данных. Например, для подключения ранее созданной базы данных test введем следующую команду:
Затем создадим в этой базе данных таблицу с помощью команды:
Данная команда создает таблицу users, в которой будет три столбца — id, name и age. id будет хранить уникальный числовой идентификатор пользователя и будет автоматически генерироваться базой данных, name будет хранить имя пользователя, а age — его возраст.
После этого мы можем добавлять и получать данные из выше созданной таблицы. Вначале добавим в таблицу одну строку с помощью следующей команды:
И в конце получим добавленные данные:
Таким образом в общих чертах мы можем работать с консольным клиентом MySQL Command Line Client.
Консоль MySQL позволяет вводить как инструкции SQL, так и служебные команды MySQL, предназначеные для администрирования и использования таблиц в базах данных MySQL. К основным командам относятся SHOW, USE и CREATE.
Рассмотрим каждую из них отдельно.
Эта команда предназначена для просмотра доступных баз данных и таблиц в конкретных базах данных. Для просмотра списка баз данных необходимо ввести такую команду:
Если, например, существуют две базы данных — mysql и test (эти две базы как правило создаются автоматически при инсталляции сервера), то MySQL ответит примерно так:
Для просмотра списка таблиц используется эта же команда в таком виде:
Эта команда выдаст список таблиц в текущей базе данных:
Замечание: вы можете работать только с одной базой данных в одно и то же время, поэтому в приведенном выше примере нет нужды указывать название базы данных, список таблиц которой стоит вывести, поскольку производится поиск в активной на данный момент базе.
Содержание
CREATE
Эта команда позволяет создавать новые базы данных. Как было сказано выше, по умолчанию сервер создает две базы данных — mysql и test. Для удобства стоит создавать новую базу данных для каждой отдельной задачи, потому что легче работать с набором таблиц, которые связаны между собой (даже если эта связь только смысловая). Создадим новую базу данных при помощи этой команды:
После этой операции будет создана новая база данных не содержащая никаких таблиц, а команда SHOW DATABASES выдаст следующее:
Как уже было сказано выше, в один и тот же момент только одна база данных может быть активна. Команда USE служит для выбора этой активной базы. Для перехода к некоторой базе данных необходимо выполнить следующую операцию:
Стоит отметить, что при создании новой базы данных (см. выше), новая база не становится активной автоматически — для ее использования необходимо сделать это самостоятельно.
Нижеследующее ознакомит вас с методами извлечения и занесения данных в таблицы при помощи языка SQL.
Наипростейшей командой является следующая:
Эта команда подразумевает, что в активной базе данных существует таблица mysql, из которой она просто извлекает все данные и выводит их на экран.
Замечание: На SQL-команды распространяется одно ограничение, которое не имеет отношения к командам MySQL — инструкции SQL обязательно должны заканчиваться точкой с запятой. Это позволяет вводит многострочные запросы к бызе данных, что не требуется для MySQL-команд, которые как правило лаконичные и короткие.
Как вы уже наверное поняли, команда SELECT служит для просмотра данных таблиц. Ее упрощенный синтаксис таков:
В списке поля может быть как один элемент, так и несколько; кроме того возможно указание символа «*» (), который говорит, что следует показать все поля таблицы.
Часть WHERE … является необязательной и позволяет выбрать из таблицы строки, удовлетворяющие определенному(ым) условию(ям). Раздел ORDER BY … служит для сортировки полученных данных по определенным полям.
Вот пример более сложного запроса, демонстрирующий сказанное выше. Допустим таблица staff содержит информацию о сотрудниках некоторой организации. Этот запрос находит записи о тех из них, кто работает больше двух лет и кого не зовут Иваном.
Рассмотрим этот запрос
Первая строка велит MySQL показать содержимое только полей name, project и works_since. Вторая строка указывает на таблицу, в которой следует искать эти данные. Оставшаяся часть запроса указывает критерии выбора — имя не должно быть ‘Иван’, а дата быть более ранней, чем 26-е апреля 1998-го года. Вот пример вывода после такой операции:
Для показа данных одного поля может использоваться такой запрос:
Как видите, этот запрос просто выбирает значения из нужного поля из каждой строки и выводит их, заодно сортируя по (единственному) полю project, поэтому в результатах встречаются повторения. Для устранения их используется инструкция DISTINCT:
SQL позволяет производить некоторые вычисления и получать некоторую описательную информацию при помощи агрегатных функций. Среди этих функций — COUNT, SUM, MAX, MIN и AVG:
COUNT — вычисляет количество найденых строк;
SUM — находит сумму значений в найденых строках;
MAX — находит найбольшее среди найденых значений;
MIN — находит наименьшее среди найденых значений;
AVG — находит среднее значение от найденых .
Используются эти функции как элементы списка таблиц в запросе с аргументом — названием поля. Вот несколько примеров.
Эти запросы находят количество выполняемых проектов, наибольшее количество проектов, выполняемое одним человеком и среднее количество проектов, в которых участвуют работники соответственно.
SQL также располагает средствами работы с регулярными выражениями (работе с которыми в Perl вы можете ознакомиться, прочтя соответствующую статью на нашем сайте).
Для сравнения значения с регулярным выражением используется оператор LIKE, а для конструирования простеших выражений — символы ‘_’ (произвольный символ) и ‘%’ (произвольное количество произвольных символов). Пример. Этот запрос находит все имена, которые начинаются с ‘Jo’:
MySQL также позволяет производить более развитое сравнение с шаблоном при помощи оператора REGEXP и средств построения шаблона теми же методами, что используются в Perl (см. Регулярные выражения в Perl).
Этот запрос выведет все строки, в которых название проекта содержит букву ‘b’ вне зависимости от регистра.
И последнее по порядку, но не по значению — использование более чем одной таблицы. Особенность заключается в том, что используемые таблицы могут иметь общие поля, которыми они связаны.
Для того, чтобы точно указывать, о поле какой таблицы идет речь, используется запись типа staff.project, где перед точкой стоит название таблицы, а после нее — название поля. Второй вариант — назначения псевдонимов (алиасов) таблицам для большего удобства. Для этого в списке таблиц название каждой таблицы указывается вместе с псевдонимом — например stuff x, projects y.
В этом примере объединяются таблицы staff и projects, причем выводятся имя сотрудника, проект над которым он работает и описание этого проекта.
Объединив все это в один запрос получим следующее:
Этот запрос показывает названия проектов, описания их и количество сотрудников, которые заняты в них для тех проектов, которые не являются секретными и описания которых начинаются с ‘Wo’, следом за которыми идут еще два символа, после чего — ‘d’ и дальше что угодно, и сортирует единственное значение по полю project_name .
Редактирование данных
Редактирование данных — это добавление, удаление и изменение их. Для выполнения этих операций используются команды INSERT, DELETE и UPDATE соответственно.
Команда INSERT служит для вставки строк в таблицы. Вот как может выглядеть такой запрос:
Возможна также вставка отдельных значений. В таком случае необходимо указать, в какие поля стоит вставлять данные, причем оставшиеся поля будут незаполнены — при выполнении запросов SELECT они будут представлены как NULL — специальное начение, означающее, что данное отсутствует.
Удаление данных производится командой DELETE. Для этого нужно указать таблицу и (необязательно) условия, которым должны удовлетворять строки, которые следует удалить. Если условия опущены, эта команда, как и SELECT, проделает эту операцию надо всеми строками — то есть очистит таблицу.
Изменение данных производится при помощи команды UPDATE. Необходимо указать, значения каких полей следует изменить, а также (опять же необязательно) условия, которым должны удовлетворять строки, которые следует обновить.
Этот запрос устанавливает дату принятия на работу, зарплату и название проекта всем Джекам, которые находятся в базе данных.
Это небольшая часть языка SQL, необходимая для работы с MySQL, в частности при программировании для Web. Для более полной информации обратитесь к официальным спецификациям SQL или к документации MySQL.
Примечание для тех, кто знаком со стандартом ANSI SQL 92: на данный момент MySQL поддерживает укороченную версию этого стандарта, в которую не входит выполнения подзапросов а также некоторые другие функции.
В данной статье соберу наиболее часто используемые команды для работы с MySQL
Список команд, используемых для работы с MySQL безграничный, но есть команды, которые используются чаще всего.
Ниже будет предствлено много команд, которые запускаются или из консоли сервера, или из консоли MySQL. Если команда начинается на mysql> — данная команда выполняется из консоли базы, т.е. после вхдоа в MySQL.
Команды рассматриваю исходя из того, что имеем дело с сервером на базе Centos (на данную операционную систему устанавливается веб-окружение 1С-Битрикс, с которым, преимущественно, я работаю).
Для того, чтобы проверить статус MySQL, необходимо выполнить команду:
Чтобы запустить, остановить или перезапустить MySQL, используются команды:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на том же хосте, используется команда:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на удаленном хосте external.com
Работа с базами, таблицами – просмотр, удаление, редактирование записей
Создать базу данных на MySQL сервере:
Показать список всех баз данных на сервере MySQL:
Переключиться для работы с определенной базой данных:
Отобразить все таблицы в базе данных:
Просмотреть формат таблицы в базе:
Удалить таблицу из базы:
Показать все содержимое таблицы:
Отобразить столбцы и содержимое столбцов в выбранной таблице:
Отобразить строки в определенной таблице, содержащие “whatever“:
Отобразить все записи в определенной таблице, содержащие “Bob” и телефонный номер “3444444:
Отобразить все записи, НЕ содержащие имя “Bob” и телефонный номер “3444444“, отсортированные по полю phone_number:
Показать все записи, начинающиеся с букв ‘bob” и телефонного номера “3444444” в определенной таблице:
Показать все записи, начинающиеся с букв ‘bob” и телефонного номера “3444444“, ограничиваясь записями с 1-ой до 5-ой:
Использование регулярных выражений (“REGEXP BINARY”) для поиска записей. Например, для регистро-независимого поиска – найти все записи, начинающиеся с буквы А:
Показать все уникальные записи:
Отобразить выбранные записи, отсортированные по возрастанию (asc) или убыванию (desc):
Показать количество строк в таблице:
Подсчитать количество столбцов в таблице:
Добавление колонки в базу данных:
Изменение имени столбца:
Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:
Изменение размера столбца:
Удаление столбца из таблицы:
Загрузка файла CSV в таблицу:
Пользователи, пароли сервера MySQL – добавление, изменение пользователей и паролей
Создание нового пользователя – подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:
Изменений пользовательского пароля из консоли на удаленном хосте external.com:
Изменение пользовательского пароля из консоли MySQL – подключение под root, обновление пароля, обновление привилегий:
Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL.
Обновление пароля root:
Установка права на подключение к серверу с хоста localhost с паролем «passwd» — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:
Установка привилегий пользователю на использование базы данных — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:
Обновление информации в базе данных:
Удаление строки в таблице:
Обновление привилегий в базе данных:
Резервные копии – создание, восстановление
Создать резервную копию (dump) всех баз данных в файл alldatabases.sql:
Создать резервную копию одной базы данных в файл databasename.sql:
Создать резервную копию одной таблицы в файл databasename.tablename.sql:
Восстановление базы данных (или таблицы) из резервной копии:
Создание таблиц
Создать таблицу, пример 1:
Создать таблицу, пример 2:
Примеры работы с MySQL
Показать текущую дату:
Можно выполнить запрос нескольких функций, для этого – после первого запроса не ставим закрывающие точку с запятой. Следующий код позволяет показать текущее время, пользователя и версию MySQL
Если в процессе вы передумали продолжать набор команд и хотите завершить набор – укажите с:
Что бы переключиться на использование определённой базы – используйте use:
Что бы проверить, какая в данный момент база используется – выполните:
Для добавления данных в таблицы используются несколько методов:
- с помощью оператора INSERT и значения VALUES;
- с помощью оператора INSERT и значения SET;
- с помощью оператора LOAD DATA;
- с помощью утилиты mysqlimport из файла, в консоли сервера.
Добавление с помощью оператора с помощью утилиты INSERT VALUES
Оператор INSERT имеет следующий синтаксис:
Порядок указания параметров VALUES должен соответствовать порядку столбцов в таблице, проверить которые можно командой:
Выделять значения можно как двойными кавычками ” “, так и одинарными – ‘ ‘. Столбцы, имеющие атрибут AUTO_INCREMENT заполняются значением NULL. Можно использовать множественный ввод для разных строк одной таблицы, указав значения через запятую:
Добавление с помощью оператора SET
При использовании оператора SET в запросе перечисляются все имена столбцов и значения, которые в них требуется установить:
Однако, с помощью оператора SET нельзя вставлять несколько строк, в отличии от схемы INSERT VALUES.
Добавление из файла с помощью LOAD DATA
Столбцы в файле должны быть разделены табуляцией (не пробелом!). Использовать кавычки не нужно. Значения VALUES располагаются как и при обычном методе INSERT VALUES – по очереди имеющихся столбцов в таблице.
Например – содержимое файла main_list.txt:
Если сервер выдал ошибку такого плана:
Добавьте в конфигурационный файл сервера MySQL my.cnf в блок [client] строку:
и перезапустите сервер.
Добавление из файла с помощью mysqlimport
Фактически, mysqlimport просто выполняет оператор LOAD DATA на сервере.
Требования к файлу такие же, как и при использовании LOAD DATA и были описаны выше.
–debug-info использовать не обязательно, тут он просто для примера.
Благодарю за внимание! Делитесь вашими замечаниями в комментариях ниже.
