By implementing hook_schema() and specifying the tables your module declares, you can easily create and drop these tables on all supported database engines. You don't have to deal with the different SQL dialects for table creation and alteration of the supported database engines.
See the Schema API documentation at http://drupal.org/node/146843 for details on hook_schema(), where database tables are defined.
Return valueA schema definition structure array. For each element of the array, the key is a table name and the value is a table structure definition.