2.删除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’);
先按照emp_no分组,找出每组中最小的id
删除id不等于以上id的项
delete from titles_test where id not in (select * from (select min(id) from titles_test group by emp_no)as a);
注意在select以后要加as a。给最小值重命名为a列。根据这一列删除其他行。
3.创建一个actor表,包含如下列信息:
列表 类型 是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
输入描述:
无
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);
**4.**获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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
));
输入描述:
无
输出描述:
emp_no manager_no emp_salary manager_salary
10001 10002 88958 72527
10009 10010 95409 94409
select
sa.emp_no,sb.emp_no as manager_no,sa.salary as emp_salary,sb.salary as manager_salary
from
(select
s.salary,de.emp_no,de.dept_no
from dept_emp de
inner join salaries s
on de.emp_no=s.emp_no where s.to_date='9999-01-01') sa
inner join
(select
s.salary,dm.emp_no,dm.dept_no
from dept_manager dm
inner join salaries s
on dm.emp_no=s.emp_no where s.to_date='9999-01-01') sb
on sa.dept_no=sb.dept_no and sa.salary>sb.salary
5.创建一个actor_name表
对于如下表actor,其对应的数据为:
actor_id first_name last_name
1 PENELOPE GUINESS
2 NICK WAHLBERG
创建一个actor_name表,将actor表中的所有first_name以及last_name导入该表。 actor_name表结构如下:
列表 类型 是否为NULL 含义
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
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);
6.【MySQL】使用子查询的方式找出属于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);
使用子查询的方式找出属于Action分类的所有电影对应的title,description
输入描述:
无
输出描述:
无
AC代码:
select title,description from film
where film_id in (select film_id from film_category
where category_id in (select category_id from category
where name='action'));
外表1 主键 in (中间层 (in 内层 条件)
错误代码:
select title,description from film,film_category,category where film.film_id=film_catagory.film_id,file_catagory.category_id= category.category_id and category.name='Anction'
- 【MySQL】使用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以及名称:
因为category表格中name不能为空,所以只需要查看film表有而film_catogory中没有的film_id即可
select film_id,title from film
where film_id not in (
select film_id from film_category
);
- 【MySQL】统计出当前各个title类型对应的员工薪水对应的平均工资
题目描述
统计出各个title类型对应的员工的薪水对应的平均工资。结果给出title以及平均工资avg。
CREATE TABLEsalaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
PRIMARY KEY (emp_no
,from_date
));
CREATE TABLE IF NOT EXISTS “titles” (
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL);
输入描述: 无
输出描述:
title avg
Engineer 94409.0
Senior Engineer 69009.2
Senior Staff 91381.0
Staff 72527.0
select titles.title,avg(salaries.salary) from titles,salaries
where titles.emp_no=salaries.emp_no
group by title;
9.将所有to_date为9999-01-01的全部更新为NULL
将所有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’);
知识点:update 表名 set 列名… 条件
update from titles_test to_date=NULL ,from_date='2001-01-01' where to_date='9999-01-01';
10.与2题目重复
- 【MySQL】按照dept_no进行汇总
按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
CREATE TABLEdept_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
));
输出格式:
dept_no employees
d001 10001,10002
d002 10006
d003 10005
d004 10003,10004
d005 10007,10008,10010
d006 10009,10010
group_concat()函数说明:
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
select dept_no,group_concat(emp_no) employees from dept_emp
group by dept_no;
12
14.【MySQL】获取有奖金的员工相关信息
获取有奖金的员工相关信息。
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
select em.emp_no, em.first_name, em.last_name, eb.btype, sa.salary,
(
case eb.btype
WHEN 1 THEN sa.salary * 0.1
WHEN 2 THEN sa.salary * 0.2
ELSE sa.salary * 0.3 END
) as bonus
from employees as em
inner join emp_bonus as eb on em.emp_no = eb.emp_no
inner join salaries as sa on em.emp_no = sa.emp_no and sa.to_date = '9999-01-01';
无语,sql里面不能使用百分号%
15 【MySQL】查找employees表所有emp_no为奇数
题目描述
查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
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 birth_date first_name last_name gender hire_date
10011 1953-11-07 Mary Sluis F 1990-01-22
10005 1955-01-21 Kyoichi Maliniak M 1989-09-12
10007 1957-05-23 Tzvetan Zielinski F 1989-02-10
10003 1959-12-03 Parto Bamford M 1986-08-28
10001 1953-09-02 Georgi Facello M 1986-06-26
10009 1952-04-19 Sumant Peac F 1985-02-18
select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where emp_no%2=1 and last_name!='Mary'
order by hire_date DESC;
讲解select语句的:
https://blog.csdn.net/Elsa15/article/details/80954174