(7)以class降序查询student表中的所有记录：

        降序：desc

        select * from student order by classdesc;

        升序：asc（默认为升序）：

        select * from student order by class;

DELETE FROM 表名称 WHERE 列名称 = 值

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

ORDER BY 语句

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

TOP 子句

TOP 子句用于规定要返回的记录的数目。

对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。

注释：并非所有的数据库系统都支持 TOP 子句。

SELECT TOP number|percent column_name(s)

FROM table_name

SELECT TOP 2* FROM Persons

SELECT TOP 50 PERCENT* FROM Persons

在数据库中很多地方都会用到，比如当你数据库查询记录有几万、几十万时使用limit查询效率非常快，只需要查询出你需要的数据就可以了·再也不用全表查询导致查询数据库崩溃的情况。

select * from Customer LIMIT 10;--检索前10行数据，显示1-10条数据

select * from Customer LIMIT 1,10;--

检索从第2行开始，累加10条id记录，共显示id为2....11

select * from Customer limit 5,10;--

检索从第6行开始向前加10条数据，共显示id为6,7....15

select * from Customer limit 6,10;--

检索从第7行开始向前加10条记录，显示id为7,8...16

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

select *fromemployees order by hire_date desc limit 2,1;

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

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

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

SELECT s.*,d.dept_no FROM salaries s ,  dept_managerd

 WHERE s.to_date='9999-01-01'

 AND d.to_date='9999-01-01'

 AND s.emp_no = d.emp_no;

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

selecte.last_name, e.first_name, d.dept_no from employees eleft joindept_emp d on e.emp_no = d.emp_no

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

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`));

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`));

此题应注意以下四个知识点：

1、由于测试数据中，salaries.emp_no 不唯一（因为号码为 emp_no 的员***有多次涨薪的可能，所以在 salaries 中对应的记录不止一条），employees.emp_no 唯一，即 salaries 的数据会多于 employees，因此需先找到employees.emp_no 在 salaries 表中对应的记录salaries.emp_no，则有限制条件e.emp_no =s.emp_no

2、根据题意注意到salaries.from_date 和employees.hire_date 的值应该要相等，因此有限制条件e.hire_date= s.from_date

3、根据题意要按照 emp_no 值逆序排列，因此最后要加上ORDER BYe.emp_no DESC

4、为了代码良好的可读性，运用了 Alias 别名语句，将 employees 简化为 e，salaries 简化为s，即 employees AS e 与 salaries AS s，其中 AS 可以省略

select e.emp_no, s.salary from salaries s, employees e

where s.emp_no = e.emp_no

and s.from_date = e.hire_date

order by e.emp_no desc;

或者

selecte.emp_no, s.salary from salaries s

inner joinemployees e

on s.emp_no =e.emp_no

and s.from_date= e.hire_date

order bye.emp_no desc;

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

此题应注意以下四点：

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数

2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t

3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可

SELECT emp_no,COUNT(emp_no) AS t FROM salaries

GROUP BY emp_noHAVING t > 15

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

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

select salaryfrom salaries where to_date='9999-01-01' group by salary order by salary DESC

或者select distinct salary from salaries whereto_date='9999-01-01' order by salary desc

注意：1、WHERE语句在GROUP BY语句之前，SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后，SQL会在分组之后计算HAVING语句

2、having 是过滤组 where过滤行，你先group by 那么having之后是得到的是满足某种条件的其中某一组，而你那个where不是在对组加条件，而是针对行，你的分组里面有不符合条件的记录

8、获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

selectd.dept_no, d.emp_no, s.salary from dept_manager d, salaries s

where d.to_date='9999-01-01'

ands.to_date='9999-01-01'

andd.emp_no=s.emp_no

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

方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录

SELECT emp_noFROM employees

WHERE emp_noNOT IN (SELECT emp_no FROM dept_manager)

方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录

SELECT emp_noFROM (SELECT * FROM employees LEFT JOIN dept_manager

ONemployees.emp_no = dept_manager.emp_no)

WHERE dept_noIS NULL

方法三：方法二的简版，使用单层SELECT语句即可

SELECTemployees.emp_no FROM employees LEFT JOIN dept_manager

ON employees.emp_no= dept_manager.emp_no WHERE dept_no IS NULL

或者

SELECTemployees.emp_no FROM salaries

EXCEPT

SELECTdept_manager.emp_no FROM dept_manager;

    -- EXPECT  集合差运算

    -- UNION 集合并运算

    -- INTERSECT集合交运算

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

selecte.emp_no, d.emp_no as manager_no from dept_emp e, dept_manager d

wheree.to_date='9999-01-01'

andd.to_date='9999-01-01'

and e.dept_no =d.dept_no

and e.emp_no!=d.emp_no

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

SELECTd.dept_no, s.emp_no, MAX(s.salary) AS salary

FROM salariesAS s INNER JOIN dept_emp As d

ON d.emp_no =s.emp_no

WHERE d.to_date= '9999-01-01' AND s.to_date = '9999-01-01'

GROUP BYd.dept_no

此题思路如下：Kebing_Lei@sjtu.edu.cn

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；

2、选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date =

'9999-01-01'作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；

3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；

4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

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

注意对于重复的emp_no进行忽略。

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

用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

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

select*fromemployees where last_name!='Mary' and emp_no%2=1 order by hire_date desc

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

select t.title,avg(s.salary) from titles t inner join salaries s

ont.emp_no=s.emp_no

wheret.to_date='9999-01-01'

ands.to_date='9999-01-01'

group byt.title

15、获取当前（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

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

selecte.emp_no, max(s.salary), e.last_name, e.first_name from employees e

inner joinsalaries s on e.emp_no=s.emp_no

wheres.to_date='9999-01-01' and s.salary not in (select max(salary)from salarieswhere to_date='9999-01-01')

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

CREATE TABLE`departments` (

`dept_no`char(4) NOT NULL,

`dept_name`varchar(40) NOT NULL,

PRIMARY KEY(`dept_no`));

CREATE TABLE`dept_emp` (

`emp_no`int(11) NOT NULL,

`dept_no`char(4) NOT NULL,

`from_date`date NOT NULL,

`to_date` dateNOT NULL,

PRIMARY KEY(`emp_no`,`dept_no`));

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`));

答：select e.last_name, e.first_name, d.dept_name fromemployees e

left joindept_emp dn on e.emp_no=dn.emp_no

left joindepartments d on dn.dept_no=d.dept_no

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

select (

(select salaryfrom salaries where emp_no=10001 order by to_date desc limit 0,1)-

(select salaryfrom salaries where emp_no=10001 order by to_date  limit 0,1)

) as growth

或select (max(salary)-min(salary)) as growth fromsalaries where emp_no=10001

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

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`));

CREATE TABLE `salaries`(

`emp_no`int(11) NOT NULL,

`salary`int(11) NOT NULL,

`from_date`date NOT NULL,

`to_date` dateNOT NULL,

PRIMARY KEY(`emp_no`,`from_date`));

select a.emp_no, (b.salary - c.salary) as growth

from

    employees as a

    inner joinsalaries as b

    on a.emp_no =b.emp_no and b.to_date = '9999-01-01'

    inner joinsalaries as c

    on a.emp_no =c.emp_no and a.hire_date = c.from_date

order by growth asc

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

selectd.dept_no, d.dept_name, count(*) as sum

from

    departments d inner join dept_emp de

    on d.dept_no = de.dept_no

    inner join salaries s

    on de.emp_no=s.emp_no

group byde.dept_no

20、对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列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`));

本题的主要思想是复用salaries表进行比较排名，具体思路如下：

1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = '9999-01-01'，挑选出当前所有员工的薪水情况。

2、本题的精髓在于 s1.salary

<= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。

3、千万不要忘了GROUP BY

s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()

4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果SELECTs1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank

FROMsalaries AS s1, salaries AS s2

WHEREs1.to_date = '9999-01-01'  AND s2.to_date= '9999-01-01' AND s1.salary <= s2.salary

GROUPBY s1.emp_no

ORDER

BY s1.salary DESC, s1.emp_no ASC主要是在对于排名的理解上，分成两张表，一张表中该工资的排名其实就是表中大于等于该工资的数目，由于相同的salary排名相同，所有count中要有distinct ，由于每个都要输出，要用group by ，否则应该是只有一条

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

selectde.dept_no,s.emp_no,s.salary

from employeese

inner joinsalaries s

one.emp_no=s.emp_no and s.to_date='9999-01-01'

inner joindept_emp de on e.emp_no=de.emp_no

where de.emp_nonot in

(select emp_nofrom dept_manager WHERE to_date = '9999-01-01')

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

selects1.emp_no, s2.emp_no manager_no, s1.salaryemp_salary,s2.salary manager_salary

from

    (select s.emp_no, de.dept_no, s.salary fromdept_emp de inner join salaries s on de.emp_no=s.emp_no ands.to_date='9999-01-01')

as s1,

    (select s.emp_no, dm.dept_no, s.salary fromdept_manager dm inner join salaries s on dm.emp_no=s.emp_no ands.to_date='9999-01-01')

as s2

wheres1.dept_no=s2.dept_no and s1.salary>s2.salary

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

selectdp.dept_no, dp.dept_name, t.title, count(t.title) as count

from titles tinner join dept_emp de

onde.emp_no=t.emp_no and de.to_date='9999-01-01'and t.to_date='9999-01-01'

inner joindepartments dp on dp.dept_no=de.dept_no

group bydp.dept_no,t.title

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

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

selects1.emp_no, s1.from_date, (s1.salary-s2.salary) as salary_growth

from salariess1 inner join salaries s2

ons1.emp_no=s2.emp_no

where strftime('%Y',s1.to_date)-strftime('%Y', s2.to_date)=1

andsalary_growth>5000

order bysalary_growth desc

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

select c.name,count(f.film_id) from category c

inner joinfilm_category fc on c.category_id=fc.category_id

inner join filmf on f.film_id=fc.film_id

wheref.description like '%robot%'

group byfc.category_id having count(f.film_id)>=2

或者select c.name, count(fc.film_id) from film_categoryfc

inner join(select * from film where description like '%robot%') as f

on fc.film_id =f.film_id

inner join(select *,count(film_id) as num from film_category group by category_id havingnum >= 5) as cfc

onfc.category_id = cfc.category_id

inner joincategory as c

on fc.category_id= c.category_id

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

selectf.film_id, f.title from film f

left joinfilm_category fc on f.film_id=fc.film_id

wherefc.category_id is null

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

select title,description from film

where film_idin(select film_id from film_category where category_id in

                (select category_id fromcategory where name='Action'))

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

explain select * from employees

explain

模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。作用

1

、表的读取顺序

2

、数据读取操作的操作类型

3

、哪些索引可以使用

4

、哪些索引被实际使用

5

、表之间的引用

6

、每张表有多少行被优化器查询

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

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

CONCAT方法：

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

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

 本题中使用：select last_name||" "||first_name asname  from employees

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

列表类型是否为NULL含义

actor_idsmallint(5)not null主键id

first_namevarchar(45)not null名字

last_namevarchar(45)not null姓氏

last_updatetimestampnot null最后更新时间，默认是系统的当前时间

create table 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'))

);获取系统默认时间是datetime（‘now’,'localtime'）

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

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

插入数据语法：insert into <表名> values (<数据1>,<数据2>....);

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

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

insert into:插入数据,如果主键重复，则报错

insert repalce:插入替换数据,如果存在主键或unique数据则替换数据

insert ignore:如果存在数据,则忽略。

create table ifnot exists actor_name (

    first_name varchar(45) not null,

    last_name varchar(45) not null);

insert intoactor_name select first_name,last_name from actor;

1.用create table 语句建立actor_name 表

2.用inset into actor select插入子查询的结果集(不需要用values()，()这种形式。这种形式是手工插入单条数据或多条数据时用圆括号分割。插入结果集是不需

32、针对如下表actor结构创建索引，对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

create uniqueindex uniq_idx_firstname on actor(first_name);

create indexidx_lastname on actor(last_name);

1.创建唯一索引:create unique index 'index_name' ontable_name(column)

2. 创建普通索引:create index 'index_name' on table_name(column)

33、针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句

方法一：注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

CREATE VIEWactor_name_view AS

SELECTfirst_name AS fist_name_v, last_name AS last_name_v

FROM actor

方法二：直接在视图名的后面用小括号创建视图中的字段名

CREATE VIEWactor_name_view (fist_name_v, last_name_v) AS

SELECTfirst_name, last_name FROM actor

创建视图语句：

CREATE VIEW <视图名称> (<视图列名1>,<视图列名2>…)

AS

<select 语句>；

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

index 强制索引的目的是对目标表添加最关键的索引，使其优先使用该索引筛选数据；

MYSQL中强制索引查询使用：FORCE INDEX(indexname);

SELECT * FROMsalaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

SQLite中强制索引查询使用：INDEXED BY indexname;

ELECT * FROMsalaries INDEXED BY idx_emp_no WHERE emp_no = 10005

35、存在actor表，现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为0000-00-00 00:00:00

ALTER TABLE 语句用于在已有的表中添加、修改或删除列

如需在表中添加列，

ALTER TABLE table_name

ADD column_name datatype

要删除表中的列

ALTER TABLE table_name

DROP COLUMN column_name

alter tableactor add create_date datetime NOT NULL default '0000-00-00 00:00:00'

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

构造触发器时注意以下几点：

create trigger 触发器名称  before/after insert/update/add on 表名

begin

触发器逻辑;

end;

1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER

2、触发器执行的内容写出 BEGIN与END 之间

3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

create triggeraudit_log after insert on employees_test

begin

     insert into audit values(new.id,new.name);

end;

#这里的 NEW.ID 是 触发器执行后，audit_log 表中 ID 字段的值，要将其插入到 audit 表的 EMP_NO 字段中，必须写new.，目的是提取新行

触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用，

[if !supportLists]1、 [endif]触发器（Trigger）可以指定在特定的数据库表发生DELETE、INSERT 或UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。

[if !supportLists]2、  [endif]WHEN 子句和触发器（Trigger）动作可能访问使用表单NEW.column-name和OLD.column-name的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。

[if !supportLists]3、  [endif]BEFORE 或AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

[if !supportLists]4、  [endif]要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用tablename，而不是database.tablename。

[if !supportLists]5、  [endif]     MySQL中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

[if !supportLists]6、  [endif]     ①在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

[if !supportLists]7、  [endif]     ②在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

[if !supportLists]8、  [endif]    ③在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

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

CREATE TABLE IFNOT EXISTS titles_test (

id int(11) notnull primary key,

emp_no int(11)NOT NULL,

title varchar(50)NOT NULL,

from_date dateNOT NULL,

to_date dateDEFAULT NULL);

insert intotitles_test values ('1', '10001', 'Senior Engineer', '1986-06-26','9999-01-01'),

('2', '10002','Staff', '1996-08-03', '9999-01-01'),

('3', '10003','Senior Engineer', '1995-12-03', '9999-01-01'),

('4', '10004','Senior Engineer', '1995-12-03', '9999-01-01'),

('5', '10001','Senior Engineer', '1986-06-26', '9999-01-01'),

('6', '10002','Staff', '1996-08-03', '9999-01-01'),

('7', '10003','Senior Engineer', '1995-12-03', '9999-01-01');

本题思路如下：先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM ... WHERE ... NOT IN ...语句删除“非每个分组最小id对应的所有记录”

Delete fromtitles_test

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

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

updatetitles_test set to_date=NULL , from_date='2001-01-01'where to_date='9999-01-01'

UPDATE 查询的基本语法如下：

UPDATEtable_name

SET column1 = value1,

　　column2 = value2....,

　　columnN = valueN

WHERE[condition];

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

①表更新使用update语句，多个更新之间用逗号隔开，而不能使用and连接。

②这里两个更新分别考察了简单update语句和搜索型update语句。

③一个比较容易出错的地方是某列更新为null时不能使用<列名> is null的方法，

正确的方法是：update <表名> set <字段> = null where <条件>;

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

replace intotitles_test values('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01')

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

 replace(要替换的值,替换前的数据,替换后的数据)

replace的使用方法：replace into table_name values(...)有则更新,无则插入注意：replace不能使用where语句，它是通过表主键来确定替换哪一条记录。

40、将titles_test表名修改为titles_2017。

修改表名：alter table 旧表名 rename to 新表名

alter tabletitles_test rename to titles_2017

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

alter tableaudit add foreign key(emp_no) references employees_test(id)

或者DROP table audit;

CREATE TABLEaudit(

    EMP_no INT NOT NULL,

    create_date datetime NOT NULL,

foreign key(EMP_no) references employees_test(ID));

42、存在如下的视图：create view emp_v as select * from employees

where emp_no >10005;如何获取emp_v和employees有相同的数据？

select*fromemployees where emp_no>10005

或者select * from emp_v intersect select * fromemployees

intersect：求交集

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

update salariesset salary = salary*1.1 where emp_no in (select emp_no from emp_bonus)

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

select"select count(*) from "||name||";" as cnts

    from sqlite_master

    where type='table';

mysql写法(牛客网不通过，mysql上运行通过)：

selectconcat("select count(*) from ","",table_name,";") as cnts

from (selecttable_name from information_schema.tables) as new;

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

selectlast_name||"'"||first_name from employees

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

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

length():统计字符串长度

replace(字符串，“需要替换的子串”，“用于替换子串的字符串”）

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

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

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

substr(string,start,length)

string - 指定的要截取的字符串。

start - 必需，规定在字符串的何处开始。正数 - 在字符串的指定位置开始，负数 - 在从字符串结尾的指定位置开始，0 - 在字符串中的第一个字符处开始。

length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。

例如：select substr('abcdefg',3,4) from dual; 结果是cdef

            select substr('abcdefg',-3,4) fromdual;结果efg

注意：字符串中的第一个位置始终为1。以下两个sql查询的结果相同：

例如：select substr('abcdefg',0,3) from dual; 结果是abc

            select substr('abcdefg',1,3) fromdual;结果是abc

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

聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来

SELECT dept_no,group_concat(emp_no) AS employees

FROM dept_empGROUP BY dept_no;

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

min()和max()都是聚合函数，是对结果集中的列进行操作而不是对单个记录进行操作

selectavg(salary) as avg_salary from salaries where to_date='9999-01-01'

andsalary!=(select max(salary ) from salaries)

andsalary!=(select min(salary ) from salaries)

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

每行5页，返回第2页的数据，即返回第6~10条记录

select*from employeeslimit 5,5

在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反

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

SELECTde.emp_no, de.dept_no, eb.btype, eb.recevied

from dept_empde left join emp_bonus eb

on de.emp_no =eb.emp_no

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

select*fromemployees e

where notexists(select emp_no from dept_emp de where de.emp_no = e.emp_no)

将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值

EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

in和exists的区别：

exists:存在，后面一般都是子查询，当子查询返回行数时，exists返回true。

select * fromclass where exists (select'x"form stu where stu.cid=class.cid)

exists(xxxxx)后面的子查询被称做相关子查询, 他是不返回列表的值的.只是返回一个ture或false的结果，也就是它只在乎括号里的数据能不能查找出来，是否存在这样的记录。

其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果，如果存在，返回ture则输出,执行顺序如下：

1.首先执行一次外部查询

2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。

3.使用子查询的结果来确定外部查询的结果集。

如果外部查询返回100行，SQL   就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。

in：包含

查询和所有女生年龄相同的男生

select * from

stu where sex='男' and age in(select age from stu where sex='女')

in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,

然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

1.什么时候用EXISTS，什么时候用IN？主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：当主表比从表大时，IN查询的效率较高；当从表比主表大时，EXISTS查询的效率较高；原因如下：

in

是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次

exists

是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

53、给出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,eb.btype, s.salary,

case eb.btype

   when 1 then s.salary*0.1

   when 2 then s.salary*0.2

else s.salary*0.3 end

from salaries s inner join emp_bonus eb ons.emp_no=eb.emp_no

inner join employees e on e.emp_no=s.emp_noand s.to_date='9999-01-01'

主要考察case 用法

case xx

when 条件 then 变化

when..

else 变化end

54、按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。

select s1.emp_no, s1.salary, (selectsum(s2.salary) from salaries s2

                              where s2.to_date='9999-01-01'

                              ands2.emp_no<=s1.emp_no) as running_total

from salaries s1 where s1.to_date='9999-01-01'

order by s1.emp_no

①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。

sum(<汇总列>)

over(<排序列>) as 别名；

②光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。

③这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前'9999-01-01'才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前。

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

-- 先要对表排序，然后找到每一行在表中的位置

-- 找到奇数位置的行，需要涉及到对同一个表的重复使用

select e1.first_name

from employees as e1

where (

select count(*)

from employees as e2

where e2.first_name <=e1.first_name    --统计当前(e1)first_name之前有多少条数据，即排在多少行数据

)%2=1;