首先创建数据库:
CREATE DATABASE mysql_shiyan;
use mysql_shiyan;
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 department
LEFT JOIN project ON project.of_dpt=department.dpt_name
LEFT JOIN employee ON employee.in_dpt=department.dpt_name
GROUP BY department.dpt_name;