mysql: create local user and remote user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON *.* TO 'myuser'@'localhost'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass GRANT ALL […]
Category: databases
mysql: extract domain from email
SUBSTR(email_addresses.email_address, INSTR(email_addresses.email_address, '@') + 1)
mysql: capitalize first word of all words
credits to: http://joezack.com/2008/10/20/mysql-capitalize-function/ create a function: CREATE FUNCTION CAP_FIRST (input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE len INT; DECLARE i […]
mysql: min value per group
surprisingly, to find the min date per company, which is a super basic query, on mysql you need to do a complicated […]
mysql: remove only_full_group_by and make it persistent
update it from the console (will revert upon reboot) from the command line go to the mysql console mysql then edit the […]
phpmyadmin: deprecation notice
sudo apt-get remove --purge phpmyadmin php-gettext php-mbstring -y sudo apt-get autoremove -y sudo apt-get update sudo apt-get install phpmyadmin php-gettext […]
pgsql: create table inside database
Example CREATE TABLE c_ordernotes ( c_ordernotes_id numeric(10,0) CONSTRAINT firstkey PRIMARY KEY, ad_client_id numeric(10,0) NOT NULL, ad_org_id numeric(10,0) NOT NULL, isactive character(1), […]
delete duplicate rows on pgsql and mysql
This code shows how to delete duplicate rows on pgsql DELETE FROM m_product WHERE sku in( SELECT sku FROM ( SELECT […]
pgsql: replace add break
UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'------------',chr(13)) WHERE em_mg_manufacturer = 1000637 AND value = 'IMI-YW356A01' Some additional replace scripts UPDATE […]
phpmyadmin: securing web console
I installed PHPMyadmin to check my help manage my database, eventhough I also use the desktop program MySQL Workbench, I fell sometimes […]