In this article series, we are going to understand more about DBMS.
What is SQL?
SQL stands for Structured Query Language
SQL can be used to communicate with any data base to perform different kinds of operations.
Data base contains data in the form of tables and if we want to perform operations on table like update the existing data, delete some data, retrieve the data we need to communicate with database using a language that data base understands like structured query language.
Data base components:
Every data base has 2 components
1)Client : This is process in which the request is sent to data to perform some operations.
⦁ It doesn’t use much of the data.
⦁ We can send sql commands using client software.
Further, there are 2 types of clients available for any type of data base.
a)GUI (Graphical Mode Interface)
b)CLI(Command Line Interface)
Note:
For CLI we need to enter commands manually.
Example:
Oracle Data Base has multiple clients among them Sql developer is a gui client and sql+ is command line client.(these 2 are the default clients).
Similarly for Mysql: MYSQL workbench(gui), MYSQL commandlinetool(cli)
Note:
When we install a data base these would be the default clients for other clients we need to install them.
2)Server : This is to store the date hence it uses memory.
Note:
⦁ All SQL commands will be executed from client, but before executing them we need to interact with server.
⦁ Client and Server need not be in the same system. Most of the times server will be in the remote system.
How Client and Server works in real time?
*In real time multiple people work on the data base and in the remote machine the data base server is installed.
*When people try to connect to the data base and perform action on data in the data base,they must install the client software only and connect to server by providing the connection details.
*Data base servers need not be installed on local systems as they are expensive and contain lot of data.one server can be connected to multiple clients. Client can be of any type.
Lets now talk about MySQL :
MySQL can be downloaded from https://dev.mysql.com/downloads/mysql/
The above picture explains how a MySQL workbench looks like.
Once the MySQL is downloaded we need to enter Workbench in search bar in our pc and the MySQLWorkBench would be displayed which is the client,further there is an option called database through which we can connect to the MySQL database.
Note :
*In mysql workbench we get default databases rest of them we can create or import from external sources.
*If a user wants to view the databases in GUI user needs to click on database schema and it would display the databases.
*However for CLI client we need to enter the command “show databases” and it would display the same databases.
List of commands used to perform actions in CLI:
1)Show Tables; : We need to provide the database name first so that the database will be located and displayed, then we need to provide the show tables command to locate all the tables in the data base.
2)Show Data Base; : This displays all the databases available.
Similarly we have use table, use database etc.
Note :
In GUI we need to give the database name along with the table name in order to locate the table.
Syntax :
SELECT* FROM database name. table name;
What is a database schema?
Schema is a portion. When we have multiple types of files we create folders and copy all the similar types of files into a folder, similarly database maintains schema, inside this we can store database like objects, etc. Schema is also considered as a database in mysql.
Example: Hr is the database employee is the table name.
SQL Languages:
DDL: Data Definition Language
Commands used :CREATE,ALTER,DROP,TRUNCATE,RENAME
DML: Data Manipulation Language
Commands used : INSERT,UPDATE,DELETE
DRL/DQL: Data Retrieval Language/Data Query Language
Commands used : SELECT
TCL: Transaction Control Language
Commands used :COMMIT,ROLLBACK,SAVE POINT
DCL: Data Control Language
Commands used: GRANT,REVOKE
As there are multiple roles in Dbms multiple people work on databases.
Example:
Administrators work on TCL security stuff that is giving permission getting permission.
Data base designers design the tables and establish relationship between the tables and they use DDL and DML.
Data base developers develop the programs to send and get request and response they use rest of the commands.
Basic entities in database:
1)Database
2)Table
Lets discuss about some of the Commands:
CREATE DATABASE database name;
DROP DATABASE database name;
OR
CREATE SCHEMA schema name;
DROP SCHEMA schema name;
Note:
We cannot create multiple databases with same name that is no duplication.Hence we can also give as CREATE DATABASE IF NOT EXISTS databasename;
That’s all about part 2. In the next series we will be working on MySQL Workbench.
