Transcript of 第八章 MS SQL 2005 理論與實務 ( 一 ) 資料庫系統理論與實務 [ 邏輯思維系列 ]
Ch08 MS SQL 2005()
MS SQL Server 2005(Fully Qualified Domain NameFQDN)1433/TCP
(Reliability)(Connection Oriented)TCP1433(Port)
(Port)
http://msdn2.microsoft.com/en-us/sql/aa336347.aspx
Relational Database
Analysis Services
Reporting Services()
Management Tools()
Relational Database()
[] \ [] \ [] \ [] \ [] \SQL Server (MSSQLSERVER)
8-3 MS SQL Server 2005
©2007
[] \ [Microsoft SQL Server 2005] \ [SQL Server Management
Studio]
8-4
©2007
Analysis Services
Reporting Services
(Reporting Services)SQL Server Reporting Services
SQL Server Mobile
Integration Services
(Integration Services)SQL Server Integration Services
©2007
SQL ServerSQL Server
8-5 Microsoft SQL Server Management Studio
©2007
(endpoints)(linked servers)SQL Server
SQL ServerSQL Server
(Agent)
©2007
(logic_file_name)MS SQL Server 2005
(os_file_name)
©2007
8-1 MS SQL Server 2005
MS SQL Server 2005 .mdf.ndf .ldf
MS SQL Server 2005 master
Database Engine master
©2007
8-2 8-1
C:\Program Files\Microsoft SQL Server\MSSQL.1\
MSSQL\DATA\.MDF
Size=3MB MaxSize=unlimited Filegrowth=1MB
Log file
C:\Program Files\Microsoft SQL Server\MSSQL.1\
MSSQL\DATA\.LDF
Size=1MB MaxSize=unlimited Filegrowth=10%
©2007
8-3 8-2
Log file
Log1 Log2
8-4 8-3
G1
[]
[(N)]
[(F)][][_log]
[]
[]
[(N)]
[(N)][(F)][][_log]
[P1][Log1][]
[] []
[]
MS SQL Server 2005
SQLSQL SQL
©2007
(Database)(Syntax) CREATE DATABASE database_name [ ON [ PRIMARY ] ←
[ <filespec> [ ,...n ] ← [ , <filegroup> [ ,...n ] ] ←
[ LOG ON { <filespec> [ ,...n ] } ] ← ] ] [;] []
<filespec> ::= { ( NAME = logical_file_name , FILENAME =
'os_file_name' [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE
= { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH =
growth_increment [ KB | MB | GB | TB | % ] ] ) [ ,...n ] } []
<filegroup> ::= { FILEGROUP filegroup_name [ DEFAULT ]
<filespec> [ ,...n ] }
©2007
LOG ON
©2007
FILEGROUP G1
FILEGROUP G2
LOG ON
[]
©2007
G1G2
[][][(A)]G11G12G21G22
G11G12G1G21G22G2(MB)5[]
©2007
©2007
CREATE DATABASE
LOG ON
(name=G12,filename='D:\G12.NDF',size=5MB,maxsize=5GB,filegrowth=10%)
TO FILEGROUP G1
TO FILEGROUP G2
©2007
(4)Primary
©2007
©2007
8-16(a)(Base Table)8-16(b)
CREATE TABLE
8-6[]
[][][]ON DELETE NO ACTION & ON UPDATE CASCADE [REFERENCES () ON
DELETE NO ACTION ON UPDATE CASCADE]
CREATE TABLE
[]+[]
CREATE TABLE
[][](Foreign Key)[][][]MS SQL Server 2005GETDATE()[]GETDATE()+7G1
ON G1
CREATE TABLE
DATETIME,
)
[]
table_name [ ,...n ]
[(Y)]
| ADD
8-6[][]
DROP TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
--[] []
ALTER TABLE
DATETIME ,
©2007
| derived_table
VALUES ('95121201', 'C0007', '2005/12/25', ‘8209241’, '', '')
[]
VALUES ('95121201', ‘8209241’, '2005/12/25', 'C0007', '', '')
VALUES ('95121201', 6, 30, 22)
INSERT INTO (, , , )
VALUES ('95121201', 12, 50, 10)
[]
’C0005’’S0006’
INSERT INTO (,,,,, , , )
SELECT 'S0006',,,,,,,
FROM
[ WHERE { <search_condition> } ]
WHERE = '95121201'
UPDATE SET =DEFAULT, ='', =NULL
WHERE = '95121201'
90
2
©2007
+ CAST(DATEPART(MM,GETDATE()) AS VARCHAR)+''
SELECT ''+CAST(DATEPART(YY,GETDATE()) - 1911 as VARCHAR)+''
+REPLICATE('0', 2 - LEN(CAST(DATEPART(MM,GETDATE()) AS
VARCHAR)))
+ CAST(DATEPART(MM,GETDATE()) AS VARCHAR)+''
+ CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+' '
©2007
©2007
FROM
©2007
SELECT , , ,
FROM
FROM
©2007
SELECT , ,
CAST(DATEPART(MM,) AS VARCHAR)+''+
SELECT , ,
2006
FROM
FROM
SELECT * INTO VIP
8-28
[][][][][][][][VIP]
SELECT , , ,, INTO VIP
FROM
8-29
[][][][][][][][VIP][VIP][][][][VIP]
SELECT AS VIP, AS VIP, AS , , INTO VIP
FROM
(push)
--- ---
8-30
[][][][][][][VIP][VIP][][][][VIP]
SELECT AS VIP, AS VIP, AS , , INTO VIP
FROM
[^]
©2007
SELECT , ,
SELECT , ,
SELECT ,
LIKE '[-adfg]'
LIKE '[ [ ]'
©2007
FROM , ,
WHERE . = . AND
WHERE . = . AND
8-39””””
SELECT ., , ,
FROM , , ,
WHERE . = . AND
[ FROM{ <table_source> } [ ,...n ] ]
WHERE ='C0011'
FROM
FROM ,
©2007
©2007
SELF-JOIN & INNER JOIN
. AS , . AS
FROM AS , AS
WHERE .= .
. AS , . AS
FROM AS
©2007
SELF-JOIN & LEFT OUTER JOIN
. AS , . AS
FROM AS
©2007
E3., E3., E4., E4.
FROM AS E1
©2007
(External Schema)
8209241
AS
select_statement
WHERE E. = O.
WHERE E. = O.
[ WITH { [ ENCRYPTION ] [ SCHEMABINDING ] } [ ,...n ] ]
WHERE E. = O.
WHERE E. = O.
WHERE E. = O.
WHERE E. = O. AND = ‘’
WITH CHECK OPTION
©2007
8-60
©2007