Difference between revisions of "MySQL"
From Useful Things
m (Fixed headings) |
|||
| Line 1: | Line 1: | ||
| − | = Command-line = | + | == Command-line == |
| − | == Common arguments == | + | === Common arguments === |
<code>-h</code> remote host<br /> | <code>-h</code> remote host<br /> | ||
<code>-u</code> remote user<br /> | <code>-u</code> remote user<br /> | ||
<code>-p</code> requires password | <code>-p</code> requires password | ||
| − | == Connect to remote MySQL server == | + | === Connect to remote MySQL server === |
<source lang="bash"> | <source lang="bash"> | ||
mysql -h remote.host.com -u remoteuser -p | mysql -h remote.host.com -u remoteuser -p | ||
</source> | </source> | ||
| − | = User management = | + | == User management == |
| − | == Adding a user == | + | === Adding a user === |
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
grant all privileges on {database_name}.* to {database_user}@'%' identified by 'their_password'; | grant all privileges on {database_name}.* to {database_user}@'%' identified by 'their_password'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | == Revoking privileges == | + | === Revoking privileges === |
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
revoke usage on {databse_name}.* from {database_user}@'%'; | revoke usage on {databse_name}.* from {database_user}@'%'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | == Removing a user == | + | === Removing a user === |
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
drop user {database_user}@'%'; | drop user {database_user}@'%'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | = Triggers = | + | == Triggers == |
| − | == Before == | + | === Before === |
* OLD table name does not exist here | * OLD table name does not exist here | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
| Line 38: | Line 38: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | == After == | + | === After === |
* OLD and NEW table names can be used to access the old value and the new value respectively | * OLD and NEW table names can be used to access the old value and the new value respectively | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
| Line 47: | Line 47: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | = Importing = | + | == Importing == |
| − | == From CSV == | + | === From CSV === |
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
load data local infile 'file_name.csv' into table table_name | load data local infile 'file_name.csv' into table table_name | ||
Latest revision as of 02:35, 11 January 2015
Contents
Command-line
Common arguments
-h remote host
-u remote user
-p requires password
Connect to remote MySQL server
mysql -h remote.host.com -u remoteuser -pUser management
Adding a user
grant all privileges on {database_name}.* to {database_user}@'%' identified by 'their_password';Revoking privileges
revoke usage on {databse_name}.* from {database_user}@'%';Removing a user
drop user {database_user}@'%';Triggers
Before
- OLD table name does not exist here
create trigger {trigger_name} before [update|insert|delete] on {table_name}
for each row begin
insert into {new_table} (col1, col2, col3) values (OLD.col1, OLD.col2, OLD.col3)
endAfter
- OLD and NEW table names can be used to access the old value and the new value respectively
create trigger {trigger_name} after [update|insert|delete] on {table_name}
for each row begin
insert into {new_table} (col1, col2, col3) values (OLD.col1, NEW.col2, OLD.col3)
endImporting
From CSV
load data local infile 'file_name.csv' into table table_name
fields terminated by ',' enclosed by '"'
lines terminated by '\n'
(col1, col2, coln, ...)