MySQL Queries for Beginner – Quick Start | EZEON – Training Division | Best For Java/J2EE | Spring Framework | Struts MVC | PHP and Drupal | Android | Corporate Training | Major/Minor Projects | Live Projects | Internship

Please read below MySQL specific queries for reference. These are just quick start queries.

Connect to server (another way to connect to server from command prompt)

mysql -h localhost -u root -p

1	mysql -h localhost -u root -p

List all the databases running in server

show databases;

1	show databases;

Creating new database

create database mydb;

1	create database mydb;

Select database to work on

use mydb;

use mydb;

List all the tables from selected database

show tables;

1	show tables;

Create new table

create table emp(id int, name varchar(50),salary float(10,2)); 
OR
create table emp(empId int(10) primary key auto_increment, name varchar(30), salary float(10,2));

create table emp(id int, name varchar(50),salary float(10,2));

create table emp(empId int(10) primary key auto_increment, name varchar(30), salary float(10,2));

Describe table

desc emp;
OR
describe emp;

desc emp;

describe emp;

Insert record into table

insert into emp(id,name,salary) values(5,'Vikram Thakur',5000.50);

1	insert into emp(id,name,salary) values(5,'Vikram Thakur',5000.50);

Select all columns and all rows

select * from emp;

1	select * from emp;

Find selected columns and all rows

select id, name from emp;

1	select id, name from emp;

Find selected column and selected rows

select id, name, salary from emp  where id>3;

1	select id, name, salary from emp where id>3;

Find data in range (between)

select * from emp where id between 1 and 3;

1	select * from emp where id between 1 and 3;

Find selected columns and all rows

select id, name from emp;

1	select id, name from emp;

Find data in from given option (in)

select * from emp where id in(1,5);

1	select * from emp where id in(1,5);

Find values other then given values (not in)

select * from emp where id not in(1,3);

1	select * from emp where id not in(1,3);

Equal condition

select * from emp where name='Raj';

1	select * from emp where name='Raj';

SQL Function Max

select max(salary) from emp;

1	select max(salary) from emp;

SQL Function MIN

select min(salary) from emp;

1	select min(salary) from emp;

SQL Function AVG

select avg(salary) from emp;

1	select avg(salary) from emp;

Pattern query(like) : % and _ : wild card char

select * from emp where name like('R%');
% : any char, n-number of chars, no char
_ (underscore) : any single char
Patterns for like :
name like('A%');
name like('%t');
name like('_a%')
like('_ _ j%r')
like('_ _j%a');
select * from emp where name like('%r');
select * from emp where name like('_a%r');

select * from emp where name like('R%');

% : any char, n-number of chars, no char

_ (underscore) : any single char

Patterns for like :

name like('A%');

name like('%t');

name like('_a%')

like('_ _ j%r')

like('_ _j%a');

select * from emp where name like('%r');

select * from emp where name like('_a%r');

Update Record Query

update emp set name='Raj Sinha' where id=1;
update emp set name='Raj Sinha', salary=50000 where id=1;

1 2	update emp set name='Raj Sinha' where id=1; update emp set name='Raj Sinha', salary=50000 where id=1;

Delete all records

DELETE FROM emp;

1	DELETE FROM emp;

Delete records for match condition

DELETE FROM emp WHERE empId=101;

1	DELETE FROM emp WHERE empId=101;

Drop Table

DROP TABLE emp;

1	DROP TABLE emp;

Drop Database

DROP DATABASE mydb;

1	DROP DATABASE mydb;

Alter Table by modifying a column(resize name column)

ALTER TABLE emp MODIFY name VARCHAR(80);

1	ALTER TABLE emp MODIFY name VARCHAR(80);

Alter Table by adding a column

ALTER TABLE emp ADD address VARCHAR(150);

1	ALTER TABLE emp ADD address VARCHAR(150);

Alter Table by dropping a column

ALTER TABLE emp DROP address;

1	ALTER TABLE emp DROP address;

Truncate table(drop and recreate structure)

TRUNCATE TABLE emp;

1	TRUNCATE TABLE emp;

Take MySql database backup for all databases

D:\>mysqldump -u root -p --all-databases > backupall.sql

1	D:\>mysqldump -u root -p --all-databases > backupall.sql

Thank you for reading.

Blog

MySQL Queries for Beginner – Quick Start

About Vikram Thakur

Ads

Recent Posts

Categories

Please write us today

EZEON is a Software Development Company wish to share our Experience with Students, Developers and Companies to help them in adopting best practices. Its our small contribution towards skill development initiative