牛客网SQL刷题41-50

41、删除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);

答案：

delete from titles_test where id not in
(select * from (select min(id)from titles_test group by emp_no) a);

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

基本的数据更新语法，UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 update titles_test set to_date = null , from_date = '2001-01-01' where to_date = '9999-01-01'

答案：
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,其他数据保持不变，使用replace实现，直接使用update会报错了。

CREATE TABLE 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);

本题考查的是replace函数，其中包含三个参数，
第一个参数为该字段的名称，
第二参数为该字段的需要被修改值，
第三个参数为该字段修改后的值。

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

44、将titles_test表名修改为titles_2017。

mysql中修改表信息的规则。
alter table 表名 change 原列名 新列名 类型； --修改表的列属性名
alter table 表名 modify 列名 类型 ； --修改表的类类型
alter table 表名 drop 列名； --删除表的某一列
alter table 表名 add 列名 类型；–添加某一列
alter table 表名 rename 新表名； --修改表名

alter table titles_test rename  titles_2017;

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

alter table audit add constraint fk_emp_no foreign key audit(emp_no)
references employees_test(id);

46、请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

create table emp_bonus(
emp_no int 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`));
答案：
update salaries 
set salary=salary*1.1
where emp_no in(select emp_no from emp_bonus)
and to_date='9999-01-01';

47、将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)

select concat(last_name,"'",first_name)as name
from employees;

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

select length('10,A,B')-length(replace('10,A,B',",","")) as cnt;

49、获取Employees中的first_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));

RIGHT函数：可以看到它能返回从最右边开始指定长度的字符串。同理LEFT函数就是返回从最左边开始的指定长度字符串。
select first_name from employees order by right(first_name,2);

50、按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。
答案：
SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp GROUP BY dept_no