If you are unfamiliar with SQL and what MySQL commands is available to you. Here is a compilation of Resources
MySQL Documentation
This is the documentation you should refer to regarding MySQL
Setup MySQL DB Instance
Go to PlanetScale
Register an account
Click on Create for New Database
Create Environment Variables File
Copy the URL from your PlanetScale via Connect
Get Instance Information
Copy the following from your PlanetScale instance
Host
Username
Password
Database
Download & Install MySQL Workbench
Download & Install the application from https://dev.mysql.com/downloads/workbench/
Run the Application > Home > Click (+) in MySQL Connections
Enter the DB Instance information
You can view your database from Schemas
Currently there is no tables.
Type in your SQL statements into the editor
Click on the Lightning Icon to Execute
You will see the result below
If you go back to view Schemas, you will see the Table employees is created with the columns
Next you will learn all the fundamental of SQL Statements and Data Types sufficient for you to kick start some projects.
Data Types
To understand what are the Data Type you can use for the TABLE, you can use this as a reference.
List of Commonly Used Types
1 | TIMESTAMP | Stores both date and time values. | |
2 | INT | A integer in 4-byte size. | |
3 | VARCHAR | Its a variable-length character string, you can also use TEXT instead. | |
4 | BOOLEAN | Only have two value, TRUE or FALSE. | |
5 | SERIAL | Serial works similar to the integers except these are automatically generated in the columns by PostgreSQL. | |
6 | NUMERIC(x, y) | NUMERIC(Precision, Scale)
Useful for situation like prices etc, NUMERIC(6, 2) | |
You can read more here.
Create Table
Below is an example of how to create table and its columns
CREATE TABLE employees(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address TEXT,
salary NUMERIC(8, 2)
);You can read more here.
Drop Table
Below is an example of how to drop table if it exist.
DROP TABLE IF EXISTS employees;You can read more here.
Using SERIAL for ID
Lets say you want to create a list of fruits, you will need to create a TABLE.
You will likely need an ID for each row in running number. This is when you use SERIAL, usually for the PRIMARY KEY
CREATE TABLE employees(
id SERIAL PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address TEXT,
salary NUMERIC(8, 2)
);You can read more here
What is CRUD?
In computer programming, create, read, update, and delete (CRUD) are the four basic operations of persistent storage.
Get familiar with the command or statement that meant the same.
INSERT Statement
INSERT INTO employees (
name,
age,
address,
salary
)
VALUES
('Tom', 32, '23 Smith St', 999.99),
('James', 26, '39 Sims View', 5000.00),
('Sam', 45, '88 Oxley Rd', 65544.66);You can read more here
SELECT Statement
This is a statement to query data from the database table. The followings can be used with the SELECT statement are listed below:
FROM : It is used to specify a column in a table.
WHERE : It is used to filter rows from a table.
ORDER BY : It is used to sort table rows.
Here is an example how to select all columns and rows from table "employees"
SELECT * FROM employees;Here is an example set condition on the query and return the rows
SELECT *
FROM employees
WHERE age > 30;Here is an example how to sort the rows
SELECT
*
FROM
employees
WHERE
age > 30
ORDER BY
name DESC;Here is an example how to select only the name and salary
SELECT
name, salary
FROM
employees
WHERE
age > 30
ORDER BY
name DESC;You can read more on the following
UPDATE Statement
Here is an example how to update the name where the id is 1
UPDATE employees
SET name = 'May'
WHERE id = 1;You can read more on the following
DELETE Statement
Here is an example how to delete the row where the name is May
DELETE FROM employees
WHERE name = 'May';You can read more on the following
Комментарии