To send text to UPPER or LOWER case user UPPER() or LOWER()
SELECT UPPER(author_lname), LOWER(author_lname) FROM books;
To only get unique records use DISTINCT
SELECT DISTINCT <column> FROM <table>;
Example:
SELECT DISTINCT author_lname FROM books;
To get DISTINCT from mutiple columns, example lets say you want DISTINCT from first_name and last_name columns
SELECT DISTINCT CONCAT(author_fname,' ', author_lname) AS 'full name' FROM books;
More Simple way:
SELECT DISTINCT author_fname, author_lname FROM books;
To sort a query use ORDER BY
Note ORDER BY is in ascending, which you can state explicitly with ASC, order by default alphanumeric starting with numbers
SELECT <column> FROM <table> ORDER BY <column>;
Example
SELECT author_lname FROM books ORDER BY author_lname;
To reverse ascending order us DESC
Example descending
SELECT author_lname FROM books ORDER BY author_lname DESC;
ORDER BY shortcut to specify which column you want to sort by use number at end of ORDER BY
SELECT <column1>, <column2>, <column3> FROM <table> ORDER BY 2;
Example
SELECT title, author_fname, author_lname FROM books ORDER BY 2 DESC;
To sort by more than one column specify multiple columns when using ORDER BY
SELECT <column1>, <column2>, <column3> FROM <table> ORDER BY <column2>, <column1>;
Example
SELECT title, author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
To limit the number of our result use LIMIT
Example you could use LIMIT to get top 3 results
SELECT <column1>, <column2> FROM <table> ORDER BY <column2> DESC LIMIT 5;
Example
SELECT title, released_year FROM books ORDER BY 2 DESC LIMIT 5;
You can also use limit by specifying a start point for example you want top 5 but start at 5 entry, which really is saying I want lower 5 results of top 10
SELECT <column1>, <column2> FROM <table> ORDER BY <column2> DESC LIMIT 5,5;
Example
SELECT title, released_year FROM books ORDER BY 2 DESC LIMIT 5,5;
To search for item based on a few characters use LIKE and percent sign %, the percent sign is a wild card attribute
Example to search for author that Da, like David, Dan, Dave, Darryl, etc
SELECT <column1>, <column2> FROM <table> WHERE author_fname LIKE '<value>%'
Example
SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%';
Other wild cards for LIKE '_' underscore
Example to get values with only 4 digits
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '____';
+----------------------+----------------+
| title | stock_quantity |
+----------------------+----------------+
| Lincoln In The Bardo | 1000 |
+----------------------+----------------+
Example to get values with only 3 or digits
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '___%';
+-------------------------------------------+----------------+
| title | stock_quantity |
+-------------------------------------------+----------------+
| A Hologram for the King: A Novel | 154 |
| A Heartbreaking Work of Staggering Genius | 104 |
| Coraline | 100 |
| Oblivion: Stories | 172 |
| fake_book | 287 |
| Lincoln In The Bardo | 1000 |
+-------------------------------------------+----------------+
In case you want to search for % or _ in your data you can escape the wild cards by using backslash \ '%\%%'
Example to get title with % in title
SELECT title FROM books WHERE LIKE '\%%';
+-------------+
| title |
+-------------+
| 10% Happier |
+-------------+
Example to get title with _ in title
SELECT title FROM books WHERE LIKE '\_%';
+-----------+
| title |
+-----------+
| fake_book |
+-----------+
What is an Aggregate Function(s)
Aggregate Function will take more than one data element and combine into whole result
To count number of records in a table use COUNT()
SELECT COUNT(*) FROM <table>;
Example
SELECT COUNT(*) FROM books;
To show a unique count records based on a column we can use DISTINCT with COUNT
Example this shows all the first names but there are duplicate first names not necessarily records
This can be problematic because this does not show unique authors see DISTINCT author_lname
We can add both author_fname and author_lname to COUNT with DISTINT
SELECT COUNT(author_fname) AS author_fname FROM books;
GROUP BY - Summarizes or aggregates identical data into single rows.
It groupls a set of rows into a set of summary rows.
It will return only one row per group
You can put more than one value for GROUP BY
SELECT <column>, COUNT(*) FROM <table> GROUP BY <column>;
Example
SELECT author_fname, COUNT(*) FROM books GROUP BY author_fname;
Because we only grouping by author_lname in our example we could have multiple authors with same last name which would give incorrect count
Here is a better example where we specify more than one value for GROUP BY and we are sorting:
SELECT CONCAT(author_fname, ' ', author_lname) AS author, COUNT(CONCAT(author_fname, ' ', author_lname)) AS number_books FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To get maximum or minimum items from a table use MAX or MIN
SELECT MIN(<column>) FROM books;
Or
SELECT MAX(<column>) FROM books;
Concept of Subqueries, we can embeded a subquery in a given query in order to get the most correct result.
Example if we try the following from a books table we will likely not get the correct result:
SELECT title, MAX(pages) FROM books;
+--------------+------------+
| title | MAX(pages) |
+--------------+------------+
| The Namesake | 634 |
+--------------+------------+
Example of getting just title the namesake with pages
SELECT title, pages FROM books WHERE title LIKE '%the namesake%';
+--------------+-------+
| title | pages |
+--------------+-------+
| The Namesake | 291 |
+--------------+-------+
Notice pages for the namesake is 291
SELECT tile, pages FROM books WHERE pages=634;
+-------------------------------------------+-------+
| title | pages |
+-------------------------------------------+-------+
| The Amazing Adventures of Kavalier & Clay | 634 |
+-------------------------------------------+-------+
We can fix this by using a subquery, Example:
SELECT title, pages FROM books WHERE pages=(SELECT MAX(pages) FROM books);
+-------------------------------------------+-------+
| title | pages |
+-------------------------------------------+-------+
| The Amazing Adventures of Kavalier & Clay | 634 |
+-------------------------------------------+-------+
Unfortunately this is an expensive operation because we are using two SELECT statement for one query.
We can try the following to get same result and should be much faster:
SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;
Here is an example of where we can find each authors first released book
SELECT CONCAT(author_fname, ' ', author_lname) AS author, MIN(released_year) AS year FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To add up a number of columns use SUM
SELECT SUM(<column>) FROM <table>;
Example
SELECT SUM(pages) FROM books;
Example more advanced list how many pages has the most pages written
SELECT CONCAT(author_fname, ' ', author_lname) AS author, SUM(pages) AS 'total pages written' FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To find the average of values use AVG
SELECT AVG(<column>) FROM <table>;
Example
SELECT AVG(released_year) FROM books;
Example more advanced showing average number of pages each author wrote
SELECT CONCAT(author_fname, ' ', author_lname) AS author, AVG(pages) AS 'total pages written' FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
CHAR datatype, CHAR is a fixed data type it right pads with space when stored
Example if you declare CHAR(10) but insert value of 'abc' it will store as 'abc '
When retrieving a CHAR column MySQL will remove trailing spaces unless PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled
CHAR is faster for fixed length text, example use would be State abbreviations, Yes No flags, Sex M F, etc
Decimal data type, to create a column with decimal use DECIMAL(#,#)
DECIMAL data type takes two arguments, the first argument is TOTAL number of digits the decimal can have BEFORE and AFTER decimal and the second argument is number of digits to the right of the DECIMAL
NOTE: when you attempt to insert a number larger that you specified MySQL will only insert the larget number allowed, Example:
ColumnaA is DECIMAL(5,2) and you insert value of 7987654, you select ColumnA back you will get 9999.99 which is the maximum allowed for 4 digits of whole and 2 digits of precision
For date and time you can use the following DATE, TIME, and DATETIME
DATE is format 'YYYY-MM-DD'
Time is format 'HH:MM:SS'
DATETIME is format 'YYYY-MM-DD HH:MM:SS'
To get current time date and date time use CURTIME(), CURDATE(), and NOW()
You can combine >= to make comparision inclusive same for <=
!= is not equal to
NOT negates the statement
BETWEEN to compare two ranges inclusive
BETWEEN is equivalent to comparing two item ranges, Example:
100 >= 5 AND 100 <= 200
IN operator checks if in a list
Long way:
SELECT <column1>, <column2>
FROM <table>
WHERE <column1>='<value1>' OR
<column1>='<value2>' OR
<column1>='<value3>;
Using IN operator you can compare to a list
SELECT <column1>, <column2>
FROM <table>
WHERE <column1> IN ('<value1>', '<value2>', '<value3>');
CASE statements, basically if else statements
SELECT <column1>, <column2>,
CASE
WHEN <column1> >= <value1> THEN 'OtherValue1'
ELSE 'OtherValue2'
END AS <NewColumnName>
FROM <table>;
Example:
SELECT title, released_year,
CASE
WHEN released_year >= 2000 THEN 'Modern Lit'
ELSE 'OtherValue2'
END AS '20th Century Lit'
FROM Genre;
Foreign Keys
A foreign key is basically a unique key from another table that ties current table to a Primary key from another table
INNER JOIN - this allows you to SELECT data from two tables based upon a primary and foreign key
Implicit Inner Join
SELECT * FROM <table01>, <table02> WHERE <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers, orders WHERE customers.id = orders.customer_id;
Explicit Inner Join
SELECT * FROM <table01>
JOIN <table02>
ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
JOIN orders
ON customers.id = orders.customer_id;
LEFT JOIN - this allows you to take the intersection of two tables plus all of the left table based upon a primary and foreign key
In the example on the right NOTE that ALL of the table01 left of "LEFT JOIN" statement will be included plus the intersection of table01 and table02
SELECT * FROM <table01>
LEFT JOIN <table02>
ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;
RIGHT JOIN - this allows you to take the intersection of two tables plus all of the left table based upon a primary and foreign key
In the example on the right NOTE that ALL of the table01 right of "RIGHT JOIN" statement will be included plus the intersection of table01 and table02
***NOTE: that if you swap table01 with table02 to the left and right of the RIGHT JOIN you are essentially mimicking the output of the LEFT JOIN above. Its important to understand the table placement to the left and right of the join call.
SELECT * FROM <table01>
RIGHT JOIN <table02>
ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;
To format the SELECT state in a list of field=value use \G at end of statement minus semicolon ;