9. Nociones de SQL

9.1. Instalación en el servidor (MySQL/MariaDB)

  • (MySQL) sudo apt install mysql-server
  • (MariaDB) sudo apt install mariadb-server

9.2. Inicio y configuración de variables

  • Iniciar servidor: mysqld (no es necesario iniciar con sudo, aunque se puede hacer. Comprueba si se inicia automáticamente al iniciar el servidor).
  • Iniciar cliente: mysql -p -u usuario
  • Parar servidor: mysqladmin -u root [-p] shutdown
  • Reiniciar servidor: sudo service mysql restart
  • (MariaDB) Archivo de configuración: /etc/mysql/my.cnf
  • Variables de servidor
    • innodb_buffer_pool_size
    • key_buffer_size

9.3. Peculiaridades de MariaDB

  • Es un “fork” (una versión adaptada) de MySQL, por lo que la gran mayoría de los comandos son iguales.
  • MariaDB no pide contraseña de “root” al instalarlo, por lo que para mejorar la seguridad hay que ejecutar el comando “sudo mysql_secure_installation” y seguir las instrucciones (si pide añadir contraseña y después vuelve a preguntar por cambiarla, acepta).
  • El servidor se inicia automáticamente.
  • Da error si nos conectamos al servidor sin “sudo”. Esto se soluciona con los siguientes comandos:
  • OPCIÓN 1 (más simple):
    $ sudo mysql -u root
    mysql> USE mysql;
    mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
    mysql> FLUSH PRIVILEGES;
    mysql> exit;
    $ sudo service mysql restart
  • OPCIÓN 2:
    sudo mysql -u root
    USE mysql;
    CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
    GRANT ALL PRIVILEGES ON *.* TO usuario@localhost;
    UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';
    FLUSH PRIVILEGES;
    exit
    sudo service mysql restart
    • Notas:
      • “mysql_native_password” (uso tradicional con contraseña específica de SQL) o “unix_socket” (a través de los usuarios del sistema Linux)
      • “unix_socket” en ubuntu o “auth_socket” en otros sistemas

9.4. Creación de la base de datos y del usuario

mysql -p -u root; #introducir contraseña, usa el puerto 3306
CREATE DATABASE nombrebase CHARACTER SET 'utf8';
CREATE USER usuario@localhost IDENTIFIED BY 'contraseña';
GRANT ALL PRIVILEGES ON nombrebase.* TO 'usuario'@'localhost'; #el asterisco indica que se incluyen todas las tablas de esa base de datos, “localhost” indica desde donde se conecta
#Método para crear el usuario y asignarlo a las bases más rápido, además permite que el usuario conceda privilegios
grant all privileges on *.* to ricardo@localhost identified by 'pass' with grant option;

9.5. Creación de la tabla que contiene los registros

mysql -p -u usuario
USE basedatos;
CREATE TABLE tabla (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, columna1 VARCHAR(100), columna2 DATE);

9.6. Mostrar las bases de datos

SHOW DATABASES; #las bases en las que el usuario tiene acceso

9.7. Mostrar las tablas

SHOW TABLES;

9.8. Borrar tabla/base de datos

DROP TABLE tabla;
DROP DATABASE base;

9.9. Mostrar columnas de una tabla

SHOW COLUMNS FROM TABLA;

9.10. Inserción de los registros en la tabla

INSERT INTO tabla (columna1) VALUES ('registro1'); #opción 1
INSERT INTO tabla SET c1 = valor1, c2 = valor2; #opción 2

9.11. Eliminación de registros

DELETE FROM tabla WHERE c2=valor2 #opción 1
DELETE * FROM tabla WHERE c2=valor2 #opción 2

9.12. Actualizar registros

UPDATE tabla SET c2=nuevovalor WHERE c1=valor

9.13. Consultas

SELECT nombre_columna (* para todas)
AS alias
FROM nombre_tabla
WHERE columna (= < > !=, IS NULL, IS NOT NULL, LIKE, BETWEEN, IN) valor (con LIKE se usan % al principio o al final del valor para indicar que busque registros que terminen o empiecen por el valor, BETWEEN se usa así: columna BETWEEN valor1 AND valor2, IN se usa: columna IN (valor1, valor2, valor3) y sustituye a múltiples OR)
ORDER BY nombre_columna DESC (por defecto es ASC de ascendiente)
LIMIT número_máx_resultados;
  • Ejemplo con subconsulta
    SELECT * FROM nombre_tabla WHERE nombre_c=(SELECT min(nombre_c) FROM nombre_tabla)
    • HAVING se usa en condiciones con agregados. Se sitúa después de GROUP BYsi éste aparece.
      SELECT COUNT(nombre_columna), col_2 FROM nombre_tabla HAVING count(nombre_columna)>2
  • COUNT(columna): muestra el número de entradas con datos o con cadenas de texto vacías, pero no los NULL.
  • OFFSET número (saltar un número de resultados)
  • SELECT DISTINCT * FROM tabla WHERE ...: selecciona los no repetidos
    • SELECT COUNT(DISTINCT columna) FROM ...
  • SELECT * FROM tabla WHERE ‘columna’>valor AND ‘columna2’<valor;
  • SELECT columna1, columna2 FROM tabla WHERE ‘columna’=“valor” OR ‘columna2’=“valor”;
  • SELECT SUM(columna1) FROM tabla;
  • SELECT AVG(columna1) FROM tabla;
  • SELECT MAX(columna1) FROM tabla;
  • SELECT COUNT(columna1) FROM tabla;
  • SELECT COUNT(*) AS “nombre”, columna2 FROM tabla GROUP BY columna2;
  • SELECT CONCAT(“Apartado: “, c1) …
  • SELECT … UNION SELECT …
    SELECT clientes.apellidos, clientes.nombre, SUM(facturas.importe) AS ventas FROM clientes INNER JOIN facturas ON clientes.cust_id=facturas.cust_id GROUP BY clientes.apellidos ORDER BY ventas DESC;

9.14. Modificar la estructura de una tabla

ALTER TABLE tabla
    ADD COLUMN
    DROP COLUMN
    ALTER columna SET DEFAULT <<valor por defecto>>

9.15. Crear y eliminar vistas

  • CREATE VIEW nombre_vista AS SELECT c1, c2 FROM nombre_tabla WHERE …
  • DROP VIEW nombre_vista

9.16. Exportar una base de datos

mysqldump -h localhost -p -u bd_usuario  --opt basedatos > ./basedatos.sql.`date +"%Y%m%d"`
  • Exportar como csv:
    select * from tabla into outfile 'export2.csv' fields terminated by ';' [optionally enclosed by '"'] lines terminated by 'rn';
    • Se ejecuta como usuario root de mysql
    • No salen los nombres de columna
    • En “enclosed ...” crea comillas en los textos y puede dar problemas con importación en MongoDB

9.17. Importar una base de datos

mysql -h [server] -p -u [username] [db_name] < basedatos_backup.sql
  • Puede necesitar usuario root de MySQL

9.18. Borrar usuario

  • drop user <<usuario>>