RUBY 和 SQLITE

1.创建数据库和获取单行或者是单列

require 'sqlite3'

  db = SQLite3::Database.new( "test.db" )
  rows = db.execute( "select * from test" )

2.数据库搜索 不同方式

  require 'sqlite3'

  db = SQLite3::Database.new( "test.db" )
  db.execute( "select * from test" ) do |row|
    ...
  end
 
  require 'sqlite3'

  db = SQLite3::Database.new( "test.db" )
  columns, *rows = db.execute2( "select * from test" )

  # or use a block:

  columns = nil
  db.execute2( "select * from test" ) do |row|
    if columns.nil?
      columns = row
    else
      # process row
    end
  end
 
 3.仅仅需要结果的第一行
 
  row = db.get_first_row( "select * from table" )count = db.get_first_value( "select count(*) from table" )
  4.重复执行的操作
 
  stmt = db.prepare( "select * from person" )

  1000.times do
    stmt.execute do |result|
      ...
    end
  end

  stmt.close

  # or, use a block

  db.prepare( "select * from person" ) do |stmt|
    1000.times do
      stmt.execute do |result|
        ...
      end
    end
  end
  5.SQL中的占位符比如
 
  :
  ??nnn
  :word 
 
  db.execute( "select * from table where a = ? and b = ?",
              "hello",
              "world" )   db.execute( "select * from table where a = :name and b = :value",
              "name" => "bob",
              "value" => "priceless" )  stmt = db.prepare( "select * from table where a = :name and b = ?" )

  stmt.bind_param( "name", "bob" )
  stmt.bind_param( 1, "value" )

  # or

  stmt.bind_params( "value", "name" => "bob" )  6.获取列信息  stmt = db.prepare( "select * from table where a = :name and b = ?" )

  stmt.bind_param( "name", "bob" )
  stmt.bind_param( 1, "value" )

  # or

  stmt.bind_params( "value", "name" => "bob" )  stmt = db.prepare( "select * from table where a = :name and b = ?" )

  stmt.bind_param( "name", "bob" )
  stmt.bind_param( 1, "value" )

  # or

  stmt.bind_params( "value", "name" => "bob" )  stmt = db.prepare( "select * from table" )
  p stmt.columns
  p stmt.types
 
  7.获取列数据 
  db.results_as_hash = true
  db.execute( "select * from table" ) do |row|
    p row['column1']
    p row['column2']
  end  require 'arrayfields'

  ...
  db.execute( "select * from table" ) do |row|
    p row[0] == row['column1']
    p row[1] == row['column2']
  end 
 
  8.I’d like the values from a query to be the correct types, instead of String.You can turn on “type translation” by setting Database#type_translation to true:
 
 
    db.type_translation = true
  db.execute( "select * from table" ) do |row|
    p row
  end
By doing this, each return value for each row will be translated to its correct type, based on its declared column type.You can even declare your own translation routines, if (for example) you are using an SQL type that is not handled by default:  # assume "objects" table has the following schema:
  #   create table objects (
  #     name varchar2(20),
  #     thing object
  #   )

  db.type_translation = true
  db.translator.add_translator( "object" ) do |type, value|
    db.decode( value )
  end

  h = { :one=>:two, "three"=>"four", 5=>6 }
  dump = db.encode( h )

  db.execute( "insert into objects values ( ?, ? )", "bob", dump )

  obj = db.get_first_value( "select thing from objects where name='bob'" )
  p obj == h
 
   9.How do insert binary data into the database? 
  
   db.execute( "insert into foo ( ?, ? )",
   
    SQLite3::Blob.new( "\0\1\2\3\4\5" ),
    SQLite3::Blob.new( "a\0b\0c\0d ) )
   
    10.How do I do a DDL (insert, update, delete) statement? 
   
     db.execute( "insert into table values ( ?, ? )", *bind_vars )
    
      11.How do I execute multiple statements in a single string?
     
        sql = <<SQL
    create table the_table (
      a varchar2(30),
      b varchar2(30)
    );

    insert into the_table values ( 'one', 'two' );
    insert into the_table values ( 'three', 'four' );
    insert into the_table values ( 'five', 'six' );
  SQL

  db.execute_batch( sql )
 
  12.How do I begin/end a transaction?
 
    database.transaction do |db| db.execute( "insert into table values ( 'a', 'b', 'c' )" ) ... end  db.transaction db.execute( "insert into table values ( 'a', 'b', 'c' )" ) db.commit