MySQL Cheat Sheet
From Shrubbery
- MySQL things that I always forget the syntax for.
Contents |
[edit] Rename a Column
alter table {table_name} change {old_col_name} {new_col_name} [col_definition]
[edit] Change column type
alter table {table_name} modify {col_name} {new_col_definition}
or
alter table {table_name} change {col_name} {col_name} {new_col_definition}
[edit] Rename a Table
alter table {old_name} rename {new_name};
[edit] Foreign Key Constraints
Defining FK constraints in the CREATE TABLE statement:
create table {table_name} ( {column_def} [ , {column_def} ] ...
primary key ( {primary_key_column(s)} ),
constraint {constraint_name} foreign key {index_ame}
( {fk_col_name} ) references {ref_table_name} ( {ref_col_name} )
);
Defining FK constraints after the fact:
alter table {table_name}
add constraint {constraint_name} foreign key {index_name}
( {fk_col_name} ) references {ref_table_name} ( {ref_col_name} );
Drop a foriegn key constraint:
alter table {table_name}
drop foreign key {index_name}
Typically, you will want to drop constraints before doing any bulk loading operations on the database, or before dropping the columns involved in the FK constraint.
[edit] Grant Permissions
Allow an app server full control over it's database:
grant all privileges on {appdbname}.* to '{appuser}'@'{apphost}' identified by {apppassword}
- {appdbname} is the database the application will use.
- {appuser} and {apppassword} are the username and password the application server will use to connect to the database.
- {apphost} is typically localhost when the app server and the db server are running on the same machine.
- {apphost} should be a subnet expression when the app server is running on a different host, e.g. 192.168.0.%.
Remember: The wildcard character for network addresses in MySQL is %, not * as you might expect.
