====== SQL ======
  * Comentarios, como en C: '' /* ... */ ''



===== Edición de bases de datos =====
==== Crear ====
''CREATE DATABASE nombre''
<code sql>
create database bookstore;
</code>

===== Edición de tablas =====
==== Crear ====
''CREATE TABLE nombre (columna tipo propiedades, columna tipo propiedades...)''
<code sql>
CREATE TABLE books (
  rec_id INT, 
  title VARCHAR(50),
  author VARCHAR(50)
);
</code>
==== Modificar ====
''ALTER TABLE nombre modificiación, modificación...'' \\ 
Las modificaciones posibles:
  * ''ADD COLUMN columna tipo propiedades''
  * ''CHANGE COLUMN columna nuevo_nombre tipo propiedades''
<code sql>
ALTER TABLE books
  CHANGE COLUMN rec_id rec_id INT AUTO_INCREMENT PRIMARY KEY,
  CHANGE COLUMN author author_id INT,
  ADD COLUMN description BLOB;
</code>
==== Tipos de datos ====
  * 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).
==== Propiedades ====
  * AUTO_INCREMENT
  * PRIMARY KEY
  * NOT NULL
  * UNSIGNED (numéricos)
  * ZERO FILL (numéricos)
  * BINARY (texto)
==== Las foreign keys ====
La sintaxis para definir una foreign key en MySql es:
<code sql>
[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}]
</code>
<code sql>
create table publicaciones (isbn varchar(10) primary key, id_editorial int, foreign key (id_editorial) references editoriales(id));
</code>
Un ejemplo para añadir una foreign key tras haber creado la tabla:
<code sql>
ALTER TABLE venta ADD FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente);
</code>
==== Eliminar ====
''DROP TABLE nombre''
<code sql>
DROP TABLE books;
</code>
Opcionalmente puedes añadir ''IF EXISTS'' tras ''TABLE'', de esa forma si la tabla no existe el comando no dará error:
<code sql>
DROP TABLE IF EXISTS books;
</code>
==== Ejemplos ====
  * Creación de una tabla con varias primary keys y varias foreign keys:
<code sql>
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));
</code>

===== Edición de datos =====
==== Insertar ====
''INSERT INTO tabla (campo1, campo2...) VALUES (valorNumerico, 'valorString'...)''
<code sql>
INSERT INTO authors (author_last, author_first, country) VALUES('Vernon','Olympia','USA');
</code>
==== Modificar ====
''UPDATE tabla SET campo1=valor1, campo2=valor2, ... [WHERE columnaN=valorN]''
<code sql>
UPDATE City SET Population=5000000 WHERE Name='JoanBrossa';
UPDATE City SET Population=5000000 WHERE Population<1000000;
</code>
==== Eliminar ====


===== Consulta de datos =====

==== Selects ====
''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.
=== Ejemplos ===
<code sql>
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}';
</code>










==== Selects avanzados ====
=== joins ===
Imaginemos una BD donde tenemos una relación de películas\director, para sacar películas por director haremos:
<code sql>SELECT title, name FROM movies m, directors d WHERE m.director = d.id;</code>
Con JOIN sería:
<code sql>SELECT title, name FROM movies m INNER JOIN directors d ON (m.director = d.id);</code>
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).
{{bd:join0.png?200|}}
  * **INNER JOIN**
{{bd:join1.png?200|}}
  * **OUTER JOIN**
{{bd:join2.png?200|}}
  * **LEFT JOIN**
{{bd:join3.png?200|}}
  * **LEFT OUTER JOIN**
{{bd:join4.png?200|}}


=== Selects anidados ===
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)):
<code sql>
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);
</code>
También podemos pedir campos que estén o no en una lista:
<code sql>
SELECT * FROM tags WHERE id not in (SELECT id FROM Elements);
SELECT * FROM tags WHERE id in (3, 4, 5);
</code>

==== Funciones ====
  * **count** devuelve el número de registros de una serie de campos en una tabla:
<code sql>select count(*) from users;</code>
  * **sum** devuelve la suma de los valores de un campo numérico en una consulta:
<code sql>select ciudad.nombre, sum(pueblo.hab) from pueblo, ciudad where (pueblo.id_ciudad = ciudad.id) group by (ciudad.nombre);</code>


===== Notas =====
==== Tips ====
=== Insertar los datos de un select ===
Teniendo dos tablas:
<code sql>
create table b (ca int, cb int);
create table c (xa int, xb int);
</code>
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:
<code sql>
insert into c (xa, xb) select ca, sum(cb) from b group by ca;
</code>






==== Errores comunes ====
  * **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:
<code>
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
</code>
  * **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.
<code>
Bad:
... WHERE a = 2 OR a = 5 OR a = 11
Better:
... WHERE a IN (2, 5, 11)
</code>
  * **No simplificar consultas complejas mediante vistas**, en las DB que soportan vistas es de gran ayuda utilizarlas
<code>
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.
</code>
  * **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 [[http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-app-developers/621891#621891|Stack Overflow]]