Insert into

The INSERT INTO statement is used to insert new records/row/tuple in a table.

Syntax :-
INSERT INTO table_name (column1, column2, column3, column4,.....)
VALUES (value1, 'value2', 'value3', value4.....);

Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (05, 'Anu', 'Delhi', 982112);

Rules :-
Column and Value order should be same.
Any value that goes into a VARCHAR, CHAR, DATE, or TEXT column has single quotes around it. There are no need of quotes for numeric values (INT, DEC).

Example :-

Without specifying column name

Syntax :-
INSERT INTO table_name
VALUES(value1, 'value2', 'value3', value4);

Ex :-
INSERT INTO my_tab
VALUES (05, 'Anu', 'Delhi', 982112);

Rules :-
The values order should be same as column
We need to insert record for each column we can not leave any column.

Changing the order of column

Syntax :-
INSERT INTO table_name (column2, column1, column4, column3)
VALUES ('value2', value1, value4, 'value3');

Ex :-
INSERT INTO my_tab(name, stu_id, mobile_no, address)
VALUES ('Anu', 05, 982112, 'Delhi');

Insert Data only in Specified Columns

Syntax :-
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, 'value2', 'value3')

Ex :-
INSERT INTO my_tab(stu_id, name, address)
VALUES (05, 'Anu', 'Delhi');

Insert multiple records at one time

Syntax :-
INSERT INTO table_name (column1, column2, column3, column4)
VALUES (value1, 'value2', 'value3', value4);
(value1, 'value2', 'value3', value4);

Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (01, 'Anu', 'Delhi', 982112),
(02, 'Rohan', 'Mumbai', 561425);

 

Read More

Data Types

INT or INTEGER - It holds whole number between -32,768 and 32,767 either it is negative or positive. It can not hold a decimal numbers. The maximum number of digits may be specified in parenthesis.

Syntax :-
 column_name INT (size);

Ex :-
 roll INT (5);


DEC or DECIMAL (size, p) - It holds fixed point numbers, size is the total number of digits and p is the number of digits after the decimal point. The decimal point and the negative sign '-' are not counted in size. If p is 0, values have no decimal point. The maximum number of size for decimal is 65 and for p 30. if p omitted the default is 0. if size is omitted, the default is 10.

Syntax :-
 column_name DECIMAL(size, p);

Ex :- 
 price DECIMAL (4,2);

CHAR or CHARACTER - It holds a fixed length string  (can contain letter, numbers, and special characters). The fixed size is specified in parenthesis. It can store up to 255haracters.

Syntax :- 
 column_name CHAR (20);

Ex :-
  name CHAR (20);

VARCHAR - It holds a variable length string (can contains letters, numbers, and special character). The maximum size is specified parenthesis. It can store up to 255 characters.
Note : If we put a greater value than 255 it will be converted to a TEXT type.

Syntax :- 
 column_name VARCHAR (size);

Ex :- 
 name VARCHAR (50);

TEXT - It holds a string with a maximum length of 65,535 characters.

Syntax :-
 column_name TEXT;

Ex :- 
 address TEXT;

DATE - It display Date values in yyyy-mm-dd format.

Syntax :-
 column_name DATE;

Ex :-
age DATE;

DATATIME - It display DATETIME values in yyyy-mm-dd  hh:mm:ss format.

Syntax :- 
 column_name DATETIME;

Ex :- 
 Date_of_join DATETIME;

TIMESTAMP - It also displayes date and time.

Syntax :-
 column_name TIMESTAMP;

Ex :- 
 login_dt TIMESTAMP;

Read More

Show Databases/ Tables

SHOW DATABASES - This command is used to view all the databases name.

Syntax :- 
 SHOW DATABASES;

SHOW TABLES - This command is used to view all tables of current database.

Syntax :- 
 SHOW TABLES;

Read More

DESC Command

This is used to describe your table. DESC only describes structure of table not the information (rows) inside table. DESC is short form of describe.

Syntax :-
 DESC table_name;

Ex :-
  DESC my_tab;

Read More

Create Table

Syntax :-

CREATE TABLE table name
(
Column_name1 data-type (size),
Column_name2 data-type (size),
Column_name3 data-type (size)
);

Ex :-
CREATE TABLE my_tab
(
name varchar (30),
roll int (4),
address varchar (100)
);

Another way to create a table

Syntax :-
CREATE TABLE table name
(
Column_name1 data-type (size) [constraints],
Column_name2 data-type (size) [constraints],
Column_name3 data-type (size) [constraints]
);

Ex :-
CREATE TABLE my_tab
(
name varchar (30) NOT NULL,
roll int (4) PRIMARY KEY,
address varchar (100)
);

Guidelines for creation of Table

 1. Table name should start with an alphabet.
 2. In table name, blank spaces and single quotes are not allowed.
 3. Reserve words of that RDBMS/DBMS cannot be used as table name.
 4. Proper data type and size should be specified.
 5. Unique column name should be specified.

 

Read More

Create Database

It is used to create database.

Syntax:- CREATE DATABASE database_name;
Ex:- CREATE DATABASE my_db;

Guide for creation of database:-
 Name should start with an alphabet
Blank space and single quotes are not allowed
Reserve words of that RDBMS/DBMS can not be used as database name

Use a database

 This is used to tell your RDBMS/DBMS that you want to use this database.

Syntax:- USE database_name;
Ex:- USE my_db;

Read More

What is database?

A database is integrated collection of related information along with the details so that it is available to the several user for the different application.

                                        This is a database

Table/Entity
  Table is the structure inside database that contains data organized in columns and rows.

Column/Field/Feature/Attributes
  The name of each column in a table is used to interpret its meaning and is called an attributes.

 

Row/Records/Tuple
  Each row in a table represents a record and is called tuple.

Example:-
     Rahul , 101 , Delhi , rahul@gmail.com
     Sona , 102 , Kolkata , sona@gmail.com
     Sumit , 103 , sumit@gmail.com , Ranchi
     Sam , sam@gmail.com , Noida , 104
                This is a database list of four person, now its create in table form

Database Management System (DBMS)
  DBMS is the software system that allows the access to the data in the database.

Relational Database Management System (RDBMS)
 RDBMS avoided the navigation model as in old DBMS and introduced Relational model. The relational model has relationship between tables using primary keys, foreign keys and indexes. thus the fetching and storing  of data become faster than the old Navigation model.RDBMS is useful to efficiently manage  vast amount of data and is used in large business application.
  Ex:- SQL Server , Oracle , MySQL , MariaDB , SQLite.

Structural Query Language (SQL)
  It is commonly used with all relational database for data definition and manipulation.

Feature of SQL :-
  It is a non procedural language.
  It is an English-like language.
  It can process a single record as well as set of records at a time.
  All SQL statement define what is to be done rather than how it is to be done.
  SQL has facilities for defining database views , security , transaction etc. 

Read More