MySQL Queries for Beginner – Quick Start
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)
|
1 |
mysql -h localhost -u root -p |
List all the databases running in server
|
1 |
show databases; |
Creating new database
|
1 |
create database mydb; |
Select database to work on
|
1 |
use mydb; |
List all the tables from selected database
|
1 |
show tables; |
Create new table
|
1 2 3 |
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)); |
Describe table
|
1 2 3 |
desc emp; OR describe emp; |
Insert record into table
|
1 |
insert into emp(id,name,salary) values(5,'Vikram Thakur',5000.50); |
Select all columns and all rows
|
1 |
select * from emp; |
Find selected columns and all rows
|
1 |
select id, name from emp; |
Find selected column and selected rows
|
1 |
select id, name, salary from emp where id>3; |
Find data in range (between)
|
1 |
select * from emp where id between 1 and 3; |
Find selected columns and all rows
|
1 |
select id, name from emp; |
Find data in from given option (in)
|
1 |
select * from emp where id in(1,5); |
Find values other then given values (not in)
|
1 |
select * from emp where id not in(1,3); |
Equal condition
|
1 |
select * from emp where name='Raj'; |
SQL Function Max
|
1 |
select max(salary) from emp; |
SQL Function MIN
|
1 |
select min(salary) from emp; |
SQL Function AVG
|
1 |
select avg(salary) from emp; |
Pattern query(like) : % and _ : wild card char
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
|
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
|
1 |
DELETE FROM emp; |
Delete records for match condition
|
1 |
DELETE FROM emp WHERE empId=101; |
Drop Table
|
1 |
DROP TABLE emp; |
Drop Database
|
1 |
DROP DATABASE mydb; |
Alter Table by modifying a column(resize name column)
|
1 |
ALTER TABLE emp MODIFY name VARCHAR(80); |
Alter Table by adding a column
|
1 |
ALTER TABLE emp ADD address VARCHAR(150); |
Alter Table by dropping a column
|
1 |
ALTER TABLE emp DROP address; |
Truncate table(drop and recreate structure)
|
1 |
TRUNCATE TABLE emp; |
Take MySql database backup for all databases
|
1 |
D:\>mysqldump -u root -p --all-databases > backupall.sql |
Thank you for reading.
