day48 查询关键字 多表查询思路

今日内容详细

查询关键字之having过滤

having与where的功能是一模一样的 都是对数据进行筛选
	where用在分组之前的筛选
  havng用在分组之后的筛选
为了更好的区分 所以将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
    ;

查询关键字之distinct去重

# 去重的前提 数据必须是一模一样的才可以(如果数据有主键肯定无法去重)
select distinct age from emp;
"""
等我们学到django orm之后 数据会被封装成对象 
那个时候主键很容易被我们忽略 从而导致去重没有效果!!!
"""

查询关键字之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;
# 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分页

# 分页即限制展示条数
# 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正则

select * from emp where name regexp '^j.*(n|y)$';
"""
补充说明:我们目前所讲的是MySQL查询关键字中使用频率较高的一些
其实还有一些关键字目前无需讲解 并且SQL语句里面同样还支持流程控制语法
如果感兴趣的话 课后可以自行百度查看 非常简单!!!
"""

回顾正则表达式：

常用的元字符

代码	说明
.	匹配除换行符以外的任意字符
\w	匹配字母或数字或下划线或汉字
\s	匹配任意的空白符
\d	匹配数字
\b	匹配单词的开始或结束
^	匹配字符串的开始（在集合字符里[^a]表示非（不匹配）的意思
$	匹配字符串的结束

常用的反义字符

代码/语法	说明
\W	匹配任意不是字母，数字，下划线，汉字的字符
\S	匹配任意不是空白符的字符
\D	匹配任意非数字的字符
\B	匹配不是单词开头或结束的位置
[^x]	匹配除了x以外的任意字符
[^aeiou]	匹配除了aeiou这几个字母以外的任意字符

常用的限定符

代码/语法	说明
*	重复零次或更多次
+	重复一次或更多次
?	重复零次或一次
	重复n次
	重复n次或更多次
	重复n到m次

常用的实用正则表达式整理（摘自自学编程网）

        只能输入数字："^[0-9]*$"。

　　只能输入n位的数字："^"d{n}$"。

　　只能输入至少n位的数字："^"d{n,}$"。

　　只能输入m~n位的数字：。"^"d{m,n}$"

　　只能输入零和非零开头的数字："^(0|[1-9][0-9]*)$"。

　　只能输入有两位小数的正实数："^[0-9]+(.[0-9]{2})?$"。

　　只能输入有1~3位小数的正实数："^[0-9]+(.[0-9]{1,3})?$"。

　　只能输入非零的正整数："^"+?[1-9][0-9]*$"。

　　只能输入非零的负整数："^"-[1-9][]0-9"*$。

　　只能输入长度为3的字符："^.{3}$"。

　　只能输入由26个英文字母组成的字符串："^[A-Za-z]+$"。

　　只能输入由26个大写英文字母组成的字符串："^[A-Z]+$"。

　　只能输入由26个小写英文字母组成的字符串："^[a-z]+$"。

　　只能输入由数字和26个英文字母组成的字符串："^[A-Za-z0-9]+$"。

　　只能输入由数字、26个英文字母或者下划线组成的字符串："^"w+$"。

　　验证用户密码："^[a-zA-Z]"w{5,17}$"正确格式为：以字母开头，长度在6~18之间，只能包含字符、数字和下划线。

　　验证是否含有^%&’,;=?$""等字符："[^%&’,;=?$"x22]+"。

　　只能输入汉字："^["u4e00-"u9fa5]{0,}$"

　　验证Email地址："^"w+([-+.]"w+)*@"w+([-.]"w+)*"."w+([-.]"w+)*$"。

　　验证InternetURL："^http://(["w-]+".)+["w-]+(/["w-./?%&=]*)?$"。

　　验证电话号码："^("("d{3,4}-)|"d{3.4}-)?"d{7,8}$"正确格式为："XXX-XXXXXXX"、"XXXX- XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX"。

　　验证身份证号(15位或18位数字)："^"d{15}|"d{18}$"。

　　验证一年的12个月："^(0?[1-9]|1[0-2])$"正确格式为："01"～"09"和"1"～"12"。

　　验证一个月的31天："^((0?[1-9])|((1|2)[0-9])|30|31)$"正确格式为;"01"～"09"和"1"～"31"。

　　利用正则表达式限制网页表单里的文本框输入内容：

　　用正则表达式限制只能输入中文：οnkeyup="value=value.replace(/[^"u4E00-"u9FA5] /g,’’)" onbeforepaste="clipboardData.setData(’text’,clipboardData.getData(’text’).replace(/[^"u4E00-"u9FA5]/g,’’))"

　　用正则表达式限制只能输入全角字符： οnkeyup="value=value.replace(/[^"uFF00-"uFFFF]/g,’’)" onbeforepaste="clipboardData.setData(’text’,clipboardData.getData(’text’).replace(/[^"uFF00-"uFFFF]/g,’’))"

　　用正则表达式限制只能输入数字：οnkeyup="value=value.replace(/[^"d]/g,’’) "onbeforepaste="clipboardData.setData(’text’,clipboardData.getData(’text’).replace(/[^"d]/g,’’))"

　　用正则表达式限制只能输入数字和英文：οnkeyup="value=value.replace(/["W]/g,’’) "onbeforepaste="clipboardData.setData(’text’,clipboardData.getData(’text’).replace(/[^"d]/g,’’))"

　　得用正则表达式从URL地址中提取文件名的javascript程序，如下结果为page1

以下是引用片段：
s="http://www.9499.net/page1.htm"
s=s.replace(/(.*"/){0,}([^".]+).*/ig,"$2")
alert(s)
　　匹配双字节字符(包括汉字在内)：[^"x00-"xff]

　　应用：计算字符串的长度(一个双字节字符长度计2，ASCII字符计1)

以下是引用片段：
String.prototype.len=function(){return this.replace([^"x00-"xff]/g,"aa").length;}
　　匹配空行的正则表达式："n["s| ]*"r

　　匹配HTML标记的正则表达式：/<(.*)>.*<"/"1>|<(.*) "/>/

　　匹配首尾空格的正则表达式：(^"s*)|("s*$)

以下是引用片段：
String.prototype.trim = function()
{
return this.replace(/(^"s*)|("s*$)/g, "");
}
　　利用正则表达式分解和转换IP地址：

　　下面是利用正则表达式匹配IP地址，并将IP地址转换成对应数值的Javascript程序：

以下是引用片段：
function IP2V(ip)
{
re=/("d+)".("d+)".("d+)".("d+)/g //匹配IP地址的正则表达式
if(re.test(ip))
{
return RegExp.$1*Math.pow(255,3))+RegExp.$2*Math.pow(255,2))+RegExp.$3*255+RegExp.$4*1
}
else
{
throw new Error("Not a valid IP address!")
}
}
不过上面的程序如果不用正则表达式，而直接用split函数来分解可能更简单，程序如下：

以下是引用片段：
var ip="10.100.20.168"
ip=ip.split(".")
alert("IP值是："+(ip[0]*255*255*255+ip[1]*255*255+ip[2]*255+ip[3]*1))
————————————————
版权声明：本文为CSDN博主「墨遥」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xuemoyao/article/details/8033138

多表查询思路

# 多表查询的思路总共就两种
	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			左连接
  	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填充'''
答案求解
	select dep.name from emp 
  inner join dep on emp.dep_id=dep.id
  where emp.name='jason'
  ;
  
"""
了解
我们学会了连表操作之后 其实就可以将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+?)
  	# 
    --
# 运行SQL文件

多表查询练习题

-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- 首先就是读懂题目然后判断题目所需要什么
-- 首先看这个题目里边有课程跟老师 说一我们也需要看这两张表
-- 所以大致查看一下这两张表，course表和teacher表
查看两张表的结果如下；
select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
select *from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)
-- SELECT * from course INNER JOIN teacher on course.teacher_id=teacher.tid;
-- 通过上述	SQL语句拿到了一张表 
+-----+--------+------------+-----+-----------------+
| cid | cname  | teacher_id | tid | tname           |
+-----+--------+------------+-----+-----------------+
|   1 | 生物   |          1 |   1 | 张磊老师        |
|   2 | 物理   |          2 |   2 | 李平老师        |
|   3 | 体育   |          3 |   3 | 刘海燕老师      |
|   4 | 美术   |          2 |   2 | 李平老师        |
+-----+--------+------------+-----+-----------------+
我们看题目 题目是说只要课程的名字和老师的名字所以，
-- 以为涉及到多表查询所以我们要用表的前缀；
-- 所以SQL语句如下
SELECT course.cname,teacher.tname FROM course INNER JOIN teacher on course.teacher_id=teacher.tid;
结果如下：
+--------+-----------------+
| cname  | tname           |
+--------+-----------------+
| 生物   | 张磊老师        |
| 物理   | 李平老师        |
| 体育   | 刘海燕老师      |
| 美术   | 李平老师        |
+--------+-----------------+
-、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到几张表 student score 然后查询这两张表的信息
学生基本信息：
select *from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   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 | 刘四   |
+-----+--------+----------+--------+

学生分数信息：
 select * from score;
+-----+------------+-----------+-----+
| sid | student_id | course_id | num |
+-----+------------+-----------+-----+
|   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 |
+-----+------------+-----------+-----+
# 先获取平均成绩大于80分的学生信息(按照student_id分组)
 select score.student_id,avg(num) from score group by score.student_id;
+------------+----------+
| student_id | avg(num) |
+------------+----------+
|          1 |  28.3333 |
|          2 |  58.3333 |
|          3 |  82.2500 |
|          4 |  64.2500 |
|          5 |  64.2500 |
|          6 |  69.0000 |
|          7 |  66.0000 |
|          8 |  66.0000 |
|          9 |  67.0000 |
|         10 |  74.2500 |
|         11 |  74.2500 |
|         12 |  74.2500 |
|         13 |  87.0000 |
+------------+----------+
select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
+------------+---------+
| student_id | avg_num |
+------------+---------+
|          3 | 82.2500 |
|         13 | 87.0000 |
+------------+---------+
# 结果需要从两个表里面的获取  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;
+--------+---------+
| sname  | avg_num |
+--------+---------+
| 张三   | 82.2500 |
| 刘三   | 87.0000 |
+--------+---------+
-- 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 );
  
  
"""
重点掌握上述五道题目即可 如果还想扩展 可以考虑下面的题目
https://www.cnblogs.com/Dominic-Ji/p/10875493.html
只需要完成三分之一及以上即可!!!
"""

补充

on、where、having的区别

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。

   根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。

   在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。

   如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

   在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什幺时候起作用，然后再决定放在那里

今日单词仅供参考

```css
department：部门
avg：平均值
avg_salary:平均工资
distinct：不同的
order：顺序
limit:限度
increment ：定期的加薪 ; 增量 ; 增加
default： 违约 ; 默认 ; 预设 ; 不履行义务 ; 预置 ; 系统设定值 
left:左边
right：使回到正常位置;把…扶正;使…直立;改正;纠正;使恢复正常;向右
ctrl：控制键
course：课程
class：班级、
score：分数