Mysql показать структуру таблицы

Содержание

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

Авторизация на сервере (из консоли), -h при необходимости авторизации на удалённом сервере

mysql -h hostname -u root -p

Создание БД

mysql> create database `databasename`;

Создание БД с указанием необходимой кодировки

mysql> create database `databasename` default character set ‘utf8’ collate ‘utf8_unicode_ci’;

Получить список всех БД на сервере

mysql> show databases;

Переключится на БД

mysql> use `db name`;

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

mysql> show tables;

Посмотреть структуру таблицы

mysql> describe `table name`;

Ещё один вариант

mysql> show columns from `table name`;

Удалить БД

mysql> drop database `database name`;

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

mysql> drop table `table name`;

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

mysql> SELECT * FROM `table name`;

Показать строки, где поле `field 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;

Показать строки с номером «3444444» не содержащие имени «Bob» отсортированные по номеру.

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 ограничить вывод пятью первыми строками

mysql> SELECT * FROM `table name` WHERE name like ‘Bob%’ AND phone_number = 3444444 limit 0,5;

Используем регулярное выражение. Для регистрозависимого выбора используйте «REGEXP BINARY». Данный запрос найдёт все записи, начинающиеся на «a»

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`;

Объединение таблиц. Как работает JOIN (в картинках)

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Создание пользователя. Вход под root. Переключение на БД mysql. Создание пользователя и обновление привилегий.

mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

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

mysqladmin -u username -h hostname -p password ‘new-password’

Смена пароля пользователя из консоли MySQL. Вход как root. Смена пароля. Обновление привелегий.

mysql -u root -p mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);

mysql> flush privileges;

Восстановление пароля root пользователя. Остановить MySQL сервер. Запустить с пониженной безопасностью. Залогинится на MySQL как root. Установить новый пароль. Разлогинится и перезапустить MySQL сервер.

/etc/init.d/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 /etc/init.d/mysql stop
/etc/init.d/mysql start

Установка пароля root если он ещё не задавался ранее

mysqladmin -u root password newpassword

Смена пароля root

mysqladmin -u root -p oldpassword newpassword

Разрешить пользователю «Bob» подключаться к серверу c локального адреса с паролем «passwd». Войти как root. Переключиться на БД mysql. Дать привилегии. Обновить привелегии.

mysql -u root -p mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Предоставить пользователю привилегии на БД. Авторизоваться как root. Переключиться на БД mysql. Предоставить привилегии. Обновить кеш привилегий.

mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (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> use mysql;
mysql> UPDATE `user` SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where `User` = ‘user’;
flush privileges;

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

mysql> DELETE from `table name` where `field_name` = ‘whatever’;

Обновить кеш привилегий

mysql> flush privileges;

Удалить колонку из таблицы

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 `colmn name`;

Загрузить данные в БД из CSV файла.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE `table name` FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Сделать дамп всех БД для бэкапа. Бэкап это файл с SQL командами для воссоздания всех баз.

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

Сделать дамп одной базы.

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

Сделать дамп одной таблицы

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

Восстановить БД (или таблицу) из бэкапа

mysql -u username -p 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),

Обращение к MySQL из Unix Shell

Для обращения к MySQL служит команда mysql

mysql —host=хост —port=порт —user=логин -p имя_базы_данных
При этом программа спросит пароль. Если вам нужно автоматизировать это действие, вместо ключа -p можно указать —password=ваш_пароль.

имя_базы_данных указывать необязательно

создание базы данных

CREATE DATABASE mybase;

создание аккаунта, уровни доступа MySQL

Для упрощения создания пользователя можно разрешить ему все операции с базой. При этом mybase — название БД, mybaseuser — имя пользователя, localhost — хост(ы) с которых разрешено обращение данного пользователя к БД.

Если необходима более тонкая настройка доступа, то вместо ALL необходимо через запятую указать права, например разрешить пользователю только читать или только записывать данные, или ограничить возможность удаления базы данных (что иногда бывает очень неприятно), полный список прав доступа MySQL здесь.

Смена пароля пользователя
Метод 1:

mysql -u root
mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpass’);

Метод 2:

mysqladmin -u root password «newpass»

или если пароль для пользователя root уже задан:

mysqladmin -u root password oldpass «newpass»

Метод 3:

mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE User = ‘root’;
mysql> FLUSH PRIVILEGES;

список баз данных MySQL

СУБД MySQL может хранить несколько баз данных. Чтобы посмотреть их список:

SHOW DATABASES;

работа с базой MySQL

Чтобы приступить к работе с нужной базой, её нужно выбрать:

USE mybase;

удаление базы данных MySQL

Удалить базу данных так же просто как и создать:

DROP DATABASE mybase;

список таблиц MySQL

Внутри базы данных находятся… …таблицы. 🙂 Чем-то это они похожи на обычные электронные таблицы. После выбора базы данных (USE) просмотреть список таблиц в этой базе можно так:

SHOW TABLES;

просмотр структуры таблицы MySQL

Каждая таблица имеет собственную структуру (заданный набор столбцов). Просмотреть структуру таблицы можно так:

DESCRIBE tablename;
SHOW TABLE STATUS;

создание структуры таблицы MySQL

Таблицы создаются с помощью (этот абстрактный пример работать не будет, он здесь просто для упрощения понимания процесса создания таблицы:

CREATE TABLE tablename (переменные тип(размер));

А вот это уже настоящий работающий пример:

CREATE TABLE test_innodb (
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id) — основной ключ
) TYPE=InnoDB;

И этот тоже:

Здесь нужно обратить внимание на понятия основного ключа (PRIMARY KEY), определяющего однозначно каждую строку таблицы и внешнего ключа (FOREIGN KEY), определяющего зависимость данных в одной таблице от данных в другой таблице.

типы таблиц MyISAM и innoDB в MySQL

На Unix системах по умолчанию создаются типы таблиц MyISAM. Они вполне подходят для баз данных с малой нагрузкой, таких, как веб-проекты. Однако для крупных информационных систем предпочтительнее использовать тип innoDB, преодалевающий множество ограничений, например позволяющий создавать базы данных, по размеру большие, чем максимальный размер файла на файловой системе. Так же innoDB позволяет блокировать (LOCK) не всю таблиу, а отдельные строки, что бывает просто необходимо в случае когда система работает в режиме 24/7/365, например для того чтобы база не «подвисала» при создании резервной копии. Выбор типа хранения для таблиц происходит на этапе создания таблицы:

CREATE TABLE table_name (…) ENGINE=MyISAM или innoDB

типы данных MySQL

BOOL истина или ложь, TRUE/FALSE или 1/0.

TINYINT очень маленькие целые числа от -128 до 127 или от 0 до 255.

SMALLINT маленькие целые числа от -32768 до 32767 или от 0 до 65535.

INT целые числа от -2147483648 до 2147483647 или от 0 до 4294967295.

BIGINT большие целые числа от -9223372036854775808 до 9223372036854775807 или от 0 до 18446744073709551615.

DOUBLE числа с плавающей запятой от -1.7976931348623157E+308 до -2.2250738585072014E-308, 0, от 2.2250738585072014E-308 до 1.7976931348623157E+308.

TIME время от ‘-838:59:59’ до ‘838:59:59’.

DATE дата от ‘1000-01-01’ до ‘9999-12-31’.

TIMESTAMP метка времени (время и дата), в секундах с 1970-01-01. Удобно для INSERT и UPDATE. По умолчанию первая колонка TIMESTAMP в таблице автоматически записывает время и дату операции, если в самой операции не указано иначе. В любую колонку TIMESTAMP можно записать текущие дату и время, присвоив NULL.

VARCHAR(n) строка из n символов.

полный список типов данных MySQL здесь

добавление записей MySQL

Добавлять записи (они же строки, кортежи) очень просто:

INSERT INTO mytable (a,b,c) VALUES (‘1′,’2′,’3’);

Вот примеры к таблицам, созданным в примере выше:

выборка записей MySQL

Для выборок из баз данных используется самый часто используемый оператор в MySQL. В простейшем виде для выбора всех данных из таблицы он выглядит так:

SELECT * FROM items;

Однако в работе обычно таблицы достаточно большие, и выборка всех данных занимает достаточное время, поэтому выбранные данные ограничивают нужными условиями, например так:

SELECT * FROM items WHERE itemname = ‘Apple Mac Book Pro’;

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

SELECT itemname,itemdesc FROM items WHERE itemname = ‘Apple Mac Book Pro’;

Количество записей, показанных в ответе можно ограничить (например 10 записями):

SELECT * FROM items LIMIT 10;

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

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

SELECT category,itemname,itemdesc FROM items,categories WHERE categories.catid=items.catid;

При выборке поля типа TIMESTAMP конвертируются в строковое значение. Чтобы этого избежать, используем UNIX_TIMESTAMP():

SELECT UNIX_TIMESTAMP(date) FROM table;

удаление записей MySQL

Как бы это странно не звучало, чтобы удалить записи, их нужно выбрать с помощью WHERE. Делается это так:

DELETE FROM users WHERE user = ‘coolhacker’;

изменение записей MySQL

Для изменения тоже нужно с помощью WHERE выбрать строки(у), которые(ую) нужно изменить. Полезный пример (работает от root), для изменения пароля пользователя:

UPDATE mysql.user SET Password=password(‘paSSwoRD’) WHERE User=’username’;

Еще один пример, с таблицей, описанной выше:

UPDATE items SET itemdesc=’Клёвый ноутбук’, itemname=’Mac Book Pro’
WHERE itemname=’Apple Mac Book Pro’;

изменение структуры таблицы MySQL

Иногда структуру созданную с помощью CREATE TABLE нужно изменить. Проще всего это сделать на пустой таблице, иначе нужно смотреть чтобы в итоге преобразования не потерялись какие-то нужные данные. В любом случае, если вы делаете это первый раз, создайте заранее резервную копию базы. Изменение структуры:

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

ALTER TABLE myfirsttable RENAME mysecondtable;

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

ALTER TABLE mytable CHANGE a b INTEGER;

Добавить новый столбец TIMESTAMP с именем mytimestamp:

ALTER TABLE mytable ADD mytimestamp TIMESTAMP;

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

ALTER TABLE mytable DROP COLUMN notneeded;

Изменить тип столбца a INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c:

ALTER TABLE mytable MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

дамп базы данных MySQL

Дамп базы созраняется утилитой коммандной строки mysqldump (в Unix Shell):

mysqldump —quick -u -p -h -P > database.sql

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

mysqldump -h -uroot -P -p —all-databases —quick —lock-tables —extended-insert > backupalldb.sql

При ошибке ‘Out Of Memory’, возникающей как правило из за ограниченности ресурсов , нужно добавить ключик —quick

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

Восстановление базы из дампа происходит через Unix Shell, с использованием утилиты mysql:

mysql -u -p -h -P -D

Бывает при восстановлении дампа получаются ошибки, например такие:

/usr/local/mysql/bin/mysql -D db_test —password=secret_passowrd —user=user

Обычно это означает что какое-то имя столбца MySQL принял за ключевое слово, в этом случае нужно отредактировать дамп на указанной строчке (161) например с помощью Vi: имя столбца (в данном примере option) нужно заключить в `обратные кавычки`. Чтобы этого не происходило можно было при создании дампа запускать mysqldump с ключем —quote-name

просмотр процессов в MySQL

Иногда бывает непонятно что делает база данных, повисла ли она, или там проходят какие-то операции, посмотреть не превышен ли лимит на количество соединений с базой:

SHOW FULL PROCESSLIST;

просмотр текущей конфигурации MySQL сервера

SHOW VARIABLES;

Оптимизация таблиц и запросов

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *