Pular para o conteúdo

Linguagem SQL (I)

CREATE


Comando utilizado para criar os principais objetos em um banco de dados.
Neste tópico vamos trabalhar com as diversas variações do comando CREATE relacionados ao início dos trabalhos com criação das entidades no banco de dados.
O Primeiro comando é o CREATE DATABASE, que cria o Banco de dados e suas dependências, como arquivos e metadados dentro do sistema. Vale lembrar que alguns sistemas gerenciadores de bancos de dados podem implementar maneiras diferentes de tratar os bancos de dados ou espaços de trabalho de cada usuário ou sistema.
No nosso banco de dados de Exemplo temos a criação básica de um banco de dados e a criação de uma tabela chamada Clientes. Depois usamos o comando use para posicionar a execução dos comandos no banco de dados MinhaCaixa.

CREATE DATABASE MinhaCaixa;

USE MinhaCaixa;

CREATE TABLE Clientes (
  ClienteCodigo int,
  ClienteNome varchar(20)
);

CONSTRAINT

Podemos ter variações do comando CREATE TABLE de acordo com a necessidade. Abaixo temos diversas implementações do comando CREATE e suas CONSTRAINT´s.
CONSTRAINT PRIMARY KEY & IDENTITY
Nesse exemplo adicionamos uma chave primária ao campo ClienteCodigo e configuramos a propriedade IDENTITY que vai gerar um número com incremento de (um) a cada inserção na tabela Clientes. Você pode personalizar o incremento de acordo com sua necessidade, neste exemplo temos (1,1) iniciando em um e incrementando um. No segundo exemplo adicionamos uma chave primária composta.

CREATE TABLE Clientes (
  ClienteCodigo int IDENTITY (1,1) CONSTRAINT PK_Cliente PRIMARY KEY,
  ...
);

CREATE TABLE Clientes (
  ClienteCodigo int IDENTITY (1,1) ,
  ClienteCPF(11)
      CONSTRAINT PK_Cliente PRIMARY KEY (ClienteCodigo,ClienteCPF)
);

CONSTRAINT FOREIGN KEY
Neste exemplo temos a criação da FOREIGN KEY dentro do bloco de comando CREATE. Se tratando de uma chave estrangeira temos que tomar o cuidado de referenciar tabelas que já existem para evitar erros. Repare que no comando abaixo estamos criando uma tabela nova chamada Contas e especificando que o código de cliente deverá estar cadastrado na tabela de Cliente, portanto deve existir antes uma tabela Cliente que será referenciada nessa chave estrangeira FOREIGN KEY. Repare que sempre damos um nome para a CONSTRAINT, isso é uma boa prática, para evitar que o sistema dê nomes automáticos.

CREATE TABLE Contas
  (
  AgenciaCodigo int,
  ContaNumero VARCHAR (10) CONSTRAINT PK_CONTA PRIMARY KEY,
  ClienteCodigo int,
  ContaSaldo MONEY,
  ContaAbertura datetime
  CONSTRAINT FK_CLIENTES_CONTAS FOREIGN KEY (ClienteCodigo) REFERENCES Clientes(ClienteCodigo)
  );

ALTER

ALTER TABLE ADD COLUMN

ALTER TABLE Pessoas ADD PessoaSexo CHAR(2);

ALTER TABLE ADD CONSTRAINT
Também podemos adicionar CONSTRAINT´s através do comando ALTER TABLE … ADD CONSTRAINT. Geralmente após criar todas as entidades podemos então criar as restrições entre elas.

ALTER TABLE Contas ADD CONSTRAINT FK_CLIENTES_CONTAS FOREIGN KEY (ClienteCodigo)
REFERENCES Clientes(ClienteCodigo);

CONSTRAINT´s de domínio. Abaixo a mensagem de tentativa de violação da CONSTRAINT acima.Apenas checando uma condição, data de nascimento menor que data atual. No SQL Server para pegarmos a data atual usamos GETDATE():

ALTER TABLE Clientes ADD CONSTRAINT chk_cliente_saldo CHECK ([ClienteNascimento] < GETDATE() AND ClienteNome <> 'Sara');

The INSERT statement conflicted with the CHECK constraint "chk_cliente_saldo". The conflict occurred in database "MinhaCaixa", table "dbo.Clientes".

ALTER TABLE Clientes ADD CONSTRAINT TESTE CHECK ([ClienteNascimento] < GETDATE());

INSERT

  • Comando utilizando para popular as tabelas no banco.Comment

Comment

O comando INSERT também possui algumas variações que devem ser respeitadas para evitar problemas. O primeiro exemplo abaixo mostra a inserção na tabela Clientes. Repare que logo abaixo tem um fragmento da criação da tabela Clientes mostRando que o campo ClienteCodigo é IDENTITY, portanto não deve ser informado no momento do INSERT.

INSERT Clientes (ClienteNome) VALUES ('Nome do Cliente');

CREATE TABLE Clientes
  (
  ClienteCodigo int IDENTITY CONSTRAINT PK_CLIENTES PRIMARY KEY...

Quando vamos fazer o INSERT em uma tabela que não possui o campo IDENTITY passamos o valor desejado, mesmo que o campo seja PRIMARY KEY.

INSERT Clientes (ClienteCodigo, ClienteNome) VALUES (1, 'Nome do Cliente');

CREATE TABLE Clientes
  (
  ClienteCodigo int CONSTRAINT PK_CLIENTES PRIMARY KEY...

INSERT Clientes (colunas) VALUES (valores);

INSERT INTO Clientes SELECT * FROM ...

UPDATE

Comando utilizado para alterar registros em um banco de dados. Antes de executar qualquer comando UPDATE, procure se informar sobre transações (será abordado mais pra frente).
Sempre que for trabalhar com o comando UPDATE ou DELETE, procure executar um SELECT antes para validar se os registros que serão afetados, são exatamente aqueles que você deseja.

UPDATE CartaoCredito SET CartaoLimite = 1000 WHERE ClienteCodigo = 1;

DELETE

Comando utilizado para deletes registros em um banco de dados.Comment

Sempre que for trabalhar com o comando UPDATE ou DELETE, procure executar um SELECTantes para validar se os registros que serão afetados, são exatamente aqueles que você deseja.

DELETE FROM CartaoCredito WHERE ClienteCodigo = 1;

SELECT

Comando utilizado para recuperar as informações armazenadas em um banco de dados.
O comando SELECT é composto dos atributos que desejamos, a ou as tabela(s) que possuem esses atributos e as condições que podem ajudar a filtrar os resultados desejados. Não é uma boa prática usar o * ou star para trazer os registros de uma tabela. Procure especificar somente os campos necessários. Isso ajuda o motor de exceção de consultas a construir bons planos de execução. Se você conhecer a estrutura da tabela e seus índices, procure tirar proveito disso usando campos chaves, ou buscando e filtrando por atributos que fazem parte de chaves e índices no banco de dados.

SELECT * FROM Clientes;

O Comando FROM indica a origem dos dados que queremos.
Na consulta acima indicamos que queremos todas as informações de clientes. É possível especificar mais de uma tabela no comando FROM, porém, se você indicar mais de uma tabela no comando FROM, lembre-se de indicar os campos que fazem o relacionamento entre as tabelas mencionadas na cláusula FROM.
O comando WHERE indica quais as condições necessárias e que devem ser obedecidas para aquela consulta.
Procure usar campos restritivos ou indexados para otimizar sua consulta. Na tabela Clientes temos o código do cliente como chave, isso mostra que ele é um bom campo para ser usado como filtro.

SELECT ClienteNome FROM Clientes WHERE ClienteCodigo=1;

Um comando que pode auxiliar na obtenção de metadados da tabela que você deseja consultar é o comando sp_help. Esse comando mostrar a estrutura da tabela, seus atributos, relacionamentos e o mais importante, se ela possui índice ou não.

sp_help clientes

/Repare que a tabela Clientes possui uma chave no ClienteCodigo, portanto se você fizer alguma busca ou solicitar o campo ClienteCodigo a busca será muito mais rápida. Caso você faça alguma busca por algum campo que não seja chave ou não esteja “indexado” (Veremos índice mais pra frente) a busca vai resultar em uma varredura da tabela, o que não é um bom negócio para o banco de dados.

Para escrever um comando SELECT procuramos mostrar ou buscar apenas os atributos que vamos trabalhar, evitando assim carregar dados desnecessários e que serão descartados na hora da montagem do formulário da aplicação. Também recomendamos o uso do nome da Tabela antes dos campos para evitar erros de ambiguidade que geralmente aparecem quando usamos mais de uma tabela.

SELECT Clientes.ClienteNome FROM Clientes;



O ALIAS ou apelido ajuda na exibição de consultas e tabelas. Dessa forma podemos dar nomes amigáveis para campos e tabelas durante a execução de consultas. Use sempre o AS antes de cada ALIAS, mesmo sabendo que não é obrigatório. Você pode usar o comando AS para dar apelidos aos campos e tabelas para melhorar a visualização e compreensão.

SELECT Clientes.ClienteNome AS Nome FROM Clientes;

SELECT C.ClienteNome FROM Clientes AS C;

SELECT Nome_agencia,C.Numero_conta,saldo AS [Total em Conta],
    Nome_cliente,D.Numero_conta AS 'Conta do Cliente'
  FROM Conta AS C, Depositante AS D
  WHERE C.Numero_conta=D.Numero_conta AND Nome_cliente IN ('Rodrigo','Laura')
  ORDER BY saldo DESC
  • Você pode usar o operador ORDER BY para ordenar os registros da tabela.

Procure identificar os campos da ordenação e verificar se eles possuem alguma ordenação na tabela através de algum índice. As operações de ordenação são muito custosas para o banco de dados. A primeira opção traz os campos ordenados em ordem ascendente ASC, não precisando informar o operador. Caso você deseje uma ordenação descendente você deverá informar o DESC.

SELECT Clientes.ClienteNome FROM Clientes
  ORDER BY Clientes.ClienteNome;

SELECT Clientes.ClienteNome FROM Clientes
  ORDER BY Clientes.ClienteNome DESC;

Outro operador que é muito utilizado em parceria com o ORDER BY é o TOP, que permite limitar o conjunto de linhas retornado. Caso ele não esteja associado com o ORDER BY ele trará um determinado conjunto de dados baseado na ordem em que estão armazenados. Caso você use um operador ORDER BY ele mostrará os TOP maiores ou menores. O Primeiro exemplo mostra as duas maiores contas em relação ao seu saldo. A segunda, as duas menores.

SELECT TOP 2 ContaNumero, ContaSaldo FROM Contas
  ORDER BY ContaSaldo DESC;

SELECT TOP 2 ContaNumero, ContaSaldo FROM Contas
  ORDER BY ContaSaldo;

O comando DISTINCT serve para retirar do retorno da consulta registros repetidos.

SELECT DISTINCT ClienteBairro FROM Clientes;

Podemos usar mais de uma tabela no comando FROM como falamos anteriormente, porém devemos respeitar seus relacionamentos para evitar situações como o exemplo abaixo. Execute o comando e veja o que acontece.

SELECT * FROM Clientes, Contas;


A maneira correta deve levar em consideração que as tabelas que serão usadas tem relação entre si “chaves”, caso não tenham, poderá ser necessário passar por um outra tabela antes. Lembre-se das tabelas associativas.

SELECT CLientes.ClienteNome, Contas.ContaSaldo
  FROM Clientes, Contas
  WHERE Clientes.ClienteCodigo=Contas.ClienteCodigo;

O comando LIKE é usado para encontrar registros usando parte do que sabemos sobre ele. Por exemplo podemos buscar todas as pessoas que tenham nome começado com R, usando um coringa % (Percentual). Podemos fazer diversas combinação com o %.

http://msdn.microsoft.com/en-us/library/ms179859.aspx/

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE 'a%' AND ClienteRua NOT LIKE 'E%';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE '%a%';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE '%a';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua NOT LIKE 'a%';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE '_a';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE 'a_';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua LIKE '_a_';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua NOT LIKE '%a_';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua NOT LIKE '_a%';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua NOT LIKE '_ _ _';

SELECT ClienteRua FROM dbo.Clientes WHERE ClienteRua NOT LIKE '_ _%';

O Comando CASE é utilizado quando queremos fazer validações e até gerar novar colunas durante a execução da consulta. No exemplo abaixo fazemos uma classificação de um cliente com base no seu saldo, gerando assim uma nova coluna Curva Cliente.

SELECT ContaNumero,
  CASE WHEN ContaSaldo < 200 THEN 'Cliente C' WHEN ContaSaldo < 500 THEN 'Cliente B'
  ELSE 'Cliente A' END AS 'Curva Cliente'
  FROM dbo.Contas;

Podemos incluir em nossas consultas diversos operadores condicionais: = (igual), <>(diferente), > (maior), < (menor), <= (menor ou igual), >= (maior ou igual), OR (ou), AND (e) e BETWEEN (entre).

SELECT Nome_agencia, Numero_conta, saldo
  FROM Conta
  WHERE saldo > 500 AND Nome_agencia = 'Joinville';

SELECT AgenciaCodigo FROM dbo.Agencias
  WHERE AgenciaCodigo BETWEEN 1 AND 3;

A SUB CONSULTA, IN e NOT IN são poderosos recursos para auxiliar em buscas e filtragem de registros. Podemos criar subconjuntos de registros e usar operadores como IN para validar se os registros estão dentro daquele subconjunto.

SELECT AgenciaCodigo FROM dbo.Agencias
  WHERE AgenciaCodigo NOT IN ('1','4');

SELECT Contas.ContaNumero, Contas.ContaSaldo, Contas.AgenciaCodigo
  FROM Contas INNER JOIN
    (
    SELECT AgenciaCodigo, MAX(ContaSaldo) AS VALOR
    FROM Contas
    GROUP BY AgenciaCodigo
    ) AS TB2
  ON
  TB2.AgenciaCodigo=Contas.AgenciaCodigo AND TB2.VALOR=Contas.ContaSaldo;

ANY e ALL

Link: https://www.cadcobol.com.br/sql_operators_with_any_and_all_p.htm


SELECT ClienteNome
FROM Clientes
WHERE ClienteCodigo = ANY
  (SELECT ClienteCodigo
  FROM CartaoCredito
  WHERE ClienteCodigo in (50,142,465, 524));

SELECT ClienteNome
FROM Clientes
WHERE ClienteCodigo = ALL
  (SELECT ClienteCodigo
  FROM CartaoCredito
  WHERE ClienteCodigo = 50);


SELECT ALL ClienteNome
FROM Clientes

IF 3 < ALL (SELECT ClienteCodigo FROM Clientes WHERE ClienteCodigo > 5)  
PRINT 'TRUE'   
ELSE  
PRINT 'FALSE' ;

O comando EXISTS é parecido com o comando IN, quando queremos comparar mais de um campo contra uma subconsulta.

SELECT * FROM  Contas C
    WHERE EXISTS
                    (SELECT * FROM  CartaoCredito CC
                            WHERE C.ClienteCodigo=CC.ClienteCodigo
                            AND C.AgenciaCodigo=CC.AgenciaCodigo
                    )

CAST, CONVERT

SELECT Clientes.ClienteNome + Clientes.ClienteCidade FROM Clientes;

SELECT Clientes.ClienteNome + ' ' + Clientes.ClienteCidade FROM Clientes;

SELECT Clientes.ClienteNome + ' de ' + Clientes.ClienteCidade FROM Clientes;

SELECT Clientes.ClienteNome + ' - R$ ' + CAST (Contas.ContaSaldo AS VARCHAR(10) )FROM Clientes INNER JOIN Contas ON Contas.ClienteCodigo = Clientes.ClienteCodigo;

SELECT Clientes.ClienteNome + ' - R$ ' + CONVERT  (VARCHAR(10), Contas.ContaSaldo )FROM Clientes INNER JOIN Contas ON Contas.ClienteCodigo = Clientes.ClienteCodigo;

CONCAT, ISNULL, COALESCE

SELECT CONCAT(ClienteNome, ' ', ClienteSobrenome) 
AS NomeCompleto FROM Clientes

SELECT COALESCE (ClienteNome, ClienteSobrenome) 
AS NomeCompleto FROM Clientes

SELECT ISNULL (ClienteNome, 'NN') 
AS NomeCompleto FROM Clientes

SUBSTRING

SELECT ClienteCPF, SUBSTRING(ClienteCPF,1,3) AS CPF_3_CARACTERES,
SUBSTRING(ClienteCPF,13,2)  AS CPF_2_ULTIMOS
FROM Clientes

PATINDEX

SELECT ClienteEmail, PATINDEX('%@%',ClienteEmail) AS POSICAO
FROM Clientes

SELECT position = PATINDEX('%[^ 0-9A-Za-z]%', 'Por favar encontre a posição do ponto de exclamação!'),
positon_static =  PATINDEX('%!%', 'Por favar encontre a posição do ponto de exclamação!');

Marcações:

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *