Moodle uses XMLDB as an abstraction layer to support multiple database platforms and schema changes. You’ll need to know how this works to work with the database. In particular you’ll need to know the relevant constants.
They are all defined under and prefixed with XMLDB
For example, you can get the database field types like so:
cat lib/xmldb/xmldb_constants.php | grep XMLDB_TYPE
If you need some examples of the syntax, check out the core moodle install and upgrade files under lib/db as well.