Отчёт по курсовой работе на тему «Лабораторный практикум по изучению языка структурированных запросов sql при использовании субд mysql по курсу «Базы данных»

Вид материала

Практикум

Содержание Удаление данных из таблицы Управление безопасностью базы данных Привилегии пользователей Системные привилегии Объектные привилегии Управление доступом к базе данных Grant select. insert Глава 2. Выборка данных Использование SQL для выборки данных из таблицы Язык запросов DQL Простейшая форма оператора SELECT Задание условий при выборке данных Операторы сравнения Логические операторы Оператор IS NULL Оператор BETWEEN...AND Оператор LIKE Оператор EXISTS Оператор UNIQUE Оператор ALL ... Полное содержание

Подобный материал:

• Методическое пособие по курсу «Базы данных и информационные системы» 2011, 489.34kb.
• Лекция №1: Стандарты языка sql, 1420.56kb.
• Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.
• Сервер баз данных, 379.17kb.
• Методические рекомендации по использованию sql-ориентированных заданий, выполняемых, 151.55kb.
•  - это аббревиатура выражения Structured Query Language (язык структурированных запросов)., 3691.83kb.
• Программа дисциплины «Базы данных», 380.05kb.
• Программа дисциплины «Базы данных», 395.38kb.
• Курсовая работа, 52.16kb.
• Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Создание таблиц и ограничений, 146.46kb.

Удаление данных из таблицы

Удаление данных из таблицы выполняется с помощью оператора DELETE. Данный оператор полностью удаляет всю запись, а не данные из отдельных полей. Синтак­сис оператора DELETE имеет следующий вид:

DELETE FROM имя_таблицы [WHERE условие]

Удаляемые записи определяются в соответствии с условием, заданным с помощью необязательного предложения WHERE. При отсутствии предложения WHERE в опе­раторе DELETE данные будут удалены из всей таблицы.

Управление безопасностью базы данных

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

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

Рассмотрим один из аспектов обеспечения безопасности — управле­ние доступом к базе данных.

Привилегии пользователей

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

Различают привилегии двух типов:

• системные привилегии;
  
• объектные привилегии.
  

Рассмотрим каждый из типов более подробно.

Системные привилегии

Системные привилегии дают пользователям базы данных возможность выполнять действия, связанные с ее администрированием: создавать, удалять и изменять структуру как самой базы данных, так и отдельных ее объектов. Кроме того, сис­темные привилегии дают право на изменение состояния базы данных и ее отдель­ных объектов.

Возможные системные привилегии существенно зависят от используемой СУБД. Но в любом случае они включают такие привилегии, как право на:

• создание таблицы;
  
• создание представления;
  
• создание хранимой процедуры;
  
• удаление таблицы;
  
• удаление представления;
  
• удаление хранимой процедуры.
  

Этот список может быть расширен. Кроме того, каждая из привилегий имеет свои особенности в различных СУБД.

Объектные привилегии

Объектные привилегии представляют собой уровни полномочий пользователей, распространяемые на объекты базы данных. Это означает, что для того, чтобы вы­полнять определенные действия над объектами базы данных, пользователь дол­жен иметь соответствующие права.

Стандартом ANSI предусмотрены следующие объектные привилегии:

• SELECT — разрешает производить выборку данных из указанной таблицы (пред­ставления);
  
• INSERTимя_поля) — разрешает выполнять добавление данных в определенное поле указанной таблицы (представления);
  
• INSERT — разрешает добавление данных во все поля указанной таблицы (пред­ставления);
  
• UPDАТЕ(имя_поля) — разрешает модифицировать данные в заданном поле ука­занной таблицы (представления);
  
• UPDATE — разрешает модифицировать данные во всех полях указанной табли­цы (представления);
  
• REFERENCE (имя_поля) — разрешает ссылаться на заданное поле указанной таб­лицы (эта привилегия требуется при установке любых ограничений целостно­сти);
  
• REFERENCE — позволяет ссылаться на все поля указанной таблицы.
  

Кроме указанных существует целый ряд объектных привилегий, доступных в различ­ных СУБД.

Управление доступом к базе данных

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

Как правило, эти операторы используются администратором базы данных или его помощником по безопасности.

Оператор GRANT

Оператор GRANT используется для предоставления пользователю как системных, так и объектных привилегий. Синтаксис данного оператора имеет следующий

вид:

GRANT привилегия_1 [. привилегия_2]

ON имя_объекта

ТО имя_пользователя [WITH GRANT OPTION]

Предоставление пользователю с именем USER права на выбор данных из таблицы СОТРУДНИКИ выполняется с помощью следующего оператора:

GRANT SELECT ON Сотрудники ТО USER

С помощью одного оператора GRANT можно задавать сразу несколько привилегий. Например, следующий оператор предоставит пользователю USER право как про­сматривать, так и добавлять данные в таблицу СОТРУДНИКИ:

GRANT SELECT. INSERT ON Сотрудники TO USER

При вызове оператора GRANT может использоваться необязательное предложение WITH GRANT OPTION. Данное предложение означает, что пользователь, для которо­го предоставляются привилегии, также получает право предоставлять привиле­гии на данный объект. Например, если вызвать рассмотренный выше оператор с предложением WITH GRANT OPTION, то пользователь с¹ именем USER, кроме права просматривать и добавлять данные в таблицу СОТРУДНИКИ, получит также право предоставлять эти привилегии другим пользователям:

GRANT SELECT. INSERT

ON Сотрудники

TO USER

WITH GRANT OPTION

Оператор REVOKE

Оператор REVOKE используется для отмены предоставленных пользователю приви­легий. Данный оператор может вызываться с одним из двух параметров — RESTRICT или CASCADE. При использовании варианта RESTRICT оператор REVOKE будет успешно выполнен только в том случае, если его выполнение не приведет к появлению так называемых оставленных привилегий.

Оставленными называются привилегии, оставшиеся у пользователя, которому они были предоставлены с помощью предложения WITH GRANT OPTION оператора GRANT.

При использовании режима CASCADE удаляются все привилегии, которые могли бы остаться у других пользователей. Это означает, что если пользователю USER1 были предоставлены привилегии с помощью параметра WITH GRANT OPTION, а он, в свою очередь, предоставил эти привилегии пользователю USER2, то отмена приви­легий пользователю USER1 в режиме CASCADE приведет к отмене привилегий и для пользователя USER2.

Синтаксис оператора REVOKE имеет следующий вид:

REVOKE привилегия_1 [. привилегия_2]

ON имя_объекта

FROM имя_пользователя [RESTRICT | CASCADE]

Например, для отмены права добавления данных в таблицу СОТРУДНИКИ для пользователя USER следует использовать следующий оператор:

REVOKE INSERT ON Сотрудники FROM USER

Глава 2. Выборка данных

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

Использование SQL для выборки данных из таблицы

Одним из наиболее эффективных и универсальных способов выборки данных из таблиц базы данных является использование запросов языка SQL. Команды SQL подразделяются на несколько категорий. Для выборки данных используются команды, относящиеся к так называемому языку запросов DQL (Data Query Language).

SQL-запросы можно использовать как при работе с локальными базами данных, так и с SQL-серверами баз данных (Oracle, Informix, Sybase, InterBase, Microsoft SQL Server). Причем при формировании SQL-запросов не имеет особого значения, какая система управления базами данных используется, так как команды языка SQL стандартизованы (стандарт ANSI SQL 92). Однако следует учитывать, что производители СУБД обычно предлагают свои реализации SQL, которые могут включать расширения команд стандарта и даже отклонения от него. Тем не менее большинство команд SQL имеют одинаковый или очень похожий синтаксис в различных реализациях. Поэтому, изучив одну из реализаций SQL, впоследствии можно легко перейти на другую.

При работе с SQL-серверами обработка запроса выполняется на стороне сервера. Поэтому особенности реализации языка SQL в этом случае определяются используемым SQL-сервером.

Язык запросов DQL

Язык запросов, являющийся одной из категорий языка SQL, состоит всего из одной команды SELECT Эта команда вместе с множеством опций и предложений используется для формирования запросов к базе данных

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

Оператор SELECT не используется автономно, вместе с ним обязательно должны задаваться уточняющие предложения. Предложения, используемые совместно с командой SELECT, могут быть обязательными и дополнительными. Обязательным является только одно предложение — FROM, без которого оператор SELECT не может использоваться.

Простейшая форма оператора SELECT

Оператор SELECT вместе с предложением FROM используется для получения информации из базы данных. Синтаксис простейшей формы оператора SELECT приведен ниже:

SELECT {* | ALL | DISTINCT fieldl, field2. . . fieldN} FROM tablel {. tab1e2. ... . tableN}

Здесь за ключевым словом SELECT следует список полей, которые возвращаются в результате выполнения запроса:

• имена полей в списке разделяются через запятую;
  
• для выборки всех полей таблицы (таблиц) используется символ подстановки «*»;
  
• опция ALL (задана по умолчанию) означает, что результат выборки будет содержать все записи, включая дублирующие друг друга;
  
• при использовании опции DISTINCT результат запроса не будет содержать дублирующихся строк.
  

Совместно с командой SELECT всегда используется предложение FROM, с помощью которого указывается имя таблицы (таблиц), из которой производится выборка. Если в предложении FROM указывается несколько таблиц, то их имена разделяются запятыми.

Чтобы выбрать не все поля, а лишь некоторые, необходимо после слова SELECT указать имена полей, которые будут включены в результат выборки. В качестве примера ниже приведен запрос, возвращающий значения только трех полей: «Код товара», «Наименование» и «Цена»:

SELECT [Код товара]. Наименование. Цена FROM Товары

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

В результате выполнения данного запроса возвращаются все записи, содержащиеся в трех полях таблицы «Товары»

Рассмотрим пример использования опции DISTINCT. Для этого выберем только одно поле — «Наименование», в котором содержатся дублирующие строки:

SELECT DISTINCT Наименование FROM Товары

В результате выполнения этого запроса выбрано только 15 записей из 16, так как в выборку включено только одно значение «Microsoft Office 2000»

Задание условий при выборке данных

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

SELECT {* | ALL | DISTINCT fieldl. field2. . . fieldN} FROM tablel {. table2. ... . tableN} WHERE условие

Специальные операторы языка SQL, применяемые для задания условия, можно разделить на следующие группы:

операторы сравнения; логические операторы; операторы объединения; операторы отрицания.

Результатом выполнения каждого из этих операторов является логическое значение (true или false). Если для некоторой записи оператор возвращает значение true, то запись включается в результат выборки, если fal se — не включается.

Операторы сравнения

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

• оператор равенства «=» используется для отбора записей, в которых значение определенного поля точно соответствует заданному;
  
• оператор неравенства «<>» возвращает значение true, если значение поля не совпадает с заданным значением;
  
• операторы «меньше» и «больше» (соответственно, «<» и «>») позволяют отбирать записи, в которых значение определенного поля меньше или больше некоторой заданной величины;
  
• операторы «меньше или равно» и «больше или равно» (соответственно, «<=» и «>=») представляют собой объединение операторов «меньше» и «равно», «больше» и «равно». В отличие от операторов «<» и «>» операторы «<=» и «>=» возвращают значение true, если значение поля совпадает с заданным значением.
  

В качестве примера рассмотрим запрос, выбирающий из таблицы «Товары» только те записи, категория товаров в которых равна 2:

SELECT * FROM Товары WHERE Категория-2

Логические операторы

К логическим относятся операторы, в которых для задания ограничений на отбор данных используются специальные ключевые слова. В SQL определены следующие логические операторы: Is null, BETWEEN...AND, IN, LIKE, EXISTS, UNIQUE, ALL, ANY.

Оператор IS NULL

Оператор IS NULL предназначен для сравнения текущего значения поля со значением NULL. Он используется для отбора записей, в некоторое поле которых не занесено никакое значение.

Для иллюстрации использования этого оператора воспользуемся таблицей «Клиенты». С помощью приведенного ниже запроса произведем выборку из нее записей клиентов, у которых не указано название предприятия, которое они представляют:

SELECT Фамилия. Имя. Отчество. Телефон. Город, Адрес

FROM Клиенты

WHERE Предприятие IS NULL


Оператор BETWEEN...AND

Оператор BETWEEN..AND применяется для отбора записей, в которых значения поля находятся внутри заданного диапазона. Границы диапазона включаются в условие отбора.

Чтобы продемонстрировать работу этого оператора, вернемся к таблице «Товары» и выберем в ней товары, цена которых находится в диапазоне от 200 до 2000. Для этого сформируем следующий запрос:

SELECT *

FROM Товары

WHERE Цена BETWEEN 200 AND 2000

данных

Оператор IN

Оператор IN используется для выборки записей, в которых значение некоторого поля соответствует хотя бы одному из значений заданного списка.

Выберем из таблицы «Клиенты» список клиентов, которые живут в Беларуси, Украине или Казахстане:

SELECT Фамилия. Иня. Отчество. Страна

FROM Клиенты

WHERE Страна IN ('Беларусь','Украина'.'Казахстан')

Оператор LIKE

Оператор LIKE применяется для сравнения значения поля со значением, заданным при помощи шаблонов. Для задания шаблонов используются два символа:

Q знак процента «£» — заменяет последовательность символов любой (в том числе и нулевой) длины;

Q символ подчеркивания «_» — заменяет любой единичный символ.

Найдем в таблице «Клиенты» записи, в которых фамилия клиента начинается с буквы «М»:

SELECT Фамилия, Имя, Отчество. Телефон

FROM Клиенты

WHERE Фамилия LIKE 'МГ

В результате выполнения этого запроса выбрано 4 записи (рис. 11.11).

А теперь найдем в этой же таблице записи, для которых номер телефона начинается на цифры (816)025-61, а две последние цифры неизвестны:

SELECT Фамилия. Имя. Отчество. Телефон

FROM Клиенты

WHERE Телефон LIKE Ч816)025-61_'

При выполнении данного запроса отобраны две записи (рис. 11.12).

Оператор EXISTS

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

Для иллюстрации его работы рассмотрим следующий пример. Из таблицы «Товары» требуется отобрать список товаров, количество продаж которых превышает 10. Сведения о продажах содержатся в таблице «Продажи» в поле «Продано». Для получения необходимой выборки воспользуемся оператором

EXISTS:

SELECT Наименование Цена

FROM Товары

WHERE EXISTS (SELECT [Код товара]

FROM Продажи

WHERE (Продажи Продано>10) AND

Товары [Код товара]=Продажи [Код товара])

В этом запросе после ключевого слова EXISTS следует оператор SELECT, отбирающий из таблицы «Продажи» записи, для которых количество продаж превышает 10.

Оператор EXISTS отбирает из таблицы «Товары» записи, в которых значение поля «Код товара» соответствует отобранным из таблицы «Продажи».

Результат выполнения данного запроса приведен на рис.

При использовании оператора EXISTS (атакже еще трех логических операторов: UNIQUE, ALL и ANY) применяется подзапрос — оператор SELECT, следующий за ключевым словом EXISTS и заключенный в круглые скобки. Более подробно подзапросы будут рассмотрены ниже.

Оператор UNIQUE

Оператор UNIQUE используется для проверки записи таблицы на уникальность. По своему действию он аналогичен оператору EX ISTS. Единственное отличие заключается в том, что подзапрос, задаваемый после ключевого слова UNIQUE, не должен возвращать более одной записи.

Оператор ALL

Оператор ALL используется для сравнения исходного значения со всеми другими значениями, входящими в некоторый набор данных.

Например, для того чтобы выбрать из таблицы «Товары» те товары, которые имеют цену большую, чем цена всех товаров, проданных в количестве более 10, используется следующий запрос:

SELECT *

FROM Товары

WHERE LleHa>ALL (SELECT Продажи.Цена

FROM Продажи

WHERE Продажи.Продано>10)

Оператор ANY

Оператор ANY применяется для сравнения заданного значения с каждым из значений некоторого набора данных. Если в предыдущем примере заменить оператор ALL на ANY, то будет возвращен список товаров, цена которых больше, чем хотя бы у одного из товаров, проданных в количестве больше 10. Результат выполнения такого запроса показан на рис. 11.15.

Операторы объединения

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

Оператор AND используется в тех случаях, когда необходимо отобрать записи, соответствующие нескольким условиям. Причем для каждой записи, включаемой в результат выборки, должны выполняться все заданные ограничения. Оператор AND объединяет несколько условий путем выполнения операции логического умножения результатов всех заданных ограничений. Результат true, соответственно, будет получен только в том случае, если все объединяемые условия принимают значение true.


328

Оператор OR выполняет операцию логического сложения результатов всех заданных условий. При использовании данного оператора запись включается в результирующую выборку в случае выполнения хотя бы одного из заданных ограничений.

При использовании операторов объединения каждое логическое выражение следует заключать в круглые скобки. Для примера произведем выборку данных о товарах, цена которых больше 50, но меньше 1000:

SELECT *

FROM Товары

WHERE (Цена>50) AND (Цена<1000)

Синтаксические правила использования оператора OR такие же, как и для оператора AND. Следующий запрос:

SELECT *

FROM Товары

WHERE (Цена<50) OR (Цена>1000)

возвратит список товаров, цена которых меньше 50 или больше 1000 (рис.).

Оператор отрицания

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

IS NOT NULL NOT BETWEEN NOT IN NOT LIKE NOT EXISTS NOT UNIQUE

Упорядочение данных

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

SELECT {* | ALL | DISTINCT fieldl. field2. ... , fieldN}

FROM tablel {. table2. ... . tableN}

WHERE условие

ORDER BY field {ASC | DESC}

После ключевых слов ORDER BY указывается имя поля (полей), по которому производится сортировка, а затем указывается режим сортировки:

• ASC — режим, используемый по умолчанию. При этом информация располагается в порядке возрастания значения указанного поля (для текстовых полей — в алфавитном порядке).
  
• DESC — используется для вывода информации в порядке убывания значений указанного поля (для текстовых полей — в порядке, обратном алфавитному).
  

Например, чтобы отсортировать список товаров по алфавиту, следует использовать следующий запрос:

SELECT Категория, Наименование. Цена

FROM Товары

ORDER BY Наименование

Результат выполнения данного запроса приведен на рис.

Вместо имени поля в предложении ORDER BY можно использовать целое число, определяющее порядковый номер поля в списке после ключевого слова SELECT (если производится выборка всех полей таблицы с помощью символа «*», то число указывает порядковый номер поля в таблице базы данных). Например, для вывода списка товаров в порядке убывания цены можно использовать следующий запрос:

SELECT Категория, Наименование. Цена FROM Товары ORDER BY 3 DESC

Результат выполнения запроса изображен на рис. 11.19.