牛客网数据库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有相同的数据，输出格式：

方法一：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语句，输出格式如下：

在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--