MySQL
Contents
Install
https://www.digitalocean.com/community/tutorials/how-to-install-the-latest-mysql-on-debian-10 sudo apt install mariadb-server sudo mysql_secure_installation sudo mysql -u root -p
Security
Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server http://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server
Just perform the following steps: 1) Connect to mysql
mysql -uroot -p
2) Create user
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
3) Grant permissions
GRANT ALL PRIVILEGES ON \*.\* TO 'user'@'%' WITH GRANT OPTION;
4) Flush priviledges
FLUSH PRIVILEGES;
Connect from outside localhost
# For maria-db sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf # For mysql sudo vim /etc/my.cnf
[mysqld] bind-address = 0.0.0.0
sudo systemctl restart mariadb.service
Importing from csv
# For maria-db sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf # For mysql sudo vim /etc/my.cnf
[mysqld] secure-file-priv=/mysqlfiles
sudo mkdir /mysqlfiles sudo chown -R mysql:mysql /mysqlfiles/
sudo systemctl restart mysql.service sudo systemctl restart mariadb.service
code snipps
clear and update tables from csv
A nice "easy" script to clear and update tables from csv.
TRUNCATE TABLE lookups.cbot_months; LOAD DATA LOCAL INFILE 'C:/Data/MySQL/Lookups/cBOT_months.csv' INTO TABLE lookups.cbot_months FIELDS TERMINATED BY ',' ENCLOSED BY '"' lines terminated by '\r\n' IGNORE 1 LINES;
Select rows from a table that are not in another
You need to do the subselect based on a column name. For example, if you had an id field common to both tables, you could do:
SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)
Using AND, OR, and NOT together
You can combine the three logical operators in a compound condition. Your DBMS uses SQL’s precedence rules to determine which operators to evaluate first. Precedence is covered in “Determining the Order of Evaluation” in Chapter 5, but for now you need know only that when you use multiple logical operators in a compound condition, NOT is evaluated first, then AND, and finally OR. You can override this order with parentheses: Everything in parentheses is evaluated first. When parenthesized conditions are nested, the innermost condition is evaluated first. Under the default precedence rules, the condition x AND NOT y OR z is equivalent to (x AND (NOT y)) OR z. It’s wise to use parentheses, rather than rely on the default evaluation order, to make the evaluation order clear.
MySQL count occurrences greater than 2, find duplicate lines/entries
SELECT count(DOC_NUMBER), DOC_NUMBER as count FROM jde_dw.ap_invoice_list GROUP BY DOC_NUMBER HAVING count >= 2;
And now how to delete them!
CREATE TABLE web_apps.localpnl_exposure_history AS SELECT * FROM web_apps.localpnl_exposure_history_back LIMIT 0; SELECT * FROM web_apps.localpnl_exposure_history_back; SELECT * FROM web_apps.localpnl_exposure_history; ALTER TABLE web_apps.localpnl_exposure_history_back ADD PRIMARY KEY (JDCS,Exposure_Date(20));
create a new temporary table with same structure
An alternative way would be to create a new temporary table with same structure.
CREATE TABLE temp_table AS SELECT * FROM original_table LIMIT 0
Then create the primary key in the table.
ALTER TABLE temp_table ADD PRIMARY KEY (primary-key-field)
Finally copy all records from the original table while ignoring the duplicate records.
INSERT IGNORE INTO temp_table AS SELECT * FROM original_table
Now you can delete the original table and rename the new table.
DROP TABLE original_table RENAME TABLE temp_table TO original_table
CAST a string into a number while removing/replacing commas
SELECT SUM(replace( ifnull( Sell_Qty, 0 ), ',', )) FROM mbs_dw.futures_cbot;
mysqldump
backup
mysqldump -u [uname] -p[pass] db_name > db_backup.sql mysqldump db_name > db_backup.sql
restore
C:\> mysql -u root -p mysql> create database mydb; mysql> use mydb; mysql> source db_backup.dump;