SQL语句面试

1.查询所有记录

SELECT * FROM emp;

2.只查询指定列

SELECT ename,sal FROM emp;

3.查询 empno 为 100 的记录

SELECT * FROM emp WHERE empno = 100;

4.模糊查询记录(like)

SELECT * FROM emp WHERE ename LIKE 'j%';  #以j开头的记录
SELECT * FROM emp WHERE ename LIKE '%k';  #以kj结尾的记录
SELECT * FROM emp WHERE ename LIKE '%a%';  #包含a的记录
SELECT * FROM emp WHERE ename NOT LIKE 'j%';  #不以j开头的记录
SELECT * FROM emp WHERE ename LIKE 'a__';  #以a开头，后面有两个字符的记录
SELECT * FROM emp WHERE ename LIKE '%a__';  #包含a,后面有两个字符的记录

5.查询指定范围内的记录(between and)

SELECT * FROM emp WHERE sal BETWEEN 8000 AND 2000;  #[8000,2000],错误示范，查不到数据
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 8000;  #[2000,8000],between and 查询范围记录时较小数在and前,较大数在and后
SELECT * FROM emp WHERE sal < 8000 AND sal > 2000;  #(8000,2000)

6.查询满足多个条件的记录(or,and)

SELECT * FROM emp WHERE sal = 8000 OR sal = 10000;
SELECT * FROM emp WHERE sal = 8000 OR sal = 10000 OR sal = 3000;
SELECT * FROM emp WHERE sal IN (3000,8000,10000);
SELECT * FROM emp WHERE ename = 'jack' AND job = '副总';

7.查询用户住址(distinct去重)

SELECT DISTINCT address FROM `tb_user_address`;
SELECT DISTINCT NAME FROM users WHERE age = 19;

8.按升降序查询记录(oeder by)

SELECT * FROM emp ORDER BY sal ASC; #ASC 默认升序
SELECT * FROM emp ORDER BY sal DESC; #降序
SELECT * FROM emp ORDER BY ename ASC,sal DESC;

9.查询总人数(聚合函数count())

SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;  #最常用，最高效
SELECT COUNT(ename) COUNT FROM emp;

10.查询各个城市的人数(group by)

SELECT addr,COUNT(addr) FROM USER GROUP BY addr;  #通过非聚合列分组

11.查询城市地址至少有两个人的地址(group by having)

SELECT addr,COUNT(addr) X FROM USER GROUP BY addr HAVING X>=2
SELECT addr,COUNT(addr) X FROM USER WHERE X>=2 GROUP BY addr;  #SQl优化，先过滤，再分组

12.查询记录中最年长和最年轻的人(聚合函数max,min)

SELECT MAX(age),MIN(age) FROM USER;
SELECT ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);  #子查询

13.查询大于平均年龄的人名(聚合函数avg)

SELECT NAME,age FROM USER WHERE age > (SELECT AVG(age) FROM USER);

14.查询工资最高的员工信息

SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

15.查询各部门最高薪资

SELECT deptno,ename,MAX(IFNULL(sal,0)) maxsal FROM emp GROUP BY deptno;

16.查询empno是100或者200的记录(or)

SELECT * FROM emp WHERE empno = 100 OR empno = 200;  #尽量避免出现or
SELECT * FROM emp WHERE empno = 100;  #应分成两部查询
SELECT * FROM emp WHERE empno = 200;

17.查询划分了部门的员工信息(in)

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);  #子查询
SELECT * FROM emp INNER JOIN dept ON emp.`deptno`=dept.`deptno`;  #连接查询
SELECT * FROM emp,dept WHERE dept.`deptno`=emp.`deptno`;  #笛卡尔积

18.查询没划分部门的员工信息(not in)

SELECT * FROM emp INNER JOIN dept ON emp.`deptno`!=dept.`deptno`;  #错误SQL，避免在where子句中出现 != 和 <>
SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept);  #只能用子查询

19.查询同名的员工记录!!!

SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp GROUP BY ename HAVING COUNT(ename)>1);

20.全部学生按出生年月排行(order by)

SELECT * FROM students ORDER BY sbirthday;

21.每个班上年龄最小的学员

SELECT ename,class,MIN(age) FROM emp GROUP BY class;
SELECT MAX(sbirthday) FROM students;

22.查询学生的姓名和年龄

SELECT sname,YEAR(NOW())-YEAR(sbirthday) age FROM students; 

23.查询男教师及其所上课程

SELECT teachers.tname,courses.`cname`FROM courses INNER JOIN teachers ON courses.`tno`=teachers.`tno`AND teachers.`tsex`='男';

24.查询所有老师各自所授课程

SELECT teachers.tname,courses.`cname`FROM courses INNER !JOIN teachers ON courses.`tno`=teachers.`tno`;
SELECT teachers.`tname`,courses.`cname`FROM teachers LEFT JOIN courses ON teachers.`tno`=courses.`tno`;
SELECT teachers.`tname`,courses.`cname`FROM teachers RIGHT JOIN courses ON teachers.`tno`=courses.`tno`;

25.查询女老师信息

SELECT * FROM teachers INNER JOIN courses ON courses.`tno`= teachers.`tno`AND teachers.`tsex`='女';

26.查询得分前三名的学员信息(limit分页)

SELECT * FROM scores ORDER BY degree DESC LIMIT 3;  #查询前三条(前三名)
SELECT * FROM scores ORDER BY degree DESC LIMIT 1,3;  #从第二条记录开始查询三条

27.查询课程是“计算机导论”的得分前三名的学员信息

SELECT `students`.`sname`,`students`.`ssex`,scores.`degree`,scores.`sno` 
FROM scores
INNER JOIN `students`ON students.`sno`=scores.`sno`
INNER JOIN courses ON courses.`cno`=scores.`cno`
WHERE cname = '计算机导论' ORDER BY scores.`degree` DESC LIMIT 3;
SELECT cno FROM courses WHERE cname = '计算机导论';

28.课程号“3-105”的倒数最后3名学员信息

SELECT students.`sname`,`students`.`ssex`,`scores`.`degree`
FROM scores
INNER JOIN `students`ON `students`.`sno`=scores.`sno`
INNER JOIN courses ON courses.`cno`= `scores`.`cno`
WHERE scores.`cno`= '3-105' ORDER BY `degree`ASC LIMIT 3;