Usuari:Sergi/Temari/Tema 39 Lenguajes para la definición y manipulación de datos en sistemas de base de datos relacionales. Tipos. Características. Lenguaje SQL.

De Acacha

Dreceres ràpides: navegació, cerca

Contingut

SQL (Standard Query Language)

Anatomia d'una declaració SQL

Imatge:Sql statement anatomy.png

Subconjunts de SQL

Data Definition Language

Recursos:

Recursos:

Dades

Una de les parts més importants de la definició de dades es definir els tipus de dades.

Tipus de dades numèrics

El més típic es el suport per a nombres enters (INT, INTEGER) i variants de INT segons el rang de valors permesos.

Per exemple a MYSQL, tenim:

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT
  • BIGINT

Un altre tema és el suport per a valors decimals. A Mysql tenim:

  • DECIMAL

Alhora de dissenyar sempre hi ha un compromís entre l'espai ocupat i el marge de valors que pot prendre un camp (fet que no sempre es fàcil de predir.)


Recursos:

Tipus de dades de data i temps

A MySQL tenim:

  • DATETIME
  • DATE
  • TIMESTAMP
  • TIME
  • YEAR

Tipus de dades caràcter i cadena de caràcters

Podem utilitzar:

  • CHAR
  • VARCHAR:
  • BLOB
  • TEXT

Tipus de dades binaris

  • BINARY
  • VARBINARY

Altres

  • ENUM
  • SET

Tipus de dades d'usuari

Alguns gestors de base de dades permeten als usuaris definir els seus propis tipus de dades.

Recursos:

CREATE

Permet crear elements de la base de dades. El més habitual és crear taules: 

CREATE TABLE my_table (
  my_field1   INT,
  my_field2   VARCHAR (50),
  my_field3   DATE         NOT NULL,
  PRIMARY KEY (my_field1, my_field2) 
);

Però també podem crear bases de dades: 

 CREATE DATABASE prova;

O d'altres com CREATE INDEX o CREATE VIEW.

Recursos:

ALTER

Permet fer modificacions de l'esquema d'una taula: 

ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;

O alterar altres elements com ALTER VIEW o ALTER DATABASE.

Recursos:

DROP

Permet eliminar un element de la base de dades. El més utilitzat és: 

DROP TABLE empleats;

També podem esborrar bases de dades 

DROP DATABASE databasename;

O altres elements com índexs amb DROP INDEX o DROP VIEW.

Recursos:

RENAME. MYSQL

RENAME TABLE table1 to table2

Recursos: http://dev.mysql.com/doc/refman/6.0/en/rename-table.html

DML en SGBDR

SQL és classifica en 2 tipus:

  • SQL interactiu: llenguatge de consultes basat en l'àlgebra relacional que també té suport per a inserir, modificar i eliminar dades
  • SQL immers: En angles anomenat SQL embedded (la traducció podria ser SQL encastat "empotrat")

Aquesta classificació no ens ha de fer pensar que hi ha dos llenguatges SQL, més aviat són dos maneres d'utilitzar SQL (de forma interactiva, normalment utilitzant la consola o una aplicació d'un SGBDR, i de forma programàtica amb llenguatges de programació)

SQL Interactiu

SQL i l'algebra relacional. Clàusules SELECT(projecció), FROM(producte cartesià) i WHERE(predicats de selecció)

SQL permet totes les operacions de l'àlgebra relacional. Complitud?

  • SELECT: Equival a l'operació de projecció de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.
  • FROM: Equival a l' operació de producte cartesià de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.
  • WHERE: Equival als predicats de selecció de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.

També permet altres operacions.

SELECT

SELECT * 
FROM books
WHERE price > 100.00
ORDER BY title;

Recursos:

Tuples duplicades

SQL pot retornar tuples duplicades (cosa que estrictament no hauria de passar segons el model relacional però que passa per qüestions d'eficiència a l'hora d'eliminar duplicats). Per aquesta raó tenim les clàusules: 

ALL | DISTINCT | DISTINCTROW

SELECT DISTINCT FROM nomtaula

Operacions de conjunts. UNION, INTERSECT i MINUS

(SELECT * FROM CLIENTS)
UNION
(SELECT * FROM CLIENTS2)



(SELECT * FROM CLIENTS)
INTERSECT
(SELECT * FROM CLIENTS2)



(SELECT * FROM CLIENTS)
MINUS
(SELECT * FROM CLIENTS2)

Recursos:

Ordenació de tuples. ORDER BY

ORDER BY DESC/ASC

Exemple: 

SELECT * FROM Employees 
ORDER BY LastName, FirstName

Ordenar pot ser una operació costosa de la qual no s'ha d'abusar. Val la pena crear un index sobre el camp d'ordenació si es preveu ordenar per aquest camp sovint.

Recursos:

JOINS

Exemple 

SELECT books.title, count(*) AS Authors
FROM books
JOIN book_authors 
ON books.isbn = book_authors.isbn
GROUP BY books.title;

Tipus de join:

  • LEFT JOIN:
  • RIGHT JOIN:

Recursos:

OPERACIONS SOBRE AGRUPACIONS. GROUP BY i HAVING

Permet agrupar camps i executar operacions i aplicar condicions sobre aquestes agrupacions 

GROUP BY nom camp
HAVING where_condition

Exemple: 

SELECT DeptID, SUM(SaleAmount) FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000

Operacions:

  • Count(expression)
  • Sum(expression)
  • Min(expression)
  • Max(expression)
  • Avg(expression)

Segons els SGBDR podem disposar d'altres operacions (operacions estadístiques, operacions matemàtiques complexes, etc.)

Recursos:

SUBCONSULTES

Exemple: 

SELECT DISCTINCT * FROM clients WHERE nifclient IN
(SELECT nifclient FROM factures WHERE importfactura > 2000)

No tots els SGBDR i versions dels SGBDR ho suporten (per exemple MySQL no ho va suportar fins a la versió 4.1)

INSERT

Exemple: 

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

Recursos:

UPDATE

Exemple: 

UPDATE T SET C1 = C1 + 1 WHERE C2 = 'a'

Recursos:

DELETE

Exemple: 

DELETE FROM pies WHERE flavour='Lemon Meringue';

Recursos:

MERGE

Exemple:

Recursos: http://en.wikipedia.org/wiki/Merge_%28SQL%29


TRUNCATE

Exemple:

Recursos:

Borra una taula de la base de dades 

TRUNCATE TABLE table_name

Recursos:


TRANSACCIONS. COMMIT i ROLLBACK

START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Recursos:

SQL immers

En angles anomenat SQL embedded (la traducció podria ser SQL encastat "empotrat") és una forma d'utilitzar SQL basada en "encastar" porcions de codi SQL en llenguatges de programació

Cursors

SQL dinàmic

Data Control Language

Recursos:

GRANT

GRANT ALL ON *.* TO 'someuser'@'somehost';


REVOKE

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

QUEL

QBE (Query By Example)

Obtingut de «http://acacha.org/wiki_privada/index.php/Usuari:Sergi/Temari/Tema_39_Lenguajes_para_la_definici%C3%B3n_y_manipulaci%C3%B3n_de_datos_en_sistemas_de_base_de_datos_relacionales._Tipos._Caracter%C3%ADsticas._Lenguaje_SQL.»
Eines de l'usuari