MySQL Handbook

Create a Database

create database abcdef;

Select a database

use command is used to select a database

use abcdef; will make abcdef as current database

create command is used to create a table

create table students(rollno int,name varchar(20),marks int);

the above command will create a table with columns rollno as int, name as varchar(20) and marks as int

insert command is used to insert a row in mysql table

the above command will store rollno as 1 , name as raman and marks as int

insert into students(1,’raman’,90);

select * from students;

the above command will retrieve whole data from mysql table students

Output

select rollno,name from students;

the above command will display columns rollno and name from table students

Output

select rollno,marks from students;

above query will display data for columns rollno and marks from stable students

Output

to display records of all columns in table students where rollno is equal to 1

select * from students where rollno=1;

Output

sql command to display records where rollno is greater than 1

select * from students where rollno>1;

command to insert three records in table students

select * from students;

the above sql query will display all the records of table students

Output

select * from students where rollno>1;

the above query will display records from table students where rollno is greater than 1

Output

select * from students where rollno<3;

the above query will display records where rollno is less than 3

Output

select * from students where rollno>3;

the above query will display records where rollno is greater than 3

the above query will display all the columns

select * from students where marks is not null;

the above query will display records where marks are not equal to null

Output

select * from students where marks is null;

the above query will display records where marks is null

Output

select * from students where marks>=80 and marks<=90;

the above sql query will display records where marks are between 80 and 90

Output

select * from students where marks=80 or marks=90;

Output

sql query to display records where marks are between 80 and 90

Output

sql query to display records where marks are in the list (20,34,89,90)

select * from students where marks in (20,74,78,90);

Output

sql query to sort records in ascending order of marks

select * from students order by name asc;

Output

select * from students order by marks desc;

sql query to sort records in descending order of marks

select * from students order by marks desc;

Output

sql query to sort records in order of marks in descending order and in descending order of rollno

Output

select * from students where name like ‘a%’;

above query will display records where name starts with a

% is wildcard character that specifies any number of alphabets after a

select * from students where name like ‘%n’;

above query will display records where name ends with n

Output

update command to update a single column marks of table students as 88 where rollno is equal to 3

update students set marks=88 where rollno=3;

delete command to delete all rows from the table

delete from students;

above command will delete all rows from table students;

delete command to delete records from table students

sql query to delete a table and table data

drop table students;

sql query to alter a table, to add a column to table

alter table students add column admnno int;