手写建表语句和sql

# employee表
CREATE TABLE `employee` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `emp_name` varchar(255) NOT NULL DEFAULT '',
  `dept_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# department表
CREATE TABLE `department` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `dept_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT
	dept.id,
	dept.dept_name,
	(
		SELECT
			count(emp.id)
		FROM
			employee emp
		WHERE
			emp.dept_id = dept.id
	) AS num
FROM
	department dept;