Mysql刷题笔记之二

23、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

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

分析：
多张表联立查询
给一张表manager，如何确定不是manager的员工

select 
de.dept_no,emp.emp_no,s.salary
from

(
    /*
    2、在排除manager即为普通的员工
    */
    select 
* 
from employees e
where emp_no not in
(
    /** 1、先查出manager的员工   **/
select 
e.emp_no
from
employees e
join dept_manager m
on e.emp_no=m.emp_no) )as emp
/**
3、联立多张表进行查询：
    注意每添加一张表，都需要添加联立条件
**/
join
salaries s
join 
dept_emp de
on 
emp.emp_no=s.emp_no
and
de.emp_no=emp.emp_no
and
s.to_date='9999-01-01';

24、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

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

/**
本题主要思想是创建两张表
（一张记录当前所有员工的工资，另一张只记录部门经理的工资）
进行比较，具体思路如下：
1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm
链接：https://www.nowcoder.com/questionTerminal/f858d74a030e48da8e0f69e21be63bef?f=discussion
来源：牛客网

3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
找出同一部门中工资比经理高的员工，
并根据题意依次输出emp_no、manager_no、emp_salary、
**/
select
e.emp_no,m.emp_no 	manager_no,
e.salary emp_salary,m.salary 	manager_salary

from

(select 
manager.emp_no,
manager.dept_no,
manager.to_date,
s.salary
from 
dept_manager manager
join 
salaries s
on 
manager.emp_no=s.emp_no
and
manager.to_date='9999-01-01'
 and
 s.to_date='9999-01-01'
) as m


join

(select 
emp.emp_no,
emp.dept_no,
emp.to_date,
 s.salary
from 
dept_emp emp
join 
salaries s
on 
emp.emp_no=s.emp_no
and
 emp.to_date='9999-01-01'
 and
 s.to_date='9999-01-01'
) as e

on
m.dept_no=e.dept_no
and
m.salary<e.salary;

---

25、汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_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 IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

多张表联立查询
注意多张表联立查询的条件

/**
    多张表联立的时候
    可以  表1  join  表2 on  条件   join  表3 on 条件
    也可以   表1 join 表2  join 表s on 条件1 and 条件2 and 条件3
**/
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM departments AS dp  INNER JOIN dept_emp AS de 

INNER JOIN titles AS t
on
t.to_date='9999-01-01'
and
t.emp_no=de.emp_no
and
dp.dept_no=de.dept_no 
and de.to_date='9999-01-01'

GROUP BY de.dept_no, t.title

---

---

26、给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_da

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 s1.emp_no,s1.from_date,s1.salary-s2.salary as salary_growth
from salaries s1,salaries s2
where strftime('%Y', s1.to_date)-strftime('%Y', s2.to_date)=1
and s1.salary-s2.salary>5000
and s1.emp_no=s2.emp_no
order by salary_growth desc

---

2020/3/20 17:30

---

27、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name AS name, COUNT(f.film_id) AS amount
FROM film AS f, film_category AS fc, category AS c,
(SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(category_id) >= 5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = cc.category_id

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

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

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp)

分析：
解题思路是运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件：
1、用LEFT JOIN连接 film 和 film_category，限定条件为f.film_id = fc.film_id，即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
这是一种类型的题目，在一张表中查询在另外一张表中没有分配数据，可使用外连接再进行空值判断

select 
f.film_id,
f.title

from
film f
left join 
film_category fc
on f.film_id=fc.film_id
/*注意这里为什么是where*/ 
where fc.category_id is null;

---

---

29、使用子查询的方式找出属于Action分类的所有电影对应的title,description

select 
f.title,f.description
from
/* 先找出 name为Action的电影id  */
(select category_id from category where name='Action') t
/* 多表联立  */
join film_category fc
on t.category_id=fc.category_id
join film f
on f.film_id=fc.film_id;

---

---

30、获取select * from employees对应的执行计划

考察：如何分析SQL引擎对Sql的执行过程

explain select * from employees;

31、将employees表的所有员工的last_name和first_name拼接起来作为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`));

考察sql的函数

//若支持函数可采用这种方法
select CONCAT(last_name," "，first_name) as name  from employees


//不支持函数采用这种方法
select last_name || ' ' || first_name as Name from employees

---

---

32、创建表

创建一个actor表，包含如下列信息
列表 类型 是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
last_update timestamp not null 最后更新时间，默认是系统的当前时间

创建表：

create table actor(
    actor_id  smallint(5) primary key,
    first_name varchar(45) not null,
    last_name  varchar(45) not null,
    last_update timestamp  not null default (datetime('now','localtime')))

---

33、插入数据

对于表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’)))

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

---

---

34、插入数据若存在则不插入

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
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’)))

插入语句中 加入关键字 ignore

insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

---

2020/3/21 16:00

---

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

从别表中搜索出来的数据插入到当前表中

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;

---

---

36、对first_name创建唯一索引uniq_idx_firstname

/*针对如下表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')))

分析创建索引的语法
create (unique) index 索引名 on 表名（列名）

create 
unique index uniq_idx_firstname 
on actor(first_name);

create 
index idx_lastname 
on actor(last_name);

---

---

37、针对actor表创建视图 actor_name_view

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
`

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

分析：
创建视图语法
create view 视图名 as select 语句

create  view actor_name_view as 

select first_name as first_name_v,

last_name as last_name_v
from actor;

---

---

38、针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

关键：如何使用关键索引

select * 
from salaries 
FORCE INDEX (idx_emp_no) 
where emp_no = 10005;

---

---

39、在last_update后面新增加一列名字create_date

存在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’)));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’

语法: alter table 表名 add 字段名类型 not null default

alter table actor add 
create_date datetime not null default '0000-00-00 00:00:00';

---

---

40、构建触发器

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

分析：
1.create trigger ：创建触发器
2.触发器要说明是在after 还是before事务发生时触发
3.要指明是insert 、delete、update操作
4. on 表名
5.begin和end之间写触发的动作
6. new 关键字表示更新后的表的字段 ，old表示更新前的表的字段

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

---

2020/3/22 14:00

---