1: Learn notes SQL
1 基础
1. 引入
SQL Structured Query language 是查询和管理关系型数据库管理系统的标准语言（RDBMS）
- DDL （Data Definition Language）：CREATE、ALTER、DROP
- DML (Data Manipulation Language) ：SELECT、INSERT、UPDATE、DELETE、MERGE
- DCL （Data Control Language）：GRANT、REVOKE
数据库的数据模型
概念数据模型 CDM : 实体 、属性、码、实体型、实体集、联系
逻辑数据模型 LDM ：数据结构、数据操作、数据约束
物理数据模型 PDM ：概念数据模型和逻辑数据模型在计算机中的表示
关系模型：
第一范式 ：数据是唯一的，属性符合原子性（atomic）
一行代表一条数据第二范式 ：满足第一范式，主键和非主键之间必须满足唯一条件
一行代表一条数据，主键和非主键之间是一对一的关系
方案：存在一对多则设计为主子表
实例：公司购物单和购物单明细，购物单为主表，明细为子表第三范式 ：满足第二范式，非主键相互独立
一行代表一条数据，主键和非主键之间是一对一的关系，非主键若存在依赖关系也必须是一对一关系
方案：存在一对多的关系设计为主子表、关系表
实例：公司购物单和购物单明细及客户，购物单为主表，明细为子表；公司和顾客建包含关系表。数据的生命周期：
联机事物处理 OLTP Online Transactional Processing : 数据输入
数据的插入、更新和删除数据仓库 DW Data Warehouse : 数据检索和生成报表环境
支持数据检索，对数据仓库中的数据模型进行设计和优化联机分析处理 OLAP Online Analytical Processing: 聚合数据动态分析
存在动态分析数据频繁请求不同级别聚合数据且对数据切片（slicing）和切块（dicing）的需求，数据处理效率可能低下，解决方案有SSAS 或者 AS数据挖掘 DM Data mining: 找出目标数据
即：数据输入、存储、分析、得出结论。
2. SQL Server 体系结构
2.1 实例
安装一个SQL Server数据库引擎/服务叫实例，一台计算机上可以安装多个实例，每个实例都是相互独立存在的；实例包含默认实例和命名实例
2.2 数据库
各种对象的容器（表，视图，存储过程）
master: 元数据信息、服务器配置、实例中所有数据库信息、初始化信息Resource: 保存所有系统对象model: 新数据库的模板，每个新创建的数据库最初都是model的一个副本tempdb: SQL Server保存临时数据的地方，每次重启SQL Server 实例时，会删除这个数据库的内容，并将其重新创建为model的一个副本msdb: SQL Server Agent 的一种服务保存其数据的地方
一个数据库至少有一个数据文件和一个日志文件，数据文件用于保存数据库对象数据，日志文件则保存SQL Server为了维护事务而需要的信息
2.3 架构和对象
一个数据库包含多个架构，每个架构又包含多个对象；可以将架构看作是各种对象的容器（表、视图、存储过程）
表属于架构，架构属于数据库
3. 创建表和定义数据完整性
创建表
USE Testdb;
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary Money NOT NULL
);
USE testdb : 切换数据库上下文
IF OBJECT_ID: 检查表， U 代表用户表，匹配成功返回内部对象ID，匹配失败返回NULL
如果不显式指定一个列是否允许NULL值，SQL Server采用默认值
数据完整性
- 声明式数据完整性（DML）
过程式数据完整性 （存储过程或者触发器）
主键约束（Primary Key Constraints）：主键唯一，伴随着唯一索引的创建
PRIMARY KEY(empid)唯一约束（Unique Constraints）：保证数据行的一个列或者一组列数据的唯一，伴随唯一索引
UNIQUE(ssn)外键约束：引用的完整性。在引用表的属性上指向被引用表的属性，外键的目的是将外键列允许的值域限制为被引用列中现有的值。引用表和被引用表可能是同一个表。
CONSTRAINT pk_Orders PRIMARY KEY(OrderId)将
Order表中的empid列支持的值域限制为现有的Employees表中的empid列取值ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid) REFERENCES dbo.Employees(empId);限制employees表中的
mgrid列支持的值域为同一个表中已经存在的empid列的值ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES Employees(empid);检查约束（Check）: 检查约束用于定义在表中输入或修改一行数据之前必须满足的谓词；谓词计算结果为FALSE时RDBMS拒接数据修改，结果为TRUE或者UNKNOWN时，RDMBMS接收数据的修改
ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salary CHECK(salary > 0);默认约束（Default）: 默认约束和特定的属性关联
COMMONT NULL; DEFAULE(CURRENT_TIMESTAMP) FOR orders;2 单表查询
USE DB20240910;
SELECT empid,YEAR(orderdate) as od, COUNT(*) as mumber
FROM Sales.Order
WHERE cusid = 100
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderdateFROM:架构 + 表
HAVING:处理分组，结果为TRUE的组，对组的进行谓词过滤或者逻辑表达式
TOP:T-SQL限制返回的行数或者百分比 limit
SELECT TOP(5) order, empid
FROM Sales.Order
GROUP BY orderdate DESC, orderid DECOVER:用于定义一个特定的窗口，以便进行特殊的运算。
--建立测试表和测试数据
CREATE TABLE Employee
(
ID INT PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO Employee
VALUES();SELECT *,
SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
SUM(Salary) OVER(ORDER BY ID) 累计工资,
SUM(Salary) OVER() 总工资
from EmployeeOVER(PARTITION BY Groupname):根据Croupname分组，分组后就Salary合计OVER(PARTITION BY groupname ORDER BY ID):根据Croupname分组，再根据ID排序，最后在组内就Salary合计OVER(ORDER BY ID): 先根据ID排序，再对排序后的数据Salary：排序SUM(Salary) OVER()：Salary 汇总
ROW_NUMBER():为查询的结果集按照递增顺序分配序号
SELECT
@row_number := @row_number + 1,
l.*
FROM
base_lang AS l,
( SELECT @row_number := 0 ) AS t
WHERE
id < 2000SELECT
ROW_NUMBER() OVER (ORDER BY l.id) AS row_number,
l.*
FROM
base_lang AS l,
( SELECT @row_number := 0 ) AS t
WHERE
id < 2000Grammar
Function
create function 函数名(参数列表) retrurns 返回类型
begin
函数体
endCREATE DEFINER=`root`@`%` FUNCTION `fun_getAllSubOrgUnits`(pid bigint) RETURNS varchar(6000) CHARSET utf8mb4
BEGIN
DECLARE strAllSubIds VARCHAR(6000);
DECLARE strTempPid VARCHAR(2000);
#先得到第一级,也可以掉用getSubNodes(pid);
SET strAllSubIds = fun_getSubOrgUnits(pid);
SET strTempPid = strAllSubIds;
#根据 strTempPid 判断是否还有子节点
WHILE strTempPid is not null DO
SELECT group_concat(id) INTO strTempPid FROM org_units WHERE FIND_IN_SET(unit_pid,strTempPid)>0 and unit_update=0 and delete_flag=0;
#需要对strTempPid判断,非空用 , 连接
IF (strTempPid is not NULL) THEN
SET strAllSubIds = concat(strAllSubIds,',',strTempPid);
END IF;
END WHILE;
RETURN strAllSubIds;
END1. @ids @l := @l + 1
SELECT
id
FROM
org_units
WHERE
id IN (
SELECT DATA
.id
FROM
(
SELECT
@ids AS _ids
(
SELECT
@ids := GROUP_CONCAT( `id` )
FROM
org_units
WHERE
FIND_IN_SET( `unit_pid`, @ids )
AND delete_flag = 0
AND update_flag = 0
) AS cids,
@l := @l + 1 AS LEVEL
FROM
org_units,
( SELECT @ids := ( '1000' ), @l := 0 ) b
WHERE
@ids IS NOT NULL
) `id`,
org_units DATA
WHERE
FIND_IN_SET( DATA.`id`, `id`._ids )
);
@rowNO := @rowNo+1
SELECT (@rowNO := @rowNo+1) AS 'file08001',t.* from (
SELECT
empbs_sapid AS 'file08002',
empbs_name AS 'file08003',
unpt_sapid AS 'file08004',
unpt_qname AS 'file08005',
unit_com1name AS 'file08008',
unit_com2name AS 'file08009',
unit_com3name AS 'file08010',
unit_com4name AS 'file08011',
unit_com5name AS 'file08012',
unit_com6name AS 'file08013',
unit_com7name AS 'file08014',
unpt_dyspe AS 'file08017',
unpt_zq AS 'file08018',
unpt_zz AS 'file08019',
unpt_zxtype AS 'file08020',
unpt_level AS 'file08021',
unpt_clevel AS 'file08022',
unpt_zx AS 'file08023',
unpt_zj AS 'file08024',
empbs_gender AS 'file08029',
empbs_birthday AS 'file08030',
empeduc_edubg AS 'file08036',
empeduc_degree AS 'file08037',
empeduc_gradusch AS 'file08038',
empeduc_sxmajor AS 'file08039',
empeduc_learnway AS 'file08040',
empeduc_gradudate AS 'file08041',
empeduc_firstedubg AS 'file08043',
empeduc_country AS 'file08044',
empeduc_yxtype AS 'file08045',
empde_fast_workdate AS 'file08046',
empbs_birthcountry AS 'file08047',
empbs_birthprovince AS 'file08048',
empbs_country AS 'file08050',
empgr_scope AS 'file08062',
empgr_scope AS 'file08063',
empgr_child_scope AS 'file08064',
empgr_child_scope AS 'file08065',
empgr_payscope AS 'file08066',
empgr_group AS 'file08067',
empgr_child_group AS 'file08068',
sign_name AS 'file08069',
empcon_termtype AS 'file08070',
empcon_type AS 'file08071',
empcon_contstartdate AS 'file08073',
empcon_effectivedate AS 'file08074',
empcon_regular_date AS 'file08075',
empcon_regular_date AS 'file08076',
empcon_signumber AS 'file08077',
empcon_issig_comperti AS 'file08082',
empcert_name AS 'file08090',
empcert_level AS 'file08091',
unpt_isimport AS 'file08103',
empat_status AS 'file08107',
empgr_jobdept AS 'empgrJobdept',
empId AS 'empId',
unit_com1sapid AS 'unitCom1sapid',
unit_com2sapid AS 'unitCom2sapid',
unit_com3sapid AS 'unitCom3sapid',
unit_com4sapid AS 'unitCom4sapid',
unit_com5sapid AS 'unitCom5sapid',
unit_com6sapid AS 'unitCom6sapid',
unit_com7sapid AS 'unitCom7sapid',
zzksrq,
zzjsrq
FROM (
SELECT
t.*,
sign.sign_name
FROM
(
SELECT
t.*,
ou.unit_com1name,
ou.unit_com2name,
ou.unit_com3name,
ou.unit_com4name,
ou.unit_com5name,
ou.unit_com6name,
ou.unit_com7name,
ou.unit_com1sapid,
ou.unit_com2sapid,
ou.unit_com3sapid,
ou.unit_com4sapid,
ou.unit_com5sapid,
ou.unit_com6sapid,
ou.unit_com7sapid,
ou.unit_sname
FROM
(
SELECT
t.*,
oup.unpt_sapid,
oup.unpt_qname,
oup.unpt_zx,
oup.unpt_zj,
oup.unpt_zxtype,
oup.unpt_level,
oup.unpt_clevel,
oup.unpt_dyspe,
oup.unpt_zq,
oup.unpt_zz,
oup.unpt_isimport
FROM
(
SELECT
eb.id AS 'empId',
eb.empbs_sapid,
eb.empbs_name,
eb.empbs_gender,
eb.empbs_birthday,
eb.empbs_birthcountry,
eb.empbs_birthprovince,
eb.empbs_country,
eb.empcert_name,
eb.empcert_level,
eb.empeduc_edubg,
eb.empeduc_degree,
eb.empeduc_country,
eb.empeduc_gradusch,
eb.empeduc_yxtype,
eb.empeduc_learnway,
eb.empeduc_sxmajor,
eb.empeduc_gradudate,
eb.empeduc_firstedubg,
eb.empde_fast_workdate,
eb.empcon_termtype,
eb.empcon_type,
eb.empcon_subject,
eb.empcon_contstartdate,
eb.empcon_effectivedate,
eb.empcon_regular_date,
eb.empcon_signumber,
eb.empcon_issig_comperti,
zzksrq,
zzjsrq,
t.empgr_scope,
t.empgr_child_scope,
t.empgr_group,
t.empgr_child_group,
t.empgr_payscope,
t.empat_status,
t.empgr_jobdept,
t.empgr_post
FROM
(
SELECT
c.emp_id,
c.empgr_jobdept,
c.empgr_post,
c.start_date AS 'zzksrq',
c.end_date AS 'zzjsrq',
c.empgr_scope,
c.empgr_child_scope,
c.empgr_group,
c.empgr_child_group,
c.empgr_payscope,
ac.empat_status
FROM
( SELECT
emp_id,
start_date,
end_date,
empat_status
FROM
emp_action ac
WHERE emp_id IS NOT NULL AND empat_status = #{empRptRosterHis.baobiao08Status} AND #{empRptRosterHis.baobiao08Date} BETWEEN start_date AND end_date
GROUP BY emp_id,start_date
) ac
LEFT JOIN emp_comgroup c ON ac.emp_id = c.emp_id
WHERE c.empgr_post IS NOT NULL AND c.empgr_jobdept IS NOT NULL
<if test="empRptRosterHis.baobiao08Status != null and empRptRosterHis.baobiao08Status != '' and empRptRosterHis.baobiao08Status == 3 ">
<!--8246的bug，已问种总，人事事件的开始日期，卡组织的开始结束日期-->
AND c.empgr_post != '0' AND ac.end_date BETWEEN c.start_date AND c.end_date
</if>
<if test="empRptRosterHis.baobiao08Status != null and empRptRosterHis.baobiao08Status != '' and empRptRosterHis.baobiao08Status == 0 ">
AND (date_sub(ac.start_date, interval 1 DAY) BETWEEN c.start_date AND c.end_date)
</if>
<if test="empRptRosterHis.baobiao08Registered !=null and empRptRosterHis.baobiao08Registered != '' and empRptRosterHis.baobiao08Registered == 'no' " >
AND c.empgr_group != '5'
</if>
<if test="empRptRosterHis.baobiao08Registered !=null and empRptRosterHis.baobiao08Registered != '' and empRptRosterHis.baobiao08Registered == 'only' " >
AND c.empgr_group = '5'
</if>
AND c.emp_id IS NOT NULL
)t
LEFT JOIN emp_info eb ON t.emp_id = eb.id
WHERE
1=1
<if test="empRptRosterHis.baobiao08Dept != null and empRptRosterHis.baobiao08Dept != ''">
and t.empgr_jobdept in
<foreach collection="empRptRosterHis.baobiao08Dept.split(',')" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="empRptRosterHis.baobiao08Empsapid != null and empRptRosterHis.baobiao08Empsapid != ''">
and eb.empbs_sapid like concat('%',#{empRptRosterHis.baobiao08Empsapid},'%')
</if>
<if test="empRptRosterHis.baobiao08EmpName != null and empRptRosterHis.baobiao08EmpName != ''">
and eb.empbs_name like concat('%',#{empRptRosterHis.baobiao08EmpName},'%')
</if>
<if test="empRptRosterHis.baobiao08Empsapids != null and empRptRosterHis.baobiao08Empsapids != ''">
and eb.empbs_sapid in
<foreach collection="empRptRosterHis.baobiao08Empsapids.split(',')" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="empRptRosterHis.baobiao08EmpNames != null and empRptRosterHis.baobiao08EmpNames != ''">
and eb.empbs_name in
<foreach collection="empRptRosterHis.baobiao08EmpNames.split(',')" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="empRptRosterHis.baobiao08Group != null and empRptRosterHis.baobiao08Group != '' and empRptRosterHis.baobiao08Registered != 'only'">
and t.empgr_group = #{empRptRosterHis.baobiao08Group}
</if>
<if test="empRptRosterHis.baobiao08Childgroup != null and empRptRosterHis.baobiao08Childgroup != ''">
and t.empgr_child_group = #{empRptRosterHis.baobiao08Childgroup}
</if>
<if test="empRptRosterHis.baobiao08Scope != null and empRptRosterHis.baobiao08Scope != ''">
and t.empgr_scope = #{empRptRosterHis.baobiao08Scope}
</if>
GROUP BY eb.id
) t
LEFT JOIN org_unitpost oup ON t.empgr_post = oup.unpt_postid
AND zzjsrq BETWEEN oup.unpt_sdate AND oup.unpt_edate
) t
LEFT JOIN org_units ou ON t.empgr_jobdept = ou.unit_unitid
AND zzjsrq BETWEEN ou.unit_sdate AND ou.unit_edate
)t
LEFT JOIN org_signcom sign ON t.empcon_subject = sign.sign_code GROUP BY t.empbs_sapid
)t
)t,(select @rowNO :=0) b2. TIMESTAMPDIFF
SELECT TIMESTAMPDIFF( YEAR, '2023-01-05', DATE_FORMAT('2023-01-05', '%Y-%m-%d' ) )3.FIND_IN_SET()
MySQL提供了一个名为FIND_IN_SET()的内置字符串函数，允许您在逗号分隔的字符串列表中查找指定字符串的位置
`FIND_IN_SET(id, 1,2,3`) id为1||2||3Index
索引类型
- NORMAL
UNIQUE
- 全表唯一
- Primary key (unique + not null)，primary key 唯一，unique 不唯一
FULLTEXT
- 全文搜索，检索长文本
SPATIAL
- 对空间类型的数据建立索引
- mysql 空间数据类型 ：GEOMETRY、POINT、LINESTRING、POLYGON
- 建立空间索引必须申明为not null
- 空间索引只在引擎为MYISAM的表中创建
索引方法
BTREE：通过节点来做的索引
- B树
HASH：通过hash值来做的索引
- Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
- Hash 索引无法被用来避免数据的排序操作。
- Hash 索引不能利用部分索引键查询。
- Hash 索引在任何时候都不能避免表扫描。
- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
- Learn notes ↩
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。