MySQL --- SQL基础知识
内连接与左（右）外连接的区别内连接关键字：inner join on
语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;
# 组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
左（外）连接关键字：left join on / left outer join on
语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;
# left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右（外）连接关键字：right join on / right outer join on
语句：SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;
#right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
# 注意：书写sql语句时连接join关键字的左右两边是将要连接的表，on后边跟着关联条件。总结：内连接：查询左右表都有的数据，不要左/右中NULL的那一部分左连接：即以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。左右连接可以相互转换：A left join B ---> B right join A 是同样的。 #A 站在 B的左边 ---> B 站在 A的右边具体见：https://blog.csdn.net/zjt980452483/article/details/82945663where、having、group by、order by、limit的区别和使用顺序where：通过在SELECT语句的WHERE子句中指定条件进行查询，WHERE子句必须紧跟在FROM子句之后。如：从员工表里查询员工id为h0001的员工的工资select 工资 from 工资表 where id='h0001';having：一般与group by组合来使用，表示在得到分类汇总记录的基础之上，进一步筛选记录。如：从部门表里查部门内员工薪水总和大于100000的部门的编号。select 部门编号,sum(薪水) from 部门表 group by 部门编号 having sum(薪水)>100000;ps：相同点：where和having都可以加条件区别：1.where在分组之前加条件，having在分组之后加条件. 2.where的效率要远远高于having. 分组本身消耗资源非常大.GROUP BY：当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。select 部门编号,sum(薪水) from 部门表 group by 部门编号;ORDER BY：order by 用来指定数据的排序方式。有升序和降序两种。desc表示降序，asc为升序，默认为升序，asc可省略。ps：order by 要写在where之后，limit之前。select * from stu_info order by id asc;// 按照id升序排序，其中asc可省略。
select * from stu_info order by id desc; //按照id降序LIMITE：LIMIT用来限定查询结果的起始行，以及总行数。如：查询10行记录，起始行从3开始select * from emp limit 3,10;综合运用时的使用顺序：从employee表中查询salary列的值>0且prize字段值>0的记录，结果以id字段分组且降序排列,起始行从0开始，显示5行：select * from emploee where salary>0 group by id having prize>0 order by id desc limit 0, 5;结果：查询到的记录条数小于5行，所以只能显示查询到的总行数。总结顺序： where(条件) - group by(分组) - having(条件) - order by(排序) - limit(限定查询结果)增删改查命令#练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
create database if not exists db4 character set gbk
#查询所有数据库的名称:
show databases;
#修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
#判断数据库存在，存在再删除
drop database if exists 数据库名称;
#查询当前正在使用的数据库名称
select database();
#使用数据库
use 数据库名称;
#复制表，注意位置
create table 表名 like 被复制的表名;
#查询表结构
desc 表名;
#删除表中指定列
alter table 表名 drop 列名;
#操作表，列名 + 数据类型，最后一行没有逗号
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);mysql常见函数与数据类型常用函数：字符函数 （自动进行数字和字符串的转化）数值计算函数比较运算符函数日期时间函数具体见：https://www.cnblogs.com/duhuo/p/5650876.html数据类型：MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。count(1),count(*),count(列)区别是啥？主要是当数据量达到一定级别，考察基本调优思路。在Mysql中的不同的存储引擎对count函数有不同的实现方式。MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高（没有where查询条件，如果有 where 条件，那么即使是 MyISAM 也必须累积计数的。InnoDB引擎并没有直接将总数存在磁盘上，在执行count()函数的时候需要一行一行的将数据读出，然来后累计总数。注：下面的讨论和结论是基于 InnoDB 引擎的。count 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。所以，count( * )、count(1)和count(主键 id) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。count(可空字段)：扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加（ps：字段非主键的情况最好不要出现，因为不走索引）count(非空字段)与count(主键 id)：扫描全表，读到server层，判断字段不可空，按行累加。count(1)：扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加。注意：count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作，ps：两者都只扫描主键。count( * )：MySQL 执行count( * )在优化器做了专门优化。因为count( * )返回的行一定不是空。扫描全表，但是不取值，按行累加。ps：InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。因此，普通索引树比主键索引树小很多。对于 count ( * ) 来说，遍历哪个索引树得到的结果逻辑上都是一样的。MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。性能对比：count(可空字段) < count(非空字段) = count(主键 id) < （count(1) ≈ count(*)，都扫描全表，不取值，按行累加）至于分析性能差别的时候，记住这么几个原则：server 层要什么就给什么；InnoDB 只给必要的值；现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。ps：Redis 存储计数会出现的问题，把计数值也放在 MySQL 中，利用事务的原子性和隔离性，就可以解决一致性的问题。数据量不大，我们尽量用 count ( * ) 实现计数；数据量很大的情况考虑新建 MySQL 表存储计数，用事务的原子性和隔离性解决。mysql常用命令Structured Query Language：结构化查询语言。其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。通用写法：SQL 语句可以单行或多行书写，以分号结尾。可使用空格和缩进来增强语句的可读性。MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。111什么是sql注入，如何防止sql注入?所谓SQL注入，黑客对数据库进行攻击的常用手段之一。一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，注入者可以在表单中输入一段数据库查询代码并提交，程序将提交的信息拼凑生成一个完整sql语句，服务器被欺骗而执行该条恶意的SQL命令。注入者根据程序返回的结果，成功获取一些敏感数据，甚至控制整个服务器，这就是SQL注入。SQL注入攻击的总体思路:（1）寻找到SQL注入的位置（2）判断服务器类型和后台数据库类型（3）针对不同的服务器和数据库特点进行SQL注入攻击如何防御SQL注入？加强用户输入内容的验证严格区分权限（普通用户和管理用户）使用参数化语句（？代表此处为一个参数，需要后期通过set设置）使用专业软件对SQL漏洞进行扫描数据库设计的三个范式？第一范式（1NF）：强调的是列的原子性（属性不可分割，即每个字段都不可能拆分），即列不能够再分成其他几列，比如字段name，中国名符合，外国名不符合。第二范式（2NF）：一是表必须有一个主键；二是其他字段必须完全依赖于主键，理解为主键约束就好了，而不能只依赖于主键的一部分，比如学生表中的学号，其他字段都可以通过学号获取信息。第三范式（3NF）：每一列数据都和主键直接相关，而不能间接相关。表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
MySQL面试题
46、试述视图的优点？ (1) 视图能够简化用户的操作(2) 视图使用户能以多种角度看待同一数据； (3) 视图为数据库提供了一定程度的逻辑独立性； (4) 视图能够对机密数据提供 安全保护。 47、 NULL 是什么意思？ 答：NULL 这个值表示 UNKNOWN(未知):它不表示“”(空字符串)。对 NULL 这 个值的任何比较都会生产一个 NULL 值。您不能把任何值与一个 NULL 值进行比 较，并在逻辑上希望获得一个答案。 使用 IS NULL 来进行 NULL 判断 48、主键、外键和索引的区别？ 主键、外键和索引的区别 定义：主键–唯一标识一条记录，不能有重复的，不允许为空 外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值 索引–该字段没有重复值，但可以有一个空值 作用： 主键–用来保证数据完整性 外键–用来和其他表建立联系用的 索引–是提高查询排序的速度个数： 主键–主键只能有一个 外键–一个表可以有多个外键 索引–一个表可以有多个唯一索引 49、你可以用什么来确保表格里的字段只接受特定范围里的值? 答：Check 限制，它在数据库表格里被定义，用来限制输入该列的值。 触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求 触发器在表格里被定义，这可能会在某些情况下影响到性能。50、说说对 SQL 语句优化有哪些方法？（选择几条） 1、Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可 以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。 2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。 3、 避免在索引列上使用计算 4、避免在索引列上使用 IS NULL 和 IS NOT NULL 5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉 及的列上建立索引。 6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃 使用索引而进行全表扫描 7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用 索引而进行全表扫描
MySQL面试题
41、什么是锁？ 答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数 据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可 能会读取和存储不正确的数据，破坏数据库的一致性。 加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进 行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定 的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。 基本锁类型：锁包括行级锁和表级锁42、什么叫视图？游标是什么？ 答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改， 查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影 响基本表。它使得我们获取数据更容易，相比多表查询。 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元 中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。 一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。43、什么是存储过程？用什么来调用？ 答：存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需 创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL， 使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。 44、如何通俗地理解三个范式？答：第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解； 第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性； 第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来， 它要求字段没有冗余。。 范式化设计优缺点: 优点: 可以尽量得减少数据冗余，使得更新快，体积小 缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引 优化 反范式化: 优点:可以减少表得关联，可以更好得进行索引优化缺点:数据冗余以及数据异常，数据得修改需要更多的成本 45、什么是基本表？什么是视图？ 答：基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。 视图是从 一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表
MySQL面试题
36、对于关系型数据库而言，索引是相当重要的概念，请回答 有关索引的几个问题：1、索引的目的是什么？ 快速访问数据表中的特定信息，提高检索速度 创建唯一性索引，保证数据库表中每一行数据的唯一性。 加速表和表之间的连接 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间 2、索引对数据库系统的负面影响是什么？ 负面影响： 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需 要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间； 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速 度。 3、为数据表建立索引的原则有哪些？ 在最频繁使用的、用以缩小查询范围的字段上建立索引。 在频繁使用的、需要排序的字段上建立索引 4、什么情况下不宜建立索引？对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等 37、解释 MySQL 外连接、内连接与自连接的区别 ？先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一 个表的所有记录和另一个表中的所有记录一一匹配。 内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合 条件的记录不会出现在结果集中，即内连接只连接匹配的行。 外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个 表中 的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。 左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中， 对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以 NULL 来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现 在结果集中。左连接和右连接可以互换，MySQL 目前还不支持全外连接。38、Myql 中的事务回滚机制概述 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个 不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤 销。 要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修 改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依 旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和 第二个表都要回到未修改的状态，这就是所谓的事务回滚 39、SQL 语言包括哪几部分？每部分都有哪些操作关键字？ SQL 语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL） 四个部分。 数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index 等 数据操纵：Select ,insert,update,delete, 数据控制：grant,revoke 数据查询：select 40、完整性约束包括哪些？ 数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。 分为以下四类： 1、实体完整性：规定表的每一行在表中是惟一的实体。 2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括 取值范围、精度等规定。 3、参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间 的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需 要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束 条件，它反映某一具体应用必须满足的语义要求。 与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、 foreign key、check、UNIQUE) 。
MySQL面试题
31、简单描述 MySQL 中，索引，主键，唯一索引，联合索引 的区别，对数据库的性能有什么影响（从读写两方面） 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们 包含着对数据表里所有记录的引用指针。 普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速 度。 普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼 此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它 定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯 一标识一条记录，使用关键字 PRIMARY KEY 来创建。 索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索 引。 索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度， 因为在执行这些写操作时，还要操作索引文件。 32、数据库中的事务是什么? 事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有 操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作 失败，则事务将回滚，该事务所有操作的影响都将取消。 事务特性： 1、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。 2、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状 态 3、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何 其他事务， 4、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后 有了其他故障，事务的处理结果也会得到保存。 或者这样理解： 事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语 句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上 有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作 为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。33、SQL 注入漏洞产生的原因？如何防止？ SQL 注入产生的原因：程序开发过程中不注意规范书写 sql 语句和对特殊字符进 行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行。 防止 SQL 注入的方式： 开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置执行 sql 语句时使用 addslashes 进行 sql 语句转换 Sql 语句书写尽量不要省略双引号和单引号。 过滤掉 sql 语句中的一些关键词：update、insert、delete、select、 * 。 提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不 易被猜到的。34、为表中得字段选择合适得数据类型 ？字段类型优先级: 整形>date,time>enum,char>varchar>blob,text 优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得 数据类型，应该优先选择占用空间小的数据类型 35、存储时期 Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒， 占用 8 个字节得存储空间，datatime 类型与时区无关 Timestamp:以时间戳格式存储，占用 4 个字节，范围小 1970-1-1 到 2038-1-19， 显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改 timestamp 列得值 Date:（生日）占用得字节数比使用字符串.datatime.int 储存要少，使用 date 只 需要 3 个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算 Time:存储时间部分得数据 注意:不要使用字符串类型来存储日期时间数据（通常比字符串占用得储存空间小， 在进行查找过滤可以利用日期得函数） 使用 int 存储日期时间不如使用 timestamp 类型
postgresql SQL
创建数据库可以用以下三种方式：1、使用 CREATE DATABASE SQL 语句来创建。2、使用 createdb 命令来创建。 createdb 是一个 SQL 命令 CREATE DATABASE 的封装。createdb [option...] [dbname [description]]3、使用 pgAdmin 工具。使用 \l 用于查看已经存在的数据库使用 \c + 数据库名 来进入数据库使用 \d 命令来查看表格是否创建成功\d tablename 查看表格信息
Laravel 7发行说明（ 二）
路由模型绑定优化路由模型绑定优化由 Taylor Otwell 开发贡献 。自定义键名有时你可能希望使用 id 以外的字段来解析 Eloquent 模型。 为此， Laravel 7 允许你在路由参数中指定某个字段：Route::get('api/posts/{post:slug}', function (App\Post $post) {
return $post;
});隐式绑定约束有时，当在路由中隐式绑定多个 Eloquent 模型时，可能希望对第二个 Eloquent 模型进行约束，使其必须是第一个 Eloquent 模型的子类。例如，考虑这种情况，该情况是通过 Slug 为特定用户查找博客文章的：use App\Post;
use App\User;
Route::get('api/users/{user}/posts/{post:slug}', function (User $user, Post $post) {
return $post;
});当使用自定义键隐式绑定作为嵌套的路由参数时，Laravel 7 将自动确定查询范围，以使用约定猜测其父级上的关系名称，以其父级检索嵌套模型。在这种情况下，将假定 User 模型关联了名为 posts(路由参数名称的复数) 的关系，该关系可用于检索 Post 模型。有关路由模型绑定的更多信息，请查阅路由文档。多邮件驱动程序多邮件驱动程序支持由 Taylor Otwell 贡献。Laravel 7 允许为单个应用配置多个邮件驱动。在 mail 配置文件中的每个邮件驱动都拥有它们自己的配置以及自己独特的 「transport」，这允许你的应用使用不同的邮件服务来发送某些邮件。例如，你的应用可以使用 Postmark 发送批量邮件，使用 Amazon SES 发送公务邮件。默认情况下，Laravel 将使用 mail 配置文件中的 default 选项指定的邮件驱动作为邮件驱动。然而，你可以通过 mailer 方法来使用特定的邮件驱动来发送邮件。Mail::mailer('postmark')
->to($request->user())
->send(new OrderShipped($order));路由缓存速度改进路由缓存速度改进由上游的 Symfony 的贡献者和 Dries Vints 贡献Laravel 7 提供了一种新的方法，用于匹配使用 Artisan 命令 route：cache 缓存的已编译缓存路由。在大型应用程序（例如，具有800条或更多路由的应用程序）上，这些改进可以使简单的「Hello World」基准测试每秒的请求速度 提高2倍 ，而无需更改应用程序。CORS 支持CORS 支持由 Barry vd. Heuvel 贡献Laravel 7 通过集成由 Barry vd. Heuvel 编写的受欢迎的 Laravel CORS 软件包，为配置跨域资源共享（CORS） OPTIONS 请求响应提供了官方支持， 默认的 Laravel 应用程序框架 中包含一个新的 cors 配置。有关 Laravel 7.x 中的 CORS 支持的更多信息，请查阅CORS文档。查询时类型转换查询时类型转换由 Matt Barlow 开发贡献.有时候需要在查询执行过程中对特定属性进行类型转换，例如需要从数据库表中获取数据的时候。举个例子，请参考以下查询：use App\Post;
use App\User;
$users = User::select([
'users.*',
'last_posted_at' => Post::selectRaw('MAX(created_at)')
->whereColumn('user_id', 'users.id')
])->get();在该查询获取到的结果集中，last_posted_at 属性将会是一个字符串。假如我们在执行查询时进行 date 类型转换将更方便。你可以通过使用 withCasts 方法来完成上述操作:$users = User::select([
'users.*',
'last_posted_at' => Post::selectRaw('MAX(created_at)')
->whereColumn('user_id', 'users.id')
])->withCasts([
'last_posted_at' => 'date'
])->get();MySQL 8+ 数据库队列改进MySQL 数据库队列改进由 Mohamed Said 开发贡献.在先前版本的 Laravel 中， database 队列的健壮性被认为无法满足生产环境的需求。但是，Laravel 7 针对使用基于 MySQL 8+ 数据库队列的应用进行了改进。通过使用 FOR UPDATE SKIP LOCKED 语句进行 SQL 的优化，database 队列驱动可以安全地用于生产环境。Artisan test 命令test 命令由 Nuno Maduro 贡献除了 phpunit命令之外，现在可以使用 test Artisan 命令来运行测试。 Artisan 测试运行器提供了漂亮的控制台，以及有关当前正在运行的测试的更多信息。 此外，运行器将在第一次测试失败时自动停止：php artisan test可以传递给 phpunit 命令的任何参数也可以传递给 Artisan test 命令：php artisan test --group=featureMarkdown 邮件模板改进Markdown 邮件模板改进由 Taylor Otwell 贡献默认的Markdown邮件模板已基于Tailwind CSS调色板做出全新、更现代的设计。 当然，可以根据您的应用程序的需求来发布和定制此模板：有关 Markdown 邮件的更多信息，请查看邮件发送.自定义桩代码自定义桩代码由 Taylor Otwell贡献Artisan 控制台的 make 命令用于创建各种类，例如控制器，任务，迁移和测试。 这些类是根据输入填充值使用「桩代码」生成文件的。 但是，有时可能希望对 Artisan 生成的文件进行小的更改。 为此，Laravel 7提供了 stub：publish 命令来发布最常见的自定义桩代码：php artisan stub:publish发布的桩代码将位于应用程序根目录中的 stubs 目录中。 当使用 Artisan 的 make 命令生成它们的相应类时，对这些桩代码所做的任何更改都会反映出来。队列maxExceptions配置maxExceptions属性由Mohamed Said提交贡献.有时可能希望指定可以尝试多次的任务，但是如果重试是由给定数量的异常触发的，则该任务将失败。在Laravel7中，可以在任务类上定义 maxExceptions 属性：<?php
namespace App\Jobs;
class ProcessPodcast implements ShouldQueue
{
/**
* 任务可以被重试的次数。
*
* @var int
*/
public $tries = 25;
/**
* 失败之前允许抛出异常的最大次数。
*
* @var int
*/
public $maxExceptions = 3;
/**
* 执行任务。
*
* @return void
*/
public function handle()
{
Redis::throttle('key')->allow(10)->every(60)->then(function () {
// 获取锁，处理博客进程...
}, function () {
// 无法获取锁...
return $this->release(10);
});
}
}在此示例中，如果应用程序无法获得 Redis 锁，则该任务将释放十秒钟，并将继续重试 25 次。但是，如果任务抛出三个未处理的异常，则该任务将失败。
站在Java的视角，深度分析防不胜防的小偷——“XSS”
1你的网站存在XSS漏洞！yrzx404这两天比较闲，说我们给小伙伴们来写一个用来提建议的网站吧，能让小伙伴第一时间将想要说的话反馈给我们，并且所有小伙伴都可以看到其他小伙伴提出的建议。这还不简单，就一个单页网站，再结合CRUD就可以搞定，那这么简单的任务就分配给znlover那家伙去搞吧。那znlover是如何实现这个小网站的？说来有一套，znlover采用了spring-boot+jpa+thymeleaf技术栈不到半个小时就搞定了，虽然界面简陋，但功能基本完成了，界面如下：东哥看到这个网站后，测试了5分钟，说“你这个网站不安全，有XSS漏洞!”。XSS漏洞？一个留言板，怎么会存在漏洞，znlover十分不解。通过查看东哥的留言，发现确实网站的运行状态不正常了，现在一打开留言网站，就会弹出一个提醒框：这是什么鬼？html就没有使用过alert函数啊，怎么会有弹框？通过数据库查询，发现东哥输入的留言是这样的：2FXSS漏洞攻击原理首先我们来看一下造成XSS漏洞攻击的原因，获取留言与展示留言的前端代码如下：可以看到，前端通过ajax向后台异步请求json数据，然后通过字符串拼接成一个table DOM的方式来实现留言的显示，这是一种很常见也很常规的前后端数据交互的代码写法，在正常情况下是不会有问题的。但与SQL注入的原理本质相同，问题就出现在HTML代码与用户的输入产生了拼接，这就为恶意漏洞利用者提供了代码执行的机会。正如在上一节中，yrzx404输入的留言是一段合法的javasrcipt代码，在通过拼接后生成的的html页面，浏览器就会将其解析成可执行的javasrcipt代码进行执行，因此造成了XSS跨站脚本攻击(Cross Site Scripting)，造成XSS执行拼接后的Html片段如下：所以，XSS漏洞的执行本质上与很多漏洞的造成原因相同，都是由于“数据与代码未严格分离”，前端将用户的数据当做代码来执行了。3FXSS漏洞的危害XSS漏洞是在客户端执行，如果XSS攻击发生在访问量很大的页面，那将会是很严重的安全事件。试想，如果一个门户网站被小黑客利用XSS搞了个弹框恶作剧，那将会极大的损害公司的声誉与口碑，为公司带来无形的损失。如果仅仅是恶作剧，可能只是一次是不成熟的白帽行为，想借此提醒开发者网站存在漏洞，请尽快修补。但往以获取用户数据为目的黑客，会很隐蔽地收集用户的隐私数据，cookie，详细的用户主机信息，最严重的是进行种网马。这将会使我们的用户处于极其不安全的环境中，这也是为什么XSS漏洞攻击常年在OWASP榜首的位置。这里我们来看看黑客通过XSS获取用户cookie的恶意脚本：上面js脚本构造一个<img> DOM，并通过document.cookie获取到了用户当前会话的cookie并藏在了img标签的url中，这样该页面被浏览器加载后便会带着用户的cookie去某一服务器访问图片了。黑客只需简单搭建一个Http服务器，看访问日志即可收货偷来的cookie：我们知道，cookie是网站服务器用于与客户端保持会话与身份认证的关键属性，一旦黑客获取到了用户cookie，往往意味着黑客同时获取到了客户当前会话的访问权利，从而可以不用登陆验证便可进入用户的系统，这太危险了！4FCookie的防御HttpOnly 为了解决XSS攻击漏洞会造成cookie被劫持攻击的问题，微软在IE 6开始支持Cookie?HttpOnly标准，HttpOnly的作用是，如果在Http Response中，使用HttpOnly来标识的Cookie在浏览器中将无法被JavaScript访问，也就是说document.cookie就失效了。从Java EE 6.0开始，便支持通过Cookie.setHttpOnly(true)来设置HttpOnly类型的Cookie，代码如下：此外还可以直接在WEB-INF/web.xml中将Session相关的Cookie添加HttpOnly标记，代码如下：可以看到，我们的XSS Payload已经无法通过获取到用户的cookie了：如今基本所有公开浏览器都对HttpOnly提供了支持，可以如此高效简单的防御一个web安全问题的手段可真不多见，真可谓四两拨千斤，但悲哀的是HttpOnly标准出现了15年了（2002年制定），但清楚知道其作用的网站开发人员却并不多。 5F前端XSS防护既然XSS漏洞造成原因是由于执行了用户输入数据造成的，那么最直接的防御策略就是对用户的输入进行处理，将有可能造成XSS攻击的字符进行转义，这样就可以防止恶意XSS数据在浏览器中解析成合法的JavaScript脚本来执行。这里我们将留言进行敏感字符转意。作者这里使用了一个开源的用于XSS过滤转义的javascript库 -- js-xss，其支持以npm bower的方式依赖引入，在其github主页有详细安装与使用方法介绍。js-xss库提供了一个非常简单方便的XSS过滤转义函数filterXSS()，我们直接用它来对提交的留言进行过滤处理，代码如下：此时，我们在将XSS Payload提交测试，可以发现浏览器没有触发XSS执行，因为js脚本确实被转义了：这里filteXSS函数将敏感的括号尖括号等字符进行了转义，因此浏览器解析时XSS Payload就失效了。此外，通过测试，用来偷Cookie的XSS Payload也同样失效了。OK，防御策略小有成效，值得庆祝，产品可以上线了！但真的没有问题了吗，这样防御真的可以解决XSS攻击吗？6F我们真的可以相信前端吗？在上一节我们通过将用户的输入内容使用js-xss进行编码转义，便达到了防止用户输入恶意脚本的效果，但是我们能确保向服务器提交的数据一定是通过网站前端正常逻辑进行提交的吗？显然不能，如果黑客通过分析浏览器向服务器提交留言请求的Http数据包格式，手工构造向服务器留言的Http请求数据包，那就绕过了网站前端的XSS过滤逻辑，这里我们模仿黑客通过PostMan来向网站提交留言：不出所料，留言提交成功了，打开网站前端，可以看到XSS Payload成功执行。前端绕过问题，是一个具有普遍性的安全问题，这里安全不仅仅是指网络攻防安全，更多的情况是发生在业务安全中。作者曾接手过一个项目就是这样，系统中有一个类似消费转账的功能，既然是消费，那余额肯定应该是越消费越少，最起码消费金额不能是负数。网站前端对消费金额进行了数字，正负数和金额范围的校验，从而保证用户的消费金额一定是一个合法有效的数字。但是后端接收订单的接口却没有对消费金额的合法性进行判断，导致用户可以绕过前端向接口提交负数的消费金额的问题，用户的余额可以越花越多！虽然这个业务安全漏洞没有被非法利用，但是却为档次参与该项目的开发人员敲响了警钟，一切我们无法保证来源的输入，都应该校验。回归XSS防御的主题，既然我们不能依赖前端过滤，那还有必要在前端进行XSS的防御吗？作者认为，还是有必要的，一方面可以防御DOM Base类型的XSS；另一方面，从网站业务的角度来说，在前端对用户的输入进行合法性校验，可以避免合法用户误操作的问题，在一定程度减轻了服务器的请求压力。7F依赖Thymeleaf模板作者在开发该网站的过程中使用了thymeleaf模板作为前端view层，那么我们一起来看看thymeleaf对于XSS的防御效果如何。这里前端不再通过ajax异步请求的方式获取历史留言，而是采用务器端渲染，直接使用ModeAndView向页面传值：现在进行XSS攻击测试，依然使用alert?XSS Payload进行留言，可以发现XSS并没有触发执行，反而被按照正常的字符串显示出来了，打开控制台，可以发现XSS被转义了：thymeleaf模板在对th:text标签进行渲染的时候，默认对于特殊字符进行了转义处理，这很符合“Security by Default”的安全原则。但是thymeleaf同时也提供了不转义的文本标签th:utext，使用th:utext将会按照数据原有的格式进行渲染，在页面拥有XSS漏洞情况下，依然会有HTML代码拼装的问题，所以这里需要大家在开发与审计过程中要多多留意，能不使用th:utext的地方尽量不要使用，如果必须使用，也要结合具体业务综合考虑此处XSS安全防御的问题。即便如此，作者还是认为将安全问题交由公用框架还是相对靠谱的SDL(安全开发生命周期)，结合当下很多公司采用前后端完全分离的技术框架，您可能会问，如果我们没有使用模板怎么办？前端可以考虑使用angular、vue等框架来构建。总之，一个原则就是尽量依赖专家代码(虽然并不能百分百保证安全)。8FOWASP ESAPI企业级防护OWASP中的ESAPI项目是专为解决web应用程序安全问题的开源项目，是由安全专家写的专家代码，很多著名公司都使用了ESAPI来为网站进行安全加固，ESAPI并不仅仅只提供了对java语言的支持，还提供了很多其他语言的版本库，但对java语言的支持是最完善的。java开发者可以很容易的将ESAPI应用入现有的项目。ESAPI Java项目大多以静态方法的方式提供接口，这里我们使用ESAPI的encoder模块对留言的输入进行编码转义：在实际项目中这样处理会存在一个问题，我们持久化的数据实际是被转义处理后的数据，并非用户的实际输入数据，会造成存储与实际输入不一致的问题，因此可以不在留言输入的接口中做编码，可以在输出接口中对留言做编码，也同样达到了防御XSS的效果，并且保证了数据的一致性。此外，ESAPI还有很多有用的解决web安全的功能，作者以后会陆续推出有关ESAPI使用的文章。9F关于富文本编辑器的处理在网站的一些业务需求中，往往需要允许用户上传具?有自定义样式的文本，例如广告、文章、公告等，而这些具有样式的文本就是富文本，实际上富文本就是一段HTML代码，因此浏览器可以无缝对其提供支持，为用户提供了极大的方便。但富文本的特性也使其成为了XSS攻击的重灾区，因此必须对其防范。关于富文本的XSS防范，我们不能简单的采用前文的方法将输入进行转义，否则转以后的富文本将无法被浏览器渲染。那应该这么办？处理富文本时，要严禁对任何JavaScript事件的支持的，因为在一般情况下，富文本的展示中不应该包含JavaScript事件这种动态效果。此外我们应考虑将一些敏感标签过滤掉，例如<script>、<iframe>、<base>、<form>等不应该出现在富文本中的HTML标签。此外在富文本中对于CSS的过滤也是件很麻烦的事情，需要检查其中是否包含恶意代码。对于威胁的监测，我们需要借助Html解析来进行识别过滤，好在对于富文本的防御过滤，OWASP开源了一个非常棒的项目Anti-Samy：在OWASP ESAPI中的validator模块下?HTMLValidationRule?便是直接引用了AntiSamy，因此如果项目中已近引用了ESAPI，那么可以很方便的对富文本进行过滤：10F?关于源码应广小伙伴的要求，本文所涉及的示例代码都已上传至公众号专用代码仓库，看一遍不如写一遍，有兴趣的小伙伴可以去阅读原文去下载。此外关于XSS的攻击，还有很多绕过技巧，感兴趣的小伙伴可以阅读以下文章：XSS学习笔记【一】XSS学习笔记【二】小结作为抛砖引玉之文，本文并无法覆盖到了XSS防御的方方面面，意在让web开发者重视XSS漏洞的问题。具体的防护方案还需要考虑系统的方方面面，此外，本文只探讨了基于HTML页面的存储型XSS的攻击原理，XSS攻击并不仅仅只发生在html页面中，CSS、Flash等也有可能存在XSS漏洞，相对于HTML更加隐蔽，不易被发现，限于本文篇幅已经过长，剩余的内容将会在以后的文章中来展现，尽情支持和与关注。
以Java的视角来聊聊SQL注入
什么是SQL注入SQL注入是影响企业运营最具有破坏性的漏洞之一。应用程序向后台数据库进行SQL查询时，如果为攻击者提供了影响该查询的能力，就会引起SQL注入。1靶场准备首先我们来准备一个web接口服务，该服务可以提供管理员的信息查询，这里我们采用springboot + jersey 来构建web服务框架，数据库则采用最常用的mysql。下面，我们来准备测试环境，首先建立一张用户表jwtk_admin，SQL如下：然后插入默认的管理员：这样我们就有了两位系统内置管理员了，管理员密码采用MD5进行Hash，当然这是一个很简单的为了作为研究靶场的表，所以没有很全的字段。接下来，我们创建?spring boot + jersey?构建的RESTFul web服务，这里我们提供了一个通过管理员用户名查询管理员具体信息的接口，如下：2SQL注入测试首先我们以开发者正向思维向web服务发送管理员查询请求，这里我们用PostMan工具发送一个GET请求，请求与结果如下图所示：不出我们和开发者所料，Web接口返回了我们想要的结果，用户名为admin的管理员信息。OK，现在开发任务完成，Git Push，Jira任务点为待测试，那么这样的接口就真的没有问题了吗？现在我们发送这样一条GET请求：发送该请求后，我们发现PostMan没有接收到返回结果，而Web服务后台却开始抛?MySQLSyntaxErrorException异常了，错误如下：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''xxxx''' at line 1原因是在我们查询的 xxxx'?处sql语句语法不正确导致。这里我们先不讨论SQL语法问题，我们继续实验，再次构造一条GET查询请求:此时，我们可以惊讶的发现，查询接口非但没有报错，反而将我们数据库jwti_admin表中的所有管理员信息都查询出来了：这是什么鬼，难道管理员表中还有 name=xxxx'or'a'='a 的用户？这就是 SQL Injection。3注入原理分析在接口中接受了一个String类型的name参数，并且通过字符串拼接的方式构建了查询语句。在正常情况下，用户会传入合法的name进行查询，但是黑客却会传入精心构造的参数，只要参数通过字符串拼接后依然是一句合法的SQL查询，此时SQL注入就发生了。正如我们上文输入的name=xxxx'or'a'='a与我们接口中的查询语句进行拼接后构成如下SQL语句：当接口执行此句SQL后，系统后台也就相当于拱手送给黑客了，黑客一看到管理员密码这个hash，都不用去cmd5查了，直接就用123456密码去登录你的后台系统了。Why？因为123456的md5哈希太常见了，别笑，这就是很多中小网站的现实，弱口令横行，不见棺材不落泪！好了，现在我们应该明白了，SQL Injection原因就是由于传入的参数与系统的SQL拼接成了合法的SQL而导致的，而其本质还是将用户输入的数据当做了代码执行。在系统中只要有一个SQL注入点被黑客发现，那么黑客基本上可以执行任意想执行的SQL语句了，例如添加一个管理员，查询所有表，甚至“脱裤” 等等，当然本文不是讲解SQL注入技巧的文章，这里我们只探讨SQL注入发生的原因与防范方法。4JDBC的预处理在上文的接口中，DAO使用了比较基础的JDBC的方式进行数据库操作，直接使JDBC构建DAO在比较老的系统中还是很常见的，但这并不意味着使用JDBC就一定不安全，如果我将传入的参数? xxxx'or'a'='a?整体作为参数进行name查询，那就不会产生SQL注入。在JDBC中，提供了 PreparedStatement?（预处理执行语句）的方式，可以对SQL语句进行查询参数化，使用预处理后的代码如下：同样，我们使用上文的注入方式注入?，此时我们发现，SQL注入没能成功。现在，我们来打印一下被被预处理后的SQL，看看有什么变化：看到了吗?所有的 '? 都被?\'?转义掉了,从而可以确保SQL的查询参数就是参数，不会被恶意执行，从而防止了SQL注入。5Mybatis下注入防范MyBatis?是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架， 其几乎避免了所有的 JDBC 代码和手动设置参数以及获取结果集。同时，MyBatis 可以对配置和原生Map使用简单的 XML 或注解，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录，因此mybatis现在在市场中采用率也非常高。这里我们定义如下一个mapper，来实现通过用户名查询管理员的接口：同样提供Web访问接口：接下来，我们尝试SQL注入name字段，可以发现注入并没有成功，通过打印mybatis的Log可以看到mybatis框架对参数进行了预处理处理，从而防止了注入：那是否只要使用了mybatis就一定可以避免SQL注入的危险？我们把mapper做如下修改，将参数#{name}修改为${name}，并使用name='xxxx' or 'a'='a'?作为GET请求的参数，可以发现SQL注入还是发生了：那这是为什么，mybatis ${}与#{}的差别在哪里？原来在mybatis中如果以${}形式声明为SQL传递参数，mybatis将不会进行参数预处理，会直接动态拼接SQL语句，此时就会存在被注入的风险，所以在使用mybatis作为持久框架时应尽量避免采用${}的形式进行参数传递，如果无法避免（有些SQL如like、in、order by等，程序员可能依旧会选择${}的方式传参），那就需要对传入参数自行进行转义过滤。6JPA注入防范?JPA是Sun公司用来整合ORM技术，实现天下归一的ORM标准而定义的Java Persistence API（java持久层API），JPA只是一套接口，目前引入JPA的项目都会采用Hibernate作为其具体实现，随着无配置Spring Boot框架的流行，JPA越来越具有作为持久化首选的技术，因为其能让程序员写更少的代码，就能完成现有的功能，例如强大的JpaRepository，常规的SQL查询只需按照命名规则定义接口，便可以不写SQL（JPQL/SQL）就可以实现数据的查询操作，从SQL注入防范的角度来说，这种将安全责任抛给框架远比依靠程序员自身控制来的保险。因此如果项目使用JPA作为数据访问层，基本上可以很大程度的消除SQL注入的风险。但是话不能说的太死，在我见过的一个Spring Boot项目中，虽然采用了JPA作为持久框架，但是有一位老程序员不熟悉于使用JPQL来构建查询接口，依旧使用字符串拼接的方式来实现业务，而为项目安全埋下了隐患。安全需要一丝不苟，安全是100 - 1 = 0的业务，即使你防御了99%的攻击，那还不算胜利，只要有一次被入侵了，那就有可能给公司带来很严重的后果。关于JPA的SQL注入，我们就不详细讨论了，因为框架下的注入漏洞属于框架漏洞范畴（如CVE-2016-6652），程序员只要遵循JPA的开发规范，就无需担心注入问题，框架都为你做好幕后工作了。7SQL注入的其他防范办法很多公司都会存在老系统中有大量SQL注入风险代码的问题，但是由于其已稳定支持公司业务很久，不宜采用大面积代码更新的方式来消除注入隐患，所以需要考虑其采用他方式来防范SQL注入。除了在在SQL执行方式上防范SQL注入，很多时候还可以通过架构上，或者通过其他过滤方式来达到防止SQL注入的效果。一切输入都是不安全的：对于接口的调用参数，要进行格式匹配，例如admin的通过name查询的接口，与之匹配的Path应该使用正则匹配（因为用户名中不应该存在特殊字符），从而确保传入参数是程序控制范围之内的参数，即只接受已知的良好输入值，拒绝不良输入。注意：验证参数应将它与输出编码技术结合使用。利用分层设计来避免危险：前端尽量静态化，尽量少的暴露可以访问到DAO层的接口到公网环境中，如果现有项目，很难修改存在注入的代码，可以考虑在web服务之前增加WAF进行流量过滤，当然代码上就不给hacker留有攻击的漏洞才最好的方案。也可以在拥有nginx的架构下，采用OpenRestry做流量过滤，将一些特殊字符进行转义处理。尽量使用预编译SQL语句：由于动态SQL语句是引发SQL注入的根源。应使用预编译语句来组装SQL查询。规范化：将输入安装规定编码解码后再进行输入参数过滤和输出编码处理；拒绝一切非规范格式的编码。小结 其实随着ORM技术的发展，Java web开发在大趋势上已经越来越远离SQL注入的问题了，而有着Entity Framework框架支持的ASP.NET MVC从来都是高冷范。在现在互联网中，使用PHP和Python构建的web应用是目前SQL注入的重灾区。本文虽然是从JAVA的角度来研究SQL注入的问题，但原理上同样适用于其他开发语言，希望读者可以通过此文，触类旁通。珍爱数据，远离拼接，有输入的地方就会有江湖...
客快物流大数据项目(六十七)：客户主题（一）
客户主题一、背景介绍客户主题主要是通过分析用户的下单情况构建用户画像二、指标明细三、表关联关系1、事实表2、维度表3、关联关系用户表与维度表的关联关系如下：四、客户数据拉宽开发1、拉宽后的字段2、SQL语句SELECT
TC."id" ,
TC."name" ,
TC."tel",
TC."mobile",
TC."email",
TC."type",
TC."is_own_reg",
TC."reg_dt",
TC."reg_channel_id",
TC."state",
TC."cdt",
TC."udt",
TC."last_login_dt",
TC."remark",
customercodes."code_desc",
sender_info.first_cdt AS first_sender_cdt ,
sender_info.last_cdt AS last_sender_cdt,
sender_info.billCount AS billCount,
sender_info.totalAmount AS totalAmount
FROM "tbl_customer" tc
LEFT JOIN (
SELECT
"ciid", min(sender_info."id") first_id, max(sender_info."id") last_id, min(sender_info."cdt") first_cdt, max(sender_info."cdt") last_cdt,COUNT(sender_info."id" ) billCount,sum(express_package."actual_amount") totalAmount
FROM "tbl_consumer_sender_info" sender_info
LEFT JOIN "tbl_express_package" express_package
ON SENDER_INFO."pkg_id" =express_package."id"
GROUP BY sender_info."ciid"
) sender_info
ON tc."id" = sender_info."ciid"
LEFT JOIN "tbl_codes" customercodes ON customercodes."type" =16 AND tc."type" =customercodes."code"3、Spark实现实现步骤：在dwd目录下创建 CustomerDWD 单例对象，继承自OfflineApp特质初始化环境的参数，创建SparkSession对象获取客户表（tbl_customer）数据，并缓存数据判断是否是首次运行，如果是首次运行的话，则全量装载数据（含历史数据）获取客户寄件信息表（tbl_consumer_sender_info）数据，并缓存数据获取客户包裹表（tbl_express_package）数据，并缓存数据获取物流字典码表（tbl_codes）数据，并缓存数据根据以下方式拉宽仓库车辆明细数据根据客户id，在客户表中获取客户数据根据包裹id，在包裹表中获取包裹数据根据客户类型id，在物流字典码表中获取客户类型名称数据创建客户明细宽表（若存在则不创建）将客户明细宽表数据写入到kudu数据表中删除缓存数据3.1、初始化环境变量初始化客户明细拉宽作业的环境变量package cn.it.logistics.offline.dwd
import cn.it.logistics.common.{CodeTypeMapping, Configuration, OfflineTableDefine, SparkUtils}
import cn.it.logistics.offline.OfflineApp
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.storage.StorageLevel
import org.apache.spark.sql.functions._
import org.apache.spark.sql.types.IntegerType
/**
* 客户主题数据的拉宽操作
*/
object CustomerDWD extends OfflineApp {
//定义应用的名称
val appName = this.getClass.getSimpleName
def main(args: Array[String]): Unit = {
/**
* 实现步骤：
* 1）初始化sparkConf对象
* 2）创建sparkSession对象
* 3）加载kudu中的事实表和维度表的数据（将加载后的数据进行缓存）
* 4）定义维度表与事实表的关联
* 5）将拉宽后的数据再次写回到kudu数据库中（DWD明细层）
* 5.1：创建车辆明细宽表的schema表结构
* 5.2：创建车辆宽表（判断宽表是否存在，如果不存在则创建）
* 5.3：将数据写入到kudu中
* 6）将缓存的数据删除掉
* 7）停止任务
*/
//1）初始化sparkConf对象
val sparkConf: SparkConf = SparkUtils.autoSettingEnv(
SparkUtils.sparkConf(appName)
)
//2）创建sparkSession对象
val sparkSession: SparkSession = SparkUtils.getSparkSession(sparkConf)
sparkSession.sparkContext.setLogLevel(Configuration.LOG_OFF)
//数据处理
execute(sparkSession)
}
/**
* 数据处理
*
* @param sparkSession
*/
override def execute(sparkSession: SparkSession): Unit = {
sparkSession.stop()
}
}3.2、加载客户相关的表并缓存加载客户表的时候，需要指定日期条件，因为客户主题最终需要Azkaban定时调度执行，每天执行一次增量数据，因此需要指定日期。判断是否是首次运行，如果是首次运行的话，则全量装载数据（含历史数据）//导入隐士转换
import sparkSession.implicits._
val customerSenderInfoDF: DataFrame = getKuduSource(sparkSession, TableMapping.consumerSenderInfo, Configuration.isFirstRunnable).persist(StorageLevel.DISK_ONLY_2)
val customerDF = getKuduSource(sparkSession, TableMapping.customer, true).persist(StorageLevel.DISK_ONLY_2)
val expressPageageDF = getKuduSource(sparkSession, TableMapping.expressPackage, true).persist(StorageLevel.DISK_ONLY_2)
val codesDF: DataFrame = getKuduSource(sparkSession, TableMapping.codes, true).persist(StorageLevel.DISK_ONLY_2)
val customerTypeDF = codesDF.where($"type" === CodeTypeMapping.CustomType)3.3、定义表的关联关系为了在DWS层任务中方便的获取每日增量客户表数据（根据日期），因此在DataFrame基础上动态增加列（day），指定日期格式为yyyyMMdd代码如下：//TODO 4）定义维度表与事实表的关联关系
val left_outer = "left_outer"
/**
* 获取每个用户的首尾单发货信息及发货件数和总金额
*/
val customerSenderDetailInfoDF: DataFrame = customerSenderInfoDF.join(expressPageageDF, expressPageageDF("id") === customerSenderInfoDF("pkgId"), left_outer)
.groupBy(customerSenderInfoDF("ciid"))
.agg(min(customerSenderInfoDF("id")).alias("first_id"),
max(customerSenderInfoDF("id")).alias("last_id"),
min(expressPageageDF("cdt")).alias("first_cdt"),
max(expressPageageDF("cdt")).alias("last_cdt"),
count(customerSenderInfoDF("id")).alias("totalCount"),
sum(expressPageageDF("actualAmount")).alias("totalAmount")
)
val customerDetailDF: DataFrame = customerDF
.join(customerSenderDetailInfoDF, customerDF("id") === customerSenderInfoDF("ciid"), left_outer)
.join(customerTypeDF, customerDF("type") === customerTypeDF("code").cast(IntegerType), left_outer)
.sort(customerDF("cdt").asc)
.select(
customerDF("id"),
customerDF("name"),
customerDF("tel"),
customerDF("mobile"),
customerDF("type").cast(IntegerType),
customerTypeDF("codeDesc").as("type_name"),
customerDF("isownreg").as("is_own_reg"),
customerDF("regdt").as("regdt"),
customerDF("regchannelid").as("reg_channel_id"),
customerDF("state"),
customerDF("cdt"),
customerDF("udt"),
customerDF("lastlogindt").as("last_login_dt"),
customerDF("remark"),
customerSenderDetailInfoDF("first_id").as("first_sender_id"), //首次寄件id
customerSenderDetailInfoDF("last_id").as("last_sender_id"), //尾次寄件id
customerSenderDetailInfoDF("first_cdt").as("first_sender_cdt"), //首次寄件时间
customerSenderDetailInfoDF("last_cdt").as("last_sender_cdt"), //尾次寄件时间
customerSenderDetailInfoDF("totalCount"), //寄件总次数
customerSenderDetailInfoDF("totalAmount") //总金额
)3.4、创建客户明细宽表并将客户明细数据写入到kudu数据表中客户明细宽表数据需要保存到kudu中，因此在第一次执行客户明细拉宽操作时，客户明细宽表是不存在的，因此需要实现自动判断宽表是否存在，如果不存在则创建实现步骤：在CustomerDWD 单例对象中调用save方法实实现过程：在CustomerDWD 单例对象Main方法中调用save方法save(customerDetailDF, OfflineTableDefine.customerDetail)3.5、删除缓存数据为了释放资源，客户明细宽表数据计算完成以后，需要将缓存的源表数据删除。//移除缓存
customerDetailDF.unpersist
codesDF.unpersist
expressPackageDF.unpersist
customerSenderDF.unpersist
customerDF.unpersist3.6、完整代码package cn.it.logistics.offline.dwd
import cn.it.logistics.common.{CodeTypeMapping, Configuration, OfflineTableDefine, SparkUtils, TableMapping}
import cn.it.logistics.offline.OfflineApp
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.storage.StorageLevel
import org.apache.spark.sql.functions._
import org.apache.spark.sql.types.IntegerType
/**
* 客户主题数据的拉宽操作
*/
object CustomerDWD extends OfflineApp {
//定义应用的名称
val appName = this.getClass.getSimpleName
def main(args: Array[String]): Unit = {
/**
* 实现步骤：
* 1）初始化sparkConf对象
* 2）创建sparkSession对象
* 3）加载kudu中的事实表和维度表的数据（将加载后的数据进行缓存）
* 4）定义维度表与事实表的关联
* 5）将拉宽后的数据再次写回到kudu数据库中（DWD明细层）
* 5.1：创建车辆明细宽表的schema表结构
* 5.2：创建车辆宽表（判断宽表是否存在，如果不存在则创建）
* 5.3：将数据写入到kudu中
* 6）将缓存的数据删除掉
* 7）停止任务
*/
//1）初始化sparkConf对象
val sparkConf: SparkConf = SparkUtils.autoSettingEnv(
SparkUtils.sparkConf(appName)
)
//2）创建sparkSession对象
val sparkSession: SparkSession = SparkUtils.getSparkSession(sparkConf)
sparkSession.sparkContext.setLogLevel(Configuration.LOG_OFF)
//数据处理
execute(sparkSession)
}
/**
* 数据处理
*
* @param sparkSession
*/
override def execute(sparkSession: SparkSession): Unit = {
//导入隐士转换
import sparkSession.implicits._
val customerSenderInfoDF: DataFrame = getKuduSource(sparkSession, TableMapping.consumerSenderInfo, Configuration.isFirstRunnable).persist(StorageLevel.DISK_ONLY_2)
val customerDF = getKuduSource(sparkSession, TableMapping.customer, true).persist(StorageLevel.DISK_ONLY_2)
val expressPageageDF = getKuduSource(sparkSession, TableMapping.expressPackage, true).persist(StorageLevel.DISK_ONLY_2)
val codesDF: DataFrame = getKuduSource(sparkSession, TableMapping.codes, true).persist(StorageLevel.DISK_ONLY_2)
val customerTypeDF = codesDF.where($"type" === CodeTypeMapping.CustomType)
//TODO 4）定义维度表与事实表的关联关系
val left_outer = "left_outer"
/**
* 获取每个用户的首尾单发货信息及发货件数和总金额
*/
val customerSenderDetailInfoDF: DataFrame = customerSenderInfoDF.join(expressPageageDF, expressPageageDF("id") === customerSenderInfoDF("pkgId"), left_outer)
.groupBy(customerSenderInfoDF("ciid"))
.agg(min(customerSenderInfoDF("id")).alias("first_id"),
max(customerSenderInfoDF("id")).alias("last_id"),
min(expressPageageDF("cdt")).alias("first_cdt"),
max(expressPageageDF("cdt")).alias("last_cdt"),
count(customerSenderInfoDF("id")).alias("totalCount"),
sum(expressPageageDF("actualAmount")).alias("totalAmount")
)
val customerDetailDF: DataFrame = customerDF
.join(customerSenderDetailInfoDF, customerDF("id") === customerSenderInfoDF("ciid"), left_outer)
.join(customerTypeDF, customerDF("type") === customerTypeDF("code").cast(IntegerType), left_outer)
.sort(customerDF("cdt").asc)
.select(
customerDF("id"),
customerDF("name"),
customerDF("tel"),
customerDF("mobile"),
customerDF("type").cast(IntegerType),
customerTypeDF("codeDesc").as("type_name"),
customerDF("isownreg").as("is_own_reg"),
customerDF("regdt").as("regdt"),
customerDF("regchannelid").as("reg_channel_id"),
customerDF("state"),
customerDF("cdt"),
customerDF("udt"),
customerDF("lastlogindt").as("last_login_dt"),
customerDF("remark"),
customerSenderDetailInfoDF("first_id").as("first_sender_id"), //首次寄件id
customerSenderDetailInfoDF("last_id").as("last_sender_id"), //尾次寄件id
customerSenderDetailInfoDF("first_cdt").as("first_sender_cdt"), //首次寄件时间
customerSenderDetailInfoDF("last_cdt").as("last_sender_cdt"), //尾次寄件时间
customerSenderDetailInfoDF("totalCount"), //寄件总次数
customerSenderDetailInfoDF("totalAmount") //总金额
)
save(customerDetailDF, OfflineTableDefine.customerDetail)
// 5.4：将缓存的数据删除掉
customerDF.unpersist()
customerSenderInfoDF.unpersist()
expressPageageDF.unpersist()
customerTypeDF.unpersist()
sparkSession.stop()
}
}
