牛客网sql题详解41-50

最新推荐文章于 2022-05-25 22:58:59 发布

迪丽娜扎

最新推荐文章于 2022-05-25 22:58:59 发布

阅读量198

点赞数

本文链接：https://blog.csdn.net/weixin_44342332/article/details/106122194

版权

41.删除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);

insert into titles_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’);

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

42.将所有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);

/*要注意若干列 to_date = NULL 和 from_date = '2001-01-01' 之间只能用
逗号连接，切勿用 AND 连接。
set 字段名1=字段值1,字段名2=字段值2  ,用逗号","分隔,不是用空格分隔。*/

UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

43.将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 有两种解法
方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5冲突，故会替换掉表中id=5 的记录，否则会插入一条新记录（例如新插入的记录id =10）。并且要将所有字段的值写出，否则将置为空：*/

REPLACE INTO titles_test VALUES 
(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

/*方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过:*/

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

/* 另外可以利用OJ系统的漏洞，不用 REPLACE 实现*/
UPDATE titles_test SET emp_no = 10005 WHERE id = 5

44.将titles_test表名修改为titles_2017。
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);

/*两个方法在mysql控制台中都可以成功，而在牛客中只有第二个可以*/

RENAME TABLE titles_test to titles_2017;
 
ALTER TABLE titles_test RENAME TO titles_2017;

/*如果是mysql，可以加to，也可以不加。*/
alter table titles_test rename titles_2017

/*题目是sqlite3，必须要加to*/
alter table titles_test rename to titles_2017

45.在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
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,
create_date datetime NOT NULL
);

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

/*MySQL下，可以直接使用如下语句 修改：*/
alter table audit
add  foreign key(emp_no) references employees_test(id)

46.存在如下的视图：
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));
在这里插入图片描述

/*由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。

方法一：用 WHERE 选取二者 emp_no 相等的记录*/

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no

/*方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集*/

SELECT * FROM employees INTERSECT SELECT * FROM emp_v

/*方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录或employees中符合条件的记录*/

SELECT * FROM emp_v;

select * from employees where emp_no >10005;


/*【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定

SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no*/

47.将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied 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));

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

48.针对库中的所有表生成select count(*)对应的SQL语句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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 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));
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、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'。
2、在 SQLite 中用 “||” 符号连接字符串
sqlite写法(牛客网通过)：*/

select "select count(*) from "||name||";" as cnts
    from sqlite_master 
    where type='table';
    
/*mysql写法(牛客网不通过，但在mysql上运行通过)：*/

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

49.将employees表中的所有员工的last_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));
在这里插入图片描述

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

/*mysql中用concat()连接字符串*/
 select concat(last_name, "'", first_name) as name
 from employees;

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

/*由于 SQLite 中没有直接统计字符串中子串出现次数的
函数，因此本题用length()函数与replace()函数的结
合灵活地解决了统计子串出现次数的问题，属于技巧题，
即先用replace函数将原串中出现的子串用空串替换，再
用原串长度减去替换后字符串的长度，最后除以子串的长
度（本题中此步可省略，若子串长度大于1则不可省）。*/
SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

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