Showing posts with label MySQL Database and Table Query For Getting Desired Result.. Show all posts
Showing posts with label MySQL Database and Table Query For Getting Desired Result.. Show all posts

Monday, August 14, 2023

MySQL Database and Table Query

MySQL Database and Table Query For Getting Desired Result.


Rename A Table MySQL :-
rename TABLE super_category to category

Rename A Table Column Name MySQL :-
ALTER TABLE category change name category_name varchar(50)

Create Table Copy with Data without inherit indexes and auto_increment:-
CREATE TABLE master_category SELECT * FROM category

Create Table Copy only Structure without Data inherit indexes and auto_increment :-
CREATE TABLE master_category like category

One Table to Another Table Copy Selected Column Data :-
INSERT INTO master_category(description, create_date)  
SELECT description,create_date FROM category
One Table to Another Table Copy All Data :-
INSERT master_category SELECT * FROM category
Create Table with Selected Column Copy from Another Table with Data :-
CREATE TABLE category SELECT id, category_name FROM super_category
Create Table with Selected Column Copy from Another Table only Structure :
CREATE TABLE super_category SELECT id, category_name FROM category limit 0
One Database to Another Database Create Table Copy with Data  :-
CREATE TABLE profile.master_category SELECT * FROM king.category
One Database to Another Database Create Table Copy Structure  :-
CREATE TABLE profile.super_category like king.category

One Database Table Selected Column Data Copy to Another Databse Table :-
INSERT INTO profile.super_category(description, create_date)  
SELECT description,create_date FROM king.category
Delete Table All Records :-
TRUNCATE TABLE master_category
Delete Complete Table :-
DROP TABLE master_category
Add New Column in Existing Table :-
ALTER TABLE super_category ADD created_at DATETIME
ALTER TABLE super_category ADD sub_category VARCHAR(100) NOT NULL
ALTER TABLE super_category ADD active BOOLEAN DEFAULT TRUE
ALTER TABLE super_category ADD stock int(11) DEFAULT 0

Delete Selected Column from Existing Table :-
ALTER TABLE super_category DROP created_at
ALTER TABLE super_category DROP sub_category
ALTER TABLE super_category DROP active
Add a Value to a Selected Column for all records :-
update super_category set stock=10
Add DEFAULT value for Selected Column :-
ALTER TABLE super_category ALTER stock SET DEFAULT 15
ALTER TABLE super_category MODIFY stock INT NOT NULL
ALTER TABLE super_category MODIFY stock INT DEFAULT 0
Delete a Default Value From a Column :-
ALTER TABLE super_category ALTER stock DROP DEFAULT
Add Auto Increment to Selected Column :-
ALTER TABLE category AUTO_INCREMENT=1000
Create a View :-
Create view tbl_category as select * from category

Show a View :-
select * from tbl_category

Conditional Sum for Employee PaySlip Record How many Times :- 
SELECT sum(if(emp_system_code='EMP-008',1,0)) as "SANAT DE",
sum(if(emp_system_code='EMP-002',1,0)) as AVALEONG
FROM paysilp_employee_details

Find duplicate values in one column :-
SELECT * FROM contacts ORDER BY email
SELECT email,COUNT(email) FROM contacts GROUP BY email HAVING COUNT(email) > 1
Find duplicate values in multiple columns :- 
SELECT first_name, COUNT(first_name),last_name,COUNT(last_name),email,
COUNT(email) FROM contacts GROUP BY first_name,last_name,email
HAVING  COUNT(first_name) > 1 AND COUNT(last_name) > 1 AND COUNT(email) > 1;

Python Blitz Bits: Zippy Snippets & Spark Projects for Instant Coding Wins

  Python Blitz Bits: Zippy Snippets & Spark Projects for Instant Coding Wins