牛客网数据库SQL实战详细剖析(41-50)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 牛客网数据库SQL实战详细剖析(41-50)

这是一个系列文章,总共61题,分6期,有答案以及解题思路,并附上解题的一个思考过程。


具体题目可参考牛客网的SQL实战模块:https://www.nowcoder.com/ta/sql?page=0



一、牛客网数据库SQL实战详细剖析(1-10)二、牛客网数据库SQL实战详细剖析(11-20)三、牛客网数据库SQL实战详细剖析(21-30)四、牛客网数据库SQL实战详细剖析(31-40)



第四十一题:构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit表中


CREATE TRIGGER audit_log AFTER insertON employees_test FOR EACH ROWBEGININSERT INTO audit VALUES(NEW.ID, NEW.NAME);END;解题思路:创建触发器CREATE TRIGGER <触发器名称> <触发时机> <触发事件>ON <表名> FOR EACH ROW <触发后执行的语句>; trigger_name 触发器名称 自定义trigger_time 触发时机 可以选 BEFORE或者AFTERtrigger_event 触发事件 INSERT,UPDATE,DELETEtrigger_stmt 触发之后需要执行的语句,可以使用BEGIN,END开始和结束 关于NEW.ID, NEW.NAME的解释:audit表里只有emp_no和name两列,所以只能插入id和name这两列,我们可以使用 NEW与OLD 关键字访问触发后(或触发前)的employees_test表单记录


 第四十二题:删除emp_no重复的记录,只保留最小的id对应的记录DELETE FROM titles_testWHERE id NOT IN (SELECT * FROM(SELECT min(id) FROM titles_test GROUP BY emp_no ) AS a);解题思路:


①使用子查询的方法,将emp_no进行分组后找出最小id对应的记录,再使用not in,将非最小id的记录删除。


②高赞答案都是只用一次子查询,但在MySQL中,UPDATE或DELETE中子查询不能为同一张表,所以在这里要嵌套一个子查询,另外,在MySQL中需要给子查询添加别名,否则会出错。 第四十三题:将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01update titles_testset from_date = '2001-01-01',to_date = nullwhere to_date = '9999-01-01';解题思路:


①表更新使用update语句,多个更新之间用逗号隔开,而不能使用and连接。②这里两个更新分别考察了简单update语句和搜索型update语句。③一个比较容易出错的地方是某列更新为null时不能使用<列名> is null的方法,正确的方法是:update <表名> set <字段> = null where <条件>;


 第四十四题:将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现方法一:update titles_testset emp_no = replace(emp_no,10001,10005)where id = 5;方法二:replace into titles_test values(5,10005, 'Senior Engineer', '1986-06-26', '9999-01-01');解题思路


①第一反应是:replace(要替换的值,替换前的数据,替换后的数据),由此得出方法一,但是这个语句在Mysql中不能实现。②本题说用replace实现,在Mysql中,应该是考察replace into语句。replace into <表名> (<列名>) values(值1,值2…) (列名可以省略)③这题要把所有列的值都列出来的原因是:如果在replace语句中没有指定某列, 在replace之后这列的值会被置空 。


参考

https://blog.csdn.net/zhangjg_blog/article/details/23267761


replace into 跟 insert (update同理)功能类似,不同点在于:replace into 首先尝试插入数据到表中,


1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。


2. 否则,直接插入新数据。要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。MySQL replace into 有三种形式:1. replace <表名>(<列名>) values(...)2. replace <表名>(<列名>) select ...3. replace <表名> set <列名>=value, ...前两种形式用的多些。其中 “into” 关键字可以省略,不过最好加上 “into”,这样意思更加直观。


参考:https://blog.csdn.net/leyangjun/article/details/38734625 


第四十五题:将titles_test表名修改为titles_2017rename table titles_test to titles_2017;解题思路:变更表名:rename table <变更前的名称> to <变更后的名称>;


 第四十六题:在audit表上创建外键约束,其emp_no对应employees_test表的主键id


alter table audit add foreign key(emp_no) references employees_test(id);
解题思路:创建外键约束语法:alter table <子表的数据表名> add foreign key(子表的外键字段) references <父表的数据表名称>(父表的主键名称); 


第四十七题:存在如下的视图:create view emp_v as select * from employees where emp_no >10005;如何获取emp_v和employees有相同的数据,输出格式:



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
10008 1958-02-19 Saniya Kalloufi M 1994-09-15
10009 1952-04-19 Sumant Peac F 1985-02-18
10010 1963-06-01 Duangkaew Piveteau F 1989-08-24
10011 1953-11-07 Mary Sluis F 1990-01-22


方法一:select * from emp_v;


方法二:select e.* from employees e, emp_v ev where e.emp_no = ev.emp_no;


解题思路:视图和表的区别在于“是否保存了实际的数据” ,在编写select语句时,不需要特别在意表和视图有什么不同,可以直接使用视图作为表进行查询 。 


第四十八题:将所有获取奖金的员工的当前薪水增加10%


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


解题思路:使用update更新表工资,限定条件为当前和emp_bonus表中所有的员工(使用in谓词解决),另外本题的emp_bonus表的用例数据可以在倒数第三题的用例中获取。 


第四十九题:针对库中的所有表生成select count(*)对应的SQL语句,输出格式如下:

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


Mysql中,针对库中的所有表生成select count(*)对应的SQL语句:select concat("select count(*) from ",table_name,";") as cntsfrom


information_schema.tables where table_schema='niuke'; (niuke是我做牛客sql题专门建立的一个数据库) Mysql中,针对所有数据库中的所有表生成select count(*)对应的SQL语句:select concat("select count(*) from ",table_name,";") as cntsfrom (select table_name from information_schema.tables) as new; 参考:


https://blog.csdn.net/yf0523/article/details/81116132


MySQL中获得所有表的名称:select table namefrom information schema.tables where table schema='mydata';MySQL语句中,‘mydata’是当前数据库的名称的意思information_schema.tables表示从表名中选择,information_schema.xxx中xxx可选的还有很多字段,如information_schema.columns表示从所有表的所有字段中选择。MySQL字符串的连接使用concat函数,多个字符串连接中间用逗号隔开。另外,结果中from和表名之间是有一个空格的,所以在字符串"select count(*) from "的from后要加一个空格。


 第五十题:将employees表中的所有员工的last_name和first_name通过(')连接起来。


select concat(last_name,"'",first_name) as namefrom employees;解题思路:考察字符串拼接函数concat(<列1>,<列2>…),多个列或字符串之间用个逗号隔开,这里last_name和first_name通过(')连接,符号 ' 要用双引号括起来。


--end--


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
1月前
|
SQL 机器学习/深度学习 人工智能
从“写SQL”到“聊数据”:NL2SQL如何用自然语言解锁数据库?
本文系统性地阐述了自然语言转SQL(NL2SQL) 技术如何让非技术背景的业务分析师实现数据自助查询,从而提升数据驱动决策的效率与准确性。
从“写SQL”到“聊数据”:NL2SQL如何用自然语言解锁数据库?
|
3天前
|
SQL 人工智能 Linux
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
62 5
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
|
2天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
3天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
20天前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构,并且通过合理使用可以显著改善查询速度及整体性能。需要注意,在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
77 8
|
25天前
|
SQL 人工智能 Java
用 LangChain4j+Ollama 打造 Text-to-SQL AI Agent,数据库想问就问
本文介绍了如何利用AI技术简化SQL查询操作,让不懂技术的用户也能轻松从数据库中获取信息。通过本地部署PostgreSQL数据库和Ollama模型,结合Java代码,实现将自然语言问题自动转换为SQL查询,并将结果以易懂的方式呈现。整个流程简单直观,适合初学者动手实践,同时也展示了AI在数据查询中的潜力与局限。
168 8
|
1月前
|
SQL 人工智能 Linux
SQL Server 2025 RC0 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC0 发布 - 从本地到云端的 AI 就绪企业数据库
111 5
|
8天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
2月前
|
SQL 缓存 监控
SqlRest让SQL秒变Http API,还支持20+数据库(含国产数据库)
杭州奥零数据科技有限公司成立于2023年,专注于数据中台业务,维护开源项目AllData并提供商业版解决方案。AllData提供数据集成、存储、开发、治理及BI展示等一站式服务,支持AI大模型应用,助力企业高效利用数据价值。
|
23天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。