天天看點
傳回

牛客網資料庫實戰題解題思路及答案

SQL1、查找最晚入職員工的所有資訊

drop table if exists  `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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

複制

答案：

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

複制

思路：先查詢出入職最早的哪天，再根據入職最早的哪天找員工。

SQL2 查找入職員工時間排名倒數第三的員工所有資訊

請你查找各個部門目前上司的薪水詳情以及其對應部門編号dept_no，輸出結果以salaries.emp_no升序排序，并且請注意輸出結果裡面dept_no列是最後一列，以上例子輸出如下: 
SELECT * from employees order by hire_date desc limit 2,1;

複制

思路：假設員工數量未知，那麼需要根據倒序得出倒數第三的員工。

SQL3 查找目前薪水詳情以及部門編号dept_no

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
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`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

複制

select dept_manager.emp_no,salaries.salary,from_date,salaries.to_date,dept_no
from salaries
         inner join dept_manager on salaries.emp_no = dept_manager.emp_no
order by salaries.emp_no asc;

複制

SQL4請你查找所有已經配置設定部門的員工的last_name和first_name以及dept_no，未配置設定的部門的員工不顯示，字段：last_name，first_name，dept_no

drop table if exists  `dept_emp` ; 
drop table if exists  `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`));
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

複制

顯示已配置設定部門的資料，未配置設定部門不顯示，使用inner join即可 
select e.last_name,e.first_name,de.emp_no
from employees as e
         inner join dept_emp as de
on e.emp_no=de.emp_no;

複制

SQL12 擷取每個部門中目前員工薪水最高的相關資訊

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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 `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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');

複制

擷取每個部門中目前員工薪水最高的相關資訊，給出dept_no, emp_no以及其對應的salary，按照部門編号升序排列。

思路：

子查詢中查出最大薪資、企業部門号，在外部查詢中使用where條件限制。

select t.dept_no, s.emp_no, t.maxSalary from 
(
select d.dept_no, max(s.salary) as maxSalary from dept_emp d,salaries s
where d.emp_no = s.emp_no group by d.dept_no
) as t, salaries s, dept_emp d
where 
t.maxSalary = s.salary
and t.dept_no = d.dept_no
and d.emp_no = s.emp_no
order by t.dept_no asc

複制

SQL5 查找所有員工的last_name和first_name以及對應部門編号dept_no

請你查找所有已經配置設定部門的員工的last_name和first_name以及dept_no，也包括暫時沒有配置設定具體部門的員工 
drop table if exists  `dept_emp` ; 
drop table if exists  `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`));
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

複制

使用外部聯結的左聯結。

内聯結，兩邊表同時有對應的資料，即任何一邊缺失資料就不顯示。

左聯結，讀取左邊資料表的全部資料，即便右邊表無對應數。即右表d中dept_no即使為NULL，也會讀取左表e中的全部emp。

select last_name, first_name, dept_no
from employees as e
         left join dept_emp as d on e.emp_no = d.emp_no;

複制

SQL7請你查找薪水記錄超過15次的員工号emp_no以及其對應的記錄次數t

drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');

複制

having用在group by之後 
select emp_no,count(emp_no) as t
from salaries
 group by emp_no having count(emp_no) > 15;

複制

SQL8 找出所有員工目前薪水salary情況

drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,72527,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

複制

distinct:查詢資料時删除重複行 
select distinct salary from salaries order by salary desc;

複制

SQL10 擷取所有非manager的員工emp_no

drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
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`));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');

複制

not in:不在

思路一：子查詢中找到是manager的資料，子查詢作為外部查詢的條件。

select emp_no
from employees
where emp_no not in (select employees.emp_no
                     from employees
                              inner join dept_manager dm on employees.emp_no = dm.emp_no);

複制

思路二：以employees為左表，以dept_manager為右表，使用左連接配接查詢，外連接配接中無法關聯的資料是Null值，其中普通員工dept_no字段是空值，那麼隻需要使用

where

限定dept_no為Null的列即可。

select employees.emp_no from employees left join dept_manager dm on employees.emp_no = dm.emp_no where dept_no is null;

複制

SQL11 擷取所有員工目前的manager

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

複制

擷取所有的員工和員工對應的經理，如果員工本身是經理的話則不顯示。

<> 不等于

思路：根據dept_no關聯dept_emp表和dept_manager表即可找出其對應的manager的資訊，在where條件中使用篩選出本身是經理的資料即可。

select de.emp_no, dm.emp_no
from dept_emp as de
         left join dept_manager dm on de.dept_no = dm.dept_no
where de.emp_no <> dm.emp_no;

複制

SQL15 查找employees表emp_no與last_name的員工資訊

drop table if exists  `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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');

複制

請你查找employees表所有emp_no為奇數，且last_name不為Mary的員工資訊，并按照hire_date逆序排列。

N %2 =1 奇數

N %2 =0 偶數

select *
from employees
where emp_no % 2 = 1
  and last_name <> 'Mary'
order by hire_date desc;

複制

SQL15統計出各個title類型對應的員工薪水對應的平均工資avg

drop table if exists  `salaries` ; 
drop table if exists  titles;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

複制

結果給出title以及平均工資avg，并且以avg升序排序。

group by修飾的列中不能包含與該列一對多的字段，比如emp_no 
select t.title, avg(s.salary)from titles as t         left join salaries s on t.emp_no = s.emp_nogroup by t.titleorder by avg(s.salary) asc;

複制

SQL17 擷取目前薪水第二多的員工的emp_no以及其對應的薪水salary

drop table if exists  `salaries` ; 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`));INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

複制

當資料出現重複時，單純使用limit1，1不正确，如資料中有兩個最高的資料。是以先查詢出次高的工資數是多少 
select emp_no, salaryfrom salarieswhere salary =    (select distinct salary        from salaries        order by salary desc        limit 1,1    );

複制

SQL18 擷取目前薪水第二多的員工的emp_no以及其對應的薪水salary

不使用order by篩選第二個，可以先查詢出最大值，再查詢小于最大值的最大值，即第二高的數值。 
select e2.emp_no, maxSalary, e2.last_name, e2.first_namefrom (         select max(salary) as maxSalary         from employees e                  left join salaries s on e.emp_no = s.emp_no         where s.salary < (select max(salary) from salaries))         as t,     employees e2,     salaries s2where e2.emp_no = s2.emp_no  and s2.salary = maxSalary;

複制

SQL19 查找所有員工的last_name和first_name以及對應的dept_name

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

複制

三張表查詢即可。 
select last_name, first_name, d.dept_name
from employees as emp
         left join dept_emp as de on de.emp_no = emp.emp_no
         left join departments d on de.dept_no = d.dept_no;

複制

SQL21 查找在職員工自入職以來的薪水漲幅情況

MySQL減法，s1-s2。

思路：查詢出入職薪資和現在的薪資做減法

牛客網資料庫實戰題解題思路及答案
select nowSalary.emp_no, nowSalary.salary - startSalary.salary as growth
from (select e.emp_no, e.hire_date, salary
      from employees as e
               left join salaries s on e.emp_no = s.emp_no
      where s.to_date = '9999-01-01') as nowSalary
         inner join
     (select e.emp_no, e.hire_date, salary
      from employees as e
               left join salaries s on e.emp_no = s.emp_no
      where hire_date = s.from_date) as startSalary
     on nowSalary.emp_no = startSalary.emp_no
order by growth asc;

複制

SQL22 統計各個部門的工資記錄數

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 `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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

複制

請你統計各個部門的工資記錄數，給出部門編碼dept_no、部門名稱dept_name以及部門在salaries表裡面有多少條記錄sum，按照dept_no升序排序。

select de.dept_no,dep.dept_name,count(salary)from departments as dep         left join dept_emp as de on de.dept_no = dep.dept_no        left join salaries as s on s.emp_no=de.emp_nogroup by dep.dept_no order by dept_no ASC;

複制

SQL23 對所有員工的薪水按照salary降序進行1-N的排名

本題的主要思想是複用salaries表進行比較排名，具體思路如下：

1、從兩張相同的salaries表（分别為s1與s2）進行對比分析，先将兩表限定條件設為to_date =

‘9999-01-01’，挑選出目前所有員工的薪水情況。

2、本題的精髓在于 s1.salary <=

s2.salary，意思是在輸出s1.salary的情況下，有多少個s2.salary大于等于s1.salary，比如當s1.salary=94409時，有3個s2.salary（分别為94692,94409,94409）大于等于它，但由于94409重複，利用COUNT(DISTINCT

s2.salary)去重可得工資為94409的rank等于2。其餘排名以此類推。

3、千萬不要忘了GROUP BY

s1.emp_no，否則輸出的記錄隻有一條（可能是第一條或者最後一條，根據不同的資料庫而定），因為用了合計函數COUNT()

4、最後先以 s1.salary 逆序排列，再以 s1.emp_no 順序排列輸出結果

drop table if exists  `salaries` ; 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`));INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');

複制

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank``FROM salaries AS s1, salaries AS s2``WHERE s1.to_date = ``'9999-01-01'`  `AND s2.to_date = ``'9999-01-01'` `AND s1.salary <= s2.salary``GROUP BY s1.emp_no``ORDER BY s1.salary DESC, s1.emp_no ASC

複制

SQL24 擷取所有非manager員工目前的薪水情況

drop table if exists  `dept_emp` ; drop table if exists  `dept_manager` ; drop table if exists  `employees` ; drop table if exists  `salaries` ; 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 `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`));INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03');INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

複制

根據dept_manager表資料篩選出誰是manager，将其過濾掉即可。

to_date = '9999-01-01’表示在職員工。

select de.dept_no,e.emp_no,s.salary from employees as e left join dept_emp de on e.emp_no = de.emp_noleft join dept_manager dm on de.dept_no = dm.dept_noleft join salaries s on e.emp_no = s.emp_nowhere e.emp_no not in (dm.emp_no)and s.to_date = '9999-01-01';

複制

SQL25 擷取員工其目前的薪水比其manager目前薪水還高的相關資訊

如圖例：

drop table if exists  `dept_emp` ; drop table if exists  `dept_manager` ; drop table if exists  `salaries` ; 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`));INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

複制

查詢非manager員工比manager員工還高的員工薪水，在子查詢中查詢出員工的manager及其薪資即可，where條件中就是非manager員工和非manager員工比其manager薪資還高的員工 
select de.emp_no, t.emp_no as manager_no, s.salary as emp_salary, t.salary as manager_salaryfrom dept_emp as de         left join salaries s on de.emp_no = s.emp_no        ,     (select salary, s.emp_no, dept_no      from dept_manager as de               left join salaries s on de.emp_no = s.emp_no) twhere de.dept_no = t.dept_no  and s.salary > t.salary  and s.to_date = '9999-01-01';

複制

SQL26 彙總各個部門目前員工的title類型的配置設定數目

這個題。老子不會

SQL28 查找描述資訊(film.description)中包含robot的電影對應的分類名稱(category.name)以及電影數目(count(film.film_id))，而且還需要該分類包含電影總數量(count(film_category.category_id))>=5部

drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO film VALUES(4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
INSERT INTO film VALUES(5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
INSERT INTO film VALUES(6,'AGENT TRUMAN','A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
INSERT INTO film VALUES(7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
INSERT INTO film VALUES(8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
INSERT INTO film VALUES(9,'ALABAMA DEVIL','A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat');
INSERT INTO film VALUES(10,'ALADDIN CALENDAR','A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO category VALUES(12,'Music','2006-02-14 20:46:27');
INSERT INTO category VALUES(13,'New','2006-02-14 20:46:27');
INSERT INTO category VALUES(14,'Sci-Fi','2006-02-14 20:46:27');
INSERT INTO category VALUES(15,'Sports','2006-02-14 20:46:27');
INSERT INTO category VALUES(16,'Travel','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(4,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(5,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(6,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(7,5,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(8,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(9,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(10,15,'2006-02-14 21:07:09');

複制

子查詢中得到分類包含電影總數量(count(film_category.category_id))>=5部的分類id，外部的分類id in子查詢即可 
select c.name, count(fc.film_id)
from film as f
         inner join film_category fc on f.film_id = fc.film_id
         inner join category c on fc.category_id = c.category_id
where f.description like '%robot%'
  and fc.category_id in (select category_id
                         from film_category
                         group by category_id
                         having count(film_id >= 5))
group by fc.category_id;

複制

SQL29 使用join查詢方式找出沒有分類的電影id以及名稱

drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');

複制

以film為基表，在左連接配接中，未比對的列是Null，即實際問題中的沒有分類的電影。 
select film.film_id,film.titleFROM film         left JOIN film_category fc on film.film_id = fc.film_id         left JOIN category c on fc.category_id = c.category_idwhere fc.category_id is null;

複制

SQL30 使用子查詢的方式找出屬于Action分類的所有電影對應的title,description

drop table if exists   film ;drop table if exists  category  ; drop table if exists  film_category  ; CREATE TABLE IF NOT EXISTS film (  film_id smallint(5)  NOT NULL DEFAULT '0',  title varchar(255) NOT NULL,  description text,  PRIMARY KEY (film_id));CREATE TABLE category  (   category_id  tinyint(3)  NOT NULL ,   name  varchar(25) NOT NULL, `last_update` timestamp,  PRIMARY KEY ( category_id ));CREATE TABLE film_category  (   film_id  smallint(5)  NOT NULL,   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

複制

子查詢中查詢出Action分類的id即可 
select  film.title,description
from film
         inner join film_category fc on film.film_id = fc.film_id
where category_id = (select category_id
                     from category
                     where name = 'Action');

複制

SQL32 将employees表的所有員工的last_name和first_name拼接起來作為Name

drop table if exists  `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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

複制

concat：多個字元串連接配接成一個字元串

文法：concat(str1str2,…)

在MySQL中拼接空格隻需要

' '

 即可 
select concat(last_name, ' ',first_name) as Name from employees;

複制

SQL33 建立一個actor表，包含如下列資訊

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-uklUGhaN-1628428302800)(https://i.loli.net/2021/07/31/YpZJy54GV3Hejsz.png)]

新增一張表之前，不排除資料庫中含有與新增表重名的資料庫，是以需要先進行删除 
DROP TABLE if EXISTS `actor`;
CREATE TABLE `actor`(
    `actor_id` smallint(5) NOT NULL COMMENT '主鍵id',
    `first_name` varchar(45) NOT NULL COMMENT '名字',
    `last_name` varchar(45) NOT NULL COMMENT '姓氏',
    `last_update` date	NOT NULL COMMENT '日期',
    PRIMARY KEY (`actor_id`)
);

複制

SQL34 批量插入資料

不同行的資料間使用逗号連接配接。 
insert into actor (actor_id, first_name, last_name, last_update)VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),       (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

複制

SQL35 批量插入資料，不使用replace操作

drop table if exists actor;CREATE TABLE actor (   actor_id  smallint(5)  NOT NULL PRIMARY KEY,   first_name  varchar(45) NOT NULL,   last_name  varchar(45) NOT NULL,   last_update  DATETIME NOT NULL);insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

複制

insert into表示插入資料，資料庫會檢查主鍵，如果出現重複會報錯；

replace into表示插入替換資料，需求表中有PrimaryKey，或者unique索引，如果資料庫已經存在資料，則用新資料替換，如果沒有資料效果則和insert into一樣；

insert ignore表示，如果中已經存在相同的記錄，則忽略目前新資料；

insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

複制

SQL36 建立一個actor_name表

MySQL三種建表方式：

正常建立

create table if not exists 目标表

複制表格

create 目标表 like 來源表

将table1的部分拿來建立table2

create table if not exists actor_name

(

first_name varchar(45) not null,

last_name varchar(45) not null

)

select first_name,last_name

from actor

create table if not exists actor_name(first_name varchar(45) not null,last_name varchar(45) not null)select first_name,last_namefrom actor

複制

SQL38 針對actor表建立視圖actor_name_view

針對actor表建立視圖actor_name_view，隻包含first_name以及last_name兩列，并對這兩列重新命名，first_name為first_name_v，last_name修改為last_name_v。

建立視圖View文法

CREATE VIEW <視圖名> AS <SELECT語句>

  • <視圖名>

     ：指定視圖的名稱。該名稱在資料庫中必須是唯一的，不能與其他表或視圖同名。

  • <SELECT語句>

     ：指定建立視圖的 SELECT 語句，可用于查詢多個基礎表或源視圖。

對于建立視圖中的 SELECT 語句的指定存在以下限制：

  • 使用者除了擁有 CREATE VIEW 權限外，還具有操作中涉及的基礎表和其他視圖的相關權限。
  • SELECT 語句不能引用系統或使用者變量。
  • SELECT 語句不能包含 FROM 子句中的子查詢。
  • SELECT 語句不能引用預處理語句參數。

SQL39 針對上面的salaries表emp_no字段建立索引idx_emp_no

強制走索引：

select * from table force index(index_name);

select emp_no, salary, from_date, to_date
from salaries force index (idx_emp_no)
where emp_no = 10005;

複制

SQL40 在last_update後面新增加一列名字為create_date

#原表
drop table if exists actor;
CREATE TABLE  actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  datetime NOT NULL);

複制

修改表文法：

ALTER TABLE table_name add column column_name type not null default ‘xxx’;

alter table actor add column create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00';

複制

SQL41 構造一個觸發器audit_log

構造一個觸發器audit_log，在向employees_test表中插入一條資料的時候，觸發插入相關的資料到audit中。

在MySQL中，建立觸發器文法如下：

CREATE TRIGGER trigger_name

trigger_time trigger_event ON tbl_name

FOR EACH ROW

trigger_stmt

其中：

  • trigger_name：辨別觸發器名稱，使用者自行指定；
  • trigger_time：辨別觸發時機，取值為 BEFORE 或 AFTER；
  • trigger_event：辨別觸發事件，取值為 INSERT、UPDATE 或 DELETE；
  • tbl_name：辨別建立觸發器的表名，即在哪張表上建立觸發器；
  • trigger_stmt：觸發器程式體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句，每條語句結束要分号結尾。 
create trigger audit_log
after insert on employees_test
for each row
begin
    insert into audit values(new.id,new.name);
end

複制

SQL42 删除emp_no重複的記錄，隻保留最小的id對應的記錄。

MySQL中不允許在子查詢的同時删除表資料（不能一邊查一邊把查的表删了）

删除的是一個範圍，子查詢中查出非最小的即可

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

複制

SQL43 将所有to_date為9999-01-01的全部更新為NULL

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

複制

update文法：·

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

修改多列使用逗号分隔

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

複制

SQL44 将id=5以及emp_no=10001的行資料替換成id=5以及emp_no=10005

replace用在update中的文法

update table set column = replace(column_name,原始值，目标值)

UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;

複制

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

複制

MySQL修改表名：

alter table rename to/as new_tablename;

alter table titles_test rename to titles_2017;

複制

SQL46 在audit表上建立外鍵限制，其emp_no對應employees_test表的主鍵id

#在audit表上建立外鍵限制，其emp_no對應employees_test表的主鍵id。
#(以下2個表已經建立了)
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
);

複制

建立外鍵文法：

alter table base_table add foreign key (column) references associated_table(column2);

alter table audit add foreign key (EMP_no) references employees_test(id);

複制

上一篇: PyTorch中使用 None、unsqueeze()、squeeze() 改變 tensor 的次元
下一篇: DOM的innerHTML