how to operate DB in drupal?

how to write and get connection in drupal?
  there is light DB abstract layer. you can go to the setting.php to see the connection of DB
  sites/$website/setting.php
  sites/default/setting.php
  define like this $db_url='mysql://username:password@localhost/databasename'; if you use pg just repace mysql to pgsql.
  use this DB abstract layer. you will not concern more about when you want to choose different DB
  we choose db_query to make the Business layer to DB layer seperately. and we can see above. when $db_url choose mysql
  it will choose different DB file. it means that when you choose mysql.it will choose database.mysqli.inc automaticly.  
  if you want to write a php code that don't related  with drupal. you can do as follow:
    1.include_once("include/bootstrap.inc") \
    2.drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE) 
    3.last you can use db_query();
   now we know how to get the connection and how to use it. so we can user "CRUDL" to operate the DB.as you can see follow
   db_query('select * from {users} where uid=%d',$node->vid);
   db_query("insert into {users}(nid,vid,punchine) values(%d,%d,'%s')",$node->nid,$node->vid,$node->punchine);
   db_query("update ");
   db_query("delete ");
   you will see the table have bracket. this is very useful. we can set the prefix for those tables. we use this style that
   will reduce the SQL inject. the follow one we will use more(%d,%s,%f,$b,%%)
   %s means string ,%d means numberic,%b means binary. we should not put quote for this,%f means float ,%%  use for serach faintly.
how to  get the total?
     $total= db_reault(db_query(?,?)) 
how to get the list?
   $sql=?
   $result=db_query(db_rewrite_sql($sql));
   while($data=db_fetch_object($result)){
         $node=node_load($data->nid);
          print node_view($node,TRUE);
   }
how to get the range data in DB?
        $type='blog'; $status=1;
        $sql="select * from {node} n where type='%s' and status=%d order by n.created desc";
        $result=db_query_range(db_rewrite_sql($sql),$type,$status,0,10);  //db_query_range is just like the limit
  
how to  make pagnation in drupal? as above we do like this.
        $type='blog'; $status=1;
        $sql="select * from {node} n where type='%s' and status=%d order by n.created desc";
        $result=pager_query(db_rewrite_sql($sql),$type,$status,0,10);  //db_query_range is just like the limit
        while($data=db_fetch_object($result)){
         $node=node_load($data->nid);
          print node_view($node,TRUE);
   }
        print theme('pager',null,10). don't need to pass the total of result. just pass the $result.
        if you want to deal more Data in DB. you need to user the temporary table and the ways.
        db_query_temporary($sql,$arguments,$tempoaray_table_name); and we can  create a table specially.
        P.S you should use bracket to temporary table.
how to change the search on any place in drupal? we can use hook. if you want to other one change the DB.
    you need to  use db_write_sql(); hook_db_rewrite_sql. if we want rewrite the some moule search
        function user_db_rewrite_sql($query,$primary_table,$primary_field,$args){
            switch($primary_filed){
                 case 'nid':
                      if(!user_access('administrator content')){
                         $array=array();
                      }
                      if($primary_table=='n'){
                          $array['where']="(n.moderate=0)";
                      }elseif(preg_match('@{node}([A-Za-z_]+)@',$query,$match)){
                          $node_table_alias=$match[1];
                          $array['join']="left join {node} n on $node_table_alias.nid=n.nid";
                          $array['where']="($node_table_alias.moderate=0)";
                      }
                      return $array
            }
        }
        if we don't use hook. it will like this select * from {node} n where n.type='blog' and n.status=1
        then we use it will change like this select * from {node} n where n.type='blog' and n.status=1 and n.moderate=0
        don't do like this  select * from node as n.comment as c where n.nid=c.nid. the db_write_sql can't understand it.
        how to connect many DB?
        as we see above. we have seen the $db_url='mysql://username:password@localhost/databasename'.actually,the $db_url
        is a array. and it's defuault if only have one. so we can do like this.
        $db_url['db1']='mysql://username:password@localhost/databasename';
        $db_url['default']='mysql://username:password@localhost/databasename'; this is drupal's default db connection.
        if we want to get this connect, we just db_set_active('db1'). that's so easy. if we want back. just db_set_active('default');
        we can't  use mysql and pgsql or some other db together. but  we can use mulply mysql or pgsql or some other db.
        if we want do use different type of DB. you can refer http://drupal.org/node/1952
        how to install  and change the DB in drupal?
        we can use module .install function. and the DB type we can get from tht $GLOBALS['db_type']. and we user hook_install
        function book_install(){
           switch($GLOBALS['db_type']){
              case 'mysql':
              case 'mysqli':
                 db_query('create sql'
                  /*!40100 DEFAULT CHARACTER SET UTF8*/ this mean mysql  version is above 4.01 that will excute this code
                  );

           }
        }
        when you want to change the db schema. you should update your install file. or write a update function like this.
        the order should be added it.
        function book_update_1(){ //the second book_update_2..
            $items=array();
            $items[]=update_sql("alter table {book} add column rank int_unsigned not null default'0'");
        }
        you can use http://yourwebsite/update.php to run it. as we mention before. we can use devel and  delete the related data in
        system table. when you uninstall it. you just delete the related table and varibles.
       how to write you own abstract DB layer?
        we call it DNAbase.. just copy includes/database.mysql.inc and rename it withe includes/database.dnabase.inc.
        when we use it. just change $db_url mysql to dnabase.