MySQL表单的查询及分组聚合整理

单表查询

1.表与表关系

1.1校区表 班级表 一对多

​ 校区表 一个校区可以有多个班级 一对多
​ 班级表 一个班级可不可以对应多个校区
​ 校区表 校区id 校区名称 校区城市 校区地址
​ 班级表 班级id 班级名称 开班日期 班主任 校区id
​ 多(foreign key)关联一这张表
​ 班级表创建foreign key关联校区表的校区id字段

1.2学生表 班级表 多对多

​ 站在学生的角度上 一个学生属于多个班级 一对多
​ 站在班级的角度上 一个班级可以有多个学生么 多对一
​ 学生表 学生id 学生姓名 ...
​ 班级表 班级id 班级名称 ...
​ 产生第三张表
​ 一个字段是外键关联另一个表的主键
​ 另一个字段外键关联另一张表的主键

1.3学生表 客户表 一对一

​ 一个客户对应一个学生
​ 学生表gid foreign key 关联客户表id主键
​ 并且gid还要设置为unique

2.单表查询

2.1有关查看的语句

select user(); # 查看当前用户
select database(); # 查看当前数据库
select now(); # 查看当前时间

2.2查看的基本语法

# select *(全部) from 表名;
select * from t1;
# select 字段名 from 表名;
select name from t1:
# select 字段名,字段名,字段名 from 表名;
select name, age from t1;

2.3distinct(去重)

# select distinct 字段 from 表;
select distinct name from t1;
# 对查出来的字段进行去重

2.4查询能进行四则运算

# select emp_name,salary*12 from 表
select emp_name,salary*12 from t1
# 字段salary参与了四则运算

2.5concat

对字段进行加工

# select concat(字段,'字符串1',字段) from 表
select concat(emp_name,' : ',salary) from employee;
# 如果按照上面的写法,加工后的字段名就会变为concat(emp_name,' : ',salary),很难看

select concat(emp_name,' : ',salary) as info from employee;
select concat(emp_name,' : ',salary) info from employee;
# 上面的两种写法效果相同,将字段名变为info

# 加工后的效果为------>姓名:工资

2.6concat_ws

# select concat_ws('分隔符',字符串,字段1,字段2) info from employee;
select concat_ws('|','信息',emp_name,salary) info from employee;
# 使用分隔符将信息分隔开,可以加入字符串进行辅助拼接
# 与concat类似可以修改字段名

2.7case

select(
    case # 语句头,相当于begin
    when emp_name = 'alex' then # when 条件 then
        concat(emp_name,'BIGSB') # 输出查询的字段,可加工
    when emp_name = 'jingliyang' then
        emp_name
    else					# 最后一条写else
        concat(emp_name,'sb')
    end # 语句尾
    ) as new_name # 字段重命名
from employee;

2.8where筛选行

select * from 表 where 条件

2.8.1范围查询

①>大于

select age from t1 where age > 20;

②<小于

select age from t1 where age < 20;

③>=大于等于

select age from t1 where age >= 20;

④<=小于

select age from t1 where age <= 20;

⑤=等于

select age from t1 where age = 20;

⑥!=或<>不等于

select age from t1 where age != 20;
# 或
select age from t1 where age <> 20;

2.8.2模糊查询

①like:

Ⅰ. % 一个百分号代表任意长度的任意字符

# 'a%' 判断以a开头
select name from t1 where name like 'a%';
# '%ing' 判断以ing结尾
select name from t1 where name like '%ing';
# '%a%' 判断存在a
select name from t1 where name like '%a%';

Ⅱ. _ 一个下划线代表一个任意字符

# 'a__' 判断以a开头的三个字符
select name from t1 where name like 'a__';

②regexp

实际上就是进行正则运算

# '^a' 判断以a开头
select name from t1 where name regexp '^a';
# 'a$'判断以a结尾
select name from t1 where name regexp 'a$';

2.8.3 is与is not

# 数据库中不能使用=去判断null,因此提供了is与is not 去判断null
select name from t1 where name is null;
select name from t1 where name is not null;

2.8.4逻辑运算

# and
select name from t1 where name='alex' and age = 18;
# or
select name from t1 where name='alex' or name = 'sb';
# not
select name from t1 where not age > 30;

3.表的修改

# alter table 表名 rename 新表名;
alter table t1 rename t2;
# alter table 表名 add 新字段 类型(宽度) 约束;
alter table t2 add post char(12) not null;
# add 新字段 类型(宽度) 约束 after id
alter table t2 add post char(12) not null after id; # 插在id之后
# add 新字段 类型(宽度) 约束 first
alter table t2 add post char(12) not null first; # 排头
# alter table 表名 drop 字段名;
alter table t1 drop post;
# alter table 表名 change 旧字段 新字段 类型(宽度) 约束;
alter table t1 change name new_name char(20) not null;
alter table t1 change name name char(12) null;# 使name可以为空
# alter table 表名 modify 存在的字段 新类型(新宽度) 新约束;
alter table t1 modify name char(12) unique;
alter table t1 modify name char(12) unique after id;

4.数据操作补充

# 增加
# insert into 表名 value (1,'alex','female') 使用value一次只能增加一条记录
insert into t1 value (1, 'alex', 'female');
# insert into 表名 values (1,'alex','female'),(1,'alex','female');
insert into t1 values (1, 'alex', 'female'),(2, 'alex', 'female')
# insert into 表名(name,gender) values ('alex','female');    *****常用
insert into t1(name, gender) values ('alex', 'female'),('alexsb', 'female');
# insert into 表名(name,gender) select (username,sex) from 表2;
insert into t1(name, gender) select (username, sex) from t2;


# 修改
# update 表名 set 字段=值1 where 条件
update t1 set age=18 where age > 18;
# update 表名 set 字段1=值1,字段2=值2 where 条件
update t1 set age=18, sex='女' where age > 18 and sex='男';

数据库中的分组与聚合

1.数据库中关键字的执行顺序

①select 想要的列 from 表
②where 先从这张表中查询的行
③group by 分组
④having 对组过滤
⑤order by 排序
⑥limit 取一个区间

2.分组(group by)

使用情况:

根据某个重复率比较高的字段进行的,
这个字段有多少种可能就分成多少个组

功能:

①分组
②去重
注:一旦分组了就不能对具体某一条数据进行操作了

数据表:

# 使用group by
select * from employee group by post;

使用group by 时会出现一些问题:

这里引出group_concat()

select group_concat(emp_name) from employee where post='teacher' group by post;

3.聚合(count,max,min,sum,avg)

聚合:

99.99%的情况都是和分组一起用的
如果没有和分组一起用,默认一整张表是一组

# count(id)  / count(*) 计数 :每个组对应几条数据
select count(id) from employee;
# max 求最大值: 这个组中某字段的最大值
select max(hire_date) from employee;
# min 求最大值: 这个组中某字段的最小值
select min(hire_date) from employee;
# avg 求平均值
select avg(salary) from employee;
# sum 求和值
select sum(salary) from employee;

4.过滤(having)

# 就是一个对组进行筛选的条件
# 要部门人数大于3个人的部门 count(id)>3
select post from employee group by post having count(id)>3;

5.排序(order by)

# order by 字段(升序)
select emp_name,age from employee order by age;
# order by 字段 asc(升序)
select emp_name,age from employee order by age asc;
# order by 字段 desc(降序)
select emp_name,age from employee order by age desc;

# order by 字段1,字段2(对字段1升序,然后在字段1的基础下对字段2升序)
select emp_name,age from employee order by age,salary;
# order by 字段 asc,字段2 desc(对字段1升序,然后在字段1的基础下对字段2降序)
select emp_name,age from employee order by age asc,salary desc;
# order by 字段 desc,字段2 asc(对字段1降序,然后在字段1的基础下对字段2升序)
select emp_name,age from employee order by age desc,salary asc;
# order by 字段 desc,字段2 desc(对字段1降序,然后在字段1的基础下对字段2降序)
select emp_name,age from employee order by age desc,salary desc;

6.限制(limit)

limit的用途:

①显示分页

# limit m,n
    # 若写为limit n,默认m为0
    # 表示从m+1开始,取n条
    # limit 0,6 表示从1开始取6条
    # limit 6,6 表示从7开始取6条
    # limit 12,6 表示从13开始取6条
    # limit 18,6 表示从19开始取6条
    
# 只显示一条信息
select emp_name,age from employee limit 1;
select emp_name,age from employee limit 0,1;# (两条效果相同)

################################################################
# 有时面试中面试官会问 limit offset的用法,你会懵逼!
# limit n offset m :从m+1开始,取n条 (*******************************)
limit 1 offset 0; 与 limit 0,1; limit 1; 完全一样!!!!!

②取前n名

# 跟order by一起用
# 取最小年龄
select emp_name,age from employee order by age limit 1;

7.使用python操作数据库

注:你的python中有pymysql模块

import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",database='db1') # 相当于socket里面的conn
cur = conn.cursor() # 记住

lst = ['学python从开始到放弃|alex|人民大学出版社|50|2018-7-1',
'学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3',
'学html从开始到放弃|alex|机械工业出版社|20|2018-4-1',
'学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2',
'学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30']

sql = 'insert into book values(%s,%s,%s,%s,%s)' # 格式化
for i in lst:
    cur.execute(sql, (i.split('|'))) # 后面传参要用可迭代对象
    
conn.commit() # 将数据提交至数据库写入
cur.close() # 关闭
conn.close() # 关闭

多表查询

1.连表查询前的序曲

1.1数据准备

#建表
create table department(
id int,
name varchar(20) 
);

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

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

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

1.2笛卡尔积

先计算两张表的笛卡尔积,再根据用户给出的条件进行筛选

# 连表
select * from employee,department;

因此我们可以使用where来进行筛选:

select * from employee,department where dep_id = department.id;

但是这里也会存在问题,我们这里通过where来进行连表操作,导致where字段被占用了,无法对后续的记录进行筛选.因此这个连表操作是错误的!

2.连表操作

概念:原来是两张表,现在拼成一张表,所有的操作都像是操作一张表一样了

2.1内连接

# 内连接  inner join ... on 连接条件
# select * from 表1 inner join 表2 on 条件
select * from employee inner join department on dep_id = department.id;
       # employee --> dep_id: 200,201,202,204
       # department --> id : 200,201,202,203
# 内连接 :只显示两张表中互相匹配的项,其他不匹配的不显示

2.2外链接

2.2.1左外连接

# 左外连接 left join .. on
# select * from 表1 left join 表2 on 条件
select * from employee left join department on dep_id = department.id;
# 不管左表中是不是匹配上都会显示所有内容

select * from department left join employee  on dep_id = department.id;
# 不管左表中是不是匹配上都会显示所有内容

2.2.2右外链接

# 右外连接 right join .. on
# select * from 表1 right join 表2 on 条件
select * from employee right join department on dep_id = department.id;
# 不管右表中是不是匹配上都会显示所有内容

2.2.3全外链接

mysql不支持full join 的写法,但是可通过union来实现全外连接

select * from department left join employee  on dep_id = department.id
union
select * from department right join employee on dep_id = department.id;

3.子查询

3.1概念

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

3.2实例

in:

# 存在年龄大于25岁员工的部门
select distinct dep_id from employee where age>25;
# 查询年龄大于25的员工的部门id并去重
select * from department where id in (select distinct dep_id from employee where age>25);
# 使用in 去筛选

exists:

select * from employee
where exists
(select id from department where id=200);
# 使用EXISTS关键字时，内层查询语句不返回查询的记录,而是返回一个真假值,True或False
# 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询.

4.连表查询与子查询

问题a:

# a.查询平均年龄在25岁以上的部门名
# 第一步:求部门的平均年龄>25岁的 部门id
select dep_id from employee group by dep_id having avg(age)>25;
# 第二步:构建子查询根据部门id求部门名
select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

问题b:

# b.查询平均年龄在25岁以上的部门名,平均年龄的值
# 方法1:
# 第一步:先查部门的平均年龄>25岁的部门id,平均年龄
select dep_id,avg(age) from employee group by dep_id having avg(age) > 25;
# 第二步:查出结果在之后再连表
select name,avg_age from department as d right join (select dep_id,avg(age) as avg_age from employee group by dep_id having avg(age) > 25) as t on d.id = t.dep_id;

总结:

①如果最终需要的结果只出现在一张表中,可以用子查询解决问题
②如果最终需要的结果出现在两个表中,那么最后用的一定是连表查询

5.补充select的用法

select还能这么使用:

数据准备:

create table employee(
id int not null unique 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 employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

实例:

# 求每个部门最早入职的员工姓名
select (select t2.name from employee as t2 where t2.post=t1.post order by hire_date desc limit 1) from employee as t1 group by post;