MySQL Tables

Creating and managing

MySQL databases

RDBMS

RDBMS stands for Relational Database Management System.

Basis for SQL, and for all modern database systems such as MS SQL.

Data is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

Using the python Invoicer example we will create a set of tables to process the data.

Everyone will see an email invitation to accept the project

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

select projet Sentiment Analysis

Need to click on project box to select sentiment analysis project

Once selected navigate via search box to cloud sql

fff

Click into customer instance

NNeed to click Open Cloud Shell in order to access MySQL environment

EnEnter password : uconnstamford

Welcome to Cloud Shell! Type "help" to get started, or type "gemini" to try prompting with Gemini CLI.

Your Cloud Platform project in this session is set to sentiment-analysis-379200.

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

bg6stamford@cloudshell:~ (sentiment-analysis-379200)$ gcloud sql connect customer --user=root --quiet <hit enter>

Allowlisting your IP for incoming connection for 5 minutes...done.

Connecting to database with SQL user [root].Enter password: uconnstamford <hit enter>

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

Your MySQL connection id is 41395

Server version: 8.0.41-google (Google)

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.

mysql>

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.

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:

Now create a database

CREATE DATABASE (database name);

Creates a new database with name as parameter

SHOW DATABASES;

We use the DROP DATABASE statement to drop a SQL database.

DROP DATABASE databasename;

Note: Create a database with first initial and last name.
mysql> create database kitchenware;

mysql> CREATE database jiacovacci;

Note: you only have to CREATE database the first time.

After it is created you only need to execute use jiacovacci;

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 jiacovacci;

Data Definition Language describes the portion of SQL that creates, alters,

and deletes database objects. These database objects include schemas,

tables, views, sequences, catalogs, indexes, variables, masks,

permissions, and aliases.

Database changed

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

....

);

We can remove a table by using the DROP command.

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.

The column parameters specify the names of the columns of the table.

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

Datatype is the type of data to store.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE

TABLE statement, or after the table is created with the ALTER TABLE

Statement.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

column should always accept an explicit value of the given data.

UNIQUE - Ensures that all values in a column are different.

PRIMARY KEY - A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table.

FOREIGN KEY - Prevents actions that would destroy links between tables.

CHECK - Ensures that the values in a column satisfies a specific condition.

DEFAULT - Sets a default value for a column if no value is specified.

CREATE INDEX - Used to create and retrieve data from the database very quickly.

AUTO INCREMENT used for incrementing a value of a field .

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Create a table within the database.

This table will hold customer information

Define your table information

Customer table

Key email - unique primary key

LastName - text

FirstName - text

Address - text

City - text

St code -text

ZIP code = int

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

mysql> CREATE TABLE customer ( email varchar(255), LastName

-> varchar(255),FirstName varchar(255), Address varchar(255), City

-> varchar(255), StateCode Varchar(2), ZipCode int, PRIMARY

-> KEY(email));

Query OK, 0 rows affected (0.24 sec)

Create a table for the items you are selling

Inventory Table

Item - text unique Primary key

Description - text

Price - float

mysql> CREATE TABLE inventory (ItemNo varchar(255), Description varchar(255), Price float(6,2), PRIMARY KEY(ItemNo));

Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql>

Create a table for orders

InvoiceNo - int Unique Primary key

Email - text

OrderDate - date

CREATE TABLE orders ( InvoiceNo int AUTO_INCREMENT, eMail varchar(255), orderDate DATE, PRIMARY KEY(InvoiceNo));

mysql> CREATE TABLE orders ( InvoiceNo int AUTO_INCREMENT, eMail varchar(255), orderDate DATE, PRIMARY KEY(InvoiceNo));

Query OK, 0 rows affected (0.07 sec)

Create a table for order details

InvoiceNo - int Unique Primary key

ItemNo - test

pQuantity - int

Pcost - float

CREATE TABLE orderdetails ( InvoiceNo int, itemNo varchar(255), pQuantity int, pCost float(6.2));

mysql> CREATE TABLE ordersdetails ( InvoiceNo int, itemNo varchar(255), pQuantity int, pCost float(6.2));

Query OK, 0 rows affected (0.15 sec)

mysql>

mysql> show tables;

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

| Tables_in_kitchenware |

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

| customer |

| inventory |

| orders |

| orderdetails |

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

4 rows in set (0.03 sec)

mysql>

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.

INSERT INTO MyTable ( Column1, Column2, Column3 ) VALUES ('John', 123, 'Lloyds Office'), ('Jane', 124, 'Lloyds Office'), ('Billy', 125, 'London Office'), ('Miranda', 126, 'Bristol Office');

MySQL Commands

INSERT INTO customer (email, LastName, FirstName, Address, City,

StateCode, ZipCode) values ("john.iacovacci1@gmail.com", "Iacovacci","John", "1 University Pl","Stamford", "CT", 06901);

mysql>

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

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.07 sec)

mysql>

Query OK, 1 row affected (0.04 sec)

Database changed

INSERT into inventory (ItemNo, Description, Price) values ("P100","Plates",4.00), ("C100","Cups",3.00), ("G100","Glasses",2.00);

mysql> INSERT into inventory (ItemNo, Description, Price) values ("P100","Plates",4.00), ("C100","Cups",3.00), ("G100","Glasses",2.00)

-> ;

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>

INSERT INTO orders (eMail, orderDate) values ("john.iacovacci1@gmail.com", "2025-09-29");

mysql> INSERT INTO orders (eMail, orderDate) values ("john.iacovacci1@gmail.com", "2024-09-29");

Query OK, 1 row affected (0.04 sec)

mysql>

To create records for orderdetails you need the auto generated invoice number;

To obtain that value I used SELECT * FROM orders;

mysql> select * from orders;

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

| InvoiceNo | eMail | orderDate |

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

| 1 | john.iacovacci1@gmail.com | 2024-09-29 |

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

1 row in set (0.04 sec)

Now I can insert my order details

INSERT into orderdetails (InvoiceNo, itemNo, pQuantity, pCost) values (1, "P100", 4, 4.00),(1, "C100", 4, 3.00),(1, "G100", 4, 2.00);

mysql> INSERT into orderdetails (InvoiceNo, itemNo, pQuantity, pCost) values (1, "P100", 4, 4.00),(1, "C100", 4, 3.00),(1, "G100", 4, 2.00);

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

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;

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

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

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

1 row in set (0.03 sec)

mysql> select * from inventory;

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

| ItemNo | Description | Price |

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

| C100 | Cups | 3.00 |

| G100 | Glasses | 2.00 |

| P100 | Plates | 4.00 |

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

3 rows in set (0.04 sec)

mysql>

mysql> select * from orders;

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

| InvoiceNo | eMail | orderDate |

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

| 1 | john.iacovacci1@gmail.com | 2024-09-29 |

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

1 row in set (0.03 sec)

mysql>

mysql> RENAME TABLE ordersdetails TO orderdetails;

Query OK, 0 rows affected (0.08 sec)

mysql>

mysql> select * from orderdetails;

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

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

| 1 | P100 | 4 | 4 |

| 1 | C100 | 4 | 3 |

| 1 | G100 | 4 | 2 |

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

3 rows in set (0.03 sec)

mysql>

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

SELECT column1, column2, ...

FROM table_name;

mysql> SELECT FirstName, LastName FROM customer where email="john.iacovacci1@gmail.com";

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

| FirstName | LastName |

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

| John | Iacovacci |

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

1 row in set (0.03 sec)

mysql>

mysql> SELECT customer.FirstName, customer.LastName, orderdetails.InvoiceNo, orderdetails.itemNo, orderdetails.pQuantity, orderdetails.pCost FROM customer, orderdetails where customer.email="john.iacovacci1@gmail.com" and orderdetails.InvoiceNo=1;

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

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

| John | Iacovacci | 1 | P100 | 4 | 4 |

| John | Iacovacci | 1 | C100 | 4 | 3 |

| John | Iacovacci | 1 | G100 | 4 | 2 |

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

3 rows in set (0.03 sec)

mysql>

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

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

mysql> update orderdetails SET pQuantity = 8 where InvoiceNo = 1 and itemNo = "G100";

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql>

mysql> SELECT * FROM orderdetails where InvoiceNo = 1 and itemNo = "G100";

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

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

| 1 | G100 | 8 | 2 |

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

1 row in set (0.03 sec)

mysql>

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

UCONN Stamford Google Cloud Development Platform

UCONN

MySQL Tables

MySQL Tables

Creating and managing

MySQL databases

SQL Create Constraints

No comments:

Post a Comment

Assignment #9 Due 11/14/25

Report Abuse