【数据库SQL实战】题目41~50答案全解析

本是古典何须时尚 2023-10-11 10:32 11阅读 0赞

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

思路：

SQLite **触发器（Trigger）**是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。以下是关于 SQLite 的触发器（Trigger）的要点：

*  SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
 *  SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
 *  WHEN 子句和触发器（Trigger）动作可能访问使用表单 **NEW.column-name** 和 **OLD.column-name** 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
 *  如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
 *  BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
 *  当触发器相关联的表删除时，自动删除触发器（Trigger）。
 *  要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 **tablename**，而不是 **database.tablename**。
 *  一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。

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

## 42. 删除emp\_no重复的记录，只保留最小的id对应的记录。 ##

删除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);
    
    insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
    ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
    ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
    ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
    ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
    ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
    ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

思路：

先用 GROUP BY 和 MIN() 选出每个 emp\_no 分组中最小的 id，然后用 DELETE FROM ... WHERE ... NOT IN ... 语句**删除 “非每个分组最小id对应的所有记录”**

delete from titles_test where id 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。

思路：

Update 语句用于修改表中的数据。语法是：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

update titles_test set to_date = null, from_date='2001-01-01' where to_date='9999-01-01';

## 44. 将id=5以及emp\_no=10001的行数据替换成id=5以及emp\_no=10005 ##

将id=5以及emp\_no=10001的行数据替换成id=5以及emp\_no=10005,其他数据保持不变，使用replace实现。

思路1：

全字段更新替换。

replace into titles_test values('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');

思路2：

运用REPLACE(X,Y,Z)函数。

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

## 45. 将titles\_test表名修改为titles\_2017 ##

将titles\_test表名修改为titles\_2017。

alter table titles_test rename to titles_2017;

## 46. 在audit表上创建外键约束，其emp\_no对应employees\_test表的主键id ##

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

## 47. 如何获取emp\_v和employees有相同的数据no ##

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

如何获取emp\_v和employees有相同的数据？

select em.* from employees em, emp_v ev where em.emp_no = ev.emp_no;

## 48. 将所有获取奖金的员工当前的薪水增加10% ##

将所有获取奖金的员工当前的薪水增加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 emp_no from emp_bonus);

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

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

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`));
    create table emp_bonus(
    emp_no int not null,
    recevied datetime not null,
    btype smallint not null);
    CREATE TABLE `dept_emp` (
    `emp_no` int(11) NOT NULL,
    `dept_no` char(4) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`));
    CREATE TABLE `dept_manager` (
    `dept_no` char(4) NOT NULL,
    `emp_no` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`));
    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`));

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

## 50. 将employees表中的所有员工的last\_name和first\_name通过(')连接起来。 ##

将employees表中的所有员工的last\_name和first\_name通过(')连接起来。

select last_name||"'"||first_name from employees;