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;