# MySQL Documentation: An Introductory guide

## Introduction
This document is meant to serve as a guide on the common tasks performed in MySQL. We will focus on basic procedures and tasks. For an in-depth explanation, please refer to the official documentation.

### Who is this guide for?
This user guide is made for users with an intermediate understanding of computer knowledge. It is written with the assumption that users have a basic understanding of relational databases.

### What Is MySQL
MySQL is a database management system (DBMS), in other words, a computer program that facilitates the easy storage and retrieval of data. It is widely used across many industries and applications, including business data warehousing, e-commerce and most commonly: data storage within computer applications.

MySQL is often used with MySQL workbench, a guided user interface (GUI) that allows users to use MySQL through menus, checkboxes and tables while maintaining all of MySQL’s features.

MySQL workbench is designed to make the use of the MySQL database management system easier and less error-prone.

<br>
### Software Requirements
Before installing this program, it is important to make sure that your computer has the capacity to run the program.
Database management systems can be power and memory intensive, we recommend installing the applications on machines with the following minimum hardware requirements:

### Computer Hardware Requirements

#### Minimum
* CPU: 64 bit x86 CPU
* RAM: 4Gb
* Display: 1024 x 768

#### Recommended
* CPU: Multi Core 64 bit x86
* RAM: 8Gb or Higher
* Display: 1920 x 1200 or Higher

### Installing MySQL
To install MySQL, please visit the official webpage:
<a name="MySQL Official webpage">https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/</a>

## Creating Databases, Tables and Inserting Data

### Creating A Connection
When using MySQL Workbench, you will first need to establish a connection, whether that is localhost which would be the root user or connecting to a database server such as JawsDB.
To Establish a connection, perform the following steps.

1. Click the '+' on the left side of the screen
2. A wizard should appear. Use 'root' as your username
3. When you first installed SQL you were prompted to input a password
4. Use that password to connect to the root user
5. Finally, Press "Test conection" and close that window
<br>
_{The homepage of MySQL Workbench :}

<br>

### Interface

After making the connection, you will be greeted with the MySQL Workbench interface.
For now, the two main sections we need to worry about are left side window titled **SCHEMAS** and the **Action Output** window at the bottom.
<br>
_{The MySQL workbench after logging in}
<br>

### Creating Schemas And Tables
In order to make tables we need to first make your database (sometimes referred to as Schema).

_{'create schema' is found on the top left}


#### Creating Schema
1. Right click on the window on the left side of the screen.
2. Click "Create Schema"
3. Set a meaningful name for the schema
4. Set "Default Charset" and "Collation" as Defaults
5. Finally, click 'apply' on the bottom right

_{Location of the 'Schema Name' field and the 'apply' button}
<br>

<br>

<b>Apply SQL to Database</b>
After clicking the 'apply' button on the 'schema editor' page, a dialogue window should appear, asking you to confirm whether the script is correct.

_{Dialogue window: confirm script}

<br>

If the SQL script was successfully executed, you should see the following confirmation message.


<br>
Now that your Schema is created, it could be found on the left side window in the **SCHEMAS** section

_{"Refresh Database" Button}

(Note: Sometimes you will need to refresh using the two arrows on the right hand some of the word **SCHEMA** in order for your schema or any changes to load)

<br>

:warning: <b>NOTE</b>
Before you create a table in the next step, make sure you are 'using' the correct database (schema)

Stating the

#### Creating A Table

Now that we created a database, we can store tables in there. We usually would store tables that are related to eachother in each database, hence the name Relational Database.

To Create a table, find the Schema that you made and click the drop down menu. you should see some more options and one of them will be "Tables", right click the tables option and press "Create Table".

<h4>You should see this:</h4>

after naming your table, you will have to select some options...

**Charset/Collation** should be set to **Default**
**Engine** should be set to **InnoDB**, If you do not specify InnoDB it will be assumed by MySQL.

(Note: If you set your table name before selecting the engine, the name of your table might reset to "new_table".)

Underneath the naming and seeing up of your table you will see this section where you can make your columns and give each column the settings that are provided such as PK (Primary Key) NN (Not NULL).


The first column is usually used as the primary key, so by convention the name of the PK will be whatever the name of your table is and you add an underscore and "id".

<h4> Example: </h4>


You can see in this screenshot that the PK is named as "example_id".

The boxes on the right side of the name are essential when using MySQL Workbench. In this screenshot for the PK what we have checked off is PK, NN, and AI.

**PK** - Primary Key
**NN** - Not NULL
**AI** - Auto Incremeent

**Setting a column as PK** will make that column the primary key for each row so each row is easily identefied, this is a crucial step becuase having rows that you can not identify will make it very difficult to work with the database.

**Setting a Column as NN** will make it so something has to be put into the column and make it required or else it will not run the commands to make the table. for example if we try to make a table and not have anything in the that cell where NN is set to true than the table will not be made.

**Setting a column AI** will eliminate the problem that could come up when something is set to NN. AI is very helpful when dealing with PK for a table becuase you want have to speicfy a number for each id but SQL will auto incremenet the first number used or if there is no number used it will start with 1 and increment each time a row is made.

<h3> Foriegn and Unique Keys</h3>

<h4>Foreign Keys (FK)</h4>

To add a FK (Foreign Key) or a UQ (Unique Key) you would do that when you are setting up your table.

So first, lets start with, what is a FK?. Lets say we have 2 tables, table A and table B. if we want to use data from table A into table B we would use the PK used in table A as a FK in table B.


"foreign_id" represents a PRIMARY KEY that is used in a different table and here we are using it as a FK.

we are not done yet...


Choose the "Foreign Keys" tab


Under the Foreign Key section, You will write the name of the FK you want to use, in this case we want to use "foreign_id" and under the "Refrenced Table" you will have a dropdown menu of the tables in your database that you can choose from. In this example we are refrencing a table called "foreign". on the right hand side you will select the FK you are making.

<h4> Unique Keys (UQ)</h4>

**What is a Unique Key?**
A Unique key is a key that you would not want to be repeated, for example, a users email. If you want users to only sign up one time per email than you would need the email to be a UQ.


We can do that by setting up the column normally and going to the "Indexes" tab instead of foreign keys.

<h4>Adding an email column...</h4>


we will follow similar steps to when we were making the FK and write email under "Index" and the "Type" will be a dropdown menu and you can choose "UNIQUE"

Now, select "email" on the right side, and hit "apply"



after hitting apply the UQ wil be automatically checked off, so no need for you to do that.

---------------------------

# SELECT
In the section below, we will be using a database called 'person_pet' to demonstrate how to use the select statement in MySQL.

:warning: NOTE:
Before running any statements, make sure that you select the correct database(aka schema), otherwise you will get an error.

When a schema is selected, it will appear bolded in the schema menu on the right

_{In the photo below, the person_pet schema is selected.}

<b>Typing SQL commands</b>
To type SQL commands, click on the SQL icon with a plus sign in the upper right corner of the page.


## Select all/ select columns
In example database 'person_pet' that we are using, there are 3 tables:
* person
* pet
* pet_type

You can find a list of the tables in the sidebar on the left.


<b>1. Enter the SQL statements in the window</b>
To select all the columns and row from the table 'person', write:

SELECT * 
FROM person;

<br>
The SELECT statement will return rows of values in a table.

The word after the FROM clause tells SQL which table you want to SELECT from. 'person' can be replaced with the name of any table, as long at that table is in the database you are currently using.
<br>

:warning: <b>NOTE</b>
- The <a href ="https://docs.oracle.com/javadb/10.6.2.1/ref/rrefclauses.html">clauses</a> (SELECT, FROM, WHERE... etc ) are capitalized and placed at the beginning of each line in SQL by convention.
- All SQL statements need to end with a semicolon ;
Otherwise you will get an error

<br>
The asterisk: *
Will cause MySQL to return all of the columns in the selected table

<br>
<b>2. Executing the command</b>

To execute the commands, click the lightning bolt icon on the top left of the window.

The icon WITH the line cursor (Green Arrow) will execute only the line selected lines of SQL commands.

The icon WITHOUT the line cursor (Red Arrow) will execute all the commands in the file.

_{Location of the icons that will execute the SQL commands}
<br>
<br>
If the SQL statements were executed successfully, you should see the results near the bottom of the page.
<br>
_{Query results will appear near the bottom of the window.}

<br>
<br>

## Select where
The WHERE clause is used to filter the results. The condition for filtering goes after the WHERE clause.
<br>
Suppose we have a table named 'person':
<br>

Now say we want to return all the rows where the last_name is 'Nygaard' we can execute this:

SELECT * 
FROM person
WHERE last_name = 'Nygaard'

<br>
If the code above works, all of the rows that we get back should have the value 'Nygaard' for the last_name column.

<br>


<br>
<br>
## Use of SELECT when altering or deleting data
When you execute a DELETE or ALTER statement in SQL, the statement cannot be undone.
It is standard practice to use the SELECT statement to see which rows will be affected to make sure you do not unintentionally alter the wrong columns.

____________________________________

# ALTER AND UPDATE TABLE

to alter a table, right click on the table and click "Alter Table..."


doing that will bring you back here where you can edit the settings of your table, the name, engine, and so on.

**Example:** If you want to change the setting of a column to allow NULL you would come back here, unselect the NN.


Another way you can Alter a table is through the Query Window using the "ALTER TABLE" statement.

**for adding a new column:**

**for deleting column:**

**for inserting data :**