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
