SQL213 查找所有员工的last name和first name以及对应的dept name
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Chirstian Koblick M 1986-12-01 有一个部门表departments表简况如下:
dept_no dept_name d001 Marketing d002 Finance d003 Human Resources 有一个,部门员工关系表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1990-08-05 9999-01-01 请你查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工,以上例子输出如下:
last_name first_name dept_name Facello Georgi Marketing Simmel Bezalel Marketing Bamford Parto Finance Koblick Chirstian NULL
首先,通过left join将employees表和dept_emp表连接起来,连接条件是employees表中的emp_no与dept_emp表中的emp_no相等。然后,再通过left join将dept_emp表和departments表连接起来,连接条件是dept_emp表中的dept_no与departments表中的dept_no相等。最后,从中选取所需的列即可。
select last_name,first_name,dept_name
from employees e left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no
SQL215 查找在职员工自入职以来的薪水涨幅情况
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 2001-06-22 10002 1964-06-02 Bezalel Simmel F 1999-08-03 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 85097 2001-06-22 2002-06-22 10001 88958 2002-06-22 9999-01-01 10002 72527 1999-08-03 2000-08-02 10002 72527 2000-08-02 2001-08-02 请你查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,以上例子输出为
(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01'时,表示依然在职,无后续调整记录)
emp_no growth 10001 3861
首先,使用子查询A1,从salaries表中选择emp_no和salary列,并通过left join将salaries表与employees表连接起来,连接条件是salaries表中的emp_no与employees表中的emp_no相等。同时,使用where子句过滤出hire_date与from_date相等的记录,即选取初始工资。
然后,使用子查询A2,从salaries表中选择emp_no和最大salary,并通过where子句过滤出to_date为'9999-01-01'的记录,即选取当前工资。
最后,通过left join将A1和A2两个子查询结果连接起来,连接条件是emp_no相等。并使用having子句过滤掉growth为空的记录。
select A1.emp_no,(A2.salary - A1.salary) as growth from
(select salaries.emp_no,salary from salaries
left join employees on salaries.emp_no = employees.emp_no
where from_date = hire_date) as A1
left join
(select emp_no,max(salary) as salary
from salaries
where to_date = '9999-01-01'
group by emp_no) as A2
on A1.emp_no = A2.emp_no
having growth is not null
order by growth
SQL216 统计各个部门的工资记录数
描述
有一个部门表departments简况如下:
dept_no dept_name d001 Marketing d002 Finance 有一个,部门员工关系表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 2001-06-22 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1996-08-03 9999-01-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 85097 2001-06-22 2002-06-22 10001 88958 2002-06-22 9999-01-01 10002 72527 1996-08-03 9999-01-01 10003 32323 1996-08-03 9999-01-01 请你统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum,按照dept_no升序排序,以上例子输出如下:
dept_no dept_name sum d001 Marketing 3 d002 Finance 1
首先,使用left join将salaries表与dept_emp表连接起来,连接条件是salaries表中的emp_no与dept_emp表中的emp_no相等。
然后,使用left join将dept_emp表与departments表连接起来,连接条件是dept_emp表中的dept_no与departments表中的dept_no相等。
接下来,使用group by子句按照dept_no进行分组,同时使用count(1)函数统计每个部门的记录数,也就是员工数量。将这个统计结果命名为sum。
最后,使用order by子句按照dept_no进行升序排序,得到最终结果。这样就可以获得每个部门的部门编号、部门名称和员工数量统计信息。
select d.dept_no,dept_name,count(1) as sum
from salaries s left join dept_emp de on s.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no
group by dept_no
order by dept_no
SQL217 对所有员工的薪水按照salary降序进行1-N的排名
描述
有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 10004 72527 2001-12-01 9999-01-01 对所有员工的薪水按照salary降序先进行1-N的排名,如果salary相同,再按照emp_no升序排列:
emp_no salary t_rank 10001 88958 1 10002 72527 2 10004 72527 2 10003 43311 3
首先,从salaries表中选择emp_no和salary两个列。
然后,使用dense_rank()窗口函数,将结果按照salary列的降序排列。dense_rank()函数会为每个不同的工资值赋予一个排名,排名相同的工资值会有相同的排名。
最后,将结果列命名为t_rank,表示工资的排名。
select emp_no,salary,dense_rank()over(order by salary desc) as t_rank
from salaries
SQL218 获取所有非manager员工当前的薪水情况
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1996-08-03 有一个,部门员工关系表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 有一个部门经理表dept_manager简况如下:
dept_no emp_no from_date to_date d001 10002 1996-08-03 9999-01-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 1986-06-26 9999-01-01 10002 72527 1996-08-03 9999-01-01 获取所有非manager员工薪水情况,给出dept_no、emp_no以及salary,以上例子输出:
dept_no emp_no salary d001 10001 88958
首先,从employees表、dept_emp表和salaries表进行连接,通过员工编号将这些表连接起来。
然后,通过条件"e.emp_no = de.emp_no"和"e.emp_no = s.emp_no"来连接这三个表。
接下来,使用子查询"select emp_no from dept_manager"来获取所有的部门经理的员工编号。
最后,通过条件"de.emp_no not in (select emp_no from dept_manager)"来排除掉那些是部门经理的员工,只保留不是部门经理的员工。
这样,查询结果包含了不是部门经理的员工的部门编号、员工编号和工资信息。
select de.dept_no,de.emp_no,s.salary from
employees e join
dept_emp de on e.emp_no = de.emp_no
join salaries s on e.emp_no =s.emp_no
where de.emp_no not in (select emp_no from dept_manager)
SQL219 获取员工其当前的薪水比其manager当前薪水还高的相关信息
描述
有一个,部门关系表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 有一个部门经理表dept_manager简况如下:
dept_no emp_no from_date to_date d001 10002 1996-08-03 9999-01-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 1996-08-03 9999-01-01 获取员工其当前的薪水比其manager当前薪水还高的相关信息,
第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary以上例子输出如下:
emp_no manager_no emp_salary manager_salary 10001 10002 88958 72527
首先,从dept_emp表、dept_manager表和salaries表进行连接。通过条件"de.dept_no=dm.dept_no"将这两个表连接起来。
然后,通过条件"de.emp_no=s1.emp_no"将salaries表和dept_emp表连接起来,获取员工的工资信息。
接下来,通过条件"dm.emp_no=s2.emp_no"将salaries表和dept_manager表连接起来,获取部门经理的工资信息。
最后,通过条件"s1.salary>s2.salary"筛选出员工的工资高于部门经理的工资的记录。
这样,查询结果中会包含员工的员工编号、对应的部门经理的员工编号、员工的工资和部门经理的工资信息,且仅包含员工的工资高于部门经理的工资的记录。
select de.emp_no,
dm.emp_no as manager_no,
s1.salary as emp_salary,
s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
SQL220 汇总各个部门当前员工的title类型的分配数目
描述
有一个部门表departments简况如下:
dept_no dept_name d001 Marketing d002 Finance 有一个,部门员工关系表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1995-12-03 9999-01-01 有一个职称表titles简况如下:
emp_no title form_date to_date 10001 Senior Engineer 1986-06-26 9999-01-01 10002 Staff 1996-08-03 9999-01-01 10003 Senior Engineer 1995-12-03 9999-01-01 汇总各个部门当前员工的title类型的分配数目,即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count,结果按照dept_no升序排序,dept_no一样的再按title升序排序
dept_no dept_name title count d001 Marketing Senior Engineer 1 d001 Marketing Staff 1 d002 Finance Senior Engineer 1
首先,我们使用LEFT JOIN将dept_emp表与titles表连接起来,连接条件是两张表中的emp_no字段相等。这样我们可以获取到每个员工所担任的职称信息。
然后,我们再次使用LEFT JOIN将dept_emp表与departments表连接起来,连接条件是两张表中的dept_no字段相等。这样我们可以获取到每个员工所在的部门信息。
接下来,我们使用GROUP BY语句按照部门编号和职称对结果进行分组。这样就可以计算每个部门每个职称的员工数量。
最后,我们使用ORDER BY语句对结果进行排序,首先按照部门编号进行升序排列,然后按照职称进行升序排列。
select d.dept_no,dept_name,title,count(1) as count
from dept_emp de left join titles t on de.emp_no=t.emp_no
left join departments d on de.dept_no = d.dept_no
group by dept_no,title
order by dept_no,title
SQL223 使用join查询方式找出没有分类的电影id以及名称
描述
现有电影信息表film,包含以下字段:
字段 说明 film_id 电影id title 电影名称 description 电影描述信息 有类别表category,包含以下字段:
字段 说明 category_id 电影分类id name 电影分类名称 last_update 电影分类最后更新时间 电影分类表film_category,包含以下字段:
字段 说明 film_id 电影id category_id 电影分类id last_update 电影id和分类id对应关系的最后更新时间 使用join查询方式找出没有分类的电影id以及其电影名称。
首先,我们从film表中选择film_id和title列,并将其作为基础结果集。
然后,我们使用LEFT JOIN将film表与film_category表连接起来。连接条件是两张表中的film_id字段相等。这样我们就可以获取到每个电影与其所属分类的对应关系。
然后,我们使用WHERE子句来过滤结果,只保留category_id为空的记录。这意味着这些记录没有与任何分类相关联。
最后,我们得到的结果就是所有没有分类的电影的电影ID和标题。这样的查询可以帮助我们找到需要进一步处理或分类的电影。
select film.film_id,title
from film
left join film_category on film.film_id=film_category.film_id
where category_id is null
SQL224 使用子查询的方式找出属于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吗
首先,我们从film表中选择title和description列,并将其作为基础结果集。
然后,我们使用LEFT JOIN将film表与film_category表连接起来。连接条件是两张表中的film_id字段相等,这样我们可以获取每个电影与其所属分类的对应关系。
接下来,我们使用另一个LEFT JOIN将film_category表与category表连接起来。连接条件是两张表中的category_id字段相等,这样我们可以获取每个电影所属分类的具体信息。
最后,我们使用WHERE子句来过滤结果,只保留分类名称为"Action"的记录。这意味着我们只获取与"Action"分类相关联的电影。
最终,我们得到的结果是所有分类为"Action"的电影的标题和描述。这样的查询可以帮助我们找到特定分类的电影信息。
select title,description
from film f
left join film_category fc on f.film_id = fc.film_id
left join category c on fc.category_id = c.category_id
where name = 'Action'
SQL226 将employees表的所有员工的last name和first name拼接起来作为Name
描述
现有员工表employees如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Chirstian Koblick M 1986-12-01 10005 1955-01-21 Kyoichi Maliniak M 1989-09-12 10006 1953-04-20 Anneke Preusig F 1989-06-02 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10 10008 1958-02-19 Saniya Kalloufi M 1994-09-15 10009 1952-04-19 Sumant Peac F 1985-02-18 10010 1963-06-01 Duangkaew Piveteau F 1989-08-24 10011 1953-11-07 Mary Sluis F 1990-01-22 请将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分。
(注:sqllite,字符串拼接为 || 符号,不支持concat函数,mysql支持concat函数)。
输入描述:
无
输出描述:
Name Facello Georgi Simmel Bezalel Bamford Parto Koblick Chirstian Maliniak Kyoichi Preusig Anneke Zielinski Tzvetan Kalloufi Saniya Peac Sumant Piveteau Duangkaew Sluis Mary
首先,我们从employees表中选择last_name和first_name两列,并使用CONCAT函数将它们拼接在一起,中间用空格分隔。将这个拼接后的结果命名为"Name"。
最终,我们得到的结果是所有员工的姓名,格式为"姓 名"的形式。这样的查询可以方便地获取员工的全名信息。
select concat(last_name," ",first_name) as Name from employees
SQL227 创建一个actor表,包含如下列信息
描述
创建一个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 最后更新时间,默认是系统的当前时间
首先,我们定义了表的结构和字段。actor表包含以下列:actor_id,first_name,last_name和last_update。
- actor_id是一个smallint(5)类型的字段,表示演员的ID。它是主键,因此不允许为空。
- first_name是一个varchar(45)类型的字段,用于存储演员的名字。它也是必需的,不允许为空。
- last_name是一个varchar(45)类型的字段,用于存储演员的姓氏。同样,它也是必需的,不允许为空。
- last_update是一个date类型的字段,用于记录记录的最后更新时间。也是必需的,不允许为空。
最后,我们将这些定义应用于创建名为actor的表。这个表将用来存储演员的信息,包括ID、名字、姓氏和最后更新时间。
create table actor
(
actor_id smallint(5) not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null
)
SQL228 批量插入数据
描述
对于表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。
然后,我们指定要插入的列名称:actor_id, first_name, last_name, last_update。
接下来,使用VALUES关键字,我们指定要插入的具体值。在这个例子中,我们插入了两条数据。
第一条数据的值为:(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33')。这表示演员的ID是1,名字是PENELOPE,姓氏是GUINESS,最后的更新时间是2006年2月15日 12:34:33。
第二条数据的值为:(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')。这表示演员的ID是2,名字是NICK,姓氏是WAHLBERG,最后的更新时间是2006年2月15日 12:34:33。
最后,将这些数据插入到actor表中。这样,我们就成功向actor表中插入了两条数据。
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');
SQL229 批量插入数据,不使用replace操作
描述
题目已经先执行了如下语句:
drop table if exists actor; CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL); insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');对于表actor插入如下数据,如果数据已经存在,请忽略(不支持使用replace操作)
actor_id first_name last_name last_update '3' 'ED' 'CHASE' '2006-02-15 12:34:33'
首先,我们指定要插入的表名称:actor。
然后,使用INSERT IGNORE INTO语句,其中IGNORE关键字的作用是如果插入的数据与表中已存在的数据冲突(例如,主键重复),则忽略这条插入语句,不会报错。
接下来,使用VALUES关键字,我们指定要插入的具体值。
在这个例子中,我们插入的数据是:"3","ED","CHASE","2006-02-15 12:34:33"。
这表示演员的ID是3,名字是ED,姓氏是CHASE,最后的更新时间是2006年2月15日 12:34:33。
最后,将这条数据插入到actor表中。如果表中已经存在ID为3的演员数据,则会忽略这条插入语句,不做任何操作。如果表中不存在ID为3的演员数据,则会插入这条数据。
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33")
SQL231 对first name创建唯一索引uniq idx firstname
描述
针对如下表actor结构创建索引:
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作,
mysql支持ALTER TABLE创建索引)
CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
alter table
actor add unique uniq_idx_firstname (first_name);
alter table
actor add index idx_lastname (last_name);
SQL232 针对actor表创建视图actor name_view
描述
针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL);后台会插入2条数据:
insert into actor values ('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
然后打印视图名字和插入的数据
create view actor_name_view as
select first_name as first_name_v,last_name as last_name_v from actor
SQL233 针对上面的salaries表emp_no字段创建索引idxemp_no
描述
针对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);
后台会检查是否使用强制索引
force index强制索引
select * from salaries force index(idx_emp_no)
where emp_no = 10005
SQL234 在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 COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];
alter table actor add column
create_date datetime not null default '2020-10-01 00:00:00'
SQL235 构造一个触发器audit log
描述
构造一个触发器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);
后台会往employees_test插入一条数据:
INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );
然后从audit里面使用查询语句:
select * from audit;
create trigger triggerName
after/before/ // 触发时间 trigger_time
insert/update/delete // 监视事件 trigger_event
on table_name // 监视地点 table
for each row // 这句话在mysql中是固定的
begin
sql语句(insert/update/delete); // 触发事件 trigger_stmt 注意这里要有分号
end;
create trigger audit_log
after
insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
SQL236 删除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');
删除后titles_test表为(注:最后会select * from titles_test表来对比结果)
id emp_no title from_date to_date 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
delete from titles_test
where id not in (
select * from (
select min(id) from titles_test group by emp_no
) as A
)
SQL237 将所有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');更新后的值:
titles_test 表的值:
id emp_no title from_date to_date 1 10001 Senior Engineer 2001-01-01 NULL 2 10002 Staff 2001-01-01 NULL 3 10003 Senior Engineer 2001-01-01 NULL 4 10004 Senior Engineer 2001-01-01 NULL 5 10001 Senior Engineer 2001-01-01 NULL 6 10002 Staff 2001-01-01 NULL 7 10003 Senior Engineer 2001-01-01 NULL 后台会执行下面SQL语句得到输出,判断正确:
select count(*) from titles_test where from_date='2001-01-01' and to_date is NULL;
将titles_test表中to_date为'9999-01-01'的记录的to_date字段设置为NULL,同时将这些记录的from_date字段设置为'2001-01-01'。
SQL语句中的UPDATE关键字用于更新表中的记录。titles_test是要更新的表名。
SET关键字后面的语句用于设置需要更新的字段和值。to_date = NULL表示将to_date字段的值设为NULL,from_date = '2001-01-01'表示将from_date字段的值设为'2001-01-01'。
WHERE关键字后面的条件用于筛选需要更新的记录。to_date = '9999-01-01'表示只选择to_date字段的值为'9999-01-01'的记录。
update titles_test set to_date = NULL,from_date = '2001-01-01'
where to_date = '9999-01-01'
SQL238 将id=5以及emp no=10001的行数据替换成id=5以及emp no=10005
描述
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
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 titles_test set emp_no=replace(emp_no,10001,10005)
where id=5
SQL239 将titles test表名修改为titles 2017
描述
将titles_test表名修改为titles_2017。
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');
alter table titles_test rename to titles_2017