if you want increment count in mysql than use this syntax

Syntax:-

column_name data_type(size) AUTO_INCREMENT

auto increment count for example 1 for 1st row 2 for 2nd row 5 for 5th row so on..

Example:-

CREATE TABLE homedetails

(

Homeno int AUTO_INCREMENT,

Homename varchar(255) NOT NULL,

state varchar(255),

country varchar(255),

PRIMARY KEY(homeno)

);

homeno	homename	state	country
1	chiguru	karnataka	india
2	krish	kerala	india
3	apti	NULL	USA

No need to insert homeno,it automatically increment

AUTO_INCREMENT with NOT NULL

Syntax:-

column_name data_type(size) NOT NULL AUTO_INCREMENT

Example:-

CREATE TABLE homedetails

(

Homeno int NOT NULL,

Homename varchar(255) NOT NULL AUTO_INCREMENT,

state varchar(255),

country varchar(255),

PRIMARY KEY(homeno)

);

Note:-

# AUTO_INCREMENT add 1 to last row data

# NOT NULL and AUTO_INCREMENT can be used for single column.

Previous Code:-
Creating Table with Primary Key

List Code:-
Mysql Codes

Next Code:-
Add new column to tables

if you want drop column in mysql than use this syntax

Syntax:-

ALTER TABLE table_name DROP COLUMN col_name;

Example:-

homeno	homename	state	country	address
206	chiguru	karnataka	india	8/3 cross
20	krish	kerala	india	3rd main

Here we deleting column address in homedetails table

ALTER TABLE homedetails DROP COLUMN address;

homeno	homename	state	country
206	chiguru	karnataka	india
20	krish	kerala	india

Previous Code:-
Add new column to tables

List Code:-
Mysql Codes

Next Code:-
Insert data into tables

if you want insert extra column to tables in mysql than use this syntax

Syntax:-

ALTER TABLE table_name ADD col_name datatype(size);

Example:-

homeno	homename	state	country
206	chiguru	karnataka	india
20	krish	kerala	india

We adding new column address to homedetails table

ALTER TABLE homedetails ADD address varchar(30);

homeno	homename	state	country	address
206	chiguru	karnataka	india	NULL
20	krish	kerala	india	NULL

Previous Code:-
Auto increment column

List Code:-
Mysql Codes

Next Code:-
Delete column from tables

You can change tables name in mysql

Syntax:-

RENAME TABLE old_table_name TO new_table_name;

Example:-

RENAME TABLE homedetails TO details;

Here homedetails is old table name and details is new table name.

Note:-

# Data will not effected by renaming table name.

# We can use any word as table name.

# Table name can be combination of Character and number but not number only.

# Number can be used as 1st digit in table name.

Previous Code:-
Delete row

List Code:-
Mysql Codes

Next Code:-
Delete table

if you want to delete data in mysql table than use following syntax

Syntax:-

DELETE FROM table_name WHERE position_of_row;

Note:-

# Entire row will be deleted.

# Position of row is required, because if you forget to write position_of_row than mysql will delete entire table.

Example:-

homeno	homename	state	country
206	chiguru	karnataka	india
27	sri ram	goa	india
20	krish	kerala	india

If you want to delete homeno 27 than syntax will be

DELETE FROM homedetails WHERE homeno=27;

homeno	homename	state	country
206	chiguru	karnataka	india
20	krish	kerala	india

2. same homeno

homeno	homename	state	country
206	chiguru	karnataka	india
20	sri ram	kerala	india
20	krish	punjab	pakistan

DELETE FROM homedetails WHERE state='punjab' and country='pakistan';

homeno	homename	state	country
206	chiguru	karnataka	india
20	sri ram	kerala	india

3. Delete all rows

Syntax:-

DELETE FROM table_name;

or

DELETE * FROM table_name;

Example:-

DELETE FROM homedetails;

homeno

homename

state

country

Previous Code:-
Add multiple row

List Code:-
Mysql Codes

Next Code:-
Rename table name

If you want to change or edit data/value in mysql tables than follow this steps.

homeno	homename	state	country
206	chiguru	karnataka	india
20	NULL	kerala	india
28	krish	goa	India

Consider above example, for homeno 20 we forget to add homename, if you want add homename as ram than mysql common will be

UPDATE homedetails SET homename='ram' WHERE homeno=20;

homeno	homename	state	country
206	chiguru	karnataka	india
20	ram	kerala	india
28	krish	goa	India

where homedetails is table name

Syntax for update

Syntax:-

UPDATE table_name SET col_name=data WHERE position_of_row;

Example:-

1. Editing data

Here we editing homename ram to sri ram

UPDATE homedetails SET homename='sri ram' WHERE homeno=20;

homeno	homename	state	country
206	chiguru	karnataka	india
20	sri ram	kerala	india
28	krish	goa	India

2. If homeno is same for 2 row

homeno	homename	state	country
206	chiguru	karnataka	india
20	sri ram	kerala	india
20	krish	punjab	pakistan

Here you want to change state kerala to karnataka than

UPDATE homedetails SET state='karnataka' WHERE homeno=20 and country='india';

homeno	homename	state	country
206	chiguru	karnataka	india
20	sri ram	karnataka	india
20	krish	punjab	pakistan

3. Multiple data update

homeno	homename	state	country
206	chiguru	karnataka	india
27	sri ram	karnataka	india
20	krish	punjab	pakistan

Here we changing punjab to goa and pakistan to india

UPDATE homedetails SET state='goa', country='india' WHERE homeno=20;

homeno	homename	state	country
206	chiguru	karnataka	india
27	sri ram	karnataka	india
20	krish	goa	india

4. What will happened if we don`t use WHERE

UPDATE homedetails SET state='goa';

homeno	homename	state	country
206	chiguru	goa	india
27	sri ram	goa	india
20	krish	goa	india

It changes all row to goa in country column

Previous Code:-
Insert data into tables

List Code:-
Mysql Codes

Next Code:-
Add multiple row

You want to add multiple data/value/row in single time than follow this syntax

Syntax:-

INSERT INTO table_name (col_name1, col_name2,...col_namen) VALUES (col_name1_data1,col_name2_data1,....col_namen_data1),(col_name1_data2,col_name2_data2,....col_namen_data2),....(col_name1_datan,col_name2_datan,....col_namen_datan);

Note:-

# Any number of row can be added.
# Order of data/values must match order of column name.

Example:-

Here we adding 3 row to homedetails table

INSERT INTO homedetails (homeno,homename,state,country) VALUES(206,"chiguru",”karnataka”,”india”),(20,"guru",”kerala”,”india”),(28,"krish",”goa”,”india”);

homeno	homename	state	country
206	chiguru	karnataka	india
20	guru	kerala	india
28	krish	goa	India

Previous Code:-
Update data in tables

List Code:-
Mysql Codes

Next Code:-
Delete row

To drop database in mysql use following syntax.

Syntax:-

DROP DATABASE database_name;

Example:-

To delete test database, we have to modify syntax to

DROP DATABASE test;

Note:-

# After deleting database, all data and tables inside database are deleted.

Previous Code:-
Creating Database

List Code:-
Mysql Codes

Next Code:-
Creating Table with Primary Key

To delete or drop tables in mysql use following syntax


Syntax:-

DROP TABLE table_name;

Example:-

To delete homedetails table we modify syntax to

DROP TABLE homedetails;

Note:-
# Before using this syntax, don`t forget to select database where table is present.
# After deleting table, all data inside that table is lost.

Previous Code:-
Rename table name

List Code:-
Mysql Codes

Next Code:-

After creating tables in mysql, next step is to insert values or row into tables.
Follow bellowing steps to insert value into tables.

Syntax:-

INSERT INTO table_name (col_name1, col_name2,..col_namen) VALUES(col_name1_data,col_name2_data...col_namen_data);

Note:-

# Order of column name and values/data must match each other.
# Column values must be matching to data type of that column.
# mysql command are case insensitive.
# Character type data much be inside "".
# you can skip value/data for any column in table, mysql automatically insert NULL
(charter datatype) 0 (int datatype) to skipped values.
# Column name are case insensitive.


Examples:-

1. we inserting data into homedetails table.

INSERT INTO homedetails (homeno,homename,state,country) VALUES(206,"chiguru",”karnataka”,”india”);

homeno	homename	state	city
206	chiguru	karnataka	india

2. order of column name in homedetails table are changed.

INSERT INTO homedetails (homename,state,homeno,country) VALUES("guru","kerala",20,”india”);

homeno	homename	state	country
206	chiguru	karnataka	india
20	guru	kerala	india

3. value/data are skipped.


INSERT INTO homedetails (homeno,homename,country) VALUES(895,"gtog",”USA”);

homeno	homename	state	country
206	chiguru	karnataka	india
20	guru	kerala	india
895	gtog	NULL	USA

4. Column name are case insensitive.

INSERT INTO homedetails (homEno,hoMenAme,State,cOUntrY) VALUES(58,"SRI Ram",”karnatAKA”,”India”);

homeno	homename	state	country
206	chiguru	karnataka	india
20	guru	kerala	india
895	gtog	NULL	USA
58	SRI RAM	karnatAKA	India

4. If data are in order.

INSERT INTO homedetails VALUES(28,"krish",”goa”,”india”);

Note:- Data must be same order of that of columns in homedetails table.

homeno	homename	state	country
206	chiguru	karnataka	india
20	guru	kerala	india
895	gtog	NULL	USA
58	SRI RAM	karnatAKA	India
28	krish	goa	India

Previous Code:-
Delete column from tables

List Code:-
Mysql Codes

Next Code:-
Update data in tables

To create a table in mysql use following commands.

Syntax:-

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
.
.
.
column_nameN data_type(size)
);

Example:-

CREATE TABLE homedetails
(
Homeno int,
Homename varchar(255),
state varchar(255),
country varchar(255)
);

Create table with primary key

Syntax:-

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
.
.
.
column_nameN data_type(size),
PRIMARY KEY(column name)
);

Example:-

CREATE TABLE homedetails
(
Homeno int(25),
Homename varchar(255),
state varchar(255),
country varchar(255),
PRIMARY KEY(homeno)
);

Here homeno as primary key

Make column as a not null
null means not having a value, not null means there much be a value.

Syntax:-

column_name data_type(size) NOT NULL

Example:-

CREATE TABLE homedetails
(
Homeno int NOT NULL,
Homename varchar(255) NOT NULL,
state varchar(255),
country varchar(255),
PRIMARY KEY(homeno)
);

Here Homeno and Homename are not null column. it should contain value/data

How to get list of tables name present in database in mysql

Get list of Table name

Syntax:-

SHOW TABLES;

Note:-

# Any number of column can be create.
# Any data type can be used for column.
# Data in Primary key column must be unique it can`t be repeated.
# We can use any word as table name.
# Table name can be combination of Character and number but not number only.
# Number can be used as 1st digit in table name.

Previous Code:-
Delete Database

List Code:-
Mysql Codes

Next Code:-
Auto increment column

Before creating tables in mysql, we have to create database.
command to create database

Command:-

CREATE DATABASE database_name;

Example:-

CREATE DATABASE naikgroup;

To use/choose/enter the database follow this command.

Command:-

USE database_name;

Example:-

USE naikgroup;

Here Naikgroup is database name.

Get list of database in mysql use this command

Syntax:-

SHOW DATABASES;

Note:-

# We can use any word as database name.
# Database name can be combination of Character and number but not number only.
# Number can be used as 1st digit in database name.

List Code:-
Mysql Codes

Next Code:-
Delete Database