Шпаргалка по командам 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, CRON
Аналогичная функция Excel ОКРУГЛТ() на PHP
CSS

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