Sql queries: Difference between revisions
From wikinotes
No edit summary |
|||
Line 50: | Line 50: | ||
<source lang="mySQL"> | <source lang="mySQL"> | ||
SELECT * | SELECT * | ||
FROM (VALUES (1), (2)) foo(id); | FROM ( | ||
VALUES (1), (2) | |||
) foo(id); | |||
</source> | </source> | ||
Line 56: | Line 58: | ||
<source lang="mySQL"> | <source lang="mySQL"> | ||
SELECT * | SELECT * | ||
FROM (VALUES (1), (2)) foo(id) | FROM ( | ||
VALUES (1), (2) | |||
) foo(id) | |||
INNER JOIN ( | INNER JOIN ( | ||
(VALUES (1)) bar(id) | (VALUES (1)) bar(id) |
Revision as of 17:19, 19 September 2021
TODO:
this should be broken down into comparison-operators vs select-syntax
Simple Queries
SELECT name, lastname FROM my_table # only name,lastname columns
SELECT * FROM my_table WHERE name = 'Smith'; #
# DISTINCT
SELECT DISTINCT * FROM my_table WHERE name='Smith'; # No Duplicate Columns
SELECT DISTINCT name, lastname FROM my_table WHERE name='Smith'; # No Columns returned where name, and lastname
# are not a unique combo (can have multiple
# johns, and multiple does, but only one john doe)
# OPERATORS
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30; # AND operator
SELECT * FROM my_table WHERE name = 'Smith' OR age < 30; # OR operator
SELECT * FROM my_table WHERE age BETWEEN 30 AND 40; # BETWEEN operator
SELECT * FROM my_table WHERE name IN ('john', 'jane', 'iggy'); # IN operator
SELECT * FROM my_table WHERE name NOT IN ('john', 'jane', 'iggy'); # NOT IN operator
# RESTRICT RESULTS
SELECT *
FROM my_table
LIMIT 0, 200 # Refine results to 200 rows after row 0
# COMBINE FIELDS
SELECT CONCAT(u.firstName, " ", u.lastName) user_fullName # 'firstName lastName'
FROM my_table
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 'ghis';
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');
SUBSTRING_INDEX
split/tokenize a string.
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2)
##> www.mysql