mysql queries

 CREATE DATABASE database name  ; //  this is use to create the datbase 

SHOW DATBASES // THIS is use to show all the datbase 

use database datbase name  /// is use to use the particular datbase 

CREATE TABLE persons(PersonsID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));// use to create the datbase 

SHOW TABLES // use to show all the tabels present in the database 

DROP TABLE table name /// is use to drop the tabel from the datbase 

TRUNCATE TABLE table_name; // THIS IS use to dletet the data inside the table 

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'); /// use to insert the data into the colum of the table 

SHOW FULL TABLES;  // is use to show the content inside the table 

desc identity; // IS USE to show the structure of the table 

 select * from identity; // is use to show all the data iside the table 

C:\Program Files\MySQL\MySQL Server 8.0\bin // go to the path in the cmd to use mysql in cmd 

mysql -u root -p // to login the mysql server then after that enter the password

select <colum name > from <table name> /// thi is used to retrive the particular column data from the table 

select <column 1 name> <column 2 name> from <table name> //  this is used to retrive the multiple column data from the table 

 create table identity(

    -> id int not null,   /// this is used to make the field neccessary without inserting it it will give error

    -> name varchar(255),

    -> age varchar(255),

    -> address varchar(255)

    -> );

     create table identity(

    -> id int not null,   

    -> name varchar(255),

    -> age varchar(255),

    -> primary key (id)   // set the primary key which cannot be a null or and contains uniqe value

    -> );

    create table identity(

    -> id int not null,   

    -> name varchar(255),

    -> age int,

    -> check(age>30) // this is use to apply the condition (like here the condition is that age should be greater than the 30)

    -> );

    create table int(

    -> id int not null,   

    -> name varchar(255),

    -> age int,

    -> diseases varchar(255) default 'perfect' // is use to fill the default value in the table 

    -> );

// how to add the index to the table columns 

create index <index name>

->on <table name>(<columns name>);

Drop index from the table 

drop index <index name> on <table name>;

To display all the indexes 

show indexes from <table name>;

how to add the column into the existing table 

alter table <table name>

->add <table name> datatype;

how to delete the particular column from the table 

alter table <table name>

->drop column <column name>;

how to modified the data type of the table colum

aleter table <table name>

->modify column <column name> new data type;

how to retrive  the column value without the duplicate value present in the column

select distinct <column name> from <table name>;

how to retrive the data from the table acording to the condition applied on the column data 

select * from <table name>

->where <column name>=comparison value;

how to retrive the data of table with the two satisfy condition 

slect * from <table name>

->where <column name> = comparison value and <second column name> = comparison value;

retrive the data without any particular data 

select * From <table name>

->where not <column name> = <value>;

we can add the comment by using 

# this is the comment 

-- this is the comment 

multiline comment 

/*   */

count command 

select count(<column name>)

-> from <table name>

-> where <column name > = value;

count all the rows from table 

select count(*)

-> from <table name>;

change the columns name on the exicuting 

select <column name> as <new name>

->from <table name>;

finding condition in  the databse column

select from * <table name>

->where <column name> in( value1,value2);

appling the not condintion on the column;

select from * <table name>

->where <column name> not in (value1,value2);

how to search the data  related to any word 

select from * <table name>

->where <column name> like '<word or aplhabet>%';

or 

->where <column name> like '%<word or aplhabet>';

or 

->where <column name> like '__<word or aplhabet>';

between condintion on the integer value 

select from * <table name>

->where <column name> between <value1> and <value2>;

deleting the data from the table acording to the condition 

delete from <table name>

-> where <column name>= <condition value>;

to delete all the rows and columns from the table 

delete from <table name>;

update value in a table in any column 

update  <table name>

-> set <column name> = <value to be set>

-> where <column name> = <old value to be update>;

to dlete the full table 

drop table <table name>;

to delete the databases;

drop database <databases name>;

get all the average value of the numeric column

select avg(column name)

->from <table name>;

sum of all the numeric values from the numeric column names

select sum(coumn name)  // min(),max()

->from <table name>;

sorting the data acording the numric columns values

select from * <table name>

->order by <column name>; // by default in assending order

or 

-> order by <column name> desc; // ordered in descending order

index values to increment automaticaly

->create table <table name>(

-> <column name> int not null auto_increment,

-> <column name> varchar

->);

unique id in a table  // primary key

create table <table name>(

->id int not null,

->name varchar(255),

->unique(id)

->);

check the data of the numrical column while inserting it into a database

create table <table name>(

->id int not null,

->name varchar(255),

->lastname varchar(255),

->unique(id),

->check(<column name> <condition operatior> <condition value>)

->);

how to set the primary key 

->id int not null,

->name varchar(255),

->lastname varchar(255),

->unique(id),

->primary key (id)

->);

apply limit to fatch the data from the table 

select from * <table name>

->limit 3;