MySQL Learning Notes

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.

Back to articles