SQL

Subqueries

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

Subquery Converted to Inner Join:

# Subquery:
SELECT name 
 FROM animal 
 WHERE id 
 IN (SELECT animal_id FROM animal_food);

# Inner Join:
SELECT DISTINCT name 
 FROM animal,animal_food 
 WHERE animal.id=animal_id;

Subquery Converted to Outer Join:

# Subquery:
SELECT name FROM animal WHERE id NOT IN (SELECT animal_id FROM animal_food);

# Outer Join:
SELECT name FROM animal LEFT JOIN  animal_food ON animal.id=animal_id WHERE animal_id IS NULL;

a.k.a. Inner Select

• MySQL :: MySQL 5.0 Reference Manual :: 12.2.9 Subquery Syntax
• MySQL Subqueries

Insert

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

ref: https://www.w3schools.com/sql/sql_insert.asp

Replace Text

UPDATE `knowledgebase`.`kbentry` SET description=REPLACE(description, '/console/', '/edit/');

# REPLACE(string, old_string, new_string)
SELECT REPLACE(MAC, '-', ':') from HOSTS;

ref: https://www.w3schools.com/sql/func_sqlserver_replace.asp