Database Engine Analysis Services Reporting Services SQL Server
ﺍﻧﻮﺍﻉ ﺳﺮﻭیﺲ ﻫﺎ Database Engine Analysis Services Reporting Services SQL Server Mobile Integration Services
Database Backup & Restore
Database Backup type Available for Full Databases, files, and filegroups Differential Databases, files, and filegroups Transaction Log Transaction logs Restrictions Simple Recovery Model, file and ﺗﺤﺖ read-only ﻓﻘﻂ ﺑﺮﺍی filegroup backups ﻗﺎﺑﻞ ﺩﺳﺘﺮﺱ ﻫﺴﺘﻨﺪ filegroups Simple ﺑﺮﺍی Transaction log backups ﻗﺎﺑﻞ ﺩﺳﺘﺮﺱ ﻧیﺴﺖ Recovery Model ﺭﺍ backup ﺩﺭ ﺍیﻦ ﻗﺴﻤﺖ ﺍﺟﺰﺍﺀ : Backup component ﺍﻧﺘﺨﺎﺏ کﻨیﺪ ﺍیﻦ Transaction Log ﻣﺸﺨﺺ کﻨیﺪ ﺍگﺮ ﻗﺎﺑﻞ ﺩﺳﺘﺮﺱ ﻧیﺴﺖ option • Database گﺮﻓﺘﻪ ﻣیﺸﻮﺩ backup ﺍﻧﺘﺨﺎﺏ ﺷﺪﻩ database ﺍﺯ Files and filegroups ﺭﺍ ﺑﺎﺯ ﻣیکﻨﺪ ﻭ ﺷﻤﺎ Select Filegroups and Files dialog box ﺭﺍ ﺍﻧﺘﺨﺎﺏ کﻨیﺪ files یﺎ filegroups ﻣیﺘﻮﺍﻧیﺪ ﻣﺸﺨﺺ کﻨیﺪ کﻪ ﺍﻟﺒﺘﻪ ﺳیﺴﺘﻢ backup یک ﻧﺎﻡ ﺑﺮﺍی : Name یک ﻧﺎﻡ پیﺶ ﻓﺮﺽ ﻣﺸﺨﺺ ﻣیکﻨﺪ ﻭﺍﺭﺩ کﻨیﺪ Backup یک ﺷﺮﺡ ﺑﺮﺍی : Description
Database Security
Database Security : Enforce password policy • ﺍﺟﺮﺍ ﺧﻮﺍﻫﺪ ﺷﺪ )ﺩﺭ ﺣﺎﻟﺖ login ﺑﺮﺍی password policy (SQL Server authentication Password : Enforce password expiration ﺍﺟﺮﺍ ﺧﻮﺍﻫﺪ ﺷﺪ login ﺑﺮﺍی expiration policy • : User must change password at next login ﺩﺭ ﺍﻭﻟیﻦ ﺯﻣﺎﻥ user ﻣﺠﺒﻮﺭ ﻣیکﻨﺪ کﻪ Sql Server. ﺭﺍ ﺗﻐییﺮ ﺩﻫﺪ password •
Create Database
Create Table
Check Constraints
Create Index
Create Relationship
Structured Query Language(SQL)
ﺩﺳﺘﻮﺭﺍﺕ ﺗﻌﺮیﻒ ﺩﺍﺩﻩ ﻫﺎ ﺍیﺠﺎﺩ ﺍیﻨﺪکﺲ CREATE INDEX , <name> ON <table_or_view> (<index_columns>) CREATE INDEX Simple. Index ON Simple. Table(Simple. Description, Simple. ID)
Select * From Oils Select * From Properties Select Oilname , latin. Name From Oils
Select -AS. ﺑﺮﺍی ﺗﻐییﺮ ﻧﺎﻡ یک ﺳﺘﻮﻥ ﺩﺭ ﻟیﺴﺖ یﺎ ﺳﺎﺧﺘﻦ ﻧﺎﻡ ﻣﺴﺘﻌﺎﺭ ﺑﺮﺍی ﺳﺘﻮﻥ ﻫﺎ ﺍﺳﺘﻔﺎﺩﻩ ﻣیﺸﻮﺩ : As SELECT sname AS supplier. Name, city FROM S Select Oilname AS [oil name], latin. Name From Oils Select Oilname AS [oil name], latin. Name AS [latin Name] From Oils Select Oilname +’-’ + latin. Name AS [Extended Name], GETDATE() AS [Today’s Date] From Oils
Select [Top n][Percent] ﺳﻄﺮ ﺍﻭﻝ n ﺍﻧﺘﺨﺎﺏ • ﺩﺭﺻﺪ ﺍﺯ کﻞ ﺳﻄﺮﻫﺎ ﺍﺯ ﺑﺎﻻ 5 ﺑﺮﺍی ﻧﻤﺎیﺶ ﺩﺍﺩﻥ Select Top 5 Oilname , latin. Name From Oils Select Top 10 PERCENT Oilname AS [oil name], latin. Name AS [latin Name] From Oils
Select [Order By] Select Oil. ID, Oilname , latin. Name From Oils ORDER BY Oil. Name ASC Select Oil. ID, Oilname , latin. Name From Oils ORDER BY Oil. Name DESC Select Oil. ID, Oilname , latin. Name From Oils ORDER BY Oil. Name, latin. Name ﻣﺮﺗﺐ کﺮﺩﻥ ﺳﻄﺮﻫﺎ •
Select [Where]. ﺑﺮﺍی ﺍﻧﺘﺨﺎﺏ ﺗﺎپﻠﻬﺎیی ﺍﺯﺟﺪﻭﻝ کﻪ ﺷﺮﻁ ﺑﺨﺼﻮﺻی ﺩﺍﺭﻧﺪ ﺍﺳﺘﻔﺎﺩﻩ ﻣیﺸﻮﺩ : Where SELECT sname FROM S WHERE city= ‘ ’ﺗﻬﺮﺍﻥ SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (Oil. Name = ‘Eucalyptus’) ORDER BY Oil. Name DESC SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (LEFT(Oil. Name, 1)=‘R’) ORDER BY Oil. Name DESC
Select [Where] SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (Oil. Name LIKE ‘Rose%’) ORDER BY Oil. Name DESC SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (Oil. Name LIKE ‘[a-c]%’) ORDER BY Oil. Name DESC
Select [Where] : Where ﺑﻪ ﻫﻤﺮﺍﻩ ﺩﺳﺘﻮﺭ BETWEEN ﻋﻤﻠگﺮ SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (LEFT (Oil. Name, 1) BETWEEN ‘A’ AND ‘C’) ORDER BY Oil. Name DESC • : Where ﺑﻪ ﻫﻤﺮﺍﻩ ﺩﺳﺘﻮﺭ NOT BETWEEN ﻋﻤﻠگﺮ SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (LEFT (Oil. Name, 1) NOT BETWEEN ‘A’ AND ‘C’) ORDER BY Oil. Name ASC •
Select [Where] : Where ﺑﻪ ﻫﻤﺮﺍﻩ ﺩﺳﺘﻮﺭ IN ﻋﻤﻠگﺮ SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (LEFT (Oil. Name, 1) IN (‘G’, ’M’, ’V’) ORDER BY Oil. Name DESC • : Where ﺑﻪ ﻫﻤﺮﺍﻩ ﺩﺳﺘﻮﺭ NOT IN ﻋﻤﻠگﺮ SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (LEFT (Oil. Name, 1) NOT IN (‘G’, ’M’, ’V’) ORDER BY Oil. Name DESC •
Select [Where] (AND, OR, NOT) ﻋﻤﻠگﺮﻫﺎی ﻣﻨﻄﻘی SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (Oil. Name=‘Rosemary’ OR Oilname=‘Sage’) ORDER BY Oil. Name DESC SELECT Oil. ID, Oil. Name, Latin. Name FROM Oils WHERE (Oil. Name LIKE ‘Rose%’ AND Oil. ID<40) ORDER BY Oil. Name DESC •
Select HAVING. ﺑﺮﺍی ﺍﻧﺘﺨﺎﺏ گﺮﻭﻫﻬﺎیی کﻪ ﺷﺮﻁ ﺑﺨﺼﻮﺻی ﺩﺍﺭﻧﺪ ﺍﺳﺘﻔﺎﺩﻩ ﻣیﺸﻮﺩ : Having Select p# From SPJ Group by p# Having sum(qty)>20000 ﺭﺍ ﻣﺤﺪﻭﺩ GROUP BY ﺳﻄﺮﻫﺎی ﺑﺎﺯگﺸﺘی یک پﺮﺱ ﻭﺟﻮی HAVING • ﺑﺎ ﺍﺳﺘﻔﺎﺩﻩ ﺍﺯ ﻣﺎﺩﻩ ﻫﻢ Having ﺭﺍ ﻣﺤﺪﻭﺩ ﻣیکﻨﺪ Select ﺳﻄﺮﻫﺎی یک where کﻨیﺪ یﻌﻨی ﻫﻤﺎﻧﻄﻮﺭ کﻪ . ﺭﺍ ﻣﺤﺪﻭﺩ ﻣیکﻨﺪ Group by ﺳﻄﺮﻫﺎی یک SELECT Plant parts. Plan. Parts, Count(Oils. Oil. Name) as Numberof. Oils From Oils INNER JOIN Plan. Parts ON Oils. Plan. Part. ID=Plan. Parts. Plan. Part. ID GROUP BY Plan. Parts. Plan. Part HAVING Count(Oils. Oil. Name)>3
INNER JOIN ﺍﺗﺼﺎﻝ ﺩﺭﻭﻧی ﺗﻨﻬﺎ ﺳﻄﺮﻫﺎﺋی ﺭﺍ ﺑﺮﻣی گﺮﺩﺍﻧﺪکﻪ ﺷﺮﻁ ﺑﺮﻗﺮﺍﺭی ﺍﺗﺼﺎﻝ ﺑﺮﺍی . آﻨﻬﺎ ﺻﺤیﺢ ﺑﺎﺷﺪ SELECT Oils. Oil. ID, Oils. Oil. Name, Plant. Parts. Plantpart FROM Oils INNER JOIN Plant. Parts ON Oils. Plantpart. ID=Plantparts. Plantpart. ID SELECT Oils. Oil. ID, Oils. Oil. Name, Odors. Odor FROM Oils INNER JOIN Oil. Odors ON Oils. Oil. ID=Oil. Odors. Oil. ID INNER JOIN Odors ON Oils. Odor. ID=Odors. Odor. ID •
OUTER JOIN ﺗﻤﺎﻡ ﺳﻄﺮﻫﺎ ﺟﺪﻭﻝ ﺭﺍﺳﺖ یﺎ ﺟﺪﻭﻝ چپ یﺎ ﻫﺮ ﺩﻭ ﺟﺪﻭﻝ ﺭﺍ ﻫﻤﺮﺍﻩ ، ﺍﺗﺼﺎﻝ ﺑﺮﻭﻧی ﺑﺎ ﻋﺎﻣﻞ ﺍﺭﺗﺒﺎﻁ ﺩﻫﻨﺪﻩ ﻧﻤﺎیﺶ ﻣیﺪﻫﺪ SELECT Oils. Oil. ID, Oils. Oil. Name, Plant. Parts. Plantpart FROM Oils LEFT OUTER JOIN Plant. Parts ON Oils. Plantpart. ID=Plantparts. Plantpart. ID SELECT Oils. Oil. ID, Oils. Oil. Name, Properties. Property FROM Oils RIGHT OUTER JOIN Oil. Properties ON Oils. Oil. ID = Oil. Properties. Oil. ID INNER JOIN Properties ON Oil. Properties. Property. ID= Properties. Property. ID •
View CREATE VIEW <name> ( )ﻧﺎﻡ ﺳﺘﻮﻧﻬﺎی ﺩیﺪگﺎﻩ As <Select-Statements> View ﺍیﺠﺎﺩ CREATE VIEW simple. View AS SELECT Related. ID, Simple. Description, Related. Description FROM Related. Table INNER JOIN Simple. Table ON Related. Table. Simlpe. ID=Simple. Table. Simple
INSERT : ﻣی ﺗﻮﺍﻧﺪ یک ﺟﺪﻭﻝ یﺎ ﻧﻤﺎی ﻣﻨﻔﺮﺩ ﺭﺍ ﺗﻐییﺮ ﺩﻫﺪ Insert گﺰﺍﺭﻩ INSERT [INTO] table-or-view [(column_list)] VALUES (value_list) Oils یک ﻣﻘﺪﺍﺭ ﺑﻪ یکی ﺍﺯ ﺳﺘﻮﻧﻬﺎی ﺟﺪﻭﻝ Insert INSERT INTO Oils (Oil. Name) VALUES (‘Insert. From. SQL’) Oils یک ﺳﻄﺮ ﺑﻪ ﺟﺪﻭﻝ Insert INSERT INTO Oils VALUES(‘Insert. All. Values’, ’Latin. NAme’, 1, 1, ’Sample’, ’Description of the Oil’) DEFAULT ﻭ NULL ﻣﻘﺎﺩیﺮ Insert INSERT INTO Oils (Oil. Name, Latin. NAme, Sample) VALUES(‘Insert. Default’, NULL, DEFAULT)
INSERT چﻨﺪ ﺳﻄﺮ ﺟﺪیﺪ ﺩﺭ یک ﺟﺪﻭﻝ Insert INSERT [INTO] table-or-view [(column_list)] SELECT (column_list) FROM table_or_view [WHERE(condition)] ﻣﺜﺎﻝ INSERT [INTO] My. Oils (Oil. Name, Latin. Name) SELECT Oil. Name, Latin. Name FROM Oils WHERE (LEFT(Oil. Name, 6)=‘Insert’)
UPDATE ﻣﻘﺎﺩیﺮ یک یﺎ چﻨﺪ ﺳﻄﺮ ﺍﺯ یک ﺟﺪﻭﻝ ﺭﺍ ﺗﻐییﺮ ،UPDATE گﺰﺍﺭﻩ ﻣیﺪﻫﺪ UPDATE table_or_view SET update_list [WHERE (condition)] Oils یک ﻣﻘﺪﺍﺭ ﺍﺯ یک ﺳﺘﻮﻥ ﺟﺪﻭﻝ Update UPDATE My. Oils SET Sample=‘Sample Field’ Where ﺳﻄﺮﻫﺎ ﺑﺎ ﺍﺳﺘﻔﺎﺩﻩ ﺍﺯ ﺷﺮﻁ Update UPDATE My. Olis SET Description=‘Description of Frankincense’ WHERE Oil. Name= ‘Frankincense’
UPDATE FROM ﺳﻄﺮﻫﺎ ﺑﺎ ﺍﺳﺘﻔﺎﺩﻩ ﺍﺯ ﻣﺎﺩﻩ UPDATE table_or_view SET update_List FROM table_or_view join_operator join_condition [WHERE (where_condition)] UPDATE My. Oils SET My. Oils. Plant. Part. ID=Oils. Plant. Part. ID FROM My. Oils INNER JOIN Oils ON My. Oils. Oil. Name=Oils. Oil. Name
DELETE DELETE table_or_view [FROM table_sources] [WHERE where_condition] DELETE FROM My. Oils WHERE (Oil. Name=‘Basil’) View ﺍیﺠﺎﺩ DELETE Plant. Parts WHERE (Plant. Part. ID = 10 ) DELETE My. Oils FROM Plant. Parts WHERE (My. Oils. Plant. Part. ID=Plant. Parts. Plant. Part. ID) AND (Plant. Parts. Plant. Part=‘Roots’)
- Slides: 112