Notes about loading data files for "Learning MySQL"

I previously downloaded the data files via wget.  I don't remember if I had to install wget first.  If I did, it was something like:

sudo apt-get install wget

mkdir Download

cd Download

wget http://url

Now install unzip:

sudo apt-get install unzip

unzip Data.zip -d Data

Now we can load up the data files -- change into directory with data files:

cd ~/download/Data/Data/SQL_files_with_foreign_key_references

Drop into mysql monitor

mysql -u root -p

Now load up the data files:

source Flight.sql

source University.sql

source music.sql

MySQL Import/Export

Load a table from a comma separated value file:

Table must already exist.  mysql does not create the table for me.

LOAD DATA INFILE 'importFile.csv' INTO TABLE myTable FIELDS TERMINATED BY ',';

Export a table TO a CSV:
SELECT col1, col2
FROM table1, table2
WHERE table1.key = table2.key
INTO OUTFILE '/tmp/exportFile.csv' FIELDS TERMINATED BY ',';


[Summarized from Learning MySQL]

MySQL Transactions

This works as expected:

START TRANSACTION;
INSERT INTO artist VALUES(8, "The Cure");
INSERT INTO album VALUES (8, 1, "Disintegration");
ROLLBACK;
-- COMMIT;


[Summarized from Learning MySQL]

MySQL user variables

Set a variable @artist to equal a value from the artist table:
SELECT @artist:=artist_name from artist where artist_id = 1;

Reveal the variable contents:
SELECT @artist;

Define a variable and value:
SET @fruit := 'banana';
SELECT @fruit;

This reveals the name of the track that was played most recently.
It shows how you can define a variable and then use it in a later query:
SELECT MAX(played) FROM played;

SELECT @recent := MAX(played) FROM played;

SELECT track_name FROM track INNER JOIN played
USING (artist_id, album_id, track_id)
WHERE played = @recent;

[Summarized from Learning MySQL]

MySQL joins

Three ways to do an INNER JOIN - all three are the same.
The first is not what I am used to.
It only works if the artist_id column exists and is the same name in both tables.

SELECT artist_name, album_name
FROM artist
INNER JOIN album
USING (artist_id);

SELECT artist_name, album_name 
FROM artist
INNER JOIN album ON artist.artist_id = album.artist_id;

SELECT artist_name, album_name
FROM artist, album
WHERE artist.artist_id = album.artist_id;

LEFT and RIGHT JOINS:

SELECT track_name, played 
FROM track LEFT JOIN played USING (artist_id, album_id, track_id)
ORDER BY played DESC;

SELECT artist_name, album_name 
FROM artist LEFT JOIN album ON artist.artist_id = album.artist_id;

[Summarized from Learning MySQL]

MySQL Breadcrumbs

Show mysql version:
SELECT VERSION();

Show available databases:
SHOW DATABASES;

Switch to a specific database:
USE dbname

Show available tables:
SHOW TABLES;

Find out what commands are available from mysql monitor:
HELP

From within the mysql monitor, bring up a vi window and edit the last command:
edit

Tell me what columns are available in a table:
SHOW COLUMNS FROM tableName;
DESCRIBE tableName;

MySQL equivalent of MS SQL TOP clause = LIMIT
SELECT col1 FROM table1 LIMIT 10;

Join - this only works if the matchingKeyColumn exists in both tables and has the same name in each
SELECT co1l, col2
FROM table1
INNER JOIN table2
USING (matchingKeyColumn);

Insert -
INSERT INTO table
VALUES ('Fred', 'Flintstone');

Delete -
DELETE FROM table
WHERE col1 = '123';

Truncate -
TRUNCATE TABLE table1;

UPDATE table1
SET col1 = 'Fred'
WHERE col2 = 'Flintstone';

Create -
CREATE TABLE tableName (
col1 SMALLINT(5) NOT NULL DEFAULT 0,
col2 CHAR(128) DEFAULT NULL,
PRIMARY KEY (col1)
);

Get SQL statement needed to create existing table:
SHOW CREATE TABLE tableName;

Drop -
DROP TABLE table1;

Reveal indexes:
SHOW INDEX FROM table1;

Create index while creating table:
Notice KEY = just an index whereas PRIMARY KEY = the primary key
CREATE TABLE table1 (
col1 SMALLINT(5) NOT NULL DEFAULT 0,
col2 CHAR(128) DEFAULT NULL,
PRIMARY KEY (col1),
KEY col2 (col2)
);

Composite index:
Notice three columns make up the composite index
CREATE TABLE table1 (
cust_id INT(4) NOT NULL DEFAULT 0,
firstname CHAR(50),
secondname CHAR(50),
surname CHAR(50),
PRIMARY KEY (cust_id),
KEY names (firstname, secondname, surname)
);

[Summarized from Learning MySQL]

Install Apache and PHP on Ubuntu

[Summarized from Digital Ocean]

Install Apache:

sudo apt-get update
sudo apt-get install apache2
sudo apache2ctl configtest
sudo systemctl restart apache2
sudo ufw status
sudo ufw app list
sudo ufw app info "Apache Full"
sudo ufw allow in "OpenSSH"
sudo ufw allow in "Apache Full"
sudo ufw allow in "Apache"
sudo ufw allow in "Apache Secure"
sudo ufw enable

This will tell you the instance's public IP address so you can test the site:
curl http://icanhazip.com

Install PHP:

sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql

Make PHP handling the default:

sudo vi /etc/apache2/mods-enabled/dir.conf

Change this line:

DirectoryIndex index.html index.cgi index.pl index.php index.xhtml index.htm

To be like this:

DirectoryIndex index.php index.html index.cgi index.pl  index.xhtml index.htm

[We simply moved index.php from the fourth option to the first.]

Restart Apache:

sudo systemctl restart apache2

Check Apache status:

sudo systemctl status apache2

Install command-line interpreter for PHP for easier debugging:

apt-cache show php-cli

sudo apt-get install php-cli

Might as well add perl DBI and CGI modules:

sudo apt-get install libdbi-perl libdbd-mysql-perl libcgi-pm-perl

MySQL on Ubuntu on AWS Lightsail

Some quick documentation on how I created an AWS Lightsail Ubuntu instance:

First create the instance:

Create instance --> Linux/Unix --> OS Only --> Ubuntu 16.04 LTS

Wait a few minutes, then open a shell prompt.

sudo apt-get update
sudo apt-get upgrade
sudo reboot
sudo apt-get update
sudo apt-get upgrade
sudo apt autoremove
sudo apt-get dist-upgrade
sudo reboot
sudo apt-get update
sudo apt-get upgrade

Nicely patched now.
To install mysql:

sudo apt-get install mysql-server
mysql_secure_installation

Now to test it:

mysql -u root -p
show databases;

Mac boot tricks

Eject CD/DVD:

Hold down mouse button

Power on

Keep holding the mouse button

Choose boot up drive:

Power on

Press and hold Option

Recovery mode:

Power on

Press and hold Command + R

Boot into safe mode:

Power on

Immediately after hearing startup tone, press and hold shift key

Release the shift key when you see the gray Apple logo and progress line

Reset the SMC:

Press and hold Shift+Control+Option+Power simultaneously

Hold down for 5 seconds

Release all keys simultaneously

PRAM Reset:

Reboot Mac and as soon as hear boot chime, press and hold the Splat (Command)+Option+P+R keys simultaneously

When you hear the chime again, let go and let Mac boot up

Run diagnostics:

Power on

Immediately press and hold D key

Target mode:
Hold T