1.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
输出格式:
dept_no | employees |
---|---|
d001 | 10001,10002 |
d002 | 10006 |
d003 | 10005 |
d004 | 10003,10004 |
d005 | 10007,10008,10010 |
d006 | 10009,10010 |
考察点:group_concat
SQLite:
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no
MySQL:
select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;
2.分页查询employees表,每5行一页,返回第2页的数据
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
考察点:Limit
方法一:利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录,OFFSET 后的数字代表从第几条记录开始返回(第一条记录序号为0),也可理解为跳过多少条记录后开始返回。
SELECT * FROM employees LIMIT 5 OFFSET 5
方法二:只利用 LIMIT 关键字。注意:在 LIMIT X,Y 中,Y代表返回几条记录,X代表从第几条记录开始返回(第一条记录序号为0),切勿记反。
SELECT * FROM employees LIMIT 5,5
3.使用含有关键字exists查找未分配具体部门的员工的所有信息。
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
考察点:IN和EXISTS
方法一: EXISTS
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no
FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no);
方法二:IN
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
FROM dept_emp);
什么时候用EXISTS,什么时候用IN?
主表为employees,从表为dept_emp,在主表和从表都对关联的列emp_no建立索引的前提下:
当主表比从表大时,IN查询的效率较高ÿ