牛客數(shù)據(jù)庫SQL實戰(zhàn)題(41-50題)
41帝蒿、構造一個觸發(fā)器audit_log
構造一個觸發(fā)器audit_log荐糜，在向employees_test表中插入一條數(shù)據(jù)的時候，觸發(fā)插入相關的數(shù)據(jù)到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;
參考：疟┦希客討論區(qū)
構造觸發(fā)器時注意以下幾點：
1）用 CREATE TRIGGER 語句構造觸發(fā)器延塑，用 BEFORE或AFTER 來指定在執(zhí)行后面的SQL語句之前或之后來觸發(fā)TRIGGER。
2）觸發(fā)器執(zhí)行的內容寫出 BEGIN與END 之間答渔。
3）可以使用 NEW與OLD 關鍵字訪問觸發(fā)后或觸發(fā)前的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的行數(shù)據(jù)替換成id=5以及emp_no=10005
其他數(shù)據(jù)保持不變，使用replace實現(xiàn)奔浅。
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)函數(shù)馆纳。其中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表上創(chuàng)建外鍵約束壹罚，其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 ... 語句來對已創(chuàng)建好的字段創(chuàng)建外鍵赂蠢，因此只能先刪除表，再重新建表的過程中創(chuàng)建外鍵辨泳。
mysql使用ALTER添加外鍵的語句表達式為：ALTER TABLE tablename ADD FOREIGN KEY...REFERENCES...虱岂。而在這里不能使用alter來添加外鍵，因此就只能先刪除表菠红，然后再建立該表第岖，在表中直接進行外鍵約束。
alter table my_tab1 add [constraint 外鍵名] foreign key(外鍵字段名) references mytab2(主鍵字段名);
47试溯、如何獲取emp_v和employees有相同的數(shù)據(jù)
存在如下的視圖：
create view emp_v as select * from employees where emp_no >10005;
如何獲取emp_v和employees有相同的數(shù)據(jù)蔑滓？
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導出的視圖，要獲取他們相同的數(shù)據(jù)键袱，直接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; |
這題的意思是要獲取所有表的名字，然后輸出上面的表格银亲，一張表一行慢叨。
參考：牛客討論區(qū)
在 SQLite 系統(tǒng)表 sqlite_master 中可以獲得所有表的索引务蝠，其中字段 name 是所有表的名字拍谐，而且對于自己創(chuàng)建的表而言，字段 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為數(shù)據(jù)庫名字。
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;
結尾
如果您發(fā)現(xiàn)我的文章有任何錯誤亡蓉，或對我的文章有什么好的建議，請聯(lián)系我喷舀！如果您喜歡我的文章砍濒，請點喜歡~*我是藍白絳，感謝你的閱讀硫麻！
