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 確認