Работа с MySQL

Материал из darklurker wiki
Перейти к: навигация, поиск

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

Базы данных

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

CREATE DATABASE [databasename];

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

SHOW DATABASES;

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

DROP DATABASE [database name];

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

USE [db name];

Таблицы

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

SHOW TABLES;

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

DESCRIBE [table name];

Создать таблицу:

CREATE TABLE [table name] (column1 TYPE, column2 TYPE DEFAULT <VALUE>) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

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

DROP TABLE [table name];

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

ALTER TABLE [table] RENAME [table2];

Изменить значение по умолчанию и указать что он не может быть NULL для столбца:

ALTER TABLE [table] MODIFY COLUMN [column] INT NOT NULL DEFAULT 0;

Переименовать колонку в таблице:

ALTER TABLE `table` CHANGE COLUMN `id_item` `item_id` INT(10) NOT NULL DEFAULT 0;

Добавить колонку в существующую таблицу:

ALTER TABLE users ADD COLUMN password VARCHAR(255) NOT NULL AFTER login;

Обновить строку в таблице:

UPDATE [table name] SET column1 = [value] WHERE [value] = [value];

Обновить с увеличением значения:

UPDATE [table name] SET column1 = column1 + 1 WHERE [value] = [value];

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

SELECT * FROM [table name];

Показать только уникальные значения столбца:

SELECT DISTINCT [column] FROM [table];

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

SHOW COLUMNS FROM [table name];

Добавить значение в таблицу:

INSERT INTO [table] (column1, column2) VALUES (value1, value2)

Сортировка значений в таблице (DESC в обратном порядке, limit=limit):

SELECT column1, column2 FROM [table] ORDER BY [column] DESC LIMIT 3;

Удалить строки из таблицы:

DELETE FROM [table] WHERE [column] = [value];

Удаление строки с частичным соответствием:

DELETE FROM [table] WHERE [column] LIKE 'pattern%';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT COUNT(*) FROM [table name];

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

SELECT SUM(*) FROM [table name];

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE [table name] DROP INDEX [column name];

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

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

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

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

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

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

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

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

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
FLUSH PRIVILEGES;

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

systemctl stop mysql
mysqld_safe --skip-grant-tables &
mysql -uroot -ppassword
USE mysql;
UPDATE user SET password=PASSWORD("newrootpassword") WHERE User='root';
FLUSH PRIVILEGES;
\q
systemctl stop mysql
systemclt start mysql

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

mysqladmin -u root -p oldpassword newpassword

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

mysql -u root -p
USE mysql;
GRANT USAGE ON *.* TO bob@localhost IDENTIFIED BY 'passwd';
FLUSH PRIVILEGES;

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

GRANT ALL PRIVILEGES ON databasename.* TO username@localhost;
FLUSH PRIVILEGES;

Права в MySQL:

  • CREATE – Позволяет пользователям создавать базы данных/таблицы
  • SELECT – Позволяет пользователям делать выборку данных
  • INSERT – Позволяет пользователям добавлять новые записи в таблицы
  • UPDATE – Позволяет пользователям изменять существующие записи в таблицах
  • DELETE – Позволяет пользователям удалять записи из таблиц
  • DROP – Позволяет пользователям удалять записи в базе данных/таблицах

Предоставить права:

GRANT CREATE, SELECT ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Забрать права:

REVOKE CREATE, SELECT ON *.* FROM 'user'@'localhost';
FLUSH PRIVILEGES;

Удалить пользователя:

DROP USER ‘user’@‘localhost’;
FLUSH PRIVILEGES;

Просмотр пользователей MySQL:

SELECT User,Host FROM mysql.user;

Просмотр прав пользователя:

SHOW GRANTS FOR username;

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

Создать резервную копию (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 -v -u username -p databasename < /tmp/databasename.sql

Починка всех таблиц

mysqlcheck -p  --auto-repair --check --optimize --all-databases