sql习题（基本题型及解答）【附有建表语句与数据插入语句】

个人对刷题后的记录和整理。随机更新。（主要运行环境：sqlite3、POLARDB-MySQL）
推荐在线刷题网站：牛客sql实战
（个人目前接触的刷题网主要是leetcode和牛客，由于牛客在线提交时，若语句没有错误，它会提供题目中的建表语句和表中数据，所以在刷题时，可以复制建表语句和插入数据的语句到私人的数据库中，在自己的库中完成sql的编写。这让我们提交自己的语句时，能先一步进行检查、更清晰的看见错误的地方。减少提交错误的次数）

结尾附有建表语句及数据

---

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

select * from employees limit 5,5;
select * from employees limit 5 offset 5;
select * from employees limit(2-1)*5,5;

解决方式：
limit x,n ：从第x条数据开始，取n条数据。第一条数据序号为0
limit x offset n ：从第x条数据开始，取n条数据。第一条数据序号为0
limit(2-1)*5,5 ：这条语句主要是为了体现limit的分页思想，*5表示每页5条数据，2-1代表跳过第一页，然后取第二页的5条数据，即整个第二页的数据

---

2、子串排序，获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

SELECT first_name from employees ORDER BY substr( first_name,-2);
SELECT first_name from employees ORDER BY substring( first_name,-2);
SELECT first_name from employees ORDER BY substr( first_name,length( first_name)-1, 2);
select first_name from employees order by right(first_name,2);

解决方式：【主要是提取字段中的字符】
substr( str,-n) 和 substring( str,-n) ：从末尾处，取字符串str的n个字符
substr( str,n) 和 substring( str,n) ：取字符串str的前n个字符
substr( str,x, n) ：从字符串str中下标为x的字符开始（包含下标为x的字符），取n个字符。字符串下标从1开始。
right(str,n) ：从字符串的右边开始，取n个字符。同理 left(str,n)从左边开始，取n个字符
length(str) ：获取str的长度，即字符个数

---

3、添加序列号，学生成绩排名，表名grade，列id，score。按成绩排名。

1、mysql，按成绩倒序（即从大到小）添加序号列。
select id,score,(@i:=@i+1) as rank from grade,(select @i:=0) b order by score desc;
2、

MySQL及Oracle添加序号列

---

4、修改表名，将titles_test表名修改为titles_2017

alter table employees rename to employees_007;

---

5、获取第二薪资，表名salaries，列：emp_no、salary。（尝试不使用order by完成）

1、按薪资倒序排序，取第二条数据。（但若有多条相同薪资，则有错误）
select * from salaries order by salary desc limit 1,1;
2、子查询：对salary薪资进行去重并倒序，获取第二条数据（即第二薪资）。主语句：获取薪资等于第二薪资的salaries表中所有数据。
select * from salaries where salary = 
(select distinct salary from salaries order by salary desc limit 1,1)
3、不使用order by。
select id,max(salary) as salary from salaries 
where salary < (select max(salary) from salaries)
[这里使用了两次求最大值，子查询：获取salaries中最大薪资；主语句：在小于最大薪资的数据中求最大薪资]

6、获取奇数偶数，从employees表中获取emp_no为奇数的员工，且按照first_name倒序。

1、使用除余来筛选
select * from employees where emp_no % 2 = 1 order by first_name desc;
2、调用函数mod：mod(x,n)=t 表示x除以n，余t。
select * from employees where mod(emp_no,2) = 1 order by first_name desc;
3、位运算
select * from employees where emp_no & 1 = 1  order by first_name desc;

7、列名拼接，从employees中查询first_name和last_name，使用空格分隔。

select first_name||' '||last_name as name from employees; --sqlite、Oracle适用。mysql不支持。
select concat(concat(first_name,' '),last_name) as name from employees; --Oracle、MySQL支持。sqlite不支持。

建表语句及数据：

CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'前端',12000),
(7,'前端',11000),
(8,'前端',9999);

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL, -- '所有的员工编号'
`dept_no` char(4) NOT NULL, -- '部门编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '经理编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO dept_manager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO dept_manager VALUES('d006',10010,'2000-06-26','9999-01-01');

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,90000,'1996-08-03','1997-08-03');