SQL— 初體驗
學習SQL已有一段時間了，對於SQL也有了大體的認識，那麼在這裡則對所學到的知識點進行系統的整理。
1.創建列表
CREATE TABLE userinfo(
uuid varchar(36) NOT NULL PRIMARY KEY,
username varchar(30) NOT NULL,
userpassword varchar(30) NOT NULL
)
2.刪除表
DROP TABLE 表名 ;
3.插入數據
INSERT INTO userinfo VALUES("1","name",123456)
插入多行數據
INSERT INTO class VALUES( 8,"lrc",20170821,86),
(9,"courge",20160725,91),
(10,"party",20150721,97);
INSERT INTO class VALUES( 12,"fore
",20160526,93)
（
為字元轉義）
4.刪除數據
DELETE FROM userinfo WHERE uuid = "4"
DELETE FROM class WHERE score=76 and username = "quchar"
5.修改數據
UPDATE userinfo SET username = "by" WHERE uuid = "1"
UPDATE class SET username = hird , birthday = 20160925 WHERE score = 86 ;
UPDATE class SET score= score+1 WHERE birthday = 20160925 OR username = "kara";
6.添加列
ALTER TABLE 表名
ADD COLUMN age VARCHAR(10)
AFTER username;
7.刪除列
ALTER TABLE 表名 DROP COLUMN clv;
8.更改表名
ALTER TABLE class RENAME TO class_info;
9.更改列名
ALTER TABLE userinfo CHANGE COLUMN class clv VARCHAR(10);
10.更改類型
ALTER TABLE userinfo MODIFY COLUMN score FLOAT;
11.表的規整
使用case表達式
UPDATE userinfo
SET score =
CASE
WHEN username = "we"
THEN 99
WHEN username = "gui"
THEN 100
ELSE 0
END;
多張表的操作
1.一對一關係
例如，下面的一張表，保存了人的相關信息，有男有女，要求查處所有的夫妻。
CREATE TABLE IF NOT EXISTS person(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
sex CHAR(1),
husband INT,
wife INT
);
INSERT INTO person VALUES(1,小花,0,3,0);
INSERT INTO person VALUES(2,小明,1,0,4);
INSERT INTO person VALUES(3,張三,1,0,1);
INSERT INTO person VALUES(4,小麗,0,2,0);
INSERT INTO person VALUES(5,小五,1,0,0);
表中可以看出，小花和張三是夫妻，小明和小麗是夫妻。通常為了查詢方便，需要兩個表，但實際項目中為了省空間，通常只建一個表，要實現一對一查詢，可以建立兩個視圖(虛表)
CREATE VIEW men AS (SELECT * FROM person WHERE sex = 1);
CREATE VIEW women AS (SELECT * FROM person WHERE sex = 0);
執行查詢:
方式一(原始方法）
SELECT men.sname AS husband,women.sname AS wife FROM men,women WHERE men.id = women.husband AND women.id = men.wife;
方式二（連接方法）
SELECT men.sname AS husband, women.sname AS wife FROM men INNER JOIN women ON men.id = women.husband AND women.id = men.wife;
2.一對多關係
例如:一個人可以擁有多輛汽車，要求查詢某個人擁有的所有車輛。
分析：這種情況其實也可以採用 一張表，但因為一個人可以擁有多輛汽車，如果採用一張表，會造成冗餘信息過多。好的設計方式是，人和車輛分別單獨建表，那麼如何將兩個表關聯呢？有個巧妙的方法，在車輛的表中加個外鍵欄位(人的編號)即可。
* (思路小結：』建兩個表，一』方不動，』多』方添加一個外鍵欄位)*
//建立人員表
CREATE TABLE human(
id VARCHAR(12) PRIMARY KEY,
sname VARCHAR(12),
age INT,
sex CHAR(1)
);
INSERT INTO human VALUES(H001,小王,27,1);
INSERT INTO human VALUES(H002,小明,24,1);
INSERT INTO human VALUES(H003,張慧,28,0);
INSERT INTO human VALUES(H004,李小燕,35,0);
INSERT INTO human VALUES(H005,王大拿,29,1);
INSERT INTO human VALUES(H006,周強,36,1);
//建立車輛信息表
CREATE TABLE car(
id VARCHAR(12) PRIMARY KEY,
mark VARCHAR(24),
price NUMERIC(6,2),
hid VARCHAR(12),
CONSTRAINT fk_human FOREIGN KEY(hid) REFERENCES human(id)
);
INSERT INTO car VALUES(C001,BMW,65.99,H001);
INSERT INTO car VALUES(C002,BenZ,75.99,H002);
INSERT INTO car VALUES(C003,Skoda,23.99,H001);
INSERT INTO car VALUES(C004,Peugeot,20.99,H003);
INSERT INTO car VALUES(C005,Porsche,295.99,H004);
INSERT INTO car VALUES(C006,Honda,24.99,H005);
INSERT INTO car VALUES(C007,Toyota,27.99,H006);
INSERT INTO car VALUES(C008,Kia,18.99,H002);
INSERT INTO car VALUES(C009,Bentley,309.99,H005);
SELECT human.sname AS 車主,car.mark AS 車輛 FROM human,car WHERE human.id = car.hid; （AS後面緊跟別名）
SELECT human.sname AS 車主,car.mark AS 車輛 FROM human INNER JOIN car WHERE human.id = car.hid;
3.多對多關係`
例如：學生選課，一個學生可以選修多門課程，每門課程可供多個學生選擇。
分析：這種方式可以按照類似一對多方式建表，但冗餘信息太多，好的方式是實體和關係分離並單獨建表，實體表為學生表和課程表，關係表為選修表，其中關係表採用聯合主鍵的方式(由學生表主鍵和課程表主鍵組成)建表。
//建立學生表
CREATE TABLE student(
id VARCHAR(10) PRIMARY KEY,
sname VARCHAR(12),
age INT,
sex CHAR(1),
class VARCHAR(6)
);
INSERT INTO student VALUES(p0001,王軍,20,1,c101);
INSERT INTO student VALUES(p0002,張宇,21,1,c101);
INSERT INTO student VALUES(p0003,劉飛,22,1,c102);
INSERT INTO student VALUES(p0004,趙燕,18,0,c103);
INSERT INTO student VALUES(p0005,曾婷,19,0,c103);
INSERT INTO student VALUES(p0006,周慧,21,0,c104);
INSERT INTO student VALUES(p0007,小紅,23,0,c104);
INSERT INTO student VALUES(p0008,楊曉,18,0,c104);
INSERT INTO student VALUES(p0009,李傑,20,1,c105);
INSERT INTO student VALUES(p0010,張良,22,1,c105);
//建立課程表
CREATE TABLE course(
id VARCHAR(10) PRIMARY KEY,
sname VARCHAR(12),
credit NUMERIC(2,1),
teacher VARCHAR(12)
);
INSERT INTO course VALUES(C001,Java,3.5,李老師);
INSERT INTO course VALUES(C002,高等數學,5.0,趙老師);
INSERT INTO course VALUES(C003,JavaScript,3.5,王老師);
INSERT INTO course VALUES(C004,離散數學,3.5,卜老師);
INSERT INTO course VALUES(C005,資料庫,3.5,廖老師);
INSERT INTO course VALUES(C006,操作系統,3.5,張老師);
//建立選修表
CREATE TABLE sc(
sid VARCHAR(10),
cid VARCHAR(10)
);
ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);
ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);
INSERT INTO sc VALUES(S0001,C001);
INSERT INTO sc VALUES(S0001,C002);
INSERT INTO sc VALUES(S0001,C003);
INSERT INTO sc VALUES(S0002,C001);
INSERT INTO sc VALUES(S0002,C004);
INSERT INTO sc VALUES(S0003,C002);
INSERT INTO sc VALUES(S0003,C005);
INSERT INTO sc VALUES(S0004,C003);
INSERT INTO sc VALUES(S0005,C001);
INSERT INTO sc VALUES(S0006,C004);
INSERT INTO sc VALUES(S0007,C002);
INSERT INTO sc VALUES(S0008,C003);
INSERT INTO sc VALUES(S0009,C001);
INSERT INTO sc VALUES(S0009,C005);
執行查詢：(沒有選課的學生信息和無學生選的課程信息)
方式1.普通方式：
SELECT * FROM student WHERE student.id NOT IN (SELECT sid FROM sc);
SELECT * FROM course WHERE course.id NOT IN (SELECT cid FROM sc);
方式2.關聯方式：
SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;
SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE cid IS NULL;
4.UNION
每個SELECT語句必須有相同數量的列，列必須有相似的數據類型，列在表中的順序必須相同
SELECT over FROM amoun1
UNION
SELECT over FROM amoun2
SELECT user_name FROM user1
UNION
SELECT user_name FROM user2
5.聯合
table1 : create table user2(id int, user_name varchar(10), over varchar(10));
insert into user2 values(1,tangseng,dtgdf);
insert into user2 values(2,sunwukong,dzsf);
insert into user2 values(3,zhubajie,jtsz);
insert into user2 values(4,shaseng,jslh)
create table user2(id int, user_name varchar(10), over varchar(10));
insert into user2 values(1,sunwukong,chengfo);
insert into user2 values(2,niumowang,chengyao);
insert into user2 values(3,jiaomowang,chengyao);
insert into user2 values(4,pengmowang,chengyao);
6.內連接
select a.id, a.user_name, b.over from user1 a inner join user2 b on a.user_name=b.user_name;
8.左外連接：left join 或 left outer join
SELECT a.id ,a.user_name, b.over FROM user1 AS a LEFT JOIN user2 AS b ON a.user_name=b.user_name;
（其中AS可省略）
9.右外連接：right join 或 right outer join
select b.user_name, b.over, a.over from user1 a right join user2 b on a.user_name=b.user_name;
10.使用join優化子查詢
select a.user_name, a.over, b.over as over2 from user1 a left join user2
b on a.user_name = b.user_name;
查詢user1中每人對應user_kills表中kills最大的日期，使用聚合子查詢語句：
select a.user_name, b.timestr, b.kills from user1 a join user_kills b on
a.id = b.user_id join user_kills c on c.user_id = b.user_id group by a.user_name, b.timestr, b.kills having b.kills = max(c.kills);
11.自聯結
給Tom同一公司的所有會員發送一條郵件
方法1.用子查詢
select *
from Customers
where Company in(select Company from Customers where Name=Tom)
方法2.用自聯結
select *
from Customers c1 , Customers c2
where c1.Company = c2.Company AND c1.name=Tom;
目前所做的練習就這麼多，在工作中若是用到有關SQL更高級地用法，後期在補上，就醬~~~
推薦閱讀：
※凈慧法師：禪的理論與實踐
※寬嚴相濟」刑事政策在審判實踐中的理解與適用
※朱鶴實踐卦例（六）
※陳氏特點申論和實踐方法
※我的形氣風水實踐四
TAG:實踐 |