SQL

From Omnia
Revision as of 20:57, 28 November 2018 by Kenneth (talk | contribs) (→‎Subqueries)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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

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