MySQL使用连接查询的方式查询（连接3个表，并使用COUNT内置函数）

首先创建数据库：

CREATE DATABASE mysql_shiyan;use mysql_shiyan;

MySQL_create.sql免积分下载
创建表：

CREATE TABLE `project` (  `proj_num` int(10) NOT NULL,  `proj_name` char(20) NOT NULL,  `start_date` date NOT NULL,  `end_date` date DEFAULT '2015-04-01',  `of_dpt` char(20) DEFAULT NULL,  PRIMARY KEY (`proj_num`,`proj_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `employee` (  `id` int(10) NOT NULL,  `name` char(20) DEFAULT NULL,  `age` int(10) DEFAULT NULL,  `salary` int(10) NOT NULL,  `phone` int(12) NOT NULL,  `in_dpt` char(20) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `phone` (`phone`),  KEY `emp_fk` (`in_dpt`),  CONSTRAINT `emp_fk` FOREIGN KEY (`in_dpt`) REFERENCES `department` (`dpt_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `department` (  `dpt_name` char(20) NOT NULL,  `people_num` int(10) DEFAULT '10',  PRIMARY KEY (`dpt_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

MySQL_insert.sql免积分下载
使用连接查询的方式，查询出各员工所在部门的人数与工程数，工程数命名为count_project。（连接3个表，并使用COUNT内置函数）

SELECT department.dpt_name AS department_name, COUNT(DISTINCT project.proj_name) AS 'count_project', COUNT(DISTINCT employee.name) AS 'count_people'FROM departmentLEFT JOIN project ON project.of_dpt=department.dpt_nameLEFT JOIN employee ON employee.in_dpt=department.dpt_nameGROUP BY department.dpt_name;