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 BY 和 MIN() 选出每个 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`));

输出格式:

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

• 输出格式

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

• 输出格式:

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函数和replace，length函数计算字符串的长度，length("10,A,B")算出整个字符串的长度。

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