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

Оператор alter table используется для изменения структуры таблицы. Преобразования, осуществляемые с помощью этого оператора, представлены ниже:

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

mysql> ALTER TABLE drivers ADD COLUMN phone_number BIGINT;

В данном примере столбец добавляется в конец таблицы, но можно было бы добавить ключевое слово first, тогда столбец стал бы первым, а можно ука­зать after birth, поместив его тем самым после указанного столбца birth.

Допустим, вы обнаружили, что в поле для указания адреса вводимые данные не помещаются, т. к. адрес длиннее 150 выделенных для него символов. Изменим определение столбца:

mysql> ALTER TABLE drivers MODIFY address char(200);

Создание индексов

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

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

Когда столбец помечается как ключ, создается индекс. Индекс хранит список
значений ключа и указатели на записи, содержащие эти значения. При создании индекса таблица уже должна содержать столбцы, по которым создается
индекс.

В базе таксопарка в таблице cars первичным ключом является учетный номер машины. Запросы же к базе могут быть основаны на поиске по государственному регистрационному номеру. Чтобы повысить скорость выполнения таких запросов, следует создать индекс по столбцу reg_number, в котором записан регистрационный номер автомобиля:

mysql>CREATE INDEX nomer ON cars(reg_number);

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

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

Вложенные запросы

Вложенный запрос создает результирующую таблицу, данные которой используются внешним запросом. Вложенный запрос помещается в скобках после выражения WHERE.

Создадим вложенный запрос с целью выяснить, на каком автомобиле работал определенный водитель 25 ноября 2008 года:

mysql> SELECT model, reg_number FROM cars

–> WHERE id = ( SELECT car_number

–>FROM timetable

–>WHERE date = ‘2008-11-25’ AND driver_number=1);

Сначала выполняется команда SELECT внутри скобок. MyQSL получает результат вложенного запроса и использует его в условии в выражении WHERE во внешнем запросе SELECT. При этом результаты внутреннего запроса не отображаются. Конструкция запроса в приведенном примере подразумевает, что результат внутреннего запроса должен представлять собой единственное значение. В противном случае в результате выполнения этого запроса появится сообщение об ошибке: “Error 1241: Subquery returns more than 1 row”. Вложенные запросы могут содержать команды SELECT, INSERT и UPDATE, а также SET.

11.1. Табличные вложенные запросы

В случае, когда результатом вложенного запроса является не одно значение, а таблица, синтаксис запроса изменяется.

Например, определим, на каких автомобилях работали водители 25 и 30 ноября 2008:

SELECT model, reg_number

FROM cars

WHERE id IN

(

(SELECT car_number FROM timetable

WHERE use_on = ‘2008-11-25’),

(SELECT car_number FROM timetable

WHERE use_on = ‘2008-11-30’)

);

Внешний запрос выбирает модель и регистрационный номер тех автомобилей, учетные номера которых указаны в таблице timetable в записях, относящихся к 25 и 30 ноября 2008 года. Перечень условий для выражения IN пишется через запятую и помещается во внешние скобки. Сначала выполняются вложенные запросы, результаты которых формируют набор значений для выражения IN.

Привилегии в MySQL

При подключении к серверу MySQL пользователь указывает имя и пароль, а сервер проверяет, имеет ли клиент право получить доступ к серверу, и в случае успеха наделяет пользователя соответствующими привилегиями. Данные о привилегиях пользователей хранятся в системной базе mysql.

Проверка пользователя осуществляется по трем полям таблицы user (host, user и password) базы mysql. Сервер устанавливает соединение только в том случае, если находит в таблице user запись, в которой имя пользователя и пароль совпадают с введенными значениями.

Посмотреть, в каком виде хранятся пароли в таблице user можно при помощи следующих команд:

mysql>USE mysql

mysql>SELECT password FROM user;

Ознакомиться со всеми таблицами базы mysql можно подав команду

mysql>show tables;

После установления соединения сервер начинает сеанс работы с пользователем. Для каждого поступающего запроса сервер проверяет, имеется ли у пользователя достаточно привилегий для выполнения запроса. Информация о привилегиях находится в таблицах — user, db, host, tables_priv или columns_priv.

Список привилегий представлен ниже:

Сервер MySQL считывает со­держимое этих таблиц во время запуска и в случаях, когда изменения в при­вилегиях вступают в силу.

Привилегии SELECT, INSERT, UPDATE И DELETE позволяют выполнять операции над строками таблиц баз данных. Привилегия index обеспечивает создание или уничтожение индексов. Привилегия alter позволяет использовать ко­манду alter table. Привилегии create и drop позволяют создавать новые или уничтожать существующие базы данных и таблицы. Привилегия GRANT gram позволяет вам предоставлять другим пользователям привилегии, которыми обладаете вы сами.

Остальные привилегии используются для администрирования SQL – сервера.. Команда shutdown завершает работу сервера.

При запуске сервера MySQL все сведения о привилегиях пользователей загружаются в память, и с этого момента привилегии вступают в силу.

Команды grant и revoke позволяют системным администраторам создавать пользователей MySQL а также предоставлять права пользователям или лишать их прав на четырех уровнях.

· Глобальный уровень.

Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.

· Уровень базы данных.

Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql .db и mysql .host.

· Уровень таблицы.

Привилегии таблицы применяются ко всем столбцам указанной таблицы.
Эти привилегии хранятся в таблице mysql. tables_priv.

· Уровень столбца.

Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql .columns_priv.

Команда grant позволяет добавлять новых пользователей сервера:

mysql> GRANT SELECT, INSERT ON taxi.* TO Mike@localhost

IDENTIFIED BY 'secret';

Эта команда дает права insert и select на базу taxi (на все таблицы - это задается символом звездочки) пользователю Mike, который подключается локально (localhost), причем, при подключении к серверу пользователь должен указать пароль 'secret^’. Пароль хранится на сервере в зашифрованном виде.

mysql> GRANT ALL PRIVILEGES ON *.* TO Ann@"%"

->IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Здесь Anna получает все права (all privileges) на все таблицы всех баз сервера (* . *), причем подключаться она может как локально, так и удаленно через сеть (это указано символом % после Anna@). Вдобавок Anna может передавать свои права другим пользователям (with grant option), т. е. сама может подавать команду grant и создавать новых пользователей системы.

mysql> GRANT USAGE ON *.* TO dummy@localhost

IDENTIFIED BY ‘password’;

Пользователь dummy, созданный в этом примере, может подсоединяться
к серверу, но только с локального компьютера. Привилегия USAGE
означает, что у пользователя отсутствуют все другие привилегии. Такое может потребоваться, когда человек принят на работу, но обязанности его еще не определены. Предполагается, что относящиеся к базам данных привилегии будут назначены позже. Не удастся создать пользователя с пустым паролем — система не позволяет это из соображений безопасности.

Команда REVOKE позволяет администратору лишать пользователей прав:

REVOKE SELECT (use_on) ON taxi.timetable FROM Mike @ localhost;

Пользователь Mike не может просматривать значения поля use_on в таблице timetable.

Команда set может устанавливать различные опции, влияющие на работу
сервера или клиента. Пароль для пользователя root также можно задать с помощью этой команды:

mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password’) ;

Пользователи могут работать и непосредственно с таблицами назначения привилегий:

mysql> USE mysql;

Mysql> UPDATE user SET Password=PASSWORD('new_password')

-> WHERE user='root';

После этого следует подать команду, заставляющую сервер перечитать таблицы привилегий — тогда они вступят в силу:

mysql> FLUSH PRIVILEGES;

Если пароль задается при помощи оператора grant. . .identified by, нет необходимости использовать функцию password (). Эта команда самостоятельно производит шифрацию пароля, поэтому пароль следует указывать в простом текстовом виде, например, таким образом:

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

Изменения, которые вносятся в таблицы назначения привилегий при помощи команд grant, revoke или set password, учитываются сервером сразу после выполнения этих инструкций.

Транзакции

В реляционных базах данных защита целостности данных реализована через механизмы транзакций и блокировки таблиц.

Транзакция – группа операций, которая может быть выполнена либо полностью успешно, с обеспечением целостности данных и независимо от параллельно выполняющихся других транзакций, либо не выполнена вообще, и тогда она не должна произвести вообще никакого эффекта. Транзакция похожа на беспосадочный перелет через океан тем, что она не может быть выполнена наполовину. Если видим, что не долететь, то поворачиваем назад. Сервер изолирует одновременные потоки друг от друга, ограничивая их доступ к модифицируемым данным. Сервер пытается выполнять транзакции так, будто они происходят последовательно, а не параллельно.

В конце транзакции происходит либо ее отмена, либо подтверждение. Отмена транзакции называется откатом (rollback). Подтверждение транзакции называется фиксацией (commit). Фиксация транзакции регистрируется в специальных журнальных файлах, информация из которых считывается при сбоях в работе компьютера и используется для восстановления состояния базы данных до сбоя.

В MySQL можно создавать базы данных на основе таблиц нескольких типов. Транзакции применимы лишь к некоторым типам таблиц.

В MySQL 3 по умолчанию создаются таблицы типа MYISAM, в которых обеспечение целостности данных при одновременных запросах происходит за счет блокировки таблиц целиком в случае необходимости.

В MySQL 5 по умолчанию создаются таблицы типа InnoDB (при работе в Windows), в которых реализован механизм транзакций.

Чтобы создать таблицу типа InnoDB, можно указать ENGINE = InnoDB или

TYPE = InnoDB в SQL-запросе на создание таблицы:

mysql>CREATE TABLE customers (a INT, b CHAR (20), INDEX (a))

->ENGINE=InnoDB;

mysql>CREATE TABLE customers (a INT, b CHAR (20), INDEX (a))

->TYPE=InnoDB;

По умолчанию каждый клиент соединяется с сервером MySQL в режиме autocommit, в котором автоматически фиксируется каждый одиночный SQL-запрос клиента. Для того чтобы использовать транзакции, состоящие из нескольких запросов, нужно подать команду

mysql>SET AUTOCOMMIT = 0;

а затем использовать команды COMMIT или ROLLBACK для фиксации или отмены транзакции. Следующие два примера демонстрируют фиксацию и отмену транзакций:

Вставка данных о Париже прошла успешно, в этом можно убедиться, запросив выборку из таблицы до отката. Но в результате отката эти данные не были зафиксированы в базе и не были сохранены, что и показывает последняя выборка. То есть мы можем посмотреть какой эффект произведет то или иное действие, а потом отменить его – произвести откат.