1. Important & Useful Links
Website | URL/Link |
---|---|
Developer’s Website | http://www.mysql.com/ |
Documentation | https://dev.mysql.com/doc/ |
W3 Schools | http://www.w3schools.com/sql/default.asp |
Codecademy | https://www.codecademy.com |
2. Notes
2.1. Keys
Keyword | Key | Description |
---|---|---|
KEY | Unique Key | Each value MUST be unique and cannot be NULL |
PRIMARY KEY | Primary Key | A type of Unique Key where only ONE can exist in a table. |
FOREIGN KEY | Foreign Key | A key which is linked to a Primary Key OUTSIDE of the table. |
PARTITION KEY | ??? |
3. Code Snippets
3.1. Select Everything from a Database
Code:
SELECT * FROM celebs;
Explanation:
SELECT
: List
*
: everything
FROM
: requested
celebs
: Databade
3.2. Create Table
Code:
CREATE TABLE celebs(id INTEGER, name TEXT, age INTEGER);
Explanation:
CREATE
: Create something
TABLE
: of type TABLE
celebs
: the name of the Table
(id INTEGER, name TEXT, age INTEGER)
: Columns of the Table
3.3. Insert Data
INSERT INTO celebs(id, name, age) VALUES (1, "Justin Biber", 21);
SELECT * FROM celebs;
3.4. Select a Particular Column
SELECT name FROM celebs;
3.5. Edit Existing Data
UPDATE celebs
SET age = 22
WHERE id = 1;
SELECT * FROM celebs;
3.6. Edit Table
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;
SELECT * FROM celebs;
3.7. Delete Row with an Empty Cell
DELETE FROM celebs WHERE twitter_handle IS NULL;
3.8. Queries
Code:
SELECT name, imdb_rating FROM movies;
Explanation:
SELECT DISTINCT
specifies that the statement is going to be a query that returns unique values in the specified column(s).
3.9. Filtering Data
Code:
SELECT * FROM movies WHERE imdb_rating > 8;
Explanation:
The way to filter queries in SQL
is to use the WHERE
clause.
SELECT * FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;
3.10. Wildcards
Code:
SELECT * FROM movies WHERE name LIKE "Se_en";
SELECT * FROM movies WHERE name LIKE "a%";
Explanation:
_
: wild character
%
: matches zero or more missing letters in the pattern.
3.11. Between
SELECT * FROM movies WHERE name BETWEEN "A" AND "J";
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
3.12. COUNT()
SELECT COUNT(*) FROM fake_apps;
3.13. Some Functions
COUNT
: Takes the name of a column(s) as an argument and counts the number of rows where the value(s) is not NULL.
GROUP BY
: Is a clause used with aggregate functions to combine data from one or more columns.
SUM()
: Takes the column name as an argument and returns the sum of all the values in that column.
MAX()
: Takes the column name as an argument and returns the largest value in that column.
MIN()
: Takes the column name as an argument and returns the smallest value in that column.
AVG()
: Takes a column name as an argument and returns the average value for that column.
ROUND()
: Takes two arguments, a column name and the number of decimal places to round the values in that column.
3.14. JOIN Data from Different Tables
SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id;
3.15. LEFT JOIN
Code:
SELECT * FROM albums LEFT JOIN artists ON albums.artist_id = artists.id;
Explanation:
The LEFT
table is simply the first table that appears in the statement. Here, the left table is albums. Likewise, the right table is the second table that appears. Here, artists is the right table.
3.16. Aliases
Code:
SELECT
albums.name AS "Album",
albums.year,
artists.name AS "Artist"
FROM
albums
JOIN artists ON
albums.artist_id = artists.id
WHERE
albums.year > 1980;
Explanation:
AS
is a keyword in SQL
that allows you to rename a column or table using an alias.