0基础学SQL(SQL+Kettle+FineBl)

一、SQL

1. 概述

数据库又分为关系型数据库 和 非关系型数据库 

2. MySQL

 

2.1 mysql下载 

小皮一键下载

2.2 SQL语言的分类

 2.3 SQL 通用语法

 3.3 数据类型

 3. DDL

3.1 DDL-数据库 

MySQL 的组成结构

一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库、商城项目shop数据库、微信项目wechat数据库。

一个数据库中还可以同时包含多个数据表，而数据表才是真正用于存放数据的位置。(类似我们office软件中的Excel表格)，理论上一个功能就对应一个数据表。如博客系统中的用户管理功能，就需要个user数据表、博客中的文章就需要一个article数据表、博客中的评论就需要一个message数据表。

一个数据表又可以拆分为多个字段，每个字段就是一个属性。

个数据表除了字段以外，还有很多行，每一行都是一条完整的数据(记录) 

 3.1.1 创建数据库

创建+数据库=create database 数据库名称(字母+数字+下划线组成，以字母开头，不能出现中文以及特殊字符)
基本语法:

 mysql> create database [ if not exists ] 数据库名称 [default charset = 编码式];

创建db_itheima库
create database db_itheima;
创建db1库并指定默认字符集
create database db_itheima default charset = utf8;
创建数据库之前先进行是否存在判断
create database if not exists db_itheima default charset=utf8;

 3.1.2 查询数据库

显示+所有数据库=show databases;
基本语法:

显示所有数据库

 show databases;

查看数据库的创建过程 

show create database dbitheima;

  3.1.2 删除数据库

删除+数据库=drop database 数据库名称;
基本语法:

mysql> drop database数据库名称;

-- 案例:删除db_itheima数据库
mysql> drop database db_itheima;

 3.2 DDL-数据表

特别注意:创建数据表必须有一个前提，首先要明确选择某一个数据库。 

use 数据库名称 => 使用哪个数据库

select database(); => 查看当前所在的数据库

3.2.1 创建表 

创建+数据表=> create table 数据表名称；

engine=innodb => 数据保存方式为innodb（默认）

default charset=utf8 => 字符为UTF-8

基本语法: 

mysql> create table 数据表名称(
    字段1 字段类型 [字段约束],
    字段2 字段类型   [字段约束]
）[engine=innodb default charset=utf8];

字段类型 

整数类型 => int
小数类型 =>decimal(数据总长度,，小数点后保留几仪)=>decimal(5，2)=>999.99

字符串类型 =>255个字符以内用char/varchar，char固定长度，varchar变化长度，大于255个字符一般使用text文本

日期时间类型 =>date类型(年-月-日)、time类型(小时:分钟:秒数)、datetime类型(年-月-日 小时:分钟: 秒数

案例：使用SQL语句创建一个会员表，有子个字段:
id编号、username用户名、password(要求使用md5进行加密 =>32位) 

use db_itheima;
create table tb_user(
    id int,
    username varchar(10),
    password char(32)

)engine=innodb default charset=utf8

3.2.2 查看数据表

显示所有数据表(当前数据库)

mysql> use 数据名称;

mysql> show tables;

显示数据表的创建过程(编码格式、字段等信息)

mysql> desc 数据表名称 ；

 `` 与 ’‘ 的区别

`` 反撇号主要用于标注数据库名称、数据表名称以及字段名称 =>把反撇号中的内容当做普通文本水进行处埋!

’‘ 单引号主要用于数据的增删改查中，用于表示文本型的数据

3.2.3 数据表的字段添加 

基本语法

mysql> alter table 数据表名称 add 新字段名称 字段类型 first | after 其他字段名称;

选项说明:

first:把新添加字段放在第一位
after字段名称:把新添加字段放在指定字段的后面

案例：在tb_article文章表中添加一个addtime字段，类型为date(年-月-日) 

把addtime添儿到数据表的第1位
alter table tb_article add addtime date first;

把addtime添加到数据表的content字段的后面
alter table tb_article add addtime date after content;.

  3.2.4 数据表字段的修改

修改字段名称与字段类型(也可以只修改名称)

 mysql> alter 数据表 change 修改前字段 修改后字段 修改后字段类型;

仅修改字段的类型

modify 只能修改字段类型

mysql> alter table 数据表 modify 字段名 修改后字段类型;

 案例：

案例:修改tb_user数据表，把username字段更改为user同时修改数据字段类型为40个字符长度

alter table tb user change username user varchar(40) desc tb user;


modify 只能修改字段类型
案例:修改tb_user数据表，把user字段类型长度更改为20

alter table tb user modify user varchar(20);

  3.2.5 数据表字段的删除

mysql> alter 表名 drop 字段名称；

 3.2.6 修改数据表名称

 rename table 旧名称 to 新名称;

  3.2.6 删除数据表

mysql> drop table 数据表名称；

 4. DML

4.1 字段类型详解

 

 4.2 插入表记录 insert

语法格式：

向表中插入所有字段,字段的顺序为创建表时的顺序

insert into 表(字段1,字段2,字段3...) values(值1,值2,值3...)

 案例：

#向数据表tb_student中的每“列添加数据

insert into tb_student values(1,'罗豪'，23，'男'，'广州市天河区');



# 向数据表tb_studnt(id，name，age)列插入数据

insert into tb_student(id,name,age)values(2,'金梁'，25)


;# 批量插入

insert into tb_student values
('杨宁'，26，'男'，'广州市花都区')，(4，'李林盛'，22，'男'，'广州市番禺区');

4.3 修改表记录 update

基本语法:

 update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,...where 更新条件;

特别说明：如果在更新数据时，不指定更新条件，则其会把这个数据表的所有记录全部更新。

 案例 ：

把tb_student数据表中id=2的记录，gender字段值更新为男

update tb student set gender='男'where id =2:


把tb_student数据表中的年龄进行+1操作目address设置为广州市海珠区

update tb_student set age=age+1, address='广州市海珠区'where id = 2;

 4.4 删除表记录 delete

基本语法：

delete from 数据表名称 [where删除条件]

案例：删除tb_user表中，id=1002的用户信息

delete from tb_user where id = 1002;

delete from与truncate清空数据表操作

delete from 数据表： truncate 数据表;

5. SQL 约束 

主键约束、非空约束、唯一约束、默认值约束、外键约束【了解】

 5.1 主键约束

create table tmp_dsj
(
    id   int primary key ,
    name varchar(12)
);

或者

create table tmp_dsj
(
    id   int,
    name varchar(12),
    primary key (id)
);

删除tb_article1数据表的主键

alter table tb_articlel drop primary key;

如果数据表已存在，添加主键方式
alter table tb_articlel add primary key(id);

自动增长 

我们可以在表中使用 auto_increment (自动增长列)关键字 
自动增长列类型必须是 整型，自动增长列必须为键(一般是 主键 )。

案例：下列 SQL 语句把"Persons" 表中的"ld" 列定义为 auto_increment 主键 

create table Persons(
    id int auto_increment primary key，
    first_name varchar(255),
    last_name varchar(255),
    address varchar(255),
    city varchar(255
)default charset=utf8;

 向persons添加数据时，可以不为Id字段设置值，也可以设置成null，数据库将自动维护主键值

insert into Persons(first_name,last_name) values('Bill','Gates'),
insert into Persons(id,first_name,last_name) values(null,'Bill', 'Gates');

删除tb_member数据表士键(有自动增长的前提下，先移除自动增长，然后再移除主键)

alter table tb_member modify id int;
alter table tb member drop primary key;

 5.2 非空约束

NOT NULL约束强制列不接受 NULL值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

id 不能为空

create table tmp_dsj
(
    id   int not null ,
    name varchar(12),
    primary key (id)
);

 5.3 唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 

 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

唯一约束 =>与主键约束非常类似，但是又有所不同：

① 唯一约束，要求此列每一个值都必须是唯一的(不能重复)

② 唯一约束，要求此列可以为空
③ 唯一约束，在一个表中可以同时存在多个!

create table tmp_dsj
(
    id   int not null ,
    name varchar(12),
    mobile char(11) unique ,
    primary key (id)
);

5.4 默认值约束

某一列在插入数据是拥有默认值

default 默认值 

name 默认为 张三

create table tmp_dsj
(
    id   int not null ,
    name varchar(12) default '张三',
    mobile char(11) unique ,
    primary key (id)
);

5.5 外键约束

 对于已经存在的字段添加外键约束

为cls_id字段添加外键约束

alter table 数据表 add foreign key(外键字段)
references 数据表(主键)
[on delete cascadel set null]
[on update cascade  set null]: 

在创建数据表时设置外键约束

#创建一个大哥表
create table dage(
    id int not null auto_increment,
    name varchar(20),
    primary key(id),
)default charset=utf8;

#添加测试数据
insert into dage values(nu11，'乌鸦');
insert into dage values (nu11，'陈浩南');

#创建一个小弟表，一定不要先插入数据，先建立外键约束=>才能插入数据
create table xiaodi(
    id int not null auto_increment,
    name varchar(20),
    dage_id int,
    primary key(id)
)default charset=utf8;

#添加外键约束，针对dage_id创建外键约束，关联dage表中的id主键
alter table xiaodi add foreign key(dage id) references dage(id) 
on delete cascade on updatecascade;

#插入测试数据
insert into xiaodi values(null,'山鸡'1);
insert into xiaodi values(null,'大天二'，1);
insert into xiaodi values(null,'乌鸦的小弟'，2);

测试外键约束，说有一天鸟鸦被干掉了，其小弟也随之而去

delete from dage where id = 2;

 删除外键约束

删除外键约束，首先要查看外键的名称
show create table xiaodi;

删除外键约束
alter table xiaodi drop foreign key xiaodi_ibfk_1;

6. DQL 数据查询

 五子句查询

select *from 数据表 [where子句][group by分组子句][having子句][order by子句][limit子
句];

where子句
group by子句

having子句
order by子句
limit子句
注意:在以且5个子句中，五子句的顺序不能颠倒! 

数据集：

CREATE TABLE product(
    pid INT PRIMARY KEY,
    pname VARCHAR(20),
    price DOUBLE,
    category_id VARCHAR(32)
);

INSERT INTO prOdUCt VALUES(1,'联想',5000,'c001');
INSERT INTO prOdUCt VALUES(2,'海尔',3000,'c001');
INSERT INTO prOduct VALUES(3,'雷神',5000,'c001');
INSERT INTO prodUct VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO prodUct VALUES(5,'真维斯',200,'c002');
INSERT INTO prOdUCt VALUES(6,'花花公子',440,'c002');
INSERT INTO prOdUct VALUES(7,'劲霸',2000,'c002');
INSERT INTO prodUct VALUES(8,'香奈儿',800,'c003');
INSERT INTO prodUct VALUES(9,'相宜本草',200,'c003');
INSERT INTO prOdUCt VALUES(10,'面霸',5,'c003');
INSERT INTO prodUct VALUES(11,'好想你枣',56,'c004');
INSERT INTO prodUct VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product VALUES(13,'海澜之家',1,'c002');

6.1 简单查询

#根据某些条件从某个表中查询指定字段的内容格式:

select [distinct] * | 列名,列名 from 表where 条件

#1.查询所有的商品
select *from product;
#2.查询商品名和商品价格
select pname,price from product;
#3.查询结果是表达式(运算查询):将所有商品的价格+10元进行显示
select pname,price+10 from product;

6.2 条件查询

 比较查询

# 查询商品名称为花花公子”的商品所有信息:
SELECT *FROM product WHERE pname ='花花公子'
# 查询价格为800商品
SELECT *FROM product WHERE price = 800;
#查询价格不是800的所有商品
SELECT * FROM product WHERE price! 800;
SELECT *FROM product WHERE price <> 800;
#查询商品价格大于60元的所有商品信息
SELECT*FROM product WHERE price>60;
查询商品价格小于等于800元的所有商品信息
SELECT *FROM product WHERE price <= 800;

 范围查询

#查询商品价格在200到1000之间所有商品
select *from product where price between 200 and 1000;
#查询商品价格是200或800的所有商品
select *from product where price in(200，800);
等价于
select *from product where price =200 or price = 800

 逻辑查询

查询商品价格在200到1000之间所有商品并
select *from product where price >= 200 and price <= 1000;
#查询商品价格是200或800的所有商品
select *from product where price =200 or price = 800;
#查询价格不是800的所有商品
select *from product where price <> 800;
select *from product where not(price = 800);
select * from  product where price != 800;

 模糊查询

模糊査询 => 关键字 like => 两个字符

%:代表匹配任意个任意字符(没有数量限制没有字符限制)

_:下划线，代表匹配任意某个字符(只能匹配1个字符)

# 查询以'香'开头的所有商品
select *from product where pname like '香%';
# 查询第二个字为'想'的所有商品
select *from product where pname like'_想%

 非空查询

空值查询与非空查询:在SQL语句中空值与非空筛选不能使用=，

必须使用is null与is not null来进行筛选

#查询没有分类的商品
select *from product where category_id is null;
#查询有分类的商品
select *from product where category_id is not null;

 6.3 聚合函数

之前做的査询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值;另外聚合函数会忽略空值。

# 1、查询商品的总条数
select count(*)from product;
# 2、查询价格大于200商品的总条数(注意:where子句和聚合函数执行有先后顺序，where子句先执行，聚合函数后执行)
select count(*)from product where price>200;
# 3、查询分类为'c001'的所有商品的总和
select sum(price)from product where category_id ='c001';#4、查询分类为'c002'所有商品的平均价格
select avg(price)from product where category_id ='c002';#5、查询商品的最大价格和最小价格
select max(price),min(price)from product;

 6.3.1 分组查询 groupby

分组查询介绍
分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。分组查询基本的语法格式如下:
        GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

说明：

        列名: 是指按照指定字段的值进行分组。

        HAVING 条件表达式: 用来过滤分组后的数据。

        WITH ROLLUP:在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

分组先发生聚合后执行

根据gender字段来分组
select gender from students group by gender;
根据name和gender字段进行分组
select name, gender from students group by name, gender;

 回溯统计：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

比如在统计学生表中男女同学的人数信息的同时，想统计学生表中的总数据量
select gender,count(*)from tb student group by gender with rollup;

having子旬=>功能与where子句非常类似，可以实现刘数据进行条件筛选

主要执行的顺序有所不同，where了旬发生在分组之前，having了旬发生在分组之后
如果我们间对分组前的数据进行筛选，只能使用where子句。

如果相对分组之后数据进行筛选，只能使用having子句。

案例:对于没有分组的情况，having子句也可以替代where子句，查询商品价格大于500元的商品信息
select *from product where price >500;
select *from product having price >500;

 案例:如果想对分组后的数据在进行选，则只能使用having子句，统计各个分类商品的个数,且只显示个数人于1的信息
分析:分组统计每个分组下商品的数量，然后再针对分组后的结果，选个数大j1的商品信息

select category_id,count(*) as count_num from product group by category_id having count_num>1;

 6.3.2 排序查询 order by

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

格式: SELECT * FROM 表名ORDER BY 排序字段 ASC | DESC;

ASC 升序(默认)

DESC 降序

SQL排序查询中，也可以同时按照多个字段进行排序

ORDER BY price DESC,category_id DESC => 先按照价格排序，一样则再按照分类排序

#1.使用价格排序(降序)
SELECT *FROM produCt ORDER BY price DESC:
#2.在价格津序(隆字)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC

 6.3.3 限制查询 limt

限制询数量
必须写在SQL语句最末端! 

基本语法:

select * from 数据表 limit M,N;

M:整数，表示从第几条索引开始，计算方式(当前页-1)*每页显示条数

N:整数，表示查询多少条数据

代表只査询数据表中的前N条数据；实际工作中,limit子句也经常与order by配合使用。 

SELECT * FROM product ORDER BY price DESC limit 5;

 6.3.4 去重操作 distinct

针对某列数据进行去重

查询product商品表中有哪些商品分类(重复的数据只保留1份)?
select distinct category_id from product;

6.4 多表查询

查询的数据分散在多张表中，需要连接数据表

准备数据：

# 准备多表査询数据集(tb_category/tb_goods)
create table tb_category
(
    cid   int auto_increment primary key,
    cname varchar(20)
) engine = innodb
  default charset = utf8;
-- 插入测试数据
insert into tb_category
values (null, '手机'); -- cid =1
insert into tb_category
values (nuLl, '电脑'); -- cid = 2

create table tb_goods
(
    id    int auto_increment primary key,
    title varchar(255),
    price decimal(11, 2),
    cid   int
)engine = innodb default charset = utf8;

-- 插入测试数据
insert into tb_goods values (null,'Apple iPhone 13',5799.00,1);
insert into tb_goods values (null,'Rog败家之眼',16999.00,2);
insert into tb_goods values (null,'黑鲨游戏手机',9998.00,1);
insert into tb_goods values(null,'Lenovo美帝良心想',16999.00,2);
insert into tb_goods values(null,'华为P50 Pro',8999.00,1);

 6.4.1 交叉连接

没有意义，但是它是所有连接的基础。其功能就是将表1和表2中的每一条数据进行连接。
结果:
字段数=表1字段+表2的字段
记录数=表1中的总数量*表2中的总数量(笛卡尔积)

6.4.2 内连接 

语法格式：

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2 

 说明:

inner join 就是内连接查询关键字
on 就是连接查询条件

例1:使用内连接查询学生表与班级表:
select * from students as s inner join classes as c on s.cls_id = c.id;

内连接查询属于默认的多表连接查询
        select * from tb_goods g join tb _category c on g.cid = c.cid

以连接查询的另外一种写法:

        select*from 表A,表B where 表A.字段= 表B.字段;

6.4.2 外连接

内连接查询只会查询满足关联条件的结果。

外连接查询有主表这样一个概念，默认情况下会返回主表中的所有数据，不管其是否否则关联条件

外连接查询又分为两种情况:
左外连接查询(语法左边数据表就是主表)

右外连接查询(语法右边数据表就是主表)

左外连接

语法格式：

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2 

说明:

• left join 就是左连接查询关键字·on 就是连接查询条件
• 表1是左表
• 表2 是右表

 案例：查询商品所属类别没有也显示:

select
id.title,price,cname
from tb_goods g
Left join tb_category c
on g.cid = c.cid;

 右外连接相反

6.4.5 自连接 

 

select *from tb_area city 
inner join tb_area province
on city.pid = province.aid 
and province.atitle ='广东省'

或

select city.aid,city.atitle from tb_area city inner join tb_area province
on city.pid = province.aid 
where province.atitle ='广东省';

7. 子查询

语法：

select * from (select * from 数据表) A;

 

8. MySQL 高级之窗口函数

窗口函数基本语法

select 聚合函数() over(PARTITION BY __ ORDER BY __) from 数据表

over()开窗函数，用于框选数据，如果没有任何参数，代表针对from后面的数据表中的所有记录

PARTITION BY：分组操作，主要是针对over开窗函数中的数据进行分组

# ORDER BY：如果数据有分组，只想组内数据进行排序，PARTITIONBY __ ORDER BY __

先执行over()开窗，框选数据 再使用聚合函数求聚合结果

 示例：

窗口函数的优点

• 简单

        窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。

• 快速

        这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。

• 多功能性

        最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号，组内排序等等

8.1 案例：窗口函数 over

准备数据源：

# 案例1:查询employee雇员表，显示员工姓名、员工薪资以及所有员工的平均薪资情况
use db_winfunc;
# 以前
select first_name, last_name, salary
from employee as e1
         join (select avg(salary) avg_salary from employee) as e2;
# 窗口函数
select first_name, last_name, salary, avg(salary) over () avg_salary
from employee;

# 案例2：创建报表，除了查询每个人的工资之外，还要统计出公司每月的工资支出
select employee.first_name, employee.last_name, employee.salary, sum(employee.salary) over ()
from employee;

# 案例3：统计采购表中的平均采购价格，并与明细一起显示(每件物品名称，价格)
select purchase.item, purchase.price, avg(purchase.price) over () avg_price
from purchase;

# 案例4：计算出员工的薪水和平均薪水之间的差
select employee.first_name,
       employee.last_name,
       employee.salary,
       avg(employee.salary) over ()                      avg_salary,
       employee.salary - avg(employee.salary) over () as diff
from employee;

# 案例5：
# 需求:创建报表统计每个员工的工龄和平均工龄之间的差值。
# 报表中包含如下字段:
# 员工的名字，员工的姓氏，员工的工龄，所有员工的平均工龄，员工工龄和平均工龄之间的差值
select first_name,
       last_name,
       years_worked,
       avg(years_worked) over (),
       years_worked - avg(years_worked) over ()
from employee;

# 案例6：需求:查询id为2的部门所采购的所有商品，并将计算了每项支出占总采购金额的占比
select item, price, price / sum(price) over () * 100
from purchase p1
where p1.department_id = 2;

# 案例7：创建报表，在purchase表基础上，添加平均价格和采购总金额两列
# 包含如下字段:id、item、price，平均价格和所有物品总价格
select item, price, avg(price) over (), sum(price) over ()
from purchase;

8.2 排序函数 

100                                        101 1

101                 rank()              100 2

92                   =>                   100 2

100                                         92  4      

# rank() 函数：有并列且序号不连续

# 案例1：根据游戏的评分进行排名，要求有并列且排名序号不连续
select name, editor_rating, rank() over (order by editor_rating)
from game;

# 案例2：需求:统计每个游戏的名字，分类，更新日期，更新日期序号
select name, genre,updated, rank() over (order by updated)
from game;

100                                                101 1

101                 dense_rank()          100 2

92                           =>                   100 2

100                                                 92  3      

# 案例1:对游戏的安装包大小进行排序，使用`DENSE RANK() 返回游戏名称，包大小以及序号。
select name, size,dense_rank() over (order by size)
from game;

100                                                101 1

101                 dense_rank()          100 2

92                           =>                   100 3

100                                                 92  4      

# 案例1:对游戏的安装包大小进行排序，使用`DENSE RANK() 返回游戏名称，包大小以及序号。
select name, size, dense_rank() over (order by size)
from game;

# row_number()函数: 不并列且永远都是连续的
select name, platform, editor_rating, row_number() over (order by editor_rating)
from game;

二、可视化ETL平台-Kettle

数据仓库

数据仓库是一个很大的数据存储集合，出于企业的分析性报告和决策支持目的而创建的，对多样的业务数据言进行筛选与 整合。它为企业提供一定的BI能力，指导业务流程改进，监视时间成本、质量以及控制。

进入解压后的文件夹data-integration,双击Spoon.bat，启动kettle

1. 快速入门

开发步骤

 2. excel数据导入到MySQL

 

 

3. Transformation开发

4. 插入更新组件

5. 条件判断

 

6. SQL脚本

 执行带参数的sql

7. JOB 开发

 

三、Fine BI