mysql预热方式_MySQL高级 第4章SQL预热

1. 常见的Join查询图

2. Join示例

2.1 建表语句

CREATE TABLE `t_dept` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`deptName` VARCHAR(30) DEFAULT NULL,

`address` VARCHAR(40) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20) DEFAULT NULL,

`age` INT(3) DEFAULT NULL,

`deptId` INT(11) DEFAULT NULL,

empno int  not null,

PRIMARY KEY (`id`),

KEY `idx_dept_id` (`deptId`)

#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES(‘华山’,’华山’);

INSERT INTO t_dept(deptName,address) VALUES(‘丐帮’,’洛阳’);

INSERT INTO t_dept(deptName,address) VALUES(‘峨眉’,’峨眉山’);

INSERT INTO t_dept(deptName,address) VALUES(‘武当’,’武当山’);

INSERT INTO t_dept(deptName,address) VALUES(‘明教’,’光明顶’);

INSERT INTO t_dept(deptName,address) VALUES(‘少林’,’少林寺’);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘风清扬’,90,1,100001);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘岳不群’,50,1,100002);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘令狐冲’,24,1,100003);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘洪七公’,70,2,100004);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘乔峰’,35,2,100005);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘灭绝师太’,70,3,100006);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘周芷若’,20,3,100007);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘张三丰’,100,4,100008);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘张无忌’,25,5,100009);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘韦小宝’,18,null,100010);

2.2 案例

1.所有有门派人员的信息(要求显示门派名称)

SELECT e.`name`,d.`deptName` FROM t_emp e INNER JOIN t_dept d ON e.`deptId`=d.`id`;

2. 列出所有人员及其门派信息

SELECT e.`name`,d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id`;

3. 列出所有门派

SELECT * FROM t_dept;

4. 所有无门派人士

SELECT * FROM t_emp WHERE deptId IS NULL;

5. 所有无人门派

SELECT d.* FROM  t_dept d LEFT JOIN t_emp e ON d.`id`=e.`deptId` WHERE e.`deptId` IS NULL;

6. 所有人员和门派的对应关系

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id`

UNION

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId`=d.`id`;

7. 所有没有入门派的人员和没人入的门派

SELECT * FROM t_emp e  LEFT JOIN t_dept d ON e.`deptId`=d.`id` WHERE e.deptId IS NULL

UNION

SELECT * FROM  t_dept d LEFT JOIN t_emp e ON d.`id`=e.`deptId` WHERE e.`deptId` IS NULL;

8. 添加CEO字段

ALTER TABLE `t_dept`

add  CEO  INT(11)  ;

update t_dept set CEO=2 where id=1;

update t_dept set CEO=4 where id=2;

update t_dept set CEO=6 where id=3;

update t_dept set CEO=8 where id=4;

update t_dept set CEO=9 where id=5;

8.1 求各个门派对应的掌门人名称

SELECT d.deptName,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id

8.2求所有当上掌门人的平均年龄

SELECT AVG(e.age) FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id

8.3求所有人物对应的掌门名称

SELECT ed.name ‘人物’,c.name ‘掌门’ FROM

(SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed

LEFT JOIN t_emp c on ed.ceo= c.id;

SELECT e.name ‘人物’,tmp.name ‘掌门’

FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp

ON e.deptId=tmp.did;

SELECT e1.name ‘人物’,e2.name ‘掌门’

FROM t_emp e1

LEFT JOIN t_dept d on e1.deptid = d.id

LEFT JOIN t_emp e2 on d.ceo = e2.id ;

SELECT e2.name ‘人物’,

(SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) ‘掌门’

from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id;

上一篇: MySQL高级 第3章Mysql逻辑架构简介

下一篇: MySQL高级 第5章索引优化分析