Основы SQL

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

Язык SQL (Structure Query Language) позволяет выполнять различные операции с базами данных: создавать таблицы, помещать, обновлять и удалять из них данные, производить запросы из таблиц и т. д.

Рассмотрим основные операторы SQL.

Для тестирования их работы можно воспользоваться клиентом mysql, который расположен в каталоге bin базы данных MySQL.

По умолчанию при установке MySQL создаются два пользователя: анонимный, в качестве имени которого выступает пустая строка, и суперпользователь, имя которого — root.

Для анонимного пользователя по умолчанию разрешен не полный набор SQL-операторов, поэтому из-под него может не получиться создать базу данных. Тогда клиент mysql следует загрузить с правами суперпользователя: mysql -u root.

основные операторы SQL

CREATE DATABASE

Создает новую базу данных:

CREATE DATABASE [IF NOT EXISTS] db_name;

Здесь db_name - имя создаваемой базы данных.

mysql> CREATE DATABASE new_base;

Каждый запрос MySQL в клиенте mysql завершается точкой с запятой (;).

При выполнении запросов из РНР-скрипта отсутствие завершающей точки с запятой не приводит к ошибке.

Ключевая фраза IF NOT EXISTS (не обязательная) сообщает, что базу данных следует создавать, только если база данных с таким именем отсутствует, что позволяет предотвратить завершение запроса ошибкой в РНР-скриптах.

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

mysql> SHOW DATABASES;

USE

Cообщает MySQL, с какой базой данных вы намерены работать.

USE db_name;

Здесь db_name — название выбираемой базы данных.

mysql> USE DATABASE new_base;
Database changed;

CREATE TABLE

Создает новую таблицу в выбранной базе данных.

CREATE TABLE table_name [[create_definition, ...)] [table_options];

Здесь table_name— имя создаваемой таблицы; create_definition— объявление столбца, его типа и атрибутов.

В конце оператора может следовать необязательное указание типа таблицы tabie_options, например, TYPE = MyiSAM.

Создадим таблицу базы данных new_base, которая называется new_table и содержит различные данные о зарегистрированных посетителях:
имя (name),
пароль (passw),
e-mail (email),
Web-адрес сайта посетителя (url),
номер ICQ (icq),
сведения о посетителе (about),
строку, содержащую путь к файлу фотографии посетителя (photo),
время добавления запроса (time), последнее время посещения (lasttime),
статус посетителя – он администратор или обычный посетитель (statususer).

Кроме перечисленных полей в таблице имеется поле id_author, являющееся первичным ключом таблицы. SQL-запрос, создающий эту таблицу

mysql> CREATE TABLE new_table (
-> id_author INT NOT NULL AUTO_INCREMENT,
-> name TINYTEXT,
-> passw TINYTEXT,
-> email TINYTEXT,
-> url TINYTEXT,
-> icq TINYTEXT,
-> about TINYTEXT,
-> photo TINYTEXT,
-> time DATETIME DEFAULT NULL,
-> last_time DATETIME DEFAULT NULL,
-> statususer INT DEFAULT NULL,
-> PRIMARY KEY (id_author)
-> ) TYPE=MyISAM;

Выполнив SQL-команду SHOW TABLES, МОЖНО убедиться, что таблица new_tables успешно создана.

Символ -> означает продолжение строки запроса. Набираться с клавиатуры он не должен, клиент mysql сам автоматически выставляет его при переходе на другую строку.

DESCRIBE

Показывает структуру созданных таблиц:

DESCRIBE table_name;

Здесь table_name— имя таблицы, структура которой запрашивается.

mysql> DESCRIBE new_table;

Более полное описание структуры таблицы authors, включающее права доступа и комментарии, можно получить, воспользовавшись оператором

SHOW FULL COLUMNS FROM new_table;

ALTER TABLE

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

ALTER TABLE table_name alter_spec;

Основные преобразования, выполняемые оператором ALTER TABLE

Синтаксис	Описание команды
ADD create definition [ FIRST | AFTER column_name]	Добавление нового столбца 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

Добавление в таблицу new_table нового столбца test с размещением его после столбца name можно выполнить следующим SQL-запросом

mysql> ALTER TABLE new_table ADD test INT(10) AFTER name;

Выполнив команду DESCRIBE new_table, можно увидеть, что столбец test успешно добавлен после столбца name.

Переименование столбца test в текстовый столбец new_test можно осуществить следующим образом.

mysql> ALTER TABLE new_table CHANGE test new_test text;

Удаление столбца new_test можно осуществить следующим запросом

mysql> ALTER TABLE new_table DROP new_ test;

DROP TABLE

Удаляет одну или несколько таблиц:

DROP TABLE table name [, table name, . . . ];

Для удаления таблицы new_table нужно выполнить следующий SQL-запрос

mysql> DROP TABLE new_table;

DROP DATABASE

Удаляет базу данных со всеми таблицами, входящими в ее состав:

DROP DATABASE database_name

Удалить базу данных new_base можно следующим SQL-запросом

mysql> DROP DATABASE forum;

INSERT INTO... VALUES

Вставляет новые записи в существующую таблицу. Синтаксис команды:

INSERT INTO table_name VALUES (values, ...);

После оператора VALUES В скобках через запятую перечисляются все значения полей таблицы в соответствии с их типами.

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

mysql> INSERT INTO new_table VALUES (0, "John", "423", "John@googler.com",
-> "www.googler.com", "", "programmer", "", "", "", 0, 0);

mysql> INSERT INTO new_table VALUES (0, "Michael", "123", "Michael@googler.com",
-> "www.googler.com", "", "programmer",
-> "", "", "", 407, 0);

mysql> INSERT INTO new_table VALUES (0, "Fred", "212", "Fred@googler.com",
-> "www.googler.com", "", "programmer",
-> "", "", "", 408, 0);

Можно добавить сразу несколько записей с помощью многострочного оператора INSERT.

mysql> INSERT INTO new_table VALUES (0, "John", "423", "John@googler.com",
-> "www.googler.com", "", "programmer",
-> "" , "", "", 0, 0),
-> (0, "Michael", "423", "Michael@googler.com",
-> "www.googler.com", "", "programmer",
-> "", "", "", 407, 0),
-> (0, "Fred", "212", "Fred@googler.com",
-> "www.googler.com", "", "programmer", "",
-> "", "", 408, 0 );

При использовании такого оператора записи приводятся в круглых скобках через запятую после ключевого слова VALUES.

Порядок добавления столбцов можно задавать самостоятельно, воспользовавшись формой оператора INSERT:

INSERT INTO tbl_name (col_namel, col_name2,...) VALUES (valuel, value2, . . .);

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

Так, первичный ключ получает значение NULL, которое интерпретируется для полей с атрибутом AUTO_INCREMENT ТОЧНО так же, как и значение 0 — происходит генерация уникального числа.

Определение порядка добавления столбцов

mysql> INSERT INTO new_table (name, mail, url, about) VALUES ("John", "John@googler.com", "www.googler.com", "programmer");

Можно задавать значения полей в операторе INSERT В форме col_name = value.

mysql> INSERT INTO new_table SET name="John", mail="John@googler.com", url="www.googler.com", about="programmer");

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

DELETE

Удаляет из таблицы table_name записи, удовлетворяющие заданным в definition условиям, и возвращает число удаленных записей.

DELETE FROM table_name [WHERE definition];

Вот как можно удалить все записи из таблицы new_table.

mysql> DELETE FROM new_table;

Важной частью запросов DELETE, UPDATE И SELECT является оператор WHERE, который позволяет задать условия для выбора записей, на которые будут действовать эти команды.

Запрос из следующего листинга удаляет из таблицы посетителя, первичный ключ для которого равен 1.

mysql> DELETE FROM new_table WHERE id_author = 1;

Условия отбора могут быть значительно сложнее.

В следующем листинге удаляются все авторы с паролем "1234", первичный ключ которых превышает значение 10.

mysql> DELETE FROM new_table WHERE passw = "1234" AND id_author > 10;

Оператор AND реализует логическое И.

В запросах можно так же применять логическое ИЛИ — OR.

Алтернативное представление AND - &&;
Алтернативное представление OR - ||;

SELECT

Извлекает данные из одной или нескольких таблиц и имеет синтаксис:

SELECT column, ... [FROM table WHERE definition] [ORDER BY col_name [ASC I DESC], ... ] [LIMIT [ offset ] , rows];

Здесь column — имя выбираемого столбца. Можно указать несколько столбцов через запятую.

Если необходимо выбрать все столбцы, можно просто ввести символ звездочки (*).

Ключевое слово FROM указывает таблицу table, из которой извлекаются записи.

Ключевое слово WHERE определяет, так же как и в операторе DELETE, условия отбора строк.

Ключевое слово ORDER BY сортирует строки запросов по столбцу col_name в прямом (ASC) ИЛИ обратном порядке (DESC).

Ключевое слово LIMIT сообщает MySQL о выводе только rows запросов, начиная с позиции offset.

Для выборки из базы данных при помощи оператора SELECT создадим таблицу forums и добавим в нее несколько записей.

mysql> CREATE TABLE forums (
-> id_forum INT(6) NOT NULL AUTO_INCREMENT,
-> name TINYTEXT,
-> rule TEXT,
-> logo TEXT,
-> pos INT DEFAULT NULL,
-> hide TINYINT(1) DEFAULT NULL,
-> PRIMARY KEY (id_forum)
-> ) TYPE=MyISAM;

В таблице forums присутствуют следующие поля: первичный ключ (id_forum), название раздела (name), правила форума (rule), краткое описание форума (logo), порядковый номер (pos), флаг, принимающий значение 1, если форум скрытый, и 0, если общедоступный (hide).

Добавим несколько строк в таблицу forums

mysql> INSERT INTO forums VALUES (0, "Форум1", "", "", 5, 0 );
-> INSERT INTO forums VALUES (0, "Форум2", "", "", 4, 0);
-> INSERT INTO forums VALUES (0, "ФорумЗ", "", "", 3, 0 );
-> INSERT INTO forums VALUES (0, "Форум4", "", "", 2, 0);
-> INSERT INTO forums VALUES (0, "Форум5", "", "", 1, 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;

В этом запросе выводятся все записи со значением поля idforum, не меньше 2, которые при этом сортируются по значению поля pos.

Для дальнейшего рассмотрения оператора SELECT нам понадобятся еще не- сколько записей в таблице forums.

Разместим группу новых форумов со значением поля hide=1.

Форумы с таким значением поля hide являются скрытыми и не доступны пользователям.

mysql> INSERT INTO forums VALUES (0, "Форумб", "", "", 5, 1 ) ,
-> (0, "Форум7", "", "", 4, 1 ) ,
-> (0, "Форум8", "", "", 3, 1 ) ,
-> (0, "Форум9", "", "", 2, 1 ) ,
-> (0, "Форум10", "", "", 1, 1);

Часто стоит задача группировки значений, которая осуществляется при по- мощи оператора GROUP BY.

mysql> SELECT * FROM forums GROUP BY hide;

UPDATE

Обновляет столбцы таблицы table в соответствии с их новыми значениями в строках существующей таблицы.

UPDATE table SET col_namel=exprl [, col_name2=expr2 ...] [WHERE definition] [LIMIT rows];

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

В выражении WHERE, если оно присутствует, задается, какие строки подлежат обновлению.

В остальных случаях обновляются все строки. Ключевое слово LIMIT позволяет ограничить число обновляемых строк.

В листинге ниже для раздела форума с первичным ключом 2 задается новое название (РНР) И устанавливается атрибут hide равным 1, делая форум невидимым.

UPDATE forums SET name="PHP", hide=1 WHERE id_forum=2;

SHOW

Оператор SHOW может принимать множество форм и предназначен для мониторинга таблиц, баз данных и сервера MySQL.

При помощи оператора SHOW CHARACTER SET можно выяснить кодировки, поддерживаемые сервером MySQL.

Оператор SHOW COLUMN TYPES позволяет вывести перечень информации о типах столбцов, которые использовались при создании таблиц MySQL.

mysql> SHOW FIELDS FROM new_table;

аналогично использыванию альтернативных операторов DESCRIBE authors И SHOW COLUMNS FROM authors.

При помощи оператора SHOW FULL COLUMNS FROM authors можно получить расширенное описание структуры таблицы.

Отображение информации обо всех индексах конкретной таблицы легко получить с помощью листинга

mysql> SHOW INDEX FROM new_table;

Оператор SHOW PROCESSLIST позволяет получить информацию о потоках, выполняющихся на сервере.

Оператор SHOW STATUS позволяет получить значения переменных состояния сервера.

Информацию о таблицах текущей базы данных можно получить при помощи оператора SHOW TABLE STATUS, выводящего многочисленные сведения: имя таблицы, тип, формат хранения строк, среднее число байтов, занимаемых таблицами, реальный размер файла данных таблицы, файла индекса, следующее значение для столбца с атрибутом AUTO_INCREMENT И т.п.

При помощи операторов SHOW DATABASES И SHOW TABLES можно просматривать список баз данных, размещенных на сервере, и список таблиц текущей базы данных.