【SQL】SQL做题笔记

SQL2 查找入职员工时间排名倒数第三的员工所有信息

Tips:

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

SQL5 查找所有员工的last_name和first_name以及对应部门编号

Tips:

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录

Tips:

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的变动次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

SQL8 找出所有员工当前薪水salary情况

Tips:

对于distinct与group by的使用:
1、当对系统的性能高并数据量大时使用group by
2、当对系统的性能不高时使用数据量少时两者皆可
3、尽量使用group by

大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。

SQL32 将employees表的所有员工的last_name和first_name拼接起

Tips:

MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串

CONCAT方法：

select CONCAT(CONCAT(last_name," "),first_name) as name from employees

或者

select CONCAT(last_name," "，first_name) as name from employees

本题中使用：

select last_name||" "||first_name as name from employees

SQL35 批量插入数据，不使用replace操作

Tips:

Sqlite执行insert or ignore 或insert or replace语句。

insert or replace into cardlog (mid,type) values (7,0);

第一条语句是每次执行时，如果不存在，则添加，如果存在，则更新。

insert or ignore into cardlog (mid,type) values (7,0);

上面的第二条语句是每次执行时，如果不存在，则添加，如果存在，则不操作。

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no

Tips:

SQLite中，使用 INDEXED BY 语句进行强制索引查询，可参考：

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

SQL51 查找字符串 10,A,B 中逗号,出现的次数cnt

Tips:

由于 SQLite中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）

> SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

SQL52 获取Employees中的first_name

Tips:

本题考查 substr(X,Y,Z) 或 substr(X,Y)函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1)

SQL53 按照dept_no进行汇总

Tips:

本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。group_concat（）函数要和group by语句同时使用才能产生效果。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）

SQL55 分页查询employees表，每5行一页，返回第2页的数据

Tips:
根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：

方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。
SELECT * FROM employees LIMIT 5 OFFSET 5
方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
SELECT * FROM employeesLIMIT 5,5