Добро пожаловать на мой блог

The world is yours

sql

автор: admin | Октябрь 14, 2010 | Раздел: Программирование

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. Ограничение ссылочной целосности- это такое ограничение при создании которого значения в одном столбце должны согласововатся со значениями в другом столбце.
МЕТОДЫ РЕАЛИЗАЦИИ ОГРАНИЧЕНИЯ в сервере

  1. Ограничения первичного ключа

Реализуется посредством Primary key

  1. Ограничения внешнего ключа

Посредством Foreign key.

  1. Ограничение уникальности Unique
  2. Ограничение проверки Check
  3. Ограничения значения по умолчанию 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’

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

13 отзыва(-ов) »

  1. [...] Создать хранимую процедуру, выводящую группы [...]

  2. [...] [...]

  3. [...] значение foreign key копировалось из primary key sql [...]

  4. [...] insert into northwind.dbo.orders(value,orderdate) values(1)getdate()) [...]

  5. [...] как отобразить защищаемые объекты sql [...]

  6. [...] оператор go sql [...]

  7. [...] тип ограничения целосности [...]

  8. [...] произошла ошибка базового поставщика в open [...]

  9. [...] 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; [...]

  10. [...] процедура получить имена студентов которые брали книг… [...]

  11. [...] [...]

  12. [...] Declare ID_BOOK int Select ID_BOOK id from S_cards UPDATE books SET Quantity Quantity 1 WHERE books … [...]

  13. Когда я искал изготовителя мозаичной плитки для своего бассейна, то обратился в компанию Евростек http://eurostek.org.ua, по рекомендациям друзей. Консультант разговаривал очень вежливо и дал мне объемную информацию об изделии. Ждал плитку недолго. Качество высокое. Она не скользит, чистится очень быстро и легко. А так же порадовала сравнительно низкая цена.

Оставить отзыв