Sql queries
From wikinotes
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