Телевизор 

Модифицирование пустых(null) значений. Вставка результатов запроса

  • Перевод
  • Tutorial
Надо “ SELECT * WHERE a=b FROM c ” или “ SELECT WHERE a=b FROM c ON * ” ?

Если вы похожи на меня, то согласитесь: SQL - это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.


А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:


SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>(SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.


Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.


Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.

1. Три волшебных слова

В SQL много ключевых слов, но SELECT , FROM и WHERE присутствуют практически в каждом запросе. Чуть позже вы поймете, что эти три слова представляют собой самые фундаментальные аспекты построения запросов к базе, а другие, более сложные запросы, являются всего лишь надстройками над ними.

2. Наша база

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







У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.

  • В таблице "books" хранится информация о заголовке, авторе, дате публикации и наличии книги. Все просто.
  • В таблице “members” - имена и фамилии всех записавшихся в библиотеку людей.
  • В таблице “borrowings” хранится информация о взятых из библиотеки книгах. Колонка bookid относится к идентификатору взятой книги в таблице “books”, а колонка memberid относится к соответствующему человеку из таблицы “members”. У нас также есть дата выдачи и дата, когда книгу нужно вернуть.

3. Простой запрос

Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”


Запрос будет таким:


SELECT bookid AS "id", title FROM books WHERE author="Dan Brown";

А результат таким:


id title
2 The Lost Symbol
4 Inferno

Довольно просто. Давайте разберем запрос чтобы понять, что происходит.

3.1 FROM - откуда берем данные

Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.


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

3.2 WHERE - какие данные показываем

WHERE просто-напросто ведет себя как фильтр строк , которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author - это “Dan Brown”.

3.3 SELECT - как показываем данные

Теперь, когда у нас есть все нужные нам колонки из нужной нам таблицы, нужно решить, как именно показывать эти данные. В нашем случае нужны только названия и идентификаторы книг, так что именно это мы и выберем с помощью SELECT . Заодно можно переименовать колонку используя AS .


Весь запрос можно визуализировать с помощью простой диаграммы:


4. Соединения (джойны)

Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:


SELECT books.title AS "Title", borrowings.returndate AS "Return Date" FROM borrowings JOIN books ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";

Результат:


Title Return Date
The Lost Symbol 2016-03-23 00:00:00
Inferno 2016-04-13 00:00:00
The Lost Symbol 2016-04-19 00:00:00

По большей части запрос похож на предыдущий за исключением секции FROM . Это означает, что мы запрашиваем данные из другой таблицы . Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице , которая создалась соединением этих двух таблиц.


borrowings JOIN books ON borrowings.bookid=books.bookid - это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет:



А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.


Давайте попробуем чуть более сложное соединение с двумя таблицами.


Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.


На этот раз давайте пойдем снизу вверх:


Шаг Step 1 - откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:


borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid

Результат соединения можно увидеть по ссылке .


Шаг 2 - какие данные показываем? Нас интересуют только те данные, где автор книги - “Dan Brown”


WHERE books.author="Dan Brown"

Шаг 3 - как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:


SELECT members.firstname AS "First Name", members.lastname AS "Last Name"

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:


SELECT members.firstname AS "First Name", members.lastname AS "Last Name" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown";

Что даст нам:


First Name Last Name
Mike Willis
Ellen Horton
Ellen Horton

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну . При этом, во время агрегирования для разных колонок используется разная логика.


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


SELECT members.firstname AS "First Name", members.lastname AS "Last Name", count(*) AS "Number of books borrowed" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

Что даст нам нужный результат:


First Name Last Name Number of books borrowed
Mike Willis 1
Ellen Horton 2

Почти все агрегации идут вместе с выражением GROUP BY . Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY . В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count , которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе.


Каждая строка в результате представляет собой результат агрегирования каждой группы.



Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY , или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT "ом, то непонятно, какие из возможных значений нужно брать.


В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:


SELECT author, sum(stock) FROM books GROUP BY author;

Результат:


author sum
Robin Sharma 4
Dan Brown 6
John Green 3
Amish Tripathi 2

Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.

6. Подзапросы


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

6.1 Двумерная таблица

Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов - использовать подзапросы:


SELECT * FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE author="Robin Sharma";

Результат:



Можно записать как: ["Robin Sharma", "Dan Brown"]


2. Теперь используем этот результат в новом запросе:


SELECT title, bookid FROM books WHERE author IN (SELECT author FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE sum > 3);

Результат:


title bookid
The Lost Symbol 2
Who Will Cry When You Die? 3
Inferno 4

Это то же самое, что:


SELECT title, bookid FROM books WHERE author IN ("Robin Sharma", "Dan Brown");

6.3 Отдельные значения

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


Давайте, к примеру, получим информацию о всех книгах, количество которых в библиотеке превышает среднее значение в данный момент.


Среднее количество можно получить таким образом:


select avg(stock) from books;

Что дает нам:


7. Операции записи

Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.

7.1 Update

Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT "ом, мы задаем знаения SET "ом.


Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:


UPDATE books SET stock=0 WHERE author="Dan Brown";

WHERE делает то же самое, что раньше: выбирает строки. Вместо SELECT , который использовался при чтении, мы теперь используем SET . Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках.


7.2 Delete

Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE , блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:


DELETE FROM books WHERE author="Dan Brown";

7.3 Insert

Пожалуй, единственное, что отличается от других типов запросов, это INSERT . Формат такой:


INSERT INTO x (a,b,c) VALUES (x, y, z);

Где a , b , c это названия колонок, а x , y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.


Взглянем на конкретный пример. Вот запрос с INSERT , который заполняет всю таблицу "books":


INSERT INTO books (bookid,title,author,published,stock) VALUES (1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2), (2,"The Lost Symbol","Dan Brown","07-22-2010",3), (3,"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4), (4,"Inferno","Dan Brown","05-05-2014",3), (5,"The Fault in our Stars","John Green","01-03-2015",3);

8. Проверка

Мы подошли к концу, предлагаю небольшой тест. Посмотрите на тот запрос в самом начале статьи. Можете разобраться в нем? Попробуйте разбить его на секции SELECT , FROM , WHERE , GROUP BY , и рассмотреть отдельные компоненты подзапросов.


Вот он в более удобном для чтения виде:


SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

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


Результат:


Full Name
Lida Tyler

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

Теги: Добавить метки

При выборке данных бывает важно получить их в определенном упорядоченном виде. Сортировка может быть выполнена по любым полям с любым типом данных. Это может быть сортировка по возрастанию или убыванию для числовых полей. Для символьных (текстовых) полей это может быть сортировка в алфавитном порядке, хотя по сути, она так же является сортировкой по возрастанию или убыванию. Она так же может быть выполнена в любых направлениях – от А, до Я, и наоборот от Я, до А.

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

2, 4, 1, 5, 9

должна привести к упорядоченной последовательности:

1, 2, 4, 5, 6

Аналогично, при сортировке по возрастанию строковых значений:

Иванов Иван, Петров Петр, Иванов Андрей

результат должен быть:

Иванов Андрей, Иванов Иван, Петров Петр

Здесь строка "Иванов Андрей" перешла в начало, так как сравнение строк производится посимвольно. Обе строки начинаются одинаковых символов "Иванов ". Так как символ "А" в слове "Андрей" идет раньше в алфавите, чем символ "И" в слове "Иван", то эта строка будет поставлена раньше.

Сортировка в запросе SQL

Для выполнения сортировки в строку запроса нужно добавить команду ORDER BY. После этой команды указывается поле, по которому производится сортировка.

Для примеров используем таблицу товаров goods:

num
(номер товара) 		title
(название) 		price
(цена)
1 Мандарин 50
2 Арбуз 120
3 Ананас 80
4 Банан 40

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

SELECT * FROM goods ORDER BY title

SELECT * FROM goods – указывает выбрать все поля из таблицы goods;

ORDER BY – команда сортировки;

title – столбец, по которому будет выполняться сортировка.

Результат выполнения такого запроса следующий:

num title price
3 Ананас 80
2 Арбуз 120
4 Банан 40
1 Мандарин 50

Так же можно выполнить сортировку для любого из полей таблицы.

Направление сортировки

По умолчанию, команда ORDER BY выполняет сортировку по возрастанию. Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово ASC (по возрастанию) или DESC (по убыванию). Таким образом, чтобы вывести нашу таблицу в порядке убывания цен, нужно задать запрос так:

SELECT * FROM goods ORDER BY price DESC

Сортировка по возрастанию цены будет:

SELECT * FROM goods ORDER BY price ASC

Сортировка по нескольким полям

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

column1 column2 column3
3 1 c
1 3 c
2 2 b
2 1 b
1 2 a
1 3 a
3 4 a

Отсортируем таблицу по следующим правилам:

SELECT * FROM mytable ORDER BY column1 ASC, column2 DESC, column3 ASC

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

column1 column2 column3
1 3 a
1 3 c
1 2 a
2 2 b
2 1 b
3 1 a
3 1 c

Порядок команды ORDER BY в запросе

Сортировка строк чаще всего проводится вместе с условием на выборку данных. Команда ORDER BY ставится после условия выборки WHERE. Например, выбираем товары с ценой меньше 100 рублей, упорядочив по названию в алфавитном порядке:

SELECT * FROM goods WHERE price 100 ORDER BY price ASC

Эта глава представляет команды которые управляют значениями представляемыми в таблице. Когда вы закончите эту главу, вы будете способны помещать строки в таблицу, удалять их, и изменять индивидуальные значения представленные в каждой строке. Будет показано использование запросов в формировании полной группы строк для вставки, а также, как может использоваться предикат для управления изменения значений и удаления строк. Материал в этой главе составляет полный объем знаний показывающий, как создавать и управлять информацией в базе данных. Более мощные способы проектировани предикатов будут обсуждены в следующей главе .

КОМАНДЫ МОДИФИКАЦИИ ЯЗЫКА DML

Значения могут быть помещены и удалены из полей, трем командами языка DML (Язык Манипулирования Данными): INSERT (ВСТАВИТЬ), UPDATE (МОДИФИЦИРОВАТЬ), DELETE (УДАЛИТЬ). Не смущайтесь, все они упоминались ранее в SQL, как команды модификации.

ВВОД ЗНАЧЕНИЙ

Все строки в SQL вводятся с использованием команды модификации INSERT. В самой простой форме, INSERT использует следующий синтаксис: INSERT INTO VALUES (, . . .); Так, например, чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее условие: INSERT INTO Salespeople VALUES (1001, "Peel", "London", .12); Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того что данные были использованы.

Им таблицы (в нашем случае - Salespeople (Продавцы)), должно быть предварительно определено, в команде CREATE TABLE (см. Главу 17), а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI, эти значения не могут составлять выражений, что означает что 3 - это доступно, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поименном порядке, поэтому первое значение с именем, автоматически попадает в столбец 1, второе в столбец 2, на так далее.

ВСТАВКА ПУСТЫХ УКАЗАТЕЛЕЙ (NULL)

Если вам нужно ввести пустое значение(NULL), вы вводите его точно так- же как и обычное значение. Предположим, что еще не имелось пол city для мистера Peel. Вы можете вставить его строку со значением=NULL в это поле, следующим образом: INSERT INTO Salespeople VALUES (1001, "Peel", NULL, .12); Так как значение NULL - это специальный маркер, а не просто символьное значение, он не включается в одиночные кавычки.

ИМЕНОВАНИЕ СТОЛБЦА ДЛЯ ВСТАВКИ (INSERT)

Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим что вы берете значения для таблицы Заказчиков из отчета выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке: INSERT INTO Customers (city, cnamе, cnum) VALUES ("London", "Honman", 2001); Обратите внимание что столбцы rating и snum - отсутствуют. Это значит, что эти строки автоматически установлены в значение - по умолчанию. По умолчанию может быть введено или значение NULL или другое значе- ние определяемое как - по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен как по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT которая относится к таблице(смотри Главу 18 для информации об ограничениях на NULL и на "по умолчанию").

ВСТАВКА РЕЗУЛЬТАТОВ ЗАПРОСА

Вы можете также использовать команду INSERT чтобы получать или вы- бирать значения из одной таблицы и помещать их в другую, чтобы использовать их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос: INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = "London"; Здесь выбираются все значения произведенные запросом - то есть все строки из таблицы Продавцов со значениями city = "London" - и помещаются в таблицу называемую Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:

* Она должна уже быть создана командой CREATE TABLE.

* Она должна иметь четыре столбца которые совпадают с таблицей

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

Общее правило то, что вставляемые столбцы таблицы, должны совпадать со столбцами выводимыми подзапросом, в данном случае, для всей таб- лицы Продавцов. Londonstaff - это теперь независима таблица которая получила некоторые значения из таблицы Продавцов(Salespeople). Если значения в таблице Продавцов будут вдруг изменены, это никак не отразится на таблице Londonstaff (хотя вы могли бы создать такой эффект, с помощью Представ- лени(VIEW), описанного в Главе 20). Так как или запрос или команда INSERT могут указывать столбцы по имени, вы можете, если захотите, переместить только выбранные столбцы а так- же переупорядочить только те столбцы которые вы выбрали.

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

Понимая что таблица Порядков охватывает последний финансовый год, а не только несколько дней, как в нашем примере, вы можете видеть преимущество использования следующего условия INSERT в подсчете и вводе значений INSERT INTO Daytotals (date, total) SELECT odate, SUM (amt) FROM Orders GROUP BY odate; Обратите внимание что, как предложено ранее, имена столбцов таблицы Порядков и таблицы Daytotals - не должны быть одинаковыми. Кроме того, если дата приобретения и общее количество - это единственные столбцы в таблице, и они находятся в данном порядке, их имена могут быть исключены из вывода из-за их очевидной простоты.

УДАЛЕНИЕ СТРОК ИЗ ТАБЛИЦ

Вы можете удалять строки из таблицы командой модификации - DELETE. Она может удалять только введенные строки, а не индивидуальные значе- ни полей, так что параметр пол является необязательным или недоступным. Чтобы удалить все содержание таблицы Продавцов, вы можете ввести следующее условие: DELETE FROM Salespeople; Теперь когда таблица пуста ее можно окончательно удалить командой DROP TABLE (это объясняется в Главе 17). Обычно, вам нужно удалить только некоторые определенные строки из таб- лицы. Чтобы определить какие строки будут удалены, вы используете предикат, так же как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести DELETE FROM Salespeople WHERE snum = 1003; Мы использовали поле snum вместо пол sname потому, что это лучшая тактика при использовании первичных ключей когда вы хотите чтобы действию подвергалась одна и только одна строка. Для вас - это аналогично действию первичного ключ. Конечно, вы можете также использовать DELETE с предикатом который бы выбирал группу строк, как показано в этом примере: DELETE FROM Salespeople WHERE city = "London";

ИЗМЕНЕНИЕ ЗНАЧЕНИЙ ПОЛЯ

Теперь, когда вы уже можете вводить и удалять строки таблицы, вы должны узнать как изменять некоторые или все значения в существующей строке. Это выполняется командой UPDATE. Эта команда содержит предложение UPDATE в которой указано им используемой таблицы и предложение SET которое указывает на изменение которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, вы можете ввести UPDATE Customers SET rating = 200;

МОДИФИЦИРОВАНИЕ ТОЛЬКО ОПРЕДЕЛЕННЫХ СТРОК

Конечно, вы не всегда захотите указывать все строки таблицы для изменения единственного значения, так что UPDATE, наподобие DELETE, может брать предикаты. Вот как например можно выполнить изменение одинаковое для всех заказчиков продавца Peel (имеющего snum=1001): UPDATE Customers SET rating = 200 WHERE snum = 1001;

КОМАНДА UPDATE ДЛЯ МНОГИХ СТОЛБЦОВ

Однако, вы не должны, ограничивать себя модифицированием единственного столбца с помощью команды UPDATE. Предложение SET может назначать любое число столбцов, отделяемых запятыми. Все указанные назначения могут быть сделаны для любой табличной строки, но только для одной в каждый момент времени. Предположим, что продавец Motika ушел на пенсию, и мы хотим переназначить его номер новому продавцу: UPDATE Salespeople SET sname = "Gibson",city = "Boston",comm = .10 WHERE snum = 1004; Эта команда передаст новому продавцу Gibson, всех текущих заказчиков быв- шего продавца Motika и порядки, в том виде в котором они были скомпонованы для Motika с помощью пол snum. Вы не можете, однако, модифицировать сразу много таблиц в одной команде, частично потому, что вы не можете использовать префиксы таблицы со столбцами измененными предложением SET. Другими словами, вы не можете сказать - "SET Salespeople.sname = Gibson" в команде UPDATE, вы можете сказать только так - "SET sname = Gibson".

ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ ДЛЯ МОДИФИКАЦИИ

Вы можете использовать скалярные выражения в предложении SET команды UPDATE, однако, включив его в выражение пол которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений - весьма полезна особенность. Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение: UPDATE Salespeople SET comm = comm * 2; Всякий раз, когда вы ссылаетесь к указанному значению столбца в предложении SET, произведенное значение может получится из текущей строки, прежде в ней будут сделаны какие-то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности, и сказать, - удвоить комиссию всем продавцам в Лондоне, таким предложением: UPDATE Salespeople SET comm = comm * 2 WHERE city = "London";

МОДИФИЦИРОВАНИЕ ПУСТЫХ(NULL) ЗНАЧЕНИЙ

Предложение SET - это не предикат. Он может вводить пустые NULL значения также как он вводил значения не используя какого-то специального синтаксиса (такого например как IS NULL). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение: UPDATE customers SET rating = NULL WHERE city = "London"; что обнулит все оценки заказчиков в Лондоне.

РЕЗЮМЕ

Теперь вы овладели мастерством управления содержанием вашей базы данных с помощью трех простых команд: INSERT - используемой чтобы помещать строки в базу данных; DELETE - чтобы удалять их; REFERENCES - чтобы изменять значения в уже вставленных строках. Вы обучались использованию предиката с командами UPDATE и DELETE чтобы определить, на которую из строк будет воздействовать команда. Конечно, предикаты как таковые - не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако, вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причем это, вы можете делать со столбцами в любом порядке. Вы узнали, что значения по умолчанию, могут помещаться в столбцы, если вы не устанавливаете это значение явно. Вы также видели использование стандартного значения по умолчанию, которым является NULL. Кроме того, вы поняли, что UPDATE может использовать выражение значения, тогда как INSERT не может. Следующая глава расширит ваше познания, показав вам, как использовать подзапросы с этими командами. Эти подзапросы напоминают те, с которыми вы уже знакомы, но имеются некоторые специальные выводы и ограничения, когда подзапросы используются в командах DML, что мы будем обсуждать в Главе 16 .

РАБОТА С SQL

1. Напишите команду которая бы поместила следующие значения, в их нижеуказанном порядке, в таблицу Продавцов: city - San Jose, name - Bianco, comm - NULL, cnum - 1100. 2. Напишите команду которая бы удалила все порядки заказчика Clemens из таблицы Порядков.

3. Напишите команду которая бы увеличила оценку всех заказчиков в Риме на 100.

4. Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

Основная задача при проектировании реляционных БД – формирование оптимальных отношений.

Для формализации процесса построения оптимальной реляционной БД используется теория нормализации, основанная на том, что определенный набор отношений обладает лучшими свойствами при включении, модификации и удалении данных, чем все остальные наборы отношений, с помощью которых могут быть представлены те же данные (см. гл. 4).

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

На этом "бумажное" построение БД заканчивается. Компьютерная реализация БД определяется языками описания (ЯОД) и манипулирования (ЯМД) данными. Они могут базироваться на реляционной алгебре (процедурные языки) и реляционном исчислении кортежей и доменов (декларативные языки). На исчислении кортежей основан язык SQL, на исчислении доменов – язык QBE.

Рассмотрим возможности языков SQL и QBE. Прикладное их использование описано в гл. 15.

Язык SQL

Для SQL имеется много вариантов и диалектов. Здесь изложим основные положения базового варианта: более подробное описание языка приведено в .

Иллюстрацию языка SQL проведем на примере базы данных "Снабжение", представленной в табл. 5.3–5.5. Для нее схема Access- связей показана на рис. 5.2.

Таблица 5.3

"Продавцы"

Таблица 5.4

"Заказчики"

С.-Петербург

Таблица 5.5

"Заказы"

Название полей таблиц

Таблица "Продавцы":

пном – уникальный номер продавца, первичный ключ;

пимя – имя продавца;

город – город расположения продавца;

комм – комиссионные продавца.

Таблица "Заказчики":

зном – уникальный номер заказчика, первичный ключ;

пном – номер продавца, внешний ключ.

Таблица "Заказы":

прном – уникальный номер заказа, первичный ключ;

сумпр – сумма (иена) заказа;

датпр – дата получения заказа;

зном – номер заказчика, делающего заказ, внешний ключ;

пном – номер продавца, продающего заказ, внешний ключ.

Рис. 5.2. Схема связей

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

Статический язык программирования SQL

В языке SQL возможно выделить три основные группы операций: создание (CREATE), обновление (INSERT, UPDATE, DELETE), запрос (SELECT). Они имеют следующие стандарты, в которых приняты обозначения: | – все, что предшествует символу, можно заменить тем, что следует за ним; {} – единое целое для применения символа; – необязательное выражение; ... – повторяется произвольное число раз; – повторяется произвольное число раз, но любое вхождение отделяется запятой.

(5.1)

Типы данных могут быть". INTEGER, CHARACTER, DECIMAL, NUMERIC, SMALLINT, FLOAT, REAL, PRECISION, LONG, VARCHAR, DATE, TIME. Четыре последние типа не входят в стандарт SQL, но им могут поддерживаться.

Тип столбца (и тип таблиц) может быть: UNIQUE, PRIMARY KEY, CHECK <предикат>, DEFAULT = <список полей>, REFERENCE <имя таблицы> [(<имя столбца>.,...)|.

(5.3)

(5.4)

Выделяют три разновидности языка SQL: интерактивный, вложенный и встроенный.

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

Встроенный SQL состоит из команд SQL, помещенных внутри программ, которые обычно написаны на другом языке (типа КОБОЛА или Паскаля). Это делает такие программы более мощными и эффективным.

Будем рассматривать преимущественно (при отсутствии упоминаний) интерактивный язык.

♦ ИНТЕРАКТИВНЫЙ ЯЗЫК SQL

В нем возможно выделить:

  • DDL (Язык Описания Данных) – язык описания схемы и в ANSI он состоит из команд, создающих объекты (таблицы, индексы, виды) в базе данных;
  • DML (Язык Манипулирования Данными) – набор команд, определяющих, какие значения представлены в таблицах в любой момент времени;
  • DCD (Язык Управления Данными) состоит из средств, которые определяют разрешение пользователю выполнять определенные действия.

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

  • 1) создание БД – структуры таблиц, создание видов, заполнение БД данными, обеспечение целостности, система доступа (разрешений), словарь данных, многопользовательский режим;
  • 2) использование БД – запрос в различных формах (в том числе с обновлением).

Создание БД

Структура таблиц. Таблицы (пустые) создаются командой CREATE TABLE (выражение (5.1)).

(5.5)

Команда CREATE TABLE в основном определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных и размеры столбцов.

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

SQL позволяет создавать временные таблицы, "время жизни" которых – сеанс работы БД (время от открытия до закрытия базы данных).

Таблица может быть глобальной, т. е. доступной всей прикладной программе, создавшей ее:

CREATE GLOBAL TEMPORARY TABLE Продавцы

(пном integer,

пимя char (10),

город char (10),

комм decimal);

Она может быть локальной, доступной только для модуля программы, в котором она была создана:

CREATE LOCAL TEMPORARY TABLE Продавцы

(пном integer,

пимя char (10),

город char (10),

комм decimal);

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

ALTER TABLE <имя таблицы> ADD <имя поля>

<тип данных> <размер>;

Столбец будет добавлен последним и со значением NULL для всех строк таблицы.

Удаление проводится по команде

DROP TABLE <имя таблицы>;

Надо быть создателем таблицы, чтобы иметь возможность удалить ее.

Аналогично создаются и удаляются индексы.

Базовая таблица (5.5) представлена в простейшем виде и будет далее усовершенствована: другие таблицы будут созданы позднее. Сейчас рассмотрим лишь создание вида.

Структура и содержание видов. Только что созданная таблица называется базовой. Можно создавать представление (вид, View) – таблицы, содержимое которых берется или выводится из других таблиц. Вид создается командой CREATE VIEW:

CREATE VIEW Москва1

FROM Продавцы

WHERE город = "Москва";

Москва 1 – представление (вид). Оно используется для целей защиты информации и вычисляется каждый раз при запросе и потому обновление данных автоматическое. Его можно использовать так же, как и любую другую базовую таблицу, однако в процедурах доступа и обновления имеется специфика.

Большое количество типов представлений доступно только для чтения. Это означает, что их можно запрашивать, но они не могут подвергаться действиям команд модификации.

Имеются некоторые виды запросов, которые не допустимы в определениях представлений: одиночное представление должно основываться на одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNION ALL), агрегатные функции, DISTINCT в определении, вычисляемые поля не разрешаются в работе с представлениями; упорядочение (ORDER BY) никогда не используется в определении представлений.

Удаление представлений осуществляется (его владельцем) командой

DROP VIEW <имя вида>.

Заполнение БД данными. Значения могут быть помещены и удалены из полей командами языка DML (Язык Манипулирования Данными – ЯМД) INSERT (ВСТАВИТЬ), DELETE (УДАЛИТЬ) – выражения (5.2) и (5.3). Так, например, чтобы ввести строку в таблицу "Продавцы", можно использовать следующее условие;

INSERT INTO Продавцы

VALUES (", "Строков", "Москва", .12);

Можно вставлять и пустое значение (NULL).

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

INSERT INTO Заказчики (город, имя, номер)

VALUES ("Москва", "Иванов", 2001);

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

DELETE FROM Продавцы;

Теперь таблица пуста и ее можно окончательно удалить командой DROP TABLE. Обычно нужно удалить только некоторые определенные строки из таблицы, для чего используется предикат. Например, чтобы удалить данные продавца Козлова из таблицы "Продавцы", можно ввести

DELETE FROM Продавцы

WHERE пном = 1003;

Команды INSERT, DELETE совместно с командой UPDATE используются в процедуре обновления при эксплуатации БД.

Задание (обеспечение ) целостности. Это разновидность команды CREATE TABLE, позволяющая устанавливать ограничения в таблицах.

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

CREATE TABLE <имя таблицы>

Сограничение таблицы> (<имя столбца>

[, <имя столбца> ]...);

Перечислим некоторые ограничения.

  • 1. Исключение пустых (NULL) указателей введением команды NOT NULL.
  • 2. Уникальность данных и первичные ключи.

Ограничение столбца UNIQUE в поле при создании таблицы отклонит любую попытку ввода в это поле для одной из строк значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL).

  • 3. SQL поддерживает первичные ключи непосредственно с ограничением PRIMARY KEY (первичный ключ). Первичные ключи не могут иметь значений NULL. Это означает, что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL.
  • 4. Ограничения на значения полей. Для этого используется ограничение CHECK: чтобы предотвратить ошибку неправильного введения значения "комм", наложим ограничение столбца – CHECK ("комм" меньше, чем I).

Сказанное может быть представлено в виде

CREATE TABLE Продавцы

город char(10),

комм decimal CHECK (комм < 1));

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

Создадим предварительно таблицу "Заказы":

CREATE TABLE Заказы

(прном integer NOT NULL UNIQUE,

сумпр decimal,

датпр date NOT NULL,

зном integer NOT NULL,

пном integer NOT NULL);

5. Установка значений no умолчанию.

Значение DEFAULT (ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца.

Если офис находится в Орле и подавляющее большинство продавцов тоже живут в Орле, то по умолчанию:

CREATE TABLE Продавцы

(пном integer NOT NULL UNIQUE,

пимя char(10) NOT NULL UNIQUE,

город char(10) DEFAULT = "Орел",

комм decimal CHECK (комм < 1);

6. Ограничения на внешний ключ (ссылочная целостность). SQL поддерживает ссылочную целостность в команде CREATE TABLE (или ALTER TABLE) ограничением FOREIGN KEY с синтаксисом

FOREIGN KEY <список полей> REFERENCES

Стаблица с родительским ключом> [<список полей>.

Создадим таблицу "Заказчики" с полем "пном", определенным в качестве внешнего ключа, ссылающегося на таблицу "Продавцы":

CREATE TABLE Заказчики

(зном integer NOT NULL PRIMARY KEY зимя char(10), город char(10),

пном integer,

FOREIGN KEY (пном) REFERENCES Продавцы(пном);

Ограничение могло быть внесено и отдельно:

CONSTRAINT полезном FOREIGN KEY (пном)

REFERENCES Продавцы(пном);

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

DROP CONSTRAINT поле_пном;

особенно удобно, если таблица заполнена.

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

CREATE TABLE Продавцы

(пном integer NOT NULL PRIMARY KEY,

пимя char(10) NOT NULL,

город char(10),

комм decimal);

CREATE TABLE Заказчики

зимя char(10) NOT NULL,

город char{ 10),

FOREIGN ΚΕΥ(πηομ) REFERENCES Продавцы,

UNIQUE (зном, пном);

CREATE TABLE Заказы

(прном integer NOT NULL PRIMARY KEY,

сумпр decimal,

датпр date NOT NULL,

зном integer NOT NULL

пном integer NOT NULL

FOREIGN KEY (зном, пном) REFERENCES

Заказчики (зном, пном);

Таким образом, созданы все три таблицы и установлены связи между ними.

Если необходимо изменить или удалить текущее ссылочное значение родительского ключа, имеется три возможности:

  • 1) ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены (RESTRICTED);
  • 2) можно сделать изменение в родительском ключе и тем самым автоматические изменения во внешнем ключе, т. е. каскадное изменение (CASCADES);
  • 3) провести изменение в родительском ключе и установить автоматически внешний ключ в NULL (полагая, что NULLS разрешен во внешнем ключе) – пустое изменение внешнего ключа (NULL).

Например, обновление и уничтожение

CREATE TABLE Заказчики

(зном integer NOT NULL PRIMARY KEY,

зимя char(10) NOT NULL, город char(10),

UPDATE OF CASCADES,

DELETE OF Продавцы RESTRICTED);

Если теперь попробовать удалить Строкова из таблицы "Продавцы", команда будет не допустима, пока не будет изменено значение поля "пном" "Заказчики" и Иванов и Крабов – для другого назначенного продавца.

В то же время можно изменить значение поля "пном" для Строкова на 1009, а у данных Иванов и Крабов значения поля "пном" будут также автоматически изменены.

Возможны и NULL (пустые) изменения. Например,

CREATE TABLE Заказы

прном integer NOT NULL PRIMARY KEY,

сумпр decimal,

датпр date NOT NULL,

3HOM integer NOT NULL REFERENCES Заказчики,

пном integer REFERENCES Продавцы,

UPDATE OF Заказчики CASCADES,

DELETE OF Заказчики CASCADES,

UPDATE OF Продавцы CASCADES,

DELETE OF Продавцы NULLS);

Естественно, что в команде DELETE с эффектом NULL в таблице "Продавцы" ограничение NOT NULL должно быть удалено из поля "пном".

С помощью языка SQL могут быть заданы база данных, хранимая процедура, генератор, счетчик, триггер.

База данных формируется так:

CREATE DATABASE "d:...a1 .gdb"

PASSWORD "masterkey"

Default character set WIN 1251

Хранимая процедура:

CREATE PROCEDURE Rashod_Tovara(IN_Tovar varchar (20))

RETURNS(OUT_Tovar varchar(20))

FOR SELECT Tovar

WHERE Tovar=: IN_Tovar

Генератор задается двумя процедурами:

CREATE PROCEDURE Get_N_Rash;

SET GENERATOR RASHOD_N_Rash TO 1;

CREATE PROCEDURE Get_N_Rash

RETURNS(NR integer)

NR=Gen_ID(RASHOD_N_Rash, 1);

Триггер каскадного обновления:

CREATE TRIGGER BU_Tovary ACTIVE

IF (OLD.TovarONEW.Tovar) THEN

SET Tovar=NEW.Tovar

WHERE Tovar=OLD.Tovar;

Система разрешений. Возможна система разрешения (привилегий) или запрета доступа к данным. Напомним, что администраторы баз данных сами создают пользователей и дают им привилегии. Однако и пользователи, которые создают таблицы, имеют права на управление этими таблицами.

Привилегии – это то, что определяет, может ли указанный пользователь выполнить данную команду.

Рассмотрим основные принципы их построения на базе языка SQL. Различают следующие приоритеты (по убыванию): роль, пользователь, группа, общий доступ (public).

Имеются разрешающие и запрещающие действия.

Разрешение дается оператором вида

GRANT <вид операции>

ON <объект>

ТО <субъект>

Последняя строка говорит о передаче права пользования.

Для уверенного управления необходимо сочетание администратора БД и владельца объекта (например, таблицы).

Создание пользователя Илья (которому предоставляется возможность создания баз данных), выполняемое администратором БД, определяется командой

CREATE USER Илья

WITH PRIVILEGES create.db;

лишение этого пользователя привилегий осуществляется командой

DROP USER Илья;

создание роли (с заданием пароля) проводится командой

CREATE ROLE create_db WITH PASSWORD = "12";

а лишение роли – командой

DROP ROLE create_db;

Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL: GRANT (допуск) и REVOKE (отмена).

Каждый пользователь в среде SQL имеет специальное идентификационное имя (идентификатор – ID) или номер.

ID-разрешения – это имя пользователя. SQL может использовать специальное ключевое слово USER, которое ссылается на идентификатор доступа, связанный с текущей командой. Команда интерпретируется и разрешается (или запрещается).

Привилегии объекта связаны одновременно и с пользователями, и с таблицами. Следует помнить, что пользователь, создавший таблицу (любого вида), является владельцем этой таблицы: он имеет все привилегии в этой таблице и может передавать эти привилегии другим пользователям.

Привилегии относятся к командам SELECT, INSERT, UPDATE, DELETE, REFERENCES (определение внешнего ключа, который использует один или более столбцов этой таблицы, как родительский ключ).

К нестандартным командам относятся INDEX (индекс), дающий право создавать индекс в таблице, и ALTER (изменить) – для выполнения команды ALTER TABLE в таблице. Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT.

Например, пользователь Илья имеет таблицу "Заказчики" и хочет позволить пользователю Петр выполнить запрос к ней:

GRANT SELECT ON Заказчики ТО Петр;

Петр может выполнить запросы к таблице "Заказчики", но не может предоставить право SELECT другому пользователю: таблица еще принадлежит Илье.

Возможны и групповые привилегии:

GRANT SELECT, INSERT ON Заказы TO Илья, Петр;

Для команд UPDATE и REFERNCES можно указывать и отдельные поля:

GRANT UPDATE (комм) ON Продавцы ТО Илья;

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

GRANT REFERENCES (зимя, зном)

ON Заказчики ТО Степан;

SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (все привилегии) или просто ALL – для команд и PUBLIC (общие) – для пользователей.

GRANT ALL ON Заказчики ТО PUBLIC;

Возможность передачи пользователем предоставленных ему привилегий осуществляется предложением WITH GRANT OPTION.

Иногда создателю таблицы хочется, чтобы другие пользователи могли получить привилегии в его таблице. Обычно это делается в системах, где один или более людей создают несколько (или все) базовые таблицы в базе данных, а затем передают ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION.

Пусть Илья передает право Петру на привилегию SELECT в таблице "Заказчики":

GRANT SELECT ON Заказчики ТО Петр

WITH GRANT OPTION;

Возможно разрешить выполнение процедуры integ check

ON PROCEDURE integ_check

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

REVOKE INSERT ON Заказы FROM Петр;

Здесь также можно использовать списки:

REVOKE INSERT, DELETE ON Заказчики FROM Петр, Степан;

Возможно задание (или отмена) привилегий с помощью рассмотренных ранее видов. Например,

CREATE VIEW Москва 1

SELECT зимя, пимя FROM Продавцы;

предоставляет Москва1 привилегию SELECT в виде (представлении), а не в самой таблице "Продавцы":

GRANT SELECT ON Москва1 ТО Виктор;

Привилегии возможно ограничивать и строками:

CREATE VIEW Москва2

FROM Заказчики

WHERE город = "Сочи"

WITH CHECK OPTION;

GRANT UPDATE ON Москва2 TO Петр;

Предложение WITH CHECK OPTION предохраняет Петр от замены значения поля "город" на любое значение, кроме Сочи.

Существует целый ряд вариантов работы с видами .

В системе любого размера всегда имеются некоторые типы суперпользователей – чаще всего Администратор Базы Данных или DBA. У него есть такие системные привилегии: CONNECT (подключить), RESOURCE (ресурс) и DBA (Администратор Базы Данных).

CONNECT состоит из права зарегистрироваться и права создавать представления и синонимы, RESOURCE состоит из права создавать базовые таблицы, DBA – это привилегия, дающая пользователю высокие полномочия в базе данных.

Некоторые системы имеют специального пользователя, иногда называемого SYSADM или SYS (Системный Администратор Базы Данных), который имеет наивысшие полномочия.

Команда GRANT, в измененной форме, является пригодной для использования с привилегиями объекта, как и с системными привилегиями:

GRANT RESOURCE ТО Мирон;

Естественно, пользователь Мирон должен быть создан.

У пользователя может быть и пароль (например, Иван). Тогда команда имеет вид

GRANT CONNECT ТО Федор IDENTIFIED BY Иван;

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

Если нужно запретить пользователю регистрироваться, следует использовать для REVOKE привилегию CONNECT, которая "удаляет" этого пользователя.

Запрещение (на создание таблиц в БД newa группе clerck) имеет вид

Иногда бывает нужно, чтобы в sql-запросе в случае выполнения условия происходила выборка по одному полю (или выражению), а в противном случае происходила выборка другого поля (или выражения), т.е. выбор поля или выражения в зависимости от условия. Либо, необходимо обновить значения в базе данных в зависимости от условия. Для решения этой задачи нужно использовать оператор CASE. У этого оператора есть 2 варианта синтаксиса, каждый из которых удобен в своих случаях. Просто приведу наглядные примеры обоих способов.

Пример 1 . Вывод разных результатов в зависимости от того, какое значение в поле.

SELECT ProductNumber, Category = CASE ProductLine WHEN "R" THEN "Road" WHEN "M" THEN "Mountain" WHEN "T" THEN "Touring" WHEN "S" THEN "Other sale items" ELSE "Not for sale" END, Name FROM Production.Product ORDER BY ProductNumber

В данном примере всё просто: если ProductLine = "R", то в выборке результатов будет текст "Road", если ProductLine = "M" - тогда получим в результатах "Mountain" и т.д.

Пример 2 . Более сложное условие для получения разного текста в зависимости от значения в поле.

SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN "Mfg item - not for resale" WHEN ListPrice < 50 THEN "Under $50" WHEN ListPrice >= 50 and ListPrice < 250 THEN "Under $250" WHEN ListPrice >= 250 and ListPrice < 1000 THEN "Under $1000" ELSE "Over $1000" END FROM Production.Product ORDER BY ProductNumber

Пример 3 . Использование конструкции наподобие "IF THEN ELSE" в запросе

SELECT FirstName, LastName, TelephoneNumber, CASE WHEN TelephoneSpecialInstructions IS NULL THEN "Any time" ELSE TelephoneSpecialInstructions END AS WhenContact FROM Person.vAdditionalContactInfo

Пример 4 . Использование условия при сортировке результатов запроса в ORDER BY

SELECT EmployeeID, EmployeeFIO, SalariedFlag FROM Employee ORDER BY CASE WHEN SalariedFlag = 1 THEN EmployeeID WHEN SalariedFlag = 0 THEN EmployeeFIO END

Принцип сортировки с условием в том, что сначала будут получены все значения в зависимости от условия (т.е. будет получено доп.поле), после чего весь результат запроса будет отсортирован по результатам выполнения условия. Именно поэтому, результаты выполнения условия должны быть одинакового типа данных.

Пример 5 . Обновление поля новыми данными в зависимости от условия.

UPDATE Employee SET VacationHours = CASE WHEN VacationHours < 10 THEN VacationHours + 40 ELSE VacationHours + 20 END OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0

В этом примере к полю VacationHours добавляется 40, если VacationHours меньше 10, иначе добавляется 20. После выполнения обновлений запрос так же выведет таблицу с колонками EmployeeID и значением поля VacationHours "До" и "После" обновления.