MySQL

入门题/简单题

tips：

• 在子查询和自连接的时候必须使用别名：https://blog.csdn.net/weixin_30954879/article/details/113348159
• 子查询用where film_id in (select fc.film_id from)

1.查询最晚入职员工（limit分页）

建表语句如下

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 * from employees 
    order by hire_date desc 
    limit 1;
*/

/* 使用limit 与 offset关键字  */
/*
select * from employees 
    order by hire_date desc 
    limit 1 offset 0;
*/

/* 使用limit关键字 从第0条记录 向后读取一个，也就是第一条记录 */
/*
select * from employees 
    order by hire_date desc 
    limit 0,1;
*/

/* 使用子查询，最后一天的时间有多个员工信息 */
/*
select * from employees
    where hire_date = (select max(hire_date) from employees);
*/

limit a offset b 表示限制a个输出，b表示从第一个开始需要跳过的数量
limit a,b 表示从第a个开始，往后输出b个。（a从0开始）

2.多表查询

SELECT e.last_name,e.first_name,d.dept_no 
FROM employees AS e
INNER JOIN dept_emp AS d
ON e.emp_no=d.emp_no;

employees表中的dept_no有可能为NULL；但dep_emp表中的dept_no一定不为NULL，所以使用内部联结，去除dept_no为NULL的employees。
联结时，如果需要把dept_no=NULL的数据也列出，要使用外部联结 LEFT/RIGHT OUTER JOIN ... ON ...

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

注意：多表查询用join联结两个表 语法是from employees join dept_emp on 联结条件
内连接 inner join是任意一个表中为null就不显示；
左外联结left join 指的是左边的表存在的行全部都要有。

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

上面这个查询中可能有员工未被分配部门，那么在dept_no就为Null。值得注意的是，对一个属性是否为空的判断不用=，而用is NULL或者is not NULL;
两种连接图示

IN的多表查询
题目：获取所有非manager的员工的emp_no
方法1：NOT IN+子查询

select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);

方法2：LEFT JOIN左连接+IS NULL

select e.emp_no from employees e
left join dept_manager d
on e.emp_no=d.emp_no
where dept_no is NULL;

3.单表查询中group by ... having

详情参考：深入浅析SQL中group用法
group by 和having的使用

通常的执行步骤如下（不是全都必须）
1、执行where子句查找符合条件的数据；
2、使用group by 子句对数据进行分组；
3、对group by 子句形成的组运行聚集函数计算每一组的值；
4、最后用having 子句去掉不符合条件的组。
having 子句中的每一个元素也必须出现在select列表中。

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
按工资降序排列 并且相同工资只显示一个：

select salary from salaries
where to_date="9999-01-01"
group by salary
order by salary desc;
另一种方法，用distinct 但是效率不及group by
select distinct salary from salaries 
where to_date='9999-01-01'
order by salary desc;

在这里之所以限制to_date主要是因为它可以表示当前这个时间。

4. 函数使用(包括条件函数和聚合函数）

concat使用

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)

select concat(last_name," ",first_name) Name
from employees;

select last_name||" "||first_name as name  from employees

group_concat

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

select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;

substr

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。
其中

• X: str; Y:start; Z:end(可缺省）
• 注意第一个字符的位置为1，而不为0，取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

  select first_name from employees order by substr(first_name,-2);

length && replace使用

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

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

round使用

保留三位小数（默认四位）
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):

select job,round(avg(score),3) as avg from grade
group by job
order by avg desc;

year使用

where(year)=2025
请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序

select job,sum(num) as cnt 
from resume_info
where date between '2025-01-01' and '2025-12-31' //-- 这里可改成where year(date)=2025
group by job
order by cnt desc;

注释：mysql的between包含边界的，如下：between...prar1 and prar2 相当于>=prar1 and <=prar2;

sum使用

有一个用户表user，一个积分表grade_info,请你写一个SQL查找积分增加最高的用户的名字，以及他的总积分是多少:
理清思路；from 后面加表，要起别名。

step 1:表一：求分数总和，id分组,因为最大的只有一个，降序求limit 1

SELECT SUM(grade_num) AS grade_num , user_id
FROM grade_info
GROUP BY user_id
order by grade_num desc limit 1

step 2: 两个表连接

SELECT u.name, bbb.grade_num AS  grade_num
FROM (
表1
) bbb
INNER JOIN user AS u
ON u.id = bbb.user_id

综上：

SELECT u.name, bbb.grade_num AS  grade_num
FROM (
    SELECT SUM(grade_num) AS grade_num , user_id
FROM grade_info
GROUP BY user_id
order by grade_num desc limit 1
) bbb
INNER JOIN user AS u
ON u.id = bbb.user_id

进阶用法：使用窗口函数sum()over()来解答

select u.name, g.grade
from
(select user_id, sum(grade_num)over(partition by user_id) grade
from grade_info
order by grade desc
limit 1)g
join
user as u
on g.user_id = u.id

参考：闲话Mysql之sum() over()与sum() group by 有啥不同~

5.表的创建/更新/插入

参考菜鸟教程：https://www.runoob.com/mysql/mysql-alter.html
对表的操作有：Create Drop Alter （alter table ...）
对数据的操作有：Insert (into) delete (from) update (set) select

创建表

create table if not exists actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null);

创建数据表时，表名和字段名不需要用引号括起来。

批量插入数据（忽略重复）

请批量对actor表插入两条数据(只能用一条insert)：

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');
注意是values而不是values of
法2：哩用union select
INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'
若不插入主键重复的数据用ignore：
insert ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33');

如果插入的数据是从另一张表中查询出来的可以用：

insert into actor_name  select a.first_name,a.last_name from actor a;//可以不用别名
//或者具体插入哪几个数据项insert into actor_name(..,..) select ....

【重点】如何把一个表中的记录插入另一个表：insert语句中values的部分替换成子查询，并且把values这个词去掉（去不去括号都可）。

删除重复数据

SQL 42) 删除emp_no重复的记录
错误方法）

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no);
MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了）

正确方法）

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT * FROM(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no)a);  
-- 把得出的表重命名那就不是原表了

解题思路
1.按照员工号分组，2.查找每一组最小的员工号，3.将2中的那些员工的所有信息查询出来，4.删除id不在3中的员工的行。

表更新

表更新语句结构：
    UPDATE 表名
    SET 字段=值
    WHERE 过滤条件
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

UPDATE titles_test
SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

replace

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

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

修改表

将titles_test表名修改为titles_2017。
参考链接：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

• ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
• ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
• ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
• ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
• ALTER TABLE 表名 RENAME TO/AS 新表名;
• ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
• ALTER TABLE 表名 CHANGE 列名和定义都可以改变

1. 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'

   alter table actor add (create_date datetime not null default '2020-10-01 00:00:00');

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

   ALTER TABLE audit
   ADD CONSTRAINT FOREIGN KEY (emp_no) //发现没有：add后面的具体的东西加括号
   REFERENCES employees_test(id);/注意references后面有个s

   创建外键语句结构：
   ALTER TABLE <表名>
   ADD CONSTRAINT FOREIGN KEY (<列名>)
   REFERENCES <关联表>（关联列）

6.视图

创建视图

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
方法一：注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor

方法二：直接在视图名的后面用小括号创建视图中的字段名

CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

7.索引

添加索引

1. 添加主键

   ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
   // 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

2. 添加唯一索引

   ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
   // 这条语句创建索引的值必须是唯一的。

3. 添加普通索引

   ALTER TABLE tbl_name ADD INDEX index_name (col_list);
   // 添加普通索引，索引值可出现多次。

4. 添加全文索引

   ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
   // 该语句指定了索引为 FULLTEXT ，用于全文索引。

5. 删除索引

   DROP INDEX index_name ON tbl_name; 
   // 或者
   ALTER TABLE tbl_name DROP INDEX index_name；
   ALTER TABLE tbl_name DROP PRIMARY KEY;

值得注意是: 普通索引时必须要写add index 而其他索引只用写 unique 或者 fulltext即可

使用索引

参考：MYSQL中常用的强制性操作（例如强制索引）
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引:

SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no = 10005

中等/较难题

1.查各个部门最大薪资

获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

此题常见漏洞：1.emp_no直接和group by dept_no一起使用，拿到了最大salary但是存在emp_no取值其实与salary不匹配的问题；2. 先使用group by获得最高salary，再去用最高salary匹配两表返回dept_no,emp_no信息，这存在A部门的最高薪水，等于B部门非最高薪水时，B部门的非最高薪水也会被显示出来。
解法一：（如果同部门有多条同等最大salary，一起显示出来）

select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
group by d.dept_no
)as r,salaries ss,dept_emp dd //这里把每个部门中最高工资查出来，然后与其他表交叉连接，再用三个对比确定唯一一个员工；
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc;
//

解法二：（如果同部门有多条同等最大salary，仅显示一条）

select r.dept_no,r.emp_no,max(r.salary) from (
select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
order by s.salary desc
)as r
group by r.dept_no
order by r.dept_no asc

注明两点：
1.题目忘记写一条信息，按照部门编号排序
2.解法二利用了GROUP BY 默认取非聚合数据的第一条记录，所以先排好序，拿到的emp_no第一条信息，也是与最大salary匹配的
3.解法一中使用多表取值，where筛选条件和内连接，on筛选条件，效果一致，可以替换。效率根据不同表的结构，数据结构而定。

解法2 不对哦(对于mysql而言)。emp_no是非聚合字段，mysql会随机选择一条，并非max_salary对应的emp_no

这里有用到交叉连接，详情参考：
cross join是什么连接
交叉连接（CROSS JOIN）

2.薪水排名第二的员工信息（不能用order by）

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
解法1 max 任意相同工资人数

select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = (select max(salary)
                from salaries
                where salary<(select max(salary) 
                              from salaries 
                             )
                )

第二种 通用型可以求任意第几高，并且可以求多个形同工资

select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from 
     salaries s1
     join
     salaries s2 on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

解法二属实强，但是看不懂，题号为18

COUNT(*).明确的返回数据表中的数据个数,是最准确的

COUNT(列),返回数据表中的数据个数,不统计值为null的字段

COUNT(DISTINCT 字段) 返回数据表中不重复的的数据个数,不统计值为null的字段

3. 统计各个部门的工资记录数(22)

有一个部门表departments简况如下:

有一个，部门员工关系表dept_emp简况如下:

有一个薪水表salaries简况如下:

请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

解答

//方法1：嵌套查询，查出一个dept_no，就进行子查询的到对应COUNT()
SELECT dept_no, dept_name, (SELECT COUNT()
FROM dept_emp AS de
INNER JOIN salaries AS s
ON de.emp_no=s.emp_no
WHERE de.dept_no=d.dept_no) AS sum
FROM departments AS d;

注意：对于这种情形的子查询，一个部门进去，必须只返回一个值，而且只能查询一个列，可以参考26题的错误示范做对比

//方法2：先进行两次内连接，再通过GROUP BY查询
SELECT de.dept_no, de.dept_name, COUNT(*) AS sum
FROM (SELECT *
FROM departments
INNER JOIN dept_emp
ON departments.dept_no=dept_emp.dept_no) AS de
INNER JOIN salaries AS s
ON de.emp_no=s.emp_no
GROUP BY de.dept_no;

注意：这里GROUP BY聚合dept_no,而dept_name看上去不是聚合列(因此似乎不能放进SELECT语句内)，但是事实上，由于dept_no是主键，所以一个dept_no只会对应一个dept_name，所以可以认为dept_name是可聚合的，所以可以放进去。特别要注意的是，如果dept_no不是主键，而是联合主键之一或者别的情况，总之dept_no不能唯一确定dept_name，那么即使在表中看上去dept_no和dept_name是一一对应的，也不能将dept_name放进SELECT中，强行放进去，在mysql中也会报错(当然在题库中不会报错)。
补充：内层的内连接，可以省略SELECT (因为内连接自己会生成临时表)：

SELECT de.dept_no, de.dept_name, COUNT() AS sum
FROM (departments
INNER JOIN dept_emp
ON departments.dept_no=dept_emp.dept_no) AS de
INNER JOIN salaries AS s
ON de.emp_no=s.emp_no
GROUP BY de.dept_no;

补充：还可以进一步省略，直接进行连续内连接：

SELECT d.dept_no, d.dept_name, COUNT(*) AS sum
FROM
(departments AS d
INNER JOIN dept_emp AS de
ON d.dept_no=de.dept_no
INNER JOIN salaries AS s
ON de.emp_no=s.emp_no)
GROUP BY de.dept_no;

注意：连续内连接中一定不要出现WHERE(21题的经验)

方法3：直接三表联查，用WHERE过滤

SELECT d.dept_no, d.dept_name, COUNT(*) AS sum
FROM departments AS d, dept_emp AS de, salaries AS s
WHERE d.dept_no=de.dept_no
AND de.emp_no=s.emp_no
GROUP BY d.dept_no;

备注：三表联查在代码上比进行两次内连接简洁

3.触发器SQL41

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

create trigger audit_log 
after insert on employees_test //注意这里有个on
for each row
begin 
    insert into audit values(new.id,new.name);//注意分号位置
end

在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 包含的多条语句，每条语句结束要分号结尾。

【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。
具体地：

1. 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
2. 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
3. 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

使用方法： NEW.columnName （columnName 为相应数据表某一列名）

4.薪水增加10%

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

//方法一：连接
update salaries as s join emp_bonus as e on s.emp_no=e.emp_no
set salary=salary*1.1
where to_date='9999-01-01'
//方法二：子查询
update salaries
set salary=salary*1.1
where to_date='9999-01-01' 
    and salaries.emp_no in(select emp_no from emp_bonus)

比较：
推荐使用连接查询（JOIN）
连接查询不需要创建+销毁临时表，因此速度比子查询快。

5. 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。

select avg(salary) from salaries 
where to_date='9999-01-01' 
and salary not in (select max(s.salary) from salaries s where to_date='9999-01-01')
and salary not in (select min(s.salary) from salaries s where to_date='9999-01-01');

注意：不能... where salary not in (select max(),min())因为这样是两列会报错。

6.关键字exists

使用含有关键字exists查找未分配具体部门的员工的所有信息。

SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no //是exists不是exists in ；注意与sth not in sth对比
                 FROM dept_emp
                 WHERE employees.emp_no = dept_emp.emp_no);

in和exists区别in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

一直以来认为exists比in效率高的说法是不准确的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

NOT EXISTS,exists的用法跟in不一样，一般都需要和子表进行关联，而且关联时，需要用索引，这样就可以加快速度。

exists 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合。
例如 exist P 表示P不空时为真; not exist P表示p为空时为真。

in表示一个标量和一元关系的关系。
例如：s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时为真.

not in 和not exists的区别如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快.

两者用法上有以下不同:

1、 select * from Table1 where exists(select 1 from Table2 where Table1.a=Table2.a) ;
Table1数据量小而Table2数据量非常大时，Table1<<Table2 时，exists的查询效率高。
2、 select * from Table1 where Table1.a in (select Table2.a from Table2) ;
Table1数据量非常大而Table2数据量小时，Table1>>Table2 时，in的查询效率高。

困难题

查询所有员工自入职起的薪资涨幅（SQL21）