Programación Web: Nociones de SQL

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

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

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

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' IDENTIFIED BY '';
        GRANT ALL PRIVILEGES ON *.* TO usuario;
        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

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 IDENTIFIED BY 'contraseña';
    GRANT ALL PRIVILEGES ON nombrebase.* TO 'usuario'; #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 identified by 'pass' with grant option;

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);

6. Mostrar las bases de datos

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

7. Mostrar las tablas

SHOW TABLES;

8. Borrar tabla/base de datos

DROP TABLE tabla;
    DROP DATABASE base;

9. Mostrar columnas de una tabla

SHOW COLUMNS FROM TABLA;

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

11. Eliminación de registros

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

12. Actualizar registros

UPDATE tabla SET c2=nuevovalor WHERE c1=valor

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;

14. Modificar la estructura de una tabla

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

15. Crear y eliminar vistas

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

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

17. Importar una base de datos

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

18. Borrar usuario

  • drop user <<usuario>>