mysql多表

1，创建员工表

create table emp(
    e_id int primary key,
    e_name varchar(20) not null,
    e_sal double(10,2)
);

2.创建部门表

create table dept(
   d_id int primary key,
   d_name varchar(20) not null
);

3.创建了两表以后，需要了解两个表之间的关系，显然，员工与部门表之间的关系是多对一的。另外一个问题就是：在应用中经常查的表是哪一个，经常需要在查哪一个表的时候将另一个表中的内容查出来。知道了这些以后就知道了外键所在的表。当然，外键一般放在多的一方。

在emp表中插入新的字段，并修改该字段为外键字段。

alter table emp add column d_id int;

ALTER TABLE emp  ADD FOREIGN KEY empFK (d_id)
   REFERENCES DEPT (d_id); 

4.分别在两个表中插入数据。

--必须先插入该表，emp表会引用该表的d_id
INSERT INTO dept(d_id,d_name) VALUES(1,"开发部");
INSERT INTO dept(d_id,d_name) VALUES(2,"管理部");
INSERT INTO dept(d_id,d_name) VALUES(3,"HR");

INSERT INTO emp(e_id,e_name,e_sal,d_id) VALUES(1,'张三',10000,1);
INSERT INTO emp(e_id,e_name,e_sal,d_id) VALUES(2,'李四',10000,2);

5.内连接查询

SELECT e.*,d.*
FROM emp e,dept d
WHERE e.d_id = d.d_id;

6.外连接查询

1.左外连接

   两个表的数据不一致的时候，比如在此例中dept表中有一个d_id为3的数据，但是在员工表中没有该数据，现在要根据部门号统计部门的员工数就需要左外连接。

SELECT COUNT(e.e_id) '员工数',d.d_name
FROM dept d LEFT JOIN emp e ON
e.e_id = d.d_id GROUP BY d.d_id;

左外连接将数据较多的放到left的左边，用左边的数据强行将右边较少的数据查出来。

2.右外连接与左外连接相反。

7.查询本部门中工资大于平均工资的人与人数

SELECT e.sal,dd.did,COUNT(e.id)
FROM emp e,(SELECT AVG(e.sal) asal,d.DEPTID did FROM emp e,dept d WHERE d.d_id=e.d_id GROUP BY d.DEPTID) dd
WHERE e.sal>=dd.asal AND e.d_id=dd.did GROUP BY dd.did

 

转载于:https://my.oschina.net/meshwon/blog/689463