SQL

• Comentarios, como en C: /* … */

CREATE DATABASE nombre

CREATE DATABASE bookstore;

CREATE TABLE nombre (columna tipo propiedades, columna tipo propiedades…)

CREATE TABLE books (
  rec_id INT, 
  title VARCHAR(50),
  author VARCHAR(50)
);

ALTER TABLE nombre modificiación, modificación…
Las modificaciones posibles:

• ADD COLUMN columna tipo propiedades
• CHANGE COLUMN columna nuevo_nombre tipo propiedades

ALTER TABLE books
  CHANGE COLUMN rec_id rec_id INT AUTO_INCREMENT PRIMARY KEY,
  CHANGE COLUMN author author_id INT,
  ADD COLUMN description BLOB;

• Enteros: BIGINT → 8bytes, INT → 4bytes, MEDIUMINT → 3bytes, SMALLINT → 2bytes, TINYINT → 1byte
• Reales: DECIMAL[(precisión)] → precisión + 2bytes, = DEC, DOUBLE[(tamaño de muestra, precisión)] → 8bytes, FLOAT → 4bytes
• Texto: VARCHAR(tamaño), LONGTEXT, TEXT, CHAR, TINYTEXT,
• Binarios: BLOB = TEXT BINARY, LONG BLOB = LONGTEXT BINARY
• Tiempo: DATE → yyyy-mm-dd (3bytes), DATETIME → yyyy-mm-dd hh:mm:ss (8bytes), TIME → hh:mm:ss (3bytes), TIMESTAMP → yyyymmddhhmmss (4bytes), YEAR → yyyy (1byte)
• Enumeraciones: ENUM, SET

Las enumeraciones se asignan entre parentesis un grupo de valores que aceptan, estos entre comillas simples:

• ENUM ('yes', 'no')
• ENUM('novel','poetry','drama')

Si se le asigna un valor que no existe provoca un warning e inserta un valor en blanco. Cada valor insertado recibe un número, siendo el primero 1, el segundo 2… Y el valor en blanco un 0. Por lo que puedes hacer selects preguntando por el valor numérico o el valor dado.
Los ENUM ocupan: 1 byte si tienen menos de 255 elementos o 2 bytes si tienen menos 65535. Los SET, en cambio, ocupan mucho más (1-8 elementos: 1byte, 9-16 elementos: 2 bytes… 33-64 elementos: 8 bytes).

• AUTO_INCREMENT
• PRIMARY KEY
• NOT NULL
• UNSIGNED (numéricos)
• ZERO FILL (numéricos)
• BINARY (texto)

La sintaxis para definir una foreign key en MySql es:

[CONSTRAINT símbolo] FOREIGN KEY (nombre_columna, ...)
  REFERENCES nombre_tabla (nombre_columna, ...)
  [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
  [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

CREATE TABLE publicaciones (isbn VARCHAR(10) PRIMARY KEY, id_editorial INT, FOREIGN KEY (id_editorial) REFERENCES editoriales(id));

Un ejemplo para añadir una foreign key tras haber creado la tabla:

ALTER TABLE venta ADD FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente);

DROP TABLE nombre

DROP TABLE books;

Opcionalmente puedes añadir IF EXISTS tras TABLE, de esa forma si la tabla no existe el comando no dará error:

DROP TABLE IF EXISTS books;

• Creación de una tabla con varias primary keys y varias foreign keys:

CREATE TABLE rel_autores_publicaciones (id_autor INT, isbn VARCHAR(10), PRIMARY KEY (id_autor, isbn), FOREIGN KEY (id_autor) REFERENCES autores(id), FOREIGN KEY (isbn) REFERENCES publicaciones(isbn));
 
CREATE TABLE diccionarios (isbn VARCHAR(10) PRIMARY KEY, id_idioma1 INT NOT NULL, id_idioma2 INT, FOREIGN KEY (isbn) REFERENCES publicaciones(idiomas), FOREIGN KEY (id_idioma1) REFERENCES idiomas (id), FOREIGN KEY (id_idioma2) REFERENCES idiomas(id));

INSERT INTO tabla (campo1, campo2…) VALUES (valorNumerico, 'valorString'…)

INSERT INTO authors (author_last, author_first, country) VALUES('Vernon','Olympia','USA');

UPDATE tabla SET campo1=valor1, campo2=valor2, … [WHERE columnaN=valorN]

UPDATE City SET Population=5000000 WHERE Name='JoanBrossa';
UPDATE City SET Population=5000000 WHERE Population<1000000;

SELECT campos FROM tablas [WHERE condicion] [ORDER BY campoOrden tipoOrden] [LIMIT límite]

• Los campos pueden ser expresados como:
  • Nombres de campos de una tabla separados por comas.
  • tabla.campo si queremos hacer un select de campos de distintas tablas.
  • Usaremos * si queremos decir todos.
  • Si queremos que un campo, cuando se muestre, su cabecera sea distinta a la que tiene realmente haremos: campo AS nombre.
• Las tablas…
  • Si indicamos que se muestren campos de varias tablas, todas estas tablas deben de aparecer aquí.
  • Si queremos usar un alias para los nombres de tablas para que así al elegir los campos sea más sencillo usaremos tabla AS otro_nombre, luego en los campos tenemos que usar otro_nombre.campo.
• Las condiciones…
  • Las separaremos por AND (&&), OR (||), NOT (!), XOR.
  • Realizaremos las comprobaciones con:
    • Operadores: =, !=, <>, ⇐, >=, <, >
    • valor BETWEEN valor1 AND valor2
    • valor IN (valor1, valor2…) o con NOT IN
    • valor1 LIKE valor2 o con NOT LIKE
    • [En: MySQL] Expresiones Regulares: REGEXP, RLIKE o NOT REGEXP.
• Para la ordenación de campos…
  • Se elige el campo por el cual se ordenará la select.
  • El tipoOrden pueden ser: ASC, DESC, si no se indica tipoOrden este será ASC (ascendente).
  • Puedes indicar varias ordenaciones separando estas por comas.
• El límite es un número que indica el máximo de elementos que han de mostrarse.
  • Podemos indicar dos números, el primero los registros anteriores a los mostrados y el segundo cuantos mostraremos. Por ejemplo, LIMIT 3, 2, mostraría el 4º y el 5º registro.
• Grupos…
  • Utilizando la clausula GROUP BY nos mostrará los resultados agrupados por el campo indicado tras esta.

SELECT * FROM books;
SELECT title, description FROM books;
SELECT title AS titulo FROM books;
SELECT rec_id, title, description FROM books WHERE genre = 'novel';
SELECT * FROM books ORDER BY title, pub_year LIMIT 20, 10;
SELECT books.title, a.name FROM books, authors AS a;
SELECT * FROM autores WHERE nombre REGEXP 'r{2}';

Imaginemos una BD donde tenemos una relación de películas\director, para sacar películas por director haremos:

SELECT title, name FROM movies m, directors d WHERE m.director = d.id;

Con JOIN sería:

SELECT title, name FROM movies m INNER JOIN directors d ON (m.director = d.id);

Los joins funcionan de la siguiente manera:

• Teniendo 2 tablas (T1 y T2) con unos campos relacionados vamos a ver lo que devolverían los distintos joins (la parte azul de las tablas).

• INNER JOIN

• OUTER JOIN

• LEFT JOIN

• LEFT OUTER JOIN

En el siguiente ejemplo se buscan todos los contactos que puede ver el trabajador 1 (los suyos y los que son públicos (visibilidad = 1)):

SELECT * FROM Contactos 
  WHERE Contactos.id IN (SELECT Contactos.id FROM Contactos, RelContTrab WHERE RelConTrab.idCon = Contactos.id AND RelConTrab.idTrab = 1)
  OR Contactos.id IN (SELECT Contactos.id FROM Contactos WHERE Contactos.Visibility = 1);

También podemos pedir campos que estén o no en una lista:

SELECT * FROM tags WHERE id NOT IN (SELECT id FROM Elements);
SELECT * FROM tags WHERE id IN (3, 4, 5);

• count devuelve el número de registros de una serie de campos en una tabla:

SELECT COUNT(*) FROM users;

• sum devuelve la suma de los valores de un campo numérico en una consulta:

SELECT ciudad.nombre, SUM(pueblo.hab) FROM pueblo, ciudad WHERE (pueblo.id_ciudad = ciudad.id) GROUP BY (ciudad.nombre);

Teniendo dos tablas:

CREATE TABLE b (ca INT, cb INT);
CREATE TABLE c (xa INT, xb INT);

Insertamos en la tabla b unos datos cb con un identificador ca (que puede estar repetido), ahora queremos insertar en la tabla c la suma de los valores cb con un mismo identificador:

INSERT INTO c (xa, xb) SELECT ca, SUM(cb) FROM b GROUP BY ca;

• No usar correctamente los índices; Deberían de tener índices obligatoriamente las foreign keys, los campos utilizados en WHERE…
• No apoyarse en la integridad referencial, si la DB lo permite (por ejemplo las foreign keys) esta debería ser usada siempre.
• Utilizar claves naturales, estas son claves únicas del mundo real (código de la seguridad social, código de productos…) pero no tienen sentido en la DB, se inventaron para identificar el elemento fuera de la DB y es preferible utilizar campos de autoincremento.
• Escribir consultas que requieran DISTINCT para funcionar, no es que la clausula DISTINCT sea incorrecta es que apunta a que pueden haber datos duplicados en la base de datos.
• Preferir agregación sobre joins, las operaciones de agregaciones (como GROUP BY) son sumamente lentas comparadas con joins. Un ejemplo:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Query time: 0.312s
------------------------------------------------------

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Query time: 0.016s

• No securizar las consultas, el usuario podría introducir código malicioso.
• No utilizar prepared statements, estos son consultas compiladas en la DB a las que les pasas parámetros (por ejemplo, según la plataforma, utilizar SELECT * FROM users WHERE username = ? o SELECT * FROM users WHERE username = :username en vez de SELECT * FROM users WHERE username = 'bob'). Cada vez que una DB moderna recibe una nueva consulta la ha de compilar, si la consulta ya “la ha visto antes” podrá cachear la ejecución además de que puede ser importante a la hora de realizar estadisticas sobre las consultas utilizadas y evita el código malicioso.
• No realizar analisis de rendimientos, como el de las consultas anteriores.
• Utilizar condiciones con OR en consultas, las condiciones AND restingen los datos mientras que los OR los hacen crecer y esto desfavorece al motor de la DB.

Bad:
... WHERE a = 2 OR a = 5 OR a = 11
Better:
... WHERE a IN (2, 5, 11)

• No simplificar consultas complejas mediante vistas, en las DB que soportan vistas es de gran ayuda utilizarlas

Model:
    * Party: people and organisations;
    * Party Role: things those parties did eg Employer and Employer;
    * Party Role Relationship: how those roles related to each other.

Example:
    * Ted is a Person, being a subtype of Party;
    * Ted has many roles, one of which is Employee;
    * Intel is an organisation, being a subtype of a Party;
    * Intel has many roles, one of which is Employer;
    * Intel employs Ted, meaning there is a relationship between their respective roles.

So there are five tables joined to link Ted to his employer. You assume all employees are Persons (not organisations) and provide this helper view:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

And suddenly you have a very simple view of the data you want but on a highly flexible data model.

• Utilizar exclusive arcs, estos consisten en crear una tabla con dos o más foreign keys de las cuales una pueda ser not-null, esto hace que sea más complejo mantener la integridad referencial.
• No normalizar suficiente, la normalización consiste en optimizar el diseño de la DB y organizar correctamente los datos en tablas.
• Normalizar demasiado. Para cuatro conceptos no deberías tener once tablas.

Sacado de Stack Overflow