SQL

Vamos dar uns toques de SQL? VI

Criado por Pedro Pinto em 2 de Julho de 2011 | 0 comentários

Por Pedro Gaspar para o PPLWARE

Esta semana vamos aprender mais algumas funções novas, desta vez relacionadas com datas.

Vamos tentar descobrir a idade das pessoas que temos na nossa base de dados, aprender a fazer contas, entre outras coisas.

Para apresentar alguns exemplos utilizei a tabela que temos utilizando nas semanas anteriores (tlb_ppware) que contem os seguintes dados:

Começando pelo inicio, o sql é uma linguagem bastante abrangente, alem de nos permitir fazer operações com tabelas, tambem nos permite fazer operações matemáticas.

EXEMPLO1

Objectivo: Listar o nome e:

• o valor do nascimento + 2
• o valor do nascimento – 2
• o valor do nascimento multiplicado por 2
• o valor do nascimento dividido por 2

Query:

SELECT nome, nascimento +2 AS soma, nascimento -2 AS subtracao, nascimento *2 AS multiplicacao, nascimento /2 AS divisao
FROM tbl_ppware

Resultado:

Fazer contas com SQL e bastante fácil! Agora para complicar um pouco mais, vamos fazer contas com datas.

O próprio SQL consegue ir buscar a data actual ao sistema onde esta a ser executado, para ir buscar essa informação utilizamos a função NOW() (outras funções de data e hora podem ser consultadas aqui), a data é-nos apresentada no formato “ano-mês-dia hora:minuto:segundo”

Query:

SELECT NOW( )

Resultado:

Esta e a forma considerada universal de apresentar a data, e se pensar-mos bem até e a que faz mais sentido. No entanto como somos europeus e gostamos também bastante da nossa forma e-nos possível também alterar a forma como a data ira ser apresentada, para isso utilizamos a função DATE_FORMAT(), que nos permite mostrar a data de outra forma completamente a nossa escolha, podem consultar a informação completa sobre esta função aqui

EXEMPLO2

Objectivo: Apresentar a data no formato europeu

Query:

SELECT DATE_FORMAT( NOW( ) , "%d/%m/%y" ) AS DATA

Resultado:

Pegando novamente na nossa tabela vamos então calcular a idade das nossas pessoas ?

No lugar de utilizar o DATE_FORMAT para obter o ano actual (e assim calcular a idade) vou utilizar a funçãoYEAR(), esta função devolve-nos apenas o ano e serve plenamente para o nosso exemplo.

EXEMPLO3

Objectivo: Apresentar o nome e a idade das pessoas

Query:

SELECT nome, YEAR( NOW( ) ) - nascimento AS idade
FROM `tbl_ppware`

ou:

SELECT nome, DATE_FORMAT( NOW( ) , '%Y' ) - nascimento AS idade
FROM `tbl_ppware`

Resultado:

Assim como existe a função YEAR(), tambem existem a função DAY() para dias e MONTH() para meses, todas elas podem ser consultadas aqui

Com estas mesmas funções também podemos fazer contas com datas, por exemplo somar meses ou dias a uma determinada data

EXEMPLO4

Objectivo: Somar um dia, uma semana, um mês e um ano a data 21/12/2012, e apresenta-la por extenso (em inglês)

Primeiro que mais nada temos que converter esta data para um formato em que o SQL a reconheça, para isso utilizamos a função STR_TO_DATE()

Query:

SELECT DATE_FORMAT( STR_TO_DATE( '21/12/2012', '%d/%m/%Y' ) + INTERVAL 1
DAY + INTERVAL 1 WEEK + INTERVAL 1
MONTH + INTERVAL 1 YEAR, '%D %M of %Y' ) AS DATA

Resultado:

EXEMPLO5

Objectivo: Obter a media de idades das pessoas que temos na nossa base de dados

A media consiste em somar todos os elementos e no fim dividir pelo numero desses mesmos elementos, logo e isso mesmo que teremos que fazer, para obter a idade das pessoas que temos na nossa tabela vamos utilizar a mesma forma que utilizamos no EXEMPLO3, de seguida utilizaremos a função SUM(), para somar todos os resultados obtidos, e por fim dividimos pelo numero de elementos utilizados, para obter o numero desses exemplo usamos a função COUNT()

Query:

SELECT SUM( YEAR( NOW( ) ) - nascimento ) / COUNT( * ) AS media
FROM `tbl_ppware`

Resultado:

Espero que tenham gostado do artigo desta semana, para a semana há mais.

Vamos dar uns toques de SQL? V

Criado por Pedro Pinto em 25 de Junho de 2011 | 7 comentários

Por Pedro Gaspar para o PPLWARE

Depois de termos aprendido algumas das funções mais básicas de sql, esta semana vamos aprender a agrupar resultados utilizando o DISTINCT e o GROUP, alem disso também vamos aprender a contar os resultados obtidos com recurso ao COUNT

Para apresentar alguns exemplos utilizei a tabela que temos vindo a usar nas ultimas semanas com os seguintes dados:

Por vezes temos dados repetidos na nossa tabela e queremos que eles apareçam apenas uma vez na listagem, pois esta semana vamos aprender duas formas de o fazer
Começando pelo DISTINCT

Sintaxe de Utilização

SELECT DISTINCT nome_coluna(s)    
FROM nome_tabela

O DISTINCT é a forma mais simples de o fazer, mas também podemos utilizar o GROUP
Sintaxe de Utilização

SELECT nome_coluna(s)
FROM nome_tabela
GROUP BY coluna1 [, coluna2, coluna3]

Nada melhor para explicar todo o conceito do que utilizar alguns exemplos:

EXEMPLO1
Objectivo: Apresentar a lista de cores de cabelo
Query utilizando o DISTINCT:

SELECT DISTINCT cor_cabelo
FROM `tbl_ppware`

Query utilizando o GROUP:

SELECT cor_cabelo
FROM `tbl_ppware`
GROUP BY cor_cabelo

Resultado:

Apesar de as duas funções apresentarem o mesmo resultado o seu funcionamento e totalmente diferente, sendo que com o GROUP conseguimos fazer queries muito mais complexas do que com o DISTINCT.

Vamos agora aprender a função COUNT para mais à frente utiliza-la em conjunto com o DISTINCT e o GROUP
O COUNT permite-nos contar o numero de registos de uma determinada coluna.

Sintaxe de utilização

SELECT COUNT(nome_coluna)
FROM nome_tabela

EXEMPLO2
Objectivo: Apresentar o numero de registos que estão na tabela tbl_pplware
Query:

SELECT COUNT( * )
FROM `tbl_ppware`

(como não queremos seleccionar nenhum campo em particular podemos utilizar o ‘*’, mas poderíamos ter utilizado um campo qualquer para o efeito)
Resultado:

Para não aparecer ‘COUNT(*)’ no nome do campo da query que acabamos de fazer podemos utilizar o AS de forma a dar o nome que quisermos ao campo, vejamos o mesmo exemplo mas utilizando o AS
Query:

SELECT COUNT( * ) AS total
FROM `tbl_ppware`

Resultado:

EXEMPLO3
Objectivo: Apresentar o numero das diferentes cores de cabelo existentes
Query:

SELECT COUNT( DISTINCT cor_cabelo ) AS cores
FROM `tbl_ppware`

Resultado:

EXEMPLO4
Agora vamos juntar tudo o que aprendemos até este ponto.
Objectivo: Apresentar as cores de cabelo existentes e ao mesmo tempo mostrar quantas vezes existe cada uma
Query:

SELECT cor_cabelo, COUNT( * ) AS total
FROM `tbl_ppware`
GROUP BY cor_cabelo

Resultado:

Espero que tenham gostado da explicação sobre sql desta semana, para a semana há mais.

Vamos dar uns toques em queries SQL?

Criado por Pedro Pinto em 28 de Maio de 2011 | 39 comentários

No âmbito de um projecto que estou a acompanhar, para desenvolvimento de um serviço Web desenvolvido em PHP e base de dados MySQL , lembrei-me que nunca tínhamos criado artigos com dicas sobre a linguagem SQL (Structured Query Language) no pplware. Nesse sentido, decidi arregaçar as mangas e, quem sabe, este não será o primeiro artigo de uma rubrica sobre SQL.

Para quem não sabe, o SQL é uma das linguagens mais usadas no âmbito de aplicações que assentam em bases de dados. De uma forma muito simples, dentro de uma base de dados temos tabelas e dentro das tabelas temos registos. Imaginem por exemplo que têm uma base de dados com uma tabela que possui registos sobre pessoas. Essa tabela tem os campos (id_pessoa, nome, telefone, cor do cabelo, idade, morada). Através da linguagem SQL podemos efectuar queries (consultas) e saber por exemplo quantos registos temos na tabela, quantas pessoas têm o cabelo preto, quantas pessoas têm idade superior a 25 e inferior a 30, etc.

A linguagem SQL foi desenvolvida pela IBM em meados dos anos 70 e depois foi regulada por normas ANSI e ISO.De uma forma geral, através do SQL podemos eliminar, inserir e modificar registos numa base de dados relacional.

Está percebido para que serve a linguagem SQL? Espero que sim! Vamos agora começar por ensinar como usar o comando SELECT.

O comando SELECT permite seleccionar informação de uma ou várias tabelas.

Sintaxe de utilização

SELECT nome_campo FROM nome_tabela;

Para o presente exemplo, decidi criar uma base de dados com o nome db_pplware e com uma tabela com o nome tbl_pplware com os seguintes campos e dados:

Alguns exemplos

EXEMPLO 1

Objectivo: Seleccionar todos os dados

Query:

SELECT * FROM tbl_pplware;

Resultado: o resultado é o mesmo da tabela apresentada anteriormente.

EXEMPLO 2

Objectivo: Seleccionar apenas os nomes

Query:

SELECT nome FROM tbl_pplware;

Resultado

EXEMPLO 3

Objectivo: Seleccionar nome e morada

Query

SELECT nome, morada FROM tbl_pplware;

Resultado

Deu para perceber? Se calhar, então o melhor mesmo para agora experimentar numa base de dados a sério. Para tal, o site w3schools.com disponibiliza um pequeno serviço onde podemos por em prática alguns comandos sobre SQL. Para testarem ó comando SELECT basta aceder ao serviço aqui e têm disponível a seguinte tabela para testarem as vossas queires.

Para finalizar, apenas gostava de saber qual a vossa opinião sobre esta rubrica. Parece-vos útil e importante? O objecto é começar do “zero” e ir apresentando alguns dos muitos comandos que a linguagem SQL disponibiliza. Aguardo o vosso feedback.

Artigos relacionados

• Cábula” do MySQL

Site do MySQL atacado via SQL Injection

Criado por Pedro Pinto em 29 de Março de 2011 | 42 comentários

Parece que o feitiço se virou contra o feiticeiro, mas segundo informação disponibilizadas no site Techie Buzz, a página oficial do projecto MySQL (motor de base de dados bastante popular) foi atacado através de um mecanismo denominado de SQLInjection (alguns sites referem que foram utilizados mecanismos mais elaborados como XSS e blind SQLi).

Ao que tudo indica, além do ataque ter sido efectuado com sucesso foram ainda “roubados” dados importantes da empresa. O ataque ao que consta foi realizado através de uma página de consulta de cliente, tendo já sido divulgadas algumas passwords, inclusive a de um Director de produtos do MySQL com apenas 4 dígitos. Algumas passwords de administradores do serviço blogs.mysql.com foram também já tornadas públicas.

Mas como funciona um ataque de SQL Injection?

O SQL injection tal como o nome indica é um mecanismo que permite  “injectar” instruções SQL dentro de uma consulta (query) realizada à base de dados. Desta forma, o hacker pode “construir” a query SQL e obter informações privilegiadas que se encontram guardadas na base de dados (ex. passwords de utilizadores).

Imaginem por exemplo um formulário HTML para autenticação de um utilizador num determinado site. Depois do utilizador carregar no botão enviar, é executado no MySQL a seguinte query SQL:

SELECT user,passwd FROM users WHERE user = 'pplware' AND passwd='123456'

Os dados user e passwd são passados via POST numa query do tipo:

SELECT user,passwd FROM users WHERE user = '$user' AND passwd='$passwd';

Agora imaginem que é um hacker e que no campo password colocou:

'123456';DROP TABLE users;

Neste caso a query ficava algo do tipo:

SELECT user,passwd FROM users WHERE user =  'pplware' AND passwd='123456';DROP table users;

Veja uma demonstração de como funciona o mecanismo SQL Injection:

Caso seja um utilizador com conta no site mysql.com é aconselhável que proceda rapidamente à alteração da sua password.