mysql牛客网训练41-60_下

最新推荐文章于 2024-04-18 20:59:41 发布

落叶无痕00

最新推荐文章于 2024-04-18 20:59:41 发布

阅读量515

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_37638061/article/details/102720836

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章目录

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

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

答案

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

REPLACE(s,s1,s2)

使用字符串 s2 替换字符串 s 中的所有的字符串 s1

SELECT REPLACE('aaa.mysql.com','a','w');
结果：www.mysql.com

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

答案

ALTER TABLE titles_test RENAME TO titles_2017

43.在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);

答案

仅针对于MySql：
ALTER TABLE audit
	ADD FOREIGN KEY(emp_no)
    REFERENCES employees_test(id);

44. 获取emp_v和employees有相同的数据

存在如下的视图：
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_no	birth_date	first_name	last_name	gender	hire_date
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10

答案

1. 交集（不适用于MySQL，MySQL中不存在交集关键字）
SELECT * FROM employees
INTERSECT
SELECT * FROM emp_v;

2. WHERE 取 emp_no 相等的记录
SELECT em.* 
	FROM employees em,emp_v ev
	WHERE em.emp_no = ev.emp_no;
	
3. 既然 emp_v 就是从 employees 中取出来的，直接输出 emp_v 不就OK了
SELECT * FROM emp_v;

UNION / UNION ALL

表 emp1 数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AU80IMik-1571891889097)(images/1569081998068.png)]

表 emp2 数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-USNEaucB-1571891889100)(images/1569082033854.png)]

emp2 的前3条数据是从 emp1 中复制过来的数据，第四条数据是随便插的
UNION

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EHTPqibo-1571891889103)(images/1569082225332.png)]

UNION ALL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DK0i8suu-1571891889106)(images/1569082254275.png)]

总结：UNION 只选取不重复的，UNION ALL会选取全部的

45. 将所有获取奖金的员工当前的薪水增加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`));

答案

解析：1. 首先从emp_bonus中获取获奖的员工emp_no，
	 2. 然后从salaries中对应的emp_no使其salary增加10%
	 
UPDATE salaries
	SET salary = salary * 1.1
	WHERE emp_no IN(
    SELECT s.emp_no FROM salaries s
        INNER JOIN emp_bonus e
        ON s.emp_no = e.emp_no
        AND s.to_date = '9999-01-01'
    )

46. 针对库中的所有表生成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);

答案

牛客网的数据库系统是SQLite
SELECT "select count(*) from " || name || ";" as cnts
	FROM sqlite_master 
	WHERE type = 'table'
	
下面是针对于MySQL的（MySQL连接字符串需要使用 concant() 的函数）
	SELECT CONCAT("select count(*) from ",table_name,";") as cnts
	FROM (SELECT table_name from information_schema.tables where table_schema = 'test')
	AS test
	
必须要有 AS xxx，随便指定一个别名就行，否则会报错Every derived table must have its own alias
通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生表指定一个别名。”引自《数据库系统概论》3.4.5节

information_schema

MySQL元数据库——information_schema

47. 将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 FROM employees

mysql:
SELECT CONCAT(last_name,"'",first_name) FROM employees

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

答案

仅适用于 SQLite

先使用replace将原串中的子串","使用空串""替代
再用原串长度减去替换后的字符串的长度
最后除以子串的长度（本题可省略，子串长度大于1，则不可以省略）
SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

49. 获取Employees中的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`));

答案

SQLite
SELECT first_name 
    FROM employees 
    ORDER BY SUBSTR(first_name,-2);
    
mysql:
SELECT first_name 
    FROM employees 
    ORDER BY SUBSTRING(first_name,-2);

思路、函数作用都一样，只不过函数名字不太一样

SUBSTRING(s, start, length)

从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 RUNOOB 中第2个位置开始向后截取3个字符，（第一个字符的编号是1）
SELECT SUBSTRING("RUNOOB", 2, 3); -- UNO

SUBSTRING(s, length)
length为正数，返回左数length到末尾
SELECT SUBSTRING("RUNOOB", 2); -- UNOOB
length为负数，返回右数length绝对值到末尾
SELECT SUBSTRING("RUNOOB", 2); -- OB

50. 按照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`));

答案

SQLite:
SELECT dept_no,GROUP_CONCAT(emp_no) employees
    FROM dept_emp 
    GROUP BY dept_no
    
MySQL:
select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;

group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’])

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
separator 为分隔符，缺省（默认）是一个逗号","
参考文章：Group_concat介绍与例子

51. 查找排除当前最大、最小salary之后的员工的平均工资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`));

答案

使用 avg()函数就OK了，注意排除最大和最小salary就好
SELECT AVG(salary) FROM salary
	WHERE to_date = '9999-01-01'
	AND salary NOT IN(SELECT MAX(salary) FROM salary WHERE to_date = '9999-01-01')
	AND salary NOT IN(SELECT MIN(salary) FROM salary WHERE to_date = '9999-01-01')
	
这个在牛客网的判别系统中是通过不了的，去掉查询最大、最小salary的where语句才能通过，但这个是系统的错误。

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

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
    LIMIT 5,5

LIMIT M,N

返回第m条数据后的n条数据
栗子：limit 5,5：返回的是第6,7,8,9,10，这五条数据

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

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

答案

SELECT e.emp_no,de.dept_no,eb.btype,eb.recevied
    FROM employees e
    INNER JOIN dept_emp de ON e.emp_no = de.emp_no
    LEFT JOIN emp_bonus eb ON e.emp_no = eb.emp_no
    
   有点懵

54. 使用含有关键字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`));

答案

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

使用 NOT IN
SELECT * FROM employees WHERE emp_no NOT IN 
    (SELECT emp_no FROM dept_emp)

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

存在如下的视图：
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`));

答案

SELECT * FROM employees WHERE EMP_NO IN (SELECT emp_no FROM emp_v)

SELECT * FROM emp_v

56. 获取有奖金的员工相关信息。

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

给出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) bonus
	FROM employees e,emp_bonus eb,salaries s
	ON e.emp_no = s.emp_no
	AND e.emp_no = eb.emp_no
	AND s.to_date = '9999-01-01'

57. 对于employees表中，给出奇数行的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 e1.first_name
from employees e1
where (select count(*)
      from employees e2 
      where e1.first_name > e2.first_name )%2 ==0;
有点懵

落叶无痕00

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql牛客网训练41-60_下

文章目录41. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。REPLACE(s,s1,s2)42. 将titles_test表名修改为titles_2017。43.在audit表上创建外键约束，其emp_no对应employees_test表的主键id。44. 获取emp_v和employees有相同的数据UN...
复制链接

扫一扫