Простая работа с MySQL в простых примерах #1 – работа с базами, создание таблиц

Автор: | 31/01/2013
 

mysqllogoЭто первая часть. Вторая часть, в которой описаны примеры заполнения таблиц данными находится тут>>>.

Ниже будут приведены наиболее полезные при работе с MySQL команды и примеры их выполнения.

Будет приведен пример работы с MySQL как из консоли сервера, так и и из mysql-клиента – просмотр баз, таблиц в них. Будет приведен пример создания простой таблицы, описание типов используемых типов столбцов в ней.  Во второй части будет дано описание и примеры работы с таблицей – наполнение данными, редактирование их и прочее.

Для примера будет создана таблица, содержащая список контактов.

Уточнение: MySQL – это сервер баз данных, а mysql – это консольный unix-like клиент, для работы с MySQL. Строки, начинающиеся с символа $ означают, что они выполняются из обычной консоли сервера.

Итак, приступим.

Для подключения к серверу MySQL используется такая команда:

$ mysql -u username -p
Enter password:
mysql>

Если сервер MySQL находится на удалённом хосте – его можно указать с помощью ключа -h:

$ mysql -u username -p -h somehost.com
Enter password:
mysql>

Очень полезная команда – status, которая выводит информацию об используемой версии MySQL и многое другое:

mysql> status
————–
mysql  Ver 14.14 Distrib 5.5.29, for FreeBSD9.0 (i386) using  5.2

Connection id:          107801
Current database:       test1
Current user:           username@localhost
SSL:                    Not in use
Current pager:          most
Using outfile:          ”
Using delimiter:        ;
Server version:         5.5.29-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 8 days 1 hour 31 min 32 sec

Threads: 5  Questions: 8391067  Slow queries: 37  Opens: 17162  Flush tables: 1  Open tables: 64  Queries per second avg: 12.044

Ещё полезная возможность – выполнять консольные команды сервера, не выходя из клиента mysql, для этого используйте команду system:

mysql> system w;
8:12PM up 8 days, 1:40, 4 users, load averages: 0.20, 0.29, 0.26
USER TTY FROM LOGIN@ IDLE WHAT
setevoy pts/1 attached-masquerader.vol 10:43AM 9:28 htop
setevoy pts/2 attached-masquerader.vol 10:43AM – w
setevoy pts/3 attached-masquerader.vol 10:43AM 54 mysql -u root -p
setevoy pts/4 attached-masquerader.vol 10:43AM 53 /usr/local/bin/bash

Теперь – примеры использования некоторых SQL-функций.

К примеру – выведем текущую дату:

mysql> select now();
+———————+
| now()               |
+———————+
| 2013-01-27 15:56:20 |
+———————+
1 row in set (0.00 sec)

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

mysql> select now(),
-> user(),
-> version()
-> ;
+———————+———————+————+
| now()               | user()              | version()  |
+———————+———————+————+
| 2013-01-27 15:59:07 | username@localhost | 5.5.29-log |
+———————+———————+————+
1 row in set (0.00 sec)

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

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

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

mysql> show databases;
+————————+
| Database |
+————————+
| base1    |
| base2    |
| base3    |
| base4    |
+————————+

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

mysql> use base1;
Database changed

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

mysql> select database();
+————+
| database() |
+————+
| base1      |
+————+
1 row in set (0.00 sec)

Просмотреть содержащиеся в ней таблицы можно командой:

mysql> show tables;
+——————–+
| Tables_in_base1    |
+——————–+
| tablename          |
+——————–+
1 row in set (0.00 sec)

В базе base1 имеется только одна таблица tablename.

Всё то же самое можно сделать и не подключаясь к серверу MySQL, а напрямую из консоли сервера. Например – просмотреть список имеющихся на сервере баз:

$ mysqlshow -u root -p
Enter password:
+——————–+
|     Databases      |
+——————–+
| base1              |
| base2              |
| base3              |
| base4              |
+——————–+

А просмотреть имеющиеся в базе данных MySQL таблицы из консоли можно так:

$ mysqlshow base1 -u root -p
Enter password:
Database: base1
+———–+
|  Tables   |
+———–+
| tablename |
+———–+

Посмотреть содержимое таблицы из консоли можно следующим образом:

$ mysqlshow base1 tablename -u root -p

Учтите, что если название таблицы содержит символ подчеркивания “_” – то mysqlshow может неверно обработать запрос, и вывести примерно следующее:

$ mysqlshow base1 tablename_1 -u root -p
Enter password:
Database: contacts  Wildcard: tablename_1
+————-+
| Tables      |
+————-+
| tablename_1 |
+————-+

Что бы избежать этого – добавьте символ % в конец запроса:

$ mysqlshow base1 tablename_1 % -u root -p
Enter password:
Database: contacts  Table: tablename_1
+——————+——————+——+—–+———+—————-+
| Field            | Type             | Null | Key | Default | Extra          |
+——————+——————+——+—–+———+—————-+
| contact_id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

Выполнять запросы SQL можно как из консоли сервера, так и из консоли клиента mysql. Так же, запросы можно вводить не напрямую, а из файла. К примеру, имеется файл с таким содержимым:

$ cat temp.sql
select now()

Что бы выполнить этот запрос из консоли сервера – выполните:

$ mysql -u root -p < temp.sql
Enter password:
now()
2013-01-31 17:59:43

А из клиента mysql – так:

mysql> . temp.sql
+———————+
| now()               |
+———————+
| 2013-01-31 18:00:48 |
+———————+
1 row in set (0.00 sec)

При этом, файл должен находится в каталоге, в котором вы находились перед подключением к mysql-клиенту.

Теперь – подключимся под пользователем root и создадим базу test1:

# mysql -u root -p
Enter password:
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

Далее – создадим пользователя user1, дадим ему права на доступ к базе test1 и установим пароль mypasswd:

mysql> grant all on test1.* to ‘user1’@’localhost’ identified by ‘mypasswd’;

Теперь – подключимся к серверу под новым пользователем и сразу переключимся на базу test1, что бы избежать ввода команды use database, после чего сразу проверим используемую базу с помощью функции select database():

# mysql -u user1 -p test1
Enter password:
mysql> select database();
+————+
| database() |
+————+
| test1      |
+————+
1 row in set (0.00 sec)

Создадим таблицу.

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

имя_столбца ТИП(значение) АТРИБУТ

Атрибуты могут иметь значение либо NULL (может быть пустым), либо NOT NULL — обязательно должен быть заполнен.

В примере будут использоваться такие типы столбцов:

VARCHAR – подразумевает, что в столбце будут использоваться символьные данные – текст – переменной длины, в (значение) указывается количество знаков, которое может быть использовано в этом столбце;
DATE – подразумевает использование даты в формате YYYY-MM-DD;
INT — только целые числа, без дробей;
UNSIGNED — только положительные значения;
AUTO_INCREMENT — автоматически задаст значение, на единицу большее существующего;
PRIMARY KEY — индексное значение, всегда должно быть уникальным;
ENUM — столбец перечисляемого типа, может принимать значения, указанные в его атрибутах, в примере это (‘Y’, ‘N’).

PRIMARY KEY указывает, что указанные столбцы являются индексами, подробнее тут>>>.

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

mysql> CREATE TABLE main_list (contact_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50), phone VARCHAR(20), city VARCHAR(20), email VARCHAR(50), skype VARCHAR(20), icq INT UNSIGNED, birth_date DATE, facebook_address VARCHAR(50), vk_address VARCHAR(50), real_type ENUM(‘Y’, ‘N’));
Query OK, 0 rows affected (0.26 sec)

Что бы просмотреть созданную таблицу – выполним:

mysql> desc main_list;
+——————+——————+——+—–+———+—————-+
| Field            | Type             | Null | Key | Default | Extra          |
+——————+——————+——+—–+———+—————-+
| contact_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name       | varchar(50)      | NO   |     | NULL    |                |
| last_name        | varchar(50)      | YES  |     | NULL    |                |
| phone            | varchar(20)      | YES  |     | NULL    |                |
| city             | varchar(20)      | YES  |     | NULL    |                |
| email            | varchar(50)      | YES  |     | NULL    |                |
| skype            | varchar(20)      | YES  |     | NULL    |                |
| icq              | int(10) unsigned | YES  |     | NULL    |                |
| birth_date       | date             | YES  |     | NULL    |                |
| facebook_address | varchar(50)      | YES  |     | NULL    |                |
| vk_address       | varchar(50)      | YES  |     | NULL    |                |
| real_type        | enum(‘Y’,’N’)    | YES  |     | NULL    |                |
+——————+——————+——+—–+———+—————-+

Добавим вторую таблицу:

mysql> CREATE TABLE main_socials (entry_id INT UNSIGNED AUTO_INCREMENT NOT NULL, contact_id INT UNSIGNED NOT NULL, vk_id VARCHAR(100), facebook_id VARCHAR(100), PRIMARY KEY (entry_id, contact_id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc main_socials;
+————-+——————+——+—–+———+—————-+
| Field       | Type             | Null | Key | Default | Extra          |
+————-+——————+——+—–+———+—————-+
| entry_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| contact_id  | int(10) unsigned | NO   | PRI | NULL    |                |
| vk_id       | varchar(100)     | YES  |     | NULL    |                |
| facebook_id | varchar(100)     | YES  |     | NULL    |                |
+————-+——————+——+—–+———+—————-+
4 rows in set (0.00 sec)

При указании столбцу параметра AUTO_INCREMENT необходимо так же установить определение индекса – PRIMARY KEY во избежание ошибки такого плана:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Так же, если вы хотите в таблице использовать более индекса 1 PRIMARY KEY – его необходимо указать в конце запроса, в виде отдельного значения:

… , PRIMARY KEY (entry_id, contact_id));

В противном случае – вы можете получить ошибку такого плана:

ERROR 1068 (42000): Multiple primary key defined

Если таблица создана ошибочно, или больше не нужна – удалить её можно командой:

mysql> drop table tablename;
Query OK, 0 rows affected (0.75 sec)

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

mysql> ALTER TABLE tablename DROP COLUMN column_name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Так же, полезная страница команд MySQL тут>>> и кратко о работе с резервными копиями – тут>>>.

Это первая часть. Вторая часть, в которой описаны примеры заполнения таблиц данными находится тут>>>.

Полезные ссылки:

http://dev.mysql.com
http://sqle.ru
http://www.spravkaweb.ru
http://sql-ex.ru
http://www.sql-tutorial.ru