In the Database API of Drupal we have the next variant to actions:

// Select.
db_select('table', 't');
// Update.
db_update('table', 't');
// Delete.
db_delete('table', 't');
// Insert.
db_insert('table', 't');
// Merge it is combination of Insert action and Update action.
// You may use ut if you need Insert or Update some value.
db_merge('table');
// Static query.
db_query("SOME SQL WHERE value = :arg", array(':arg' => 'value'));

Below variants of execute.

// Returns array.
->fetchAll();
// Returns single record.
->fetchField();
// Returns associative array.
->fetchAllKeyed();
// Returns two-dimensional associative array.
->fetchAllAssoc('__some_name__');
// Fetches the next row and returns it as an associative array.
->fetchAssoc();
// Get object.
->fetchObject();
// Returns an entire single column of a result.
->fetchCol();
// Returns count of a row.
->rowCount();

Examples:

Get node title by node ID.

$title = db_select('node', 'n')
  ->fields('n', array('title'))
  ->condition('n.nid', 123)
  ->execute()
  ->fetchField();

Get all fields of a node by node ID.

$node = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', 123)
  ->execute()
  ->fetchObject();

Get "title" and "type" of all published nodes.

$nodes = db_select('node', 'n')
  ->fields('n', array('title', 'type'))
  ->condition('n.status', NODE_PUBLISHED)
  ->execute()
  ->fetchAll();

Get "title" of all published nodes and sort by date.

$nodes = db_select('node', 'n')
  ->fields('n', array('title'))
  ->condition('n.status', NODE_PUBLISHED)
  ->orderBy('n.created', 'DESC')
  ->execute()
  ->fetchAll();

Get fields "nid", "title", "status" and "created" of all published nodes and sort by date plus group by "uid".

$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title', 'status', 'created'))
  ->condition('n.status', NODE_PUBLISHED)
  ->orderBy('n.created', 'DESC')
  ->groupBy('n.uid');
  ->execute()
  ->fetchAll();

Get only 10 nodes.

$nodes = db_select('node', 'n')
  ->fields('n', array('title',))
  ->range(0, 10)
  ->execute()
  ->fetchAll();

Use "JOIN".

$query = db_select('node', 'n');
$query->Join('users', 'u', 'n.uid = u.uid');
// Also, you can use "innerJoin", "leftJoin" or "rightJoin".
// $query->innerJoin('users', 'u', 'n.uid = u.uid');
// $query->leftJoin('users', 'u', 'n.uid = u.uid');
// $query->rightJoin('users', 'u', 'n.uid = u.uid');
$query->fields('u', array('name'));
$query->fields('n', array('title'));
$query->condition('u.mail', 'admin@gmail.com');
$result = $query->execute()->fetchAll();

Use "OR".

$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition(
    db_or()
      ->condition('n.status', NODE_PUBLISHED)
      ->condition('n.nid', 123)
  )
  ->execute()
  ->fetchAll();

Use "IN".

$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', array(123, 456, 789), 'IN')
  ->execute()
  ->fetchAll();

Use "LIKE".

$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.title', '%' . db_like('Some node title') . '%', 'LIKE')
  ->execute()
  ->fetchAll();

Use "BETWEEN".

$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', array(123, 789), 'BETWEEN')
  ->execute()
  ->fetchAll();

Use "NULL".

$data = db_select('table', 't')
  ->fields('t')
  ->isNull('t.field')
  // ->isNotNull('t.field')
  ->execute()
  ->fetchAll();

Use "DISTINCT".

$data = db_select('table', 't')
  ->distinct()
  ->fields('t', array('field'))
  ->execute()
  ->fetchAll();

Use "RANDOM".

$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->orderRandom();
  ->execute()
  ->fetchAll();

Use "WHERE".

$nodes = db_select('node', 'n');
$nodes->fields('n', array('nid', 'title', 'status'));
$nodes->condition('n.status', NODE_PUBLISHED);
$nodes->where('YEAR(FROM_UNIXTIME(n.created)) = :created', array(':created' => 2012));
$result = $year->execute()->fetchAll();

Sometime you need do a select with join and both tables can have field like "title". Well Drupal will set separate name for these double fields like "title" and "title_1". But you can set any name for these fields.

$query = db_select('node', 'n');
$query->Join('table', 't', 'n.uid = t.uid');
$query->fields('n', array('title'));
$query->fields('t', array('title'));
$query->addField('n', 'title', 'node_title');
$query->addField('t', 'title', 'table_title');
$result = $query->execute()->fetchAll();

Date format.

$query = db_select('node', 'n');
$query->fields('n', array('created'));
$query->addExpression("FROM_UNIXTIME(n.created, '%Y/%m/%d')", 'new_date_format');
$date = $date->execute()->fetchAll();

Count the number of records in the table.

$count = db_select('node', 'n')
  ->countQuery()
  ->execute()
  ->fetchField();

Get max value of the field.

$query = db_select('node');
$query->addExpression('MAX(nid)');
$max = $query->execute()->fetchField();

Get associative array where key "nid" and value "title".

$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->execute()
  ->fetchAllKeyed();

Get two-dimensional associative array.

$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->execute()
  ->fetchAllAssoc('nid');

Update record.

db_update('node')
  ->fields(array('status' => NODE_PUBLISHED))
  ->condition('nid', 123)
  ->execute();

Delete record.

db_delete('node')
  ->condition('nid', 123)
  ->execute();

Insert record.

db_insert('table')
  ->fields(array('value' => 123))
  ->execute();

Merge record.

db_merge('table')
  ->key(array('id' => 123))
  ->insertFields(array(
    'field' => 'some_value',
  ))
  ->updateFields(array(
    'field' => 'some_value',
  ))
  ->execute();

Check table.

if (db_table_exists('table')) {
  // Actions ...
}

Cleaning table.

db_truncate('table')->execute();

Remove table / tables.

// Just remove a table.
db_drop_table('table');
// Remove all tables from hook_schema() implementation.
drupal_uninstall_schema('module_name');

Static query.

$title = db_query("SELECT title FROM {node} WHERE nid = :nid", array(':nid' => 123))->fetchField();
# drupal7 # database