Введение основы T-SQL и примеры, которые будут разобраны
В данной статье будут подробно рассмотрены основные объекты, создаваемые на языке T-SQL, которые покрывают 80% всех задач по обработке данных (триггеры, хранимые процедуры, табличные функции):
Таблицы базы данных, в которых хранятся собственно данные, содержат:
- cтроки: каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
- cтолбцы: каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер
Представления (виртуальные таблицы) или Views — создаются для отображения данных из таблиц. Подобно реальным таблицам, Views содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект
Хранимые процедуры (Stored Procedures) – это группа команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое Хранимые процедуры (Stored Procedure) являются программами, хранящимися в базе данных и выполняющими различные действия, обычно с данными из базы данных, хотя процедуры могут и не осуществлять никаких обращений к базе. К хранимым процедурам могут обращаться любые программы, работающие с базой данных, к ним также могут обращаться и другие хранимые процедуры и триггеры. Допустима рекурсия, когда хранимая процедура обращается к самой себе. Хранимые процедуры выполняются на стороне сервера, а не на стороне клиента. Во многих случаях это может резко снизить сетевой трафик при решении различных задач работы с большой по объему базой данных и повысить производительность системы.
Триггеры (Triggers) – специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы. Триггеры (trigger) так же как и хранимые процедуры, являются программами, выполняющимися на стороне сервера. Однако напрямую обращение к триггерам невозможно. Они автоматически вызываются при наступлении некоторого события базы данных — при добавлении, изменении и или удалении строк конкретной таблицы. Триггеры могут вызываться при соединении с базой данных.
Функции, определенные пользователем (user defined functions, UDF) — это конструкции, содержащие исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение/набор данных. К функциям можно обращаться из триггеров, хранимых процедура и из других программных компонентов.
Description
вставляет новые строки в таблицу. Можно вставить одну или несколько строк, указанных в выражениях значений, или ноль или несколько строк, полученных в результате запроса.
Имена целевых столбцов могут быть перечислены в любом порядке. Если список имен столбцов не задан, по умолчанию используются все столбцы таблицы в их объявленном порядке; или первые имен столбцов, если есть только столбцов, предоставленных предложением или . Значения, предоставленные предложением или , связаны с явным или неявным списком столбцов слева направо.
Каждый столбец,не присутствующий в явном или неявном списке столбцов,будет заполнен значением по умолчанию,либо объявленным по умолчанию,либо нулевым,если такового нет.
Если выражение для какого-либо столбца не имеет правильного типа данных,будет предпринята попытка автоматического приведения типа.
в таблицы, в которых отсутствуют уникальные индексы, не будет заблокирован параллельной активностью. Таблицы с уникальными индексами могут блокироваться, если параллельные сеансы выполняют действия, которые блокируют или изменяют строки, соответствующие вставляемым значениям уникального индекса; подробности описаны в Разделе 64.5 . можно использовать для указания действия, альтернативного созданию ограничения уникальности или ошибке нарушения ограничения исключения. (См. ниже.)
Необязательное предложение заставляет вычислить и вернуть значение (я) на основе каждой фактически вставленной (или обновленной) строки, если использовалось предложение ). Это в первую очередь полезно для получения значений, которые были предоставлены по умолчанию, таких как порядковый номер. Однако допускается любое выражение, использующее столбцы таблицы. Синтаксис списка идентичен синтаксису списка вывода . Будут возвращены только те строки, которые были успешно вставлены или обновлены. Например, если строка была заблокирована, но не обновлена из-за предложения ON CONFLICT DO UPDATE … WHERE , строка не будет возвращена.
У вас должна быть привилегия для таблицы, чтобы выполнять вставку в нее. Если присутствует , также требуется привилегия для таблицы.
Если указан список столбцов, вам нужна только привилегия для перечисленных столбцов. Точно так же, когда указано , вам нужна только привилегия для столбца (столбцов), которые перечислены для обновления. Однако также требует привилегии для любого столбца, значения которого читаются в выражениях или .
Примеры использования предикатов
Предикаты представляют собой выражения, принимающие истинностное значение. Они могут представлять собой как одно выражение, так и любую комбинацию из неограниченного количества выражений, построенную с помощью булевых операторов AND, OR или NOT. Кроме того, в этих комбинациях может использоваться SQL-оператор IS, а также круглые скобки для конкретизации порядка выполнения операций
Предикат в языке SQL может принимать одно из трех значений TRUE (истина), FALSE (ложь) или UNKNOWN (неизвестно). Исключение составляют следующие предикаты: NULL (отсутствие значения), EXISTS (существование), UNIQUE (уникальность) и MATCH (совпадение), которые не могут принимать значение UNKNOWN.
Правила комбинирования всех трех истинностных значений легче запомнить, обозначив TRUE как 1, FALSE как 0 и UNKNOWN как 1/2 (где то между истинным и ложным).
AND с двумя истинностными значениями дает минимум этих значений. Например, TRUE AND UNKNOWN будет равно UNKNOWN.
OR с двумя истинностными значениями дает максимум этих значений. Например, FALSE OR UNKNOWN будет равно UNKNOWN.
Отрицание истинностного значения равно 1 минус данное истинностное значение. Например, NOT UNKNOWN будет равно UNKNOWN.
Помимо этого, используются предикаты сравнения.
Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. Имеется шесть традиционных операторов сравнения: =, >, <, >=, <=, <>.
Данные типа NUMERIC (числа) сравниваются в соответствии с их алгебраическим значением.
Данные типа CHARACTER STRING (символьные строки) сравниваются в соответствии с их алфавитной последовательностью. Если a1a2…an и b1b2…bn — две последовательности символов, то первая «меньше» второй, если а1<b1, или а1=b1 и а2<b2 и т.д. Считается также, что а1а2…аn<b1b2…bm, если n<m и а1а2…аn=b1b2…bn, т.е. если первая строка является префиксом второй. Например, ‘folder'<‘for’, т.к. первые две буквы этих строк совпадают, а третья буква строки ‘folder’ предшествует третьей букве строки ‘for’. Также справедливо неравенство ‘bar’ < ‘barber’, поскольку первая строка является префиксом второй.
Данные типа DATETIME (дата/время) сравниваются в хронологическом порядке.
Данные типа INTERVAL (временной интервал) преобразуются в соответствующие типы, а затем сравниваются как обычные числовые значения типа NUMERIC.
Пример. Получить информацию о компьютерах, имеющих частоту процессора не менее 500 Мгц и цену ниже $800:
SELECT * FROM PC
WHERE speed >= 500 AND price < 800;
Запрос возвращает следующие данные (Таблица 6.):
Таблица 6. – Пример информационного запроса.
Существуют так же и другие предикаты, например, BETWEEN, IN, LIKE.
Имена столбцов, указанных в предложении SELECT, можно переименовать. Это делает результаты более читабельными, поскольку имена полей в таблицах часто сокращают с целью упрощения набора. Ключевое слово AS, используемое для переименования, согласно стандарту можно и опустить, т.к. оно неявно подразумевается.
Например, запрос:
SELECT ram AS Mb, hd Gb
FROM PC
WHERE cd = ’24x’;
переименует столбец ram в Mb (мегабайты), а столбец hd — в Gb (гигабайты). Этот запрос возвратит объемы оперативной памяти и жесткого диска для тех компьютеров, которые имеют 24-скоростной CD-ROM (Таблица 7.):
Таблица 7. – Пример запроса SELECT AS.
Получение итоговых значений:
Существует возможность получения итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции (Таблица 8.):
Таблица 8 .– Описание (агрегатных) функций.
Все эти функции возвращают единственное значение. При этом, функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT() состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
SELECT MIN(price) AS Min_price, MAX(price) AS Max_price
FROM PC;
Результатом будет единственная строка, содержащая агрегатные значения (таблица 9.):
Таблица 9. – Строка содержащая (агрегатные) значения.
Для просмотра данных наиболее удобно использовать совместно значения оператора COUNT — счетчик (позволяет узнать количество записей в запросе), и оператора CURSOR — позволяет принимать не все записи сразу, а по одной (указанной пользователем).
Инструкция INSERT в T-SQL
INSERT – это инструкция языка T-SQL, которая предназначена для добавления данных в таблицу, т.е. создания новых записей. Данную инструкцию можно использовать как для добавления одной строки в таблицу, так и для массовой вставки данных. Для выполнения инструкции INSERT требуется разрешение на вставку данных (INSERT) в целевую таблицу.
Существует несколько способов использования инструкции INSERT в части данных, которые необходимо вставить:
- Перечисление конкретных значений для вставки;
- Указание набора данных в виде запроса SELECT;
- Указание набора данных в виде вызова процедуры, которая возвращает табличные данные.
Упрощённый синтаксис
- INSERT INTO – это команда добавления данных в таблицу;
- Таблица – это имя целевой таблицы, в которую необходимо вставить новые записи;
- Список столбцов – это перечень имен столбцов таблицы, в которую будут вставлены данные, разделенные запятыми;
- VALUES – это конструктор табличных значений, с помощью которого мы указываем значения, которые будем вставлять в таблицу;
- Список значений – это значения, которые будут вставлены, разделенные запятыми. Они перечисляются в том порядке, в котором указаны столбцы в списке столбцов;
- SELECT – это запрос на выборку данных для вставки в таблицу. Результирующий набор данных, который вернет запрос, должен соответствовать списку столбцов;
- EXECUTE – это вызов процедуры на получение данных для вставки в таблицу. Результирующий набор данных, который вернет хранимая процедура, должен соответствовать списку столбцов.
Вот примерно так и выглядит упрощённый синтаксис инструкции INSERT INTO, в большинстве случаев именно так Вы и будете добавлять новые записи в таблицы.
Список столбцов, в которые Вы будете вставлять данные, можно и не писать, в таком случае их порядок будет определен на основе фактического порядка столбцов в таблице. При этом необходимо помнить этот порядок, когда Вы будете указывать значения для вставки или писать запрос на выборку. Лично я Вам рекомендую все-таки указывать список столбцов, в которые Вы планируете добавлять данные.
Также следует помнить и то, что в списке столбцов и в списке значений, соответственно, должны присутствовать так называемые обязательные столбцы, это те, которые не могут содержать значение NULL. Если их не указать, и при этом у столбца отсутствует значение по умолчанию, будет ошибка.
Еще хотелось бы отметить, что тип данных значений, которые Вы будете вставлять, должен соответствовать типу данных столбца, в который будет вставлено это значение, ну или, хотя бы, поддерживал неявное преобразование. Но я Вам советую контролировать тип данных (формат) значений, как в списке значений, так и в запросе SELECT.
Хватит теории, переходим к практике.
Вставка значений с использованием оператора SET в MySQL
В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с
использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:
INSERT INTO ИМЯ_ТАБЛИЦЫ
SET
ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ,
ИМЯ_СТОЛБЦА_2=ЗНАЧЕНИЕ,
…,
ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ
В подобных запросах можно указывать имена не всех столбцов, при этом не указанные столбцы
принимают значения по умолчанию.
Пример 6. База данных и таблица —
те же, что и в предыдущих примерах.
Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:
INSERT INTO ADS
SET
Id=13,
Category=’Недвижимость’,
Part=’Гаражи’
В результате выполнения запроса в таблице появится новая строка:
| 13 | Недвижимость | Гаражи | NULL | NULL |
Автоматическая вставка данных
Создавая таблицу, вы можете применить к столбцам определенные атрибуты, которые заставят СУБД автоматически заполнить их данными.
Чтобы проиллюстрировать это, запустите следующую команду. Она создаст таблицу interns, которая включает в себя три столбца. Первый столбец, internID, содержит данные типа int
Однако обратите внимание, что он также включает атрибут AUTO_INCREMENT. С его помощью SQL будет автоматически генерировать уникальное числовое значение для каждой новой строки, начиная с 1 (по умолчанию) и увеличивая его на 1 с каждой последующей записью
Второй столбец, department, включает ключевое слово DEFAULT. С его помощью СУРБД автоматически вставит значение по умолчанию — в этом примере мы используем ‘production’ — если вы не укажете department в списке столбцов оператора INSERT INTO:
CREATE TABLE interns ( internID int AUTO_INCREMENT PRIMARY KEY, department varchar(20) DEFAULT 'production', name varchar(30) );
Примечание: Атрибут AUTO_INCREMENT — это специфичная функция MySQL, многие другие СУБД имеют собственный метод увеличения целых чисел. Чтобы лучше понять, как ваша СУБД управляет автоматическим счетом, рекомендуем обратиться к ее официальной документации.
Здесь мы собрали для вас список документаций нескольких популярных БД с открытым исходным кодом:
- Документация MySQL по атрибуту AUTO_INCREMENT
- Документация PostgreSQL по последовательным типам данных
- Документация SQLite по Autoincrement
Чтобы продемонстрировать работу этих функций, мы заполним таблицу interns некоторыми данными, выполнив следующую команду INSERT INTO. Эта операция задает значения только для столбца name:
INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');
Вы получите такой вывод:
Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
Затем запустите этот запрос, чтобы вернуть каждую запись из таблицы:
SELECT * FROM interns;
Вы получите такой результат:
+----------+------------+----------+ | internID | department | name | +----------+------------+----------+ | 1 | production | Pierre | | 2 | production | Sheila | | 3 | production | Francois | +----------+------------+----------+ 3 rows in set (0.00 sec)
Результат показывает, что из-за определений столбцов предыдущая команда INSERT INTO добавила значения как в internID, так и в department, хотя мы не указывали их.
Чтобы добавить в столбец department нестандартное значение, вам нужно будет указать его в операторе INSERT INTO, например, так:
INSERT INTO interns (name, department)
VALUES
('Jacques', 'management'),
('Max', 'quality assurance'),
('Edith', 'management'),
('Daniel', DEFAULT);
Результат выглядит так:
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
Обратите внимание, последняя строка значений в этом примере вместо строкового значения включает ключевое слово DEFAULT. Благодаря этому БД вставит значение по умолчанию (‘production’):
SELECT * FROM interns;
Результат выглядит так:
+----------+-------------------+----------+ | internID | department | name | +----------+-------------------+----------+ | 1 | production | Pierre | | 2 | production | Sheila | | 3 | production | Francois | | 4 | management | Jacques | | 5 | quality assurance | Max | | 6 | management | Edith | | 7 | production | Daniel | +----------+-------------------+----------+ 7 rows in set (0.00 sec)
Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
Очень часто возникает необходимость добавлять много данных в таблицу, например, на основе запроса на выборку, т.е. SELECT. Для этого вместо VALUES нам всего лишь нужно указать запрос.
В данном примере мы написали запрос SELECT, который возвращает данные из таблицы TestTable, но не все, а только те, у которых идентификатор больше 2. А результат вставили все в ту же таблицу TestTable.
В качестве примера того, как можно добавлять записи в таблицу без указания списка столбцов, давайте напишем еще один запрос на вставку данных, который сделает равно то же самое что и запрос выше, только в нем не будет перечисления столбцов для вставки.
В данном случае мы уверены в том, что в таблице TestTable первый столбец это ProductName, а второй Price, поэтому мы можем позволить себе написать именно так. Но, снова повторюсь, на практике лучше указывать список столбцов.
Если Вы заметили, я во всех примерах не указывал столбец Id, а он у нас есть, ошибки не возникло, так как данный столбец со свойством IDENTITY, он автоматически генерирует идентификаторы, поэтому в такой столбец вставить данные просто не получится.
Копирование данных с помощью оператора SELECT
Чтобы не указывать данные построчно, вы можете скопировать несколько строк данных из одной таблицы и вставить их в другую с помощью запроса SELECT.
Синтаксис такой операции выглядит следующим образом:
INSERT INTO table_A (col_A1, col_A2, col_A3) SELECT col_B1, col_B2, col_B3 FROM table_B;
Вместо ключевого слова VALUES, которое обычно идет за списком столбцов, в этом примере за ним следует оператор SELECT. Оператор SELECT в этом примере включает только оператор FROM, но он может обработать любой допустимый запрос.
Выполните следующую операцию CREATE TABLE, чтобы создать новую таблицу showroomEmployees
Обратите внимание, что столбцы этой таблицы имеют те же имена и типы данных, что и в таблице factoryEmployees, которую мы создали в предыдущем разделе:
CREATE TABLE showroomEmployees ( name varchar(30), hourlyWage decimal(4,2), startDate date );
Вывод:
Query OK, 0 rows affected (0.02 sec)
Теперь вы можете загрузить в эту новую таблицу некоторые данные из таблицы factoryEmployees, которую создали ранее, включив запрос SELECT в INSERT INTO.
Если запрос SELECT возвращает то же количество столбцов в том же порядке, что и столбцы целевой таблицы, и они имеют совместимые (или совпадающие) типы данных, вы можете опустить список столбцов в операторе INSERT INTO:
INSERT INTO showroomEmployees SELECT factoryEmployees.name, factoryEmployees.hourlyWage, factoryEmployees.startDate FROM factoryEmployees WHERE name = 'Agnes';
Вывод выглядит так:
Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
Примечание: Каждому из столбцов, перечисленных в запросе SELECT этой операции, предшествует имя таблицы factoryEmployees и точка. Когда вы указываете имя таблицы при ссылке на такой столбец, это называется fully qualified column reference. В данном конкретном случае это делать не обязательно. На самом деле следующий оператор INSERT INTO даст тот же результат, что и предыдущий:
INSERT INTO showroomEmployees SELECT name, hourlyWage, startDate FROM factoryEmployees WHERE name = 'Agnes';
В примерах данного раздела для ясности используются полные (fully qualified) ссылки на столбцы, что на практике может стать хорошей и полезной привычкой. Подобные ссылки не только помогут упростить понимание и устранение неполадок в SQL, но и пригодятся в некоторых операциях, которые ссылаются на более чем одну таблицу (это, например, запросы, включающие предложения JOIN).
Оператор SELECT в этой операции включает опцию WHERE, благодаря которой запрос возвращает только строки из таблицы factoryEmployees, чей столбец name содержит значение Agnes. Поскольку в исходной таблице есть только одна такая строка, только именно она будет скопирована в таблицу showroomEmployees.
Выполните следующий проверочный запрос, чтобы извлечь все записи в таблице showroomEmployees:
SELECT * FROM showroomEmployees;
Вы получите такой результат:
+-------+------------+------------+ | name | hourlyWage | startDate | +-------+------------+------------+ | Agnes | 26.50 | 2017-05-01 | +-------+------------+------------+ 1 row in set (0.00 sec)
Вы можете вставить несколько строк данных через любой запрос, который вернет более одной строки из исходной таблицы. Например, следующий запрос вернет каждую запись из БД factoryEmployees, в которой значение в столбце name не начинается с буквы J:
INSERT INTO showroomEmployees SELECT factoryEmployees.name, factoryEmployees.hourlyWage, factoryEmployees.startDate FROM factoryEmployees WHERE name NOT LIKE 'J%';
Вы получите:
Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
Запустите этот запрос еще раз, чтобы получить все записи в таблице showroomEmployees:
SELECT * FROM showroomEmployees;
Результат выглядит так:
+--------+------------+------------+ | name | hourlyWage | startDate | +--------+------------+------------+ | Agnes | 26.50 | 2017-05-01 | | Agnes | 26.50 | 2017-05-01 | | Harry | 26.50 | NULL | | Marie | 27.88 | 2018-03-29 | | Giles | 26.50 | 2019-08-06 | | Daphne | 32.45 | 2017-11-12 | +--------+------------+------------+ 6 rows in set (0.00 sec)
Обратите внимание на две одинаковые строки со значением Agnes. Каждый раз, когда вы запускаете INSERT INTO вместе с SELECT, SQL обрабатывает результаты запроса как новый набор данных
Если вы не наложите определенные ограничения на свою таблицу или не разработаете более детализированные запросы, при добавлении данных в таблицу они частично будут повторяться.
Insert data into a table with columns that have a default value
This statement shows inserting rows into a table with columns that automatically generate a value or have a default value. Column_1 is a computed column that automatically generates value by concatenating a string with the value inserted into column_2. Column_2 is defined with a default constraint.
If a value is not specified for this column, the default value is used.
Column_3 is defined with the row version data type, which automatically generates a unique, incrementing binary number.
Column_4 does not automatically generate a value. When a value for this column is not specified, NULL is inserted. The INSERT statements insert rows that contain values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause.
Syntax
Пример использования ключевого слова DEFAULT VALUES
В SQL Server вы также можете вставить запись в таблицу с использованием синтаксиса DEFAULT VALUES.
Например:
Transact-SQL
INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id <= 100;
|
1 |
INSERTINTOcontacts (contact_id,last_name,first_name) SELECTemployee_id,last_name,first_name FROMemployees WHEREemployee_id<=100; |
Поместив инструкцию SELECT в оператор INSERT, вы можете быстро выполнить многострочную вставку.
С этим типом вставки вы можете проверить количество вставленных строк. Вы можете определить количество строк, которые будут вставлены, запустив следующий SQL оператор SELECT перед выполнением вставки.
Transact-SQL
SELECT count(*)
FROM employees
WHERE employee_id <= 100;
|
1 |
SELECTcount(*) FROMemployees WHEREemployee_id<=100; |
Предоставлять, отзывать и запрещать разрешения для базы данных
Язык управления данными (DCL) является подмножеством языка структурированных запросов (SQL) и позволяет администраторам баз данных настраивать безопасный доступ к реляционным базам данных. Он дополняет язык определения данных (DDL), который используется для добавления и удаления объектов базы данных, и язык манипулирования данными (DML), используемый для извлечения, вставки и изменения содержимого базы данных.
DCL является самым простым из подмножеств SQL, поскольку он состоит только из трех команд: GRANT, REVOKE и DENY. В совокупности эти три команды предоставляют администраторам возможность гибко устанавливать и удалять разрешения для базы данных.
Добавление разрешений с помощью команды GRANT
Команда GRANT используется администраторами для добавления новых разрешений пользователю базы данных. У него очень простой синтаксис, определенный следующим образом:
GRANT ON TO
Вот краткое описание каждого из параметров, которые вы можете указать с помощью этой команды:
Привилегия – может быть ключевым словом ALL (для предоставления широкого спектра разрешений) или определенным разрешением базы данных или набором разрешений. Примеры включают CREATE DATABASE, SELECT, INSERT, UPDATE, DELETE, EXECUTE и CREATE VIEW.
Объект – может быть любым объектом базы данных. Допустимые параметры привилегий зависят от типа объекта базы данных, который вы включаете в это предложение. Как правило, объект будет либо базой данных, функцией, хранимой процедурой, таблицей или представлением.
Пользователь – может быть любым пользователем базы данных. Вы также можете заменить роль для пользователя в этом пункте, если хотите использовать безопасность баз данных на основе ролей.
Если вы добавите необязательное условие WITH GRANT OPTION в конце команды GRANT, вы не только предоставите указанному пользователю разрешения, определенные в операторе SQL, но и дадите пользователю возможность предоставить те же разрешения. другим пользователям базы данных
По этой причине используйте этот пункт с осторожностью.
Например, предположим, что вы хотите предоставить пользователю Джо возможность извлекать информацию из таблицы сотрудников в базе данных под названием HR. Вы можете использовать следующую команду SQL:
ВЫБРАТЬ ГРАНТ НА HR.employees TO Джо
Теперь у Джо будет возможность извлекать информацию из таблицы сотрудников. Однако он не сможет предоставить другим пользователям разрешение на извлечение информации из этой таблицы, поскольку вы не включили условие WITH GRANT OPTION в оператор GRANT.
Отмена доступа к базе данных
Команда REVOKE используется для удаления доступа к базе данных у пользователя, ранее предоставившего такой доступ. Синтаксис этой команды определяется следующим образом:
REVOKE ON FROM
Вот краткое описание параметров команды REVOKE:
- Разрешение – указывает разрешения для базы данных, которые необходимо удалить для указанного пользователя. Команда отменяет оба утверждения GRANT и DENY, ранее сделанные для указанного разрешения.
- Объект – может быть любым объектом базы данных. Допустимые параметры привилегий зависят от типа объекта базы данных, который вы включаете в это предложение. Как правило, объект будет либо базой данных, функцией, хранимой процедурой, таблицей или представлением.
- Пользователь – может быть любым пользователем базы данных. Вы также можете заменить роль для пользователя в этом пункте, если хотите использовать безопасность баз данных на основе ролей.
- Предложение GRANT OPTION FOR устраняет возможность указанного пользователя предоставлять указанное разрешение другим пользователям. Примечание . Если вы включите условие GRANT OPTION FOR в оператор REVOKE, основное разрешение будет не отменено. Этот пункт отменяет только возможность предоставления.
- Параметр CASCADE также отменяет указанное разрешение у всех пользователей, которым указанный пользователь предоставил разрешение.
Например, следующая команда отзывает разрешение, предоставленное Джо в предыдущем примере:
ОТМЕНИТЬ ВЫБРАТЬ НА HR.employees ОТ Джо
Явный отказ в доступе к базе данных
Команда DENY используется для явного запрета пользователю получать определенное разрешение. Это полезно, когда пользователь является участником роли или группы, которой предоставлено разрешение, и вы хотите запретить этому отдельному пользователю наследовать разрешение путем создания исключения. Синтаксис этой команды следующий:
DENY ON TO
Параметры для команды DENY идентичны параметрам, используемым для команды GRANT.Например, если вы хотите, чтобы Мэтью никогда не получал возможность удалять информацию из таблицы сотрудников, введите следующую команду:
УДАЛЕНИЕ ДЕНИ НА HR.employees TO Matthew
Использование запросов в PHP
Подключаемся к базе данных:
mysql_connect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
mysql_select_db (‘db_name’);
mysql_query(«SET NAMES ‘utf8′»);
* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.
Также можно создать постоянное подключение:
mysql_pconnect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
* однако есть вероятность достигнуть максимально разрешенного лимита хостинга. Данным способом стоит пользоваться на собственных серверах, где мы сами можем контролировать ситуацию.
Завершить подключение:
mysql_close();
* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).
Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():
$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) {
echo $mass . ‘<br>’;
}
* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.
Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.
