排序
select ... from 表名 where group by having order by 列1，列2...
-- 查询学生的成绩信息，并且成绩从低到高排序 select stu_id,name,score from students order by score asc select stu_id,name,score from students order by score -- 查询学生的成绩信息，并且成绩从高到低排序 select stu_id,name,score from students order by score desc -- 查询学生的信息，按成绩排序，成绩相同，按年龄从大到小排序 select * from students ORDER BY score,age desc -- 查询学生的信息，按成绩降序排序，成绩相同，按年龄从小到大排序 select * from students ORDER BY score desc,age -- 查询每个班的人数，并且从小到大排序 select count(*) as c,class_id from students GROUP BY class_id ORDER BY c -- 查询男生信息，先按照成绩排序，成绩相同，按照年龄排序 select * from students where sex = '男' ORDER BY score,age -- 生日排序 select * from students ORDER BY birth_date -- 查询每个老师所带学生的平均成绩和最高成绩，并且按照平均成绩排序 select avg(score) as a,max(score),teacher_id from students where teacher_id is not null GROUP BY teacher_id ORDER BY a分页
将查询结果进行分页显示
1)limit 值1，值2
2)limit 值
值1：从第几条数据开始显示，从0开始的
值2：显示的条数
select * from 表 limit 3,5
值：显示前几条
-- 从第四条数据开始，显示5条 select * from students LIMIT 3,5 -- 显示分页， -- 一页显示5条 -- 第一页 select * from students limit 0,5 -- 第二页 select * from students limit 5,5 -- 第三页 select * from students limit 10,5 -- 第四页 select * from students limit 15,5 -- 第n页，每页显示m条1imit(n-1)*m,m -- 查询成绩前三的学生信息 select * from students ORDER BY score desc limit 3谓词
in
in(值1，值2，值3...)
代表和in里面的值进行等值判断，如果相同，返回真
-- 查询年龄为20,21,23,25 select * from students where age=20 or age=21 or age=23 or age=25 select * from students where age in(220,21,23,25) -- 查询和老师年龄相同的学生信息 -- 查询老师的年龄 select age from teacher select * from students where age in(select age from teacher)between
between 值1 and 值2 [值1，值2]
-- 查询年龄在20-25之间的学生信息 select * from students where age BETWEEN 20 and 25 -- 查询成绩在80-90之间的学生信息 select * from students where score BETWEEN 80 and 90 -- 查询生日在1998-2000之间的学生信息 select * from students where year(birth_date) BETWEEN 1998 and 2000模糊查询
like %0-多个字符 _ 一个字符
-- 查询姓张的学生信息 select * from students where name like '张%' -- 查询名字（不带姓）为六的学生信息 select * from students where name like '_六' -- 查询姓名中含有七的学生信息 select * from students where name like '%七%' -- 查询名字的第三个为七的学生信息 select * from students where name like '__七' -- 查询名字中没有七的学生信息 select * from students where name not like '%七%'子查询
在查询结果上再次查询
-- 查询刘伟老师所带的学生信息 -- 查询刘伟老师的id select teacher_id from teacher where name = '刘伟' -- 查询tea_id=1的学生信息 select * from students where teacher_id = (select teacher_id from teacher where name = '刘伟') -- 查询成绩最高的学生信息 -- 查询最高成绩 select max(score) from students -- 查询成绩等于最高成绩的学生信息 select * from students where score=(select max(score) from students) -- 查询比1班的平均成绩高的所有2班的学生信息 -- 查询1班的平均成绩 select avg(score) from students where class_id = 1 -- 查询2班的学生信息，成绩高于1班平均成绩 select * from students where class_id = 2 and score > (select avg(score) from students where class_id = 1) -- 查询比老师年龄大的男生信息 -- 查询老师的最小年龄 select min(age) from teacher -- 查询比老师最小年龄大的男生 select * from students where sex='男' and age>(select min(age) from teacher) -- 查询和老师年龄相同的学生信息 select age from teacher select * from students where age in (select age from teacher) -- 人数最多的班级人数 -- 每个班的班级人数 select count(*),class_id from students GROUP BY class_id select max(s.c) from (select count(*) c,class_id from students GROUP BY class_id) s -- 人数最多的班级号 select s1.class_id from (select count(*) c,class_id from students GROUP BY class_id) s1 where s1.c=(select max(s.c) from (select count(*) c,class_id from students GROUP BY class_id) s) -- 查询比平均分高的学生人数 -- 平均分 select avg(score) from students -- 比平均分高的学生人数 select count(*) from students where score>(select avg(score) from students) -- 查询老师所带的最高平均分 -- 查询每个老师的平均分 select avg(score),teacher_id from students where teacher_id is not null GROUP BY teacher_id -- 最高平均分 select max(s.a) from (select avg(score) a,teacher_id from students where teacher_id is not null GROUP BY teacher_id) s级联查询（多表查询）
多张表进行查询
笛卡尔集
A = {a, b}
B = {0, 1, 2}
C = {a0,a1,a2,b0,b1,b2}
多表查询就是在笛卡尔集的基础上进行筛选
100*1000*10000
-- 查询学生信息和老师信息
select 表1.*，表2.* from 表1，表2
-- 查询老师和学生的所有信息 select teacher.*,students.* from teacher,students select teacher.*,students.* from teacher,students where students.teacher_id=teacher.teacher_id select teacher.*,students.* from students join teacher on students.teacher_id=teacher.teacher_id where students.sex='男'92标准 排除了null值的
select 表1.*，表2.* from 表1，表2... where ...
99标准
select ... from 表1 join 表2 on 条件 join 表3 on 条件 where group by having limit
select teacher.*,students.* from students join teacher on students.teacher_id=teacher.teacher_id where students.sex='男'
99标准的级联查询分为
内级联：inner join 筛除掉null值，和92标准的结果是一样的 ,inner可以省略
select stu.*,teacher.* from stu inner join teacher on teacher.tea_id=stu.tea_id where stu.sex='男'
外级联：left outer join,right outer join 左外级联和右外级联 可以省略outer关键词
left join ,right join 左级联和右级联
-- 左级联 以左边表为主，显示左边表中的所有信息，如果右表没有匹配信息，显示为nullselect students.*,teacher.* from students left join teacher on students.teacher_id=teacher.teacher_id
-- 右级联 以右边表为主，显示右边表中的所有信息，如果左表没有匹配信息，显示为null
select students.*,teacher.* from students right join teacher on students.teacher_id=teacher.teacher_id
-- 查询1，2号老师所带的班级信息（班级名称和班级地址） select class.*,teacher.* from students,teacher,class where students.teacher_id=teacher.teacher_id and students.class_id=class.class_id and teacher.teacher_id in (1,2) select class.*,teacher.* from students join teacher on students.teacher_id=teacher.teacher_id join class on students.class_id=class.class_id where teacher.teacher_id in (1,2)改
update 表名 set 列1= 值，列2 =值 where
-- 将张泽的成绩修改为60,性别改为女 update students set score=60,sex='女' where name='张泽' -- 将张泽的名字改为汤姆 update students set name='汤姆' where name='张泽'mysql的查询语句书写顺序
select ... from 表1 (join)
where
group by having
order by
limit
mysql的查询语句执行顺序
from （join）
where
group by having
select
order by
limit
课后练习
create database day3 use day3 -- 新表设计：员工表 (employee) -- 此表设计包含了文档中提到的各种数据类型和约束，以确保练习的全面性。 -- 创建员工表 (employee) CREATE TABLE employee ( emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号', name VARCHAR(100) NOT NULL COMMENT '姓名', gender CHAR(2) DEFAULT '男' COMMENT '性别', age INT COMMENT '年龄', salary DECIMAL(10, 2) COMMENT '月薪', department VARCHAR(50) COMMENT '部门', position VARCHAR(50) COMMENT '职位', hire_date DATE COMMENT '入职日期', performance_score INT COMMENT '绩效分数' )ENGINE=InnoDB DEFAULT CHARSET=utf8 -- 插入数据 INSERT INTO employee (name, gender, age, salary, department, position, hire_date, performance_score) VALUES ('李明', '男', 28, 18500.00, '销售部', '销售专员', '2018-05-12', 82), ('李娜', '女', 26, 22000.00, '销售部', '销售主管', '2020-03-15', 90), ('张伟', '男', 32, 26000.00, '研发部', '后端开发工程师', '2019-07-20', 88), ('刘芳', '女', 29, 20000.00, '研发部', '测试工程师', '2021-01-08', 92), ('王强', '男', 36, 14500.00, '研发部', '运维工程师', '2017-11-22', 76), ('赵静', '女', 27, 16800.00, '市场部', '市场专员', '2020-09-10', 86), ('孙浩', '男', 34, 23500.00, '市场部', '市场经理', '2019-04-18', 95), ('陈丽', '女', 30, 19200.00, '人事部', '人事专员', '2022-02-14', 89), ('周凯', '男', 33, 21800.00, '财务部', '财务经理', '2018-08-30', 91), ('吴敏', '女', 25, 17600.00, NULL, '实习生', '2023-06-05', 78), ('李军', '男', 29, 24200.00, '销售部', '销售经理', '2020-12-01', 93), ('郑雪', '女', 28, 15600.00, '研发部', '前端开发工程师', '2019-09-16', 87), ('黄涛', '男', 31, 20500.00, '市场部', '渠道专员', '2021-05-25', 84), ('徐艳', '女', 26, 18900.00, '销售部', '销售专员', '2022-03-12', 88), ('马峰', '男', 35, 19800.00, '人事部', '人事经理', '2019-10-20', 94), ('朱婷', '女', 24, 17200.00, '财务部', '出纳', '2023-01-15', 81); -- 题目1：复杂条件查询 (WHERE, AND, OR, BETWEEN) -- 查询“销售部”所有员工的姓名、职位和月薪。 select name,position,salary from employee where department='销售部' -- 查询月薪在15000到25000元之间（包含边界值）的“研发部”员工信息。 select * from employee where department='研发部' and salary between 15000 and 25000 -- 查询在2019年（含）以后入职，且绩效分数高于85的员工姓名和入职年份。 select name,year(hire_date) from employee where year(hire_date)>2019 and performance_score>85 -- 查询年龄在30岁以下（含）的女性员工，或者年龄在35岁以下（含）的男性员工的所有信息。 select * from employee where (gender='女' and age<=30) or (gender='男' and age<=35) -- 题目2：模糊查询与NULL值判断 (LIKE, IS NULL) -- 查询所有姓“李”的员工信息。 select * from employee where name like '李%' -- 查询职位名称中包含“经理”二字的员工姓名和职位。 select name,position from employee where position like '%经理%' -- 查询尚未分配部门（即department为NULL）的员工编号和姓名。 select emp_id,name from employee where department is null -- 题目3：分组聚合查询 (GROUP BY, HAVING, 聚合函数) -- 统计每个部门的员工人数。 select count(*),department from employee GROUP BY department -- 计算每个部门的平均月薪，并仅显示平均月薪高于20000元的部门名称和平均月薪。 select avg(salary) a,department from employee GROUP BY department HAVING a>20000 -- 查询每个部门中绩效分数最高的员工分数（显示部门名称和最高分）。 select max(performance_score),department from employee GROUP BY department select max(performance_score),department from employee GROUP BY department HAVING department is not null -- 统计每个部门下不同性别（gender）的员工人数。 select count(*),gender from employee GROUP BY gender -- 查询员工人数超过5人的部门名称。 select count(*) c,department from employee GROUP BY department HAVING c>5 -- 题目4：排序与限制结果集 (ORDER BY, LIMIT) -- 查询所有员工信息，按月薪从高到低排序。 select * from employee ORDER BY salary desc -- 查询“市场部”的员工信息，按入职日期从早到晚排序。 select * from employee where department='市场部' ORDER BY hire_date ASC -- 查询公司月薪最高的前3名员工的姓名和月薪。 select name,salary from employee ORDER BY salary desc limit 3 -- 查询每个部门月薪最高的员工信息（显示部门、姓名、月薪），并在部门内按月薪降序排列。 select department,name,max(salary) from employee GROUP BY department ORDER BY salary descuse day3 -- 创建老师表 CREATE TABLE teachers ( teacher_id INT PRIMARY KEY AUTO_INCREMENT, teacher_name VARCHAR(50) NOT NULL, gender CHAR(1) CHECK (gender IN ('M', 'F')), age INT, subject VARCHAR(50) ) -- 创建学生表 CREATE TABLE students ( student_id INT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(50) NOT NULL, gender CHAR(1) CHECK (gender IN ('M', 'F')), birth_date DATE, class_name VARCHAR(30) ) -- 创建成绩表 CREATE TABLE scores ( score_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_name VARCHAR(50) NOT NULL, score DECIMAL(5,2), semester VARCHAR(20), FOREIGN KEY (student_id) REFERENCES students(student_id) ) -- 插入老师表数据 INSERT INTO teachers (teacher_name, gender, age, subject) VALUES ('lily', 'F', 32, '数学'), ('tom', 'M', 35, '英语'), ('jack', 'M', 29, '语文'), ('mary', 'F', 34, '物理'); -- 插入学生表数据 INSERT INTO students (student_name, gender, birth_date, class_name) VALUES ('张三', 'M', '2006-03-12', '一班'), ('李四', 'F', '2006-05-20', '一班'), ('王五', 'M', '2005-11-08', '二班'), ('赵六', 'F', '2006-07-15', '二班'), ('孙七', 'M', '2005-09-22', '三班'), ('周八', 'F', '2006-01-30', '三班'), ('吴九', 'M', '2005-06-18', '一班'), ('郑十', 'F', '2006-02-15', '二班'); -- 插入成绩表测试数据 INSERT INTO scores (student_id, course_name, score, semester) VALUES (1, '数学', 92.5, '2025上'), (1, '英语', 88.0, '2025上'), (1, '语文', 79.0, '2025上'), (2, '数学', 85.0, '2025上'), (2, '英语', 93.0, '2025上'), (3, '数学', 76.0, '2025上'), (3, '英语', 82.0, '2025上'), (3, '语文', 95.0, '2025上'), (4, '数学', 96.0, '2025上'), (5, '英语', 75.0, '2025上'), (6, '语文', 89.0, '2025上'), (7, '数学', 68.0, '2025上'), (7, '英语', 72.0, '2025上'), (8, '数学', 88.0, '2025上'); -- 查询所有学生及其成绩记录（包括没有成绩的学生） select * from students left join scores on students.student_id = scores.student_id -- 查询成绩大于90分的记录，显示学生姓名和课程名称 select students.student_name,scores.course_name from students join scores on students.student_id=scores.student_id where scores.score>90 -- 查询每个学生的平均成绩 select avg(scores.score),students.student_name,students.student_id from students join scores on students.student_id=scores.student_id GROUP BY students.student_id -- 查询没有成绩记录的学生名单 select students.student_name from students join scores on students.student_id=scores.student_id where scores.score is null GROUP BY students.student_id -- 查询选修了"数学"课程的所有学生姓名和成绩 select students.student_name,scores.score from students join scores on students.student_id=scores.student_id where scores.course_name='数学' -- 查询每门课程的平均分，并按平均分降序排列 select avg(scores.score) a,scores.course_name from scores GROUP BY scores.course_name ORDER BY a desc -- 查询选修了超过3门课程的学生信息 select count(*) c,students.student_id,students.student_name from students join scores on students.student_id=scores.student_id GROUP BY students.student_id HAVING c>3 -- 查询每门课程的最高分和最低分 select max(scores.score),min(scores.score),scores.course_name from scores GROUP BY scores.course_name -- 查询同时选修了"数学"和"英语"课程的学生名单 select s1.student_id,s1.student_name from students s1 JOIN scores s2 on s1.student_id=s2.student_id and s2.course_name='数学' join scores s3 on s1.student_id=s3.student_id and s3.course_name='英语' GROUP BY s1.student_id -- 查询每个班级(按class_name分组)的平均成绩 select avg(scores.score),students.class_name from students JOIN scores on students.student_id=scores.student_id GROUP BY students.class_name use day2 -- 查询每个学生姓名及其对应老师的姓名(使用级联查询) select students.name,teacher.name from students,teacher where students.teacher_id=teacher.teacher_id -- 查询比"lily"老师所带学生平均成绩高的学生信息 select avg(students.score) from students join teacher on students.teacher_id=teacher.teacher_id where teacher.name='lily' select * from students join teacher on students.teacher_id=teacher.teacher_id where students.score>(select avg(students.score) from students join teacher on students.teacher_id=teacher.teacher_id where teacher.name='lily') -- 使用子查询找出成绩高于平均成绩的学生 select avg(score) from students select * from students where score>(select avg(score) from students) -- 查询年龄大于所有老师平均年龄的学生信息 select avg(teacher.age) from teacher select * from students where age>(select avg(teacher.age) from teacher) -- 分页显示学生信息，每页5条，按生日升序排列，写出获取第3页数据的SQL select * from students ORDER BY birth_date LIMIT 0,5 select * from students ORDER BY birth_date LIMIT 5,5 select * from students ORDER BY birth_date LIMIT 10,5 -- 查询每个班级成绩最好的学生信息(包括班级ID、学生姓名和成绩) select max(score) from students GROUP BY class_id select class_id,name,score from students where score in (select max(score) from students GROUP BY class_id) and class_id is not null -- 编写SQL统计每个老师所带学生的最高分、最低分和平均分，并按平均分降序排列 select max(score),min(score),avg(score),teacher.name from students JOIN teacher on students.teacher_id = teacher.teacher_id GROUP BY students.teacher_id