Чтобы получить информацию о таблице, необходимо выполнить следующую хранимую процедуру:
sp_help имя таблицы.
После исполнения этой команды на экране появляется целый ряд информационных таблиц: таблица с общей информацией, таблица со свойствами колонок, таблица с ограничением IDENTITY, таблица с информацией о размещении на файлах, таблица с информацией об индексах, таблица с данными об ограничениях, таблица с информацией о ссылающихся таблицах (см. sp_depends и sp_keys).
Задание 1. С помощью утилиты Enterprise Manager создать таблицу требуемо структуры, выполнив следующие действия:
1. Выберите в левом окне Enterprise Manager базу данных, в которой планируется создание новой таблицы, дважды щелкнув по ней с помощью левой клавиши мыши.
2. Выберите элемент Table в указанной базе данных.
3. Один раз щелкните правой клавишей мыши по выделенному элементу или на свободном пространстве правого окна.
4. В контекстном меню выберите элемент New Table. (Вместо пунктов 2,3 и 4 можно было исполнить команду Action/New Table).
5. В открывшемся окне для создания новой таблицы необходимо указать названия столбцов, тип данных из раскрывающегося списка, размер для типов char, nvarchar, binary, varchar и nchar, а также задать флажок в графе Allow Nulls для тех колонок, в которых допускаются значения NULL.
6. Выбирая по очереди столбцы в верхней части окна, задайте в нижней части окна дополнительные параметры для этих столбцов:
Description – текстовое описание соответствующего столбца;
Default Value – значение по умолчанию;
Precision – точность p для типа numeric или decimal: Scale – масштаб, т. е. число цифр после точки; Identity = Yes – для столбца – счетчика;
Identity Seed – начальное значение счетчика;
Identity Increment – шаг приращения;
Is RowGuid = Yes – для колонки с глобальными идентификаторами;
Formula – формула для вычисления значений столбца;
Collation – сопоставление для столбца.
7. Сохранить новую таблицу в базе данных, щелкнув по кнопке Save и задав ей требуемое имя.
Задание 2. Показать сценарий создания новой таблицы, исполнив команду All
Tasks/Generate SQL Scripts ее контекстного меню, и сохранить в файле …Abc\Tabl Script. sql.
Задание 3. Для демонстрационных баз данных Pubs и Northwind создать их диаграммы,
используя мастер создания диаграмм Create Database Diagram Wizard:
1. Выберите базу данных Pubs (гостиницы).
2. Откройте контекстное меню базы данных.
3. Исполните команду New Database Diagrams.
4. Ознакомьтесь с порядком создания диаграмм:
а) создать новую диаграмму и включить в нее требуемые
таблицы в соответствии с поставленными целями просмотра базы данных;
б) добавить в диаграмму таблицы, связанные с выбранными;
в) упорядочить таблицы на диаграмме, что делается автоматически.
5. Щелкните по кнопке Next.
6. В левой части окна по очереди выбирайте требуемые таблицы из списка всех таблиц базы данных Pubs и щелкайте по кнопке Add. Кнопка Remove позволяет исключить таблицу из выбранного списка.
7. Установите флажок Add related tables automatically для автоматического добавления связанных таблиц.
8. Щелкните по кнопке Next.
9. Просмотрите список выбранных и связанных таблиц и если требуется его корректировка, то возвратитесь назад.
10. Щелкните по кнопке Next.
11. Созданная диаграмма заносится в папку Diagrams базы данных Pubs.
12. Откройте папку Diagrams базы данных Pubs.
13. Выберите новую диаграмму и для ее просмотра дважды щелкните на ней.
14. Убедитесь, что таблицы можно просматривать в различных режимах (Show), редактировать структуру (Column Property), отношения, ограничения целостности, индексы и ключи (Properties), модифицировать триггеры (Task/Manage Trigger), управлять доступом к таблице (Task/Manage Permissions), выяснять зависимости (Task/Display Dependencies), сгенерировать сценарий создания таблицы (Task/Generate SQL Scripts).
15. Выполните пункты 1-14 для базы данных Northwind.
Задание 4. Создать три взаимосвязанные таблицы jobs (работы), employee (служащий) и publishers (издатели), используя необходимые ограничения, исполнив следующие Transact-SQL команды:
CREATE TABLE jobs
(job_id smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, job_desc varchar (so) NOTT NULL
DEFAULT ‘New Position – title not formalized jet’, min_lul tinyint NOT NULL CHECK (min_lul > = 10), max_lul tinyint NOT NULL CHECK (max_lul > = 250)
)
CREATE TABLE employee
(emp_id CONSTRAINT PK_emp_id PRIMARY KEY NOTICLUSTERED
CONSTRAINT PK_emp_id CHECK (emp_id LIKE
‘[A-Z][A-Z][A-Z][1-9][0-9][0-9][FM]’ or
‘[A-Z]-[A-Z][1-9][0-9][0-9][0-9][FM]’, fname varchar (20) NOT NULL,
minit char (1) NULL,
lname varchare (30) NOT NULL,
job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs (job. id), job_lul tinyint DEFAULT 10,
pub_id char (4) NOT NULL DEFAULT(‘9952’)
REFERENCES publishers (pub_id),
hire_date datetime NOT NULL varchar (40) NULL, (getdate ())
)
CREATE TABLE publishers
(pub_id char (4) NOT NULL
CONSTRAINT OPKL_pubid PRIMARY KEY CLOSTERD CHEC (pub_id IN (‘1389’, ‘0736’, ‘0877’, ‘1622’, ‘1756’
OR pub_id LIKE ‘99[0-9][0-9]’), pub_name varchar (40) NULL,
city varchar (20) NULL, state char (2) NULL,
country varchar (30) NULL DEFAULT (‘OSA’)
)
Задание 5. Создать таблицу с глобальным идентификатором, выполнив команду: CREATE TABLE GUIDTAB
(guid uniqueidentifier CONSTRAINT GuidDefault DEFAULT NEWID (), Emploec_Name varchar (60) CONSTRAINT Guid_PK PRIMARY KEY (guid)
)
Задание 6. Создать таблицу с вычисляемым столбцом, исполнив команду: CREATE TABLE My Table
(low int, high int,
myavg AS (low + hvgh)/2
)--myuser-name AS USER_NAME()
Задание 7. Создать временную локальную и временную глобальные таблицы и найти их в системной базе tempdb.
Задание 8. Создать программную таблицу, объявив переменную типа Table, и заполнить ее данными. Создать аналогичную таблицу в базе данных и переписать в нее содержимое программной таблицы. Написать пакет команд для сравнительного анализа эффективности работы с этими двумя таблицами.
Задание 9. Получить информацию о свойствах таблицы: ее название, сведения о владельце, дата создания, колонки-счетчики с ограничением IDENTITY, колонки с глобальными идентификаторами, группа файлов для хранения данных, сведения об индексах, сведения об
ограничениях, сведения о таблицах, на которые ссылается данная таблица с помощью ограничения
FOREIGN KEY. Для получения указанных свойств исполнить команду:
EXEC *****@***= Имя таблицы (например job)
Задание 10. Получить информацию об объектах, зависимых от таблицы orders базы данных Northwind, исполнив команду:
EXEC sp_depends orders
Задание 11. Используя хранимую системную процедуру sp_fkeys, получить информацию о таблицах, связанных с данной через ограничения PRIMARY KEY и FOREIGN KEY.
Задание 12. Дополнить таблицу AllTypes базы данных Proba новыми колонками и занести в них значения по умолчанию.
Лабораторная работа №8
Добавление, извлечение, модификация и удаление данных в таблицах
Цель работы – изучение синтаксиса и семантики команд INSERT, SELECT, INTO, UPDATE, DELETE, их разделов SELECT, INTO, FROM, WHERE, GROUP BY, HAVING, UNION, ORDER BY, COMPUTE, FOR, OPTION и функций агрегирования, имеющихся в языке Transact- SQL для управления данными SQL Server 2000, а также приобретение навыков применения этих команд для манипулирования данными и использования программы массового копирования BCP (Bulk Copy Program) для включения в базы данных текстовых файлов.
SQL Server 2000 обеспечивает разнообразные механизмы управления данными, такие как ADO, OLE DB, ODBC, DB - Library и команды языка Transact-SQL, являющегося фундаментом всех остальных технологий. Для добавления данных в языке используются команды INSERT и SELECT INTO, для изменения данных – команда UPDATE и для удаления строк из таблиц – команда DELETE. Команда SELECT позволяет реализовать многофункциональный механизм доступа к данным любой сложности.
Команда INSERT позволяет вставить в таблицу одну или несколько строк. Упрощенный синтаксис этой команды таков:
INSERT [INTO] имя модифицируемой таблицы
[WITH (уровень блокировки запроса)]
{[(список колонок модифицируемой таблицы)]
{VALUES (список значений новой строки)\
команда SELECT}}\ DEFAULT VALUES
Если необходимо явно вставлять значения в колонки – счетчики, имеющие свойство
IDENTITY, то для модифицируемой таблицы надо выполнить команду: SET IDENTITY_INSERT имя модифицируемой таблицы ON
Если список столбцов не задан, то сервер будет вставлять данные последовательно во все столбцы, начиная с первого. Для каждого столбца должен быть указан аргумент, имеющий соответствующий тип. Аргументами могут быть константы, выражения соответствующего типа, значение NULL и значение по умолчанию DEFAULT. В списке столбцов можно не указывать столбцы со свойством IDENTITY, столбцы допускающие значение NULL и столбцы типа timestamp.
Если в команде задан источник данных DEFAULT VALUES, то строка будет содержать только значения по умолчанию или значения NULL.
Если в данной команде в место имени таблицы задать имя представления, то новая строка будет вставлена в представление, а точнее, в ту исходную таблицу, на основе которой было создано представление, при этом изменение данных через представление должно быть разрешено специальной командой. Для представления невозможно задать уровень блокировки.
Если необходимо быстро создать таблицу, имеющую такую структуру, чтобы в ней можно было сохранить результат выполнения запроса, то следует использовать следующую команду:
SELECT список выбираемых колонок исходных таблиц INTO имя автоматически создаваемой таблицы FROM список исходных таблиц
[условия выбора значений из таблиц]
Имена колонок новой таблицы либо совпадают с именами колонок исходных таблиц, либо задаются после ключевого слова AS, следующего за именем колонки исходной таблицы. Имя создаваемой таблицы должно быть уникальным в пределах базы данных. Чаще всего эта команда используется для создания временных локальных (#) и глобальных (##) таблиц.
Для любой базы данных использование команды SELECT… INTO запрещено. Для установки разрешения на ее использование необходимо выполнить команду:
EXES sp_dboption ‘имя базы данных’, ‘select into/bulkcopy’, ‘on’
Наиболее часто доступ к данным реализуется командой SELECT, начиная от простой выборки всех строк одной таблицы и кончая сложными запросами, обрабатывающими десятками и сотнями тысяч строк в десятках таблиц.
В большинстве случаев используется упрощенный вариант команды SELECT, имеющей следующий синтаксис:
SELECT опции выбора список выбора
[INTO новая таблица]
FROM список исходных таблиц или представлений
[WHERE условие поиска]
[GROUP BY условие группировки] [HAVLNG условия группового поиска]
[ORDER BY выражение для упорядочивания [AS\DESC]]
Части этой команды называются разделами и должна записываться в запросе именно в данном порядке.
Опции выбора определяют количественные характеристики запроса:
ALL – все строки таблицы;
DISTINCT – все несовпадающие строки таблицы; TOP n – первые n несовпадающие строки таблицы; TOP n PERCENT – первые n% несовпадающих строк;
WITH TIES – разрешает вывод дублирующих строк для последних двух вариантов.
Список выбора содержит имена столбцов различных таблиц для формирования результата. Столбцы могут переименовываться с помощью конструкции AS.
Если в качестве списка выбора задана звездочка (*), то в запрос будут включены все столбцы всех таблиц и представлений, заданных в качестве источников данных. Если в списке задана конструкция имя. *, то это означает, в список выбора надо включить все колонки объекта с данным именем. В список выбора можно включить и выражения, тогда их значения будут вычисляться при формировании каждой строки. Эта колонка будет либо безымянной, либо будет иметь имя, заданной конструкцией AS, следующей после выражения. Элемент списка в виде имя. IDENTICOL [AS имя счетчика] позволяет включить в результат колонку – счетчик объекта с данным именем. С помощью ключевого слова POWGVIDCOL аналогичным образом можно включить глобально уникальный идентификатор строк того или иного объекта.
Раздел INTO позволяет создать новую таблицу для хранения результатов запроса, структура которой будет определяться списком выбора, т. е. динамически при выполнении запроса.
Раздел FROM является обязательным в команде SELECT и определяет таблицы и представления для выборки данных. Если таблица одновременно является и источником данных, и получателем результатов, то ей необходимо задать псевдоним с помощью конструкции AS, что обеспечит правильную работу сервера. Для таблиц можно указывать уровни блокировок: HOLDLOCK, NOLOCK и т. д.
В разделе FROM можно задавать связанные таблицы для формирования сложных запросов:
исходная таблица, тип связи, исходная таблица
ON условие поиска или
исходная таблица CROSS JOIN исходная таблица.
При использовании ключевых слов CROSS JOIN или запятой происходит связывание каждой строки левой таблицы с каждой строкой правой таблицы, так что количество строк при выполнении запроса определяется как произведение количества строк первой таблицы на количество строк второй таблицы.
При использовании ключевых слов INNER JOIN или пробела выбираются пары строк из связанных таблиц, для которых имеются строки, удовлетворяющие критерию связывания в обеих таблицах. Строки из левой таблицы, для которых не имеется пары в связанной таблице, в результат не включаются. Также не включаются строки правой таблицы, для которых нет соответствующей строки в левой таблице.
При использовании ключевых слов LEFT [OUTER] JOIN в результат будут включены все строки левой таблицы, независимо от того, есть для них соответствующая строка в правой таблице или нет, при этом для соответствующих столбцов правой таблицы, включенных в запрос, при отсутствии соответствия будут заноситься значения NULL.
При использовании ключевых слов RIGHT [OUTER] JOIN в результат будут включены все строки правой таблицы, независимо от того, есть для них соответствующие строки в левой таблице. В столбцах левой таблицы, для тех строк, для которых нет соответствия, запишутся значения NULL.
При использовании ключевых слов FULL [OUTER] JOIN в результат будут включены все строки как правой, так и левой таблицы.
Логическое условие связывания двух таблиц, которое записывается после ключевого слово ON, должно быть логическим выражением, включающим любые операторы сравнения: =, <,
>, <=, >=,! =, <>. В ряде случаев в запросах используются опции оптимизации: LOOP, HASH, MERGE, REMOTE. Если эти опции не заданы, то оптимизацию запросов осуществляет сервер.
С помощью раздела WHERE можно сузить количество обрабатываемых строк данных,
написав одно или несколько логических условий:
WHERE условие поиска
или имя колонки * = имя колонки -- LEFT OUTER JOIN
или имя колонки * = имя колонки -- RIGHT OUTER JOIN
Логическое условие может быть произвольным, в том числе и не связанным с данными. Оно может включать логические операторы NOT, AND и OR. В ряде случаев, в особенности для сложных запросов предпочтительнее использовать условия связывания * = (аналог LEFT OUTER JOIN) или = * (аналог RIGHT OUTER JOIN), которые выполняются гораздо эффективнее.
Раздел GROUP BY позволяет выполнить группировку строк таблиц по определенным критериям. Для каждой группы можно выполнить специальные функции агрегирования, которые будут применены ко всем строкам группы:
AVG (имя столбца) – среднее значение в группе; SUM (имя столбца) – сумма значений группы;
MIN (имя столбца) – минимальное значение в группе; MAX (имя столбца) – максимальное значение в группе;
COUNT (имя столбца) – число строк в группе с непустым значением; COUNT (*) – число строк в группе, включая и пустые (NULL).
Синтаксис раздела следующий:
GROUP BY условие группировки --или BY ALL [WITH CUBE или ROLLUP] --супергруппировка.
В качестве условия группировки задается имя одного или нескольких столбцов таблицы. Именно эти столбцы должны быть включены в запрос. Наличие других столбцов не допускается. Если в запросе было определено условие, сужающее диапазон группировки выбираемых строк, то включение слова ALL обеспечит вывод всех групп, но функции агрегирования для дополнительных групп выполняться не будут.
Ключевое слово WITH CUBE инициирует суперагрегирование, когда в результат включаются строки, являющиеся результатом агрегирования уже агрегированных данных. В итоге получается многоуровневое агрегирование с итоговыми данными по всем уровням. Опция WITH ROLLUP позволяет устранить лишние строки при суперагрегировании.
Раздел HAVING задает условия поиска при определении групп с помощью раздела
GROUP BY:
HAVING условие поиска
Этот раздел аналогичен разделу WHERE.
Раздел UNION позволяет выполнить объединение данных из нескольких таблиц, имеющих одинаковый список столбцов, порядок их следования и свойства, при этом по умолчанию дублирующие строки не включаются, если не задано ключевое слово ALL:
SELECT список столбцов FROM имя первой таблицы
UNION [ALL]
SELECT список столбцов FROM имя второй таблицы и т. д.
Раздел ORDER BY используется, когда необходимо отсортировать данные в результирующем наборе:
ORDER BY | имя столбца в запросе [ASC или DESC], | |
имя столбца в запросе [ASC или DESC]… | ||
Здесь ключевые | слова ASC и DESC обеспечивают сортировку соответственно | по |
возрастанию или убыванию значений в соответствующем столбце.
Раздел COMPUTE позволяет применять к выбираемым столбцам функции агрегирования:
COMPUTE функция агрегирования (имя столбца запроса)
BY имена для группировки
Порядок использования столбцов в функциях агрегирования должен соответствовать порядку следования столбцов, указанному в разделе ORDER BY. Не допускается также использование ключевого слова DISTINCT.
Изменение данных в таблицах или задание значений переменным производится командой
UPDATE:
UPDATE имя таблицы или представления WITH блокировка SET имя колонки или переменной = выражение… FROM имена исходных таблиц
WHERE условия поиска
Удаление данных из таблиц производится командой DELETE: DELETE FROM имя таблицы или представления
или
DELETE FROM имя таблицы
WHERE условие поиска OPTION (уровни блокировки)
Экспорт данных из таблиц в файлы и импорт из файлов производиться утилитой bcp: Bcp “Northwind..shippers” out file. txt-c--символьный.
Задание 1. Создать базу данных InsertDB и необходимые таблицы для выполнения следующих команд INSERT:
A)Использование простой команды INSERT:
Create Database InsertDB
Create Table TabA(Col1 int, Col2 varchar(30)) Insert TabA Values(1,'Пример 1')
Select * From TabA
B)Использование произвольной последовательности колонок таблицы:
Create Table TabB(Col1 int, Col2 varchar(30)) Insert TabB (Col2,Col1) Values('Пример В',1)
Select * From TabB
C)Использование нулевых значений и значений по умолчанию:
Create Table TabC(Col1 int IDENTITY, Col2 varchar(30)
CONSTRAINT default_name DEFAULT('Значение TabC. Col2'), Col3 int NULL,
Col4 varchar(40))
Insert Into TabC(Col4) Values('Значение 1 TabC. Col4')
Insert Into TabC(Col2,Col4) Values('Явное значение TabC. Col2','Значение 2 TabC. Col4') Insert Into TabC(Col2,Col3,Col4) Values('SAV',44, 'ABC')
Select * From TabC
D) Явное задание значения для колонки-счетчика (с ограничением IDENTITY):
Create Table TabD(Col1 int IDENTITY, Col2 varchar(30))
Insert Into TabD Values('Счетчик изменяется автоматически') Insert Into TabD(Col2) Values('Вторая строка')
Set IDENTITY_INSERT TabD ON
Insert Into TabD(Col1,Col2) Values(-99, 'Явное значение') Select * From TabD
E)Использование представления для занесения значений в таблицу:
Create Table TabE(Col1 int, Col2 varchar(30)) GO
Create View ViewE AS Select Col2,Col1 From TabE GO
Insert Into ViewE Values('Строка 1',1) -- порядок ViewE Select * From TabE -- порядок TablE
F)Использование опции DEFAULT VALUES:
Create Default bound_default2 AS 'Col4' GO
Create Table TabF(Col1 int IDENTITY, Col2 varchar(30)
CONSTRAINT Fdefault_name DEFAULT('Значение TabF. Col2'), Col3 timestamp,
Col4 varchar(30), Col5 int NULL
)
GO
EXEC sp_bindefault 'bound_default2','TabF. Col4' GO
USE InsertDB
Insert Into TabF DEFAULT VALUES Select * From TabF
G)Использование команды SELECT для копирования данных в таблицу:
Create Table TabG(Col1 int IDENTITY, Col2 varchar(50))
GO
Set IDENTITY_INSERT TabG ON GO
Insert Into TabG (Col1,Col2) Select Col1,Col2 From TabD GO
Select * From TabG GO
Использование команды SELECT для копирования данных из таблицы:
Create Table TabG(Col1 int IDENTITY, Col2 varchar(30))
Set IDENTITY_INSERT TabG ON Insert Into TabG
Select * From TabD Select * From TabG
H)Копирование таблиц из базы данных Northwind в базу данных НордОст:
USE НордОст
[Set IDENTITY_INSERT имя таблиц из НордОст ON] Insert Into имя таблицы из НордОст
Select * From Northwind. dbo. Имя таблицы из Northwind
I)Использование команды SELECT...INTO:
EXEC sp_dboption ‘InsertDB’, ‘select into/bulkCopy’, ‘on’ SELECT Col1 AS Счетчик, Col2 AS Имя,
Col3 AS Версия, Col5 AS Номер
Into Новая_TabF From TabF
J)Выборка начальных строк таблицы:
USE Northwind
Select Top 7 * From Territories --различные
Select Top 10 Percent * From Territories --различные упорядоченные по TerritoryID Select Order By TerritoryID With Ties * From Territories --все 7
Select All * From Territories --все строки таблицы
Select Distinct * From Territories -- все без дублирующих
K)Использование псевдонимов в запросе:
USE Pubs
Select Top 10 au_id, au_fname AS [Фамилия], au_lname
From Authors --имя 2-го столбца изменено
L)Включение в результат дополнительного столбца-выражения:
Select Top 7 NewID() AS Глобальный_уникальный_номер, '--', au_id, DatePart
(ms, GetDate())
From Authors --три колонки являются выражениями, из них две-безыменные
M)Использование подзапроса, возвращающего одну строку:
Select Top 5 (Select au_fname From authors --значение подзапроса определяется
Where au_id='') AS Подзапрос, --заново для каждой строки
title_id
From Titles
N)Включение столбца-счетчика:
Select Top 50 Percent jobs. IDENTITYCOL AS Number, job_id, job_desc From jobs
-- для одной таблицы задание ее имени для счетчика необязательно, т. е.jobs
необязательно
O)Использование ключевых слов CROSS JOIN для связывания двух таблиц:
USE Pubs
SELECT discounts. stor_id, discounts. discounttype, stores. stor_name
FROM discounts CROSS JOIN stores --3*6=18 строк
-- убедиться, что CROSS JOIN можно заменить запятой
P)Использование ключевых слов INNER JOIN для связывания двух таблиц: SELECT authors. au_lname, authors. au_fname, titleauthor. au_ord, titleauthor. royaltyper FROM authors INEER JOIN titleauthor
ON authors. au_id = titleauthor. au_id
WHERE authors. sate = 'CA'
-- убедиться, что можно INNER JOIN заменить пробелом
Q)Использование ключевых слов LEFT OUTER JOIN для связывания двух таблиц:
SELECT authors. au_lname, authors. au_fname, titleauthor. royaltyper
FROM authors LEFT OUTER JOIN titleauthor
ON authors. au_id = titleauthor. au_id
WHERE (authors. state = 'CA') --19 строк
R)Использование ключевых слов RIGHT OUTER JOIN для связывания двух таблиц: SELECT titleauthor. au_ord, titleauthor. royaltyper, authors. au_lname, authors. au_fname FROM titleauthor RIGHT OUTER JOIN authors
ON titleauthor. au_id = authors. au_id
WHERE (authors. state = 'CA') --19 строк
S)Использование ключевых слов FULL OUTER JOIN для связывания двух таблиц:
SELECT discounts. stor_id, discounts. discounttype, stores. stor_name
FROM discounts FULL OUTER JOIN stores
ON discounts. stor_id = stores. stor_id --8 строк
-- убедиться, что в примерах Q, R и S слово OUTER можно опустить
T)Использование раздела WHERE оператора SELECT: Select * From authors Where 3=6 --0 строк Select * From authors Where state <> 'CA' --Калифорния Declare @@Var1 int
Set @@Var1 = 4095
Slect title_id, type, pub_id, price From titles
Where ((ytd_sales = @@Var1) OR
(price BETWEEN 5 AND 1строк
-- заменить OR на AND и убедиться, что получится одна строка
Select discounts. stor_id, discounts. discounttype, stores. stor_name
From discounts, stores --здесь”,”-это CROSS JOIN Where disscounts. stor_id = stores. stor_id --1 строка
--это уже INNER OUTER JOIN
-- Аналог этой команды следующий:
Select discounts/stor_id, discounts. discounttype, stores. stor_name
From discounts INEER JOIN stores
ON discounts. stor_id = stores. stor_id
-- этот запрос эффективнее предшествующего.
Select discounts. stor_id, discounts. discounttype, stores. stor_name
From discounts, stores
Where discounts. stor_id, *= stores. stor_id--3 строки
-- это - аналог LEFT OUTER JOIN: Select discounts. stor_id, discounts. disconttype, stores. stor_name From discounts LEFT OUTER JOIN stores
ON discounts. stor_id = stores. stor_id --3 строки
Select discounts. stor_id, discounts. discounttype, stores. stor_name
Where discounts. stor_id =* stores. stor_id
-- это - аналог RIGHT OUTER JOIN Select discounts. stor_id, discounts. discounttype, stores. stor_name From discounts RIGHT OUTER JOIN stores
ON discounts. stor_id = stores. stor_id --6 строк
U)Использование разделов GROUP BY и HAVING:
Select type, SUM(price), COUNT(*)
From titles -- type-тип изданий
Group By type --6 строк
-- выдается колонка type, колонка SUM и счетчик COUNT Select type, SUM(price), count = COUNT(*) -- имя столбца
Form titles
Where type < 'ps'
Group BY type --3 строки
Select type, SUM(price), count = COUNT(*) -- имя счетчика count
From titles
Where type < 'ps'
Group By ALL type --6 строк
-- строки с type >= 'ps'- без агрегирования
Select type, pub_id, SUM(price), COUNT(*) From titles
Where price <> 0
Group By type, pub_id --7 агрегированных строк
Select type, pub_id, SUM(price), COUNT(*) From titles
Where price <> 0
Group By type, pub_id With Cube --суперагрегирование
Select type, pub_id, SUM(price), COUNT(*) From titles
Where price <> 0
Group By type, pub_id --13 строк
With ROLLUP
V)Объединение таблиц с помощью раздела UNION:
USE Northwind
Select City, Phone Info #Tab1 From customers
Where contacttitle = 'Marketing Assistant' Select City, Phone Info #Tab 2 From Customers
Where contacttitle = 'Sales Associate' Selest City, Phone From #Tab1
UNION
Select City, Phone From #Tab2
W)Использование раздела ORDER BY:
USE Northwind
Select ContactTitle, City From Customers
Where ContactTitle IN ('Marketing Assistant'
'Sales Agent'
'Sales Associate')
Order By ContactTitle, City --18 строк
X)Использование раздела COMPUTE:
Select title_id, price From Titles
Where (type = 'bisiness') OR (type = 'mod_cook') Compute SUM(price), COUNT(price) --6строк и
Select title_id, price From Titles
-- SUM
-- =====
--77.9000
-- CNT
-- =====
-- 6 --всего 7строк
Where (type = 'business') OR (type = 'mod_cook') Order By type
Compute Count(price), Sum(Price) By type
Y)Обработка данных с помощью команды UPDATE:
Declare @@Var1 int
SET @@Var1 = 0
Update #TempTableAuthors SET @@Var1 = @@Var1 + 1
Where Contact = 0
Select @@Var1
GO
Declare @@Var2 varchar(40)
Update #TempTableAuthors SET @@Var2 = au_lname = au_lname + '__NO CONTRACT ' Where Contact = 0
Select au_id, au_lname, phone From #TempTableAuthors --2строки
Where Contract = 0
Select @@Var2 AS 'Значение переменной @@Var2' --1строка
Z)Использование команды DELETE:
Delete From #TempTableAuthors
Select * From #TempTableAuthors --0строк
Лабораторная работа №9
Планирование использования, создание и управление индексами
Цель работы – изучение назначения, типов и способов определения индексов, синтаксиса и семантики команд языка Transact-SQL для их создания, переименования, перестройки, создания статистики для оптимизации индексов и удаления, а также приобретения навыков создания и управления индексами с помощью команд, системных хранимых процедур, графических средств утилита Enterprise Manager и мастеров Create Index Wizard и Index Tuning Wizard.
Индекс представляет собой средство, помогающее ускорить поиск необходимых данных за счет физического или логического их упорядочения и эффективных способов использования таких упорядочений данных. Программная реализация индекса – это набор ссылок, упорядоченных по определенному столбцу таблицы, который в данном случае будет называться индексированным столбцом. Индексы – это своего рода предметные указатели для таблиц баз данных, при этом физически, в зависимости от типа индекса, данные могут храниться в порядке добавления их в таблицу.
В SQL Server 2000 реализованы эффективные алгоритмы поиска нужного значения в упорядоченной последовательности данных. Одних из таких алгоритмов является метод деления пополам, на каждом шаге которого данные делятся пополам и путем сравнения со средним значением определяется, найдено ли требуемое значение, а если не найдено, то решается, в какой половине данных оно находиться, при этом на каждом шаге происходит сокращение интервала поиска.
Индексы могут существенно повысить производительность выполнения операций поиска и выборки данных.
В идеале можно создать индексы для всех столбцов таблицы, но здесь есть одно существенное ограничение. Когда выполняется изменение строк таблицы, то помимо обновления самих данных необходимо выполнить обновление всех индексов. Обновление индексов требует практически столько же времени, сколько и обновление данных, поэтому на практике ограничиваются 4 или 5 индексами. Таким образом, основным преимуществом использования индексов является значительное ускорение выборки данных, а основным недостатком – замедление процесса обновление данных.
При определении столбца для индекса следует выбирать ключевые столбцы, которые задают критерии выборки данных, например, порядок сортировки. Не следует использовать столбцы с очень длинными данными. В крайнем случае, следует создавать укороченные варианты таких столбцов и использовать их для индексирования.
В MS SQL Server 2000 реализованы следующие типы индексов:
а) кластерные индексы;
б) не кластерные индексы;
в) уникальные индексы.
Не кластерные индексы являются наиболее типичными индексами. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки (указатели – row locator), которые включают в себя:
а) информацию об идентификационном номере файла (ID file), в котором храниться
строка;
б) идентификационный номер страницы данных;
в) номер соответствующей строки на странице;
г) содержимое столбца.
При определении кластерного индекса физическое расположение данных
перестраивается в соответствии со структурой индекса. Логическая структура таблицы в этом случае представляет скорее словарь, чем индекс. Естественно, в таблице может быть определен только один кластерный индекс. Он может включать несколько столбцов, которые наиболее часто используются при поиске. Однако количество таких столбцов следует по возможности свести к минимуму. Желательно, чтобы столбцы кластерного индекса не слишком часто изменялись, так как из-за этого будут часто физически переупорядочиваться строки таблицы.
При создании в таблице первичного ключа с помощью ключевых слов PRIMARI KEY сервер автоматически для него кластерный индекс, если он не был создан ранее или если при определении ключа не был явно указан не кластерный индекс (NONCLUSTERED). Если в таблице определяются другие индексы, то их указатели ссылаются не на физическое положение строки, а на соответствующей элемент кластерного индекса.
Уникальные индексы гарантируют уникальность значений в индексируемом столбце. Он является надстройкой для таблицы и может быть реализован как для кластерного, так и для не кластерного индекса. Уникальные индексы используются редко, а для обеспечения целостности данных следует использовать ограничения UNIQVE или PRIMARI KEY.
При определении индекса надо задавать параметр, который будет устанавливать плотность записи данных на странице, - фактор заполнения (fill factor). Его значение определяет, какой процент доступного пространства индексных страниц будет заполнен данными при создании индекса, а какой процент будет заполняться постепенно по мере загрузки данных в таблицу. Если таблица используется только для чтения, то значение этого параметра надо задавать близким к 100%, если таблица часто обновляется и дополняться, то значение фактора дополнение должно быть задано небольшим.
Право на создание индекса имеет только владелец таблицы, и это право не может быть передано другому пользователю. Индекс можно создать одним из следующих способов:
а) с помощью команд языка Transact – SQL, которые предоставляют пользователю максимум возможностей;
б) с помощью Enterprise Manager;
в) средствами мастеров Create Index Wizard и Index Tuning Wizard.
Создание индекса командами языка Transact – SQL производится следующим образом:
а) автоматически при создании первичного ключа, когда создается кластерный индекс
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 |
