数据库操作练习3

1. 删除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');

sql1:  先用 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)

笔记：

    order by 用于排序,一般与asc升序或desc降序一起使用.
例:select * from 表A order by 列a
    group by 用于分类汇总,一般与聚合函数(比如avg平均、sum合计、max最大、min最小、count计算行)一起使用。
例:select 月份,sum(工资)as 总工资 from 工资表 group by 月份 
此语句用于统计每个月的日总工资，在使用group by的语句中，只能select用于分类的列（表达式），或聚合函数。
where条件用于group by之前，having用于group by 之后对结果进行筛选。

2. 针对库中的所有表生成select

题目描述

针对库中的所有表生成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`));
输出格式:

cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;
select count(*) from emp_bonus;

sql1: 在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'

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

3.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

题目描述

将所有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);

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

sql1:

        update用法：

               UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

        另外要注意若干列 to_date = NULL 和 from_date = '2001-01-01' 之间只能用逗号连接，切勿用 AND 连接。

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

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

题目描述

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

子查询：

        根据题意，最简单粗暴的解法是直接 FROM 三张表查询，且用 WHERE 并列三个限定条件：
1、三个限定条件分别是【f.film_id = fc.film_id】、【fc.category_id = c.category_id 】、【c.name = 'Action'】 

        SELECT f.title, f.description FROM film f, film_category fc, category c WHERE f.film_id = fc.film_id AND fc.category_id = c.category_id 
AND c.name = 'Action'

非子查询：

        select f.title,f.description from film as f inner join film_category as fc on f.film_id = fc.film_id inner join category as c on c.category_id = fc.category_id where c.name = 'Action';

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

sql1:

        update salaries set salary=salary+salary*0.1 where emp_no in (select emp_no from emp_bonus) and to_date='9999-01-01'

sql2:

        update salaries set salary=salary*1.1 where emp_no in
(select sa.emp_no from salaries sa inner join emp_bonus em on sa.emp_no=em.emp_no and sa.to_date='9999-01-01')

6. 获取所有员工的emp_no

题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
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`));
输出格式:

e.emp_no	dept_no	btype	recevied
10001	d001	1	2010-01-01
10002	d001	2	2010-10-01
10003	d004	3	2011-12-03
10004	d004	1	2010-01-01
10005	d003
10006	d002
10007	d005
10008	d005
10009	d006
10010	d005
10010	d006

 

select e.emp_no,e.dept_no,em.btype,em.recevied from dept_emp e left join emp_bonus em on e.emp_no=em.emp_no

 

7. 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

题目描述

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

输入描述:

无

输出描述:

Name
Facello Georgi
Simmel Bezalel
Bamford Parto
Koblick Chirstian
Maliniak Kyoichi
Preusig Anneke
Zielinski Tzvetan
Kalloufi Saniya
Peac Sumant
Piveteau Duangkaew
Sluis Mary

这题用CONCAT(last_name,' ',first_name )始终通不过，由于concat函数来只支持MySQL、SQL Server、Oracle等数据库，SQLite数据库连接字符串要用连接符号"||"

select last_name||' '||first_name as Name from employees

8. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

题目描述

查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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	t
10001	17
10004	16
10009	18

WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>15的条件
4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可（参考牛客网网友解答）其次广义涨幅的涨幅既包括涨也包括跌，因此只需统计emp_no出现的次数即可。having 一般跟在group by的后面。

select emp_no,count(emp_no) as t from salaries group by emp_no having t>15

9. 获取所有非manager的员工emp_no

题目描述

获取所有非manager的员工emp_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`));

输入描述:

无

输出描述:

emp_no
10001
10003
10007
10008
10009
10011

sql1:

        使用NOT IN选出在employees但不在dept_manager中的emp_no记录

        select emp_no from employees where emp_no not in(select emp_no from dept_manager)

sql2:

        先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录  

       select emp_no from (select * from employees em left join dept_manager de on em.emp_no=de.emp_no) where dept_no is 

10. 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

 

题目描述

针对如下表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')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

唯一的索引 (Unique Index)

在表格上面创建某个一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX 索引名称
ON 表名称 (列名称) 

"列名称" 规定你需要索引的列。

简单的索引

在表上创建一个简单的索引。当我们省略关键词 UNIQUE 时，就可以使用重复的值。

CREATE INDEX 索引名称
ON 表名称 (列名称)

"列名称" 规定你需要索引的列。

create unique index uniq_idx_firstname on actor(first_name);
create index  idx_lastname on actor(last_name);