牛客数据库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语句

输出格式为：

这题的意思是要获取所有表的名字，然后输出上面的表格，一张表一行。
参考：牛客讨论区
在 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;

结尾

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