Шпаргалка по командам MySQL

Шпаргалка по командам MySQL


freelanceland
Лучшие исполнители для ваших задач
SELECT, CREATE, ALTER,
DROP, INSERT, DELETE,
UPDATE, Управление пользователями, Управление базой данных,
Статистика БД, Оптимизация БД, Дамп (резервная копия),
Применение дампа (импорт), Восстановление root-пароля,

Создать новую БД

CREATE DATABASE db_name;
db_name - имя БД

Показать список БД

SHOW DATABASES;


Для начала работы с БД db_name

mysql> USE db_name;


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

mysql> CREATE TABLE authors (
id_author int(6) NOT NULL auto_increment,
name text,
age int(10),
time datetime default NULL,
themes int(10) default NULL,
PRIMARY KEY (id_author)
) TYPE=MyISAM;


Показать структуру db_name

mysql> DESCRIBE db_name;


Добавление нового столбца db_name

mysql> ALTER TABLE forums ADD test int(10) AFTER name;
Добавим в таблицу новый столбец test, разместив его после столбца name.

Переименовать столбец

mysql> ALTER TABLE db_name CHANGE test new_test text;


Изменяем тип столбца
mysql> ALTER TABLE db_name CHANGE new_test new_test int(5) not null;


Удалить столбец new_test

mysql> ALTER TABLE db_name DROP new_test;


Удалить таблицу

mysql> DROP TABLE db_name;


Удалить БД forum

mysql> DROP DATABASE forum;


Вставить новые записи в существующую таблицу db_name

mysql> INSERT INTO db_name VALUES (1, 'Maks', '123', ' maks@mail.ru ', ' www.softtime.ru ', '', 'программист', '', '', '', 0, 0);


Удаление из таблицы записей с условием WHERE

mysql> DELETE FROM db_name WHERE passw = '123' AND id_author > 10;


Выборка из таблицы записей с условием WHERE c сортировкой по полю

mysql> SELECT * FROM db_name WHERE id_forum > 2 ORDER BY pos;


Выбираем записи без повторений

mysql> SELECT DISTINCT city FROM db_name;


Выбираем записи с помощью оператора LIKE

mysql> SELECT * FROM db_name WHERE city LIKE `Б_л%`;


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

mysql> SELECT COUNT(*) FROM city WHERE name LIKE `Х%;


SELECT «GROUP BY» и «HAVING»

mysql> SELECT city_name, count(*) AS `Количество` FROM city GROUP BY city_name HAVING count(*)>1;
Использование «GROUP BY» и «HAVING» на примере задачи: вывести названия городов, в которых больше одного клуба, и их количество.

SELECT с оператором JOIN

mysql> SELECT t1.command_name, t2.command_name FROM commands t1 JOIN commands t2 WHERE t1.id <> t2.id AND t1.id > t2.id
из таблицы «commands» выбрать команды таким образом чтобы получилась турнирная таблица, в которой каждая команда встретилась по-разу с другой.

Выбираем записи c фильтром по DATETIME (в промежутке)

mysql> SELECT * FROM db_name WHERE datep BETWEEN (`2013-04-01`) and (`2013-04-03`)ж


Обновить записи в таблице

mysql> UPDATE db_name SET name='PHP', hide=1 WHERE id_forum=2;


Показать список столбцов в таблице

mysql> SHOW FIELDS FROM db_name;


Управление пользователями



Список пользователей
#mysql> SELECT User,Host FROM mysql.user;
Список прав у пользователя root@localhost
#mysql> SHOW GRANTS FOR root@localhost;
Создание нового пользователя
#mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
Добавим выбранные привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'
#mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON dbname.* TO 'user'@'localhost';
Добавить все привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'
#mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Удаление прав пользователя 'user'@'localhost' для БД dbname:
#mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';
Перезагрузка привилегий
#mysql> FLUSH PRIVILEGES;
Новый пароль для root
#mysqladmin -uroot password 'secret'

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



Создание базы данных
#mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Создание базы данных из консоли
#mysqladmin -u root -p create dbname
Удаления базы данных из консоли
#mysqladmin -u root -p drop dbname

Cтатистика



Список всех баз данных
#mysql> SHOW DATABASES;
Список всех таблиц в выбранной базе данных
#mysql> SHOW TABLES;
Статистика по работе сервера
#mysql> SHOW GLOBAL STATUS;

Оптимизация


При помощи команды mysqlcheck можно выполнять проверку, оптимизацию и исправление ошибок.

Поверка на ошибки БД dbname
#mysqlcheck -p dbname
Восстановление и оптимизация всех БД
#mysqlcheck -Aor -p
аргументы:
-p – использовать пароль
-A – проверять все базы данных
-r – ремонтировать БД
-o – оптимизировать БД

Скрипт простой оптимизации БД, можно добавить в крон для выполнение раз в сутки
#mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
Установим mysqltuner, который показывает статистику по работающему mysql-серверу и дает полезные советы по оптимизации.
#cd /usr/local/bin
#wget http://mysqltuner.pl/mysqltuner.pl
#chmod +x mysqltuner.pl
Запустим
#/usr/local/bin/mysqltuner.pl
При запуске скрипт спросит логин и пароль для root'a. После корректной авторизации будет выведена статистика и разные советы по правке конфигурационного файла MySQL.

Дамп (резервная копия)



Дамп базы данных
#mysqldump -u root -p dbname > dump.sql
Дамп выбранных баз
#mysqldump -u root -p -B dbname1 dbname2 > dump.sql
Дамп всех баз
#mysqldump -u root -p -A > dump.sql
Дамп только структуры, без данных
#mysqldump -u root -p --no-data dbname > database.sql
Другие опции
--add-drop-table - добавляет команду DROP TABLE перед каждой командой CREATE TABLE
--add-locks - добавляет команду LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы
--no-create-db, -n - не добавлять команду CREATE DATABASE, которая добавляется при использовании параметров --databases и --all-databases
--no-data, -d - дампить только структуру таблиц
--no-create-info, -t - не создавать команду CREATE TABLE
--skip-comments - не выводить комментарии.
--compact - использовать компактный формат
--create-options - добавляет дополнительную информацию о таблице в команду CREATE TABLE: тип, значение AUTO_INCREMENT и т.д. Не нужные опции можно вырезать с помощью sed.
--extended-insert, -e - применение команды INSERT с многострочным синтаксисом (повышает компактность и быстродействие операторов ввода)
--tables - дампить только таблицы из списка, следующего за этим параметром, разделитель - пробел

Применение дампа (импорт)


#mysql -uroot -p dbname1 < dump.sql
Определение кодировки файла
file --mime-encoding dump.sql
Конвертирование из кодировки latin1 в utf8
mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname

Восстановление root-пароля


#service mysqld stop
#mysqld_safe --skip-grant-tables &
#mysql

#mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root';
#mysql> FLUSH PRIVILEGES;
#mysql> \q

#service mysqld restart


читать подробнее

Популярное

Проверка ИНН по контрольной сумме PHP/JavaScript
PHP, округление до 10 или 100 в большую или меньшую сторону
Шпаргалка Git команд

Возврат к списку