Basics of MySql
What is Database?
Database is an organized collection of structure data or information which is stored in computer system. It can be accessed and managed by user. It allows us to organize data into tables, rows, columns and indexes to find the relevant information or data.
Each database has one or more distinct API's for creating, managing, accessing and searching the data from the database.
What is MySQL?
MySQL is currently the most popular Open Source database management system and it is developed, distributed and supported by Oracle Corporation. It is fast, scalable and easy to use.
MySQL is an RDBMS(Relational Database Management System) that provide many things, which are as follows:
- It allows us to implement database operations on tables, rows, columns and indexes.
- It allows us to update the table index automatically.
- It uses "SQL" language to query the database.
Create Database:
The CREATE database statement is used to create new database.
Syntax:
CREATE DATABASE databasename;
Example:
CREATE DATABASE user;
Show Database:
The SHOW databases statement is used to show the all available databases.
Example:
SHOW DATABASES;
Drop Database:
The DROP database statement is used to drop an existing database.
Syntax:
DROP DATABASE databasename;
Example:
DROP DATABASE user;
Create Table:
The CREATE table statement is used to create a new table in database.
Syntax:
CREATE TABLE table_name(column1 datatype,
column2 datatype,
......,
columnN datatype);
Example:
CREATE TABLE user_info(Id int,
FirstName varchar(255),
LastName varchar(255),
EmailId varchar(255),
Address varchar(255));
Select Statement:
The SELECT statement is used to select data from the table.
Syntax:
SELECT * FROM table_name;
Example:
SELECT * FROM user_info;
Insert Statement:
The INSERT statement is used to insert new records into the table.
Syntax:
INSERT INTO table_name(column1, column2,..., columnN)
VALUES(value1, value2, ...., valueN);
Example:
INSERT INTO user_info(FirstName, LastName, EmailId, Address)
VALUES("Henry","doe", "henrydoe02@gmail.com","something..");
Update Statement:
The UPDATE statement is used to update/Modify the existing records from the table.
Syntax:
UPDATE table_name SET column1=value1, column2=value2 Where condition;
Example: UPDATE user_info SET FirstName="Rock", EmailId="rock@gmail.com" Where id=1;
Alter Statement:
The Alter statement is used to Add a column into the table.
Syntax:
ALTER TABLE table_name ADD column_name datatype;
Example: ALTER TABLE user_info ADD ContactNo varchar(255);
Delete Statement:
The DELETE statement is used to delete existing records from the table.
Syntax:
DELETE FROM table_name Where condition;
Example: DELETE FROM user_info Where id=1;
Drop Table:
The DROP table command is used to delete the table from the database.
Syntax:
DROP TABLE table_name;
Example: DROP TABLE user_info;
Truncate Table:
The TRUNCATE table command is used to delete the data from the table, but not the table itself.
Syntax:
TRUNCATE TABLE table_name;
Example: TRUNCATE TABLE user_info;
Filtering Data
- WHERE - Used to filter the data based on specified condition.
- AND - This is Logical operator that combines two or more boolean expression and returns true if both conditions are true.
- OR - The OR Operator Combines two boolean expression and returns true when either condition is true.
- IN - The IN Operator allows you to specify multiple values in a WHERE clause.
- BETWEEN - The BETWEEN operator allows you to specify whether a value in a range or not.
- LIKE - It allows a query data based on a specific pattern.
- LIMIT - Return the record based on a limit value.
- IS NULL - Check the value is null or not using IS NULL operator.
- SELECT DISTINCT - return the distinct[different] records.
Sorting Data:
- ORDER BY - Returns the sorted result set[ASC/DESC] using order by clause.
Comments