mysql 综合查询_mysql 综合查询练习（关系查询、子查询）

建表语句见文末empsalarygrade

1、找到名字为两位的员工信息

-- 方法一

select * from emp

where length(ename) = 6; #一个汉字的长度为3

-- 方法二

select * from emp

where ename like '__'; -- 两根下划线

2、查询工资多于宋江的员工信息 (子查询)

select * from emp

where salary > (select salary from emp where ename = '宋江');

3、查询销售部门的员工姓名

-- 法一：join

select dname , ename from

emp left join dept on emp.dept_id = dept.id

where dname = '销售部';

-- 法二：关系查询

select ename , dname

from emp , dept

where emp.dept_id = dept.id and dname = '销售部'; -- 关系连接 ,结果和内连接一样

-- 法三：子查询

select ename from emp

where dept_id = (select id from dept where dname = '销售部'); -- 子查询没法将部门名称放上去

4、返回工资级别处于第4的员工姓名及工资(子查询作为虚拟表) ***

select ename , salary from emp ,(select losalary , hisalary from salarygrade where grade = 4) as a

where salary between losalary and hisalary;

5、-- 查询所有员工对应的工资等级 ***

6、查询选修编号为“3-105“课程且成绩至少高于课程选修编号为“3-245”的记录,并按Degree从高到低降序排序。

select * from score

where cno = '3-105' and degree >any (select degree from score where cno = '3-245')

order by degr