2023年6月27日发(作者：)

⽜客⽹SQL刷题记录⽂章⽬录查找最晚⼊职员⼯的所有信息select *

from employeesorder by hire_date desc limit 1;查找⼊职员⼯时间排名倒数第三的员⼯所有信息select * from employeesorder by hire_date desc limit 2,1;查找各个部门当前(to_date=‘9999-01-01’)领导当前薪⽔详情以及其对应部门编号dept_noselect s.*,_nofrom salaries as s inner join dept_manager as don _date='9999-01-01'and _date='9999-01-01'and _no=_no查找所有已经分配部门的员⼯的last_name和first_name,dept_noselect _name,_name,_nofrom employees as e inner join dept_emp as don _no=_no查找所有员⼯的last_name和first_name以及对应部门编号dept_no，也包括展⽰没有分配具体部门的员⼯select _name,_name,_no

from employees as e left join dept_emp as don _no=_no查找所有员⼯⼊职时候的薪⽔情况，给出emp_no以及salary， 并按照emp_no进⾏逆序select _no,from employees as e inner join salaries as son _no=_noand _date=_dateorder by _no desc查找薪⽔涨幅超过15次的员⼯号emp_no以及其对应的涨幅次数t?select emp_no,count(emp_no)as tfrom salariesgroup by emp_nohaving t>15找出所有员⼯当前(to_date=‘9999-01-01’)具体的薪⽔salary情况，对于相同的薪⽔只显⽰⼀次,并按照逆序显⽰select distinct salaryfrom salarieswhere to_date='9999-01-01'order by salary desc获取所有部门当前manager的当前薪⽔情况，给出dept_no, emp_no以及salary，当前表⽰to_date=‘9999-01-01’select _no,_no,from dept_manager as d,salaries as swhere _no=_noand _date='9999-01-01'and _date='9999-01-01'获取所有⾮manager的员⼯emp_noselect emp_nofrom employeeswhere emp_no not in (select emp_no from dept_manager)获取所有员⼯当前的manager，如果当前的manager是⾃⼰的话结果不显⽰，当前表⽰to_date=‘9999-01-01’。结果第⼀列给出当前员⼯的emp_no,第⼆列给出其manager对应的manager_no。?select _no,_nofrom dept_emp as dp inner join dept_manager as dmon _no=_noand _no<>_noand _date='9999-01-01'and _date='9999-01-01'获取所有部门中当前员⼯薪⽔最⾼的相关信息，给出dept_no, emp_no以及其对应的salaryselect _no,_no,max()from dept_emp as d inner join salaries as son _no=_noand _date='9999-01-01'and _date='9999-01-01'group by _no从titles表获取按照title进⾏分组，每组个数⼤于等于2，给出title以及对应的数⽬t。select title,count(title) as tfrom titlesgroup by titlehaving t>=2从titles表获取按照title进⾏分组，每组个数⼤于等于2，给出title以及对应的数⽬t。注意对于重复的title进⾏忽略。?select distinct title,count(distinct emp_no)as tfrom titlesgroup by titlehaving t>=2查找employees表所有emp_no为奇数，且last_name不为Mary的员⼯信息，并按照hire_date逆序排列select *

from employees

where emp_no%2=1and last_name<>'Mary'order by hire_date desc;统计出当前各个title类型对应的员⼯当前（to_date=‘9999-01-01’）薪⽔对应的平均⼯资。结果给出title以及平均⼯资avg。select ,avg() as avgfrom titles as t,salaries as swhere _date='9999-01-01'and _date='9999-01-01'and _no=_nogroup by 获取当前（to_date=‘9999-01-01’）薪⽔第⼆多的员⼯的emp_no以及其对应的薪⽔salaryselect emp_no,max(salary)from salarieswhere to_date='9999-01-01'and salary not in (select max(salary) from salaries)或者select emp_no,salaryfrom salarieswhere to_date='9999-01-01'order by salary desc limit 1,1查找当前薪⽔(to_date=‘9999-01-01’)排名第⼆多的员⼯编号emp_no、薪⽔salary、last_name以及first_name，不准使⽤order byselect _no,max(),_name,_namefrom salaries as s,employees as ewhere _no=_noand _date='9999-01-01'and not in (select max(salary) from salaries)查找所有员⼯的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员⼯select _name,_name,_namefrom employees as e left join dept_emp as dpon _no=_noleft join departments as don _no=_no查找员⼯编号emp_no为10001其⾃⼊职以来的薪⽔salary涨幅值growthselect(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查找所有员⼯⾃⼊职以来的薪⽔涨幅情况，给出员⼯编号emp_no以及其对应的薪⽔涨幅growth，并按照growth进⾏升序?select _no,()as growthfrom employees as ainner join

salaries as bon _no=_noand _date='9999-01-01'inner joinsalaries as con _no=_noand _date=_dateorder by growth统计各个部门对应员⼯涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum答案select _no,_name,count()as sumfrom dept_emp as dp,departments as d,salaries as swhere _no=_noand _no=_nogroup by _no⾃⼰写的(属实垃圾)?select _no,_name,m departments as dinner join

(select _no,count() as sumfrom dept_emp as dp,salaries as swhere _no=_nogroup by _no)as newon _no=_no对所有员⼯的当前(to_date=‘9999-01-01’)薪⽔按照salary进⾏按照1-N的排名，相同salary并列且按照emp_no升序排列⾃⼰写的，属实优秀??select emp_no,salary,dense_rank() over (order by salary desc)from salarieswhere to_date='9999-01-01'答案写的select _no,,count(distinct ) rankfrom salaries s1, salaries s2where <= and _date = '9999-01-01' and _date = '9999-01-01'group by _no order by rank;获取所有⾮manager员⼯当前的薪⽔情况，给出dept_no、emp_no以及salary ，当前表⽰to_date=‘9999-01-01’select _no,_no,from dept_emp as dp,salaries as swhere _no not in (select emp_no from dept_manager where to_date='9999-01-01')and _no = _noand _date='9999-01-01'and _date='9999-01-01'获取员⼯其当前的薪⽔⽐其manager当前薪⽔还⾼的相关信息，当前表⽰to_date=‘9999-01-01’,结果第⼀列给出员⼯的emp_no，第⼆列给出其manager的manager_no，第三列给出该员⼯当前的薪⽔emp_salary,第四列给该员⼯对应的manager当前的薪⽔manager_salaryselect _no,_no as manager_no, as emp_salary, as manager_salaryfrom

(select _no,_no,from dept_emp as dp,salaries as swhere _no=_noand _date='9999-01-01'and _no not in (select emp_no from dept_manager))as a,(select _no,_no,from dept_manager as dm,salaries as swhere _no=_noand _date='9999-01-01')as bwhere >and _no=_no本题思想，制作两张表，⼀个员⼯表，⼀个经理表汇总各个部门当前员⼯的title类型的分配select _no ,_name,,count()as countfrom titles as t inner join dept_emp as dpon _no=_noand _date='9999-01-01'and _date='9999-01-01'inner join departments as dmon _no=_nogroup by _no,⾃⼰写的select _no,_name,,count(_no)as countfrom departments as d,dept_emp as dp,titles as twhere _no=_noand _no=_noand _date='9999-01-01'and _date='9999-01-01'group by _no,给出每个员⼯每年薪⽔涨幅超过5000的信息select _no,_date,()as salary_growthfrom salaries as s1,salaries as s2where _no=_noand salary_growth>5000and (strftime('%Y',_date)-strftime('%Y',_date)=1or strftime('%Y',_date)-strftime('%Y',_date)=1)order by salary_growth desc查找描述信息中包含robot的电影对应的分类名称以及电影数⽬，⽽且还需要该分类的电影数⽬>=5部select ,count(_id)from (select category_id,count(film_id)as category_num from film_category group by category_id having count(film_id)>=5) as cc,film as f,category as c,film_category as fcwhere ption like'%robot%'and _id=_idand ry_id=ry_idand ry_id=ry_id使⽤join查询⽅式找出没有分类的电影id以及名称select _id,rom film as f left join film_category as fcon _id=_idwhere ry_id is null使⽤⼦查询的⽅式找出属于Action分类的所有电影对应的title,descriptionselect ,ptionfrom film as f,(select _id from film_category as fc,category as c where ry_id=ry_id and ='Action')as newwhere _id=_id这个是⾃⼰写的select title,descriptionfrom film

where _id in(select _id from film_category as fc,category as c where ry_id=ry_id

and ='Action')以下这两个都是⽹上的select title,description

from film

where _id in

(select film_id

from film_categorywhere category_id in (select category_id

from category

where name='Action'))select title,description

from film

where _id in

(select film_id

from film_categorywhere category_id in (select category_id

from category

where name='Action'))将employees表的所有员⼯的last_name和first_name拼接起来作为Name,中间以⼀个空格区分。这是sqlite的⽅法select last_name||" "|| first_namefrom employees

这是mysql的⽅法select concat(last_name,' ',first_name)from employees创建⼀个actor表，包含下列信息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')));对于表actor批量插⼊如下数据，如果数据已经存在，请忽略，不使⽤replace操作INSERT or ignore INTO ACTOR(actor_id,first_name,last_name,last_update)values( 3,'ED','CHASE','2006-02-15 12:34:33')创建⼀个actor_name表，将actor表中的所有first_name以及last_name导⼊该表。create table actor_name(first_name varchar(45) not null,last_name varchar(45) not null);insert into actor_nameselect first_name,last_namefrom actor;创建索引创建唯⼀索引create unique index 索引名 on 表名(某列名)创建普通索引create index 索引名 on 表名(某列名)create unique index uniq_idx_firstname on actor(first_name);create index idx_lastname on actor(last_name);创建视图我觉得视图就像是⼀个虚拟新表，后来执⾏查询时就可以在这个虚拟新表中进⾏。create view 视图名 asselect ...reate view actor_name_view as

select first_name as first_name_v,last_name as last_name_vfrom actor;使⽤强制索引mysqlselect|delete|update column1,column2,...forced index index_namewhere (condition);sqliteselect|delete|update column1,column2,...from table_nameindexed by index_namewhere (condition);增加新列alter table table_nameadd column column_name type ...,add column column_name type ...;alter table actor

add column create_date datetime not null default('0000 00:00:00')创建触发器create trigger audit_log after insert on employees

begininsert into audit values(,);end;删除某条记录delete from table_namewhere (condition)delete from titles_testwhere id not in (select min(id) from titles_test group by emp_no);更新数据update table_nameset更新要求1，更新要求2,...where(condition)update titles_testsetto_date=NULL,from_date='2001-01-01'where to_date='9999-01-01';replace⽤法update table_nameset column_name=replace(column_name,str1,str2)where(condition)把表的column_name列的str1替换为str2update titles_testset emp_no=replace(emp_no,10001,10005)where id =5;表名重命名?alter table table_namerename [to|as] new_table_name;或者（但是下⾯这种在⽜客⽹上不通过，在mysql上可以通过）rename table table_name to new_table_name;修改表名，to或as都可以，也可省略to或asalter table titles_testrename titles_2017;外键约束将所有获取奖⾦的员⼯当前的薪⽔增加10%。update salariesset salary=salary*1.1where emp_no in (select emp_no from emp_bonus)and to_date='9999-01-01'针对库中的所有表⽣成select count(*)对应的sql语句。这题答案是⽤sqlite写的select "select count(*) from "||name|| ';'as cntsfrom sqlite_master where type='table';1.在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，⽽且对于⾃⼰创建的表⽽⾔，字段 type永远是 ‘table’，详情可参考：2.在 SQLite 中⽤ “||” 符号连接字符串mysql写法：select concat("select count(*) from",' ',table_name,";")as cntsfrom (select table_name from information_) as new;这⾥要注意必须给from后⾯的这个表加⼀个别名，不然会出现every derived table must has its own alias的错误。将employees表中的所有员⼯的last_name和first_name通过（‘）连接起来。myql写法：select concat(last_name,"'",first_name)as namefrom employees;sqlite写法：select last_name||"'"||first_name as namefrom employees;字符，字节等1个汉字=1个字=1个字符⼀个字母=⼀个字符=1个字节1个字符=1个字节=8bit(ASCII码)1个字符=2个字节=16bit(Unicode码下)1个字符=3个字节（utf-8）length()计算的是字节长度char_length()计算的是字符长度查找字符串’10,A,B‘中逗号’,'出现的次数 lenth('10,A,B')-length(replace('10,A,B',',','')) as cnt;substr()函数substr(str,pos),从postion位置开始的往后的所有字符substr(str from pos),同上substr(str,pos,len),从position位置开始的往后的len个字符select first_namefrom employeesorder by substr(first_name,-2);或者：select first_namefrom employeesorder by substr(first_name,-2,2);group_concat()函数官⽅⽂档例⼦：按班级进⾏汇总，把同⼀班级的⽤逗号隔开。group_concat()默认是⽤逗号隔开的⽜客⽹题⽬：按照dept_no汇总，属于同⼀个部门的emp_no按照逗号进⾏连接，结果给出dept_no以及连接出的结果 dept_no,group_concat(emp_no)as employeesfrom dept_empgroup by dept_no;查找排除当前最⼤、最⼩salary之后的员⼯平均⼯资avg_ avg(salary) as avg_salaryfrom salarieswhere salary not in (select max(salary) from salaries)and salary not in (select min(salary) from salaries)and to_date='9999-01-01';分页查询select column_1,column_2 from table_name limit 偏移量,查询条数;分页查询employees,每5⾏⼀页，返回第2页的数据select * from employees limit 5,5;偏移量从0计数 ，每5⾏⼀页，那么第2页的数据是6-10⾏，所以limit5,5是从第6⾏起查询5条数据获取所有员⼯的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员⼯不显⽰select _no,_no,,edfrom employees as e inner join dept_emp as dp

on _no=_noleft join emp_bonus as ebon _no=_no;使⽤含有关键字exists查找未分配具体部门的员⼯的所有信息。select * from employees

where not exists (select emp_no from dept_emp where dept__no=_no);获取有奖⾦的员⼯相关信息。给出emp_no、first_name、last_name、奖⾦类型btype、对应的当前薪⽔情况salary以及奖⾦⾦额bonus。 bonus类型btype为1其奖⾦为薪⽔salary的10%，btype为2其奖⾦为薪⽔的20%，其他类型均为薪⽔的30%。 当前薪⽔表⽰to_date=‘9999-01-01’。考察case when⽤法。case column_namewhen column_name满⾜某条件 thenwhen···else ···end答案select _no,_name,_name,,,(case hen 1 then *0.1when 2 then *0.2else *0.3end)as bonusfrom emp_bonus as eb,employees as e,salaries as swhere _no=_noand _no=_noand _date='9999-01-01';我⾃⼰写的select _no,_name,_name,,,rom (select _no,,,(case hen 1 then *0.1when 2 then *0.2else *0.3end)as bonusfrom emp_bonus as eb inner join salaries as son _no = _noand _date='9999-01-01') as bbinner join employees as eon _no=_no;按照salary的累计和running_total，其中running_total为前两个员⼯的salary累计和，其他以此类推。select _no,,sum() as running_totalfrom salaries as a,salaries as bwhere _no>=_noand _date='9999-01-01'and _date='9999-01-01'group by _no;对于employees表中，给出奇数⾏的first_ first_namefrom employees as awhere (select count(*) from employees as b where _name>=_name)%2=1;⾃⼰写的（⽜客⽹上不通过，但是mysql上可以通过）select first_namefrom employees,(select @rownum:=0)as rwhere (@rownum:=@rownum+1)%2=1;