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