Основные команды SQL, которые должен знать каждый программист

mysql -u root -p

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

CREATE TABLE <table_name1> ( <col_name1><col_type1>, <col_name2><col_type2>, <col_name3><col_type3> PRIMARY KEY(<col_name1>), FOREIGN KEY(<col_name2>) REFERENCES <table_name2>(<col_name2>)
);

CREATE TABLE instructor ( ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …) VALUES (<value1>, <value2>, <value3>, …); 

INSERT INTO <table_name> VALUES (<value1>, <value2>, <value3>, …);

UPDATE <table_name> SET <col_name1> = <value1>, <col_name2> = <value2>, ... WHERE <condition>;

SELECT <col_name1>, <col_name2>, … FROM <table_name>; 

SELECT * FROM <table_name>;

SELECT DISTINCT <col_name1>, <col_name2>, … FROM <table_name>; 

SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <condition>; 

SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; 

SELECT <col_name1>, <col_name2>, … FROM <table_name> GROUP BY <col_namex>; 

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name; 

SELECT <col_name1>, <col_name2>, ... FROM <table_name> GROUP BY <column_namex> HAVING <condition> 

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1; 

SELECT <col_name1>, <col_name2>, … FROM <table_name> ORDER BY <col_name1>, <col_name2>, … ASC|DESC;

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namex> BETWEEN <value1> AND <value2>;

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000; 

SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namex> LIKE <pattern>; 

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___';

SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <col_namen> IN (<value1>, <value2>, …);

SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

SELECT <col_name1>, <col_name2>, … FROM <table_name1> JOIN <table_name2> ON <table_name1.col_namex> = <table2.col_namex>;

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

CREATE VIEW <view_name> AS SELECT <col_name1>, <col_name2>, … FROM <table_name> WHERE <condition>;

DROP VIEW <view_name>;

SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN ( SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010 ); 

От автора: опять заплутали, и не знаете, как зайти в MySQL? Не вы первый! Собирайте всех заблудившихся, и сейчас отправимся прямиком в гости к ней. К кому «к ней»? К самой популярной СУБД всемирной паутины!

Где в Денвере находится MySQL

Проходим к базам данных через «лабиринт»

Мы с вами отправимся в «дремучий» лес, и постараемся через него «пробраться» к серверу баз. Не подумайте, что мы от кого-то прячемся, просто сейчас мы будем действовать, как настоящие веб-разработчики. Поехали!

Перед тем, как зайти в MySQL через командную строку, следует выяснить, где находится главная директория сервера баз данных. Если вы используете СУБД в «чистом» виде без «примесей» (вне «джентльменского» набора), то корневая папка сервера расположена здесь: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe

Если вы используете Денвер, то искомый фал находится по этому пути: Z:\usr\local\mysql-5.5\bin\mysql.exe,

где Z – это виртуальный диск, который создается после запуска локального веб-сервера. У вас он может быть под другим именем (точнее, буквой).

Нас интересует именно файл mysql.exe. Запустим его на выполнение, указав имя сервера баз данных, пароль и пользователя, под которым зайдем в MySQL.

Как включить MySQL через командную строку: заходим в «Пуск», среди стандартных программ выбираем «Командная строка». Запускаем утилиту, и с этого момента опишем всю процедуру «хождения» в дремучем лесу пошагово, чтобы вся группа «заблудившихся» не потерялась еще раз 🙂 Стартуем:

В черное окно CMD (командной строки) вводим путь, где хранится исполняемый файл MySQL, а рядом указываем имя пользователя СУБД и пароль. Так как у нас локальный сервер, то мы вводим после идентификатора u имя пользователя root без пароля.

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

Вот весь кусок «кода»: Z:\usr\local\mysql-5.5\bin\mysql.exe -u root

Если все прошло успешно, то ниже в окне командной строки должна появиться информация о версии запущенного сервиса, год выпуска и другая «ненужная» для нас информация.

Теперь, чтобы проверить правильность подключения, введем после оператора mysql> запрос, который отобразит все базы данных хранящиеся на сервере. Код запроса:

Не забывайте в конце запроса ставить точку с запятой, иначе система выдаст сообщение об ошибке. После ввода запроса и нажатия на клавишу «Enter» мы получили список всех баз, хранящихся на сервере СУБД:

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

db1 – это имя нужной нам базы.

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

Копаем глубже!

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

В phpMySQL данный запрос был бы успешно выполнен и сервер возвратил выборку из всех строк, содержащихся в запрашиваемой таблице.

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

Что можно еще накопать?

Вглубь мы «докопались» до самого дна MySQL. Теперь давайте «копнем» немного в сторону. Какие еще сведения можно получить через командную строку, опрашивая MySQL? Хотите узнать структуру таблицы, типы данных всех столбцов, первичный ключ? Пожалуйста! Запустите на выполнение этот запрос:

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

Теперь вы знаете не только «секретные» тропы, но и все способы, как включить MySQL. А с таким «багажом» знаний точно не заблудитесь, потому что он не затруднит вашего передвижения, и даже ускорит его!

В данной статье соберу наиболее часто используемые команды для работы с MySQL

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

Ниже будет предствлено много команд, которые запускаются или из консоли сервера, или из консоли MySQL. Если команда начинается на mysql> — данная команда выполняется из консоли базы, т.е. после вхдоа в MySQL.

Команды рассматриваю исходя из того, что имеем дело с сервером на базе Centos (на данную операционную систему устанавливается веб-окружение 1С-Битрикс, с которым, преимущественно, я работаю).

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

service mysqld status

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

service mysqld start
service mysqld stop
service mysqld restart

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

mysql -u username -p

Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на удаленном хосте external.com

mysql -u username -p -h external.com

Работа с базами, таблицами – просмотр, удаление, редактирование записей

Создать базу данных на MySQL сервере:

mysql> CREATE DATABASE [databasename];

Показать список всех баз данных на сервере MySQL:

mysql> SHOW DATABASES;

Переключиться для работы с определенной базой данных:

mysql> USE [db name];

Отобразить все таблицы в базе данных:

mysql> SHOW TABLES;

Просмотреть формат таблицы в базе:

mysql> DESCRIBE [table name];

mysql> DROP DATABASE [database name];

Удалить таблицу из базы:

mysql> DROP TABLE [table name];

Показать все содержимое таблицы:

mysql> SELECT * FROM [table name];

Отобразить столбцы и содержимое столбцов в выбранной таблице:

mysql> SHOW COLUMNS FROM [table name];

Отобразить строки в определенной таблице, содержащие “whatever“:

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Отобразить все записи в определенной таблице, содержащие “Bob” и телефонный номер “3444444:

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Отобразить все записи, НЕ содержащие имя “Bob” и телефонный номер “3444444“, отсортированные по полю phone_number:

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Показать все записи, начинающиеся с букв ‘bob” и телефонного номера “3444444” в определенной таблице:

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Показать все записи, начинающиеся с букв ‘bob” и телефонного номера “3444444“, ограничиваясь записями с 1-ой до 5-ой:

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Использование регулярных выражений (“REGEXP BINARY”) для поиска записей. Например, для регистро-независимого поиска – найти все записи, начинающиеся с буквы А:

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Показать все уникальные записи:

mysql> SELECT DISTINCT [column name] FROM [table name];

Отобразить выбранные записи, отсортированные по возрастанию (asc) или убыванию (desc):

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Показать количество строк в таблице:

mysql> SELECT COUNT(*) FROM [table name];

Подсчитать количество столбцов в таблице:

mysql> SELECT SUM(*) FROM [table name];

mysql> ALTER TABLE [table name] DROP COLUMN [column name];

Добавление колонки в базу данных:

mysql> ALTER TABLE [table name] ADD COLUMN [new column name] varchar (20);

Изменение имени столбца:

mysql> ALTER TABLE [table name] CHANGE [old column name] [new column name] varchar (50);

Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:

mysql> ALTER TABLE [table name] ADD UNIQUE ([column name]);

Изменение размера столбца:

mysql> ALTER TABLE [table name] MODIFY [column name] VARCHAR(3);

Удаление столбца из таблицы:

mysql> alter table [table name] DROP INDEX [column name];

Загрузка файла CSV в таблицу:

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name]	FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);

Пользователи, пароли сервера MySQL – добавление, изменение пользователей и паролей

Создание нового пользователя – подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:

mysql -u root -p

mysql> USE mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username', PASSWORD('password'));
mysql> flush privileges;

Изменений пользовательского пароля из консоли на удаленном хосте external.com:

# mysqladmin -u username -h db1.example.org -p password 'new-password'

Изменение пользовательского пароля из консоли MySQL – подключение под root, обновление пароля, обновление привилегий:

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL.

# service mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# service  mysql stop
# service  mysql start

Обновление пароля root:

mysqladmin -u root -p oldpassword newpassword

Установка права на подключение к серверу с хоста localhost с паролем «passwd» — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:

mysql -u root -p

mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Установка привилегий пользователю на использование базы данных — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:

mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)	VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

Обновление информации в базе данных:

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y'	where [field name] = 'user';

Удаление строки в таблице:

mysql> DELETE from [table name] where [field name] = 'whatever';

Обновление привилегий в базе данных:

mysql> flush privileges;

Резервные копии – создание, восстановление

Создать резервную копию (dump) всех баз данных в файл alldatabases.sql:

mysqldump -u root -p password --opt >/tmp/alldatabases.sql

Создать резервную копию одной базы данных в файл databasename.sql:

mysqldump -u username -p password --databases databasename >/tmp/databasename.sql

Создать резервную копию одной таблицы в файл databasename.tablename.sql:

mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql

Восстановление базы данных (или таблицы) из резервной копии:

mysql -u[username] -p[password] [databasename] < /tmp/databasename.sql

Создание таблиц

Создать таблицу, пример 1:

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3),	lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8),	email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));

Создать таблицу, пример 2:

mysql> create table [table name] (personid INT(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,	firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default 'bato');

Примеры работы с MySQL

Показать текущую дату:

mysql> select now();

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

mysql> select now(),
-> user(),
-> version()
-> ;

Если в процессе вы передумали продолжать набор команд и хотите завершить набор – укажите с:

mysql> select now(),
-> c
mysql>

Что бы переключиться на использование определённой базы – используйте use:

mysql> use base1;
Database changed

Что бы проверить, какая в данный момент база используется – выполните:

mysql> select database();

Для добавления данных в таблицы используются несколько методов:

• с помощью оператора INSERT и значения VALUES;
• с помощью оператора INSERT и значения SET;
• с помощью оператора LOAD DATA;
• с помощью утилиты mysqlimport из файла, в консоли сервера.

Добавление с помощью оператора с помощью утилиты INSERT VALUES

Оператор INSERT имеет следующий синтаксис:

INSERT INTO tablename VALUES(value1, value2, value3…)

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

mysql> desc tablename;

Выделять значения можно как двойными кавычками ” “, так и одинарными – ‘ ‘. Столбцы, имеющие атрибут AUTO_INCREMENT заполняются значением NULL. Можно использовать множественный ввод для разных строк одной таблицы, указав значения через запятую:

INSERT INTO tablename VALUES(value1, value2, value3…), (value4, value5, value6…);

Добавление с помощью оператора SET

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

mysql> INSERT INTO main_socials SET entry_id=NULL, contact_id=”7“, vk_id=”https://vk.com/paibx“, facebook_id=”NULL”;
Query OK, 1 row affected (0.01 sec)

Однако, с помощью оператора SET нельзя вставлять несколько строк, в отличии от схемы INSERT VALUES.

Добавление из файла с помощью LOAD DATA

Столбцы в файле должны быть разделены табуляцией (не пробелом!). Использовать кавычки не нужно. Значения VALUES располагаются как и при обычном методе INSERT VALUES – по очереди имеющихся столбцов в таблице.

Например – содержимое файла main_list.txt:

cat main_list.txt
INSERT INTO main_list VALUES(NULL Name Gool +380XXXXXXXXX Town NULL NULL 222555888 NULL Y );
INSERT INTO main_list VALUES(NULL Name Trestor +380XXXXXXXXX Town NULL NULL NULL NULL Y );

mysql> LOAD DATA LOCAL INFILE “main_list.txt” into TABLE main_list;
Query OK, 2 rows affected, 7 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 7

Если сервер выдал ошибку такого плана:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

local-infile=1

и перезапустите сервер.

Добавление из файла с помощью mysqlimport

Фактически, mysqlimport просто выполняет оператор LOAD DATA на сервере.

Требования к файлу такие же, как и при использовании LOAD DATA и были описаны выше.

–debug-info использовать не обязательно, тут он просто для примера.

mysqlimport –local –debug-info -u setevoy -p contacts main_list.txt
Enter password:
contacts.main_list: Records: 2 Deleted: 0 Skipped: 0 Warnings: 7
User time 0.00, System time 0.00
Maximum resident set size 2804, Integral resident set size 1236
Non-physical pagefaults 201, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 6 out 6, Signals 0
Voluntary context switches 20, Involuntary context switches 7

День добрый, друзья! 🙂 Для подключения к mysql наберите

mysql -h 127.0.0.1 -u root -p 

• h — хост базы данных, если коннектимся к локальной базе (по ip 127.0.0.1), этот параметр можно опустить
• u — имя пользователя
• p — пароль, его нужно будет ввести после нажатия enter

Например, если нужно законнектиться к локальной быдухе под root`ом и пароль у него не установлен (пустой пароль — это плохо), достаточно написать

mysql -u root

Если на экране появляется приветствие mysql, то всё прошло ok.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 344
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Для выхода из командной строки mysql можно написать exit, в linux достаточно нажать ctrl+C. Теперь можно писать любые mysql-запросы. Чтобы запрос выполнился, в конце обязательно нужно поставит точку с запятой «;». При написании длинных запросов удобно делать enter`ом переход на следующую строку. А после полного написание запроса поставить «;» и нажать «enter».

Для работы с конкретной базой данных нужно её выбрать с помощью оператора use. Показать все базы данных:

show databases;

Результатом будет что то вроде этого:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jeka |
| linky |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
7 rows in set (0.00 sec)

Выбрать базу данных jeka:

mysql> use jeka
Database changed

Посмотреть названия всех таблиц в конкретной базе данных (после выполнения use):

mysql> show tables;

Просмотреть структуру таблицы (имена и тип полей):

mysql> describe post_estimations;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| post_id | int(10) unsigned | YES | MUL | NULL | |
| user_id | int(10) unsigned | YES | MUL | NULL | |
| estimation | float(2,1) unsigned | NO | | NULL | |
| date | int(10) unsigned | NO | | NULL | |
+------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Достать информацию о юзере с id=22:

mysql> SELECT * FROM users WHERE id=22;

Бэкап структуры таблицы, а точнее sql-запрос на её создание

show create table table_name;

Если в конце запроса поставить не ; а \G, то результат будет показан другим образом — иногда весьма удобно, если таблица имеет много полей:

select * from update__v4_errors where id = 1102003\G

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

show processlist;

Учтите, что будут отображены еще не выполненные (активные) запросы, поэтому show processlist; актуальная команды только для нагруженных БД, в других случаях маловероятно, что команда что то покажет.

Подробнее о том, как изменять структуру mysql-таблиц (alter table).

Что делать если вижу знаки вопросов вместо текста в MySQL?

Это проблема с кодировкой. Вероятно в my.ini файле mysql-сервера не добавлено:

[mysqld]
init-connect='SET NAMES utf8'

Нужно добавить в .ini файл либо выполнить в консоли команду «SET NAMES utf8», после чего во время текущей сессии отображаться русский текст в mysql-консоли будет без проблем:

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

Как сделать бэкап базы mysql через консоль Linux?

Сделать бэкап базы database в файл dump_name.sql

mysqldump -u [username] -p [password] [database] > [dump_name.sql]

Развернуть базу из файла через командную строку

mysql -u [username] -p [password] [database] < [dump_to_restore.sql]

Сделать дамп структуры одной таблицы mysql (без данных):

mysqldump -u[user] -p[password] -h[host] [database] [table_name] --no-data > /path/dump_name.sql

mysqldump -uroot mydatabase users > users.dump.sql

Развернуть mysql-dump в БД с именем database_name:

mysql -uroot database_name < users.dump.sql

Ещё одна интересная штука. Выполнять из консоли, не требуется предварительно подключаться к mysql (mysql>). Вывести список таблиц базы данных, не подключаясь непосредственно к mysql:

mysql -uroot -e 'show tables' database_name && echo done

mysql> show variables like '%incr%';

Небольшая памятка по работе с сервером MySQL из командной строки.

Получить информацию об установленной версии MySQL

mysql -V

Для подключения к mysql в консоли наберите команду

mysql -h you_sql_server -u user_name -p

• h — хост c MySQL. Если подключаемся с локальной машины, параметр можно опустить
• u — имя пользователя MySQL (root или другой пользователь MySQL)
• p — пароль, который будет предложено ввести после нажатия enter

Приглашение командной строки изменится, это значит, сервер MySQL ждёт от вас команд.

mysql>

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

Для отключения от MySQL нужно написать exit или (в unix-системах) нажать комбинацию клавиш ctrl+с.

Для вывода всех баз данных на сервере используйте команду show databases.

SHOW DATABASES;

Выберите нужную базу данных командой use.

USE db_name;

Теперь можно вводить запросы.

Чтобы подключиться к MySQL и сразу выбрать нужную базу

mysql -u user_name -h host_name db_name -p

Полезные команды MySQL

Показать все таблицы выбранной базы данных в текущей БД.

SHOW TABLES;

Показать все таблицы базы данных db_name.

SHOW TABLES FROM db_name;

Показать список столбцов в таблице table_name в текущей БД

SHOW COLUMNS FROM table_name;

Показать список столбцов в таблице table_name из БД db_name

SHOW COLUMNS FROM table_name FROM db_name;

Вывести структуру нужной таблицы

DESCRIBE table_name;

Показать структуру таблицы, будет выведен sql-запрос на её создание через «CREATE TABLE».

SHOW CREATE TABLE table_name;

Вывести значения системных переменных.

SHOW VARIABLES;

Показать список выполняющихся в настоящий момент запросов.

SHOW PROCESSLIST;

Общая статистика MySQL.

SHOW STATUS;

Статистика по всем таблицам в базе db_name.

SHOW TABLE STATUS FROM db_name;

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

mysql -uroot -e 'SHOW TABLES' db_name && echo done

Управление базами данных

Создание базы данных из консоли сервера

$ mysqladmin -u root -p create db_name

Удаление базы данных из консоли сервера

mysqladmin -u root -p drop db_name

Создание базы данных db_name из консоли MySQL

CREATE DATABASE db_name COLLATE utf8_general_ci;

Удаление базы данных db_name из консоли MySQL

DELETE DATABASE db_name;

Замена в поле одной подстроки на другую

UPDATE table SET field=replace(field,'original string','new string');

Работа с пользователями

Вывести список пользователей

SELECT User,Host FROM mysql.user;

SHOW GRANTS FOR user_name FROM db_name;

Создать нового пользователя

CREATE USER 'user'@'host' IDENTIFIED BY'password';

Чтобы создаваемый пользователь смог подключаться к серверу MySQL с любого IP адреса или хоста (за исключением localhost), можно использовать символ процента, вот так

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Чтобы разрешрешить пользователю подключаться вообще со всех хостов, придётся создать для него две учётные записи.

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX
ON db_name.* TO 'user'@'localhost';

Чтобы изменить права пользователю, иногда удобно сначала сбросить все права

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

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

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

REVOKEALLON db_name.* FROM 'user'@'localhost';

Сделать из пользователя суперпользователя и дать полный доступ ко всем БД на сервере

GRANT ALL ON *.* TO 'user'@'localhost';

DROP USER user@localhost;

FLUSH PRIVILEGES;

Изменить пароль пользователя в консоли MySQL

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='user_name';
FLUSH PRIVILEGES;
service mysqld restart;

$ mysqladmin -uuser_name password'password'

Исправление и оптимизация баз данных в MySQL

Чтобы исправить ошибки в поврежденной таблице, в консоли MySQL выполните:

> REPAIR TABLE table_name;

Если же в БД много поврежденных таблиц можно воспользоваться командой mysqlcheck.

Проверить db_name на ошибки.

$ mysqlcheck -p db_name

Восстановление и оптимизация всех БД

$ mysqlcheck -Aor -p

• p – использовать пароль
• -A, —all-databases – проверять все базы данных
• -o, —optimize – оптимизировать
• -r, —repair – восстанавливать повреждённые
• —auto-repair – автоматическое восстановление

Бэкап MySQL из командной строки

Экспорт базы MySQL

$ mysqldump -u username -p db_name > dump.sql

Дамп нескольких баз

$ mysqldump -u username -p -B db_name1 db_name2 > dump.sql

Дамп всех баз на сервере

$ mysqldump -u username -p -A > dump.sql

Дамп только структуры базы, без данных

$ mysqldump -u username -p --no-data db_name > database.sql

Дамп структуры одной таблицы mysql, без данных:

$ mysqldump -u username -p -h host db_name table_name --no-data > /path/dump.sql

Развернуть базу данных MySQL из дампа

$ mysql -u username -p db_name < dump_to_restore.sql

Клонирование таблиц

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

CREATE TABLE NEW_TableName LIKE OLD_TableName

Клонировать таблицы базы данных со всеми данными.

CREATE TABLE NEW_TableNameSELECT *FROM OLD_TableName

CREATE TABLE NEW_TableName
LIKE OLD_TableName
INSERT INTO NEW_TableName
SELECT *
FROM OLD_TableName;

На этом всё. Но вы можете поддержать проект. Даже небольшая сумма поможет нам писать больше полезных статей.

Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.