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