Comandos no SQL Server #1

Definição da Linguagem: Linguage Statmends

A linguagem SQL é dividida em 5 tipos de instruções de linguagem primária: DML, DDL, DCL, TCL

DML: Manipulação de linguagem de Dados, ou seja ão os que vai permitir manipular o banco de dados

DDL: Linguagem de Definição de Dados, são os comandos de criação de alteração de objetos dentro do banco de dados.

DSL: Linguagem de domínio específico, são os comandos para controlarmos os acessos dos usuários a base de dados ou tabela.

TCL: Linguagem de controle de transação, são as instruções que vai controlar as operações no banco de dados.

DML 

Select - recuperar dados 

Insert - inserir dados em uma tabela

Update - atualiza os dados existentes em uma tabela

Delete - exclui registros de uma tabela

DDL

Create - criar objetos no banco de dados, tabelas, indexes, procedures, views, functions,e tiggers

Alter - altera a estrutura da base de dados, tabelas, indexes, procedures, views, functions,e tiggers

Drop - apaga objeto do banco de dados, tabelas, indexes, procedures, views, functions,e tiggers

Truncate - remover todos os registros de uma tabela, tabelas, indexes, procedures, views, functions,e tiggers

DCL

Grante - atribui privilégios de acesso do usuário a objetos do banco de dados

Revoke - remove os privilégios de acesso aos objetos obtidos com o comando GRANT

Deny - nega permissão a um usuário ou grupo para realizar operação em um objeto ou recurso

TCL

Begin Transaction - Inicia uma transação

Commit -  salva o trabalho feito

Save Transaction - identificar um ponto em uma transação para que mais tarde você pode efetuar um ROLLBACK

Rollback - restaurar banco de dados ao original desde o último COMMIT

SELECT p.firstname,

p.lastname, 

e.vacationhours,

e.sickleavehours,

e.vacationhours + e.sickleavehours AS 'Horas Ausente'

FROM humanresources.employee AS e 

 JOIN person.person AS p 

 ON e businessentityid p businnessentityid

ORDER BY 'Horas Ausente' ASC

SELECT (2+5) -- retorna 7

SELECT (10%2) -- retorna 5

-- calcula a diferença de taxas de imposto

SELECT Max(taxrate) - Min(taxrate) AS 'Tax rate Difference' -- seleciona o maior e o menor valor

FROM sales salestaxrate

WHERE stateprovicenid IS NOT NULL

SELECT '1', 1 UNION SELECT 'A', 2 -- seleciona 2 colunas (1:A ; 1:2)

--seleciona tudo de city e ordena crescente por city

SELECT city FROM customers UNION ALL SELECT city

   FROM suppliers ORDER BY city

-- DDL CRIAÇÃO DA TABELA PARA EXERCICIOS

CREATE TABLE FUNCIONARIOS (

 ID int identy (1,1), -- campo do int com identificador (alto numeração)

 NOME VARCHAR(50) NOT NULL

 SALARIO DECIMAL(10,2) -- 2 casas decimais

 SETOR VARCHAR(30)

)

-- DML SELECT, EXEMPLO

SELECT *FROM funcionarios

SELECT nome, setor from funcionarios 

-- DML INSERT

INSERT INTO funcionarios VALUES ('Joao', 1000,''),

 ('Jose', 2000, '')

INSERT INTO funcionarios ('nome',salario), VALUES ('Pedro', 1000)-- insere 1 valor nulo na 3 coluna

--DML UPDATE

UPADATE funcionarios SET salario = '1500' WHERE id = '1' -- Altera o valor alterior para 1500

UPDATE funcionarios SET salario = salario * 1.5 WHERE id = '1' -- Altera e multiplica o salario pelo fator 1.5

--DML DELETE

DELETE funcionarios WHERE id = '1' -- exclui na tabela funcionarios onde 1 seja igual a 1

________________//_______________

Comandos DDL servem para gerar novas tabelas

--CREATE TABLE funcionario (

matricula INT IDENTITY (1,1), --inteiro, começa com 1 e acrescenta 1+1

nome CHAR(50) NOT NULL, -- 50 caracter e não pode ser nulo

data_nasc DATETIME -- só vai aceitar valor de data

)

--DDL GERANDO TABELA COM CHAVE ESTRANGEIRA

CREATE TABLE salario (

matricula INT PRIMARY KEY NOT NULL, --Não pode 2 vezes o mesmo número gravado na tabela

salario DECIMA (10, 2) NOT NULL,

FOREIGN KEY (matricula) REFERENCES funcionario(matricula)

)

--DDL PARA ADICIONAR CHAVE PRIMARIA

ALTER TABLE FUNCIONARIO ADD PRIMARY KEY (MATRICULA) 

--DDL GERANDO INDEX // índice para performação de consulta

CREATE INDEX ix_func1 ON funcionario(data_nasc)

CREATE INDEX ix_func2 ON funcionario(cidade, pais)

--Adicionando novo campo na tabela

ALTER TABLE funcionario  ADD genero  CHAR(1);

 

--Renomeando campo da tabela

EXEC sp_rename 'TABELA_ORIGEM.CAMPO_ORIG', 'Campo_orig', 'COLUMN'

EXEC Sp_rename 'funcionario.endereco', 'ender', 'COLUM' --'tabela que vai alterar . e o campo endereco' , 'para no nome x', 'tipo do objeto'

--Excluindo campo da tabela

ALTER TABLE funcionario DROP COLUMN genero; --exclui o campo genero

--Renomeando Tabela

EXEC sp_rename 'FUNCIONAIRO', 'FUNC'; -- renomeia de FUNCIONARIO para FUNC

--Excluindo tabela

DROP DATABASE CURSO;

--VISUALIZAÇÃO

CREATE VIEW v_funcionario AS SELECT *FROM FUNCIONARIOS

--Excluir um index

DROP index ix_func1 ON FUNCIONARIOS; --excluindo ix_func1 na tabela funcionarios

--Excluindo procedure

DROP PROCEDURE proc_salario;

--Excluindo função

DROP function func_salario;

--Excluindo Trigger

DROP trigger trig_func_salario;

--DDL TRUNCATE //VAI ELIMINAR TODOS OS DADOS DA TABELA, MAS A TABELA CONTINUARÁ DENTRO DO SISTEMA

TRUNCATE TABLE CIDADES; --os comandos anteriores serão eliminados

--Fazendo backup em tabela temporaria

SELECT *INTO #HJTEMP FROM person.Password -- person.password = nome da tabela

--ANALISE DE REGISTROS ANTES DO TRUNCATE

SELECT Count(*) AS AntesTruncateCount FROM person.Password;

go -- fará que ele siga para as instruções seguinte

--DDL TRUNCATE APAGADOS DADOS DA TABELA

TRUNCATE TABLE person.Password;

go

--VERIFICANDO TABELAS APOS TURNCATE

SELECT Count(*) AS DepoisTruncate FROM humanresources.jobcandidate;

______________________________/ /_________________________________

Comandos DCL (LINGUAGEM DE CONTROLE DE DADOS)

--GRANT, REVOKE e DENY: são comandos transact-sql usados para atribuir

--e negar permissões para acessos à objetos e recursos.

--CONCEDER (GRANT) - permite aos usuários ler / escrever em determinados objetos

de banco de daodos

--Cria um login e dá permissões no banco

exec master.dbo.sp_addlogin 'UsrTeste', 'SenhaTeste' --UsrTeste = usuário

--Concedendo Acesso de atualização para UsrTeste

GRANT UPDATE ON FUNCIONARIOS TO UsrTeste; -- Tabela 'FUNCIONARIOS' para o usuário UsrTeste

--Concedendo Acesso para INSERT usuário UsrTeste

GRANT INSERT ON FUNCIONARIOS TO UserTeste;

--Concedendo acesso de leitura para UsrTeste

GRANT SELECT ON FUNCIONARIOS TO UsrTeste;

--Concedendo acesso de DELETE PARA UsrTeste

GRANT DELETE ON FUNCIONARIOS TO UsrTeste;

--Concedendo acesso para EXECUTAR PROC TESTE_PROC para UsrTeste.

GRANT EXECUTE ON testproc TO UsrTeste;

--gerando uma procedure

CREATE PROCEDURE testproc as select *from cidades;

excutando procedure

EXEC testproc -- retorna o conteúdo da tabela cidades

--REVOGAR (REVOKE) - mantém os usuários de permissão de leitura / gravação em

objetos de banco de dados

--NEGAR (DENY) - Nega permissão a um usuário ou grupo para realizar operação

em um objeto ou recurso

--Verificando usuario logado

select CURRENT_USER

--ALTERANDO USUARIO LOGADO

SETUSER 'UsrTeste'

--inserindo dados

INSERT into FUNCIONARIOS values ('Maria', '1000', 'TI')

--UPDATE

UPDATE FUNCIONARIOS set nome='Marisa' where id='7'

--3° PARTE NEGA-DENY

--negando acesso de atualização UsrTeste

DENY UPDATE ON FUNCIONARIOS TO UsrTeste;

--NEGANDO ACESSO DE ATUALIZAÇÃO UsrTeste

DENY INSERT ON FUNCIONARIOS TO UsrTeste;

--NEGANDO ACESSO DE LEITURA UsrTeste

DENY SELECT ON FUNCIONARIOS TO UsrTeste;

--nega acesso a execução de procedure

DENY EXECUTE ON testtproc TO UsrTeste;

--VERIFICANDO USUARIO LOGADO

select CURRENT_USER

--Alterando usuario logado

SETUSER 'UsrTeste'

--EXECUTANDO PROCEDURE COM USUARIO UsrTeste

EXEC testproc

______________________/ / _______________________________

--TCL são usados para gerenciar as mudanças feitas por instruções

DML. Ele permite que as declarações sejam agrupadas em transações lógicas

BEGIN - Abre uma transação

COMMIT - Confirma/Salva uma transação

ROLLBACK = Rerter uma transação em caso de qualquer erro

--INICIA A TRANSAÇÃO

BEGIN TRANSACTION

--RETORNA A TABELA NO ESTADO ANTERIOR DO BEGIN TRANSACTION

ROLLBACK

--EFETIVA AS INFORMAÇÕES NA TABELA DO BD

COMMIT TRANSACTION --salva todas as informações 

--GERA UM PONTO DE RECUPERAÇÃO

SAVE TRANSACTION a3

--Restaura a tabela ate o ponto A2

ROLLBACK TRANSACTION a2

--Somente apresenta os clientes que fizeram compras antes da data atual

SELECT cli_cod

cli_nome

FROM clientes WHERE cli_cod IN (

SELECT cli_cod

FROM pedidos

WHERE ped_data <Getdate()

--Apresenta todos os clientes somente se qualquer cliente já fez algum pedido

SELECT cli_cod

cli_nome

FROM clientes WHERE cli_cod IN (

SELECT cli_cod

FROM pedidos

--Sub select

/*

P.num_ped = num_ped

P.data = data

P.cod_cli = cod_cliente

NOME_CLI */

SELECT P.num_ped, P.data, P.cod_cli,

(SELECT C.NOME_CLI FROM cliente C -- sub select para apresentar o nome do cliente

WHERE P.cod_cli = C.cod_cli) AS --onde o codigo do cliente seja = cod do cliente

NOME_CLI FROM pedidos AS P

--Trazer o total de cada cliente da tabela pedidos

P.cod_cli = cod_cli

NOME_CLI = NOME_CLI

(p.total) total = total

SELECT P.cod_cli,

(SELECT C.NOME_CLI FROM cliente C -- sub select para apresentar o nome do cliente

WHERE P.cod_cli = C.cod_cli) AS --onde o codigo do cliente seja = cod do cliente

NOME_CLI, Sum(p.total) total

FROM pedidos AS P GROUP BY P.cod_cli 

--GERANDO UMA CHAVE PRIMARIA COMPOSTA

ALTER TABLE MATRICULA ADD CONSTRAINT PK_1 PRIMARY KEY (ID_ALUNO, id_disciplina)

--ADICIONANDO CHAVE PRIMARIA TABELA DISCIPLINA

ALTER TABLE DISCIPLINA ADD CONSTRAINT PK_2 PRIMARY KEY(id_disciplina)

--Adicionando chave estrangeira na tabela matricula campo ID_ALUNO

ALTER TABLE MATRICULA ADD CONSTRAINT fk_mat1 FOREIGN KEY

(id_aluno) REFERENCES alunos(id_aluno)

_________________________/ / ____________________

INNER JOIN

--INNER JOIN

SELECT a.nome, c.nome_disc, b.periodo

FROM alunos a 

inner join matricula b

on a.id_aluno=b.id_aluno

inner join disciplina c

on b.id_disciplina=c.id_disciplina

________________________/ / ______________________

AGREGAÇÃO

--AGREGAÇÃO: São funções que executam um cálculo em um conjunto 

de valores e retornam um único valor

--AVG: Retorna a média dos valores em um grupo

--MIN: Retorna o mínimo na expressão

--MAX: Retorna o valor máximo na expressão

--SUM: Retorna a soma de todos os valores ou somente os valores

DISTINCT na expressão

--COUNT: Retorna o número de itens do grupo

--STEDV: Retorna o desvio padrão estatístico para a população de todos

os valores na expressão especificada

--GROUPING: Indica se uma expressão de coluna especificada em uma lista GROUPY BY

é agregada ou não.

--GROUPING_ID: É uma função que calcula o nível de agrupamento

--VAR: Retorna a variância estatistica de todos os valores da expressão especificada

--VARP: Retorna a variância estatística para o preenchimento de todos os valores

 da expressão especificada

--AVG Retorna a média dos valores em um grupo

SELECT AVG(populacao) FROM cidades

--AVG MEDIA POR ESTADO

SELECT UF, UF(POPULACAO) FROM CIDADES 

GROUP BY UF

ORDER BY 2

--AVG POR REGIÃO

SELECT B.regiao, AVG(a.POPULACAO) FROM CIDADES A

INNER JOIN regiao_uf 8

ON A.cod_uf=B.ID

GROUP BY B.regisao

ORDER BY 2 desc

--MIN 

SELECT MIN (populacao) from cidades 

--MIN POR ESTADO

SELECT UF, MIN(POPULACAO) FROM CIDADES

GROUP BY UF ORDER BY 2

--MIN POR REGIÃO

SELECT B.regiao, MIN(POPULACAO) FROM CIDADES A

  INNER JOIN regiao_uf B

  ON A.cod_uf=B.ID

  GROUP BY B.regiao

  ORDER BY 2

--MAX retorna o valor máximo na expressão

SELECT MAX(populacao) from cidadees

--MAX POR ESTADO

SELECT UF, MAX(POPULACAO) FROM CIDADES

GROUP BY UF

ORDER BY 2

SELECT B.regiao, MAX(a.POPULACAO) maximo FROM CIDADES a --a.POPULACAO= campo populacao

 INNER JOIN regiao_uf B

 ON A.cod_uf=B.ID

 GROUP BY B.regiao

 ORDER BY 2

/*As funções de classificação retornam um valor de classificação

para cada linha em uma partição. Dependendo da função usada, para

cada linha em uma partição.*/

SELECT rack() OVER (ORDER BY estado ASC) AS rack_uf,

 estado from uf

SELECT rack() OVER (ORDER BY estado ASC) AS rack_uf,

 regiao,

 estado from regiao_uf

--ROW NUMBER EM SQL

Retona o número sequencial de uma linha em uma partição de um 

conjunto de resultados iniciando em 1 para a primeira linha

de cada partição

SELECT ROW_NUMBER() OVER (ODER BY estado ASC) ROW_NUMBER_uf

regiao, estado, FROM regiao_uf

--FUNÇÕES LÓGICAS

--CHOOSE: Retorna o item ao índice espedifico de uma lista

de valores no SQL Server

SELECT Choose(1, 'Gerentee', 'Diretor', 'Desenvolvedor')AS Escolhido -- 1= posição

SELECT productcategoryid, Choose (productcateroyid, 'A', 'B', 'C') AS

Expressao FROM production.productcategory

SELECT jobtitle, hiredate,

Month(hiredate)mes,

Choose(Month(hiredate), 'Winter','Winter','Spring','Spring',

... ), As Quarter_hired

FROM humanresourses.employee 

--IIF: Retorna um de 2 valores 

DECLARE @a INT = 45,

@b INT = 40;

SELECT IIF (@a > @b, 'TRUE', 'FALSE') AS Resultado;

DECLARE @a INT = 45, @b INT = 40;

SELECT IIF (@a > @b, "Maior', "Menor') AS Resultado;

--FUNÇÕES MATEMÁTICAS

ABS: Uma função matemática que retorna o valor absoluto

SELECT Abs(-1.0),

Abs(0.0),

Abs(1.0),

Abs(-9);

RAND: Retorna um valor float pseudoaleatorio de 0 a 1

SELECT Rand();

SELECT Rand(), Rand(), Rand();

DECLARE @cont smallint;

SET @cont = 1;

WHILE @cont < 5 --enquanto contador for menor que 5

  BEGIN -- então faça

    SELECT RAND() Random_Number

    SET @cont = @cont + 1

  END;

ROUND: Retorna um valor numérico, arredondado.

SELECT Round(123.9994, 3), Round(123.9995);

SELECT Round(123.4545, 2); --precisão de 2 casas decimais

SELECT Round(123.45, -2); 

_____________/ / _____________________

--FUNÇÃO DE LIMITE

--TOP: É útil em grandes tabelas com milhares de registros

SELECT TOP 3 * FROM customers

_______________________ / /___________________________

--SRT: Retorna dados de caractere convertido em dados numéricos

SELECT Str(123.45, 6, 1);

--Conversao de numérico para caractere

SELECT 'Teste' +Str(123.45, 6, 1);

--CONCAT: Retorna uma cadeia de caracteres que retorna da concatenação

de 2 ou + valores

SELECT Concat (CURRENT_USER, 'Seu saldo é R$', 11.00, ' em',

  day(getdate()), '/', month(getdate()), '/', year(getdate())),

  AS resultado

--Substitui a cadeia de caracter cde em abcef...

SELECT REPLACE ('abcdefghicde', 'cde', 'xxx') para

SELECT REPLACE ('Isto é um teste' de,  'teste', 'producao') para;

SELECT REGIAO, REPLACE(REGIAO, 'sul', 'South') FROM regiao_uf

__________________/ / ____________

DATA E HORAS 

SELECT Sysdatetime() exSysdatetime

SELECT Sysdatetimeoffset() exSysdatetimeoffset

SELECT Syssutcdatetime() exSyssutcdatetime

SELECT CURRENT_TIMESTAMP exCURRENT_TIMESTAMP

SELECT Getdate() exGetdate

SELECT Getutcdate() exGetutcdate

--FUNÇÃO DE DATA E HORA PARTES QUE RETORNA O DIA/MES/ANO

SELECT Datename(day, Getdate()) DIA_N

  Datename(month, Getdate()) MES_N,

  Datename(year, Getdate()) ANO_N

--DATEADD: Retorna um novo valor datetime adicioando um intervalo

à datepart especificada da date especificada

SELECT Dateadd(day, 90, Getdate())

SELECT getdate() agora, Dateadd (day, 90, Getdate())-- adcionando 90 dias

SELECT getdate() agora, Dateadd(month, 2, Getdate()) -- adc 2 meses

SELECT getdate() agora, Dateadd(year, 3, Getdate())-- adc 3 anos

--DATEDIF: Retorna o número de limites de datepart de data ou

hora entre duas datas especificadas

--Diferença em dias

SELECT Datediff (day, '2022-03-12', '2017-07-13')

--Diferença em meses

SELECT Datediff (month, '2022-03-12', '2017-07-13')

--Diferença em anos

SELECT Datediff (year, '2022-03-12', '2017-07-13')

--FORMATANDO DATAS

SELECT convert(varchar(10), getdate(), 103)

____________________/ / ___________________________________

--USO DO CASE

DECLARE @data DATETIME

SET @data = getdate()-1

SELECT @data, CASE WHEN @data = Getdate() THEN 'Hoje'

SELECT CASE WHEN @data = Getdate() THEN 'Ontem'

SELECT CASE WHEN @data = Getdate() THEN 'Amanhã'

 END dia

USE AdventureWorks2014 SELECT 

  productnumber, productline,

  Categoria = CASE productline --nome da coluna: Categoria

  WHEN 'R' THEN 'Road'

  WHEN 'M' THEN 'Mountain'

  WHEN 'Not for sale' END, 

  NAME

FROM production.product

ORDER BY productnumber;

SELECT productnumber, NAME,

  CASE WHEN lisprice = 0 THEN 'Não está a venda'

  CASE WHEN lisprice < 50 THEN 'Abaixo de $50'

  CASE WHEN lisprice >= 0 AND listprice < 250 THEN 'Abaixo de $1000'

  ELSE 'Acima de $1000' END Price_Range, 'Produtos' AS Categoria

FROM production.product

ORDER BY productnumber

_________________________/ /_____________________________

--VIEWS: Pode ser definida como uma tabela virtual composta por linhas

e colunas de dados vindos de tabelas relacionadas em uma query

VANTAGENS: 

Reuso: Reutilização das views como objetos de caráter permanente

Segurança: As views permitem que ocultemos determinadas colunas de uma tabela

Simplificação do código: As views nos permitem criar um código de

programação muito mais limpo, na medida em que podem conter um SELECT complexo.

CREATE VIEW v_data_contratacao AS SELECT p.firstaname,

   p.lastname, e.businessentiyid, e.hiredate FROM

   humanresources.employee e JOIN person.person AS p

   ON e.businessentifyid = p.businessentiyid;

CREATE VIEW v_fornec_geral AS SELECT id_fornec, fornec FROM

   fornecedores1 UNION ALL

   SELECT id_fornect, fornec FROM fornecedores2 UNION ALL

_________________________ / / ____________________________________

--TEMP TABLE: Tabelas temporárias são geradas no database TempDB e 

podem ser classificadas como Locais e Globais:

* Tabelas Temporárias são geradas no prefixo "#" e possuem visibilidade para a conexão

responsável por sua criação; outas conexões não "enxergam" a tabela.

* Tabelas Temporárias Globais são geradas com o prefixo '##' e são visíveis por todas as conexões

--OPÇÃO DE DROP

- Uma tabela temporária (Local ou Global) só existe enquanto a conexão responsável

pela sua criação estiver ativa.

- No momento da desconexão, tabelas temporárias remanescentes serão dropadas automaticamente.

--GERANDO TABELA TEMPORÁRIA

CREATE TABLE #minhatemporaria (

  campo1 VARCHAR(80) NOT NULL,

  campo2 VARCHAR(80) NOT NULL)

--inserindo informações na tabela temporária

INSERT INTO #minhatemporaria VALUES ('Real', 1000)

INSERT INTO #minhatemporaria VALUES ('Dolar', 3000)

--Gerando tabela temporária através de um select

SELECT * INTO #minhatemporaria2 FROM #minhatemporaria

--Atualizando a tabela temporaria

UPDATE #minhatemporaria SET campo1= 'Libra' WHERE campo1= 'Real' 

--Deletando registros

DELETE FROM #minhatemporia

--Dropando a tabela temporaria

DROP TABELA #minhatemporaria