LOGAN

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

Ограничения это требования к данным распологаемых в столбце или таблицы.

Три типа ограничения есть:

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

МЕТОДЫ РЕАЛИЗАЦИИ ОГРАНИЧЕНИЯ в сервере Ограничения первичного ключа Реализуется посредством Primary key Ограничения внешнего ключа Посредством Foreign key. Ограничение уникальности Unique Ограничение проверки Check Ограничения значения по умолчанию Default

Ограничение целостности 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 сервер представлены в иерархии структур:

1. База данных.

1. Экстент – основная еденица памяти используемая при распределение пространства и индексов. Занимает 8 страниц (каждая 64 кб)

1. Страницы – еденица распределения памяти в эстенте. Страница состоит из строк таблицы или индекса. Количество строк в расчете на одну страницу изменяется в зависимости от размера строк.

1. Индекс – это список указателей, на физическое строк , упорядочненный по значению определенного поля (столбца).

Типы строение индексов

Для организации индексов в SQL сервер используются сбалансированные деревья.

ТИПЫ ИНДЕКСОВ В SQL сервер:

1. Кластерный индекс
2. Не кластерный индекс:
   1. Не кластерный индекс на неупорядочненной таблице.
   2. Не кластерный индекс на кластерном индексе.

Кластерный индекс:

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

Некластерный индекс на неупорядочненной таблице -

В листовых узлах индекса хранятся RID по которому можно перейти к данным.

RID – включает номер экстента, номер страницы, и смещение строки страницы.

Создание, удаление, модификация индексов.

1. Явное создание с помощью команды create index
2. Неявное создание индекса в результате чего ввода ограничения.

Первый способ

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. 1. При добавлении значения поля основной таблицы имеющие спецификатор not null должны иметь значения по умолчанию
2. 2. Изменять данные полей через представление можно только в том случае, если они ссылаются на одну таблицу.
3. 3. Поля которые формируются с использованием функции агрегирования изменять нельзя.
4. 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’

Zforrt Верстка 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

Задания Хранимые процедуры

1. Необходимо создать хранимую процедуру, выводящую на экран список преподавателей, которые брали книги в библиотеке (база данных Library).

2.  Нужно показать книги по определенной тематике, при этом тематику необходимо передать при вызове.

1. Необходимо создать хранимую процедуру, с помощью которой можно получить список студентов, у которых первая буква в фамилии лежит в диапазоне от А до К.

4.  Необходимо создать хранимую процедуру, которая вернет Издательство, который выдало наибольшее кол-во книг.

1. Необходимо создать хранимую процедуру, которая вернет кол-во взятых с библиотеки книг преподавателями.

6.  Необходимо написать хранимую процедуру, выводящую на экран книги и цены по указанной категории, при этом необходимо указывать направление сортировки. 0 – сортировка результата по цене по убыванию, 1 – по возрастанию, любое другое – без сортировки. (многотабличная Books).

1. Написать хранимую процедуру, выводящую на экран список студентов, не сдавших книги.
2. Написать хранимую процедуру, возвращающую имя и фамилию библиотекаря, выдавшего наибольшее кол-во книг.
3. Написать хранимую процедуру, подсчитывающую факториал числа. (5! = 1*2*3*4*5 = 120) (0! = 1) (факториала отрицательного числа не существует).

Написать такие функции:

1. Функцию, возвращающую кол-во студентов, которые не брали книги.
2. Функцию, возвращающую минимальное из трех переданных параметров.
3. Функцию, которая принимает в качестве параметра двухразрядное число и определяет какой из разрядов больше, либо они равны. (используйте % – деление по модулю. Например: 57%10=7)
4. Функцию, возвращающую кол-во взятых книг по каждой из групп и по каждой из кафедр (departments).
5. Функцию, возвращающую список книг, отвечающих набору критериев (например, имя автора, фамилия автора, тематика, категория), и отсортированный по номеру поля, указанному в 5-м параметре, в направлении, указанном в 6-м параметре.
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- (Надежность, живучесть) Сервер защищен от внешнего воздействия.

Режимы работы транзакций:

(Минимальной транзакций есть запрос)

1. autocommit- Автофиксация
2. explicit- Явный режим
3. 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

Создание вложенных транзакций:

1. В случае вложенных транзакций

Нужно явно фиксировать каждую внутренню транзакцию, сервер не выполняет фактическое фиксирвания внутренних транзакций пока не произойдет успешное фиксирование внешних транзакций.

Откаты могут производиться в 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

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

1. Чтение незафиксированных данных.
2. Неповторяемое чтение.
3. Фантомы.
4. Потерянные обновления.

Чтение незафиксированных данных

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

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

Потерянные обновления не решаются средствами блокировок, а решаются с помощью триггеров

Триггеры

Применяются:

1. Для ссылочной целостности.
2. Триггеры используются для создания журналов ( то есть таблица)отслеживания модификации данных.
3. Триггеры применяются для поддержки ограничений.
4. Триггеры применяются для вставки данных с использованием представлений.

Триггер это Хранимая процедура особого вида которые вызываются в ответ на определенное событие.

Триггер нельзя вызывать явно.

Его вызывает сервер

Триггер не принимает параметры.

Триггер не возвращает значение.

Но может иметь ретерн для прерывания работы.

Требования к триггерам:

Триггеры вызываются только после завершение оператора который вызвал триггер.

Если какой-либо оператор пытается выполнитть операцию которое нарушает любое ограничение (или другая ошибка)

То связанный с ним триггер не будет активизирован.

Триггер рассматривается как часть одной транзакции с оператором который его вызвал.

Триггер активируется только один раз для одного оператора.

Виды триггеров

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) – СУБД которая получает реплицированные данные.

И сохраняет в репликационной базе данных.

Роли данных:

Публикация- единица данных которая предоставляется для  репликации. Подписчики подписываются на публикации.

Статья – данные, из какой либо таблицы, либо представления на издателя.

Подписка – это запрос на получение публикации.

Подписка бывает двух видов:

1. PUSH подписка – принудительная подписка. Инициируется издателем.
2. PULL подписка – запрашиваемая подписка, инициатором выступает подписчик.

Типы подписчиков:

1. По умолчанию ЛОКАЛЬНЫЙ – это единственный сервер который знает об существовании , издатель и распространитель.
2. Глобальный подписчик – О существовании данного подписчика знают несколько изданий.
3. Анонимный подписчик- существует только во время соединения.

Типы репликации:

1. Репликация моментальных снимков (snapshot replication) – это такой тип когда информация на подписчике через определенный интервал времени перезаписывается информацией с издателя

1. Репликация слияния (Merge) – изменения происходит на издателе и на подписчике. Все изменения сводятся воедино на издателя, который решает конфликты в случае их возникновения. Конфликты могут возникать если работа производится с одними и теми же данными у издателя и подписчика.

3. Репликация транзакций (transactional) -

В начале подписчику применяется моментальный снимок исходных данных, через определенные интервалы времени подписчику передается и применяется информация о произошедших на издателе изменениях.(Транзакция)

Агенты репликации- Агент который используется для репликации снимками называется snapshot agent. Агент распределения distribution agent.

Агент репликации слияния –merge agent.

Процесс репликации транзакций -

Топология репликации:

Централизованный издатель-распределитель

Централизированный издатель удаленный распределитель

Централизованный подписчик

Смешанные схемы:

Процесс репликации:

1. Настройка баз данных издателя и распределителя.
2. Настройка публикаций для репликаций.
3. Настройка подписчиков.

1. Принципалы –те объекты которым можно предоставить разрешения :

А)Уровень ОС – входят логин доменной учетной записи или локальной записи 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’

Мой блог находят по следующим фразам

• одинарная кавычка xml
• msxml ошибка в элементе верхнего уровня документа
• произошла ошибка базового поставщика в open.
• winforms структура проекта
• entity+framework+произошла+ошибка+базового+поставщика+в+open+wcf
• событие создания формы c# winforms