MySQL之单表查询、多表查询

---

一、单表查询

一、 单表查询的语法

SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

二、 关键字的执行优先级(重点)

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1. 找到表:from
2. 拿着where指定的约束条件，去文件/表中取出一条条记录
3. 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4. 将分组的结果进行having过滤
5. 执行select
6. 去重
7. 将结果按条件排序：order by
8. 限制结果的显示条数

三、 表准备

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

四、 查询关键字之where约束

拿着where指定的约束条件，去文件/表中取出一条条记录

where字句中可以使用：

1. 比较运算符：>   <   >=   <=   !=

2. between 10 and 20 值在10到20之间

3. in(10,20,30) 值是10或20或30

4. 模糊查询
	关键字  
		like
	关键符号
		%:匹配任意个数的任意字符
		_:匹配单个个数的任意字符
	show variables like '%mode%';

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

wheret条件筛选：


# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;  


# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 简写



# 模糊查询
# 3.查询姓名中带有字母o的员工姓名和薪资
select name,salary from emp where name like '%o%';


# 4.查询姓名由四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) =4;


# 5.查询id小于3或者大于6的数据
select *  from emp where id not between 3 and 6;


# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);


# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

五、 查询关键字之group by分组

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
分组之后频繁需要使用的
	max		最大值
    min		最小值
    sum		求和
    count	计数
    avg		平均值

分组
	将单个单个的个体按照指定的条件分成一个个整体

"""
select post from employee group by post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数,如group_concat()、聚合函数
"""
# 严格模式
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'；


# 通过聚合函数
# 1.每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.每个部门的人数
select post,count(id) from emp group by post;
# 5.每个部门的月工资总和
select post,sum(salary) from emp group by post;


"""
可以给字段起别名(as还可以给表起别名)
    select post as '部门',sum(salary) as '总和' from emp group by post;
"""

# 查询分组之后的部门名称和每个部门下所有的员工姓名

group_concat()  获取分组以外的字段数据 并且支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;

concat()  未分组之前使用的拼接功能
select concat(name,':',sex) from emp;

concat_ws()
select concat_ws(':',name,sex,salary,age) from emp;

六、 查询关键字之having过滤

注意

功能上having与where是一模一样的
但是使用位置上有所不同
	where在分组之前使用
    having在分组之后使用
    
！！！执行优先级从高到低：where  >  group by  >  having 
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
# 1.先筛选出所有30岁以上的员工
	select * from emp where age>30;
# 2.然后再按照部门分组
	'''SQL语句的查询结构我们也可以直接看成是一张表'''
	select post,avg(salary) from emp where age>30 group by post;
# 3.分组之后做过滤操作
	select post,avg(salary) from emp 
    	where age>30 
        group by post 
        having avg(salary)>10000
        ;

七、 查询关键字之distinct去重

去重有一个非常严格的前提条件 数据必须是完全一样
	如果数据带有主键那么肯定无法去重
select distinct age from emp;

八、 查询关键字之order by排序

将结果按条件排序

按单列排序
# 查看emp表所有人按薪资升序排序
select * from emp order by salary;  # 默认是升序
select * from emp order by salary asc;  # 升序关键字 可以不写
select * from emp order by salary desc;  # 降序

# 排序也可以指定多个字段
select * from emp order by age desc,salary asc;

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
	select post,avg(salary) from emp 
    	where age>10
        group by post
        having avg(salary)>1000
        order by avg(salary);

九、 查询关键字之limit分页

用来限制数据的展示条数

select * from emp limit 5;  # 前五条（默认初始位置为0）
select * from emp limit 5,5;  # 起始位置、条数

# 查询工资最高的人的详细信息
	# 先按照工资排序 然后限制展示条数
select * from emp order by salary desc limit 1;

十、 查询关键字之regexp正则

正则表达式
	用一些特殊符号的组合去字符串中筛选出符合条件的数据
   	
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$'  j开头 中间无所谓 n或者y结尾

点击链接👉：正则表达式内容

二、 多表查询

一、 多表查询思想

1.子查询
	分步解决问题
    将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件
 
2.连表操作
	先将所有需要用到的表拼接到一起(一张表)
    然后就是转换成单表查询

二、 前期表准备

#建表
create table dep(
id int primary key auto_increment,
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',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

三、 子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
4：还可以包含比较运算符：= 、!=、> 、<、>=、<=

点击链接👉：带in、any、all、exists关键字子查询

# 查询jason所在的部门名称
	# 第一步 先获取jason所在的部门id
    select dep_id from emp where name='jason';
   	# 第二步 根据id号去部门表中筛选
    select * from dep where id = 200;
    # 完整句式
    select * from dep where id=(select dep_id from emp where name='jason');

四、 连表操作

# 前戏(了解)
# 交叉连接生成笛卡尔积
select * from emp,dep;

# 基于上表筛选数据(了解)
	'''为了避免字段冲突 可以在字段名前面加表名明确'''
select * from emp,dep where emp.dep_id=dep.id;


########################掌握############################
inner join	内连接	拼接公共的部分
	select * from emp inner join dep on emp.dep_id=dep.id;
	
left join	左连接 以左表为基准展示所有数据 没有的null填充
	select * from emp left join dep on emp.dep_id=dep.id;
	
right join	右连接 以右表为基准展示所有数据 没有的null填充
	select * from emp right join dep on emp.dep_id=dep.id;
	
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;

五、 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;

六、 多表查询题

1、 查询所有的课程的名称以及对应的任课老师姓名
4、 查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级

#####################关键字习惯都用大写###############################
# 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点  慢慢拼凑起来
-- 1、 查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要的表	course表 teacher表
-- 	select * from course;
-- 	select * from teacher;
# 2.连表操作 明确字段
-- SELECT
-- 	course.cname,
-- 	teacher.tname
-- FROM
-- 	course
-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先查看成绩表 
-- 	select * from score;
# 2.求所有学生的平均成绩
-- 	select score.student_id,avg(num) from score group by score.student_id;
# 3.筛选出大于80分
-- 	select score.student_id,avg(num) as 'avg_num' from score group by score.student_id having avg(num)>80
-- 	;
# 4.学生表与上述查询出来的表连接
-- SELECT
-- 	student.sname,
-- 	t1.avg_num 
-- FROM
-- 	student
-- 	INNER JOIN ( SELECT score.student_id, avg( num ) AS 'avg_num' FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 1.正向思路:课下可以尝试一下
# 2.反向思路:先找所有报了李平老师课程的学生 再取反
# 1.先查询李平老师教授的课程id号
-- select tid from teacher WHERE tname='李平老师';
-- select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师');
# 2.去成绩表中筛选出所有报了李平老师课程的学生id号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师')); 
# 3.去学生表中 取反获取没有报李平老师课程的学生姓名
-- SELECT
-- 	sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid NOT IN (
-- 	SELECT DISTINCT
-- 		student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		course_id IN (
-- 		SELECT
-- 			cid 
-- 		FROM
-- 			course 
-- 		WHERE
-- 		teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要)
# 1.先查询物理 和 体育课程的id号
-- 	select cid from course where cname in ('物理','体育');
# 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 3.按照学生id分组 统计每个学生报了的课程数目
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- 	group by student_id
-- 	having count(course_id) = 1
-- ;
# 4.去学生表中根据id获取学生姓名
-- SELECT
-- 	sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid IN (
-- 	SELECT
-- 		student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		course_id IN (
-- 		SELECT
-- 			cid 
-- 		FROM
-- 			course 
-- 		WHERE
-- 		cname IN ( '物理', '体育' ))
-- 	group by student_id
-- 	having count(course_id) = 1);
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先去成绩表中 筛选出分数小于60分的数据
-- select * from score where num<60;
# 2.按照学生id分组 然后统计个数
-- select student_id from score where num<60 group by student_id
-- 	having count(num) >= 2
-- ;
# 3.将班级表与学生表拼接起来
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) >= 2 );

数据库练习题大礼包