Sql queries: Difference between revisions
From wikinotes
No edit summary |
|||
Line 1: | Line 1: | ||
= | = Example = | ||
< | <blockquote> | ||
SELECT | <syntaxhighlight lang="MySQL"> | ||
SELECT | SELECT * | ||
FROM users | |||
WHERE username = "dvader" | |||
AND age > 40; | |||
</syntaxhighlight> | |||
</blockquote><!-- Example --> | |||
= Select = | |||
<blockquote> | |||
Determines what columns you want to return. | |||
Basics | |||
<syntaxhighlight lang="MySQL"> | |||
SELECT * # select all columns | |||
SELECT col1, col2 # select col1, and col2 only | |||
SELECT col1 AS foo # rename column in results table | |||
</syntaxhighlight> | |||
Distinct only returns one result per unique value of N | |||
SELECT DISTINCT | <syntaxhighlight lang="MySQL"> | ||
SELECT DISTINCT username | |||
FROM users | |||
WHERE last_name = "skywalker"; | |||
</syntaxhighlight> | |||
LIMIT lets you choose only the first N results | |||
<syntaxhighlight lang="MySQL"> | |||
SELECT * | SELECT * | ||
FROM users | |||
LIMIT 10 | |||
</syntaxhighlight> | |||
CONCAT lets you combine columns into one | |||
SELECT | <syntaxhighlight lang="MySQL"> | ||
FROM | SELECT CONCAT(first_name, " ", last_name) AS fullname | ||
FROM users; | |||
</syntaxhighlight> | |||
SUBSTRING_INDEX lets you split/tokenize a string | |||
SELECT | <syntaxhighlight lang="mySQL"> | ||
FROM | SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) | ||
</ | ##> www.mysql | ||
</syntaxhighlight> | |||
</blockquote><!-- Select --> | |||
= Where = | |||
<blockquote> | |||
Defines the Matches that will be selected. | |||
Basics | |||
<syntaxhighlight lang="MySQL"> | |||
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> | |||
</syntaxhighlight> | |||
</blockquote><!-- Operators --> | |||
= Nested Queries = | = Nested Queries = | ||
<source lang="mySQL"> | <source lang="mySQL"> | ||
SELECT user_Id | SELECT user_Id | ||
FROM ( | FROM ( | ||
SELECT DISTINCT d.user_Id , | SELECT DISTINCT d.user_Id , | ||
CONCAT(u.firstName, " ", u.lastName) user_fullName | |||
FROM userDepartmentTable d | FROM userDepartmentTable d | ||
INNER JOIN userTable AS u ON d.user_Id = u.user_Id | INNER JOIN userTable AS u ON d.user_Id = u.user_Id | ||
WHERE d.department_Id = 2 | WHERE d.department_Id = 2 | ||
) u | ) u | ||
WHERE u.user_fullName REGEXP ' | WHERE u.user_fullName REGEXP 'andr%'; | ||
</source> | </source> | ||
Line 49: | Line 83: | ||
<source lang="mySQL"> | <source lang="mySQL"> | ||
SELECT * | SELECT * | ||
FROM ( | FROM ( | ||
VALUES (1), (2) | VALUES (1), (2) | ||
Line 80: | Line 114: | ||
'Disputed', | 'Disputed', | ||
'Shipped'); | 'Shipped'); | ||
</source> | </source> | ||
Revision as of 17:37, 19 September 2021
Example
SELECT * FROM users WHERE username = "dvader" AND age > 40;
Select
Determines what columns you want to return.
Basics
SELECT * # select all columns SELECT col1, col2 # select col1, and col2 only SELECT col1 AS foo # rename column in results tableDistinct only returns one result per unique value of N
SELECT DISTINCT username FROM users WHERE last_name = "skywalker";LIMIT lets you choose only the first N results
SELECT * FROM users LIMIT 10CONCAT lets you combine columns into one
SELECT CONCAT(first_name, " ", last_name) AS fullname FROM users;SUBSTRING_INDEX lets you split/tokenize a string
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) ##> www.mysql
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>
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');