DuckDb

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-- =-=-=-=-=- 02/07/2025 - scripts de teste do DuckDB -=-=-=-=-=-

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

--pasta: C:\Jhan\duckDB

-- =-=-=-=-=-=-=-=-=-=-=-=-=

-- Lendo uma tabela do Excel

-- =-=-=-=-=-=-=-=-=-=-=-=-=

select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/PessoaDados.xlsx');

select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/PessoaDados.xlsx', ignore_errors=true);

select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/Valores_Mes.xlsx');

select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/Pandas_Excel_2.xlsx');

select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/TbComercialCredito.xlsx');

describe select * from read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/PessoaDados.xlsx');

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- Criando uma Tabela origem: arquivo Excel

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

--Drop table TbComissao;

create table TbComercialCredito as SELECT * FROM read_xlsx('/home/jhan/Jhan/ArquivosDev/Origem/TbComercialCredito.xlsx');

select * from TbComercialCredito;

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- Criando uma Tabela origem: arquivo CSV

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

--Drop table TbComissao;

create table TbComissao as select * FROM read_csv('C:\Jhan\duckDB\CadastroComercialCredito_duck.csv');

select * from TbComissao;

select * from TbComercialCredito a inner join TbComissao b

on a.CpfCnpjAgente = b.CpfCnpjAgente

where a.CpfCnpjAgente = '02308023112'

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- Criando uma Tabela origem: arquivo json

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- json Simples:

select * from read_json('/home/jhan/Jhan/ArquivosDev/Origem/Filmes.json');

-- drop table TbFilmes;

create table TbFilmes as select * FROM read_json('/home/jhan/Jhan/ArquivosDev/Origem/Filmes.json');

-- --------------------

select * from TbFilmes;

-- json Complexo:

select * from read_json('/home/jhan/Jhan/ArquivosDev/Origem/dadosMultiplos.json');

-- drop table TbDadosMultiplos;

create table TbDadosMultiplos as select * FROM read_json('/home/jhan/Jhan/ArquivosDev/Origem/dadosMultiplos.json');

-- ----------------------------

select * from TbDadosMultiplos;

select * from TbComercialCredito a inner join TbComissao b

on a.CpfCnpjAgente = b.CpfCnpjAgente

where a.CpfCnpjAgente = '02308023112'

describe select * from read_json('C:\Jhan\duckDB\testeJson2.json');

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- Tratamento de Datas

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- Data Corrente, data atual, data de hoje:

select current_date, now(), today();

select get_current_time();

-- Transforma Date em string:

select cast(today() as text);

select Substring(cast(today() as text), 1,4);

select Substring(cast(today() as text), 6,2);

select Substring(cast(today() as text), 9,2);

select cast(today() as text) as dataAtual,

Substring(cast(today() as text), 1,4) as ano,

cast(Substring(cast(today() as text), 6,2) as int) as mes,

Substring(cast(today() as text), 9,2) as dia;

CREATE TEMPORARY TABLE constantes AS

select '064.787.018-59' as cpf;

select * FROM constantes;

select cpf = '121.656.656/5645'

select replace(replace(cpf,'.',''),'-','') as cpf from constantes;

select strftime("DtAtualizacao", '%Y-%m'), count(*) from TbComercialCredito

group by strftime("DtAtualizacao", '%Y-%m'), strftime("DtAtualizacao", '%Y-%m')

select strftime(DATE '1992-03-02', '%Y-%m');

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-- Conexão com Banco de dados MySql LocaWeb

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

attach 'host=dbnausa.mysql.dbaas.com.br user=dbnausa password=Lo@jhn3t0@ port=3306 database=dbnausa' as mysql_db (type mysql);

use mysql_db;

select * from TbUsuario;

select * from TbArmario;

select * from TbUnidade;

SHOW ALL TABLES;

-- drop table mysqldb.TbTeste;

create table mysql_db.TbTeste (

CdUsuario INTEGER NOT NULL,

NmUsuario VARCHAR(60) NOT NULL,

CpfCnpjUsuario VARCHAR(14) NOT NULL,

NmLogin VARCHAR(30) NOT NULL,

Email VARCHAR(60) NOT NULL,

Senha VARCHAR(64) NULL,

DtAtualizacao datetime NOT NULL);

select * from mysql_db.TbTeste;

insert into TbTeste values(1,'José Hilário','06478701859','jose.neto','jose.neto@grupoeuro17.com.br', '1717', '2025-05-17');

insert into TbTeste values(2,'Maria Antonia','52214789632','Maria','Maria@grupoeuro17.com.br','1717','2025-05-17');

insert into TbTeste values(3,'Oswaldo de Lima','88744105690','Oswaldo.vital','Oswaldo@grupoeuro17.com.br','1717','2025-05-17');

insert into TbTeste values(4,'Marta Sodre','02344714169','Marta.yahoo','Marta@grupoeuro17.com.br','1717',now());