Sql через командную строку windows

Доброго времени суток, коллеги 🙂

Да, именно коллеги, потому как простому смертному MySQL командная строка (или MySQL Shell, как её ещё любят называть разработчики), равно как и работа с MySQL в консоли, вряд ли пригодится.

Для того, чтобы данная тема была интересна, нужно быть, как минимум, бэкэнд-разработчиком или начинающим системным администратором, которые не пользуются phpMyAdmin и другими интерфейсами принципиально.

Хотя, возможно, вы просто подписаны на уведомления и решили прочитать новую статью из чистого любопытства.

Ну что ж… Весьма похвально 🙂

В любом случае, я постараюсь, чтобы материал был одинаково интересен и понятен всем, кем бы вы ни работали и как бы вы ни были связаны с программированием. Собственно, чего я своими статьями и добиваюсь, в принципе 🙂

Итак, сегодня речь пойдёт о том, как работать с MySQL через командную строку на сервере. Я расскажу, в каких ситуациях данные знания вам могут пригодиться, как подключиться к базе данных MySQL в консоли, запускать командную строку MySQL и производить основные действия с БД, таблицами и их записями.

Одним словом, мы рассмотрим весь жизненный цикл БД и все основные операции, которые могут вам понадобиться в процессе. В результате у нас получится что-то вроде шпаргалки системного администратора, которой я сам буду активно в будущем пользоваться, т.к. всего в памяти не удержишь.

Поехали 🙂

Сразу скажу, что, если вы не собираетесь работать системным администратором или деплойщиком, то каждый день работать с MySQL через командную строку вам вряд ли понадобиться. Для повседневного использования вполне подойдёт старый добрый phpMyAdmin либо какой-то другой веб интерфейс или приложение для работы с MySQL.

Хотя, здесь имеет место привычка. Лично у меня есть знакомые из ранга «трушных кодеров», которые являются фанатами консоли со времён MS-DOS и чистого Linux, видимо, где работать с ОС можно было только из консоли.

Поэтому они и презирают всякого рода графические «примочки». Хотя, большинству пользователей подойдут именно они.

Лично я сам предпочитаю использовать phpMyAdmin как основную программу при создании сайтов для работы с MySQL, т.к. по природе своей являюсь визуалом. Однако, иногда на практике встречаются ситуации, когда знания MySQL командной строки и умения работы с MySQL в консоли сервера просто необходимы.

У меня на данный момент их было три:

1. Впервые я столкнулся с работой в MySQL консоли, когда понадобилось сделать импорт большого дампа БД. Через phpMyAdmin он не загружался целиком, т.к. отваливался где-то посередине по таймауту, несмотря на изменение настроек времени выполнения операции и размеров загружаемых файлов. В принципе, можно было подобрать нужные значения, но мне показалось это слишком длительным процессом.
2. В следующий раз поработать с MySQL через командную строку пришлось для отключения проверки внешних ключей, когда нужно было удалить данные из таблицы, которая с помощью ключей была связана с другими. В phpMyAdmin я просто не нашёл, как это сделать.
3. В серьёзных компаниях для работы с MySQL используется исключительно консоль без каких-либо phpMyAdmin-ов. Не знаю почему конкретно, но знающие люди говорили, что это как-то связано с безопасностью. В результате, всем приходилось работать с командной строкой MySQL, в том числе и мне 🙂

Но, повторюсь, работа с MySQL в консоли и командной строке вполне подходит и для повседневного использования. Все известные визуальные программы для работы с БД всё равно работают на основе консольных команд MySQL. Поэтому, кому как нравится 🙂

Запуск консоли на разных ОС

Поскольку я буду демонстрировать работу с командной строкой MySQL в консоли сервера, то для начала неплохо бы было её запустить.

Действие простое, многим знакомое, для которого достаточно знания «горячих клавиш».

Запуск консоли в Windows:

• Win+R для открытия командной строки Windows с правами администратора;
• Вводим команду cmd
• Нажимаем Enter на клавиатуре

Запуск консоли в Linux дистрибутивах (терминала): в Ubuntu, с которой я работал, достаточно системной комбинации клавиш Ctrl+Alt+T. Про остальные ОС на базе Linux ничего сказать не могу.

Запуска консоли в MacOS: сам я таким не занимался, т.к. Мак-ом ещё не обзавёлся и вряд ли обзаведусь за ненадобностью, но, насколько мне удалось узнать, «горячих клавиш» для вызова терминала в этой ОС нет. Так что если вы являетесь пользователем «яблочной» продукции, то запустите консоль через интерфейс ОС, благо, что мануалов в сети много.

Немного стыдно даже такую информацию публиковать на случай, если на данную статью случайно набредёт какой-то профессиональный системный администратор с бородатым опытом, т.к. он подумает: «Автор считает своих читателей программистами, но при этом учит запускать консоль… Псих какой-то :-)».

Да, вполне логично 🙂 Но я просто учитываю ситуации, что наряду с профессиональными разработчиками сюда могут попасть и новички. Поэтому я и стараюсь делать информацию полной и доступной для всех категорий пользователей, о чём уже говорил.

Основные команды консоли MySQL

Итак, первым делом, нам нужно получить доступ к MySQL командной строке в консоли. Для этого открываем консоль сервера и, если у вас MySQL установлен глобально в виде сервиса, то для «проверки связи» прописываем следующее:

mysql -V

Консольная команда mysql позволяет нам запустить одноименную утилиту, являющуюся командной строкой MySQL.

Это позволит нам узнать версию MySQL, установленного на компьютере и убедиться, что он вообще установлен как служба. Если это так, то в ответ в консоли вы увидите примерно следующий текст: mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64).

Да, я не «трушный кодер», потому что сижу под Виндой 🙂 Но, не суть. На Unix системах процедура будет такой же.

Если же вдруг MySQL у вас не установлен глобально на компьютере или у вас есть необходимость работы с несколькими версиями MySQL, при которой есть только каталоги с файлами и библиотеками MySQL, то запуск MySQL через консоль будет выглядеть следующим образом.

В консоли переходим в каталог, где находится исполняемый файл MySQL (на Windows, по крайней мере, это mysql.exe) следующей командой:

cd C:\OpenServer\modules\database\MySQL-5.7-x64\bin

Путь к дистрибутиву у вас, естественно, может отличаться. В качестве примера я решил запустить одну из версий MySQL, входящих в комплект OpenServer.

И запускаем MySQL, проверяя попутно его версию:

mysql.exe -V

В результате в консоль должно было вывестись аналогичное первому случаю сообщение mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64).

Всё, с запуском MySQL командной строки через консоль сервера разобрались, теперь будем подключаться непосредственно к серверу MySQL.

Подключение к серверу MySQL в консоли

Cервер MySQL может не обязательно находиться на той же машине, на которой вы пытаетесь получить к нему доступ в консоли. Следовательно, для того, чтобы подключение к удалённому серверу MySQL через консоль было возможно, утилита mysql имеет множество параметров, с помощью которых можно указывать необходимые настройки.

Для того, чтобы запустить командную строку MySQL в консоли, нам достаточно прописать в консоли сервера следующее:

mysql

Однако, в таком случае вы получите следующую ошибку: ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO). Так сообщение об ошибке выглядит у меня на Windows. Если же вы используете Linux, то вместо ODBC будет имя вашего системного пользователя, под которым вы совершаете действия в системе.

Всё это потому, что по умолчанию при подключении к MySQL серверу в консоли используется пользователь ODBC на Windows без пароля и системный юзер на Linux с его же паролем. Хост по умолчанию localhost, т.е. данный пользователь может подключаться только с локальной машины.

Поэтому выхода у вас два: либо использовать другого пользователя для входа в командную строку MySQL, либо создать пользователя MySQL с нужной учёткой. Но для второго способа нам без первого всё равно не обойтись 🙂 Поэтому для начала подключаемся к серверу MySQL под стандартным пользователем root, который создаётся при установке MySQL на компьютер и для которого по умолчанию разрешён доступ с localhost:

mysql -u root -p

При требовании ввести пароль просто нажимаем Enter (если вы его не вводили, конечно, при установке MySQL). После этого вы будете подключены к серверу MySQL с присвоением вам идентификатора подключения и получением доступа к командной строке MySQL.

Если же вы захотите подключиться к серверу MySQL, имеющему специфическое имя хоста или IP или захотите залогиниться под другим пользователем, то используйте команду следующего формата:

mysql -u имя_пользователя -p пароль_пользователя -h хост_или_IP_сервера_MySQL

Вместо кириллических символов с подчёркиванием, естественно, нужно ввести свои данные латинницей. Кстати, данную команду при желании можете прописать немного в другом формате:

mysql --user=имя_пользователя --password=пароль_пользователя --host=хост_или_IP_сервера_MySQL

Если по какой-то причине вы не хотите, чтобы ваш пароль подключения к MySQL отображался в консоли (что правильно, на самом деле), можете использовать следующую команду:

mysql -u имя_пользователя -h хост_или_IP_сервера_MySQL -p

Поскольку пароль не указан явно, вам будет предложено его ввести на следующем шаге. Причём, введённые вами символы отображены не будут даже в виде звёздочек (wildcards), вместо этого будет просто пустая строка.

Помимо указанных настроек подключения возможно использовать следующие параметры, значения которым будут указываться аналогичным с вышеперечисленными образом:

1. —port или -P — для указания порта подключения к серверу MySQL;
2. —protocol — протокол, по которому будет осуществляться подключение (возможные варианты: TCP для Windows и Linux, SOCKET для Linux, PIPE и MEMORY для Windows);
3. —socket или -S — данный параметр пригодится, если вы захотите подключаться через сокеты, следовательно, значение параметра будет сокет;
4. —pipe или -W — параметр нужен, если вы захотите использовать именованные «трубопроводы» или «пайпы» для подключения;
5. —shared-memory-base-name — этот параметр вам пригодится для MEMORY подключений через общую память на Windows;

Понятное дело, что на этом список всех параметров подключения к MySQL серверу не ограничивается. В реальности их намного больше.

Если по какой-то причине стандартный вариант с указанием хоста, пользователя и пароля вам не подойдёт, то для выяснения полного перечня параметров подключения вам будет полезна следующая информация — https://dev.mysql.com/doc/refman/5.7/en/connecting.html

Как создать базу данных в MySQL консоли

После того, как мы подключились к серверу MySQL и запустили командную строку MySQL, время начать жизненный цикл нашей базы данных сайта, который стартует с её создания. Для создания базы данных MySQL через командную строку нужно ввести следующую команду:

CREATE DATABASE имя_базы_данных;

То же самое действие можно сделать и с помощью специальной MySQL утилиты mysqladmin. Запускается она изолированно от командной строки MySQL, т.е. для её использования вам нужно будет выйти из неё или открыть новую консоль сервера.

А затем вызвать следующую команду:

mysqladmin create имя_базы_данных;

К слову, с помощью утилиты mysqladmin можно не только создавать и удалять базы данных, не заходя в консоль MySQL, но и оперировать серверной конфигурацией, процессами MySQL, управлять репликациями, пинговать сервера и делать ещё много всего интересного.

Более подробно о mysqladmin можете прочитать здесь — https://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html

Как создать пользователя MySQL в командной строке

Не часто, но иногда требуется создать нового пользователя MySQL. Причём, в консольном режиме.

В командной строке MySQL это делается следующим образом:

CREATE USER 'имя_пользователя'@'хост_или_IP_машины' IDENTIFIED BY 'пароль_пользователя';

Пользователь создан. Параметр хост_или_IP_машины означает, что при создании пользователя нужно указывать IP, с которого он сможет подключаться к серверу, или имя хоста (подойдёт доменное имя рабочей машины в сети).

Кстати, при указании хоста для подключения к MySQL серверу можно использовать символ процента — %, который означает, что создаваемый пользователь может соединяться с сервером MySQL с любого IP адреса или хоста.

При этом нужно учитывать, что в данном случае localhost не входит в список адресов, указываемых с помощью %, т.к. localhost обозначает подключение через UNIX сокет вместо стандартного TCP/IP. Т.е. если созданный пользователь MySQL будет подключаться к серверу не с помощью сокетов, а по другому протоколу, указываемому при соединении с MySQL сервером в консоли, то ему нужно будет создавать две учётные записи пользователя:

CREATE USER 'имя_пользователя'@'%' IDENTIFIED BY 'password';
CREATE USER 'имя_пользователя'@'localhost' IDENTIFIED BY 'password';

С созданием пользователя MySQL в консоли мы разобрались. Теперь зададим права пользователя для выполнения любых действий с нашей только что созданной базой данных следующей командой:

GRANT ALL PRIVILEGES ON имя_базы_данных.* TO 'имя_пользователя'@'хост_или_IP_машины';

Опция ALL, как вы поняли, как раз и указывает на то, что пользователю разрешаются любые действия с определённой БД. Полный список прав, которые можно выдавать пользователям с помощью команды GRANT, можно найти здесь (правда, описание параметров на английском) — https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-privileges

Можно, кстати, использовать несколько параметров привилегий, указывая их при вызове команды через запятую.

Если захотите создать суперюзера, т.е. обладающего глобальными привилегиями для осуществления различных действий со всеми БД на сервере, то используйте следующий вызов команды:

GRANT ALL ON *.* TO 'имя_пользователя'@'хост_или_IP_машины';

Кстати, не лишним будет очистить кэш MySQL сервера после установки привилегий. Если будете их менять, то также не забывайте делать это вызовом следующей команды:

FLUSH PRIVILEGES;

А если захотите поменять права пользователя, то используйте сначала следующую команду чтобы сбросить все права:

REVOKE ALL PRIVILEGES ON *.* FROM 'имя_пользователя'@'хост_или_IP_машины';

А затем установите ему нужные с помощью GRANT, как это было описано ранее.

Если же вам в будущем потребуется изменить пароль пользователя MySQL, то для этого достаточно выполнить в MySQL Shell следующие команды:

SET PASSWORD FOR 'имя_пользователя'@'хост_или_IP_машины' = PASSWORD('новый_пароль');

FLUSH PRIVILEGES;

Сброс кэша привилегий MySQL сервера нужен с той же целью, что и при смене прав — без этого действия смена пароля пользователя MySQL может быть не засчитана, поэтому не ленитесь пользоваться 🙂

В Интернете, кстати, полно примеров использования следующей команды для сброса пароля MySQL пользователя:

UPDATE mysql.user SET Password=PASSWORD('пароль') WHERE User='имя_пользователя';

Но у меня на MySQL 5.7 данный вариант не сработал, выдав ошибку ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’. Её причина оказалось отсутствие поля Password в таблице mysql.user.

Из чего можно предположить, что данный вариант работает лишь для старых версий MySQL, где в таблице пользователей данное поле существовало. Поэтому для уверенности используйте мой первый вариант, если вы, конечно не вынуждены работать с допотопным ПО без возможности обновления версий 🙂

На этом создание нового пользователя MySQL и обзор действий с его правами и паролями закончен. Идём далее.

Выбор базы данных при работе с MySQL через командную строку

Теперь, после создания пользователя в MySQL Shell и назначения ему прав на работу с БД нам нужно выбрать эту самую базу, чтобы можно было оперировать самой базой и хранящимися в ней данными.

Для этого используем следующую команду в MySQL консоли:

USE имя_базы_данных;

Если всё прошло успешно, то в консоли выведется сообщение Database changed, что будет сигнализировать о том, что мы выполнили вход в базу MySQL через консоль. Кстати, при соединении с сервером MySQL, изначально можно указывать БД, с которой необходимо будет работать. Для этого нужно прописать следующую команду в консоли сервера:

mysql --user=имя_пользователя --password=пароль_пользователя --host=хост_или_IP_сервера_MySQL --database=имя_базы_данных

Или то же самое, только с запросом ввода пароля пользователя MySQL:

mysql -u имя_пользователя -h хост_или_IP_сервера_MySQL имя_базы_данных -p

На этом всё. Думаю, никаких сложностей с подключением к базе данных MySQL через консоль теперь у вас не возникнет 🙂

Работа с таблицами MySQL через консоль MySQL

Итак, базу данных MySQL через консоль мы создали. Теперь было бы неплохо научиться с ней работать для случая, когда командная строка MySQL будет единственным средством доступа к хранящимся на сервере данным (как это было у меня, о чём я говорил в начале статьи).

Реляционная база данных, как известно, состоит из таблиц, внутри которых уже и хранится информация в виде записей с несколькими полями. Поэтому, следуя по иерархии размещения информации, для начала научимся производить типичные CRUD операции с таблицами.

CRUD операции, если кто-то не в курсе, — это операции по созданию, чтению, обновлению и удалению данных от англ. «Create, Read, Update, Delete» (возможно, на собеседованиях вам такое понадобится).

Напоминаю, что для выполнения действий с таблицами вы должны сперва подключиться к БД MySQL с помощью команды USE.

Итак, первая на повестке у нас команда создания таблицы MySQL в БД через командную строку, которая выглядит так:

CREATE TABLE имя_таблицы (название_поля_1 тип_поля_1, название_поля_2 тип_поля_2(размер_поля_2), INDEX(название_поля_1), ...);

Как вы понимаете, полей может быть сколько угодно, типы их могут быть различны, равно как и наличие индексов и ключей опционально.

Кстати, если вы захотите скопировать таблицу в другую базу данных или просто создать копию в текущей, вам помогут следующие команды:

CREATE TABLE новое_имя_таблицы LIKE старое_имя_таблицы;
INSERT новое_имя_таблицы SELECT * FROM старое_имя_таблицы;

Данные команды позволяют скопировать структуру таблицы и её данные вместе с индексами и триггерами таблиц. Если вам нужны просто данные и структура (имена полей и их типов данных), то можете обойтись вызовом одной команды:

CREATE TABLE новое_имя_таблицы AS SELECT * FROM старое_имя_таблицы;

Следующая операция из блока CRUD у нас чтение. В случае таблиц чтением будет вывод на экран их структуры. Для этого существует следующие четыре команды:

SHOW FULL COLUMNS FROM имя_таблицы;
DESCRIBE имя_таблицы;
EXPLAIN имя_таблицы;
SHOW CREATE TABLE имя_таблицы;

Первая выводит информацию о полях таблицы БД в табличном виде в консоль MySQL с указанием названия поля, типа данных, наличия ключей, значением по умолчанию и т.д. При использовании ключевого слова FULL можно получить расширенную информацию, включая привилегии на каждое из полей для текущего пользователя, комментарии к каждому из них и значение кодировки.

Вторая и третья команды являются просто сокращёнными формами первой команды без расширенной информации. Зачем было их плодить — даже не представляю… Разве что, чтобы было что спросить на собеседованиях при приёме на работу? 🙂

Четвёртая команда, помимо имени, типов полей и значений их по умолчанию позволяет получить значения ключей таблиц, движков таблиц (InnoDB, MyISAM), кодировку и др.

Update операция в случае таблиц представляет собой изменение их структуры, т.е. различные действия с полями таблиц MySQL:

ALTER TABLE имя_таблицы DROP COLUMN название_поля;
ALTER TABLE имя_таблицы ADD COLUMN название_поля VARCHAR(20);
ALTER TABLE имя_таблицы CHANGE старое_название_поля новое_название_поля VARCHAR(50);
ALTER TABLE имя_таблицы MODIFY название_поля VARCHAR(3);

Первая команда позволяет удалить определённое поле таблицы, вторая — добавить, третья позволяет переименовать поле и попутно изменить тип хранимых в нём данных, а четвёртая — изменить исключительно тип данных.

Тоже самое можно делать и с индексами таблиц с помощью во многом похожих команд:

ALTER TABLE имя_таблицы ADD UNIQUE INDEX имя_индекса (название_поля_1, ...);
ALTER TABLE имя_таблицы rename INDEX старое_имя_индекса TO новое_имя_индекса;
ALTER TABLE имя_таблицы DROP INDEX имя_индекса;

Приведённые команды позволяют добавлять, переименовывать и удалять индексы из таблиц MySQL через командную строку. Для добавления и удаления индексов есть, кстати, ещё альтернативный вариант использования самостоятельных команд, а не делать это через ALTER TABLE. Поэтому при желании можете использовать их:

CREATE UNIQUE INDEX имя_индекса (название_поля_1, ...) ON имя_таблицы;
DROP INDEX имя_индекса ON имя_таблицы;

Приведённые команды являются эквивалентами первой и последней из предыдущего блока. Для переименования индекса, к сожалению, отдельной команды не существует. А для изменения типа индекса, к сожалению, вообще нет никакой возможности в MySQL. Единственный выход — удалить индекс и создать его снова с нужным типом.

Ну, и наконец, мы дошли до последней операции из блока CRUD — к удалению. Удалить таблицы MySQL из БД очень просто. Достаточно в консоли MySQL выполнить следующую команду:

DROP TABLE имя_таблицы;

Иногда на практике возникают ситуации, из-за которых не получается удалить таблицу или изменить её структуру. Как правило, это связано с использованием в БД внешних ключей для связи таблиц между собой. С этой ситуацией я лично сталкивался неоднократно, о чём говорил в начале статьи.

Поэтому, если при удалении или обновлении структуры таблицы или её данных MySQL вернул вам ошибку с текстом Cannot delete or update a parent row: a foreign key constraint fails, то следующая информация вам будет как нельзя кстати.

Для того, чтобы осуществить задуманное, нам нужно временно отключить проверку существования внешних ключей, произвести необходимую операцию, а затем снова включить проверку, т.к. она действительно нужна и позволяет предохраняться от нарушения целостности данных в большинстве случаев.

Собственно говоря, с данной целью внешние ключи MySQL и нужны.

Итак, для удаления данных, которому мешают внешние ключи необходимо выполнить следующие действия в консоли MySQL:

SET FOREIGN_KEY_CHECKS=0;
#необходимая_mysql_команда
SET FOREIGN_KEY_CHECKS=1;

Кстати, если захотите удалить внешний ключ, то процедура будет такой же, как и при удалении индекса:

ALTER TABLE имя_таблицы DROP FOREIGN KEY имя_внешнего_ключа;

Чтобы узнать имя внешнего ключа MySQL таблицы, используйте уже знакомую команду MySQL консоли SHOW CREATE TABLE.

Работа с данными таблиц MySQL через командную строку

Для таблиц CRUD операции в MySQL консоли мы рассмотрели. Для полной картины данной шпаргалке не хватает только команд для работы с самими данными, хранящимися в таблицах БД. Думаю, многие знают эти команды и пользовались ими на практике, но всё же напомню их ещё раз.

CRUD операции для работы с данными таблиц MySQL будут выглядеть так:

INSERT INTO имя_таблицы (поле1, поле2, ...) VALUES (значение_поля_1, значение_поля_2, ...);
SELECT поле1, поле2, ... FROM имя_таблицы;
UPDATE имя_таблицы SET поле1 = значение_поля_1, поле2 = значение_поля_2;
DELETE FROM имя_таблицы WHERE поле1 = значение_поля_1;

Приведённые выше команды соответствуют операциям создания, чтения, обновления и удаления данных из таблиц БД MySQL. При использовании SELECT и UPDATE также возможно использовать уточняющий оператор WHERE, с помощью которого можно конкретизировать выбор данных способом, описанным в случае использования DELETE.

Также при выборке данных из БД с помощью SELECT можно использовать следующий вариант для получения значений всех полей таблицы:

SELECT * FROM имя_таблицы;

Естественно, что в данных операциях могут использоваться и другие операторы, помимо WHERE. Особенно их много при выборке данных с помощью SELECT: тут и UNION для объединения результатов нескольких запросов, и различные типы JOIN. Все перечислять очень долго и утомительно как для меня, так и для вас будет читать это.

Поэтому давайте договоримся: если вам захочется о чём-то узнать поподробнее — просто напишите об этом в комментариях, и я постараюсь вам ответить. Или же это сделают другие сведущие участники нашего сообщества. Ок? 😉

Так что пока на данном блоке останавливаться не будем.

Если же вам нужно будет удалить все данные из таблицы, то можете воспользоваться следующей MySQL командой:

TRUNCATE имя_таблицы;

Перед её вызовом, как уже ранее и говорилось, вам может потребоваться отключение проверки внешних ключей в случае наличия связанных таблиц MySQL, которая может препятствовать произведению необходимого действия.

Ещё один интересный момент, который здесь нужно учесть, это то, что данная команда не производит сброс счётчика AUTO_INCREMENT, который используется, как известно, для автоматической генерации значения поля без необходимости его ручной установки.

Поля данного типа чаще всего используются для генерации значений главного ключевого поля id, которое используется для установки связей между данными разных таблиц.

Т.е., если до удаления данных таблицы с помощью TRUNCATE максимальное значение счётчика было 1200, то у первой записи после данной процедуры значение идентификатора будет 1201. В принципе, ничего страшного. Если вы задали достаточный размер для данного поля, то переполнение значений вам грозит не скоро.

Однако, в отдельных случаях, когда в коде приложения есть какая-то привязка к значению поля, то данное поведение может доставлять неудобства.

Чтобы этого избежать, используйте вместо команды выше данный вариант:

TRUNCATE TABLE someTable RESTART IDENTITY;

Данный вариант вызова команды TRUNCATE позволит вам сбросить значение счётчика полей с AUTO_INCREMENT. Поэтому значение поля первой добавленной записи после данного удаления будет 1 вместо 1201, как в примере выше.

Как удалить базу данных MySQL через командную строку

Жизненный цикл работы с базой данных подходит к концу и завершается вполне логично — её удалением. Для того, чтобы сделать данную операцию в консоли MySQL, нужно выполнить следующую команду (при этом удаляемая БД может быть и не выбрана командой USE):

DELETE DATABASE имя_базы_данных;

То же самое действие можно сделать и с помощью MySQL утилиты mysqladmin, о которой я уже упоминал в начале статьи при создании БД:

mysqladmin drop имя_базы_данных;

При вызове команды в консоли сервера появится следующее сообщение:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ‘имя_базы_данных’ database [y/N]

Вкратце — это предупреждение о том, что удаление базы данных MySQL — идея очень плохая. Также запрашивается подтверждение действия. Если согласны — пишем y и нажимаем Enter на клавиатуре, после чего на экран выведется следующее сообщение (если всё прошло успешно, конечно же):

Database «имя_базы_данных» dropped

Вот так 🙂

Как удалить пользователя MySQL в консоли

Теперь та же участь постигнет и создаваемого нами пользователя MySQL, чтобы продемонстрировать вам, как это делается. Но для начала неплохо бы перед самим удалением проверить, существует ли требуемый пользователь на самом деле.

Начиная с MySQL 5.7 для обоих этих действий существует одна команда:

DROP USER IF EXISTS имя_пользователя;

В более ранних версиях MySQL требовалось две отдельные команды:

GRANT USAGE ON *.* TO 'имя_пользователя'@'хост_или_IP_адрес';
DROP USER 'имя_пользователя'@'хост_или_IP_адрес';

К сожалению, в данном случае сообщение о выполнении операции в консоли MySQL традиционно малоинформативно 🙁 Поэтому, чтобы узнать, что удаление пользователя пользователь MySQL всё-таки произошло, можно воспользоваться следующей командой, которая выводит на экран список всех существующих на текущем MySQL сервере пользователей:

SELECT User FROM mysql.user;

Данный вариант команды выведет только имена пользователей. Если же вам нужно будет посмотреть хосты, с которых пользователи могут коннектиться к серверу, и список привилегий, то можете вызвать команду в следующем виде:

SELECT User, Host, Grant_priv FROM mysql.user;

Также в таблице mysql.user существует масса других полей, в которых хранятся другие типы привилегий и прочая информация, с полным списком которой можно познакомиться здесь — https://mariadb.com/kb/en/library/mysqluser-table/

Не смущайтесь, что это документация к СУБД MariaDB. Технически это то же самое, что и MySQL, т.к. MariaDB является всего лишь её ответвлением или форком от англ «fork» — ответвление, вилка.

Зачем её было делать — снова без понятия 🙂 Разве что, чтобы всем гордо заявлять, что «у меня своя СУБД»?.. Но о точных мотивах и различиях MySQL и MariaDB я, честно говоря, не сильно в курсе. Поэтому если вы что-то об этом знаете — было бы интересно прочитать об этом в комментариях.

Выход из консоли MySQL

Вот и всё, жизненный цикл БД и пользователя, начавшийся их созданием и завершившийся удалением подошёл к концу. Следовательно, командная строка MySQL, с которой мы работали в консоли сервера с помощью утилиты mysql, нам больше не нужна.

Осталось только выйти из неё…

Казалось бы, действие тривиальное, но многие в ситуации, когда нужно выйти из командной строки MySQL просто закрывают консоль сервера и открывают её снова. Можно, конечно, и так действовать, но это отнимает лишние секунды рабочего времени и заставляет раздражаться каждый раз, когда это сделать необходимо.

Правильным поведением в данной ситуации будет всего лишь вызов команды exit в командной строке MySQL, после чего сервис с нами вежливо попрощается 🙂

И всего-то 🙂 Так что в следующий раз не нужно плодить консоли и переоткрывать их каждый раз, когда нужно будет выйти из MySQL в консоли и снова получить к ней доступ для управления сервером.

Вот и всё, что я хотел вам сегодня рассказать. Надеюсь, что моя шпаргалка по работе с командной строкой MySQL через консоль пригодится не только мне, но и вам.

Говоря о практическом применении изложенной в статье информации хочется напомнить, что приведёнными конструкциями можно пользоваться не только в командной строке MySQL и консоли сервера, но и в консоли phpMyAdmin и прочего ПО, которое предоставляет такую возможность.

Пользоваться вам ею или нет — ваше дело. Но, как я сказал в самом начале статьи, бывают ситуации, когда использование консоли MySQL может сэкономить ваше время и нервы. А пользоваться ли командной строкой каждый день или нет — дело исключительно вкуса и индивидуальных предпочтений.

Пишите комментариях о том, какие команды вы используете чаще всего. А, возможно, вы знаете ещё какие-то хитрости. В любом случае делитесь своим мнением с остальными и поддерживайте беседы в комментариях других пользователей.

Обещаю, что в процессе вы узнаете много нового, как и я при написании каждой своей статьи 🙂

Если статья вам понравилась, в качестве благодарности можете поделиться ею со своими друзьями в социальных сетях или же помочь проекту материально с помощью формы под статьёй.

На этом всё! Удачи и до новых встреч 🙂

Постоянные читатели журнала, возможно, заметили, что я стал чаще писать о сценариях и некоторых развивающихся платформах и инструментах для взаимодействия с Microsoft SQL Server. Тому есть объяснение, но пока я не могу вдаваться в подробности. Достаточно сказать, что нас ждет дивный новый мир SQL Server на Linux, в котором взаимодействие с нашими экземплярами не ограничено средой Microsoft SQL Server Management Studio.

В этой статье речь пойдет об SQLCMD — инструменте командной строки, который используется для взаимодействия с Microsoft SQL Server.

Это не новый инструмент, он появился несколько десятков лет назад. Вы можете запускать SQLCMD не только из приложения командной строки Command, встроенного в Windows, но и из среды SQL Server Management Studio (SSMS).

Многие новые инструменты и методы взаимодействия с Microsoft SQL Server основаны на синтаксисе, используемом SQLCMD. Я взялся за эту ознакомительную статью, чтобы помочь вам разобраться в следующих вопросах:

1. Запуск SQLCMD из командной строки.
2. Основной синтаксис для подключения к Microsoft SQL Server с использованием SQLCMD.
3. Основной синтаксис для передачи запросов в экземпляр Microsoft SQL Server с помощью SQLCMD.
4. Выполнение запросов с использованием SQLCMD из среды SQL Server Management Studio.

Запуск SQLCMD из командной строки

Прежде чем подключиться к Microsoft SQL Server с помощью SQLCMD, необходимо запустить его. Конечно, сделать это очень просто, но, как вы знаете, мой подход заключается в том, чтобы провести вас по всем шагам от А до Я, не пропуская ни одного действия и не предполагая, что вам известны даже самые простые этапы процесса. Всегда досадно, когда не удается выполнить описанные в инструкции действия из-за отсутствия некоторых деталей. В своих статьях я стараюсь избежать таких упущений. В данном случае запустить SQLCMD настолько просто, что мне, вероятно, потребовалось в пять раз больше места, чтобы объяснить, почему я подробно разбираю столь элементарные вещи, чем на описание самого процесса.

Итак, нажмите комбинацию клавиш Windows + R, чтобы открыть окно выполнения, введите cmd, чтобы запустить приложение Command, а затем введите sqlcmd в ответ на приглашение и следом одну из многочисленных возможных комбинаций строк подключения, которые описаны ниже.

После успешного подключения вы получите приглашение вида «1>», чтобы начать взаимодействие с SQLCMD. При необходимости можно предоставить запрос как часть начального подключения для немедленной обработки, в соответствии с описанием, приведенным далее в статье.

Базовый синтаксис SQLCMD для установки подключения SQL Server

Как инструмент командной строки, SQLCMD представляет собой комбинацию команд и параметров. Ниже перечислены наиболее распространенные параметры, необходимые для организации подключения к экземпляру SQL Server, на котором выполняются инструкции.

Параметр сервера

• -S: указывает имя сервера или имя сервера/экземпляра в случае именованного экземпляра.

Параметры проверки подлинности

• -U: указывает имя пользователя при использовании проверки подлинности SQL Server;
• -P: пароль для пользователя, указанного в параметре -U;
• -E: обозначает использование доверенной проверки подлинности (Active Directory). Этот параметр выбран по умолчанию и не обязательно вводится при использовании доверенной проверки подлинности. Ниже приведены два примера инструкций подключения с помощью SQLCMD.

Сначала выполняется доверенная проверка подлинности для экземпляра по умолчанию с последующим именованным экземпляром:

sqlcmd -S <имя сервера>
sqlcmd -S <имя сервера>\
   <имя экземпляра>

При использовании доверенной проверки подлинности параметры -S остаются идентичными, достаточно просто указать имя пользователя и пароль:

sqlcmd -S <имя сервера > -U
   <имя пользователя > -P <пароль>
sqlcmd -S <имя сервера>\
   <имя экземпляра>
   -U <имя пользователя> -P <пароль>

При использовании проверки подлинности SQL Server можно опустить параметр -P в целях безопасности, и тогда во время выполнения будет запрошен пароль.

Параметры ввода

Существует два способа передать запрос в экземпляр SQL Server: как входной файл или нерегламентированный запрос.

• -Q или -q: текст запроса, передаваемого в экземпляр SQL Server;
• -i: предоставляет полный путь и имя файла, обрабатываемого экземпляром SQL Server.

Параметры вывода

• -o: предоставляет полный путь и имя файла для публикации результатов запроса.

В действительности список параметров гораздо больше. Чтобы получить дополнительные сведения, введите команду

sqlcmd -?

Две команды, на которые следует обратить внимание, управляют запуском скрипта и выходом из SQLCMD.

• GO: в отличие от знакомого вам аналога T-SQL, GO — не просто инструкция завершения пакета, а команда, выполняющая команду в SQLCMD.
• EXIT: эта команда завершает текущий сеанс SQLCMD. Впоследствии, чтобы установить активный сеанс, вам потребуется повторно ввести сведения о подключении.

Помните, что для всех перечисленных параметров используется синтаксис, обеспечивающий одновременный запуск SQLCMD и подключение к экземпляру SQL Server. Если вы уже запустили SQLCMD и получили приглашение «1>», то можно избавиться от инструкции sqlcmd в начале всех приведенных выше фрагментов программного кода.

Базовый синтаксис SQLCMD для выполнения запросов

Используя лишь эти несколько параметров, с помощью SQLCMD можно решать почти любые задачи. Рассмотрим три различных варианта применения описанных выше параметров.

Сценарий 1. Подключить экземпляр SQL Server по умолчанию с использованием проверки подлинности SQL и выполнить команду, которая составляет список имен всех баз данных в выходном файле:

sqlcmd -S <имя сервера> -U
   <имя пользователя> -P <пароль>
   -i  "SELECT name FROM master;"
   -o c:\temp\db.txt

Сценарий 2. Подключить именованный экземпляр SQL Server с использованием доверенной проверки подлинности и выполнить последовательность команд из входного файла, отображая результаты команды в выходном файле:

sqlcmd -S <имя сервера>\
   <имя экземпляра> -i
   C:\temp\sqlscript.sql -o c:\temp\db.txt

Сценарий 3. Подключить экземпляр SQL Server по умолчанию с использованием доверенной проверки подлинности и выполнить команду, которая выводит список имен всех баз данных непосредственно на экран:

sqlcmd -S <имя сервера > -i
   «SELECT name FROM master;»

Использование SQLCMD из среды SQL Server Management Studio

Теперь, когда мы рассмотрели основные особенности подключения к SQLCMD и выполнения запросов из командной строки, обратим внимание на возможность выполнять форматированные скрипты SQLCMD из среды SQL Server Management Studio. Это превосходный способ продолжить использование уникальных функций сценариев, о которых я подробнее расскажу в следующей статье на тему SQLCMD, когда нужно запустить один скрипт, охватывающий несколько экземпляров последовательно.

Откройте новое окно запроса в среде SQL Server Management Studio. Необходимо поставить SSMS в известность, что этот запрос будет выполняться с помощью SQLCMD. Для этого перейдите к строке меню в верхней части экрана и выберите Query («Запрос»), а затем SQLCMD Mode («Режим SQLCMD»).

Выполним простое подключение к серверу. Сделать это можно с использованием доверенной проверки подлинности и простой команды:

: CONNECT <имя сервера>

Запрос немедленно отключается от экземпляра SQL после завершения выделенного вами текста запроса при его выполнении или полного содержимого запроса.

Нечто подобное приведенному ниже варианту демонстрирует возможность подключиться к экземпляру SQL Server (в данном случае используется локальный экземпляр SQL Server) и возвратить имя сервера и список всех баз данных на экземпляре:

: Connect.
SELECT @@SERVERNAME, name
FROM master.sys.databases
ORDER BY name;

Конечно, это упрощенный подход с точки зрения запроса, но моя задача — рассмотреть различные варианты подготовки сценариев и использования SQLCMD. В следующей статье я расскажу об использовании SQLCMD в среде SSMS, что значительно облегчает работу со скриптами, требующими нескольких подключений для выполнения задачи.

Итак, теперь вы можете использовать SQLCMD из приложения Command операционной системы Windows или из среды SQL Server Management Studio. Кроме того, вы можете организовать подключение всего с несколькими необходимыми параметрами, знакомыми каждому, кто хотя бы немного работал с SQL Server, и передать запрос в SQL Server через SQLCMD для обработки.

Время прочтения: 4 мин.

В практике аудита использование СУБД SQL Server для хранения и анализа данных – одно из непременных условий, существенно повышающих качество проводимой работы.

А обслуживание базы данных, включающее, в том числе, резервное копирование данных, крайне важно для обеспечения ее бесперебойной работы.

В этой статье я поделюсь альтернативным способом того, как мы автоматизировали создание ежедневной резервной копии базы данных для обеспечения возможности восстановления данных в случае какого-либо инцидента. Мы сделали это с помощью служебной программы SQLCMD без запуска SQL Server Management Studio.

Но для начала немного простых примеров использования sqlcmd (все данные тестовые).

Посмотрим, как это работает в интерактивном режиме.

Запускаем командную строку Windows – cmd.

Чтобы подключиться к именованному экземпляру SQL Server, укажем имя сервера и имя экземпляра SQL Server (в примере соответственно DESKTOP\SQLEXPRESS), с которым необходимо соединиться.

sqlcmd -S DESKTOP\SQLEXPRESS –E

где:

–S указывает на server\instance_name;

–E –trusted connection (доверительное соединение).

Отображаемая в командной строке цифра 1> означает, что подключение состоялось и есть готовность принимать запросы для исполнения.

Если ранее при создании пользователя SQL Server для пользователя была включена аутентификацию SQL Server,

то при подключении требуется указать имя пользователя и ввести его пароль.

Чтобы это выполнить, предварительно необходимо выйти из sqlcmd и заново войти с указанием учетных данных:

sqlcmd -S DESKTOP\SQLEXPRESS –U DB_User 

Командная строка запросит пароль для пользователя DB_User, который необходимо ввести с клавиатуры.

При желании можно указать пароль в командной строке, хотя это не рекомендуется по соображениям безопасности:

sqlcmd -S DESKTOP\SQLEXPRESS –U DB_User –P 12345678

Давайте теперь проверим текущую базу данных.

При создании входа в SQL Server  можно определить базу данных, в которую необходимо войти, по умолчанию. Если она не указана, то базой данных по умолчанию будет является основная база данных (master).

select db_name()
go

Следующий пример использования sqlcmd – вывод списка баз данных в экземпляре SQL:

select name from sys.databases
go

Теперь давайте посмотрим на запуск sqlcmd в командном режиме.

Получим список таблиц в базе данных db1, выведя его в файл tables.txt, используя в sqlcmd подготовленный заранее и сохраненный на жестком диске ПК, сценарий tablesList.sql со следующим содержанием:

select 	table_name from db1.information_schema.tables;

Затем вызовем sqlcmd для выполнения созданного сценария tablesList.sql:

sqlcmd -S DESKTOP\SQLEXPRESS -E -iC:\Users\User\SQL\Scripts\tablesList.sql -oC:\Users\User\SQL\tables.txt

где:

—i используется для указания ввода,  указывается файл сценария, в примере это сохраненный tablesList.sql;
—o используется для отображения результатов вывода в файл, в примере это tables.txt

В tables.txt отображается следующий результат (для примера выбрана тестовая база данных, состоящая из одной таблицы):

Теперь вернемся к нашей задаче автоматизации резервного копирования с использованием sqlcmd и планировщика задач Windows.

Во-первых, создадим файл сценария резервного копирования базы данных db1 и сохраним его в файле backup.sql:

declare @Backup nvarchar(400)
set @Backup=concat('C:\Users\User\SQL\Backup\db1-',(format(getdate(),'yyyy-MM-dd-hh-mm-ss-tt')),'.bak')
backup database [db1] to disk=@Backup
with noformat, noinit,  name = n'db1-Backup', skip, norewind, nounload, stats=10
go

Файл резервной копии в результате выполнения этого сценария сохраняется в каталог C:\Users\User\SQL\Backup\.

Далее создаем командный файл run-sqlcmd-backup.bat следующего содержания:

@Echo off
sqlcmd -S DESKTOP\SQLEXPRESS -E -iC:\Users\User\SQL\Scripts\backup.sql

И, наконец, открываем Планировщик заданий Windows (подробнее о планировщике заданий Windows можно прочесть в официальных документах Microsoft по этой тематике), для того, чтобы создать задачу резервного копирования базы данных db1, запускающую командный файл run-sqlcmd-backup.bat по необходимому расписанию.

В итоге с помощью sqlcmd мы получили регулярное резервное копирование выбранной нами базы данных.

Обычно утилита sqlcmd не так часто используется, если сравнивать с SQL Server Management Studio (SSMS). У меня есть коллеги, которые даже не знают о ее существовании. Причина в том, что SSMS весьма сильный инструмент, который является предпочтительным выбором для ежедневной работы, но есть случаи, когда sqlcmd оказывается наилучшим вариантом.

Особенно, когда вы работаете с SQL Server на Linux. Обычно больше компании держат серверы критических баз данных в демилитаризованных зонах (DMZ), поэтому маловероятно, чтобы вы могли подключиться к этим серверам с помощью SSMS с вашей рабочей станции. Другими словами, вам может потребоваться доступ к серверу с помощью клиента ssh и либо выполнять то, что вам нужно с помощью sqlcmd, либо устанавливать туннель и подключаться с помощью SSMS.

Что такое sqlcmd?

Sqlcmd — это утилита, которая устанавливается с клиентским инструментарием при установке SQL Server. Этот инструмент позволяет выполнять операторы Transact-SQL, хранимые процедуры и файлы скриптов из консоли. Он также позволяет использовать предопределенные системные переменные и переменные, определенные пользователем. Как вы можете догадаться, то, что sqlcmd позволяет выполнять хранимые процедуры, делает возможным выполнять задания SQL Server Agent.

Другим замечательным свойством sqlcmd является возможность подключения к любой версии SQL Server, чего вы не всегда можете сделать с помощью SSMS. Причина в том, что sqlcmd использует драйвер ODBC, а не .NET System.Data.SqlClient Namespace, которое является частью ADO.NET, используемой SSMS.

Подключение sqlcmd к экземпляру по умолчанию SQL Server

Если вы подключаетесь к локальному серверу, используя Windows Authentication (проверка подлинности Windows), и текущий пользователь имеет права доступа, то вы можете просто вызвать sqlcmd в командной строке, как показано ниже, и вы подключитесь к экземпляру по умолчанию.

sqlcmd

Если вам даны подходящие разрешения, вы увидите приглашение sqlcmd, как на скриншоте ниже.

1> означает номер строки, который будет возрастать всякий раз, когда вы нажимаете клавишу ENTER и сбрасываться в 1, когда вы введете разделитель пакетов GO, как показано ниже.

На рисунке выше видно, что номер строк возрастает, пока не будет введена инструкция GO, после чего приглашение sqlcmd вернется к строке 1 после выполнения кода.

Подключение sqlcmd к экземпляру SQL Server, используя проверку подлинности Windows

Если ваша организация использует Active Directory, вы можете использовать Trusted Authentication (доверительная аутентификация) для подключения к удаленному или локальному серверу. Вам просто нужно указать сервер и имя экземпляра с помощью параметра -S и -E для использования Trusted Authentication. Тем, кто не знает, что такое Trusted Authentication, скажу, что это способ, с помощью которого Microsoft ссылается на использование аккаунта учетной записи Windows для аутентификации пользователя в разных приложениях.

Вот как можно подключиться к экземпляру. Подставьте имя своего сервера и имя экземпляра, который вам нужен.

sqlcmd -S [SERVERNAME\INSTANCE] -E

Подключение sqlcmd к экземпляру SQL Server, используя аутентификацию SQL Server

Если вам нужно подключиться с помощью аутентификации SQL Server, укажите также параметр -U с именем пользователя и параметр -P с паролем пользователя, как показано ниже. Опять таки, подставьте свои значения этих параметров.

sqlcmd -S [SERVERNAME\INSTANCE] –U [username] –P [password]

На следующем скриншоте можно увидеть, что я подключился к удаленному экземпляру SQL Server, работающему на Linux с помощью аутентификации SQL Server.

Подключение sqlcmd к экземпляру SQL Server с помощью выделенного подключения администратора (DAC)

Бывают обстоятельства, когда вы не можете подключиться к SQL Server, например, из-за нехватки ресурсов. SQL Server имеет планировщик, предназначенный для обеспечения доступа только одному подключению во всех случаях. Это и есть выделенное подключение администратора (DAC). Вы не можете подключиться к DAC, как вы делаете это обычно с помощью SQL Server Management Studio, поскольку SSMS использует два подключения — одно для браузера объектов, а другое для окна запросов. В подобных случаях sqlcmd является одним из лучших вариантов для подключения к экземпляру SQL Server и выполнения необходимых действий, чтобы вернуть экземпляр в нормальное состояние.

Для подключения к экземпляру SQL Server с помощью DAC в sqlcmd, вам потребуется добавить параметр -A.

Если вы подключаетесь с логином Windows, то вызов sqlcmd будет таким:

sqlcmd -S [SERVERNAME\INSTANCE] –E -A

Если вы хотите использовать логин SQL Server, то вы должны вызывать sqlcmd так:

sqlcmd -S [SERVERNAME\INSTANCE] –U [username] –P [password] -A