数据库SQL实战｜SQL答案集合及解析(41-50)

牛客数据库SQL实战题(41-50题)

41、构造一个触发器audit_log

构造一个触发器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
);

答案

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

参考：牛客讨论区
构造触发器时注意以下几点：
1）用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER。
2）触发器执行的内容写出 BEGIN与END 之间。
3）可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录。

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

答案

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

43、将所有to_date为9999-01-01的全部更新为NULL

将所有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'

update后面不用加table关键字，修改的两列用逗号链接，而不是and。

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

答案

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

运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。

45、将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

mysql中不用写to

46、在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));

这一题判断代码的程序有问题，中间的字段前面必须4个空格，并且最后的);提头写到新行会判错。
再就是SQLite中不能通过 ALTER TABLE ... ADD FOREIGN KEY ... REFERENCES ... 语句来对已创建好的字段创建外键，因此只能先删除表，再重新建表的过程中创建外键。
mysql使用ALTER添加外键的语句表达式为：ALTER TABLE tablename ADD FOREIGN KEY...REFERENCES...。而在这里不能使用alter来添加外键，因此就只能先删除表，然后再建立该表，在表中直接进行外键约束。
alter table my_tab1 add [constraint 外键名] foreign key(外键字段名) references mytab2(主键字段名);

47、如何获取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_v本来就是从employees导出的视图，要获取他们相同的数据，直接select * from emp_v就好。
用where：

select v.*
from emp_v as v, employees as em
where v.emp_no=em.emp_no

select v.*或者select em.*都可以，但是不能直接select *，否则会得到两张表中符合条件的重复记录。
用intersect求交集的方法：

select *
from emp_v
intersect
select *
from employees

两表位置可以调换。

48、将所有获取奖金的员工当前的薪水增加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 sa.emp_no
                 from salaries as sa 
                 inner join emp_bonus as bo
                 on sa.emp_no=bo.emp_no 
                 where sa.to_date='9999-01-01')

49、针对库中的所有表生成select count(*)对应的SQL语句

输出格式为：

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

这题的意思是要获取所有表的名字，然后输出上面的表格，一张表一行。
参考：牛客讨论区
在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'。
然后在 SQLite 中用 “||” 符号连接字符串。

答案

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

在mysql中获取所有表名的语句为：

select table_name from information_schema.tables where table_schema='shop';

其中shop为数据库名字。
mysql中合并字段用concat()
所以mysql版本答案为：

create table hi as 
select table_name from information_schema.tables where table_schema='shop';

select * from hi;

select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts from hi;

或将上述两个步骤合并为一条语句为：

select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts
 from (select table_name from information_schema.tables where table_schema='shop') as hi;

50、将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;

结尾

如果您发现我的文章有任何错误，或对我的文章有什么好的建议，请联系我！如果您喜欢我的文章，请点喜欢~*我是蓝白绛，感谢你的阅读！

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,214评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,307评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,543评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,221评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,224评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,007评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,313评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,956评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,441评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,925评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,018评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,685评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,234评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,240评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,464评论 1赞 261
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,467评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,762评论 2赞 345

推荐阅读更多精彩内容

  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,698评论 0赞 44
  • 数据库精华备份
    http://www.itpub.net/thread-1393352-1-1.htmlhi,看到你关于数据库存储...
    yahzon阅读 580评论 1赞 0
  • 《数据库SQL实战》全部答案
    最近正好有点时间，就整理了一下牛客网上的数据库SQL实战中的题，里面的每一道题我都亲自编写并运行了一下，有的题可能...
    战神_白起阅读 4,977评论 1赞 8
  • 在落叶
    窗外的枫树在落叶 我看的见，摸不着 一叶绿，一叶黄 一半在悠闲，一半在仿徨 一片叶刚落地 小鸟飞到树枝上 窗外的孤...
    Sans_阅读 328评论 0赞 4
  • 一部8.9分的纪录片《象牙游戏》
    他是一名中国留学生，毕业后做了一个卧底。 看过《动物世界》的人，大概都对一段台词耳熟能详：“春天来了，万物复苏，广...
    TD_Bolter阅读 4,165评论 0赞 1