MySQL Commands
1) To create new table
Sy : create table student(roll integer, name varchar(10), city
varchar(10), marks integer);
2) To check all existing tables
Sy : show tables;
Output :
student
3) To see the structure of the table
Sy : describe student; or desc student;
Field Type Null Key Default Extra
admn int YES NULL
name varchar(10) YES NULL
city varchar(10) YES NULL
marks int YES NULL
4) There are two methods to insert values in a student table
a) insert into student values(123, "Aditya", "Delhi", 78);
b) insert into student(marks, roll, city, name) values(88, 789,
'Mumbai', 'Manas');
5) To display all records from student tables
Sy : select * from student;
Output
admn name city marks
123 Aditya Delhi 78
789 Manas Mumbai 88
569 Ajay Goa 77
456 Sonam Delhi 65
745 Rekha Mumbai 84
985 Poonam Goa 45
6) To display some particular column from student table i.e. name,
marks.
Sy. : select name, marks from student;
Output :
name marks
Aditya 78
Manas 88
Ajay 77
Sonam 65
Rekha 84
Poonam 45
7) To display a record using “where” condition
Sy : Select * from student where name =”Manas”;
Output :
admn name city marks
789 Manas Mumbai 88
8) To display record using multiple condition with the help of where
clause along with AND logical operator
Sy :
select * from student where marks=88 and city="Mumbai" ;
roll name city marks
789 Manas Mumbai 88
9) To display records after removing duplicate records from column city
Sy : select distinct(city) from student;
city
Delhi
Mumbai
Goa
10) To display all marks from students
Sy : select ALL marks from student;
marks
78
88
77
65
84
45
11. To display multiple record from student table using OR logical
operator
Sy.: select * from student where city="Goa" or city="Delhi";
roll name city marks
123 Aditya Delhi 78
569 Ajay Goa 77
456 Sonam Delhi 65
985 Poonam Goa 45
12. Use IN to display students who are staying in Goa and Delhi
Sy.: select * from student where city in("Goa", "Delhi");
roll name city marks
123 Aditya Delhi 78
569 Ajay Goa 77
456 Sonam Delhi 65
985 Poonam Goa 45
13. To display student records who have scored 78 and more than
78 marks or 88 and less than 88.
Sy.: select * from student where marks>=78 or marks<=88;
roll name city marks
123 Aditya Delhi 78
789 Manas Mumbai 88
569 Ajay Goa 77
456 Sonam Delhi 65
745 Rekha Mumbai 84
985 Poonam Goa 45
14. select * from student where marks between 78 and 88;
roll name city marks
123 Aditya Delhi 78
789 Manas Mumbai 88
745 Rekha Mumbai 84
15. To display current date
Sy.: select curdate();
curdate()
2022-02-17
16. To update records temporary
Sy.: select marks+10 as "Updated Marks" from student;
Updated Marks
88
98
87
75
94
55
17. Extract information using like operator
Sy.: select * from student where name like "R%a";
roll name city marks
745 Rekha Mumbai 84
18. To add new column “pincode” in student table and display
also.
Sy.: alter table student add pincode integer;
Sy.: Select * from student;
roll name city marks pincode
123 Aditya Delhi 78 NULL
789 Manas Mumbai 88 NULL
569 Ajay Goa 77 NULL
456 Sonam Delhi 65 NULL
745 Rekha Mumbai 84 NULL
985 Poonam Goa 45 NULL
19. To make permanent change in student table using AND operator
and display output also.
Sy. : update student set name="Naman" where marks = 78 and
city="Delhi";
Sy.: Select * from student;
roll name city marks pincode
123 Naman Delhi 78 NULL
789 Manas Mumbai 88 NULL
569 Ajay Goa 77 NULL
456 Sonam Delhi 65 NULL
745 Rekha Mumbai 84 NULL
985 Poonam Goa 45 NULL
20. To make permanent change in student table using AND
operator and display output also.
Sy.: update student set pincode=110029 where city="Delhi" or
city="Mumbai";
Sy.: select * from student;
roll name city marks pincode
123 Naman Delhi 78 110029
789 Manas Mumbai 88 110029
569 Ajay Goa 77 NULL
456 Sonam Delhi 65 110029
745 Rekha Mumbai 84 110029
985 Poonam Goa 45 NULL
22. To display all null values
Sy.: select * from student where pincode is null;
roll name city marks pincode
569 Ajay Goa 77 NULL
985 Poonam Goa 45 NULL
23. Display student table after deleting records.
Sy.: delete from student where name="Sonam";
Sy.: Select * from student;
roll name city marks pincode
123 Naman Delhi 78 110029
789 Manas Mumbai 88 110029
569 Ajay Goa 77 NULL
745 Rekha Mumbai 84 110029
985 Poonam Goa 45 NULL
24. To delete all records from student table
Sy. : delete from student;
No output because removed all records from student tables.
25. To remove student table
Sy. : drop table student;
25. To arrange data in ascending order according to name.
Sy : select * from student order by name;
roll name city marks pincode
569 Ajay Goa 77 NULL
789 Manas Mumbai 88 110029
123 Naman Delhi 78 110029
985 Poonam Goa 45 NULL
745 Rekha Mumbai 84 110029
26. To arrange data in descending order according to name.
Sy : select * from student order by name desc;
roll name city marks pincode
745 Rekha Mumbai 84 110029
985 Poonam Goa 45 NULL
123 Naman Delhi 78 110029
789 Manas Mumbai 88 110029
569 Ajay Goa 77 NULL