Baixe o app para aproveitar ainda mais
Prévia do material em texto
Perfil: Martin José Fagonde Morães. Possui Mestrado em Engenharia de Produção pela Universidade Federal de Santa Catarina – UFSC (2001), graduado em Processamento de Dados (1997). Atua como professor nos componentes curriculares de desenvolvimento de sistemas há mais de 15 anos. Foi coordenador de cursos de graduação e de pós-graduação. Atuou na área de TI por mais de 18 anos como: coordenador de TI, consultor e desenvolveu e implementou sistemas diversos sistemas. Currículo Lattes: http://lattes.cnpq.br/2746554350570335 AULA 06 – Projeto Lógico e Abordagem Direta Introdução: Structured Query Language, ou Linguagem de Consulta Estruturada ou SQL, é a linguagem de pesquisa declarativa padrão para banco de dados relacional (base de dados relacional). Muitas das características originais do SQL foram inspiradas na álgebra relacional. O SQL foi desenvolvido originalmente no início dos anos 70 nos laboratórios da IBM em San Jose, dentro do projeto System R, que tinha por objetivo demonstrar a viabilidade da implementação do modelo relacional proposto por E. F. Codd. O nome original da linguagem era SEQUEL, acrônimo para "Structured English Query Language" (Linguagem de Consulta Estruturada, em Inglês)[1] , vindo daí o facto de, até hoje, a sigla, em inglês, ser comumente pronunciada "síquel" ao invés de "és-kiú-él", letra a letra. No entanto, em português, a pronúncia mais corrente é a letra a letra: "ésse- quê-éle". (A history and evaluation of System R. Available online at http://dl.acm.org/citation.cfm?doid=358769.358784, checked on 10/21/2015.) Contextualizando SQL DDL - Implementando um DB Nesta etapa, estudaremos a linguagem SQL, os comandos para criar uma base de dados com todos os recursos definidos no modelo. Em termos técnicos, estudaremos o conjunto de instruções SQL DDL - Data Definition Language, ou seja, o conjunto de comandos referentes a Linguagem de Definição de Dados do SQL. Os principais recursos para se ter uma base de dados é a própria base de dados (Data Base – DB), as tabelas (table), as view, procedures, trigger, user e vários outros recursos. Para nossos estudos focaremos no Data Base (base de dados) e nas table (tabelas). É comum ouvirmos referências a estes recursos com o termo “objeto do DB”. Neste sentido objeto não tem relação com a orientação a objetos (OO). A relação entre os dados, as tabelas e a base de dados, ilustrativamente pense que os dados são guardados dentro das tabelas, as tabelas são guardadas dentro da base de dados. Sendo assim para se guardar os dados precisamos primeiramente ter as tabelas e para criarmos as tabelas precisamos ter a base de dados criada. Executando Instruções Para executar instruções SQL no MySQL Workbench é necessário digitar a instrução na área de instruções SQL e clicar em um dos dois primeiros botões que tem como icone um raio amarelo. Na Figura 0:1 aparecem acinalados com uma elipse em vermelho. O primeiro botão , o que aparece só a imagem do ráio, faz com que todas as intruções que estejam digitadas na área de código sejam executadas. O outro botão , o que tem um raio amarelo com um cursor, executa apenas a instrução que está na linha em que o cursor esteja. Para executar a instrução pode-se também utilizar as teclas de atalho Ctrl + ENTER, e a instrução em que o cursor esteja será executada. A Figura 0:1 mostra uma instrução que já foi executada. Repare que na área de resultados aparece uma linha indicando com um icone verde que a instrução foi realizada, ao lado indica qual a instrução que foi realizada e ainda na mesma linha indica quantas linhas, na base de dados, foram afetadas com esta instrução. Na área de bases de dados, aparecerá a base criada depois de solicitar a atualização. Solicita-se a atualização clicando no botão , indicado na Figura 0:1 por uma seta vermelha. Figura 0:1 - Create database. Criar Os recursos são criados com o comando “create” seguido da diretiva que identifica o recurso desejado mais o nome com o qual estamos nomeando o recurso a ser criado e termina a instrução com ponto e vírgula. CREATE DATABASE Para criar uma base de dados a instrução é: CREATE DATABASE nome_da_base; Vamos criar a base de dados ‘redemensagens’. Na área de instruções SQL digite o comando create mais o identificador database, o nome da base redemensagens e finalise com o ; . Veja na Figura 0:1 como foi digitado. CREATE SCHEMA e CREATE DATABASE, em MySQL são sinônimos, ou seja, terão o mesmo resultado que será a criação de uma base de dados. CRATE TABLE Para criar uma tabela, é necessário que se esteja conectado a base desejada e a instrução é: CRATE TABLE nome_da_tabela; Na criação de uma tabela, podemos criar juntamente os campos e as respectivas definições. Algumas das principais definições são: NOT NULL: Define que o campo, obrigatoriamente, tem de ter algum valor na criação do registro e que nunca pode ficar null. AUTO_INCREMENT: Define que o campo numérico terá seu valor gerado automaticamente e com valor maior que o do último registro inserido. Por padrão é incrementado de um em um. UNIQUE: É uma instrução utilizada juntamente com os tipos de dados (domínios) para definir que o campo NÃO pode receber um valor que já esteja sendo utilizado em outro registro no mesmo campo. Ao definir um campo como chave primária ou como chave estrangeira eles recebem a instrução de UNIQUE. PRIMARY KEY: Define que o campo será uma chave primária. Todo campo definido como primary key, automaticamente é unique e not null. FOREING KEY: Define que o campo será uma chave estrangeira. Todo campo definido como primary key, automaticamente é not null. Sintaxe para criação de uma tabela. CREATE TABLE tbl_name( col_name data_type definições_opcionais, col_name data_type definições_opcionais, . . .); Definições opcionais (definições_opcionais) [NOT NULL][AUTO_INCREMENT] [UNIQUE | [PRIMARY KEY] [FOREIGN KEY REFERENCES tbl_name (index_col_name,...)] Explicando a sintaxe. tbl_name: Nome que será dado a tabela. col_name: Nome que será dado a coluna. data_type: Tipo de dados que o campo tem de suportar. definições_opcionais: é opcional definir o campo como NOT NULL, AUTO_INCREMENT, UNIQUE ou PRIMARY KEY e FOREIGN KEY. Exemplo 1: Com primary key ao final. CREATE TABLE usuario ( idusuario INT NOT NULL AUTO_INCREMENT , nome VARCHAR(80), email VARCHAR(45), dtcadastro DATETIME, PRIMARY KEY (idusuario)); Exemplo 2: Com primary key na definição do campo. CREATE TABLE usuario ( idusuario INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(80), email VARCHAR(45), dtcadastro DATETIME); Exemplo 3: Com o “tipo” serial e primary key no campo. CREATE TABLE usuario( idusuario serial PRIMARY KEY, nome VARCHAR(80), email VARCHAR(45), dtcadastro DATETIME); Os exemplos 1 e 2 criam a tabela usuario. A diferença entre os dois é de como pode ser definido a chave primária. O exemplo 3 mostra a utilização do ‘tipo’ SERIAL que é um alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Alterar ALTER TABLE altera a estrutura de uma tabela. Pode-se adicionar ou excluir colunas, alterar o tipo de colunas existentes, ou renomear colunas ou a própria tabela Sintaxe. ALTER TABLE tbl_name [opções]; Opções | ADD col_name column_definition | ADD PRIMARY KEY (index_col_name) | ADD UNIQUE (index_col_name) | ADD FOREIGN KEY (index_col_name) REFERENCES tbl_name (index_col_name)| CHANGE old_col_name new_col_name column_definition | MODIFY col_name column_definition | DROP col_name | DROP PRIMARY KEY Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que receberá a alteração. col_name: Nome da coluna que receberá a alteração. column_definition: Definições para a nova coluna. index_col_name: Coluna que será definida como primária, estrangeira ou unique. Adicionando uma coluna Vamos adicionar a coluna de endereço (endereco) na tabela de usuários (usuario), com capacidade para vinte (20) caracteres. Sintaxe: ALTER TABLE tbl_name ADD col_name column_definition; Comando: alter table usuario add endereco varchar(20); Modificando o tipo do dado Vamos modificar o tipo do dado do campo endereço para oitenta (80) caracteres. Sintaxe: ALTER TABLE tbl_name MODIFY col_name column_definition Comando: alter table usuario modify endereco varchar(80); Renomeando e modificando o tipo de dado Vamos renomear o campo endereco para peso e vamos defini-lo para ser do tipo real. Sintaxe: ALTER TABLE tbl_name CHANGE old_col_name new_col_name column_definition Comando: alter table usuario change endereco peso real; Excluir um campo Vamos excluir o campo peso. Sintaxe: ALTER TABLE tbl_name DROP col_name; Comando:alter table usuario drop novo_endereco; Adicionar chaves Para adicionar chave primária ou estrangeira em uma coluna a sintaxe é: Sintaxe: ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name); ALTER TABLE tbl_name ADD FOREIGN KEY (index_col_name) REFERENCES tbl_name (index_col_name) Comando para inserir uma chave primária na tabela usuario. alter table usuario add primary key(idusuario); Derrubar / Destruir A instrução para excluir definitivamente um recurso da base de dados é o DROP. DROP TABLE remove uma ou mais tabelas. Tem que ter o privilégio para cada tabela. Todos os dados da tabela e definição da tabela são removidos. Se uma das tabelas nomeadas na lista de argumentos não existir, o MySQL retorna um erro indicando por nome quais tabelas não existem e que não deu para excluir, as demais tabelas na lista serão excluídas. Sintaxe: DROP TABLE tbl_name [, tbl_name] ... Comando: drop table usuario; SQL DML - Trabalhando com os Dados O conjunto de instruções DML (Linguagem de Manipulação de Dados) são para lidar com os dados. Inserir, alterar ou excluir os dados de uma tabela. Nesta sessão utilizaremos a tabela usuário, veja Figura 8:1 Tabela usuário para referência. para aplicar as instruções nos exemplos. Inserir dados Para inserir novos registros em uma tabela utiliza-se a instrução INSERT. Tem três formas se se utilizar a instrução INSERT, a INSERT ... VALUES, INSERT ... SET e INSERT ... SELECT. Vamos conhece-los: INSERT ... VALUES Esta é a forma mais conhecida. Pode-se inserir vários registros com o mesmo comando. Sintaxe: INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (...),(...),... Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que receberá a alteração. col_name: Nome da coluna que receberá a alteração. Se não especificar as colunas, fica entendido que terá valor para todas elas na ordem em que estão definidas na tabela. INTO: A utilização do INTO é opcional. VALUES e VALUE: São sinônimos, qualquer um dos dois pode ser usado Exemplo 1 – Inserindo um registro com colunas específicas CREATE TABLE usuario( idusuario serial PRIMARY KEY, nome VARCHAR(80), email VARCHAR(45), dtcadastro DATETIME); Figura 0:1 Tabela usuário para referência. Comando: insert into usuario (nome, email) values ('a', 'a@a'); Aqui estamos inserindo um registro na tabela usuario para os campos nome e email os valores ‘a’ e ‘a@a’, respectivamente. Da mesma forma pode-se inserir vários registros com o mesmo comando colocando-se os outros valores entre parênteses e separando por vírgula, veja no exemplo seguinte em que estão sendo inseridos os registros a, b e c com o mesmo comando Ex.: insert into usuario (nome, email) values ('a', 'a@a'), ('b', 'b@b'), ('c', 'c@c'); Exemplo 2 – Inserindo um registro com valores para todas as colunas. Nesta situação não é preciso explicitar as colunas, basca colocar os valores na mesma ordem em que as colunas estão na tabela. Comando: insert into usuario values (0,'a', 'a@a', '2015/12/30'); Aqui estamos inserindo um registro na tabela usuario com valores para todos campos sendo 0, ‘a’ e ‘a@a’ e '2015/12/30' respectivamente. O valor zero (0) é para o campo idusuario que é auto-increment. Na instrução insert tem de ter um valor qualquer, o valor que valerá será o que for gerado pelo auto-increment. A data segue o padrão do campo ano, mês e dia. INSERT ... SET Utilizando o SET, ao invés de VALUES a identificação do campo é obrigatória após a instrução SET. Sintaxe: INSERT [INTO] tbl_name SET col_name= Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que receberá a alteração. col_name: Nome da coluna que receberá a alteração. INTO: A utilização do INTO é opcional. Exemplo 1: Inserindo um registro na tabela usuario com valor ‘g’ para o campo nome. Comando: insert into usuario set nome='g'; Exemplo 2: Inserindo um registro na tabela usuario com valor ‘g’ para o campo nome e valor 'g@g' para o campo email. Comando: insert into usuario set nome='g', email='g@g'; INSERT ... SELECT A instrução INSERT ... SELECT é para inserir registro em uma tabela com dados selecionados de outra tabela. Só precisa coincidir as colunas da tabela que vai receber os dados com as colunas resultantes da instrução select. Sintaxe: INSERT [INTO] tbl_name [(col_name,...)] SELECT ... Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que receberá a alteração. col_name: Nome da coluna que receberá a alteração. INTO: A utilização do INTO é opcional. Comando: insert into cliente select * from usuario; Neste comando estamos inserindo na tabela cliente, que já existe e tem a mesma estrutura da tabela usuario, todos os registros da tabela usuario. Alterar dados O UPDATE atualiza uma coluna de registros existentes na tabela. A cláusula SET indica quais colunas modificar e os valores que devem ser atribuídos. A cláusula WHERE, especifica as condições que identificam quais os registros para atualizar. Sem cláusula WHERE, todos os registros receberão a atualização. Sintaxe: UPDATE tbl_name SET col_name1={valor} [, col_name2={valor}] ... [WHERE where_condition] Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que receberá a alteração. col_name: Nome da coluna que receberá a alteração. Valor: Novo dado a ser atribuído ao campo. Exemplo 1: Alterando uma data. Considera a tabela usuario povoada com os dados conforme Figura 8:1 Tabela usuário para referência. O comando: update usuario set dtcadastro='2015/09/20'; faz com que todos os usuários fiquem com o valor '2015/09/20' no campo dtcadastro, veja na Figura 0:2. Figura 0:2 Tabela usuario povoada. Figura 0:3 Tabela usuario com dtcadastro. Exemplo 2: Alterando registros específicos. Para que o efeito da instrução UPDATE seja sobre registros específicos utiliza-se a clausula WHERE. Vamos alterar a dtcadastro, do usuário de idusuario = 18. Comando: update usuario set dtcadastro='1968/04/15'where idusuario = 18; Clausula WHERE A clausula WHERE é utilizada para restringir os registros que receberão a ação das instruções UPDATE, DELETE e SELECT, em outras palavras para aplicar filtro nos registros selecionados. A clausula WHERE é baseada nos operadores relacionais, lógicos e operadores próprios. Os operadores relacionais são para comparar o valor de um campo com o valor de outro campo ou com um valor literal, este conjunto é denominado de expressão. Os operadores lógicos são para comparar o resultado de duas expressões relacionais. Operadores relacionais Os operadores relacionais são: Operador Descrição Exemplo retorna TRUE = Compara dois elementos e retorna verdadeiro se forem iguais. 21 = 21 >= Compara dois elementos e retorna verdadeiro se o elemento da esquerda for maior ou igual ao elemento da direita. 21 >= 20 <= Compara dois elementos e retorna verdadeiro se o elemento da esquerda for menor ou igual ao elemento da direita. 20 <= 21 > Compara dois elementos e retorna verdadeiro se o elemento da esquerda for maior que o elemento da direita. 21 > 20 < Compara dois elementos e retorna verdadeiro se o elemento da esquerda for menor que o elemento da direita. 20 < 21 <> Compara dois elementos e retorna verdadeiro se o elemento da esquerda for diferente do elemento da direita. 20 <> 21 Operadores lógicos Operador Descrição Exemplo retorna TRUE OR Compara duas expressões e retorna verdadeiro se uma delas for verdadeira false or true AND Compara duas expressões e retorna verdadeiro se ambas forem verdadeiras false and true NOT Inverte o resultado de uma expressão Not false IS [NOT] {TRUE | FALSE | UNKNOWN} Testa se o resultado de uma expressão for verdadeira, falsa ou desconhecida. Is not false Outras Os seguintes operadores possibilitam testar valores para os campos especificados. Operador Descrição [NOT] IN (subquery) Compara o valor de um campo com o resultado de uma subquery [NOT] IN (expr [, expr] ...) Compara o valor de um campo com expressões [NOT] BETWEEN bit_expr AND predicate Verifica se o valor de um campo está entre o intervalo de valores definidos. [NOT] LIKE simple_expr [ESCAPE simple_expr] Verifica se o valor de um campo está no valor de verificação. Aceita meta caracteres como o % e o ?. Vamos aplicar os operadores da clausula where ao utilizar o update, delete e select. Excluir dados A instrução DELETE é utilizada para excluir registros de uma tabela. Sintaxe: DELETE FROM tbl_name [WHERE where_condition] Vajamos alguns detalhes gerais da sintaxe. tbl_name: Nome da tabela que terá registros excluídos. Exemplo 1: Excluindo todos os registros da tabela usuario. Todos os registros serão excluídos e não tem como desfazer a operação. Comando: Delete from usuario; Exemplo 2: Excluindo um registro específico, o que tem o valor 18 no campo idusuario. Para atingir registros específicos entra em sena a clausula where. Comando: delete from usuario where idusuario = 18; Selecionar – Recuperar os dados de uma tabela SELECT é usada para recuperar os registros desejados a partir de uma ou mais tabelas, e pode ter subconsultas e UNIÃO. Os resultados de um select são na forma de linhas para os registros e colunas para os campos, lembrando o conceito de tabelas. Sintaxe: A instrução select tem vários recursos muito uteis para recuperar os dados armazenados na forma organizada que se desejar. SELECT [DISTINCT] select_expr [, select_expr ...] [FROM table_references] [WHERE where_condition] [GROUP BY {col_name | expr | position}] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] Vajamos alguns detalhes gerais da sintaxe. select_expr: Nomes das colunas desejadas. table_references: Nome da (s) tabela (s) que terá registros selecionados. where_condition: Condições de filtro. Recursos do select: * – Para indicar todas as colunas da seleção. DISTINCT – Utilizado para restringir a uma única ocorrência de cada valor. FROM – Indica as tabelas envolvidas na seleção. WHERE – Aplica filtro nos registros selecionados. GROUP BY – Agrupa os resultados quando utilizado funções. HAVING – Aplica filtros com a possibilidade de utilizar também funções. ORDER BY – Ordena o resultado da seleção pela (s) colunas indicadas, de forma ascendente (ASC) ou descendente (DESC). LIMIT – Impõe um limite máximo de registros a serem encontrados em uma seleção. Colunas e distinct Exemplo 1: Selecionando todas as colunas e todos os registros Comando: select * from usuario; Exemplo 2: Selecionando as colunas nome e email e todos os registros Comando: select nome, email from usuario; Exemplo 3: Selecionando as colunas nome e email sem repetir dados. Comando: select distinct nome, email from usuario; Where – filtrando registros Exemplo 1: Selecionando todas as colunas dos registros de nome ‘a’ Comando: select * from usuario where nome = 'a'; Exemplo 2: Selecionando todos os registros que a dtcadastro seja null Comando: select * from usuario where dtcadastro is null; Funções As funções operam sobre as colunas em que forem aplicadas. Para uma referência completa das funções disponíveis no MySQL consulte a documentação disponível em https://dev.mysql.com/doc/refman/5.0/en/func-op- summary-ref.html. Algumas funções para utilizarmos em nossos exemplos: UPPER() Converte para maiúsculo. YEAR() Retorna o ano de uma data. COUNT() Retorna a contagem de linhas retornadas. Exemplo 1: Total de registros Comando: select count(*) from usuario; e select count(dtcadastro) from usuario; O caracter asterisco indica a função count todos os registros. O nome do campo na função indica só as ocorrências em que o valor for diferente de NULL. Exemplo 2: Só o ano de uma data. Comando: select nome, year(dtcadastro) from usuario; Exemplo 3: Nome em maiúsculo. Comando: select upper(nome), year(dtcadastro) from usuario; Group By Esta clausula trabalha em conjunto com as funções, agrupando os resultados. Exemplo 1: Quantidade de ocorrências para cada um dos nomes. Comando: select nome, count(nome) from usuario group by nome; Group by com indicação do nome da coluna de agrupamento. Exemplo 2: select year(dtcadastro), count(year(dtcadastro)) from usuario group by 1; Group by com indicação da ordem da coluna, resultante, para agrupamento. A contagem está sendo feita levando em conta somente o ano. operações de restrição, projeção e junção (DATE; VIEIRA; LIFSCHITZ, 2004, p. 51) Having Filtrando os registros utilizando funções. Exemplo 1: Listar quando tiver mais de duas (2) ocorrências do mesmo nome. Comando: select nome, count(nome) from usuario having count(nome) >2; Order by Ordena pela coluna indicada. Exemplo 1: Ordenando pela coluna nome. Comando: select * from usuario order by nome; Limit Impõe um limite na quantidade de linhas retornadas. Exemplo 1: Limitando o retorno a duas (2) linhas. Comando: select * from usuario limit 2; Pesquisa Vão ao site do MySQL (https://dev.mysql.com/doc/refman/5.0/en/func- op-summary-ref.html) e estude as outras funções disponíveis. Trocando Ideias Compartilhecom os colegas, através do fórum, uma função que considere muito útil no dia a dia. Síntese O SQL é muito poderoso, fundamental para as atividades com os bancos de dados relacionais. O conjunto de instruções create, alter e drop, conhecidos como DDL, são para definir a estrutura de uma base de dados. O conjunto de instruções insert, update e delete, conhecidos como DML, são para fazer o tratamento dos dados na base de dados. A instrução SELECT é o mais utilizado, é com ele que recuperamos os dados que estão armazenados na base. Esta instrução juntamente com seus recursos permite recuperar os dados com tratamento. Compartilhando Elabore uma sentença com a instrução SELECT utilizando novas funções e compartilhe com os colegas e discutam a aplicabilidade. Autoavaliação Referências HEUSER, Carlos Alberto (Org.). Projeto de banco de dados. 6. ed. Porto Alegre (RS): Bookman, 2009.xii, 282. KROENKE, David M. (Org.). Banco de Dados: Fundamentos, Projeto e Implementação. 6. ed. Rio de Janeiro - RJ: LCT, 1999.
Compartilhar