|
|
Взаимодействие PHP и MySQL «Команды SQL» |
||
|
|||
В этом разделе мы изучим основные
команды языка SQL. Для этого на компьютере с установленным MySQL необходимо
запустить клиента mysql, в окне которого можно вводить команды SQL.
Примечание
Команды SQL не чувствительны к
регистру, но традиционно они набираются прописными буквами.
Типы полей базы данных
Список наиболее часто встречающихся
типов приведен в таблицах 13.6 - 13.8. Для многих типов данных задается
максимальная ширина отображения, указываемая в скобках, которую мы далее будем
обозначать символом max. К примеру, запись INT(2) означает, что значение
данного поля не может превышать 100.
К числовым типам относятся целые
числа и числа с плавающей точкой. Для чисел с плавающей точкой, кроме
максимальной ширины отображения можно также указывать число значащих цифр после
запятой, далее обозначаемое символом P.
Таблица 13.6. Числовые типы
|
Тип |
Описание |
|
TINYINT[(max)] |
Очень маленькие целые числа диапазона
-127…128. |
|
SMALLINT[(max)] |
Маленькие целые числа диапазона
-32768…32767. |
|
MEDIUMINT[(max)] |
Средние целые числа. |
|
INT[(max)] |
Обычные целые числа. |
|
FLOAT[(max,P)] |
Числа с плавающей точкой одинарной
точности. |
|
DOUBLE[(max,P)] |
Числа с плавающей точкой двойной
точности. |
|
DECIMAL[(max,P)] |
Числа с плавающей точкой, приведенные
к типу char. |
Типы даты и времени приведены в таблице 13.7.
Таблица 13.7. Типы даты и времени
|
Тип |
Описание |
|
DATE |
Дата в формате ГГГГ-ММ-ДД. |
|
TIME |
Время в формате ЧЧ-ММ-СС. |
|
DATETIME |
Дата и время в формате ГГГГ-ММ-ДД
ЧЧ-ММ-СС. |
|
YEAR |
Год в формате ГГ или ГГГГ. |
|
TIMESTAMP |
Метка времени для отсчетов по
транзакциям в формате ГГГГ-ММ-ДД ЧЧ-ММ-СС. |
Основные строковые типы приведены
в таблице 13.8.
Таблица 13.8. Строковые типы
|
Тип |
Описание |
|
CHAR (len)[BINARY] |
Строки с длиной len, которое не
превышает 255 символов. Ключевое слово BINARY указывает на то, что данные
должны обрабатываться независимо от регистра. |
|
CHAR |
Синоним CHAR(1). |
|
VARCHAR (len)[BINARY] |
Синоним CHAR(len)за исключением того,
что строки могут быть произвольной длины. |
|
TEXT |
Строки с максимальной длиной символов
равной 65535. Данные этого типа чувствительны к регистру. |
|
BLOB |
Двоичные строки с максимальной длиной
символов равной 65535. Тип BLOB (binary large object - большой двоичный
объект) предназначен для хранения двоичных данных, в том числе изображений и
звуковых последовательностей. |
CREATE
DATABASE
Эта команда создает новую базу данных:
CREATE DATABASE db_name;
Здесь db_name
является именем создаваемой базы данных. Для того чтобы создать новую базу
данных forum, наберите в строке-приглашении клиента MySQL mysql> эту команду и укажите название базы данных:
mysql> CREATE DATABASE forum;
Примечание
Каждый запрос MySQL завершается
точкой с запятой.
При успешном выполнении команды MySQL
выдаст строку, в которой сообщается, что этот запрос выполнен успешно и
показано время, затраченное на выполнение запроса:
Query
OK, 1 row affected (0.02 sec)
Далее в тексте строку MySQL с
результатом выполнения команды мы будем приводить непосредственно в
соответствующем листинге.
Для того, чтобы убедится, что база
данных forum успешно создана, можно выполнить команду SHOW DATABASES, которая покажет, какие базы данных существуют на вашем компьютере:
mysql> SHOW DATABASES;
Как видим, среди различных баз данных на
компьютере автора присутствует и только что созданная база данных forum:
Замечание
Команда SHOW DATABASES является внутренней командой MySQL, отсутствующей в
стандарте SQL и неподдерживаемой другими базами данных.
Замечание
Изначально, в МуSQL присутствует
только две базы данных: test и mysql. В последней хранится системный каталог, описывающий
внутреннюю структуру СУБД MySQL.
USE
Для того чтобы начать работу с
таблицами, необходимо сообщить MySQL с какой базой данных вы намерены работать.
Это осуществляется при помощи команды USE:
USE db_name;
Здесь db_name -
название выбираемой базы данных. Выберем созданную базу forum:
mysql>
CREATE DATABASE forum;
Database changed;
CREATE
TABLE
Команда CREATE TABLE
создает новую таблицу в выбранной базе данных и которая в простейшем случае
имеет следующий синтаксис:
CREATE
TABLE table_name [(create_definition, ...)]
Здесь table_name
- имя создаваемой таблицы.
Создадим первую таблицу базы данных forum,
которая называется authors и содержит различные данные об зарегистрированных
посетителях форума: ник (name), пароль (passw), e-mail (email), Web-адрес сайта посетителя (url),
номер ICQ (iсq), сведения об посетителе (about),
строку содержащую путь к файлу фотографии посетителя (photo),
время добавления запроса (time), последнее время посещения форума (last_time),
статус посетителя - является ли он модератором, администратором или обычным
посетителем (statususer). Кроме перечисленных полей в таблице имеется поле id_author,
являющийся первичным ключом таблицы. SQL-запрос, создающий эту таблицу приведен
в листинге:
mysql> CREATE TABLE authors (
id_author int(6)
NOT NULL auto_increment,
name text,
passw text,
email text,
url text,
iсq text,
about text,
photo text,
time datetime default NULL,
last_time datetime default NULL,
themes int(10) default NULL,
statususer int(2) default NULL,
PRIMARY KEY (id_author)
)
TYPE=MyISAM;
Выполнив SQL-команду SHOW TABLES,
можно убедиться, что таблица authors успешно создана
Давайте теперь аналогичным образом
создадим другие таблицы.
Следующей по порядку идет таблица
forums, в которой содержатся данные о разделах форума.
Примечание
Для удобства на форуме может быть
создано несколько различных разделов. К примеру, на форуме по языкам
программирования для того, чтобы не смешивать темы, относящиеся к различным
языкам, имеет смысл создать следующие разделы: С++, PHP, Java и т. д.
В таблице forums
присутствуют следующие поля: первичный ключ (id_forum),
название раздела (name), правила форума (rule),
краткое описание форума (logo), порядковый номер (pos),
флаг, принимающий значение 1, если форум скрытый и 0, если общедоступный (hide).
Вот SQL-запрос, создающий таблицу forums
mysql>
CREATE TABLE forums (
id_forum int(6) NOT NULL auto_increment,
name text,
rule text,
logo text,
pos int(6) default NULL,
hide int(1) default NULL,
PRIMARY KEY
(id_forum)
)
TYPE=MyISAM;
Структура форума может быть следующей:
имеются список разделов, переход по которым приводит посетителя к списку тем
раздела. При переходе по теме посетитель приходит к обсуждению этой темы,
состоящих из сообщений других посетителей. Теперь создадим таблицу themes,
содержащую темы форума:
mysql> CREATE TABLE themes (
id_theme int(11)
NOT NULL auto_increment,
name text,
author text,
id_author int(6) default NULL,
hide int(1) default NULL,
time datetime default NULL,
id_forum int(2) default NULL,
PRIMARY KEY (id_theme)
)
TYPE=MyISAM;
В таблице themes
присутствуют следующие поля: первичный ключ (id_theme),
название темы (name), автор темы (author), внешний ключ к
таблице авторов (id_author), флаг, принимающий значение 1, если тема отмечена скрытой
и 0, если отображается (hide) - это поле необходимо для модерирования, время
добавления темы (time), внешний ключ к таблице форумов (id_forum),
для того чтобы определить к какому разделу форума относится данная тема.
В таблице themes
нормализация проведена частично, она содержит два внешних ключа: id_author
и id_forum для таблиц посетителей и списка форумов, в тоже время
в ней дублируется имя автора author, присутствующее также в таблице посетителей authors
под именем name. Этот случай является примером денормализации
предназначенной для того, чтобы не запрашивать каждый раз таблицу авторов при
выводе списка тем и их авторов, чтобы обеспечить приемлемую скорость работы
форума.
Создадим последнюю таблицу posts, в
которой хранятся сообщения:
mysql>
CREATE TABLE posts (
id_post int(11) NOT NULL auto_increment,
name text,
url text,
file text,
author text,
id_author int(6) default NULL,
hide int(1) default NULL,
time datetime default NULL,
parent_post int(11) default NULL,
id_theme int(11) default NULL,
PRIMARY KEY
(id_post)
)
TYPE=MyISAM;
В таблице posts
присутствуют следующие поля: первичный ключ (id_post),
тело сообщения (name), необязательная ссылка на ресурс, которую автор
сообщения может ввести при добавлении сообщения (url),
путь к файлу прикрепляемому к сообщению (file), имя автора (author),
внешний ключ к таблице авторов (id_author), флаг, принимающий значение 1, если сообщение
отмечено как скрытое и 0, если он отображается (hide) -
это поле необходимо для модерирования, время добавления сообщения (time),
сообщение ответом на которое является данное сообщение (parent_post),
если это первое сообщение в теме - это поле равно 0, внешний ключ к тем (id_theme),
для того чтобы определить к какой теме относится сообщение.
Убедимся, что все таблицы успешно
созданы, выполнив команду SHOW
TABLES.
DESCRIBE
Команда DESCRIBE
показывает структуру созданных таблиц и имеет следующий синтаксис:
DESCRIBE tаble_name
Здесь tаble_name
- имя таблицы структура которой запрашивается.
Замечание
Команда DESCRIBE не входит в стандарт SQL и является внутренней командой СУБД MySQL.
Давайте посмотрим, к примеру, структуру
таблицы forums, выполнив следующий SQL-запрос:
mysql> DESCRIBE forums;
После выполнения этой команды,
интерпретатор mysql выведет следующую таблицу
ALTER TABLE
Команда ALTER TABLE
позволяет изменить структуру таблицы. Эта команда позволяет добавлять и удалять
столбцы, создавать и уничтожать индексы, переименовывать столбцы и саму
таблицу. Команда имеет следующий синтаксис:
ALTER TABLE table_name alter_spec
Параметр alter_spec имеет значения, представленные
в таблице
|
Синтаксис |
Описание команды |
|
ADD
create_definition [FIRST|AFTER column_name] |
Добавление нового столбца
create_definition. create_definition представляет собой название нового
столбца и его тип. Конструкция FIRST добавляет новый столбец перед столбцом
column_name. Конструкция AFTER добавляет новый столбец после столбца
column_name. Если место добавления не указано, по умолчанию столбец
добавляется в конец таблицы. |
|
ADD
INDEX [index_name] (index_col_name,...) |
Добавление индекса index_name для столбца index_col_name. Если имя индекса index_name не указывается, ему
присваивается имя совпадающее с именем столбца index_col_name. |
|
ADD
PRIMARY KEY (index_col_name,...) |
Делает столбец index_col_name или
группу столбцов первичным ключом таблицы. |
|
CHANGE
old_col_name new_col_name type |
Изменение столбца с именем
old_col_name на столбец с именем new_col_name и типом type. |
|
DROP col_name |
Удаление столбца с именем col_name. |
|
DROP PRIMARY KEY |
Удаление первичного ключа таблицы. |
|
DROP INDEX index_name |
Удаление индекса index_name. |
Добавим в таблицу forums
новый столбец test, разместив его после столбца name.
mysql>
ALTER TABLE forums ADD test int(10) AFTER name;
Выполнив команду DESCRIBE forums, можно увидеть, что столбец test успешно добавлен после столбца name
Давайте переименуем созданный столбец test в
текстовый столбец new_test
mysql>
ALTER TABLE forums CHANGE test new_test text;
Как видно из рисунка, столбец успешно
переименован:
При изменении только типа столбца, а не
его имени, указание имени все-равно необходимо, хотя в этом случае оно будет
фактически повторяться.
mysql>
ALTER TABLE forums CHANGE new_test new_test int(5) not null;
Результат выполнения этого запроса
приведен на рисунке:
Теперь удалим столбец new_test:
mysql>
ALTER TABLE forums DROP new_test;
Как видно из рисунка, после удаления
этого столбца таблица forums приобрела исходную структуру:
DROP
TABLE
Команда DROP TABLE
предназначена для удаления одной или нескольких таблиц:
DROP
TABLE table_name [ ,table_name,...]
К примеру, для удаления таблицы forums
нужно выполнить следующий SQL-запрос:
mysql>
DROP TABLE forums;
DROP DATABASE
Команда DROP DATABASE удаляет базу данных со всеми таблицами входящими в её состав:
DROP DATABASE database_name
Удалим, например, базу данных forum:
mysql>
DROP DATABASE forum;
INSERT INTO…VALUES
Команда INSERT…VALUES вставляет новые записи в существующую таблицу. Синтаксис команды:
INSERT INTO table_name VALUES (values,…)
После оператора VALUES в
скобках через запятую перечисляются значения соответствующих полей таблицы в
соответствии с их типами.
Давайте вставим в базу данных authors
несколько записей, в которых расположена информация об зарегистрированных
посетителях форума:
mysql>
INSERT INTO authors VALUES (1, 'Maks', '123', ' maks@mail.ru ',
' www.softtime.ru ', '', 'программист',
'', '', '', 0, 0);
mysql>
INSERT INTO authors VALUES (2, 'Igor', '123', 'igor@mail.ru',
'http://www.softtime.ru', '',
'Программист', '', '', '', 407, 0);
mysql>
INSERT INTO authors VALUES (3, 'Sergey', '212', 'sergey@mail.ru',
'http://www.softtime.ru', '',
'Дизайнер', '', '', '', 408, 0);
DELETE
DELETE
FROM table_name [WHERE definition]
Команда DELETE
удаляет из таблицы table_name записи, удовлетворяющие заданным в definition
условиям, и возвращает число удаленных записей.
Вот как можно удалить все записи из
таблицы authors:
mysql> DELETE FROM authors;
Важной частью запросов DELETE, UPDATE и SELECT
является оператор WHERE, который позволяет задать условия для выбора записей,
на которые будут действовать эти команды. Следующий запрос удаляет из таблицы
посетителя, первичный ключ для которого равен 1:
mysql>
DELETE FROM authors WHERE id_author = 1;
Условия отбора могут быть значительно
сложнее, так в листинге 13.16 удаляются все авторы с паролем '123' и первичный
ключ которых превышает 10:
mysql>
DELETE FROM authors WHERE passw = '123' AND id_author > 10;
Оператор AND является логическим
"и". В запросах можно так же применять логическое или
"или".
SELECT
Команда SELECT
предназначена для извлечения строк данных из одной или нескольких таблиц и
имеет в общем случае следующий синтаксис:
SELECT
column,...
[FROM
table WHERE definition]
[ORDER
BY col_name [ASC | DESC], ...]
[LIMIT
[offset], rows]
Здесь column - имя выбираемого столбца. Можно указать несколько столбцов через запятую. Если
необходимо выбрать все столбцы можно просто указать знак звёздочки *. Ключевое
слово FROM указывает таблицу table из
которой извлекаются записи. Ключевое слово WHERE определяет, так же
как и в операторе DELETE определяет условия отбора строк. Ключевое слово ORDER BY
сортирует строки запросов по столбцу col_name в прямом (ASC) или обратном порядке (DESC).
Ключевое слово LIMIT сообщает MySQL об выводе только rows
запросов начиная с позиции offset.
Давайте вставим в таблицу forums
несколько записей, чтобы потом на их примере выполнять различные варианты
команды SELECT.
mysql>
INSERT INTO forums VALUES (1, 'Форум1', '', '', 1, 0);
mysql>
INSERT INTO forums VALUES (2, 'Форум2', '', '', 2, 0);
mysql>
INSERT INTO forums VALUES (3, 'Форум3', '', '', 3, 0);
mysql>
INSERT INTO forums VALUES (4, 'Форум4', '', '', 4, 0);
mysql>
INSERT INTO forums VALUES (5, 'Форум5', '', '', 5, 0);
Для того чтобы посмотреть всю таблицу forums
выполняется следующий запрос:
mysql> SELECT * FROM forums;
Выбираем все столбцы из таблицы forums
без ограничений. Результат показан на рисунке:
Можно выбрать не все столбцы таблицы, а
лишь часть, для этого необходимо явно задать список выбираемых столбцов:
mysql>
SELECT id_forum, name FROM forums;
В этом случае MySQL выведет лишь два
столбца с первичным ключом id_forum и названием форума name
Оператор LIMIT
используется для ограничения количества строк, возвращенных командой SELECT. К
примеру:
mysql> SELECT * FROM forums LIMIT 3;
В результате этого запроса будет
выведено только первые 3 записи из 5
Оператор LIMIT
может также принимать два числовых аргумента, которые должны быть целыми
числами. В этом случае последний аргумент задает максимальное количество
возвращаемых строк, а первый сообщает MySQL начиная с какой по счёту строки
производить отсчёт
mysql>
SELECT * FROM forums LIMIT 1,3;
В этом случае будут возвращены строки 2,
3 и 4
Оператор WHERE
применяется в команде SELECT точно так же, как и в команде DELETE.
Выберем из таблицы только те записи, у которых значение id_forum
больше 2:
mysql>
SELECT * FROM forums WHERE id_forum > 2;
Результат показан на слудующем рисунке:
Порядок сортировки выводимых записей
можно задавать при помощи оператора ORDER
BY:
mysql>
SELECT * FROM forums WHERE id_forum > 2 ORDER BY pos;
В этом запросе выводятся все записи со
значением поля id_forum не меньше двух, которые при этом сортируются по
значению поля pos. Результат такого запроса показан на рисунке:
UPDATE
UPDATE table
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE
definition]
[LIMIT rows]
Команда UPDATE
обновляет столбцы таблицы table в соответствии с их новыми значениями в строках
существующей таблицы. В выражении SET указывается, какие именно столбцы следует
модифицировать и какие величины должны быть в них установлены. В выражении WHERE,
если оно присутствует, задается, какие строки подлежат обновлению. В остальных
случаях обновляются все строки. Ключевое слово LIMIT
позволяет ограничить число обновляемых строк.
В следующим листинге разделу форума с
первичным ключом 2 устанавливается новое название (PHP) и устанавливается
атрибут hide равным 1, делая форум невидимым.
UPDATE
forums SET name='PHP', hide=1 WHERE id_forum=2;
SHOW
С этой командой мы уже встречались
ранее, когда выполняли запросы вида show
databases и show tables
для получения списка баз данных и таблиц в выбранной базе данных. Рассмотрим
еще несколько вариантов использования этой команды.
Вывести список всех столбцов выбранной
таблицы можно при помощи следующего запроса:
mysql>
SHOW FIELDS FROM authors;
Результат приведен на следующем рисунке:
Можно также отобразить информацию обо
всех индексах конкретной таблицы:
mysql>
SHOW INDEX FROM authors;
Выполнив команду SHOW PROCESSLIST можно увидеть список всех выполняющихся в системе запросов:
