Difference between revisions of "MySQL"
From Useful Things
| Line 34: | Line 34: | ||
insert into {new_table} (col1, col2, col3) values (OLD.col1, NEW.col2, OLD.col3) | insert into {new_table} (col1, col2, col3) values (OLD.col1, NEW.col2, OLD.col3) | ||
end | end | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | = Importing = | ||
| + | |||
| + | == From CSV == | ||
| + | <syntaxhighlight lang="mysql"> | ||
| + | load data local infile 'file_name.csv' into table table_name | ||
| + | fields terminated by ',' enclosed by '"' | ||
| + | lines terminated by '\n' | ||
| + | |||
| + | (col1, col2, coln, ...) | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 03:30, 4 July 2014
Contents
User 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, ...)