comandos útiles de MySQL Server

Querys y Comandos útiles de MySQL Server

La administración técnica de bases de datos es una tarea casi que diaria de muchos sysadmin y webmasters. Para el caso de muchos, específicamente MySQL Server. No todo en la vida laboral es phpMyAdmin, ó Webmin, ó MyWebSQL. Si dominamos a cabalidad la consola de comandos de MySQL Server, además de dominar prácticamente cualquiera de sus frontends, también podrémos optimizar operaciones de consultas complejas que son ejecutadas a diario. Por eso en en este artículo quiero compartir algunos querys y comandos útiles de MySQL Server.

La lista de comandos que publico tal vez sea corta, pero te aseguro que irá extendiendose con el tiempo.

Creación de tablas y campos


Crear campo de fecha para que guarde los registros con hora del sistema automáticamente

CREATE TABLE la_tabla fecha timestamp default current_timestamp;

Adicionar campo de fecha que funciona solo para actualizaciones de registros

ALTER TABLE LOTE ADD tu_fecha TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL;

ó

ALTER TABLE LOTE ADD tt_fecha TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL AFTER tu_fecha;

Crear una tabla

mysql> CREATE TABLE PEDIDOS (

-> id int(11) not null auto_increment,
-> documento_de_venta int(8),
-> material int(9),
-> cantidad_pendiente char(11),
-> cerrado char(7),
-> fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY(id)
-> );

Evitar guardar registros duplicados en una tabla de Mysql

Primero crea la tabla normal donde se registrarán los datos. Luego adiciona un index a esa tabla índicando el campo que MySQL evitará registrar duplicados:

ALTER TABLE tabla ADD UNIQUE INDEX(campo);

Culaquier intento de registrar información repetida, no será guardada en esa tabla.

Adicionar un campo que quede de primero en la tabla

Para este ejemplo voy a adicionar un campo de llave primaria:

ALTER TABLE nombre_tabla ADD id char(11) FIRST;

Luego:

ALTER TABLE nombre_tabla ADD PRIMARY KEY (`id`);
ALTER TABLE nombre_tabla MODIFY id INT AUTO_INCREMENT;

Modificaciones en campos y tablas MySQL


Establecer el valor inicial de un campo auto_increment

Se crea la tabla, luego se procede con el siguiente comando:

ALTER TABLE “LA TABLA” AUTO_INCREMENT=101;

Convertir tabla de InnoDB a MyISAM

ALTER TABLE nombre_de_la_tabla ENGINE=’MyISAM’;

Querys de operaciones sobre datos


Reemplazar caracteres en consultas y registros

Por ejemplo, debemos visualizar el número registrado 4.56 como 4,56:

SELECT REPLACE(el_campo,'.',',') FROM la_tabla;

Reemplazar guión por guión bajo

UPDATE tabla SET campo = REPLACE(campo, "-", '_');

Copiar información de una columna a otra

Copiamos los valores del campo “fecha” dentro del campo FECHA_CUMPLIDO y donde el campo CUMPLIDO no esté vacío. Ambos campos (fecha y FECHA_CUMPLIDO) deben tener la misma longitud.

UPDATE SOLICITUD_VISITA SET FECHA_CUMPLIDO = CAST(fecha AS CHAR) WHERE CUMPLIDO IS NOT NULL;

Copiar datos de una tabla a otra

INSERT INTO empleados(cedula, nombres, seccion) SELECT cedula, nombres, seccion FROM tabla_vieja;

Actualizar registros según el año

UPDATE SOLICITUD_VISITA SET CUMPLIDO='cumplido' WHERE year(fecha_ingreso)='2018';

Consultas – Querys y Comandos Útiles de MySQL Server


Querys para consultar campos vacíos

Cuando el campo esté vacío (NULL):

SELECT id, generico2 FROM registro WHERE generico2 IS NULL;

Cuando el campo tenga un valor vacío:

SELECT id, generico2 FROM registro WHERE generico2 = ‘’;

o integrando los dos métodos:

SELECT id, generico2 FROM registro WHERE generico2 = ‘’ OR generico2 IS NULL;

Totalizar la suma los valores de varios campos de un registro

SELECT idea, (aplicable + originalidad + seguridad + calidad) as total FROM CALIFICA_IDEA ORDER BY total DESC;

Query para consultar algunos registros excepto uno en especial (un número de consecutivo)

SELECT consecutivo, cliente_ppal, cliente_usuario, telefono FROM SOLICITUD_VISITA WHERE cliente_ppal='$cliente_principal' AND consecutivo <> '$consecutivo';

Totalizar la suma de registros por mes

SELECT MONTH(fecha) as mes, COUNT(o_fabricacion) as total FROM barras_parcial WHERE DATE(fecha) BETWEEN '2019-11-01' AND '2019-12-31' GROUP BY mes;

Consultar registros que comiencen por determinado patrón

Todos los que comiencen con el número 14:

SELECT * FROM REMISIONES WHERE remision LIKE '14%';

Ahora, todos los que No contengan la letra N y la vocal A en el campo ACCES_2:

SELECT * FROM SOLICITUD_VISITA WHERE ACCES_2 NOT LIKE '%N%' AND ACCES_2 NOT LIKE '%A%'

Con el caracter porcentaje (%) indicamos al query el patrón o la configuración del criterio

Con el carácter línea abajo ( _ ) indicamos que coincida con cualquier carácter.

Consultar un campo no null

SELECT * FROM `members` WHERE contact_number IS NOT NULL;

Consultar dos tablas en el mismo query

SELECT TA_INVENTAR.in_material, TA_MB52.in_material as material_mb52 FROM TA_INVENTAR, TA_MB52 WHERE TA_INVENTAR.in_material = TA_MB52.in_material LIMIT 10;
+-------------+---------------+

| in_material | material_mb52 |

+-------------+---------------+

| 90001681 | 90001681 |

| 90001682 | 90001682 |

| 90001771 | 90001771 |

| 90002171 | 90002171 |

| 90015118 | 90015118 |

| 90015300 | 90015300 |

| 90044931 | 90044931 |

| 90061764 | 90061764 |

| 90063410 | 90063410 |

| 90063749 | 90063749 |

+-------------+---------------+

Lo que hace este query es buscar una relación de códigos en ambas tablas, o dicho de otra forma, encontrar coincidencias de dichos códigos en ambas tablas, y mostrar su resultado.

Consultar sumar un dato contenido en varios campos

SELECT SUM(KILOS) AS TOTAL FROM BODEGA WHERE 'el dato' IN (CAMPO_1, CAMPO_2);

Consulta anidada

SELECT campo FROM tabla WHERE mi_campo IN (SELECT el_otro_campo FROM la_otra_tabla WHERE mi_otro_campo LIKE '%22%');

Visualizar tamaño de la base de datos MySQL

SELECT table_schema AS "BDD",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Visualizar tipo de tablas (InnoDB o MyISAM)

SHOW TABLE STATUS;

Visualizar claves foráneas en las tablas

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY';

Condicionales y Operadores en querys de MySQL


 
AND, && Y
OR, || O
IF Si
IFNULL Si es nulo
= Igual a
> Mayor que
>= Mayor que o igual a
< Menor que
<= Menor que o igual a
<>, != Diferente a
IS NOT NULL No es nullo
IS NULL Es nulo
BETWEEN Entre dos valores
NOT BETWEEN No esta entre los valores
IN Lista de valores
NOT IN No esta en la lista de valores
LIKE Se ajusta a un patrón
NOT LIKE No es igual al patrón
CONCAT Concatena caracteres

 

Querys y comandos para copias de seguridad de la base de datos


Hacer un backup a todas la bases de datos de MySQL
 
mysqldump --user=root --password=****** -A > /Ruta/Hacia/archivo_dump.SQL
Restaurar un backup de todas las bases de datos
 
mysql --user=root --password=****** < TODAS_LAS_BASESDEDATOS.sql

ó

mysql -u root -p < TODAS_LAS_BASESDEDATOS.sql

Realizar backup de la tabla barras_parcial, de la base de datos mi_bd, entre un periodo definido

mysqldump --user=root --password=***** --opt --where="fecha BETWEEN '2020-01-30 00:00:00' and '2020-01-31 00:00:00'" mi_bd barras_parcial > el_backup.sql

 


Valora este artículo:

Comandos útiles de MySQL Server
5,0 rating based on 12.345 ratings
Overall rating: 5 out of 5 based on 1 reviews.

 

Name
Email
Review Title
Rating
Review Content

 

Todo ok

★★★★★
“ Muy explicado”
- Jhon Hamilton

 

Comparte esto en
Publicado en Desarrollo Web.