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) 去除头尾空格和tab,换行无法去除且占2B
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 id,year(zt.starttime) year, month(zt.starttime) month
from zmfun_t zt;
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. 视图与索引
12.1 视图概念
MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。
建表：
drop table zmview;
truncate table zmview;
create table zmview(
id smallint primary key auto_increment,
name varchar(20) not null,
age tinyint not null ,
address varchar(50) default '福州',
deptid smallint
)auto_increment=101;
describe zmview;
show create table zmview;
insert into zmview values (null,'王一',23,default,101),(null,'王二',22,'大连',104),
(null,'王三',28,'上海',102),(null,'王四',23,'西安',101),
(null,'王五',25,'杭州',102),(null,'王六',19,'深圳',101),
(null,'王七',29,'芜湖',103),(null,'王八',22,default,104),
(null,'王九',24,'武汉',105),(null,'王十',25,'哈尔滨',101),
(null,'王十一',28,'泉州',102),(null,'王十二',31,'北京',106),
(null,'王十三',18,'武汉',103),(null,'王十四',21,'合肥',107),
(null,'王十五',22,'上海',104),(null,'王十六',30,'贵州',102),
(null,'王十七',28,'北京',105),(null,'王十八',27,'重庆',107),
(null,'王十九',26,'黄冈',102),(null,'王二十',24,'四川',106);
drop table zmview_f;
truncate table zmview_f;
create table zmview_f(
deptid smallint primary key ,
deptname varchar(50),
deptmoney mediumint not null
);
describe zmview_f;
show create table zmview_f;
insert into zmview_f values (101,'web前端',9000),
(102,'数据库设计','11000'),
(103,'python应用开发',10000),
(104,'大数据应用开发',14000),
(105,'AI应用开发',16000),
(106,'软件测试',7000),
(107,'嵌入式开发',12000),
(108,'物联网',13000);
12.2 视图的基本操作
# 创建单表视图
create view nameage as
select id,name,age from zmview;
describe nameage;
show create view nameage;
select * from nameage; # 查看视图
# 创建多表视图
create view namemoney as
select name,age,deptmoney from zmview inner join zmview_f zf
on zmview.deptid = zf.deptid where age>20;
describe namemoney;
show create view namemoney;
select * from namemoney;
# 修改视图
alter view namemoney as
select name,age,deptmoney from zmview inner join zmview_f zf
on zmview.deptid = zf.deptid where age>25;
select * from namemoney;
/*
！在使用update进行修改视图上的数据时，会通过视图转到基本表上进行更新，
如果对视图增加或删除记录，实际上是对基本表增加或删除记录。
*/
# 在视图上更新的数据会改变真实表中的数据！
update namemoney set age=100 where name="王三";
# 删除视图
drop view nameage;
drop view namemoney;
select * from nameage;
select * from namemoney;
12.3 索引概念
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。
索引的优点如下：
- 通过创建唯一索引可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的查询速度
- 在实现数据的参考完整性方面可以加速表与表之间的连接
- 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
索引的缺点如下：
- 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加
- 索引需要占磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度
索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。
12.4 索引的基本操作
use zimo;
show create table zmview;
# 创建索引
create index deptvalue on zmview(deptid desc); # 创建一个交deptvalue的索引
# 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值，一般为主键
create unique index dept_value on zmview(age); # 会报错，因为我的age有重复的值
# 在创表时创建索引
create table zmindex(
# 创建主键也就会创建主键的索引
stuid smallint primary key auto_increment,
cid int,
name varchar(30),
unique key (name), # index和key都表示索引可以互换
key (cid)
);
show create table zmindex;
drop table zmindex;
# 在表中添加索引
create table zmindex(
# 创建主键也就会创建主键的索引
stuid smallint,
cid int,
name varchar(30)
);
# 添加索引
alter table zmindex add primary key(stuid);
alter table zmindex add unique key (name);
alter table zmindex add index (cid desc);
show create table zmindex;
drop table zmindex;
# 查看索引
show index from zmindex;
# 删除索引
drop index cid on zmindex;
alter table zmindex drop primary key;
12. 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()
100
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
