vishavjeet

singh.vishavjeet11@gmail.com

Basic SQL/MYSQL QUERY

Posted on March 11, 2021




SHOW DATABASES;

#Delete table

DROP TABLE IF EXISTS test_db;

#DROP TABLE based on a pattern

DROP TABLE like '%test_%'

#Delete database

DROP DATABASE test_db;

#Table structure for table `old_test_table`

#Create Table;

CREATE TABLE `old_test_table` (

`id` int(11) NOT NULL,

`user` varchar(64) NOT NULL,

`mobile` varchar(10) NOT NULL,

`email` varchar(120) NOT NULL,

`address` varchar(128) DEFAULT NULL,

`gender` enum('male','female') NOT NULL DEFAULT 'male',

`created_on` timestamp NOT NULL DEFAULT current_timestamp(),

`updated_at` datetime NOT NULL

)

#Rename Table old_test_table to test_table

RENAME TABLE old_test_table to test_table;

#Rename Colunm

ALTER TABLE test_table CHANGE created_at created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `test_table` CHANGE `address1` `address` VARCHAR(128) DEFAULT NULL;

ALTER TABLE test_table ADD PRIMARY KEY (`id`);

#Indexes  AUTO_INCREMENT for table test_table

ALTER TABLE test_table MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

#Add Colunm salary

ALTER TABLE test_table ADD salary INT(1) NOT NULL AFTER updated_at;


#INSERT INTO Statement

INSERT INTO test_table (user, mobile, email, address, gender, created_on, updated_at, salary) VALUES (NULL, 'Anupam', '8368689068', 'singh.anupam@gmail.com', 'Lucknow', 'male', '2021-03-24 10:10:42', '2021-03-26 10:10:42', 140000);

# Insert multiple row data in single query

INSERT INTO test_table (user, mobile, email, address, gender, created_on, updated_at, salary) VALUES

('vishavjeet', '8448813565', 'singh.vishavjeet11@gmail.com', 'test', 'male', '2021-03-11 04:42:22', '2021-03-26 10:10:42', 40000),

('Anupam', '8368689068', 'singh.anupam@gmail.com', 'Lucknow', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 140000),

('Nimesh', '9335208575', 'singh.nimesh@gmail.com', 'Ray Bareilly', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 120000),

('Syad Gulfam Abbas Naqvi', '8368688443', 'syed.gulfamabbas786@gmail.com', 'Azamgarh', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 95000),

('Sara', '8368688443', 'sara.khan@gmail.com', 'Varansi', 'female', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 91000),

('Nishu', '8368688443', 'nishu.anupam@gmail.com', 'Rampur', 'female', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 110000);

#UPDATE Statement

UPDATE `test_table` SET `mobile` = '8448813565' WHERE `test_table`.`id` = 1;

UPDATE `test_table` SET `salary` = '30000' WHERE `test_table`.`id` = 1;

UPDATE `test_table` SET `salary` = '140000' WHERE `test_table`.`id` = 2;

# DELETE Statement

DELETE FROM `test_table` WHERE `test_table`.`id` = 3"

#For Fetching/Filter data from table

#SELECT Statement

SELECT user, email FROM test_table;

SELECT * FROM `test_table` WHERE user like '%visha%'

select * from test_table where user like 'sara'

select * from test_table where salary>95000

#DISTINCT Statement

SELECT DISTINCT user, email FROM test_table;

#WHERE Clause

select * from test_table where user='sara'

select * from test_table where user like 'anupam'

select * from test_table where salary>95000

# ORDER BY Keyword

select * from test_table order by created_on DESC

select * from test_table where id >1 order by created_on DESC

#LIKE Operator

select * from test_table where email like '%singh%'

select * from test_table where email like '%singh%' order by created_on DESC

#NULL Values

select * from test_table where address IS NULL;

select * from test_table where address IS NOT NULL;

#AND, OR and NOT Operators

select * from test_table where address='lucknow' AND salary>40000

select * from test_table where address='lucknow' OR salary>40000

select * from test_table where (address='lucknow' AND salary>40000) OR (address='rampur' AND salary<200000)

#Aggregate Functions: MIN(), Max(), COUNT(), AVG() and SUM()

select min(salary) from test_table

select max(salary) from test_table

select sum(salary) from test_table

select avg(salary) from test_table

select count(id) from test_table

select count(id) as total from test_table GROUP BY address

#IN Operator

select * from test_table where id IN (1,3,6)

#Alternative Second way

select * from test_table where id=1 OR id=3 OR id=6

#BETWEEN Operator

select * from test_table where created_on BETWEEN "2021-03-24" AND "2021-03-27"

#Alternative Second way

select * from test_table where created_on >= "2021-03-24" AND <="2021-03-27"

#JOIN comming Soon...


Sign In for comment and like the post.