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

How to Install XAMPP on Ubuntu/Linux?

Installing XAMPP on Ubuntu/Linux

Step 1: Download the installation package

Link: https://www.apachefriends.org/index.html

Step 2: Make the installation package executable

> cd /home/vishavjeet/Downloads

>sudo su

Download#>chmod 755 xampp-linux-x64-7.2.10-0-installer.run

Step 3: Confirm execute permission

Download#> ls -l xampp-linux-x64-7.2.10-0-installer.run

Step 4: Launch the Setup Wizard

Download#>  ./xampp-linux-7.2.10-0-installer.run

Step 5: Work through the graphical setup wizard

>

Step 6: Launch XAMPP through the Terminal

Download#>  /opt/lampp/lampp start

This output shows that XAMPP is started and already running. Please note that you need to manually start XAMPP each time you restart your system.

If you get the following output after starting XAMPP, it means that Net Tools are not installed on your system:

In order to install Net Tools, run the following command as root:

Download#> apt install net-tools

Step 7: Verify Installation

http://localhost

http://localhost/phpmyadmin

Uninstall XAMPP

Download#>cd /opt/lampp

Download#> ./uninstall

Download#> rm -r /opt/lampp

View More...

Why use Numpy in python?

Use of Numpy in linear algebra, fourier transform, and matrices.

Python have no array, that  lists serve the purpose of arrays.

NumPy is  provide an array  which is up to 50 times(50x) faster than traditional Python lists.

NumPy is called ndarray.

NumPy  are stored data in local memory unlike lists

So processes can access and manipulate them very efficiently

Numpy Library written in partially in python but most part is written in C or C++ because require fast computation

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

Install mysqlclient in python-django

In Ubuntu

>pip install mysqlclient

if you got installation error in window then download whl files from here

https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient


for window 32 bit

>pip install mysqlclient-1.4.6-cp38-cp38-win32.whl

for window 64 bit

>pip install mysqlclient-1.4.6-cp38-cp38-win_amd64.whl

View More...

First Previous 3 4 5 6 7 Next Last