Introdução ao Transact-SQL (T-SQL)
Mục lục
Introdução ao Transact SQL (T-SQL)
SQL é uma linguagem comum usada no mundo do banco de dados. A maioria dos produtos RDBMS modernos usa algum tipo de dialeto SQL como sua linguagem de consulta primária. O SQL pode ser usado para criar ou destruir objetos como tabelas em um servidor de banco de dados e para manipular esses objetos, como adicionar, editar, excluir dados ou recuperar dados deles.
Transact-SQL (T-SQL) é uma versão do SQL herdada e desenvolvida pela Microsoft, chamada T-SQL, esta linguagem é implementada para implementar uma forma padronizada de comunicação com bancos de dados.
Transact-SQL é uma linguagem poderosa que fornece recursos como tipos de dados, objetos temporários e procedimentos armazenados estendidos. Cursores roláveis, tratamento condicional, controle de transações, exceções e tratamento de erros também são suportados pelo Transact-SQL.
O T-SQL no SQL Server 2019 melhora o desempenho em relação às versões anteriores, aumentando os recursos e dando suporte a muitos recursos avançados. As melhorias incluem funções escalares, paginação, sequenciamento, alocação de metadados e melhor suporte ao tratamento de erros…
Todos os exemplos abaixo usam o AdventureWorks2019, instale o AdventureWorks2019 na máquina para testar os exemplos.
Considere a instrução Transact-SQL abaixo, que é uma instrução SELECT, usada para recuperar os dados da coluna loginID da tabela Employee com JobTitle como ‘Design Engineer’ na tabela Employee:
USE AdventureWorks2019 SELECT LoginID FROM HumanResources.Employee WHERE JobTitle = 'Design Engineer'
O T-SQL inclui muitos elementos sintáticos usados ou que afetam a maioria das instruções. Esses elementos incluem tipos de dados, predicados, variáveis de função, expressões, controle de fluxo, comentários e separadores de lote.
Lista de tipos de instrução T-SQL
O SQL Server oferece suporte a 3 tipos de instruções T-SQL: DDL, DML e DCL
Linguagem de definição de dados (DDL)
DDL, que faz parte do RDBMS, é usado para definir e gerenciar todas as propriedades dos dados, incluindo layout de linha, definições de coluna, colunas de chave primária, locais de arquivo e armazenamento. Os comandos DDL são usados para construir e modificar a estrutura de tabelas e objetos, como gatilhos de visualizações, procedimentos de armazenamento… Para cada objeto, existem palavras-chave CREATE , ALTER , DROP . Exemplo: CREATE TABLE, ALTER TABLE, DROP TABLE
A maioria das instruções DDL está em conformidade com um padrão, onde object_name é o nome da tabela, visualização, gatilho, procedimento armazenado… da seguinte forma:
- CREATE object_name
- ALTER object_name
- DROP object_name
Linguagem de manipulação de dados (DML)
DML é usado para selecionar (recuperar), inserir (adicionar), atualizar (atualizar) ou excluir (excluir) dados em objetos (objetos) definidos por DDL. Todos os usuários do banco de dados podem usar essas instruções em operações no banco de dados.
Instruções DML que incluem as seguintes palavras-chave:
- SELECIONAR
- INSERIR
- ATUALIZAR
- EXCLUIR
Linguagem de controle de dados (DCL)
Os dados são uma parte importante do banco de dados, portanto, as etapas apropriadas devem ser tomadas para verificar se nenhum usuário inválido tem acesso aos dados. Uma linguagem de controle de dados é usada para controlar permissões em objetos de banco de dados. As permissões são controladas usando instruções GRANT, REVOKE, DENY. As instruções DCL também são usadas para proteger o banco de dados. As três instruções DCL básicas são as seguintes:
- GRANT (atribuir)
- REVOGAR (recordar)
- NEGAR (recusar)
Tipos de dados
Tipo de dados é um atributo que define o tipo de dados ou objetos que podem ser contidos. Os tipos de dados devem ser fornecidos para colunas, parâmetros, variáveis, valores de dados de retorno de função e procedimentos armazenados com retorno. O T-SQL inclui vários tipos de dados, como varchar,text,int… Todos os dados armazenados no SQL Server devem ser compatíveis com um dos tipos de dados básicos.
Os objetos têm tipos de dados:
- Colunas (colunas) representam em tabelas e visualizações
- parâmetros em procedimentos armazenados
- Variáveis (variáveis)
- função que retorna um ou mais valores com um tipo de dado especificado
- Procedimentos armazenados que retornam código do tipo inteiro
O SQL Server oferece suporte a 3 tipos de tipos de dados da seguinte forma:
Tipos de dados definidos pelo sistema
Esse tipo de dados é fornecido pelo SQL Server, mostrado na tabela a seguir:
| Categoria | Tipo de dados | Coluna deste tipo de dados |
| Numeração exata (números exatos/números naturais) | int | Ocupa 4 bytes de memória, usado para armazenar valores inteiros, pode armazenar valores de -2^31(-2.147.483.648) a 2^31-1 (2.147.483.647) |
| smallint | Ocupa 2 bytes de memória, pode armazenar valores inteiros de -32.768 a 32m.767 | |
| minúsculo | Ocupa 1 byte de memória, contém valores de 0 a 255 | |
| bigint | Ocupa 8 bytes de memória. Contém dados de -2^63 a 2^63-1 | |
| numérico | tem uma precisão e escala fixas | |
| dinheiro | ocupa 8 bytes de espaço de memória. Representa valores de dados de moeda entre -2^63/1000 e 2^63-1 | |
| Números Aproximados (números aproximados/reais) | flutuador | Ocupa 8 bytes de memória. Representação numérica após ponto flutuante de -1,79E+308 a 1,79E+38 |
| real | Ocupa 4 bytes de memória. Representa os números após o ponto flutuante de -3,40E+38 a 3,40E+38 | |
| Data e hora | data hora | Representa a data e hora, ocupa 8 bytes na memória (2 vezes 4 bytes – inteiro) |
| smalldatetime | Desempenho de data e hora | |
| Cadeia de caracteres | Caracteres | Armazenar dados de caracteres com comprimento especificado e não Unicode |
| varchar | Armazena dados de caracteres de comprimento variável e não Unicode de até 8.000 caracteres | |
| texto | Armazena dados de caracteres de comprimento variável e não Unicode com um comprimento máximo de 2^31 – 1(2.147.483.647) caracteres | |
| nchar | Armazena caracteres Unicode com um comprimento especificado | |
| nvarchar | Armazena dados de caracteres unicode variáveis. | |
| Outros tipos de dados | carimbo de data/hora | Ocupa 8 bytes de memória. Os valores armazenados podem ser gerados automaticamente, valores numéricos binários exclusivos gerados e armazenados em um banco de dados de simulação de timestamp unix em tempo real. |
| binário(n) | Armazena dados binários de um comprimento especificado, até 8000 bytes. | |
| varbinário(n) | Armazena dados binários com comprimento variável, até 8000 bytes. | |
| imagem | Armazena um valor binário variável com um comprimento máximo de 2^30-1 (1.073.741.823) bytes. | |
| identificador único | Ocupando 16 bytes de memória, gerará automaticamente um valor exclusivo que é identificador global exclusivo (GUID) |
Tipos de dados de alias (tipos de dados de alias)
Esses tipos de dados são baseados nos tipos de dados fornecidos pelo sistema. Um tipo de dados de alias é usado quando várias tabelas armazenam o mesmo tipo de dados em uma coluna e têm características semelhantes, como comprimento, nulidade e tipo de dados. Nesses casos, pode ser criado um tipo de dados de alias que pode ser usado universalmente por todas essas tabelas.
Tipos de dados de alias podem ser criados através da instrução CREATE TYPE. A sintaxe da instrução CREATE TYPE é a seguinte:
CREATE TYPE [schema_name.]type_name FROM base_type [NULL/NOTNULL];Por exemplo:
CREATE TYPE usertype from varchar(20) NOT NULLTipos definidos pelo usuário
Os usuários podem definir seus próprios tipos de dados usando uma linguagem de programação suportada pelo .NET Framework.
Elementos de linguagem Transact-SQL
Os elementos da linguagem Transact-SQL são usados no SQL Server 2019 para trabalhar em dados importados em bancos de dados SQL Server. Os elementos da linguagem Transact-SQL incluem predicados, operadores, variáveis, funções, expressões, controle de fluxo e erros (erro) e transações, comentários e separadores de lote.
Predicados (predicado)
Os predicados podem ser entendidos como uma expressão lógica usada para avaliar se a expressão retorna TRUE, FALSE ou UNKNOWN.
As cláusulas no Transact-SQl são usadas nos seguintes casos:
- Determina se um valor especificado corresponde a qualquer valor em uma subconsulta ou lista
- Especifique um intervalo de valores para verificar
- Usado para combinar caracteres com um padrão especificado
- Pesquise correspondências exatas ou menos exatas com palavras e frases únicas, palavras a uma certa distância umas das outras ou correspondências ponderadas
exemplos de cláusulas de predicados:
| Predicado | Por exemplo |
| IMPRIMIR | SELECT PersonType, Title, FirstName, LastName FROM AdventureWorks2019.Person.Person WHERE PersonType IN (‘EM’,’SC’) |
| ENTRE | SELECT BusinessEntityID, NationalIDNumber, LoginID, JobTitle, HireDate FROM AdventureWorks2019.HumanResources.Employee WHERE HireDate BETWEEN ’01-01-2010′ AND ’01-01-2013′ |
| CURTI | SELECT DepartmentID, Name, GroupName, ModifiedDate FROM AdventureWorks2019.HumanResources.Department WHERE Name LIKE ‘P%’ |
| CONTÉM | SELECT * FROM AdventureWorks2019.Person.Address ONDE CONTÉM (AddressLine1,’Street’) |
Operadores (operadores)
Os operadores são usados para realizar aritmética, comparação, concatenação ou atribuição de valores. Por exemplo, os dados podem ser verificados para verificar se a coluna PAÍS para dados do cliente está preenchida (ou tem um valor NOT NULL). Nas consultas, qualquer pessoa que possa ver os dados na tabela que requer o operador pode realizar operações. As permissões apropriadas são necessárias antes que os dados possam ser alterados com sucesso. O SQL Server tem sete tipos de operadores:
| Operador | Descrição | Exemplo |
| Comparação | Compara um valor com outro valor uma expressão | =,<,>,>=,<=,!=,!> |
| Lógico | Verificando o resultado verdadeiro (lógico) de uma condição | E, OU, NÃO |
| Aritmética | Realizar operações aritméticas, como adição, subtração, multiplicação e divisão | +,-,*,/,% |
| Concatenação | Combine 2 correntes em uma string | + |
| Atribuição | Atribuir um valor a uma variável | = |
A ordem de precedência dos operadores:
| Ordem | Operador |
| primeiro | () Parênteses |
| 2 | *, /, % |
| 3 | + , – |
| 4 | = , < , > , >= , <= , != , !> |
| 5 | NÃO |
| 6 | E |
| 7 | ENTRE, EM , CONTÉM, CURTIR, OU |
| 8 | = |
Exemplo de precedência do operador:
DECLARE @Number int; SET @Number = 2 + 2 * (4 + (5 - 3)) SELECT @NumberO resultado exibirá 14, com a seguinte ordem de execução:
1. 2 + 2*(4+(5-3)) 2. 2 + 2*(4+2) 3. 2 + 2*6 4. 2 + 12 5. 14Funções (funções)
Uma função é um conjunto de instruções, uma instrução T-SQL consiste em um conjunto de funções que são muito úteis para calcular ou trabalhar com dados. No SQL, a função trabalha com dados, agrupa os dados, para retornar o valor solicitado, e então pode usar a cláusula SELECT para obter os dados retornados da expressão
Existem 4 tipos de funções no SQL Server da seguinte forma:
Funções do conjunto de linhas
no transact-SQL, a função rowet é usada para retornar um objeto que pode ser usado no lugar de uma referência de tabela. Por exemplo, OPENDATASOURCE, OPENQUERY, OPENROWSET e OPENXML são funções de conjunto de linhas.
Funções agregadas
O T-SQL fornece um conjunto de funções para suportar o resumo de grandes volumes de dados, por exemplo, SUM, MIN, MAX, AVG, COUNT, COUNTBIG…
Funções de classificação
O processamento de muitas tarefas, como criar matrizes, gerar números ordinais, encontrar classificações, etc., pode ser feito com mais facilidade e rapidez usando as funções de classificação. Por exemplo, RANK,DENSE_RANK,NTILE, ROW_NUMBER são funções de classificação.
Funções escalares
Em funções escalares, a entrada é um valor único e a saída também é um valor único
Algumas funções escalares em SQL:
| Tipo de função | Descrição | Exemplo _ |
| Função de conversão | A função de conversão é usada para converter o valor de um tipo de dados para outro. Além disso, pode ser usado para obter formatos de data especiais. | CONVERTER |
| Função de data e hora | A função datetime é usada para trabalhar com dados de data e hora, útil para calcular o tempo | GETDATE, SYSDATETIME, GETUTCDATE, DATEADD, DATEDIFF, ANO, MÊS, DIA |
| Funções matemáticas | As funções matemáticas realizam operações algébricas em valores numéricos. | ALEATÓRIO, REDONDO, POTÊNCIA, ABS, CELULAR, PISO |
| Funções do sistema | O SQL Server fornece funções do sistema que retornam metadados ou definições de configuração | HOST_ID,HOST_NAME,ISNULL |
| Funções de string | As funções de string são usadas para manipular entradas como char ou nvarchar. A saída pode ser string ou valor numérico | SUBSTRING, LEFT, RIGHT, LEN, DATALENGTH, REPLACE, REPLICATE, UPPER, LOWER, RTRIM, LTRIM |
Existem também algumas outras funções escalares no SQL Server, como funções de cursor, funções lógicas, funções de metadados, funções de segurança…
Variáveis (variáveis)
Uma variável é um objeto que pode armazenar valores de dados. No T-SQL, as variáveis podem ser divididas em variáveis locais e variáveis globais.
No T-SQL, as variáveis locais são criadas e usadas para armazenamento temporário quando as instruções SQL são executadas. Os dados podem ser passados por meio de instruções SQL por meio de variáveis locais. O nome de uma variável local é sempre prefixado com a palavra-chave ‘@’.
Por exemplo:
DECLARE @Search NVARCHAR(30) SET @Search = N'hello'Nas versões anteriores do SQL Server, existia um conceito chamado variáveis globais, que se refere a variáveis internas que são definidas e mantidas pelo sistema. No SQL Server 2019, as substituições são classificadas como funções. Eles são prefixados com dois sinais ‘@’. Os valores de retorno dessas funções podem ser recuperados com uma simples consulta SELECT.
Por exemplo
SELECT @@LANGUAGE as 'Language'Eles retornarão o idioma usado pelo SQL Server
Algumas das funções que podem ser encontradas:
- @@DATEFIRST
- @@LÍNGUA
- @@LOCK_TIMEOUT
- @@MAX_CONNECTIONS
- @@NOME DO SERVIDOR
- @@VERSÃO
Expressão (expressão)
Uma expressão é uma combinação de identificadores, valores e operadores que o SQL Server pode avaliar para obter um resultado. As expressões podem ser usadas em vários lugares diferentes ao acessar ou alterar dados.
Exemplo de uma expressão combinando a cláusula SELECT, obtendo o ano atual e calculando o próximo ano
USE AdventureWorks2019 SELECT SalesOrderID, CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) AS CurrentYear, YEAR(OrderDate) + 1 AS NextYear FROM Sales.SalesOrderHeader
Controle de Fluxo, Erros, Transações
Embora o transact-SQL seja principalmente uma linguagem de acesso a dados, ele oferece suporte ao controle de fluxo para execução e detecção de erros. O controle de fluxo define o fluxo de execução de instruções transact-SQL, blocos de códigos, funções definidas pelo usuário e procedimentos de armazenamento.
Controles comuns de fluxo em T-SQL
| Declaração de Controle de Fluxo | Descrição |
| SE… OUTRA | Controle de ramificação com base na condição lógica |
| ENQUANTO | Repita comandos ou blocos de instruções enquanto a condição de teste for verdadeira |
| COMEÇO… FIM | Definição de escopo de um bloco T-SQL |
| TENTE… PEGUE | Definição de estrutura para tratamento de exceções e erros |
| INICIAR TRANSAÇÃO | Marcar um bloco de instruções como parte de uma transação explícita |
Exemplo de uso de IF ELSE em T-SQL:
IF DATENAME(weekday, GETDATE()) IN (N'Saturday',N'Sunday') SELECT 'It is a Weekend'; ELSE SELECT 'It is Weekday';Comentários
Comentários são sequências de texto descritivo, também conhecidas como comentários, no código do programa que serão ignoradas pelo compilador. Os comentários podem ser inseridos no código-fonte de uma instrução, um bloco de código ou um procedimento de armazenamento. Os comentários explicam o propósito do programa, condições especiais de execução e fornecem informações sobre o histórico de revisões…. Sintaxe:
-- Day la comment inline -- Day la comment inline /*day la comment khoi lenh*/Separadores de Lote
Lote é um conjunto de uma ou mais instruções T-SQL que são enviadas em uma única execução de aplicativo. As instruções em T-SQL em um lote são reempacotadas em uma unidade de execução (uma execução), chamada de plano de execução. O processo de execução dos conjuntos de instruções dentro do lote é chamado de processamento em lote.
Um separador de lote é controlado por ferramentas de cliente do SQL Server, como SSMS, para executar o comando. Por exemplo, você define GO como um separador de lote no SSMS.
Exemplo de separador de lote:
USE AdventureWorks2019 SELECT * FROM HumanResources.Employee GO -- cau lenh tiep theoLógica de conjuntos e predicados (conjuntos e lógica de predicados)
Conjuntos e lógica de predicado são 2 fundamentos matemáticos usados no SQL Server 2019. Ambas as teorias são usadas para consultar dados no SQL Server 2019
Teoria dos conjuntos (teoria dos conjuntos)
A teoria dos conjuntos é uma base matemática usada no modelo de banco de dados relacional. Um conjunto é uma coleção de objetos distintos considerados como um todo. Por exemplo, todos os funcionários em uma tabela Employee podem ser considerados como um conjunto.
| Aplicações da Teoria dos Conjuntos | Aplicativos em consultas do SQL Server |
| Aja em todo o conjunto de uma só vez | Consultar a tabela inteira de uma vez |
| Manipulação e declaração baseada em conjunto | Use propriedades no SQL Server para recuperar dados específicos |
| Os elementos do conjunto devem ser únicos | Definir chave exclusiva para tabela |
| Sem instruções de classificação | Os resultados da consulta não são recuperados em nenhuma ordem |
Um dos operadores de conjunto é o operador INTERSECT. Ele retorna as linhas distintas geradas pelos operadores de consulta de entrada esquerdo e direito.
Exemplo de uso do INTERSECT:
USE AdventureWorks2019 GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder;Lógica de predicados (lógica de predicados)
Veja mais conceito de predicado lógico aqui: https://en.wikipedia.org/wiki/Logic_b%E1%BA%ADc_nh%E1%BA%A5t
A lógica de predicados é uma estrutura matemática que consiste em testes lógicos que fornecem um resultado. O resultado é sempre exibido como verdadeiro ou falso. Em T-SQL, expressões como WHERE e CASE são baseadas em lógica de predicado. A lógica de predicado também é usada em outras situações em T-SQL
Algumas lógicas de predicado no T-SQL são as seguintes:
- impor a confidencialidade dos dados usando uma restrição CHECK
- Controle de fluxo usando a instrução IF .
- Junte-se a tabelas usando o filtro ON .
- Filtre dados em consultas usando as cláusulas WHERE e HAVING .
- Fornece lógica condicional para expressões CASE .
- Definir subconsulta
Ordem lógica dos operadores em uma instrução SELECT .
Junto com a sintaxe dos vários elementos do SQL Server, os usuários do SQL Server também devem saber como toda a consulta é executada. Esse procedimento é um processo lógico que interrompe a consulta e a executa em uma sequência predefinida. A instrução SELECT é uma consulta que será usada para explicar o processo lógico de execução da consulta.
Sintaxe da instrução SELECT:
SELECT <select list> FROM <table source> WHERE <search condition> GROUP BY <group by list> HAVING <search condition> ORDER BY <order by list>Descrição dos elementos na instrução SELECT:
| Elemento | Descrever |
| SELECIONAR <selecionar lista> | Defina as colunas a serem recuperadas |
| DE <fonte da tabela> | Definição da tabela consultada |
| ONDE <condição de pesquisa> | Filtrar linhas por predicado |
| GROUP BY <agrupar por lista> | Classificar linhas por grupo |
| TENDO <condição de pesquisa> | Filtrar grupos por predicado |
| ORDER BY <ordenar por lista> | Classificar saída. |
Considere o exemplo abaixo:
USE AdventureWorks2019 SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM Sales.SalesOrderHeader WHERE CustomerID=30084 GROUP BY SalesPersonID, YEAR(OrderDate) HAVINg COUNT(*) > 1 ORDER BY SalesPersonID,OrderYear;No exemplo acima, a sequência de execução da instrução SELECT é a seguinte:
- A cláusula FROM é avaliada para determinar a tabela de origem a ser consultada
- A cláusula WHERE é avaliada para filtrar as linhas na tabela de origem, que é definida pelo predicado mencionado após a cláusula WHERE.
- Em seguida, a cláusula GROUP BY é avaliada. Esta cláusula classifica os dados filtrados na cláusula WHERE .
- A cláusula HAVING é avaliada
- A cláusula SELECT é executada para especificar quais colunas devem gerar o mesmo resultado da consulta
- Finalmente, a instrução ORDER BY é executada para exibir a saída
USE AdventureWorks2019 -- thu tu thuc thi cua cau lenh select 5.SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM 1.Sales.SalesOrderHeader 2.WHERE CustomerID=30084 3.GROUP BY SalesPersonID, YEAR(OrderDate) 4.HAVINg COUNT(*) > 1 6.ORDER BY SalesPersonID,OrderYear;
Bài viết liên quan:
Dịch vụ thiết kế Wesbite
