赞
踩
这部分主要是为了帮助大家回忆回忆MySQL的基本语法,数据库来自于MySQL的官方简化版,题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。
select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname = 'sales';
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
mysql> select avg(sal) as avgsal from emp;
+-------------+
| avgsal |
+-------------+
| 2073.214286 |
+-------------+
然后多表连接
mysql> select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal having sal>(select avg(sal) as avgsal from emp); +-------+---------+-------------+-------+ | ename | sal | dname | grade | +-------+---------+-------------+-------+ | JONES | 2975.00 | RESEARCHING | 4 | | BLAKE | 2850.00 | SALES | 4 | | CLARK | 2450.00 | ACCOUNTING | 4 | | SCOTT | 3000.00 | RESEARCHING | 4 | | KING | 5000.00 | ACCOUNTING | 5 | | FORD | 3000.00 | RESEARCHING | 4 | +-------+---------+-------------+-------+
先找出scott的岗位,然后找出从事此工作的员工并与部门表连接显示部门名,注意排除scott本人
mysql> select e.ename,d.dname from emp e join dept d where e.deptno=d.deptno and e.job=(select job from emp where ename='scott') and e.ename<>'scott';
+-------+-------------+
| ename | dname |
+-------+-------------+
| FORD | RESEARCHING |
+-------+-------------+
先取出部门30的最高薪水,然后链接表和部门。
select a.ename,a.sal,d.dname from (select * from emp where sal > (select max(sal) from emp where deptno=30) and deptno<>30) a join dept d on a.deptno = d.deptno;
+-------+---------+-------------+
| ename | sal | dname |
+-------+---------+-------------+
| JONES | 2975.00 | RESEARCHING |
| SCOTT | 3000.00 | RESEARCHING |
| KING | 5000.00 | accounting |
| FORD | 3000.00 | RESEARCHING |
+-------+---------+-------------+
使用to_days()将时间转换为公元元年到某时间点的天数
mysql> select d.deptno, count(e.ename)as number,ifnull(avg(e.sal),0) as avgsal, ifnull(avg((to_days(now())-to_days(e.hiredate))/365),0) as avgtime from dept d left join emp e on d.deptno=e.deptno group by d.deptno; +--------+--------+-------------+-------------+ | deptno | number | avgsal | avgtime | +--------+--------+-------------+-------------+ | 20 | 5 | 2175.000000 | 35.57588000 | | 30 | 6 | 1566.666667 | 37.84750000 | | 10 | 3 | 2916.666667 | 37.54886667 | | 40 | 0 | 0.000000 | 0.00000000 | +--------+--------+-------------+-------------+
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。