数据库操作练习4

1. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

题目描述

使用含有关键字exists查找未分配具体部门的员工的所有信息。
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 `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`));
输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22

这里主要就是设计了exists的语法格式（从网上找了一下这个的格式，如下）：

EXISTS语句不关心子查询的具体内容，因此用“SELECT *”，“Exists + 子查询”用来判断该子查询是否返回记录。
Exists：若子查询的结果集非空时，返回“True”；若子查询的结果集为空时，返回“False” 。
NOT EXISTS ：若子查询结果为空，返回“TRUE”值；若子查询的结果集非空时，返回 “FALSE。
查询没有选C1课程的学生的学号、姓名
SELECT 姓名
FROM 学生表
WHERE NOT EXISTS
(
    SELECT *
    FROM 选课表
    WHERE 学生表.学号 = 选课表.学号 AND 课程号 = 'C1'
);

本题中：

        用 EXISTS 关键字的方法如下：意为在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录，即当 employees.emp_no=10011时。反之，把NOT去掉，则输出 employees.emp_no=10001~10010时的记录。

select * from employees em where not exists (select emp_no from dept_emp where emp_no=em.emp_no)

 

2. 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

题目描述

对于如下表actor，其对应的数据为:

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

1.用create table 语句建立actor_name 表
2.用inset into actor select插入子查询的结果集(不需要用values()，()这种形式。这种形式是手工插入单条数据或多条数据时用圆括号分割。插入结果集是不需要）

 

create table actor_name(
	first_name varchar(45) not null,
    last_name varchar(45) not null
);

insert into actor_name select first_name,last_name from actor

 

3. 批量插入数据

题目描述

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

本题考查批量插入数据库的操作（当然去掉(actor_id,first_name,last_name,last_update) 也是可以的）：

 

insert into actor (actor_id,first_name,last_name,last_update)  values 
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK' ,'WAHLBERG','2006-02-15 12:34:33')

下面这个参考牛客网网友:

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'

4. 针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005,

题目描述

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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`));
create index idx_emp_no on salaries(emp_no);

SQLite中，使用 INDEXED BY 语句进行强制索引查询:

select * from salaries indexed by idx_emp_no where emp_no=10005

MySQL中，使用 FORCE INDEX 语句进行强制索引查询：

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

5. 构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中

题目描述

构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到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

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

创建 触发器（Trigger） 的基本语法如下：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;[BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

6. 使用join查询方式找出没有分类的电影id以及名称

题目描述

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

CREATE TABLE IF NOT EXISTS film (

film_id smallint(5)  NOT NULL DEFAULT '0',

title varchar(255) NOT NULL,

description text,

PRIMARY KEY (film_id));

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category  (

category_id  tinyint(3)  NOT NULL ,

name  varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (

film_id  smallint(5)  NOT NULL,

category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

使用join查询方式找出没有分类的电影id以及名称

SQL LEFT JOIN 关键字

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

由此sql1：

 

SELECT f.film_id,f.title from film f left join film_category fc 
on f.film_id=fc.film_id where fc.category_id is null

sql2:inner join

 

先用film_id相等条件连接两个表，然后再在film表中找出film_id不在连接的表中的部分

 

SELECT f.film_id,f.title from film f where f.film_id not in(select f.film_id from film f inner join film_category 
                                                          fc where f.film_id=fc.film_id)

sql3:

 

 

select f.film_id,f.title
from film as f left join film_category as fm
on f.film_id = fm.film_id
where f.film_id not in (select film_id from film_category);

 

sql4:

 

select bb.film_id,bb.title
from (film left join film_category on film.film_id = film_category.film_id) as bb
where bb.category_id is null

 

7. 统计salary的累计和running_total

题目描述

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
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`));
输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

本题的思路为复用 salaries表进行子查询，最后输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = '9999-01-01'

select s1.emp_no,s1.salary,
(
	SELECT SUM(s2.salary) from salaries as s2 where s1.emp_no>=s2.emp_no and s2.to_date='9999-01-01'
)as running_total
from salaries as s1 where s1.to_date='9999-01-01'

8.获取有奖金的员工相关信息。

题目描述

获取有奖金的员工相关信息。
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 `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 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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:

emp_no	first_name	last_name	btype	salary	bonus
10001	Georgi	Facello	1	88958	8895.8
10002	Bezalel	Simmel	2	72527	14505.4
10003	Parto	Bamford	3	43311	12993.3
10004	Chirstian	Koblick	1	74057	7405.7

本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。

 

条件表达式：
该表达式的语法规则如下：
1). CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
2). CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

 

select eb.emp_no, first_name, last_name, btype, salary,
(case eb.btype
when 1 then salary*0.1
when 2 then salary*0.2
else salary*0.3
end) as bonus
from emp_bonus eb,employees e ,salaries s where
eb.emp_no = e.emp_no and e.emp_no=s.emp_no and s.to_date='9999-01-01' ;

sql2:

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

sql3:

其实观察测试数据会发现 btype 只有1,2,3三种情况，即使不会 CASE 表达式，也能运用四则运算解出：（注意要除以10.0间接转换为double类型，如果除以10的话，得到的是int类型。结果的小数位会被舍去）

 

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

 

在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。

以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;[BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

CREATE TRIGGER audit_log AFTER INSERT 
ON employees_test
BEGIN
	INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;

9. 查找所有已经分配部门的员工的last_name和first_name

题目描述

查找所有已经分配部门的员工的last_name和first_name
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 `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`));

在SQL中：

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

SQL INNER JOIN 关键字

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

INNER JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：INNER JOIN 与 JOIN 是相同的。

SELECT employees.last_name, employees.first_name, dept_emp.dept_no from employees inner join 
dept_emp on employees.emp_no=dept_emp.emp_no;

自然连接：natural join，自动对两个表，按照同名的列进行内连接，是一种特殊的等值连接：

SELECT employees.last_name,employees.first_name,dept_emp.dept_no from employees natural join dept_emp