2.SQL

一.区分SQL

	mysql：专门管理并发操作服务器上的系统的软件或者系统
	navicat：便于在mysql中编写查询指令的的工具
	sql：类似python，为人机之间的交互语言

二.基础运行逻辑

1.建立客户端与服务端的链接

--注释的表示：单行：-- 多行：/*  */
--建立链接
mysql -u 用户名 -p 密码 [-P端口号]
--展示数据库
show databases;
--使用数据
use 库名;
/*查询sid字段等于1的所有数据
一般不建议使用*，大量数据的查询对服务器端有一定压力*/
select * from 表名 where sid =1;

2.查询过程

	分析器进行词法,语法的分析；
	优化器生成一个高效的查询执行计划（高效一般指对索引结果的安排）；
	执行器操作引擎,返回结果（引擎进行B+树查询）

三.库和表的增删改操作

1.对于库的代码操作

--显示库
show databases;
--新建库（charset指定库的操作引擎）
create database 库名 charset utf8;
--修改库的操作引擎
alter database 库名 charset gbk;
--详细查看库的属性
show create database 库名;
--删除库
drop database 库名;
--使用该库，需要进入才能使用库中的众多表！！！
use 库名;

2.对于表的代码操作

	首先需要了解表中字段的几种字段属性
		几个常见的属性：int,
		有关时间的字段属性：date,time,datetime,timestamp
		有关字符的字段属性：定长char,自动化长度varchar
		几个选择类字段属性：单选enum,多选set
	还有几个对字段属性的约束条件
		主键：parmary key【影响查询速度】
		自增：auto_increment【常见于id类】
		外键：foreign key【链接外表】
		非空：not null【必填字段】
		唯一：unique【不可重复】

--创建表（记得需要使用库，use 库名）
drop table stu if EXISTS stu；
create table stu(
	id int not null PRIMARY KEY, comment "字段注释",
	name varchar(50) not null,
	age varchar(50) not null default "默认值" comment "字段注释"
）engine=innodb default chartset=utf8;
--修改字段属性类别
alter table stu change age age char(6);
--增加字段
alter table stu add sex enum("男","女");
--删除字段
alter table stu drop sex,drop age；
--查看表详细信息
desc stu;
------------------------------------------------------
--以上完成对于表的搭建，但是表格中缺少数据，需要添加
--增加数据
insert into stu(属性1,属性2)  values("值1","值2");
--修改数据
update stu set 属性=新值 where 条件;
--删除数据
delete from stu where 条件;

四.表的查询

查询很重要，单独列一个大段

1.普通查询操作

--普通查询
select 字段1,字段2 from 表名;
--限制查询--limit
select 字段 from 表名 limit 2;/*查询第1，2行数据*/
select 字段 from 表名 limit 3,2;/*查询第4，5行数据*/
--去重--distinct
select distinct 字段 from 表名;
--条件筛选 <>=! ；is null；is not null；like 【 _匹配一个；%匹配多个】
select 字段 from 表名 where 条件;
--排序--order by
select 字段 from 表名 where 条件 order by 字段 【默认升序asc，desc为降序】;
--分组--group by【将数据按照某字段分开，计算每组的结果】 用having筛选分组后的结果
select avg(sal) 'avg',country from 表名 group by country having avg >5000;(报错)

	为什么？
	上述最后一个代码的正确执行顺序为from-where(省略)-group by-having-select
当执行到having时候，sql并不能理解你所说的avg是什么东西，修改的代码为：

select avg(sal) 'avg',country from 表名 group by country having avg(sal) >5000;

将结果重新展示就行，表示的含义是：挑选出各个国家中薪资高于5000的国家。

2.子查询和链接查询

子查询理解为套娃，将返回结果当作一个表进行查询
链接主要是将多个表按照某种方式链接为一个表,主要分为内连接(join),左/右外连接(left/right join),还有一种为全连接(UNION:执行去重操作,UNION ALL:不会执行去重操作)，连接条件用on。

	直接进行实际操作吧，这样理解比较快
	现在有stu，sc两个表，其参数字段分别为：
		stu:  sid, s_name, s_age, s_sex
		sc:    sid, cid , t_name, score
	要求是：展示出所有科目平均成绩大于60的学生

select t1.sid,t2.s_name,t1.avg_score
from
(select sid
,avg(score) as "avg_score"
from sc
group by sid
) t1
left join
(select sid,s_name
from stu
) t2
on t1.sid=t2.sid
where t1.avg_score>60;

	首先从第一个from开始找到第一个表根据课程id进行分组，计算出均值并别名该表为t1；
接着左(外)连接学生部分信息表，链接条件为 t1.sid=t2.sid ，然后筛选出均值大于60的学生
最后select展示结果。
	有意思的地方是avg的依据为组内数据的sum()/count(),当需要的”count()“并不是被需要的值的时候
可以自拟出sum()/count(distinct 字段)。

3.窗口函数【sum() over(partition by 字段 order by 字段 desc)】

【小小说一嘴里面的判断函数】

select *
,case
	when age<20 then "20以下"
	when age<25 then "20-25"
	when age>=25 then "25以上"
	else "其他"
end 'age_cut'
from 表名;

	窗口用到的排序函数有
		row_number() 顺排1，2，3，4
		rank() 占位排 1，1，3，3，5，5
		dense_rank() 不占位排 1，1，2，2，2，3
	计算同比，环比用到的窗口函数
		lag(字段，步长n，默认值填充) 往前移动n
		lead(同上) 往后移动n

--收益表：日期，门店，利润
select 
,当日利润-前一周利润 '绝对周利润增长'
,concat(round(((当日利润-前一周利润)/前一周利润)*100,0),'%') '周同比增长率'
from
(
	select
	日期,门店,利润 '当日利润'
	,lag('利润',7,0) over (partition by 门店 order by 日期) '前一周利润'
	from 收益表
	where substring(日期,1,4)='2024' and 门店 like '%鸡你%'
) a