第14週MySQL-兩個資料表的關聯

班級代碼表depart

假設資料有

164D11,四技資管一甲

164D12,四技資管一乙

164D21,四技資管二甲

164D22,四技資管二乙

164D31,四技資管三甲

164D32,四技資管三乙

164D41,四技資管四甲

164D42,四技資管四乙

學生基本資料表 stmd 

 假設資料有

164D11,10314D023,李泓毅,臺北市中正區泉州街43號4樓

164D12,10314D087,吳秉薪,台北市信義區長春里3鄰虎林街82巷8號

164D13,10314D079,陳家芸,台北市南港區忠孝東路六段七十巷五弄二十五號六樓

164D21,10214D013,鄭諺,台北市中山區行政里松江路357巷12號4F

164D22,10214D015,王凱廷,台北市大安區溫州街74巷6號3F-3

164D23,10214D020,黃詩哲,宜蘭縣頭城鎮拔雅里7鄰復興路51巷13號

164D31,10114D008,梁予貞,台北市松山區新聚里5鄰寶清街43號5樓

164D32,10114D011,王?瑋,臺北市萬華區和德里3鄰西藏路251巷9號

164D33,10114D014,蕭慶宏,新北市土城區樂利里7鄰裕民路92巷27弄8號4樓

164D41,10014D002,陳昱慈,台北市文山區福興路78巷20弄73號4樓

164D42,10014D003,劉冠余,台北市內湖區康樂街20巷7號5樓

164D43,10014D006,蔡佳容,台北市內湖區行善路59巷1號4樓

這兩個資料表有一個欄位相互關聯，即 depart.depart_class與stmd.depart_class

 Inner Join : 只選擇兩個資料表有匹配的(只顯示有配對的部份)

SELECT stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name,

stmd.address FROM stmd,depart WHERE stmd.depart_class=depart.depart_class;

結果

164D41,四技資管四甲,10014D002,陳昱慈,台北市文山區福興路78巷20弄73號4樓

164D42,四技資管四乙,10014D003,劉冠余,台北市內湖區康樂街20巷7號5樓

164D31,四技資管三甲,0114D008,梁予貞,台北市松山區新聚里5鄰寶清街

164D32,四技資管三乙,10114D011,王?瑋,臺北市萬華區和德里3鄰西藏路251巷9號

164D21,四技資管二甲,10214D013,鄭諺,台北市中山區行政里松江路357巷12號4F

164D22,四技資管二乙,10214D015,王凱廷,台北市大安區溫州街74巷6號3F-3

164D11,四技資管一甲,10314D023,李泓毅,臺北市中正區泉州街43號4樓

164D12,四技資管一乙,10314D087,吳秉薪,台北市信義區長春里3鄰虎林街82巷8號

其中 四筆資料未出現，因為 inner join的關係，也就是找不到

stmd.depart_class=depart.depart_class

164D13,10314D079,陳家芸,台北市南港區忠孝東路六段七十巷五弄二十五號六樓

164D23,10214D020,黃詩哲,宜蘭縣頭城鎮拔雅里7鄰復興路51巷13號

164D33,10114D014,蕭慶宏,新北市土城區樂利里7鄰裕民路92巷27弄8號4樓

164D43,10014D006,蔡佳容,台北市內湖區行善路59巷1號4樓

left join

SELECT stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name,stmd.address FROM stmd left join depart on stmd.depart_class=depart.depart_class;

如果欄位名稱沒有重複時，可以省略 Table名稱，例如

SELECT depart_class,student_no,student_name,address,class_name FROM stmd left join class on depart_class=class_no;

結果

164D41,四技資管四甲,10014D002,陳昱慈,台北市文山區福興路78巷20弄73號4樓

164D42,四技資管四乙,10014D003,劉冠余,台北市內湖區康樂街20巷7號5樓

164D43,NULL,10014D006,蔡佳容,台北市內湖區行善路59巷1號4樓

164D31,四技資管三甲,10114D008,梁予貞,台北市松山區新聚里5鄰寶清街43號5樓

164D32,四技資管三乙,10114D011,王?瑋,臺北市萬華區和德里3鄰西藏路251巷9號

164D33,NULL,10114D014,蕭慶宏,新北市土城區樂利里7鄰裕民路92巷27弄8號4樓

164D21,四技資管二甲,10214D013,鄭諺,台北市中山區行政里松江路357巷12號4F

164D22,四技資管二乙,10214D015,王凱廷,台北市大安區溫州街74巷6號3F-3

164D23,NULL,10214D020,黃詩哲,宜蘭縣頭城鎮拔雅里7鄰復興路51巷13號

164D11,四技資管一甲,10314D023,李泓毅,臺北市中正區泉州街43號4樓

164D13,NULL,10314D079,陳家芸,台北市南港區忠孝東路六段七十巷五弄二十五號六樓

164D12,四技資管一乙,10314D087,吳秉薪,台北市信義區長春里3鄰虎林街82巷8


DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;
USE mydb;
CREATE TABLE depart(
  depart_class   CHAR(6)     COMMENT  '班級代碼',
  depart_name    VARCHAR(20) COMMENT  '班級名稱',
  primary key(depart_class)
) comment '班級代碼表';
LOAD DATA INFILE "c:/AppServ/www/mydb/depart2.txt" INTO TABLE depart FIELDS TERMINATED BY ",";
CREATE TABLE stmd (
  depart_class   CHAR(6)     COMMENT '班級代碼',
  student_no     CHAR(9)     COMMENT '學號',
  student_name   VARCHAR(20) COMMENT '姓名',
  address        VARCHAR(60) COMMENT '地址',
  primary key(student_no)
) comment '學生基本資料表';
 
LOAD DATA INFILE "c:/AppServ/www/mydb/stmd2.txt" INTO TABLE stmd FIELDS TERMINATED BY ",";

作業(必須含班級名稱) 分別作 inner join 與 left join

查詢所有資料

查詢資管二甲所有資料

查詢資管二年級所有資料按班級學號由小到大

查詢資管三年級乙班所有資料按班級學號由大到小

查詢資管二年級所有男生資料

查詢資管二年級所有男生住台北市的資料

查詢二年級所有資料

查詢二年級住台北市所有資料按班級學號由小到大

查詢台北校區所有資料按班級學號由大到小

全部共 0則留言
留言...
登入帳號密碼代表遵守學術網路規範