SQL指令練習(非12/17上課用喔)

張貼日期：Dec 16, 2013 5:22:40 PM

CREATE DATABASE MIS4G2A;

USE MIS4G2A;

CREATE TABLE 書籍(

登錄號 VARCHAR(20),

書名 VARCHAR(40) NOT NULL,

作者 VARCHAR(20),

出版社 VARCHAR(20),

出版日期 DATETIME,

專家書評 REAL,

PRIMARY KEY(登錄號),

CHECK(專家書評>=0 AND 專家書評<=10)

);

CREATE TABLE 學生(

學號 INT,

姓名 VARCHAR(20) NOT NULL,

電子郵件 VARCHAR(40),

科系 VARCHAR(20),

年齡 INT,

PRIMARY KEY(學號),

UNIQUE(電子郵件),

CHECK(年齡 >= 15 AND 年齡 <= 50)

);

CREATE TABLE 借書記錄(

學號 INT,

登錄號 VARCHAR(20),

借書日期 DATETIME DEFAULT GETDATE(),

到期日 DATETIME DEFAULT GETDATE()+60,

歸還日期 DATETIME NULL,

PRIMARY KEY(學號,登錄號),

FOREIGN KEY(學號) REFERENCES 學生(學號),

FOREIGN KEY(登錄號) REFERENCES 書籍(登錄號)

);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C165076','會計學原理','汪亞平','新文京開發','1999/05/25',7.9);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C165077','會計學原理','汪亞平','新文京開發','1999/05/25',7.9);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C141516','自慢','何飛鵬','商周/城邦文化','1997/06/26',8);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C142315','自慢','何飛鵬','商周/城邦文化','1997/06/26',8);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C171211','自慢/聰明糊塗心','何飛鵬','商周/城邦文化','1999/07/01',8.4);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C153636','專業/你的唯一生存之道','大前研一','天下遠見','1998/05/22',9);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C154949','再起動/職場絕對生存手冊','大前研一','天下遠見','1998/09/14',9.5);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C171050','中國大趨勢:八大支柱起經濟強權','約翰.奈思比','天下遠見','1999/07/01',9);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C063073','會計學原理','嚴玉珠','五南','1994/06/29',8.6);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C006113','總體經濟學','趙鳳培','三民','1979/09/01',8.5);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C006114','總體經濟學','趙鳳培','三民','1979/09/01',8.5);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C006055','經濟學概要','趙鳳培','三民','1987/03/01',8);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C006056','經濟學概要','趙鳳培','三民','1987/03/01',8);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C006117','總體經濟學','張慶輝','三民','1977/07/01',9);

INSERT INTO 書籍(登錄號,書名,作者,出版社,出版日期,專家書評)

VALUES('C130420','成本會計','嚴玉珠','五南','1993/02/27',8.5);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95433131,'陳智遠','95433131@yahoo.com.tw','資訊管理系',22);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95433121,'石智瑋','95433121@yahoo.com.tw','資訊管理系',28);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95433107,'林治勝','95433107@hotmail.com','資訊管理系',23);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95433114,'王勝為','95433114@gmail.com','資訊管理系',24);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95418107,'陳精鋒','95418107@hotmail.com','金融系',24);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95418133,'彭正敏','95418133@gmail.com','金融系',22);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95418104,'張建明','95418104@gmail.com','金融系',20);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(95418112,'張嘉浩','95418112@hotmail.com','金融系',22);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(97435137,'陳彥風','97435137@gmail.com','行銷系',18);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(97435125,'陳冠任','97435125@yahoo.com.tw','行銷系',19);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(97435122,'麥格倫','97435122@yahoo.com.tw','行銷系',18);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(97435132,'林治平','97435132@yahoo.com.tw','行銷系',19);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(96434208,'詹治堯','96434208@yahoo.com.tw','企管系',20);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(96434211,'高智崗','96434211@yahoo.com.tw','企管系',21);

INSERT INTO 學生(學號,姓名,電子郵件,科系,年齡)

VALUES(96434230,'庫倫','96434230@yahoo.com.tw','企管系',22);

INSERT INTO 借書記錄(學號,登錄號,借書日期,到期日,歸還日期)

VALUES(95433131,'C171050','1998/09/21','1998/09/21','1998/10/01');

INSERT INTO 借書記錄(學號,登錄號,借書日期,到期日,歸還日期)

VALUES(95433131,'C063073','1998/06/30','1998/09/30','1998/08/18');

INSERT INTO 借書記錄(學號,登錄號,借書日期,到期日,歸還日期)

VALUES(97435137,'C141516','1998/05/24','1998/08/24','1998/07/29');

INSERT INTO 借書記錄(學號,登錄號,借書日期,到期日,歸還日期)

VALUES(97435122,'C142315','1997/07/05','1997/10/05','1997/10/07');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(95433131,'C165076');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(97435137,'C165077');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(97435122,'C130420');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(97435122,'C171211');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(97435125,'C153636');

INSERT INTO 借書記錄(學號,登錄號)

VALUES(97435132,'C154949');

----刪除已建立的表格

DROP TABLE 借書記錄;

DROP TABLE 學生;

DROP TABLE 書籍;

----複製「學生」表格的內容成為另一個表格，名稱為student_bk

SELECT *

INTO student_bk

FROM 學生;

----新增限制式：主鍵

ALTER TABLE student_bk

    ADD CONSTRAINT CS999 PRIMARY KEY (學號);

----刪除限制式：主鍵

ALTER TABLE student_bk

    DROP CONSTRAINT CS999;

----通式：依條件撈出所需欄位及符合條件的紀錄

SELECT 欄位清單

FROM 表格名稱

WHERE 條件;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 專家書評 > 8.5;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 出版社 = '五南'  

   OR 出版社 = '天下遠見' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 出版社 = '三民' 

  AND 專家書評 > 8.8;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 出版社 <> '三民' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '%會計%' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '會計%' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '%會計' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '_經濟%' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '__經濟%' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '%經濟_' ;

----範例

SELECT 登錄號, 書名, 出版社

FROM 書籍

WHERE 書名 LIKE '%經濟__' ;

----範例(兩端點紀錄都會包括在內)

SELECT *

FROM 借書記錄

WHERE 借書日期 BETWEEN '1998-06-30' AND '1998-12-31';

----範例

SELECT *

FROM 借書記錄

WHERE 借書日期 > '1998-06-30' 

  AND 借書日期 < '1998-12-31';

----通式：降冪排序

SELECT 欄位清單

FROM 表格名稱

ORDER BY 欄位 DESC;

----通式：升冪排序

SELECT 欄位清單

FROM 表格名稱

ORDER BY 欄位 ASC;

----範例

SELECT *

FROM 借書記錄

ORDER BY 借書日期 ASC;

----範例

SELECT *

FROM 借書記錄

ORDER BY 借書日期 DESC;

----範例

SELECT *

FROM 借書記錄

ORDER BY 借書日期 ;

----SUM()    加總

----AVG()    平均

----MAX()    最大

----MIN()    最小

----COUNT()  計數

SELECT SUM(年齡)

FROM 學生;

SELECT AVG(年齡)

FROM 學生;

SELECT MAX(年齡)

FROM 學生;

SELECT MIN(年齡)

FROM 學生;

SELECT COUNT(年齡)

FROM 學生;

SELECT COUNT(*)

FROM 學生;

---- DISTINCT 可區別的

SELECT DISTINCT(書名)

From 書籍;

SELECT DISTINCT(科系)

From 學生;

SELECT DISTINCT(學號)

From 借書記錄;

SELECT DISTINCT(登錄號)

From 借書記錄;

----- 前 n 筆記錄

SELECT TOP 5 *

From 書籍;

SELECT TOP 3 書名, 出版社

From 書籍;

----找出最舊的三本書

SELECT TOP 3 書名, 出版社, 出版日期

From 書籍

ORDER BY 出版日期 ASC;

----- 前 n% 筆記錄

SELECT TOP 50 PERCENT *

From 書籍;

-----------------------------

SELECT 分組欄位,運算式

FROM 表格

GROUP BY 分組欄位;

SELECT 科系

FROM 學生

GROUP BY 科系;

SELECT 科系, COUNT(學號)

FROM 學生

GROUP BY 科系;

SELECT 科系, COUNT(學號) AS 人數

FROM 學生

GROUP BY 科系;

SELECT 科系, COUNT(*)

FROM 學生

GROUP BY 科系;

SELECT 科系, MAX(年齡)

FROM 學生

GROUP BY 科系;

SELECT 出版社, AVG(專家書評)

FROM 書籍

GROUP BY 出版社;

SELECT 出版社, COUNT(*)

FROM 書籍

GROUP BY 出版社;

SELECT 學號, COUNT(登錄號)

FROM 借書記錄

GROUP BY 學號 ;

-----------------------------

SELECT 分組欄位,運算式

FROM 表格

GROUP BY 分組欄位

HAVING 分組條件 ;

-----------------------------

SELECT 出版社, AVG(專家書評)

FROM 書籍

GROUP BY 出版社

HAVING AVG(專家書評) > 8.5 ;

SELECT 學號, COUNT(登錄號)

FROM 借書記錄

GROUP BY 學號 

HAVING COUNT(登錄號) > 2 ;

---- 聯集 

SELECT pname

FROM store1

UNION

SELECT pname

FROM store2;

---- 交集 

SELECT pname

FROM store1

INTERSECT

SELECT pname

FROM store2;

---- 差集 

SELECT pname

FROM store1

EXCEPT

SELECT pname

FROM store2;

---- 差集 

SELECT pname

FROM store2

EXCEPT

SELECT pname

FROM store1;

--差集、交集

--有借過書的學生

SELECT DISTINCT(學號)

FROM 借書記錄;

--沒借過書的學生

SELECT 學號

FROM 學生

EXCEPT

SELECT 學號

FROM 借書記錄;

--有借過書的學生

SELECT 學號

FROM 學生

INTERSECT

SELECT 學號

FROM 借書記錄;

---VIEW是什麼？ VIEW的用途？

CREATE VIEW StuView(No,Name,Department)

AS

SELECT 學號,姓名,科系

FROM 學生;

SELECT * 

FROM StuView;

SELECT No,Department 

FROM StuView;

DROP VIEW StuView;

-------------------------------------

CREATE VIEW StuViewIM(No,Name,Department)

AS

SELECT 學號,姓名,科系

FROM 學生

WHERE 科系='資訊管理系';

SELECT * 

FROM StuViewIM;

SELECT No,Department 

FROM StuViewIM;

---- 另一個左右合併的例子

DROP TABLE store1;

DROP TABLE store2;

CREATE TABLE store1

(

pname char(12) PRIMARY KEY,

amount int

);

INSERT INTO store1

VALUES ('平板電腦',300);

INSERT INTO store1

VALUES ('個人電腦',2000);

INSERT INTO store1

VALUES ('液晶螢幕',150);

INSERT INTO store1

VALUES ('筆記型電腦',1000);

CREATE TABLE store2

(

pname char(12) PRIMARY KEY,

amount int

);

INSERT INTO store2

VALUES ('個人電腦',1000);

INSERT INTO store2

VALUES ('筆記型電腦',500);

INSERT INTO store2

VALUES ('數位相機',1200);

INSERT INTO store2

VALUES ('燒錄器',100);

----等位合併

SELECT * 

FROM store1, store2

WHERE store1.pname = store2.pname;

----左合併

SELECT *

FROM store1 LEFT OUTER JOIN store2

ON store1.pname = store2.pname ;

----右合併

SELECT * 

FROM store1 RIGHT OUTER JOIN store2

ON store1.pname = store2.pname;

----------------------------------------------------------

---- 批次宣告T-SQL變數@balance且指定變數為1000，使用PRINT指令顯示結果。

DECLARE @balance int

SET @balance = 1000

PRINT @balance

---- 宣告資料表變數後，使用指令插入記錄資料後，最後在使用指令查詢資料表內容。

DECLARE @students table( std_no char(4), name varchar(12) )

INSERT @students

SELECT 學號, 姓名 FROM 學生

WHERE 性別 = '男'

SELECT * FROM @students

---- 使用IF條件判斷[教授]資料表是否有存在記錄

IF (SELECT COUNT(*) FROM 教授) >= 1

BEGIN 

PRINT 'Professors table not empty!'

PRINT '執行這裡?'

END

ELSE

PRINT 'Professors table empty!'

---- 使用迴圈計算從1加到100的總和。

DECLARE @counter int, @total int

SET @counter = 1

SET @total = 0

WHILE @counter <= 100

BEGIN

SET @total = @total + @counter

SET @counter = @counter + 1

END

PRINT '1 Add TO 100 = ' + CAST(@total AS char)

---- 建立預存程序"課程資料報表"，並且執行預存程序。

CREATE PROCEDURE 課程資料報表 

AS

BEGIN

    SELECT 課程編號, 名稱, 學分

    FROM 課程

END

EXEC 課程資料報表

---- 建立參數為[薪水查詢]的預存程序，參數是薪水salary，可以顯示員工資料。

CREATE PROCEDURE 薪水查詢

@salary money

AS

BEGIN

    SELECT 姓名, 薪水

    FROM 員工

    WHERE 薪水 > = @salary

END

EXEC 薪水查詢 @salary=20000

---- 宣告名為[學生_資料指標]的資料指標，並將資料一一顯示出來。

DECLARE 學生_資料指標 CURSOR

FOR SELECT 學號, 姓名, 電話 FROM 學生

WHERE 性別 = '男'

DECLARE @id char(5)

DECLARE @name varchar(10)

DECLARE @tel varchar(15)

OPEN 學生_資料指標

FETCH NEXT FROM 學生_資料指標 INTO @id, @name, @tel

PRINT @id + ' ' + @name + ' ' + @tel

FETCH NEXT FROM 學生_資料指標 INTO @id, @name, @tel

PRINT @id + ' ' + @name + ' ' + @tel

FETCH NEXT FROM 學生_資料指標 INTO @id, @name, @tel

PRINT @id + ' ' + @name + ' ' + @tel

FETCH NEXT FROM 學生_資料指標 INTO @id, @name, @tel

PRINT @id + ' ' + @name + ' ' + @tel

FETCH NEXT FROM 學生_資料指標 INTO @id, @name, @tel

CLOSE 學生_資料指標

DEALLOCATE 學生_資料指標

---- 在[教務系統]資料庫的[員工]資料表建立名為員工管理的觸發程序。

CREATE TRIGGER 員工管理

ON 員工

AFTER DELETE, UPDATE

AS 

    INSERT INTO 員工_1

    SELECT * FROM Deleted

DELETE FROM 員工

WHERE 身份證字號='A123456789'

---- 建立索引：主要為加速查詢的動作

---- 在表格的schema中加PRIMARY KEY或UNIQUE的限制條件，則系統會自動產生對應索引

CREATE INDEX books_idx1

ON books(bookname);

DROP INDEX books_idx1 ON books(bookname);

---- 異動管理(交易管理)Transaction Management

---- 轉帳、提款的例子

ROLLBACK 撤回 or 取消

COMIT 提交 or 確認