Site Is Deployed! And MySQL Basics

Daily Standup

January 03, 2018

Ok now it’s deployed! Probably should have saved the celebratory dancing for today:
PARTY!!!

CloudFront Settings

Following on from yesterday's post, I didn’t need to use an AWS Lambda function after all. CloudFront is capable of handling redirects to an index.html file, it was just a matter of changing the origin settings in the CloudFront distribution. This super helpful post on the AWS forums pointed me in the right direction: rather than selecting the S3 bucket from the dropdown menu, I made the origin a custom origin, and used the endpoint of the S3 bucket instead. And voila! Everything works. I probably wouldn’t have figured this out so quickly (relatively!) without a tip in the right direction from someone in the Learn Teach Code Slack so super grateful for that too!

MySQL Basics

Jumping into the next topic I want to learn, today I started learning about MySQL, starting with some SQL basics. For future reference:

Cloud 9 Commands

  • mysql-ctl start: Start the MySql server
  • mysql-ctl stop: Stop the MySQL server (rarely do this)
  • mysql-ctl cli: Start the MySQL command line interface

Basic MySQL Commands

  • exit; or quit; or \q; or ctrl-c: Leave the MySQL CLI
  • help;: Get a list of commands
  • show databases;: Show all of your databases on this server
  • select @@hostname;: See you own host name

Creating Databases

  1. Start the CLI: mysql-ctl cli;
  2. List available databases: show databases;
  3. The general command for creating a database: CREATE DATABASE database_name;
    • A specific example: CREATE DATABASE soap_store;

Dropping Databases

  • DROP DATABASE database_name;
  • For Example: DROP DATABASE hello_world_db;

Using/Opening Databases

  • USE <database name>;
  • For Example: USE dog_walking_app;
  • SELECT database();: Find out what database you’re using now

Creating Databases

  • The format:
CREATE TABLE tablename
  (
    column_name data_type,
    column_name data_type
  );
  • For example:
CREATE TABLE cats
  (
    name VARCHAR(100),
    age INT NOT NULL
  );
  • Include NOT NULL if the column is not allowed to be empty.

See What’s In Tables

  • SHOW TABLES;: See what tables are available in this database
  • SHOW COLUMNS FROM tablename;: Show how the table has been set up
  • DESC tablename;: Same as SHOW COLUMNS FROM or also DESCRIBE
  • Example output:
mysql> DESCRIBE tweets;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| username        | varchar(15)  | YES  |     | NULL    |       |
| content         | varchar(140) | YES  |     | NULL    |       |
| favorites_count | int(11)      | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

Dropping Tables

  • DROP TABLE <tablename>;
  • A specific example: DROP TABLE cats;

Adding Data to Tables

  • The format: INSERT INTO table_name(column_name) VALUES (data);
  • For example: INSERT INTO cats(name, age) VALUES ('Jetson', 7);
  • NOTE: The order you add the data doesn’t matter, but it must be consistent between how it’s initiated and what the values are.
  • Multiple insert just extends the previous:
INSERT INTO table_name
            (column_1_name, column_2_name)
VALUES      (value_col_1, value_col_2),
            (value_col_1, value_col_2),
            (value_col_1, value_col_2);
  • SHOW WARNINGS;: If the most recent insert gives a warning, this is how you see it. But you must do this right when the warning is given; it won’t work later (although app server should have error handling)

NULL / NOT NULL & Default Values

  • NOT NULL means this column is not allowed to have no value
  • Unless specified the default value for an INT will be 0
  • Unless specified the default value for a VARCHAR will be an empty string ''
  • You can insert data into a table as NULL unless that column is specifically marked NOT NULL
  • To set a default value, add this when creating the table (can be combined with NOT NULL):
CREATE TABLE cats
  (
    name VARCHAR(20) NOT NULL DEFAULT ‘unnamed’,
    age INT NOT NULL DEFAULT 99
  );

Primary Key & Auto Increment

  • Primary key is a unique value assigned to each row for identification
  • This can be set up as an auto-incrementing column when creating a table:
CREATE TABLE unique_cats (
    cat_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);
  • Primary key can also be set along with the actual column:
CREATE TABLE employees (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    middle_name VARCHAR(40),
    age INT NOT NULL,
    current_status VARCHAR(40) NOT NULL DEFAULT 'employed'
);
  • Result:
mysql> DESC employees;
+----------------+-------------+------+-----+----------+----------------+
| Field          | Type        | Null | Key | Default  | Extra          |
+----------------+-------------+------+-----+----------+----------------+
| id             | int(11)     | NO   | PRI | NULL     | auto_increment |
| last_name      | varchar(40) | NO   |     | NULL     |                |
| first_name     | varchar(40) | NO   |     | NULL     |                |
| middle_name    | varchar(40) | YES  |     | NULL     |                |
| age            | int(11)     | NO   |     | NULL     |                |
| current_status | varchar(40) | NO   |     | employed |                |
+----------------+-------------+------+-----+----------+----------------+

Reading Data In Tables

  • SELECT * FROM tablename: Read all data in the table, in the default order / how the table was created.
  • SELECT name FROM tablename: Show one column.
  • Can also be combined: SELECT name, age FROM tablename
  • Columns will be displayed in the order that the SELECT command is written; but again if it’s SELECT * it will display in the default order.

WHERE Keyword

  • Select by age: SELECT * FROM cats WHERE age=4; (INTs don’t require quotes)
  • Select by name: SELECT * FROM cats WHERE name='Egg'; (VARCHARs require quotes)
  • Queries are case-insensitive. Produces same result as previous: SELECT * FROM cats WHERE name='egG';
  • Can also compare columns to each other: SELECT cat_id, age FROM cats WHERE cat_id=age;

Aliases

  • Change the display of column names. Useful when joining tables which may have columns of the same name.
  • SELECT cat_id AS id, name FROM cats;: Renames cat_id column display to id
  • SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;: Can do multiple columns at once.

Updating Data In Tables

  • The format: UPDATE tablename SET column_name='new value' WHERE column_name='select value'
  • Change tabby cats to shorthair: UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';
  • Update Misty’s age: UPDATE cats SET age=14 WHERE name='Misty';

Deleting Data From Tables

  • The format: DELETE FROM table_name WHERE column_name="data"
  • Best to select first to ensure you have the right data: SELECT * FROM cats WHERE name='egg';
  • …THEN run the delete command: DELETE FROM cats WHERE name='egg';
  • DELETE FROM cats;: DELETES ALL ROWS IN THE TABLE!!!!!

Up Next

Tomorrow I’m scheduled to continue working on MySQL.

Tags icon Stacked price tag looking things Tags: