12 KiB
AYUDA MYSQL
===========
Acceso consola de mysql/mariadb
./mysql u- root (si no tiene password)
./mysql u- root -p
Backups mysqldump desde terminal
./mysqldump -u root -p nombre_base_datos > backup.sql
./mysqldump -u root -p --all-databases > all_databases_backup.sql
mysqldump -h localhost -u root -p --port=3307 mi_base > backup.sql Para establecer puerto 3307
Restaurar un backup mysql/mariadb
Terminal del sistema operativo
mysql -u root -p nombre_base_datos < backup.sql
mysql -h localhost -u usuario -p --port=3307 nombre_base < backup.sql Estableciendo puerto
Consola mysql/mariadb
show databases; Mostrar bases de datos
use database_name; Seleccionar base de datos
drop database database_name Borrar base de datos
create database database_name Crear base de datos
DROP USER 'juan'@'localhost'; Borrar usuario
DROP USER 'juan'@'%'; Si el usuario se conecta desde cualquier host (%):
SELECT user, host FROM mysql.user; Ver usuarios
Modificar tablas a InnoDb
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = 'nombre_base_datos' AND engine = 'MyISAM';
Luego ejecutas las sentencias generadas.
ALTER TABLE tabasunto ENGINE=InnoDB;
ALTER TABLE tabmodus ENGINE=InnoDB;
ALTER TABLE tabobjetos ENGINE=InnoDB;
ALTER TABLE taboficios ENGINE=InnoDB;
ALTER TABLE tabdetenpol ENGINE=InnoDB;
ALTER TABLE tabusers ENGINE=InnoDB;
ALTER TABLE tabregistro ENGINE=InnoDB;
Usar mysqldump en linux con Xampp
/opt/lampp/bin/mysqldump -u [usuario] -p [nombre_base_de_datos] > [archivo_salida.sql]
gzip ~/copia_seguridad_mi_basedatos.sql Comprimir la copia de seguridad
export PATH=/opt/lampp/bin:$PATH Activar mysqldump en el PATH
mysqldump -u root -p mi_basedatos > ~/copia_seguridad.sql
PhpMyAdmin
Aumentar liminte de carga. Editar php.ini (/etc/php/ c:\xampp\php)
modificar los valores:
upload_max_filesize = 2000M
post_max_size = 2000M
max_execution_time = 300
max_input_time = 300
Reiniciar apache
sudo systemctl restart apache2 Linux
Poner en mayusculas / minusculas
UPDATE productos SET categoria = UPPER(categoria); (actualizar)
SELECT id, UPPER(nombre) AS nombre_mayusculas FROM categorias; (solo visualizar)
UPDATE tabla SET columna = LOWER(columna);
UPDATE tabla SET columna = CONCAT(
UPPER(SUBSTRING(columna, 1, 1)), -- Primera letra en mayúscula
LOWER(SUBSTRING(columna, 2)) -- Resto en minúsculas
SELECT nombre, CONCAT(UPPER(SUBSTRING(nombre, 1, 1)), LOWER(SUBSTRING(nombre, 2))) AS nombre_formateado
FROM categorias; (solo visualizacion select)
Guardar datos de una tabla a otra de la misma base de datos.
INSERT INTO tabla1 (apellido, nombre, sexo) SELECT apellido, nombre, sexo FROM tabla2;
Guardar datos de una tabla a otra de la misma base de datos.
INSERT INTO tabla1 (apellido, nombre, sexo) SELECT apellido, nombre, sexo FROM tabla2;
Guardar datos de una tabla a otra en diferente base de datos.
INSERT INTO db1.tabla (rut, apellido, nombre, sexo) SELECT rut, apellido, nombre, sexo FROM dbo2.tabla;
Insert into db1.tabla select * from db2.tabla; (ambas tablas tienen que tener la misma estructura).
Actualizar datos tabla
UPDATE tabla_nombre SET column1 = value1, column2 = value2 … WHERE condicion;
UPDATE tabla_nombre SET column1 = value1, column2 = value2 … WHERE condicion LIKE ‘%hola%’;
Insertar datos en campo/s desde otra tabla
INSERT INTO tabDestino (column1, column2) SELECT columnX, columnY FROM tabOrigen;
SELECT
SELECT * FROM tabregistro WHERE registro NOT LIKE '%/%' AND fechaent BETWEEN '2020-01-01' and '2020-12-31'
SELECT * FROM tabregistro WHERE fechaent BETWEEN '2020-01-01' AND '2020-12-31'
UPDATE
UPDATE oeste_oficios SET grupo = 'oeste';
UPDATE tabregistro SET procedencia = 'ODAC Central' WHERE procedencia = 'I.C.G.'; UPDATE centro_detenpol SET padres = CONCAT (padre,' - ',madre);
UPDATE centro_detenpol SET nacFecha = CONCAT (año,mes,dia);
UPDATE centro_registro SET grupo = 'centro';
ACTUALIZAR O MODIFICAR LOS ID
SET @COUNT = 200000;
UPDATE tabregistro SET tabregistro.id = @count:= @count + 1;
ALTER TABLE tabregistro AUTO_INCREMENT=1
ACTUALIZAR SQLITE AÑADIENDO EL AÑO SQLITE
UPDATE tab_detenidos set atestado = atestado || '/' ||
SUBSTR(CAST(strftime('%Y',fecha_hora) AS TEXT), 3, 2)
WHERE atestado NOT LIKE '%/' || SUBSTR(CAST(STRFTIME('%Y', fecha_hora) AS TEXT), 3, 2);
QUITAR EL AÑO
UPDATE tab_detenidos2 SET atestado = SUBSTR(atestado, 1, LENGTH(atestado) -3) WHERE atestado LIKE "%/%"
CONCAT
UPDATE tabregistro SET juzgado=CONCAT(tipoDoc,' ',operacion) where id='1'
En sqlite en lugar de CONCATE se utiliza ||
UPDATE funcionarios_agp SET nombre_comp = (NOMBRE || ' ' || APELLIDOS) where dni='11111111'
CONTAR NUMERO DE FILAS DE UNA TABLA
SELECT COUNT(*) FROM contactos;
DUPLICAR TABLA
CREATE TABLE tabla_nueva LIKE tabla_original;
INSERTAR DATOS DE UNA TABLA A OTRA
INSERT INTO tabla_nueva SELECT * FROM tabla_original;
DUPLICAR TABLA Y INSERTAR TODOS LOS REGISTROS
CREATE TABLE tabla_nueva SELECT * FROM tabla_original;
Para sqlite:
CREATE TABLE nueva_tabla AS SELECT * FROM tabla_original
VER REGISTROS DUPLICADOS
SELECT telefono FROM clientes GROUP BY telefono HAVING COUNT(*)>1;
VER REGISTROS DUPLICADOS (CUENTA DE DUPLICADOS)
SELECT telefono, count(*) FROM usuarios GROUP BY telefono HAVING COUNT(*)>1;
BORRAR REGISTROS DUPLICADOS (TODOS)
DELETE FROM clientes WHERE telefono IN (SELECT telefono FROM clientes GROUP BY telefono HAVING COUNT(*)>1)
BORRAR REGISTROS DUPLICADOS (DEJANDO UNO) repetir varias veces ya que eliminar uno de los duplicados por cada consulta ejecutada.
DELETE FROM contactos1 WHERE id IN (SELECT * FROM (SELECT id FROM contactos1 GROUP BY nombre HAVING (COUNT(*) > 1)) AS A);
BORRAR REGISTROS DUPLICADOS CREANDO TABLA TEMPORAL
CREATE TABLE TableA_Verify AS SELECT DISTINCT * FROM TableA;
DELETE FROM TableA;
INSERT INTO TableA SELECT * FROM TableA_Verify;
DROP TABLE TableA_Verify;
BUSCAR LOS QUE ESTAN REPETIDOS
SELECT nie, count(*) FROM identidades
GROUP BY nie
HAVING COUNT(*)>1;
BORRAR REGISTROS REPETIDOS Y DEJAR SOLO UNO. SE ESCRIBE TODO SEGUIDO EN LA CONSULTA FUNCIONA COMPROBADO. OK
WITH repetidos AS (
SELECT MIN(id) as id, nie FROM identidades
GROUP BY nie
HAVING COUNT(*)>1
)
DELETE FROM identidades
WHERE id not IN (
SELECT id FROM repetidos
) and nie IN (SELECT nie FROM repetidos) ;
SQLITE
SELECT atestado, delito, count(*) FROM tab_detenidos2
GROUP BY atestado
HAVING COUNT(*)>1;
WITH repetidos AS (
SELECT MIN(id) as id, atestado FROM tab_detenidos2
GROUP BY atestado
HAVING COUNT(*)>1
)
DELETE FROM tab_detenidos2
WHERE id not IN (
SELECT id FROM repetidos
) and atestado IN (SELECT atestado FROM repetidos) and delito <> "RECLAMACIÓN JUDICIAL";
DATOS REPETIDOS
SELECT u.* FROM usuarios u WHERE telefono IN
( SELECT telefono FROM usuarios GROUP BY telefono HAVING COUNT(*)>1 );
BUSCAR DUPLICADOS
SE LISTAN TODAS LAS IDENTIDADES QUE ESTAN REPETIDAS
SELECT tabla_empleados.* FROM tabla_empleados
WHERE identidad IN (
SELECT identidad FROM tabla_empleados
GROUP BY identidad
HAVING COUNT(*)>1
) order by identidad DESC
NUMERO DE VECES QUE SE REPITE UNA IDENTIDAD
SELECT identidad, count(*) FROM tabla_empleados
GROUP BY identidad
HAVING COUNT(*)>1;
SUMAR VALORES COLUMNAS (SUM)
SELECT SUM (column_name) FROM table_name WHERE condition;
SELECT SUM (cantidad) as total FROM ventas;
ELIMINAR SALTO DE LINEA con \n por
UPDATE table SET gestiones = REPLACE(gestiones, '\n','
') WHERE id = '8072';
REPLACE
REPLACE(str, find_string, replace_with)
SELECT REPLACE(country,'K','SA') FROM publisher WHERE country='UK';
UPDATE tabregistro SET registro = REPLACE(registro, '/2020','/20');
CREATE TABLE inves.ciber SELECT * FROM ciber.principal;
UPDATE ciber SET asunto1=CONCAT(asunto,' ',tipohecho)
UPDATE ciber SET implicados=CONCAT(denunciante,' DNI ',dni,' TLFN ',telefono,'
EMAIL ',email)
UPDATE ciber SET grupo ='ciber' WHERE grupo LIKE '%tecnologicos%';
Detenpol pasar año de fecha nacimiento a varchar antes de concatenar en fecha nacimiento
INSERTAR DATOS DE UNA TABLA A OTRA
INSERT INTO inves.tabdetenpol SELECT * FROM bdcentro.centro_detenpol;
INSERT INTO inves.taboficios SELECT * FROM bdcentro.centro_oficios;
INSERT INTO inves.tabobjetos SELECT * FROM bdcentro.centro_objetos;
INSERT INTO inves.tabregistro SELECT * FROM bdcentro.centro_registro;
UPDATE tabregistro SET registro = CONCAT(registro,'/20') where id = '6976'
INSERT INTO tabregistro SELECT * FROM ciber;
INSERT INTO tabregistro SELECT * FROM oeste_registro;
iNSERT INTO tabdetenpol SELECT * FROM ufam_detenpol;
update ufam_oficios set grupo = 'ufam';
SQLITE COPIAR TABLA DE UNA BD A OTRA BD
- Archivo / Anexar base de datos
Tambien se puede hacer con ATTACH DATABASE 'ruta_a_la_base_de_datos_origen.sqlite' AS origen
- CREATE TABLE tab_desempeño AS SELECT * FROM data.tab_desempeño
ACTUALIZAR TELEFONOS
## MySQL ###
UPDATE tabcontactos
SET telefono1 = CONCAT(
SUBSTRING(telefono1, 1, 3), ' ',
SUBSTRING(telefono1, 4, 3), ' ',
SUBSTRING(telefono1, 7, 3)
)
WHERE LENGTH(telefono1) = 9;
## SQLITE ##
UPDATE tabcontactos
SET telefono1 = substr(telefono1, 1, 3) || ' ' || substr(telefono1, 4, 3) || ' ' || substr(telefono1, 7, 3)
WHERE length(telefono1) = 9;
Importar SQL desde XAMPP
Si trabajamos con un servidor local como XAMPP y necesitamos importar una gran base de datos para trabajar desde nuestro PC, podemos realizar la misma importación desde un terminal mySQL.
Posicionarnos en la ruta donde se encuentra el servidor mySQL en XAMPP:
/opt/lampp/bin/
Dentro de esta ruta tecleamos la orden:
./mysql -u root -p base_de_datos (se abre terminal mysql en la base seleccionada)
source /home/jesus/backup.sql
CONSULTAS APLICACION INVENTARIO
SELECT
p.id,
p.nombre,
p.precio,
m.cantidad,
(p.precio * m.cantidad) AS precio_total
FROM
productos p
JOIN
movimientos m ON p.id = m.producto_id;
PRECIO TOTAL DE TODAS LAS UNIDADES VENDIDAS
SELECT
SUM(a.precio * v.unidades_vendidas) AS precio_total_general
FROM
articulos a
JOIN
ventas v ON a.articulo = v.articulo;
FITRAR ENTRE FECHAS
SELECT
- articulo,
- precio,
SUM(v.unidades_vendidas) AS unidades_vendidas_periodo,
SUM(a.precio * v.unidades_vendidas) AS precio_total_periodo
FROM
articulos a
JOIN
ventas v ON a.articulo = v.articulo
WHERE
v.fecha_venta BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
- articulo, a.precio;
TOTAL GENERAL FILTRAR ENTRE FECHAS
SELECT
SUM(a.precio * v.unidades_vendidas) AS precio_total_periodo
FROM
articulos a
JOIN
ventas v ON a.articulo = v.articulo
WHERE
v.fecha_venta BETWEEN '2023-01-01' AND '2023-12-31';
---------------------------
CONSULTA BASE_D
SELECT grupo, COUNT(*) FROM tabregistro WHERE grupo = 'centro' AND fechaEnt BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM tabregistro WHERE grupo = 'norte' AND fechaEnt BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM tabregistro WHERE grupo = 'este' AND fechaEnt BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM tabregistro WHERE grupo = 'oeste' AND fechaEnt BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM taboficios WHERE grupo = 'centro' AND fecha BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM taboficios WHERE grupo = 'norte' fecha BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM taboficios WHERE grupo = 'este' AND fecha BETWEEN '2025-01-01' AND '2025-09-25';
SELECT grupo, COUNT(*) FROM taboficios WHERE grupo = 'oeste' AND fecha BETWEEN '2025-01-01' AND '2025-09-25';
CENTRO 4083 107
NORTE 3841 677
ESTE 1828 207
OESTE 6500 210
-------------------------------