SQL语句基础（习题和解析）

前言

1、定义：SQL是一种结构化语言，用于访问数据库，是一种ANSI的标准计算机语言。

2、SQL语言可以分为两部分：数据操作语言（DML）和数据定义语言（DDL）。

DML部分包含常见的插入、删除、查询、更新记录的语法：

• SELECT - 从数据库表中获取数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据
• INSERT INTO - 向数据库表中插入数据

DDL部分包括常见的创建、删除表格、建立索引、建立表连接等：

• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

3、SELECT查询语句格式：

• SELECT [ALL | DISTINCT] 栏位名 [,栏位名...]
• FROM 资料表名 [,资料表名...]
• [WHERE 筛选条件式]
• [GROUP BY 栏位名[,栏位名...]]
• [ORDER BY 栏位名[,栏位名...]]
• HAVING 合计函数(栏位名) 运算操作 数值

 

习题

题源来自牛客网。

1、查找最晚入职员工的所有信息。

select * 
from employees
where hire_date = (select max(hire_date) from employees);

分析：这里用到了max函数，用于返回一列中的最大值，语法为：select max(col_name) ［as alias］ from table_name;

 

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

select distinct *
from employees
order by hire_date desc
limit 2,1;

分析：在一张表中，可能存在重复值。distinct用于返回唯一的一条记录。这里，如果不使用distinct可能返回多条记录。语法为：select distinct col_name from table_name;

limit m,n  表示从m+1条开始，取n条数据。而 limit n 是 limit 0,n 的缩写，表示从第一条开始取n条数据。语法为：select col_name from table_name limit m,n;

order by用于根据指定列对结果集进行排序，默认排序方式为ASC升序方式，DESC表示对指定列进行降序排列。语法为：order by col_name desc/asc;

 

3、查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no。

select s.*,d.dept_no
from salaries as s join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01';

分析：join关键字用于表连接，根据两个或多个表的列之间的关系进行数据的查询。具有以下四种分类：

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

语法为：select col_name from table_a as a join table_b as b on a.col_namex=b.col_namex where condition;

 

4、查找所有已经分配部门的员工的last_name和first_name以及dept_no。

select e.last_name,e.first_name,d.dept_no
from dept_emp as d 
left join employees as e
on d.emp_no=e.emp_no;

分析：这里涉及到两个表的连接查询，要求查询已经分配部门的员工，所有已经分配的员工都在部门表中，即部门表中的记录都可以从员工表中找到对应，但是反之不可以，可能存在员工记录但是还未分配部门。所以这里可以用inner join或者left join，不可用right join。inner join表示内连接，返回两个表交叉后公共的部分作为结果集，而left join为左连接，返回的结果集将显示左表的全部内容和右表中与左表公共得内容。具体语法见上题。

 

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工。

select e.last_name,e.first_name,d.dept_no
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no;

分析：跟上题相反，在这一题中，它相当于查询员工表中的所有记录，并显示他们的部门。这里通过员工表左连接部门表，可以显示出所有的员工记录，并通过连接查询部门表获得每个员工的所在部门，如果在部门表中没有查询到该记录，那么为空值。

 

6、查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序。

select emp_no,salary
from salaries
group by emp_no
having min(from_date)
order by emp_no desc;

分析：这里的salaries表中包含了emp_no和salary两种属性。对于一个员工，在工作过程中可能发生多次涨薪，因此在salaries表中可能存在一个一个员工（emp_no）拥有多条薪水（salary）记录。那么可以直接根据from_date获取结果。

group by用于结合合计函数的sql语句中，根据指定列对结果集进行分组，语法为：select col_name from table_name where condition group by sp_col_name;

这里用到了min函数，由于where关键字无法与合计函数一起使用，所以这里使用了having子句。

 

7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t。

select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t>15;

分析：由上题分析可知，一个员工（emp_no）可对应多条薪水记录，因此，这里将根据员工编号进行分组，统计员工编号对应的记录书即涨薪次数。由题干知，薪水涨幅需要超过15，又这里使用了合计函数count，所以使用having进行条件选择。

 

8、找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示。

select distinct salary
from salaries
where to_date='9999-01-01'
group by emp_no
order by salary desc

分析：由相同的薪水只显示一次知需要根据员工编号进行分组并使用distinct关键字，由按照逆序显示可知需要根据薪水进行逆序排序，使用order by语句以及desc关键字。

 

9、获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
select m.dept_no,m.emp_no,s.salary
from dept_manager as m
left join salaries as s
on m.emp_no=s.emp_no 
and s.to_date=m.to_date
where m.to_date='9999-01-01'

分析：这里看起来较容易出错。可以使用左连接的方法将manager表与salaries表进行连接，重要把握题干信息“当前表示to_date='9999-01-01'”，在salaries表中，表示当前的薪水，在dept_manager表中表示当前的manager。这里通s.to_date=m.to_date where m.to_date='9999-01-01'来筛选出当前manager的当前薪水。

 

10、获取所有非manager的员工emp_no

select emp_no
from employees
where emp_no not in (select emp_no
                     from dept_manager);

分析：所有员工都在员工表employees中，但只有manager身份才在demp_manager表中，所以只要查询员工表employees的所有员工，除去在管理者dept_manager表中的数据即可，这里用到了not in语句。操作符in用于在where子句中规定多个值。语法为：select col_name from table_name where col_namex [not] in (value1,value2...);

 

11、获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

select e.emp_no,m.emp_no as manager_no
from dept_emp as e,dept_manager as m
where e.dept_no=m.dept_no 
and e.emp_no<>m.emp_no
and m.to_date='9999-01-01';

分析：由题干知，第一列为员工表中的员工编号emp_no，第二列为管理者表中的员工编号emp_no，需求同时对两张表进行查询。由题知条件有三：员工与管理者在同一个部门中（部门编号相同）、当前的manager是自己的话不显示（员工表中的员工编号与管理者的员工编号不相同）、当前的manager。由此可得该sql查询语句的条件。在SQL语句中，不等于有两种表达形式，一个是 “!=” ，另一个是 “<>” ，用法都是一样的。

 

12、获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary。

select e.dept_no,e.emp_no,s.salary
from dept_emp as e
left join salaries as s
on e.emp_no=s.emp_no
where e.to_date='9999-01-01' and s.to_date='9999-01-01'
group by dept_no
having s.salary=max(s.salary);

分析：看起来似乎有点复杂。首先根据要求写出对应的列，由于涉及到两张表的数据，且员工表dept_emp中涉及的部门编号dept_no均要包含在结果集中，因此这里进行了左连接。由题干中的“当前”需要对to_date列进行限制，由于是每个部门的最高薪水，所以这里根据部门编号进行分组，通过max函数查找出最大值。

 

13、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(title) as t
from titles
group by title
having count(title)>=2；

分析：此题跟第7题相似，根据指定title列对表数据进行分组，通过having子句进行条件选择。这里一是注意设置检索列的别名，二是用having来进行条件选择。

 

14、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

select title,count(distinct emp_no) as t
from titles
group by title
having t>=2;

分析：此题跟上一题不同的是，附加了一个条件对于重复的emp_no进行忽略。通过使用distinct关键字对emp_no进行去重。

 

15、查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列。

select *
from employees
where (emp_no+1)%2=0 and last_name<>'Marry'
order by hire_date desc;

分析：题干有三个条件，一是emp_no为奇数，二是last_name不为Mary，三是逆序排列。奇数条件设置通过取余运算，通过<>比较last_name，通过order by和desc关键字进行逆序排序。

 

16、统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

select t.title,avg(s.salary)
from salaries as s
inner join titles as t
on t.emp_no=s.emp_no
and s.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by t.title;

分析：由于是各个title类型对应的工资，所以用到group by子句进分组；通过合计函数avg获得每个分组的平均值。在写这一题的时候错了很多次，试了左连接和右连接，好像只能用内连接inner join，写到这里好疲惫啊......

 

17、获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary。

select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc limit 1,1;

分析：这里考查两个用法，一个是逆序排序，另一个是limit子句。第2题中已经涉及到limit的用法，语法为：select col_name from table_name order by col_namex limit m,n;

 

18、查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by。

select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees as e
inner join salaries as s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01'
and s.salary not in(select max(salary)
                   from salaries
                   where to_date='9999-01-01');

分析：此题与上题要查询的内容一样，但不同的是，这里不允许使用order by子句，所以这里用了操作符not in。使用max函数找到薪水最高者，然后除去最高薪水者，剩余最大值便是次高者。

 

19、查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工。

select e.last_name,e.first_name,d.dept_name
from employees as e
left join dept_emp as de on e.emp_no=de.emp_no
left join departments as d on de.dept_no=d.dept_no;

分析：这里要检索的数据涉及到三张表，要求检索员工表中所有的记录，并返回对应的部门名字。由题意，这里应该用到左连接，将三张表进行连接起来。

 

20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth。

select (select salary 
        from salaries 
        where emp_no=10001
        order by to_date desc limit 1)-(select salary
                                     from salaries
                                     where emp_no=10001
                                     order by to_date limit 1) as growth;

分析：这一题比较考查数理逻辑，思路是：通过查询最初的薪水和最近的薪水，然后相减作为要查询的growth值。在查询时，要注意两个条件，一个是员工编号，另一个是对于最初和最晚的薪水的取值。这里用最近的最水减去最初的薪水即所要的值，最近的薪水通过order子句对to_date列进行逆序排序，使用limit子句而获得。

 

21、查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序。

select e.emp_no,(s1.salary-s2.salary) as growth
from employees as e
inner join salaries as s1
on e.emp_no = s1.emp_no and s1.to_date = '9999-01-01'
inner join salaries as s2
on e.emp_no = s2.emp_no and e.hire_date = s2.from_date
order by growth;

分析：本题需要查询所有员工入职以来的薪水涨幅情况，思路是用每个员工当前的（to_date='9999-01-01'）的薪水减去入职时（from_date=hire_date）的薪水。这里将员工表与薪水表进行内连接，构造了两张表，分别用来查询入职时的薪水与当前的薪水。

 

22、统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum。

select de.dept_no,d.dept_name,count(s.emp_no)
from salaries as s,dept_emp as de,departments as d
where s.emp_no = de.emp_no
and de.dept_no = d.dept_no
group by d.dept_no;
分析：这里涉及到三张表的数据，分别是部门表departments、部门员工表dept_emp、薪资表salaries。将三张表通过where条件子句进行连接，由于是对各个部门的数据进行统计，那么按照部门编号进行分组查询，使用group by。

 

23、对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列。

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries as s1,salaries as s2
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
and s1.salary <= s2.salary
group by s1.emp_no 
order by rank;

分析：本题的主要思想是复用salaries表进行排名，一张表用来排序，一张表用来计算排名。通过条件to_date = '9999-01-01'筛选出所有员工当前的薪水，通过count(distinct s2.salary)计算排名，使用order by rank对结果集进行排序。由于这里用到了合计函数count，所以需要进行分组查询。

 

24、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'。

select de.dept_no,e.emp_no,s.salary
from employees as e
left join dept_emp as de
on e.emp_no = de.emp_no
left join salaries as s
on e.emp_no = s.emp_no
where s.to_date='9999-01-01' and de.to_date='9999-01-01' and e.emp_no not in (select emp_no
                                                  from dept_manager
                                                 where to_date='9999-01-01');

分析：这里涉及到四张表，解法有多种，这里我将部门表、员工表、薪水表根据员工编号进行了连接，再使用操作符not in除去身份为manager的员工，根据剩下的选择条件进行查询便可得到所需的数据。操作符in允许在where子句中规定多个值，语法格式为：select col_name from table_name where [condition and] [not] in(values)。

 

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary。

select de.emp_no as emp_no, dm.emp_no as manager_no,se.salary as emp_salary,sm.salary as manager_salary
from dept_emp as de,dept_manager as dm
left join salaries as se on de.emp_no = se.emp_no
left join salaries as sm on sm.emp_no = dm.emp_no
where de.dept_no = dm.dept_no
and de.to_date = '9999-01-01'
and dm.to_date = '9999-01-01'
and se.to_date = '9999-01-01'
and sm.to_date = '9999-01-01'
and se.salary > sm.salary;

分析：主要思想是创建两张表，一张记录所有员工的工资，一张记录所有manager的工资，这里使用左连接left join分别将员工表和薪资表进行连接，将manager表和薪资表进行连接，最后再通过where条件子句进行过滤。

 

26、汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count。

select d.dept_no,d.dept_name,t.title,count(t.title) as count
from dept_emp as de 
inner join departments as d
on d.dept_no = de.dept_no
inner join titles as t
on de.emp_no = t.emp_no
where de.to_date = '9999-01-01' and t.to_date = '9999-01-01'
group by de.dept_no,t.title;

分析：此题的思想也是构造两张表，分别是将员工表与部门表、员工表与title表进行内连接，前者用来检索部门编号和部门名称，后者用来统计title。由于是每个部门内的统计，以及统计每个title类型的数目，因此，这里需要根据员工所在部门的编号以及title进行分组。

 

27、给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

select s2.emp_no,s2.from_date,(s2.salary - s1.salary) as salary_growth
from salaries as s1,salaries as s2
where s1.emp_no = s2.emp_no
and salary_growth > 5000
and (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
order by salary_growth desc;

分析：思路是构造两张表，一张是涨薪前的表s1，一张是涨薪后的表s2。两张表薪水的差值作为薪水涨幅值，使用where条件子句筛选出涨幅值超过5000且时间间隔超过一年的记录，最后根据涨幅值进行排序。

 

28、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部。

select c.name,count(f.film_id) as amount
from film as f
inner join film_category as fc on f.film_id = fc.film_id
inner join category as c on c.category_id = fc.category_id
where f.description like '%robot%'
group by c.category_id
having amount >= 2;

select c.name,count(c.category_id) as amount
from film as f,film_category as fc,category as c
where f.description like '%robot%'
and f.film_id = fc.film_id
and fc.category_id = c.category_id
and c.category_id in (select category_id 
                       from film_category 
                       group by category_id 
                       having count(film_id)>=5)；

分析：使用第一种也可以在牛客网也可以通过，题目中说的大于等于5不是说分类中包含robot信息的电影数量大于等于5，而是说包含robot所在分类的电影数量>=5，也就是该分类的总数本来就是大于等于5。在第一题磕了不少，看讨论后发现把5改成2就通过了。这里可以简单粗暴的使用第二种方法，将三张表直接使用where条件子句关联起来。

 

29、使用join查询方式找出没有分类的电影id以及名称。

select f.film_id,f.title
from film as f
left join film_category as fc on f.film_id = fc.film_id 
where fc.category_id is null;

分析：题目给出三张表，但实际上只涉及到两张表，使用左连接便可以实现。left join表示左连接，返沪左表中所有的行，即使右表中没有匹配。如果有表中没有匹配，那么对应的数据为null。其语法为：select col_name form table_name1 left join table_name2 on table_name1.col_namex = table_name2.col_namex。

 

30、使用子查询的方式找出属于Action分类的所有电影对应的title,description。

select film.title,film.description
from film left join film_category as fc
on film.film_id = fc.film_id
where fc.category_id in (select category_id
                        from category
                        where name = 'Action');

分析：这一题考查自查询，相对于其他涉及到三张表的题目还算简单。这里将电影表和电影分类表进行了连接，然后在where条件子句中，使用了自查询，对分类id进行过滤。子查询也称嵌套查询、内部查询，指的是where条件子句中嵌入查询语句。一个select语句的查询结果可以作为另一个语句的输入值，必须用括号括起来，可以返回多行，必须与多值操作符如in一起使用。

 

31、获取select * from employees对应的执行计划。

explain select * from employees;

分析：使用explain命令可以查看执行计划。explain显示了如何使用索引来处理select语句以及连接表，进而分析查询语句或表结构的性能瓶颈。

 

32、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分。

select last_name||' '||first_name as Name from employees;

分析：考查字符串的拼接。这里使用 "||" 来进行字段的拼接，在mysql、oracle等其他非sqlite数据库中使用concat方法进行拼接：select concat(last_name,' ',first_name) as Name from employees。

 

33、创建一个actor表，包含如下列信息

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间

create table if not exists actor(
    actor_id smallint(5) not null primary key,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update timestamp not null default (datetime('now','localtime'))
);

分析：考查表的创建，使用create table子句。该子句用于创建数据库中的表，表由行和列组成，每个表必须有个表名，其语法为：create table table_name(column_name dataType(size))。这里考虑三个地方：是否数据库中已存在该表、设置主键、默认设置系统当前时间。第一个通过在table后面加上if not exists实现，第二个主键的设置可以在字段后面添加primary key，可以单独使用表表中使用primary key(col_name)，主键必须包含唯一的值且不能为空；第三个通过default (datetime('now','localtime'))实现默认当前时间。

 

34、对于表actor批量插入如下数据。

insert into actor
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

分析：考查数据库中表的插入，通过insert into子句实现向数据库的某张表中添加新的数据行，其语法为：insert into table_name[column_name] values (column_name)。

 

35、对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

insert or ignore into actor
values ('3','ED','CHASE','2006-02-15 12:34:33');

分析：与上题不同的是，这里考虑到插入到重复数据，在实际的应用中也极有可能插入重复数据。通过insert or ignore into子句实现插入操作时忽略数据库中已存在的数据，使用insert or replace子句实现插入操作时更新代替数据库中已存在的重复数据。

 

36、创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。

create table actor_name as
select first_name,last_name from actor;

分析：本题考查表的创建，表中的数据来自另外一张表。这里使用create table子句创建表，使用select子句对actor的数据进行检索，通过关键字as将查询出来的数据导入到actor_name表格中。

 

37、对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname。

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

分析：本题考察索引的创建。在不读取整个表的情况下，索引可以使数据库应用程序更快地查找数据。用户无法看见索引，索引只能被用来加快搜索／查询。创建索引使用到create index子句，其语法为：create [unique] index index_name on table_name(col_name)。

 

38、针对演员表创建视图actor_name_view，只包含如first_name以及姓氏两列，并对这两列重新命名，如first_name为first_name_v，姓氏修改为last_name_v。

create view actor_name_view(first_name_v,last_name_v) as
select  first_name,last_name
from actor;

分析：本题考察了视图的创建，需要对涉及的列进行重命名，用到了create view子句。视图是可视化的表，在sql中，视图是基于sql语句的结果集的可视化的表，其语法为：create view view_name(alias) as select col_name form table_name where condition 。

 

39、针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

select * from salaries indexed by idx_emp_no where emp_no = 10005;

分析：题目要求针对系统的索引表idx_emp_no进行索引查询。创建索引使用create index子句，语法为：create index index_name on table_name(column_name)。在sqlite中强制索引使用indexed by index_name子句，在mysql中使用force index index_name。indexed by index_name子句规定必须需要命名的索引来查找前面表中值，如果索引名index_name不存在或者不能用于查询，sqlite语句的准备失败。其语法为：select|delete|uodate col_name from table_name indexed by index_name where condition

 

40、现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'。

alter table actor 
add column create_date datetime not null default '0000-00-00 00:00:00';

分析：这里需要对表actor增加一个列，使用alter table子句。该子句可以用来增加、删除、修改列，增加列的语法为：alter table table_name add column col_name datatype；删除列的语法为：alter table table_name drop column col_name；修改列的语法为：alter table table_name alter column datatype;

 

41、构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

create trigger audit_log after insert on employees_test
begin
    insert into audit values(new.id,new.name);
end;

分析：此题考查触发器的创建。触发器是数据库的回调函数，会在指定的数据库事件发生时自动执行／调用。语法为：create trigger trigger_name before/after event_name on table_name begin --触发器逻辑.. end; 注意，触发器执行的内容要写在begin与end之间，是一个或多个完整的sql语句，单个的sql语句后面一定要带分号，否则会报错！！可以使用new或old关键字访问触发前或触发后的表单记录。

 

42、删除emp_no重复的记录，只保留最小的id对应的记录。

delete from titles_test
where id not in(select min(id)
               from titles_test
               group by emp_no);

分析：本题考查删除表记录的操作，通过delete语句实现。delete语句用于删除表中的记录，语法为：delete from table_names where col_names=col_values。

 

43、将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

update titles_test
set to_date = null ,from_date = '2001-01-01'
where to_date = '9999-01-01';

分析：此题考查表记录的更新操作，使用update语句实现。update语句用于更新表中的记录，语法为：update table_name set col_name1 = col_value1,col_name2 = col_value2,... where col_namex = col_valuex; 注意，set后面的字段名之间只能用逗号隔开，不能使用and。

 

44、将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5;

分析：这里考查replace的使用。有两种方法，一是使用replace into子句，二是使用replace函数都可以实现。前者的语法和insert类似，这里要注意的是插入的值一定要完整，否则由于表中已存在id＝5的记录会插入新的记录；后者使用的是replace(X,Y,Z)，第一个参数表示待处理的字符串，第二参数表示要被替换的字符串，第三个参数表示用来替换的字符串。

 

45、将titles_test表名修改为titles_2017。

alter table titles_test
rename to titles_2017;

分析：这里考查了表格的重命名，使用alter语句以及rename to子句。更改表名的语法为：alter table table_name rename to modified_name。

 

46、在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

drop table audit;
create table audit(
    emp_no int not null,
    create_date datetime not null,
    foreign key(emp_no) references employees_test(id));

alter table audit
add  foreign key(emp_no) references employees_test(id);

分析：考查外键约束的添加，一般使用alter语句对表进行修改，使用add子句添加外间约束。语法为：alter table table_name add foreign key(col_name) references foreign_table_name(foreign_col_name); 但是在牛客网这里，只能先将audit表删除，使用drop语句，语法为：drop table table_name; 然后再重新创建表，最后加上foreign key(col_name) references foreign_table_name(foreign_col_name);

 

47、存在如下的视图：create view emp_v as select * from employees where emp_no >10005; 如何获取emp_v和employees有相同的数据？

select * from emp_v intersect select * from employees;

分析：考查对于两个表求交集。使用intersect关键字可对两张表进行求交集，语法为：select语句 intersect select 语句；

 

48、将所有获取奖金的员工当前的薪水增加10%。

update salaries
set salary = salary*1.1
where emp_no in (select emp_no
                from emp_bonus);

分析：这里考查数据表的更新操作以及子查询操作。前者使用update语句，后者使用in关键字。

 

49、针对库中的所有表生成select count(*)对应的SQL语句。

select "select count(*) from "||name||";"
from sqlite_master
where type = 'table';

分析：本题只要将所有表的表名检索出来然后进行字符串的拼接即可。检索表名通过检索sqlite系统表sqlite_master实现，该表可以获得所有表的索引，其中字段name表示表名，对于自己创建的表，type类型为table。

 

50、将employees表中的所有员工的last_name和first_name通过(')连接起来。

select last_name||"'"||first_name
from employees;

分析：本地考查了字符串的连接。在sqlite中只支持用连接符号“||”来连接字符串，不支持用函数连接。在mysql、oracle等其他非sqlite数据库中使用concat方法进行拼接：select concat(last_name,"'",first_name) as Name from employees。

 

51、查找字符串'10,A,B' 中逗号','出现的次数cnt。

select length('10,A,B')-length(replace('10,A,B',',','')) as cnt;

分析：这里不同的是不是对数据库的表进行相关操作，而是对字符串进行操作。那么思路是：对字符串中的指定字符进行替换，然后计算长度，用原始长度减去替换后的长度即是指定字符出现的字数。这里对于替换使用到了replace函数。

 

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

select first_name
from employees
order by substr(first_name,length(first_name)-1,2);

分析：这里的难点是获取first_name的最后两个字母，使用substr函数。数据库中的substr(X,Y,Z)用于截取字符串，第一个参数表示待截取的字符串，第二个参数表示截取的起始位置，第三个参数表示截取长度。如果第三个参数省略，那么将截取到最后一个字符。

 

53、按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees。

select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no;

分析：本题考查聚集函数group_concat的使用。在sqlite中，聚集函数group_concat(X,Y)用于对指定表字段名的值进行连接，X表示字段名，Y表示连接符号，默认为逗号。

 

54、查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01'
and salary not in (select max(salary) from salaries)
and salary not in (select min(salary) from salaries);

分析：这里求某一列的平均值，但是不是求全部数据的平均值，二是附带了条件，要求去掉当前最大和最小。对于最大和最小数据，可以分别使用函数max和函数min获得。通过where条件子句以及子查询去掉最大值和最小值即可。

 

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

select *
from employees
limit 5,5;

分析：考查分页查询。对于数据库的分页查询，使用limit子句可实现。limit m,n，第一个参数表示结果的索引值，默认从0开始，第二个参数表示查询结果返回的数量。语法为：select col_name from table_name limit m,n。

 

56、获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示。

select de.emp_no, de.dept_no, eb.btype, eb.recevied
from dept_emp as de left join emp_bonus as eb 
on de.emp_no = eb.emp_no;

分析：没有分配具体的员工不显示，由于已分配的员工都记录在dept_emp中，那么只需要将部门员工表dept_emp与员工薪资表emp_bonus进行左连接即可。

 

57、使用含有关键字exists查找未分配具体部门的员工的所有信息。

select *
from employees
where not exists (select emp_no
                  from dept_emp
                  where dept_emp.emp_no = employees.emp_no);

分析：考查exists关键字的使用。exists对外表使用loop逐条查询，每次查询都会查看exists的条件语句，如果条件语句可以返回记录时，条件为真，返回loop到的这条记录。exists关键字的语法为：select col_name form table_name where exists (select col_namex from table_namex where conditino);

 

58、获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

select e.* 
from employees as e,emp_v as ev
where e.emp_no = ev.emp_no;

分析：应该是考查对两张表共同数据的检索。这里也可以直接对视图emp_v进行检索，因为视图是在原表的基础之上建立的，使徒的数据都来自于原表，是一种只读形式的虚表。

 

59、给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'。

select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,(case b.btype
                                                          when 1 then s.salary*0.1
                                                          when 2 then s.salary*0.2
                                                          else s.salary*0.3 end) as bonus
from emp_bonus as b left join employees as e
on b.emp_no = e.emp_no
left join salaries as  s
on s.emp_no = e.emp_no
where s.to_date='9999-01-01';

分析：考查了列值的计算，使用到了case子句。涉及到三张表的数据，使用左连接将三张表进行连接。由题目知，这里需要检索的是获取奖金的员工信息，那么将奖金表作为左表，只需要根据左表获得员工其他信息即可。对于奖金bonus的计算，这里使用到case子句进行计算。语法为：case col_name when col_value1 then value1 when col_value2 then value2 else value3 end; 或者：case when col_name=col_value1 then value1 when col_name=col_value2 then value2 else value3 end。

 

60、按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

select s1.emp_no,s1.salary, sum(s2.salary) as running_total
from salaries as s1,salaries as s2
where s2.emp_no<=s1.emp_no
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
order by s1.emp_no;
分析：这里的难点在于前面薪水之和的计算，题目表述应该是有问题的。通过sum函数对前面员工的薪水进行求和作为running_total。

 

61、对于employees表中，给出奇数行的first_name。

select e1.first_name
from employees as e1
where (select count(*) from employees as e2 where e1.first_name>=e2.first_name)%2==1;

分析：题目要求检索奇数行的名字，那么只要找出奇数行的条件即可： (select count(*) from employees as e2 where e1.first_name>=e2.first_name)%2==1。