Noções básicas de banco de dados relacional

Neste capítulo... | Organizando Informações | O que é um banco de dados | O que é um SGBD | Comparação de modelos de banco de dados | O que é um banco de dados relacional

Noções básicas de SQL

Neste capítulo... | O que é SQL | Equívocos sobre SQL | Uma análise dos diferentes padrões SQL | Introdução aos comandos SQL padrão e palavras reservadas | Representação de números, símbolos, datas, horas e outros tipos de dados | Valores e restrições indefinidos

Componentes SQL

Neste capítulo... | Criação de bases de dados | Processamento de dados | Proteção de banco de dados | SQL é uma linguagem projetada especificamente para criar e manter dados em bancos de dados relacionais. E embora as empresas que fornecem sistemas para gerenciamento desses bancos de dados ofereçam suas próprias implementações de SQL, o desenvolvimento da linguagem em si é determinado e controlado pelo padrão ISO/ANSI.

Criando e mantendo um banco de dados simples

Neste capítulo... | Crie, modifique e exclua uma tabela de um banco de dados usando a ferramenta RAD. | Crie, modifique e exclua uma tabela de um banco de dados usando SQL. | Transferindo um banco de dados para outro SGBD.

Criando um banco de dados relacional multitabelas

Neste capítulo... | O que deve estar no banco de dados | Definindo relacionamentos entre elementos do banco de dados | Vinculando tabelas usando chaves | Projeto de integridade de dados | Normalização de banco de dados | Este capítulo apresentará um exemplo de criação de um banco de dados multitabelas.

Manipulando dados do banco de dados

Neste capítulo... | Trabalhando com dados | Obtenção dos dados necessários da tabela | Exibindo informações selecionadas de uma ou múltiplas tabelas | Atualizando informações em tabelas e visualizações | Adicionando uma nova linha a uma tabela

Definindo Valores

Neste capítulo... | Usando variáveis ​​para reduzir codificação redundante | Recuperando informações solicitadas com frequência localizadas em um campo de tabela de banco de dados | Combinando Valores Simples para Criar Expressões Compostas | Este livro enfatiza continuamente a importância da estrutura de um banco de dados para manter a integridade de um banco de dados.

Expressões complexas com significado

Neste capítulo... | Usando instruções de caso condicionais | Convertendo um item de dados de um tipo de dados para outro | Economize tempo de entrada de dados com expressões com valor de registro | No Capítulo 2, SQL foi chamada de sublinguagem de dados.

"Segmentação" para os dados necessários

Neste capítulo... | Especificando as tabelas necessárias | Separando as linhas necessárias de todas as outras | Criando cláusulas Where eficazes | Como trabalhar com valores nulos | Criando expressões compostas com conectivos lógicos | Agrupando a saída do resultado da consulta por coluna

Operadores Relacionais

Neste capítulo... | Unir tabelas com estrutura semelhante | Mesclando tabelas que possuem estruturas diferentes | Obtendo os dados necessários de várias tabelas | SQL é uma linguagem de consulta usada em bancos de dados relacionais.

Usando consultas aninhadas

Neste capítulo... | Recuperando dados de múltiplas tabelas com uma única instrução SQL | Encontrar itens de dados comparando um valor de uma tabela com um conjunto de valores de outra | Encontrar itens de dados comparando um valor de uma tabela com um único valor selecionado usando uma instrução select de outra

Consultas recursivas

Protegendo o banco de dados

Neste capítulo... | Controlando o acesso às tabelas do banco de dados | Decidir se deve conceder acesso | Concessão de permissões de acesso | Revogando autoridade de acesso | Prevenção de tentativas de acesso não autorizado

Proteção de dados

Neste capítulo... | Como evitar a corrupção do banco de dados | Problemas causados ​​por operações simultâneas | Resolvendo esses problemas usando mecanismos SQL | Configurando o nível de segurança necessário usando o comando set transaction

Usando SQL em aplicativos

Neste capítulo... | SQL na aplicação | Compartilhando SQL com Linguagens Procedurais | Como evitar incompatibilidades | Código SQL incorporado no código processual | Chamando módulos SQL a partir de código processual | Chamando SQL de uma ferramenta RAD | Nos capítulos anteriores, analisamos principalmente os comandos SQL individualmente, ou seja, A tarefa de processamento de dados foi formulada e uma consulta SQL foi criada para ela.

ODBC e JDBC

Neste capítulo... | Definição ODBC | Descrição das peças ODBC | Usando ODBC em um ambiente cliente/servidor | Usando ODBC na Internet | Usando ODBC em Redes Locais | Usando JDBC | Todos os anos, os computadores de uma organização ou de várias organizações diferentes estão cada vez mais conectados entre si. Portanto, há necessidade de estabelecer acesso compartilhado aos bancos de dados na rede.

SQL:2003 e XML

Neste capítulo... | Usando SQL com XML | XML, bancos de dados e Internet | Um dos novos recursos mais significativos do SQL:2003 é o suporte para arquivos XML (Extensible Markup Language), que estão se tornando cada vez mais um padrão universal para troca de dados entre plataformas distintas.

Cursores

Neste capítulo... | Definindo o escopo do cursor na instrução declare | Cursor de abertura | Amostragem de dados linha por linha | Fechando o cursor | O SQL difere da maioria das linguagens de programação mais populares porque executa operações nos dados de todas as linhas de uma tabela simultaneamente, enquanto as linguagens procedurais processam os dados linha por linha.

Hoje, os cursos de SQL “para manequins” estão se tornando cada vez mais populares. Isso pode ser explicado de forma muito simples, porque no mundo moderno é cada vez mais possível encontrar os chamados serviços web “dinâmicos”. Eles se distinguem por um shell bastante flexível e são baseados em Todos os programadores novatos que decidem dedicar sites, antes de mais nada, inscrevem-se em cursos de SQL “para manequins”.

Por que aprender esse idioma?

Em primeiro lugar, o SQL é ensinado para criar ainda mais uma ampla variedade de aplicativos para um dos mecanismos de blog mais populares da atualidade - o WordPress. Depois de completar algumas lições simples, você poderá criar consultas de qualquer complexidade, o que só confirma a simplicidade desta linguagem.

O que é SQL?

Ou uma linguagem de consulta estruturada, criada com um único propósito: determinar, fornecer acesso e processá-los em períodos de tempo bastante curtos. Se você conhece o significado de SQL, entenderá que este servidor é classificado como uma linguagem dita “não processual”. Ou seja, suas capacidades incluem apenas a descrição de quaisquer componentes ou resultados que você deseja ver no futuro no site. Mas quando não indica exatamente quais resultados serão obtidos. Cada nova solicitação nesta linguagem é como uma “superestrutura” adicional. É na ordem em que são inseridas no banco de dados que as consultas serão executadas.

Quais procedimentos podem ser realizados usando esta linguagem?

Apesar de sua simplicidade, o banco de dados SQL permite criar uma ampla variedade de consultas. Então, o que você pode fazer se aprender essa importante linguagem de programação?

• crie uma grande variedade de tabelas;
• receber, armazenar e modificar dados recebidos;
• altere as estruturas das tabelas a seu critério;
• combinar as informações recebidas em blocos únicos;
• calcular os dados recebidos;
• garantir proteção completa das informações.

Quais comandos são os mais populares nesta linguagem?

Se você decidir fazer um curso SQL for Dummies, receberá informações detalhadas sobre os comandos usados ​​na criação de consultas usando-o. Os mais comuns hoje são:

1. DDL é um comando que define dados. É usado para criar, modificar e excluir uma ampla variedade de objetos no banco de dados.
2. DCL é um comando que manipula dados. É utilizado para fornecer a diferentes usuários acesso às informações do banco de dados, bem como para utilizar tabelas ou visualizações.
3. TCL é uma equipe que gerencia uma variedade de transações. Seu principal objetivo é determinar o andamento de uma transação.
4. DML - manipula os dados recebidos. Sua tarefa é permitir ao usuário mover diversas informações do banco de dados ou ali inseri-las.

Tipos de privilégios que existem neste servidor

Privilégios referem-se às ações que um determinado usuário pode realizar de acordo com seu status. O mínimo, claro, é um login normal. É claro que os privilégios podem mudar com o tempo. Os antigos serão excluídos e novos serão adicionados. Hoje, todos aqueles que fazem cursos "para manequins" de SQL Server sabem que existem vários tipos de ações permitidas:

1. Tipo de objeto - o usuário pode executar qualquer comando apenas em relação a um objeto específico localizado no banco de dados. Ao mesmo tempo, os privilégios diferem para objetos diferentes. Eles também estão vinculados não apenas a um usuário específico, mas também a tabelas. Se alguém, usando suas capacidades, criou uma mesa, ele é considerado seu dono. Portanto, ele tem o direito de atribuir novos privilégios a outros usuários relacionados às informações nele contidas.
2. O tipo de sistema é o chamado copyright de dados. Os usuários que receberam tais privilégios podem criar vários objetos no banco de dados.

História do SQL

Esta linguagem foi criada pelo IBM Research Laboratory em 1970. Naquela época seu nome era um pouco diferente (SEQUEL), mas após alguns anos de uso foi alterado, encurtando-o um pouco. Apesar disso, ainda hoje muitos especialistas em programação mundialmente famosos ainda pronunciam o nome à moda antiga. O SQL foi criado com um único objetivo: inventar uma linguagem que fosse tão simples que até mesmo usuários comuns da Internet pudessem aprendê-la sem problemas. Um fato interessante é que naquela época o SQL não era a única linguagem desse tipo. Na Califórnia, outro grupo de especialistas desenvolveu um Ingres semelhante, mas nunca se difundiu. Antes de 1980, havia diversas variações de SQL que eram apenas ligeiramente diferentes umas das outras. Para evitar confusão, foi criada uma versão padrão em 1983, que ainda hoje é popular. Os cursos de SQL "para manequins" permitem que você aprenda muito mais sobre o serviço e estude-o completamente em poucas semanas.

Linguagem de consulta estruturada ou SQLé uma linguagem de programação declarativa para uso em bancos de dados quase relacionais. Muitos dos recursos originais do SQL foram retirados do cálculo de tuplas, mas extensões recentes do SQL incluem cada vez mais álgebra relacional.
SQL foi originalmente criado pela IBM, mas muitos fornecedores desenvolveram seus próprios dialetos. Foi adotado como padrão pelo American National Standards Institute (ANSI) em 1986 e pela ISO em 1987. No padrão da linguagem de programação SQL, ANSI afirmou que a pronúncia oficial de SQL é "es q el". No entanto, muitos especialistas em banco de dados usaram a pronúncia de "gíria" "Sequel", que reflete o nome original da linguagem, Sequel, que foi posteriormente alterado devido a uma marca registrada e conflito de nome com a IBM. Programação para iniciantes.
Linguagem de programação SQL foi revisado em 1992 e esta versão é conhecida como SQL-92. 1999 foi então revisado novamente para se tornar SQL:1999 (AKA SQL3). Programação para manequins. O SQL 1999 oferece suporte a objetos que não eram suportados anteriormente em outras versões, mas no final de 2001, apenas alguns sistemas de gerenciamento de banco de dados suportavam implementações SQL: SQL 1999.
SQL, embora definido como ANSI e ISO, possui muitas variações e extensões, muitas das quais com características próprias, como a implementação "PL/SQL" da Oracle Corporation ou a implementação da Sybase e da Microsoft chamada "Transact-SQL", o que pode ser confuso para aqueles familiarizados com os fundamentos da programação. Também não é incomum que implementações comerciais omitam o suporte aos principais recursos do padrão, como tipos de dados como data e hora, preferindo algumas de suas próprias variantes. Como resultado, ao contrário do ANSI C ou ANSI Fortran, que geralmente podem ser portados de plataforma para plataforma sem grandes mudanças estruturais, as consultas da linguagem de programação SQL raramente podem ser portadas entre diferentes sistemas de banco de dados sem modificações significativas. A maioria das pessoas na indústria de bancos de dados acredita que essa falta de compatibilidade é intencional, a fim de fornecer a cada desenvolvedor seu próprio sistema de gerenciamento de banco de dados e vincular o comprador a um banco de dados específico.
Como o próprio nome sugere, a linguagem de programação SQL foi projetada para propósitos específicos e limitados – consultar dados contidos em um banco de dados relacional. Como tal, é um conjunto de instruções de linguagem de programação para a criação de amostras de dados, em vez de uma linguagem processual como C ou BASIC, que são projetadas para resolver uma gama muito mais ampla de problemas. Extensões de linguagem como "PL/SQL" são projetadas para resolver essa limitação adicionando elementos procedimentais ao SQL, mantendo os benefícios do SQL. Outra abordagem é incorporar comandos de linguagem de programação processual em consultas SQL e interagir com o banco de dados. Por exemplo, Oracle e outros suportam Java no banco de dados, enquanto PostgreSQL permite que funções sejam escritas em Perl, Tcl ou C.
Uma piada sobre SQL: "SQL não é estruturado nem é uma linguagem." O objetivo da piada é que SQL não é uma linguagem de Turing. .

Dada uma tabela T, a consulta Select * from T exibirá todos os elementos de todas as linhas da tabela.
Da mesma tabela, a consulta Selecionar C1 de T exibirá os elementos da coluna C1 de todas as linhas da tabela.
Da mesma tabela, a consulta Select * from T where C1=1 exibirá todos os elementos de todas as linhas onde o valor da coluna C1 é “1”.

Palavras-chave SQL

As palavras SQL são divididas em vários grupos.

O primeiro é Linguagem de manipulação de dados ou DML(linguagem de gerenciamento de dados). DML é um subconjunto da linguagem usada para consultar bancos de dados e adicionar, atualizar e excluir dados.

• SELECT é um dos comandos DML mais comumente usados ​​e permite ao usuário especificar uma consulta como uma descrição definida do resultado desejado. A consulta não especifica como os resultados devem ser organizados - traduzir a consulta em um formato que possa ser executado no banco de dados é tarefa do sistema de banco de dados, mais especificamente do otimizador de consulta.
• INSERT é usado para adicionar linhas (conjunto formal) a uma tabela existente.
• UPDATE é usado para alterar os valores dos dados em uma linha de tabela existente.
• DELETE especifica linhas existentes que serão excluídas da tabela.

Pode-se dizer que três outras palavras-chave se enquadram no grupo DML:

• BEGIN WORK (ou START TRANSACTION, dependendo do dialeto SQL) pode ser usado para marcar o início de uma transação de banco de dados que será concluída ou não será executada.
• COMMIT afirma que todas as alterações de dados feitas após a execução das operações são salvas.
• ROLLBACK especifica que todas as alterações de dados após o último commit ou rollback devem ser destruídas, até o ponto que foi registrado no banco de dados como um “rollback”.

COMMIT e ROLLBACK são usados ​​em áreas como controle e bloqueio de transações. Ambas as instruções completam todas as transações atuais (conjuntos de operações no banco de dados) e removem todos os bloqueios na alteração de dados nas tabelas. A presença ou ausência de uma instrução BEGIN WORK ou similar depende da implementação SQL específica.

O segundo grupo de palavras-chave pertence ao grupo Linguagem de definição de dados ou DDL (linguagem de definição de dados). DDL permite ao usuário definir novas tabelas e seus elementos associados. A maioria dos bancos de dados SQL comerciais tem suas próprias extensões DDL que permitem o controle sobre elementos não padrão, mas geralmente vitais, de um sistema específico.
Os pontos principais do DDL são os comandos de criação e exclusão.

• CREATE especifica os objetos (como tabelas) que serão criados no banco de dados.
• DROP especifica quais objetos existentes no banco de dados serão excluídos, geralmente de forma permanente.
• Alguns sistemas de banco de dados também suportam o comando ALTER, que permite ao usuário modificar um objeto existente de diversas maneiras, como adicionar colunas a uma tabela existente.

O terceiro grupo de palavras-chave SQL é Linguagem de controle de dados ou DCL (linguagem de controle de dados). DCLé responsável pelos direitos de acesso aos dados e permite ao usuário controlar quem tem acesso para visualizar ou manipular os dados no banco de dados. Existem duas palavras-chave principais aqui.

Neste material tentei combinar todos os pontos principais Transact SQL, que ajudará programadores de banco de dados novatos a dominar essa linguagem e a escrever consultas e aplicativos na linguagem Transact-SQL.

A programação de bancos de dados, e mais especificamente, bancos de dados rodando Microsoft SQL Server, se tornou muito popular, por isso resolvi fazer uma espécie de mini livro de referência onde você encontrará todos os pontos principais com explicações e, claro, exemplos.

Para praticar a escrita de consultas SQL ou a criação de objetos de banco de dados, você pode usar a edição gratuita do SQL Server Express; no momento da compilação do livro de referência, a versão mais recente é o Microsoft SQL Server 2014 Express.

Referência Transact-SQL para iniciantes – descrição da referência

Este livro de referência ficará assim, primeiro darei um pequeno índice com navegação, depois começará o próprio livro de referência, haverá comentários, explicações e exemplos para cada item. Além disso, se já examinamos detalhadamente ou usamos em algum lugar dos materiais de nosso site este ou aquele objeto ou ação, é claro que fornecerei links para que você possa ver exemplos detalhados ou como usar esta ou aquela ação na prática .

Como é simplesmente impossível cobrir absolutamente tudo, não se surpreenda se não encontrar algo aqui. Repito mais uma vez que este livro de referência foi criado para programadores Transact-SQL novatos, bem como para administradores simples que precisam baixar periodicamente alguns dados de um servidor SQL.

Para um estudo detalhado da linguagem T-SQL, recomendo a leitura do meu livro “The T-SQL Programmer’s Way. Tutorial sobre a linguagem Transact-SQL”, onde falo sobre a linguagem T-SQL com o máximo de detalhes possível, com um grande número de exemplos.

Base de dados

Mesmo um programador Transact-SQL novato deve saber como criar um banco de dados ou alterar suas propriedades, portanto, antes de examinar tabelas, visualizações, funções e tudo mais, vamos dar uma olhada no processo de criação, modificação e exclusão de um banco de dados Transact-SQL.

Criação

Para criar um banco de dados, você precisa executar a seguinte consulta:

CRIAR teste de BANCO DE DADOS

onde teste é o nome do banco de dados.

Falamos mais sobre como criar um banco de dados em um servidor SQL no material Como criar um banco de dados no MS Sql 2008

Remoção

Se precisar excluir um banco de dados, você pode usar a consulta:

Teste DROP DATABASE

Mudar

Para alterar os parâmetros do banco de dados, você pode usar a interface gráfica do Management Studio, na qual todos os parâmetros são descritos detalhadamente, ou você pode enviar consultas ALTER DATABASE, por exemplo, para habilitar a compactação automática do banco de dados de teste, use a seguinte consulta

Teste ALTER DATABASE SET AUTO_SHRINK ON; --E para desligar o teste ALTER DATABASE SET AUTO_SHRINK OFF;

Espero que esteja claro, ALTER DATABASE é um comando para alterar, test é o nome do banco de dados que está sendo alterado, SET é um comando que indica que iremos alterar os parâmetros do banco de dados, AUTO_SHRINK o próprio parâmetro, ON/OFF o valor do parâmetro.

Tipos de dados

O mais comum e freqüentemente usado

Números exatos

• minúsculoint - 1 byte
• pequeno inteiro - 2 bytes
• interno - 4 bytes
• bigint – 8 bytes
• numérico e decimal ( tipo com precisão e escala fixas)
• dinheiro - 8 bytes
• dinheiro pequeno - 4 bytes

Números aproximados

• float [(n)] – o tamanho depende de n (n pode ser de 1 a 53, padrão 53)
• real - 4 bytes

data e hora

• data – data
• tempo - tempo
• datetime - uma data que inclui a hora do dia com frações de segundo no formato de 24 horas.

Sequências de caracteres

• char [(n)] – uma string de comprimento fixo, onde n é o comprimento da string (de 1 a 8.000). O tamanho do armazenamento é de n bytes.
• varchar [(n | max)] é uma string de comprimento fixo, onde n é o comprimento da string (de 1 a 8.000). Se você especificar max, o tamanho máximo de armazenamento será 2 ^ 31-1 bytes (2 GB) e, se você especificar n, o comprimento real dos dados inseridos mais 2 bytes.
• texto – dados de string de comprimento variável, tamanho máximo 2.147.483.647 bytes (2 GB).
• nchar[(n)] é uma string Unicode de comprimento fixo, onde n é o comprimento da string (de 1 a 4.000). O tamanho do armazenamento é o dobro do valor de n em bytes
• nvarchar [(n | max)] é uma string Unicode de comprimento fixo, onde n é o comprimento da string (de 1 a 4.000). Se você especificar max, o tamanho máximo de armazenamento será 2 ^ 31-1 bytes (2 GB) e, se n, o dobro do comprimento real dos dados inseridos mais 2 bytes.
• ntext são dados de string de comprimento variável, com comprimento máximo de string de 1.073.741.823 bytes.

Dados binários

• binário [(n)] - dados binários com comprimento fixo, tamanho n bytes, onde n é um valor de 1 a 8.000. O tamanho de armazenamento é n bytes.
• varbinary [ (n | max) ] - dados binários com comprimento variável, onde n pode ter um valor de 1 a 8.000. Se você especificar max, o tamanho máximo de armazenamento será 2 ^ 31-1 bytes (2 GB). Quando n é especificado, o tamanho do armazenamento é o comprimento real dos dados de entrada mais 2 bytes.
• imagem - dados binários de comprimento variável, variando em tamanho de 0 a 2 ^ 31 - 1 (2.147.483.647) bytes.

Outro

• xml – armazenamento de dados xml. Discutimos em detalhes no material Transact-sql - trabalhando com xml, e se você não sabe o que é XML, falamos sobre isso no artigo XML Basics for Beginners.
• tabela – armazenamento do conjunto de linhas resultante.

Tabelas

Existem exemplos suficientes de criação de tabelas neste site, já que em quase todos os artigos relacionados a SQL dou um exemplo de criação de tabela de teste, mas para consolidar o conhecimento vamos criar, modificar e deletar uma tabela de teste. Vejamos apenas como os tipos de dados dos campos são definidos nas tabelas Transact-SQL.

Criação

Adicionando uma coluna

• test_table é o nome da tabela;
• adicionar — comando para adicionar;
• prosto_pole – nome da coluna;
• pólo numérico(18, 0) – tipo de dados da nova coluna;
• NULL – um parâmetro que significa que um valor NULL pode ser armazenado neste campo.

Alterando o tipo de dados

Vamos alterar o tipo de dados do novo campo que acabamos de criar (prosto_pole) de numeric(18, 0) para bigint e aumentar o comprimento do campo de comentário para 300 caracteres.

ALTER TABLE test_table ALTER COLUMN prosto_pole bigint; ALTER TABLE test_table ALTER COLUMN comentário varchar(300);

Observação! O servidor SQL não poderá alterar o tipo de dados se os valores destes campos não puderem ser convertidos, neste caso você terá que deletar a coluna com todos os dados e adicionar novamente ou limpar todos os dados deste campo.

Removendo uma coluna

Para excluir uma coluna específica, use o comando drop, por exemplo, para excluir o campo prosto_pole, use a seguinte consulta

ALTER TABLE test_table DROP COLUMN prosto_pole

Excluindo uma tabela

Para excluir uma tabela, vamos escrever esta consulta simples, onde test_table é a tabela a ser excluída

DROP TABLE tabela_teste

Representação

Um objeto muito útil no banco de dados é uma visualização (VIEW) ou, em nossa opinião, simplesmente uma visualização. Se alguém não sabe, uma view é um tipo de consulta armazenada que pode ser acessada da mesma forma que uma tabela. Vamos criar uma visualização baseada na tabela de teste test_table e supor que muitas vezes precisamos escrever uma consulta, por exemplo, de acordo com a condição, a soma é maior que 1000, portanto, para não escrever essa consulta todas as vezes, escreveremos a visão uma vez e posteriormente nos referiremos a ela.

Criação

Exemplo de acesso a uma visualização:

SELECIONE * DE test_view

Mudar

Remoção

Visualizações do sistema

O SGBD MS SQL Server possui objetos de sistema que às vezes podem fornecer informações bastante úteis, por exemplo, visualizações do sistema. Agora analisaremos algumas dessas representações. Você pode acessá-los da mesma forma que as visualizações normais ( por exemplo, selecione * na visualização do título)

• sys.all_objects – contém todos os objetos do banco de dados, incluindo parâmetros como: nome, tipo, data de criação e outros.
• sys.all_columns – retorna todas as colunas das tabelas com suas características detalhadas.
• sys.all_views – Retorna todas as visualizações no banco de dados.
• sys.tables – todas as tabelas do banco de dados.
• sys.triggers – todos os gatilhos do banco de dados.
• sys.databases – todos os bancos de dados no servidor.
• sys.sysprocesses – processos ativos, sessões no banco de dados.

Na verdade, existem muitos deles, então é impossível classificar todos eles. Se você quiser ver como eles podem ser utilizados na prática, já fizemos isso, por exemplo, nos materiais

Funções

O servidor MS SQL permite criar funções que retornarão dados específicos, ou seja, o próprio usuário pode escrever uma função e utilizá-la no futuro, por exemplo, quando for necessário obter valores que requeiram cálculos complexos ou complexos seleção de dados. Às vezes, apenas para reduzir o código, quando uma chamada de função substitui valores frequentemente necessários em diferentes solicitações e aplicativos.

Criação

• CREATE FUNCTION – comando para criar um objeto função;
• test_function – nome da nova função;
• @par1 e @par2 – parâmetros de entrada;
• RETURNS varchar(300) – tipo de retorno;
• DECLARE @rezult varchar(300) – declaração de uma variável do tipo varchar(300);
• A instrução select em nosso caso são as ações da função;
• RETURN @rezult – retorna o resultado;
• BEGIN e END são o início e o fim do código de função, respectivamente.

Um exemplo de uso em uma solicitação:

SELECIONE função_teste(1, 20)

Mudar

Remoção

Funções integradas

Além do fato de o SQL Server permitir a criação de funções personalizadas, ele também oferece a capacidade de usar funções integradas que os desenvolvedores de DBMS já escreveram para você. Existem muitos deles, então dividi os mais comuns em grupos e tentei descrevê-los brevemente.

Funções do sistema

Aqui darei alguns exemplos de funções que retornam vários dados do sistema

• @@VERSION – retorna a versão do SQL Server;
• @@SERVERNAME – retorna o nome do servidor;
• SUSER_NAME() – nome de login do usuário no servidor, ou seja, sob qual login este ou aquele usuário trabalha;
• user_name() – nome de usuário do banco de dados;
• @@SERVICENAME – nome do serviço SGBD;
• @@IDENTITY - o último identificador inserido na tabela;
• db_name() - nome do banco de dados atual;
• db_id() – identificador do banco de dados.

Funções agregadas

Funções que calculam um valor com base em um conjunto (grupo) de valores. Se, ao chamar essas funções, você precisar especificar uma coluna para exibir o resultado, deverá agrupar os dados (agrupar por) por este campo. Discutimos essa construção em detalhes no artigo Transact-SQL agrupando grupo de dados por

• avg – retorna o valor médio;
• contagem – número de valores;
• máx – valor máximo;
• min – valor mínimo;
• soma – soma dos valores.

Exemplo de uso:

SELECT COUNT(*) como contagem, SUM(summa) como soma, MAX(id) como max, MIN(id) como min, AVG(summa) como média FROM test_table

Funções de string

Este tipo de função funciona com strings de acordo.

Esquerda (expressão de cadeia, Personagens) – retorna o número especificado de caracteres em uma string começando da esquerda.

Exemplo

SELECT LEFT("Exemplo da função esquerda", 10) --Resultado de "Exemplo de"

Certo (expressão de cadeia, Personagens) – retorna o número especificado de caracteres em uma string começando da direita

Exemplo

SELECT Right("Exemplo da função Right", 10) -- Resultado da "Função Right"

Len (linha) – retorna o comprimento da string.

Exemplo

SELECT len("Exemplo de como funciona a função len") --Resultado 28

Mais baixo (linha) – retorna uma string na qual todos os caracteres são convertidos em letras minúsculas.

Exemplo

SELECT lower("Exemplo da função inferior") --Resultado de "exemplo da função inferior"

Superior (linha) - retorna uma string na qual todos os caracteres são convertidos em letras maiúsculas.

Exemplo

SELECT Upper("Exemplo da função Upper") --Resultado de "EXEMPLO DA FUNÇÃO UPPER"

Ltrim (linha) – retorna uma string com todos os espaços iniciais removidos.

Exemplo

SELECT ltrim("Exemplo de funcionamento da função ltrim") --Resultado de "Exemplo de funcionamento da função ltrim"

Rtrim (linha) – retorna uma string com todos os espaços do lado direito removidos

Exemplo

SELECT Rtrim ("Exemplo de funcionamento da função Rtrim") -- Resultado de "Exemplo de funcionamento da função Rtrim"

Substituir (linha, O que você está procurando, com o que podemos substituí-lo?) – substitui em uma expressão de string todas as ocorrências especificadas no segundo parâmetro pelos caracteres especificados no terceiro parâmetro.

Exemplo

SELECT Substituir ("Exemplo da função Substituir", "trabalho", "REPLACEMENT") - Resultado de "Exemplo da função REPLACE"

Replicar (linha, número de repetições) – repete a linha (primeiro parâmetro) quantas vezes forem especificadas no segundo parâmetro.

Exemplo

SELECT Replicar ("Exemplo de replicação", 3) - Resultado "Exemplo de replicação Exemplo de replicação Exemplo de replicação"

Reverter (linha) – retorna tudo na ordem inversa.

Exemplo

SELECT Reverse ("Exemplo da função Reverse") - Resultado de "esreveR iitsknuf etobar op remirP"

Espaço (número de espaços) – retorna uma string como um número especificado de espaços.

Exemplo

SELECIONE Espaço(10) -- Resultado " "

Substring (linha, posicão inicial, quantos caracteres) – retorna uma string com comprimento igual ao número especificado no terceiro parâmetro, começando no caractere especificado no segundo parâmetro.

Exemplo

SELECT Substring("Exemplo de operação da função Substring", 11, 14) -- Resultado da "operação da função"

Funções matemáticas

Redondo (número, precisão de arredondamento) – arredonda uma expressão numérica para o número de dígitos especificado no segundo parâmetro

Exemplo

SELECIONE Rodada(10.4569, 2) -- Resultado "10.4600"

Chão (número) – retorna um número inteiro arredondado para baixo.

Exemplo

SELECIONE Piso(10.4569) -- Resultado "10"

Teto (número) – retorna um número inteiro arredondado.

Exemplo

Poder (número, grau) — retorna um número elevado à potência especificada no segundo parâmetro.

Exemplo

SELECIONE Potência (5,2) - Resultado "25"

Quadrado (número) – retorna um valor numérico ao quadrado

Exemplo

SELECIONE Quadrado(5) -- Resultado "25"

Abdômen (número) – retorna um valor positivo absoluto

Exemplo

SELECIONE Abs(-5) -- Resultado "5"

Registro(número) – logaritmo natural com ponto flutuante.

Exemplo

SELECIONE Log(5) -- Resultado "1.6094379124341"

Pi- Pi.

Exemplo

SELECIONE Pi() - Resultado "3.14159265358979"

Rand– retorna um número aleatório de ponto flutuante entre 0 e 1

Exemplo

SELECIONE rand() - Resultado "0,713273187517105"

Funções de data e hora

Obterdata()– retorna a data e hora atuais

Exemplo

SELECIONE Getdate () - Resultado "2014-10-24 16:36:23.683"

Dia(data) – retorna o dia de uma data.

Exemplo

SELECIONE Dia(Getdate()) -- Resultado "24"

Mês(data) – retorna o número do mês de uma data.

Exemplo

SELECIONE Mês (Getdate ()) - Resultado "10"

Ano (data) – retorna o ano a partir de uma data

Exemplo

SELECIONE ano(Getdate()) -- Resultado "2014"

DATAPARTE(seção de data, data) – retorna a seção especificada a partir da data (DD,MM,AAAA, etc.)

Exemplo

SELECT DATEPART(MM,GETDATE()) -- Resultado "10"

Isdate (data) – verifica a expressão inserida para ver se é uma data

Exemplo

SELECT Isdate(GETDATE()) -- Resultado "1"

Funções de conversão

Elenco (expressão como tipo de dados) – uma função para converter um tipo em outro. No exemplo convertemos o tipo float para int

Exemplo

SELECIONE CAST(10.54 como int) --resultado 10

Converter – (tipo de dados, expressão, formato de data) é uma função para converter um tipo de dados em outro. Muitas vezes é usado para converter uma data, usando um terceiro parâmetro opcional - o formato da data.

Exemplo

SELECT GETDATE(), CONVERT(DATE, GETDATE(), 104) --Result --2014-10-24 15:20:45.270 – sem conversão; --2014/10/24 após a conversão.

Funções de tabela

Eles são criados para obter dados deles como em tabelas, mas após vários tipos de cálculos. Falamos detalhadamente sobre funções de tabela no material Transact-sql - Funções de tabela e tabelas temporárias

Criação

Mudar

Remoção

Como você pode ver, para criar, alterar ou excluir tais funções, são usados ​​os mesmos operadores das funções regulares, a única diferença é o tipo que a função retorna.

Um exemplo de chamada desta função

SELECIONE * DE fun_test_tabl(1)

Procedimentos

Procedimentosé um conjunto de instruções SQL que são compiladas uma vez e, assim como as funções, podem receber vários parâmetros. Usado para simplificar cálculos e realizar ações de grupo.

Criação

Mudar

Remoção

Chamando um procedimento

Você pode chamá-lo de diferentes maneiras, por exemplo:

EXECUTE sp_test_procedure 1 --ou EXEC sp_test_procedure 1

Onde EXECUTE e EXEC são uma chamada de procedimento, sp_test_procedure, respectivamente, o nome do nosso procedimento, valor de 1 parâmetro

Procedimentos do sistema

Procedimentos do sistema– são procedimentos para realizar diversas ações administrativas tanto nos objetos do servidor quanto na configuração do próprio servidor. Eles são chamados da mesma forma que os procedimentos regulares, mas no contexto de qualquer banco de dados.

Há um grande número deles, então darei apenas alguns exemplos.

sp_configure– um procedimento para exibir e fazer alterações na configuração do kernel do DBMS. O primeiro parâmetro é o nome do parâmetro de configuração, o segundo parâmetro é o valor.

Exemplo

Altere o valor do parâmetro EXEC sp_configure "Ad Hoc Distributed Queries",1 reconfigure --apply EXEC sp_configure --basta examinar os valores de todos os parâmetros

onde 'Consultas Distribuídas Ad Hoc' é o nome do parâmetro, 1 correspondente ao valor que queremos alterar, a reconfiguração aplicará o valor inserido.

Na prática, utilizamos este procedimento no material Consulta Interdatabase em Transact-SQL

sp_executesql– executa uma instrução Transact-SQL ou um conjunto de instruções que podem ser geradas dinamicamente. Usamos este procedimento no material registrando alterações de dados em uma tabela usando Transact-SQL

Exemplo

EXECUTE sp_executesql N"SELECT * FROM test_table WHERE id = @id", N"@id int", @id = 1

Onde o primeiro parâmetro é a instrução sql (uma string em Unicode), o segundo é a definição de todos os parâmetros incorporados na instrução sql, o terceiro é o valor dos parâmetros.

sp_help– retorna informações detalhadas sobre qualquer objeto de banco de dados.

Exemplo

EXECUTAR sp_help "test_table"

sp_renome– renomeia um objeto no banco de dados. Pode ser usado para renomear tabelas, índices, nomes de colunas em tabelas. Não é recomendado usar este procedimento para renomear procedimentos de usuário, gatilhos e funções.

Exemplo de renomeação de uma tabela

EXEC sp_rename "test_table", "test_table_new"

onde o primeiro parâmetro é um objeto com o nome antigo e o segundo parâmetro é o novo nome do objeto.

Exemplo de renomeação de uma coluna em uma tabela

EXEC sp_rename "test_table.summa", "summa_new", "COLUMN"

O terceiro parâmetro indica que a coluna está sendo renomeada.

Gatilhos

Acionaré um procedimento normal, mas é chamado por um evento, não pelo usuário. Um evento, por exemplo, poderia ser a inserção de uma nova linha em uma tabela (insert), a atualização de dados em uma tabela (update) ou a exclusão de dados de uma tabela (delete).

Criação

Mudar

Remoção

Habilitar desabilitar

Falamos sobre gatilhos no artigo – Como criar um gatilho no Transact-SQL.

Índices

Este é um objeto de banco de dados que melhora o desempenho da recuperação de dados classificando os dados por um campo específico. Se fizermos uma analogia, por exemplo, pesquisar determinadas informações em um livro é muito mais fácil e rápido com base em seu índice do que se esse índice não existisse. Os seguintes tipos de índices existem no MS SQL Server DBMS:

Índice Clusterizado— com tal índice, as linhas da tabela são classificadas com uma determinada chave, ou seja, o campo especificado. Este tipo de índice por tabela no servidor MS SQL só pode ser um e, a partir do MS SQL 2000, é criado automaticamente quando uma chave primária (PRIMARY KEY) é especificada na tabela.

Índice não clusterizado– ao usar este tipo de índice, o índice contém ponteiros de linha classificados pelo campo especificado, e não as próprias linhas, devido aos quais ocorre uma busca rápida pela linha necessária. Uma tabela pode ter vários desses índices.

Índice de coluna– este tipo de índice é baseado na tecnologia de armazenamento de dados de tabela não como linhas, mas como colunas (daí o nome); uma tabela pode ter um índice columnstore.

Ao utilizar este tipo de índice, a tabela passa imediatamente a ser somente leitura, ou seja, não será mais possível adicionar ou alterar dados na tabela; para isso será necessário desabilitar o índice, adicionar/alterar dados e ative o índice novamente.

Esses índices são adequados para conjuntos de dados muito grandes usados ​​em armazéns.

As operações que usam funções agregadas usando agrupamento são executadas muito mais rapidamente (várias vezes!) com esse índice.

O índice Columnstore está disponível a partir da versão 2012 do SQL Server nas edições Enterprise, Developer e Evaluation.

Criação

Índice clusterizado

CRIAR ÍNDICE CLUSTERADO idx_clus_one ON test_table(id) GO

Onde CREATE CLUSTERED INDEX é uma instrução para criar um índice clusterizado, idx_clus_one é o nome do índice, test_table(id) é a tabela e o campo-chave para classificação, respectivamente.

Índice não clusterizado

CRIAR ÍNDICE idx_no_clus ON test_table(summa) GO

Índice de coluna

CREATE columnstore INDEX idx_columnstore ON test_table(date_create) GO

Desligar

Remoção

Cursores

Cursor -é um tipo de dado usado principalmente em procedimentos e gatilhos. Representa um conjunto de dados regular, ou seja, o resultado da solicitação.

Exemplo (tudo isso em código de procedimento)

Declarar variáveis ​​DECLARE @id BIGINT DECLARE @fio VARCHAR(100) DECLARE @summa FLOAT --declarar o cursor DECLARE test_cur CURSOR FOR SELECT id, fio, summa FROM test_table --abrir o cursor OPEN test_cur --ler os dados da primeira linha no cursor --e escreva-os nas variáveis ​​FETCH NEXT FROM test_cur INTO @id, @fio, @summa --execute o loop até que as linhas no cursor acabem WHILE @@FETCH_STATUS = 0 BEGIN --podemos executar sql instruções para cada iteração do loop -- ...................Instruções SQL................... - -leia a próxima linha do cursor FETCH NEXT FROM test_cur INTO @id , @fio, @summa END --feche o cursor CLOSE test_cur DEALLOCATE test_cur

Falamos detalhadamente sobre cursores no material Usando Cursores e Loops no Transact-SQL.

Consultas DML

DML (Linguagem de manipulação de dados) são instruções SQL que manipulam dados. Isso inclui selecionar, atualizar, inserir, excluir.

SELECIONAR

Exemplo

SELECIONE *FROM tabela_teste

ATUALIZAR

Usado para atualizar dados

Exemplo

Todas as linhas da tabela serão atualizadas UPDATE test_table SET summa=500 -- somente linhas com id maior que 10 serão atualizadas UPDATE test_table SET summa=100 WHERE id > 10

INSERIR

Operador para adicionar dados

Adicionando uma linha INSERT INTO test_table (fio, summa, date_create, comment) VALUES ("Full name",100, "10/26/2014", "test record") --adição em massa com base na consulta INSERT INTO test_table SELECT fio , summa, date_create , comentário FROM test_table

EXCLUIR

Você pode usar este operador para excluir dados.

Exemplo

Limpando a tabela inteira DELETE test_table - excluindo apenas linhas que atendem à condição DELETE test_table WHERE summa > 100

É isso, o guia acabou! Espero que tenha ajudado você de alguma forma. Boa sorte!