- 题目数量:查找/计算类(38)、更改/替换类(4)、插入类(2)、删除类(1)、创建类(6)
- 总结内容:常用API(12)、常见概念/问题(10)
文章目录
- 一、查找 / 计算
- 二、更改 / 替换
- 三、插入
- 四、删除
- 五、创建
- 🚩总结
- 常用API总结
- (1)3种concat拼接函数——concat() / concat_ws() / group_concat()
- (2)年/月筛选——YEAR() / MONTH()
- (3)字符串/对应字段长度——length() / char_length()
- (4)替换字符串/字段某字符——replace()
- (5)截取字段的某段字符——LEFT() / RIGHT()
- (6)三种不同效果的插入——insert into() / replace into() / insert ignore into()
- (7)3种创建表的方式——create ... if not exists(+select ... from) / like
- (8)添加:主键 、 唯一索引、普通索引、全文索引——PRIMARY KEY / UNIQUE INDEX / INDEX / FULLTEXT
- (9)设置默认值——default
- (10)3种排序函数——row_number() / dense_rank() / rank()
- (11)日期格式函数——DATE_FORMAT()
- (12)不同取值不同操作——case...end
- Q1:内连接、外连接、交叉连接
- Q2:"不等于"的3种表示——<>、!=、is not
- Q3:多个取值都可以时的写法——in(.. , .. , ..)
- Q4:MySQL不允许边查边删的解决措施?)——重命名表格
- Q5:2种引号写法——`"'"`、`'\''`
- Q6:强制索引 force index() 的作用——指定索引
- Q7:触发器
- Q8:如何设置外键约束?
- Q9:多条件分组 / 排序?——逗号隔开
- Q10:where和having子句设置条件的区别?——where指定分组之前,having子句指定分组之后
一、查找 / 计算
-
查找某数字列最大值对应的行数据(可能不止1列)
select * from Xtable where Xcol = (select max(Xcol) from Xtable);
-
寻找所有员工的薪水(列)情况,并去重、降序排列
- 方法1(推荐,group by去重效率高)
select salary from salaries where to_date = '9999-01-01' /* 注意单引号*/ group by salary /* group by:表示根据某列进行分组(即判断种类,去重)*/ order by salary desc /* order by ... desc 降序 */
- 方法2(不推荐,distinct去重效率低)
select distinct(salary) from salaries /* distinct去重 */ where to_date = '9999-01-01' order by salary desc; /* order by ... desc 降序 */
-
寻找number列中出现次数>=3的所有值
select number from grade group by number having count(*) >= 3 /* having...相当于修饰语,数量>=3的所有number */
-
查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
SELECT * from employees where emp_no % 2 = 1and /* and不能省 */ last_name != 'Mary' /* 不相等有三种写法:<>、!=、IS NOT */ ORDER BY hire_date desc;
-
查询各个岗位分数的平均数,并且按照分数降序排序,结果保留小数点后面3位(3位之后四舍五入):
/* 将job中同一类的通过AVG求均值,保留3位小数,并将该列命名为avg */ SELECT job, ROUND(AVG(score), 3) AS avg FROM grade GROUP BY job /* 最终肯定是按工作分成三类 */ ORDER BY avg DESC;
-
有2个表。employees 记录员工信息,dept_emp 记录员工部门。查找所有已经分配部门的员工的last_name和first_name以及dept_no,未分配的部门的员工不显示。
- 方法一:隐式内连接
SELECT e.last_name,e.first_name,d.dept_no FROM employees AS e,dept_emp as d WHERE e.emp_no = d.emp_no
- 方法二:左连接
select last_name,first_name,dept_no from employees e left join dept_emp d on e.emp_no=d.emp_no where d.emp_no is not null;
- 方法三:右连接
select e.last_name,e.first_name,d.dept_no from employees e right join dept_emp d on e.emp_no = d.emp_no;
-
🌈写出一个sql语句查询每个用户最近一天登录的日子,并且按照user_id升序排序
SELECT user_id,max(date) as d from login GROUP by user_id ORDER by user_id;
-
查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
select emp_no,count(emp_no) as t from salaries group by emp_no having t > 15; /* 这里就可以简写为t */
-
【2表连接】有一个员工表和部门领导表(记录员工的部门,有的没有),找出所有非部门领导的员工emp_no
- 🚩方法一:左连接,比较妙
SELECT e.emp_no FROM employees e LEFT JOIN dept_manager d ON e.emp_no = d.emp_no /* 左连接显示坐标所有行(即所有员工)*/ WHERE dept_no IS NULL /* 但条件是要求部门为空,故只剩下了无部门那个人 */
- 方法二:not in
SELECT emp_no FROM employees WHERE emp_no NOT IN (select emp_no from dept_manager)
-
【排序查找】查找employees里入职员工时间排名倒数第三的员工所有信息
SELECT * FROM employees ORDER BY hire_date DESC limit 2,1;
-
【列拼接】将employees表的所有员工的last_name和first_name拼接起来作为Name
SELECT concat_ws(' ', last_name, first_name) Name from employees; /* ws:with separator,带分隔符 */
-
【多取值写法】查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单,并且按照order_info的id升序排序
SELECT * FROM order_info WHERE status = 'completed' and date>'2025-10-15' and product_name in ('Java','Python','C++') /* 多个取值都可以的写法:in (..,..,..) */ ORDER BY id
-
【筛选某年】查询在2025年内投递简历的岗位和数量,并且按数量降序排序
/*这道题包含了一个隐含条件,就是得把2025年每个岗位的所有投递加起来 */ SELECT job,sum(num) as cnt FROM resume_info WHERE date < '2026-01-01' and date > '2024-12-31' /* 筛选2025的简便写法:where YEAR(date) = '2025' */ GROUP BY job /* 须按工作分组 */ ORDER BY cnt desc;
-
【左连接】有两个表,一个储存基本信息,一个储存人的任务情况。找到每个人的任务情况,并且输出出来,没有任务的也要输出,而且输出结果按照person的id升序排序
SELECT p.id, p.name, t.content /* 没有任务的也要输出,所以连接task表的时候要使用左连接,且同样适用于左边对右边为1对多的情况 */ FROM person p left JOIN task t on p.id = t.person_id /* 注意:不能是where */ ORDER BY id;
-
【分页查询】分页查询employees表,每5行一页,返回第2页的数据
SELECT * FROM employees LIMIT 5,5;
-
【字符串】查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
/* 1.统计字符串/对应字段长度:char_length('string') / char_length(column_name) 2.替换函数:replace("str","原字符","替换字符") */ SELECT CHAR_LENGTH('10,A,B')-CHAR_LENGTH(REPLACE('10,A,B',',',''))
-
【组内拼接】按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
/* group_concat():将group by产生的同一个分组中的值连接起来,返回一个字符串结果。*/ SELECT dept_no,group_concat(emp_no) as employees from dept_emp GROUP BY dept_no
-
【字符排序查找】查询first_name,并按照first_name最后两个字母升序进行排列
SELECT first_name FROM employees ORDER BY RIGHT(first_name,2); /* RIGHT(col,2):表示从col列字段的右边开始的2个字符 */
-
【数字累加】🚩写出一个SQL查询,如果一个学生知道了自己综合成绩以后,最差是排第几名? 结果按照grade升序排序
/* 🚩方案1:采用窗口函数SUM() OVER():即在..的基础上累加*/ SELECT grade,SUM(number) OVER(ORDER BY grade) FROM class_grade ORDER BY grade; /*🚩方案2:采用自联结 cross join */ SELECT c1.grade,SUM(c2.number) FROM class_grade c1 CROSS JOIN class_grade c2 ON c1.grade >= c2.grade /* ② 所以比较时是c1 >= c2 */ GROUP BY c1.grade /* ① 遍历的是c1的ABCD */ ORDER BY c1.grade ASC; /* 方案3:采用CASE表达式+子查询 */ SELECT grade ,CASE grade WHEN 'A' THEN(SELECT SUM(number) FROM class_grade WHERE grade <='A') WHEN 'B' THEN(SELECT SUM(number) FROM class_grade WHERE grade <='B') WHEN 'C' THEN(SELECT SUM(number) FROM class_grade WHERE grade <='C') WHEN 'D' THEN(SELECT SUM(number) FROM class_grade WHERE grade <='D') ELSE (SELECT SUM(number) FROM class_grade) END AS t_rank FROM class_grade ORDER BY grade;
-
【3表子查询】使用子查询的方式找出属于Action分类的所有电影对应的title,description
/* 方案1:film表和film_category 相连接 */ /* 意思就是:先确定动作片对应的分类id,再看哪些电影id对应这些分类id*/ select f.title,f.description from film f left join film_category fc on f.film_id = fc.film_id /* 动作片对应的分类id */ where fc.category_id = ( select category_id from category where name = 'Action' ) /* 方案2:链接表film_category和category */ /* 意思就是:先确定那些电影id对应的分类id是动作片的,再去看哪些电影id在这些找出的id里面 */ select title,description from film f where f.film_id in ( select fc.film_id from film_category fc left join category c on fc.category_id = c.category_id where name='Action' )
-
【拼接】将employees表中的所有员工的last_name和first_name通过引号连接起来
/* 2种引号写法:"'"或者'\'' */ SELECT CONCAT_WS("'",last_name,first_name) from employees
-
【排序查找】获取薪水第二多的员工的emp_no以及其对应的薪水salary
SELECT emp_no,salary from salaries WHERE salary = (SELECT salary FROM salaries GROUP BY salary /* 去重 */ ORDER BY salary DESC /* 降序排列 */ LIMIT 1,1) /* limit i,j表示从索引i开始的j个数,i默认从0开始,所以limit 1,1表示第2个数开始的1个数,也就是第2名 */
-
【左连接】查找所有已经分配部门的员工的last_name和first_name以及dept_no,也包括暂时没有分配具体部门的员工
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
-
【左连接】使用join查询方式找出没有分类的电影id以及名称
select f1.film_id, f1.title FROM film f1 left JOIN film_category f2 ON f1.film_id = f2.film_id where f2.category_id IS NULL
-
🚩**【强制索引】针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005,使用强制索引。**
select * from salaries force index (idx_emp_no) /* 括号内是索引名 */ where emp_no = 10005
-
🚩【分组统计】统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg,并且以avg升序排序
select t.title,avg(s.salary) as avs from titles t,salaries s where t.emp_no = s.emp_no group by t.title order by avs
-
【exists查找】使用含有关键字exists查找未分配具体部门的员工的所有信息。
SELECT * FROM employees WHERE NOT EXISTS ( /* 没有in */ /* 所有有部门的员工 */ SELECT emp_no FROM dept_emp WHERE employees.emp_no = dept_emp.emp_no );
-
【连接+条件】获取所有的员工和员工对应的经理,如果员工本身是经理的话则不显示
SELECT d1.emp_no,d2.emp_no AS manager FROM dept_emp d1 left JOIN dept_manager d2 ON d1.dept_no = d2.dept_no /* 该条件用于寻找员工对应的经历*/ where d1.emp_no != d2.emp_no /* 该条件用于排除掉员工自己是经理的情况*/
-
【不跳过可重复排序-dense_rank()】输出通过的题目的排名,通过题目个数相同的,排名相同,此时按照id升序排列
/* DENSE_RANK() 为不可跳过可重复排序,即 1 2 2 3 4 */ SELECT id,number,DENSE_RANK() over(order by number desc) FROM passing_number
-
【三表一个连一个情况的查询】查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
/* 做法1:两层left join嵌套*/ select e.last_name,e.first_name,d.dept_name from ( employees e left join dept_emp de /*第1层 */ on e.emp_no = de.emp_no ) /* 第1步:根据员工id找到部门id */ left join departments d/*第2层 */ on de.dept_no=d.dept_no/* 第2步:根据部门id找到部门名称 */ /* 做法2:先内连接departments表和dept_emp表形成新表m,再左连接employees表和m表 */ SELECT e.last_name,e.first_name,m.dept_name FROM employees e LEFT JOIN ( SELECT d1.emp_no,d2.dept_name FROM dept_emp d1 INNER JOIN departments d2 ON d1.dept_no = d2.dept_no ) as m ON e.emp_no = m.emp_no
-
【双表连接】写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序
SELECT g.id,g.job,g.score FROM grade g INNER JOIN ( /* 内连接 */ SELECT job,avg(score) as score FROM grade GROUP BY job ) as m ON g.score > m.score WHERE g.job = m.job
-
【多条件分组、排序】写出SQL语句查询在2025年内投递简历的每个岗位,每一个月内收到简历的数量,并且按先按月份降序排序,再按简历数目降序排序
select job,DATE_FORMAT(date, '%Y-%m') as mon,sum(num) as cnt /* 注意:不能用 MONTH(date),它求出来的月只有一个数字,不是标准的月份08 */ from resume_info where YEAR(date) = 2025 /* 也可写作date like '2025%'*/ group by job,mon /*多条件分组,一次group by,逗号隔开*/ order by mon desc,cnt desc; /*多条件排序,一次order by,逗号隔开(分先后顺序)*/
-
select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') /* 还有JS等课程 */ group by user_id /* where指定分组之前数据行的条件 having子句用来指定分组之后条件 */ having count(user_id) > 1 order by user_id
-
【三表连接】统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum,按照dept_no升序排序
SELECT d1.dept_no,d1.dept_name,count(s.salary) /*统计数量用count*/ FROM departments d1 LEFT JOIN dept_emp d2 ON d1.dept_no = d2.dept_no LEFT JOIN salaries s ON d2.emp_no = s.emp_no GROUP BY d1.dept_no ORDER BY d1.dept_no
-
查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary
题目没交代清楚,方法1是去除所有最大值和最小值,方法2是各去除一条/* 方法1:子查询 */ SELECT avg(salary) from salaries WHERE to_date = '9999-01-01' and salary not IN (select max(salary) from salaries WHERE to_date = '9999-01-01') and salary not IN (select min(salary) from salaries WHERE to_date = '9999-01-01') /* 方法2:聚合函数 */ SELECT (sum(salary)-max(salary)-min(salary))/(count(1)-2) /* count(1) 代表字段总长度 */ from salaries where to_date = '9999-01-01';
-
【case处理多种情况】给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01’
SELECT e1.emp_no,e1.first_name,e1.last_name,e2.btype,s.salary, case when e2.btype=1 then s.salary*0.1 when e2.btype=2 then s.salary*0.2 else s.salary*0.3 end bonus /* case...end 才是完整结构 */ FROM employees e1,emp_bonus e2,salaries s WHERE e1.emp_no = e2.emp_no and s.emp_no = e1.emp_no AND s.to_date='9999-01-01';
-
【计算累加和】按照salary,计算累计和running_total,其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和,其他以此类推。
SELECT emp_no,salary,sum(salary) over(order by emp_no) as running_total /*计算顺序为emp_no升序的顺序*/ FROM salaries WHERE to_date = '9999-01-01'
-
【筛选奇数项】对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
/* 我的做法 */ SELECT e.first_name FROM employees e inner join( /* 名字和对应排序 */ SELECT first_name,ROW_NUMBER() over (ORDER BY first_name) num FROM employees ) m on e.first_name = m.first_name WHERE m.num % 2=1
二、更改 / 替换
-
更改表名titles_test为titles_2017
ALTER TABLE titles_test RENAME TO titles_2017;
-
将表titles_test 的id=5的emp_no列的10001更改为10005
UPDATE titles_test SET emp_no = REPLACE(emp_no, 10001, 10005) WHERE id = 5;
-
将所有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';
-
将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%,emp_bonus里面的emp_no都是当前获奖的所有员工
UPDATE salaries SET salary = salary*1.1 WHERE to_date='9999-01-01' /* 获奖员工 */ and salaries.emp_no in (SELECT emp_no FROM emp_bonus)
三、插入
-
插入指定的2条数据,且不能有2个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');
-
对于表actor插入指定数据,如果数据已经存在(主键存在),请忽略(不支持使用replace操作)
INSERT IGNORE INTO actor /* insert ignore表示,如果中已经存在相同的记录,则忽略当前新数据;*/ VALUE('3','ED','CHASE','2006-02-15 12:34:33')
四、删除
-
删除emp_no重复的记录,只保留每种emp_no里最小id对应的记录。
/* 问 题:MySQL中不允许在子查询的同时删除表数据,即不能一边查一边删 解决方法:把查询的结果重命名后删除 */ /* ③ 删除不在满足要求的id里面的其他数据 */ DELETE FROM titles_test WHERE id not in ( /* ② 把①的所有结果重命名为a */ select * FROM ( /* ① 以下2行:找到emp_no分组之后最小的得ID */ SELECT MIN(id) FROM titles_test GROUP BY emp_no ) a )
五、创建
-
【新建视图view】针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v
/* 写法1:在select前面直接添加新的视图字段名 */ create view actor_name_view (first_name_v,last_name_v) as select first_name ,last_name from actor /* 写法2:在select后面将列重命名为视图的字段名 */ CREATE VIEW actor_name_view AS select first_name as first_name_v,last_name as last_name_v from actor
-
【使用其他表的一部分创建新表】创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表
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
-
【创建索引】对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
CREATE UNIQUE INDEX uniq_idx_firstname on actor (first_name); CREATE INDEX idx_lastname ON actor (last_name);
-
【创建新列】在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’2020-10-01 00:00:00’
ALTER TABLE actor ADD COLUMN create_date /* DEFAULT,用于设置默认值,after last_update可不加 */ datetime NOT NULL DEFAULT '2020-10-01 00:00:00' after last_update
-
【创建触发器-insert型】构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
create trigger audit_log after insert on employees_test /* ① 触发条件:employees_test已经插入数据的时候,注意是on */ /* ② 触发操作 */ for each row begin /* 每插入1条,就...,begin...end 之间是触发的操作 */ /* 必须加分号!!! insert型触发器中,new代表将要/已经插入的数据 */ insert into audit values(new.id,new.name); end
-
【创建表】根据指定内容创建表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PvNhOz3l-1630487065660)(C:\Users\0\AppData\Roaming\Typora\typora-user-images\image-20210829220021536.png)]
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 date not null )
🚩总结
常用API总结
(1)3种concat拼接函数——concat() / concat_ws() / group_concat()
-
concat(str1,str2)
:不带分隔符 -
concat_ws('分隔符',str1,str2)
:带分割符 -
group_concat(col) ... group by XXX
:根据group by的分组结果将每组内col列的值拼接起来,返回一个字符串例子:按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
/* group_concat():将group by产生的同一个分组中的值连接起来,返回一个字符串结果。*/ SELECT dept_no,group_concat(emp_no) as employees from dept_emp GROUP BY dept_no
(2)年/月筛选——YEAR() / MONTH()
- 某一年:
WHERE YEAR(date) = '2025'
- 某一月:
WHERE MONTH(date) = '07'
(3)字符串/对应字段长度——length() / char_length()
区别:length种1个中文会算作长度3,char_length中1个中文算作长度1;
🚩推荐使用
char_length
char_length('string') / char_length(column_name)
(4)替换字符串/字段某字符——replace()
replace("str","原字符","替换字符")
(5)截取字段的某段字符——LEFT() / RIGHT()
LEFT(col,n)
:表示从col列字段的左边开始的n个字符RIGHT(col,n)
:表示从col列字段的右边开始的n个字符
(6)三种不同效果的插入——insert into() / replace into() / insert ignore into()
insert into()
:若输入数据(主键)存在,则报错replace into()
:若输入数据(主键)存在,则使用新数据替换insert ignore into()
:若输入数据(主键)存在,则忽略新数据
(7)3种创建表的方式——create … if not exists(+select … from) / like
-
直接创建:
create table if not exists 目标表();
-
复制创建:
create 目标表 like 来源表
-
使用其他表的一部分创建
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
(8)添加:主键 、 唯一索引、普通索引、全文索引——PRIMARY KEY / UNIQUE INDEX / INDEX / FULLTEXT
CREATE PRIMARY KEY / UNIQUE INDEX / INDEX / FULLTEXT 索引名 on 表(列);
(9)设置默认值——default
例子:在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’2020-10-01 00:00:00’
ALTER TABLE actor ADD COLUMN create_date
/* DEFAULT,用于设置默认值,after last_update可不加 */
datetime NOT NULL DEFAULT '2020-10-01 00:00:00' after last_update
(10)3种排序函数——row_number() / dense_rank() / rank()
row_number()
:对应唯一排序:1、2、3、4dense_rank()
:对应相同次序可重复,但不跳过下一个次序值:1、2、2、3rank()
:对应相同次序可重复,并且跳过下一个次序值:1、2、2、4
用法:rank() over(order by XXX)
(11)日期格式函数——DATE_FORMAT()
DATE_FORMAT(col,格式)
函数:用于以不同的格式显示日期/时间数据。例如DATE_FORMAT(date,'%Y-%m'); //只显示年月
常用格式:
%Y
--年,4 位
%m
--月,数值(00-12)
%M
--月名
%k
--小时(0-23)
(12)不同取值不同操作——case…end
case when e2.btype=1 then s.salary*0.1
when e2.btype=2 then s.salary*0.2
else s.salary*0.3
end as bonus /* case...end 才是完整结构 */
Q1:内连接、外连接、交叉连接
-
内联接(
inner join
):(典型的联接运算,使用像 = 或 <> 之类的比较运算符)。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students和courses表中学生标识号相同的所有行。 -
外联接(
left / right / full join
): 外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:- 1)
LEFT JOIN
或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。 - 2)
RIGHT JOIN
或RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。 - 3)
FULL JOIN
或FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
- 1)
-
交叉联接 / 笛卡尔积(
cross join
): 交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。
Q2:"不等于"的3种表示——<>、!=、is not
<>
!=
IS NOT
Q3:多个取值都可以时的写法——in(… , … , …)
XXX in ('Java','Python','C++') /* 多个取值都可以的写法:in (..,..,..) */
Q4:MySQL不允许边查边删的解决措施?)——重命名表格
把查询的结果重命名后再进行删除
例子:删除emp_no重复的记录,只保留每种emp_no里最小id对应的记录。/*
/* ③ 删除不在满足要求的id里面的其他数据 */
DELETE FROM titles_test WHERE id not in (
/* ② 把①的所有结果重命名为a */
select * FROM (
/* ① 以下2行:找到emp_no分组之后最小的得ID */
SELECT MIN(id) FROM titles_test
GROUP BY emp_no
) a
)
Q5:2种引号写法——"'"
、'\''
"'"
或者'\''
Q6:强制索引 force index() 的作用——指定索引
-
问题:使用 sql 语句查询数据的时候,如果表里有好几个索引,mysql 优化器会自己判断使用哪个索引进行搜索,但并不一定使用的是最优索引
-
解决:在 mysql 中,可以添加
force index(字段)
可以强制 sql 查询时使用指定索引
Q7:触发器
MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中触发了触发器的那一行数据。
- 在
INSERT
型触发器中,NEW 用来表示将要 / 已经插入的新数据; - 在
UPDATE
型触发器中,OLD 用来表示将要 / 已经被修改的原数据,NEW 用来表示将要 / 已经修改为的新数据; - 在
DELETE
型触发器中,OLD 用来表示将要 / 已经被删除的原数据;
- 将要—BEFORE,已经—AFTER
- 字段数据表示方式:
NEW.columnName
Q8:如何设置外键约束?
一个表中的 FOREIGN KEY
指向另一个表中的 PRIMARY KEY
。
/* 添加外键模板 */
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY **(**<列名>**)**
REFERENCES <关联表>**(**关联列**)**
Q9:多条件分组 / 排序?——逗号隔开
group by job,mon
/多条件分组,一次group by,逗号隔开/order by mon desc,cnt desc;
/多条件排序,一次order by,逗号隔开(分先后顺序)/
Q10:where和having子句设置条件的区别?——where指定分组之前,having子句指定分组之后
-
区别:
where
指定分组之前数据行的条件,having子句用来指定分组之后条件 -
例子:写出一个sql语句查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id,并且按照user_id升序排序
select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') /* 还有JS等课程 */ group by user_id /* where指定分组之前数据行的条件 having子句用来指定分组之后条件 */ having count(user_id) > 1 order by user_id