python四十二期---mysql创表后插入数据后------查询数据相关知识

补充

在mysql里面在操作mysql的时候，可以先写一些注释，比如这样写：
/*创建员工信息表*/
/*插入数据*/
这样写完回车一下不会报错，mysql也能正常执行！！！下面正常写sql语句就行了！！！

.
.
.
.
.
.
.

昨日内容回顾

• 无符号与零填充

unsigned

zerofill


* 非空
  not null


* 默认值
  default

-------------------------------

* 唯一值


  unique
  1.单列唯一
  	id int unique
  2.多列唯一
  	ip varchar(32),
    port int,
    unique(ip,port)

-------------------------------

* 主键

  primary key
  1.单列主键(热门)
  	id int primary key

  2.联合主键(冷门)
  	uid int,
    sid int,
    primary key(uid,sid)

  """
  1.单从约束层面上是非空且唯一
  2.innodb存储引擎规定了表必须有且只有一个主键
  3.基于主键查询数据速度会非常的快 并且主键是组织表数据的必备条件
  4.如果创建表的时候没有定义主键那么有两种情况
  	没有主键也没有非空且唯一的字段>>>:用隐藏字段作为主键(无法使用)
  	没有主键但是有非空且唯一的字段>>>:自动升级(从上往下)一个为主键！！
  """

-------------------------------

* 自增

  auto_increment
  主要是用来配合主键一起使用完成字段的自增 减少数据录入的麻烦
  """
  自增特性
  	不会因为数据的删除而自动回退、重置
  truncate格式化表
  """

-------------------------------

* 字段类型与字段约束条件的关系

  name varchar(16) not null
  约束条件是基于字段类型之上的额外限制


* 外键前戏

  外键:用来建立数据与数据之间的关系


-------------------------------
-------------------------------

* 各种关系的判断与SQL建立


  采用换位思考原则： 判断每一边一条数据能否对应另外一边多条数据！！！！！！

  一对多
    如果判断的结果是一边可以一边不可以 那么就是一对多
    	ps:外键字段建在多的一方
  多对多
    如果判断的结果是两边都可以 那么就是多对多
    	ps:需要创建第三张关系表专门记录
  一对一
    如果判断的结果是两边都不可以 那么就是一对一或者没有关系
    	ps:外键字段建在任何一方都可以 但是建议建在查询频率较高的表中
    cascade就是级联或者小瀑布的只要就是要表达：连续传递过程  的意思


  一对多关联表：
  dep_id int,
  foreign key(dep_id) references dep(id)
  on update cascade
  on delete cascade


  多对多关联表：
  book_id int,
  foreign key(book_id) references book(id)
  on update cascade
  on delete cascade,
  author_id int,
  foreign key(author_id) references author(id)
  on update cascade
  on delete cascade


  一对一关联表：
  detail_id int unqieu,
  foreign key(detail_id) references user_detail(id)
  on update cascade
  on delete cascade

今日内容概要

• 报错及作业讲解

* SQL语句查询关键字
  select
  from
  where
  group by
  having
  distinct
  order by
  limit
  regexp


* 多表查询的两种方式

  子查询
  连表操作

今日内容详细

报错及作业讲解

报错
	1.粗心大意 单词拼写错误
	2.手忙脚乱 不会看报错思考错误的核心
---------------------------------------
作业讲解
表与表中数据的关系可能会根据业务逻辑的不同发送改变 不是永远固定的
--------------------------------------------
	服务器表与应用程序表
	一台服务器可以运行多个应用程序
	一个应用程序也可以运行在多台服务器上
	多对多
---------------------------------------------
课程表与班级表：
	一门课程可以属于多个班级
	一个班级可以拥有多门课程
	多对多
----------------------------------------------
学生表与班级表
	一名学生不能属于多个班级
	一个班级可以拥有多名学生
	一对多
----------------------------------------------
老师表与课程表
	一名老师可以教授多门课程
	一门课程可以被多名老师教
	多对多
----------------------------------------------
书籍表与出版社表
	一本书不可以多个出版社出版
	一个出版社可以出版多本书
	一对多
	ps:上述关系的确认并不是绝对
对于我们而言不单单要学会关系判断还要非常熟练的敲出SQL语句

.
.

补充一下：

# SQL语句中关键字的执行顺序和编写顺序并不是一致的

我们在写sql语句时顺序，和sql执行语句的顺序由时候是有一点点的区别的！！
并不一定完全一致！！！

-------------------

例如:
select id,name from userinfo;
我们先写的select再写的from
但是执行的时候是先执行的from在执行select

-----------------------------------------------

对应关键字的编写顺序和执行顺序我们没必要过多的在意 熟练之后会非常自然的编写
我们只需要把注意力放在每个关键字的功能上即可

.
.
.
.
.
.

SQL语句----查询关键字

select				指定需要查询的字段信息
	select *			查所有字段信息
	select name 			查name字段信息
	select char_length(name) 	查对字段处理后的信息

------------------------------------------------

select总结就是查！！！！！  存到mysql里面的各种信息！！！

------------------------------------------------

from				指定需要查询的表信息
	from mysql.user
	from t1

------------------------------------------------

from总结就是指定！！！   各种表！！！

------------------------------------------------

.
.
.
.
.
.
.
.

前期数据准备

# 创建员工信息表
create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    gender 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,gender,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);

.
.
.
.
.
.

编写SQL语句的小技巧

针对select后面的字段名可以先用*占位往后写 最后再回来修改
-----------

在实际应用中select后面很少直接写*  因为*表示所有
当表中字段和数据都特别多的情况下非常浪费数据库资源

-----------

SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补

-----------

.
.
.
.
.
.

1. 查询关键字----where

# 限定查询条件的也就是过滤作用

# Where 是sql语句中用来限定查询条件的，符合条件的则在结果中显示，不符合则不在结果中显示。

# 在MySQL中也有很多内置方法  使用： help 方法名   可以查看帮助手册了解使用方法

# 1.查询id大于等于3小于等于6的数据
select * 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.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

-------------------------------------------

.
.
.
.
.

模糊查询 关键字 like

一般配合where使用的！！！

# 4.查询员工姓名中包含字母o的员工姓名与薪资
条件不够精确的查询称之为：   模糊查询
模糊查询的关键字是：        like

----------------------------------------

1. 模糊查询的常用符号	%    匹配任意个数的任意字符
	%o%  首尾不管只要含有o就行比如   o  jason  owen  loo  wwoww
	%o   首不管只要末尾含有o就行比如   o  asdasdo  asdo

select * from emp where name like '%o%';

----------------------------------------------------

2. 模糊查询的常用符号	_    匹配单个个数的任意字符
	_o_   o的左右边只能有一个单个任意字符    aox  wob  iok
	o_    o的右边只能有一个单个任意字符      oi   ok    ol

select * from emp where name like '_o%';
select * from emp where name like '_o_';

-----------------------------------------------------

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

-----------------------------------------------------

# 6.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select * from emp where post_comment=NULL;  		不能这样写，会报错
select * from emp where post_comment is NULL;		可以

------------------------------------------------------

.
.
.
.
.
.

2. 查询关键字----group by

# 分组

# group by 的解释：对哪个字段 （ 哪几个字段）进行分组。

分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
     将班级学生按照性别分组
     将全国人民按照民族分组
     将全世界的人按照肤色分组
------------

分组的目的:   是为了更好的统计相关数据 !!!
eg:
    每个班级的男女比例
    每个民族的总占比
    每个部门的平均薪资

---------------------------------------------------

1.将员工数据按照职位分组
	select * from emp group by post;

post就是表里面的字段名，或者说就是分组的依据！！！
按职业分组，职位就是最小操作单位！！
注意：按什么分组，什么将来就是最小操作单位

-----------------------------------------------------

# 注意：
# MySQL5.6默认不会报错，但是显示结果也是有问题的！！
# MySQL5.7及8.0默认都会直接报错

------------------------------------------------------

需要配置文件那边严格模式那再加点命令 only_full_group_by
set global sql_mode='strict_trans_tables,only_full_group_by'

------------------------------------------------------

原因是分组之后 select后面默认只能直接填写分组的依据!!!,不能再写其他字段!!!
	select post from emp group by post;
	select age from emp group by age;
---------
以什么分组，select后面就写啥！！！
分组之后默认的最小单位就应该是组,而不应该再是组内的单个数据单个字段!!!

------------------------------------------------------

.
.分组后只能显示组里面的有的数据了，并且还不能重复！！！！！！

.
.
.
.
.
.

分组可以实现哪些操作！！！

1. 获取 每个职位 的最高工资

-------------------------------------------

聚合函数！！！
	专门用于分组之后的数据统计！！！！！！
	max\min\sum\avg\count
	最大值、最小值、求和、平均值、统计个数
注意聚合函数是对分组后的每一个组里面所包含的所有的数据进行统计的！！！
打个比方职位组里面有4各不同的职位，
那么max就是对这每一个职位里面的而所有数据进行一个统计！！
比如第一个职位里面包含100各人的信息，那么这个最大值就是求得这100个人得最大值，
同理第二，第三，第四个职位也这样求最大值！！

------------------------------------------------------

1.   获取 每个职位 的最高工资
'''要不要分组我们完全可以从题目的需求中分析出来，尤其是出现关键字 每个 平均'''
select post,max(salary) from emp group by post;

自动统计职位字段名下，每个不同的职位里最高薪资是多少！！！
当以什么字段分组之后，select后面就不能出现其他字段，
要想要查看组里面包含的其他字段的信息，就必须要用聚合函数！！！

--------------------------------------------------------

针对sql语句执行之后的结果，我们是可以给字段名称取别名 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;

---------------------------------------------------------

.

.
.
.
.
.
.

2. 一次获取职位薪资相关统计

2.   一次获取职位薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;

自动统计职位字段名下，每个不同的职位里最高薪、最低薪等，各是多少  ！！！
as 可以不写，但是不建议不写！！！！！
--------------------------------------------------

.
.
.
.
.

3. 统计每个职位的人数

select post,count(id) from emp group by post;

count 是统计个数     对哪个字段进行统计个数？
最好是找一个具有唯一标识性的字段，去统计个数，所以用id来计数！！！

好像这样写count(*) 也行

.
.

4. 统计每个职位的职位名称 和 职位下的员工姓名

# 关键字：group_concat()

concat 合并多个数组（n）

统计每一个职位的职位名称非常好统计，因为我们就是以职位分组的！！！
关键是：每一个职位里面的所有员工的姓名怎么统计！！！
因为select后面是没办法直接接name的，因为我们是以职位分组的，
默认情况下select后面只可以直接填写post 其他字段是不能直接填写的！！！

# 分组以外的字段无法直接填写 需要借助于方法

select post,name from emp group by post;  # 语法不对，直接报错！！！

select post,group_concat(name) from emp group by post;
-------------------------------------------------------

group_concat()  是专门用来帮我们获取分组之后，除分组字段之外的其他字段数据！！
并都放到一起展示！！！

对员工表按照职位分组，然后获取职位的名称，以及每个职位下面的员工姓名，
并把员工姓名放到一起展示！！

------------------------------------------------------

select post,group_concat(name,age) from emp group by post;

group_concat（）  括号里面可以写多个字段数据！！！就可以拿多个字段数据了！！！

-------------------------------------------------------

select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;

甚至可以规定group_concat(）括号里面的两个字段数据在拼接的时候，中间可以加什么字符！！！

--------------------------------------------------------

.

.

.
.
.
.
.
.
.

3.查询关键字----having 过滤

# select 语句执行顺序大致是：
where（数据查询） -> group by（数据编组） -> having（结果过滤） -> order by（排序）

having与where本质是一样的 都是用来对数据做筛选
只不过where用在分组之前(首次筛选)
having用在分组之后(二次筛选)

--------------------------------------------

1.统计各职位里面年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''
稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
每条SQL的结果可以直接看成就是一张表
基于该表如果还想继续操作，则直接在产生该表的SQL语句上添加SQL语句即可！！！
'''

步骤1:先筛选出所有年龄大于30岁的员工数据
	select * from emp where age > 30;

步骤2:再对筛选出来的数据按照职位分组并统计平均薪资
	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;
----------------------------------------------

# 注意：
统计各职位里面年龄在30岁以上的员工平均工资 并且保留大于10000的数据
这句话里面虽然（年龄在30岁以上）这个定语是在各职位（分组）的后面，但是你要想，
分组之后的聚合函数都是对每一个组里面的所有数据操作的，
所以30岁以上这个筛选条件必须要在分组之前完成！！否则求的平均值就不对了！！！

.
.
.
.
.

4.查询关键字----distinct

# 去重
distinct 清楚的，明显的（adj）
distinction 区别, 差别, 特征，卓越, 优秀（n）
distinguish 辨别, 区别,显扬自己, 使自己扬名(v)
distinguished 卓越的;著名的;受人尊敬的


# 去重有一个必须的条件也是很容易被忽略的条件
# 数据必须一模一样才可以去重！！！！！！

-----------------------------------------

select distinct id,age from emp;

distinct关键字针对的是 它后面所有字段组合的结果的去重！！！
-----------------------------------------

select age from emp;
select distinct age from emp;   # 这样就能把age里面重复的去除了！！！

select age,post from emp;
select distinct age,post from emp;
# 这样就能把age与post组合后结果还重复的去除了！！！

.
.
.
.
.
.

5.查询关键字----order by

# 排序
descent 下降（n）
ascent 上升（n）


1.可以是单个字段排序
select * from emp order by age asc;  默认升序，从小到大(asc可以省略)
---------------
select * from emp order by age desc;  降序
----------------------------------------------

2.也可以是多个字段排序
先按照第一个字段排序，当第一个字段下有一样大小的数据时，
再按照第二个字段的要求对第一个字段一样的数据排序
那么如果第一个字段里面的所有数据没有一样的数据，那么就不会再执行第二个字段的排序了！！

select * from emp order by age asc,salary desc;
先按照年龄升序排（asc可以省略）,相同的情况下再按照薪资降序排！！！！！！

-----------------------------------------------

.
.
.
.
.
.

排序练习

统计各职位里面年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，
然后对平均工资进行排序
--------------------------------------------------------
1.先筛选出所有年龄大于10岁的员工
	select * from emp where age > 10;
--------------------------------------------------------
2.再对他们按照部门分组统计平均薪资
	select post,avg(salary) from emp where age > 10 group by post;
--------------------------------------------------------
3.针对分组的结果做二次筛选
	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
---------------------------------------------------------
4.最后按照指定字段排序
	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
---------------------------------------------------------
补充：
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果时,
我们可以节省操作(主要是节省了底层运行效率,代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;

给聚合函数avg(salary)取别名后，只需要调用一次聚合函数就可以了，提高代码的运行效率了！！
---------------------------------------------------------

对聚合函数的起别名好像不行！！

.
.
.
.
.
.

6. 查询关键字----limit

# 分页

通过关键字 limit 数字        来限制展示的数据有多少！！！

当表中数据特别多的情况下,我们很少会一次性获取所有的数据！！！
很多网站也是做了分页处理 一次性只能看一点点

---------------------------------------

select * from emp;  # 不写limit 就只能一次性查看全部数据了！！！

select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5;  从第5条开始往后读取5条

---------------------------------------

查询工资最高的人的详细信息！！！
'''千万不要惯性思维，一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;

.

.
.
.
.
.
mysql也支持正则表达式！！！

7. 查询关键字----regexp

# 正则表达式

Regular Expression  规律的表达，又叫正则表达式
regexp就是两单词简写


SQL语句的模糊匹配如果用不习惯,也可以自己写正则,批量查询,精准查找！！！

select * from emp where name regexp '^j.*(n|y)$';
匹配name字段里面 包含的数据要符合  以j开头，中间可以是任意字符除换行符匹配0次或多次，
最后以n或者y结尾  老版本的sql里面.*？ 就会报错

--------------------------------------------------

select * from emp where name regexp '^j.*?(n|y)$';
新版本的这样写就不会报错！！！

--------------------------------------------------
正则相关符号含义：
上尖号^    匹配字符串的开头！！！
美元符号$  匹配字符串的结尾！！！
n|y       匹配n或者y(管道符的意思是或)

------------------------------------
元字符
.         匹配除换行符以外的任意字符

------------------------------------
量词
*         匹配零次或多次,默认是多次(无穷次)
?         匹配零次或一次  作为量词意义不大主要用于非贪婪匹配
------------------------------------

'''正则表达式默认情况下都是贪婪匹配>>>:尽可能多的匹'''
.*       匹配除换行符以外的任意字符0次或多次  贪婪匹配
.*?      匹配除换行符以外的任意字符0次或多次  非贪婪匹配
.*与.*?一般要再其两边加字母数字符号做限定


注意当*与? 组合时，？只是作为非贪婪匹配的关键使用了，
就没有对前面点号.修饰次数的作用了

非贪婪匹配是：比如a.*?a  就是在文本里面只要有符合aXXXa的都算匹配上了（aa也算匹配上）
贪婪匹配是：比如a.*?a  就是在文本里面出现有符合aXXXa，还不算完，一直往后匹配，
除非一直到最后都没有出现，才算匹配结束！！！如过后面又出现a，就会忽略掉前面的那个a！！！

--------------------------------------------------

.
.
.
.
.
.
.

多表查询的思路

表数据准备
create table dep2(
  id int primary key auto_increment,
  name varchar(20)
);

create table emp2(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);
-----------------------------------------------------

#插入数据
insert into dep2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp2(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
------------------------------------------------------

select * from emp2,dep2;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义！！！
应该将有关系的数据对应到一起才合理！！！
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来！！！
------------------------------------------------------

涉及到两张及以上的表时，字段很容易冲突！！！
我们需要在字段前面加上表名来指定！！！
select * from emp2,dep2 where emp2.dep_id=dep2.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

# 使用笛卡尔积加where条件过滤，查到想要的数据，虽然能查到数据，但是效率很低！！！
-------------------------------

.
.错误操作 select * from emp2,dep2;

.
.正确操作 select * from emp2,dep2 where emp2.dep_id=dep2.id;

.
.
.
.
.
.
.
.
.
.
.

作业

1.整理今日内容及博客
2.完成下列基础练习题SQL编写
	1. 查询岗位名以及岗位包含的所有员工名字
	select post,group_concat(name) from emp group by post;
--------------
	2. 查询岗位名以及各岗位内包含的员工个数
	select post,count(id) from emp group post;
--------------
	3. 查询公司内男员工和女员工的个数
	select gender,count(id) from emp group by gender;
--------------
	4. 查询岗位名以及各岗位的平均薪资
	select post,avg(salary) from emp group by post;
	select post as '职位',avg(salary) as '平均薪资'from emp group by post;
-------------
	5. 查询岗位名以及各岗位的最高薪资
	select post,max(salary) from emp group by post;
-------------
	6. 查询岗位名以及各岗位的最低薪资
	select post,min(salary) from emp group by post;
------------
	7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
	select gender,avg(salary) from emp group by gender;
------------
	8. 统计各部门年龄在30岁以上的员工平均工资
	select post,avg(salary) from emp where age > 30 group by post;
------------
	9.统计各部门年龄在30岁以上的员工平均工资,并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age > 30 group by post having avg(salary)>1000 order by avg(salary);
------------
3.复习本周内容预习前端内容