MySQL notes

Notes for working with MySQL

Basic table join for update from one table to another with where clause

UPDATE tableA a
  JOIN tableB b
    ON a.a_id = b.a_id
  JOIN tableC c
    ON b.b_id = c.b_id
   SET b.val = a.val+c.val
 WHERE a.val > 10
   AND c.val > 10;

Another way

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

Full Text search

SELECT 
	productName 
FROM 
	products 
WHERE 
	MATCH(productName) 
    AGAINST('1992' WITH QUERY EXPANSION);

Simple JOIN

Also called an INNER JOIN clause using the event database on backup01 This assumes that there will ALWAYS be a match between the two tables. If there is not, then no rows returned.

 SELECT d.hostname,d.address,p.Properties FROM Device d JOIN DeviceProperties p on p.DeviceId=d.id  WHERE d.id=14 \G
*************************** 1. row ***************************
  hostname: nas01.iwillfearnoevil.com
   address: 192.168.15.125
Properties: {"snmpEnable":"true","snmpVersions":["2","1","3"],"snmpVersion":"2","snmpPort":161,"snmpTries":2,"snmpMonitorTimeout":1000000,"snmpV3SecurityName":"123Fake","snmpV3Hash":"456StillFake","snmpV3Password":"789Fake","snmpCommunities":["public","private","foobar"],"snmpCommunity":"public","snmpInterfaceIgnore":"lo vmnet[0-9]","snmpFilesystemIgnoreType":"romram virtualMemory loop","snmpfilesystemIgnoreMatch":"\/snap \/blah\/lxd rando\/foobar","snmpMaxOidPerRequest":"40","winUser":"admin administrator","winPassword":"password password123","winDomain":"DC:\\Domain","winEventLogSeverity":"2","winEventLogMonitor":"False","winWmiMonitorIgnore":"True","nrpeMonitorTimeout":"20","nmapDiscoverPortArgs":"-p 1-10024 -sT --open -oG -","nmapUpdatePortList":"False","nrpeCommandPath":"\/usr\/local\/bin\/nrpe","nmapCommandPath":"\/usr\/bin","sshPort":"22","sshKeyBase64":"LS0tLS1CRUdJTiBSU0EgUFJJVkFURSBLRVktLS0tLQpNSUlFcEFJQkFBS0NBUUVBdHRON05jVmtl","sshUser":"failSafeUser","sshPassword":"notAGoodIdea","sshPassowrdEncrypted":"stillNotAGreatIdea","pingMonitorIgnore":"False","comments":"","links":"https:\/\/linux.org"}
1 row in set (0.00 sec)

LEFT Join:

This makes no assumption on the second table, just that there will be a match in the first one.

SELECT d.hostname,d.address,p.Properties FROM Device d LEFT JOIN DeviceProperties p on p.DeviceId=d.id  WHERE d.id=26 \G
*************************** 1. row ***************************
  hostname: pipeload.com
   address: 172.92.156.161
Properties: NULL
1 row in set (0.00 sec)

Group and sorts

mysql> select eventSeverity, count(*) as count from history group by eventSeverity order by eventSeverity DESC;
+---------------+-------+
| eventSeverity | count |
+---------------+-------+
|             5 | 27170 |
|             4 |  8376 |
|             3 | 19299 |
|             2 |  1496 |
|             1 |  1995 |
|             0 |    13 |
+---------------+-------+
6 rows in set (0.19 sec)