中華大學 104 5/4資料庫系統實習範例

5-4 DB實習.sql

-- 建立資料庫
CREATE DATABASE Sample;

-- 接下來都對這個資料庫操作
USE Sample;

-- 建立表格
CREATE TABLE Student (
	S# int NOT NULL PRIMARY KEY,
	SName varchar(50) NULL,
	Department char(2) NULL,
	Class char(2) NULL,
	AGE int NULL,
	CITY varchar(50) NULL
);

-- 輸入資料
-- 此範例按照講義第一章P10
INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9401001', 'Albert', 'CS', '2A', '20', 'Hsinchu');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9401002', 'Brain', 'CS', '2B', '20', 'Taipei');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9402001', 'Cathy', 'EE', '2A', '19', 'Hsinchu');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9403001', 'David', 'AP', '2A', '20', 'Taichung');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9501001', 'Ellen', 'CS', '1A', '19', 'Hsinchu');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9502001', 'Fiona', 'EE', '1A', '19', 'Taichung');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9502002', 'Grace', 'EE', '1B', '20', 'Kaohsiung');

INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9503001', 'Howard', 'AP', '1A', '19', 'Hualien');

-- 查詢學生表格的所有欄位、所有資料
SELECT * FROM Student;

-- 查詢住在新竹的學生
-- 參考講義第一章P11
SELECT SName, Department, Class
FROM Student
WHERE CITY = 'Hsinchu';

-- 移除年齡低於20歲的學生（不要誘拐小朋友阿～）
-- 參考講義第一章P12
DELETE FROM Student
WHERE AGE < 20;

-- 再新增一筆資料
-- 參考講義第一章P13
INSERT INTO Student (S#, SName, Department, Class, AGE, CITY)
VALUES ('9504001', 'Isaac', 'IM','1B', '20','Taitung');

-- 修改在2B班上且在CS部門的資料為BI部門
-- 參考講義第一章P14
UPDATE Student
SET Department = 'BI'
WHERE Class = '2B' AND Department='CS';