首先先准备表
# 员工表和部门表#建表create table department(id int,name varchar(20) );create table employee1(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);#插入数据insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insert into employee1(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);查看表：
多表连接查询
交叉连接：不适用任何匹配条件。生成笛卡尔积
select * from employee1 ,department;内连接：找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出正确的结果(只连接匹配的行)。
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来select * from employee1,department where employee1.dep_id=department.id;#上面用where表示的可以用下面的内连接表示，建议使用下面的那种方法select * from employee1 inner join department on employee1.dep_id=department.id;#也可以这样表示哈select employee1.id,employee1.name,employee1.age,employee1.sex,department.name from employee1,department where employee1.dep_id=department.id;注意：内连接的join可以忽略不写，但是还是加上看起来清楚点
左连接：优先显示左表全部记录。
#左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录select * from employee1 left join department on department.id=employee1.dep_id;select * from department left join employee1 on department.id=employee1.dep_id;
右链接：优先显示右表全部记录。
#右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录select * from employee1 right join department on department.id=employee1.dep_id;select * from department right join employee1 on department.id=employee1.dep_id;
全外连接：显示左右两个表的全部记录。
注意：mysql不支持全外连接 full join
强调：mysql可以使用union间接实现全外连接
select * from employee1 left join department on department.id=employee1.dep_id unionselect * from employee1 right join department on department.id=employee1.dep_id;
符合条件连接查询
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出公司所有部门中年龄大于25岁的员工
select * from employee1 inner join department on employee1.dep_id=department.id and age>25;示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from employee1 inner join department on employee1.dep_id=department.id and age>25 and age>25 order by age asc;子查询
- 子查询是将一个查询语句嵌套在另一个查询语句中。
- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
- 还可以包含比较运算符：= 、 !=、> 、
小练习
#查询平均年龄在25岁以上的部门名select name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);#查看技术部员工姓名select name from employee where dep_id = (select id from department where name='技术');#查看小于2人的部门名select name from department where id in (select dep_id from employee1 group by dep_id having count(id) < 2)unionselect name from department where id not in (select distinct dep_id from employee1);#提取空部门select * from department where id not in (select distinct dep_id from employee1);#或者：select name from department where id in(select dep_id from employee1 group by dep_id having count(id) < 2unionselect id from department where id not in (select distinct dep_id from employee1););综合练习
SELECT语句关键字的定义顺序
SELECT DISTINCT
FROM >
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
SELECT语句关键字的执行顺序
- (7) SELECT
- (8) DISTINCT
- (1) FROM
- (3) JOIN
- (2) ON
- (4) WHERE
- (5) GROUP BY
- (6) HAVING
- (9) ORDER BY
- (10) LIMIT
准备数据
表结构图如下：
创建表和插入数据：
SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for class-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of class-- ----------------------------INSERT INTO `class` VALUES ('1', '三年二班');INSERT INTO `class` VALUES ('2', '三年三班');INSERT INTO `class` VALUES ('3', '一年二班');INSERT INTO `class` VALUES ('4', '二年九班');-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, 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;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', '生物', '1');INSERT INTO `course` VALUES ('2', '物理', '2');INSERT INTO `course` VALUES ('3', '体育', '3');INSERT INTO `course` VALUES ('4', '美术', '2');-- ------------------------------ Table structure for score-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, 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;-- ------------------------------ Records of score-- ----------------------------INSERT INTO `score` VALUES ('1', '1', '1', '10');INSERT INTO `score` VALUES ('2', '1', '2', '9');INSERT INTO `score` VALUES ('5', '1', '4', '66');INSERT INTO `score` VALUES ('6', '2', '1', '8');INSERT INTO `score` VALUES ('8', '2', '3', '68');INSERT INTO `score` VALUES ('9', '2', '4', '99');INSERT INTO `score` VALUES ('10', '3', '1', '77');INSERT INTO `score` VALUES ('11', '3', '2', '66');INSERT INTO `score` VALUES ('12', '3', '3', '87');INSERT INTO `score` VALUES ('13', '3', '4', '99');INSERT INTO `score` VALUES ('14', '4', '1', '79');INSERT INTO `score` VALUES ('15', '4', '2', '11');INSERT INTO `score` VALUES ('16', '4', '3', '67');INSERT INTO `score` VALUES ('17', '4', '4', '100');INSERT INTO `score` VALUES ('18', '5', '1', '79');INSERT INTO `score` VALUES ('19', '5', '2', '11');INSERT INTO `score` VALUES ('20', '5', '3', '67');INSERT INTO `score` VALUES ('21', '5', '4', '100');INSERT INTO `score` VALUES ('22', '6', '1', '9');INSERT INTO `score` VALUES ('23', '6', '2', '100');INSERT INTO `score` VALUES ('24', '6', '3', '67');INSERT INTO `score` VALUES ('25', '6', '4', '100');INSERT INTO `score` VALUES ('26', '7', '1', '9');INSERT INTO `score` VALUES ('27', '7', '2', '100');INSERT INTO `score` VALUES ('28', '7', '3', '67');INSERT INTO `score` VALUES ('29', '7', '4', '88');INSERT INTO `score` VALUES ('30', '8', '1', '9');INSERT INTO `score` VALUES ('31', '8', '2', '100');INSERT INTO `score` VALUES ('32', '8', '3', '67');INSERT INTO `score` VALUES ('33', '8', '4', '88');INSERT INTO `score` VALUES ('34', '9', '1', '91');INSERT INTO `score` VALUES ('35', '9', '2', '88');INSERT INTO `score` VALUES ('36', '9', '3', '67');INSERT INTO `score` VALUES ('37', '9', '4', '22');INSERT INTO `score` VALUES ('38', '10', '1', '90');INSERT INTO `score` VALUES ('39', '10', '2', '77');INSERT INTO `score` VALUES ('40', '10', '3', '43');INSERT INTO `score` VALUES ('41', '10', '4', '87');INSERT INTO `score` VALUES ('42', '11', '1', '90');INSERT INTO `score` VALUES ('43', '11', '2', '77');INSERT INTO `score` VALUES ('44', '11', '3', '43');INSERT INTO `score` VALUES ('45', '11', '4', '87');INSERT INTO `score` VALUES ('46', '12', '1', '90');INSERT INTO `score` VALUES ('47', '12', '2', '77');INSERT INTO `score` VALUES ('48', '12', '3', '43');INSERT INTO `score` VALUES ('49', '12', '4', '87');INSERT INTO `score` VALUES ('52', '13', '3', '87');-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `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;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '男', '1', '理解');INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋');INSERT INTO `student` VALUES ('3', '男', '1', '张三');INSERT INTO `student` VALUES ('4', '男', '1', '张一');INSERT INTO `student` VALUES ('5', '女', '1', '张二');INSERT INTO `student` VALUES ('6', '男', '1', '张四');INSERT INTO `student` VALUES ('7', '女', '2', '铁锤');INSERT INTO `student` VALUES ('8', '男', '2', '李三');INSERT INTO `student` VALUES ('9', '男', '2', '李一');INSERT INTO `student` VALUES ('10', '女', '2', '李二');INSERT INTO `student` VALUES ('11', '男', '2', '李四');INSERT INTO `student` VALUES ('12', '女', '3', '如花');INSERT INTO `student` VALUES ('13', '男', '3', '刘三');INSERT INTO `student` VALUES ('14', '男', '3', '刘一');INSERT INTO `student` VALUES ('15', '女', '3', '刘二');INSERT INTO `student` VALUES ('16', '男', '3', '刘四');-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('1', '张磊老师');INSERT INTO `teacher` VALUES ('2', '李平老师');INSERT INTO `teacher` VALUES ('3', '刘海燕老师');INSERT INTO `teacher` VALUES ('4', '朱云海老师');INSERT INTO `teacher` VALUES ('5', '李杰老师');SET FOREIGN_KEY_CHECKS=1;1、查询所有的课程的名称以及对应的任课老师姓名
select course.cname 课程,teacher.tname 讲师 from course inner join teacher on course.teacher_id=teacher.tid;2、查询学生表中男女生各有多少人
select gender 性别,count(*) 人数 from student group by gender;3、查询物理成绩等于100的学生的姓名
select sname 姓名 from student where sid in(select student_id from score inner join course on score.course_id=course.cid where course.cname='物理' and score.num=100);4、查询平均成绩大于八十分的同学的姓名和平均成绩
select student.sname 姓名,avg(score.num) 平均分 from score left join student on score.student_id=student.sid group by student_id;5、查询所有学生的学号，姓名，选课数，总成绩
select student.sid 学号,student.sname 姓名,count(score.course_id) 课程数,sum(score.num) 总成绩 from student left join score on student.sid=score.student_id group by student.sid;6、 查询姓李老师的个数
select tname 姓李的老师 from teacher where tname like "李%";7、 查询没有报李平老师课的学生姓名
select sid 学号,sname 姓名 from student where sid not in (select student_id from score where course_id in(select course.cid from teacher left join course on teacher.tid=course.teacher_id where teacher.tname="李平老师"));8、 查询物理课程比生物课程高的学生的学号
select phy.student_id 学号 from (select score.student_id,score.num from score left join course on score.course_id=course.cid where cname="物理") as phy inner join (select score.student_id,score.num from score left join course on score.course_id=course.cid where cname="生物") as bio on phy.student_id=bio.student_id where phy.num>bio.num9、 查询没有同时选修物理课程和体育课程的学生姓名
select sname 姓名 from student where sid not in(select student_id from score left join course on score.course_id=course.cid where cname="体育" or cname="物理" group by student_id having count(course_id)>1));10、查询挂科超过两门(包括两门)的学生姓名和班级
select sname,caption from student inner join class onstudent.class_id=class.cid where student.sid in(select student_id from score where score.num<60 group by student_id having count(student_id)>=2);11 、查询选修了所有课程的学生姓名
select sname 姓名 from student where sid in (select student_id from score group by student_id having count(student_id) = (select count(cid) from course));12、查询李平老师教的课程的所有成绩记录
select course.cname 课程,score.num 成绩 from score inner join courseon course_id=cidwhere course_id in (select cid from course left join teacher on teacher_id=tidwhere tname like"李平%");13、查询全部学生都选修了的课程号和课程名
select cid,cname from course where cid in(select course_id from scoregroup by course_idhaving count(course_id) = (select count(sid) from student));14、查询每门课程被选修的次数
select course.cname 课程,count(course_id) 选修次数 from score inner join course on score.course_id=course.cidgroup by course_id;15、查询只选修了一门课程的学生姓名和学号
select sid 学号,sname 姓名 from studentwhere sid in(select student_id from score group by student_idhaving count(student_id)=1);16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
select sname 姓名,sum(num) 总分 from score inner join studenton score.student_id=student.sidgroup by student_idorder by sum(num) desc;17、查询平均成绩大于85的学生姓名和平均成绩
select student.sname 姓名,avg(num) 平均成绩 from student inner join scoreon student.sid=student_idgroup by student_idhaving avg(num)>85;18、查询生物成绩不及格的学生姓名和对应生物分数
select sname 姓名,num 分数 from score inner join student on student_id=student.sidwhere course_id in (select cid from course where cname="生物")having num <60;19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select student.sname 姓名,avg(num) 平均成绩 from score inner join studenton score.student_id=student.sidwhere course_id in (select cid from course inner join teacheron teacher_id=tidwhere teacher.tname like "李平%" )group by student_idorder by avg(num) desclimit 1;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
