Sql queries: Difference between revisions

From wikinotes
No edit summary
 
 
(26 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{ TODO |
this should be broken down into comparison-operators vs select-syntax
}}


= Simple Queries =
= Example =
<source lang="mySQL">
<blockquote>
SELECT name, lastname FROM my_table            # only name,lastname columns
<syntaxhighlight lang="MySQL">
SELECT * FROM my_table WHERE name = 'Smith';  #
SELECT *                  # select all rows
FROM  users                # from users table
WHERE username = "dvader"  # where the 'username' column value is 'dvader'
AND  age > 40;            # and the 'age' column value is above 40
</syntaxhighlight>
</blockquote><!-- Example -->
 
= Query Components =
<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">
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
</syntaxhighlight>
 
After your selection you may:
* use [[sql joins]] to associate the queried table's data to other tables.<br>''(ex: find users assigned to a project)''<br><br>
* use [[sql aggregate functions]] to operate on groups of rows<br>''(ex: find average age of users in users table)''<br><br>
* use [[sql comparison operators]] in your <code>WHERE</code> statement to determine what you want to select.<br>''(ex: find users whose name starts with an 'A')''
 
</blockquote><!-- Query Components -->
 
= Techniques =
<blockquote>
== Nested Queries ==
<blockquote>
You can nest SQL queries and treat them as tables.
 
The example below finds <code>users.id</code> values<br>
that are not present in the many:many <code>user_departments</code> table,<br>
by joining a user table query to itself.


# DISTINCT
<syntaxhighlight lang="MySQL">
SELECT DISTINCT        *       FROM my_table WHERE name='Smith';  # No Duplicate Columns
SELECT users.*
SELECT DISTINCT name, lastname FROM my_table WHERE name='Smith';  # No Columns returned where name, and lastname
FROM users
                                                                  # are not a unique combo (can have multiple
                                                                  # johns, and multiple does, but only one john doe)


# OPERATORS
LEFT JOIN (
SELECT * FROM my_table  WHERE name = 'Smith'  AND age < 30;          # AND operator
  SELECT DISTINCT users.id AS user_id
SELECT * FROM my_table  WHERE name = 'Smith'  OR age < 30;          # OR operator
  FROM   user_departments
SELECT * FROM my_table  WHERE age BETWEEN  30 AND 40;                # BETWEEN operator
  INNER JOIN users ON users.id = user_departments.user_id
SELECT * FROM my_table  WHERE name IN ('john', 'jane', 'iggy');      # IN operator
) AS users_with_departments
SELECT * FROM my_table  WHERE name NOT IN ('john', 'jane', 'iggy');  # NOT IN operator
ON users_with_departments.user_id = users.id


# RESTRICT RESULTS
WHERE users_with_departments.user_id IS NULL;
SELECT *
</syntaxhighlight>
FROM my_table
LIMIT 0, 200  # Refine results to 200 rows after row 0


# COMBINE FIELDS
You can also nest a query within a <code>WHERE</code> condition.<br>
SELECT CONCAT(u.firstName, " ", u.lastName) user_fullName  # 'firstName lastName'
Joined tables are accessible within this nested query.
FROM my_table   
</source>


= Nested Queries =
<syntaxhighlight lang="MySQL">
<source lang="mySQL">
SELECT *
SELECT user_Id
FROM users
FROM (
LEFT JOINS assignments ON assignments.user_id = users.id
  SELECT DISTINCT    d.user_Id                                          ,
WHERE ((SELECT COUNT(1) FROM users WHERE assignments.active = 1) > 0) # the outer 'assignmens' is in scope here
                        CONCAT(u.firstName, " ", u.lastName) user_fullName
</syntaxhighlight>
  FROM               userDepartmentTable            d
</blockquote><!-- Nested Queries -->
  INNER JOIN          userTable AS u ON d.user_Id  =  u.user_Id
  WHERE               d.department_Id              =  2
) u
WHERE u.user_fullName REGEXP 'ghis';
</source>


= Dynamically Defined Tables =
== Dynamically Defined Tables ==
<blockquote>
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.


<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 79:
<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)
) ON foo.id = bar.id
) ON foo.id = bar.id
</source>
</source>
 
</blockquote><!-- Techniques -->
= ORDER BY =
</blockquote><!-- Dynamically Defined Tables -->
<source lang="mySQL">
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');
 
</source>
 
= SUBSTRING_INDEX =
split/tokenize a string.
<syntaxhighlight lang="mySQL">
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2)
##> www.mysql
</syntaxhighlight>

Latest revision as of 20:41, 9 August 2022

Example

SELECT *                   # select all rows
FROM  users                # from users table
WHERE username = "dvader"  # where the 'username' column value is 'dvader'
AND   age > 40;            # and the 'age' column value is above 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

After your selection you may:

  • use sql joins to associate the queried table's data to other tables.
    (ex: find users assigned to a project)

  • use sql aggregate functions to operate on groups of rows
    (ex: find average age of users in users table)

  • use sql comparison operators in your WHERE statement to determine what you want to select.
    (ex: find users whose name starts with an 'A')

Techniques

Nested Queries

You can nest SQL queries and treat them as tables.

The example below finds users.id values
that are not present in the many:many user_departments table,
by joining a user table query to itself. 

SELECT users.*
FROM users

LEFT JOIN (
  SELECT DISTINCT users.id AS user_id
  FROM   user_departments
  INNER JOIN users ON users.id = user_departments.user_id
) AS users_with_departments
ON users_with_departments.user_id = users.id

WHERE users_with_departments.user_id IS NULL;

You can also nest a query within a WHERE condition.
Joined tables are accessible within this nested query. 

SELECT *
FROM users
LEFT JOINS assignments ON assignments.user_id = users.id
WHERE ((SELECT COUNT(1) FROM users WHERE assignments.active = 1) > 0)  # the outer 'assignmens' is in scope here

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
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_queries&oldid=31486"