SQL高级查询与预处理语句解析以及实战练习题

一、sql高级查询

1.1 表结构创建

通过sql语句创建5张核心表（班级表、课程表、成绩表、学生表、教师表），并定义字段类型、主键、外键及自增规则，确保表间数据关联的完整性。

-- 1. 班级表（class）：存储班级信息
drop table if exists `class`;
create table `class` (
  `cid` int(11) not null auto_increment, -- 班级id，自增主键
  `caption` varchar(32) not null, -- 班级名称（如“高一1班”）
  primary key (`cid`)
) engine=innodb auto_increment=5 default charset=utf8;

-- 2. 课程表（course）：存储课程信息，关联教师表
drop table if exists `course`;
create table `course` (
  `cid` int(11) not null auto_increment, -- 课程id，自增主键
  `cname` varchar(32) not null, -- 课程名称（如“数学”）
  `teacher_id` int(11) not null, -- 授课教师id，关联教师表的tid
  primary key (`cid`),
  key `fk_course_teacher` (`teacher_id`), -- 为外键创建索引，提升查询效率
  constraint `fk_course_teacher` foreign key (`teacher_id`) references `teacher` (`tid`) -- 外键约束
) engine=innodb auto_increment=5 default charset=utf8;

-- 3. 成绩表（score）：存储学生成绩，关联学生表和课程表
drop table if exists `score`;
create table `score` (
  `sid` int(11) not null auto_increment, -- 成绩记录id，自增主键
  `student_id` int(11) not null, -- 学生id，关联学生表的sid
  `course_id` int(11) not null, -- 课程id，关联课程表的cid
  `num` int(11) not null, -- 分数（如85、92）
  primary key (`sid`),
  key `fk_score_student` (`student_id`), -- 外键索引
  key `fk_score_course` (`course_id`), -- 外键索引
  constraint `fk_score_course` foreign key (`course_id`) references `course` (`cid`), -- 外键约束（关联课程）
  constraint `fk_score_student` foreign key (`student_id`) references `student` (`sid`) -- 外键约束（关联学生）
) engine=innodb auto_increment=53 default charset=utf8;

-- 4. 学生表（student）：存储学生信息，关联班级表
drop table if exists `student`;
create table `student` (
  `sid` int(11) not null auto_increment, -- 学生id，自增主键
  `gender` char(1) not null, -- 性别（如“男”“女”）
  `class_id` int(11) not null, -- 班级id，关联班级表的cid
  `sname` varchar(32) not null, -- 学生姓名
  primary key (`sid`),
  key `fk_class` (`class_id`), -- 外键索引
  constraint `fk_class` foreign key (`class_id`) references `class` (`cid`) -- 外键约束（关联班级）
) engine=innodb auto_increment=17 default charset=utf8;

-- 5. 教师表（teacher）：存储教师信息
drop table if exists `teacher`;
create table `teacher` (
  `tid` int(11) not null auto_increment, -- 教师id，自增主键
  `tname` varchar(32) not null, -- 教师姓名
  primary key (`tid`)
) engine=innodb auto_increment=6 default charset=utf8;

1.2 基础查询

基础查询是sql的核心操作，用于从表中提取所需数据，主要包括“查询全部字段”“查询部分字段”“给字段起别名”“去除重复记录”四种场景，语法简单且高频使用。

1. 查询全部字段：使用select *获取表中所有字段的数据，适用于需要完整数据的场景，但效率较低（不推荐在表字段较多时使用）。

   select * from student; -- 查询student表中所有学生的全部信息
   

2. 查询部分字段：指定需要的字段名（用逗号分隔），只获取目标数据，效率高于查询全部字段。

   select `sname`, `class_id` from student; -- 只查询student表中的“学生姓名”和“班级id”
   

3. 给字段起别名：使用as关键字给字段重命名，使查询结果更易理解，注意别名不能使用sql关键字（如“select”“from”）。

   select `sname` as '姓名' , `class_id` as '班级id' from student; -- 字段别名分别为“姓名”“班级id”
   

4. 去除重复记录：使用distinct关键字，删除查询结果中完全重复的行，适用于需要唯一值的场景（如统计有多少个不同的班级）。

   select distinct `class_id` from student; -- 查询student表中所有不重复的班级id
   

1.3 条件查询

条件查询通过where子句筛选符合条件的数据，只返回满足条件的记录，支持单条件和多条件（用and/or连接）查询，是数据筛选的核心。

1. 单条件查询：只设置一个筛选条件，获取特定数据。

   -- 查询姓名为“邓洋洋”的学生的全部信息（字符串条件需用单引号或双引号包裹）
   select * from `student` where `sname` = '邓洋洋';
   

2. 多条件查询：用and（同时满足）或or（满足其一）连接多个条件，实现更精准的筛选。

   -- 查询“性别为男”并且“班级id为2”的学生的全部信息（同时满足两个条件）
   select * from `student` where `gender`="男" and `class_id`=2;
   

1.4 范围查询

范围查询用于筛选“字段值在某个连续范围内”的数据，主要使用between and关键字，注意该关键字包含范围的边界值（即“起始值”和“结束值”都会被包含）。

-- 查询班级id在1到3之间的学生的全部信息（包含班级id=1和班级id=3的学生）
select * from `student` where `class_id` between 1 and 3;

1.5 判空查询

判空查询用于筛选“字段值为null”或“字段值为空字符串”的记录，需区分两种空值场景，且需注意is null会导致索引失效（影响查询效率）。

1. 判断字段值为null/非null：使用is null（为空）或is not null（不为空），适用于字段值未赋值的场景（null表示“无值”，而非空字符串）。

   select * from `student` where `class_id` is not null; -- 查询class_id字段值不为null的学生
   select * from `student` where `class_id` is null;    -- 查询class_id字段值为null的学生
   

2. 判断字段值为空字符串/非空字符串：使用 = = =（为空字符串）或<>（不为空字符串），适用于字段值赋值为空字符串（''）的场景。

   select * from `student` where `gender` <> ''; -- 查询gender字段值不为空字符串的学生
   select * from `student` where `gender` = '';  -- 查询gender字段值为空字符串的学生
   

1.6 模糊查询

模糊查询通过like关键字实现“非精确匹配”，支持通配符来替代不确定的字符，常用通配符有%（任意数量字符，包括0个）和_（单个字符，必须有一个）。

1. %通配符：匹配“指定字符开头/结尾/包含指定字符”的字符串。

   -- 查询教师姓名以“谢”开头的所有教师信息（“谢”后面可以跟任意数量的字符）
   select * from `teacher` where `tname` like '谢%';
   

2. _通配符：匹配“指定位置有特定字符”的字符串，每个_代表一个占位符（必须匹配一个字符，不能多也不能少）。

   -- 查询教师姓名中第二个字为“小”的所有教师信息（第一个字任意，第二个字是“小”，后面任意）
   select * from `teacher` where `tname` like '_小%';
   

1.7 分页查询

分页查询用于“按页获取数据”，通过limit关键字实现，语法为 limit 起始位置, 显示条数，注意表中默认第一条记录的起始位置为0。

-- 查询student表中“从第2条记录开始，显示2条记录”（即第2条和第3条记录）
-- 起始位置计算：第n条记录的起始位置 = n-1（第2条的起始位置是1）
select * from `student` limit 1,2;

1.8 查询后排序

查询后排序通过order by关键字对查询结果按指定字段排序，支持“升序”和“降序”，也可按多个字段排序，确保结果的有序性。

1. 单字段排序：按一个字段排序，asc表示升序（从小到大，默认值，可省略），desc表示降序（从大到小）。

   -- 按成绩表（score）的分数（num）升序排序（分数从低到高）
   select * from `score` order by `num` asc;
   

2. 多字段排序：按多个字段排序，先按第一个字段排序，若第一个字段值相同，则按第二个字段排序，以此类推。

   -- 先按课程id（course_id）降序排序，课程id相同的再按分数（num）降序排序
   select * from `score` order by `course_id` desc, `num` desc;
   

1.9 聚合查询

聚合查询通过“聚合函数”对一列数据进行统计计算，返回单个结果（如“总分”“平均分”），常用聚合函数有5种，适用于数据统计场景。

常用聚合函数说明：

聚合函数示例：

select sum(`num`) from `score`;  -- 计算score表中所有分数的总和
select avg(`num`) from `score`;  -- 计算score表中所有分数的平均值
select max(`num`) from `score`;  -- 找出score表中分数的最大值
select min(`num`) from `score`;  -- 找出score表中分数的最小值
select count(`num`) from `score`;-- 统计score表中分数的有效记录数（num不为null的行数）

1.10 分组查询

分组查询通过group by关键字将数据按指定字段“分组”，再对每组数据进行统计（常与聚合函数搭配），也可通过having筛选分组后的结果（区别于where筛选分组前的数据）。

1. 基础分组：仅按指定字段分组，返回每组的唯一值。

   -- 按学生性别（gender）分组，返回“男”“女”两个分组（每个分组一行）
   select `gender` from `student` group by `gender`;
   

2. 分组+拼接字段：用group_concat(字段名)将每组中指定字段的所有值拼接成一个字符串，方便查看组内详情。

   -- 按性别分组，同时拼接每组学生的年龄（ages为别名）
   select `gender`, group_concat(`age`) as ages from `student` group by `gender`;
   

3. 分组+聚合函数：对每组数据使用聚合函数统计，获取每组的统计结果（如“每组的人数”“每组的平均分”）。

   -- 按性别分组，统计每组的学生人数（num为别名，count(*)表示统计每组的总行数）
   select `gender`, count(*) as num from `student` group by `gender`;
   

4. 分组+筛选（having）：用having对分组后的结果筛选（类似where，但having作用于分组后，支持聚合函数）。

   -- 按性别分组，统计每组人数后，只保留人数大于6的分组
   select `gender`, count(*) as num from `student` group by `gender` having num > 6;
   

1.11 联表查询

联表查询用于“关联多个表”获取数据（因为单表数据有限，需结合多表信息），核心是通过“外键”建立表间关联，常用连接方式有三种：inner join、left join、right join。

三种连接方式的区别

联表查询示例

1. inner join（内连接）

   -- 关联course（课程表）和teacher（教师表），只查询“有对应教师的课程”的课程id
   -- on后面是两表的关联条件：课程表的teacher_id = 教师表的tid
   select course.cid
   from `course`
   inner join `teacher` on course.teacher_id = teacher.tid;
   

2. left join（左连接）

   -- 关联course（左表）和teacher（右表），保留所有课程，关联对应的教师（无教师的课程cid仍显示，教师信息为null）
   select course.cid
   from `course`
   left join `teacher` on course.teacher_id = teacher.tid;
   

3. right join（右连接）

   -- 关联course（左表）和teacher（右表），保留所有教师，关联对应的课程（无课程的教师仍显示，课程cid为null）
   select course.cid
   from `course`
   right join `teacher` on course.teacher_id = teacher.tid;
   

1.12 子查询/合并查询

子查询是“嵌套在其他查询中的查询”，内层查询的结果作为外层查询的条件或数据源，按返回结果行数可分为“单行子查询”和“多行子查询”，也可在from子句中作为临时表使用。

单行子查询

内层查询返回“一行一列”的结果，外层查询用“=”“>”等单值比较符使用该结果，适用于“基于单个值筛选”的场景。

-- 先查“谢小二老师”的tid（内层子查询），再查该教师授课的所有课程（外层查询）
select * from course where teacher_id = (select tid from teacher where tname = '谢小二老师');

多行子查询

内层查询返回“多行数据”，外层查询需用支持多行的关键字（如in、exists、all、any），适用于“基于多个值筛选”的场景。

1. in关键字：检测外层查询的字段值是否“在”内层查询的结果集中，存在则返回该记录。

   -- 先查“teacher_id=2的教师”所授课程的cid（内层），再查“班级id在这些课程中的学生”（外层）
   select * from student where class_id in (select cid from course where teacher_id = 2);
   

2. exists关键字：判断内层查询是否“存在满足条件的记录”（不关心具体结果，只返回真假）。若内层返回真（有记录），则执行外层查询；若返回假（无记录），则外层查询无结果。

   -- 先判断“是否存在cid=5的课程”（内层），若存在，则查询所有学生（外层）；若不存在，则无结果
   select * from student where exists(select cid from course where cid = 5);
   

3. all关键字：外层查询的字段值需“满足内层查询返回的所有结果”，才返回该记录（如“大于所有值”“小于所有值”）。

4. any关键字：外层查询的字段值只需“满足内层查询返回的任意一个结果”，就返回该记录（如“大于任意一个值”“小于任意一个值”）。

from子句中的子查询

将内层查询的结果作为“临时表”（需用as取别名），外层查询从临时表中获取数据，适用于“需先筛选数据，再关联其他表”的场景。

-- 1. 内层查询：筛选score表中“course_id=1或course_id=2”的记录，作为临时表a
-- 2. 外层查询：关联临时表a和student表，获取学生id和学生姓名
select
   student_id,
   sname
from
    (select * from score where course_id = 1 or course_id = 2) as a -- 临时表a（必须取别名）
      left join student on a.student_id = student.sid; -- 关联临时表a和student表

1.13 正则表达式查询

正则表达式通过regexp关键字实现“更灵活的模糊匹配”，支持多种匹配规则（如“开头匹配”“任意字符匹配”），比like的匹配能力更强。

常用正则表达式规则：

正则表达式示例：

-- 查询教师姓名“以‘谢'开头”的所有教师信息（用^匹配开头，regexp指定正则规则）
select * from `teacher` where `tname` regexp '^谢';

二、预处理语句

预处理语句是“将sql语句分为‘准备’和‘执行’两个阶段”的技术，主要用于高频重复执行的sql，核心优势是“提升效率”和“防止sql注入”。

2.1 预处理语句的核心流程

准备阶段：将sql语句发送给数据库服务器，服务器对sql进行解析、编译、优化，并生成“执行计划”缓存起来（只执行一次）。

执行阶段：将具体的参数传递给缓存的执行计划，服务器直接使用计划执行sql（无需再次解析编译）。

2.2 预处理语句的优点

• 减少重复解析和编译：同一sql只需准备一次，后续执行只需传参数，提升高频查询的效率。
• 防止sql注入：参数会被数据库自动检查数据类型，避免因“拼接字符串”导致的sql注入漏洞（如用户输入恶意sql片段）。

2.3 预处理语句示例

预处理语句用?作为“参数占位符”，表示后续会传递具体值，示例如下：

-- 预处理语句：查询表中id等于某个参数的记录（?为参数占位符）
select * from table where id = ?;

说明：该语句第一次执行时，服务器会解析、编译并缓存执行计划；后续执行时，只需传入?对应的具体值（如1、2），服务器直接使用缓存计划执行，无需重复处理sql结构。

三、练习

1. 查询平均成绩大于60分的同学的学号和平均成绩

思路：按学生id分组（group by student_id），用avg(num)计算平均分，再用having筛选平均分大于60的分组。

select
  student_id, -- 学生学号（关联student表的sid）
  avg( num ) as avg_num -- 平均成绩（别名avg_num）
from
  score -- 从成绩表查询
group by
  student_id -- 按学生id分组（确保每个学生一个分组）
having
  avg_num > 60; -- 筛选平均分大于60的分组

2. 查询 ‘c++’ 课程比 ‘数据库’ 课程成绩高的所有学生的学号：

思路：先分别查询“c++”和“数据库”课程的学生成绩（作为两个临时表），再关联两个临时表，筛选c++成绩大于数据库成绩的学生id。

1）先查询“c++”和“数据库”课程的cid（确定课程唯一标识）：

select cid from course where cname = 'c++'; -- 假设返回cid=1
select cid from course where cname = '数据库'; -- 假设返回cid=2

2）分别查询两门课程的“学生id”和“分数”（作为临时表a和b）：

-- 临时表a：c++课程的学生id和分数
select student_id, num from score where course_id = (select cid from course where cname = 'c++');
-- 临时表b：数据库课程的学生id和分数
select student_id, num from score where course_id = (select cid from course where cname = '数据库');

3）关联临时表a和b，筛选c++成绩大于数据库成绩的学生id（两种情况）：

• 情况1：只保留“同时选了两门课”的学生（用inner join，无对应课程的学生过滤）：

  select a.student_id 
  from
    (select student_id, num from score where course_id = (select cid from course where cname = 'c++')) as a
    inner join -- 内连接，只保留两表都有记录的学生（同时选两门课）
    (select student_id, num from score where course_id = (select cid from course where cname = '数据库')) as b
    on a.student_id = b.student_id -- 按学生id关联
  where a.num > b.num; -- 筛选c++成绩（a.num）大于数据库成绩（b.num）的学生
  

• 情况2：保留“选了c++但没选数据库”的学生（用left join，没选数据库的学生b.num为null，a.num>null不成立，实际仍只保留有两门成绩的学生，逻辑与inner join类似）：

  select a.student_id 
  from
    (select student_id, num from score where course_id = (select cid from course where cname = 'c++')) as a
    left join -- 左连接，保留所有选c++的学生
    (select student_id, num from score where course_id = (select cid from course where cname = '数据库')) as b
    on a.student_id = b.student_id
  where a.num > b.num;
  

3. 查询所有同学的学号、姓名、选课数、总成绩

思路：关联student表（获取学号、姓名）和score表（统计选课数、总成绩），按学生id分组，用count(course_id)计算选课数，sum(num)计算总成绩。

select
  s.sid as '学号',
  s.sname as '姓名',
  count(sc.course_id) as '选课数', -- 统计每个学生的课程数量（course_id非null）
  sum(sc.num) as '总成绩' -- 统计每个学生的分数总和
from
  student s -- 学生表（别名s）
  left join score sc on s.sid = sc.student_id -- 左连接成绩表（确保没选课的学生也显示，选课数为0，总成绩为null）
group by
  s.sid, s.sname; -- 按学生id和姓名分组（确保每个学生一条记录）

4. 查询没学过 ‘谢小二’ 老师课的同学的学号、姓名

思路：先查“谢小二老师”授课程的cid，再查“学过这些课程的学生id”，最后从student表中排除这些学生，获取没学过的学生信息。

-- 步骤1：查谢小二老师的tid；步骤2：查该老师授课程的cid；步骤3：查学过这些课程的学生id；步骤4：排除这些学生
select sid as '学号', sname as '姓名'
from student
where sid not in (
  select distinct sc.student_id -- 学过谢小二老师课程的学生id（去重）
  from score sc
  join course c on sc.course_id = c.cid
  join teacher t on c.teacher_id = t.tid
  where t.tname = '谢小二' -- 筛选谢小二老师
);

5. 查询学过课程编号为 ‘1’ 并且也学过课程编号为 ‘2’ 的同学的学号、姓名

思路：按学生id分组，用count(distinct course_id)统计“同时选了1和2课程”的学生（分组后课程数为2），再关联student表获取姓名。

select
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  join score sc on s.sid = sc.student_id
where
  sc.course_id in (1, 2) -- 只保留选了1或2课程的记录
group by
  s.sid, s.sname
having
  count(distinct sc.course_id) = 2; -- 筛选同时选了1和2课程的学生（课程数为2）

6. 查询学过 ‘谢小二’ 老师所教的所有课的同学的学号、姓名

思路：先查“谢小二老师所教课程的总数”，再查“每个学生学过该老师课程的数量”，筛选“学生学过的数量=课程总数”的记录（即学过所有课）。

-- 临时表：谢小二老师所教课程的总数（假设为2）
with teacher_course_count as (
  select count(cid) as total from course where teacher_id = (select tid from teacher where tname = '谢小二')
)
-- 查询学过所有课程的学生
select
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  join score sc on s.sid = sc.student_id
  join course c on sc.course_id = c.cid
  join teacher t on c.teacher_id = t.tid
where
  t.tname = '谢小二'
group by
  s.sid, s.sname
having
  count(distinct sc.course_id) = (select total from teacher_course_count); -- 学生学过的数量=课程总数

7. 查询有课程成绩小于 60 分的同学的学号、姓名

思路：从score表中筛选num<60的学生id，去重后关联student表获取姓名（避免重复显示同一学生）。

select distinct
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  join score sc on s.sid = sc.student_id
where
  sc.num < 60; -- 筛选成绩小于60的记录，distinct避免同一学生重复显示

8. 查询没有学全所有课的同学的学号、姓名

思路：先查“所有课程的总数”，再查“每个学生的选课数”，筛选“选课数<课程总数”的学生（即没学全）。

-- 临时表：所有课程的总数（假设为4）
with all_course_count as (
  select count(cid) as total from course
)
-- 查询没学全所有课的学生
select
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  left join score sc on s.sid = sc.student_id
group by
  s.sid, s.sname
having
  count(distinct sc.course_id) < (select total from all_course_count) -- 选课数<总课程数
  or count(distinct sc.course_id) is null; -- 包含没选任何课的学生（选课数为null）

9. 查询至少有一门课与学号为 ‘1’ 的同学所学相同的同学的学号和姓名；

思路：先查“学号1的同学所学的所有课程id”，再查“学过这些课程的其他学生”（排除学号1本身）。

select distinct
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  join score sc on s.sid = sc.student_id
where
  sc.course_id in (
    select course_id from score where student_id = 1 -- 学号1的同学所学的课程id
  )
  and s.sid <> 1; -- 排除学号1本身，distinct避免同一学生重复显示

10. 查询至少学过学号为 ‘1’ 同学所有课的其他同学学号和姓名

思路：先查“学号1的同学所学课程的总数”，再查“其他学生学过这些课程的数量”，筛选“学生学过的数量=课程总数”的记录（即学过学号1的所有课）。

-- 临时表：学号1的同学所学课程的总数（假设为3）
with student1_course_count as (
  select count(course_id) as total from score where student_id = 1
)
-- 查询至少学过学号1所有课的其他同学
select
  s.sid as '学号',
  s.sname as '姓名'
from
  student s
  join score sc on s.sid = sc.student_id
where
  s.sid <> 1 -- 排除学号1本身
  and sc.course_id in (select course_id from score where student_id = 1) -- 只保留学号1学过的课程
group by
  s.sid, s.sname
having
  count(distinct sc.course_id) = (select total from student1_course_count); -- 学生学过的数量=学号1的课程总数

总结 

到此这篇关于sql高级查询与预处理语句解析以及实战练习题的文章就介绍到这了,更多相关sql高级查询与预处理语句内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！