Comandos básicos de SQL

Objetivos

Durante a execução deste tutorial, você deverá se familiarizar:

  • Aprender os comandos básicos de SQL
  • Entender a estrutura das tabelas e suas relações.
  • Trabalhar com bancos de dados em servidores remotos.

Acesso aa linha de comando

Para a realização das etapas abaixo, você deverá acessar a uma interface de linha de comando - CLI. Veja as etapas iniciais do tutorial Instalando o NCBI-Entrez-Direct-E-utilities.

Neste tutorial utilizaremos o MariaDB por conta da sua facilidade. O MySQL não é livre, é proprietário, mas é não é pago em sua versão básica. Os mesmos comandos (ou pequenas variações) podem ser utilizados caso o SGDB seja outro. O aluno que possuir computador com SO Linux ou macOS poderá fazer isto no próprio computador.

Instalando o MariaDB

Para instalar o MariaDB você irá precisar de permissão de administrador em um sistema Linux, MacOs ou Windows (neste último utilizando o WSL). Uma vez em um terminal/CLI, instale o MariaDB com o comando abaixo:

sudo apt install mariadb-client mariadb-server -y

Após a instalação, uma primeira configuração é necessária para iniciarmos o MariaDB. Primeiro, vamos verificar se o MariaDB está funcionando:

sudo systemctl status mariadb

Uma saída semelhante abaixo deverá sair:

mariadb.service - MariaDB 10.6.16 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2024-02-28 07:41:07 -03; 1 day 3h ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 1121 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 1147 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1150 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_>
    Process: 1256 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1258 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 1203 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 7 (limit: 37853)
     Memory: 122.9M
        CPU: 19.197s
     CGroup: /system.slice/mariadb.service
             └─1203 /usr/sbin/mariadbd

Caso a saída seja diferente, use o seguinte comando:

sudo systemctl start mariadb

Na primeira execução vamos realizar as seguintes tarefas: - Criar uma senha de administrador (root). - Criar uma senha de usuário com permissão de administrador.

Primeiro execute o seguinte comando:

sudo mysql_secure_installation

Ele irá pedir a senha atual (caso exista) e você poderá criar uma. O script faz ainda uma série de configurações. Basta você pressionar Y ou N para cada pergunta.

O primeiro acesso será como o usuário root, com o seguinte comando:

sudo mariadb -u root -p

Agora, criaremos um usuário admin_user (você pode usar outro nome de usuário);

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'secret_password';

e depois execute o comando para registrar os novos privilégios:

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost';
FLUSH PRIVILEGES;

Para sair do prompt do MariaDB, execute:

EXIT;

Acessando o MySQL

Para ter acesso aos bancos de dados, o sistema gerenciador de banco de dados (SGBD) deverá ser executado. Para isso, digite na janela de terminal, o seguinte comando:

$ mysql -u admin_user -p

Não esqueça de trocar o nome admin_user pelo nome de usuário que criaste no tópico acima!

A opção -u deve sempre ser acompanhada por um nome de usuário (neste caso admin_user). A opção -p indica que o password deve ser inserido em seguida a execução do comando.

Ao parecer o pedido de Password, coloque a senha dada em sala de aula. Aparecerá agora na janela do terminal o seguinte:

mysql>

Criando um primeiro banco de dados

Vamos usar um exemplo de banco de dados bem simples, o banco SP. Primeiramente baixe o arquivo abaixo, executando o seguinte comando:

wget --no-check-certificate 'https://docs.google.com/uc?export=download&id=1QeVQNPlT9o9Yt_OAZsS-vlVB7BYvpKVW' -O sp.sql

O baixe o arquivo AQUI.

Salve o arquivo sp.sql dentro de uma pasta e execute de dentro desta parte o comando abaixo no terminal:

$ mysql -u admin_user -p

Não esqueça de trocar o nome admin_user pelo nome de usuário que criaste no tópico acima!

Vamos agora criar um banco novo para carregar as informações contidas no arquivo sp.sql:

CREATE DATABASE SP;

Agora saia do MariaDB com o comando (ou apertando Ctrl +D):

EXIT;

Na linha de comando, vá até a pasta onde você baixou o arquivo acima sp.sqle digite:

mysql -u admin_user -p SP < sp.sql

Mais uma vez: Não esqueça de trocar o nome admin_user pelo nome de usuário que criaste no tópico acima!

Se o comando acima for executado sem problemas, nada irá aparecer na tela, ele não tem output. Agora acesse o MariaDB com o comando:

$ mysql -u admin_user -p

Acessando o banco criado

Agora vamos verificar quais bancos estão armazenados neste servidor, utilizando o seguinte comando:

show databases;

Verifique daqui por diante que, enquanto você estiver usando o MySQL, o ; ao final de cada comando é necessário.

Após a entrada do comando, a lista de todos os bancos de dados será retornada, aparecendo algo semelhante ao descrito abaixo (podem não ser os mesmos bancos):

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| SP                 |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

Verifique o número e o nome dos bancos de dados disponíveis. Daqui por diante, utilizaremos o banco SP, que é um banco teste para simples aprendizado da estrutura relacional em SQL. Para escolher este banco utilizaremos o seguinte comando:

use SP;

Trabalhando com o banco escolhido

Continuaremos explorando o banco escolhido acima, com o comando abaixo:

show tables;

A saída será similar a esta:

mysql> show tables;
+------------------+
| Tables_in_SP     |
+------------------+
| P                |
| S                |
| SP               |
+------------------+
3 rows in set (0.00 sec)

O comando show tables; retorna todas as tabelas com dados existentes no banco escolhido. Utilizaremos alguns comandos para acessar e manipular os dados.

Para Listar todo o conteúdo da tabela S:

select * from S;

Utilize o mesmo comando acima para as outras tabelas. Observe as 3 tabelas.

  • Você pode dizer quais são as chaves primárias e/ou as chaves estrangeiras de cada uma delas?

Mais alguns comandos básicos

Teste os seguintes comandos e observe a sua saída:

select nome, cidade from S;
select SC, nome from S where cidade='Londres';

A cláusula distinct é utilizada para eliminar dados duplicados. Veja o resultado dos comandos abaixo, executados em ordem:

select SC from SP;
select distinct SC from SP;

É possível também fazer operações aritméticas em SQL. O peso das peças na tabela P está em libras. Podemos saber o peso em gramas, multiplicamos o valor por 454, utilizando o comando abaixo:

select PC, peso*454 from P;

Podemos também ordenar a saída por ordem crescente ou decrescente:

select PC, cidade, peso*454 from P order by cidade ASC;

Operadores matemáticos, como +, -, *, /, %. podem ser utilizados para buscar resultados mais exatos ou realizar buscas mais complexas.

  • Exemplo prático:
    • Para o envio de uma determinada peça, o peso total do pacote não deve ultrapassar 7 kg, sendo que apenas a embalagem pesa 1,5 kg. Portanto, em quais cidades e quais peças poderiam ser utilizadas?
select PC, cidade from P  where peso*454+1.5 < 7000;

Outras expressões lógicas também podem utilizadas, envolvendo os operadores AND, OR e NOT. As condições simples são definidas com os operadores =,!=,<,<=,>,>=.

Exemplo:

select * from S where Cidade='Londres' OR (Status >= 5 AND Status <= 15);

O comando acima recupera todas as informações dos fornecedores que são de Londres ou que têm status no intervalo fechado [5,15].

O mesmo resultado teria sido recuperado com o comando abaixo:

select * from S where Cidade='Londres' OR (Status between 5 and 15);

Para recuperar o código dos fornecedores que fornecem a peça P2, execute o comando abaixo:

select SC from SP where PC='P2';

Junções entre tabelas e os seus respectivos dados também podem ser realizadas. Observe o comando abaixo:

select * from S, SP where S.SC=SP.SC;

Este comando recupera as tuplas da tabela de fornecimentos combinadas com a tuplas da tabela de fornecedores, quando os códigos de fornecedores são iguais.

select S.SC, Nome, Cidade, from S, SP where S.SC=SP.SC and PC='P2';

Este comando recupera o código, o nome e a cidade dos fornecedores que fornecem a peça 'P2'.

Outros comandos podem ser consultados em livros de MySQL, na internet (Hello Google). Um guia rápido com vários comandos pode ser verificado AQUI.

Exercícios

  • Baseado neste guia rápido elabore um projeto conceitual para um banco de dados que você poderia utilizar para o seu trabalho. Ele pode ser de sequências, de amostras, de espécies, com dados clínicos, resultados experimentais, etc.
  • Depois você irá preparar um projeto lógico, que finaliza em DER (Diagrama Entidade-Relacionamento).
  • Esta sua proposta de banco deverá ter no mínimo 3 tabelas, com 5 linhas de entradas de dados. Devem ser explicitados a chave primária e as chaves estrangeiras. Utilize o exemplo acima e os outros exemplos dados em sala para construir seu diagrama.
  • Já escreva, em documentos de texto, ou em uma planilha, as possíveis entradas das tabelas.
  • Após o preparo do DER, os comandos de montagem e preenchimento do seu banco serão passados. Será que ele funciona?