mysql面试题

1.根据部门号从高到低，工资从低到高列出员工的信息

select * from employee order by dept_id desc,salary

2.union和union all的区别

用union连接表后，重复的记录会被删除掉
用union all连接表后，记录会被完整保留

3.分页语句（用sql语句直接分页，效率高）

pageSize = 20;
pageNo = 5;
select * from tbname limit (pageNo-1)*pageSize,pageSize

4.数据库三范式
第一范式字段具有原子性，不可再分
第二范式表中的每列都和主键相关
第三范式每列都和主键列直接相关，而不是间接相关

5.用一条SQL语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100
王五英语 90

SELECT name, `subject`, score, SUM(score > 80) AS jg
FROM result
GROUP BY `name`
HAVING jg = (
	SELECT COUNT(DISTINCT rs.subject)
	FROM result rs
)

另一种写法，高大人写的

SELECT ttt.name
FROM (SELECT COUNT(r.name) AS c, r.name
	FROM result r
	WHERE r.score > 80
	GROUP BY r.`name`
	) ttt
WHERE ttt.c = (
	SELECT COUNT(DISTINCT `subject`)
	FROM result
	)

6.自动编号学号姓名课程编号课程名称分数

1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息

create TABLE tmp as SELECT s.code FROM student s 
GROUP BY s.`name`,s.kname,s.scode,s.kcode,s.score ;

DELETE from student
where code not in (select code from tmp);

drop table tmp;

7.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

SELECT * FROM 
department INNER JOIN department as a
ON
department.`name` <> (a.`name`)

8.列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

SELECT name, deptid, salary
FROM employee
WHERE salary > (
	SELECT AVG(salary)
	FROM employee
	)
GROUP BY deptid ASC

写完后把自己的练习SQL导出一份传上来

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目： 
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。

SELECT * FROM(SELECT id,realname,row_number()over(ORDER BY id asc) rn FROM T_USER)WHERE rn BETWEEN 10 AND 20

SELECT * FROM (SELECT ROWNUM rn,id,realname FROM (SELECT id,realname FROM T_USER)WHERE ROWNUM<=20) t2 WHERE T2.rn >=10;

8、数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

每天进步一步步，坚持坚持坚持