《学习》5连接查询（高级查询）

CREATE TABLE t(
tid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tname VARCHAR(30),
PRIMARY KEY (tid)
)ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;

INSERT INTO t VALUES(NULL,'李老师'),(NULL,'赵老师'),(NULL,'张老师');
SELECT * FROM t;

CREATE TABLE s(
sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
sname VARCHAR(10)NOT NULL,
sgender ENUM ('男','女','人妖'),
sdept VARCHAR(20) NOT NULL DEFAULT '计算机科学',
sscore INT UNSIGNED,
tid INT UNSIGNED,
PRIMARY KEY(sid)
)ENGINE=MYISAM AUTO_INCREMENT=1601 DEFAULT CHARSET =utf8;
INSERT INTO s VALUES(NULL,'张三','男','美术',80,1),(NULL,'张三丰','人妖','英语',45,2);
INSERT INTO s VALUES(NULL,'李丽','女','美术',90,3),(NULL,'黄瑶','女','财经',99,3);

INSERT INTO s(sname,sgender,sscore) VALUES('赵磊','男',50),('周磊','人妖',68);

DROP TABLE job;
SELECT * FROM s;

-- 多表查询(不推荐使用)
SELECT s.`sid`,s.`sname`,t.`tname`
FROM s,t
WHERE s.tid = t.tid;

-- 连接查询 join left join right join
SELECT s.`sid` 学号,s.`sname` 姓名,s.`sdept` 专业,IF(t.`tname` IS NULL,'没安排老师',t.`tname`) 老师
FROM s LEFT JOIN t ON s.`tid`=t.`tid`;

 

CREATE TABLE job(
jid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
jname VARCHAR(20) NOT NULL,
jgender ENUM('男','女'),
jpjid INT
)ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;

INSERT INTO job VALUES(NULL,'张三','男',NULL);
INSERT INTO job VALUES(NULL,'李四','男',1);
INSERT INTO job VALUES(NULL,'王五','女',1);
INSERT INTO job VALUES(NULL,'赵六','男',3);

SELECT * FROM job;
DESC job;

-- 查询每个员工的编号姓名及领导的名字
-- 自连接查询
SELECT j1.`jid` 编号,j1.`jname` 姓名,j2.`jname` 领导姓名
FROM job j1,job j2
WHERE j1.jpjid=j2.jid

SELECT j1.`jid` 编号,j1.`jname` 姓名,IF(j2.`jname` IS NULL,'总经理',j2.`jname`) 领导姓名
FROM job j1 LEFT JOIN job j2
ON j1.jpjid=j2.jid