排序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值22)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谓词inin(值1值2值3...)代表和in里面的值进行等值判断如果相同返回真-- 查询年龄为20,21,23,25 select * from students where age20 or age21 or age23 or age25 select * from students where age in(220,21,23,25) -- 查询和老师年龄相同的学生信息 -- 查询老师的年龄 select age from teacher select * from students where age in(select age from teacher)betweenbetween 值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_id1的学生信息 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_idteacher.teacher_id select teacher.*,students.* from students join teacher on students.teacher_idteacher.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 limitselect teacher.*,students.* from students join teacher on students.teacher_idteacher.teacher_id where students.sex男99标准的级联查询分为内级联inner join 筛除掉null值和92标准的结果是一样的 ,inner可以省略select stu.*,teacher.* from stu inner join teacher on teacher.tea_idstu.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_idteacher.teacher_id-- 右级联 以右边表为主显示右边表中的所有信息如果左表没有匹配信息显示为nullselect students.*,teacher.* from students right join teacher on students.teacher_idteacher.teacher_id-- 查询12号老师所带的班级信息班级名称和班级地址 select class.*,teacher.* from students,teacher,class where students.teacher_idteacher.teacher_id and students.class_idclass.class_id and teacher.teacher_id in (1,2) select class.*,teacher.* from students join teacher on students.teacher_idteacher.teacher_id join class on students.class_idclass.class_id where teacher.teacher_id in (1,2)改update 表名 set 列1 值列2 值 where-- 将张泽的成绩修改为60,性别改为女 update students set score60,sex女 where name张泽 -- 将张泽的名字改为汤姆 update students set name汤姆 where name张泽mysql的查询语句书写顺序select ... from 表1 (join)wheregroup by havingorder bylimitmysql的查询语句执行顺序from joinwheregroup by havingselectorder bylimit课后练习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 绩效分数 )ENGINEInnoDB DEFAULT CHARSETutf8 -- 插入数据 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_score85 -- 查询年龄在30岁以下含的女性员工或者年龄在35岁以下含的男性员工的所有信息。 select * from employee where (gender女 and age30) or (gender男 and age35) -- 题目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 a20000 -- 查询每个部门中绩效分数最高的员工分数显示部门名称和最高分。 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 c5 -- 题目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_idscores.student_id where scores.score90 -- 查询每个学生的平均成绩 select avg(scores.score),students.student_name,students.student_id from students join scores on students.student_idscores.student_id GROUP BY students.student_id -- 查询没有成绩记录的学生名单 select students.student_name from students join scores on students.student_idscores.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_idscores.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_idscores.student_id GROUP BY students.student_id HAVING c3 -- 查询每门课程的最高分和最低分 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_ids2.student_id and s2.course_name数学 join scores s3 on s1.student_ids3.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_idscores.student_id GROUP BY students.class_name use day2 -- 查询每个学生姓名及其对应老师的姓名(使用级联查询) select students.name,teacher.name from students,teacher where students.teacher_idteacher.teacher_id -- 查询比lily老师所带学生平均成绩高的学生信息 select avg(students.score) from students join teacher on students.teacher_idteacher.teacher_id where teacher.namelily select * from students join teacher on students.teacher_idteacher.teacher_id where students.score(select avg(students.score) from students join teacher on students.teacher_idteacher.teacher_id where teacher.namelily) -- 使用子查询找出成绩高于平均成绩的学生 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