SQL学习——进阶篇
欲邀明月饮一壶， 抬头不知月何处。 恰有瘦竹探窗来， 先干为敬你自如。
8. 多表连接
8.1 概念
在了解交叉连接之前，先搞懂笛卡儿积：
若A = {1,2} B = {3,4,5}
则：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
其中AxB 与 BxA 就是所谓的笛卡尔乘积，很明显AxB != BxA
内连接和外连接的区别 :
- 内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
- 外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。
- 左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。
- 右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。
8.2 图示
8.3 代码
drop table zmfind;
truncate table zmfind;
create table zmfind(
id smallint primary key auto_increment,
name varchar(20) not null,
age tinyint not null ,
address varchar(50) default '福建省福州市',
deptid smallint,
constraint f_and_dept foreign key (deptid) references zmfind_dept(deptid)
)auto_increment=101;
describe zmfind;
show create table zmfind;
insert into zmfind values (null,'王一',23,default,101),
(null,'王二',22,'大连',104),
(null,'王三',28,'上海',102),
(null,'王四',23,'西安',101),
(null,'王五',25,'杭州',102),
(null,'王六',25,'深圳',101),
(null,'王七',26,'芜湖',103),
(null,'王八',28,'重庆',104);
drop table zmfind_dept;
truncate table zmfind_dept;
create table zmfind_dept(
deptid smallint primary key ,
deptname varchar(50),
deptmoney mediumint not null
);
describe zmfind_dept;
show create table zmfind_dept;
insert into zmfind_dept values (101,'web前端',100),
(102,'数据库设计','200'), # 会被强制转换成mediumint
(103,'python应用开发',250),
(104,'大数据应用开发',300),
(105,'AI应用开发',250);
# 交叉连接(了解就行),本质就是笛卡尔连接
select * from zmfind cross join zmfind_dept; # 一共有8x4=32条数据
# 内连接，可以省略inner(若内连接不加查询条件，则曾为交叉查询）
# (只保留符合条件的数据)
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
inner join zmfind_dept d on z.deptid = d.deptid;
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
join zmfind_dept d on z.deptid = d.deptid where age>=25;
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
join zmfind_dept d; # 等价于交叉查询
# 左外连接，可以省略outer(左边数据全部保留，右边保留符合连接条件的行，不符合条件的用null填充)
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
left outer join zmfind_dept d on z.deptid = d.deptid;
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
left join zmfind_dept d on z.deptid = d.deptid;
# 右外连接，可以省略outer(右边数据全部保留，左边保留符合连接条件的行，不符合条件的用null填充
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
right outer join zmfind_dept d on z.deptid = d.deptid;
select z.name,z.age,d.deptname,d.deptmoney from zmfind z
right join zmfind_dept d on z.deptid = d.deptid;
8.4 自连接
表中数据：
其中市/ 区pid = 所属省/市的id。
# 自连接(同样可以内连接和外连接！）
select * from tb_areas as city join tb_areas as province on city.pid=province.id where province.title='福建省';
select * from tb_areas as city join tb_areas on city.pid=tb_areas.id where tb_areas.title='福州市';
select city.id,city.title,province.title from tb_areas as city
join tb_areas as province on city.pid=province.id where province.title='福建省';
9. 高级查询
9.0 建表语句
drop table zmfun;
truncate table zmfun;
create table zmfun(
id smallint primary key auto_increment,
name varchar(20) not null,
age tinyint not null ,
address varchar(50) default '福建省福州市',
deptid smallint,
constraint fun_fun1 foreign key (deptid) references zmfind_dept(deptid)
)auto_increment=101;
describe zmfun;
show create table zmfun;
insert into zmfun values (null,'王一',23,default,101),
(null,'王二',22,'大连',104),
(null,'王三',28,'上海',102),
(null,'王四',23,'西安',101),
(null,'王五',25,'杭州',102),
(null,'王六',25,'深圳',101),
(null,'王七',26,'芜湖',103),
(null,'王八',28,'重庆',104);
drop table zmfun_1;
truncate table zmfun_1;
create table zmfun_1(
deptid smallint primary key ,
deptname varchar(50),
deptmoney mediumint not null
);
describe zmfun_1;
show create table zmfun_1;
insert into zmfun_1 values (101,'web前端',1000),
(102,'数据库设计','2000'), # 会被强制转换成mediumint
(103,'python应用开发',2500),
(104,'大数据应用开发',3000),
(105,'AI应用开发',4000);
9.1 简单函数
# 简单函数
# min,max,count,avg,distinct
select min(deptmoney),max(deptmoney),avg(age) Avgage,
count(distinct zmfun.deptid) Deptcount
from zmfun left join zmfun_1 on zmfun.deptid=zmfun_1.deptid;
count(*) 和 count(1)和count(列名)区别:
1. 执行效果上：
- count(*)包括了所有的列，相当于行数，在统计结果时，不会忽略列值为NULL
- count(1)包括了忽略所有列，用1代表代码行，在统计结果时，不会忽略列值为NULL
- count(列名)只包括列名那一列，在统计结果时，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。
2. 执行效率上：
列名为主键：count(列名)会比count(1)快；列名不为主键：count(1)会比count(列名)快。
- 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
- 如果有主键，则 select count（主键）的执行效率是最优的
- 如果表只有一个字段，则 select count（*）最优。
9.2 数字函数
# 计算所有人的平均工资/平均年龄 (77.5)
## round(a,b) a是数字，b吧表示保留几位小数,不写b默认为0,四舍五入
select round(avg(zz.deptmoney)/avg(zz.age))
from (select z0.age,z1.deptmoney from zmfun z0 inner join zmfun_1 z1
on z0.deptid = z1.deptid) as zz; # (78)
## truncate(a,b) a是数字，b吧表示保留几位小数,必须写b,直接舍去
select truncate(avg(zz.deptmoney)/avg(zz.age),0)
from (select z0.age,z1.deptmoney from zmfun z0 inner join zmfun_1 z1
on z0.deptid = z1.deptid) as zz; # (77)
## rand(a) 0.0~1.0之间的随机数,包左不包右;a为随机种子
select rand()*100;
# ceil(a) 返回比a大的最小整数; floor(a) 返回比a小的最大整数
select ceil(5.01); # 6
select floor(5.99); # 5
# abs(a) 返回绝对值; mod(a,b) 返回a%b; pow(a,b) 返回a^b
select abs(-5.2); # 5.2
select mod(10.6,3.4); # 0.4
select pow(2,4); # 16
# 圆周率
select pi();
9.3 字符串函数
## isnull() 非空校验3
select isnull(null); # 1
select isnull('zimo'); # 0
## concat(*str) 把所有字符串拼接起来，其中false为0，true为1
select concat('ab', 1314, false); # ab13140
## concat_ws(sep,*str) 用sep来拼接字符串
select concat_ws(':','男','520',true,'ab'); # 男:520:1:ab
# length(str) 获取str的长度
select name,length(name) from zmfun where age>25; # 一个中文3B
# left(str,int) 获取字符串前N字符
select left('zimo dashuaibi _ hhhhh ',14) truth;
# locate(strmin,str) 查找前字符串在后字符串中第一次出现的位置
select locate('zimo', '大帅逼zimo') location; # 从1开始数的.
# reverse(str)字符串反转函数：
select reverse('墨子必帅');
# substr(str,a,b) 从位子a开始，截取长度为b，b为空时默认截取到最后
select substr('dashuaibizimo', 6); # aibizimo
select substr('dashuaibizimo', 6, 5); # aibiz
select substr('dashuaibizimo', -3); # imo
# upper,ucase字符串转大写函数; lower,lcase字符串转小写函数
select upper('abc');
select ucase('abc');
select lower('ABC');
select lcase('ABC');
# replace(str,old,new)字符串替换,用new替换str中的old
select replace('帅zimo', '帅', '大帅逼');
# trim(str) 去除头尾空格
select trim(' zi mo ') name;
9.4 日期函数
create table zmfun_t(
id tinyint primary key auto_increment,
starttime date not null
);
insert into zmfun_t values(null,"2022-09-8"),
(null,"2022-09-5"),
(null,"2022-09-6"),
(null,"2022-09-3"),
(null,"2022-08-30");
# current_date() 获取当前日期
select current_date(); # 2022-09-8
# now()和current_timestamp() 获取当前时间
select now(); # 2022-09-8 16:53:27
select current_timestamp(); # 2022-09-8 16:53:27
## interval关键字
# 查询当前时间之前2个小时的日期：
select now()-interval 5 hour;
# 日期加减函数
select date_add(starttime,interval 5 day) from zmfun_t;
select adddate(starttime,interval 5 day) from zmfun_t;
select date_sub(starttime,interval 5 day) from zmfun_t;
select subdate(starttime,interval 5 day) from zmfun_t;
# year(time) 日期转年; month(time) 日期转月
select year('2022-09-8 16:53:27');
select month('2022-09-8 16:53:27');
9. 多表操作
create table manytable(
id smallint primary key auto_increment,
name varchar(20) not null,
age tinyint not null ,
address varchar(50) default '福建省福州市',
deptname varchar(50),
deptmoney int not null
)auto_increment=101;
insert into manytable values (null,'王一',23,default,'web前端',1000),
(null,'王二',22,'大连','大数据应用开发',3000),
(null,'王三',28,'上海','数据库设计',2000),
(null,'王四',23,'西安','web前端',1000),
(null,'王五',25,'杭州','数据库设计',2000),
(null,'王六',25,'深圳','web前端',1000),
(null,'王七',26,'芜湖','python应用开发',3000),
(null,'王八',28,'重庆','大数据应用开发',4000);
create table manytable_1(
id tinyint primary key auto_increment,
deptname varchar(50),
deptmoney int not null
)auto_increment=1;
# 1.把一个表的数据插入另一个表
# insert into 表 select ~ from 表；
insert into manytable_1(deptname, deptmoney)
select distinct deptname,deptmoney from manytable;
# 2.通过另一个表更新一个表
# update 表 join 表 on ~=~ set 表.~ = ~；
update manytable m join manytable_1 m1
on m.deptname = m1.deptname
set m.deptname=m1.id;
alter table manytable modify deptname tinyint;
alter table manytable drop deptmoney;
# 3.通过另一个表的数据创建新的表
# create table xxx（~~~~）select xx from
# 表 ；# 注意点：xx 与表xxx中的字段名要一致
create table manytable_2(
id smallint primary key auto_increment,
cname varchar(50),
cplace varchar(50)
) select name cname,address cplace from manytable;
10. 窗口函数
10.1 概念
**窗口函数功能： **
不减少原表的行数，所以经常用来在每组内排名，同时具有分组（partition by）和排序（order by）的功能
窗口函数使用场景：
业务需求每组内排名，如排名问题：每个部门按业绩来排名；topN问题：找出每个部门排名前N的员工进行奖励。
注意点：
窗口函数原则上只能写在select子句中，partition子句可以省略，省略就是不指定分组，但是，这就失去了窗口函数的功能，所以一般不要这么使用。
10.1 聚合函数
drop table zmwindow;
truncate table zmwindow;
create table zmwindow(
id smallint primary key auto_increment,
name varchar(20) not null,
age tinyint default 0,
sex enum ('男','女'),
money smallint not null,
deptid tinyint not null
)auto_increment=1001;
describe zmwindow;
show create table zmwindow;
insert into zmwindow values (null,'王一',23,'男',3000,01),
(null,'王二',22,'女',5000,01),
(null,'王三',28,'男',6000,02),
(null,'王四',28,'女',7000,03),
(null,'王五',25,'男',11000,05),
(null,'王六',24,'女',8000,04),
(null,'王七',26,'女',6000,02),
(null,'王八',33,'男',5000,04),
(null,'王九',19,'女',5000,04),
(null,'王十',21,'男',5000,04);
# count 窗口函数
select name,age,count(id) over()
from zmwindow where age>=25;
# sum 窗口函数
select name,sex,money,sum(money) over() allmoney
from zmwindow where sex='女';
# max、min、avg 窗口函数
select name,sex,money,
min(money) over(partition by sex) minMoney,
max(money) over(partition by sex) maxMoney,
avg(money) over(partition by sex) avgMoney
from zmwindow;
10.2 排序窗口函数
# rank() 排序 【跳跃式排名】
select name,money,rank() over (order by money desc) Mrank
from zmwindow where sex='男'; # 1 2 3 3 5
# dense_rank() 排序 【连续式排名】
select name,money,dense_rank() over (order by money desc) Mrank
from zmwindow where sex='男'; # 1 2 3 3 4
# row_number() 排序 【唯一式排名】
select name,money,row_number() over (order by money desc) Mrank
from zmwindow where sex='男'; # 1 2 3 4 5
10.3 分组排序窗口函数
# ntile(n) 切片函数(把数据切成n份)
select name,money,age,ntile(5) over (order by age) agerank from zmwindow;
# 在上面的基础上选出第5组
select * from (
select name,money,age,ntile(5)
over (order by age) agerank from zmwindow
)a
where a.agerank=5
11. Pymysql 语法
import pymysql
# 1.创建连接对象
conn=pymysql.connect(
host="127.0.0.1",
user="root",
password="root",
port=3306,
charset="utf8"
# database="zimo"
)
conn.select_db("zimo")
# 2.创建游标对象
cursor=conn.cursor()
# 3.执行sql语句
sql_str= 'select * from zmfun where name=%s'
sql_list=[]
sql_list.append(input("查询"))
cursor.execute(sql_str,sql_list)
# 4.获取执行结果
result=cursor.fetchall()
print(result)
# 5.提交数据
conn.commit()
# 6.关闭游标和连接
cursor.close()
conn.close()