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
-
Check the server's MySQL version from the command line
mysqld --version
-
Check the client's MySQL version from the command line
mysql --version
-
Check the version from the SQL console
SELECT 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
-
Check the current default character encoding for the database
dbname
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "dbname";
-
Change the database
dbname
default character encoding toUTF-8
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
-
Create a new database
dbname
character encodingCREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin
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
- You can use the
DISTINCT
keyword to get the number of unique values for a key-column.
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
- By default, the
ORDER BY
statement will order byASC
but you can still include theASC
if you would like to.
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
%ex%
matches any string containingex
ex%
matches any string beginning withex
%ex
matches any string ending withex
The LIKE
operator uses the _
character to match a single character
_r%
matches any string withr
as the second character%_r
matches any string withr
as the second-to-last charactera__%
matches any string, of length 3, starting witha
Fancy combinations
a%o
matches any string that starts witha
and ends witho
?
lower()
The lower()
function will convert a key-column's values to lower case.
SELECT lower(name) FROM my_table WHERE name='austin'
- Useful when you're trying to make a query case-insensitive
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
-
Count the number of children given a particular name in the year 1999
SELECT name, SUM(number) as total FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE year = 1999 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
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:
- A floating point value
- The # of decimal points to include
SELECT
name,
ROUND(AVG(duration),2) AS average_duration
FROM
swim_times
COUNT()
- You can use
COUNT(*)
to determine the number of records in the table. - You can use
COUNT(column_name)
to determine the number of rows in the table that contain a value for that key-column name.
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
INNER JOIN
: Returns all entries with matching values in both tablesOUTER JOIN
: Returns all entries with matching values in either tableLEFT JOIN
Returns all entries in the left table, and the matching records in the right tableRIGHT 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
-
DESC
DESC MyTable;
-
SHOW COLUMNS
SHOW COLUMNS FROM MyDatabase.MyTable;
-
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!
-
A primary key is a field used to supply data to other tables
-
A foreign key is a field used to receive data from other tables
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
-
Show the current user:
SELECT CURRENT_USER(); -- Show current user
-
Show all users;
SELECT user FROM mysql.user; -- Show all users
Changing a user’s password for mysql
SET PASSWORD FOR 'root'@'localhost' = 'root';
Setting The Time Zone
-
Setting the time zone to Universal Coordinated Time (UTC)
SET GLOBAL time_zone = '+00:00';
-
Permanently setting the time zone to California
-- Option 1 SET GLOBAL time_zone = 'America/Los_Angeles'; -- Option 2 (Fixed offset, will not respect Daylight Savings) SET GLOBAL time_zone = '-08:00'
-
Temporarily setting the time zone to California time
SET time_zone = 'America/Los_Angeles'
-
Setting the time zone in the configuration file
~/.my.cnf
[client] port = 3306 [mysqld] prompt = '\u@\h [\d]> ' default-time-zone = '+00:00'
-
Converting a time-zone in
mysql
/* Convert the timestamp from it's "from_timezone" -05:00 (PST) to the "to_time zone" UTC time. It was 8:53 in California, which was (output: 13:53) in London */ SELECT CONVERT_TZ('2012-06-07 8:53:23', '-05:00', '+00:00');
Misc
-
Creating a table that uses timestamps
CREATE TABLE Searches ( search_id INT(4) PRIMARY KEY AUTO_INCREMENT, search_query VARCHAR(100) NOT NULL, period TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
-
Supporting UTF-8 encoding
SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Database Administration
-
Create a user named
jeffrey
who can log in from anywhere-- Option 1, implied wildcard hostname CREATE USER 'jeffrey' -- Option 2, explicit wildcard hostname CREATE USER 'jeffrey'@'%'
-
Create a user named
tommy
who can only log in locallyCREATE USER 'tommy'@'127.0.0.1'
-
Allow a user to perform all database administration commands
GRANT ALL PRIVILEGES ON * . * TO 'jeffrey' -- Flushing the active permissions causes these changes to take effect FLUSH PRIVILEGES
-
Change password of user
tommy
-- Change tommy's password to "letmein" ALTER USER 'tommy'@'127.0.0.1' IDENTIFIED BY 'letmein';
-
Display information about all known users
SELECT user, host, password FROM mysql.user;
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
-
Example
my.cnf
file# 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
Date Comparisons
-
Select all entries scanned after
2020-01-01
:SELECT * FROM `database.ip_addresses` WHERE 'public_ip' IS NOT NULL AND 'scan_date' < '2020-01-01'
ARRAY_AGG
-
Get the list of each doctor's patients
SELECT doctor, ARRAY_AGG(patient) as patients FROM hospital-dataset.patient-info GROUP BY doctor
Output
[ { "doctor": "John", "patients": [ "Crosby", "Stills", "Nash" ] }, { "doctor": "Kelly", "patients": [ "Young" ] }, { "doctor": "Austin", "patients": [ "Simon", "Garfunkel" ] } ]
JSON_OBJECT
-
Return each result as a string, containing the key-value pairs
artist_id
andname
for the matching row:SELECT JSON_OBJECT("artist_id", artist_id, "name", name) FROM artists LIMIT 5;
Output
asdf
DATE_SUB
-
Filter out partitions that are more than 5 days old
SELECT * FROM mydataset.mytable WHERE DATE(_PARTITIONTIME) > DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)
MySQL vs. PostgreSQL
There are differences between quotations in PostgreSQL and quotations in MySQL when used to reference identifiers.