MySQL練習（1）——牛客網[xiaoshun]

一、牛客網網址

https://www.nowcoder.com/ta/sql，一共76道真題，

二、題目

1、查找最晚入職員工的所有資訊，為了減輕入門難度，目前所有的資料里員工入職的日期都不是同一天(sqlite里面的注釋為--,mysql為comment)
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`));

解題：

（1）假設員工入職的日期都不是同一天，日期降序排序后，直接截取第一條資料即可，

select * from employees order by hire_date desc limit 1;

　　limit m,n：m為起始索引位置（索引從0開始），往后獲取n行資料，如limit 6,10 獲取第7行到16行資料，另一種寫法：limit n offset m

　　limit 陳述句運行順序排到最后，因為它是從結果集中截取部分行數，

（2）假設員工入職的日期有可能同一天，

select *
from employees
where hire_date = (
    select max(hire_date)
    from employees)；
#1.用子查詢獲取最遲入職日期；
#2.把入職日期等于最遲入職日期的所有全找出來，

2、查找入職員工時間排名倒數第三的員工所有資訊，為了減輕入門難度，目前所有的資料里員工入職的日期都不是同一天
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`));

解題：

（1）假設員工入職的日期都不是同一天

select * from employees order by hire_date desc limit 2,1;
# 直接降序排序獲取第三行記錄即可

（2）假設員工入職的日期有可能同一天

select *
from employees
where hire_date=(
    select distinct hire_date
    from employees
    order by hire_date desc
    limit 1 offset 2
);
#1.先用distinct把重復的日期去重，得到的日期都是唯一的
#2.再排序后，用limit獲取倒數第三天入職的日期
#3.最后把入職日期等于倒數第三天日期的員工資訊全部獲取

3、

查找各個部門當前(dept_manager.to_date='9999-01-01')領導當前(salaries.to_date='9999-01-01')薪水詳情以及其對應部門編號dept_no (注:請以salaries表為主表進行查詢，輸出結果以salaries.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`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部門編號'
`emp_no` int(11) NOT NULL, --  '員工編號'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

解題：

select sa.*, dm.dept_no
from  salaries as sa
inner join dept_manager as dm
on dm.emp_no = sa.emp_no
where sa.to_date='9999-01-01'
and dm.to_date='9999-01-01'
order by sa.emp_no asc;
# 考察表的內連接和一些實際情況業務的了解

4、查找所有已經分配部門的員工的last_name和first_name以及dept_no(請注意輸出描述里各個列的前后順序)
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 `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`));

解題：

select last_name, first_name, dept_no
from employees e
inner join dept_emp d
on e.emp_no = d.emp_no
where dept_no is not null;
# 考察表內連接，不用指定dept_no是否為null，因為內連接中，任何一邊有缺失資料就不會顯示，

5、查找所有員工的last_name和first_name以及對應部門編號dept_no，也包括暫時沒有分配具體部門的員工(請注意輸出描述里各個列的前后順序)
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 `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`));

select last_name,first_name,dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no；
#即使沒有分配部門的員工也要顯示，意味著employees表中所有emp_no都要顯示，不管dept_emp表的對應dept_no是否缺失，考察左連接

多表連接查詢知識點總結：

表1 inner join 表2　　兩邊表同時有對應的資料，即任何一邊有缺失資料就不顯示；

主 left join 從　　　　左邊主表的資料全部讀取，右邊從邊無對應資料的填充NULL值；

從 right join 主　　　 反之；

 

6、查找所有員工入職時候的薪水情況，給出emp_no以及salary， 并按照emp_no進行逆序(請注意，一個員工可能有多次漲薪的情況)
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 `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`));

解題：

（1）表連接

select e.emp_no, s.salary
from employees e
inner join salaries s
on e.emp_no = s.emp_no
where s.from_date = e.hire_date
order by e.emp_no desc;
# 1.有漲薪或者降薪，說明salaries表中的每一個emp_no至少有一個，薪水變過就有重復值，
# 2.只要from_date等于入職日期hire_date，薪水就是入職時的薪水，

（2）分組之后求日期最小值

select emp_no,salary
from salaries
group by emp_no
having min(from_date)
order by emp_no desc;
# 1.先用emp_no分組，得到每個員工分組資料
# 2.再用having對每個分組求最早日期，即剛入職的日期，不能求最小工資，因為員工有可能被降薪，
# 3.資料量大時，不建議用分組查詢，因為having之后，程式會在每個分組表中在遍歷一遍，可能導致性能不佳，

7、查找薪水變動超過15次的員工號emp_no以及其對應的變動次數t
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`));

解題：

select emp_no,count(*) as t
from salaries
group by emp_no
having count(*) > 15;
# 考察點是分組統計查詢，

本題一些思考：一天之內多次變動，from_date和to_date均一樣，count(distinct from_date)就不太合適，但這種情況實際發生的概率應該很少；

　　　　　　　錄入時資料粗心大意，可能導致salary一樣，count(distinct salary)也不太合適，這種情況有可能發生，但是員工發工資時應該會發現；

　　　　　　　假如統計的是漲工資次數，而不是變動次數，應該用自連接來判斷工資是漲還是降，

分組統計查詢知識點歸納：

• 統計的欄位最好和用來分組的欄位是一樣的，假如不一樣，統計欄位也必須和分組欄位存在一一映射（值可以重復）；
• 統計時，最好用count(*)，效率最高，（可能無法用count（*），因為欲統計的欄位有重復值，需要count（distinct  欄位））
• where是分組前篩選原始表，having是分組后篩選每個分組表，所有能在where前篩選就不要分組后再篩選，
• 分組函式做條件篩選肯定放在having陳述句中，where用一些邏輯判斷篩選即可，

8、找出所有員工當前(to_date='9999-01-01')具體的薪水salary情況，對于相同的薪水只顯示一次,并按照逆序顯示
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`));

解題：

（1）distinct關鍵字排除重復

select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;
# distinct 多列去重時，只有所有列的資訊完全一致才認為時重復的，

（2）用group by去重，據說資料量很大時，效率比較高

select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;

9、獲取所有部門當前(dept_manager.to_date='9999-01-01')manager的當前(salaries.to_date='9999-01-01')薪水情況，給出dept_no, emp_no以及salary(請注意，同一個人可能有多條薪水情況記錄)
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`));

解題：

select dept_no,d.emp_no,salary
from dept_manager d
inner join salaries s
on d.emp_no = s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01';

10、獲取所有非manager的員工emp_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 `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`)); 解題： （1）左外連接

select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where d.dept_no is null;
# 1.假如員工不是manager，那么dept_no欄位應該為null
# 2.通過左連接的方式，使不是manager的dept_no欄位為null，再篩選即可

（2）子查詢

select emp_no
from employees
where emp_no not in(
    select d.emp_no
    from dept_manager d
    inner join employees e
    on d.emp_no = e.emp_no
);
# 1.先用子查詢查出是manger的emp_no
# 2.再判斷員工emp_no不在manger的emp_no里面即可
# 3.子查詢可以直接用select emp_no from dept_manager，畢竟dept_manager這個表存的是manage的資訊，

11、獲取所有員工當前的(dept_manager.to_date='9999-01-01')manager，如果員工是manager的話不顯示(也就是如果當前的manager是自己的話結果不顯示)，輸出結果第一列給出當前員工的emp_no,第二列給出其manager對應的emp_no，
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`)); 解題：

select de.emp_no, dm.emp_no as manager_no
from dept_emp de
inner join dept_manager dm
on dm.dept_no = de.dept_no
where de.emp_no <> dm.emp_no
and dm.to_date='9999-01-01'
and de.to_date='9999-01-01';
# 1.兩表內連接后，只有manager的de.emp_no和dm.emp_no是一樣的，用where篩選掉即可，
# 2.因為部門的manager有可能會離職或者更換，要保證manage和普通員工都在同一時間在同一部門才能構成從屬關系，兩個跨時空的人戀愛會很慘的！！！

 未完待補充

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/168075.html

標籤：其他

上一篇：做站外包給三方需要注意哪些問題

下一篇：資料庫并發性問題