Базы данных MySQL

Если UNSIGNED - от 0 до 255

SMALLINT - Целое. Занимает 2 байта. Хранит значения от -32 768 до 32 767.

Если UNSIGNED - от 0 до 65535

MEDIUMINT - Целое. Занимает 3 байта. Хранит значения от -8 388 608 до 8 388 607.

Если UNSIGNED - от 0 до 16 777 215

INT - Целое. Занимает 4 байта. Хранит значения от -2 147 483 648 до 2 147 483 647.

Если UNSIGNED - от 0 до 4 294 967 295

Положительное число всегда меньше отрицательного на единицу. Эту самую единицу автоматически занимает знак числа. Если Вы точно знаете, что у Вас в поле будет лежать значение только 1 или 0, то такому полю определять тип данных INT  особого смысла нет, вполне хватит TINYINT.

Строковые

CHAR - Хранит длину строки до 255 символов. 1 символ - 1 байт.

VARCHAR - Хранит длину строки до 255 символов. (1 символ - 1 байт) + 1 байт.

TEXT - Хранит длину строки до 65 535 символов. (1 символ - 1 байт) + 2 байта.

MEDIUMTEXT - Хранит длину строки до 16 777 215 символов. (1 символ - 1 байт) + 3 байта.

LONGTEXT - Хранит длину строки до 4 294 967 295 символов. (1 символ - 1 байт) + 4 байта.

2 типа данных CHAR и VARCHAR хранят в себе одинаковое количество  символов, а VARCHAR всегда на 1 байт  занимает больше. CHAR - фиксированная длина строки, а VARCHAR -переменная. На сайте разработчиков MySQL есть вот такая табличка:

Если мы определяем для  поля тип данных CHAR(4), то сколько бы символов мы туда не писали, их там будет ровно 4. Недостающая разница добавляется пробелами. Это разумно использовать при фиксированных величинах: артикул товара, номер паспорта, почтовый индекс, табельный номер сотрудника, номер сотового телефона, которые всегда равны фиксированной длине. Используется достаточно редко. А вот VARCHAR(4), занимает всегда на 1 байт больше, чем туда положили, зато не добавляется пробелами. В этом самом одном байте хранится информация о количестве символов, хранящихся в этом поле. Используется повсеместно: логины, пароли, контактная информация, пути к файлам и т.д.

Дата и время

DATETIME - Хранит дату и время (через пробел) в формате: ГГГГ-ММ-ДД ЧЧ:ММ:СС

Например: 2011-05-25 01:35:12

YEAR - Хранит год в формате: ГГГГ

Например: 2011

DATE - Хранит дату в формате: ГГГГ-ММ-ДД

Например: 2011-05-25

TIME - Как уже не сложно догадаться время в формате: ЧЧ:ММ:СС

Например: 01:35:12

TIMESTAMP - Хранит количество секунд, прошедших с 1 января 1970 года (начало "Эры UNIX-систем") до текущей точки времени.

 

 

5. Движки MySQL, как технология хранения информации

MySQL предоставляет широкий выбор вариантов хранения информации на сервере. Эти механизмы хранения называются движками или Storage Engine. Основных 7-8, почти все они оптимизированы под OLTP соединения. У каждого движка есть свои неоспоримые плюсы и минусы, на основании которых уже можно и нужно сделать вывод, на каком из них хранить информацию базы данных.

InnoDB

Один их самых популярных используемых движков. Поддерживает почти  весь функционал сервера MySQL. Используется по умолчанию. Данных движок хорошо себя показывает на больших проектах с  высокой нагрузкой. Лимит дискового  пространства 64 Тб.

Особенности:

● Хранит все данных в  одном файле.

Плюсы:

● Поддержка транзакций.

● Поддержка внешних ключей.

● Позволяет управлять  конкурентным доступом с помощью  многоверсионности (MVCC).

● Поддержка блокировок таблиц (LOCK).

● Быстрая выборка (SELECT).

● Индексация полей.

Минусы:

● Не поддерживает полнотекстовый поиск.

 

 

 

MyISAM

Очень популярный движок для web-проектов. Очень быстрая вставка  и изменение записей, за счет отсутствия внешних ключей и транзакций. В  отличии от InnoDB, все данные хранит по отдельности (все базы данных лежат  в папке C:/SERVER/MySQL/Data/, 1 папка - 1 база): файлы с расширением . MYI (MYIndex) содержат индексы полей. Файлы с расширением .MYD (MYData) содержат сами данные. Файлы с расширением .frm (format) содержат структуру таблиц. Чаще всего используется для справочников, Лимит дискового пространства 256 Тб.

Особенности:

● Отсутствует поддержка  транзакций.

● Не поддерживает внешние  ключи.

● Хранит все данные отдельно друг от друга.

Плюсы:

● Поддержка блокировок таблиц (LOCK).

● Быстрая вставка строк (INSERT).

● Быстрое изменение строк (UPDATE).

● Быстрое удаление строк (DELETE).

● Поддержка полнотекстового  поиска.

● Индексация полей.

Минусы:

● Гораздо медленнее производит выборку (SELECT), чем InnoDB, да и многие другие.

● Отсутствует возможность  восстановления данных по журналу.

● Отсутствуют инструменты  резервного копирования.

● Слабая реализация сортировки.

● Риски нарушения стабилизации работы, потери данных.

 

Archive

Этот движок предназначен для хранения редко изменяющихся данных в большом объеме.

Особенности:

● Транзакций нет.

● Отсутствует поддержка  первичного ключа, внешних соответственно тоже.

● Отсутствие индексации, полнотекстовый поиск соответственно.

 

CSV

Comma Separated Values (CSV) - переводится  как данные, разделенные запятыми. Текстовый документ, в котором  построчно записаны строки таблиц  базы, разделенные запятыми. 1 строка  документа - 1 строка таблицы базы  данных.

 
Memory (HEAP)

Данный движок предназначен для создания временных таблиц (TEMPORARY TABLE). Все данные хранит в оперативной  памяти машины, на котором стоит  сервер. Мгновенная скорость работы с  данными. После остановки сервера, все данные теряются.

 
MERGE

Этот движок предназначен для объединения идентичных таблиц MyISAM. Применяется в основном для расчета статистических данных, сводки: например, анализ архивов за несколько лет.

Особенности:

● Наличие идентичной структуры.

● Порядок полей (колонок) должен совпадать.

● Отсутствие полнотекстового  поиска.

 

6. Создание базы данных MySQL

Работать будем в командной  строке Windows - это самый доступный клиент для БД. Хотя можно использовать и любой другой клиент, например MySQL Workbench, в нем проще работать, но, на мой взгляд, лучше всё делать в ручную.

 
6.1 Перед стартом

Если создать какой-либо объект БД в имени которого содержатся русские символы, можно получить «иероглифы» на выводе на экран в командной строке

Дело в том, что командная строка Windows работает с кодировкой cp866, а сервер отправляет данные в кодировке utf-8.

Открываем конфиг-файл сервера (C:/SERVER/MySQL/my.ini), и в нем есть 3 секции:

[client] - Настройка для всех клиентских программ.

[mysql]- Настройка для командной строки Windows.

[mysqld]- Настройка для самого сервера MySQL.

 
Ищем секцию [mysql], находим там строку:

default-character-set=utf8

и меняем на:

default-character-set=cp866

Сохраняемся, перезапускаем  сервер и командную строку.

 

6.2 Подключение к серверу

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

mysql -uлогин -pпароль

По умолчанию логин root, а пароль тот, что Вы указывали в мастере по настройке сервера. У меня passw0rd.

Открываем командную строку Windows и пишем в:

mysql -uroot –ppassw0rd

После чего будет вот так:

Клиент подсказывает, что признаком концом строки является ; и \g. Именно этими символами и будет оканчиваться каждый запрос. Поэтому сам запрос можно формировать хоть построчно, хоть в одну строку.

Посмотрим, какие базы данных были автоматически созданы на сервере  с помощью команды:

SHOW DATABASES;

information_schema - База данных, которая содержит в себе всю информацию о структуре сервера: все названия таблиц, все названия баз данных, привилегий, индексов, колонок, кодировок и т.д. На самом деле физически это не база данных, просто сервер представляет эти данные как базу данных, чтобы администратор мог пользоваться служебной информацией сервера. mysql - Содержит все настройки сервера MySQL: пользователи, привилегии, часовые пояса, сообщения об ошибках и пр.

test - Пример пустой базы данных.

performance_schema - База данных, содержащая информацию для мониторинга производительности сервера.

 

6.3 Создание базы данных

Самый простой способ создать  пустую БД, надо выполнить запрос к  серверу следующего вида:

CREATE DATABASE [IF NOT EXISTS] имя_базы

В языке SQL все что создается, обычно начинается с CREATE. Необязательный параметр IF NOT EXISTS "Если не существует" будет осуществлять проверку на сосуществование уже имеющейся базы с таким же именем.

 

Выполним запрос:

CREATE DATABASE 09_SBK;

Query OK, 1 row affected (0.01 sec) - Запрос  успешно выполнен, обработан 1 ряд  за 0.01 сек.

Также сервер поддерживает имена с пробелами, только для  этого необходимо само имя заключать  вот в символ: `

Затем посмотрим список созданных БД:

SHOW DATABASES;

 

 

6.4 Удаление базы данных

Удаление объектов в языке SQL, как правило начинается с ключевого слова DROP. Синтаксис удаления БД имеет следующий вид:

DROP DATABASE [IF EXISTS] Имя_БД

Необязательный параметр IF EXISTS, "Если существует" осуществляет удаление только когда БД с указанным именем существует.

Например: чтобы удалить  БД «test_bd» нужно написать запрос

DROP DATABASE test_bd;

 

6.5 Выбор рабочей БД

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

USE 09_sbk;

 

 

 

 

7. Создание таблиц в БД MySQL

Таблицы являются основным механизмом хранения данных. Задать ее структуру можно напрямую запросом либо на основании выборки (в основном для временного хранения). Основные действия над структурой таблицы: добавление/изменение/удаление колонок, изменение типа данных полей. Над информацией содержащийся в  таблице - вставка/изменение/удаление строк.

 

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

Общий синтаксис прямого  указания структуры выглядит так:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <имя_таблицы> [DEFINITION] [TABLE_OPTIONS] [SELECT_STATEMENT]

Значение в квадратных скобках являются не обязательными.

[TEMPORARY] - если указано ключевое слово TEMPORARY, будет создана временная таблица и она будет удалена после окончания сеанса.

[IF NOT EXISTS] - проверка, на существование таблицы с таким же именем.

[DEFINITION] - указывается структура таблицы: имена и типы данных полей, индексы, ключи и пр.

[TABLE_OPTIONS] - дополнительные атрибуты к таблице, так называемые EXTRA параметры: указание движка, генерация суррогатного ключа и пр.

[SELECT_STATEMENT] - поля и их значения в полученные после выборки SELELCT. Это как раз тот случай создания таблиц на основе запросов.

Cоздадим новую таблицу “Prodav”, которая будет содержать в себе информацию о продавцах выполнив запрос:

mysql> CREATE TABLE prodav

-> (name_prodav char(10), city_prodav char (10), comiss float, cod_prodav int(11));

Таблица создана. Чтобы просмотреть ее стурктуру, необходимо выполнить команду DESCRIBE или сокращенно DESC

DESCRIBE prodav;

Аналогично создадим и  выведем описание для таблиц “pokup”  и “sale”.

mysql> CREATE TABLE pokup

-> (cod_pokup int(11), name_pokup char(10), city_pokup char(10), rating int(11), cod_prodav int(11));

mysql> DESCRIBE pokup;

mysql> CREATE TABLE sale

->(cod_s int(11), cost_s float, date_s date, cod_pokup int(11), cod_prodav int(11));

mysql> DESCRIBE sale;

 

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

Например, в таблице «pokup»  добавим новую колонку phone после колонки name_pokup. Для этого используется команда ALTER:

ALTER TABLE pokup ADD phone TINYTEXT NOT NULL AFTER name_pokup;

 

И после этого выполним DESC для просмотра текущей структуры таблицы:

 

7.3 Клонирование структуры таблицы

Для создания полной копии  структуры таблицы в новую, используется оператор LIKE:

CREATE TABLE kopia LIKE pokup;

И просмотреть список всех таблиц:

SHOW TABLES;

 

7.4 Удаление таблицы

Для удаения таблицы используем оператор DROP TABLE:

DROP TABLE kopia;

Клонированная таблица удалена.

 

8. Добавление записей в таблицу БД

Вариант 1:

INSERT INTO <имя_таблицы> (<имя_столбца_1>, <имя_столбца_2>, <имя_столбца_3> ...)      VALUES (<значение_1>, <значение_2>, <значение_3> ...)

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

При таком способе добавления записей, некоторые поля можно опустить. Не указанные поля будут заполнены  автоматически значением "По-умолчанию". Исключение составляют поля, свойство которых указано NOT NULL т.е. пустым такое поле оставлять нельзя.

 
Вариант 2:

INSERT INTO <имя_таблицы> VALUES (<значение_1>, <значение_2>, <значение_3> ...)

К заполнению обязательны  все значения с сохранением последовательности столбцов таблицы и значений соответственно.

 

 

 

 

 

 

Заполним наши таблицы  данными. Сначала заполним таблицу sale

mysql> INSERT INTO sale

-> VALUES (3003, 767, "2005-03-04", 2001, 1001);

mysql> INSERT INTO sale

-> VALUES (3002, 1900, "2006-05-06", 2007, 1004);

mysql> INSERT INTO sale

-> VALUES (3005, 5160, "2006-05-09", 2003, 1002);

mysql> INSERT INTO sale

-> VALUES (3006, 1098, "2006-12-31", 2008, 1007);

mysql> INSERT INTO sale

-> VALUES (3009, 1713, "2005-09-09", 2002, 1003);