* TABLE *
************************************/
/* SQL Server 2012 table 特徵 */
-- 理論上 可以有20億個 table
-- 每個 table 定義 1024 個 column
-- 每筆 record 最多 8k bytes
-------------------------------------------------------------
/******************
* CREATE TABLE *
******************/
--(1) SQL -- DDL
--(2) GUI Utiltiy -- SQL Server Management Studio
--------------------------------------------------------------
/* (1) CREATE TABLE USE SQL (DDL) */
-- Syntax of CREATE TABLE
/*
CREATE TABLE
[database_name.[schema]. | schema. ] table_name
( {
| column_name AS computed_column_expression
|
[, ...n])
[ON { filegroup | "default"}]
[TEXTIMAGE_ON { filegroup | "default"}]
[FILESTREAM_ON { filegroup | "default"}]
*/
/*
[COLLATE collation_naem ]
[DEFAULT constraint_expression]
| IDENTITY [ (seed, increment) [ NOT FOR REPLICATION]]]
[ROWGUIDCOL]
[
*/
/*
{ [ NULL | NOT NULL]
| [ {PRIMARY KEY | UNIQUE}
[CLUSTERED | NOCLUSTERED]
[WITH FILLFACTOR =fillfactor]
[ON {filegroup | DEFAULT}]]
| [FOREIGN KEY]
REFERENCES ref_table [ (ref_column)]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
[NOT FOR REPLICATION]
| CHECK [ NOT FOR REPLICATION]
(logical_expression)}
*/
---------------------------------------------------------------------------------
/* table name */
-- \\domain_name\server_name\instance_name\databse_name.schema_name.table_name
---------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
/* simple example: */
create database mis
go
USE mis
GO
-- 員工基本資料檔 (employee)
CREATE TABLE employee
(
emp_id char(8),
emp_name char(12),
sex char(2),
address char(24),
salary numeric(10,1)
)
GO
-- 驗證
sp_help employee
select * from employee
-- 練習:
CREATE TABLE tmp1
(p_id char(10),
f_name varchar(20),
l_name varchar(10),
birth datetime )
go
---------------------------------------------------------------
/* 範例資料庫 MIS */
-- employee 員工資本資料表
-- order_master 訂單主檔
-- order_detail 訂單明細檔
-- products 料品資本資料檔
-- customer 客戶基本資料檔
-- gallery 圖庫資料檔
------------------------------------------------------------------------------------------
/* FIELD ATTRIBUTE 欄位屬性 */
-- column_name
-- data_type
-- NULL/NOT NULL
-- COLLATE collation_name (定序)(char,varchar,text,nchar,nvarchar,ntext)
-- DEFAULT constant_expression
-- IDENTITY(seed,increment) (欄位值是 "資料表" 唯一的)
-- ROWGUIDCOL (欄位值是 "全世界" 唯一的)
-- 一個資料表中最多只能有一個 IDENTITY 及 ROWGUIDCOL 欄位
-- FILESTREAM
/* FIELD CONSTRAINT 欄位限制 */
-- NULL / NOT NULL (about NULL)
-- PRIMARY KEY
-- UNIQUE
-- DEFAULT
-- CHECK
/* TABLE CONSTRAINT */
-- FOREIGN KEY
-- 指定檔案群組
-- COMPUTED COLUMN
------------------------------------------------------------------------------
/***************************
* ALTER TABLE *
***************************/
------------------------------------------------------------------------------
/***************************
* DROP TABLE *
***************************/
-------------------------------------------------------------------------------
/* TEMPORARY TABLE */
==============================================
/**************************
* CREATE TABLE *
**************************/
-- method (1) SQL (DDL)
-- example 1: ( order_master 訂單主檔 )(IDENTITY, PRIMARY KEY, UNIQUE)
USE mis
GO
CREATE TABLE order_master
(
order_no int IDENTITY(100,1) CONSTRAINT pk_order_no PRIMARY KEY,
-- IDENTITY 會自動給號 從100開始逐次增加1
-- CONSTRAINT pk_order_no 給主鍵名字, 不打的話系統會自己給
cust_id char(8),
order_date datetime,
ship_date datetime,
sales_id char(8),
invoice_no char(10) UNIQUE,
-- UNIQUE 唯一鍵
total_amt numeric(10,1)
-- numeric(10,1) 十進位的數字 一共有10位數, 其中一個為小數
)
GO
-- identity 說明
-- 產生序號的方法各家資料庫做法不同
a. Oracle --> ROWID ; SEQUENCE
b. SQL Anywere --> NUMBER(*)
c. MySQL --> AUTOINCREMENT 欄位屬性
-- E. M.
-- data type datatime 說明
-- datetime 資料型別 90/01/01 12:00:00:000
-- date 資料型別 (SQL server 2005 以後)
-- e.g.
INSERT INTO order_master(cust_id,order_date,invoice_no)
VALUES('C007','2010/01/01','INV009')
SELECT * FROM order_master
--練習: date 資料型別
CREATE TABLE order_master_2
(
order_no int IDENTITY(100,1) PRIMARY KEY,
cust_id char(8),
order_date date,
ship_date date,
sales_id char(8),
invoice_no char(10) UNIQUE,
total_amt numeric(10,1)
)
GO
INSERT INTO order_master_2(cust_id,order_date,invoice_no)
VALUES('C007','2010/01/01','INV009')
SELECT * FROM order_master_2
-------------------------------------------------------------------------------------------
-- example 2: ( order_master 訂單主檔 ) (NULL /NOT NULL ; UNIQUE)
-- 先移除 old table
DROP TABLE order_master
-- 重新 create table
USE mis
GO
CREATE TABLE order_master
(
order_no char(10),
cust_id char(8),
order_date char(10),
ship_date char(10),
sales_id char(8) NOT NULL, /* 一定要有接單人 */
invoice_no char(10) UNIQUE, /* 系統給定 unique constraint name */
total_amt numeric(10,1),
PRIMARY KEY(order_no)
)
GO
-- about NULL: NULL 表示資料中有了
a. 漏失
b. 未知
c. 雜類
d. 不恰當的值
-- 在數學運算式中使用NULL,運算結果也會是NULL
-----------------------------------------------------------------------------------
-- example 3: (customer 客戶基本資料檔) (DEFAULT 說明)
USE mis
GO
CREATE TABLE customer
(
cust_id char(8) PRIMARY KEY,
cust_name varchar(20),
contant varchar(20), /* 聯絡人 */
tel varchar(14), /* 聯絡電話 */
address varchar(24),
cust_level char(1) DEFAULT 'B' /* 預設等級為 'B' */
)
-- 測試 (1): E. M.
-- 測試 (2): INSERT
INSERT INTO customer (cust_id, cust_name)
VALUES ('C005','三七仲介')
-- 驗證:
SELECT * FROM customer
------------------------------------------------------------------------------------------------
-- example 4: (customer 客戶基本資料檔) (part II : CHECK 說明)
-- 先移除 old table
DROP TABLE customer
-- 重新 create table
CREATE TABLE customer
(
cust_id char(8) PRIMARY KEY,
cust_name varchar(20),
contant varchar(20), /* 聯絡人 */
tel varchar(14), /* 聯絡電話 */
address varchar(24),
cust_level char(1) DEFAULT 'B', /* 預設等級為 'B' */
CONSTRAINT ck_contant_1
CHECK (tel is not null or address is not null) /* 一定要有電話或住址 */
)
-- 測試 : INSERT
INSERT INTO customer (cust_id, cust_name)
VALUES ('C005','三七仲介') -- X
INSERT INTO customer (cust_id, cust_name, tel)
VALUES ('C005','三七仲介','0204-858585') -- OK
SELECT * FROM customer
-- 練習: (check)
table employee2
p_id char(10)
f_name varchar(20)
l_name varchar(20)
sex char(2) {'男','女',NULL}
salary numeric(10,2) {15680~100000}
-- 解答:
create table employee2
(p_id char(10),
f_name varchar(20),
l_name varchar(20),
sex char(2),
salary numeric(10,2),
check (sex in ('男', '女')),
check (salary between 15680 and 100000))
go
-- 測試:
insert into employee2
values('E005','jack','huang','中',30000)
go
insert into employee2
values('E005','jack','huang','男',300000)
go
insert into employee2
values('E005','jack','huang','男',30000)
go
-- 猜猜看? 可不可以?為甚麼?
insert into employee2(p_id,f_name,l_name)
values('E005','jack','huang')
go
------------------------------------------------------------------------------------
/* TABLE CONSTRAINT */
-- FOREIGN KEY
-- REFRENCE CONSTRAINT
-- (Type I ) : Update / Delete Restrict
-- (Type II) : Update Cascade
-- (Type III): Delete Cascade
-- (Other ) : Set null / Set Default
/* example 5: ( order_detail 訂單明細檔 )
( foreign key and referential integrity constraint 說明) */
USE mis
GO
CREATE TABLE order_detail
(
order_no char(10), -- foreign key
item_no int, -- 項次
product_id char(15), -- 料品代碼
qty smallint,
unit_price decimal(10,1),
note varchar(28), -- 備註
PRIMARY KEY (order_no, item_no),
FOREIGN KEY (order_no) REFERENCES order_master(order_no)
)
GO
-- 測試
-- INSERT (order_master)
INSERT INTO order_master (order_no, sales_id, invoice_no)
VALUES ('P0111001','E0011023','RTS87875')
SELECT * FROM order_master
--INSERT (order_detail)
INSERT INTO order_detail (order_no, item_no, product_id, qty)
VALUES ('P0111001',1,'rg58009',78)
INSERT INTO order_detail (order_no, item_no, product_id, qty)
VALUES ('P0111001',2,'ju68007',88)
SELECT * FROM order_detail
--INSERT (違反參考整合性限制的操作)
INSERT INTO order_detail (order_no, item_no, product_id, qty)
VALUES ('P0111008',1,'nikonfm2',1) --X
--DELETE (違反參考整合性限制的操作)
DELETE FROM order_master
WHERE order_no = 'P0111001' -- X
----------------------------------------------------------------------------------------------
-- FOREIGN KEY (part II) (以 database diagram 說明)
/* example 5: (products 料品基本資料檔 )
( foreign key and referential integrity constraint 說明) */
CREATE TABLE products
(
product_id char(15) primary key,
product_name varchar(24),
spec varchar(24),
unit varchar(10)
)
-- Management Studio
-- order_detail(product_id) references to products(product_id)
DELETE FROM order_detail
---------------------------------------------------------------------------------
/* Delete Cascade 串聯刪除 */
/* example 6: (訂單主檔/訂單明細檔 串聯刪除) */
-- 先 Drop table order_detail
DROP TABLE order_detail
-- 重新 CREATE TABLE order_detail
CREATE TABLE order_detail
(
order_no char(10), -- foreign key
item_no int, -- 項次
product_id char(15), -- 料品代碼
qty smallint,
unit_price decimal(10,1),
note varchar(28), -- 備註
PRIMARY KEY (order_no, item_no),
FOREIGN KEY (order_no) REFERENCES order_master(order_no)
ON DELETE CASCADE
)
-- 驗證 :
INSERT INTO order_detail (order_no, item_no, product_id, qty)
VALUES ('P0111001',1,'rg58009',78)
INSERT INTO order_detail (order_no, item_no, product_id, qty)
VALUES ('P0111001',2,'ju68007',88)
SELECT * FROM order_master
SELECT * FROM order_detail
-- 刪除 order_master.order_no='P0111001'
DELETE FROM order_master
WHERE order_no='P0111001'
-- 看 order_master/order_detail 結果
SELECT * FROM order_master
SELECT * FROM order_detail
--------------------------------------------------------------------------------
/* Update Cascade 串聯更新 */
/* example 7: (訂單主檔/訂單明細檔 串聯更新) */
-- 先 Drop table order_detail
DROP TABLE order_detail
-- 重新 CREATE TABLE order_detail
CREATE TABLE order_detail
(
order_no char(10), -- foreign key
item_no int, -- 項次
product_id char(15), -- 料品代碼
qty smallint,
unit_price decimal(10,1),
note varchar(28), -- 備註
PRIMARY KEY (order_no, item_no),
FOREIGN KEY (order_no) REFERENCES order_master(order_no)
ON UPDATE CASCADE
)
-------------------------------------------------------------
-- 練習:重新 CREATE TABLE order_detail :
1.foreign key (order_no) references to order_master (order_no)
串聯更新及串聯刪除。
2.foreign key (product_id) references to products(product_id)
預設阻止策略。
======================================================================
/* 建立計算欄位 (Computed column) */
-- example:
USE mis
GO
CREATE TABLE eva
( eva_id char(8),
product_id char(15),
unit_price numeric(10,2),
qty int,
total_money as qty*unit_price -- computed column
)
-- test:
-- input data to table eva
INSERT INTO eva
VALUES ('EVA0001','S0098078',10.3,2500)
INSERT INTO eva
VALUES ('EVA0002','S0098099',0.7,12000)
SELECT * FROM eva
======================================================================
/* ROWGUIDCOL (全域唯一識別碼)屬性說明 */
/* Example 8: 圖庫資料表 */
-- create gallery table
USE mis
GO
DROP TABLE gallery
GO
CREATE TABLE gallery
(local_id int IDENTITY (10001, 1),
global_id uniqueidentifier DEFAULT NEWID() ROWGUIDCOL, -- 新增紀錄時自動產生全域唯一值
g_file_name nvarchar(20) NOT NULL,
create_date date DEFAULT CONVERT(date, GETDATE())
)
-- INSERT 圖檔檔案 測試
INSERT INTO gallery(g_file_name)
VALUES ('d:\5592.jpg')
INSERT INTO gallery(g_file_name)
VALUES ('d:\92.jpg')
-- SELECT 測試
SELECT * FROM gallery
/* 一個資料表中最多只能有一個 IDENTITY 欄位及一個ROWGUIDCOL欄位,
而且在查詢時還可以用 $IDENTITY 及 $ROWGUID 來代表這二個欄位 */
-- SELECT 測試
SELECT $ROWGUID
FROM gallery
WHERE $IDENTITY = 10001
==============================================================
/* FILESTREAM 說明 */
/* 1.針對VARBINARY(MAX)資料型別，微軟從SQL Server 2008開始所提供的一種
資料儲存方式。該方式就是透過資料庫引擎直接將VARBINARY(MAX)的資料
儲存在作業系統，此稱之為檔案資料流(FileStream)
2.指定此欄位的值要以『檔案』形式個別儲存,
但前題是必須先啟用所屬 SQL Server 伺服器及資料庫的 FILESTREAM 功能。
3.只能用於 varbinary(max) 型別的欄位, 通常用來儲存文字檔、
圖檔、影片、或 Word、Excel 等文件檔的內容。
4.使用 FILESTREAM 時有一個限制, 那就是在資料表中還必須有一個ROWGUIDCOL欄位,
而且已設定 NOT NULL 及『 UNIQUE 或 PRIMARY KEY』條件約束。*/
/* Example 9: FILESTREAM */
-- CREATE DATEBASE exfs (CONTAINS FILESTREAM)
USE master
GO
DROP DATABASE exfs
GO
CREATE DATABASE exfs
ON
PRIMARY
(NAME = exfs, FILENAME = 'd:\mssql\exfs.mdf'),
FILEGROUP fs_group1
CONTAINS FILESTREAM
(NAME = fs1, FILENAME = 'd:\mssql\fs1') -- FILESTREAM 資料夾
LOG ON
(NAME = exfs_log, FILENAME = 'd:\mssql\exfs.ldf')
-- create table doc1 on database exfs
CREATE TABLE exfs..doc1
(doc_no uniqueidentIfier DEFAULT NEWID() ROWGUIDCOL NOT NULL UNIQUE,
d_file_name nvarchar(20) NOT NULL,
content varbinary(max) FILESTREAM
)
-- insert one record into file doc1
INSERT exfs.dbo.doc1 (d_file_name,content)
VALUES ('d:\BOM.doc',CONVERT(varbinary(max),'檔案文字儲存測試檔案文字儲存測試...'))
-- 查詢 FILESTREAM 欄的內容
SELECT *
FROM exfs.dbo.doc1 -- content 格式不對
SELECT d_file_name, CONVERT(varchar(max),content)
FROM exfs.dbo.doc1
-------------------------------------------------------
/* Example 9-1: FILESTREAM 說明 (create table myphoto)*/
-- 啟動SQL Server FILESTREAM 功能
SQL Server 組態管理員-->右鍵/內容-->FILESTREAM 頁面
--建立FILESTREAM資料表myphoto
USE AdventureWorks
GO
DROP TABLE myphoto
GO
-- 設定database 中FILESTREAM group 和 FILESTREAM datafile
filestream.doc (說明圖檔)
-- create table myphoto with filestream field
CREATE TABLE myphoto
(row_guid uniqueidentifier ROWGUIDCOL UNIQUE DEFAULT (NEWID()) NOT NULL,
-- 非NULL 的唯一 ROWGUID 資料行
myid int identity not null,
-- 圖片代號
photoname nvarchar(100),
-- 圖片名稱
photo VARBINARY(max) FILESTREAM null
-- 儲存圖片的大數值類型資料行
)
----------------------------------------------------
-- filestream.hdr 紀錄每一個檔案資料流資料表使用路徑
filestream.doc (說明圖檔)
-- 使用filestream table
SELECT * FROM myphoto
-- 使用filestream table 利用 OPENROWSET 新增大型 BLOB 資料
INSERT INTO myphoto(photoname,photo)
SELECT N'天后宮.jpg',a.*
FROM OPENROWSET (BULK 'd:\temp\天后宮.jpg',SINGLE_BLOB) a
GO
INSERT INTO myphoto(photoname,photo)
SELECT N'高雄港.jpg',a.*
FROM OPENROWSET (BULK 'd:\temp\高雄港.jpg',SINGLE_BLOB) a
GO
-- 測試: 檢視FILESTREAM所在位置 .PathName()
USE AdventureWorks
GO
SELECT photoname, photo.PathName() AS BLOB_PATH
FROM dbo.myphoto
GO
====================================================================
/***************************
* ALTER TABLE *
***************************/
-- syntax of ALTER TABLE
ALTER TABLE
ADD [COLUMN]
| ALTER [COLUNM]
| DROP [COLUMN]
| ADD
| DROP CONSTRAINT
-----------------------------------------------------------------------------------
-- example: table employee
-- schema of table employee
USE mis
GO
sp_help employee
----------------------------------------------------------------
/* alter table add column */
-- 新增電話 (tel ) 欄位
ALTER TABLE employee
ADD tel char(10)
ALTER TABLE employee
ADD fax char(10)
--verify:
sp_help employee
----------------------------------------------------------
/* alter table alter column */
-- 修改電話欄位長度
-- (注意: instance ; 前端開發工具)
ALTER TABLE employee
ALTER COLUMN tel char(18)
--verify:
sp_help employee
-----------------------------------------------------------
/* alter table drop column */
ALTER TABLE employee
DROP COLUMN fax
--verify:
sp_help employee
------------------------------------------------------------
/* alter table add primary key constraint */
ALTER TABLE employee
ADD CONSTRAINT pk_const_1 -- constraint name
PRIMARY KEY (emp_id) -- X
-- 先修改
ALTER TABLE employee
ALTER COLUMN emp_id char(8) NOT NULL
-- 再加 PRIMARY KEY CONSTRAINT
ALTER TABLE employee
ADD CONSTRAINT pk_const_1 -- constraint name
PRIMARY KEY (emp_id) -- OK.
----------------------------------------------------------------------
/* alter table add unique constraint */
-- 公司不允許同名同姓
ALTER TABLE employee
ADD CONSTRAINT uniq_const_1
UNIQUE (emp_name)
/* alter table delete constraint */
-- delete unique constraint
ALTER TABLE employee
DROP CONSTRAINT uniq_const_1
-----------------------------------------------------------------------
/* DEFAULT part I */
ALTER TABLE employee
ADD is_foreign_worker char(1) DEFAULT 'F'
/*DEFAULT part II */
ALTER TABLE employee
ADD hired_date char(10) DEFAULT
substring(cast(getdate() as varchar),1,10)
--練習:
USE mis
GO
CREATE TABLE product_2
(product_id nchar(12),
product_name nvarchar(40),
spec nvarchar(40),
listprice numeric(10,2))
修改 TABLE product_2:
1. product_id 為 primary key
2. spec 為 unique
3. listprice 值在 0.00~1000000.00 間 (預設為 0.00)
===================================================
/***************************
* DROP TABLE *
***************************/
-- syntax of DROP TABLE
DROP TABLE
===================================================
/*****************************
* DATA TYPES *
*****************************/
/* 使用於 */
-- 資料表欄位
-- 變數
-- 參數
/* 種類 */
-- 系統內建資料型別
-- 使用者自訂資料型別
-------------------------------------------------
/* 資料型別 (data types) 四個屬性 */
-- 資料種類
-- 儲存值長度或大小
-- 精確度
-- 小數位數
--------------------------------------------------
/* 系統內建資料型別 */
-- 數值型別
-- 整數 (Integer)
-- int (4 bytes, -2147483648~2147483647)
-- smallint (2 bytes, -32768~32767)
-- tinyint (1 byte, 0 ~ 255)
-- bigint (8 bytes,...)
-- bit (0,1, null)
-- 精確位數 (Exact numeric)
-- numeric(精確度p,小數位數s)
-- decimal(精確度p,小數位數s)
-- default numeric(18,0)
-- 近似值 (Approximate)
-- float
-- real
-- 日期時間 (Data and Time)
-- datetime (1753/1/1~9999/12/31)
-- datetime2 (0001-01-01~9999-12-31)
-- smalldatetime (1900/1/1~2079/6/6)
-- date (0001:01:01~9999:12:31)
-- time (00:00:00.000000~23:59:59.999999)
-- 字串
-- char(n) (1~8000 character)
-- varchar(n) (1~8000 character)
-- varchar(max) (1~(2 31) -1 character) (2GB)
-- text (1~(2 31) -1 character) (2GB) (已不建議使用)
-- Unicode 字串 (2 bytes per character)
-- nchar(n) (1~4000 character)
-- nvarchar(n) (1~4000 character)
-- nvarchar(max) (1~(2 30) -1 character) (2GB)
-- ntext (1~(2 30) -1 character) (2GB) (已不建議使用)
-- binary (二元碼字串)
-- binary (n)
-- varbinary (n)
-- varbinary (max) (用來儲存 Word, Excel, *.gif, *.jpeg)
-- image (已不建議使用)
-- 貨幣
-- money
-- smallmoney
-- 標記
-- timestamp (sql 7.0)
-- uniqueidentifier
-- rowversion (取代 timestamp, 紀錄資料更新的時間戳記)
-- XML
-- 在 SQL Server 中儲存 XML 格式的欄位, 內容是符合 XML 格式的文件。
-- 資料的內容也可以利用 XML Schema 和 XML Query來規範及查詢。
-- 相較於關聯式資料庫的正規化分析
-- 空間資料
-- 兩種資料型別：
-- geometry (儲存平面資料)
-- geography (儲存橢圓體資料, 例如 GPS 的經緯度座標)
-- 線上叢書--> 『設計及實作空間儲存體』 主題
-- GOOGLE MAP
-- 變數資料型別 (sql_variant,cursor,table)
-- sql_variant
-- 可存放各種資料型別,除了text,ntext,image,timestamp,sql_variant)
-- e.g. (OK)
DECLARE @var1 sql_variant,@var2 sql_variant
SET @var1=65000
SET @var2=34
SELECT CAST(@var1 AS int)+CAST(@var2 AS tinyint)
-- e.g. (NOT OK)
DECLARE @var1 sql_variant,@var2 sql_variant
SET @var1=65000
SET @var2=34
SELECT CAST(@var1 AS int)+@var2
-- cursor (**1)
-- table 資料型別 (**2)
-- (**1,**2 只能用在程式設計中)
---------------------------------------------------------------------------------------------
-- 數值資料 (numeric, decimal)
numeric(精確度p,小數位數s)
p: 1~38(不含小數點), s: -84~127
numeric：代表+(1x10-130 ~ 9.99x10125)或
-(1x10-130 ~ 9.99x10125)或0之實數
numeric(p)=numeric(p,0)
-- example:
USE mis
GO
CREATE TABLE test2
(product_name VARCHAR(25),
procudt_price NUMERIC(4,2)
)
INSERT INTO test2 VALUES('p1',1);
INSERT INTO test2 VALUES('P2',2.5);
INSERT INTO test2 VALUES('p3',50.75);
INSERT INTO test2 VALUES('P4',99.99);
INSERT INTO test2 VALUES('P5',90.999);
-- 查詢測試
SELECT * FROM test2;
-- 錯誤示範
INSERT INTO test2 VALUES('P6',101.5);
-- 練習: 猜猜看
INSERT INTO test2 VALUES('P7',99.999);
DROP TABLE test2;
----------------------------------------------------------------
--日期型別
. date (ANSI SQL-92)
. datetime(SQL Server 2005)
. ANSI 預設日期格式: yyyy.mm.dd
. oracle 中文繁體環境預設日期格式: dd-MON-yy(yyyy)
. SQL Server 預設日期格式: CAST() & CONVERT()
-- example:
select getdate()
select convert(char(10),getdate(),101) --U.S.
select convert(char(10),getdate(),102) --ANSI
select convert(char(10),getdate(),111) --JAPAN,TAIWAN
-- 練習(新的日期函數:SysDateTime())
select SYSDATETIME()
-- Julian Dates (朱利亞日期)
. 系統初始日定為 January 1, 4712 BC (理論上)
. 實務上 1900.1.1
-- example:
CREATE TABLE test3
(product_name VARCHAR(25),
procust_price NUMERIC(4,2),
purchase_date DATETIME
);
INSERT INTO test3 VALUES('p1',1,'11/12/2006');
INSERT INTO test3 VALUES('P2',2.5,'2006-11-12');
INSERT INTO test3 VALUES('p3',50.75,'2006.10.10');
INSERT INTO test3 VALUES('P4',99.99,'06/11/02');
SELECT * FROM test3;
DROP TABLE test3;
-----------------------------------------------------------------------------
/* UNICODE */
-- What is unicode (Universal Character Set, UCS)
-- 一個標準, 把每個字元關聯到一個整數 (數值由 0 到 65536)
-- Version : UCS2, UCS3, UCS4 beta
-- 字元形別 (非 Unicode): char, varchar, text
-- 字元形別 (Unicode) : nchar,nvarchar,ntext
--要把資料定義為 nchar 或是 nvarchar 時,在字串前加上大寫 N
-- e.g.
USE tempdb
GO
-------------------------- create table Customers
CREATE TABLE Customers
(cus_id char(8) PRIMARY KEY,
cus_name nvarchar(40) )
----------------------------------- insert data into Customers
INSERT INTO Customers
VALUES ('C0001',N'國際標準公司')
SELECT * FROM Customers
--------------------------------------------------------------------
/* 進階討論1 */
/* 二進位資料(binary)*/
-- Varbinary(MAX) : 最大可到2G Bytes
--example:
use mis
go
-- create table TbImage
CREATE TABLE TbImage(
FileId int IDENTITY PRIMARY KEY,
FileName nvarchar(60),
FileType nvarchar(20),
ImageFile varbinary(MAX))
go
-- insert data into table TbImage use select clause
INSERT INTO TbImage (FileName, FileType, ImageFile)
SELECT 'img010.jpg' as FileName, 'JPG' as FileType,
* FROM openrowset(bulk N'C:\IMG010.JPG', SINGLE_BLOB) as ImageFile
-- 查詢驗證
SELECT * FROM TbImage
-- 使用驗證 (VS2012)
VS2012-->檔案-->新增專案-->Windows Form 應用程式(c#) -->
工具 --> 連接到資料庫
工具箱 --> PictureBox --> DataBinds --> Image
--> SizeMode --> StrechImage
/* XML */
=================================================
/* 隱含轉換 implicit conversion */
-- CAST, CONVERT
=================================================
/* 習題: */
-- create emp_sal 員工薪資檔(員工代號,本薪,職務津貼,交通津貼,膳食費)
-- create emp_edu 員工訓練紀錄 (員工代號,受訓日期,課程名稱,概要)
-- alter table add reference constraint from table order_master to customer
===============================================================================
/* TEMPORARY TABLE (暫存資料表) */
-- 會放在 tempdb 資料庫中
-- 離線後會自動被刪除
-- 種類:
-- 區域暫存資料表: 以#開頭，只有建立者可以使用
-- 全域暫存資料表: 以##開頭, 所有的使用者都可以使用
-- example:(dbo,dbuser01)
USE mis
GO
CREATE TABLE #order_1 (od_no char(8), qty int) --區域暫存資料表
CREATE TABLE ##order_2 (order_no char(8), qty int) --全域暫存資料表
CREATE LOGIN dbuser01
WITH Password='123' -- create a login account
CREATE USER dbuser01 FOR LOGIN dbuser01 -- create a database account
測試1: (dbo)
SELECT * FROM #order_1
SELECT * FROM ##order_2
測試2: (變更身分 dbuser01 並從另一query/session 連接)
新增查詢-->變更連接-->SQL Server 認證 (dbuser01/123) 登入
執行:
SELECT * FROM #order_1 --no go
SELECT * FROM ##order_2 --ok
/***************************
* 特殊資料表 *
***************************/
-- 寬型資料表
-- 壓縮資料表
-- FILESTREAM資料表
-- 資料分割資料表
0 件のコメント:
コメントを投稿