Copy Below Code View As A Text File Show Text Only Show API Edit Code

                            
//Create Table Sample Laravel

CREATE TABLE `sources` ( 
  `id` INT AUTO_INCREMENT NOT NULL,
  `title` VARCHAR(250) NOT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
   PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

ALTER TABLE expenses ADD `is_paid` TINYINT DEFAULT 1 after description;

ALTER TABLE site_listing ADD `service_boarding` enum('No','Yes') DEFAULT 'No';

ALTER TABLE site_listing ADD `service_boarding` VARCHAR(255) DEFAULT NULL after id;

//MAKE PRIMAY KEY ----id
ALTER TABLE tablename MODIFY COLUMN id INT auto_increment PRIMARY KEY;

/// Add Foreign Key 
ALTER TABLE friends
ADD CONSTRAINT FK_UserID
FOREIGN KEY (userID) REFERENCES users(id); 

---------
ALTER TABLE expense_details
ADD CONSTRAINT fk_expense_details_expense_id
FOREIGN KEY (expenseID) REFERENCES expenses(id); 

#DELETE FROM expense_details WHERE expenseID NOT IN (SELECT id FROM expenses);

//Json decode  ***********************
SELECT JSON_UNQUOTE(JSON_EXTRACT(custom_fields, '$.username')) FROM contacts WHERE id = 40;



====================CALCULATE LAT AND LON===================================
SELECT denumire, (6371 * acos( cos( radians(45.20327) ) * cos( radians( coord_lat ) ) * cos( radians( 23.7806 ) - radians(coord_long) ) + sin( radians(45.20327) ) * sin( radians(coord_lat) ) )) AS distanta 
FROM obiective 
WHERE coord_lat<>'' 
    AND coord_long<>'' 
HAVING distanta<50 
ORDER BY distanta desc
========================================================
//FIND AND REPLACE
UPDATE menus SET menu_url=REPLACE(menu_url,'http://attorney-lawyers.com/client/prg/','http://localhost/pivtal/index.php/') WHERE 1
--------------------------------------
//MATCH
SELECT *
FROM tblcenter
WHERE MATCH (address) AGAINST('Magnolia Katherine')

SELECT * FROM tblcenter WHERE address REGEXP 'Magnolia|sdfdfd|Katherine';
-----------------------------------------------------------------------------
ALTER TABLE Persons DROP COLUMN DateOfBirth
-----
ALTER TABLE `site_listing` CHANGE `manufacurerid` `manufacturerid` INT;

================================================================================
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany'; 
---------------------------------------------------------------------------------


=============Creat Table====================
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);


CREATE TABLE `tableName` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `leadID` int(11) unsigned DEFAULT NULL,
  `hash` varchar(100) DEFAULT NULL,
  `field1` tinyint(1) DEFAULT NULL,
  `field2` tinyint(1) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `leadID_idx` (`leadID`),
   KEY `hash_idx` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


=================================

INSERT INTO db1.Vendors (id, company, email, active)
(SELECT id, company, email, active FROM db2.Vendors WHERE id = 75);

====================Check DB Access ===================================
SHOW VARIABLES LIKE 'max_user_connections';