盐城网站建设流程百度图片
这部分主要是为了帮助大家回忆回忆MySQL的基本语法，数据库来自于MySQL的官方简化版，题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。
列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号
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.gradefromemp ejoindept done.deptno=d.deptnojoinsalgrade sone.sal between s.losal and s.hisalhavingsal>(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的岗位，然后找出从事此工作的员工并与部门表连接显示部门名，注意排除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的全部员工的员工姓名，薪水，部门名称
先取出部门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 avgtimefromdept dleft joinemp eond.deptno=e.deptnogroup byd.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 |
+--------+--------+-------------+-------------+