Sql queries: Difference between revisions
From wikinotes
(→Select) |
No edit summary |
||
Line 10: | Line 10: | ||
</blockquote><!-- Example --> | </blockquote><!-- Example --> | ||
= | = Query Components = | ||
<blockquote> | |||
== SELECT == | |||
<blockquote> | <blockquote> | ||
Determines what columns you want to return. | Determines what columns you want to return. | ||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * # select all columns | SELECT * # select all columns | ||
Line 21: | Line 21: | ||
SELECT col1 AS foo # rename column in results table | SELECT col1 AS foo # rename column in results table | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== DISTINCT == | === DISTINCT === | ||
<blockquote> | <blockquote> | ||
Distinct only returns one result per unique value of N | Distinct only returns one result per unique value of N | ||
Line 33: | Line 32: | ||
</blockquote><!-- DISTINCT --> | </blockquote><!-- DISTINCT --> | ||
== LIMIT == | === LIMIT === | ||
<blockquote> | <blockquote> | ||
LIMIT lets you choose only the first N results | LIMIT lets you choose only the first N results | ||
Line 43: | Line 42: | ||
</blockquote><!-- LIMIT --> | </blockquote><!-- LIMIT --> | ||
== CONCAT == | === CONCAT === | ||
<blockquote> | <blockquote> | ||
CONCAT lets you combine columns into one | CONCAT lets you combine columns into one | ||
Line 52: | Line 51: | ||
</blockquote><!-- CONCAT --> | </blockquote><!-- CONCAT --> | ||
== SUBSTRING_INDEX == | === SUBSTRING_INDEX === | ||
<blockquote> | <blockquote> | ||
SUBSTRING_INDEX lets you split/tokenize a string | SUBSTRING_INDEX lets you split/tokenize a string | ||
Line 62: | Line 61: | ||
</blockquote><!-- Select --> | </blockquote><!-- Select --> | ||
= | == JOINS == | ||
<blockquote> | |||
</blockquote><!-- JOINS --> | |||
== WHERE == | |||
<blockquote> | <blockquote> | ||
Defines the Matches that will be selected. | Defines the Matches that will be selected. | ||
Line 78: | Line 82: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Operators --> | </blockquote><!-- Operators --> | ||
</blockquote><!-- Query Components --> | |||
= Nested Queries = | = Techniques = | ||
<blockquote> | |||
== Nested Queries == | |||
<source lang="mySQL"> | <source lang="mySQL"> | ||
SELECT user_Id | SELECT user_Id | ||
Line 92: | Line 99: | ||
</source> | </source> | ||
= Dynamically Defined Tables = | == Dynamically Defined Tables == | ||
Some databases do not implement the SQL IN operator.<br> | Some databases do not implement the SQL IN operator.<br> | ||
Instead, you can use VALUES. | Instead, you can use VALUES. | ||
Line 114: | Line 121: | ||
) ON foo.id = bar.id | ) ON foo.id = bar.id | ||
</source> | </source> | ||
</blockquote><!-- Techniques --> | |||
= ORDER BY = | = ORDER BY = |
Revision as of 17:41, 19 September 2021
Example
SELECT * FROM users WHERE username = "dvader" AND age > 40;
Query Components
SELECT
Determines what columns you want to return.
SELECT * # select all columns SELECT col1, col2 # select col1, and col2 only SELECT col1 AS foo # rename column in results tableDISTINCT
Distinct only returns one result per unique value of N
SELECT DISTINCT username FROM users WHERE last_name = "skywalker";LIMIT
LIMIT lets you choose only the first N results
SELECT * FROM users LIMIT 10CONCAT
CONCAT lets you combine columns into one
SELECT CONCAT(first_name, " ", last_name) AS fullname FROM users;SUBSTRING_INDEX
SUBSTRING_INDEX lets you split/tokenize a string
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) ##> www.mysqlJOINS
WHERE
Defines the Matches that will be selected.
Basics
SELECT * FROM users WHERE name = 'Smith' AND age < 30; # AND SELECT * FROM users WHERE name = 'Smith' OR age < 30; # OR SELECT * FROM users WHERE age BETWEEN 30 AND 40; # BETWEEN SELECT * FROM users WHERE name IN ('john', 'jane', 'iggy'); # IN SELECT * FROM users WHERE name NOT IN ('john', 'jane', 'iggy'); # NOT IN SELECT * FROM users WHERE first_name LIKE "Andr%"; # LIKE (glob match) SELECT * FROM users WHERE first_name REGEXP "^A.*" # REGEXP</syntaxhighlight>
Techniques
Nested Queries
SELECT user_Id FROM ( SELECT DISTINCT d.user_Id , CONCAT(u.firstName, " ", u.lastName) user_fullName FROM userDepartmentTable d INNER JOIN userTable AS u ON d.user_Id = u.user_Id WHERE d.department_Id = 2 ) u WHERE u.user_fullName REGEXP 'andr%';Dynamically Defined Tables
Some databases do not implement the SQL IN operator.
Instead, you can use VALUES.SELECT * FROM ( VALUES (1), (2) ) foo(id);Most useful within a join
SELECT * FROM ( VALUES (1), (2) ) foo(id) INNER JOIN ( (VALUES (1)) bar(id) ) ON foo.id = bar.id
ORDER BY
SELECT lastName, firstName FROM customers ORDER BY lastName; # Returned results order determined by lastName
SELECT lastName, firstName FROM customers ORDER BY lastName DESC; # Returned results order determined by lastName, order inverted
SELECT orderNumber, status # Custom Sort order based on contained data
FROM orders
ORDER BY FIELD(status, 'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');