Управление параллельной работой
Как вы уже знаете, данные в базе данных обычно используются многими программами пользовательских приложений. Ситуация, при которой несколько программ пользовательских приложений читают и пишут одни и те же данные в одно и то же время, называется конкурентным доступом. Следовательно, каждая СУБД должна иметь некоторый механизм управления для разрешения проблем конкурентного доступа. Высокий уровень конкурентного доступа возможен в системе базы данных, которая может управлять многими активными пользовательскими приложениями без их влияния друг на друга. И наоборот, система базы данных, где различные активные приложения влияют друг на друга, поддерживает низкий уровень конкурентного доступа. Этот раздел начинается с описания двух моделей управления конкурентным доступом, которые поддерживает Database Engine. В следующем разделе объясняется, как проблемы конкурентного доступа могут быть решены с использованием транзакций. Это обсуждение включает вводные сведения в четыре свойства транзакций, известные как свойства ACID (Atomicity, Consistency, Isolation, Durability- атомарность, согласованность, изолированность, устойчивость), обсуждаются связанные с транзакциями операторы Transact-SQL, вводится понятие протокола транзакции. Затем рассматривается блокировка и три основных свойства блокировки: модели блокировки, ресурсы блокировки, длительность блокировки
Также вводится важное понятие взаимной блокировки, которая может возникать как результат обычной блокировки
Изменение таблицы
На поздних этапах проектирования или уже после разработки базы часто возникает необходимость:
- добавить или удалить столбец;
- изменить тип столбца;
- назначить столбец или группу столбцов первичным или внешним ключом, или снять эти ограничения.
Для всех этих операций предназначена инструкция переопределения ATER TABLE
.
Чтобы добавить столбец инструкция ALTER TABLE применяется с предложением ADD. Добавим новый столбец к таблице housemates из прошлого раздела:
ALTER TABLE housemates
ADD PhoneNumber nvarchar(12) NULL;
Нужно применить к нему предложение ALTER COLUMN
внутри ALTER TABLE:
ALTER TABLE housemates
ALTER COLUMN PhoneNumber char(25) NOT NULL;
Удаляется столбец применением DROP COLUMN
внутри ALTER TABLE:
ALTER TABLE housemates
DROP COLUMN PhoneNumber;
Первичный или внешний ключ удаляется и добавляется конструкциями ALTER TABLE ADD CONSTRAINT/DROP CONSTRAINT, соответственно:
Снятие ограничения первичного ключа со столбца housemeateID
ALTER TABLE housemates
DROP CONSTRAINT primary_id;
—Назначение столбца housemeateID первичным ключом
ALTER TABLE housemates
ADD CONSTRAINT PK_ID PRIMARY KEY (housemeateID);
Если попытаться присвоить таблице первичный ключ, который уже там есть транслятор запросов вернет ошибку. Поэтому если в таблице уже есть первичный ключ, следует снять его и назначить группу столбцов первичным ключом. Например, так:
ALTER TABLE housemates
DROP CONSTRAINT PK_ID;
ALTER TABLE housemates
ADD CONSTRAINT PK_ID,PK_AN PRIMARY KEY (housemeateID,apartmentnumber);
Table-Valued Parameters
The Table-Valued Parameter (TVP) is a special type of table variable that extends its use. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference, a pointer to the table in the TempDB.
Table-valued parameters have been used since SQL Server 2008 to send several rows of data to a Transact-SQL routine or to a batch via .. Their particular value to the programmer is that they can be used within TSQL code as well as in the client application, so they are good for sending client tables to the server. From TSQL, you can declare table-valued variables, insert data into them, and pass these variables as table-valued parameters to stored procedures and functions.Their more general usefulness is limited by the fact that they are only passed as read-only. You can’t do , , or statements on a table-valued parameter in the body of a routine.
You need to create a User-Defined Table Type and define a table structure to use them. Here is a simple example of their use in TSQL
|
1 |
/* First you need to create a table type. */ CREATETYPENamesASTABLE (NameVARCHAR(10)); GO CREATEPROCEDUREChooseAName @CandidateNamesNamesREADONLY AS DECLARE@candidatesTABLE(NAMEVARCHAR(10), theOrderUNIQUEIDENTIFIER) INSERT INTO@candidates (name,theorder) SELECT name,NEWID() FROM @CandidateNames SELECTTOP1 NAME FROM @Candidates ORDERBYtheOrder GO DECLARE@MyFavouriteCowNameASNames; INSERT INTO@MyFavouriteCowName (Name) SELECT’Bossy’UNIONSELECT’Bessy’UNIONSELECT’petal’UNIONSELECT’Daisy’UNIONSELECT’Lulu’UNIONSELECT’Buttercup’UNIONSELECT’Bertha’UNIONSELECT’Bubba’UNIONSELECT’Beauregard’UNIONSELECT’Brunhilde’UNIONSELECT’Lore’UNIONSELECT’Lotte’UNIONSELECT’Rosa’UNIONSELECT’Thilde’UNIONSELECT’Lisa’UNIONSELECT’Peppo’UNIONSELECT’Maxi’UNIONSELECT’Moriz’UNIONSELECT’Marla’ EXECchooseAName@MyFavouriteCowName GO |
As with Table Variables, the table-valued parameter ceases to exist once it is out of scope but the type definition remains until it is explicitly dropped. Like Table Variables they do not acquire locks when the data is being populated from a client, and statistics aren’t maintained on columns of table-valued parameters. You cannot use a table-valued parameter as target of a or statement. As you’d expect, a table-valued parameter can be in the clause of or in the string or stored-procedure.
The TVP solves the common problem of wanting to pass a local variable to dynamic SQL that is then executed by a . It is poorly documented by Microsoft, so I’ll show you a worked example to get you started
|
1 |
DECLARE@SeaAreasTABLE(NAMEVarchar(20)) INSERTINTO@SeaAreas (name) SELECT’Viking’UNIONSELECT’North Utsire’UNIONSELECT’South Utsire’UNIONSELECT’Forties’UNIONSELECT’Cromarty’UNIONSELECT’Forth’UNIONSELECT’Tyne’UNIONSELECT’Dogger’UNIONSELECT’Fisher’UNIONSELECT’German Bight’UNIONSELECT’Humber’UNIONSELECT’Thames’UNIONSELECT’Dover’UNIONSELECT’Wight’UNIONSELECT’Portland’UNIONSELECT’Plymouth’UNIONSELECT’Biscay’UNIONSELECT’Trafalgar’UNIONSELECT’Finisterre’UNIONSELECT’Sole’UNIONSELECT’Lundy’UNIONSELECT’Fastnet’UNIONSELECT’Irish Sea’UNIONSELECT’Shannon’UNIONSELECT’Rockall’UNIONSELECT’Malin’UNIONSELECT’Hebrides’UNIONSELECT’Bailey’UNIONSELECT’Fair Isle’UNIONSELECT’Faeroes’UNIONSELECT’Southeast Iceland’ CREATETYPEseanamesASTABLE(NameVARCHAR(20)); DECLARE@SeaAreaNamesASSeaNames; INSERT INTO@SeaAreaNames (name) SELECT * FROM @SeaAreas EXECsp_executesqlN’SELECT * FROM @MySeaAreas’, N’@MySeaAreas . READONLY’,@MySeaAreas=@SeaAreaNames
|
Before we move on to describe the more traditional temporary tables and their use, we’ll need to delve into the place where temporary tables are held. TempDB.
Задание псевдонимов для столбцов запроса
| ФИО | Дата приема | Дата рождения | ZP |
|---|---|---|---|
| Иванов Иван Иванович | 2015-04-08 | 1955-02-19 | 5000 |
| Петров Петр Петрович | 2015-04-08 | 1983-12-03 | 1500 |
| NULL | 2015-04-08 | 1976-06-07 | 2500 |
| NULL | 2015-04-08 | 1982-04-17 | 2000 |
| FullName1 | FullName2 | FullName3 |
|---|---|---|
| Иванов Иван Иванович | Иванов Иван Иванович | Иванов Иван Иванович |
| Петров Петр Петрович | Петров Петр Петрович | Петров Петр Петрович |
| NULL | Сидоров Сидор | Сидоров Сидор |
| NULL | Андреев Андрей | Андреев Андрей |
Основные арифметические операторы SQL
| Оператор | Действие |
|---|---|
| + | Сложение (x+y) или унарный плюс (+x) |
| — | Вычитание (x-y) или унарный минус (-x) |
| * | Умножение (x*y) |
| Деление (x/y) | |
| % | Остаток от деления (x%y). Для примера 15%10 даст 5 |
| ID | Name | Result1 | Result2 | Result3 |
|---|---|---|---|---|
| 1000 | Иванов И.И. | 2500 | 2500 | 2500 |
| 1001 | Петров П.П. | 225 | 225 | 225 |
| 1002 | Сидоров С.С. | NULL | ||
| 1003 | Андреев А.А. | 600 | 600 | 600 |
| 1004 | Николаев Н.Н. | NULL | ||
| 1005 | Александров А.А. | NULL |
| ID | Name |
|---|---|
| 1000 | Иванов И.И. |
| 1004 | Николаев Н.Н. |
| 1002 | Сидоров С.С. |
Идентификаторы
Идентификаторы – это имена объектов, на которые можно ссылаться в программе, написанной на языке Transact SQL. Первый символ может состоять из букв английского алфавита или «_», »@», »#». В качестве остальных символов идентификатора могут быть дополнительно использованы цифры и символ «$». Имя идентификатора не должно совпадать с зарезервированным словом (среда SQL Server Management Studio подсвечивает зарезервированные слова синим цветом).
Иногда в качестве идентификатора удобно использовать словосочетание. Можно воспользоваться заменой пробела на нижнее подчеркивание, а можно использовать ограничители идентификаторов — квадратные скобки или одинарные кавычки
Здесь надо обратить внимание на параметр QUOTED_IDENTIFIER
Для ограничителей идентификаторов при установленном параметре
можно использовать как квадратные скобки, так и одинарные кавычки, а строковые значения могут быть заключены только в одинарные кавычки (режим по умолчанию).
Если использовать установленный параметр в режиме
то в качестве ограничителей идентификаторов можно использовать только квадратные скобки, а строковые значения указываются в одинарных или двойных кавычках.
Переменные используются для сохранения промежуточных данных в хранимых процедурах и функциях. Все переменные считаются локальными. Имя переменной должно начинаться с @.
SQL Server Temp Table
SQL Server allows for two types of temporary tables:
- Local Temporary Tables are visible only in the current session
- Global Temporary Tables are visible to all sessions
How to Create a Temporary Table in SQL Server
Creating a temporary table in SQL Server is similar to creating a normal table. There are two ways to create this table:
- Using CREATE
- Using SELECT INTO
Here’s an example of using the CREATE statement:
A # character must be provided as a prefix to the table name to mark it as a temporary table. There is no “TEMPORARY” keyword that can be used in SQL Server.
This is also a “local temporary table” because it starts with a single #.
A “global temporary table” starts with two hash characters: ##.
The SQL temp table is now created. To store data in it, you can run an INSERT statement just like with a normal table:
The #temp_customers table will now contain the data from the SELECT query. It can be accessed within the session by the current connection.
Notice that the INSERT statement refers to the table with a # as a prefix. If the # prefix is omitted, then the table will not be found and you’ll get an error.
You can also create a temporary table in SQL Server by using the SELECT INTO syntax:
This will create a temporary table called #temp_customers and insert the results of the SELECT query into it in a single statement. You don’t need to specify the data types as they will be determined from the source table, so this may be an easier way for you to populate the table.
All SQL temp tables are created in the dbo schema. They are dropped when the session ends.
Global Temporary Tables in SQL Server
Global temporary tables are accessible to all connections. They are created in a similar way to local temporary tables, except you use a double # instead of a single # as a prefix.
They can be created in the same way as a local temporary table, using CREATE or SELECT INTO.
Here’s an example using CREATE:
Here’s an example using SELECT INTO:
Notice the syntax is the same, but they have a double # character as a prefix.
Drop a Temporary Table
In SQL Server, a temporary table is deleted or dropped when the connection that created it is closed. This is when you disconnect from the database, or when you close the query window in SSMS (or another IDE) and don’t save the changes.
If you want to drop the temporary table manually, you can do so in the same way as dropping a normal table:
This will drop the temporary table from the database.
The same method can be used on global tables:
Основное использование
Давайте теперь обсудим, как мы можем реализовать временные таблицы MySQL и сценарии, в которых нам может потребоваться их использование.
Общий синтаксис создания временной таблицы MySQL следующий:
Создание временной таблицы аналогично созданию обычной таблицы MySQL, за исключением использования ключевого слова TEMPORARY.
Например, чтобы создать временную таблицу, см. Запрос ниже:
После создания таблицы пользователь может выполнять основные операции с таблицей, включая INSERT, UPDATE, DELETE, SELECT и DROP.
Например, давайте вставим некоторые данные во временную таблицу, созданную выше:
Основные понятия
Сущности — предметы или факты, информацию о которых необходимо хранить. Например, сотрудник фирмы или проекты, реализуемые предприятием. Атрибуты — составляющая, которая описывает или квалифицирует сущность. Например, атрибут сущности «работник» — заработная плата, а атрибут сущности «проект» — сметная стоимость. Связи — ассоциации между двумя элементами. Она может быть двунаправленная. Также существует рекурсивная связь, то есть связь сущности с самой собой.
Также необходимо определить ключи и условия, при которых сохранится целостность базы данных. Что это значит? Другими словами — ограничения, которые помогут сохранить базы данных в правильном и согласованном виде.
Передача результатов вызова табличной функции в курсорной переменной
Табличные функции помогают решить проблему, с которой разработчики сталкивались в прошлом, — а именно как передать данные, полученные в программе PL/SQL (то есть данные, не хранящиеся в таблицах базы данных), в управляющую среду без поддержки PL/SQL ? Курсорные переменные позволяют легко передать результирующие наборы на базе SQL , допустим, в программу Java, потому что курсорные переменные поддерживаются в JDBC . Но если сначала нужно провести сложные преобразования в PL/ SQL , как вернуть эти данные вызывающей программе?
Теперь эта проблема решается объединением мощи и гибкости табличных функций с широкой поддержкой курсорных переменных в средах без поддержки PL/SQL . Допустим, я хочу сгенеририровать данные семейства животных (полученные вызовом функции pet_family из предыдущего раздела) и передать строки данных интерфейсному приложению, написанному на Java.
Это делается очень просто:
В этой программе я воспользуюсь преимуществами предопределенного слабого курсорного типа SYS_REFCURSOR (появившегося в Oracle9i Database ) для объявления курсорной переменной. Курсорная переменная открывается вызовом OPEN FOR и связывается с запросом, построенным на базе табличной функции pet_family .
Затем курсорная переменная передается интерфейсной части Java . Так как JDBC распознает курсорные переменные, код Java легко выполняет выборку строк данных и интегрирует их в приложение.
DECLARE SQL: описание
T-SQL поддерживает только локальные переменные, которые доступны исключительно в той партии, которая их создала. Пакет — это оператор (или группа операторов), который база данных анализирует как единицу. Каждый клиентский инструмент или интерфейс имеет свой собственный способ указания, где заканчивается пакет. Например, в Query Analyzer вы используете команду GO, чтобы указать, где заканчивается пакет. Если у вас есть синтаксическая ошибка в любом заявлении, пакет не проходит фазу разбора, поэтому клиентский инструмент не отправляет пакет на SQL Server для дальнейшей обработки. Вы можете запустить код, который объявляет переменную таблицы, а затем вставляет строку в таблицу в той же партии.
Пример SQL Declare Table:
DECLARE @mytable table col1 int NOT NULL INSERT INTO @mytable VALUES (1) GO
Теперь объявите переменную таблицы в одной партии, а затем вставьте строку в таблицу в другую партию:
DECLARE @mytable table col1 int NOT NULL INSERT INTO @mytable VALUES (1)GO
Оператор INSERT терпит неудачу, потому что переменная таблицы выходит за пределы области видимости, и появляется следующее сообщение об ошибке: Сервер: Msg 137, уровень 15, состояние 2, строка 2.
Как работать с базой
Для реализации крупных проектов чаще всего требуется создание нескольких баз данных, и каждая требует множество таблиц. Конечно, удержать всю информацию в голове пользователям невозможно. Для этого предусмотрена возможность посмотреть структуру баз данных и таблиц в них. Существует несколько команд, а именно:
- SHOW DATABASES — показывает на экране все созданные базы данных SQL;
- SHOW TABLES — выводит список всех таблиц для текущей базы данных, которые выбираются командой USE;
- DESCRIBE название_таблицы
— показывает описание всех столбцов таблицы. - ALTER TABLE — позволяет изменять структуру таблицы.
Последняя команда позволяет:
- добавить в таблицу столбец или ограничение;
- изменить существующий столбец;
- удалить столбец или столбцы;
- удалить ограничения целостности.
Синтаксис этой команды выглядит так: ALTER TABLE название_таблицы
{ | | | | [{ENABLE | DISABLE} CONSTANT имя_ограничения
] | }.
Существуют и другие команды:
- RENAME — переименование таблицы.
- TRUNCATE TABLE -удаляет все строки из таблицы. Эта функция может быть нужна, когда необходимо заполнить таблицу заново, а хранить предыдущие данные нет необходимости.
Также бывают ситуации, когда структура базы поменялась и таблицу следует удалить. Для этого существует команда DROP. Конечно, предварительно нужно выбрать базу данных, из которой нужно удалить таблицу, если она отличается от текущей.
Синтаксис команды довольно простой: DROP TABLE название
_таблицы.
В SQL Access создание таблиц и их изменение осуществляется теми же командами, перечисленными выше.
С помощью CREATE TABLE можно создать пустую таблицу и в дальнейшем заполнить ее данными. Но это еще не все. Также можно сразу создавать таблицу из другой таблицы. Как это? То есть существует возможность определить таблицу и заполнить ее данными другой таблицы. Для этого существует специальное ключевое слово AS.
Синтаксис очень простой:
- CREATE TABLE название
_таблицы
[(определение_столбцов
)] AS подзапрос; -
определение_столбцов —
имена столбцов, правила целостности для столбцов вновь создаваемой таблицы и значения по умолчанию; - подзапрос — возвращает такие строки, которые нужно добавить в новую таблицу.
Таким образом, такая команда создает таблицу с определенными столбцами, вставляет в нее строки, которые возвращаются в запросе.
Объявление переменных в DECLARE SQL
Для объявления переменной в T-SQL используется оператор DECLARE ( ). Например, в случае объявления переменной i как целое с использованием данного оператора команда будет выглядеть так: DECLARE @i int.
Хотя Microsoft не документирует эту функцию, T-SQL также поддерживает указание ключевого слова AS между именем переменной и ее типом данных, как в следующем примере: DECLARE @i AS int. Ключевое слово AS упрощает чтение инструкции DECLARE. Единственный тип данных, который не позволяет указать ключевое слово AS, — это тип данных таблицы, который является новым в SQL Server 2000. Он дает возможность определить переменную, содержащую полную таблицу.
Создание базы
Сначала неоходимо запустить сервер MySQL. Для его запуска следует зайти в меню «Пуск», затем в «Программы», далее в MySQL и MySQL Server, выбрать MySQL-Command-Line-Client.
Для создания базы данных применяется команда Create Database. Данная функция имеет следующий формат:
CREATE DATABASE название_базы_данных.
Ограничения на название базы следующие:
- длина составляет до 64 знаков и может включать буквы, цифры, символы «» и «»;
- имя может начинаться с цифры, но в нем должны присутствовать буквы.
Нужно помнить и общее правило: любой запрос или команда заканчиваются разделителем (delimiter). В SQL принято в качестве разделителя использовать точку с запятой.
Серверу необходимо указать, с какой базой данных нужно будет работать. Для этого существует оператор USE. Этот оператор имеет простой синтаксис: USE название_базы_данных.
