牛客网数据库实战题解题思路及答案

关忆北.

发布于 2021-12-07 16:52:49

4990

发布于 2021-12-07 16:52:49

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

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-07-27 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

云数据库 SQL Server

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

数据库

云数据库 SQL Server

牛客网数据库实战题解题思路及答案

牛客网数据库实战题解题思路及答案

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐