41 删除emp_no重复的记录,只保留最小的id对应的记录

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

41.2 题解

-- 2 删除不在最小id里的数据
delete from 
    titles_test
where 
    id not in (
        -- 1 先对emp_no进行分组,找到每组的最小id
        select
            min(id) id
        from 
            titles_test 
        group by
            emp_no)
  • 先用 GROUP BYMIN() 选出每个 emp_no 分组中最小的 id,然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”

42 将所有to_date为9999-01-01的全部更新为NULL

42.1 题目描述

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

42.2 题解

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

43* 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

43.1 题目描述

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

43.2 题解 replace

43.2.1 replace into

REPLACE INTO 
    titles_test 
VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
  • 全字段更新替换。由于 REPLACE 的新记录中 id=5,与表中的主键 id=5 冲突,故会替换掉表中 id=5 的记录,否则会插入一条新记录(例如新插入的记录 id = 10)。==并且要将所有字段的值写出,否则将置为空==。

43.2.2 replace(字段,oldval,newval)

update
    titles_test 
set 
    emp_no = replace(emp_no, 10001, 10005)
where 
    id = 5

44 将titles_test表名修改为titles_2017

44.1 题目描述

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

44.2 题解 rename to

alter table titles_test rename to titles_2017
  • mysql只需要rename就行
  • oracle rename user to user_HDU;

45* 在audit表上创建外键约束,其emp_no对应employees_test表的主键id

45.1 题目描述

在audit表上创建外键约束,其emp_no对应employees_test表的主键id。

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,
create_date datetime NOT NULL
);

45.2 题解(通过题解有问题)

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));
  • 正常题解 mysql
alter table
    audit
add foreign key
    EMP_no 
references 
    employees_test(id)

46 如何获取emp_v和employees有相同的数据no

46.1 题目描述

存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?

输出格式:

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_nobirth_datefirst_namelast_namegenderhire_date
100061953-04-20AnnekePreusigF1989-06-02
100071957-05-23TzvetanZielinskiF1989-02-10
100081958-02-19SaniyaKalloufiM1994-09-15
100091952-04-19SumantPeacF1985-02-18
100101963-06-01DuangkaewPiveteauF1989-08-24
100111953-11-07MarySluisF1990-01-22

46.2 题解 intersect求交集

select 
    v.emp_no
    ,v.birth_date
    ,v.first_name
    ,v.last_name
    ,v.gender
    ,v.hire_date
from    
    emp_v v
    ,employees  e
where 
    v.emp_no = e.emp_no
  • 视图就是从原表中拿出来的数据,直接拿出视图数据也行的,没必要关联视图和原表
select
    * 
from emp_v 
intersect
select 
    *
from 
    employees 
  • 可以使用intersect求视图和原表的交集

47 将所有获取奖金的员工当前的薪水增加10%

47.1 题目描述

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

47.2 题解

update
    salaries
set 
    salary = salary * 1.1
where 
    emp_no in (
        select
            emp_no
        from
            emp_bonus)

48 针对库中的所有表生成select count()对应的SQL语句

48.1 题目描述

针对库中的所有表生成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;

48.2 题解 sqlite_master

select 
    'select count(*) from ' || name || ';' as cnts
from 
    sqlite_master 
where type = 'table'
  • 本题主要有以下两个关键点:

    • 在 SQLite 系统表 sqlite_master 中可以获得所有表的索引,其中字段 name 是所有表的名字,而且对于自己创建的表而言,字段 type 永远是 'table',详情可参考: http://blog.csdn.net/xingfeng0501/article/details/7804378
    • 在 SQLite 中用 || 符号连接字符串

49 将employees表中的所有员工的last_name和first_name通过(')连接起来

49.1 题目描述

将employees表中的所有员工的last_name和first_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

49.2 题解

49.2 .1 sqlite (||)

select 
    last_name || "'" || first_name
from 
    employees

49.2.2 mysql (concat)

select 
    concat(last_name,"'",first_name)
from 
    employees

50* 查找字符串'10,A,B' 中逗号','出现的次数cnt

50.1 题目描述

查找字符串'10,A,B' 中逗号','出现的次数cnt。

50.2 题解

select
    length('10,A,B') - length(replace('10,A,B',',','')) as cnt
  • 解题思路:

①巧用length函数和replacelength函数计算字符串的长度,length("10,A,B")算出整个字符串的长度。

②使用replace, 替换为空,那么整个字符串减少的长度等于 , 的长度,两者相减就是 , 出现的次数。

Last modification:May 13th, 2020 at 10:00 am