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