SHARE
TWEET

Untitled

a guest Feb 23rd, 2019 90 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Basic Queries
  2. --  lter your columns
  3.     SELECT col1, col2, col3, ... FROM table1 --  lter the rows
  4.     WHERE col4 = 1 AND col5 = 2 -- aggregate the data
  5.     GROUP by ...
  6. -- limit aggregated data
  7.     HAVING count(*) > 1 -- order of the results
  8.     ORDER BY col2
  9.  
  10. Useful keywords for SELECTS:
  11.     DISTINCT - return unique results
  12.     BETWEEN a AND b - limit the range, the values can be numbers, text, or dates
  13.     LIKE - pattern search within the column text
  14.     IN (a, b, c) - check if the value is contained among given.
  15.     Data Modi cation
  16. -- update speci c data with the WHERE clause UPDATE table1 SET col1 = 1 WHERE col2 = 2
  17. -- insert values manually
  18.     INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME) VALUES (1, ‘Rebel’, ‘Labs’);
  19. -- or by using the results of a query
  20.     INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME) SELECT id, last_name,  rst_name FROM table2
  21.  
  22. Views
  23.     CREATE VIEW view1 AS SELECT col1, col2 FROM table1
  24.     WHERE ...
  25.  
  26. JOINs
  27. AB AB
  28.       LEFT OUTER JOIN - all rows from table A, even if they do not exist in table B
  29.     INNER JOIN - fetch the results that exist in both tables
  30.     RIGHT OUTER JOIN - all rows from table B, even if they do not exist in table A
  31.  
  32.  Updates on JOINed Queries
  33. You can use JOINs in your UPDATEs
  34.     UPDATE t1 SET a = 1
  35.     FROM table1 t1 JOIN table2 t2 ON t1.id = t2.t1_id WHERE t1.col1 = 0 AND t2.col2 IS NULL;
  36.     NB! Use database speci c syntax, it might be faster!
  37. Semi JOINs
  38.     You can use subqueries instead of JOINs: SELECT col1, col2 FROM table1 WHERE id IN
  39.     (SELECT t1_id FROM table2 WHERE date > CURRENT_TIMESTAMP)
  40. Indexes
  41.     If you query by a column, index it! CREATE INDEX index1 ON table1 (col1)
  42.  
  43. Useful Utility Functions
  44. -- convert strings to dates:
  45.     TO_DATE (Oracle, PostgreSQL), STR_TO_DATE (MySQL) -- return the  rst non-NULL argument:
  46.     COALESCE (col1, col2, “default value”) -- return current time:
  47.     CURRENT_TIMESTAMP
  48. -- compute set operations on two result sets
  49.     SELECT col1, col2 FROM table1 UNION / EXCEPT / INTERSECT SELECT col3, col4 FROM table2;
  50.     Union - Except -
  51.     returns data from both queries
  52.     rows from the  rst query that are not present in the second query
  53.     Intersect - rows that are returned from both queries Reporting
  54.     Use aggregation functions
  55.     COUNT - return the number of rows
  56.     SUM - cumulate the values
  57.     AVG - return the average for the group MIN / MAX - smallest / largest value
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top