SQL
Колосветов Евгений
13пр31
1.Обзор объекта SQL-сервер
2.Создание баз данных
3.Создание таблиц в СУБД SQL-сервер
Системные базы данных :
1.Master- содержит набор таблиц которые отслеживают
Функционирование всей системы.
2. Model- модель с помощью которой создается база даннах.
3. msdb – база данныз предназначена для хранения задач выполняемых SQL- сервером.
4. tempdb – рабочая область SQL-сервер в которой создаются временные таблицы.
Команда для удаления базы данных:
Drop database имя базы данных
Синтаксис drop type_object name_object
Просмотреть таблицу
use master;
Select * from dbo.spt_values; |
Оператор Create.
Create type_object name_object
Например Create database my base;
USE master;
GO CREATE DATABASE logan ON PRIMARY (NAME = ‘My base1′, FILENAME = ‘E:my base.mdf’, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = ‘base’, FILENAME = ‘e:my base.ldf’, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) GO |
exec sp_helpdb logan; – информация о базе |
аттач базы
USE master; GO CREATE DATABASE lala ON (FILENAME = ‘C:Documents and SettingsÊîëîñâåòîâ.ÅâãåíèéÐàáî÷èé ñòîëbooks.mdf’) FOR ATTACH ; GO |
use logan
go
CREATE TABLE Employees
(
EmployeeID int identity not null,
FirstName varchar(25) not null,
MiddleInitial char(1) null,
LastName varchar(25) not null,
Title varchar(25) not null,
SSN char(2) not null,
Salary money not null,
PriorSalary money not null,
LastRaise as Salary – PriorSalary,
HireDate smalldatetime not null,
TerminationDate smalldatetime not null,
ManagerEmpID int not null,
Departament varchar(25) not null
)
ALTER
Rename :
use test
go
exec sp_rename ‘employees.phone’,'asd’,'column’;
go
Ограничения это требования к данным распологаемых в столбце или таблицы.
Три типа ограничения есть:
- Ограничение домена – распространяется на один столбец, обеспечивает соответствие данных в конкретном столбце конкретным критерием.
- Ограничение сущности- ограничение относится к строкам согласно этому ограничению в каждой строке таблицы должно присуствовать уникальное значение одного столбца
- Ограничение ссылочной целосности- это такое ограничение при создании которого значения в одном столбце должны согласововатся со значениями в другом столбце.
МЕТОДЫ РЕАЛИЗАЦИИ ОГРАНИЧЕНИЯ в сервере
Реализуется посредством Primary key
Посредством Foreign key.
|
Ограничение целостности Primary key-
Первичные ключи это уникальные ключи это идентификаторы для каждой строки не допускается НУЛЛ значения , таблица не может иметь больше одного первичного ключа.
use test
go Create table Customers (CustomerID int Identity not NULL Primary key, name varchar(25) not null, Phone varchar(15) null); |
use test
go alter table Employees add constraint PK_Employees Primary key (EmployeeID);
|
Внешний ключ – это столбец значение которого совпадает со значениями другой таблицы.
Используется : для связи, обеспечения целостности.
use test
go Create table Orders (OrderID int Identity not null Primary key, Employee int not null Foreign key References Employees( EmployeeID), Customer int not null,description varchar(100) null); |
use test
go alter table Orders add constraint FK_orderCustomer Foreign key ( customer)References Customers (customerID); |
ДЗ ДОДЕЛАТЬ ПРОШЛОЕ И ПРАКТИЧЕСКАЯ НОМЕР ТРИ!
Индексы
1.струтуры памяти SQL сервера
2.Что такое индексы
Данные в SQL сервер представлены в иерархии структур:
- База данных.
- Экстент – основная еденица памяти используемая при распределение пространства и индексов. Занимает 8 страниц (каждая 64 кб)
- Страницы – еденица распределения памяти в эстенте. Страница состоит из строк таблицы или индекса. Количество строк в расчете на одну страницу изменяется в зависимости от размера строк.
- Индекс – это список указателей, на физическое строк , упорядочненный по значению определенного поля (столбца).
Типы строение индексов
Для организации индексов в SQL сервер используются сбалансированные деревья.
ТИПЫ ИНДЕКСОВ В SQL сервер:
- Кластерный индекс
- Не кластерный индекс:
- Не кластерный индекс на неупорядочненной таблице.
- Не кластерный индекс на кластерном индексе.
Кластерный индекс:
Кластерный индекс может быть задан только один, если на таблице задан кластерный индекс информация хранится в упорядочненном виде, на листовом уровне, кластерного индекса находятся физические данные. При добавлении новой записи, имеющиеся данные и индекс пересортировуются.
Некластерный индекс на неупорядочненной таблице -
В листовых узлах индекса хранятся RID по которому можно перейти к данным.
RID – включает номер экстента, номер страницы, и смещение строки страницы.
Создание, удаление, модификация индексов.
- Явное создание с помощью команды create index
- Неявное создание индекса в результате чего ввода ограничения.
Первый способ
Create index nameIndex
On nameTable(nameColumn);
Можно ставить после width :
Fillfactor- на сколько процентов должна быть заполнена страница Индекса.
Ignore_Dup_key – при попытке вставить дублирущее значение возникает не ошибка а предупреждение.
Drop_existing – существующий индекс с таким же именем будет удален.
statistiesnorecompute-отмена автоматического обновления статистики.
Изменение индекса- нужно удалить индекс и поставить новый.
Удаление индекса – Drop index nameTable.nameIndex
Некоторые примеры:
–use master;
–create database Test; use Test go Create table Table1 ( id int not null, col1 int not null, col2 int not null ); use Test go Create table Table2 ( id int not null, col1 int not null, col2 int not null ); |
Заполнение таблиц
use Test
go Declare @i int Select @i=0 while(@i<100000) begin Insert Into Table2 Values(@i,@i*2,@i/2) Select @i=@i+1 End |
Запрос
use Test
go select * From Table1 where id> (Select MAX(col2)From Table2); |
Установка индекса
use Test go Alter table Table1 Add constraint PK_Table1 primary key(id);
|
Создание индекса
use Test
go create nonclustered index in_Table2 on Table2(col2); |
Сопровождение индекса
Просмотр о заполняймости страниц индекса use Test go DBCC showcontig (‘Table1′,’PK_Table1′); |
||
Удалить строку use Test go delete from Table1 where id=666; |
||
Дефрагментация
use Test
go DBCC DBREINDEX (‘Table1′,’PK_Table1′); |
Представление -
Виртуальная таблица
Представляющая собой поименованный запрос, может состоять из одного или из несколькоких полей одной или нескольких таблиц.
Создается:
Create View NameView
As
Select elements;
use library go Create View BooksView As Select Books.Name, Authors.FirstName,Authors.LastName from Books,Authors where Books.Id_Author=Authors.Id;
|
Обращение к представлению
use library
go select * from NameView; |
|||
use library go Create View pagesView As Select P.Name, sum(B.Pages* B.Quantity) as SumPages from Books AS b,Press as P where b.Id_Press=P.id group by P.Name;
|
|||
use library
go select MAX(Sumpages) from pagesView; |
|||
Ограничения на изменения данных через представление :
- 1. При добавлении значения поля основной таблицы имеющие спецификатор not null должны иметь значения по умолчанию
- 2. Изменять данные полей через представление можно только в том случае, если они ссылаются на одну таблицу.
- 3. Поля которые формируются с использованием функции агрегирования изменять нельзя.
- 4. Если представление содержит оператор union
То возможно только выборка данных.
use master
drop database LOGAN
go
create database LOGAN
GO
use LOGAN
create table Parent
(
ID int identity(10,10) not null,
Primary key (ID)
)
go
create table child1
(
ID int identity not null,
Parent_id int not null,
Foreign key (Parent_id)References Parent(ID)
)
go
create table child2
(
ID int identity not null,
Parent_ID int not null,
foreign key (Parent_id)References Parent(ID)
)
Через команндную строку
Cmd –запускаем консоль
Sqlcmd
-Eиспользуются учетные данные Windows
-Sдаленный сервер
- q “Запрос”
-i имя файла сценария
Например:
Sqlcmd –i “D:logan.sql” –E
Объявление переменных:
Declare @var int
Select @var=10
Или
Set var=20
Переменные:
@@version
Sqlcmd “select @@version”
@@Error –возвращает номер последней ошибки
@@identity –узнать последнее значение которое вставлялось в колонку со свойствами IDENTITY
use logan insert into Parent default values select @@identity insert into child1 (parent_id) values(@@identity) select @@identity –insert into child2 (parent_id) value() |
use logan insert into Parent default values Declare @a int set @a=@@identity select @@identity select @@identity insert into child1 (parent_id) values(@@identity) insert into child2 (parent_id) values(@a) |
Хранимые процедуры- оформеленный специальный проэкт которые хранится в БД |
Create procedure имя
Параметры
Опции
As
Begin
…
End
Удаление drop procedure имя
Изменение alter procedure имя
Параметры
Опции
As
Begin
…
End
use Northwind
go
create proc prCustomer
as
begin
select * from Customers
end
ВЫЗОВ
use northwind go execute prCustomer
|
use northwind
go create proc prInsertShipper @CompanyName nvarchar(40), @Phone nvarchar(25) as begin insert into Shippers values(@CompanyName,@Phone) end;
|
|
use northwind
go prInsertShipper ‘IBM’,'diz’ |
use northwind go exec prInsertShipper @companyname=”asd”, @Phone=”asd” |
|
use northwind
go create proc prInsertShip1per @CompanyName nvarchar(40)=”asd”, @Phone nvarchar(25)=”asd” as begin insert into Shippers values(@CompanyName,@Phone) end; |
use northwind
go exec prInsertShipper @companyname= “” |
|||
use northwind
go create proc prInsertOrder @CustID nvarchar(5), @EmployeeID int, @orderDate datetime=null, @OrderID int output as begin insert into Orders(CustomerID,EmployeeID,OrderDate) values(@CustID,@EmployeeID,@orderDate); set @orderID=@@identity end; |
|||
declare @prIdent int;
exec prInsertOrder ‘993′,2,’2010-01-25′,@prIdent output print @prIdent |
|||
use northwind go create proc prCountInCountry @Country nvarchar(50), @numEmployee int output, @numCustomer int output as begin select * from Customers where Customers.Country=’Mexico’ end |
use northwind go alter proc prCountInCountry @Country nvarchar(50), @numEmployee int output, @numCustomer int output as begin select @numCustomer=count(*) from Customers where Customers.Country=’Mexico’ select @numEmployee=count(*) from Employees where Employees.Country=’Mexico’ end |
Проверка на НУЛЛ is NULL
use master
go create database [Учет больничных листов] |
use [Учет больничных листов]
go create table [Должности] ( Id int identity not null, SpecName varchar(25)not null, primary key(id) ) |
|||
use [Учет больничных листов]
go create table [Цеха] ( Id int identity not null, DeptNum varchar(25)not null, DeptName varchar(25)not null, primary key(id) ) |
|||
use [Учет больничных листов]
go create table [Рабочий] ( Id int identity not null, SpecID int not null, DeptName varchar(25)not null, WorkName varchar(25)not null, primary key(id) ) |
|||
use [Учет больничных листов]
go create table [Больничный] ( Id int identity not null, WorkerID int not null, StartDate date not null, EndDate date not null, primary key(id) ) |
use [Учет больничных листов]
go alter table [Больничный] add constraint FK_worker foreign key (WorkerID)references [Рабочий](id) |
use [Учет больничных листов]
go alter table [Рабочий] add constraint FK_Dolj foreign key (SpecID)references [Должности](id) |
use [Учет больничных листов]
go alter table [Должности] add constraint FK_zeh foreign key (ID)references [Цеха](id) |
|
use [Учет больничных листов]
go alter table [Рабочий] add constraint FK_Dolj foreign key (Id)references [Должности](id) |
use [Учет больничных листов]
go alter table Рабочий –add birthday date not null –add Married bit null, –add [образование] varchar (25)not null add [Размер зарплаты] int not null |
|
use [Учет больничных листов]
go alter table [Рабочий] add [Дата поступления] date not null |
use [Учет больничных листов]
go alter table [Рабочий] add constraint yearCHeck check (birthday<[дата поступления]); |
use [Учет больничных листов]
go alter table [Рабочий] add constraint yearCHeck2 check (year(getdate())<=year([дата поступления])); |
use [Учет больничных листов]
go alter table [Рабочий] add constraint ifMarried check (married=0 or married=1); |
use [Учет больничных листов]
go Create table [Образование] ( id int not null, name varchar(25) not null Primary key(id), ) |
use [Учет больничных листов]
go alter table [Рабочий] add constraint FK_O foreign key(id)references [Образование](id) |
use [Учет больничных листов]
go alter table [Больничный] add constraint bol check (enddate>startdate); |
use [Учет больничных листов]
go alter table цеха add vred int not null use [Учет больничных листов] go create nonclustered index zeh_index on [цеха](deptname); |
use [Учет больничных листов]
go Create View info_rab As Select [Рабочий].WorkName,[Рабочий].SpecId,[Рабочий].deptName from [Рабочий] |
use [Учет больничных листов]
go Create View info_rab2 As Select [Больничный].startdate,[Больничный].enddate from [Больничный] |
use [Учет больничных листов]
go Create View info_zeh As Select [цеха].deptName from [цеха] |
Сценарии и пакеты и пользовательские функции и хранимые процедуры
Сценарий – это несколько инструкций языка t-sql которые хранятся в файле.
use library go declare @Id int Insert into Faculties(Name) values(‘music_teach) set @Id=@@IDENTITY Insert Into Groups (Name,Id_Faculty) values(‘gr11′,@Id) select ‘id=’+CONVERT(varchar(10),@id) |
Cast или convert (Конвертирует в соответствующий тип)
Пакет – группа или одна инструкция языка T-SQL отправляемая единовременно из приложения в SQL сервер для выполнения.
Чтобы разделить сценарий на несколько пакетов можна использовать оператор GO :
Этот оператор должен находится на одной строке.
Оператор GO это не оператор языка T-SQL, это команда которая распознается только утилитами SQL сервер.
Границы пакета опредяляются до оператора GO.
Особенности пакетов:
CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER и CREATE VIEW
После них ставить GO!ОБЯЗАТЕЛЬНО!!!
В одном и том же пакете нельзя изменять а затем и использовать таблицу.
Оператор управления выполнением
If, if else, case, while,try catch, waitFor
|
use library
go if exists ( select * from sys.tables where sys.tables.name=’books’) print’Table is exist else print ‘Create table please’ |
Верстка Querix- отправить резюме на англ |
use library
go select S.FirstName, Position= case S.id%2 when 0 then ‘CHETNIE’ when 1 then ‘Nechet’ else ‘;)’ End from S_Cards as SC,Students AS S where SC.id=s.Id |
use library
go select S.FirstName, Position= case when sc.DateIn is not null then ‘Good student’ when SC.DateIn IS null and DATEDIFF(day,sc.dateOut,GetDate())<30 Then ‘bad student’ else ‘In a black list’ End from S_Cards as SC,Students AS S where SC.id=s.Id |
use library
go Begin try create table books (col1 int) end try Begin catch if Error_number()=2714 Print ‘The table is alrady exist’ end catch |
Хранимые процедуры
Хранимые процедуры – это набор инструкци t-sql которые хранятся в скомпилированным виде, и хранятся в области кэш памяти SQL-сервера.
Системные – sp_help
Sp_helpdb
Пользовательские – ………………………
Расширенные xp_ – …………………………………………
Exec sp_help – использование.
Create Proc spSelectBooks
As
Select * from books;
Вызов exec spSelectBooks
Create Proc spselectBooksName
@Name varchar
Select *from books
Where books.name=@name
Вызов такой процедуры exec spselectBooksName ‘Name’;
С выход параметрами.
Create Proc spselectBooksName
@Name varchar+int Output
Select *from books
Where books.name=@name
Declare@C int;
Exec spselectBooksName ‘Name’, @count=C output
Задания Хранимые процедуры
- Необходимо создать хранимую процедуру, выводящую на экран список преподавателей, которые брали книги в библиотеке (база данных Library).
2. Нужно показать книги по определенной тематике, при этом тематику необходимо передать при вызове.
- Необходимо создать хранимую процедуру, с помощью которой можно получить список студентов, у которых первая буква в фамилии лежит в диапазоне от А до К.
4. Необходимо создать хранимую процедуру, которая вернет Издательство, который выдало наибольшее кол-во книг.
- Необходимо создать хранимую процедуру, которая вернет кол-во взятых с библиотеки книг преподавателями.
6. Необходимо написать хранимую процедуру, выводящую на экран книги и цены по указанной категории, при этом необходимо указывать направление сортировки. 0 – сортировка результата по цене по убыванию, 1 – по возрастанию, любое другое – без сортировки. (многотабличная Books).
- Написать хранимую процедуру, выводящую на экран список студентов, не сдавших книги.
- Написать хранимую процедуру, возвращающую имя и фамилию библиотекаря, выдавшего наибольшее кол-во книг.
- Написать хранимую процедуру, подсчитывающую факториал числа. (5! = 1*2*3*4*5 = 120) (0! = 1) (факториала отрицательного числа не существует).
Написать такие функции:
- Функцию, возвращающую кол-во студентов, которые не брали книги.
- Функцию, возвращающую минимальное из трех переданных параметров.
- Функцию, которая принимает в качестве параметра двухразрядное число и определяет какой из разрядов больше, либо они равны. (используйте % – деление по модулю. Например: 57%10=7)
- Функцию, возвращающую кол-во взятых книг по каждой из групп и по каждой из кафедр (departments).
- Функцию, возвращающую список книг, отвечающих набору критериев (например, имя автора, фамилия автора, тематика, категория), и отсортированный по номеру поля, указанному в 5-м параметре, в направлении, указанном в 6-м параметре.
- Функцию, которая возвращает список библиотекарей и кол-во выданных каждым из них книг.
Пользовательские функции
Это множество операторов T-sql которое откомпилировано хранится в памяти sql сервера может быть выполнена как единый модуль.
Отличия от ХРАНИМЫХ ПРОЦЕДУР это возврат полученных результатов.
Вызов функции:
Select nameDatabase.name_owner.name_function(передача_параметров) |
Возвращаемые значения функции:
Функция :
use library go create function ConvertDate(@DATE datetime) returns varchar(10) as begin return Convert(varchar(10),@DATE,102) end Вызов ее: use library go select Students.FirstName from S_Cards,Students where Students.Id=S_Cards.Id_Student and (select library.dbo.ConvertDate(s_cards.DateIn))=(select library.dbo.ConvertDate(GetDate())) |
Пользовательские функции которые возвращают таблицы:
Принцип создания:
В запросе который будет формировать возвращаемую таблицу должны использоваться псевдонимы таблиц.
Создаются после FROM.
Пример создания:
use library go create function BooksAndAuthors() Returns table as return (select b.name,A.name from books as B, Authors as A where b.Id_Author=A.id) Пример запуска: use library go Select * from books,Authors |
Пример многозапросной функции
Create function Books_By_Authors ()
returns @Books_Authors table (author varchar(25), amm int)/*Определяем переменную и
тип возвращаемой таблицы!!!*/
as
begin
Declare @temp_books table (author1 varchar(25), amm1 int)/*Объявляем временную таблицу!!!*/
insert @temp_Books/*Записываем в нее список авторов и
кол-во взятых студентами книг этих авторов!!!*/
Select authors.firstname+' '+authors.lastname,count(s_cards.id_book)
from authors,books,s_cards
where authors.id=books.id_author and books.id=s_cards.id_book
group by authors.firstname,authors.lastname
insert @temp_Books/*Дописываем в нее список авторов и
кол-во взятых преподавателями книг этих авторов!!!*/
Select authors.firstname+' '+authors.lastname,count(t_cards.id_book)
from authors,books,t_cards
where authors.id=books.id_author and books.id=t_cards.id_book
group by authors.firstname,authors.lastname
Declare @temp_books2 table (author2 varchar(25), amm2 int)/*Объявляем еще одну временную таблицу!!!*/
insert @temp_books2/*Записываем в нее содержимое первой временной таблицы,
сумируя при этом кол-во книг одного автора!!!*/
Select t.author1,sum(t.amm1)
from @temp_books t
group by t.author1
insert @Books_authors/*Записываем содержимое в возвращаемую переменную!!!*/
Select t.author2, t.amm2
from @temp_books2 t
return
end
Вызов:
Select * from Books_By_Authors()
ДЗ пр 1,2
И хранимые процедуры, и функции.
1.необходимо создать хранимую процедуру, выводящую на экран список преподавателей, которые брали книги в библиотеке (база данных Library).
use library go Create proc Show_t as select Teachers.FirstName from T_Cards,Teachers where T_Cards.Id_Teacher=Teachers.Id –exec Show_t |
2.Нужно показать книги по определенной тематике, при этом тематику необходимо передать при вызове
use library go Create proc Show_themes @themes_name varchar(25)output as begin select Books.Name from Themes,Books where @themes_name=Themes.Name end –use library –go –exec Show_themes ‘Базы данных’ |
3. Необходимо создать хранимую процедуру, с помощью которой можно получить список студентов, у которых первая буква в фамилии лежит в диапазоне от А до К.
use library
go create proc show_s as begin select Students.FirstName from Students where ‘В’=CONVERT(char,Students.FirstName) end |
Это способ с помощью которого осуществляется объединения определенного кол-ва операций в одну конструкцию, данная конструкция выполняет подтверждение или отмену операций описанных в блоке.
Свойства транзакции:
Atomicity- (Атомарность) неделимость блока.
Consistency- (Логичность, связность) поддержка целостности данных.
Isolation- (Изоляция) Транзакции не могут взаимодействовать между собой, но могут вкладываться в друг друга.
Durability- (Надежность, живучесть) Сервер защищен от внешнего воздействия.
Режимы работы транзакций:
(Минимальной транзакций есть запрос)
- autocommit- Автофиксация
- explicit- Явный режим
- implicit- Не явный режим
Фиксация транзакций- Фиксированной называется транзакция все модификации которой стали постоянной часть базы данных.
До фиксирования транзакции записи о модификации заносятся в журнал транзакций.
ЯВНЫЙ РЕЖИМ ПРИМЕР-
use library
go begin transaction UpdateS_cards update S_Cards set DateIn=GETDATE() where DateIn is null commit tran UpdateS_cards |
Неявный режим- В неявном режиме транзакция начинается автоматически, при использовании определенных операторов T-SQL и продолжается, пока не появится оператор окончания операции.(Commit или Rollback)
Если оператор окончания не указан все операции будут отменены.
Определенные операторы:
ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, UPDATE
Они автоматически начинают неявную транзакцию.
Для того чтобы включить неявный режим:
set Implicit_transaction on|off |
Создание вложенных транзакций:
- В случае вложенных транзакций
Нужно явно фиксировать каждую внутренню транзакцию, сервер не выполняет фактическое фиксирвания внутренних транзакций пока не произойдет успешное фиксирование внешних транзакций.
Откаты могут производиться в 2-х формах отката:
Автоматический откат- производится при сбое транзакции, в результате любой серъезной ошибки.
Программирумый либо ручной откат – выполянется с помощью оператора Rollback
ROLLBACK!!!
use library
go begin tran test delete from Teachers where Id=1 select * from Teachers rollback tran test select * from Teachers |
–use master
–go
–create database Credit
use credit
go
–create table customer(id int identity, name varchar(20), credit_limit money, credit money)
–create table package(customer_id int, name varchar(20), price money)
–alter table customer add constraint ch_credit check (credit_limit > credit)
–insert customer values(‘Mary’,1000,0)
create proc spBuy
@customer_id int,
@name_package varchar(25),
@price money
as
begin
begin tran tran_spBuy
insert into package values(@customer_id,@name_package,@price)
Begin try
update Customer set credit=credit+@price
end try
begin catch
print ‘Credit Failed
RollBack tran TBuy
return -1;
end catch
print ‘Thnx
Commit tran TBuy
end
Save Tran name_point
А потом RollBack tran name_point
Нарушения в работе которые предотвращаются с помощью различных уровней блокировок:
- Чтение незафиксированных данных.
- Неповторяемое чтение.
- Фантомы.
- Потерянные обновления.
Чтение незафиксированных данных
Пример в классе насчет блокировки
use credit
go begin tran tr1 update customer set credit_limit=500 where name=’Mary’ Select * from Customer waitfor delay ‘00:00:5′ Rollback tran tr1 |
А потом
use Credit
go select * from customer |
Уровни блокировки:
set transaction isolation level repeatable read |
Проблемы которые решаются с блокировки
ФАНТОМЫ
use Credit
go set transaction isolation level serializable |
|
use credit
go begin tran tr1 update customer set credit_limit=5000 where name=’Mary’ waitfor delay ‘00:00:05′ alter table customer add constraint ch_credit1 check (credit_limit > 3000) commit tran tr1 |
use credit
go begin tran tr1 insert customer values(‘Alina’,5000,0) commit tran tr1 |
Потерянные обновления не решаются средствами блокировок, а решаются с помощью триггеров
Триггеры
Применяются:
- Для ссылочной целостности.
- Триггеры используются для создания журналов ( то есть таблица)отслеживания модификации данных.
- Триггеры применяются для поддержки ограничений.
- Триггеры применяются для вставки данных с использованием представлений.
Триггер это Хранимая процедура особого вида которые вызываются в ответ на определенное событие.
Триггер нельзя вызывать явно.
Его вызывает сервер
Триггер не принимает параметры.
Триггер не возвращает значение.
Но может иметь ретерн для прерывания работы.
Требования к триггерам:
Триггеры вызываются только после завершение оператора который вызвал триггер.
Если какой-либо оператор пытается выполнитть операцию которое нарушает любое ограничение (или другая ошибка)
То связанный с ним триггер не будет активизирован.
Триггер рассматривается как часть одной транзакции с оператором который его вызвал.
Триггер активируется только один раз для одного оператора.
Виды триггеров
DML(data manipulation language) и DDL (data definition language).
DML
Триггер: после Insert после update после delete и комбинация из них.
use library
go –create trigger qyantity –on books –for update –as –print ‘Countrow=’+Convert(varchar(25),@@RowCount)–информация сколько строк задейственно в запросе Update books set books.quantity=books.quantity+1 from Press where books.id_press=Press.id and press.name=’BHV’ |
Таблицы Deleted и inserted-в SQL сервере при операциях удаления, вставки и обновления, (insert,delete,update)Создаются таблицы inserted и deleted. Таблицы имеют одинаковую структуру с таблицей по которой определяется данный триггер.
Таблица deleted содержит копии строк, на который повлиял оператор delete либо update
Анологично с inserted.
Написать такие триггера:
1. Чтобы при взятии определенной книги, ее кол-во уменьшалось на 1.
2. Чтобы при возврате определенной книги, ее кол-во увеличивалось на 1.
3. Чтобы нельзя было выдать книгу, которой уже нет в библиотеке (по кол-ву).
4. Чтобы нельзя было выдать более трех книг одному студенту.
5. Чтобы при удалении книги, данные о ней копировались в таблицу Удаленные.
6. Если книга добавляется в базу, она должна быть удалена из таблицы Удаленные.
use library
go
create trigger InsertTcards1
ON t_cards
for insert
as
begin
declare @id int
select @id=id_book from inserted–èäåíòèôèêàòîð êíèãè â òàáëèöà inserted
declare @count int
select @count=quantity from books where books.id=@id
if @count =1
begin
print ‘only one book!’
rollback transaction
end
else
begin
set @count=@count-1
update books set quantity=@count
where books.id=@id
print ‘Good luck!’
end
end
Брать книги
INSERT INTO [library].[dbo].[T_Cards]
([Id_Teacher]
,[Id_Book]
,[DateOut]
,[DateIn]
,[Id_Lib])
VALUES
(1
,2
,2009-22-22
,2009-22-24
,2)
GO
use library
go create trigger ogran on press for DELETE as begin if exists (select * from deleted where name=’testBHV3′) print ‘testBHV3 dont touch ‘ Insert into press values(‘testBHV3′) end |
use library
go
–Create table InsDelBooks
–(
–operation varchar(15) not null,
–BookName varchar(50) not null,
–OperationDate datetime not null
–)
Create trigger Journal
on Books
for Delete, Update, Insert
as
begin
if exists (Select * From Deleted) and exists (Select * From Inserted)
Insert into InsDelBooks Select ‘Update’, Name, GetDate() from Inserted
else
begin
if exists (Select * From Deleted)
Insert into InsDelBooks Select ‘Delete’, Name, GetDate() from Deleted
else
Insert into InsDelBooks Select ‘Insert’, Name, GetDate() from Inserted
End end
Триггеры DDL
Триггеры DDL — это особый вид триггеров, которые срабатывают при выполнении инструкций языка описания данных DDL. Они могут применяться при выполнении административных задач (например, для аудита и регулирования операций в базе данных).
Триггеры DDL обычно используются в следующих целях:
• Предотвращение внесения определенных изменений в схему БД.
• Выполнение в БД некоторые действия в ответ на изменения в схеме базы данных.
• Запись изменения или события схемы базы данных.
Запрос на создание DDL триггера:
CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE }
[WITH ENCRIPTION | EXECUTE AS <CALLER | SELF | <user>>]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement }
Область триггера:
ALL SERVER – триггер срабатывает при событии на сервере
DATABASE – триггер срабатывает при событии в текущей БД
event_type – имя события, приводящего к срабатыванию триггера
event_group – имя группы событий, приводящих к срабатыванию триггера
Область триггера зависит от события, вызвавшее его срабатывание.
Например:
События уровня БД: CREATE_TABLE, CREATE_USER
События уровня сервера: CREATE_DATABASE, CREATE_LOGIN
1. Создание триггера уровня БД, запрещающего удаление и изменение таблиц
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “safety” to drop or alter tables!’
ROLLBACK ;
2. Создание триггера, запрещающего создание новой БД
CREATE TRIGGER NewBD
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT ‘You can not create a database’
ROLLBACK ;
Просмотр триггеров: представления sys.triggers и sys.server_triggers (в БД master)
3. Удаление триггера
DROP TRIGGER NewBD ON ALL SERVER
ДЗ!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Использование триггера вставку в представления.
Возможно только
Написать такие триггера:
1. Чтобы при взятии определенной книги, ее кол-во уменьшалось на 1.
2. Чтобы при возврате определенной книги, ее кол-во увеличивалось на 1.
3. Чтобы нельзя было выдать книгу, которой уже нет в библиотеке (по кол-ву).
4. Чтобы нельзя было выдать более трех книг одному студенту.
5. Чтобы при удалении книги, данные о ней копировались в таблицу Удаленные.
6. Если книга добавляется в базу, она должна быть удалена из таблицы Удаленные.
И практическая 6
Репликация- это процесс синхронизации информации на разных источниках данных. Также это процесс копирование информации из одной базы данных в другую.
Распределитель(distributer)-система базы данных, которая содержит дистрибутивную базу данных, используемую для поддержки управления репликацией.
Издатель(publisher)- родитель БД.
Подписчик(subscriber) – СУБД которая получает реплицированные данные.
И сохраняет в репликационной базе данных.
Роли данных:
Публикация- единица данных которая предоставляется для репликации. Подписчики подписываются на публикации.
Статья – данные, из какой либо таблицы, либо представления на издателя.
Подписка – это запрос на получение публикации.
Подписка бывает двух видов:
- PUSH подписка – принудительная подписка. Инициируется издателем.
- PULL подписка – запрашиваемая подписка, инициатором выступает подписчик.
Типы подписчиков:
- По умолчанию ЛОКАЛЬНЫЙ – это единственный сервер который знает об существовании , издатель и распространитель.
- Глобальный подписчик – О существовании данного подписчика знают несколько изданий.
- Анонимный подписчик- существует только во время соединения.
Типы репликации:
- Репликация моментальных снимков (snapshot replication) – это такой тип когда информация на подписчике через определенный интервал времени перезаписывается информацией с издателя
- Репликация слияния (Merge) – изменения происходит на издателе и на подписчике. Все изменения сводятся воедино на издателя, который решает конфликты в случае их возникновения. Конфликты могут возникать если работа производится с одними и теми же данными у издателя и подписчика.
3. Репликация транзакций (transactional) -
В начале подписчику применяется моментальный снимок исходных данных, через определенные интервалы времени подписчику передается и применяется информация о произошедших на издателе изменениях.(Транзакция)
Агенты репликации- Агент который используется для репликации снимками называется snapshot agent. Агент распределения distribution agent.
Агент репликации слияния –merge agent.
Процесс репликации транзакций -
Топология репликации:
Централизованный издатель-распределитель
Централизированный издатель удаленный распределитель
Централизованный подписчик
Смешанные схемы:
Процесс репликации:
- Настройка баз данных издателя и распределителя.
- Настройка публикаций для репликаций.
- Настройка подписчиков.
- Принципалы –те объекты которым можно предоставить разрешения :
А)Уровень ОС – входят логин доменной учетной записи или локальной записи Windows .
Б)Уровень SQL-сервера – логин SQL –сервера.
В)Уровень БД- роли, пользователи и роли приложений.
2.Защищаемые объекты – объекты которым можно предоставить разрешения.
А)Уровень SQL-сервера – логин, база данных, точка подключения.
Б)Уровень базы данных – роли,таблицы и все что из чего состоит БД.
В)На уровне схемы – Схема это некое пространство контейнер. Входит все что может создавать пользователь.
Процесс предоставления разрешения состоит:
1.создания логина.
2.создаем пользователя БД.
3.предоставление пользователю необходимых разрешений.
create login logan2
with password =’logan’
–create login logan2222
–with password =’logan’
——————————
–create user Jenia12
–for
–LOGIN logan222
exec sp_addrolemember db_datareader, ‘Jenia12′
exec sp_dropuser Jenia12
use library
go
grant select
on
books
to Jenia12
use library
go
grant all
on
books
to Jenia12
deny select
on books to Jenia12
BCP..books out F:/books.txt –c –t;-rn –T
BCP..books1 in F:/books.txt –c –t;-rn -T
–use master
–go
–create database bookShop
–use bookShop
–go
–create table authors
–(
–id_authors int identity not null primary key,
–id_book int not null,
–name varchar(25)not null
–)
–create table seller
–(
–id_seller int identity not null primary key,
–name varchar(25) not null,
–id_book int not null
–)
–create table book
–(
–id int identity not null Primary key ,
–name varchar(25) not null,
–id_authors int not null Foreign key References authors(
–id_authors ),
–min_price int not null,
–max_price int not null,
–id_seller int not null Foreign key References seller(
–id_seller),
–timebooks smalldatetime not null
–)
–alter table seller
–add prime int not null
use bookShop
go
————————————–
–Выбрать самые популярные 10 книг
–create proc popular
–as
–begin
–select book.name from book,seller
–where book.id=seller.id_book
–end
–execute popular
———————————
–Редактирование табличек
–alter table seller
–add buy_day int null
–add buy_vix int null
————————————
–execute popular
——————————————–
–Раскупаемость книги в какие дни
–create proc statistic1
–as
–begin
–select book.name from seller,book
–where seller.buy_day>seller.buy_vih
–print ‘От пенедельника до пятницы’
–or ‘В выходные’
–end
–execute statistic1
————————————————
–какой автор популярен
–create proc statistic
–as
–begin
–select authors.name from authors,book
–where authors.id_book=book.id
–end
–execute statistic
–use bookShop
–go
——————————————————————
–Создание Триггера
–create trigger book_srok
–on book
–for DELETE
–as
–begin
–if exists (select book.timebooks from book where book.timebooks>GetDate())
–delete book.id
–end
—————————————–
–Создание кластеров
–use bookShop
–go
–create nonclustered index book_index
–on book(id);
———————————————-
–Создание кластеров
–use bookShop
–go
–create nonclustered index book_index2
–on book(id_seller);
———————————————–
–USER
–use bookShop
–go
–create login LOGAN91111
–with password =’12345′
–create user Jenia91111
–for
–LOGIN LOGAN91111
–exec sp_addrolemember db_datareader, ‘Jenia91111′
–deny all
–on seller
–to Jenia91111
–deny all
–on book
–to Jenia91111
–deny all
–on authors
–to Jenia91111
–grant select
–on
–book
–to Jenia91111
–grant select
–on
–authors
–to Jenia91111
——————————————-
–ADMIN;)
–create login administrator
–with password =’12345′
–create user adminNumberOne
–for
–LOGIN administrator
–exec sp_addrolemember db_owner, ‘adminNumberOne’
Мой блог находят по следующим фразам
[...] Создать хранимую процедуру, выводящую группы [...]
[...] [...]
[...] значение foreign key копировалось из primary key sql [...]
[...] insert into northwind.dbo.orders(value,orderdate) values(1)getdate()) [...]
[...] как отобразить защищаемые объекты sql [...]
[...] оператор go sql [...]
[...] тип ограничения целосности [...]
[...] произошла ошибка базового поставщика в open [...]
[...] 2. написать хранимую процедуру, возвращающую имя и фами… В закладки! var a2a_config = a2a_config || {}; a2a_localize = { Share: "Отправить другу", Save: "Сохранить", Subscribe: "Подписаться", Email: "E-mail", Bookmark: "В закладки!", ShowAll: "Показать все", ShowLess: "Показать остальное", FindServices: "Найти сервис(ы)", FindAnyServiceToAddTo: "Найти сервис и добавить", PoweredBy: "Работает на ", ShareViaEmail: "Подписаться по e-mail", SubscribeViaEmail: "Подписаться по e-mail", BookmarkInYourBrowser: "Добавить в закладки", BookmarkInstructions: "Нажмите Ctrl+В или ⌘+D чтобы добавить страницу в закладки", AddToYourFavorites: "Добавить в Избранное", SendFromWebOrProgram: "Отправить через e-mail сервис", EmailProgram: "E-mail сервисы" }; a2a_config.linkname="ITE"; a2a_config.linkurl="http://logan.progryz.ru/2010/10/ite/"; a2a_config.hide_embeds=0; [...]
[...] процедура получить имена студентов которые брали книг… [...]
[...] [...]
[...] Declare ID_BOOK int Select ID_BOOK id from S_cards UPDATE books SET Quantity Quantity 1 WHERE books … [...]
Когда я искал изготовителя мозаичной плитки для своего бассейна, то обратился в компанию Евростек http://eurostek.org.ua, по рекомендациям друзей. Консультант разговаривал очень вежливо и дал мне объемную информацию об изделии. Ждал плитку недолго. Качество высокое. Она не скользит, чистится очень быстро и легко. А так же порадовала сравнительно низкая цена.