Intro to SQL and Relational Databases: Build and Destroy

-

Overview

-

What is a database?

A database is an organized collection of data. A database will usually have some sort of api to query the data. Using a Structured Query Language (SQL) will allow you to interact with the data in a predictable way. A query in this case is a statement you ask a database. A properly made database will be able to answer a query with all relevent information.

-

Connecting to a database

-

MySQL

MySQL is a popular Relational Database Management System (RDBMS). It runs on most systems, so it can be run directly on your own machine. It is sometimes preferred to run it on a Docker instance running LINUX.

-

MySQL

In order to connect to a MySQL instance, you need to know a few things:

-

Installing MySQL on MacOS Catalina and later:

Catalina threw us a whammy!

To install MySQL successfully on Catalina, first make sure no other versions are running on your machine:

$ sudo ps ax | grep mysql

If you see more lines in the response than the one that contains grep mysql, then you’ll need to locate the mysql folders and remove them.

To search for them, press ⌘ and the space bar and enter mysqladmin into the prompt, followed by selecting “Show All In Finder” in the left rail.

-

Installing MySQL on MacOS Catalina and later (continued):

Once all the other versions are removed, reboot your computer and install the DMG package from: https://dev.mysql.com/downloads/mysql/

-

Installing MySQL on MacOS Catalina and later (continued):

Once you have successfully installed the package, add the mysql binary to the .bash_profile (if bash is your default terminal):

nano ~/.bash_profile

If zsh is your default terminal:

nano ~/.zshrc

At the bottom of the file, add the following line:

export PATH="$PATH:/usr/local/mysql/bin"

Quit Terminal, and restart it, opening a new Terminal window. You should now be able to connect to MySQL successfully. (You can avaid having to restart Terminal by using the following command: source ~/.bash_profile or source ~/.zshrc)

-

MySQL

Once installed, you can connect to your MySQL server via the command line:

$ mysql -u root -p

You will be prompted to enter the password:

Enter password:

Once the password is entered correctly, you will be connected, and a MySQL prompt will appear:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To disconnect, type exit. -

MySQL

Once you have connected, it is advised for security reasons that you create a new user. The current user is the root user which will always have all privileges, and it is insecure to use it for general purposes. To make a new user and give them permissions to do anything (ergo an admin user), you may use the following SQL Script:

CREATE USER new_user@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO new_user@localhost;

This will create a user with username new_user and password password. Note if you forget the root password and this password you will not have a way of resetting this.

-

MySQL

-

Creating a schema with tables

-

Schemas and Tables

Lets say we are designing a database for our backend developers. They are creating an app for teachers to keep track of their student’s assignments. We’ll also want to keep meta data on the teachers. Their application has Students, Teachers, Labs, and Submissions. Let’s try to go through designing this database

First we’ll need to make a schema. To do this we can run the following script

CREATE SCHEMA zipcode;

(the command CREATE DATABASE will do the same)

-

Schemas and Tables

What have we just done? We created a SCHEMA

-

Schemas and Tables: SHOW DATABASES

To display the databases that exist on your server, use the SHOW DATABASES command:

SHOW DATABASES;

Expected result:

+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| db_example            |
| mysql                 |
| performance_schema    |
| sys                   |
| zc_de001              |
| zipcode               |
+-----------------------+
7 rows in set (0.00 sec)

-

Schemas and Tables: USE

USE zipcode;

Expected result:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

-

Schemas and Tables: CREATE TABLE

-

Schemas and Tables: CREATE TABLE syntax

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype
);

-

Schemas and Tables: CREATE TABLE example

Teachers have a name and a specialty. We are going to create a table with these fields as well as a unique identifier. To create a a table we use the CREATE TABLE command. Let’s make the Teacher Table

CREATE TABLE zipcode.teachers
(
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    specialty ENUM('FRONT END', 'MIDDLE TIER', 'DATA TIER')
);

In this query we create each column followed by the properties of the columns. We are using the INTEGER, VARCHAR, and ENUM data types

-

Schemas and Tables: CREATE TABLE example

Next, let’s make a student table. The students will have a name, and a classroom as well notes from the teachers on a particular student

CREATE TABLE zipcode.students
(
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    classroom TINYTEXT,
    notes TEXT
);

In this query, we create each of these columns and are using INTEGER, VARCHAR, TINYTEXT, and TEXT.

-

Schemas and Tables: CREATE TABLE example

Next, we’ll need a table that shows the relationship between the assignments and the students (a pivot table)

CREATE TABLE zipcode.assignment_student
(
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL
);

-

Schemas and Tables: CREATE TABLE example

Lastly we need an assignment table. This should just have the assignment name and a link to the assignment

CREATE TABLE zipcode.assignments
(
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name TINYTEXT NOT NULL,
    URL CHAR(255) NOT NULL,
    teacher_id INTEGER
);

Here we are creating a table similar to the last two, but making the URL a fixed length CHAR field and making that unique. Why?

-

Schemas and Tables

Lastly, we’ll create the teacher_meta table. In the meta table, the developers want to keep track of the number of years a teacher has worked here, and the room number of the teacher’s office.

CREATE TABLE zipcode.teacher_meta
(
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    room_number TINYINT UNSIGNED,
    years TINYINT UNSIGNED
);

Here, we use the TINYINT column and make those unsigned

-

Schemas and Tables: SHOW TABLES

To display the tables in your chosen database (schema), use the SHOW TABLES command:

SHOW TABLES;

Expected result:

+--------------------+
| Tables_in_zipcode  |
+--------------------+
| assignment_student |
| assignments        |
| students           |
| teacher_meta       |
| teachers           |
+--------------------+
5 rows in set (0.00 sec)

-

Schemas and Tables: DESCRIBE

To display the details of a table settings, use the DESCRIBE command:

describe teachers;

Expected result:

+------------+---------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                        | Null | Key | Default | Extra          |
+------------+---------------------------------------------+------+-----+---------+----------------+
| id         | int(11)                                     | NO   | PRI | NULL    | auto_increment |
| specialty  | enum('FRONT END','MIDDLE TIER','DATA TIER') | YES  |     | NULL    |                |
| name       | varchar(50)                                 | NO   |     | NULL    |                |
+------------+---------------------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

-

Schemas and Tables: ALTER TABLE

Now we have tables, but we have learned that teachers are actually going to have a first name and last name, and the developers have said that they want these to be two separate fields. Before we continue, let’s ALTER this table.

-

Schemas and Tables: ALTER TABLE Syntax

ALTER TABLE table_name
    [alter_specification [, alter_specification] ...]

-

Schemas and Tables: ALTER TABLE ADD

ALTER TABLE ADD statement appends a new column to a table.

In this statement, you must specify the following: - the name of the table in which you want to add the new column - the name of the column, data type, and constraint if applicable

ALTER TABLE table_name
ADD column_name data_type column_constraint;

-

Schemas and Tables: ALTER TABLE ADD

If you want to add multiple columns to a table at once using a single ALTER TABLE statement, specify a comma-separated list of columns that you want to add to a table after the ADD clause:

ALTER TABLE table_name
ADD column_name_1 data_type_1 column_constraint_1,
ADD column_name_2 data_type_2 column_constraint_2,
    ...,
    column_name_n data_type_n column_constraint_n;

-

Schemas and Tables: ALTER TABLE CHANGE

Sometimes, you will need to rename a column and retain its data. For this, you will use CHANGE clause, specifying the following: - the name of the table in which you want to rename the column. - the name of the column that you want to rename and its desired settings.

ALTER TABLE table_name CHANGE old_name new_name data_type column_constraint;

-

Schemas and Tables: ALTER TABLE CHANGE

If you want to change multiple columns in the same statement:

ALTER TABLE table_name
    CHANGE old_name new_name data_type column_constraint,
    CHANGE old_name2 new_name3 data_type column_constraint,
    CHANGE old_name3 new_name3 data_type column_constraint ;

-

Schemas and Tables: ALTER TABLE DROP COLUMN

Sometimes, you need to remove one or more columns from a table. To do this, you use the ALTER TABLE DROP COLUMN statement. In this statement, you must specify the following: - the name of the table from which you want to delete the column. - the name of the column that you want to delete.

ALTER TABLE table_name
DROP COLUMN column_name;

-

Schemas and Tables: ALTER TABLE DROP COLUMN - a note

Sometimes a constraint on the column will require additional steps before dropping it. For example, if the column that you want to delete has a CHECK constraint, you must delete the constraint first before removing the column.

We’ll more talk about adding and dropping contraints later.

-

Schemas and Tables: ALTER TABLE DROP COLUMN Syntax

To delete multiple columns at once, specify columns that you want to drop as a list of comma-separated columns in the DROP COLUMN clause:

ALTER TABLE table_name
DROP COLUMN column_name_1, DROP COLUMN column_name_2,...;

Now, back to our updates as requested by the developers.

-

Schemas and Tables: ALTER TABLE example

For our change according to developer request, we are going to ADD firstname and lastname columns. Then, we will DROP the name column.

ALTER TABLE zipcode.teachers
    ADD firstname VARCHAR(25) NOT NULL,
    ADD lastname VARCHAR(25) NOT NULL;

ALTER TABLE zipcode.teachers DROP name;

This will add our new columns to the teachers table, in the order they were entered, and remove the name column completely.

-

Schemas and Tables: ALTER TABLE example

After our updates, the developers have requested that we change the firstname and lastname columns to first_name and last_name.

ALTER TABLE teachers CHANGE firstname first_name varchar(255);

This change the names of the columns, while leaving it’s underlying settings, and their content intact in the teachers table.

-

Seeding a Database

-

Seeding a Database: INSERT

Our database is set up for our devs to use, but they’ve asked us to create some mock data for them to demo their app. To do this we’ll have to INSERT some data.

Let’s start by inserting a few teachers.

-

Seeding a Database: INSERT

Puts data into a table. Inserts will generally have 3 parts:

Let’s create a teacher, and then that teacher’s meta info.

-

Seeding a Database: INSERT syntax – No column names

-

Seeding a Database: INSERT example – No column names

INSERT INTO zipcode.teachers
    VALUES ('FRONT END', 'John', 'Smith');

-

Seeding a Database: INSERT syntax – With column names

INSERT INTO table(column1_name, column2_name)
VALUES(column1_value, column2_value);

-

Seeding a Database: INSERT example – With column names

INSERT INTO zipcode.teachers (first_name, last_name, specialty)
    VALUES ('John', 'Smith', 'FRONT END');

Here we are creating a new row in the teachers table. The first_name is set to John, the last_name is set to Smith, and the specialty is set to FRONT END. Note that the order of the columns we listed will be the order we list the values. If there is a different number of columns and values, then this will throw an error. Also note that since the id on this table is set to auto increment, it will automatically be filled in with the id of 1.

-

Inserts

We now have one teacher in this table and we can add a few more, but instead of running them one by one, we can also just add many at the same time.

INSERT INTO zipcode.teachers (first_name, last_name, specialty)
    VALUES ('Tabitha', 'Schultz', 'MIDDLE TIER'),
      ('Jane', 'Herman', 'DATA TIER');

-

Inserts

With the ability to insert data, we can also start populating the other tables

INSERT INTO zipcode.teacher_meta (teacher_id, years, room_number)
    VALUES (1, 3, 2),
      (2, 3, 2),
      (3, 10, 1);

INSERT INTO zipcode.students (name, classroom, notes)
  VALUES  ('Linnell McLanachan', '1A', 'Likes Data'),
	('Lorianna Henrion', '1A', 'Loves Data'),
	('Corena Edgeson', '1A', 'Cannot get enough of data'),
	('Archaimbaud Lougheid', '2A', 'Would rather do nothing other than sit down in front of a mountain of data and read through it like a book. SERIOUSLY needs to seek help about this because there is no way it is healthy for this person to like data any more than they do'),
	('Dun Pettet', '2A', NULL ),
	('Hymie Parrington', '2A', 'Enjoys the Star Wars prequels');

-

Inserts

INSERT INTO zipcode.assignments (name, URL, teacher_id)
  VALUES ('Pokemon Lab', 'https://github.com/Zipcoder/PokemonSqlLab', 3),
  ('Poll App', 'https://github.com/Zipcoder/CR-MacroLabs-Spring-QuickPollApplication', 3),
  ('Sum or Product', 'https://github.com/Zipcoder/ZCW-MicroLabs-JavaFundamentals-SumOrProduct', 2);


INSERT INTO zipcode.assignment_student (assignment_id, student_id)
    VALUES (1, 1), (1, 2), (1,3), (1,4), (1,5), (1,6), (2, 1);

-

Updates

Updating data is very similar to inserting, but we will use an UPDATE clause instead. For this we will need to specify a table we want to update, then a SET clause to specify how to change a field or fields.

Our devs inform us that they want a way to increment the number of years each teacher has worked here. Let’s see how we would write that update statement

-

Updates

UPDATE zipcode.teacher_meta
SET years = years+1;

Here we are able to increment the years by setting years = to whatever its own value is, plus one. This works because SQL will go row by row. In each row, the years variable is set to whatever the value is in that particular row.

-

ALTER TABLE

-