Guia de SQL: Criação de Banco de Dados, Tabelas, DML e Consultas

Guia de SQL: Criação de Banco de Dados, Tabelas e Consultas

1) Criação de Banco de Dados

Criar o banco de dados dbfatura:

CREATE DATABASE dbfatura
ON PRIMARY (
    NAME = fatura,
    FILENAME = 'c:\db\fatura.mdf',
    SIZE = 30MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 20%
)
LOG ON (
    NAME = faturalog,
    FILENAME = 'c:\db\fatura.log',
    SIZE = 9MB,
    MAXSIZE = 30MB,
    FILEGROWTH = 20%
)

2) Criação de Tabela

CREATE TABLE tabcli (
    codcli INT,
    nomecli CHAR(30),
    endcli CHAR(30),
    cidcli CHAR(30),
    uf_cli CHAR(2) DEFAULT 'SP',
    cpfcli CHAR(10),
    rg_cli CHAR(10),
    CONSTRAINT pk_tabcli PRIMARY KEY (codcli),
    CONSTRAINT chk_nomecli CHECK (nomecli <> '')
)

Linguagens do SQL:

• DDL (Data Definition Language): definição dos dados.
• DML (Data Manipulation Language): manipulação e controle de dados (inclui, altera e exclui).
• DCL (Data Control Language): controle de dados.
• DQL: consulta.

Na arquitetura IR, a integridade referencial é controlada pelo banco de dados, diferentemente da arquitetura AI, onde o programador deve controlar essa integridade.

3) Manipulação de Dados (DML)

Inclusão de linhas (INSERT):

• INSERT INTO nome_tabela VALUES (valor1, 'texto2', valor3, valor4, ...) — valores para todas as colunas.
• INSERT INTO nome_tabela (nomecol1, nomecol2, nomecol3, ...) VALUES (valor1, 'texto2', valor3, ...) — somente nas colunas definidas; as demais colunas não podem ser chaves e aceitam NULL.
• INSERT INTO nome_tabela SELECT ... FROM ... WHERE ... — inclui as informações resultantes da consulta.

Atualização de linhas (UPDATE):

UPDATE nome_tabela SET nome_col1 = novo_valor ou 'texto', nome_col2 = novo_valor ou 'texto', ... WHERE condição. Se a cláusula WHERE for omitida, todas as linhas serão atualizadas.

Condições:

• Simples: nomedacoluna = | <> | > | < | >= | <= valor ou 'texto'
• Composta: condição AND / OR condição. Mais de uma condição pode ser utilizada.

As condições compostas são baseadas na teoria de conjuntos: AND para interseção (restritivo) e OR para união (abrangente). Pode-se utilizar parênteses ( ) para priorizar a execução, da condição mais interna para a mais externa.

• Operações relacionais: =, <>, >, <, >=, <=
• Operações lógicas: AND, OR, ( )

4) Funções

Conversão: CONVERT(tipo, valor, formato). Ex.: CONVERT(DATETIME, '03/09/2009', 103)

Intervalo: BETWEEN valor_inicial AND valor_final (inclusive os termos). Ex.: BETWEEN 1000 AND 2500

Texto / contexto (LIKE):

• Coringa: %
• Início: LIKE 'texto%'
• Fim: LIKE '%texto'
• Contexto: LIKE '%texto%'

5) Consultas (SELECT)

Sintaxe:

• SELECT o que = colunas, expressões, funções
• FROM de onde = tabela
• WHERE condição que o conteúdo deve satisfazer = linhas
• GROUP BY como agrupar
• HAVING condição sobre o grupo
• ORDER BY ordenação por qual coluna

Expressão: qualquer expressão envolvendo colunas, valores, textos fixos ou funções. Ex.: salfun * 1.1

Funções de texto:

• CAST(col AS VARCHAR) — converte para texto
• SUBSTRING(col, posição_inicial, tamanho) — parte do texto
• UPPER(col) — converte para maiúsculas
• LOWER(col) — converte para minúsculas
• RTRIM(col) — remove espaços à direita
• LTRIM(col) — remove espaços à esquerda
• LEN(col) — tamanho da string

Funções numéricas:

• SQRT(col) — raiz quadrada
• ABS(col) — valor absoluto
• SIN(col) — seno
• CAST(col AS NUMERIC) — converte para número

Funções de data:

• DATEPART(parte, col) — parte especificada da data
• DATEADD(parte, valor, col) — adiciona valor à parte da data
• DATEDIFF(parte, col1, col2) — diferença entre datas na parte indicada
• GETDATE() — data e hora atuais

Partes da data: dd (dia), mm (mês), yy (ano), dw (dia da semana), hh (hora), mi (minuto), ss (segundo), ms (milissegundo), qq (trimestre).

Ordenação: ORDER BY coluna1, coluna2 ... ASC (padrão) / DESC (decrescente).

Funções de agrupamento:

• SUM(col) — soma dos valores
• AVG(col) — média dos valores
• MAX(col) — valor máximo
• MIN(col) — valor mínimo
• COUNT(col) — número de linhas do grupo

O HAVING condiciona agrupamentos. Exemplo: somar as faturas por dia e mostrar apenas os dias em que houve faturamento acima de 50000.

Conceitos importantes:

• Colunas de controle de classificação ou agrupamento devem sempre ser exibidas no SELECT.
• Pode-se exibir valores individuais (linhas) ou agrupados (grupos), não sendo possível misturar ambos na mesma consulta. Também não é possível utilizar uma função de grupo na mesma expressão de uma coluna individual.

Exemplo:

SELECT numfat, datfat, vlrfat FROM fatura WHERE vlrfat > 5000 ORDER BY vlrfat DESC

6) Subconsultas (Subquery / Subselect)

Seguem o mesmo conceito da teoria dos conjuntos, porém obedecem a uma hierarquia de execução. Os dados resultantes da subconsulta são passados como lista para a consulta superior, que exibe o resultado final.

Exemplo:

SELECT coddep, nomdep FROM tabdep WHERE coddep NOT IN (SELECT coddep FROM tabfun)

As subconsultas podem ser utilizadas em diversas situações e, em determinados casos, consomem menos recursos e possuem desempenho superior ao JOIN. Podem utilizar os mesmos operadores relacionais das demais consultas.

Estrutura vertical de execução e reconhecimento de tabelas/colunas.

Exemplo: nome do funcionário que emitiu a maior fatura no primeiro semestre de 2009.

• Nome do funcionário — tabfun
• Maior fatura — tabfat
• Data da fatura — tabfat

7) JOIN — Junção de Tabelas

Sintaxe:

SELECT coluna FROM tabela1 t1 INNER JOIN tabela2 t2 ON t1.coluna = t2.coluna WHERE condição ORDER BY / GROUP BY coluna_de_controle HAVING condição_grupo

• Necessário quando se deseja exibir informações de tabelas distintas simultaneamente.
• Estrutura horizontal de execução e reconhecimento de tabelas/colunas.

Exemplo: descrição dos produtos vendidos no primeiro semestre de 2009. Neste caso, o problema pode ser resolvido tanto por subconsulta quanto por JOIN, devendo-se observar qual abordagem apresenta melhor desempenho para o cenário.