Sql queries: Difference between revisions
From wikinotes
(→SELECT) |
|||
Line 12: | Line 12: | ||
= Query Components = | = Query Components = | ||
<blockquote> | <blockquote> | ||
SELECT which columns you'd like to see, and how you'd like them to be presented (order, max results, etc). | |||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * # select all columns | SELECT * # select all columns | ||
Line 24: | Line 23: | ||
</blockquote><!-- Select --> | </blockquote><!-- Select --> | ||
* Use [[sql joins]] to associate the queried table's data to other tables. | |||
* Use [[sql comparison operators]] in your <code>WHERE</code> statement to determine what you want to select. | |||
< | |||
</blockquote><!-- Query Components --> | </blockquote><!-- Query Components --> | ||
Revision as of 18:46, 19 September 2021
Example
SELECT * FROM users WHERE username = "dvader" AND age > 40;
Query Components
SELECT which columns you'd like to see, and how you'd like them to be presented (order, max results, etc).
SELECT * # select all columns SELECT name, age FROM users; # select name, and age only SELECT name AS userame FROM users; # rename column in results table SELECT DISTINCT name FROM users; # select 1x row for each unique name in users SELECT * FROM users LIMIT 10; # only select first 10 results SELECT * FROM users ORDER BY name ASC # sort results in ascending order by name
- Use sql joins to associate the queried table's data to other tables.
- Use sql comparison operators in your
WHERE
statement to determine what you want to select.
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