Pesquisar neste blog

08/01/2022

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






























Nenhum comentário: