Какие операторы являются основными при модификации данных с помощью sql

Какие операторы являются основными при модификации данных с помощью sql

В этом разделе мы изучим различные операторы SQL, включая операторы для выбора данных, их добавления, удаления или изменения, изменения метаданных и пр.

Выбор данных

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

SELECT column-list FROM table-list [WHERE where-clause] [ORDER BY order-by-clause]

Операторы SELECT должны содержать слова SELECT и FROM; другие ключевые слова, такие как WHERE или ORDER BY, являются необязательными.

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

SELECT *

Для выбора одной колонки применяется следующий синтаксис:

SELECT CompanyName

Пример выбора нескольких колонок имеет вид:

SELECT CompanyName, ContactName, ContactTitle

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

SELECT Customers.CompanyName, Shippers.CompanyName

Предложение FROM

Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:

Этот запрос возвратит все поля из таблицы Customers.

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

Пример запроса к более чем одной таблице приведен ниже:

SELECT Customers.CompanyName, Shippers.CompanyName FROM Customers, Shippers

Предложение WHERE

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

WHERE expression1 [ expression2 […]]

Например, вместо получения полного списка продуктов можно ограничиться только теми из них, у которых значение поля CategoryID равно 4:

SELECT * FROM Products WHERE CategoryID = 4

В предложении WHERE можно использовать различные выражения, например:

SELECT * FROM Products WHERE CategoryID = 2 AND SupplierID > 10

SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 3 OR UnitPrice < 50

SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL

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

В предложении WHERE можно использовать один из шести операторов отношений, определенных в SQL. Эти операторы приведены в табл. 8.

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

Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘M%’

В данной маске символ ‘%’ (процент) заменяет любую последовательность символов, а символ ‘_’ (подчеркивание) — один любой символ. Тот же самый результат может быть получен следующим способом:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘M’ AND ‘N’

В последнем примере мы можем расширить область поиска. В частности, при поиске компаний с именами, начинающимися с букв от A до C, можно выполнить следующий оператор SELECT:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN ‘A’ AND ‘D’

Используя оператор LIKE, мы можем сузить диапазон поиска, применив более сложную маску для сравнения. Например, чтобы найти компании, содержащие в своем названии подстроку bl, можно применить следующий запрос:

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘%bl%’

Маска ‘%bl%’ показывает, что до и после искомой подстроки может быть любое количество произвольных символов.

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

SELECT CompanyName, ContactName FROM Customers WHERE CustomerID IN (‘ALFKI’, ‘BERGS’, ‘VINET’)

Операторы AND, OR и NOT

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

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ AND Country = ‘USA’

Результатом выполнения этого запроса будет список заказчиков, находящихся в США, название которых начинается с буквы S.

Оператор OR позволяет выбрать записи, удовлетворяющие хотя бы одному из перечисленных условий, в то время как оператор NOT используется для исключения из набора данных записей, удовлетворяющих данному условию. Например, можно применить оператор OR для поиска всех заказчиков, либо находящихся в Калифорнии, либо имеющих название, начинающееся с буквы S (и при этом находящихся где угодно):

SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE ‘S%’ OR Region=’CA’

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

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

SELECT CompanyName, ContactName FROM Customers WHERE Country NOT IN (‘USA’, ‘UK’)

В результате выполнения этого запроса мы получим список заказчиков из всех стран, кроме США и Великобритании.

Предложение ORDER BY

Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Синтаксис предложения ORDER BY имеет вид:

ORDER BY column1 [] [, column2 [] [,…]

Например, для сортировки сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:

SELECT LastName, FirstName, Title FROM Employees ORDER BY LastName, FirstName

Если сортировка данных требуется в убывающем порядке (например, требуется список продуктов в порядке убывания цен), используется ключевое слово DESC:

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC

Связывание таблиц

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

SELECT ProductName, CategoryName FROM Products, Categories

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

SELECT ProductName, CategoryName FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID

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

SELECT column-list FROM table1, table2 WHERE table1.column1=table2.column2

Следующие несколько примеров связывания таблиц характерны для Microsoft Access и Microsoft SQL Server и могут не работать с другими СУБД, однако мы полагаем, что иллюстрируемая ими функциональность достаточно важна.

Существует несколько типов связывания таблиц. Например, следующий оператор SQL осуществляет так называемое внутреннее соединение таблиц (inner join) — в этом случае в результирующем наборе данных содержатся записи, в которых значения в связанных полях совпадают:

SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Так называемые внешние соединения (outer joins) позволяют нам включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой таблицы. Например:

SELECT ProductName, CategoryName FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Это было так называемое левое внешнее соединение (left outer join). Существуют также правые внешние соединения (right outer join), возвращающие все строки из второй (то есть правой) таблицы и соответствующие им строки из другой таблицы:

SELECT ProductName, CategoryName FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

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

SELECT ProductName, CategoryName FROM Products FULL OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

Для получения всех комбинаций строк из обеих таблиц (декартова произведения) можно использовать ключевое слово CROSS JOIN без указания связываемых полей:

SELECT ProductName, CategoryName FROM Products CROSS JOIN Categories

Если в запросе используется более трех таблиц, можно иcпользовать вложенные соединения.

Предложение GROUP BY

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

GROUP BY [, …]

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

SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID

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

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

Предложение HAVING

Предложение HAVING имеет назначение, сходное с предложением WHERE, но используется с агрегатными данными. Например:

SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID HAVING COUNT(Orders.OrderID) >= 10

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

Ключевые слова ALL и DISTINCT

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

SELECT DISTINCT Country FROM Customers

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

Ключевое слово TOP

Ключевое слово TOP может быть использовано для возврата первых n строк или первых n процентов таблицы. Например, запрос:

SELECT TOP 10 * FROM PRODUCTS ORDER BY ProductName

возвращает первые 10 продуктов из таблицы, тогда как запрос:

SELECT TOP 25 PERCENT * FROM PRODUCTS ORDER BY ProductName

вернет первую четверть записей таблицы.

Модификация данных

До сих пор мы изучали операторы SQL для извлечения данных. Помимо этого язык SQL может быть использован для обновления и удаления данных, копирования записей в другие таблицы и выполнения многих других операций. Ниже мы рассмотрим операторы UPDATE, DELETE и INSERT, используемые для решения некоторых из этих задач.

Оператор UPDATE

Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтакcис этого оператора имеет вид:

UPDATE tableSET column1 = expression1 [, column2 = expression2] [,…] [WHERE criteria]

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

UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE UnitPrice < 10

Оператор DELETE

Для удаления строк из таблиц следует использовать оператор DELETE, синтаксис которого имеет вид:

DELETE FROM table [WHERE criteria]

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

Например, для удаления из списка всех продуктов, которые больше не поставляются, можно выполнить следующий запрос:

DELETE FROM Products WHERE Discontinued = 1

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

SELECT ProductName FROM Products WHERE Discontinued = 1

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

SELECT CompanyName FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

а затем заменить оператор SELECT на оператор DELETE:

DELETE FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

Замечание. При использовании в операторах SQL даты или времени, а также полей, содержащих такие данные, следует уточнить синтаксис таких предложений в документации из комплекта поставки используемой СУБД.

Оператор INSERT

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

INSERT [INTO] table ( [column_list] < VALUES ( < DEFAULT | NULL | expression >> [, …] )

Например, для добавления нового клиента в таблицу Customers можно использовать следующий запрос:

INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘XYZFO’, ‘XYZ Deli’)

Модификация метаданных

Существует несколько операторов SQL для управления метаданными, используемых для создания, изменения или удаления баз данных и содержащихся в них объектов (таблиц, представлений и др.). Мы рассмотрим некоторые из них: CREATE TABLE, ALTER TABLE и DROP.

Оператор CREATE TABLE

Для создания новой таблицы необходимо использовать оператор CREATE TABLE, синтаксис которого имеет вид:

CREATE TABLE table ( column1 type1 [(size1)][CONSTRAINT _ column-constraint1] [, column2 type2 [(size2)][CONSTRAINT _ column-constraint2] [, . ]] [CONSTRAINT table-constraint1 _ [,table-constraint2 [, . ]]]);

В этом операторе следует указать имя поля, тип данных для него (тип данных должен поддерживаться данной СУБД), длину (для некоторых типов полей) и, если нужно, серверные ограничения (с применением ключевого слова CONSTRAINT). Например, следующий запрос создает таблицу с именем Simple с четырьмя колонками — LastName, FirstName, EMail и HomePage:

CREATE TABLE Simple (FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255) )

Мы можем расширить эту таблицу добавлением поля PersonID, которое будет использовано как первичный ключ:

CREATE TABLE Simple ( PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255) )

и указать, что комбинация полей LastName и FirstName должна быть уникальна:

CREATE TABLE Simple ( PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255), CONSTRAINT SimpleConstraint UNIQUE (FirstName, LastName) )

Используя предложение SELECT и ключевое слово INTO, мы можем создавать новые таблицы, основанные на условии, указанном в предложении WHERE. Например:

SELECT * INTO NewOrders FROM Orders WHERE OrderDate > 1/1/97

Этот запрос создаст новую таблицу NewOrders и заполнит ее данными о заказах начиная с 1 января 1997 года.

Оператор ALTER TABLE

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

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

ALTER TABLE table ADD [COLUMN] column datatype [(size)] [CONSTRAINT sinlge-column-constraint]

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

ALTER TABLE Simple ADD Phone varchar(30)

Вторая разновидность оператора ALTER TABLE применяется для добавления серверных ограничений к таблице, а ее синтаксис имеет вид:

ALTER TABLE table ADD CONSTRAINT constraint

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

Третья разновидность предложения ALTER TABLE применяется для удаления поля из таблицы:

ALTER TABLE table DROP [COLUMN] column

Ключевое слово COLUMN использовать не обязательно. Например:

ALTER TABLE Simple DROP Phone

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

ALTER TABLE table DROP CONSTRAINT index

Ниже приведен пример такого запроса:

ALTER TABLE Simple DROP CONSTRAINT PrimaryKey

Оператор DROP

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

DROP TABLE table

Вторая разновидность используется для удаления индекса:

DROP INDEX index ON table

Другие операторы SQL

Как было отмечено ранее, существует около 40 операторов SQL. Мы рассмотрели большинство из них. Некоторые из не рассмотренных нами операторов перечиcлены ниже:

1. ператоры CREATE, такие как CREATE DATABASE, CREATE VIEW и CREATE TRIGGER (два последних из них мы рассмотрим в следующей статье данного цикла);
2. операторы ALTER, такие как ALTER DATABASE, ALTER VIEW и ALTER TRIGGER;
3. операторы DROP, такие как DROP DATABASE, DROP VIEW и DROP TRIGGER;
4. BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK TRANSACTION для выполнения группы нескольких операторов как единой логической группы;
5. DECLARE CURSOR, OPEN и FETCH для работы с курсорами;
6. GRAND и REVOKE для добавления или удаления прав на использование объектов базы данных, а также CREATE USER, ALTER USER, DROP USER, CREATE GROUP, ALTER GROUP и DROP GROUP для управления списком пользователей и групп пользователей.

Заключение

В данной статье мы рассмотрели все основные компоненты языка SQL. Мы узнали, что:

1. SQL — непроцедурный язык, предназначенный для управления данными в реляционных СУБД. Последний официальный стандарт был опубликован ANSI в 1992 году, и современная реализация SQL называется SQL92. Язык SQL поддерживается большинством производителей СУБД;
2. оператор SELECT следует использовать для извлечения данных из таблиц. Предложение WHERE можно применять для того, чтобы ограничить результирующий набор данных записями, удовлетворяющими заданному условию;
3. предложение GROUP BY может быть использовано для создания результирующего набора данных, содержащего суммарные данные из одной или нескольких таблиц;
4. для получения данных из нескольких таблиц можно использовать ключевое слово JOIN;
5. для изменения данных применяется операторы INSERT, UPDATE и DELETE;
6. операторы CREATE, ALTER и DROP могут быть использованы для создания, модификации и удаления баз данных и содержащихся в них объектов — таблиц, представлений и др.

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

Основы языка SQL

Кроме перечисленных выше в языке SQL реализованы операции модификации содержимого строк таблицы и пополнения таблицы новыми строками (что теоретически может рассматриваться как операция объединения), а также операции управления таблицами.
Рассмотренные выше операции над таблицами реляционной БД обладая функциональной полнотой, будучи реализованы на практике в своем ?чистом? каноническом виде, как правило, крайне неэкономичны (в первую очередь это относится к комбинации операций ограничения и декартового произведения). Разработчики реальных реляционных СУБД прибегают ко всевозможным приемам и ?ухищрениям? для минизации вычислительных затрат (в первую очередь, машинного времени) при выполнении этих операций. Общим способом, нашедшим отражение в языке SQL, повышения эффективности выполнения запросов в реляционных СУБД являются импользование ключей индексов.

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

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

• Sybase SQLserver фирмы Sybase, Inc.;
• Oracle фирмы Oracle Corporation;
• Ingres фирмы Computer Associates International;
• Informix фирмы Informix Corporation.

• PostgreSQL организации PostgreSQL;
• microSQL фирмы Hughes Technologies Pty. Ltd.;
• mySQL фирмы T.C.X DataKonsult AB.

• Интерактивные клиенты, обеспечивающие пользователю-человеку возможность общения с SQL-сервером непосредственно с помощью языка SQL.
• ИПП-клиенты, обеспечивающие интерфейс прикладного программирования (ИПП) прикладным программам, использующим средства SQL-сервера. Такой ИПП может быть средством общения прикладной программы с SQL-сервером на языке SQL или набором стандартных функций доступа к реляционной SQL БД без формирования символьных строк запросов (например, стандартный интерфейс ODBC).
• WWW-клиенты, встраиваемые в World Wide Web-сервера и обеспечивающие доступ к информационным возможностям SQL-сервера пользователям сети Internet по протоколу HTTP (протоколу передачи гипертекстовых документов).

Основы синтаксиса языка SQL

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

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

• Прописными (большими) буквами (напрмер, SELECT, FROM, WHERE) набраны зарезервированные слова.
• Курсивом (например, имя_табл , сложн_условие ) набраны переменные (нетерминальные символы), подлежащие замене в реальном операторе конструкцией из терминальных символов (идентификаторов, знаков операций, имен функций и т.п.).
• В квадратные скобки (?[. ]?) заключается необязательная часть оператора, которую можно опустить при создании реального оператора (сами квадратные скобки в текст оператора не включаются).
• Вертикальная черта (?|?) означает возможность выбора (?или?) из двух или нескольких вариантов синтаксической конструкции (сама вертикальная черта в текст оператора не включается). Подчеркнутый вариант (например, в ?[ ALL | DISTINCT >?) является умолчательным.
• Последовательность символов ?, . обозначает возможность повторения произвольное количество раз (в том числе и нулевое) предшествующей запятой конструкции. Символ . включается в реальный оператор в качестве разделителя перед каждым повторением конструкции.

• от двойного минуса (?--?) до конца строки;
• от символа ?#? до конца строки;
• между последовательностями ?/*? и ?*/? (стиль комментариев языка СИ).

Учебная база данных

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

Метод конечных элементов (МКЭ) - универсальный метод решения краевых задач (систем дифференциальных уравнений в частных производных с краевыми условиями), к которым относится и задача анализа (моделирования) напряженно-деформированного состояния плоских механических объектов. Одним из основных этапов метода является этап разбиения ?тела? моделируемого объекта на элементарные участки, называемые конечными элементами (КЭ). Для плоских объектов чаще всего такие КЭ представляют собой треугольники. Пример покрытия объекта (типа рычага) сеткой конечных элементов представлен ниже. В дальнейшем МКЭ обеспечивает нахождение численных значений фазовых переменных , характеризующих состояние объекта (в нашем случае напряжений поля сил и деформаций), в вершинах таких треугольников, называемых узлами (nodes).

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

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

В задачах исследования поведения механических объектов под воздействием внешних факторов с каждым КЭ связан набор свойств материала, покрываемого КЭ, в состав которого входят, например, плотность (density) среды, модуль Юнга (elastic module), коэффициент Пуассона (Poisson's coefficient), прочность (strength) и др.

• произвольно направленная сила;
• произвольно направленный момент сил;
• ?заделка?, жестко фиксирующая положение узла сетки по линейным координатам и углу вращения;
• шарнир, позволяющий узлу свободно ?вращаться? относительно его фиксируемого положения по линейным координатам;
• ?каток?, дающий узлу возможность свободно перемещаться по оси x или y.

1. таблица ?nodes?, содержащая информацию об узлах КЭ-сетки (идентификатор, x- и y-координаты);
2. таблица ?elements?, содержащая информацию обо всех КЭ, составляющих сетку (номер КЭ, идентификаторы трех вершин, наименование материала);
3. таблица ?materials?, содержащая информацию о свойствах различных конструкционных материалов (наименование, плотность, модуль Юнга, коэффициент Пуассона, прочность);
4. таблица ?loadings?, содержащая информацию о граничных условиях решаемой задачи (вид условия, его ?направление?, номер узла приложения, числовое значение).

Типы данных языка SQL

• INT[( len )] - целое число длиной 4 байта, представляемое при выводе максимально len цифрами;
• SMALLINT[( len )] - целое число длиной 2 байта, представляемое при выводе максимально len цифрами;
• FLOAT[( len , dec )] - действительное число, представляемое при выводе максимально len символами с dec цифрами после десятичной точки;
• CHAR( size ) - строка символов фиксированной длины размером size символов;
• VARCHAR( size ) - строка символов переменной длины максимальным размером до size символов;
• BLOB (Binary Large OBject) - массив произвольных (двоичных) байтов (максимальный размер зависит от реализации, обычно это 65535 байт); этот тип данных может использоваться, например, для хранения изображений;
• DATE - астрономическая дата;
• TIME - астрономическое время.

• знак числа;
• десятичное число с точкой;
• символ ?е?;
• знак (?+? или ?-?) показателя степени;
• целое число, играющее роль показателя степени числа 10.

Отличие типов данных CHAR и VARCHAR заключается в том, что для хранения в таблице строк символов типа CHAR используется точно size байт (хотя содержание хранимых строк может быть значительно короче), в то время как для строк типа VARCHAR незанятые символами строк (?пустые?) байты в таблице не хранятся.
Подчеркнем, что величины len и dec (в отличие от size ) не влияют на размер хранения данных в таблице, а только форматируют вывод данных из таблицы.

Примечание. Тип данных BLOB поддерживается непосредственно не всеми СУБД, однако каждая из них предлагает его аналог (например, BINARY или IMAGE).

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

Манипулирование таблицами

Для создания, изменения и удаления таблиц в SQL БД используются операторы CREATE TABLE, ALTER TABLE и DROP TABLE.

Создание таблицы

Создание таблицы в БД реализуется оператором CREATE TABLE, имеющим следующий синтаксис

CREATE TABLE имя_табл (с_спецификация, . );

1. Описание столбца таблицы

имя_столбца тип_данных [NULL]

имя_столбца тип_данных NOT NULL [DEFAULT по_умолч] [PRIMARY KEY]

PRIMARY KEY имя_ключа (имя_столбца, . )

KEY имя_ключа (имя_столбца, . )

Примеры

Ниже приводятся примеры использования оператора CREATE TABLE для создания четырех таблиц учебной БД.

CREATE TABLE nodes (

 id SMALLINT NOT NULL PRIMARY KEY, # номер узла

 x FLOAT NOT NULL, # x-координата

 y FLOAT NOT NULL); # y-координата

CREATE TABLE elements (

 id SMALLINT NOT NULL PRIMARY KEY, # номер КЭ

 n1 SMALLINT NOT NULL, # номер первой вершины

 n2 SMALLINT NOT NULL, # номер второй вершины

 n3 SMALLINT NOT NULL, # номер третьей вершины

 props CHAR(12) NOT NULL DEFAULT 'steel');

Столбец props таблицы elements предназначен для хранения названия материала КЭ и не может содержать ?пустых? полей, его значением ?по умолчанию? является строка символов ?steel? (сталь).

CREATE TABLE materials (

 name CHAR(12) NOT NULL PRIMARY KEY, # название материала

 density FLOAT NOT NULL, # плотность

 elastics FLOAT NOT NULL, # модуль Юнга

 poisson FLOAT NOT NULL, # к-т Пуассона

 strength FLOAT NOT NULL); # прочность

CREATE TABLE loadings (

 type CHAR(1) NOT NULL, # тип граничного условия

 direction CHAR(1), # направление действия

 node SMALLINT NOT NULL, # номер узла приложения

 value FLOAT, # числовое значение

 KEY key_node (node) ); # вторичный ключ

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

Модификация таблицы

Модификация существующей таблицы в БД реализуется оператором ALTER TABLE, имеющим следующий синтаксис

ALTER TABLE имя_табл м_специкация [,м_спецификация . ]

1. Добавление нового столбца

ADD COLUMN с_спецификация

DROP PRIMARY KEY

ALTER COLUMN имя_столбца SET по_умолч

ALTER COLUMN имя_столбца DROP DEFAULT

ALTER TABLE materials ADD COLUMN capacity FLOAT NOT NULL, # теплоемкость ADD COLUMN conductivity FLOAT NOT NULL; # теплопроводность

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

Удаление одной или сразу нескольких таблиц из БД реализуется оператором DROP TABLE, имеющим следующий простой синтаксис

DROP TABLE имя_табл, . 

Подчеркнем, что оператор DROP TABLE удаляет не только все содержимое таблицы, но и само описание таблицы из БД. Если требуется удалить только содержимое таблицы, то необходимо использовать оператор DELETE FROM.

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

1. Добавление строки перечислением значений всех ее ячеек

INSERT INTO имя_табл VALUES (знач, . );

INSERT INTO имя_табл (имя_столбца, . ) VALUES (знач, . );

• ячейки, соответствующие столбцам со спецификацией NULL в операторе CREATE TABLE, будут пустыми;
• ячейки, соответствующие столбцам со спецификацией NOT NULL в операторе CREATE TABLE, заполняются значениями ?по умолчанию?.

INSERT INTO имя_табл [(имя_столбца, . )] SELECT . 

INSERT INTO nodes VALUES (25, 6.3, 1.8);

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

Пример
Добавление информации о новом КЭ в таблицу elements:

INSERT INTO elements (n1, n2, n3, id) VALUES (14, 25, 18, 46);

В результате в таблице elements появится новая строка, содержащая в поле props значение ?steel?, как умолчательное значение, определенное при создании таблицы.

Пример
Включение в таблицу materials сведений о новом материале:

INSERT INTO materials VALUES ( 'wood', 0.6, 2.0, 0.12, 50);

Пример
Добавление в таблицу граничных условий loadings информации об ориентированном горизонтально ?катке? в узле 2:

INSERT INTO loadings VALUES ( 'r', 'x', 2, NULL);

Выборка данных из таблиц

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

SELECT [ALL | DISTINCT] в_выражение, . 

 FROM имя_табл [син_табл], . 

 [WHERE сложн_условие]

 [GROUP BY полн_имя_столбца|ном_столбца, . ]

 [ORDER BY полн_имя_столбца|ном_столбца [ASC|DESC], . ]

 [HAVING сложн_условие];

• количество и смысл (семантика) столбцов определяется списком элементов в_выражение ;
• содержимое строк определяется содержимым исходных таблиц из списка FROM и критерием выборки, задаваемым сложн_условие .

[имя_табл|син_табл.]имя_столбца

Описание столбцов результирующей таблицы

SELECT * FROM materials;

+--------------+---------+----------+---------+----------+ | name | density | elastics | poisson | strength | +--------------+---------+----------+---------+----------+ | steel | 7.80 | 200.00 | 0.25 | 1000.00 | | aluminium | 2.70 | 65.00 | 0.34 | 600.00 | | concrete | 5.60 | 25.00 | 0.12 | 300.00 | | duraluminium | 2.80 | 70.00 | 0.31 | 700.00 | | titanium | 4.50 | 116.00 | 0.32 | 950.00 | | brass | 8.50 | 93.00 | 0.37 | 300.00 | +--------------+---------+----------+---------+----------+

2. Простым (и также часто используемым) случаем в_выражение является полное имя столбца одной из таблиц списка FROM.
Пример
Пусть необходимо определить идентификаторы всех узлов КЭ-сетки, к которым приложено какое-либо граничное условие, при этом необходимо знать тип приложенного условия. Эта задача может быть решена с помощью следующего оператора:

SELECT node, type FROM loadings;

+------+------+ | node | type | +------+------+ | 1 | r | | 2 | r | | 3 | r | | 14 | h | | 27 | f | | 27 | f | +------+------+

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

SELECT DISTINCT node, type FROM loadings;

+------+------+ | node | type | +------+------+ | 1 | r | | 2 | r | | 3 | r | | 14 | h | | 27 | f | +------+------+

3. В общем случае в_выражение может представлять собой сложное скобочное выражение над содержимым столбцов таблицы, использующее арифметические, строковые, логические операции и функции. Наиболее часто используемые функции описаны ниже в таблицах 1, 2, 3.
Пример
Используемая нами таблица свойств материалов materials содержит в своих столбцах density и elastics значащие разряды чисел, выражающих, соответственно, плотность и модуль Юнга каждого материала. Для получения реальных значений этих свойств в системе единиц измерения СИ (кг/м 3 и Па) необходимо домножить их на масштабные коэффициенты, что реализуется следующим оператором

SELECT name, density*1000, elastics*1e+9 FROM materials;

+--------------+--------------+-----------------+ | name | density*1000 | elastics*1e+9 | +--------------+--------------+-----------------+ | steel | 7800.00 | 200000000000.00 | | aluminium | 2700.00 | 65000000000.00 | | concrete | 5600.00 | 25000000000.00 | | duraluminium | 2800.00 | 70000000000.00 | | titanium | 4500.00 | 116000000000.00 | | brass | 8500.00 | 93000000000.00 | +--------------+--------------+-----------------+

Таблица 1. Арифметические функции

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

SELECT name, name LIKE '%alu%', density FROM materials;

+--------------+-------------------+---------+ | name | name LIKE '%alu%' | density | +--------------+-------------------+---------+ | steel | 0 | 7.80 | | aluminium | 1 | 2.70 | | concrete | 0 | 5.60 | | duraluminium | 1 | 2.80 | | titanium | 0 | 4.50 | | brass | 0 | 8.50 | +--------------+-------------------+---------+

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

SELECT id, n1, n2, n3, MAX(ABS(n1-n2),ABS(n1-n3),ABS(n2-n3))

 FROM elements;

+----+----+----+----+---------------------------------------+ | id | n1 | n2 | n3 | MAX(ABS(n1-n2),ABS(n1-n3),ABS(n2-n3)) | +----+----+----+----+---------------------------------------+ | 29 | 24 | 26 | 25 | 2 | | 30 | 24 | 25 | 23 | 2 | | 31 | 22 | 26 | 24 | 4 | | 1 | 2 | 3 | 5 | &nbs p; 3 | | 2 | 1 | 2 | 4 | &nbs p; 3 | | 3 | 2 | 5 | 4 | &nbs p; 3 | | 4 | 4 | 5 | 6 | &nbs p; 2 | | 25 | 24 | 23 | 21 | 3 | | 20 | 20 | 19 | 17 | 3 | | 21 | 21 | 19 | 20 | 2 | | 22 | 21 | 23 | 19 | 4 | | 12 | 12 | 14 | 13 | 2 | | 13 | 12 | 15 | 14 | 3 | | 14 | 13 | 14 | 18 | 5 | | 26 | 28 | 27 | 22 | 6 | | 7 | 7 | 8 | 9 | &nbs p; 2 | | 8 | 8 | 10 | 9 | 2 | | 9 | 9 | 10 | 11 | 2 | | 10 | 10 | 12 | 11 | 2 | | 11 | 11 | 12 | 13 | 2 | | 16 | 16 | 17 | 14 | 3 | | 17 | 18 | 17 | 14 | 4 | | 18 | 16 | 20 | 17 | 4 | | 19 | 19 | 18 | 17 | 2 | | 15 | 15 | 16 | 14 | 2 | | 27 | 27 | 29 | 26 | 3 | | 28 | 22 | 27 | 26 | 5 | | 5 | 5 | 7 | 6 | &nbs p; 2 | | 6 | 5 | 8 | 7 | &nbs p; 3 | | 23 | 20 | 22 | 21 | 2 | | 24 | 24 | 21 | 22 | 3 | +----+----+----+----+---------------------------------------+

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

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

SELECT MAX(elastics) FROM materials;

+---------------+ | MAX(elastics) | +---------------+ | 200.00 | +---------------+

Пример
Следующий оператор SELECT позволяет определить общее количество конечных элементов в КЭ-сетке из нашего примера.

SELECT COUNT(*) FROM elements;

+----------+ | COUNT(*) | +----------+ | 31 | +----------+

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

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

прост_условие

прост_условие AND сложн_условие

прост_условие OR сложн_условие

• Сравнение

полн_имя_столбца @ полн_имя_столбца_или_константа

полн_имя_столбца [NOT] LIKE образец

полн_имя_столбца IS [NOT] NULL

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

Пример
Для определения координат местоположения узла 11 может использоваться следующий оператор:

SELECT * FROM nodes WHERE >

+----+--------+--------+ | id | x | y | +----+--------+--------+ | 11 | -35.00 | -10.00 | +----+--------+--------+

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

 SELECT id FROM elements WHERE n1 = 20 OR n2 = 20 OR n3 = 20;

+----+ | id | +----+ | 20 | | 21 | | 18 | | 23 | +----+

Пример
Для определения идентификаторов узлов КЭ-сетки, расположенных в первом квадранте системы координат можно использовать следующий оператор

SELECT * FROM nodes WHERE x ?= 0 AND y ?= 0;

+----+-------+-------+ | id | x | y | +----+-------+-------+ | 14 | 0.00 | 0.00 | | 15 | 5.00 | 20.00 | | 16 | 20.00 | 8.00 | +----+-------+-------+

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

SELECT * FROM loadings WHERE value IS NOT NULL;

+------+-----------+------+--------+ | type | direction | node | value | +------+-----------+------+--------+ | f | y | 27 | -50.00 | | f | x | 27 | -10.00 | +------+-----------+------+--------+

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

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

Пример
Для вывода информации об узлах КЭ-сетки в убывающем порядке их (узлов) идентификаторов может быть использован следующий оператор:

SELECT * FROM nodes ORDER BY id DESC;

+----+--------+--------+ | id | x | y | +----+--------+--------+ | 29 | 83.00 | -9.00 | | 28 | 65.00 | -5.00 | | 27 | 75.00 | -7.00 | | 26 | 80.00 | -20.00 | | 25 | 75.00 | -35.00 | | 24 | 65.00 | -25.00 | | 23 | 60.00 | -39.00 | | 22 | 60.00 | -15.00 | | 21 | 50.00 | -25.00 | | 20 | 40.00 | -3.00 | | 19 | 30.00 | -27.00 | | 18 | 10.00 | -20.00 | | 17 | 20.00 | -10.00 | | 16 | 20.00 | 8.00 | | 15 | 5.00 | 20.00 | | 14 | 0.00 | 0.00 | | 13 | -15.00 | -14.00 | | 12 | -15.00 | 15.00 | | 11 | -35.00 | -10.00 | | 10 | -40.00 | 15.00 | | 9 | -55.00 | -6.00 | | 8 | -65.00 | 15.00 | | 7 | -75.00 | -3.00 | | 6 | -85.00 | -1.00 | | 5 | -80.00 | 15.00 | | 4 | -95.00 | 10.00 | | 3 | -80.00 | 20.00 | | 2 | -87.50 | 20.00 | | 1 | -95.00 | 20.00 | +----+--------+--------+

• в первую очередь по идентификаторам узлов, являющихся первой вершиной конечного элемента;
• во вторую очередь по идентификаторам узлов, являющихся второй вершиной конечного элемента;

SELECT * FROM elements ORDER BY n1, n2;

+----+----+----+----+-------+ | id | n1 | n2 | n3 | props | +----+----+----+----+-------+ | 2 | 1 | 2 | 4 | steel | | 1 | 2 | 3 | 5 | steel | | 3 | 2 | 5 | 4 | steel | | 4 | 4 | 5 | 6 | steel | | 5 | 5 | 7 | 6 | steel | | 6 | 5 | 8 | 7 | steel | | 7 | 7 | 8 | 9 | steel | | 8 | 8 | 10 | 9 | steel | | 9 | 9 | 10 | 11 | steel | | 10 | 10 | 12 | 11 | steel | | 11 | 11 | 12 | 13 | steel | | 12 | 12 | 14 | 13 | steel | | 13 | 12 | 15 | 14 | steel | | 14 | 13 | 14 | 18 | steel | | 15 | 15 | 16 | 14 | steel | | 16 | 16 | 17 | 14 | steel | | 18 | 16 | 20 | 17 | steel | | 17 | 18 | 17 | 14 | steel | | 19 | 19 | 18 | 17 | steel | | 20 | 20 | 19 | 17 | steel | | 23 | 20 | 22 | 21 | steel | | 21 | 21 | 19 | 20 | steel | | 22 | 21 | 23 | 19 | steel | | 31 | 22 | 26 | 24 | steel | | 28 | 22 | 27 | 26 | steel | | 24 | 24 | 21 | 22 | steel | | 25 | 24 | 23 | 21 | steel | | 30 | 24 | 25 | 23 | steel | | 29 | 24 | 26 | 25 | steel | | 27 | 27 | 29 | 26 | steel | | 26 | 28 | 27 | 22 | steel | +----+----+----+----+-------+

Оператор SELECT выводит значения агрегативных функций для самых ?малых? подгрупп.

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

SELECT type, COUNT(*) FROM loadings GROUP BY type;

+------+----------+ | type | COUNT(*) | +------+----------+ | f | 2 | | h | 1 | | r | 3 | +------+----------+

Примечание . Конструкция HAVING сложн_условие , как необязательная составная часть предложения GROUP BY, позволяет определять дополнительный (к WHERE сложн_условие ) критерий выборки строк в группы. Этот дополнительный критерий применяется в режиме постпроцессорной обработки к таблице, полученной в результате использования критерия из конструкции WHERE.

Выборка из нескольких таблиц

1. построение промежуточной таблицы, представляющей собой декартово произведение таблиц из списка FROM (т.е. таблицы, строки которой представляют собой все возможные сочетания строк исходных таблиц);
2. копирование в результирующую таблицу всех строк промежуточной, отвечающих критерию из WHERE сложн_условие (если таковой определен).

SELECT id, elastics FROM elements, materials WHERE AND props = name;

+----+----------+ | id | elastics | +----+----------+ | 25 | 200.00 | +----+----------+

Примечание. Обратите внимание, что в данном примере нигде в операторе SELECT не потребовалось использовать полные имена столбцов различных таблиц. Объясняется это тем, что имена столбцов таблиц elements и materials различны, и поэтому неоднозначностей в именовании быть не может.
Примечание. Хотя концептуальная модель обработки оператора SELECT со списком FROM из двух и более таблиц подразумевает построение декартового произведения этих табллиц, в реальности этого не происходит в силу ?ограниченности? синтаксиса сложн_условие из конструкции WHERE. Так, в нашем последнем примере запрос на выборку осуществлялся в 2 ?коротких? этапа: 1) из таблицы elements (с использованием первичного ключа) прямым доступом извлекается строка с 2) из таблицы materials (опять с использованием первичного ключа) прямым доступом извлекается информация о материале ?steel? (сталь). Очевидно, что такой ?оптимизированный? подход несравненно более эффективен по сравнению с каноническим (через декартово произведение).
Пример
Для вывода координат трех вершин каждого конечного элемента в КЭ-сетке в одной таблице можно использовать следующий оператор

SELECT e.id, node1.x, node1.y, node2.x, node2.y, node3.x, node3.y FROM elements e, nodes node1, nodes node2, nodes node3 WHERE e.n1 = node1.id AND e.n2 = node2.id AND e.n3 = node3.id;

+----+--------+--------+--------+--------+--------+--------+ | id | x | y | x | y | x | y | +----+--------+--------+--------+--------+--------+--------+ | 29 | 65.00 | -25.00 | 80.00 | -20.00 | 75.00 | -35.00 | | 30 | 65.00 | -25.00 | 75.00 | -35.00 | 60.00 | -39.00 | | 31 | 60.00 | -15.00 | 80.00 | -20.00 | 65.00 | -25.00 | | 1 | -87.50 | 20.00 | -80.00 | 20.00 | -80.00 | 15.00 | | 2 | -95.00 | 20.00 | -87.50 | 20.00 | -95.00 | 10.00 | | 3 | -87.50 | 20.00 | -80.00 | 15.00 | -95.00 | 10.00 | | 4 | -95.00 | 10.00 | -80.00 | 15.00 | -85.00 | -1.00 | | 25 | 65.00 | -25.00 | 60.00 | -39.00 | 50.00 | -25.00 | | 20 | 40.00 | -3.00 | 30.00 | -27.00 | 20.00 | -10.00 | | 21 | 50.00 | -25.00 | 30.00 | -27.00 | 40.00 | -3.00 | | 22 | 50.00 | -25.00 | 60.00 | -39.00 | 30.00 | -27.00 | | 12 | -15.00 | 15.00 | 0.00 | 0.00 | -15.00 | -14.00 | | 13 | -15.00 | 15.00 | 5.00 | 20.00 | 0.00 | 0.00 | | 14 | -15.00 | -14.00 | 0.00 | 0.00 | 10.00 | -20.00 | | 26 | 65.00 | -5.00 | 75.00 | -7.00 | 60.00 | -15.00 | | 7 | -75.00 | -3.00 | -65.00 | 15.00 | -55.00 | -6.00 | | 8 | -65.00 | 15.00 | -40.00 | 15.00 | -55.00 | -6.00 | | 9 | -55.00 | -6.00 | -40.00 | 15.00 | -35.00 | -10.00 | | 10 | -40.00 | 15.00 | -15.00 | 15.00 | -35.00 | -10.00 | | 11 | -35.00 | -10.00 | -15.00 | 15.00 | -15.00 | -14.00 | | 16 | 20.00 | 8.00 | 20.00 | -10.00 | 0.00 | 0.00 | | 17 | 10.00 | -20.00 | 20.00 | -10.00 | 0.00 | 0.00 | | 18 | 20.00 | 8.00 | 40.00 | -3.00 | 20.00 | -10.00 | | 19 | 30.00 | -27.00 | 10.00 | -20.00 | 20.00 | -10.00 | | 15 | 5.00 | 20.00 | 20.00 | 8.00 | 0.00 | 0.00 | | 27 | 75.00 | -7.00 | 83.00 | -9.00 | 80.00 | -20.00 | | 28 | 60.00 | -15.00 | 75.00 | -7.00 | 80.00 | -20.00 | | 5 | -80.00 | 15.00 | -75.00 | -3.00 | -85.00 | -1.00 | | 6 | -80.00 | 15.00 | -65.00 | 15.00 | -75.00 | -3.00 | | 23 | 40.00 | -3.00 | 60.00 | -15.00 | 50.00 | -25.00 | | 24 | 65.00 | -25.00 | 50.00 | -25.00 | 60.00 | -15.00 | +----+--------+--------+--------+--------+--------+--------+

Примечание. Обратите внимание, что необходимая для выполнения данного запроса промежуточная таблица в виде декартового произведения (если бы она реально строилась) имеет размер в 31*29*29*29=756059 строк (31 строка в таблице elements и 29 строк в таблице nodes).

Манипулирование строками таблиц

Для удаления и изменения строк таблиц SQL БД применяются операторы DELETE и UPDATE.

Удаление строк

Удаление строк таблицы реализуется оператором DELETE FROM, имеющим следующий синтаксис

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

где сложн_условие имеет описанный выше синтаксис. В результате выполнения оператора из таблицы удаляются все строки, удовлетворяющие критерию сложн_условие . Если в операторе DELETE FROM конструкция WHERE опущена, то удаляются все строки таблицы.

Модификация строк

Изменение содержимого строк таблицы реализуется оператором UPDATE, имеющим следующий синтаксис

UPDATE имя_табл SET имя_столбца=выражение, . 

[WHERE сложн_условие]

где выражение - выражение (в простейшем случае - константа), согласующееся по результату с типом данных столбца. В выражение допустимо использование значений ячеек любых столбцов таблицы, рассмотренных ранее операций и функций (но не агрегативных), а также прежнего содержимого модифицуруемой ячейки. Обновлению подлежат столбцы строк, отвечающих критерию сложн_условие . Если конструкция WHERE в операторе отсутствует, то обновляются все строки таблицы.
Пример
Для изменения наименования материала, из которого выполнена механическая конструкция, для всех элементов КЭ-сетки можно использовать следующий оператор

UPDATE elements SET props='brass'; SELECT * FROM elements;

+----+----+----+----+-------+ | id | n1 | n2 | n3 | props | +----+----+----+----+-------+ | 29 | 24 | 26 | 25 | brass | | 30 | 24 | 25 | 23 | brass | | 31 | 22 | 26 | 24 | brass | | 1 | 2 | 3 | 5 | brass | | 2 | 1 | 2 | 4 | brass | . . . | 28 | 22 | 27 | 26 | brass | | 5 | 5 | 7 | 6 | brass | | 6 | 5 | 8 | 7 | brass | | 23 | 20 | 22 | 21 | brass | | 24 | 24 | 21 | 22 | brass | +----+----+----+----+-------+

Пример
В нашей КЭ-сетке элемент 22 имеет ?неправильную? форму. Ставится задача заменить его двумя новыми конечными элементами, имеющими форму, более близкую к равносторонней. Эта задача может быть решена следующей последовательностью операторов

DELETE FROM elements WHERE INTO nodes VALUES (30, 45.0, -33.5); INSERT INTO elements VALUES (22, 21, 30, 19); INSERT INTO elements VALUES (32, 21, 23, 30);

Пример
Для решения предыдущей задачи можно также использовать и другой набор операторов

INSERT INTO nodes VALUES (30, 45.0, -33.5); UPDATE elements SET n2 = 30 WHERE INTO elements VALUES (32, 21, 23, 30);

Литература

1. Дж. Мартин. Организация баз данных в вычислительных системах. - М.;Мир,1980. - 662с.
2. С.Д. Кузнецов. Стандарты языка реляционных баз данных SQL: краткий обзор. //СУБД, 1996, N2, сс. 6-36.
3. С.Д. Некузнецов
4. Зенкевич О., Морган К. Конечные элементы и аппроксимации. - М.:Мир, 1979. - 318с.

Упражнения

1. Определить наилучший материал по показателю ?прочность/плотность?.
2. Получить таблицу, содержащую значения наибольших разностей идентификаторов узлов - вершин для каждого конечного элемента.Таблица должна быть упорядочена по убыванию значений разностей.
3. Определить максимальное значение в таблице из предыдущего задания.
4. Определить протяженность механического объекта вдоль оси x.
5. Определить расстояние каждого узла КЭ-сетки до начала системы координат.
6. Определить расстояние каждого узла КЭ-сетки до узла, в котором приложено граничное условие в виде горизонтальной силы (подчеркнем, не до узла с заданным идентификатором, а до узла с заданным типом граничного условия).
7. Найти наибольшее расстояние между узлами КЭ-сетки.
8. Получить таблицу длин сторон всех элементов КЭ-сетки в виде ?идентификатор элемента - длина стороны 1 - длина стороны 2 - длина стороны 3?.
9. Получить таблицу расстояний между узлами КЭ-сетки, соединенных сторонами конечных элементов, в виде ?идентификатор узла 1 - идентификатор узла 2 - длина?.
10. Получить таблицу площадей всех конечных элементов. Напомним, для вычисления площади треугольника можно использовать формулу

s = SQRT(p*(p-a)*(p-b)*(p-c))

SELECT [ALL|DISTINCT] в_выражение [AS син_столбца], . 

 FROM . и т.д.

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть пятая

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

Данная часть, как я и говорил, будет больше обзорная. Здесь я буду описывать только те основные формы операторов модификации данных, которыми я сам регулярно пользуюсь. Поэтому на полноту изложения рассчитывать не стоит, здесь будут показан только необходимый минимум, который новички могут использовать как направление для более глубокого изучения. За более подробной информацией по каждому оператору обращайтесь в MSDN. Хотя кому-то возможно и в таком объеме информации будет вполне достаточно.

Т.к. прямая модификация информации в РБД требует от человека большой ответственности, а также потому что пользователи обычно модифицируют информацию БД посредством разных АРМ, и не имеют полного доступа к БД, то данная часть больше посвящается начинающим ИТ-специалистам, и я буду здесь очень краток. Но конечно, если вы смогли освоить оператор SELECT, то думаю, и операторы модификации вам будут под силу, т.к. после оператора SELECT здесь нет ничего сверхсложного, и по большей части должно восприниматься на интуитивном уровне. Но порой сложность представляют не сами операторы модификации, а то что они должны выполняться группами, в рамках одной транзакции, т.е. когда дополнительно нужно учитывать целостность данных. В любом случае можете почитать и попытаться проделать примеры в ознакомительных целях, к тому же в итоге вы сможете получить более детальную базу, на которой можно будет отработать те или иные конструкции оператора SELECT.

Проведем изменения в структуре нашей БД

Давайте проведем небольшое обновление структуры и данных таблицы Employees:

-- информацию по ЗП решено хранить до 2-х знаков после запятой ALTER TABLE Employees ALTER COLUMN Salary numeric(20,2) -- информацию по процентам решено хранить только в целых числах ALTER TABLE Employees ALTER COLUMN BonusPercent tinyint 

А также для демонстрационных целей расширим схему нашей БД, а за одно повторим DDL. Назначения таблиц и полей указаны в комментариях:

-- история изменений ЗП у сотрудников CREATE TABLE EmployeesSalaryHistory( EmployeeID int NOT NULL, -- ссылка на ID сотрудника DateFrom date NOT NULL, -- с какой даты DateTo date, -- по какую дату. Содержит NULL если это последняя установленная ЗП. Salary numeric(20,2) NOT NULL, -- сумма ЗП за этот период CONSTRAINT PK_EmployeesSalaryHistory PRIMARY KEY(EmployeeID,DateFrom), CONSTRAINT FK_EmployeesSalaryHistory_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID) ) GO -- таблица для хранения истории начислений по ЗП CREATE TABLE EmployeesSalary( EmployeeID int NOT NULL, SalaryDate date NOT NULL, -- дата начисления SalaryAmount numeric(20,2) NOT NULL, -- сумма начисления Note nvarchar(50), -- примечание -- здесь сумма ЗП может фиксироваться по человеку 1 раз в день CONSTRAINT PK_EmployeesSalary PRIMARY KEY(EmployeeID,SalaryDate), -- связь с таблицей Employees CONSTRAINT FK_EmployeesSalary_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID) ) GO -- справочник по типам бонусов CREATE TABLE BonusTypes( ID int IDENTITY(1,1) NOT NULL, Name nvarchar(30) NOT NULL, CONSTRAINT PK_BonusTypes PRIMARY KEY(ID) ) GO -- таблица для хранения истории начислений бонусов CREATE TABLE EmployeesBonus( EmployeeID int NOT NULL, BonusDate date NOT NULL, -- дата начисления BonusAmount numeric(20,2) NOT NULL, -- сумма начисления BonusTypeID int NOT NULL, BonusPercent tinyint, Note nvarchar(50), -- примечание -- бонус одного типа может фиксироваться по человеку 1 раз в день CONSTRAINT PK_EmployeesBonus PRIMARY KEY(EmployeeID,BonusDate,BonusTypeID), -- связь с таблицей Employees и BonusTypes CONSTRAINT FK_EmployeesBonus_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID), CONSTRAINT FK_EmployeesBonus_BonusTypeID FOREIGN KEY(BonusTypeID) REFERENCES BonusTypes(ID) ) GO 

Вот такой полигон мы должны были получить в итоге:

Кстати, потом этот полигон (когда он будет наполнен данными) вы и можете использовать для того чтобы опробовать на нем разнообразные запросы – здесь можно опробовать и разнообразные JOIN-соединения, и UNION-объединения, и группировки с агрегированием данных.

INSERT – вставка новых данных

1. INSERT INTO таблица(перечень_полей) VALUES(перечень_значений) – вставка в таблицу новой строки значения полей которой формируются из перечисленных значений
2. INSERT INTO таблица(перечень_полей) SELECT перечень_значений FROM … – вставка в таблицу новых строк, значения которых формируются из значений строк возвращенных запросом.

В диалекте MS SQL слово INTO можно отпускать, что мне очень нравится и я этим всегда пользуюсь.

К тому же стоит отметить, что первая форма в диалекте MS SQL с версии 2008, позволяет вставить в таблицу сразу несколько строк:

INSERT таблица(перечень_полей) VALUES (перечень_значений1), (перечень_значений2), … (перечень_значенийN) 

INSERT – форма 1. Переходим сразу к практике

Наполним таблицу EmployeesSalaryHistory предоставленными нам данными:

INSERT EmployeesSalaryHistory(EmployeeID,DateFrom,DateTo,Salary) VALUES -- Иванов И.И. (1000,'20131101','20140531',4000), (1000,'20140601','20141230',4500), (1000,'20150101',NULL,5000), -- Петров П.П. (1001,'20131101','20140630',1300), (1001,'20140701','20140930',1400), (1001,'20141001',NULL,1500), -- Сидоров С.С. (1002,'20140101',NULL,2500), -- Андреев А.А. (1003,'20140601',NULL,2000), -- Николаев Н.Н. (1004,'20140701','20150131',1400), (1004,'20150201','20150131',1500), -- Александров А.А. (1005,'20150101',NULL,2000) 

Таким образом мы вставили в таблицу EmployeesSalaryHistory 11 новых записей.

SELECT * FROM EmployeesSalaryHistory 

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

INSERT EmployeesSalaryHistory VALUES -- Иванов И.И. (1000,'20131101','20140531',4000), (1000,'20140601','20141230',4500), (1000,'20150101',NULL,5000), … 

Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.

• Порядок перечисления полей не имеет значения, вы можете написать и (EmployeeID,DateFrom,DateTo,Salary) и (DateFrom,DateTo, EmployeeID,Salary). Здесь важно только то, чтобы он совпадал с порядком значений, которые вы перечисляете в скобках после ключевого слова VALUES.
• Так же важно, чтобы при вставке были заданы значения для всех обязательных полей, которые помечены в таблице как NOT NULL.
• Можно не указывать поля у которых была указана опция IDENTITY или же поля у которых было задано значение по умолчанию при помощи DEFAULT, т.к. в качестве их значения подставится либо значение из счетчика, либо значение, указанное по умолчанию. Такие вставки мы уже делали в первой части.
• В случаях, когда значение поля со счетчиком нужно задать явно используйте опцию IDENTITY_INSERT.

В предыдущих частях мы периодически использовали опцию IDENTITY_INSERT. Давайте и здесь воспользуемся данной опцией для создания строк в таблице BonusTypes, у которой поле ID указано с опцией IDENTITY:

-- даем разрешение на добавление/изменение IDENTITY значения SET IDENTITY_INSERT BonusTypes ON INSERT BonusTypes(ID,Name)VALUES (1,N'Ежемесячный'), (2,N'Годовой'), (3,N'Индивидуальный') -- запрещаем добавление/изменение IDENTITY значения SET IDENTITY_INSERT BonusTypes OFF 

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

-- Иванов И.И. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1000,'20131130',4000,NULL), (1000,'20131231',4000,NULL), (1000,'20140115',2000,N'Аванс'), (1000,'20140131',2000,NULL), (1000,'20140228',4000,NULL), (1000,'20140331',4000,NULL), (1000,'20140430',4000,NULL), (1000,'20140531',4000,NULL), (1000,'20140630',6500,N'ЗП + Аванс 2500 за 2014.07'), (1000,'20140731',2000,NULL), (1000,'20140831',4500,NULL), (1000,'20140930',4500,NULL), (1000,'20141031',4500,NULL), (1000,'20141130',4500,NULL), (1000,'20141230',4500,NULL), (1000,'20150131',5000,NULL), (1000,'20150228',5000,NULL), (1000,'20150331',5000,NULL) 

-- Петров П.П. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1001,'20131130',2600,N'ЗП + ЗП за 2013.12'), (1001,'20140228',2600,N'За 2 месяца 2014.01, 2014.02'), (1001,'20140331',1300,NULL), (1001,'20140430',1300,NULL), (1001,'20140510',300,N'Аванс'), (1001,'20140520',500,N'Аванс'), (1001,'20140531',500,NULL), (1001,'20140630',1300,NULL), (1001,'20140731',1400,NULL), (1001,'20140831',1400,NULL), (1001,'20140930',1400,NULL), (1001,'20141031',1500,NULL), (1001,'20141130',1500,NULL), (1001,'20141230',3000,N'ЗП + ЗП за 2015.01'), (1001,'20150228',1500,NULL), (1001,'20150331',1500,NULL) 

-- Сидоров С.С. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1002,'20140131',2500,NULL), (1002,'20140228',2500,NULL), (1002,'20140331',2500,NULL), (1002,'20140430',2500,NULL), (1002,'20140531',2500,NULL), (1002,'20140630',2500,NULL), (1002,'20140731',2500,NULL), (1002,'20140831',2500,NULL), (1002,'20140930',2500,NULL), (1002,'20141031',2500,NULL), (1002,'20141130',2500,NULL), (1002,'20141230',2500,NULL), (1002,'20150131',2500,NULL), (1002,'20150228',2500,NULL), (1002,'20150331',2500,NULL) 

-- Андреев А.А. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1003,'20140630',2000,NULL), (1003,'20140731',2000,NULL), (1003,'20140831',2000,NULL), (1003,'20140930',2000,NULL), (1003,'20141031',2000,NULL), (1003,'20141130',2000,NULL), (1003,'20141230',2000,NULL), (1003,'20150131',2000,NULL), (1003,'20150228',2000,NULL), (1003,'20150331',2000,NULL) 

-- Николаев Н.Н. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1004,'20140731',1400,NULL), (1004,'20140831',1400,NULL), (1004,'20140930',1400,NULL), (1004,'20141031',1400,NULL), (1004,'20141130',1400,NULL), (1004,'20141212',400,N'Аванс'), (1004,'20141230',1400,NULL), (1004,'20150131',1400,NULL), (1004,'20150228',1500,NULL), (1004,'20150331',1500,NULL) 

-- Александров А.А. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1005,'20150131',2000,NULL), (1005,'20150228',2000,NULL), (1005,'20150331',2000,NULL) 

INSERT – форма 2

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

Для демонстрации наполним таблицу с начислениями бонусов одним большим запросом:

INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent) -- расчет ежемесячных бонусов SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent,1 BonusTypeID,emp.BonusPercent FROM EmployeesSalaryHistory hist JOIN ( VALUES -- весь период работы компании - последние дни месяцев ('20131130'), ('20131231'), ('20140131'), ('20140228'), ('20140331'), ('20140430'), ('20140531'), ('20140630'), ('20140731'), ('20140831'), ('20140930'), ('20141031'), ('20141130'), ('20141230'), ('20150131'), ('20150228'), ('20150331') ) bdate(BonusDate) ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0 AND NOT EXISTS( -- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период SELECT * FROM ( VALUES (1001,'20140115'), (1001,'20140430'), (1001,'20141031'), (1001,'20141130'), (1001,'20150228') ) exclude(EmployeeID,BonusDate) WHERE exclude.EmployeeID=emp.ID AND exclude.BonusDate=bdate.BonusDate ) UNION ALL -- годовой бонус за 2014 год - всем кто проработал больше полугода SELECT hist.EmployeeID, '20141231' BonusDate, hist.Salary/100* CASE DepartmentID WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам ELSE 5 -- всем остальным по 5% END BonusAmount, 2 BonusTypeID, CASE DepartmentID WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам ELSE 5 -- всем остальным по 5% END BonusPercent FROM EmployeesSalaryHistory hist JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') AND emp.HireDate 

SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent FROM ( VALUES (1001,'20140930',300), (1002,'20140331',500), (1002,'20140630',500), (1002,'20140930',500), (1002,'20141230',500), (1002,'20150331',500), (1004,'20140831',200) ) indiv(EmployeeID,BonusDate,BonusAmount) 

SELECT 1001 EmployeeID,'20140930' BonusDate,300 BonusAmount,3 BonusTypeID,NULL BonusPercent UNION ALL SELECT 1002,'20140331',500,3,NULL UNION ALL SELECT 1002,'20140630',500,3,NULL UNION ALL SELECT 1002,'20140930',500,3,NULL UNION ALL SELECT 1002,'20141230',500,3,NULL UNION ALL SELECT 1002,'20150331',500,3,NULL UNION ALL SELECT 1004,'20140831',200,3,NULL 

TRUNCATE TABLE EmployeesBonus 

WITH cteBonusType1 AS( -- расчет ежемесячных бонусов SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent BonusAmount,1 BonusTypeID,emp.BonusPercent FROM EmployeesSalaryHistory hist JOIN ( VALUES -- весь период работы компании - последние дни месяцев ('20131130'), ('20131231'), ('20140131'), ('20140228'), ('20140331'), ('20140430'), ('20140531'), ('20140630'), ('20140731'), ('20140831'), ('20140930'), ('20141031'), ('20141130'), ('20141230'), ('20150131'), ('20150228'), ('20150331') ) bdate(BonusDate) ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0 AND NOT EXISTS( -- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период SELECT * FROM ( VALUES (1001,'20140115'), (1001,'20140430'), (1001,'20141031'), (1001,'20141130'), (1001,'20150228') ) exclude(EmployeeID,BonusDate) WHERE exclude.EmployeeID=emp.ID AND exclude.BonusDate=bdate.BonusDate ) ), cteBonusType2 AS( -- годовой бонус за 2014 год - всем кто проработал больше полугода SELECT hist.EmployeeID, '20141231' BonusDate, hist.Salary/100* CASE DepartmentID WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам ELSE 5 -- всем остальным по 5% END BonusAmount, 2 BonusTypeID, CASE DepartmentID WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам ELSE 5 -- всем остальным по 5% END BonusPercent FROM EmployeesSalaryHistory hist JOIN Employees emp ON hist.EmployeeID=emp.ID WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231') AND emp.HireDate 

-- приведем даты приема в порядок UPDATE Employees SET HireDate='20131101' WHERE Employees SET HireDate='20131101' WHERE Employees SET HireDate='20140101' WHERE Employees SET HireDate='20140601' WHERE Employees SET HireDate='20140701' WHERE а здесь еще почистим поле FirstName UPDATE Employees SET HireDate='20150101',FirstName=NULL WHERE форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e 

UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department 

SELECT e.ID, e.PositionID,e.DepartmentID, -- старые значения e.Position,e.Department, p.ID,d.ID, -- новые значения p.Name,d.Name FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department 

UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department 

ALTER TABLE Employees ADD Position nvarchar(30),Department nvarchar(30) 

SELECT e.ID, e.Position, p.Name NewPosition, e.Department, d.Name NewDepartment FROM Employees e LEFT JOIN Positions p ON p.ID=e.PositionID LEFT JOIN Departments d ON d.ID=e.DepartmentID 

UPDATE e SET e.Position=p.Name, e.Department=d.Name FROM Employees e LEFT JOIN Positions p ON p.ID=e.PositionID LEFT JOIN Departments d ON d.ID=e.DepartmentID 

SELECT * FROM Employees 

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e 

UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department 

SELECT e.ID, e.PositionID,e.DepartmentID, -- старые значения e.Position,e.Department, p.ID,d.ID, -- новые значения p.Name,d.Name FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department 

UPDATE e SET PositionID=p.ID, DepartmentID=d.ID FROM Employees e LEFT JOIN Positions p ON p.Name=e.Position LEFT JOIN Departments d ON d.Name=e.Department WHERE d.ID=3 -- обновить только данные по ИТ-отделу 

UPDATE e SET HireDate='20131101', MiddleName=N'Иванович' FROM (SELECT MiddleName,HireDate FROM Employees WHERE e 

WITH cteEmp AS( SELECT MiddleName,HireDate FROM Employees WHERE cteEmp SET HireDate='20131101', MiddleName=N'Иванович' 

-- удалим неиспользуемые должности Логист и Кладовщик DELETE Positions WHERE ID IN(6,7) 

SELECT pos.* FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN Positions pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees 

DELETE pos -- удалить из этой таблицы FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN Positions pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees 

INSERT Positions(Name) VALUES('Test 1'),('Test 2') 

DELETE pos -- удалить из этой таблицы FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN ( SELECT ID FROM Positions WHERE ID>4 -- отбираем должности по условию ) pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees 

INSERT Positions(Name) VALUES('Test 1'),('Test 2') 

WITH ctePositionc AS( SELECT ID FROM Positions WHERE ID>4 -- отбираем должности по условию ) DELETE pos -- удалить из этой таблицы FROM ( SELECT DISTINCT PositionID FROM Employees ) emp RIGHT JOIN ctePositionc pos ON pos.ID=emp.PositionID WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees 

INSERT Positions(Name) VALUES('Test 1'),('Test 2') 

DELETE emp FROM ( SELECT TOP 2 * -- 2. берем только 2 верхние записи FROM Positions ORDER BY ID DESC -- 1. сортируем по убыванию ) emp 

SELECT bonus.EmployeeID, bonus.BonusDate, bonus.BonusAmount-bonus.BonusAmount BonusAmount, -- обнулим значения bonus.BonusTypeID, bonus.BonusPercent, bonus.Note INTO EmployeesBonusTarget -- сохраним результат в новой таблице EmployeesBonusTarget FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3 

SELECT ID, CONCAT(LastName,' ',FirstName,' ',MiddleName) FullName, -- используем псевдоним FullName Salary, BonusPercent, Salary/100*ISNULL(BonusPercent,0) Bonus -- используем псевдоним Bonus INTO #EmployeesBonus -- сохранить результат во временной таблице FROM Employees 

SELECT … FROM #EmployeesBonus b JOIN … 

SELECT * INTO EmployeesBackup FROM Employees 

CREATE DATABASE TestTemp GO SELECT * INTO TestTemp.dbo.EmployeesBackup -- используем префикс ИмяБаза.Схема. FROM Employees 

CREATE TABLE EMPLOYEES_BACK -- сохранить результат в новой таблице с именем EMPLOYEES_BACK AS SELECT * FROM EMPLOYEES 

INSERT EmployeesBonusTarget(EmployeeID,BonusDate,BonusAmount,BonusTypeID,Note)VALUES (9999,'20150101',9999.99,0,N'это мусор'), (9999,'20150201',9999.99,0,N'это мусор'), (9999,'20150301',9999.99,0,N'это мусор'), (9999,'20150401',9999.99,0,N'это мусор'), (9999,'20150501',9999.99,0,N'это мусор'), (9999,'20150601',9999.99,0,N'это мусор') 

MERGE EmployeesBonusTarget trg -- таблица приемник USING EmployeesBonus src -- таблица источник ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния -- 1. Строка есть в trg но нет сопоставления со строкой из src WHEN NOT MATCHED BY SOURCE THEN DELETE -- 2. Есть сопоставление строки trg со строкой из источника src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3. Строка не найдена в trg, но есть в src WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note); 

MERGE EmployeesBonusTarget trg -- таблица приемник USING ( SELECT bonus.* FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3 ) src -- источник ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния -- 2. Есть сопоставление строки trg со строкой из источника src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3. Строка не найдена в trg, но есть в src WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note); 

WITH cteBonus AS( SELECT bonus.* FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID WHERE emp.DepartmentID=3 ) MERGE EmployeesBonusTarget trg -- таблица приемник USING cteBonus src -- источник ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния -- 2. Есть сопоставление строки trg со строкой из источника src WHEN MATCHED THEN UPDATE SET trg.BonusAmount=src.BonusAmount, trg.BonusPercent=src.BonusPercent, trg.Note=src.Note -- 3. Строка не найдена в trg, но есть в src WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note) VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note); 

INSERT Positions(Name) OUTPUT inserted.* VALUES (N'Test 1'), (N'Test 2'), (N'Test 3') 

INSERT Positions(Name) OUTPUT inserted.ID,inserted.Name,'I' VALUES (N'Test 4'), (N'Test 5'), (N'Test 6') 

DELETE Positions OUTPUT deleted.ID,deleted.Name,'D' WHERE Name LIKE N'Test%' 

UPDATE Employees SET LastName=N'Александров', FirstName=N'Александр' OUTPUT deleted.ID, deleted.LastName [Старая Фамилия], deleted.FirstName [Старое Имя], inserted.ID, inserted.LastName [Новая Фамилия], inserted.FirstName [Новое Имя] WHERE > 
 
ID
 
Старая Фамилия
 
Старое Имя
 
ID
 
Новая Фамилия
 
Новое Имя
 
 
1005
 
NULL
 
NULL
 
1005
 
Александров
 
Александр
 
 

SELECT CAST(ID AS int) ID, -- чтобы поле создалось без опции IDENTITY Name+'-old' Name -- изменим название INTO PositionsTarget FROM Positions WHERE -- вставим только одну должность 

INSERT PositionsTarget(ID,Name)VALUES (100,N'Qwert'), (101,N'Asdf') 

MERGE PositionsTarget trg -- таблица приемник USING Positions src -- таблица источник ON trg.ID=src.ID -- условие слияния -- 1. Строка есть в trg но нет сопоставления со строкой из src WHEN NOT MATCHED BY SOURCE THEN DELETE -- 2. Есть сопоставление строки trg со строкой из источника src WHEN MATCHED THEN UPDATE SET trg.Name=src.Name -- 3. Строка не найдена в trg, но есть в src WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET INSERT(ID,Name) VALUES(src.ID,src.Name) OUTPUT deleted.ID Old_ID, deleted.Name Old_Name, inserted.ID New_ID, inserted.Name New_Name, CASE WHEN deleted.ID IS NOT NULL AND inserted.ID IS NOT NULL THEN 'U' WHEN deleted.ID IS NOT NULL THEN 'D' WHEN inserted.ID IS NOT NULL THEN 'I' END OperType; 

CREATE TABLE PositionsLog( LogID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_PositionsLog PRIMARY KEY, ID int, Old_Name nvarchar(30), New_Name nvarchar(30), LogType char(1) NOT NULL, LogDateTime datetime NOT NULL DEFAULT SYSDATETIME() ) 

-- добавление INSERT Positions(Name) OUTPUT inserted.ID,inserted.Name,'I' INTO PositionsLog(ID,New_Name,LogType) VALUES (N'Test 1'), (N'Test 2') -- обновление UPDATE Positions SET Name+=' - new' -- обратите внимание на синтаксис "+ sql">SELECT * FROM PositionsLog 

TRUNCATE TABLE EmployeesBonusTarget 

-- получение сводной таблицы при помощи GROUP BY SELECT EmployeeID, SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1, SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2, SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3, SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4, SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5, SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6, SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7, SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8, SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9, SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10, SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11, SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12, SUM(BonusAmount) TotalBonusAmount FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' -- отберем данные за 2014 год GROUP BY EmployeeID 

-- получение сводной таблицы при помощи PIVOT SELECT EmployeeID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12], ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)+ ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0) TotalBonusAmount FROM ( /* в данном подзапросе мы отберем только необходимые для свода данные: - поля BonusMonth и BonusAmount будут задействованы в конструкции PIVOT - прочие поля, в данном случае это только EmployeeID, будут использованны для группировки данных */ SELECT EmployeeID, MONTH(BonusDate) BonusMonth, BonusAmount FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' ) q PIVOT(SUM(BonusAmount) FOR BonusMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p 

SELECT EmployeeID, SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1, SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2, SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3, SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4, SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5, SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6, SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7, SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8, SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9, SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10, SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11, SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12, SUM(BonusAmount) TotalBonusAmount INTO DemoPivotTable -- сбросим сводный результат в таблицу FROM EmployeesBonus WHERE BonusDate BETWEEN '20140101' AND '20141231' GROUP BY EmployeeID 

SELECT * FROM DemoPivotTable 

-- демонстрация UNPIVOT SELECT *, CAST(REPLACE(ColumnLabel,'BonusAmount','') AS int) BonusMonth FROM DemoPivotTable UNPIVOT(BonusAmount FOR ColumnLabel IN(BonusAmount1,BonusAmount2,BonusAmount3,BonusAmount4, BonusAmount5,BonusAmount6,BonusAmount7,BonusAmount8, BonusAmount9,BonusAmount10,BonusAmount11,BonusAmount12)) u 

-- GROUP BY ROLLUP и функция GROUPING SELECT --GROUPING(YEAR(bonus.BonusDate)) g1, --GROUPING(bonus.EmployeeID) g2, --GROUPING(emp.Name) g3, CASE WHEN GROUPING(YEAR(bonus.BonusDate))=1 THEN 'Общий итог' WHEN GROUPING(bonus.EmployeeID)=1 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) -- исключаем ненужный итог обрабатывая GROUPING HAVING NOT(GROUPING(YEAR(bonus.BonusDate))=0 AND GROUPING(bonus.EmployeeID)=0 AND GROUPING(emp.Name)=1) 

-- GROUP BY ROLLUP и функция GROUPING_ID SELECT /* GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c) бинарное 001 = десятичное 1 бинарное 011 = десятичное 3 бинарное 111 = десятичное 7 */ --GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID, CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) WHEN 7 THEN 'Общий итог' WHEN 3 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) -- исключаем ненужный итог обрабатывая GROUPING_ID HAVING GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)<>1 

-- GROUP BY GROUPING SETS и функция GROUPING_ID SELECT /* GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c) бинарное 001 = десятичное 1 бинарное 011 = десятичное 3 бинарное 111 = десятичное 7 */ --GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID, CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) WHEN 7 THEN 'Общий итог' WHEN 3 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год' END RowTitle, emp.Name, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4, SUM(bonus.BonusAmount) TotalBonusAmount FROM EmployeesBonus bonus JOIN Employees emp ON bonus.EmployeeID=emp.ID GROUP BY GROUPING SETS( (YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name), -- Имя сотрудника (YEAR(bonus.BonusDate)), -- Сумма по годам () -- Общий итог ) 

-- пример использования FULL JOIN WITH cteBonus AS( SELECT YEAR(BonusDate) BonusYear, EmployeeID, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=1 THEN BonusAmount END) BonusAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=2 THEN BonusAmount END) BonusAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=3 THEN BonusAmount END) BonusAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=4 THEN BonusAmount END) BonusAmountQ4, SUM(BonusAmount) TotalBonusAmount FROM EmployeesBonus GROUP BY YEAR(BonusDate),EmployeeID ), cteSalary AS( SELECT YEAR(SalaryDate) SalaryYear, EmployeeID, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=1 THEN SalaryAmount END) SalaryAmountQ1, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=2 THEN SalaryAmount END) SalaryAmountQ2, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=3 THEN SalaryAmount END) SalaryAmountQ3, SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=4 THEN SalaryAmount END) SalaryAmountQ4, SUM(SalaryAmount) TotalSalaryAmount FROM EmployeesSalary GROUP BY YEAR(SalaryDate),EmployeeID ) SELECT ISNULL(s.SalaryYear,b.BonusYear) AccYear, ISNULL(s.EmployeeID,b.EmployeeID) EmployeeID, s.SalaryAmountQ1,s.SalaryAmountQ2,s.SalaryAmountQ3,s.SalaryAmountQ4, s.TotalSalaryAmount, b.BonusAmountQ1,b.BonusAmountQ2,b.BonusAmountQ3,b.BonusAmountQ4, b.TotalBonusAmount, ISNULL(s.TotalSalaryAmount,0)+ISNULL(b.TotalBonusAmount,0) TotalAmount FROM cteSalary s FULL JOIN cteBonus b ON s.EmployeeID=b.EmployeeID AND s.SalaryYear=b.BonusYear 

-- добавим новые должности SET IDENTITY_INSERT Positions ON INSERT Positions(ID,Name)VALUES (10,N'Маркетолог'), (11,N'Логист') SET IDENTITY_INSERT Positions OFF 

-- новые сотрудники INSERT Employees(ID,Name,DepartmentID,PositionID,HireDate,Salary,Email)VALUES (1006,N'Антонов А.А.',4,10,'20150215',1800,'a.antonov@test.tt'), (1007,N'Максимов М.М.',5,11,'20150405',1200,'m.maksimov@test.tt'), (1008,N'Данилов Д.Д.',5,11,'20150410',1200,'d.danolov@test.tt'), (1009,N'Остапов О.О.',5,11,'20150415',1200,'o.ostapov@test.tt') 

SELECT ID, Name, DepartmentID, Salary, -- получаем сумму ЗП всех сотрудников SUM(Salary) OVER() AllSalary, -- получаем сумму ЗП сотрудников этого же отдела SUM(Salary) OVER(PARTITION BY DepartmentID) DepartmentSalary, -- процент ЗП сотрудника от суммы ЗП всего отдела CAST(Salary/SUM(Salary) OVER(PARTITION BY DepartmentID)*100 AS numeric(20,3)) SalaryPercentOfDepSalary, -- кол-во всех сотрудников COUNT(*) OVER() AllEmplCount, -- кол-во сотрудников в отделе COUNT(*) OVER(PARTITION BY DepartmentID) DepEmplCount FROM Employees 

SELECT ID, Name, -- нумирация в порядке значений Name ROW_NUMBER() OVER(ORDER BY Name) EmpNoByName, -- нумирация в порядке значений LastName,FirstName,MiddleName ROW_NUMBER() OVER(ORDER BY LastName,FirstName,MiddleName) EmpNoByFullName FROM Employees ORDER BY Name 

SELECT emp.ID, emp.Name EmpName, dep.Name DepName, -- нумирация сотрудников в разрезе отделов, в порядке значений Name ROW_NUMBER() OVER(PARTITION BY dep.ID ORDER BY emp.Name) EmpNoInDepByName FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID ORDER BY dep.Name,emp.Name 

SELECT emp.ID, emp.Name EmpName, emp.PositionID, -- кол-во сотрудников в разрезе должностей COUNT(*) OVER(PARTITION BY emp.PositionID) EmpCountInPos, -- ранжирование с дырками - следующий номер зависит от кол-ва записей в предыдущей группе RANK() OVER(ORDER BY emp.PositionID) RankValue, -- ранжирование без дырок – плотная нумерация (последовательная) DENSE_RANK() OVER(ORDER BY emp.PositionID) DenseRankValue FROM Employees emp LEFT JOIN Positions pos ON emp.PositionID=pos.ID 

SELECT ID CurrEmpID, Name CurrEmpName, -- значения предыдущей строки LAG(ID) OVER(ORDER BY ID) PrevEmpID, LAG(Name) OVER(ORDER BY ID) PrevEmpName, LAG(ID,2) OVER(ORDER BY ID) PrevPrevEmpID, LAG(Name,2,'not found') OVER(ORDER BY ID) PrevPrevEmpName, -- значения следующей строки LEAD(ID) OVER(ORDER BY ID) NextEmpID, LEAD(Name) OVER(ORDER BY ID) NextEmpName, LEAD(ID,2) OVER(ORDER BY ID) NextNextEmpID, LEAD(Name,2,'not found') OVER(ORDER BY ID) NextNextEmpName FROM Employees ORDER BY ID 

SELECT ID CurrEmpID, Name CurrEmpName, DepartmentID, -- первое значение в группе FIRST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpID, FIRST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpName, -- последнее значение в группе LAST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpID, LAST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpName FROM Employees ORDER BY DepartmentID,ID 

SELECT ID, Salary, SUM(Salary) OVER() Sum1, -- сумма всех строк - "все предыдущие" и "все последующие" SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND unbounded following) Sum2, -- сумма строк до текущей строки включительно - "все предыдущие" и "текущая строка" SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND current row) Sum3, -- сумма всех последующих от текущей строки включительно - "текущая строка" и "все последующие" SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN current row AND unbounded following) Sum4, -- сумма следующих трех строк - "1 следующую" и "3 следующие" SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 following AND 3 following) Sum5, -- сумма трех строк - "1 предыдущая" и "1 следующую" SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 1 following) Sum6, -- сумма предыдущих "трех предыдущих" и "текущей" SUM(Salary) OVER(ORDER BY ID ROWS 3 preceding) Sum7, -- сумма "всех предыдущих" и "текущей" SUM(Salary) OVER(ORDER BY ID ROWS unbounded preceding) Sum8 FROM Employees ORDER BY ID 

SELECT PositionID, Salary, SUM(Salary) OVER(PARTITION BY PositionID) Sum1, -- сумма ЗП для всех значений PositionID - "все меньшие" и "все большие" SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND unbounded following) Sum2, -- сумма ЗП для значений меньших PositionID до текущего значения включительно - "все меньшие" и "текущее значение" (значения<=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND current row) Sum3, -- сумма ЗП для всех больших значений от текущего значения включительно - "текущее значение" и "все большие" (значения>=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN current row AND unbounded following) Sum4, /* Увы следующие комбинации для RANGE в MS SQL не работают, хотя в Oracle они работают. Вырезки из MSDN: Предложение RANGE не может использоваться со PRECEDING или со FOLLOWING. PRECEDING Указывается с для обозначения числа строк или значений перед текущей строкой. Эта спецификация не допускается в предложении RANGE. FOLLOWING Указывается с для обозначения числа строк или значений после текущей строки. Эта спецификация не допускается в предложении RANGE. */ -- сумма ЗП для трех значений - "+1" и "+3" (значение BETWEEN PositionID+1 AND PositionID+3) --SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 following AND 3 following) Sum5, -- сумма ЗП для трех значений - "-1" и "+1" (значение BETWEEN PositionID-1 AND PositionID+1) --SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 preceding AND 1 following) Sum6, -- сумма ЗП для предыдущих трех значений - "-3" и "текущее" (значение BETWEEN PositionID-3 AND PositionID) --SUM(Salary) OVER(ORDER BY PositionID RANGE 3 preceding) Sum7, -- сумма ЗП для "всех предыдущих значений" и "текущего" (значения<=PositionID) SUM(Salary) OVER(ORDER BY PositionID RANGE unbounded preceding) Sum8 FROM Employees ORDER BY PositionID 

 ОПЕРАТОР аргументы ИНСТРУКЦИЯ аргументы;

 CREATE DATABASE db_name;

 CREATE TABLE table_name ( column_name_1 type1(size) constraint_1, column_name_2 type2(size) constraint_2, ... CONSTRAINT constraint_name_n constraint_n arguments, ... INDEX index_name_1 (columns...), ... );

 CONSTRAINT pk_table PRIMARY KEY (col1, col2, ...)

 CONSTRAINT fk_table FOREIGN KEY (col1, ...) REFERENCES tbl_name (ref_col1, ...) ON UPDATE upd_action ON DELETE del_action;

 CREATE INDEX index_name ON table_name (column_name); CREATE UNIQUE INDEX index_name ON table_name (column_name);

 DROP DATABASE db_name;

 DROP TABLE table_name;

 DROP INDEX index_name ON table_name;

 ALTER TABLE table_name COMMAND arguments;

 SELECT * FROM table_name;

 SELECT column_1, column_2 FROM table_name;

 SELECT ... FROM table_name WHERE condition;

 SELECT DISTINCT ... FROM table_name ...;

 INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2,...);

 DELETE FROM table_name WHERE condition;

 UPDATE table_name SET column_1=value_1, column_2=value_2, ... WHERE condition;