MySql Basic Part 1

///////////////Select/Show/Use--DATABASE////////////////

#Show databases
->SHOW databases;#use database->USE database_name;#During the working if we want to know that currently in which database we are working.
->SELECT database();
#Show tables of any database
->SHOW tables;//////////We Are Giving The Example As Per The Following Table/////////////
->Here the table name is STUDENT_05 And COLLUMNS are:
   roll_no
   student_name
   course
   course_year
   marks
   percentage
   city
   Enroll_no

 

////////Select Command////////////////////
#Select any table from database
->select * from student_05;
#Select Collumns from table
->select roll_no,student_name from student_05;
#Select row from table
->select * from student_05 where roll_no='012';
#Select row and collumn from table
->select roll_no,student_name from student_05 where roll_no='012';
#collecting random record from a table->SELECT * FROM student_05 where percentage='70%' ORDER BY RAND() limit 1 

To get 10 random records from the same table. 

->SELECT * FROM student_05 where percentage='70%' ORDER BY RAND() limit 1 0
#Getting the first & Second highest number record from a table
->SELECT * FROM student_05 WHERE course='b.tech'  ORDER BY marks DESC LIMIT 0,1
->SELECT * FROM student_05 WHERE course='b.tech'  ORDER BY marks DESC LIMIT 0,2
#Displaying top 3 records only
->SELECT * FROM student_05 WHERE course='b.tech'  ORDER BY marks DESC LIMIT 0,3

/////////CREATE TABLE And Insert Data In Table/////////////
#Create a table->create table table_name(Column_1_name data_type,Column_2_name data_type,Column_3_name data_type,Column_4_name data_type);For Example:->create table student_05(
 roll_no varchar (4),
 student_name varchar (12),
 course varchar (8),
 course_year varchar (12),
 marks varchar (5),
 percentage varchar (5),
 city varchar (12),
 Enroll_no varchar (12)
);#Create Table From A Table
create table new_table_name(select * from old_table_name)
(OR)
create table new_table_name(Column_name,Column_name) AS SELECT column_name,column_name from old_tablr_name;

#Insert Data In A Tableinsert into student_05(`roll_no`, `student_name`, `course`, `course_year`, `marks`, `percentage`, `city`, `Enroll_no`) values('012','Sharjeel','B.Tech','2009','786','70%','Seohara','1234567');

#Insert Data InTo A Table From Another Table
->insert into table_name SELECT column_name,column_name FROM table_name;

#ELIMINATION OF DUPLICATES from the select statement
->select DISTINCT * from table_name
#A table could hold duplicate rows,then see only unique rows->select DISTINCT collumn_name,collumn_name FROM table_name;

#Sorting Data In A Table->select * from table_name ORDERED BY column_name desc

#DELETE TABLE
#Remove all row->DELETE FROM table_name
#Remove specific row
->DELETE FROM table_nmae WHERE search_condition;

////////////UPDATE TABLE///////////////////
#Upade all rows from a table
->UPDATE table_name SET column_name=expression,collumn_name=expression;#Update record conditionaly
->UPDATE table_name SET collumn_name=expression,collumn_name=expression WHERE collumn_name=expression;

//////ALTER TABLE////////
#Adding new collumn->ALTER TABLE table_name ADD(new_collumn_name varchar());
#Modify Existing Collumns:->ALTER TABLE table_name MODIFY clumn_name new_datatype(newsize);

#RENAME TABLE
->RENAME old_table_name TO new_table_name;

#DESTROYING TABLE
->DROP TABLE table_name;

#DESCRIBE STRUCTURE OF TABLE
->DESCRIBE table_name;