Текущее время: 28 мар 2024, 16:48

Калининград & ХОББИ

Занятие любимым хобби - это путь к счастливой жизни!

Transact SQL справочник

Все о языках программирования, примеры программ, функций, процедур, классов, синтаксис, скрипты , на любых языках программирования
Аватар пользователя
 
Сообщений: 19
Зарегистрирован: 01 апр 2013, 09:31

Transact SQL справочник

Сообщение Еремин » 17 авг 2013, 08:02

справочник по Transact SQL

Код: выделить все
ALTER DATABASE
ALTER DATABASE database_name
[ON [DEFAULT | database_device][=size]
[,database_device[=size]]...]
[FOR LOAD]

Позволят производить различные операции с базой данных после ее создания
Пример
Этот пример изменяет testing, расположенную на DEVICE1, добавляя 8-ми мегабайтный фрагмент:
Код: выделить все
 ALTER DATABASE testing
     ON DEVICE1 = 8
________________________________________
ALTER TABLE
ALTER TABLE [database.[owner].]table_name
[WITH NOCHECK]
[ADD
 {col_name column_properties column_constraints]
 |[[,]table_constraint]}
    [,{next_col_name|next_table_constraint}]...]
| DROP [CONSTRAINT]
 constraint_name[,constraint_name]

Позволят производить различные операции с таблицей после ее создания
Пример

1. Добавление PRIMARY KEY CONSTRAINT:
2. ALTER TABLE authors
3. ADD
CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED (au_id)
4. Добавление FOREIGN KEY CONSTRAINT:
5. ALTER TABLE titles
6. ADD
CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)
7. Добавление UNIQUE CONSTRAINT:
8. ALTER TABLE titles
9. ADD
CONSTRAINT UNC_name_city UNIQUE NONCLUSTERED (stor_name,city)
10. Добавление DEFAULT CONSTRAINT:
11. ALTER TABLE authors
12. ADD
DEFAULT 'UNKNOWN' FOR phone
Будьте внимательны с default!
13. Добавление CHECK CONSTRAINT:
14. ALTER TABLE authors
15. ADD
CONSTRAINT CK_zip CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]')
16. Добавление новой колонки
17. ALTER TABLE publishers
18. ADD
19. country varchar(30) NULL
DEFAULT('USA')
________________________________________
Batches
Batch - это набор операторов TSQL, передаваемых на выпонение и выполняющихся вместе, как одно целое. Batch компилируется целиком и оканчивается специальным символом-сигналом конца (go). Все последовательности операторов TSQL, набираемые в ISQL/w или в Enterprise Manager интерпретируются именно как batch'и (интересно то, что при выделении некоторой части текста в окне выполняться будет именно она)
Пример

Несколько SELECT в одном batch'е
Код: выделить все
  SELECT COUNT(*) FROM titles
  SELECT COUNT(*) FROM authors

________________________________________
Выражения CASE
Простое CASE Expession
Код: выделить все
CASE expression
 WHEN expression1 THEN exression1
 [[WHEN expression2 THEN expression2[..]]
 [ELSE expressionN]
 END
CASE Expression с поиском
 CASE
  WHEN Boolean_expression1 THEN expression1
  [[WHEN Boolean_expression2 THEN expression2[..]]
  [ELSE expressionN]
  END

Функции, полезные для CASEобразия:
COALESCE(expression1,expression2,...)
NULLIF(expression1,expression2)
ISNULL(expression1, expression)
COALESCE возвращает первое не-NULL выражение из списка, NULLIF возвращает NULL, если два выражения равны, ISNULL возвращает expression2 в том случае, если expression1 'is null'
Примеры

Код: выделить все
 SELECT Category=
   CASE type
       WHEN 'popular_comp' THEN 'Popular Computing'
       WHEN 'mod_cook' THEN 'Modern Cooking'
       WHEN 'business' THEN 'Businness'
       WHEN 'psyhology' THEN 'Psyhology'
       WHEN 'trad_cook' THEN 'Traditional Cooking'
       ELSE 'Not yet categorized'
    END,
    'Shortended Tiitle' = CONVERT(varchar(30), title),
    Price = price
 FROM titles
 WHERE price IS NOT NULL
 ORDER BY type
 COMPUTE AVG(price) BY TYPE
 go


Category Shortedned Title Price
---------------------- ------------------------------ -----------
Business Cooking with Computers: Surrep 11.95
Business Straight Talkk About Computers 19.99
Business The Busy Executive's Database 19.99
Business You Can Combat Computer Stress 2.99


avg
============
13.73
________________________________________
Оператор CHECKPOINT
CHECKPOINT
Записывает на диск все страницы, измененные с момента последнего выполнения оператора CHECKPOINT. Контрольные точки(Checkpoints), производимые оператором CHECKPOINT, являются дополнительными к тем контрольным точкам, которые берет сервер, исходя из заданного параметра recovery time
________________________________________
Комментарии
/*Текст комментария*/
или
--Текст комментария
________________________________________
Поток управления
Порядок выполнения запросов и хранимых процедур TSQL может изменяться с помощью определенных операторов (как бы перевести Control-of-Flow Language )
Оператор Описание
BEGIN...END Определяет блок.
Код: выделить все
      BEGIN
       {sql_statement|statement_block}
      END


GOTO label Безусловный переход к метке label Метки описываются незамысловато:
:label
Код: выделить все
   GOTO label


IF...ELSE Условный оператор. Тоже ничего неожиданного
IF Boolean_expression
{sql_statement|statement_block}
[ELSE [Boolean_expression]
{sql_statement|statement_block}

RETURN Безусловный выход. Синтаксис бесхитростен:
RETURN ([integer_expression])

Жалко только, что возвращаемое значение - это не значение функции, а некий код возврата, причем часть значений зарезервирована под специальные состояния (0 - все нормально):
Код Значение
0 Все нормально
-1 Объект не найден
-2 Ошибка типа данных
-3 Процесс стал жертвой дедлока
-4 Ошибка доступа
-5 Синтаксическая ошибка
-6 "Некоторая" ошибка
-7 Ошибка с ресурсами(н., нет места)
-8 Произошла исправимая внутренняя ошибка
-9 Системный лимит исчерпался
-10 Неисправимое нарушение внутренней целостности
-11 То же самое
-12 Разрушение таблицы или индекса
-13 Разрушение базы данных
-14 Ошибка оборудования

WAITFOR Ожидание определенного события.
Код: выделить все
WINTFOR {DELAY 'time'|TIME 'time'}


DELAY - определение задержки time, ну а TIME - ожидание до указанного времени. time задается аналогично значениям для datetime
WHILE Цикл с предусловием.
Код: выделить все
      WHILE Boolean_condition
        {sql_statement|statement_block}
        [BREAK]
         {sql_statement|statement_block}
        [CONTINUE]


...BREAK Выход из цикла WHILE
...CONTINUE Продолжение цикла WHILE
Кроме вышеуказанных операторов могут оказаться полезными при написании запросов и хранимых процедур следующие возможности TSQL:
Возможность Описание
CASE
Позволяет выражениям принмать значение в зависимости от условий. Между прочим, CASE является стандартной возможностью ANSI SQL-92
Комментарии Очень даже полезно хотя бы изредка комментировать свой код. Для комметатриев можно использовать две формы - первая, аналогичная комментариям в С:
/*некий глубокомысленный комметарий*/,
и вторая, аналогичная комментариям в Аде:
--Еще один заумный комметарий
Оператор DECLARE Повзоляет объявлять локальные переменные К слову сказать, он может стоять не только в начале процедуры или batch'а, но и где угодно в их теле. Удобно объявлять переменные там, где они используются, а не тремястами строками выше. Впрочем, на мой взгляд, злоупотреблять этим тоже не стоит...
Оператор PRINT Выдает заданное значение на экран.
Оператор RAISERROR Устанавливает ошибочное состояние
Пример

Код: выделить все
 DECLARE @Name varchar(40)
 DECLARE CT CURSOR FOR SELECT Name from Peoples
 OPEN CT
 WHILE 1=1 BEGIN
  FETCH FROM CT INTO @Name
  /*Так, на мой взгляд, удобнее обходиться с курсорами*/
  IF @@fetch_status=-1
      BREAK
  IF @@fetch_status=-2
      CONTINUE
  PRINT @Name
 END
 DEALLOCATE CT
________________________________________
CREATE DATABASE
 CREATE DATABASE database_name
 [ON [DEFAULT|database_device][= size]
       [,database_device][= size]]...]
 [LOG ON database_device[= size]
       [,database_device[= size]...]
 [FOR LOAD]

Создает БД и, возможно, журнал транзакций на указанных devices, size - размер в мегабайтах. При создании новой базы данных используется как образец БД model.
Пример

Код: выделить все
 CREATE DATABASE sales
 ON DEVICE3 = 125
 LOG ON DEVICE4 = 60
________________________________________
CREATE DEFAULT
 CREATE DEFAULT [owner.]default_name
 AS constant_expression

Создает объект, который впоследствии можно будет прикрепить к некоторой колонке или пользовательскому типу данных, причем при добавлении новых данных значение constant_expression будет использовано по умолчанию, т.е. тогда, когда не было указано значение для этой колонки или было использовано ключевое слово DFAULT или DEFAULT VALUES. Для привязки созданного DEFAULT следует пользоваться процедурой sp_bindefault
Следующая таблица показывает соотношение между определением колонки и добавляемым значением:
Определение колонки Нет данных, нет DEFAULT Нет данных, DEFAULT определен Вводится NULL, нет DEFAULT Вводится NULL, DEFAULT определен
NULL NULL DEFAULT NULL NULL
NOT NULL error default error error
Обратите внимание - DEFAULT можно создать только в текущей БД.
Пример

Код: выделить все
CREATE DEFAULT  phonedflt AS 'unknown'
 sp_bindefault phonedflt,'authors.phone'
________________________________________
CREATE INDEX
 CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]INDEX index_name
  ON [[database.]owner.]table_name(column_name[,column_name]...)
 [WITH]
  [FILLFACTOR = x]
  [[,] IGNORE_DUP_KEY]
  [[,] {SORTED_DATA | SORTED_DATA_REORG}]
  [[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
 [ON segment_name]

Создает индекс для перечмсленных колонок на указанном сегоменте.
CLUSTERED - создавать кластеризованный индекс, т.е. такой индекс, при котором в листьях B-дерева, образующего индекс, находятся не ссылки на данные, а собственно страницы данных.
FILLFACTOR - позволяет управлять заполнением страниц B-дерева индекса, задается в процентах, 100% - полное заполнение.
Пример
Код: выделить все
CREATE UNIQUE CLUSTERED INDEX au_id_ind
     ON authors (au_id)

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

! Обратите внимание: если уже существующий пользовательский тип данных конфликтует с другим пользовательским или системным типом данных, то он будет использоваться как базовый системный тип.
Существуют следующие системные типы данных:
Вид данных Системное представление
Двоичные binary[(n)]
varbinary[(n)]
Символьные char[(n)]
varchar[(n)]
Дата и время datetime
smalldatetime
Точное представление чисел decimal[(p[, s])]
numeric[(p[, s])]
Представление чисел с плавающей точкой float[(n)]
real
Целочисленные типы int - 4 байта
smallint - 2 байта
tinyint -1 байт
Денежные типы money
smallmoney
Специальные bit
timestamp
типы, определяемые пользователем
Текст и картинки text
image
Синонимы binary varying для varbinary
character для char
character для char (1)
character (n) для char (n)
character varying (n) для varchar (n)
dec для decimal
integer для int
double precision для float
float [(n)] для n = 1-7 для real
float [(n)] для n = 8-15 для float
Типы данных даты и времени
Данные и время представляются алфвавитно-цифровыми данными, в виде строки. По умолчанию для показа даты используется формат Mon dd yyyy hh:mmAM, например, "Apr 15 1996 10:23AM". При вводе данных следует обращать внимание на порядок лет, месяцев, дней и т.п.
При вводе данных используйте один из нескольких форматов, заключая значение в одиночные кавычки - " ' ". Если требуется получить секунды или миллисекунды - для этого применяется функция CONVERT. Существуют следующие типы даты и времени:
datetime
Этот тип данных имеет размер в 8 байт, т.е. два четырехбайтных целых - 4 байта на количество дней, прошедших или еще не наступивших с 1 января 1900, и 4 байта на число миллисекунд, прошедших с полуночи.
datetime может содержать даты с 1 января 1753 года и по 31 декабря 9999 года, с точностью в три тысячных секунды. По умолчанию datetime имеет значение 1 января 1900 года, полдень.
smalldatetime
Тип данных, во многом аналогичный datetime, но менее точный. Размер его - 4 байта, два байта на число дней, прошедших с 1 января 1900 года, и два байта на число минут с полуночи. Даты могут быть представлены в диапазоне с 1 января 1900 года и по 6 июня 2079 года, с точностью в минуту
Для ввода дат и времени можно применять следующие форматы:
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
Apr[il] [19]96 15
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
[19]96 APR[IL] 15
1996 [15] APR[IL]

[0]4/15/[19]96 (mdy)
[0]4-15-[19]96 (mdy)
[0]4.15.[19]96 (mdy)
[04]/[19]96/15 (myd)

15/[0]4/[19]96 (dmy)
15/[19]96/[0]4 (dym)
[19]96/15/[0]4 (ydm)
[19]96/[04]/15 (ymd)
но, наверное, самым удобным и безопасным является формат [19]960415 - строка из шести или восьми цифр, в формате ггггммдд или ггммдд. Строка из четырех цифр будет интерпретирована как год.
Денежные типы данных
Типы данных money и smallmoney предназначены в первую очередь для представления денег.
money
Тип данных money в состоянии представлять числа в диапазоне от -922,337,203,685,477.5808 до +922,337,203,685,477.5807 с точностью в одну десятитысячную, имеет размер в 8 байт.
smallmoney
Тип данных smallmoney в состоянии представлять числа в диапазоне от -214,748.3648 до +214,748.3647, размер 4 байта.
Специальные типы данных
Среди всех более или менее обычных типов данных, имеющихся в SQL Server, несколько выделяются два типа данных - bit и timestamp.
Первый - bit - предназначен для представления целых чисел в диапазоне от 0 до 1, причем при вводе числа, отличного от 1, принимается значение, равное 1. Тип данных bit имеет размер в один байт, но при наличии нескольких полей типа bit в таблице они все будут упакованы вместе, например, если у нас есть 7 полей типа bit, то суммарное занимаемое ими пространство будет равно одному байту.
! Обратите внимание: Для полей, имеющих тип bit, невозможно построить индекс.
Значение другого типа данных - timestamp - обновляется при каждом изменении записи.
! Обратите внимание: Поля типа timestamp не имеют никакого отношения к системной дате или системному времени.
Если при создании таблицы будет указана колнока с именем timestamp, и при этом не будет указан никакой тип, то эта колонка автоматически будет иметь тип timestamp.
timestamp имеет размер 8 байт и представлено как varbinary(8)
________________________________________
Оператор DBCC
Код: выделить все
DBCC {
    CHECKALLOC [(database_name [, NOINDEX])] |
    CHECKCATALOG [(database_name)] |
    CHECKTABLE (table_name [, NOINDEX | index_id]) |
    CHECKDB [(database_name [, NOINDEX])] |
    CHECKIDENT [(table_name)] |
    DBREPAIR (database_name, DROPDB [, NOINIT]) |
    dllname (FREE) |
    INPUTBUFFER (spid) |
    MEMUSAGE |
    NEWALLOC [(database_name [, NOINDEX])] |
    OPENTRAN ({database_name} | {database_id})
        [WITH TABLERESULTS] |
    OUTPUTBUFFER (spid) |
    PERFMON |
    PINTABLE (database_id, table_id) |
    SHOW_STATISTICS (table_name, index_name) |
    SHOWCONTIG (table_id, [index_id]) |
    SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']]) |
    SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} |
        {THREADS} | {LOGSPACE}) |
    TEXTALL [({database_name | database_id}[, FULL | FAST])] |
    TEXTALLOC [({table_name | table_id}[, FULL | FAST])] |
    TRACEOFF (trace#) |
    TRACEON (trace#) |
    TRACESTATUS (trace# [, trace#...]) |
    UNPINTABLE (database_id, table_id) |
    UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]]) |
    USEROPTIONS}
[WITH NO_INFOMSGS]

где
CHECKCATALOG [(database_name)]
Проверяет целостность системных таблиц и отношений между ними
CHECKTABLE (table_name [, NOINDEX | index_id])
Проверяет указанную таблицу, убеждаясь, что ее страницы и страницы указанного индекса корректно связаны. Если сегмент журнала расположен на отдельном устройстве, то запуск DBCC CHECKTABLE для таблицы syslogs позволит узнать размер использованного и свободного места в журнале.
CHECKDB[(table_name)]
Проводит проверку, аналогичную CHECKTABLE, но только для каждой таблицы и ее индексов в указанной базе данных, или в текущей, если никакая не указана.
CHECKIDENT[(table_name)]
Проверяет текущее значение IDENTITY для указанной таблицы и сравнивает его с маскимальным из фактически имеющихся, изменяя его в случае необходимости.
NOINDEX
Указывает, что необходимо проверять только кластеризованный индекс и его листья - то есть собственно страницы данных, если же кластеризованный индекс отсутсвует - проверяются только страницы данных.
DBREPAIR(database_name,DROPDB,NOINIT)
Удаляет указанную базу данных, обычно разрушенную, после чего уже невозможно получить к ней доступ. Если указана опция NOINIT, то тогда страницы данных, представляющих из себя базу данных, не модифицируются. DBREPAIR - это, собственно говоря, пережиток прошлого. Теперь для удаления разрушенной базы данных нормальные пацаны пользуются оператором DROP DATABASE. Если же и он не работает (во дают!), то следует воспользоваться хранимой процедурой sp_dbremove. Указаний, куда идти, если и это не помогает, Microsoft не дает, впрочем, большинство и так догадывается.
dllname(FREE)
Выгружает из памяти указанную библиотеку.
INPUTBUFFER(spid)
Возвращает первые 255 байт буфера SQL запроса для указанного spid.
MEMUASGE
Выдает данные об использовании памяти сервером.
NEWALLOC(database_name[,NOINDEX])
Проверяет корректность текущей или указанной бд, выдавая более подробный отчет, нежели чем CHECKALLOC, и не прерывая работы в случае нахождения ошибки.
! Обратите внимание: CHECKALLOC или NEWALLOC могут сообщать об ошибке в том случае, если база данных испольщуется кем-то еще, так что старайтесь запускать проверку при минимальной загрузке или даже в однопользовательском режиме.
OPENTRAN({database_name|database_id}) WITH TABLERESULTS
Позволяет получить информацию об начатых, но незавершенных транзакциях, и о начатых, но не распространенных(distributed) транзакциях.
OUTPUTBUFFER(spid)
Позволяет просмотреть выходные данные для процесса spid
PERFMON
Позволяет получить статистику всех трех типов - IOSTATS, LRUSTATS и NETSTATS.
PINTABLE(database_id, table_id)
Помещает таблицу и связанные с ней индексы в оперативную память. Не следует считать, что PINTABLE прочитывает таблицу непосредственно в память; напротив, данные, к которым был доступ, помещаются в кеш, откуда уже не вытесняются до последующего вызова DBCC UNPINTABLE.
SHOW STATISTICS(table_name,index_name)
Позволяет получить статистику о распределении страниц в таблице
SHOWCONTIG(table_id,index_id)
Показывает фрагментированность таблицы
SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])
Позволяет сократить по возможности размер, занимаемый базой данных.
SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} {THREADS} {LOGSPACE})
Позволяет просмотреть различного рода статистику
TEXTALL[({database_name|database_id})[,FULL|FAST])
Проверяет корректность размещения данных во всей базе данных в колонках типа text или image.
TEXTALLOC[({table_name|table_id},[FULL|FAST])]
Проверяет корректность размещения данных в колонках типа text или image.
TRACEOFF(trace#)
Выключает указанный флаг трассировки
TRACEON(trace#)
Включает указанный флаг трассировки
TRACESTATUS(trace#[,trace#...])
Показывает статус для указанных флагов трассировки
UNPINTABLE(database_id,table_id)
Объявляет указанную таблицу как нерезидентную в памяти сервера. Данные, однако, остаются в кеше до тех пор, пока они не будут сборошены на диск по тем или иным причинам.
UPDATEUASGE({0|database_name},table_name[,index_id])
Исправляет возможные неаккуратности в сообщениях о размере базы данных.
USEROPTIONS
Позволяет посмотреть список опций, установденных командой SET
WITH NO_INFOMSGS
Подавляет выдачу информационных сообщений (уровень с 1 по 10)
Пример

Код: выделить все
DBCC CHECKALLOC(pubs)
go
Checking pubs
Alloc page 0 (# of extent=32 used pages=58 ref pages=58)
Alloc page 256 (# of extent=26 used pages=37 ref pages=37)
Alloc page 512 (# of extent=14 used pages=40 ref pages=40)
Alloc page 768 (# of extent=1 used pages=8 ref pages=2)
Alloc page 1024 (# of extent=1 used pages=0 ref pages=0)
Alloc page 1280 (# of extent=1 used pages=0 ref pages=0)
Total (# of extent=75 used pages=143 ref pages=137) in this database
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
________________________________________
CREATE PROCEDURE
CREATE PROCedure [owner.]procedure_name[;number]
    [(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
    [{[WITH] | [,]} ENCRYPTION]]
AS sql_statements

parameter=
@parameter_name datatype[=default][OUTPUT]
Создает процедуру с указанным именем. Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb. Для создания временных процедур следует начинать ее имя с '#' или '##'. Длина имени хранимой процедуры вместе с ## не может превышать 20 символов. Одна процедура может вызывать другую процедуру, уровень вложенности не может превышать 16, текущий уровень вложенности можно узнать из глобоальной переменной @@NESTLEVEL
Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи - в базе данных master. Таблицы, используемые в системной процедуре, определямой пользователем, также сначала отыскиваются в текущей базе данных, и если это не удалось - в базе данных master.
Пример

Код: выделить все
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
    FROM authors a, titles t, publishers p, titleauthor ta
        WHERE a.au_id = ta.au_id
        AND t.title_id = ta.title_id
        AND t.pub_id = p.pub_id
________________________________________
CREATE RULE
CREATE RULE [owner.]rule_name
AS condition_expression

Создает правило - некоторый объект, который впоследствие может быть привязан к какой-либо колонке или типу, определяемому пользователем.
Вы можете привязать правило к колонке, к которой до этого уже было привязано какое-либо другое правило; после этого уже будет использоваться вновь привязанное правило. Более того, привязка правила имеет больший приоритет, чем тип данных, определяемый пользователем.
Пример

Код: выделить все
CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877')
________________________________________
CREATE TABLE
CREATE TABLE [database.[owner].]table_name
(
    {col_name column_properties [constraint [constraint [...constraint]]]
    | [[,] constraint]}
        [[,] {next_col_name | next_constraint}...]
)
[ON segment_name]

Создает таблицу
Кроме обычных таблиц можно также создавать и временные таблицы - такими таблицами являются те, чье имя начинается с #(локальные временные таблицы) или ##(глобальные временные таблицы). Временные таблицы сушествуют только на время клиентской сессии и после ее окончания автоматически уничтожаются. Кроме того, временные таблицы, созданные в хранимой процедуре автоматически уничтожаются после ее окончания. Временные таблицы создаются в базе данных tempdb, и вносятся в таблицу tempdb..sysobjects под указанным именен + некоторая строка, генереруемая сервером. При создании временных таблиц нельзя использовать ограничение FOREIGN KEY и ON segment_name

column_properties =
datatype [NULL | NOT NULL | IDENTITY[(seed, increment)]]
datatype
Опрелеляет тип создаваемой колонки - как системный, так и определяемый пользователем.
IDENTITY[(seed, increment)]
Для колонки с таким свойством сервером автоматически генерируется возрастающая последовательность, начиная с seed и приращением increment.
! Обратите внимание: Сервер не гарантирует непрерывность или уникальность значений - в реальных данных, лежащих в таблице могут появляться разрывы. Для избежания ошибок с уникальностью используйте ограничение PRIMARY KEY

constraint
Может включать в себя ограничение как для столбца, так и для всей таблицы. Всего на таблицу может быть не более одного PRIMARY KEY, не более чем 249 UNIQUE, не более чем 31 FOREIGN KEY (каждый из которых может ссылаться не более чем на 16 колонок), не более одного DEFAULT на колонку, и неограниченное число CHECK. Все эти ограничения могут находиться в одном операторе CREATE TABLE. Синтаксис этих ограничений таков:
Код: выделить все
PRIMARY KEY:
[CONSTRAINT constraint_name]
    PRIMARY KEY [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2 [..., col_name16]])
        [ON segment_name]

UNIQUE:
[CONSTRAINT constraint_name]
    UNIQUE [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2 [..., col_name16]])
        [ON segment_name]

FOREIGN KEY:
[CONSTRAINT constraint_name]
    [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
        REFERENCES [owner.]ref_table [(ref_col [, ref_col2
            [..., ref_col16]])]

DEFAULT:
[CONSTRAINT constraint_name]
    DEFAULT {constant_expression | niladic-function | NULL}
        [FOR col_name]

CHECK:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
Определяет первичный ключ таблицы и тип индекса, который будет для него построен.
!   Обратите внимание: Все колонки, входящие в первичный ключ не могут быть объявлены как NULL. Если же этот параметр при созданни был опущен - для всех колонок принимается NOT NULL.
 
UNIQUE [CLUSTERED | NONCLUSTERED]
Определяет ограничение уникальности для колонки, и указывает тип индекса, создаваемого для этого. Хотя для UNIQUE и можно использовать поля, допускающие значения типа NULL, все же рекомендуется этого не делать.
 По умолчанию созжается некластеризованный индекс.
[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
REFERENCES [owner.]ref_table [(ref_col
[, ref_col2 [..., ref_col16]])]
Создает ограничение FOREIGN KEY для таблицы. Число полей и их тип должны совпадать. FOREIGN KEY не может ссылаться на таблицу, находящуюся в другой базе данных.
!   Обратите внимание: Ограничение FOREIGN KEY, в отличие от PRIMARY KEY, НЕ СОЗДАЕТ ИНДЕКСА.
 
DEFAULT
Указывает значение, используемое по умолчанию. Это должно быть либо константное выражение, в котором допустимо использование т.н. niladic функций. Это
•   USER
•   CURRENT_USER
•   SESSION_USER
•   SYSTEM_USER
•   CURRENT_TIMESTAMP

DEFAULT может использоваться для колонок любых типов, кроме timestamp или тех, для которых указано IDENTITY.
CHECK(expression)
Задает условие проверки
Указанное expression должно вычисляться в булево выражение, и если оно принимает значение FALSE - попытка добавления или изменения данных отвергается. При создании таблицы возможно указать только один CHECK, в дальнейшем же их число можно увеличить.
ON segment_name
Указывает сегмент, который будет использоваться для таблицы.
! Обратите внимание: Если в дальнейшем для этой таблицы будет создан CLUSTERED индекс без указания сегмента - все данные будут перенесены на DEFAULT сегмент.

NB Обратите внимание: Вышеуказанным свойством можно пользоваться для перемещения таблицы по разным дискам и т.п.

Максимальное число байт на строку равно 1962 - размеру страницы.
NB Обратите внимание: Дополнив запись до 1962 байта можно получить строчную блокировку.
Для получения информации о таблице используйте хранимую процедуру sp_help
Пример

Код: выделить все
CREATE TABLE employee
(
    emp_id  empid
        CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
        CONSTRAINT CK_emp_id CHECK (emp_id LIKE
            '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
            emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
        /* Each employee ID consists of three characters that
        represent the employee's initials, followed by a five
        digit number ranging from 10000 to 99999 and then the
        employee's gender (M or F). A - (hyphen) is acceptable
        for the middle initial. */
    fname   varchar(20)     NOT NULL,
    minit   char(1) NULL,
    lname   varchar(30)     NOT NULL,
    job_id  smallint        NOT NULL
        DEFAULT 1
        /* Entry job_id for new hires. */
        REFERENCES jobs(job_id),
    job_lvl tinyint
        DEFAULT 10,
        /* Entry job_lvl for new hires. */
    pub_id  char(4) NOT NULL
        DEFAULT ('9952')
        REFERENCES publishers(pub_id),
        /* By default the Parent Company Publisher is the company
        to whom each employee reports. */
    hire_date       datetime        NOT NULL
        DEFAULT (getdate())
        /* By default the current system date will be entered. */
)
________________________________________
CREATE TRIGGER
 CREATE TRIGGER [owner.]trigger_name
 ON [owner.]table_name
 FOR {INSERT, UPDATE, DELETE}
 [WITH ENCRYPTION]
 AS sql_statements
Или используя предложение IF UPDATE
 CREATE TRIGGER [owner.]trigger_name
 ON [owner.]table_name
 FOR {INSERT, UPDATE}
 [WITH ENCRYPTION]
 AS
 IF UPDATE (column_name)
 [{AND | OR} UPDATE (column_name)...] sql_statements

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

Таблицы INSERTED и DELETED
Когда триггер срабатывает и начинает выполняться, во время его выполнения существуют две специальные таблицы - INSERTED и DELETED. В них находятся записи, соответсвенно добавляемые или удаляемые. Глобальная переменная @@ROWCOUNT указывает на число записей, учавствующих в операциях с данными.
! Обратите внимание: Триггер срабатывает один раз на всю операцию вставки, модификации или удаления данных. Даже если операция реально не затрагивает ни одной записи, как, например, в случае
DELETE FROM PAYS WHERE 1=2
триггер все равно сработает, @@ROWCOUNT будет равен нулю, а таблица DELETED пуста
Невозможно создать триггер на VIEW.
Пример

Код: выделить все
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
    @max_lvl tinyint,
    @emp_lvl tinyint,
    @job_id smallint
SELECT @min_lvl = min_lvl,
    @max_lvl = max_lvl,
    @emp_lvl = i.job_lvl,
    @job_id = i.job_id
FROM employee e, jobs j, inserted i
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
    RAISERROR ('Job id 1 expects the default level of 10.',16,-1)
    ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
    RAISERROR ('The level for job_id:%d should be between %d and %d.',
        16, -1, @job_id, @min_lvl, @max_lvl)
    ROLLBACK TRANSACTION
END
________________________________________
CREATE VIEW
CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

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

CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
________________________________________
Курсоры
Курсоры позволяют обрабатывать данные для каждой возвращаемой строки отдельно, не пользуясь множественными, традиционными для SQL операциями. Эти курсоры - серверные (server-based), и их не следует путать с курсорами, предоставляемыми DB-Library или ODBC. Для работы с курсорами используются следующе операторы:
Оператор Описание
DECLARE CURSOR Создает курсор
OPEN Открывает курсор
FETCH Выбирает данные
CLOSE Закрывает ранее открытый курсор
DEALLOCATE Уничтожает ранее созданый курсор
Для обновления текущей сроки используется следующая форма оператора UPDATE:
Код: выделить все
UPDATE table_name
    SET column_name1 =
            {expression1 | NULL | (select_statement)}
        [, column_name2 =
            {expression2 | NULL | (select_statement)}...]
WHERE CURRENT OF cursor_name
а для удаления - следующая:
DELETE FROM table_name
WHERE CURRENT OF cursor_name
Курсор объявляется оператором DECLARE:
 DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
 FOR select_statement
 [FOR {READ ONLY | UPDATE [OF column_list]}]

Оператор OPEN служит для открытия курсора. Он имеет следующий синтаксис: OPEN cursor_name После открытия курсора в переменной @@CURSOR_ROW может быть одно из следующих значений:
-m
В этом случае данные для курсора выгребаются асинхронно. Значение m представляет число уже выбранных записей.
n
Все данные получены, n - число записей
0
Курсор не открыт
Оператор FETCH служит для получения данных. Он имеет следующий синтаксис:
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM] cursor_name
[INTO @variable_name1, @variable_name2, ...]
NEXT
Получает следующую запись
PRIOR
Получает предыдущую запись
FIRST
Получает первую запись
LAST
Получает последнюю запись
ABSOLUTE
Получает n-ю запись
RELATIVE
Получает n-ю запись относительно текущей
Для того, чтобы опции PRIOR, LAST, FIRST, ABSOLUTE и RELATIVE были доступны, необходимо, чтобы курсор был создан с опцией SCROLL.
Глобальная переменная @@FETCH_STATUS возвращает результат последнего fetch'а -
0
Все нормально, данные получены
-1
Курсор кончился
-2
Текущая строка не является более членом требуемого множества записей, заданного в операторе select при объявлении курсора.
Оператор CLOSE закрывает ранее созданный курсор, однако структуры данных, необходимых для его функционирования остаются нетронутыми; таким образом, курсор позднее может быть открыт вновь.
Оператор DEALLOCATE закрывает курсор и уничтожает все связанные с ним структуры данных.
Example

Код: выделить все
DECLARE @Name varchar(40)
 DECLARE CT CURSOR FOR SELECT Name from Peoples
 OPEN CT
 WHILE 1=1 BEGIN
  FETCH FROM CT INTO @Name
  /*Так, на мой взгляд, удобнее обходиться с курсорами*/
  IF @@fetch_status=-1
      BREAK
  IF @@fetch_status=-2
      CONTINUE
  PRINT @Name
 END
 DEALLOCATE CT
________________________________________
DECLARE
  DECLARE @variable_name datatype
       [, @variable_name datatype...]

Создает локальные переменные для batch'а или процедуры. Также служит для объявления курсоров.
Пример

DECLARE @I INT
________________________________________
DELETE
DELETE [FROM] {table_name | view_name}
[WHERE clause]
Где clause - {search_conditions | CURRENT OF cursor_name}
Ключевое слово IDENTITYCOL может быть использовано вместо имени колонки, имеющей свойство IDENTITY. Оператор TRUNCATE TABLE работает быстрее, чем DELETE, хотя и делает то же самое. Дело в том, что если DELETE удаляет по одной строке, занося данные об этом в журнал транзакций, то TRUNCATE TABLE заносит в журнал только данные об удалении страниц.
В дополнение к стадартному синтаксису Transact-SQL имеет расширение:
Код: выделить все
 DELETE [FROM] {table_name | view_name}
     [FROM {table_name | view_name}
         [, {table_name | view_name}]...]
             [..., {table_name16 | view_name16}]]
 [WHERE clause]

что позволяет использовать при удалении более одной таблицы в WHERE
Пример

Код: выделить все
DELETE FROM titleauthor
    FROM authors a, titles t
        WHERE a.au_id = titleauthor.au_id
        AND titleauthor.title_id = t.title_id
        AND t.title LIKE '%computers%'
________________________________________
DISK INIT
 DISK INIT
     NAME = 'logical_name',
     PHYSNAME = 'physical_name',
     VDEVNO = virtual_device_number,
     SIZE = number_of_2K_blocks
 [, VSTART = virtual_address]

Создает DEVICE с указанным логическим именем и соответствующий файлу с именем physical_name, размером в number_of_2K_blocks. VDEVNO - номер вирутального устройства, может быть любым в промежутке от 1 до 255. Номер 0 зарезервирован для master device
Пример


Код: выделить все
DISK INIT
    NAME = 'DEVICE2',
    PHYSNAME = 'd:sqldatadevice2.dat',
    VDEVNO = 2,
    SIZE = 8192
________________________________________
DISK REINIT
  DISK REINIT
   NAME = 'logical name',
   PHYSNAME = 'physical_name',
   VDEVNO = 'virtual_device_number',
   SIZE = number_of_2K_blocks
   [,VSTART = virtual_address]

Позволяет использовать ранее созданный DEVICE, если по тем или иным причинам о нем нет записи в master database. logical_name - его наименование, physical_name указывает, что это за файл, virtual_device_number - виртуальный номер (см. DISK INIT), number_of_2K_blocks - число двухкилобайтных блоков в нем, virtual_address - виртуальный начальный адрес, по умолчанию 0.
! При некорректном указании размера существовашего ранее DEVICE оно будет разрушено при попытке подключения.
________________________________________
DISK REFIT
DISK REFIT
Используется после DISK REINIT для завершения процесса подключения ранее существовавшего DEVICE.
________________________________________
DROP DATABASE
DROP DATABASE database_name [, database_name...]
Уничтожает базу данных - то есть уничтожает все объекты, относящиеся к ней, освобождает распределенное для нее место на диске и уничтожает все ссылки на нее из master'а. Невозможно уничтожить базу данных, кем-либо используемую в данный момент.
Пример

DROP DATABASE pubs, newpubs
________________________________________
DROP DEFAULT
DROP DEFAULT [owner.]default_name [, [owner.]default_name...]
Уничтожает DEFAULT. Перед тем, как уничтожить DEFAULT, его необходимо предварительно открепить с помощю хранимой процедуры sp_unbindefault от колонок, к которым он был ранее прикреплен. Если данные колонки допускали значение NULL, то оно будет в них вставлено, если же колонка не допускает NULL, вы получите сообщение об ошибке.
Пример

sp_unbindefault 'authors.phone'
go
DROP DEFAULT phonedflt
________________________________________
DROP INDEX
DROP INDEX [owner.]table_name.index_name
[, [owner.]table_name.index_name...]
Уничтожает указанный индекс.
Пример

DROP INDEX authors.au_id_ind
________________________________________
DROP PROCEDURE
DROP PROCedure [owner.]procedure_name [, [owner.]procedure_name...]
Уничтожает процедуру в текущей базе данных. Если процедура является пользовательской системнрй процедурой, т.е. начинается с символов sp_, то она ищется в базе данных master. Если процедура с указанным имененем не найдена в текущей базе данных - она опять-таки ищется в базе данных master.
Группа процедур (т.е. процедуры с одинаковым именем, но с разным параметром ;number могут быть удалены одним оператором DROP

Пример

DROP PROCEDURE byroyalty
________________________________________
DROP RULE
DROP RULE [owner.]rule_name [, [owner.]rule_name...]
Удаляет правило. Для того, чтобы правило можно было удалить, необходимо его предварительно открепить от колонки с помощью хранимой процедуры sp_unbindrule
Пример

sp_unbindrule 'publishers.pub_id'
go
DROP RULE pub_id_rule
________________________________________
DROP TABLE
DROP TABLE [[database.]owner.]table_name
[, [[database.]owner.]table_name...]
Уничтожает таблицу. Невозможно уничтожить таблицу, на которую ссылаются другие таблицы с помощью FOREIGN KEY, кроме того, невозможно уничтожить системную таблицу. Не следует путать операторы DROP TABLE, DELETE и TRUNCATE TABLE - в то время как первый из них уничтожает таблицу, то второй и третий просто удаляют из нее данные.
Пример

DROP TABLE titles1
________________________________________
DROP TRIGGER
DROP TRIGGER [owner.]trigger_name [, [owner.]trigger_name...]
Уничтожает указанный триггер
Пример

DROP TRIGGER employee_insupd
________________________________________
DROP VIEW
DROP VIEW [owner.]view_name [, [owner.]view_name...]
Уничтожает указанный VIEW
Пример

DROP VIEW titles_view
________________________________________
DUMP
DUMP DATABASE {dbname | @dbname_var}
TO dump_device [, dump_device2 [..., dump_device32]]
[WITH options
[[,] STATS [ = percentage]]]

Резеврное копирование журнала транзакций:
DUMP TRANSACTION {dbname | @dbname_var}
[TO dump_device [, dump_device2 [..., dump_device32]]]
[WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE}
{options}]
Создает резервную копию базы данных или журнала транзакций, которые впоследствие могут быть загружены оператором LOAD. При резервном копировании журнала транзакций автоматически удаляется его неактивная часть.
! Обратите внимание: Если после DUMP TRANSACTION уменьшение размеров журнала транзакций не происходит - у вас в нем сидит незакрытая транзакция, для проверки чего используйте DBCC OPENTRAN
При резервном копировании журнала транзакций с параметром TRUNCATE_ONLY реального копирования не происходит - журнал транзакуий просто очищается. Крайне желательно после этого немедленно сделать резервную копию базы данных.
Если же у вас переполнился лог до такой степени, что и TRUNCATE_ONLY не помогает - используйте опцию WITH NO_LOG

Пример

* The next day. */
DUMP TRANSACTION corporate
TO DISK = '\servernamesharenamedirectoryfilename.ext'
WITH NO_LOG
go
________________________________________
EXECUTE
EXEC[ute]
{[@return_status =]
{[[[server.]database.]owner.]procedure_name[;number] |
@procedure_name_var}
[[@parameter_name =] {value | @variable [OUTPUT]]
[, [@parameter_name =] {value | @variable [OUTPUT]}]...]
[WITH RECOMPILE]]

EXEC[ute] ({@str_var | 'tsql_string'} [{@str_var | 'tsql_string'}...)}
Выполняет указанную хранимую процедуру с указанными параметрами, возвращая код возврата. Кроме процедур также можно ивыполнять и строку симоволов
Пример

________________________________________
Выражения
constant | column_name | function | (subquery)}
[{operator | AND | OR | NOT}
{constant | column_name | function | (subquery)}...]
Используются вместе с константами, переменными и именами колонок в большинстве операторов TSQL. Строковые константы представляются как имеющие тип varchar, все выражения, имеющие тип varchar при сравнении с выражениями типа char преобразуются в него же, т.е. к ним добавляются дополнительные пробелы
________________________________________
Функции
Аггрегативные функции
Аггрегативные функции возвращают суммарные значения. Вот список этих функций:
AVG COUNT(*) MIN
COUNT MAX SUM
Функции манипуляции датой и временем:
DATEADD DATENAME GETDATE
DATEDIFF DATEPART
Математические функции
ABS DEGREES RAND
ACOS EXP ROUND
ASIN FLOOR SIGN
ATAN LOG SIN
ATN2 LOG10 SQRT
CEILING PI TAN
COS POWER
COT RADIANS
Niladic-функции
Эти функции возвращают различные системные значения.
CURRENT_TIMESTAMP SYSTEM_USER
CURRENT_USER USER
SESSION_USER
Функции для манипуляции со строками
LTRIM SOUNDEX
ASCII PATINDEX SPACE
CHAR REPLICATE STR
CHARINDEX REVERSE STUFF
DIFFERENCE RIGHT SUBSTRING
LOWER RTRIM UPPER
Системные функции
COALESCE HOST_NAME OBJECT_NAME
COL_LENGTH IDENT_INCR STATS_DATE
COL_NAME IDENT_SEED SUSER_ID
DATALENGTH INDEX_COL SUSER_NAME
DB_ID ISNULL USER_ID
DB_NAME NULLIF USER_NAME
GETANSINULL HOST_ID OBJECT_ID
CONVERT
Функция CONVERT служит для преобразования различных типов данных.
________________________________________
GRANT
Полномочия на выполнение операторов:
GRANT {ALL | statement_list}
TO {PUBLIC | name_list}
Полномочия на объекты:
GRANT {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
TO {PUBLIC | name_list}
Назначает полномочия пользователям.
По умолчанию полномочия даются системному администратору, владельцу базы данных, владельцу объекта или группе PUBLUC. Нектороые из них они могут передать другим пользователям. По умолчанию системный администратор имеет все полномочия, он может использовать хранимую процедуру sp_changedbowner для изменения владельца базы данных. Владелец базы данных или системный администратор могут передавать полномочия на сам оператор GRANT.
Пользователи могут входит только в одну группу, исключая группу PUBLIC.
Для измения прав кроме оператора GRANT существует оператор REVOKE, имеющий следующий синтаксис:
Полномочия на выполнение операторов:
REVOKE {ALL | statement_list}
FROM {PUBLIC | name_list}
Полномочия на объекты:
REVOKE {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
FROM {PUBLIC | name_list
По умолчанию Могут ли быть переданы/изъяты
Оператор Системный администратор Владелец БД Владелец обьекта Public Да Нет Чушь!
ALTER DATABASE - X - -1 - - -
ALTER TABLE - - X - - X -
BEGIN TRANSACTION - - - X - - X
CHECKPOINT - X - - - X -
COMMIT TRANSACTION - - - X - - X
CREATE DATABASE X - - - X - -
CREATE DEFAULT - X - - X - -
CREATE INDEX - - X3 - - X -
CREATE PROCEDURE - X - - X - -
CREATE RULE - X - - X - -
CREATE TABLE - X -2 X2 - - -
CREATE TRIGGER - - X3 - - X -
CREATE VIEW - X - - X - -
DBCC 4 - X - - - X -
DELETE - - X - X - -
DISK INIT X - - - - X -
DISK MIRROR X - - - - X -
DISK REFIT X - - - - X -
DISK REINIT X - - - - X -
DISK REMIRROR X - - - - X -
DISK UNMIRROR X - - - - X -
DROP any object - - X - - X -
DUMP DATABASE - X - - X - -
DUMP TRANSACTION - X - - X - -
EXECUTE 5 - - X - X - -
GRANT - X - - - X -
GRANT on object - - X - - X -
INSERT - - X - X - -
KILL X - - - - X -
LOAD DATABASE - X - - - X -
LOAD TRANSACTION - X - - - X -
PRINT - - - X - - X
RAISERROR - - - X - - X
READTEXT - - X -6 - - -
RECONFIGURE X - - - - X -
REFERENCES - - X - X - -
REVOKE - X - - - X -
REVOKE on object - - X - - X -
ROLLBACK TRANSACTION - - - X - - X
SAVE TRANSACTION - - - X - - X
SELECT - - X - X - -
SET - - - X - - X
SETUSER - X - - - X -
SHUTDOWN X - - - - X -
TRUNCATE TABLE - - X - - X -
UPDATE - - X - X - -
UPDATE STATISTICS - - X - - X -
UPDATETEXT - - X -7 - - -
WRITETEXT - - X -7 - - -

1. Передается вместе с оператором CREATE DATABASE
2. Члены группы PUBLIC имеют полномочия создавать временные таблицы
3. По умолчанию относится к владельцу БД
4. Для определения прав на оператор DBCC см. Transact-SQL Reference, DBCC Statement
5. По умолчанию относится к владельцу хранимой процедуры
6. Передается вместе с полномочиями на SELECT
7. Передается вместе с полномочиями на UPDATE
Пример

Код: выделить все
GRANT SELECT
ON authors
TO public
go
GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom

________________________________________
Идентификаторы
Идентификаторы в TSQL должны состоять из символов латинского алфавита, цифр или из символов _ ,@ , #. Дополнительно существуют следующие правила:
• Все идентификаторы, начинающиеся с @, почитаются за локальные переменные.
• Все идентификаторы, начинающиеся с #, считаются именами временный объектов.
• В противном случае идентификаторы должны начинаться с символа латинского алфавита.
• По умолчанию в имени объектов не могут встречаться пробелы, но, используя режим "Quoted identifiers", это можно обойти. Для более подробного ознакомления с режимимом "Quoted identifiers" см. Transact-SQL Reference.
Для имен объектов необязательно быть уникальными в базе данных, например, имена колонок и индексов должны быть уникальными только в пределах таблицы или представления(view), все же имена других объектов должны быть быть уникальными в пределах базы данных для каждого владельца. Любую колонку или таблицу можно уникально идентифицировать следующим составным именем - имя базы данных, имя владельца, имя таблицы или представления. Промежуточные значения - имя владельца может быть опущено, если это не приводит к конфликтам имен. В случае удаленных хранимых процедур ее имя задается следующим образом: server.database.owner.procedure
Если вы указываете имя объекта не целиком, то сервер сначала пытается найти его среди объектов, которыми владеете вы, после этого производится попытка найти указанный объект как database.dbowner.name. Для определеня видимости хранимых процедур, начинающихся с символов sp_ см. раздел, посвященный оператору CREATE PROCEDURE

________________________________________
INSERT
Код: выделить все
INSERT [INTO]
    {table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}

Добавляет запись в таблицу.
• При указании значений конкретный полей вместо использования каких-либо значений можно использовать ключевое слово DEFAULT
• Вставка пустой строки приводит к добавлению пробела ' ', а не значения NULL
Пример

Код: выделить все
INSERT titles
    VALUES('BU2222', 'Faster!', 'business', '1389',
        NULL, NULL, NULL, NULL, 'ok', '06/17/87')

INSERT titles(title_id, title, type, pub_id, notes, pubdate)
    VALUES ('BU1237', 'Get Going!', 'business', '1389',
        'great', '06/18/86')

 
INSERT INTO newauthors
    SELECT *
        FROM authors
            WHERE city = 'San Francisco'

________________________________________
Ключевые слова
Следующие слова являются зарезервированными и не могут быть использованы для именования объектов в базе данных, за исключением режима Quoted Identifiers
ADD ALL ALTER
AND ANY AS
ASC AVG BEGIN
BETWEEN BREAK BROWSE
BULK BY CASE
CHECK CHECKPOINT CLOSE
CLUSTERED COALESCE COMMIT
COMMITTED COMPUTE CONFIRM
CONSTRAINT CONTINUE CONTROLROW
CONVERT COUNT CREATE
CURRENT CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR
DATABASE DBCC DEALLOCATE
DECLARE DEFAULT DELETE
DESC DISK DISTINCT
DOUBLE DROP DUMMY
DUMP ELSE END
ERRLVL ERROREXIT EXCEPT
EXEC EXECUTE EXISTS
EXIT FETCH FILLFACTOR
FLOPPY FOR FOREIGN
FROM GOTO GRANT
GROUP HAVING HOLDLOCK
IDENTITY IDENTITY_INSERT IDENTITYCOL
IF IN INDEX
INSENSITIVE INSERT INTERSECT
INTO IS ISOLATION
KEY KILL LEVEL
LIKE LINENO LOAD
MAX MIN MIRROREXIT
NOCHECK NONCLUSTERED NOT
NULL NULLIF OF
OFF OFFSETS ON
ONCE ONLY OPEN
OPTION OR ORDER
OVER PERM PERMANENT
PIPE PLAN PRECISION
PREPARE PRIMARY PRINT
PROC PROCEDURE PROCESSEXIT
PUBLIC RAISERROR READ
RECONFIGURE REFERENCES REPEATABLE
REPLICATION RETURN REVOKE
ROLLBACK ROWCOUNT RULE
SAVE SCROLL SELECT
SERIALIZABLE SESSION_USER SET
SETUSER SHUTDOWN SOME
STATISTICS SUM SYSTEM_USER
TABLE TAPE TEMP
TEMPORARY TEXTSIZE THEN
TO TRAN TRANSACTION
TRIGGER TRUNCATE TSEQUAL
UNCOMMITTED UNION UNIQUE
UPDATE UPDATETEXT USE
USER VALUES VARYING
VIEW WAITFOR WHEN
WHERE WHILE WITH
WRITETEXT
________________________________________
KILL
KILL spid
Уничтожает процесс с заданным spid. Для просмотра списка процессов и их spid используйте хранимую процедуру sp_who.
Пример

KILL 15
________________________________________
LOAD
Загрузка базы данных:
LOAD DATABASE {dbname | @dbname_var}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options
[[,] STATS [ = percentage]]]

Загрузка журнала транзакций:
LOAD TRANSACTION {dbname | @dbname_var}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options]

Загрузка информации о резервной копии:
LOAD HEADERONLY
FROM dump_device
Оператор LOAD служит для загрузки ранее сделанной с помощью оператора DUMP резервной копии базы данных (LOAD DATABASE) или ее журнала транзакций(LOAD TRANSACTION). Во время восстановления базы данных она не должна использоваться, и все данные, ранее находящиеся в ней, будут уничтожены и заменены загружаемыми.
Кроме восстановления после какого-либо сбоя и прочего несчаcтья (пожар, падение Тунгусского метеорита) можно также использовать LOAD DATABASE для переноса базы данных с различных устройств или серверов на другие.
! Обратите внимание: оба сервера должны иметь одинковую кодовую страницу и порядок сортировки (sort order). Также необходимо, чтобы размер базы данных на том сервере, нак котором производится загрузка данных был не меньше, чем на исходном. Необходимо также следить за соблюдением количества и порядка фрагменов DEVICES, например, если исходная база данных имела 10 мегабайт данных, за которыми следовало 6 мегабайт журнала транзакций, за которыми следовало еще 10 мегабайт данных, то и принимающая база данных должна иметь те же особенности.
Если при создании исходной базы данных использовались сегменты, то и у базы данных, в которую производится загрузка, должны быть те же сегменты и DEVICE's должны иметь тот же размер.
Для получения информации базы данных - их размере и фрагментах - используйте хранимую процедуру sp_helpdb, и системную таблицу sysusages для определения порядка следования фрагментов.
Для подробного описания фрагментов DEVICES и работы с ними см. CREATE DATABASE Statement в Transact-SQL Reference.
Для более подробного описания процедур резервного копирования см. соответсвующий раздел в Database Administration Companion.
Пример

LOAD DATABASE corporate
FROM tape_dev1
LOAD DATABASE corporate
FROM TAPE = '\.tape0'
________________________________________
NULL
Значения типа NULL служат для представления пустых значений, не следует путать их с пустой строкой или с 0 в числовых полях, NULL подразумевает, что значение неизвестно или неопределено.
Следует обратить внимание на то, что если вы создаете таблицу с колонками, допускающими NULL, то значение будут внутрисерверно преобразовываться в соответствии со следующей таблицей:
Тип данных Преобразуется в
binary varbinary
char varchar
datetime datetim
decimal decimal
float float
int int
money money
numeric numeric
real float
smalldatetime datetim
smallint int
smallmoney money
tinyint int
Аггрегативные функции игнорируют значения NULL, за исключением функции count(*). Значения NULL считаются дублирующимися при использовании в GROUP BY, ORDER BY, или при использовании DISTINCT.
________________________________________
Операции
SQL Server допускает следующие операции
Сложение +
Вычитание -
Умножение *
Деление /
Побитное И &
Побитное ИЛИ |
Побоитное НЕ ~
Исключающее ИЛИ ^
Равно =
Больше >
Меньше <
Больше или равно >=
Меньше или равно <=
Неравно <> или != (нестандартно!)
Не больше !>
Не меньше !<
LEFT OUTER JOIN *=
RIGHT OUTER JOIN =*
Конкатенация строк +
________________________________________
PRINT
PRINT {'any ASCII text' | @local_variable | @@global_variable}
Возвращает сообщение, определяемое пользователем, пользовательскому обработчику сообщений. Длина строки с сообщением не должна превышать 255 символов. Глобальной переменной в настоящее время можеть быть только переменная @@VERSION, так как только она является глобальной и одновременно символьной переменной.
Пример

IF EXISTS (SELECT zip
FROM authors
WHERE zip = '94705')
PRINT 'Berkeley author'

________________________________________
RAISERROR
RAISERROR ({msg_id | msg_str}, severity, state
[, argument1 [, argument2]] )
[WITH LOG]
Возвращает пользовательское сообщение об ошибке и устанавливает систменый флаг, указывающий на то, что произошла ошибка. severity определяет важность ошибки, которые можно посмотреть в System Administration Companion. Только системный администратор может использовать RAISERROR с severity от 19 до 25
Пример
REATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e, jobs j, inserted i
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.',16,-1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, -1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
________________________________________
REVOKE
Полномочия на выполнение операторов:
REVOKE {ALL | statement_list}
FROM {PUBLIC | name_list}
Полномочия на объекты базы данных:
REVOKE {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
FROM {PUBLIC | name_list}
Оператор REVOKE служит для отъятия различных прав у пользователей. При определении прав они сохраняются в системной таблице sysprotecs.
! Обратите внимание: полномочия, даваемые группе public, являются более значимые, чем полномочия, опрделенные ранее для некоторого пользователя или группы пользователей. Порядок выдачи полномочий имеет значение: только полномочия, выданные последними, имеют эффект. Таким образом, старайтесь сначала выдавать полномочия группе public, и далее по нисходящей
Более подробно см. GRANT
Пример

REVOKE CREATE TABLE, CREATE DEFAULT
FROM Mary, John
________________________________________
Условия поиска
{WHERE | HAVING} [NOT] Boolean_expression
{WHERE | HAVING} [NOT] Boolean_expression {AND | OR} Boolean_expression
{WHERE | HAVING} [NOT] column_name IS [NOT] NULL
{WHERE | HAVING} [NOT] column_name join_operator column_name
{WHERE | HAVING} [NOT] column_name [NOT] LIKE 'match_string'
{WHERE | HAVING} [NOT] EXISTS (subquery)
{WHERE | HAVING} [NOT] expression comparison_operator expression
{WHERE | HAVING} [NOT] expression [NOT] BETWEEN expression AND expression
{WHERE | HAVING} [NOT] expression [NOT] IN (value_list | subquery)
{WHERE | HAVING} [NOT] expression comparison_operator {ANY | ALL} (subquery)
{WHERE CURRENT OF cursor_name}
Определяет условия поиска, используемые для получения или модификации данных. Если вы используете более одного условия, соединяйте их логическими операторами AND и OR. При использовании LIKE с датами будьте внимательны: SQL Server преобразует даты к стандартному виду для дат, и поэтому дата 01.01.1997 будет представлена в виде Jan 1, 1997 12:00AM. Так что придется искать с % по обеим сторонам шаблона.
________________________________________
Код: выделить все
SELECT
SELECT [ALL | DISTINCT] select_list
    [INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
    [[, {table_name2 | view_name2}[(optimizer_hints)]
    [..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

Производит выборку данных из БД или присваивает значения переменным, причем выборка данных и присваивание значений не могут производиться одновременно. При установленной опции select into/bulkcopy сервера после ключевого слова INTO может идти имя новой таблицы - она будет создана и в нее будут помещены данные, выбираемые этим оператором; если же эта опция не установлена, то в качестве таблицы-получателя может выступать только временная таблица.
! Впрочем, этой особенностью не стоит пользоваться - при больших выборках происходит смертельная блокировка таблиц tempdb..sysobjects, tempdb..sysindexes и tempdb..syscolumns,и, таким образом, всего tempdb, причем настолько здорово, что сервер приходится останавливать с помощью команды SHUTDOWN WITH NOWAIT.
Для более точной настройки пути выборки данных можно указывать т.н. optimizer hints - подсказки оптимизатору. Вот они:
INDEX = {index_name | index_id}
Указывает индекс, который будет использован для выборки данных из такблицы. Если указать вместо имени или идентификатора индекса 0, то будет произведено сканирование таблицы.
NOLOCK
Включает режим "грязного чтения",
HOLDLOCK
Указывает, что блокировки следует сохранять до конца транзакции.
UPDLOCK
Блокирует "блокировками обновления", а не разделяемыми блокировками, сохраняя их до конца транзакции.
TABLOCK
Блокирует таблицу в разделяющем режиме. Если используется вместе с HOLDLOCK, то блокировки сохраняюся до конца транзакции.
PAGLOCK
Блокирует разделяемой блокировкой на страничном уровне в тех случаях, когда блокировка была бы на всю таблицу.
TABLOCKX
Блокирует таблицу в исключающем режиме до конца BATCH или до конца транзакции.
FASTFIRSTROW
Указвает оптимизатору, что необходимо использовать некластеризованный индекс (конечно, если существует подходящий) для выборки данных в порядке, задаваемом ORDER BY даже в том случае, если оптимизатор, сравнив затраты на выборку с помощью индекса и произвольной выборкой и последующей сортировкой находит более выгодым использование сортировки.
В одном операторе SELECT не может участвовать более 16 таблиц, включая подзапросы.
________________________________________
SET
Код: выделить все
SET {
{{{ANSI_NULL_DFLT_OFF | ANSI_NULL_DFLT_ON}
| ARITHABORT
| ARITHIGNORE
| FMTONLY
| FORCEPLAN
| IDENTITY_INSERT [database.[owner.]]tablename
| NOCOUNT
| NOEXEC
| OFFSETS {keyword_list}
| PARSEONLY
| PROCID
| QUOTED_IDENTIFIER
| SHOWPLAN
| STATISTICS IO
| STATISTICS TIME}
    {ON | OFF}}
| DATEFIRST number
| DATEFORMAT format
| DEADLOCKPRIORITY {LOW | NORMAL}
| LANGUAGE language
| ROWCOUNT number
| TEXTSIZE number
| TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ
    UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}}

Позволяет изменить различные установки сервера.
ANSI_NULL_DFLT_OFF
Устанавливает режим обработки значений NULL, несовместимый с ANSI SQL. Колонки во вновь создаваемых таблицах создаются как NOT NULL.
ANSI_NULL_DFLT_ON
Отменяет режим, устанавливаемый ANSI_NULL_DFLT_OFF
ARITHABORT
Остававливает запрос, если во время его выполнения встретилась арифметическая ошибка, такая как деление на ноль или переполнение
ARITHIGNORE
Указывает игнорировать возникающие при выполнении запроса арифметические ошибки. Если не установлена ни эта, ни предыдущая опция - сервер возвращает NULL вместо значений, при выполнении которых возникла ошибка.
FMTONLY
Возвращает только описание колонок, получающихся при выполнении запроса.
FORCEPLAN
Подавляет оптимизатор и указывает ему использовать порядок соединения таблиц, указываемый в запросе.
IDENTITY_INSERT [database.[owner.]]tablename
Разрешает вставку данных в колонку со свойством IDENTITY в указанной таблице.
NOCOUNT
Подавляет выдачу сообщений о количестве строк таблицы, получающейся в качестве запроса. Глобальная переменная @@ROWCOUNT обновляется даже когда установлена опция NOCOUNT.
NOEXEC
Указывает только скомпилировать запрос, но не выполнять его.
OFFSETS
Возвращает смещение keyword_list. Используется только вместе с DB-Library
PARSEONLY
Отменяет компиляцию и выполнение запроса, только проверяет синтаксис.
PROCID
Возвращает ID хранимой процедуры клиентской части запроса. Используется вместе с DB-Library.
QUOTED_IDENTIFIER
Указывает, что в текущей сессии ' и " различны. Строки в двойных кавычках (") считаются ключевыми словами или именами объектов.
SHOWPLAN
Возвращает вместе с данными план выполнения запроса.
STATISTICS IO
Сообщает статистику по вводу-выводу.
STATISTICS TIME
Сообщает статистику по времени.
DATEFIRST number
Устанавливает первый день недели. По умолчанию стоит америкоидный - неделя начинается с воскресенья.
DATEFORMAT format
Устанавливает формат даты. Формат может быть mdy, dmy, ymd, ydm, myd, and dym. По умолчанию стоит америкоидный - mdy.
(все у них не как у людей!)
DEADLOCKPRIORITY {LOW | NORMAL}
Устанавливает режим убиваемости запросов в текущей сессии при взаимоблокировках. При LOW процесс считается наилучшей жертвой при их разрешении.
LANGUAGE language
Устанавливает язык сообщений об ошибках. По умолчанию, само собой, английский.
ROWCOUNT number
Ограничивает число строк таблиц, участвующих в запросе. Обратите внимание: - это относится ко всем таблицам, участвующим в запросе!
TEXTSIZE number
Устанавливает размер возвращаемый размер данных типа text. По умолчанию - 4 килобайта.
TRANSACTION ISOLATION LEVEL
Устанавливает уровень изолированности транзакций Допустимые значения:
• READ COMMITED
• READ UNCOMMITED
• REPEATABLE READ | SERIAZABLE
Если вы используете оператор SET в хранимой процедуре, триггере или в BATCH'е, то все параметры, изменяемые им, изменяются им только в пределах процедуры/триггера или BATCH'а.
________________________________________
SETUSER
SETUSER ['username' [WITH NORESET]]
Представляет sa в текущей сессии как пользователя username. Если опущены все параметры, то sa вновь становится sa, если предварительно он использовал этот оператор как SETUSER username. При предварительном указании опции WITH NORESET такая возможность становится недоступной. Пример

Код: выделить все
SETUSER 'mary'
go
GRANT SELECT ON computer_types TO joe
go
SETUSER

________________________________________
SHUTDOWN
SHUTDOWN [WITH NOWAIT]
Завершает работу SQL Server. Если используется опция WITH NOWAIT, то это случается нещамедлительно - не происходит ожидания конца работы запросов, не берется финальная контрольная точка. Пример

SHUTDOWN
________________________________________
Подзапросы
expression comparison_operator [ANY | ALL | SOME] (subquery)expression [NOT] IN (subquery)[NOT] EXISTS (subquery)


Код: выделить все
(SELECT [ALL | DISTINCT] subquery_select_list
[FROM {table_name | view_name}[optimizer_hints]
    [[, {table_name2 | view_name2}[optimizer_hints]
    [..., {table_name16 | view_name16}[optimizer_hints]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause])

Позапросы могут быть использованы помимо всего прочего и как выражения. Пример

Код: выделить все
 declare @a varchar(80)
 select @a=(select first_name from employee ...)+
            ' получает '+
           (select convert(sum(pay)) from pays...)

________________________________________
TRUNCATE TABLE
TRUNCATE TABLE [[database.]owner.]table_name
Уничтожает данные в таблице, но оставляет ее структуру и индексы нетронутыми, делает то же самое, что и DELETE FROM, только быстрее. Да, не забудте сказать UPDATE STATISTICS после TRUNCATE TABLE. Пример

Код: выделить все
TRUNCATE TABLE authors
________________________________________
UNION
SELECT select_list [INTO clause]
    [FROM clause]
    [WHERE clause]
    [GROUP BY clause]
    [HAVING clause]
[UNION [ALL]
SELECT select_list
    [FROM clause]
    [WHERE clause]
    [GROUP BY clause]
    [HAVING clause]...]
[ORDER BY clause]

[COMPUTE clause]
Объединяет результаты нескольких запросов в один. Имена колонок берутся из первого запроса. Колонки объединяются по порядку; они должны иметь одинаковый тип или приводится к одному типу. Пример

Код: выделить все
/* CORRECT */
SELECT cities = city FROM storeseast
UNION
SELECT city FROM stores
ORDER BY cities

________________________________________
UPDATE
UPDATE {table_name | view_name}
SET [{table_name | view_name}]
{column_list
| variable_list
| variable_and_column_list}
[, {column_list2
| variable_list2
| variable_and_column_list2}
... [, {column_listN
| variable_listN
| variable_and_column_listN}]]
[WHERE clause]
Этот оператор служит для обновления данных. Обратите внимание на то, что в если удается произвести т.н. update in-place, то производительность обновлений заметно возрастает. Это происходит при выполнении следующих условий:
• Изменяемые колонки не входят в кластеризованный индекс
• У таблицы не существует триггера на обновление
• Для обновления одной строки:
1. Размер обновленной записи должен совпадать с размером необновленнной.
2. Если на изменяемые колонки построен индекс, то он должен быть неуникальным некластеризованным и колонки имеют фиксированную длину.
3. Обновляемые колонки могут находиться в некластеризованном уникальном индексе в том случае, если в условии WHERE было заданно точное соответствие и для поиска обновляемой строки был использован именно этот индекс.
4. Следующая строка должна отличаться от обновляемой не более чем на 50% по размеру и иметь общее число discontiguous differing bytes меньшее или равное 24
5. Обновляемая строка должна регулярно смазываться елеем, освященным епископом и не содержать богохульных или антисоветских данных.
• Для обновления многих строк они должны отвечать пунктам 1-3 и не иметь колонки типа timestamp
Transact-SQL имеет следующее расширение стандартного синтаксиса UPDATE:
UPDATE {table_name | view_name}
SET [{table_name | view_name}]
{column_list
| variable_list
| variable_and_column_list}
[, {column_list2
| variable_list2
| variable_and_column_list2}
... [, {column_listN
| variable_listN
| variable_and_column_listN}]]
[FROM {table_name | view_name}
[, {table_name | view_name}]...]
[..., {table_name16 | view_name16}]]
[WHERE clause]
что позволяет несколько более изящно проделывать различные операции.
Ранее было невозможно одновременно получить старое значение колонки и присвоить ему новое, для этого приходилось использовать что-то вроде
Код: выделить все
BEGIN TRANSACTION
SELECT variable_name = column_name1
    FROM table_name
        WHERE column_name2 = expression
            HOLDLOCK
UPDATE table_name
    SET column_name1 = expression
        WHERE column_name2 = expression
COMMIT TRANSACTION
теперь же можно поступать следующим образом:
UPDATE table_name
    SET column_name1 = expression, variable_name = column_name1
        WHERE column_name2 = expression
Пример
 
UPDATE titles
    SET ytd_sales =
        (select sum(qty)
            FROM sales
                WHERE sales.title_id = titles.title_id
                    AND sales.date IN (SELECT MAX(date) FROM sales))
    FROM titles, sales

________________________________________
UPDATE STATISTICS
Код: выделить все
UPDATE STATISTICS [[database.]owner.]table_name [index_name]

Обновляет статистические данные о распределении ключей в индексе. Эти данные используются оптимизатором для поиска наиболее выгодного плана, поэтому весьма важно не забывать периодически обновлять статистику; впрочем, для автоматического обновления можно воспользоваться SQL Executive, а в Transact-SQL Reference приведен пример полезной хранимой процедуры, обновляющей статистические данные для всех пользовательских таблиц. Эти данные автоматически обновляются при перестроении индекса. Утилита SQLMAINT.EXE предоставляет интерфейс командной строки для того же.
________________________________________
USE
USE database_name
Изменяет текущую для данной сессии базу данных.
Пример

Код: выделить все
 USE pubs



Аватар пользователя
 
Сообщений: 19
Зарегистрирован: 01 апр 2013, 09:31

Работа с датами на SQL

Сообщение Еремин » 17 авг 2013, 08:09

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

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

Как вы уже знаете, существует множество различных реализаций SQL. В этой книге рассмотрен стандарт ANSI и общие для многих реализаций языка функции, команды и операторы Для примеров выбрана реализация Oracle Но даже в рамках Oracle даты могут храниться в различных форматах. Уточнить особенности хранения дат можно по документации. Но, независимо от формы хранения дат, каждая конкретная реализация языка должна предлагать вам функции, позволяющие преобразовать даты из одного формата в другие.
Как хранятся даты?
В каждой реализации языка есть формат хранения дат, принятый по умолчанию. Такие форматы для различных реализаций SQL часто оказываются разными, но то же самое можно сказать и о форматах хранения других типов данных. В следующих разделах рассматривается стандартный формат и его составляющие для данных типа DATETIME- После этого мы с вами ознакомимся с особенностями представления дат и времени в основных реализациях SQL, таких как Oracle, Sybase и Microsoft SQL Server.

Стандартные типы данных для дат и времени
Имеется три стандартных типа данных SQL, предназначенных для хранения дат и времени (DATETIME).
Тип данных____ Использование
DATE Для хранения значений дат
Т1МЕ Для хранения значений времени
TIMESTAMP Для хранения значений дат и времени
Формат и диапазон допустимых значений для этих типов данных следующий.
DATE_______________
Формат: гпт-ММ-дд
Диапазон допустимых значений: от 0001-01-01 до 9999-12-31
TIME___________
Формат: чч: мм: сс.пп
Диапазон допустимых значений: от 00:00:00... до 23:59:61.999...
TIMESTAMP_________
Формат: гггг-ММ-ДД чч: мм: сс.пп
Диапазон допустимых значений: от 0001-01-01 ОО'ОО'ОО ' до 9999-12-31 23:59:61.999...
Компоненты типа DATETIME
Компонентами типа данных DATETIME являются элементы, из которых складываются значения дат и времени. Вот список отдельных компонентов типа DATETIME вместе с диапазонами допустимых значений.
YEAR (ГОД) ОТ 0001 ДО 9999
MONTH (месяц) от 01 до 12
DAY (день) от 01 до 31
HOUR (час) от оо до 23
MINUTE (минута) от оо До 59
SECOND (секунда) от о.000... до 61.999... Секунды можно представить в виде десятичных чисел с десятыми, сотыми частями секунды, миллисекундами и т. д. С указанными здесь компонентами, кроме последнего, вопросов возникать не должно, поскольку они представляют компоненты дат и времени, используемые в повседневной жизни. Удивление может вызвать лишь допущение, что в минуте может содержаться более 60 секунд. В соответствии со стандартом ANSI число 61.999 Для секунд возникает из-за возможного добавления или пропуска секунд при корректировке точного времени, что само по себе является достаточно редким явлением. Чтобы уточнить диапазон допустимых значений, обратитесь к документации по используемой вами реализации языка, поскольку по вопросам хранения дат и времени у производителей единого мнения нет.

Вариации представления конкретных типов данных

Как и для других типов данных, разные реализации SQL предлагают свои представления и синтаксис. В этом разделе мы поговорим о том, как с датами и временем обращаются Oracle, Sybase и SQLBase.
Продукт___Тип данных_____Использование_______________________
Oracle DATE Хранит дату и время вместе
Sybase DATETIME Хранит дату и время вместе
SMALLDATETIME Хранит дату и время вместе, но допускает более
узкий диапазон для дат, чем DATETIME
SQLBase DATETIME Хранит дату и время вместе
TIMESTAMP Хранит дату и время вместе
DATE Хранит дату
TIME Хранит время
Каждая реализация языка предлагает свои типы данных для дат и времени, но чаще всего реализации поддерживают стандарт ANSI в том смысле, что все компоненты даты и времени включены в соответствующие типы данных Но то, в каком внутреннем формате хранятся значения дат и времени, зависит от конкретной реализации языка
Функции для работы с датами
Функции для работы с датами в SQL зависят от конкретной реализации языка. Функции для работы с датами используются для управления представлением значений дат и времени, подобно тому, как с символьными строками используются строковые функции. Функции для работы с датами и временем часто используются для того, чтобы представить соответствующие данные в формате, удобном для чтения, сопоставления, выполнения с ними операций и т. п.

Текущая дата
Наверное, вы уже подумали: "Как получить из базы данных информацию о текущем времени?" Такая необходимость может возникнуть в самых разных ситуациях, но обычно текущая дата бывает необходима для сравнения со значениями дат, хранящимися в базе данных, или для использования текущей даты в качестве временной метки.
Текущая дата отслеживается главным (управляющим) компьютером базы данных и называется системной датой. При работе с базой данных посредством использования определенной операционной системы имеется возможность извлечь системную дату для внутренних потребностей самой базы данных или для использования этой даты в различных операциях типа запросов.
Рассмотрим несколько методов извлечения системной даты с использованием команд разных реализаций языка.
В Sybase для получения системной даты используется функция GETDATE() • В запросах эта функция используется следующим образом (в данном случае текущей датой оказался новогодний вечер конца 1999 года).
SELECT GETDATEO
Dec 31, 1999
Обсуждаемые в этой книге возможности реализаций языка от Sybase и Microsoft применимы в обеих этих реализациях, поскольку обе они в качестве сервера своих баз данных используют SQL Server. Обе эти реализации языка используют одно и то же расширение стандартного SQL, известное как Transact-SQL.
В Огас1едля извлечения системной даты используется то, что в нем называется псевдостолбцом SySDATE- Обращаться с SySDATE можно как с любым другим столбцом в таблице и выбрать его можно из любой таблицы базы данных, хотя он и не является частью определения таблицы.
Для извлечения системной даты в Oracle используется следующий оператор.
SELECT SYSDATE FROM ИМЯ_ТАБЛИЦЫ;
31-DEC-99

Часовые пояса

При работе с датами и временем может возникнуть необходимость учитывать различия во времени для разных часовых поясов. Например, 6 часов вечера в США не соответствует 6 часам вечера в Австралии, хотя с точки зрения мирового времени рассматривается один момент. Там, где используется летнее и зимнее время, приходится корректировать показания часов дважды в год. Если это необходимо, имеется возможность учесть различия во времени или осуществить необходимую корректировку времени с помощью подходящих операторов SQL, если таковые соответствующей реализацией языка предусмотрены.
Вот список некоторых из часовых поясов с принятыми для них аббревиатурами.
Сокращение____ Расшифровка_________
AST, ADT Атлантическое (нью-йоркское) поясное время, летнее время
BST, ВDT Берингово поясное время, летнее время
СSт, CDT Центральное поясное время, летнее время
EST, EDT Восточное пояснее время, летнее время
GMT Всемирное (гринвичское среднее) время или время по Гринвичу
HST, НОТ Гавайское поясное время, летнее время
NST Ньюфаундлендское поясное время
PST, PDT Тихоокеанское время, летнее время
YST, YDT Юкона поясное время, летнее время
В некоторых реализациях SQL имеются средства для учета поясного времени. Уточните по документации имеются ли в используемой вами реализации языка возможности для учета поясного времени, а также вообще оцените необходимость учета поясного времени в вашей конкретной базе данных.

Добавление интервалов времени к датам

К датам можно добавлять дни, месяцы и другие компоненты времени для сравнения дат или, например, для того, чтобы уточнить условия в выражении ключевого слова WHERE в запросе.
Интервалы времени можно добавлять к значениям типа DATETIME. В соответствии со стандартом интервалы времени должны использоваться так, как показано в следующих примерах.
DATE 4999-12-31' + INTERVAL Ч' DAY
'2000-01-01'
DATE 4999-12-31' + INTERVAL '!' MONTH
'2000-01-31'
В следующем примере используется функция DATEADD SQL Server.
SELECT DATEJHIRE, DATEADD(MONTH, 1, DATE_HIRE)
FROM EMPLOYEE_PAY_TBL
DATE_HIRE DATEADD(M)
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91
6 rows affected.
В следующем примере используется функция ADD_MONTH Oracle.

Код: выделить все
 SELECT DATEJHIRE, ADD_MONTH(DATE_HIRE,1) 
FROM EMPLOYEE_PAY_TBL;
 DATE_HIRE ADD_MONTH
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91
6 rows selected. 

Чтобы добавить к дате один день в Oracle, используется следующий оператор.
Код: выделить все
 SELECT DATE_HIRE, DATE_HIRE + 1 
FROM EMPLOYEE_PAY_TBL 
WHERE EMP_ID = '311549902';
 DATE_HIRE DATE_HIRE
23-MAY-89 24-MAY-89 1 row selected.

Обратите внимание на то, что эти примеры из Oracle и SQL Server хотя и отличаются синтаксически от стандарта ANSI, по сути базируются на тех же принципах, что и стандарт SQL.
Сравнение дат и значений времени
Очень полезным условным оператором стандарта SQL для значений типа DATETIME является оператор OVERLAPS. Оператор OVERLAPS используется для сравнения двух отрезков времени и возвращает TRUE (Истина), если эти отрезки времени пересекаются, и FALSE (Ложь) — если нет. Например, в результате следующего сравнения возвращается значение TRUE:
(TIME '01:00:00', TIME '05:59:00')
OVERLAPS
(TIME '05:00:00', TIME '07:00:00')
В результате следующего сравнения возвратится значение FALSE:
(TIME '01:00:00', TIME '05:59:00')
OVERLAPS
(TIME '06:00:00', TIME '07:00:00')

Другие функции для работы с датами

В следующем списке представлены некоторые полезные функции для работы с датами, предлагаемые реализациями Oracle и SQL Server.
SQL Server___________
DATE PART Возвращает числовое (целое) значение DATE PART для даты
DATENAME Возвращает текстовое значение DATEPART для даты
GETDATE () Возвращает системную дату
DATEDIFF Возвращает разность двух дат для указанных компонентов,
таких как дни, минуты или секунды
Oracle______________
NEXT_DAY Возвращает для указанной даты следующий день недели в
заданном виде(например, ПЯТНИЦА)
MONTH_BETWEEN Возвращает число месяцев между двумя заданными датами

Преобразования дат
Преобразования дат могут понадобиться по ряду причин. Главным образом, преобразования дат используются для изменения типа данных, изначально определенных по умолчанию как DATETIME или как-нибудь иначе в зависимости от реализации SQL.
Вот несколько типичных причин, по которым применяются преобразования дат:
• необходимость сравнения значений дат разных типов;
• необходимость представления значений дат в виде строк заданного формата;
• необходимость конвертирования символьных строк в формат даты.
Для конвертирования одних типов данных в другие используется ANSI-оператор CAST. Его базовый синтаксис следующий.
CAST ( выражение AS новый тип данных )
Конкретные примеры использования этого операторы будут показаны в следующих разделах.
Шаблоны представления дат
Шаблон представления даты (date picture) состоит из элементов форматирования и используется для извлечения из базы данных информации о дате и времени в требуемом виде. В некоторых реализациях SQL использование шаблонов представления дат не предусмотрено.
Без использования форматирования информация о дате и времени извлекается из базы данных в определенном принятом по умолчанию виде, например,
1999-12-31
31-DEC-99
1999-12-31 23:59:01.11
Но как быть, если дату нужно представить, например, в следующем виде?
December 31, 1999
Тогда необходимо конвертировать дату из формата DATETIME в символьный. Это делается с помощью соответствующих функций, применение которых будет рассматриваться в следующих разделах.
Шаблоны представления дат Sybase

Код: выделить все
УУ                        год
qq                         квартал
mm                       месяц
dy                         день года
wk                        неделя
dw                        день недели
hh                         час
mi                         минута
ss                         секунда
ms                        миллисекунда


Шаблоны представления дат Oracle

Код: выделить все
AD                       Н. Э.
AM                      ДО ПОЛУДНЯ
ВС                       ДО Н. Э.
CC                          столетие
D                           номер дня недели
DD                         номер дня месяца
DDD                      номер дня года
DAY                      название дня недели (MONDAY)
Day                        название дня недели (Monday)
day                         название дня недели (monday)


Шаблоны предстявления дат Oracle
Код: выделить все
DY 
Dy 
dy 
HH 
НН12 
НН24
 J 
MI 
MM 
MON 
Mon 
mon 
MONTH 
Month 
month 
PM 

KM 
RR
 3 
SSSSS 
SYYYY 

WW 

YY 
YYY 
YYYY 
YEAR 
Year 
year   трехбуквенное сокращение для дня недели (MON) 
трехбуквенное сокращение для дня недели (Моп) 
трехбуквенное сокращение для дня недели (топ) 
час дня 
час дня 
час дня в 24-часовом формате 
день по юлианскому календарю с 12-31-4713 до н. э. 
минута часа 
номер месяца 
трехбуквенное сокращение для месяца (JAN) 
трехбуквенное сокращение для месяца (Jan) 
трехбуквенное сокращение для месяца (Jan) 
название месяца (JANUARY)
название месяца (January)
название месяца (January) 
после полудня 
номер квартала 
римский номер месяца 
две цифры года 
секунда минуты 
число секунд со времени полуночи 
год со знаком: например, 500 г. до н. э. = -500 
номер недели месяца 
номер недели года 
последняя цифра года 
последние две цифры года 
последние три цифры года 
год 
год словами (NINETEEN-NINETY-NINE)
год словами (Nineteen-Ninety-Nine) 
год словами (nineteen-ninety-nine)


Преобразование дат в строки символов

Значения типа DATETIME конвертируют (преобразуют) в символьные строки для того, чтобы иметь возможность изменить представление дат при выводе данных запроса. Для этого используются функции преобразования. Вот пример преобразования значения даты и времени в символьную строку в запросе SQL Server:
Код: выделить все
SELECT DATE_HIRE = DATENAME(MONTH, DATE_HIRE) 
FROM EMPLOYEE_PAY__TBL
 DATE_HIRE
May 
June
August 
June 
July 
January
6 rows affected. 


В следующем примере для преобразования используется функция TO_CHAR из Oracle:

Код: выделить все
SELECT DATE_HIRE, TO_CHAR(DATE_HIRE,'Month dd, yyyy') HIRE 
FROM EMPLOYEE_PAY_TBL;
 DATE_HIRE HIRE
23-MAY-89    May 23, 1989
17-JUN-90  June 17, 1990
14-AUG-94  August 14, 1994
28-JUN-97  June 28, 1997
22-JUL-96   July 22, 1996
14-JAN-91   January 14, 1991
6 rows selected.


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

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

Код: выделить все
SELECT TO_DATE('JANUARY 01 1999','MONTH DD YYYY') 
FROM EMPLOYEE_PAY_TBL;
 TO_DATE('
Ol-JAN-99 
Ol-JAN-99 
Ol-JAN-99 
Ol-JAN-99 
Ol-JAN-99 
Ol-JAN-99
6 rows selected.


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

Мы получили представление о значениях типа DATETIME, определяемых на базе стандарта ANSI. Но как и в случае многих других элементов SQL, в большинстве реализаций языка имеются отклонения имен функций и их синтаксиса от стандарта SQL, хотя лежащие в их основе концепции определяются стандартом. На предыдущем уроке вы видели, как варьируют в зависимости от реализации языка предлагаемые функции, а в ходе этого урока вы получили возможность увидеть подобные отклонения для типов даты и времени и используемых с ними функций и операторов. Не забывайте о том, что не все предлагаемые здесь примеры будут работать в рамках используемой вами реализации SQL, но вы должны понять принципы работы со значениями дат и времени, которые применимы в любой реализации языка.



Аватар пользователя
 
Сообщений: 19
Зарегистрирован: 14 июн 2016, 19:30
Откуда: Москва

Re: Transact SQL справочник

Сообщение Tanyatet » 18 авг 2017, 20:20

Спасибо огромное. Очень нужные вещи. Помню искала долго))




Вернуться в Программирование

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 2