Difference between revisions of "Mysql examples"
From lippmann wiki
(→rename or change a colume data type) |
|||
(3 intermediate revisions by the same user not shown) | |||
Line 5: | Line 5: | ||
GRANT ALL ON database.* TO 'newuser'@'localhost'; | GRANT ALL ON database.* TO 'newuser'@'localhost'; | ||
− | == | + | ==Set/update the password of a user== |
SET PASSWORD FOR 'user'@'host' = PASSWORD('new pw'); | SET PASSWORD FOR 'user'@'host' = PASSWORD('new pw'); | ||
+ | or | ||
+ | ALTER USER 'user'@'host' IDENTIFIED BY 'new pw'; | ||
==add a column to a table== | ==add a column to a table== | ||
Line 15: | Line 17: | ||
==rename or change a colume data type== | ==rename or change a colume data type== | ||
− | ALTER TABLE exports CHANGE hostname name | + | ALTER TABLE exports CHANGE hostname name INT |
==insert some values== | ==insert some values== | ||
Line 25: | Line 27: | ||
SELECT * FROM employees INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' | SELECT * FROM employees INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' | ||
− | With echo in a single line: | + | With echo in a single line in a shell: |
echo SELECT \* FROM employees INTO OUTFILE \'/tmp/employees.csv\' FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\\n\' | mysql -u root -p company | echo SELECT \* FROM employees INTO OUTFILE \'/tmp/employees.csv\' FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\\n\' | mysql -u root -p company | ||
+ | |||
+ | or using a here string (not supported in all shells) and cleaner quotes: | ||
+ | |||
+ | mysql -p company <<< "SELECT * FROM employees INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" |
Latest revision as of 14:33, 2 December 2019
Contents
create a database with a dedicated localhost only user
CREATE DATABASE database; CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON database.* TO 'newuser'@'localhost';
Set/update the password of a user
SET PASSWORD FOR 'user'@'host' = PASSWORD('new pw');
or
ALTER USER 'user'@'host' IDENTIFIED BY 'new pw';
add a column to a table
ALTER TABLE exports ADD level VARCHAR(20);
rename or change a colume data type
ALTER TABLE exports CHANGE hostname name INT
insert some values
INSERT INTO exports (hostname, export) VALUES('fuego', 'sec=sys,rw' )
output to csv
Directly from the mysql cli, or when piped to the mysql client:
SELECT * FROM employees INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
With echo in a single line in a shell:
echo SELECT \* FROM employees INTO OUTFILE \'/tmp/employees.csv\' FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\\n\' | mysql -u root -p company
or using a here string (not supported in all shells) and cleaner quotes:
mysql -p company <<< "SELECT * FROM employees INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"