MySQL Cheat Sheet

From Shrubbery

Jump to: navigation, search


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.

[edit] See Also

Personal tools