MYSQL DATABASE.

Crud operations in the MySQL database.

For beginners lets understand the SQL database. Structured query language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the relationships between the data values. SQL statements are used to perform tasks such as updating data on a database or retrieving data from a database. A database is a structured collection of data.

Though often associated with internet applications or web services, MySQL was designed to be extensively compatible with other technologies and architectures. The Relational Database Management System (RDBMS) runs on all major computing platforms, including Unix-based operating systems, such as the myriad Linux distributions or Mac OS, and Windows. So everyone can use MySQL database regardless the Operating System.

Databases like MySQL contain records in multiple, separate, and highly codified tables, as opposed to a single all-encompassing repository, or collections of semi- or unstructured documents.

This allows RDBMS(s) to better optimize actions like data retrieval, updating information, or more complex actions like aggregations. A logical model is defined over all of the contents of the database, describing for example the values allowed in individual columns, characteristics of tables and views, or how indices from two tables are related.

Some of the SQL Database Management Systems are MySQL, Oracle, PostgreSQL, SQLite, and many others. But today let’s understand MySQL. MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL).

Anyways, you have to connect to the database. Crane Cloud provides remote databases for users, connect to a remote MySQL database.

SQL is composed of four sub-languages, each with a different scope.

DQL: The Data Query Language (DQL) is the most familiar and is used to run queries on databases and extract information from stored data. For example, selecting and returning the maximum value in a column.

Select :

SELECT * FROM <table_name>

DDL: A Data Definition Language (DDL) is used to codify a database’s particular structures and schemas. Creating a table or defining data types is an example.

Create table :

CREATE TABLE <table_name>;

Forexample

CREATE TABLE trial_table (
 ID.No INT PRIMARY KEY,
 Name VARCHAR(255),
 country VARCHAR(255),
 gender enum( 'female',' male') not null,
);

Create database :

CREATE DATABASE <database_name>;

Alter table :

The alter command helps to modify the table by either adding or deleting a column from the table.

ALTER TABLE <table_name> ADD <column_name> [datatype];

or 

ALTER TABLE <table_name> DROP <column_name>;

DML: A Data Manipulation Language is used to make modifications to existing components of a database, like inserting records, updating values in cells, or deleting data.

Insert :

INSERT INTO <table_name> VALUES (Value1, Value2, Value3);

Forexample

INSERT INTO trial_table (ID.No, Name, country, gender) VALUES ("200","Mukisa Havi","Uganda","male");  

Update :

UPDATE <table_name> SET <column_name> = "new value" WHERE <column_name> = "value";  

In the example above where we insert the data into the table, we can update a given attribute. For instance changing the country to “Kenya” by using the UPDATE command statement.

UPDATE trial_table SET country = "Kenya" WHERE ID.No = "200" ;

Delete :

— This can delete a database.

DROP DATABASE <database_name>;

— This is to delete a table

DROP TABLE <table_name>;

DCL: A Data Control Language defines access, authorizations, and permissions for users and processes accessing the database, including granting administrator privileges, or restricting users to read-only privileges only. Here we use the GRANT command to allow specified users to perform specified tasks and also use the REVOKE command to remove user access to the database objects.

TCL: Finally, Transnational Control Language is used to control transitions in the database. For this case, we get to use the BEGIN, TRAN, COMMIT, and ROLLBACK commands.

illustration for the SQL language statements

Data Types in SQL.

Data types are used to represent the nature of the data that can be stored in a database table. The data type is assigned to the attributes so that the attribute can accept the correct type of data. Following are the different types of data types in SQL:

There are three categories for the data-types:

1. STRING TYPES:

# char(n)- It is a fixed-width character string data type. Its size is up to 8000 characters.
# varchar(n)- It is a variable-width character string data type. Its size is up to 65,535 characters.
# Blob (Binary Large Objects) — used to store binary data.
# Text — is used for storing a large number of strings.
# Enum (‘a’,’b’) or null / not null — this allows one to pick among the given values.

2. NUMERIC DATA-TYPES:

# bit- Accepted data in this type are 0, 1, or null.
# Int- Allows the number of Integers.
# Tiny-int — it allows 128 to 127 width of four digits.
# Small-int — it ranges up to 5 digits.
# Medium-int — it allows up to 9 digits.
# Big-int — it allows up to 20 digits.
# Money- It is used for monetary data.
# Float — it allows digits to have decimal numbers.

3. DATE AND TIME:
# Date — YYYY-MM-DD format

# Date-time — YYYY-MM-DD HH:MM: SS

A quick reminder, you now don’t need to have a local MySQL server, Crane Cloud provides MySQL databases for you for free. And is secure since it also provides the MySQL credentials to be used to log in to the database (username, database name, password, host IP)

Thank you for taking time to read through. Wait for more commands in MySQL coming soon! But right now you are good to create , insert , update , delete tables and also retrieve data in the tables.