Utilização de SQL básico

Introdução

Já percorremos um bom caminho até aqui. Instalamos o PostgreSQL em diferentes cenários, vimos algumas ferramentas para utilização no dia a dia, mas você já deve estar um pouco cansado disso e louco para colocar a mão na massa, não é? Então, neste artigo, exploraremos os principais comandos e conceitos envolvidos na administração e manipulação de dados em um ambiente SQL, mostrando comandos que podem ser executados no psql, desde a criação e o gerenciamento de papéis/usuários (roles) até a manipulação dos principais objetos (tabelas, esquemas e bases de dados), com muitos exemplos. A ideia é dar uma visão geral, para você se sentir mais confiante e sair explorando possibilidades para seus próprios projetos independentemente dos próximos artigos na sequência. Boa diversão!

Roteiro:

• CREATE / ALTER / DROP …
  • … ROLE
  • … DATABASE
  • … SCHEMA
  • … TABLE
• INSERT
• SELECT
• UPDATE 
• DELETE

CREATE / ALTER / DROP

Os comandos CREATE, ALTER e DROP são fundamentais para a administração de bancos de dados em SQL, sendo responsáveis pela criação, alteração e exclusão de objetos, respectivamente. Em termos mais formais, esses comandos correspondem a um subconjunto da linguagem SQL conhecida como DDL: Linguagem de definição de dados (do Inglês Data Definition Language).

CREATE: como dito anteriormente, esse comando é utilizado para criar objetos dentro do banco de dados. Isso inclui a criação de tabelas, bancos de dados, esquemas, usuários, papéis (roles), entre outros. 

ALTER: esse comando é utilizado para modificar a estrutura de objetos existentes no banco de dados. Ele permite alterar tabelas, adicionar ou remover colunas, modificar tipos de dados, entre outras operações.

DROP: é utilizado para excluir objetos do banco de dados. Isso pode incluir a exclusão de tabelas, bancos de dados, esquemas, usuários, entre outros. É uma operação irreversível e deve ser usada com cuidado, pois excluir um objeto pode resultar na perda permanente de dados.

Exemplos

Aplicação em ROLE

O comando CREATE ROLE é utilizado para criar usuários dentro do banco de dados. Um grupo de usuários ou role é uma entidade que define um conjunto de permissões e privilégios na utilização desse banco. Essas permissões podem ser atribuídas a usuários específicos ou a grupos, facilitando a gestão de acesso e segurança. Ao criar uma nova role com o comando CREATE ROLE, você pode especificar diversas configurações, como permissões específicas, restrições de acesso, entre outros. Por exemplo:

CREATE ROLE meu_role LOGIN PASSWORD 'senha';

Nesse exemplo dado, foi fornecida permissão para fazer login (LOGIN) com as credenciais fornecidas (nome da role e senha, identificada como PASSWORD), portanto, pode ser considerado uma role de usuário. Caso fosse criado sem permissão de login, seria uma role para controle de privilégios, que poderia ser aplicado em diversos usuários. Além disso, é válido lembrar que também temos o comando CREATE USER para criar usuários, que por debaixo dos panos chama o CREATE ROLE com a permissão de login inclusa, tornando-o, assim, específico para usuários (note que o uso do comando CREATE USER está depreciado, por isso não recomendamos a sua utilização). Existem, ainda, muitas outras permissões possíveis, que podem ser consultadas na página oficial do comando CREATE ROLE. Algumas das principais são:

• SUPERUSER: define se a role terá privilégios de superusuário, que incluem acesso irrestrito a todos os objetos e comandos no banco de dados. É um acesso sensível pelo tanto de privilégios que tem e só deve ser usado em caso de necessidade, por exemplo, para um usuário que fará papel de administrador;
• CREATEDB: define se a role pode criar bases de dados;
• CREATEROLE: define se a role pode criar outras roles.

Para exemplificar esse e outros comandos do SQL, bem como suas permissões e níveis de acesso, criamos as seguintes roles:

• dba: será o administrador do banco de dados, criado como SUPERUSER;
• Aplicação 1:
  • app1_admin: será o administrador da aplicação1, criado com ADMIN;
  • app1_user1: será um usuário representando um módulo da aplicação 1;
  • app1_user2: será outro usuário representando mais um módulo da aplicação 1.
• Aplicação 2:
  • app2_admin: será o administrador da aplicação 1;
  • app2_user1: será um usuário representando um módulo da aplicação 2;
  • app2_user2: será outro usuário representando mais um módulo da aplicação 2.

Perceba que para as roles de administradores, vamos conceder permissões extras além de apenas o login. Os comandos utilizados foram:

CREATE ROLE dba SUPERUSER LOGIN PASSWORD 'senha';
CREATE ROLE app1_admin CREATEDB CREATEROLE LOGIN PASSWORD 'senha';
CREATE ROLE app2_admin CREATEDB CREATEROLE LOGIN PASSWORD 'senha';
CREATE ROLE app1_user1 LOGIN PASSWORD 'senha1';
CREATE ROLE app1_user2 LOGIN PASSWORD 'senha2';
CREATE ROLE app2_user1 LOGIN PASSWORD 'senha1';
CREATE ROLE app2_user2 LOGIN PASSWORD 'senha2';

Agora, para exemplificar o ALTER ROLE, podemos usar o seguinte cenário: suponha que a role app1_user2 foi criado sem permissão de login, e que isso inviabilizaria o uso do banco. Para corrigir, poderíamos rodar:

ALTER ROLE app1_user2 LOGIN;

E isso é válido para qualquer permissão, tanto para acrescentar quanto para removê-la. Claro que, ao se tratar de permissões mais sensíveis, apenas um superusuário pode editar.

Também é possível adicionar/remover um usuário de uma role com o comando ALTER ou adicionar/remover uma role de um grupo de usuários.

Para remover uma role, basta usar o comando DROP ROLE da seguinte forma:

DROP ROLE app_user3;

Aplicação em DATABASE

O comando CREATE DATABASE permite a criação de novos bancos de dados, possibilitando que diferentes conjuntos de dados sejam segregados e gerenciados de forma independente, melhorando a organização e a segurança do sistema.

Um exemplo simples de utilização do CREATE DATABASE é mostrado abaixo:

CREATE DATABASE novo_banco;

O comando CREATE DATABASE também possui diversas opções e configurações que podem ser especificadas durante a criação do banco de dados. Por exemplo, é possível definir a codificação de caracteres, o proprietário do banco de dados, as permissões de acesso e outras configurações relevantes para o ambiente de armazenamento de dados, o que pode ser conferido nesta página. 
Além disso, como citado anteriormente, não é todo usuário que consegue criar um banco novo, é preciso ter permissão para isso, por ser uma operação de maior impacto. 
Para prosseguir com nosso exemplo de banco, vamos criar a base de dados tendo nossos administradores (app1_admin e app2_admin) como proprietários, tudo feito através do usuário dba:

CREATE DATABASE aplicacao1 OWNER app1_admin;
CREATE DATABASE aplicacao2 OWNER app2_admin;

Utilizando o comando ALTER DATABASE, podemos modificar as configurações de um banco de dados existente, como alterar o proprietário:

ALTER DATABASE meu_banco OWNER TO novo_proprietario;

E o DROP DATABASE segue sua mesma função de excluir permanentemente esse objeto:

DROP DATABASE meu_banco;

Aplicação em SCHEMA

O comando CREATE SCHEMA possibilita a criação de espaços de nomes lógicos que contêm objetos relacionados, como tabelas, índices, visualizações e outros schemas. Essa funcionalidade é essencial para organizar e estruturar os objetos dentro de um banco de dados, melhorando a manutenção e a legibilidade do sistema. Um exemplo de utilização seria:

CREATE SCHEMA novo_esquema;

Assim como os outros usos do CREATE, aqui também temos diversas opções e configurações que podem ser especificadas, como definição de proprietário e permissões de acesso para usuários, que podem ser conferidas na página oficial do comando CREATE SCHEMA. Para prosseguir com nosso exemplo principal, vamos criar os schemas aplicacao1 e aplicacao2, pertencentes aos usuário app1_admin e app2_admin respectivamente:

CREATE SCHEMA aplicacao1 AUTHORIZATION app1_admin;
CREATE SCHEMA aplicacao2 AUTHORIZATION app2_admin;

Para modificar configurações de um schema existente, utilizamos o ALTER SCHEMA, conforme exemplo de alteração do proprietário:

ALTER SCHEMA meu_schema OWNER TO novo_proprietario;

E para excluí-lo é só utilizar o DROP SCHEMA:

DROP SCHEMA meu_schema;

Aplicação em TABLE

O comando CREATE TABLE é utilizado para criar novas tabelas em um banco de dados. Como exemplo:

CREATE TABLE aplicacao1.tabela1_app1 (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(150),
    idade VARCHAR(150)
);

Nesse exemplo, estamos criando uma nova tabela chamada tabela_app1, no schema aplicacao1, com três colunas: id, nome e e-mail. A coluna id é definida como uma chave primária usando o tipo de dado SERIAL, que é um tipo autoincrementável, garantindo valores únicos para cada registro. Os campos nome e e-mail são definidos como VARCHAR, que é o tipo de dado ideal para armazenar strings de tamanho variável. Além disso, a tabela foi criada no database aplicacao1, com o usuário dba, e além dela vamos criar mais duas tabelas, totalizando três tabelas em cada database:

CREATE TABLE aplicacao1.tabela2_app1 (
    id SERIAL PRIMARY KEY,
    endereco VARCHAR(150)
);

CREATE TABLE aplicacao1.tabela3_app1 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(150)
);

Replicamos as mesmas tabelas para o schema aplicacao2.

O comando CREATE TABLE, assim como seus similares, também possui diversas opções e configurações que podem ser especificadas durante a criação da tabela. Por exemplo, é possível definir restrições de chave estrangeira, índices, gatilhos e outras propriedades específicas da tabela. Para mais informações é só consultar a página oficial.

Podemos também adicionar/modificar uma tabela existente utilizando o comando ALTER TABLE, por exemplo, para adicionar uma coluna nova:

ALTER TABLE minha_tabela
ADD COLUMN nova_coluna VARCHAR(100);

Finalmente, o comando DROP TABLE é utilizado para excluir uma tabela existente juntamente com todos os seus dados e objetos relacionados:

DROP TABLE minha_tabela;

Antes de executar o comando DROP TABLE, verifique se você realmente deseja excluir a tabela e se não há dados importantes dentro dela. Essa operação é irreversível e não pode ser desfeita.

INSERT

O comando INSERT serve para adicionar novos registros a uma tabela existente. Ele permite inserir dados em uma ou mais colunas específicas de uma tabela, criando, assim, novas entradas de dados que podem ser consultadas, atualizadas ou excluídas posteriormente.

Por exemplo, suponha que temos uma tabela chamada ‘usuários’ com colunas ‘id’, ‘nome’ e ‘e-mail’. Podemos inserir um novo usuário na tabela da seguinte maneira:

INSERT INTO usuarios (nome, email)
VALUES ('João', 'joao@example.com');

Nesse exemplo, estamos inserindo um novo registro na tabela ‘usuários’, fornecendo valores para as colunas ‘nome’ e ‘e-mail’. Se a coluna ‘id’ for uma chave primária autoincrementável, ela será gerada automaticamente pelo sistema.

Além disso, o comando INSERT também permite inserir múltiplas linhas de uma vez, fornecendo uma lista de valores para cada coluna. Vamos exemplificar isso inserindo dados nas nossas tabelas criadas anteriormente (replicando tudo no schema aplicacao2):

-- Inserção de dados na tabela1_app1
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('João', '30');
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('Maria', '25');
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('Pedro', '40');

-- Inserção de dados na tabela2_app1
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Rua A, 123');
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Avenida B, 456');
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Praça C, 789');

-- Inserção de dados na tabela3_app1
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('joao@example.com');
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('maria@example.com');
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('pedro@example.com');

SELECT

O comando SELECT permite recuperar dados de uma ou mais tabelas de um banco de dados. Com o SELECT, é possível realizar consultas complexas para obter exatamente os dados desejados, filtrar resultados, realizar cálculos e até mesmo unir informações de várias fontes diferentes.

A seguir, você pode conferir um exemplo simples de utilização do comando SELECT:

SELECT * FROM minha_tabela;

Nesse exemplo, estamos selecionando todos os registros e todas as colunas da tabela minha_tabela. O * é um operador curinga que significa “todas as colunas”. Se desejar, você também pode categorizar colunas específicas para retornar:

SELECT nome, idade FROM minha_tabela;

Além disso, o SELECT permite realizar filtragens e ordenações nos resultados, utilizando a cláusula WHERE para especificar condições de filtro e a cláusula ORDER BY para ordenar os resultados de acordo com uma ou mais colunas:

SELECT * FROM minha_tabela WHERE idade > 18 ORDER BY nome;

Com esse comando, estamos selecionando todos os registros da tabela minha_tabela, em que a idade é maior que 18, e ordenando os resultados pelo nome.

O comando SELECT também suporta funções de agregação, como SUM, COUNT, AVG, entre outras, que podem ser usadas para realizar cálculos sobre os dados selecionados:

SELECT COUNT(*) FROM minha_tabela;

Esse comando retorna o número total de registros na tabela minha_tabela.

UPDATE

O comando UPDATE é utilizado no PostgreSQL para modificar os dados existentes em uma ou mais linhas de uma tabela. Ele oferece uma maneira eficiente de realizar alterações nos registros já existentes, permitindo atualizar valores de colunas de acordo com condições específicas. Confira a seguir um exemplo simples de utilização do comando UPDATE:

UPDATE minha_tabela
SET coluna1 = novo_valor
WHERE condicao;

Nesse exemplo, estamos atualizando os valores da coluna coluna1 na tabela minha_tabela, definindo-os como novo_valor, apenas para as linhas que atendem à condição especificada após a cláusula WHERE.

Por exemplo, para aumentar o salário de todos os funcionários com mais de 10 anos de serviço, podemos fazer o seguinte:

UPDATE funcionarios
SET salario = salario * 1.1
WHERE anos_de_servico > 10;

Com esse comando, estamos multiplicando o salário de todos os funcionários com mais de 10 anos de serviço por 1.1, aumentando-o em 10%.

Além disso, o UPDATE permite atualizar múltiplas colunas em uma única consulta e utilizar subconsultas para determinar os novos valores a serem atribuídos. Por exemplo, vamos supor que temos uma tabela chamada ‘tarefas’ com as colunas ‘id’, ‘descricao’ e ‘status’. Queremos atualizar todas as tarefas que estão com o status ‘Atrasada’ para o status ‘Em Andamento’:

UPDATE tarefas
SET status = 'Em Andamento'
WHERE status = 'Atrasada';

Com esse comando, estamos atualizando o status de todas as tarefas que estão como ‘Atrasada’ para ‘Em Andamento’.

DELETE

O comando DELETE é usado para remover registros de uma tabela. Ele permite remover uma ou mais linhas de uma tabela, de acordo com as condições especificadas, proporcionando uma maneira eficaz de limpar ou alterar os dados em um banco de dados. Exemplo:

DELETE FROM minha_tabela
WHERE condicao;

Nesse exemplo, estamos removendo registros da tabela minha_tabela que atendem à condição especificada após a cláusula WHERE. Isso significa que apenas as linhas que atendem a essa condição serão excluídas da tabela. Por exemplo, para remover todas as tarefas que estão concluídas e foram marcadas como ‘finalizadas’, podemos fazer o seguinte:

DELETE FROM tarefas
WHERE status = 'finalizadas';

Com esse comando, estamos excluindo todos os registros da tabela ‘tarefas’ que têm o status ‘finalizadas’.

O comando DELETE também pode ser usado sem uma cláusula WHERE para excluir todos os registros de uma tabela:

DELETE FROM minha_tabela;

Esse comando remove todos os registros da tabela minha_tabela, tornando-a vazia. Existe, ainda, uma outra forma mais rápida e eficiente de excluir todos os registros de uma tabela, utilizando o comando TRUNCATE.

É importante ter cuidado ao usar o comando DELETE, pois ele remove os dados de forma permanente e irreversível. Certifique-se sempre de ter feito backup dos dados antes de executar operações de exclusão em larga escala ou com condições complexas.

Conclusão

Ok, agora você já viu os comandos básicos para criar seus objetos (com os comandos CREATE / ALTER / DROP), manipular seus dados (com os comandos INSERT, UPDATE e DELETE) e, por fim, como extrair os dados com o comando SELECT. 

Em tese, isso é tudo o que você precisa para começar a colocar suas ideias em prática. Com o tempo, veremos que existem inúmeras opções e alternativas para cada um dos comandos apresentados aqui. Nós iremos revisitar cada um desses comandos com mais calma e atenção mais para a frente. No entanto, recomendamos que você não espere tanto assim. Tente, erre, comece novamente. Nada pode substituir a sua experiência pessoal por mais exemplos e descrições detalhadas que possamos dar aqui. Portanto, tire um tempo para testar, brincar e, se bobear, até se divertir. Só lembre-se de fazer isso longe dos bancos de dados de produção!