Apuntes, tips en MySQL (Resumen)

# -------------------------------------------------------------------
# - MySQL tips, Apuntes
# -------------------------------------------------------------------
  
# -------------------------------------------------------------------
# Comandos comunes acceso rápido
  
# Iniciar sesión
$ mysql -u wilzonmj -p    # Lo normal que se usa
$ mysql -h localhost -u wilzonmj -p
$ mysql -h 192.168.25.2 -u wilzonmj -p  (ejemplo: si es que es remoto)
 
# --
# Detener MySQL
# En Windows
mysqladmin -u wilzonmj shutdown -p
 
# En Linux
$ service mysql start
$ service mysql stop
$ service mysql restart
# /--
 
# Ejecutar SQL
$ source /home/wilzonmj/database.sql;
 
# Cargar datos en bloque de CSV a MySQL
$ load data infile '/home/wilzon/people.txt'
 
# Cómo importar una base de datos MySQL
$ mysql -u username -p databasename < /home/wilzonmj/filename.sql
 
# Exportar a CSV
$ SELECT * FROM personas INTO OUTFILE ‘/home/wilzonmj/personas_backup.sql’;
 
# Exportar DB
$ mysqldump -u username -p databasename > /home/wilzonmj/filename.sql
 
# Exportar DB con procedimiento almacenados y funciones
$ mysqldump -u username -p --routines databasename > /home/wilzonmj/filename.sql
 
# Exportar una tabla de una base de datos
$ mysqldump -u username -p databasename tablename > /home/wilzonmj/filename.sql
 
# Exportar las estructuras de una base de datos MySQL
$ mysqldump -u username -p --no-data databasename > /home/wilzonmj/filename.sql
 
# Exportar sólo los datos de una base de datos MySQL
$ mysqldump -u username -p --no-create-info databasename > /home/wilzonmj/filename.sql
 
# Exportar varias bases de datos MySQL
$ mysqldump -u username -p --databases databasename1 [databasename2 ...] > /home/wilzonmj/filename.sql
 
# Exportar todas las bases de datos del servidor MySQL
$ mysqldump -u username -p --all-databases > /home/wilzonmj/filename.sql
 
# Renombrar Base de datos
$ RENAME DATABASE nombre_viejo TO nombre_nuevo;
 
# Asignar privilegios
$ GRANT ALL PRIVILEGES ON  `nombrebasededatos` . * TO  'usuario01'@'localhost' WITH GRANT OPTION ;
 
# Refrescar los privilegios para ver los cambios sin reiniciar el MySQL
$ FLUSH PRIVILEGES;
 
# Crear base de datos
$ CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
 
$ CREATE SCHEMA `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; (así lo hace en Workbench)
 
# --
# Ejemplo de dar todos los permisos sobre una base de datos, GRANT OPTION Y eliminar todos los privilegios y el GRANT OPTION
# Se le da el privilegio sobre DB con GRANT OPTION  (recomendable no dar permidos de GRANT OPTION ya que admin nada mas debe tenerlo)
$ GRANT ALL PRIVILEGES ON  nombreDB . * TO  'usuario01'@'localhost';  (Recomendable)
$ GRANT ALL PRIVILEGES ON  nombreDB . * TO  'usuario01'@'localhost' WITH GRANT OPTION;   (Esto se ejecuta en PhpMydqmin)
 
# Aca se elimina todos los privilegios
$ REVOKE ALL PRIVILEGES ON  nombreDB . * FROM  'usuario01'@'localhost'; 
 
# Aca se elimina el GRANT OPTION
$ REVOKE GRANT OPTION ON  nombreDB . * FROM  'usuario01'@'localhost'; 
# /--
 
 
# Aumentar tiempos de ejecuacion y memorias
$ mysql --max_allowed_packet=32M
 
# Mostrar variables:
$ SHOW VARIABLES;
$ SHOW VARIABLES LIKE 'auto_inc%';
 
# Consultar puerto y nombre el hostname
# SHOW VARIABLES WHERE Variable_name IN ('hostname','port')
 
# Utiliza el PROCEDURE ANALYSE() de MySQL
# Esta es una característica muy desconocida de MySQL pero de gran utilidad. Se añade tras una consulta y lo que hace es procesarla y devolver estadísticas sobre cada campo seleccionado. Dice máximos, mínimos, medias y lo más importante, propone el tipo de campo óptimo para cada columna.
$ SELECT * FROM `persona` WHERE 1 PROCEDURE ANALYSE();
 
 
 
 
 
# -------------------------------------------------------------------
# Importar - Exportar: MySQL - CSV
# Donde
 # INTO OUTFILE : directorio y archivo donde se guardara.
 # FIELDS TERMINATED BY : separador de cada campo.
 # ENCLOSED BY : delimitación de campo. (columna encerrado por ejem:: comilla)
 # LINES TERMINATED BY : separador de fila.
 
 
# Importar de CSV a MySQL
# Tu base de datos y el archivo CSV tienen que tener la misma colation  (si es UTF8 tu DB, el archivo debe ser igual)
$ load data infile '/home/wilzonmj/persona.csv’ into table persona;        # Lo normal
$ load data local infile '/home/wilzonmj/persona.csv’ into table persona;  # Me funciona en sitios q tiene permisos restringidos
 
# Si se desea ignorar un linea, por ejemplo para q no inserte la cabecera se hace asi (al poner ignore 1, le decimos que ignore la primera fila):
load data infile '/home/wilzon/persona.csv’ into table persona
ignore 1 lines;
 
 
# Importar CSV con parámetros más completos
$ load data infile '/home/wilzonb/people.csv'
    into table persona
    fields terminated by '\t'
    lines terminated by '\n'
    ignore 1 lines;
 
 
 
# -------------------------------------------------------------------
# Exportar a CSV desde MySQL
 
# Básico
SELECT * FROM personas INTO OUTFILE '/home/wilzon/personas_backup.sql';
 
# Exportacion más completa:
SELECT * FROM personas
 INTO OUTFILE '/home/wilzonmj/persona.csv'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'";

# --
# Ejemplo 01: completamente funcional de como exportar con PHP y MySQLI
/**
 * Exportar a CSV
 */
$mysqli = new mysqli("localhost", "wilzonmj", "wilzon_mj", "base_de_datos");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Error de conexión: %s\n", mysqli_connect_error());
    exit();
}
 
$dateToday = date('d-m-Y-h-i-s');
$comilla = '"';
$archivo = "/homewilzonmj/$dateToday.csv";
 
$sql = "select *
INTO OUTFILE '$archivo'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '$comilla' ESCAPED BY '$comilla'
ENCLOSED BY '$comilla'
LINES TERMINATED BY '\n'
FROM persona";
 
$resutl = $mysqli->query($sql);
if ($resutl)
    $mysqli->error;
else
    echo 'Exportado correctamente :)';
 
$mysqli->close();
# /Fin ejemplo

# Ejemplo 02: Con nombre de campos (esta es la más acertada)
# Si sale error generralmente es por q el campo esta vacio, en ese caso hay q poner 0 o null o lo q sea para que trabaje correctamente.
SELECT *
INTO OUTFILE '/home/wilzonmj/persona.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM (SELECT ID
   , CASE
       WHEN Name IS NULL THEN '' ELSE Name
     END AS Name
   , CASE
       WHEN CountryCode IS NULL THEN '' ELSE CountryCode
     END AS CountryCode
   , CASE
       WHEN District IS NULL THEN '' ELSE District
     END AS District
   , CASE
       WHEN Population IS NULL THEN '' ELSE Population
     END AS Population
  FROM City) Subquery;
 
 
 
# -------------------------------------------------------------------
# Consultar registro duplicado
SELECT email, COUNT(*) Total
FROM persona
GROUP BY email
HAVING COUNT(*) > 1;
 
# Ordenandolo mejor :)
SELECT
    nombre_campo, count(*)
FROM
    nombre_tabla
GROUP BY
    nombre_campo
HAVING
    count(*) > 1;
     
     
# Consultar cantidad caracter de un registro
SELECT * FROM persona where CHARACTER_LENGTH(nombre)=6;
 
 
# Estos busca cualquiera de esos valores lo q esta dentro del paréntesis
SELECT `id` , `nombre`
FROM `persona`
WHERE `nombre`
IN (
'Wilzon', 'Lili', 'Pedro'
)
LIMIT 0 , 30;
 
 
// Para la consulta sucia(hace consultas aun que la tabla esta siendo modificado, etc. sin importar nada este te ayuda hacer consultas por es mas rapido)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 SELECT * FROM tabla;
COMMIT;
 
 
 
# -------------------------------------------------------------------
# - Crear una clave foránea, campo unico en MySQL
# -------------------------------------------------------------------
# En esta instrucción SQL se modifica la tabla1 para añadirle una clave foránea al campotabla1 indicándole a que tabla y campo hace referencia.
ALTER TABLE tabla1 
ADD CONSTRAINT nombreclaveforanea 
FOREIGN KEY (campotabla1) 
REFERENCES tablaquereferencia (campoquereferencia) 
ON DELETE NO ACTION
ON UPDATE NO ACTION;
 
# Eliminar una clave foránea
ALTER TABLE nombretabla DROP FOREIGN KEY nombre_clave_foranea;
 
# Agregar campo unico
ALTER TABLE  nombretabla ADD UNIQUE (nombrecampo);

- Insertar valores a una tabla desde otra

INSERT INTO tabla1 (
        titulo
        ,slug
        ,descripcion
        ,user_id
        )
SELECT titulo
       ,slug
       ,descripcion
       ,user_id
FROM tabla2

- MySQL, Server SQL Modes

# -------------------------------------------------------------------
# - MySQL, Server SQL Modes
# -------------------------------------------------------------------
  
# -------------------------------------------------------------------
# Si tienes una tabla en la que por defecto tienes '0000-00-00 00:00:00', en el modo estrictio no te permite crear esta tabla.
# A partir de la referencia: NO_ZERO_DATE - En modo estricto, no permitir '0000-00-00' como una fecha válida. Todavía se puede insertar fechas con la opción IGNORE. Cuando no está en modo estricto, la fecha se acepta pero se genera una advertencia.
  
# Para que nos permita crear la tabla, primero debe ir estas instrucciones
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  
# Creando una tabla con fecha por defecto '0000-00-00 00:00:00'
CREATE TABLE IF NOT EXISTS `prueba` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(501) DEFAULT NULL,
  `creado` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `actualizado` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;