Difference between revisions of "Mysql examples"

From lippmann wiki
Jump to: navigation, search
(rename or change a colume data type)
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Change the password of a user==
+
==create a database with a dedicated localhost only user==
  
SET PASSWORD FOR 'user'@'host' = PASSWORD('new pw');
+
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==
 
==add a column to a table==
Line 9: Line 17:
 
==rename or change a colume data type==
 
==rename or change a colume data type==
  
  ALTER TABLE exports CHANGE hostname name INT
+
  ALTER TABLE exports CHANGE hostname name INT
  
 
==insert some values==
 
==insert some values==
Line 19: 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

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'"