MySQL-4
目录
mysql
表查询关键字
having过滤
'where'是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在'结果返回之前起作用'，且“where”后面不能写“聚合函数”。
'having'是一个过滤声明，是在查询数据库结果返回之后进行过滤，即在'结果返回值后起作用'，并且“having”后面可以写“聚合函数”。
# where、聚合函数、having在from后面的执行顺序：
where > 聚合函数(sum,min,max,avg,count) > having
having与where的功能是一模一样的 都是对数据进行筛选,where用在分组之前的筛选,having用在分组之后的筛选,为了更好的区分,所以将where说成筛选 having说成过滤
例
# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
'''编写SQL语句 不要指望着一步到位 边写边看慢慢拼凑'''
# 1.先获取每个部门年龄在30岁以上的员工的平均薪资
先筛选出30岁以上的员工数据 然后再对数据进行分组
select post,avg(salary) from emp where age>30 group by post;
# 2.在过滤出平均薪资大于10000的数据
针对分组之后的数据再次筛选 需要使用having而不是where
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000
;
'''针对聚合函数 如果还需要在其他地方作为条件使用 可以先起别名'''
select post,avg(salary) as avg_salary from emp
where age>30
group by post
having avg_salary > 10000
;
注意事项
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
where和having的使用
# (1) where和having均可使用：
select score name from student where score>60;
select score name from student having score>60;
'可用having的原因是已经筛选出score字段，在这种情况下是和where等效的。'
# (2) 只能用where、不能用having
select score name from student where sex="man"; # 正确的
select score name from student having sex="man"; # 错误的，因为前面没有筛选sex。
# (3) 只能用having、不能用where
select goods_category_id,avg(goods_price) as ag from goods_table group by goods_category having ag>100; # 正确的
select goods_category_id,avg(goods_price) as ag from goods_table where ag>100 group by goods_category; # 错误的
'注意:'
where 后面要跟的是数据表里的字段，如果把ag换成avg(goods_price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。
distinct去重
# 采用DISTINCT关键字去除返回结果中的重复项，NULL也是一类数据，返回结果中如果有多个null，使用distinct将多个null合并为一条。
# 去重的前提 数据必须是一模一样的才可以(如果数据有主键肯定无法去重)
select distinct age from emp;
"""
学习django orm之后，数据会被封装成对象
那个时候主键很容易被我们忽略 从而导致去重没有效果
"""
# 多列使用
多列数据都相同的情况下会被合并，（distinct只能放在第一个字段名之前）
select DISTINCT age,salary from shop;
order by排序
# 1.按照薪资高低排序
select * from emp order by salary; # 默认是升序(从小到大)
select * from emp order by salary asc; # 升序排列,关键字asc,可以省略
select * from emp order by salary desc; # 降序(从大到小)
# 2.先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序
select * from emp order by age asc,salary desc;
'第一个优先级最高(age)，升序排列，遇到相同时，再按salary的数据降序排列。'
# 3.统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照从大到小的顺序排序
select post,avg(salary) as avg_salary from emp
where age > 10
group by post
having avg_salary > 1000
order by avg_salary desc;
limit分页
# LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
'LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。'如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
'初始记录行的偏移量是 0(而不是 1)'
举例
SELECT * FROM table LIMIT 5,10; # 检索记录行 6-15
# 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.
# 如果只给定一个参数，它表示返回最大的记录行数目：
SELECT * FROM table LIMIT 5; # 检索前 5 个记录行
也可以理解为：分页即限制展示条数
# 1.限制只展示五条数据
select * from emp limit 5;
# 2.分页效果
select * from emp limit 5,5;
# 3.查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
"""
当数据特别多的时候 经常使用limit来限制展示条数 节省资源 防止系统崩溃
"""
regexp正则
模式 模式匹配什么
^ 匹配字符串开头
$ 匹配字符串结尾
. 匹配任意单个字符
[...] 匹配方括号间列出的任意字符
[^...] 匹配方括号间未列出的任意字符
p1|p2|p3 交替匹配任意 p1 或 p2 或 p3
* 匹配前面的元素的零次或多次
+ 匹配前面的元素的一次或多次
{n} 匹配前面的元素 n 次
{m,n} 匹配前面的元素 m 至 n 次
select * from emp where name regexp '^j.*(n|y)$';
"""
补充说明:我们目前所讲的是MySQL查询关键字中使用频率较高的一些
其实还有一些关键字目前无需讲解 并且SQL语句里面同样还支持流程控制语法
如果感兴趣的话 课后可以自行百度查看 非常简单!!!
"""
多表查询思路
多表查询的思路总共就两种
# 1.子查询
就相当于是我们日常生活中解决问题的方式(一步步解决)
将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
子查询的步骤：
1.先查jason所在的部门编号
2.根据部门编号去部门表中查找部门名称
# 2.连表操作
先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
连表操作：
1.先将员工表和部门表按照某个字段拼接到一起
2.基于单表查询
实际演练
create table dep(
id int primary key auto_increment,
name varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
age int,
dep_id int
);
insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'),(205,'安保');
insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);
# 使用子查询 获取jason所在的部门名称
# 1.先获取jason的部门编号
select dep_id from emp where name='jason';
# 2.将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='jason');
# 使用连表操作 获取jason所在的部门名称
笛卡尔积(了解知识)
select * from emp,dep; # 会讲所有的数据全部对应一遍
select * from emp,dep where emp.dep_id=dep.id; # 效率低下
"""
1.一条SQL语句的查询结果 我们也可以看成是一张虚拟表
2.如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分
"""
连表操作关键字
inner join——内连接
就是两张表共有的部分(内连接)，取交集。
select * from emp inner join dep on emp.dep_id=dep.id;
'''只连接两张表中有对应关系的数据'''
left join——左连接
A独有的部分加上和A和B公共 的部分。也叫左外连接。
select * from emp left join dep on emp.dep_id=dep.id;
'''以左表为基准 展示所有的数据 没有对应项则用NULL填充'''
right join——右连接
恰好跟左外连接相反（右外连接）。
select * from emp right join dep on emp.dep_id=dep.id;
'''以右表为基准 展示所有的数据 没有对应项则用NULL填充'''
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;
'''左右两表数据全部展示 没有对应项则用NULL填充'''
我们学会了连表操作之后，其实就可以将N多张表拼接到一起
思路:我们可以将两张表拼接之后的结果起别名当做一张表使用
然后再去跟另外一张表拼接
select * from emp inner join
(select emp.id as epd,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1
on emp.id=t1.epd;
可视化软件之Navicat
Navicat可以充当很多数据库软件的客户端 提供了图形化界面能够让我们更加快速的操作数据库
# 下载
navicat有很多版本 并且默认都是收费使用
正版可以免费体验14天
针对这种图形化软件 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)
# 使用
内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
连接数据库 创建库和表 录入数据 操作数据
外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)
# 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写
SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)
多表查询练习
数据准备
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;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
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;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
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;
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');
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;
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', '刘四');
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;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
SET FOREIGN_KEY_CHECKS = 1;
题目
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
解决
'1、查询所有的课程的名称以及对应的任课老师姓名'
# 1.先明确需要几张表 course表 teacher表
# 2.大致查找一些表中的数据情况
# 3.既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)
# 4.编写完成后 使用美化功能 将SQL语句规范化
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
'4、查询平均成绩大于八十分的同学的姓名和平均成绩'
# 1.先明确需要用到几张表 student score
# 2.大致查看一下两张表里面的数据
# 3.先获取平均成绩大于80分的学生信息(按照student_id分组)
-- select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
# 4.结果需要从两个表里面的获取 student SQL语句执行之后的虚拟表
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN ( SELECT 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、查询没有报李平老师课的学生姓名'
# 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生id 之后取反即可)
# 1.先明确需要用到几张表 四张表
# 2.先查询李平老师的编号
-- select tid from teacher where tname='李平老师'
# 3.再查李平老师教授的课程编号
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
# 4.根据课程编号 去score表中筛选出所有选了课程的学生编号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
# 5.根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名
-- 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 = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )
'8、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门)'
# 1.先明确需要用到几张表 三张
# 2.先获取物理课程和体育课程的编号
-- select cid from course where cname in ('物理','体育');
# 3.再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 4.如何剔除选了两门的数据(按照学生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;
# 5.根据上述学生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.先明确需要几张表 三张表
# 2.先去score表中筛选出所有不及格的数据
-- select * from score where num < 60;
# 3.如何筛选每个学生挂科的门数(按照学生id分组 对学科计数即可)
-- select student_id from score where num < 60 group by student_id
-- HAVING count(course_id) >= 2;
# 4.由于最终的结果需要取自两张表 所以应该拼接
-- select student.sname,class.caption from class inner join student on class.cid=student.class_id;
# 5.使用步骤3获取到的学生编号 对步骤4的表结果筛选数据
SELECT
student.sname,
class.caption
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( course_id ) >= 2 );
