Работа с базами данных в СУБД MySQL

Размещено на http://www.allbest.ru/

ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ

Государственное образовательное учреждение высшего профессионального образования

САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ

Учебное пособие для вузов

Работа с базами данных в СУБД MySQL

Санкт-Петербург - 2009

Оглавление

• Введение
• 1. Типы данных MySQL
• 1.1 Числовые типы данных
• 1.2 Текстовые типы данных
• 1.3 Типы даты и времени
• 2. Создание базы данных taxi
• 2.1 Запись данных в таблицы
• 3. Запросы к базе данных
• 4. Утилиты MySQL
• 4.1 Утилита командной строки mysql
• 4.2 Утилита mysqldump
• 5. Запросы с указанием критерия отбора данных
• 6. Группировка данных и агрегатные функции
• 7. Запросы к двум и более таблицам
• 8. Команды обновления и удаления данных в таблицах
• 9. Изменение структуры таблицы
• 10. Создание индексов
• 11. Вложенные запросы
• 11.1 Табличные вложенные запросы
• 12. Привилегии в MySQL
• 13. Транзакции
• Список литературы
• Краткие справочные сведения по MySQL
• Введение
• реляционный база табличный пользователь
• В данном пособии описаны методы выполнения основных действий с базами данных в среде MySQL. Рассмотрен синтаксис основных SQL-инструкций для создания и сопровождения реляционной базы данных. Приведены примеры управления привилегиями пользователей по работе с табличными данными. Для отладки и демонстрации примеров был использован сервер MySQL версии 5.0.1.
• В приложении приведен список ключевых слов и выражений для сервера MySQL, что может быть использовано в качестве справочного пособия.
• При написании учебного пособия в качестве базового источника сведений была выбрана книга [ 1 ] , т.к. в данной книге, по мнению авторов, необходимый для изучения материал представлен, во-первых, достаточно полно и, во-вторых, без излишнего нагромождения несущественных для первого ознакомления деталей.
• 1. Типы данных MySQL
• Перед созданием базы данных необходимо точно определить, какие типы данных будут использованы.
• Все типы данных, с которыми работает MySQL, можно разбить на три группы: числовые, текстовые и даты-времени. Рассмотрим эти типы данных по порядку.
• 1.1 Числовые типы данных
• Числовые типы столбцов используются для хранения чисел, все числовые типы можно разбить на два подтипа: для хранения точных чисел и чисел с плавающей точкой. Все числовые типы характеризуются длиной хранимых чисел, а типы с плавающей точкой еще и числом десятичных разрядов. Эти значения указываются после объявления типа столбца, например, FLOAT(10, 2). В примере указано, что запись числа содержит 10 символов и два знака после десятичного разделителя.
• Объявление числовых типов можно заканчивать ключевыми словами ZEROFILL и (или) USIGNED. Ключевое слово USIGNED означает, что столбец содержит только положительные числа или нули. ZEROFILL означает, что число будет отображено с ведущими нулями.
• Типы данных NUMERIC и DECIMAL (DEC) идентичны. Эти типы данных используются для хранения чисел с плавающей точкой. Обычно их используют для хранения денежных значений.
• Тип данных INTEGER можно сократить до INT. Это означает целое число в заданном диапазоне. Для хранения этого типа данных отводится 4 байта, что соответствует числам от -2 147 483 648 до 2 147 483 647. Также существует несколько вариантов типа INTEGER.
• Тип данных TINYINT используется для однобайтных чисел от -128 до 127. Для хранения двухбайтных чисел (от -32 768 до 32 767) используется тип данных SMALLINT, а для хранения самых больших целых чисел (от -263 до 263-1) используется тип BIGINT .
• Числа с плавающей точкой имеют либо тип FLOAT (четырехбайтные числа в диапазоне от -3.4 * 10 38 до 3.4 * 1038 ), либо тип DOUBLE (восьмибайтные числа в диапазоне от -10 -308 до 10308 )
• 1.2 Текстовые типы данных
• В типах данных char и varchar хранятся данные, состоящие из следующих компонентов:
• - знаки в верхнем и нижнем регистре, например a, b и C.
• - цифры, например 1, 2 и 3.
• - специальные знаки, например символы @, & !.
• Тип CHAR используется для хранения строк фиксированной длины. После ключевого слова CHAR обычно указывается длина строки, например, CHAR(50). Если длина строки не указана, то считается, что длина равна одному символу. Максимальная длина поля данного типа равняется 255 символам. Если число переданных в строку символов меньше указанной длины, то строка будет дополнена пробелами, если больше, то обрезана. При возврате значения пробелы будут удалены из строки.
• Тип VARCHAR предназначен для хранения строк переменной длины. Как и в предыдущем типе данных задается максимальная длина строки, например, VARCHAR(30). При этом, более длинные строки, переданные в этот столбец, будут обрезаны.
• Разница двух описанных типов заключается в том, что по строкам с фиксированной длиной выборка идет намного быстрее. И если важна скорость работы с базой данных, то предпочтительно выбирать именно тип CHAR.
• Тип полей TEXT используется для хранения более длинных фрагментов текста, чем допускается предыдущими типами. Тип BLOB означает большой двоичный объект. Эти два типа одинаковы за исключением того, что в типе BLOB сравнение строк идет с учетом регистра символов, а в типе TEXT без учета регистра. Оба типа имеют переменную длину и оба имеют некоторые вариации:
• - TINYTEXT и TINYBLOB могут хранить до 255 символов;
• - TEXT и BLOB могут хранить до 64 килобайт информации;
• - MEDIUMTEXT и MEDIUMBLOB могут хранить до 16 мегабайт;
• - LONGTEXT и LONGBLOB могут хранить до 4 гигабайт.
• Тип ENUM позволяет перечислить набор возможных значений для ввода в поле и хранит только одно значение из представленного списка. Например, ENUM(`m`,`a`,`z`) . Если не указать, какое значение используется в поле по умолчанию, то будет использовано первое значение списка.
• Тип SET аналогичен типу ENUM, но позволяет хранить несколько значений из списка значений в поле.
• 1.3 Типы даты и времени
• Тип DATE применяется для хранения дат в формате ( гггг-мм-дд ), а тип TIME применяется для хранения времени в формате (чч:мм:сс).
• Тип DATETIME объединяет два предыдущих типа и имеет формат (гггг-мм-дд чч:мм:сс ) .
• Поле типа TIMESTAMP обычно используется для хранения даты и времени, когда была создана или изменена соответствующая строка.
• Поле типа YEAR содержит значение года. Возможны две длины: YEAR(2) и YEAR(4) для двух, и четырех цифр года соответственно. При использовании YEAR(2) диапазон дат принимается с 1970 по 2069 годы.
• 2. Создание базы данных taxi
• Создадим базу данных для фирмы, предлагающей услуги такси. В фирме на нескольких автомобилях работает посменно несколько водителей. В базе данных будут храниться сведения о водителях, данные об автомобилях и расписание их работы с указанием фамилий водителей.
• База данных taxi создается командой CREATE DATABASE:
• При правильном вводе сервер выдает сообщение «Query OK, 1 row affected»
• Затем нужно сообщить серверу о том, что вы собираетесь работать с базой данных “taxi”. Это делается командой
• mysql>USE taxi;
• которую нужно подавать каждый раз перед работой с базой данных “taxi”.
• Таблицы создаются командой CREATE TABLE. При создании нужно указать не только имя таблицы, но и ее полное определение, состоящее из определений отдельных полей (столбцов таблицы).
• Создадим таблицу cars, которая будет содержать следующие поля:
• model - название модели автомобиля;
• madein - года выпуска автомобиля;
• reg _number - государственный регистрационный номер;
• color - цвет автомобиля.
• Необходимо помнить, что названия баз данных, таблиц и полей должны задаваться одним словом (без использования символов-разделителей).
• Кроме того, нам понадобится столбец id, однозначно идентифицирующий запись об автомобиле - первичный ключ. Значения этого поля не должны повторяться и не могут быть пустыми, желательно, чтобы эти значения были целыми числами, увеличивающимися на единицу в каждой новой записи.
• mysql>CREATE TABLE cars (
• model CHAR(50) NON NULL,
• madein YEAR(4) NOT NULL,
• reg_number CHAR(12) NOT NULL,
• color CHAR(15) NOT NULL,
• id SMALLINT AUTO_INCREMENT,
• PRIMARY KEY(id)
• Атрибут NOT NULL означает, что все строки таблицы должны иметь значение в этом столбце. Если NOT NULL не указано, поле в отдельных записях может не заполняться (т.е. иметь значение NULL).
• Ключевое выражение PRIMARY KEY после имени столбца определяет, что этот столбец является первичным ключом для таблицы. Данные в этом столбце должны быть уникальными.
• AUTO_INCREMENT - атрибут, используемый для создания столбца с уникальными значениями. Если при вставке строк оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. В каждой таблице может быть не больше одного такого поля.
• MySQL использует следующий алгоритм для инициализации счетчика в столбце id, имеющем атрибут AUTO_INCREMENT: после запуска сервера MySQL при первом запросе на добавление данных в таблицу cars сервер высчитывает максимальное значение в столбце id. Полученное значение увеличивается на единицу, заносится в новую запись и в счетчик. Если таблица была пуста, то счетчик устанавливается в единицу.
• Таблица drivers содержит столбцы для хранения имени (name), отчества (second_name), фамилии (family_name) водителя, даты его рождения (birth), домашнего адреса (address) и даты поступления на работу (startdate).
• mysql>CREATE TABLE drivers (
• name CHAR(30) NOT NULL,
• second_name CHAR(30),
• family_name CHAR(30) NOT NULL,
• birth DATE NOT NULL,
• address CHAR(150) NOT NULL DEFAULT `unknown',
• startdate DATE NOT NULL DEFAULT `2002-01-01',
• id SMALLINT AUTO_INCREMENT,
• PRIMARY KEY (id)
• Кроме того, при создании таблицы для некоторых полей могут применяться дополнительные ключевые слова, уточняющие диапазон возможных значений.
• Ключевое слово UNSIGNED может применяться после указания целочисленного типа и означает, что значение в поле может быть либо положительным, либо нулевым.
• После ключевого слова DEFAULT указывается значение по умолчанию для данного столбца. Например: DEFAULT `no description'.
• Таблица timetable содержит следующие поля: дату поездки (use_on), номер машины (car_number) и идентификатор водителя (driver_number).
• mysql>CREATE TABLE timetable (
• use_on DATE DEFAULT NULL,
• car_number SMALLINT,
• driver_number SMALLINT,
• id SMALLINT AUTO_INCREMENT,
• PRIMARY KEY(id),
• INDEX (car_number),
• INDEX (driver_number),
• FOREIGN KEY (car_number) REFERENCES cars (id),
• FOREIGN KEY (driver_number) REFERENCES drivers (id)
• MySQL поддерживает ссылочную целостность базы с помощью ограничения внешнего ключа FOREIGN KEY. Назначение этого ключа - проверять соответствие значений в столбцах родительского и внешнего ключей. Эти столбцы должны иметь одинаковый тип данных, и только те значения, которые встречаются в родительском ключе, могут использоваться во внешнем ключе. Родительский ключ должен содержать неповторяющиеся значения и не содержать значения NULL.
• Последние два столбца таблицы timetable являются внешними ключами, и MySQL 5 требует, чтобы перед созданием внешних ключей на этих столбцах были созданы индексы.
• Индекс формируется из значений одного или нескольких столбцов таблицы и позволяет ускорить поиск нужных строк по заданным значениям в поле. Для ускорения выполнения запросов индексы обычно создаются на тех столбцах таблицы, которые часто используются в запросах.
• После создания таблиц можно посмотреть их список и структуру командой
• mysql>SHOW TABLES;
• Можно просмотреть структуру таблицы командой
• mysql>DESCRIBE table_name;
• Здесь table_name - имя таблицы. Эта команда позволит просмотреть типы данных столбцов и дополнительные атрибуты, указанные при создании таблицы.
• Например, просмотрим структуру таблицы cars.
• 2.1 Запись данных в таблицы
• Для внесения данных в базу данных используется оператор insert. Самый простой вариант - внесение данных во все поля таблицы:
• mysql>INSERT INTO cars VALUES
• ('Volga', '2003', 'A786YC78', 'white', '1' );
• mysql>INSERT INTO cars VALUES
• ->('Volga', '2002 ' , 'A788YC78', 'yellow', `2' );
• mysql>INSERT INTO cars VALUES
• ->('Volkswagen', '2003', 'A789YC78', 'red', '3' );
• Аналогично можно добавлять данные только в избранные поля, при этом нужно указать имена полей явно:
• mysql>INSERT INTO cars (model, madein, reg_number, color) VALUES
• ->('Renault', '2005' , 'A790YC78', 'white');

• Добавим еще несколько записей в таблицы:
• mysql>INSERT INTO drivers VALUES (
• ->'Stanislav', 'Petrovitch', 'Nikolaev',
• ->'1970-01-22', 'Nevsky prospect 11, flat 54', '2004-02-25', 1);
• mysql>INSERT INTO drivers (name, second_name, family_name, birth,
• ->address, startdate)
• ->VALUES ('Fedor', 'Sergeevitch', 'Sentsov',
• ->'1960-11-02', 'Moskovsky prospect 92, flat 112', '2003-12-30');
• mysql>INSERT INTO timetable VALUES ('2008-11-25', 3,1,1),
• ->('2008-11-30', 2,2,2), ('2008-12-01', 2,2,3);
• Последняя запись обычно делается диспетчером автопарка каждый день утром. Чтобы упростить его работу, можно использовать встроенную в MySQL функцию curdate(), которая возвращает текущую дату как раз в нужном формате:
• mysql> INSERT INTO timetable VALUES (curdate(), 2,2,4);
• Если вы попытаетесь оставить пустыми поля, отмеченные not null и не имеющие значения по умолчанию, то сервер MySQL выдаст сообщение об ошибке и не выполнит команду.
• Кроме того, сервер MySQL 5 проверяет связи между таблицами при вводе записей. То есть нельзя ввести в поле, являющееся внешним ключом, значение, отсутствующее в связанной таблице.
• 3. Запросы к базе данных
• На языке баз данных команды, адресованные базе, называются инструкциями либо запросами. Результаты выполнения запроса обычно выводятся на экран.
• В запросах часто используется команда select, которая позволяет извлечь нужную информацию из одной или нескольких таблиц.
• Для диалога с сервером баз данных запустим клиентскую программу MySQL Command Line Client, а затем подадим команду выбора базы данных:
• mysql>USE taxi
• Теперь запрашиваем данные о модели и цвете автомобилей автопарка:
• mysql>SELECT model, color FROM cars;
• Результат запроса представлен ниже:
• SELECT -- ключевое слово, которое сообщает серверу баз данных о запросе, на извлечение данных;
• model, color -- список столбцов, которые выбираются из таблицы и помещаются в результирующую выборку в памяти;
• FROM - ключевое слово, за которым следует список таблиц, где находится извлекаемая информация.
• Если данных в таблице немного, то можно запросить их все:
• mysql>SELECT * FROM table_name;
• Здесь символ звездочки означает, что следует выбрать значения из всех столбцов. Но чаще запрашивают значения отдельных полей, поскольку обработка такого запроса требует меньших ресурсов.
• Например, запросим все данные из таблицы cars:
• Выясним, какие водители работали за то время, пока ведется учет с помощью базы. Нам нужен только список водителей из таблицы timetable, без повторов.
• distinct -- параметр в запросе select, дающий возможность исключить повторяющиеся данные из результатов запроса.
• mysql>SELECT DISTINCT driver_number FROM timetable;
• Результат запроса будет таким:
• Результат получился действительно без повторений, но ненаглядный -- указаны номера, а не фамилии водителей. Но фамилии находятся в другой таблице. Как выбрать данные из двух и более таблиц, мы узнаем, рассмотрев способы объединения таблиц.
• 4. Утилиты MySQL
• Интерфейс MySQL Command line client, с которым мы работали в предыдущем разделе, удобен, но не позволяет использовать функциональность утилиты mysql полностью. Кроме того, в состав MySQL входит еще ряд полезных утилит, с одной из которых, mysqldump, необходимо познакомиться даже начинающим.
• 4.1 Утилита командной строки mysql
• Утилита mysql может работать в двух режимах: интерактивном и пакетном. Клиентская утилита mysql запускается в интерактивном режиме в DOS-окне, когда в меню выбирается пункт Command line client. До MySQL 5 в комплекте поставки сервера не было такой возможности запуска mysql, и приходилось заходить следующим способом: щелкаем по кнопке Пуск, выбираем пункт меню Выполнить и в появившемся окне Запуск программы набираем cmd. Появляется DOS-окно со строкой, содержащей имя текущего каталога, -- это приглашение ввести команду.
• Утилиты предназначены для работы с сервером и при запуске потребуют указать имя пользователя, под которым мы хотим подключиться к серверу, и пароль. При конфигурировании сервера MySQL мы указали пароль для суперпользователя root, теперь следует им снова пора воспользоваться. Обычно для получения доступа к серверу MySQL необходимо сообщить клиентской программе имя хоста, с которым вы хотите соединиться. Например, утилиту mysql можно запустить следующим образом:
• > mysql -h host_name -u user_name -pyour_pas
• Здесь ключ -h указывает на то, что дальше последует имя хоста, к которому мы хотим подключиться. По умолчанию подключение осуществляется к серверу localhost. Ключ -u означает, что за ним следует имя пользователя, осуществляющего соединение, а после ключа -p указывается пароль. Обратите внимание на то, что пробела перед паролем нет.
• Формат вызова утилиты mysql в нашем случае будет таким:
• > mysql -u root -p
• После ключа -p не указан пароль. Мы знаем, что он нужен, но не хотим вводить его сразу, а дождемся приглашения (нажмем Enter) и введем так, чтобы информация осталась конфиденциальной (набираемый пароль отображается на экране звездочками. После ввода пароля мы должны увидеть приветствие и приглашение командной строки уже самой утилиты mysql:
• Завершение сеанса работы происходит по команде mysql>quit
• Существует возможность повтора ранее поданных команд: щелчками по кнопкам со стрелками вверх и вниз.
• Для работы с утилитой в пакетном режиме следует написать сценарий, состоящий из последовательности команд SQL, которые предстоит исполнить для создания базы и таблиц, входящих в базу. Сценарии принято хранить в файлах с расширением «sql». При исправлении ошибок в сценарии вы запускаете его несколько раз, но уже при повторном запуске получаете сообщение о том, что нельзя создать уже существующую таблицу. Значит, следует предварять создание таблицы командой:
• mysql> DROP TABLE IF EXISTS table_name;
• Тогда сценарий создания и заполнения таблицы cars будет выглядеть так:
• use taxi;
• DROP TABLE IF EXISTS cars;
• CREATE TABLE cars (
• model CHAR(50) NON NULL,
• madein YEAR(4) NOT NULL,
• reg_number CHAR(12) NOT NULL,
• color CHAR(15) NOT NULL,
• id SMALLINT AUTO_INCREMENT,
• PRIMARY KEY(id)
• INSERT INTO cars VALUES
• ('Volga', '2003', 'A786YC78', 'white', '1' ) ,
• ('Volga', '2002 ', 'A788YC78', 'yellow', `2' ) ,
• (Volkswagen', '2003', 'A789YC78', 'red', '3' );
• Скрипт создается в любом текстовом редакторе.После завершения написания скрипта, его можно воспроизвести в сервере MySQL следующим образом:
• >mysql -u root -p < c:\temp\taxi.sql

Здесь taxi.sql - это созданный скрипт-файл.

4.2 Утилита mysqldump

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

Ниже рассмотрены несколько примеров использования предложения WHERE .

Узнаем, какого цвета имеются автомобили "Волга":

SELECT color FROM cars WHERE model = ' Volga';

Узнаем, когда выходил на работу водитель с учетным номером 1:

mysql>SELECT use_on FROM timetable WHERE driver_number = 1;

Определим номера всех автомобилей "Волга" желтого цвета:

mysql> SELECT reg_number FROM cars

-> WHERE model = 'Volga' AND color = 'yellow';

Оператор in определяет принадлежность значения поля заданному множеству значений. Например, чтобы определить номерные знаки автомобилей "Волга" и "Volkswagen", подайте команду

mysql> SELECT reg__number FROM cars WHERE model

IN ('Volga', 'Volkswagen');

Оператор between задает границы, в которые должно попасть значение, чтобы условие запроса выполнялось. За словом BETWEEN пишется начальное значение, затем ключевое слово and и конечное значение. Например, выясним, кто из водителей устроился на работу в период с 1 января 2001 г. по 1 января 2004 г.:

mysql> SELECT family_name FROM drivers

-> WHERE startdate BETWEEN '2001-01-01' AND '2004-01-01';

Оператор LIKE использует простой механизм соответствия . Образец

может состоять из обычного текста и знака процента (%) для указания совпадения с произвольной текстовой строкой. В MySQL соответствия не чувствительны к регистру. Например, шаблон ' Fed%' соответствует любой строке, которая начинается с ' Fed' или с `fed'.

mysql>SELECT * FROM drivers WHERE name LIKE 'Fed%;

6. Группировка данных и агрегатные функции

Группировка и агрегирование данных совершаются с помощью функций, определенных в MySQL. Опишем некоторые из них:

AVG (столбец) - средняя величина значений в указанном столбце

COUNT (столбец) - число ненулевых значений в указанном столбце. Если перед именем столбца указать DISTINCT , то выдается только количество неодинаковых значений;

MIN (столбец) - минимальное значение в столбце;

MAX (столбец) - максимальное значение в столбце;

SUM (столбец) - сумма значений в столбце.

Агрегатные функции можно использовать в запросе SELECT в качестве информации для вывода. Например, чтобы определить количество водителей в автопарке, можно подать запрос

mysql>SELECT COUNT (id) FROM drivers;

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

mysql>SELECT COUNT (DISTINCT driver_number) FROM timetable;

Функция COUNT(*) определит число строк в таблице, но если указать COUNT(field) - определить число значений в поле field, то значения NULL в этом поле учтены не будут.

Предложение GROUP BY позволяет группировать записи, вошедшие в результаты запроса. Это дает возможность объединять поля и агрегатные функции в одном предложении SELECT.

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

mysql>SELECT model, COUNT(model) FROM cars GROUP by model;

Каждая группа состоит из строк с одинаковым значением поля model. результат этого запроса представлен ниже:

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

Упорядочим сведения о водителях по дате рождения:

mysql>SELECT * FROM drivers ORDER BY birth;

В полученной выборке список откроют старшие водители. Если требуется упорядочить данные по убыванию, следует использовать ключевое слово DESC (от англ. Descending - по убыванию):

mysql>SELECT * FROM drivers ORDER BY birth DESC;

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

mysql>SELECT * FROM drivers ORDER BY birth LIMIT 2;

Можно также использовать два параметра: номер строки, с которой следует начать, и количество строк.

mysql>SELECT family_name From drivers LIMIT 2, 3 ;

Последний запрос можно интерпретировать так: "Выбрать фамилии водителей и в результате отобразить три строки, начиная со строки 2". Нумерация строк начинается с нуля.

7. Запросы к двум и более таблицам