创建两张表
# 创建表 create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); # 插入记录 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',38,201), ('nick','female',28,202), ('owen','male',18,200), ('jerry','female',18,204) ; # 当初我们把表分开是为了放标管理,在硬盘上确实是多张表,但是到了内存中我们应该吧他们在拼成一张表尽心查询才合理
表查询
select * from emp,dep; # 左表(emp)一条记录与右表(dep)所有记录都对应一遍>>>笛卡尔积
# 将所有数据都对应了一遍,虽然不合理,但是其中有合理的数据,限制我们需要做的就是找出合理的数据 # 1.查询员工及所在部门信息 select * from emp,dep where emp.dep_id = dep.id;
# 2.查询部门为技术部的员工及部门信息 select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';
1.内连接 inner join
# 只取两张表有对应关系的记录 select * from emp inner join dep on emp.dep_id = dep.id; select * from emp inner join dep on emp.dep_id = dep.id where dep.name = '技术'
2.左连接 left join
# 在内连接的基础上保留左表全部的记录 select * from emp left join dep on emp.dep_id = dep.id;
3.右连接 right join
# 在内连接的基础上保留右表全部的记录 select * from emp right join dep on emp.dep_id = dep.id;
4.全连接 union
# 在内连接的基础上 保留左右两张表的即使没有对应关系的记录 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;
子查询
# 核心就是查询语句的结果,用括号括起来,当做另外一个查询语句的条件用 # 1.查询部门是技术或者人力资源的员工信息 子查询方法 解析: 1.先拿到 技术部和人力资源部的 id 2.以拿到的技术部和人力资源部的id为条件,筛选出该两个部门的人员信息 select * from emp where emp.dep_id in (select dep.id from dep where name in ('技术','人力资源')); 连表查新 解析: 1.将两个表内连接 连接条件是 emp.dep_id = dep.id 2.连接后的表 筛选出技术部和人力资源部的员工 select * from emp inner join dep on emp.dep_id = dep.id where dep.name in ('技术','人力资源')
# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询 select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1 inner join (select post,max(hire_date) as max_date from emp group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date ; select name from dep where id = (select dep_id from emp where avg(age) > 25);
# 3.查询平均年轻在25岁以上的部门名 解析: 1.将两个表内连接 连接条件是 emp.dep_id = dep.id 2.连接后的表按部门分组 3.对分组后的表筛选 条件是 avg(age) > 25 select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25; select * from dep where id in (select emp.dep_id from emp group by dep_id having avg(age) > 25;) ''' 规律: 表的查询结果可以作为作为其他表的查询条件,也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询 '''
exist
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录， 而是返回一个真假值，True或False。 当返回True时，外层查询语句将进行查询 当返回值为False时，外层查询语句不进行查询。 select * from emp where exists (select id from dep where id > 3); select * from emp where exists (select id from dep where id > 250);
导入 .sql文件
/* 数据导入： Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; 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` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- 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` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT; -- ---------------------------- -- 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` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT; -- ---------------------------- -- 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` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); COMMIT; -- ---------------------------- -- 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` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
打开navicat新建数据库，选中新建的数据库鼠标右键选择运行SQL文件
弹出文件框，选中要导入的.sql文件即可
练习题
1、查询所有的课程的名称以及对应的任课老师姓名 select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;
4、查询平均成绩大于八十分的同学的姓名和平均成绩 解析: 1.每个学生都有几门课程成绩,按学生id分组,再筛选出平均成绩大于80的 as t1 2.将student 与 t1 表内连接,内连接条件 student.sid = t1.student_id SELECT student.sname, t1.avg_num FROM student INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;
7、 查询没有报李平老师课的学生姓名 解析: 1.先获取李平老师的id 2.根据老师id获取师课程的id 3.根据课程id获取此课程id的学生的id 4.再反取学生id SELECT student.sname FROM student WHERE student.sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )
8、 查询没有同时选修物理课程和体育课程的学生姓名 select student.sname from student where student.sid in( select distinct score.student_id from score where score.course_id in (select course.cid from course where course.cname = '物理' or course.cname = '体育')group by student_id having count(course_id) = 1);
9、 查询挂科超过两门(包括两门)的学生姓名和班级 解析: select * from class inner join student on class.cid = student.class_id where student.sid in (select score.student_id from score where num < 60 group by score.student_id having count(course_id) >=2) ;