MySQL Tables

 

Creating and managing MySQL databases

Go to SQL

Note: For this exercise everyone will access the same project

An email will go out to gmail account to accept project

You now have access to the project

A database instance set of memory structures manage database files.

Set of physical files created by the CREATE DATABASE statement. 

Instance manages its data and serves the users of the database.

Select customer instance

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:

To create a database on the Cloud SQL instance:

Click OPEN 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

Note: Create a database with first initial and last name.

mysql> CREATE database uconntrade;

Query OK, 1 row affected (0.03 sec)

USE (database name);

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

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

mysql> use uconntrade;

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 column should always accept an explicit value of the given data

AUTO INCREMENT used for auto incrementing a value of a field .

primary key field in a table uniquely identifies each record in a database.

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

Create a state table for validation

Define your table information

Account table

Number  - int (unique primary key)

Name - text

Address - text

City - text

StateCode - text

ZipCode = int

eMail - text

Phone - text

Same in all multi-line code below. One line is to view the command the smaller line can be copied directly into the sql command line.

CREATE TABLE account ( Number int, Name 

varchar(255), Address varchar(255), City 

varchar(255), StateCode CHAR(2), ZipCode int, eMail varchar(255), Phone varchar(255),  PRIMARY KEY(Number));

Query OK, 0 rows affected (0.07 sec)

SQL INSERT INTO Statement is used to add new rows of data to a table.

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

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

Insert multiple rows with one insert statement by adding comma's.

MySQL Commands

INSERT INTO account (Number, Name, Address, City, StateCode, ZipCode, eMail, Phone ) values (100,"John Iacovacci", "1 University Pl","Stamford", "CT", 06901,

"john.iacovacci1@gmail.com","917-701-6177");

        

Query OK, 1 row affected (0.04 sec)

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> mysql> select * from account;

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

| Number | Name           | Address         | City     | StateCode | ZipCode | eMail                     | Phone        |

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

|    100 | John Iacovacci | 1 University Pl | Stamford | CT        |    6901 | john.iacovacci1@gmail.com | 917-701-6177 |

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

1 row in set (0.04 sec)

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)