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.

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.

Choose MySQL

instance name:  customer

Password: uconnstamford

Select us-east1 for the region

Zonal availability Single zone

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

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;

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 Data Types

Each column in a table is required to have a name and a data type.

Developers decide what type of data to be stored in each column. 

E.G.

• Numeric - INT, FLOAT, DECIMAL

• Date and Time - DATE, TIME

• String Types - CHAR, VARCHAR, TEXT

Data type	Description
CHAR(size)	A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1
VARCHAR(size)	A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters - can be from 0 to 65535
BINARY(size)	Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1
TEXT(size)	Holds a string with a maximum length of 65,535 bytes
BLOB(size)	For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data
BIT(size)	A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.
BOOL	Zero is considered as false, nonzero values are considered as true.
INT(size)	A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)	A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions
FLOAT(p)	A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()
DATE	A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
DATETIME(fsp)	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time
TIMESTAMP(fsp)	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition
TIME(fsp)	A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.

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

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 a table for order details

InvoiceNo - int Unique Primary key

ItemNo - test

PNumber - int

Pcost - float

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

mysql> CREATE TABLE purchases (invoiceNo int NOT NULL AUTO_INCREMENT, ItemNo varchar(255), email varchar(255), PNumber int,  PRIMARY 

    -> KEY(InvoiceNo), FOREIGN KEY (ItemNo) REFERENCES inventory(ItemNo));

Query OK, 0 rows affected (0.13 sec)

mysql> 

CREATE TABLE ordersdetails ( 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                |

| ordersdetails         |

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

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", "2024-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 ordersdetails (InvoiceNo, itemNo, pQuantity, pCost) values (1, "P100", 4, 4.00),(1, "C100", 4, 3.00),(1, "G100", 4, 2.00);

mysql> INSERT into ordersdetails (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;

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

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

I created the table name wrong so I used RENAME to change.

mysql> RENAME TABLE ordersdetails TO orderdetails;

Query OK, 0 rows affected (0.08 sec)

mysql> select * from orderdetails;

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

| InvoiceNo | itemNo | pQuantity | pCost |

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

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

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

| FirstName | LastName  | InvoiceNo | itemNo | pQuantity | pCost |

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

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

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

| InvoiceNo | itemNo | pQuantity | pCost |

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

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