180 lines
4.0 KiB
Plaintext
180 lines
4.0 KiB
Plaintext
= SQL =
|
|
|
|
Stuctered Query Language is a language that describes a method of fetching and
|
|
describing the relationship between different types of data
|
|
|
|
== Filtering data ==
|
|
|
|
To filter data we by some specifiers we use the `WHERE` clause. The where
|
|
clause can filter based on the default comparison operators. It can also do
|
|
string comparisons.
|
|
|
|
This system also works with dates, where all dates are in format `YYYY-MM-DD`.
|
|
|
|
=== IN ===
|
|
|
|
To see if an attribute is in a set we can use the `IN` keyword in conjunction
|
|
with a `WHERE` statement. For example,
|
|
`SELECT * FROM holidays WHERE Country IN ('spain', 'portugaul', 'USA');`
|
|
|
|
=== NOT ===
|
|
|
|
The `NOT` keyword can be placed before a conditional to specify that we want
|
|
everything that does NOT mean that condition.
|
|
|
|
=== LIKE ===
|
|
|
|
`LIKE` lets us filter on patterns. We can use the character `%` to specify a
|
|
wildcard, for any number of characters. We can use the character `_` to give an
|
|
exact number of wildcard characters we are looking for. For example,
|
|
|
|
`SELECT * FROM Bands WHERE BandName LIKE '%light%';`
|
|
|
|
will return all bands with the string 'light' in their name.
|
|
|
|
=== DISTINCT ===
|
|
|
|
`DISTINCT` filters results by only returning unique values of a column.
|
|
Therefore, we can only be used to return a single column.
|
|
|
|
{{{
|
|
SELECT DISTINCT nationality FROM users;
|
|
}}}
|
|
|
|
== Presentation ==
|
|
|
|
=== Order by ===
|
|
|
|
`ORDER BY` allows us to descide how the query will be shown. For example,
|
|
|
|
{{{
|
|
SELECT Firstname, Lastname
|
|
FROM users
|
|
ORDER BY Lastname [SORT];
|
|
}}}
|
|
|
|
where `[SORT]` is
|
|
|
|
* DESC
|
|
* descending order
|
|
* ASC
|
|
* ascending order
|
|
|
|
=== CASE ===
|
|
|
|
Case is a statement block where we can choose exactly what text is returned
|
|
based on values in the database.
|
|
|
|
{{{
|
|
SELECT Name,
|
|
CASE
|
|
WHEN Country = 'USA' THEN 'North America'
|
|
WHEN Country = 'UK' THEN 'Europe'
|
|
ELSE 'Wolrd'
|
|
END
|
|
FROM Users;
|
|
}}}
|
|
|
|
=== LIMIT ===
|
|
|
|
`LIMIT` allows us to retstrict the number or rows returned.
|
|
|
|
`SELECT * FROM users ORDER BY name DESC LIMIT 1`
|
|
|
|
=== COUNT ===
|
|
|
|
If we want just the number of rows, we can use count.
|
|
|
|
`SELECT COUNT(*) FROM users WHERE dob > '2000-01-01'`
|
|
|
|
=== math functions ===
|
|
|
|
If we want to sum the values in ONE column we can use sum
|
|
|
|
`SELECT SUM(cash_balance) FROM users`
|
|
|
|
If we want to find an average,
|
|
|
|
`SELECT AVG(price) FROM products`
|
|
|
|
This same principle applies for
|
|
|
|
* `MAX`
|
|
* `MIN`
|
|
|
|
=== GROUP BY ===
|
|
|
|
`GROUP BY` allows us to combine with the above math and `COUNT` functions by
|
|
grouping totals based upon some other row.
|
|
|
|
`SELECT Team, COUNT(*) FROM Players GROUP BY Team`
|
|
|
|
=== JOIN ===
|
|
|
|
`JOIN` allows two tables to be returned together, where they are 'joined' upon
|
|
a shared field.
|
|
|
|
{{{
|
|
SELECT Orders.ID, Customers.Name, Orders.Name
|
|
FROM Orders
|
|
INNER JOIN Customers ON Orders.CustomerId=Customers.Id
|
|
}}}
|
|
|
|
There are four types of joins
|
|
* `INNER JOIN` returns all records that having matching records in both tables
|
|
* `LEFT OUTER JOIN` returns all records from the left (Orders in above example)
|
|
table, and the matched records from the right table
|
|
* `RIGHT OUTER JOIN` returns all records from the right (Customers in above
|
|
example) table, and the matched records from the left table
|
|
* `FULL OUTER JOIN` returns all records when there is a match in either the
|
|
left or right table
|
|
|
|
== Views ==
|
|
|
|
A view can be created with the following command
|
|
|
|
{{{
|
|
CREATE VIEW [view_name] AS
|
|
SELECT column1, column2, ...
|
|
FROM table
|
|
WHERE condition;
|
|
}}}
|
|
|
|
For example, this view returns all products witha price higher than the average
|
|
price of the table
|
|
|
|
{{{
|
|
CREATE VIEW [Products above average price] AS
|
|
SELECT Name, Price
|
|
FROM Products
|
|
WHERE Price > (SELECT AVG(Price) FROM Products)
|
|
|
|
SELECT * FROM [Prodcuts above average price]
|
|
}}}
|
|
|
|
Views *can* be inserted into, you simply must specify the columns being
|
|
inserted into.
|
|
|
|
== Acess control commands ==
|
|
|
|
Two commands for managing access rights
|
|
|
|
* grant
|
|
* used to grant one or more access rights or can be used to assign a user to
|
|
a role
|
|
* revoke
|
|
* revokes the acess rights
|
|
|
|
Typical access rights include
|
|
|
|
* select
|
|
* insert update
|
|
* delete
|
|
* references
|
|
|
|
== Also see ==
|
|
|
|
[[databases]]
|
|
|
|
[[index]]
|