P1-S13|
@hb_mulesoftTechworld
SQL Basics
=========
Data is nothing but the information
Eg: Person, Location/address, Object
Types of Data
Structured data : Relational Data
Pre-defined structure
Rows and Columns
RDBMS - Relational Database Management System (Oracle, MySql, MS-Sql, Db2)
Un-structured data : Non-relational data
dynamic in structure, evolve with the new requirements very quickly
Column based rather than rows based information
Key Value pairs
NO-SQL databases ( Mongo DB, Redis, Cassandra)
These days the world is moving towards digital data.
Which means the files or books of information is now stored in machines.
To store the information in machines or systems we need use some sort of Databases to understand and fulfil the needs of an organisation.
Because we need to use the data and perform the activities
To analyse the orders received and delivered
To analyse the expenses of organisation
To analyse the hire/attrition rate of employees with in a company
To analyse the number of sales in a month and compare
To expose the data and use them in other applications across organisation
RDBMS
::
Create a database
Create a tables
columns
Table
Columns:Column1, Column2
Row1:key1,value1
row2:key2,value2
SQL -- Structured Query Language
CRUD operations
CREATE - INSERT record
READ - SELECT record/records / Select query operation
UPDATE - UPDATE record
DELETE - DELETE record
SELECT *
FROM tablename
WHERE colname = 'value1';
UPDATE tablename
SET colname2= value2
WHERE colname1=value1
;
DELETE FROM tablename
WHERE colname= value
;
show databases;
CREATE DATABASE HBT_NEW;
USE HBT;
CREATE TABLE TABLE_NAME1 (
COL1 INT AUTO_INCREMENT,
COL2 DATA_TYPE
);
CONSTRAINTS
--
WHAT KIND OF INFROMATION WE CAN STORE IN A COLUMN
PRIMARY KEY - we can not store null values, duplicate values
NOT NULL - we can not store null values
UNIQUE - we can not store duplicate values
DEFAULT - if value is not provided , then default value will be stored
===
Mulesoft DB Connector with Use cases
:: We can use this as Message processor (source) by adding scheduler as source in flow.
We will use this connector to connect with most of the databases.
eg: we will make use of our MySql database.
MY SQL DB details
===========
host-localhost
port-3306
user-root
pwd-***
db-mysql
From mulesoft
We need drivers to be installed in our project
My Sql Database Suppliers
==================
Create suppliers record in database using mulesoft service.
One flow to get supplier details from db using select query
get supplier details from db using select query with parameter
Format the db response to understandable json format
Update the suppliername, zip based on supplier number
Delete the supplier record based on supplier number
=================
CREATE Database HBT;
USE HBT;
show databases;
CREATE TABLE hbt.suppliers(
supplier_number varchar(30) PRIMARY KEY,
supplier_name varchar(30) NOT NULL UNIQUE,
address varchar(50),
zip varchar(30),
created_by varchar(30),
creation_date date,
last_updated_by varchar(30),
last_updation_date date
);
SELECT *
FROM hbt.suppliers
;
SELECT ISNULL(null);
INSERT INTO tablename (col1, col2) values(value1, value2);
INSERT INTO hbt.suppliers(supplier_number, supplier_name, address, zip, created_by, creation_date, last_updation_date, last_updated_by )
VALUES('Sup1', 'Human Brains Supplychain', 'Kadapa','500101', 'Chandra','2021-12-14', '2021-12-14', 'MySql')
;
UPDATE hbt.suppliers
SET zip='500110'
WHERE supplier_number = 'Sup1'
;
DELETE FROM hbt.suppliers
WHERE supplier_number = 'Sup1'
;
select supplier_number, supplier_name, created_by, creation_date from hbt.suppliers;
Watch video P1-S13| online without registration, duration hours minute second in high quality. This video was added by user Mulesoft 17 December 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 29 once and liked it people.