【典型例题】- 50道MySQL数据库SQL语句练习

现在具有两个表，一个是部门表，另一个员工表，它们的表结构如下：

第一步：创建test数据库，命令：create database test;

第二步，建表：

emp（员工表）

create table emp(
       empno int(11) primary key comment "员工号",
       ename varchar(32) comment "员工姓名",
       job varchar(32) comment "工种",
       mgr varchar(32) comment "经理",
       hiredate number(10) comment "入职日期",
       sal int(11) comment "薪水",
       comm int(11) comment "奖金",
       deptno int(11) comment "部门编号",
       foreign key(deptno) references dept(deptno)
)

dept（部门表）

create table dept(
       deptno int(11) primary key comment "部门编号",
       dname varchar(32) comment "部门名称",
       loc varchar(32) comment "地点"
)

第三步，插入数据：

insert into dept values(1, "技术部" ,"南泥湾");
insert into dept values(2, "SALES" ,"深圳市");
insert into dept values(3, "事业部" ,"北京市");
insert into dept values(4, "服务部" ,"延安");
insert into dept values(5, "生产部" ,"南京市");
insert into dept values(6, "宣传部" ,"上海市");
insert into dept values(7, "打杂部" ,"广州市");
insert into dept values(8, "司令部" ,"重庆市");
insert into dept values(9, "卫生部" ,"长沙市");
insert into dept values(10, "文化部" ,"武冈市");
insert into dept values(11, "娱乐部" ,"纽约");
insert into dept values(12, "管理部" ,"伦敦");
insert into dept values(13, "行政部" ,"天津市");
select * from dept


insert into emp values(1, "关羽羽", "CLERK" ,"刘备备", 20011109, 2000, 1000, 3);
insert into emp values(2, "SMITH", "CLERK" ,"刘备备", 20120101, 2000, 800, 6);
insert into emp values(3, "刘备备", "MANAGER" ,"宋祖英", 20080808, 9000, 4000, 3);
insert into emp values(4, "TOM", "ENGINEER" ,"Steve", 20050612, 3000, 1000, 1);
insert into emp values(5, "Steve", "MANAGER" ,"宋祖英", 20110323, 80000, 9000, 1);
insert into emp values(6, "张飞飞", "CLERK" ,"刘备备", 20101010, 2000, 1000, 3);
insert into emp values(7, "SCOTT", "CLERK" ,"刘备备", 20071204, 2000, 1000, 3);
insert into emp values(8, "宋祖英", "Boss" ,"无", 20060603, 2000, 1000, 8);
insert into emp values(9, "曹仁人", "SALESMAN" ,"曹操操", 20120130, 2000, 1000, 2);
insert into emp values(10, "曹操操", "MANAGER" ,"宋祖英",20090815, 2000, 1000, 2);
insert into emp values(11, "酱油哥", "HAPI" ,"XXX",20090215, 3, 1, 2);

题目：

列出至少有一个员工的所有部门。

select deptno,dname loc from dept where deptno in (select deptno from emp);

​

列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）

select empno,ename,sal from emp where emp.sal>(select sal from emp emp1 where emp1.ename = "SMITH")

​

列出所有员工的姓名及其直接上级的姓名。

select a.ename,b.ename as mgr from emp a,emp b where a.mgr=b.ename;

​

解析：这里使用表的自连接方式，以a表中经理的编号与b表中的员工号连接起来，目的将员工与经理一一对应上。最后显示的就是员工对应经理的详细信息。

列出受雇日期早于其直接上级的所有员工。

select a.empno, a.ename from emp a,emp b where a.mgr=b.ename and a.hiredate

正确：select dname,e.* from dept d LEFT JOIN emp e on d.deptno = e.deptno（使用left join）

错误：select dname,e.* FROM dept d,emp e WHERE d.deptno = e.deptno; （只选出了也有员工的部门）

select ename,dname,job from emp e join dept d on e.deptno=d.deptno where e.job="CLERK";

正确：select job from emp GROUP BY job having min(sal)>1500;

错误：select job FROM emp WHERE sal>1500

select ename from emp, dept where emp.deptno = dept.deptno and dept.dname = "SALES"; 

select * from emp where sal >(select avg(sal) from emp);