MySQL
Example on how to add FOREIGN KEY in MYSQL
FOREIGN KEY :
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE `customer_address`
ADD CONSTRAINT `fk.customer_address.customer_id`
FOREIGN KEY (`customer_id`) REFERENCES `user` (`ID`)
ON UPDATE CASCADE
ON DELETE CASCADE;
Magento 2 Delete the not used Cart after upgrade Magento
It's very recommanded to delete unused carts after you finish the upgrade using these queries all customers carts that are not converted to order will be deleted
select *
FROM `quote_address`
WHERE `customer_address_id` IS NULL AND `firstname` IS NULL AND `lastname` IS NULL and quote_id in (SELECT entity_id FROM `quote` where is_active= 1)
select * from quote where entity_id not in (select quote_id from quote_address)
Converting a single comma separated row into multiple rows
we can have it like this
SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS String FROM (SELECT [State], CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String FROM TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
assuiming we have table with two columns
1- State.
2- City (separated by Comma)
Recover MySQL password
Please check this URL
https://stackoverflow.com/a/34207996/4135373
Install percona-server on Mac Catalina
First of all Install Homebrew from here
After you finish you need to run this command
brew install percona-server
Initialize the DB
unset TMPDIR
mysqld --initialize --datadir=/usr/local/var/mysql --user={your username} --port=3307 --general-log-file=/var/log/percona/percona-error.log
Note :Don't forget to replace the username with your user {your username} = root
Export MySQL query to a file
the Main phrase is
Select ......... where ........... {INTO OUTFILE 'C:\\file_name.csv'}
Backup and Restore Database SSH
in this wiki we have Backing up and restoring Database from local server and external server,
Backup Database
Backup from local server
mysqldump -u{username} -p{passoword} {database} | gzip > {path}/backup_filename_$(date +%F).sql.gz
Backup from external server
mysqldump -P {port} -h {host} -u{username} -p{passoword} {database} | gzip > {path}/backup_file_name_$(date +%F).sql.gz
Backup specific tables
mysqldump -u{username} -p{passoword} {database} table1 table2 > {path}/table1_table2.sql