Import dump:
1 |
mysql -u root [dbname] < script.sql |
Create dump:
1 |
mysqldump -h localhost -u root -p -c –add-drop-table –add-locks –lock-tables [name of the database] sqldump.sql |
Drop:
1 |
drop database if exists [dbname]; |
Create:
1 |
create database is not exists [dbname]; |
Update:
1 2 3 |
UPDATE catalog_stock SET quantity=1 WHERE id_catalog_stock=32557 |
Insert:
1 2 |
INSERT INTO cms_item_type (`key`, `label`, `description`, `xtype`) VALUES (“default_sorting”, “Default Sorting Option”, “Default Sorting Option”, “dropdown”); |
Select duplicates:
1 2 3 4 |
Select duplicates in fk_config SELECT COUNT(*), fk_config,name FROM product_table GROUP BY fk_config HAVING COUNT(*) > 1 |
Add new column and update Primary Key
1 2 |
alter table my_table add COLUMN my_column VARCHAR(10) NOT NULL DEFAULT ''; ALTER TABLE my_table drop PRIMARY KEY, add PRIMARY KEY (some_col, other_col, my_column); |
INNER Join:
1 2 3 4 |
SELECT prod.`id`, csto.`quantity`, prod.status FROM product_table prod INNER JOIN stock csto ON prod.`id` = csto.`fk_prod` WHERE csim.status = ‘active’ AND quantity < 4 ORDER BY prod.`id` ASC; |