Difference between revisions of "MySQL"

From Useful Things
Jump to: navigation, search
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

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)
	end

After

  • 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)
	end

Importing

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, ...)