欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

牛客SQL实战刷题记录(31-61题)

程序员文章站 2022-09-14 14:19:05
31.获取select * from employees对应的执行计划explain select * from employees;32.将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分(注:该数据库系统是sqllite,字符串拼接为 || 符号,不支持concat函数)CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL,`birth_date` date NOT N...

37.创建索引 create [unique] index 索引名 on 表名/视图名(列名);

38.创建视图语句 create view 视图名 as ...

39.强制索引(强制把哪列作为索引) select * from xxx force index 列名 from 表名 

                                                            select * from xxx force index(列1,列2) from 表名

40.向表中添加一列        alter table 表名 add [column] 列名 数据类型 [not null] [default ] ...;

41.创建触发器  CREATE TRIGGER 触发器名 BEFORE/AFTER [INSERT/UPDATE/ADD] ON 表 BEGIN 执行语句 END;

42.删除 delete from xxx where xxx

43.更新 update 表名 set 列1=值1,列2=值2

44.replace 替换   replace(字段,“需要替换的值”,“替换后的值”)

45.重命名表名 MySQL 和 SqlLite都支持的用法:alter table tname_old rename to tname_new

注,在MySQL中,可以这样使用rename:

reanme table tname_old to tname_new

46. 添加外键 alter table 表名 add foreign key (列) references 外表(列);

47.求两表的交集 select * from employees intersect select * from emp_v;

49.concat用法 concat(xx1,xx2,xx3) 连接字符串

    concat_ws用法 concat_ws(',',列1,列2,列3) 用第一个字符连接后面的列

52.substr用法  substr(列,起始位置,个数) substr(列,起始位置)  注意:第一个索引为1 ,倒数第二个开始可以写-2

53.group_concat(列) 将列用,相连 常与group by相连。

55.limit 用法 limit x,y 从第x条开始,寻y条数据。第一条数据为0 limit x offset y 寻x条数据,从第y条开始。

59.条件筛选 case 条件 when 值1 then xxx when 值2 then xxx else xxx end

60.蛮难

61.蛮难


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

explain select * from employees;

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

(注:该数据库系统是sqllite,字符串拼接为 || 符号,不支持concat函数)

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 last_name||" "||first_name name from employees

33.创建一个actor表,包含如下列信息(注:sqlite获取系统默认时间是datetime('now','localtime'))

列表 类型 是否为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 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'))
)

34.对于表actor批量插入如下数据(不能有2条insert语句哦!)
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')))

actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33

方法一:利用VALUES(value1, value2, ...), (value1, value2, ...), ...(value1, value2, ...),

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

方法二:利用 UNION SELECT 批量插入

INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'

35.对于表actor批量插入如下数据,如果数据已经存在,请忽略(不支持使用replace操作)
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')))

actor_id first_name last_name last_update
'3' 'ED' 'CHASE' '2006-02-15 12:34:33'


因为题目判定系统使用的是sqlite3,所以必须按sqlite3的写法来做,

1

2

insert or ignore into actor

values(3,'ED','CHASE','2006-02-15 12:34:33');

如果是mysql,那么把or去掉,像下面这样:

1

2

insert IGNORE into actor

values(3,'ED','CHASE','2006-02-15 12:34:33');

 

36.

对于如下表actor,其对应的数据为:

actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33

请你创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表.

actor_name表结构如下:

列表 类型 是否为NULL 含义
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏

题目使用的是sqlite3,可以这么做:

1

2

create table actor_name as

select first_name,last_name from actor;

如果是mysql,那么as可以去掉,也可以不去掉,例如:

1

2

create table actor_name

select first_name,last_name from actor;

 

37.

针对如下表actor结构创建索引:

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)

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

对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname

(请先创建唯一索引,再创建普通索引)

给指定表或者视图的某列添加索引使用语句:CREATE [UNIQUE/...] INDEX indexName ON tableName(colName);

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

 

38.针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
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 VIEW ... AS ... 的 AS 是创建视图语法中的一部分,而后面的两个 AS 只是为字段创建别名

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

39.强制索引

针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
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`));
create index idx_emp_no on salaries(emp_no);

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

http://www.runoob.com/sqlite/sqlite-indexed-by.html

1

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

MySQL中,使用 FORCE INDEX 语句进行强制索引查询,可参考:

http://www.jb51.net/article/49807.htm

1

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

 

40. 存在actor表,包含如下列信息:
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')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为'0000-00-00 00:00:00'

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

 

41.构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

触发器写法:

1.创建触发器使用语句:CREATE TRIGGER trigname;

2.指定触发器触发的事件在执行某操作之前还是之后,使用语句:BEFORE/AFTER [INSERT/UPDATE/ADD] ON tablename

3.触发器触发的事件写在BEGIN和END之间;

4.触发器中可以通过NEW获得触发事件之后2对应的tablename的相关列的值,OLD获得触发事件之前的2对应的tablename的相关列的值

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

 

42.删除emp_no重复的记录,只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

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

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

 43.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

更新

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

44.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

replace(字段,“需要替换的值”,“替换后的值”)

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

 

45.将titles_test表名修改为titles_2017。

//sqlite3
alter table titles_test rename to titles_2017;

//mysql 可以去掉to
alter table titles_test rename titles_2017;

//只有mysql可以的写法
rename table titles_test to titles_2017;

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

mysql

ALTER TABLE audit ADD FOREIGN KEY (emp_no) REFERENCES employees_test(id);

sqlite中必须删表,再建表

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

47.存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?
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 * from employees where emp_no >10005完成吗,挑战一下自己对视图的理解吧)

SELECT * FROM employees INTERSECT SELECT * FROM emp_v

 

48.请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

create table emp_bonus(
emp_no int not null,
btype smallint not null);
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`));

update salaries set salary=salary*1.1 where to_date='9999-01-01' and emp_no in
(select emp_no from emp_bonus);

 

49.针对库中的所有表生成select count(*)对应的SQL语句,如数据库里有以下表,

(注:在 SQLite 中用 “||” 符号连接字符串,无法使用concat函数)

employees

departments

dept_emp

dept_manage

那么就会输出以下的样子:

cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;

MYSQL中写法

select concat("select count(*) from "," ",table_name,";") as cnts
from (select table_name from information_schema.tables) as new;

SQLite中写法

在 SQLite 系统表 sqlite_master 中可以获得所有表的索引,其中字段 name 是所有表的名字,而且对于自己创建的表而言,字段 type 永远是 'table',详情可参考:

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

50.将employees表中的所有员工的last_name和first_name通过(')连接起来。(不支持concat,请用||实现)
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`));
输出格式:

name
Facello'Georgi
Simmel'Bezalel
Bamford'Parto

MYSQL中用法

select concat(last_name,"‘",first_name) as name from employees;

SQLite中用法

SELECT last_name || "'" || first_name FROM employees

 

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

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

 

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

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

 

53.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
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`));
输出格式:

dept_no employees
d001 10001,10002
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no

 

54.查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。
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`));

不是最大或最小

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

在最大和最小之间

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

55.分页查询employees表,每5行一页,返回第2页的数据

根据题意,每行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 employees LIMIT 5,5

 

56.获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received,没有分配奖金的员工不显示对应的bonus类型btype和received

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

CREATE TABLE `emp_bonus`(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);

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 de.emp_no,de.dept_no,eb.btype,eb.received from dept_emp de left join emp_bonus eb using(emp_no);

 

57.使用含有关键字exists查找未分配具体部门的员工的所有信息。
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 `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`));

使用exists关键字

SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)

不使用exists关键字

SELECT e.* FROM employees AS e LEFT JOIN dept_emp AS de
ON e.emp_no=de.emp_no WHERE de.emp_no IS NULL;
SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)

58.存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
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`));

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

(你能不用select * from employees where emp_no >10005 这条语句完成吗,挑战一下自己对视图的理解)

SELECT * FROM emp_v;

59.获取有奖金的员工相关信息。
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 `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`));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
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`));
给出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 employees e join emp_bonus b using(emp_no) 
    join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01'

60.按照salary的累计和running_total,其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和,其他以此类推。 具体结果如下Demo展示。。
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`));
输出格式:

emp_no salary running_total
10001 88958 88958
10002 72527 161485
10003 43311 204796
10004 74057 278853
select s1.emp_no,s1.salary,
(select sum(s2.salary) from salaries s2 
 where s2.to_date='9999-01-01' and s1.emp_no>=s2.emp_no) as running_total
from salaries s1 where s1.to_date='9999-01-01';

61.对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
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(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

输出格式:

first_name
Georgi
Anneke

因为Georgi按first_name排名为3,Anneke按first_name排名为1,所以会输出这2个

SELECT e1.first_name FROM employees e1
WHERE
(SELECT count(*) FROM employees e2 WHERE e1.first_name >=e2.first_name)%2=1;

 

本文地址:https://blog.csdn.net/qq_33836092/article/details/107142168