Consultas SQL úliles

Para buscar registros duplicados

SELECT nombre_campo, count(*) FROM nombre_tabla GROUP BY nombre_campo HAVING count(*) > 1; 

SELECT email, count(*) FROM st_contactos GROUP BY email HAVING count(*) > 1; 

SELECT id, count(*) FROM st_meli_items GROUP BY id HAVING count(*) > 1; 

SELECT id_orig, count(*) FROM rj_contactos WHERE form = 'info rojo' GROUP BY id_orig HAVING count(*) > 1; 

select * from rj_contactos where id_orig IN(1813,1814,1818,1819,1821,1822,1823,1824,1825,1826,1827,1828,1829,1830,1831,1832,1833,1834,1835,1836,1837,1838,1839,1840,1841,1842,1843,1844,1845,1846,1847,1848,1849,1850,1851,1852,1853,1856,1857,1858,1859,1860,1861,1862,1863,1864,1866,1870,1871,1872,1873,1874,1875,1876,1877,1878,1879,1880,1881,1882,1883,1884,1885,1886,1890,1891,1892,1893,1894,1895,1899,1900,1901,1902,1903,1904,1905,1907,1909,1910,1911,1912,1913,1914,1915,1916,1917,1918,1919,1921,1922,1923,1924,1925,1926,1928,1929,1930)

# para filtrar duplicados y mostrar los registros
SELECT *
FROM st_nxpost_envios
WHERE id_mensaje IN (
       SELECT id_mensaje
       FROM st_nxpost_envios
       GROUP BY id_mensaje
       HAVING COUNT(id_mensaje) > 1
)

 

Para borrar registros duplicados

DELETE t1 FROM st_contactos_cursos t1
INNER JOIN st_contactos_cursos t2 
WHERE t1.fecha_alta < t2.fecha_alta
AND t1.id_contacto = t2.id_contacto
AND t1.id_curso = t2.id_curso

Para resetear un orden

SET @count = 0;
UPDATE st_archivos SET orden = @count:= @count + 1
WHERE idrelacional = 31

 

lo mismo pero en una sola línea

UPDATE st_conexiones as a, (SELECT @numeroConsecutivo:= (SELECT max(orden) FROM st_conexiones)) as tabla 
SET a.orden=@numeroConsecutivo:=@numeroConsecutivo+1 WHERE a.orden = 0;

 

Cambio de dominio en Wordpress

UPDATE wp_options SET option_value = replace(option_value, 'http://localhost:8888', 'http://altel.demositios.com.ar') WHERE option_name IN('home', 'siteurl', 'dashboard_widget_options', 'theme_mods_Aqua', '_transient_feed_f835fcade4ab539e67a73a2a477cbac6');
UPDATE wp_posts SET guid = replace(guid, 'http://localhost:8888','http://altel.demositios.com.ar');
UPDATE wp_posts SET post_content = replace(post_content, 'http://localhost:8888', 'http://altel.demositios.com.ar');
UPDATE wp_layerslider SET data = replace(data, 'localhost:8888', 'altel.demositios.com.ar');

 

FEHGRA - Consulta para extractar listado anunciantes

SELECT A.id, U.filial, anunciante, ciudad,direccion, A.cp, telefono,A.email,web,A.imagen,caracteristica FROM st_anunciantes A
Left join st_anunciantes_rubros SR ON SR.id_anunciante = A.id
Left Join st_loc_ciudades LC ON LC.id = A.id_ciudad
Left Join st_usuarios U ON U.id = A.id_padre
Left Join st_caracteristicas_txt CT ON CT.id_caracteristica = A.id_estrellas
WHERE papelera = 0
AND U.filial <> ''
AND anunciante <> ''
ORDER BY filial

 

FEHGRA - Consulta exclusiva filiales

SELECT A.id, U.filial, A.habilitado, A.papelera, anunciante, ciudad,direccion, A.cp, telefono,A.email,web,A.imagen,caracteristica FROM st_anunciantes A
Left join st_anunciantes_rubros SR ON SR.id_anunciante = A.id
Left Join st_loc_ciudades LC ON LC.id = A.id_ciudad
Left Join st_usuarios U ON U.id = A.id_padre
Left Join st_caracteristicas_txt CT ON CT.id_caracteristica = A.id_estrellas
WHERE papelera = 0
AND anunciante <> ''
AND id_padre = 8
ORDER BY filial

 

Para actualizar datos de una tabla a otra

UPDATE TablaB
INNER JOIN TablaA
ON TablaA.Id = TablaB.Id
SET TablaB.datosX = TablaA.datosX

 

Para insertar datos de una tabla a otra

INSERT INTO st_diccionario_funciones (id_texto_funcion, funcion) SELECT id, funcion FROM st_diccionario

 

Para armar un campo con todos los datos relacionados.

Mirar en el SQL del diccionario como se toman las funciones relativas. Se usa la instrucción GROUP_CONTACT junto con el LEFT JOIN de la tabla respectiva.

SELECT *, GROUP_CONCAT(distinct CONCAT(funcion, '/', modulo)) as funciones FROM st_diccionario INNER JOIN st_diccionario_txt ON id = id_diccionario LEFT JOIN st_diccionario_funciones ON id = id_diccionario_funcion WHERE principal = 1

Consulta para obtener informe de rebotes NXPOST

SELECT st_nxpost_errores.fecha, id_suscriptor, id_mensaje, mensajes, cod_error, mensaje FROM st_nxpost_errores
INNER JOIN st_suscriptores ON st_suscriptores.id = st_nxpost_errores.id_suscriptor
INNER JOIN st_nxpost_mensajes ON st_nxpost_mensajes.id = st_nxpost_errores.id_mensaje
ORDER BY id_suscriptor, id_mensaje, fecha
LIMIT 0, 100

 

Cambiar el collate a toda la base

https://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database/9531221#9531221

Heres how to change all databases/tables/columns. Run these queries and they will output all of the subsequent queries necessary to convert your entire schema to utf8. Hope this helps!

-- Change DATABASE Default Collation

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET latin1 COLLATE latin1_spanish_ci;')
from information_schema.tables
where TABLE_SCHEMA like ‘lazarillo';

-- Change COLUMN Collation / Char Set

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';

Para cambiar masivamente tablas

SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = 'rarares_en'

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET latin1 COLLATE latin1_spanish_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'lazarillo'
AND table_name not like 'kfm%';

 

Borrar datos de 2 tablas

DELETE s, r from st_proveedores s left join st_proveedores_txt r on s.id = r.id_proveedor where s.id_lista = 7

 

Crear vista

 

CREATE [OR REPLACE] VIEW nombre_vista [column_list] AS consulta_SELECT

CREATE [OR REPLACE] VIEW nombre_vista [column_list] AS consulta_SELECT

 

Problemas con usuarios

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'

Consultas para vistas

Modulos y posiciones

SELECT P.posicion, M.orden, M.nombre, M.funcion, M.clase, M.parametros FROM st_modulos M
INNER JOIN st_posiciones P ON P.id = M.id_posicion
ORDER BY P.posicion, M.orden

Campos de un tipo de contenido

SELECT C.nombre, C.activa, C.tipo, C.perfiles, C.perfiles_usrs, C.tabla as tablas,
C.parametros, C.idioma, C.valida, C.etiqueta, C.etiqueta as texto_etiqueta, C.ayuda, C.ayuda as texto_ayuda
FROM st_campos C
WHERE tabla = 'negocios'

Campos Vistas de un tipo de contenido

SELECT C.nombre_campo as nombre, C.orden, C.tabla as tablas, C.ubicacion, C.parametros_vista as parametros
FROM st_campos_vistas C
WHERE tabla = 'negocios'
ORDER BY ubicacion, orden

 

Cambio de idioma

update st_articulos_txt set idioma = 'en';
update st_banners_txt set idioma = 'en';
update st_caracteristicas_txt set idioma = 'en';
update st_diccionario_txt inner join st_diccionario ON id = id_diccionario and modulo = 1 set idioma = 'en';
update st_enlaces_txt set idioma = 'en';
update st_paginas_txt set idioma = 'en';
update st_perfiles_txt set idioma = 'en';
update st_productos_txt set idioma = 'en';

 

Borrar datos con un subquery de la misma tabla

Ref: https://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

delete from st_redirects where id IN(select id from (select * from st_redirects) as st_redirects where CONCAT('/', url_new) = url_old)

 

Agregar columna con valor por defecto

Por ejemplo, en Megatravel era necesario en la conexión de usuarios agregar la columna habilitado con valor "S" por defecto. Esto se hace agregando la siguiente línea a la consulta: "nombre por defecto" AS NombreColumnaTabla. Ej:

SELECT mail, password, "S" as habilitado  from agents

 

Para agregar una columna con un indice

select @rownum:=@rownum+1 nro, PP.* from st_productos_precios PP, (SELECT @rownum:=0) r where PP.id_moneda = 2 AND neto > 0