牛客網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