Mod BD UD5 Realización Consultas

De MediaWiki
Ir a la navegación Ir a la búsqueda

Ferramentas gráficas proporcionadas polo sistema xestor para a realización de consultas

Como vimos na anterior unidade didáctica, existen ferramentas gráficas (proporcionados polo sistema xestor ou por terceiros) que nos poden ser de moita axuda.

No caso de MySQL, e o MySQL Workbench o que ofrece un editor visual co que elaborar as querys, con axudas como o autocompletado, accións rápidas, uso de snippets, importación e exportación de scritps, etc.

Como dicíamos, outras ferramentas de terceiros, coma Squirrel ou HeidiSQL tamén facilitan a labor de realizar consultas de xeito gráfico.

Introdución

SQL

Existen varias divisións da linguaxe SQL para xestionar a información da base de datos.

Ímonos centrar na aprendizaxe de consultas, principalmente DML (Data Manipulation Language), deixando para máis adiante DDL (Data Definition Language), DCL(Data Control Language) e TCL (Transaction Control Language).

DML

Para os exemplos inciais partiremos dun exemplo moi básico. Unha táboa personas, con 3 campos (nome, apelido1, apelido2) e 3 filas introducidas.

Datos da táboa “personas”
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ
XOSE RUIZ GONZALEZ

Sentenza SELECT

Select!

A consulta máis basica é a seguinte: 

1 -- Uso de wildcard * para obter todos os campos. 
2 -- No from escollemos a táboa
3 SELECT * 
4 FROM personas

Devolverá:

Resultado de
SELECT * FROM personas
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ
XOSE RUIZ GONZALEZ


Pódense escoller os campos 

1 --Podemos escoller os campos
2 --separandoos por comas
3 select nome,apelido1
4 FROM personas

Devolverá:

Resultado de SELECT nome,apelido1 FROM personas
nome apelido1
MARIA PEREZ
MARIA GARCIA
XOSE RUIZ

Con DISTINCT

Cando realizamos unha consulta pode ocurrir que existan valores repetidos para algunhas columnas. Por exemplo 

1 -- O select devolve por defecto valores duplicados (de existir os mesmos)
2 SELECT nome 
3 FROM personas

Devolverá:

Resultado de SELECT nome FROM personas
nome
MARIA
MARIA
XOSE

Se queremos que no se amosen valores repetidos (por exemplo queremos saber os nomes diferentes que hai na táboa personas) utilizaremos DISTINCT. 

1 -- Con distinct non se devolven duplicados
2 SELECT DISTINCT nome 
3 FROM personas

Devolverá:

Resultado de SELECT DISTINCT nome FROM personas
nome
MARIA
XOSE

Con WHERE

A cláusula WHERE utilízase para facer filtros nas consultas; seleccionar solamente algunahs filas que cumplan unha determinada condición.

Por exemplo, seleccionar as persoas que teñen por nome MARIA 

1 -- Uso de cláusula where
2 SELECT * FROM personas
3 WHERE nome = 'MARIA'


Resultado de
SELECT * FROM personas
WHERE nome = 'MARIA'
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ

Con AND, OR, NOT

O operador AND amosará os resultados cando se cumpran as dúas condicións.

A siguiente sentencia (exemplo AND): 

1 -- Exemplo AND
2 SELECT * FROM personas
3 WHERE nome = 'MARIA'
4 AND apelido1 = 'GARCIA'

Dará o seguiente resultado:

Exemplo AND
nome apelido1 apelido2
MARIA GARCIA RODRIGUEZ

O operador OR amoará os resultados cando se cumpra calquera das dúas condicións.

A siguiente sentencia (exemplo OR): 

1 -- Exempplo OR
2 SELECT * FROM personas
3 WHERE nome = 'MARIA'
4 OR apelido1 = 'GARCIA'

Dará o seguiente resultado:

Datos da táboa “personas”
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ

Pódense combinar AND e OR (e pode ser recomendable marcar con paréntesis para ter claras as precedencias): 

1 -- Exemplo combinación AND e OR
2 SELECT * FROM personas
3 WHERE nome = 'MARIA'
4 AND (apelido1 = 'GARCIA' OR apelido1 = 'LOPEZ')

Dará o seguiente resultado:

Combinación de AND e OR
nome apelido1 apelido2
MARIA GARCIA RODRIGUEZ

Finalmente, tamén hay opción de negación co NOT 

1 -- Exemplo NOT
2 SELECT * FROM personas
3 WHERE NOT nome = 'MARIA'

Dará o seguiente resultado:

Exemplo de NOT
nome apelido1 apelido2
XOSE RUIZ GONZALEZ

Con ORDER BY

ORDER BY emprégase para ordenar os resultados dunha consulta, segundo o valor da columna especificada.

Ordénase de xeito ascendente (ASC) segundo os valores da columna por defecto: 

1 SELECT nome, apelido1
2 FROM personas
3 ORDER BY apelido1 ASC

Devolverá:

Ordenación ascendente
por apelido1
nome apelido1
MARIA PEREZ
MARIA GARCIA
XOSE RUIZ

Para ordenar por orden descendente emprégase a palabra DES: 

1 SELECT nome, apelido1
2 FROM personas
3 ORDER BY apelido1 DESC

Devolverá:

Ordenación descendente
por apelido1
nome apelido1
MARIA PEREZ
MARIA GARCIA
XOSE RUIZ

Con LIMIT

Tamén coñecida como TOP, emprégase para especificar o número de filas a amosar no resultado; é útil en táboas con moitos rexistros, para limitar o número de filas e que así sexa máis rápida a consulta (consumindo tamén menos recursos no sistema).

Esta cláusula especifícase de xeito diferente segundo o sistema de bases de datos utilizado.

Cláusula SQL TOP para SQL SERVER 

1 SELECT TOP número
2 PERCENT nombre_columna
3 FROM nombre_tabla

Cláusula SQL TOP para MySQL 

1 SELECT columna(s) 
2 FROM tabla
3 LIMIT númerofilas

Cláusula SQL TOP para ORACLE 

1 SELECT columna(s) 
2 FROM tabla
3 WHERE ROWNUM <= númerofilas

1 -- Exemplo de limit
2 SELECT * FROM personas LIMIT 2
Exemplo de limit
que devolve dous valores
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ

Con valores nulos

Con funcións agregadas

As funcións agregadas:

  1. Realizan cálculos en múltiples filas
  2. Dunha soa columna dunha táboa
  3. E devolvendo un so valor

A norma ISO define cinco (5) funcións agregadas:

  1. COUNT
  2. SUM
  3. AVG
  4. MIN
  5. MAX

Operadores

Aritméticos

Operadores aritméticos
Operador Descripción Proba o funcionamento
+ Suma Exemplo suma
- Resta Exemplo resta
* Multiplicación Exemplo multiplicación
/ División Exemplo división
% Modulo Exemplo operación modulo

De comparación

Operadores de comparación
Operador Descripción Proba o funcionamento
= Igual que Exemplo de igual que
<=> Igual que (comproba igualdad con valores NULL e NOT NULL) Exemplo de igual que con nulos
> Maior que Exemplo de maior que
< Menor que Exemplo de menor que
>= Maior ou igual que Exemplo de maior ou igual que
<= Menor ou igual que Exemplo de menor ou igual que
<> ou != non igual a (equivalente a distinto de) Exemplo de non igual a


Funcións de comparación e outros operadores
Operador Descripción Proba o funcionamento
BETWEEN ... AND ... Whether a value is within a range of values
COALESCE() Return the first non-NULL argument
GREATEST() Return the largest argument
IN() Whether a value is within a set of values
INTERVAL() Return the index of the argument that is less than the first argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Whether a value is not within a range of values
NOT IN() Whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings

Lóxicos

O exclusivo
Operadores lóxicos
Operador Descripción Proba o funcionamento
AND, && AND lóxico
NOT, ! Negación do valor
OR, || (desaconsellado) OR Lóxico
XOR XOR Lóxico

Amósase como exemplo o OR exclusivo, por ser probablemente o de máis dificil comprensión 

1 -- XOR
2 mysql> SELECT 1 XOR 1;
3         -> 0
4 mysql> SELECT 1 XOR 0;
5         -> 1
6 mysql> SELECT 1 XOR NULL;
7         -> NULL
8 mysql> SELECT 1 XOR 1 XOR 1;
9         -> 1

De asignación

Operadores de asignación
Operador Descripción Proba o funcionamento
:= Asigna un valor [https://dev.mysql.com/doc/refman/8.0/en/assignment-operators.html#operator_assign-value Exemplo de :=
= Asigna un valor (parte dunha sentenza SET) Exemplo de =


Precedencia

 1 -- Orde de precedencia de maior a menor
 2 INTERVAL
 3 BINARY, COLLATE
 4 !
 5 - (unary minus), ~ (unary bit inversion)
 6 ^
 7 *, /, DIV, %, MOD
 8 -, +
 9 <<, >>
10 &
11 |
12 = (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
13 BETWEEN, CASE, WHEN, THEN, ELSE
14 NOT
15 AND, &&
16 XOR
17 OR, ||
18 = (assignment), :=

No seguinte exemplo,podemos comprobar que a primeira consulta devolve sete, porque a multiplicación ten maior precedencia que a suma. No segundo, devolve 9, porque a precedencia é fixada cos parénteses. En caso de dúbida (e para gañar claridade e mellora lexibilidade, pódese recomendar usar sempre parénteses) 

1 mysql> SELECT 1+2*3;
2         -> 7
3 mysql> SELECT (1+2)*3;
4         -> 9

Consultas calculadas

Sinónimos

Dado o nome dun esquema (base de datos), este procedemento crea un esquema con sinónimos contento as vistas que refiren a todas as táboas e vistas do esquema orixinnal.

Isto é util para crear un nome máis corto co que referirse o esquema.

Por exemplo, supoñamos que temos un esquema de base datos da empresa Computer Associates que se chama computer_associates.

Podemos acortar o nome ó das súas siglas facendo: 

1 -- Creando sinónimos ca para esquema computer_associates
2 CALL sys.create_synonym_db('computer_associates', 'ca');

O uso de sinónimos e dependente de cada sistema xestor de base detos. O exemplo amosado é correcto para MySQL 8.0.

Consultas de resumo. Agrupamento de rexistros

Unión de consultas

Emprégase para acumular os resultados de dúas sentenzas SELECT; éstas teñen que ter o mesmo número de columnas, co mesmo tipo de dato e na mesmo orde.

Supoñamos que temos unha táboa mestres:

Datos da táboa “mestres”
nome apelido1 apelido2 idade
MARIA PEREZ GOMEZ 36
MARIA GARCIA RODRIGUEZ 59
XOSE RUIZ GONZALEZ 47

E unha táboa alumnos

Datos da táboa “alumnos”
nome apelido1 apelido2 idade
ALBA BLANCO TOURIÑO 18
BRAIS CANDAL CEDEIRA 17 
1 -- Exemplo de union que amosa todas as personas 
2 -- dunha escola (mestres e alumnos) 
3 SELECT nome, apelido1,apelido2 FROM mestres
4 UNION
5 SELECT nome, apelido1,apelido2 FROM alumnos
Datos que devolve
a UNION de
mestres e alumnos
nome apelido1 apelido2
MARIA PEREZ GOMEZ
MARIA GARCIA RODRIGUEZ
XOSE RUIZ GONZALEZ
ALBA BLANCO TOURIÑO
BRAIS CANDAL CEDEIRA

Composicións internas e externas

Subconsultas

Pola súa maior complexidade, ímos adicar unha sección de subconsultas específica para a súa explicación.

Outras funcións avanzadas

If

Datos da táboa “persoas”
nome apelido1 apelido2 idade
MARIA PEREZ GOMEZ 15
MARIA GARCIA RODRIGUEZ 18
XOSE RUIZ GONZALEZ 68 
1 -- Exemplo de IF
2 SELECT nome, idade, IF(idade>=18, "Maior de idade", "Menor de idade") AS responsabilidade
3 FROM persoas;
Resultado do IF
nome idade responsabilidade
MARIA 15 Menor de idade
MARIA 18 Maior de idade
XOSE 68 Maior de idade

Case

1 -- Exemplo de CASE
2 SELECT nome, idade,
3 CASE
4     WHEN idade > 65 THEN "Pensionista"
5     WHEN idade > 18 and idade <= 65  THEN "En idade de traballar"
6     ELSE "Menor de idade"
7 END
8 AS CategoriaLaboral
9 FROM persoas;
Resultado do CASE
nome idade CategoriaLaboral
MARIA 15 Menor de idade
MARIA 18 En idade de traballar
XOSE 68 Pensionista

Vistas

View

Este capítulo céntrase particularmente na DML (Data Manipulation Language).

As vistas, pola contra, ubicanse na DDL (Data Definition Language).

Unha vista é unha táboa virtual baseada no resultado dunha consulta (SELECT) a unha ou varias táboas. Poren, crear vistas en MySQL significa amosar información dunha fonte de orixe sen necesidade de expoñer a fonte en sí; as VIEWS non son máis que SELECT Queries.

Cada vez que un usuario consulta unha vista, o sistema de base de datos, actualiza os datos da vista, para mostrar siempre datos reais.

Algunhas vantaxes do uso de vistas:

  • Control de accesos: permite escoller qué información específicamente se desexa compartir con outros usuarios; así non terán acceso ó resto dos datos da táboa, solo as VIEWS.
  • Mellora do rendemento: podense crear queries a partir de vistas que extraen datos de SELECT complexas, evitando ter que executar queries; e algúns SXBD permiten o que se denomina materialized views (vista materializada) que utilizan mecanismos de cacheo
  • Probas seguras: as vistas ofrecen un entorno de táboas de proba para que os desenvolvedores non poidan afectar á información real
  • Reusabilidade de consultas: gracias ás vistas non é preciso crear consultas complexas que requiran uniones de xeito repetido.
  • Mantenmento da integridade: creando aplicacións que usen as VIEWS en vez das táboas reales garantese que ditas aplicacións non 'se rompan' cando se realizan cambios na estructura da base de datos.

Partiremos da seguinte táboa:

Datos da táboa “personas”
nome apelido1 apelido2 idade
MARIA PEREZ GOMEZ 15
MARIA GARCIA RODRIGUEZ 17
XOSE RUIZ GONZALEZ 21

Supoñamos que desexamos crear unha vista que só permite amosar as personas maiores de idade (18 anos) para protexer a súa privacidade: 

1 -- Vista que conterá os datos de personas maiores de idade (18 anos)
2 CREATE VIEW mariores_idade AS
3 SELECT nome, apelido1, apelido2,idade
4 FROM personas
5 WHERE idade >= 18

Poderemos consultar os datos desa vista: 

1 -- Consultar unha vista 
2 SELECT * FROM maiores_idade
Vista con maiores de idade (18 anos)
nome apelido1 apelido2 idade
XOSE RUIZ GONZALEZ 21



Supoñamos que hai un reforma lexislativa, que fixa a maioría de idade en 16 anos. Para actualizar a vista: 

1 -- Actualización da vista para conter os datos de personas maiores de idade (16 anos)
2 REPLACE VIEW maiores_idade AS
3 SELECT nome, apelido1, apelido2, idade
4 FROM personas
5 WHERE idade => 16

Se consultamos aora os datos desa vista: 

1 -- Consultar unha vista 
2 SELECT * FROM maiores_idade
Vista con maiores de idade (16 anos)
nome apelido1 apelido2 idade
MARIA GARCIA RODRIGUEZ 17
XOSE RUIZ GONZALEZ 21

Supoñamos que as grandes empresas presionan, e xa on é obrigatorio protexer a privacidade dos menores, e non fai falla esa vista: 

1 -- Borrado de vista
2  DROP VIEW maiores_idade

Referencias e créditos

Boletíns

Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=Mod_BD_UD5_Realización_Consultas&oldid=19088»