SQL

The structured query language

Monday, Jun 1, 2020

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

Note: This is assuming you installed mysql using brew (and why wouldn’t you?)

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

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

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

DISTINCT

If you use the DISTINCT keyword, it will apply to all fields that are being selected from.

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

Note: Always put the ORDER BY statement at the end of your query.

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 value-type is used to save time in SQL.

SELECT
  cast(time_of_event as DATE) as date_of_event,
  extract(year FROM year_of_event) as year_of_event,
  extract(month FROM year_of_event) as month_of_event,
  extract(week FROM year_of_event) as week_of_event,
  extract(day FROM year_of_event) as day_of_event
  extract(hour FROM year_of_event) as hour_of_event,
  extract(minute FROM year_of_event) as minute_of_event,
  extract(second FROM year_of_event) as second_of_event

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

GROUP BY

Count the number of children named Emily in every state, for the year 1999

SELECT
  name,
  SUM(number) as sum_number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year = 1999
  AND name = 'Emily'
GROUP BY
  name

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

Note: The WHERE clause must come before the GROUP BY clause, because the WHERE clause is filtering the data set that GROUP BY will apply to.

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()

Note: Make sure to use AS to alias the name of the column returned by this value, as by default it will return some junk name, such as f0_

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;

Tip: This command even works if you’re already inside a different 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
```sql
DESC MyTable;
```
  1. SHOW COLUMNS
```sql
SHOW COLUMNS FROM MyDatabase.MyTable;
```
  1. SHOW FULL COLUMNS
```sql
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);

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

Update: If you’re using mysql version 8 or later, this option is chosen by default.

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

# This is a comment

# Read by both the client and the server
[client-server]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

# Read by the server
[mysqld]
port = 3306
bind-address = 0.0.0.0

# Comment out the line below if you want a database that does not accept remote connections
# skip-networking