MySQL Exercise

 

Creating and managing MySQL databases

Before you begin

Before completing the tasks on this page, you must have:

• Created the Cloud SQL instance.

Creating instances

CLICK ON SQL under STORAGE section

Create an  Instance 

A database instance is a set of memory structures that manage database files.

A database is a set of physical files on disk created by the CREATE DATABASE

statement. The instance manages its associated data and serves the users of the

database.

CREATE A NEW INSTANCE

Choose MySQL

Choose a name for your instance customer

Password uconnstamford

I also selected us-central1 for the region

Leave MySQL 5.7 as version

Then hit Create

Creating a database

Follow the rules in Schema Object Names when you create your database name. If not set on creation, new databases have the following default values:

• Character set: utf8

• Collation: utf8_general_ci

To create a database on the Cloud SQL instance:

Click on Connect using Cloud Shell

Welcome to Cloud Shell! Type "help" to get started.

Your Cloud Platform project in this session is set to uconn-engr.

Use “gcloud config set project [PROJECT_ID]” to change to a different project.

john_iacovacci1@cloudshell:~ (uconn-engr)$ gcloud sql connect customer --user=root --quiet <hit enter>

Allow listing your IP for incoming connection for 5 minutes...done.

Connecting to database with SQL user [root].Enter password: uconnstamford

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 331

Server version: 5.7.25-google-log (Google)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

Now create a database

CREATE DATABASE (database name);

Creates a new database with name as parameter

mysql> CREATE database customer;

Query OK, 1 row affected (0.03 sec)

USE (database name);

 you must select it for use each time you begin a mysql session. 

The SQL USE statement is used to select any existing database in the SQL schema.

mysql> use customer;

Database changed

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

   ....

);

Table name is the database table name being created within the database. Databases may have many tables in them and tables can be related to each via relationship or field.

Column is the name of the field you wish to store in the table.

Datatype is the type of data to store. 

E.G.

• Numeric - INT, FLOAT, DECIMAL

• Date and Time - DATE, TIME

• String Types - CHAR, VARCHAR, TEXT

NOT NULL signifies that column should always accept an explicit value of the given data

AUTO INCREMENT is a keyword used for auto incrementing a value of a field in the table.

A primary key is a field in a table which uniquely identifies each row/record in a database table.

create a table within the database - this table will hold customer information

Create a state table for validation

Define your table information

Customer table

Key email - unique primary key

Name - text

Address - text

City - text

St code -text

ZIP code = int

State Table

State code - text

State name - text

mysql> CREATE TABLE customer (email varchar(255), LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255), StateCode Varchar(255), ZipCode int, PRIMARY KEY(email));

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE states (StateCode varchar(255), StateName varchar(255), PRIMARY KEY(StateCode));

Query OK, 0 rows affected (0.05 sec)

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

VALUES is the value of that will be stored in the column name.

INSERT INTO (table name) column name VALUES "data";

May insert multiple rows to a table with one insert statement by adding comma's.

MySQL Commands

mysql> INSERT INTO customer (email, LastName, FirstName, Address, City, StateCode, ZipCode) values ("john.iacovacci1@gmail.com", "Iacovacci", "John", "1 University Pl","Stamford", "CT", 06901);

Query OK, 1 row affected (0.04 sec)

mysql> insert into states (StateCode, StateName) values ("CT","Connecticut");

Query OK, 1 row affected (0.04 sec)

mysql>

SELECT (field list or *) FROM (table name) WHERE column name = "value";

SELECT * FROM (table name) 

lists all records

Select (column name) FROM (table name) WHERE (column name) = "value";

mysql> select * from customer;

+---------------------------+-----------+-----------+-----------------+----------+-----------+---------+

| email                     | LastName  | FirstName | Address         | City     | StateCode | ZipCode |

+---------------------------+-----------+-----------+-----------------+----------+-----------+---------+

| john.iacovacci1@gmail.com | Iacovacci | John      | 1 University Pl | Stamford | CT        |    6901 |

+---------------------------+-----------+-----------+-----------------+----------+-----------+---------+

1 row in set (0.03 sec)

mysql> select * from states;

+-----------+-------------+

| StateCode | StateName   |

+-----------+-------------+

| CT        | Connecticut |

+-----------+-------------+

1 row in set (0.03 sec)

mysql>

mysql> select customer.FirstName, customer.LastName, states.StateName from customer, states where custome

r.StateCode = states.StateCode;

+-----------+-----------+-------------+

| FirstName | LastName  | StateName   |

+-----------+-----------+-------------+

| John      | Iacovacci | Connecticut |

+-----------+-----------+-------------+

1 row in set (0.03 sec)

mysql>

DELETE FROM table_name WHERE condition;

Deletes a record from a table where column name = value.

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Updates a record in a table by setting a value of a column where a condition is met (email = "john.iacovacci1@gmail.com)