第11週 INNER-JOIN LEFT-JOIN

第11週 INNER-JOIN LEFT-JOIN

test1 資料表                   

DROP DATABASE IF EXISTS mydb1;

CREATE DATABASE mydb1 DEFAULT CHARACTER SET utf8;

USE mydb1;

CREATE TABLE test1 (

  id     int(10) auto_increment not null comment '自動遞增識別碼',

  name   varchar(20) comment  '姓名',

  phone  varchar(20)  comment '電話',

  primary key (id)

);

INSERT INTO test1 VALUES(null,'Mr. Wu',     '0920098239');

INSERT INTO test1 VALUES(null,'Mrs. Chang', '0989098281');

INSERT INTO test1 VALUES(null,'Miss. Wang', '0923872632');

INSERT INTO test1 VALUES(null,'Dr. Wen',    '0919827323');

INSERT INTO test1 VALUES(null,'Phd. Chung', '0928372723');

INSERT INTO test1 VALUES(null,'Mr. Chen',   '0988098111');

INSERT INTO test1 VALUES(null,'Mrs. Chiang','0935098064');

INSERT INTO test1 VALUES(null,'Miss. Fang', '0942387298');

INSERT INTO test1 VALUES(null,'Mr. Kuang',  '0932956776');

INSERT INTO test1 VALUES(null,'Miss. Liao', '0998372862');

test2資料表                                     

 CREATE TABLE test2 (

  id        int(10) comment 'test1.id',

  country   varchar(20) comment  '國家',

  primary key (id)

);

 INSERT INTO test2 VALUES(1,'Taiwan');

INSERT INTO test2 VALUES(2,'USA');

INSERT INTO test2 VALUES(3,'Japan');

INSERT INTO test2 VALUES(5,'Korea');

INSERT INTO test2 VALUES(6,'China');

INSERT INTO test2 VALUES(8,'India');

 SELECT test1.*, test2.* FROM test1,test2 where test1.id = test2.id;

 SELECT test1.*,test2.* FROM test1 LEFT JOIN test2 on test1.id=test2.id;

 SELECT test2.*,test1.* FROM test2 RIGHT JOIN test1 on test2.id=test1.id;

作業:回到先前的兩個資料表

DROP DATABASE IF EXISTS mydb;

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;

USE mydb;

CREATE TABLE stmd (

  depart_class   CHAR(6)  COMMENT  '班級代碼',

  student_no     CHAR(9)  COMMENT  '學號',
  sex                    CHAR(1) COMMENT '性別',

  student_name   VARCHAR(20) COMMENT  '姓名',

  address        VARCHAR(60) COMMENT '地址',

  primary key(student_no)

) comment '學生基本資料表';

CREATE TABLE class (

  class_no      CHAR(6)      COMMENT '班級代碼',

   name    VARCHAR(20) COMMENT '班級名稱',

  primary key(class_no)

 );

 請匯入資料

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

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

SELECT depart_class,student_no,student_name,address,name FROM stmd,class WHERE stmd.depart_class=class.class_no;

 如果欄位名稱沒有重複時，可以省略 Table名稱，例如
SELECT depart_class,name,student_no,student_name,address FROM stmd,class WHERE depart_class=class_no;

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

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

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

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

1.      查詢所有資料, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

2.      查詢資管二甲所有資料, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

3.      查詢資管二年級所有資料按班級學號由小到大, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

4.      查詢資管三年級乙班所有資料按班級學號由大到小, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

5.      查詢資管二年級所有男生資料, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

6.      查詢資管二年級所有男生住台北市的資料, 列出班級代碼, 班級名稱,  學號,  姓名,  性別, 地址

7.      查詢每班級所有學生總數, 列出班級代碼, 班級名稱,  學生總數

8.      查詢每班級所有男生女生總數, 列出班級代碼, 班級名稱, 男生總數, 女生總數

9.      查詢班級男生總數大於3人者, 列出班級代碼, 班級名稱,  男生總數

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