SQL指令上機教學
張貼日期：Dec 03, 2013 5:2:12 AM
----請複製以下指令，依教學指示執行----
1.
CREATE DATABASE bob;
2.
USE bob;
2.5
DROP TABLE Orders;
DROP TABLE Books;
DROP TABLE Bookstores;
3.
CREATE TABLE Books
(
id int NOT NULL,
bookname char(20) NOT NULL,
author char(10),
price int,
publisher char(25),
PRIMARY KEY (id)
);
CREATE TABLE Bookstores
(
no int NOT NULL,
name char(10) NOT NULL,
rank int,
city char(8),
PRIMARY KEY (no)
);
CREATE TABLE Orders
(
no int NOT NULL,
id int NOT NULL,
quantity int,
PRIMARY KEY (no,id),
FOREIGN KEY (no) REFERENCES Bookstores (no),
FOREIGN KEY (id) REFERENCES Books (id)
);
4.
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (1, '三國演義', '羅貫中', 120, '古文出版社');
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (2, '水滸傳', '施耐庵', 170, '中庸出版社');
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (3, '紅樓夢', '曹雪芹', 170, '春秋出版社');
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (4, '西遊記', '吳承恩', 140, '聊齋出版社');
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (5, '水經注', '酈道元', 120, '易經出版社');
INSERT INTO Books (id, bookname, author, price, publisher)
VALUES (6, '道德經', '老子', 190, '大唐出版社');
INSERT INTO Bookstores (no, name, rank, city)
VALUES (1, '巨蟹書局', 20, '臺北市');
INSERT INTO Bookstores (no, name, rank, city)
VALUES (2, '射手書局', 10, '高雄市');
INSERT INTO Bookstores (no, name, rank, city)
VALUES (3, '水瓶書店', 30, '新竹市');
INSERT INTO Bookstores (no, name, rank, city)
VALUES (4, '天秤書局', 20, '臺中市');
INSERT INTO Bookstores (no, name, rank, city)
VALUES (5, '獅子書局', 30, '臺南市');
INSERT INTO Orders (no, id, quantity)
VALUES (1, 1, 30);
INSERT INTO Orders (no, id, quantity)
VALUES (1, 2, 20);
INSERT INTO Orders (no, id, quantity)
VALUES (1, 3, 40);
INSERT INTO Orders (no, id, quantity)
VALUES (1, 4, 20);
INSERT INTO Orders (no, id, quantity)
VALUES (1, 5, 10);
INSERT INTO Orders (no, id, quantity)
VALUES (2, 1, 30);
INSERT INTO Orders (no, id, quantity)
VALUES (2, 2, 40);
INSERT INTO Orders (no, id, quantity)
VALUES (3, 2, 20);
INSERT INTO Orders (no, id, quantity)
VALUES (4, 2, 20);
INSERT INTO Orders (no, id, quantity)
VALUES (4, 4, 30);
INSERT INTO Orders (no, id, quantity)
VALUES (4, 5, 40);
5.
SELECT *
FROM Books;
SELECT bookname, author, price
FROM Books;
6.
SELECT bookname, price
FROM Books
WHERE price > 150;
7.
SELECT DISTINCT price
FROM Books;
8.
SELECT bookname, price * 0.8 AS 新價格
FROM Books;
8.5
SELECT id, bookname, price
FROM Books
WHERE id IN
(
SELECT DISTINCT id
FROM Orders
);
8.8
SELECT id, bookname, price
FROM Books
WHERE id NOT IN
(
SELECT DISTINCT id
FROM Orders
);
22.
DELETE FROM Books
WHERE id = 7;
DELETE FROM Books
WHERE id = 6;
DELETE FROM Books
WHERE price = 120;
23.
UPDATE Books
SET bookname = '三國志'
WHERE id = 1;
UPDATE Books
SET bookname = '三國志'
WHERE bookname = '三國演義';
UPDATE Bookstores
SET name = '元智書坊', rank = rank + 10
WHERE no = 2;
11.
SELECT *
FROM Bookstores
WHERE city LIKE '臺_市';
12.
SELECT *
FROM Books
WHERE bookname LIKE '水__';
SELECT *
FROM Books
WHERE bookname LIKE '水%';
SELECT *
FROM Books
WHERE bookname LIKE '%經%';
13.
SELECT no, name, city
FROM Bookstores
ORDER BY no;
SELECT no, name, city
FROM Bookstores
ORDER BY no ASC;
SELECT no, name, city
FROM Bookstores
ORDER BY no DESC;
SELECT no, name, rank, city
FROM Bookstores
ORDER BY rank DESC, no ASC;
14.
SELECT COUNT(*)
FROM Bookstores;
15.
SELECT COUNT(no)
FROM Orders;
SELECT COUNT(DISTINCT no)
FROM Orders;
SELECT COUNT(DISTINCT id)
FROM Orders;
16.
SELECT SUM(quantity)
FROM Orders;
SELECT SUM(quantity)
FROM Orders
WHERE no = 2;
17.
SELECT AVG(quantity)
FROM Orders;
SELECT AVG(quantity)
FROM Orders
WHERE no = 2;
18.
SELECT MAX(quantity)
FROM Orders
WHERE no = 1;
SELECT MIN(quantity)
FROM Orders
WHERE no = 1;
19.
SELECT no, COUNT(id)
FROM Orders
GROUP BY no;
SELECT id, COUNT(no)
FROM Orders
GROUP BY id;
20.
SELECT no, SUM(quantity)
FROM Orders
GROUP BY no;
SELECT no, SUM(quantity)
FROM Orders
GROUP BY no
HAVING SUM(quantity) > 80;
21.
SELECT name, rank
FROM Bookstores
WHERE rank < ( SELECT AVG(rank) FROM Bookstores );
9.
SELECT Bookstores.no, name, rank, city, id, quantity
FROM Bookstores, Orders
WHERE Bookstores.no = Orders.no;
SELECT name, bookname, quantity
FROM Bookstores, Orders, Books
WHERE Bookstores.no = Orders.no
AND Books.id=Orders.id ;
10.
SELECT no, name, rank, city
FROM Bookstores
WHERE rank > 10 and no > 2;