SQL

MySQL

MySQL is an open source version of SQL that allows you to store & query a database both locally and remotely.

The first few lines of code are from inside your terminal, but the rest of the commands will be run from within the MySQL shell, which opens once you've logged in.

Installing mysql

brew install mysql

Starting up the mysql Server

brew services start mysql

Stopping the mysql Server

brew services stop mysql

Export a Database to .sql File

mysqldump -u [username] -p [database name] > [database name].sql

Execute a sql script on a Database

mysql -u root -p db_name < script.sql > output.tab

Check the current version

The commands below will require you to log into your mysql server, which you can do by typing in the following command.

Working with character encodings

Logging into a mysql Server

Connecting to MySQL server from the command line

# [Local MySQL Server]
mysql -u root
# [Remote MySQL Server]
mysql \
-h myremoteserver.amazonaws.com \
-P 3306 \
--user="root"
--password="root"
# -P refers to port 3306

Connecting to MySQL server from NodeJS

const connection = mysql.createConnection({
  host: "myremoteserver.amazonaws.com",
  user: "admin",
  password: "CorrectHorseBatteryStaple",
  database: "mydb"
})

SQL Clauses

AS

The keyword AS lets you alias, or define names for the values returned from a query.

SELECT COUNT(*) AS rows FROM db.name

ARRAYUNIQUE

DISTINCT

SELECT COUNT(DISTINCT year) FROM db.name

ORDER BY

/* Order by a single field */
SELECT DISTINCT states FROM my_table ORDER BY state DESC
/* Order by multiple fields */
SELECT DISTINCT states, names FROM my_table ORDER BY states, names

LIMIT

The keyword LIMIT can be used to set a maximum number of matches for a query to return

/* Select only the first 100 results */
SELECT * FROM my_table LIMIT 100

WHERE

The WHERE clause filters down results, and is always put after the SELECT clause

SELECT state FROM `my_table` WHERE state='CA'
SELECT name, gender FROM `my_table` WHERE name='Austin' AND gender='M'

OR

SELECT trip_duration from my_table WHERE
(trip_duration >= 3 AND trip_duration <= 5)
OR (trip_duration >= 7 AND trip_duration <= 9)

Listing with the , Keyword

You can make a list of values, as if you were using multiple OR statements

SELECT age from my_table WHERE age = (3,5,7,9)

LIKE

The LIKE operator uses the % character to help specify substrings to match with

The LIKE operator uses the _ character to match a single character

Fancy combinations

lower()

The lower() function will convert a key-column's values to lower case.

SELECT lower(name) FROM my_table WHERE name='austin'

TIMESTAMP

The TIMESTAMP() function can be used to manipulate datetimes. This isn't the only function, either. There's a handful you can use to manipulate datetimes and extract its components, such as MONTH()

The extract() function is part of MySQL but the support works in many languages.

GROUP BY

This clause is covered well in Learning SQL's section on the group by and having clauses

Rank the most popular names of 2010

SELECT
  name,
  sum(number) as sum_number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year = 2010
GROUP BY
  name
ORDER BY
  sum_number DESC

HAVING

The HAVING clause is similar to the WHERE clause, but it is used when referring to data that was aggregated by the GROUP BY clause.

Show all female names with at least 1,000,000 occurrences

SELECT
  name,
  sum(number) as sum_number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = 'F'
GROUP BY
  name
HAVING
  sum_number > 1000000

Aggregate Functions

Grouping is useful for more than just taking the SUM() of a group, the syntax can also be applied to MAX() and MIN()

Find the most expensive trip taken for each payment type:

SELECT
  payment_type,
  MAX(trip_total) AS max_trip_total
FROM
  my_table
GROUP BY
  payment_type
ORDER BY
  payment_type

Find the shortest person in the table of medical records:

SELECT
  gender,
  MIN(height) AS min_height
FROM
  medical_records
GROUP BY
  gender

Find the average height in the table of medical records:

SELECT
  gender,
  AVG(height) as avg_height
FROM
  medical_records
GROUP BY
  gender

Find the total net worth of each industry

SELECT
  industry,
  SUM(wealth) as sum_wealth
FROM
  wealth_data
GROUP BY
  industry

Combining GROUP BY and WHERE

SELECT
  payment_processor,
  SUM(purchase) as sum_purchase
FROM
  credit_card_payments
WHERE
  payment_processor IN ('Visa', 'Mastercard')
GROUP BY
  payment_processor

Grouping by Multiple Columns

The GROUP BY clause can apply to more than one column. If more than one column is specified, it will form a group among entries that share the same value in every column specified within the GROUP BY clause.

SELECT
  name,
  state,
  SUM(number) AS total_with_name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
  name,
  state
ORDER BY
  total_number_of_people DESC

ROUND()

The ROUND() clause can be used to reduce the granularity of a floating point number. It accepts two arguments:

  1. A floating point value
  2. The # of decimal points to include
SELECT
  name,
  ROUND(AVG(duration),2) AS average_duration
FROM
  swim_times

COUNT()

JOIN

The JOIN clause is used to merge two tables together

The most common types of joins are, INNER JOIN, OUTER JOIN, LEFT JOIN, and RIGHT JOIN

  1. INNER JOIN: Returns all entries with matching values in both tables
  2. OUTER JOIN: Returns all entries with matching values in either table
  3. LEFT JOIN Returns all entries in the left table, and the matching records in the right table
  4. RIGHT JOIN Returns all entries in the right table, and the matching records in the left table

Create a joined table containing every customer's name, the order ID, and the order date

SELECT 
  Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM 
  Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

Managing Databases

Creating a database

CREATE DATABASE my_database;

Showing available databases

SHOW DATABASES;

Deleting a database

DROP DATABASE if exists my_database;

Opening a database

USE my_database;

Database Tables

Showing the tables currently in a database

SHOW TABLES;

Removing a table

DROP 
  TABLE this_table;

Renaming a table

RENAME 
  TABLE this_table 
  TO new_table_name;

There are multiple ways that you can show/describe the Various Key-Columns in a Table

  1. DESC

    DESC MyTable;

  2. SHOW COLUMNS

    SHOW COLUMNS FROM MyDatabase.MyTable;

  3. SHOW FULL COLUMNS

    SHOW FULL COLUMNS FROM MyTable IN MyDatabase;

Creating a table in a database

CREATE TABLE Users (
  user_id INT(4) PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  pass VARCHAR(20) NOT NULL
);

Specifying default values for a table's key-columns

CREATE TABLE Example (
	number INT(4) DEFAULT -1,
	word VARCHAR(10) DEFAULT '',
	-- specify a number with 6 digits max, of which 2 may be decimal points
	latitude FLOAT(6,2) DEFAULT 0.00
	longitude FLOAT(6,2) DEFAULT 0.00
);

Inserting a single record into a table

INSERT 
  INTO Searches (search_query) 
  VALUES ('whoami');

Inserting a multiple records into a table at once

INSERT 
  INTO Searches (search_query)
	VALUES 
    ('what is love'), 
    ('baby dont hurt me'), 
    ('dont hurt me'), 
    ('no more')

Finding the most recent search

SELECT 
  * 
FROM 
  Searches
WHERE 
  search_id=(SELECT MAX(search_id) FROM Searches);

Keys in SQL Tables

I got the keys

  • DJ Khaled, Senior Database Administrator

Understanding the difference between primary keys and foreign keys is, well... key!

Foreign Keys

Creating a table with foreign keys

CREATE TABLE Searches (
	search_id INT(4) PRIMARY KEY AUTO_INCREMENT,
	user_query VARCHAR(100) NOT NULL,
  user_id INT(4)
	user_id FOREIGN KEY references Users(user_id)
	/*
  The foreign "Key Column" (in this case 'user_id')
  can have a different name than the column that it referenced
  */
);

Reference a foreign key

SELECT 
  userQuery 
FROM 
  Users U, Searches S 
WHERE 
  u.userID = s.userID 
  AND s.userID = 1 
ORDER BY 
  searchID DESC;

Removing a foreign key from a table

ALTER 
  TABLE this_table 
DROP 
  FOREIGN KEY my_foreign_key;

Changing a key-column name in a table

ALTER 
  TABLE this_table 
CHANGE 
  old_column_name 
  new_column_name VARCHAR(10);

Adding a key-column to a table

ALTER 
  TABLE this_table 
ADD 
  new_column_name VARCHAR(10);

Managing Users

Looking up the current user using mysql

Changing a user’s password for mysql

SET PASSWORD FOR 'root'@'localhost' = 'root';

Setting The Time Zone

Misc

Database Administration

MySQL Configurations

Configurations to the mysqld daemon process that starts a server on port 3306 can be specified by making changes to the file /etc/my.cnf (or sometimes, /etc/mysql/my.cnf)

An example configuration file is included below

Date Comparisons

ARRAY_AGG

JSON_OBJECT

DATE_SUB

MySQL vs. PostgreSQL

There are differences between quotations in PostgreSQL and quotations in MySQL when used to reference identifiers.