生命不止，继续 go go go !!!
继续与大家分享，go语言的实战，今天介绍的是如何操作sqlite数据库。
何为sqlite3？
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine.
最主要的是，sqlite是一款轻型的数据库
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, err := sql.Open("sqlite3", "./foo.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)
// insert
stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)")
checkErr(err)
res, err := stmt.Exec("wangshubo", "国务院", "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)
}
}
