07 - SQL 2 - Postgresql

Prévia do material em texto

SQL 
Marcelo Gomes de Paula
Tipos de dados
Temos muitos tipos diferentes de dados no postgreSQL, você pode ver todos 
na documentação oficial em 
https://www.postgresql.org/docs/current/datatype.html 
2
https://www.postgresql.org/docs/current/datatype.html
Numéricos
Tipo Descrição
smallint Armazena valores inteiros com sinal, ocupando 2 bytes de tamanho
integer Armazena valores inteiros com sinal, ocupando 4 bytes de tamanho
bigint Armazena valores inteiros com sinal, ocupando 8 bytes de tamanho
decimal armazena valores com muitos dígitos. Geralmente usado para armazenar valores 
financeiros. O desenvolvedor pode especificar a precisão e a escala para esse tipo
numeric um apelido para o tipo de dados decimal
real armazena valores de ponto flutuante com 4 bytes e precisão de 6 dígitos decimais
double precision armazena valores de ponto flutuante com 8 bytes e precisão de 15 dígitos decimais
3
Exemplo - Create table
CREATE TABLE numero
(
 t1 SMALLINT,
 t2 INTEGER,
 t3 BIGINT,
 t4 DECIMAL(5,3),
 t5 NUMERIC(3,2),
 t6 REAL,
 t8 DOUBLE PRECISION
);
4
Exemplo - Insert
INSERT INTO numero
 (t1,t2,t3,t4,t5,t6,t7)
 VALUES (1000, 2000, 3000, 40.123, 5.12, 6000.123, 7000.123 );
5
Integer
INSERT INTO numero (t2) VALUES (-2147483648), (2147483647)
Menor e Maior valor
6
O tipo numeric
t5 numeric(3,2)
● numeric(3,2) indica:
○ 3: Precisão total (dígitos à direita e à esquerda do ponto decimal).
○ 2: Escala (dígitos à direita do ponto decimal).
O 2 vai indicar a quantidade de dígitos e o 2 indicam quantos dígitos após a 
vírgula. 
7
O tipo numeric
INSERT INTO numero
 (t5)
 VALUES
 (-9.99), 
 (9.99);
Menor e 
Maior valores
8
O tipo numeric
INSERT INTO numero
 (t5)
 VALUES
 (1.123),
 (1.125),
 (1.126); 
9
O tipo numeric
INSERT INTO numero
 (t5)
 VALUES
 (TRUNC(1.123, 2)),
 (TRUNC(1.124, 2)),
 (TRUNC(1.125, 2)); 
10
Os tipos seriais
Não são verdadeiramente um tipo, na verdade é uma notação conveniente 
para especificar uma coluna identificadora, semelhante ao AUTO_INCREMENT 
encontrado no MySQL.
11
Exemplo - Serial
CREATE TABLE serial_numero (
 c1 SERIAL,
 c2 INTEGER
);
INSERT INTO serial_numero (c2) VALUES
 (1), (2), (3), (1), (2), (3);
12
Caractere
Tipo Descrição
char armazena strings de tamanho fixo com um tamanho n específico
varchar armazena strings de tamanho variável com com um tamanho n máximo
text armazena strings de tamanho variável sem tamanho máximo
13
Caractere - Exemplo
CREATE TABLE palavras (
 c1 char(5),
 c2 varchar(5),
 c3 text
);
INSERT INTO palavras (c1, c2, c3) VALUES ('abcde','abcde','abcde...z');
14
Data e tempo
Tipo Descrição
date armazena datas sem informação de time zone (YYYY-MM-DD)
time armazena valores de tempo sem informação de time zone (HH:MM:SS)
timestamp armazena valores de tempo e de data sem informações de time zone
timestamptz armazena valores de tempo e de data com informação de time zone
interval um intervalo de tempo, como a diferença de dois timestamps
15
Data e tempo - Exemplo
CREATE TABLE data_hora (
 c1 date,
 c2 time,
 c3 timestamp,
 c4 timestamptz,
 c5 interval
);
16
Data e tempo - Exemplo
INSERT INTO data_hora (c1, c2, c3, c4, c5)
VALUES (
 '2024-04-08',
 '19:25:00',
 '2024-04-08 19:25:00',
 '2024-04-08 19:25:00-03:00',
 '1 DAY 2 HOURS 3 MINUTES 4 SECONDS'
);
17
Data e tempo - Exemplo
INSERT INTO data_hora (c1, c2, c3, c4)
VALUES (
 CURRENT_DATE,
 CURRENT_TIME,
 CURRENT_TIMESTAMP,
 CURRENT_TIMESTAMP AT TIME ZONE 'America/Sao_Paulo'
);
18
Data e tempo - Exemplo
CREATE TABLE pessoa (
 id SERIAL,
 nome VARCHAR(100),
 dt_nascimento date
);
INSERT INTO pessoa (nome, dt_nascimento)
VALUES
('Maria Oliveira' , '1990-02-02'),
('Pedro Souza', '2000-03-03');
19
Data e tempo - Exemplo
SELECT
 id,
 nome,
 dt_nascimento,
 EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM dt_nascimento) AS idade
 FROM pessoa;
20
Booleano
Tipo Descrição
boolean Um tipo de dados para lidar com verdadeiro e falso
21
Booleano - Exemplo
CREATE TABLE verdade (
 c1 boolean
); 
 
INSERT INTO verdade (c1) VALUES(TRUE);
INSERT INTO verdade (c1) VALUES(FALSE);
INSERT INTO verdade (c1) VALUES(true);
22
Check Constraints
Uma restrição de verificação é o tipo mais genérico de restrição. Permite 
especificar que os valores de uma determinada coluna devem corresponder a 
uma expressão booleana (valor-verdade). 
Por exemplo, para permitir apenas notas com valores positivos pode ser 
usado:
23
Check Constraints - Exemplo
CREATE TABLE produto (
 cod_produto SERIAL,
 nome TEXT,
 preco NUMERIC(10,2) CHECK (preco > 0)
); 
 
INSERT INTO produto (nome, preco) VALUES ('batata', 5.22);
24
Check Constraints - Exemplo
CREATE TABLE aluno (
 id_aluno SERIAL,
 nome TEXT,
 nota NUMERIC(4,2),
 CHECK (nota >= 0 AND nota <= 10)
); 
 
INSERT INTO aluno (nome, nota) VALUES ('Ana', 5.5);
INSERT INTO aluno (nome, nota) VALUES ('Bruna', 0);
INSERT INTO aluno (nome, nota) VALUES ('Camila', 10);
25
Check Constraints - Exemplo
CREATE TABLE aluno (
 id_aluno SERIAL,
 nome TEXT,
 nota NUMERIC(4,2),
 sexo VARCHAR(1) CHECK (sexo IN ('M', 'F'))
);
26
Check Constraints Not NULL
CREATE TABLE produto (
 cod_produto SERIAL,
 nome VARCHAR(100) NOT NULL
);
27
Check Constraints UNIQUE
CREATE TABLE produto (
 cod_produto SERIAL,
 nome VARCHAR(100) NOT NULL
);
28
Check Constraints UNIQUE
CREATE TABLE aluno (
 id_aluno SERIAL,
 nome VARCHAR(50) NOT NULL,
 sobrenome VARCHAR(50) NOT NULL,
 nota NUMERIC(4,2),
 CHECK (nota >= 0 AND nota <= 10),
 UNIQUE(nome, sobrenome)
);
29
Chave primária
Uma restrição de chave primária indica que uma coluna, ou grupo de colunas, 
pode ser usado como identificador único para as linhas da tabela. 
Isso requer que os valores sejam únicos e não nulos. 
30
Chave primária - Exemplo
CREATE TABLE produto (
 cod_produto SERIAL PRIMARY KEY,
 nome VARCHAR(100) NOT NULL
);
31
Chave primária - Exemplo
CREATE TABLE aluno (
 nome VARCHAR(50),
 sobrenome VARCHAR(50),
 nota NUMERIC(4,2),
 PRIMARY KEY (nome, sobrenome)
);
32
Chave estrangeira
A restrição de chave estrangeira específica que o valor da coluna (ou grupo de 
colunas) deve corresponder a algum valor existente em uma linha de outra 
tabela. Dizemos que a chave estrangeira mantém a integridade referencial 
entre duas tabelas relacionadas. 
33
Chave estrangeira
CREATE TABLE produto (
 id_produto SERIAL PRIMARY KEY,
 nome VARCHAR(50),
 preco NUMERIC(10,2) CHECK (preco > 0)
);
CREATE TABLE pedido (
 id_pedido SERIAL PRIMARY KEY,
 cod_produto INTEGER REFERENCES produto (id_produto),
 quantidade INTEGER
);
34
Cascade
CREATE TABLE produto (
 id_produto SERIAL PRIMARY KEY,
 nome VARCHAR(50),
 preco NUMERIC(10,2) CHECK (preco > 0)
);
CREATE TABLE pedido (
 id_pedido SERIAL PRIMARY KEY, 
 quantidade INTEGER
);
CREATE TABLE itens_pedido (
 id_produto integer REFERENCES produto ON DELETE RESTRICT,
 id_pedido integer REFERENCES pedido ON DELETE CASCADE,
 quantidade integer,
 PRIMARY KEY (id_produto, id_pedido)
);
35
Valor padrão
CREATE TABLE produto (
 id_produto SERIAL PRIMARY KEY,
 nome VARCHAR(50) DEFAULT 'Não informado',
 preco numeric DEFAULT 9.99,
 dt_cadastro date DEFAULT CURRENT_DATE
);
36
transação
➢ begin - inicia uma nova transação
➢ commit - confirmar a transação
➢ rollback - desfazer as alterações feitas durante uma transação
37
transação - Exemplo
CREATE TABLE aluno (
 id_aluno SERIAL PRIMARY KEY,
 nome VARCHAR(50),
 nota_b1 NUMERIC(4,2),
 nota_b2 NUMERIC(4,2)
);
38
transação - Exemplo
BEGIN;
INSERT INTO aluno (nome, nota_b1, nota_b2)
VALUES
('Anna', 7.5, 8.15),
('Bruna', 5, 3.9),
('Camila', 0, 0);
SELECT * FROM aluno;
COMMIT;
39
transação - Exemplo
BEGIN;
UPDATE aluno SET nota_b1 = 10, nota_b2 = 10;
SELECT* FROM aluno;
ROLLBACK;
BEGIN;
UPDATE aluno SET nota_b1 = 10, nota_b2 = 10 WHERE id_aluno = 3;
SELECT * FROM aluno;
COMMIT;
40
Backup e Restore
pg_dump -h localhost -U postgres ${DATABASE_NAME} > backup.sql
psql -h localhost -U postgres ${DATABASE_NAME} < backup.sql
41
Questões
1 - Sabendo que um int suporta números de -2147483648 a 2147483647, o 
que acontece se um usuário informar um valor fora desta faixa?
42
Questões
DROP TABLE aluno;
 
CREATE TABLE aluno (
 id_aluno SERIAL,
 nome TEXT,
 nota NUMERIC(4,2),
 CHECK (nota >= 0 AND nota <= 10)
); 
 
INSERT INTO aluno (nome, nota) VALUES ('Ana', 5.5);
INSERT INTO aluno (nome, nota) VALUES ('Bruna', 0);
INSERT INTO aluno (nome, nota) VALUES ('Camila', 10);
INSERT INTO aluno (nome, nota) VALUES ('Felipe', 12);
 
SELECT * FROM aluno;
43
2 - Considere a execução do código a seguir, quantos alunos vão estar 
cadastrados no banco?
Questões - Resposta
1 - É apresentado o erro “ERROR: integer out of range SQL state: 22003”
2 - 3 alunos
44
Fontes
https://www.postgresql.org/docs/current/datatype.html 
https://halleyoliv.gitlab.io/pgdocptbr/index.html
https://www.tabnews.com.br/NobreLucas/postgresql-tipos-de-dados
45
https://www.postgresql.org/docs/current/datatype.html
https://halleyoliv.gitlab.io/pgdocptbr/index.html
https://www.tabnews.com.br/NobreLucas/postgresql-tipos-de-dados
docker-compose.yml
version: '3'
 
services:
 db:
image: postgres
restart: always
environment:
 POSTGRES_PASSWORD: senha
ports:
 - "5432:5432"
# volumes:
# - db-data:/var/lib/postgresql/data
 admin:
image: dpage/pgadmin4
restart: always
environment:
 PGADMIN_DEFAULT_EMAIL: admin@example.com
 PGADMIN_DEFAULT_PASSWORD: senha
ports:
 - 8080:80
volumes:
 - admin-data:/var/lib/pgadmin
volumes:
 db-data:
 admin-data:
46
C#
47
48
49
50
51
Busca
using Npgsql;
string connectionString = "Server=localhost;Port=5432;Database=aula;User ID=postgres;Password=dbadmin;";
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT id_aluno,nome,nota_b1,nota_b2 FROM 
aluno", connection);
using (NpgsqlDataReader reader = command.ExecuteReader())
{
 while (reader.Read())
 {
 // Acesse os dados da linha atual
 Console.WriteLine(reader["id_aluno"]);
 Console.WriteLine(reader["nome"]);
 Console.WriteLine(reader["nota_b1"]);
 Console.WriteLine(reader["nota_b2"]);
 }
}
}
52
Insert
using Npgsql;
string connectionString = "Server=localhost;Port=5432;Database=aula;User ID=postgres;Password=dbadmin;";
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO aluno (nome, nota_b1, nota_b2) 
VALUES (@nome, @nota_b1, @nota_b2)", connection);
command.Parameters.AddWithValue("@nome", "Novo Aluno");
command.Parameters.AddWithValue("@nota_b1", 8.5);
command.Parameters.AddWithValue("@nota_b2", 9.0);
command.ExecuteNonQuery();
connection.Close();
}
53
Nunca use string
using Npgsql;
using System.Runtime.CompilerServices;
string connectionString = "Server=localhost;Port=5432;Database=aula;User ID=postgres;Password=dbadmin;";
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Entre com o novo nome: ");
string nome = Console.ReadLine();
string sql = $"INSERT INTO aluno (nome) VALUES ('{nome}')";
Console.WriteLine(sql);
Console.ReadKey();
NpgsqlCommand command = new NpgsqlCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
}
54
Nunca use string
procure pelo nome
novo'); DROP TABLE aluno; SELECT ('a
55
Java
56
57
58
Botão direito do mouse em Libraries, nome 
menu, vá em add Library.
59
Caso não localize, faça o download em 
https://jdbc.postgresql.org/download/
60
https://jdbc.postgresql.org/download/
61
Main
62
consultar
public static void consultar() throws SQLException{
 String connectionString = 
"jdbc:postgresql://localhost:5432/aula?user=postgres&password=dbadmin";
 Connection connection = DriverManager.getConnection(connectionString);
 String sql = "SELECT * FROM aluno";
 Statement statement = connection.createStatement();
 ResultSet resultSet = statement.executeQuery(sql);
 while (resultSet.next()) {
 System.out.println(resultSet.getString("id_aluno"));
 System.out.println(resultSet.getString("nome"));
 System.out.println(resultSet.getDouble("nota_b1"));
 System.out.println(resultSet.getDouble("nota_b2"));
 }
 connection.close(); 
}
63
consultar
64
inserir
public static void inserir() throws SQLException{
 String connectionString = 
"jdbc:postgresql://localhost:5432/aula?user=postgres&password=dbadmin";
 Connection connection = 
DriverManager.getConnection(connectionString);
 String sql = "INSERT INTO aluno (nome, nota_b1, nota_b2) VALUES (?, ?, ?)";
 PreparedStatement preparedStatement = 
connection.prepareStatement(sql);
 preparedStatement.setString(1, "Novo Aluno2");
 preparedStatement.setDouble(2, 8.5);
 preparedStatement.setDouble(3, 9.0);
 preparedStatement.executeUpdate();
 preparedStatement.close();
}
65
inserir
66