生命不止，继续 go go go !!!
继续与大家分享，go语言的实战，今天介绍的是如何操作sqlite数据库。
何为sqlite3？
sqlite is a self-contained,high-reliability,embedded,full-featured,public-domain,sql database engine.
database/sql包
go中有一个database/sql package，我们看看是怎样描述的：
Package sql provides a generic interface around sql (or sql-like) databases.
The sql package must be used in conjunction with a database driver
很清晰吧，需要我们自己提供一个database driver。当然，我们可以在github上找到相关的sqlite3的driver，稍后介绍。
下面介绍接个数据相关的操作：
Open
func Open(driverName,dataSourceName string) (*DB,error)
需要提供两个参数，一个driverName，一个数据库的名。
Prepare
func (db *DB) Prepare(query string) (*Stmt,error)
Prepare creates a prepared statement for later queries or executions.
返回的 *Stmt是什么鬼？
Stmt
Stmt is a prepared statement. A Stmt is safe for concurrent use by multiple goroutines.
func (*Stmt) Exec
准备完成后，就要执行了。
func (s *Stmt) Exec(args ...interface{}) (Result,error)
Exec executes a prepared statement with the given arguments and returns a Result summarizing the effect of the statement.
返回的Resault是什么鬼？
Result
type Result interface {
// LastInsertId returns the integer generated by the database
// in response to a command. Typically this will be from an
// "auto increment" column when inserting a new row. Not all
// databases support this feature,and the Syntax of such
// statements varies.
LastInsertId() (int64,error)
// RowsAffected returns the number of rows affected by an
// update,insert,or delete. Not every database or database
// driver may support this.
RowsAffected() (int64,error)
}
Query
func (s *Stmt) Query(args ...interface{}) (*Rows,error)
查询，返回的Rows是什么鬼？
Rows
Rows is the result of a query. Its cursor starts before the first row of the result set.
func (rs *Rows) Next() bool
Next prepares the next result row for reading with the Scan method
func (rs *Rows) Scan(dest ...interface{}) error
Scan copies the columns in the current row into the values pointed at by dest.
介绍少不多了，下面介绍一个sqlite3的Driver：
mattn/go-sqlite3
sqlite3 driver for go that using database/sql
github地址：
https://github.com/mattn/go-sqlite3
执行：
go get -u github.com/mattn/go-sqlite3
下面要开始我们的实战了！！！！
创建数据库，创建表
//打开数据库，如果不存在，则创建
db,err := sql.Open("sqlite3","./test.db")
checkErr(err)
//创建表
sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT,username VARCHAR(64) NULL,departname VARCHAR(64) NULL,created DATE NULL ); `
db.Exec(sql_table)
新建一个数据库叫test.db，并在这个数据库中建一个表，叫做userinfo。
userinfo中包含了四个字段，uid username departname created.
把uid设置为主键，并AUTOINCREMENT，自增。
插入数据
stmt,err := db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
checkErr(err)
res,err := stmt.Exec("wangshubo","国务院","2017-04-21")
checkErr(err)
显示Prepare，然后Exec.
接下来，就不再赘述了，我们需要一个基本的sql知识。
补充：import中_的作用
官方解释：
To import a package solely for its side-effects (initialization),use the blank identifier as explicit package name:
import _ "lib/math"
当导入一个包时，该包下的文件里所有init()函数都会被执行。
然而，有些时候我们并不需要把整个包都导入进来，仅仅是是希望它执行init()函数而已。这个时候就可以使用 import _ 引用该包。
最后献上全部代码：
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//打开数据库，如果不存在，则创建
db,"./foo.db")
checkErr(err)
//创建表
sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT,created DATE NULL ); `
db.Exec(sql_table)
// insert
stmt,"2017-04-21")
checkErr(err)
id,err := res.LastInsertId()
checkErr(err)
fmt.Println(id)
// update
stmt,err = db.Prepare("update userinfo set username=? where uid=?")
checkErr(err)
res,err = stmt.Exec("wangshubo_new",id)
checkErr(err)
affect,err := res.RowsAffected()
checkErr(err)
fmt.Println(affect)
// query
rows,err := db.Query("SELECT * FROM userinfo")
checkErr(err)
var uid int
var username string
var department string
var created time.Time
for rows.Next() {
err = rows.Scan(&uid,&username,&department,&created)
checkErr(err)
fmt.Println(uid)
fmt.Println(username)
fmt.Println(department)
fmt.Println(created)
}
rows.Close()
// delete
stmt,err = db.Prepare("delete from userinfo where uid=?")
checkErr(err)
res,err = stmt.Exec(id)
checkErr(err)
affect,err = res.RowsAffected()
checkErr(err)
fmt.Println(affect)
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
