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.
Search for Cloud SQL
An instance has been created called financial, click on the instance
Scroll down to the link that says Open Cloud Shell and click
Authorize access
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.
john_iacovacci1@cloudshell:~ (sentiment-analysis-379200)$ gcloud sql connect financial --user=root --quiet
Hit Enter at the end of the string to connect to the sql database
Starting Cloud SQL Proxy: [/usr/bin/cloud-sql-proxy sentiment-analysis-379200:us-east1:financial --port 9470]
2026/02/15 14:31:38 Authorizing with Application Default Credentials
2026/02/15 14:31:38 [sentiment-analysis-379200:us-east1:financial] Listening on 127.0.0.1:9470
2026/02/15 14:31:38 The proxy has started successfully and is ready for new connections!
Connecting to database with SQL user [root].Enter password:
Password will not show up type uconnstamford
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 266
Server version: 8.0.43-google (Google)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
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: Don’t need to
mysql> CREATE database trading;
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 trading;
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
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 security information
Security Table
Key FIGI -unique primary key
NAME - text
TICKER- text
ExchangeCd - text
SecurityType - text
MarketSector - text
FIGI Composite - text
ShareClass - text
mysql> CREATE TABLE security ( FIGI varchar(255), NAME varchar(255), TICKER varchar(20), ExchangeCd varchar(10), SecurityType varchar(20), MarketSector varchar(50), FIGIComposite varchar(20), ShareClass varchar(20), PRIMARY KEY(FIGI));
Query OK, 0 rows affected (0.11 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> INSERT INTO security (FIGI, NAME, TICKER, ExchangeCd, SecurityType, MarketSector, FIGIComposite, ShareClass) values ("BBG000BJ29X7", "SS FINANCIAL SELECT SECTOR", "XLF", "US", "ETP", "Equity","BBG000BJ29X7","BBG001S7T223");
Query OK, 1 row affected (0.05 sec)
mysql>
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 security;
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------------+
| FIGI | NAME | TICKER | ExchangeCd | SecurityType | MarketSector | FIGIComposite | ShareClass |
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------------+
| BBG000BJ29X7 | SS FINANCIAL SELECT SECTOR | XLF | US | ETP | Equity | BBG000BJ29X7 | BBG001S7T223 |
+--------------+----------------------------+--------+------------+--------------+--------------+---------------+--------------+
1 row in set (0.00 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
The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
No comments:
Post a Comment