-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql.html
93 lines (93 loc) · 3.77 KB
/
sql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<!DOCTYPE html>
<html>
<head>
<title>SQL Query Reference Sheet</title>
<link rel="stylesheet" href="style.css" type="text/css"/>
</head>
<body>
<div id="container">
<div id="main">
<h1>SQL Query Reference Sheet</h1>
<p>A variety of SQL query examples, lots of basic stuff taken from basic tutorials as well as more personally-useful stuff.</p>
<h2>Basic SQL Stuff</h2>
<p>Super basic stuff I don't want to screw up live.</p>
<h3>Base update/delete queries</h3>
<pre>UPDATE table_name<br />SET col1='val1', col2='val2'<br/>WHERE key='keyvalue' [AND/OR] col2='val2';</pre>
<pre>DELETE FROM table_name<br/>WHERE key='keyvalue' [AND/OR] col2='val2';</pre>
<h3>Insert Into</h3>
<pre>INSERT INTO table_name (col1, col2, col3)<br/>VALUES (val1, val2, val3);</pre>
<p>Columns don't have to specified in the first line, but they determine specific columns where things will be inserted. Depending on needs and specificity.</p>
<h3>Unique results</h3>
<pre>SELECT DISTINCT col1, col2<br/>FROM table_name<br/>WHERE condition='fulfilled';</pre>
<h3>Order by</h3>
<pre>ORDER BY col1, col2 ASC|DESC;</pre>
<p>Ascending by default. May order by all columns if decided.</p>
<h2>SQL Joins</h2>
<p>Fuuuuuuuucking SQL Joins. Anyway, in theory:</p>
<pre>SELECT table_1.col_a, table_1.col_d, table_2.col_c<br/>table_1 JOINTYPE table_2<br/> ON (table_1.col_a=table_2.col_f)<br/>WHERE col_b='value'</pre>
<p>Where JOINTYPE = the type of join— right, inner, etc.</p>
<p>The "database_name." can be affixed to the beginning of table names.</p>
<h3>Inner Join</h3>
<p>Returns a result row when both paired columns have matching values.</p>
<h3>Outer Join</h3>
<p>Returns a result row when either table has a column with the matching value.</p>
<p>
<h4>Left Outer Join</h4>
<p>Left, in this case, refers to the first table in the pairing. It'll match every line in the first table and designated data from the second if it exists.</p>
<pre>SELECT product.id AS "ID", product.name AS Name, "$" + stock.price AS Price, stock.number AS "Stock Count"<br/>FROM product LEFT JOIN stock<br/> ON product.id=stock.id</pre>
<p>For a pair of tables where:</p>
<pre>1, Teddy Bear<br/>2, Stuffed Gorilla<br/>3, Stuffed Octopus</pre>
<p>and</p>
<pre>1,$14.99,29<br/>2,,<br/>3,,24</pre>
<p>This will return:</p>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Price</th>
<th>Stock Count</th>
</tr>
<tr>
<td>1</td>
<td>Teddy Bears</td>
<td>$14.99</td>
<td>29</td>
</tr>
<tr>
<td>2</td>
<td>Stuffed Gorilla</td>
<td/>
<td/>
</tr>
<tr>
<td>3</td>
<td>Stuffed Octopus</td>
<td></td>
<td>24</td>
</tr>
</table>
<h4>Right Outer Join</h4>
<p>Right, in this case, refers to the second table in the pairing. It'll match every line in the second table and designated data from the second if it exists. See above example, only to the right vs. the left.</p>
</div>
<div id="footer" class="centered">
<p xmlns:dct="http://purl.org/dc/terms/" xmlns:vcard="http://www.w3.org/2001/vcard-rdf/3.0#">
<a rel="license"
href="http://creativecommons.org/publicdomain/zero/1.0/">
<img src="http://i.creativecommons.org/p/zero/1.0/88x31.png" style="border-style: none;" alt="CC0" />
</a>
<br />
To the extent possible under law,
<a rel="dct:publisher"
href="http://ruthtillman.com">
<span property="dct:title">Ruth Tillman</span></a>
has waived all copyright and related or neighboring rights to
<span property="dct:title">Code Reference Project</span>.
This work is published from:
<span property="vcard:Country" datatype="dct:ISO3166"
content="US" about="http://code.ruthtillman.com">
United States</span>.
</p>
</div>
</div>
</body>
</html>