Как заполнить таблицу mysql через консоль
Обновлено: 02.07.2024
Для подключения к серверу MySQL используется такая команда:
Если сервер MySQL находится на удалённом хосте – его можно указать с помощью ключа -h:
Очень полезная команда – status, которая выводит информацию об используемой версии MySQL и многое другое:
Теперь – примеры использования некоторых SQL-функций.
К примеру – выведем текущую дату, пользователя и версию MySQL:
Если в процессе вы передумали продолжать набор команд и хотите завершить набор – укажите с:
Что бы отобразить список доступных баз – используйте команду:
Что бы переключиться на использование определённой базы – используйте use:
Что бы проверить, какая в данный момент база используется – выполните:
Просмотреть содержащиеся в ней таблицы можно командой:
В базе base1 имеется только одна таблица tablename.
Всё то же самое можно сделать и не подключаясь к серверу MySQL, а напрямую из консоли сервера. Например – просмотреть список имеющихся на сервере баз:
А просмотреть имеющиеся в базе данных MySQL таблицы из консоли можно так:
Посмотреть содержимое таблицы из консоли можно следующим образом:
$ mysqlshow base1 tablename -u root -p
Выполнять запросы SQL можно как из консоли сервера, так и из консоли клиента mysql. Так же, запросы можно вводить не напрямую, а из файла. К примеру, имеется файл с таким содержимым:
Что бы выполнить этот запрос из консоли сервера – выполните:
А из клиента mysql – так:
При этом, файл должен находится в каталоге, в котором вы находились перед подключением к mysql-клиенту.
Теперь – подключимся под пользователем root и создадим базу test1:
Далее – создадим пользователя user1, дадим ему права на доступ к базе test1 и установим пароль mypasswd:
mysql> grant all on test1.* to ‘user1’@’localhost’ identified by ‘mypasswd’;
Теперь – подключимся к серверу под новым пользователем и сразу переключимся на базу test1, что бы избежать ввода команды use database, после чего сразу проверим используемую базу с помощью функции select database():
При создании таблицы используется такой синтаксис:
имя_столбца ТИП(значение) АТРИБУТ
Атрибуты могут иметь значение либо NULL (может быть пустым), либо NOT NULL — обязательно должен быть заполнен.
В примере будут использоваться такие типы столбцов:
VARCHAR – подразумевает, что в столбце будут использоваться символьные данные – текст – переменной длины, в (значение) указывается количество знаков, которое может быть использовано в этом столбце;
DATE – подразумевает использование даты в формате YYYY-MM-DD;
INT — только целые числа, без дробей;
UNSIGNED — только положительные значения;
AUTO_INCREMENT — автоматически задаст значение, на единицу большее существующего;
ENUM — столбец перечисляемого типа, может принимать значения, указанные в его атрибутах, в примере это (‘Y’, ‘N’).
PRIMARY KEY указывает, что указанные столбцы являются первичным ключом.
И – пример создания таблицы списка контактов, имена столбцов достаточно красноречивы, что бы можно было определить их назначение и выдвигаемые к столбцам требования:
Что бы просмотреть созданную таблицу – выполним:
Если таблица создана ошибочно, или больше не нужна – удалить её можно командой:
Следующая команда соединит нас с БД devdb , имя пользователя devuser и пароль mysecretpwd .
Мы будем вводить данные в таблицу employee . Структура таблицы приведена ниже:
Пример базового использования команды INSERT
Следующая команда добавит новую запись в таблицу. После ключевого слово VALUES указываются значения столбцов таблицы:
Используйте команду SELECT для проверки введенных данных.
Вставка данных только для определенных столбцов
Если вы хотите вставить данные только в несколько столбцов, то их следует перечислить. Следующая команда вставит значения только в столбцы id и name .
Мы не указали значения полей dept и salary. В итоге, мы получили NULL в этих столбцах. Обратите внимание, это не строковое значение “NULL”, это значение, указывающие на то, что ячейка пуста.
Пример Insert Set
Вместо ключевого слова values вы можете использовать слово SET для установки значения нужному полю.
Следующая команда является полным аналогом той, что мы рассмотрели выше:
Вставка значений на основе значений из другой таблицы
В этом примере мы используем INSERT. SELECT метод, для выбора значений из другой таблицы.
Следующая команда копирует значения из таблицы contractor в employee .
Также вы можете комбинировать условия WHERE , для выбора определенных полей.
Внимание: Если вы работаете с БД Oracle, то используйте синтаксис insert into employee AS select * from contractor . MySQL не поддерживает ключевого слова AS в данном контексте.
Ввод выбранных столбцов из другой таблицы
Конечно, вы можете выбрать определенный набор столбцов для ввода их значений в другую таблицу.
Следующая команда выбирает поля id и name всех записей из таблицы contractor и вводит их в таблицу employee .
Также допускается использование условий WHERE.
Обратите внимание, если запись с указанным ID уже существует, вы получит ошибку. ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
Ввод записей в определенную секцию
Если вы создавали таблицу разбитую на разделы по диапазону записей, то вы можете указать определенный раздел для записи.
В следующем примере данные вводятся в раздел p1 .
Обратите внимание, что запись с этим ID уже существует в указанном разделе. Поэтому мы получим соответствующую ошибку.
Внимание: Этот пример отработает только в MySQL 5.6 и выше.
Вставка записей в несколько разделов таблицы
Вы можете при помощи одной команды вставить записи сразу в несколько разделов таблицы. Следующий запрос вставит данные в разделы p1 и p2 .
Обратите внимание, если по какой-либо причине одна из записей вызовет ошибку при обработке, то весь запрос будет отменен.
Снова, пример отработает только в MySQL 5.6 и выше.
Игнорирование ошибок при вводе данных
Если по каким-либо причинам вам необходимо пропускать ошибки при вводе данных, то вы можете использовать ключевое слово IGNORE .
К примеру, следующая команда вызовет ошибку, так как запись с таким ID уже существует.
Чтобы пропускать подобные ошибки вы можете обратиться в команде insert ignore . Помните, что запись не будет внесена в таблицу. Вы просто игнорируете возникающую ошибку.
Значение по-умолчанию
Если MySQL запущен в строгом режиме, то, не указав значения по-умолчанию для полей, мы получим ошибки.
Если же строгий режим отключен (по-умолчанию), то не указав значение для поля, будет использовано значение по-умолчанию для поля уровня таблицы.
Например, в таблице bonus оба поля не могут принимать значение NULL.
Введем значение только для id.
При выборке данных, мы видим, что поле amount приняло значение 0.
Если мы опустим оба поля при вводе данных, то они примут значение 0. Если поля не указаны при вводе, то они принимают значения по-умолчанию для таблицы.
Внимание: допускается применения ключевого слова DEFAULT . Результат выполнения запроса идентичен предыдущему.
Для строковых полей значение по-умолчанию - пустая строка. Обратите внимание, что для полей AUTO_INCREMENT значение по-умолчанию - увеличенное на единицу значение предыдущей записи.
Выражения в качестве значение в INSERT
В следующем примере, в качестве значения поля bonus мы указали 5000+id . В результате поле bonus будет равно сумме размера бонуса и ID .
Допускается использование + , - , * , или любого другого корректного оператора MySQL. В следующем примере используется 50*2 в качестве значения ID. В итоге ID примет значение 100 .
Вы также можете обращаться к значения других столбцов. Например, 5000+id для поля bonus . То есть мы используем значение столбца id (100) и добавляем его к 5000. Результирующее значение - 5100.
Изменение приоритета команды INSERT
При работе с движками, допускающими блокирование таблиц (MyISAM), допускается указывать приоритет команде INSERT .
Например, следующая команда откладывает операцию INSERT до тех пор, пока сервер не обработает все операции чтения над таблицей.
Также можно указывать высокий приоритет. Это операции полностью противоположна предыдущей.
Не забывайте, если ваша таблица обрабатывает много операций чтения, то установив низкий приоритет, операция записи может не отработать в течение довольно долгого периода времени.
Обратите внимание, что команда очень похожа на INSERT. DELAY , которая считается устаревшей начиная с MySQL версии 5.6.6, поэтому стоит избегать её использования.
Обновления строки при повторении
Если в процессе записи будет обнаружена запись с повторяющимся значением ID, операция будет остановлена с соответствующей ошибкой.
Но, вы можете обновить поля такой записи (при обнаружении дубликата) при помощи команды ON DUPLICATE KEY UPDATE .
В примере ниже мы обновляем значение поля salary , при возникновении дубликата записи, увеличив его на 500 .
Обратите внимание, что, хотя в предыдущем примере мы добавляем только одну строку, мы все равно получаем в ответ 2 rows affected .
Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:
id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).
Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.
id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)
Итак, таблица готова, и ее окончательный вариант выглядит так:
create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);
Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:
FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);
Укажем, что id_author - внешний ключ:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)
Таблица готова, и ее окончательный вариант выглядит так:
create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,
id_author int (10) NOT NULL,
PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);
create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);
Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.
Запускаем сервер MySQL (Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:
Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.
Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе, например в Блокноте, а затем копировать и вставлять их в черное окошко.
Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:
И, наконец, посмотрим структуру нашей последней таблицы posts:
Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ('да', 'нет'), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:
married enum ('да', 'нет') NOT NULL default('да')
Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.
Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.
Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:
Давайте попробуем внести в нашу таблицу users следующие значения:
Второй вариант используется для внесения данных в некоторые поля таблицы:
В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:
Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:
Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):
Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то не существует):
Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.
Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Если на экране появляется приветствие mysql, то всё прошло ok.
Для выхода из командной строки mysql можно написать exit, в linux достаточно нажать ctrl+C. Теперь можно писать любые mysql-запросы. Чтобы запрос выполнился, в конце обязательно нужно поставит точку с запятой ";". При написании длинных запросов удобно делать enter`ом переход на следующую строку. А после полного написание запроса поставить ";" и нажать "enter".
Для работы с конкретной базой данных нужно её выбрать с помощью оператора use. Показать все базы данных:
Результатом будет что то вроде этого:
Выбрать базу данных jeka:
Посмотреть названия всех таблиц в конкретной базе данных (после выполнения use):
Просмотреть структуру таблицы (имена и тип полей):
Достать информацию о юзере с >
Бэкап структуры таблицы, а точнее sql-запрос на её создание
Если в конце запроса поставить не ; а \G, то результат будет показан другим образом - иногда весьма удобно, если таблица имеет много полей:
Посмотреть, какие запросы выполняются в данный момент на сервере MySQL:
Учтите, что будут отображены еще не выполненные (активные) запросы, поэтому show processlist; актуальная команды только для нагруженных БД, в других случаях маловероятно, что команда что то покажет.
Подробнее о том, как изменять структуру mysql-таблиц (alter table).
Что делать если вижу знаки вопросов вместо текста в MySQL?
Это проблема с кодировкой. Вероятно в my.ini файле mysql-сервера не добавлено:
Нужно добавить в .ini файл либо выполнить в консоли команду "SET NAMES utf8", после чего во время текущей сессии отображаться русский текст в mysql-консоли будет без проблем:
Как сделать бэкап базы mysql через консоль Linux?
Сделать бэкап базы database в файл dump_name.sql
[username] – имя пользователя который имеет доступ к базе данных.
[password] – пароль пользователя.
[database] – имя базы данных.
[dump_name.sql] – имя файла, дампа на выходе.
Развернуть базу из файла через командную строку
[username] – имя пользователя который имеет доступ к базе данных.
[password] – пароль пользователя.
[database] – имя базы данных.
[dump_to_restore.sql] – имя файла, из которого поднимаем базу
Сделать дамп структуры одной таблицы mysql (без данных):
Например, задампим таблицу users из базы данных mydatabase:
Развернуть mysql-dump в БД с именем database_name:
Ещё одна интересная штука. Выполнять из консоли, не требуется предварительно подключаться к mysql (mysql>). Вывести список таблиц базы данных, не подключаясь непосредственно к mysql:
Заключение
Может показаться, что писать запросы в командной строке не удобно. Но это заблуждение. На слабых машинах phpmyadmin работает не так шустро как хотелось бы. Тем более не на всех серверах стоят морды с phpmyadmin, adminer и т.д. Linux + mysql в терминале реально повысили мою производительность.
Поисковые запросы , по которым приходили пользователи
- Как добавить AI в существующую таблицу MySQL? 2 ответа
- В чём разница int(3) int(10) int(12) и так далее? mysql 3 ответа
- mysqldump только структуры таблиц 1 ответ
- mysqldump: Got error: 1044: Access denied for user when using LOCK TABLES 1 ответ
- ERROR 9006: ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 11 Решено!
- MySQL - изменение полей, структуры таблицы 0 комментариев
- Запросы инъекции для mysql 0 комментариев
- Доступ к mysql из-вне3 комментария
- Mysql перенос таблиц из одной базы данных в другую 0 комментариев
- Анализ производительности MySQL с использованием performance_schema 0 комментариев
Комментарии 2
Viktor, спасибо. Судя по скринам, штука интересная.
Правда, уж больно много зависимостей она имеет.. Как решу проблему с зависимостями, отпишусь.
Читайте также: