Um exemplo de criação de uma consulta (Query) em um banco de dados MS SQL Server. O banco de dados está localizado em um arquivo local *.mdf. Como escrever consultas SQL - exemplos detalhados Colocando um controle DataGridView e configurando comunicações de banco de dados

Exemplo

Vamos criar uma view composta por cursos com 3 créditos:

24. Funções agregadas

Estas funções são utilizadas para obter um resultado agregado relacionado aos dados em questão. A seguir estão as funções agregadas comumente usadas:

• COUNT (col_name) - retorna o número de linhas;
• SUM (col_name) - retorna a soma dos valores desta coluna;
• AVG (col_name) – retorna o valor médio de uma determinada coluna;
• MIN (nome_col) – retorna o menor valor de uma determinada coluna;
• MAX (col_name) – Retorna o maior valor de uma determinada coluna.

25. Subconsultas aninhadas

Subconsultas aninhadas são consultas SQL que incluem cláusulas SELECT , FROM e WHERE aninhadas em outra consulta.

Exemplo

Vamos encontrar os cursos ministrados no outono de 2009 e na primavera de 2010:

SELECT DISTINCT course_id FROM seção WHERE semestre = 'Outono' AND ano= 2009 AND course_id IN (SELECT course_id FROM seção WHERE semestre = 'Primavera' AND ano= 2010);

Cada um de nós encontra e usa regularmente vários bancos de dados. Quando selecionamos um endereço de e-mail, estamos trabalhando com um banco de dados. Os bancos de dados são usados ​​por serviços de pesquisa, bancos para armazenar dados de clientes, etc.

Mas apesar do uso constante de bancos de dados, mesmo para muitos desenvolvedores de sistemas de software ainda existem muitos pontos cegos devido a diferentes interpretações dos mesmos termos. Daremos uma breve definição dos termos básicos de banco de dados antes de abordar a linguagem SQL. Então.

Base de dados - um arquivo ou coleção de arquivos para armazenar estruturas de dados ordenadas e seus relacionamentos. Muitas vezes, um sistema de gerenciamento é chamado de banco de dados - é apenas um repositório de informações em um formato específico e pode funcionar com vários SGBDs.

Mesa - Vamos imaginar uma pasta na qual estão armazenados os documentos, agrupados de acordo com uma determinada característica, por exemplo, uma lista de pedidos do último mês. Esta é uma tabela em um computador. Uma tabela separada possui seu próprio nome exclusivo.

Tipo de dados - o tipo de informação que pode ser armazenada em uma coluna ou linha separada. Podem ser números ou texto de um determinado formato.

Coluna e Linha- Todos nós trabalhamos com planilhas, que também possuem linhas e colunas. Qualquer banco de dados relacional funciona com tabelas de maneira semelhante. As linhas às vezes são chamadas de registros.

Chave primária- Cada linha de uma tabela pode ter uma ou mais colunas para identificá-la de forma exclusiva. Sem uma chave primária, é muito difícil atualizar, alterar e excluir linhas relevantes.

O que é SQL?

SQL(Inglês - linguagem de consulta estruturada) foi desenvolvido apenas para trabalhar com bancos de dados e atualmente é o padrão para todos os SGBDs populares. A sintaxe da linguagem consiste em um pequeno número de operadores e é fácil de aprender. Mas, apesar de sua aparente simplicidade, permite a criação de consultas sql para operações complexas com um banco de dados de qualquer tamanho.

Desde 1992, existe um padrão geralmente aceito chamado ANSI SQL. Ele define a sintaxe básica e as funções dos operadores e é suportado por todos os líderes de mercado de DBMS, como ORACLE. É impossível considerar todos os recursos da linguagem em um pequeno artigo, portanto consideraremos brevemente apenas as consultas SQL básicas. Os exemplos mostram claramente a simplicidade e as capacidades da linguagem:

• criação de bancos de dados e tabelas;
• amostragem de dados;
• adição de registros;
• modificação e exclusão de informações.

Tipos de dados SQL

Todas as colunas de uma tabela de banco de dados armazenam o mesmo tipo de dados. Os tipos de dados em SQL são iguais aos de outras linguagens de programação.

Criamos tabelas e bancos de dados

Existem duas maneiras de criar novos bancos de dados, tabelas e outras consultas em SQL:

• através do console SGBD
• Usando as ferramentas de administração interativas incluídas no servidor de banco de dados.

Um novo banco de dados é criado pelo operador CRIAR BANCO DE DADOS<наименование базы данных>; . Como você pode ver, a sintaxe é simples e concisa.

Criamos tabelas dentro do banco de dados usando a instrução CREATE TABLE com os seguintes parâmetros:

• Nome da tabela
• nomes de colunas e tipos de dados

Como exemplo, vamos criar uma tabela Commodity com as seguintes colunas:

Crie uma tabela:

CRIAR TABELA Mercadoria

(commodity_id CHAR(15) NÃO NULO,

vendor_id CHAR(15) NÃO NULO,

nome_commodity CHAR(254) NULL,

preço_commodity DECIMAL(8,2) NULO,

mercadoria_desc VARCHAR(1000) NULL);

A tabela consiste em cinco colunas. Após o nome vem o tipo de dados, as colunas são separadas por vírgulas. O valor da coluna pode aceitar valores vazios (NULL) ou deve ser preenchido (NOT NULL), e isso é determinado no momento da criação da tabela.

Recuperando dados de uma tabela

O operador de busca de dados é a consulta SQL mais comumente usada. Para obter informações, devemos indicar o que queremos selecionar nessa tabela. Primeiro um exemplo simples:

SELECIONE nome_commodity da mercadoria

Após a instrução SELECT especificamos o nome da coluna para obter informações e FROM define a tabela.

O resultado da consulta serão todas as linhas da tabela com valores Commodity_name na ordem em que foram inseridos no banco de dados, ou seja, sem qualquer classificação. Uma cláusula ORDER BY adicional é usada para ordenar o resultado.

Para consultar vários campos, liste-os separados por vírgulas, como no exemplo a seguir:

SELECIONE commodity_id, commodity_name, commodity_price FROM Commodity

É possível obter o valor de todas as colunas de uma linha como resultado da consulta. Para fazer isso, use o sinal “*”:

SELECIONE * DA Mercadoria

• Além disso, SELECT suporta:
• Classificando dados (operador ORDER BY)
• Seleção de acordo com as condições (ONDE)
• Termo de agrupamento (GROUP BY)

Adicione uma linha

Para adicionar uma linha a uma tabela, são usadas consultas SQL com o operador INSERT. A adição pode ser feita de três maneiras:

• adicione uma nova linha inteira;
• parte de uma string;
• resultados da consulta.

Para adicionar uma linha completa, você deve especificar o nome da tabela e os valores das colunas (campos) da nova linha. Aqui está um exemplo:

INSERT INTO Commodity VALUES("106 ", "50", "Coca-Cola", "1,68", "Sem álcool,)

O exemplo adiciona um novo produto à tabela. Os valores são listados após VALUES para cada coluna. Se não houver valor correspondente para a coluna, então NULL deverá ser especificado. As colunas são preenchidas com valores na ordem especificada quando a tabela foi criada.

Se você adicionar apenas parte de uma linha, deverá especificar explicitamente os nomes das colunas, como no exemplo:

INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)

VALORES("106 ", ‘50", "Coca-Cola",)

Inserimos apenas os identificadores do produto, fornecedor e seu nome, e deixamos os demais campos em branco.

Adicionando resultados de consulta

INSERT é usado principalmente para anexar linhas, mas também pode ser usado para anexar os resultados de uma instrução SELECT.

Alterando dados

Para alterar informações nos campos de uma tabela de banco de dados, deve-se utilizar a instrução UPDATE. O operador pode ser usado de duas maneiras:

• Todas as linhas da tabela são atualizadas.
• Somente para uma linha específica.

UPDATE consiste em três elementos principais:

• tabela em que alterações precisam ser feitas;
• nomes de campos e seus novos valores;
• condições para selecionar linhas a serem alteradas.

Vejamos um exemplo. Digamos que o preço de um produto com ID=106 mudou, então esta linha precisa ser atualizada. Escrevemos o seguinte operador:

ATUALIZAR Mercadoria SET commodity_price = "3,2" WHERE commodity_id = "106"

Especificamos o nome da tabela, no nosso caso Commodity, onde será realizada a atualização, depois de SET - o novo valor da coluna e encontramos o registro desejado especificando o valor do ID necessário em WHERE.

Para alterar várias colunas, a instrução SET é seguida por vários pares de valores de coluna separados por vírgulas. Vejamos um exemplo em que o nome e o preço de um produto são atualizados:

ATUALIZAR Commodity SET commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

Para remover informações de uma coluna, você pode atribuir a ela o valor NULL se a estrutura da tabela permitir. Deve-se lembrar que NULL é justamente o valor “nenhum”, e não zero na forma de texto ou número. Vamos remover a descrição do produto:

ATUALIZAR Mercadoria SET commodity_desc = NULL WHERE commodity_id = "106"

Removendo linhas

Consultas SQL para excluir linhas em uma tabela são executadas usando a instrução DELETE. Existem dois casos de uso:

• Certas linhas da tabela são excluídas;
• Todas as linhas da tabela são excluídas.

Um exemplo de exclusão de uma linha de uma tabela:

EXCLUIR DA mercadoria WHERE commodity_id = "106"

Após DELETE FROM indicamos o nome da tabela na qual as linhas serão excluídas. A cláusula WHERE contém a condição pela qual as linhas serão selecionadas para exclusão. No exemplo, excluímos a linha de produtos com ID=106. Especificar WHERE é muito importante porque omitir esta instrução excluirá todas as linhas da tabela. Isso também se aplica à alteração do valor dos campos.

A instrução DELETE não especifica nomes de colunas ou metacaracteres. Exclui linhas completamente, mas não pode excluir uma única coluna.

Usando SQL no Microsoft Access

Normalmente usado de forma interativa para criar tabelas, bancos de dados, para gerenciar, modificar, analisar dados em um banco de dados e para implementar consultas SQL Access por meio de um designer de consultas interativo conveniente (Query Designer), com o qual você pode construir e executar imediatamente instruções SQL de qualquer complexidade.

Também é suportado o modo de acesso ao servidor, no qual o SGBD Access pode ser utilizado como gerador de consultas SQL para qualquer fonte de dados ODBC. Este recurso permite que os aplicativos do Access interajam com qualquer formato.

Extensões SQL

Como as consultas SQL não possuem todos os recursos das linguagens de programação processuais, como loops, ramificações, etc., os fabricantes de SGBDs estão desenvolvendo sua própria versão de SQL com recursos avançados. Em primeiro lugar, trata-se de suporte para procedimentos armazenados e operadores padrão de linguagens procedurais.

Os dialetos mais comuns da língua:

• Banco de Dados Oracle - PL/SQL
• Interbase, Firebird-PSQL
• Microsoft SQL Server - Transact-SQL
• PostgreSQL - PL/pgSQL.

SQL na Internet

O MySQL DBMS é distribuído sob a Licença Pública Geral GNU gratuita. Existe uma licença comercial com capacidade para desenvolver módulos customizados. Como componente, está incluído nos conjuntos de servidores de Internet mais populares, como XAMPP, WAMP e LAMP, e é o SGBD mais popular para desenvolvimento de aplicações na Internet.

Foi desenvolvido pela Sun Microsystems e atualmente é suportado pela Oracle Corporation. São suportados bancos de dados de até 64 terabytes, padrão de sintaxe SQL:2003, replicação de bancos de dados e serviços em nuvem.

Expressões de tabela são chamadas de subconsultas usadas onde a presença de uma tabela é esperada. Existem dois tipos de expressões de tabela:

tabelas derivadas;

expressões de tabela generalizadas.

Essas duas formas de expressões de tabela são discutidas nas subseções a seguir.

Tabelas derivadas

Tabela derivadaé uma expressão de tabela incluída na cláusula FROM de uma consulta. As tabelas derivadas podem ser usadas nos casos em que o uso de aliases de colunas não é possível porque o tradutor SQL processa outra instrução antes que o alias seja conhecido. O exemplo abaixo mostra uma tentativa de usar um alias de coluna em uma situação em que outra cláusula está sendo processada antes que o alias seja conhecido:

USE SampleDb; SELECT MONTH(EnterDate) como enter_month FROM Works_on GROUP BY enter_month;

Tentar executar esta consulta produzirá a seguinte mensagem de erro:

Msg 207, Nível 16, Estado 1, Linha 5 Nome de coluna inválido "enter_month". (Mensagem 207: Nível 16, Estado 1, Linha 5 Nome de coluna inválido enter_month)

O motivo do erro é que a cláusula GROUP BY é processada antes que a lista correspondente da instrução SELECT seja processada e o alias da coluna enter_month é desconhecido quando o grupo é processado.

Este problema pode ser resolvido usando uma tabela derivada que contém a consulta anterior (sem a cláusula GROUP BY) porque a cláusula FROM é executada antes da cláusula GROUP BY:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

O resultado da execução desta solicitação será assim:

Normalmente, uma expressão de tabela pode ser colocada em qualquer lugar em uma instrução SELECT onde um nome de tabela possa aparecer. (O resultado de uma expressão de tabela é sempre uma tabela ou, em casos especiais, uma expressão.) O exemplo abaixo mostra o uso de uma expressão de tabela na lista de seleção de uma instrução SELECT:

O resultado desta consulta:

Expressões genéricas de tabela

Expressão de tabela comum (OTB)é uma expressão de tabela nomeada suportada pela linguagem Transact-SQL. Expressões de tabela comuns são usadas nos dois tipos de consultas a seguir:

não recursivo;

recursivo.

Esses dois tipos de solicitações são discutidos nas seções a seguir.

Consultas OTB e não recursivas

A forma não recursiva de OTB pode ser usada como alternativa às tabelas e visualizações derivadas. Normalmente OTB é determinado por Cláusulas COM e uma consulta adicional que faz referência ao nome usado na cláusula WITH. No Transact-SQL, o significado da palavra-chave WITH é ambíguo. Para evitar ambiguidade, a instrução que precede a instrução WITH deve terminar com ponto e vírgula.

USAR AdventureWorks2012; SELECIONE SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005 ")/2.5;

A consulta neste exemplo seleciona pedidos cujo total de impostos (TotalDue) é maior que a média de todos os impostos e cujos encargos de frete (Freight) são maiores que 40% da média de impostos. A principal propriedade desta consulta é o seu comprimento, pois a subconsulta precisa ser escrita duas vezes. Uma maneira possível de reduzir a quantidade de construção de consultas é criar uma visualização que contenha uma subconsulta. Mas esta solução é um pouco complicada porque requer a criação de uma visualização e sua exclusão após a conclusão da execução da consulta. Uma abordagem melhor seria criar um OTB. O exemplo abaixo mostra o uso de OTB não recursivo, o que encurta a definição da consulta acima:

USAR AdventureWorks2012; COM price_calc (year_2005) AS (SELECT AVG (TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR (OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_calc) ) /2,5;

A sintaxe da cláusula WITH em consultas não recursivas é a seguinte:

O parâmetro cte_name representa o nome do OTB que define a tabela resultante, e o parâmetro column_list representa a lista de colunas da expressão da tabela. (No exemplo acima, o OTB é denominado price_calc e possui uma coluna, year_2005.) O parâmetro inner_query representa uma instrução SELECT que especifica o conjunto de resultados da expressão de tabela correspondente. A expressão de tabela definida pode então ser usada em outer_query. (A consulta externa no exemplo acima usa OTB price_calc e sua coluna year_2005 para simplificar a consulta duplamente aninhada.)

OTB e consultas recursivas

Esta seção apresenta material de maior complexidade. Portanto, ao lê-lo pela primeira vez, é recomendável ignorá-lo e retornar a ele posteriormente. Os OTBs podem ser usados ​​para implementar recursões porque os OTBs podem conter referências a si mesmos. A sintaxe básica do OTB para uma consulta recursiva é semelhante a esta:

Os parâmetros cte_name e column_list têm o mesmo significado que no OTB para consultas não recursivas. O corpo de uma cláusula WITH consiste em duas consultas combinadas pelo operador UNIÃO TODOS. A primeira consulta é chamada apenas uma vez e começa a acumular o resultado da recursão. O primeiro operando do operador UNION ALL não faz referência ao OTB. Essa consulta é chamada de consulta de referência ou origem.

A segunda consulta contém uma referência ao OTB e representa sua parte recursiva. Por causa disso, é chamado de membro recursivo. Na primeira chamada à parte recursiva, a referência OTB representa o resultado da consulta de referência. O membro recursivo usa o resultado da primeira chamada de consulta. Depois disso, o sistema chama novamente a parte recursiva. Uma chamada para um membro recursivo é interrompida quando uma chamada anterior retorna um conjunto de resultados vazio.

O operador UNION ALL une as linhas atualmente acumuladas, bem como as linhas adicionais adicionadas pela chamada atual ao membro recursivo. (A presença do operador UNION ALL significa que as linhas duplicadas não serão removidas do resultado.)

Finalmente, o parâmetro outer_query especifica a consulta externa que o OTB usa para recuperar todas as chamadas para a junção de ambos os membros.

Para demonstrar a forma recursiva do OTB, utilizamos a tabela Avião definida e preenchida com o código mostrado no exemplo abaixo:

USE SampleDb; CREATE TABLE Avião(ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantidadeContida INT, UnitCost DECIMAL(6,2)); INSERIR VALORES EM Avião ("Avião", "Fuselagem", 1, 10); INSERT INTO Avião VALORES ("Avião", "Asas", 1, 11); INSERT INTO Avião VALUES ("Avião", "Cauda", 1, 12); INSERIR VALORES EM Avião ("Fuselagem", "Salão", 1, 13); INSERIR VALORES EM Avião ("Fuselagem", "Cockpit", 1, 14); INSERIR VALORES EM Avião ("Fuselagem", "Nariz", 1, 15); INSERT INTO Avião VALUES ("Cabin", NULL, 1,13); INSERT INTO Avião VALUES ("Cockpit", NULL, 1, 14); INSERT INTO Avião VALUES ("Nose", NULL, 1, 15); INSERT INTO Avião VALUES ("Asas", NULL,2, 11); INSERT INTO Avião VALUES ("Tail", NULL, 1, 12);

A tabela Avião possui quatro colunas. A coluna ContainingAssembly identifica a montagem e a coluna ContainAssembly identifica as peças (uma por uma) que compõem a montagem correspondente. A figura abaixo mostra uma ilustração gráfica de um possível tipo de aeronave e seus componentes:

A tabela Avião consiste nas seguintes 11 linhas:

O exemplo a seguir usa a cláusula WITH para definir uma consulta que calcula o custo total de cada build:

USE SampleDb; COM list_of_parts(assembly1, quantidade, custo) AS (SELECT ContainingAssembly, QuantidadeContida, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2) ) FROM list_of_parts l, Avião a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Peça", quantidade "Quantidade", custo "Preço" FROM list_of_parts;

A cláusula WITH define uma lista OTB chamada list_of_parts, que consiste em três colunas: assembly1, quantidade e custo. A primeira instrução SELECT no exemplo é chamada apenas uma vez para armazenar os resultados da primeira etapa do processo de recursão. A instrução SELECT na última linha do exemplo exibe o seguinte resultado.