· 9 years ago · Jan 28, 2017, 01:08 AM
1CREATE DATABASE test ON PRIMARY (NAME='test_data', FILENAME = 'C:\test_data.mdf',SIZE=1740000KB, MAXSIZE=UNLIMITED, FILEGROWNTH=16384KB)
2LOG ON
3(NAME='test_log', FILENAME='C:\test_log.ldf', SIZE=2048KB, MAXSIZE=1GB, FILEGROWNTH=16348KB)
4
5
6
7USE [master]
8GO
9IF EXISTS (SELECT name FROM master..sysdatabases WHERE name='Uczelnia') DROP DATABASE [Uczelnia]
10GO
11CREATE DATABASE [Uczelnia]
12GO
13SELECT name, size, size*1.0/128 AS [Size in MBs]
14FROM sys.master_files
15WHERE name = 'Uczelnia';
16
17=====================================================================================
18USE [Uczelnia]
19GO
20IF OBJECT_ID('[dbo].[Wydzialy]') IS NOT NULL DROP TABLE [dbo].[Wydzialy]
21GO
22CREATE TABLE [dbo].[Wydzialy](
23 [IdWydzialu] [smallint] NOT NULL,
24 [Nazwa] [VARCHAR](30) NOT NULL
25 CONSTRAINT PK_Wydzialy PRIMARY KEY (IdWydzialu),
26)
27GO
28-------------------------------------------------------------------------------------
29INSERT INTO [dbo].[Wydzialy] VALUES (1,'Administracji i Prawa')
30INSERT INTO [dbo].[Wydzialy] VALUES (2,'Informatyki')
31INSERT INTO [dbo].[Wydzialy] VALUES (3,'Nauk społecznych')
32INSERT INTO [dbo].[Wydzialy] VALUES (4,'Filozoficzny')
33INSERT INTO [dbo].[Wydzialy] VALUES (5,'Antropologii')
34INSERT INTO [dbo].[Wydzialy] VALUES (6,'Geodezyjny')
35INSERT INTO [dbo].[Wydzialy] VALUES (7,'Metalurgiczny')
36INSERT INTO [dbo].[Wydzialy] VALUES (8,'Fizyki jÄ…drowej')
37INSERT INTO [dbo].[Wydzialy] VALUES (9,'Matematyki')
38INSERT INTO [dbo].[Wydzialy] VALUES (10,'Biologiczny')
39
40
41=====================================================================================
42
43
44USE [Uczelnia]
45GO
46IF OBJECT_ID('[dbo].[Tytuly]') IS NOT NULL DROP TABLE [dbo].[Tytuly]
47GO
48CREATE TABLE [dbo].[Tytuly](
49 [IdTytulu] [smallint] NOT NULL,
50 [Tytul] [VARCHAR](30) NOT NULL
51 CONSTRAINT PK_Tytuly PRIMARY KEY (IdTytulu),
52)
53GO
54-------------------------------------------------------------------------------------
55INSERT INTO [dbo].[Tytuly] VALUES (1,'inż.')
56INSERT INTO [dbo].[Tytuly] VALUES (2,'mgr')
57INSERT INTO [dbo].[Tytuly] VALUES (3,'mgr inż.')
58INSERT INTO [dbo].[Tytuly] VALUES (4,'dr')
59INSERT INTO [dbo].[Tytuly] VALUES (5,'dr inż.')
60INSERT INTO [dbo].[Tytuly] VALUES (6,'dr hab.')
61INSERT INTO [dbo].[Tytuly] VALUES (7,'dr hab. inż.')
62INSERT INTO [dbo].[Tytuly] VALUES (8,'prof. nadz. dr hab.')
63INSERT INTO [dbo].[Tytuly] VALUES (9,'prof. nadz. dr hab. inż.')
64INSERT INTO [dbo].[Tytuly] VALUES (10,'prof. dr hab.')
65INSERT INTO [dbo].[Tytuly] VALUES (11,'prof. dr hab. inż.')
66
67USE [Uczelnia]
68GO
69IF OBJECT_ID('[dbo].[Pracownicy]') IS NOT NULL DROP TABLE [dbo].[Pracownicy]
70GO
71CREATE TABLE [dbo].[Pracownicy](
72 [IdPracownika] [int] NOT NULL,
73 [Imie] [NVARCHAR](50) NOT NULL,
74 [Nazwisko] [NVARCHAR](50) NOT NULL,
75 [IdTytulu] [SMALLINT] NOT NULL,
76 [IdWydzialu] [SMALLINT] NOT NULL,
77 [Stawka] [INT] NOT NULL,
78 [Data_zatrudnienia] [DATE] NOT NULL,
79 [Data_zwolnienia] [DATE] NULL
80CONSTRAINT PK_Pracownicy PRIMARY KEY (IdPracownika),
81CONSTRAINT OGR_Wydzial FOREIGN KEY (IdWydzialu) REFERENCES [dbo].[Wydzialy](IdWydzialu),
82CONSTRAINT OGR_Tytul FOREIGN KEY (IdTytulu) REFERENCES [dbo].[Tytuly](IdTytulu)
83)
84-------------------------------------------------------------------------------------
85INSERT INTO [dbo].[Pracownicy] VALUES (1,'Stephanie','Alexander',1,4,1017,'1985-02-28',NULL)
86INSERT INTO [dbo].[Pracownicy] VALUES (2,'Dawn','Sharma',4,6,1792,'1992-06-03',NULL)
87INSERT INTO [dbo].[Pracownicy] VALUES (3,'Clayton','Ye',7,10,1363,'2010-12-04',NULL)
88INSERT INTO [dbo].[Pracownicy] VALUES (4,'Bradley','Xie',9,6,3452,'2011-07-12',NULL)
89INSERT INTO [dbo].[Pracownicy] VALUES (5,'Julia','Lopez',2,10,1243,'1999-01-18',NULL)
90INSERT INTO [dbo].[Pracownicy] VALUES (6,'Anna','Powell',7,9,1862,'1998-05-12',NULL)
91INSERT INTO [dbo].[Pracownicy] VALUES (7,'Meghan','Torres',4,1,2166,'2002-06-12',NULL)
92INSERT INTO [dbo].[Pracownicy] VALUES (8,'Roberto','Ramos',3,4,3627,'2002-02-14',NULL)
93INSERT INTO [dbo].[Pracownicy] VALUES (9,'Morgan','Bailey',8,8,3123,' 1996-02-16',NULL)
94INSERT INTO [dbo].[Pracownicy] VALUES (10,'Clayton','Shan',11,5,2984,' 1996-02-18',NULL)
95INSERT INTO [dbo].[Pracownicy] VALUES (11,'Edwin','Ye',1,8,3680,'2010-04-26',NULL)
96INSERT INTO [dbo].[Pracownicy] VALUES (12,'Diane','Jimenez',9,8,1270,'2010-04-26',NULL)
97INSERT INTO [dbo].[Pracownicy] VALUES (13,'Mallory','Dominguez',6,10,3693,'2010-08-27',NULL)
98INSERT INTO [dbo].[Pracownicy] VALUES (14,'Grace','Wood',6,1,1521,'2006-08-27',NULL)
99INSERT INTO [dbo].[Pracownicy] VALUES (15,'Erin','Rogers',5,3,2306,'2006-09-28',NULL)
100INSERT INTO [dbo].[Pracownicy] VALUES (16,'Joe','Ashe',3,9,2583,'2006-11-04',NULL)
101INSERT INTO [dbo].[Pracownicy] VALUES (17,'Alexandria','Griffin',2,3,3773,'2006-11-04',NULL)
102INSERT INTO [dbo].[Pracownicy] VALUES (18,'Jacob','Harris',1,10,3228,'2005-11-05',NULL)
103INSERT INTO [dbo].[Pracownicy] VALUES (19,'Joy','Alvarez',11,9,2079,'2005-11-06',NULL)
104INSERT INTO [dbo].[Pracownicy] VALUES (20,'Ian','Moore',10,6,1811,'1945-11-06',NULL)
105INSERT INTO [dbo].[Pracownicy] VALUES (21,'Jada','Baker',9,3,1689,'2005-11-06',NULL)
106INSERT INTO [dbo].[Pracownicy] VALUES (22,'Mason','Mitchell',8,10,1666,'1945-11-07',NULL)
107INSERT INTO [dbo].[Pracownicy] VALUES (23,'Nathan','Flores',7,4,1492,'1945-11-08',NULL)
108INSERT INTO [dbo].[Pracownicy] VALUES (24,'Devin','Howard',6,4,1922,'2007-11-10',NULL)
109INSERT INTO [dbo].[Pracownicy] VALUES (25,'Marcus','Cooper',5,10,2835,'2007-11-18',NULL)
110INSERT INTO [dbo].[Pracownicy] VALUES (26,'Seth','Jenkins',4,10,1496,'2007-11-28',NULL)
111
112
113/*dodanie grupy plików do bazy*/
114USE Uczelnia
115ALTER DATABASE Uczelnia
116ADD FILEGROUP uczelnia_file_group
117/*sprawdzenie grup plików*/
118EXEC sp_helpfilegroup
119/*dodanie plików do grupy plików, można dodawać więcej niż jeden plik*/
120ALTER DATABASE Uczelnia
121ADD FILE
122 (name='uczelnia_data2', FILENAME = 'C:\test\Uczelnia_data2.mdf',size=2),
123 (name='uczelnia_data3', FILENAME = 'C:\test\Uczelnia_data3.mdf',size=2)
124 TO FILEGROUP uczelnia_file_group
125/*zmiany plików w bazie danych*/
126USE Uczelnia
127ALTER DATABASE Uczelnia
128MODIFY FILE (name='U_data2', FILENAME ='C:\test2\ssss.ndf')
129/*zmiana domyślnej grupy plików*/
130USE Uczelnia
131ALTER DATABASE Uczelnia
132MODIFY FILEGROUP "PRIMARY" DEFAULT
133/*usunięcie pliku */
134USE Uczelnia
135ALTER DATABASE Uczelnia
136REMOVE FILE uczelnia_data3
137/*usunięcie grupy, nie da się usunąć grupy póki nie jest pusta*/
138USE Uczelnia
139ALTER DATABASE Uczelnia
140REMOVE FILEGROUP uczelnia_file_group
141/*sprawdzanie plików i ich nazw */
142SELECT
143 name,
144 physical_name
145FROM sys.master_files
146WHERE
147 database_id = DB_ID('Uczelnia')
148
149
150
151
152
1531.Sprawdzić jakie grupy plików posiada baza danych AdventureWorks2014.
1542.Dodać nową grupę plików o nazwie test.
1553.Dodać dwa nowe pliki danych o nazwie test.
1564.Usunąć jeden z plików danych.
1575.Usunąć utworzoną grupę plików.
158
159BEGIN
160 USE AdventureWorks2014
161 EXEC sp_helpfilegroup
162
163 ALTER DATABASE AdventureWorks2014
164 ADD FILEGROUP test
165
166 ALTER DATABASE AdventureWorks2014
167 ADD FILE
168 (name='plik_1', FILENAME = 'C:\test2\plik1.mdf',size=2),
169 (name='plik_2', FILENAME = 'C:\test\plik2.mdf',size=2)
170 TO FILEGROUP test
171
172 ALTER DATABASE AdventureWorks2014
173 MODIFY FILEGROUP test DEFAULT
174
175 ALTER DATABASE AdventureWorks2014
176 REMOVE FILE plik_2
177
178 ALTER DATABASE AdventureWorks2014
179 MODIFY FILEGROUP "PRIMARY" DEFAULT
180
181
182 ALTER DATABASE AdventureWorks2014
183 REMOVE FILE plik_1
184
185 ALTER DATABASE AdventureWorks2014
186 REMOVE FILEGROUP test
187END
188
189/* ustawienie bazy w tryb pojedynczego użytkownika */
190USE AdventureWorks2014
191ALTER DATABASE AdventureWorks2014 SET MULTI_USER
192/* sprawdza spójność pliki bazy danych, a dokładnie konkretnej bazy danych) */
193DBCC CHECKALLOC (AdventureWorks2014, NOINDEX)
194/* jeśli powyższy check pokaże błedy, to polecenie niżej naprawia tylko podstawowe błędy, np błędy spójności dysków */
195DBCC CHECKALLOC (AdventureWorks2014, REPAIR_FAST)
196/* stara się naprawić dane, nawet z ututatą danych, chodzi o transakcje anuluje transakcje które wiszą lub są zakleszczkone */
197DBCC CHECKALLOC (AdventureWorks2014, REPAIR_ALLOW_DAT_LOSS)
198/* najnardziej praco chłonna operacja, przetwarza cały plik od podstaw, transakcje pozamykane plik jest jak nowy, porządkuje wszystkie indexy tak jak ma być*/
199DBCC CHECKALLOC (AdventureWorks2014, REPAIR_REBUILD)
200/* zmiejsza rozmiar pliku jak się tablica indeksów rozjedzie to system robi zwyczajnie nową
201nie ruszając starej przez co baza puchnie poniższe zapytanie wywali te złe tabele indexu z bazy
202ZANIM WYKONAMY SHRINKDATABASE KONIECZNA JEST KOPIA ZAPASOWA*/
203USE uczelnia
204DBCC SHRINKDATABSE (uczelnia)
205/* poniższe polecenie pokazuje informacje o tabeli, również jedną bardzo ważną informację w ile procentach jest zajęta strona
206jeśli jest na 85% to znaczy że tracimy sporo danych, każda strona zajmuje 8 bitów, przy małych bazach jest to nie ważne, ale przy dużych
207Będziemy mieli sporo problemów ten problem spowodowany jest złym typem danych, jak najlepiej trzeba określić jakie dane będą np int to bardzo duży przedział
208a my potrzebujemy tylko przedział danych od 1 do 10, więc musimy to określić z góry*/
209USE [Uczelnia]
210DBCC showconting('dbo.Pracownicy')
211/*Schematy
212Tworzeone w kontekście bazy w której pracujemy
213Schematy sÄ… przypisane do konkretnej bazy danych
214Schematy są przypisany do bazy i użytkowników
215Przykład jest prosty mamy schemat o nazwie pracownik Kadr do którego przypisana jest baza kadry i uprawnienie odczytu
216więc jak przychodzi nowy pracownik wystarczy nadać mu login i dodać do tego schematu, a nie robić wszystko dla każdego użtkownika
217tak samo jak zmienia się polityka pracy kadry to wystarczy zmienić to w schemacie a nie każdemu użytkownikowi (pozwolenie edycja dla przykładu */
218
219
220/* Wyświetlenie wszystkich dostępnych schematów dla danej bazy dancych */
221USE AdventureWorks2014
222EXEC sp_schemata_rowset
223/* wyświetlenie tabel i w jakich schematach pracują | relacja tabela <> schemat */
224SELECT * FROM INFORMATION_SCHEMA.TABLES
225/* poniższe zapytanie robi dokładnie to samo co sp_chemata_rowset */
226SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
227/* rozpisana cała pojedynczą tabele, mamy pozycję kolumny i różne informację o nich */
228SELECT * FROM INFORMATION_SCHEMA.COLUMNS
229WHERE
230 TABLE_NAME='Person'
231
232/*nie mieliśmy użtkownika to sobie go stworzyliśmy i daliśmy uprawnienia oczywiście */
233USE Uczelnia
234CREATE LOGIN test WITH PASSWORD ='!QAZ2wsx3edc'
235EXEC sp_grantdbaccess test
236/* teraz poniżej tworzymy pusty schemat */
237USE Uczelnia
238GO
239CREATE SCHEMA studenci2 AUTHORIZATION test
240/* jak nie ma użtkownika można go stworzyć już w skrypcie */
241USE Uczelnia
242GO
243/* to polecenie dokładnie robi tak że tworzymy użytkownika który nazywa się test i loguje się za pomocą loginu test,
244nie muszą się nazywać tak samo, fajne to jest jak pracownik zmienia nazwisko, można mu zmienić wyświetlanie w bazie ale login
245pozostawić stary */
246CREATE USER test FOR login test
247GO
248CREATE SCHEMA studenci AUTHORIZATION test
249/* usuwanie scheamtu */
250DROP SCHEMA studenci
251/* zmiana właściciela schematu */
252ALTER AUTHORIZATION ON SCHEMA::studenci2 TO test
253/* ponieważ według Pana Tatonia, wyklikiwać to może użytkonik a nie admin, może zdarzyć się że usunięcie schematu będzie wymagało większych uprawnień
254to zapytanie rozwiąże sprawę */
255EXEC Uczelnia..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin'
256/* krótki skrypt który sprawdza czy schemat istnieje i jeśli istnieje wywala go a jak nieistnieje to go tworzy */
257USE Uczelnia
258
259IF EXISTS (SELECT * FROM sys.schemas WHERE name='studenci2')
260 BEGIN
261 EXEC ('DROP SCHEMA [studenci2]')
262 END
263ELSE
264 BEGIN
265 EXEC('CREATE SCHEMA [Studenci2]')
266 END
267/* Ważne informacje jeśli dajemy coś w [] to tylko po to żeby wykluczyć że nie jest to słowo kluczowe
268a jeśli robimy jakiś PRINT to najlepiej przed nawiasem dać N('coś tam chce wypisać') to N powoduje że zdanie jest pobierane
269znak po znaku wykluczy to literówki bądź ewentualne błędy zaś jeśli go nie ma to ściąga całego stringa, możliwe że pokażą się błędy
270w zdaniu */
271
272/* tworzy schamat użytkownika i login, użytkownika ustanawia jako właściciela schaematu są if które sprawdzają czy te dane istnieją
273jeśli istnieją to je usuwa i tworzy na nowo */
274DECLARE @schemat varchar(8)
275DECLARE @user varchar(4)
276DECLARE @login varchar(4)
277SET @login = 'test'
278SET @user = 'test'
279SET @schemat = 'studenci'
280
281USE [Uczelnia]
282
283IF EXISTS (SELECT * FROM sys.schemas WHERE name=@schemat)
284 BEGIN
285 EXEC ('DROP SCHEMA [studenci]')
286 EXEC('CREATE SCHEMA [studenci]')
287 END
288ELSE
289 BEGIN
290 EXEC('CREATE SCHEMA [studenci]')
291 END
292IF EXISTS (SELECT * FROM sys.syslogins WHERE name=@login)
293 BEGIN
294 EXEC ('DROP LOGIN test')
295 EXEC('CREATE LOGIN test WITH PASSWORD =''!QAZ@WSX33dd''')
296 END
297ELSE
298 BEGIN
299 EXEC('CREATE LOGIN test WITH PASSWORD =''!QAZ@WSX33dd''')
300 END
301IF EXISTS (SELECT * FROM sys.sysusers WHERE name=@user)
302 BEGIN
303 EXEC('DROP USER test')
304 EXEC ('CREATE USER test FOR LOGIN test')
305 EXEC ('ALTER AUTHORIZATION ON SCHEMA::studenci TO test')
306 END
307ELSE
308 BEGIN
309 EXEC ('CREATE USER test FOR LOGIN test')
310 EXEC ('ALTER AUTHORIZATION ON SCHEMA::studenci TO test')
311 END
312/* możliwe połączenia z MS SQL serwerem
313 OLE DB(zewnętrze sterowniki oferowane przez np windows
314 ODBC niezależne od języka programowania, nie ma znaczenia system operacyjny Standard interfejsu to API
315
316 Narzędzia tekstowe za pomocą konsoli tekstowej możemy
317 POłączyć się z SerweremMS SQL
318 Wprowadzania instrukcji Transact-SQL
319 itd.
320
321 SQLCMD + najnowsze narzędzie linii komend
322 OSQL - oparty na sterowniku odbc
323otwieramy konsole windows (cmd), a następnie polecenie sqlcmd -S <serwer> -U <użytkownik> -o <miejsce pliku do którego zapisuje wyniki jeśli chcemy>
324
325
326
327
328
329
330
331EXEC sp_helplogins
332EXEC sp_helplogins [użytkownik]
333
334
335CREATE LOGIN [test] WITH PASSWORD=’123456’;
336EXEC sp_addlogin [test2], ‘123456’;
337EXEC sp_addlogin [test3];
338
339
340EXEC sp_who
341
342Alter database sss SET SINGLE_USER WITH ROLLBACK
343Baza do odczytu jedne osobie z wycofaniem transakcji jeśli takie są
344ALTER DATABASE ssss SET MULTI_USER
345
346
347ALTER LOGIN test WITH PASSWORD = ‘1111’
348EXEC sp_password NULL, ‘1233’
349
350EXEC sp_defaultdb test, master;
351Ustawianie bazy maste jako domyślnej dla użytkownika test
352
353EXEC sp_defaultlanguage test, polish;
354
355CREATE LOGIN test4 WITH PASSWORD =’123456’ MUST_CHANGE DEFAULT_DATABAE=master, DEFAULT_LANGUAGE=polish, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
356ALTER LOGIN test ENABLE/DISABLE DENY CONNECT SQL TO master
357EXEC sp_helpsrvrole
358Wyświetla role
359EXEC addsrvrolemember test, securityadmin
360Dodanie roli użytkownikowi
361
362EXEC sp_dropsrvrolemember test, securityadmin
363
364
365USE uczelnia
366EXEC sp_tables -- @table_name
367EXEC sp_help
368DBCC checktable ‘pracownicy’
369
370
371WÅ‚asne typy danych:
372EXEC sp_addtype ‘kod_pocztowy’, ‘char(6)’, null
373
374Usuwanie:
375EXEC sp_droptype ‘kod_pocztowy’
376
377
378USE uczelnia
379IF OBJECT_ID’
380
381
382AUTO_INCREMENT w tSQL = IDENTITY
383
384BACKUP DATABASE nazwa
385TO DISC=’c:/bleble.bak
386WITH FORMAT,’
387MEDIANAME=’test2’,
388NAME = ‘test’’
389GO