Crear Base y Usuario #
CREATE DATABASE sugar CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE user sugar;
GRANT ALL ON sugar.* TO 'sugar'@'%';
SET PASSWORD FOR 'sugar'@'%'=PASSWORD('sugar');
Otorgar privilegios a un usuario@host a una base #
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';
UTF8 #
Definir UTF8 en el cliente
mysql --default-character-set=utf8
Definir UTF8
SET NAMES utf8; SELECT * FROM contacts;
MySQL de Bash #
Definir Alias
set alias sugar = 'mysql --default-character-set=utf8 -u sugar -psugar -B sugar -h localhost '
Ejecutar consulta
echo "SELECT user_name FROM users WHERE is_admin = 1;" | sugar
Listado de Tablas
set alias tablas='echo "show tables" | sugar | grep -v Tables_in | sort -u'
Listado Tabla Campo
set alias tablacampo='echo "show tables" | sugar | grep -v Tables_in | while read t; \
do d=$(echo "desc "$t";" | cct15 | grep -v Field | awk1); \
for c in $d;do echo $t"__"$c;done ;done'
Quitar nombre de campo
mysql -N
Vaciar todas las tablas que contengan la palabra calls
sugar < $(echo "show tables" | sugar | grep calls | while read t;do echo "TRUNCATE table $t;";done)
MySQL desde Vim #
Agregar en .vimrc
command -range=% SUGAR :<line1>,<line2>w !mysql -u sugar -psugar -B sugar -t -v -v -v
Ejecutar todo el archivo
:SUGAR
Ejecutar la línea actual
:. SUGAR
Ejecutar un rango de líneas
:3,8 SUGAR
Ejecutar desde línea actual hasta el final
:.,$ SUGAR
Usar diccionarios
:set dictionary=tablas, tablacampo
CTRL-X CTRL-K
Reemplazar Texto #
Traducir valores #
1SELECT REPLACE(REPLACE(direction,'Inbound','Entrante'),'Outbound','Saliente') AS direccion
2FROM calls;
Actualizar registros #
1UPDATE contacts SET last_name = REPLACE(last_name, 'NUNEZ', 'NUÑEZ');
Buscar duplicados #
1SELECT last_name, first_name, COUNT(id) AS total
2FROM contacts
3GROUP BY first_name, last_name
4HAVING total > 1
5ORDER BY last_name, first_name;
Agrupar #
1SELECT name,
2(
3SELECT CASE
4WHEN EXTRACT(HOUR FROM date_start) < 12
5THEN 'mañana'
6ELSE 'tarde'
7END
8) AS turno,
9COUNT(id) as total
10FROM calls
11GROUP BY name, turno
12HAVING total > 500
13ORDER BY total DESC;
Cruzando tablas #
Explicito mejor que implicito
1SELECT COUNT(cc.id) AS total
2FROM calls_contacts cc
3LEFT JOIN contacts co ON co.id = cc.contact_id ;
4AND cc.deleted = 0
5AND co.deleted = 0
6
7SELECT COUNT(cc.id) AS total
8FROM calls_contacts cc, contacts co
9WHERE co.deleted = 0
10AND co.id = cc.contact_id
11AND cc.deleted = 0
Contactos con llamadas
1SELECT COUNT(cc.id) AS total
2FROM calls_contacts cc
3LEFT JOIN contacts co ON co.id = cc.contact_id;
Contactos con llamadas entrantes
1SELECT co.last_name,
2co.first_name,
3CONVERT_TZ(date_start, '+00:00', '-03:00') as fecha
4FROM contacts co
5INNER JOIN calls_contacts cc ON (cc.contact_id = co.id AND cc.deleted = 0)
6INNER JOIN calls ca ON (ca.id = cc.call_id AND ca.deleted = 0)
7WHERE co.deleted = 0
8AND last_name IS NOT NULL
9LIMIT 20;
Actualizar las llamadas de un contacto
1UPDATE calls ca, contacts co, calls_contacts cc
2SET ca.assigned_user_id = (
3SELECT id
4FROM users
5WHERE user_name = 'osiris'
6)
7WHERE ca.id = cc.call_id
8AND co.id = cc.contact_id
9AND co.id = '2a756d50-ae20-0754-a7c7-49beb64cee37';
10
11
12UPDATE calls ca
13INNER JOIN calls_contacts cc ON cc.call_id = ca.id
14INNER JOIN contacts co ON co.id = cc.contact_id
15SET ca.assigned_user_id = (
16SELECT id
17FROM users
18WHERE user_name = 'osiris'
19)
20WHERE co. = '2a756d50-ae20-0754-a7c7-49beb64cee37';
Insertar desde otra tabla #
1DROP TABLE IF EXISTS calls_contacts_today;
2
3CREATE TABLE `calls_contacts_today` (
4`id` varchar(36) NOT NULL,
5`contact_id` varchar(36) NOT NULL,
6`call_id` varchar(36) NOT NULL,
7`status` varchar(25) default NULL,
8`direction` varchar(25) default NULL,
9PRIMARY KEY (`call_id`)
10);
11
12INSERT INTO calls_contacts_today
13(id, contact_id, call_id, status, direction)
14SELECT UUID(), cc.contact_id, ca.id, ca.status, ca.direction
15FROM calls ca
16INNER JOIN calls_contacts cc ON cc.call_id = ca.id
17WHERE DATE(ca.date_start) = CURDATE();
AUTOCOMPLETE #
1\#
2
3SELECT FROM t<Presionar TAB>
COUNT #
1UPDATE calls set deleted = 1 WHERE status = 'Not Held';
2SELECT COUNT(*) FROM calls;
3SELECT COUNT(id) FROM calls;
4SELECT COUNT(id) FROM calls WHERE deleted = 0;
5SELECT SQL_CALC_FOUND_ROWS id FROM calls WHERE deleted = 0 LIMIT 1; SELECT FOUND_ROWS();
EXPLAIN #
1EXPLAIN SELECT COUNT(id)
2FROM calls
3WHERE deleted = 0
4AND assigned_user_id = 'ba8630eb-7442-73f9-a88e-49b6be5882c2';
INDEX #
1SHOW INDEX IN calls;
2ALTER TABLE calls ADD INDEX idx_deleted_user (deleted, assigned_user_id);
3ALTER TABLE calls DROP INDEX idx_deleted_user;
UNIQUE #
1ALTER TABLE calls_contacts_today ADD UNIQUE idx_contact_call (contact_id, call_id);
AUTOINCREMENT #
1ALTER TABLE tracker AUTO_INCREMENT = 9;
2ALTER TABLE tracker MODIFY id INT(11) AUTO_INCREMENT;
REGEXP #
1UPDATE contacts
2SET postal = SUBSTR(postal,4,7)
3WHERE postal NOT REGEXP '^[0-9]{4}$'
4AND postal REGEXP '^[A-Z]{3}[0-9]{4}$'
RANDOM #
1SELECT RAND();
2SELECT MD5(RAND());
3SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8);