Basic SQL/MYSQL QUERY


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...

View More...

Create Product Category Table for ecommerce

CREATE TABLE `master_category` (

`created_datetime` datetime(6) NOT NULL,

`updated_datetime` datetime(6) NOT NULL,

`status` varchar(10) NOT NULL,

`category_id` int NOT NULL,

`category_title` varchar(128) NOT NULL,

`slug` varchar(150) NOT NULL,

`sort_order` int DEFAULT NULL,

`parent_id` int DEFAULT NULL

)


--

-- Dumping data for table `master_category`

--

INSERT INTO `master_category` (`created_datetime`, `updated_datetime`, `status`, `category_id`, `category_title`, `slug`, `sort_order`, `parent_id`) VALUES

('2021-01-18 06:13:46.775498', '2021-01-18 06:13:46.775559', 'Active', 2, 'Indian & Fusion Wear', 'indian-&-fusion-wear', 0, NULL),

('2021-01-18 06:15:04.800319', '2021-01-18 06:15:04.800372', 'Active', 3, 'Ethnic Sets', 'ethnic-sets', 0, 2),

('2021-01-18 06:15:36.076440', '2021-01-18 06:15:36.076519', 'Active', 4, 'Kurta Sets', 'kurta-sets', 0, 3),

('2021-01-18 06:16:04.349098', '2021-01-18 06:16:04.349149', 'Active', 5, 'Ethnic Bottom', 'ethnic-bottom', 0, 3),

('2021-01-18 06:16:26.676249', '2021-01-18 06:16:26.676306', 'Active', 6, 'Western Wear', 'western-wear', 0, NULL),

('2021-01-18 06:16:46.576680', '2021-01-18 06:16:46.576734', 'Active', 7, 'Jumpsuite and Rompers', 'jumpsuite-and-rompers', 0, 6),

('2021-01-18 06:17:05.143047', '2021-01-18 06:17:05.143101', 'Active', 8, 'Dresses', 'dresses', 0, 6),

('2021-01-18 06:17:36.219680', '2021-01-18 06:17:36.219755', 'Active', 9, 'Dress', 'dress', 0, 8),

('2021-01-18 06:17:52.319460', '2021-01-18 06:17:52.319511', 'Active', 10, 'Rompers', 'rompers', 0, 7),

('2021-01-18 06:18:03.693877', '2021-01-18 06:18:03.693929', 'Active', 11, 'Jumpsuite', 'jumpsuite', 0, 7),

('2021-01-19 07:47:34.291383', '2021-01-19 07:47:34.291486', 'Active', 12, 'asd', 'asd', 0, NULL),

('2021-02-12 06:48:53.414654', '2021-02-12 06:48:53.414710', 'Active', 13, 'Jumpsuite', 'jumpsuite-zfwk', 0, 7),

('2021-02-12 06:50:26.829550', '2021-02-12 06:50:26.829603', 'Active', 14, 'Jumpsuite', 'jumpsuite-o7q5', 0, 7),

('2021-02-12 06:52:00.015627', '2021-02-12 06:52:00.015680', 'Active', 15, 'abc', 'abc', 0, 7),

('2021-02-12 06:52:40.197679', '2021-02-12 06:52:40.197767', 'Active', 16, 'Jumpsuite', 'jumpsuite-7nub', 0, 7),

('2021-02-12 06:58:44.859319', '2021-02-12 06:58:44.859377', 'Active', 17, 'abc', 'abc-jd1d', 0, 7),

('2021-02-12 07:03:29.085166', '2021-02-12 07:03:29.085221', 'Active', 18, 'abc', 'abc-5ifn', 0, 7),

('2021-02-23 11:55:01.030882', '2021-02-23 11:55:07.323311', 'Active', 19, 'test', 'kjkjkj', 0, 2),

('2021-02-23 11:57:12.106908', '2021-02-23 11:57:12.106990', 'Active', 20, 'TEST MAP', 'MAP_LUG', 0, 2);


--

-- Indexes for dumped tables

--

--

-- Indexes for table `master_category`

--

ALTER TABLE `master_category` ADD PRIMARY KEY (`category_id`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `master_category_parent_id_84c7169e_fk_master_ca` (`parent_id`)


--

-- AUTO_INCREMENT for table `master_category`

--

ALTER TABLE `master_category` MODIFY `category_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21


--

-- Constraints for table `master_category`

--

ALTER TABLE `master_category`

ADD CONSTRAINT `master_category_parent_id_84c7169e_fk_master_ca` FOREIGN KEY (`parent_id`) REFERENCES `master_category` (`category_id`);




 

View More...