golang入门笔记—数据库操作-Go开发-郭俊杰的知识库

golang入门笔记—数据库操作

Data: 2018-10-19 13:09:36Form: JournalClick: 10

//Go连接MySQL示例
import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)
func connect()(err error) {
	//数据库信息
	dsn := "root:******@tcp(localhost:3306)/login" //"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	//连接数据库
	db, err := sql.Open("mysql", dsn) //不会校验用户名密码是否正确，只校验数据源格式
	if err != nil {                   //dsn格式不正确时报错
		fmt.Println("open database err", err)
		return
	}
	err = db.Ping() //用来测试账号密码
	if err != nil {
		fmt.Println("open database err", err)
		return
	}
	db.SetMaxOpenConns(10) //设置最大数据库连接数
	fmt.Println("连接数据库成功！")

}

//数据库的查询
type user struct {
	id       string
	password string
	money    int
	score    int
}

//查询单条记录
func queryRow(id int) {
	var u1 user
	// 1.写查询单条记录的sql语句sqlStr
	sqlStr := "Select password,score from user where id=?"

	//2.执行
	rowObj := db.QueryRow(sqlStr, id) //从连接池里拿一个连接出去数据库查询单条记录

	//3.拿到结果
	rowObj.Scan(&u1.password, &u1.score) //必须对rowObj对象调用Scan方法。因为该方法会释放数据库链接
	//也可以写成db.QueryRow(sqlStr,id).Scan(&u1.password, &u1.score)
	//打印结果
	fmt.Printf("u1:%#v\n", u1)
}

//查询多条数据
func queryMulRow(score int) {
	var u1 user
	// 1.写查询单条记录的sql语句sqlStr
	sqlStr := "Select id,password from user where score>?"
	//2.执行
	rows, err := db.Query(sqlStr, score) //从连接池里拿一个连接出去数据库查询单条记录
	if err != nil {
		fmt.Println("query failed,err:", err)
	}
	//3.一定要关闭rows
	defer rows.Close()
	//4.循环取值
	for rows.Next() {
		err := rows.Scan(&u1.id, &u1.password)
		if err != nil {
			fmt.Println("scan failed err:\n", err)
		}
		fmt.Printf("u1:%#v\n", u1)
	}
}

func insert() (err error) {
	//1.写SQL语句
	sqlStr := `Insert into user(id,password) values("10","qwe")`
	//2.exec
	ret, err := db.Exec(sqlStr)
	if err != nil {
		fmt.Println("insert failed,err", err)
		return
	}
	//如果是插入数据的操作，能够拿到插入数据的id
	id, err := ret.LastInsertId()
	if err != nil {
		fmt.Println("get id failed:", err)
		return
	}
	fmt.Println("id:", id)
	return nil
}

//数据库更新
func update(score int, newid string) {
	sqlStr := `Update user set score=? where id=?`
	ret, err := db.Exec(sqlStr, score, newid)
	if err != nil {
		fmt.Println("insert failed,err:", err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Println("get id failed,err:", err)
		return
	}
	fmt.Printf("更新了%d行数据\n", n)
}

//删除
func delete(id int) {
	sqlStr := "delete from user where id=?"
	ret, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("delete failed,err:", err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Println("get affected failed,err:", err)
		return
	}
	fmt.Printf("删除了%d行数据\n", n)
}

Mysql预处理：

func prepareInsert(){
	sqlStr:=`Insert into user(id,password) values(?,?)`
	stmt,err:=db.Prepare(sqlStr) //把sql语句先发给Mysql预处理一下
	if err!=nil{
		fmt.Println("prepare failed,err",err)
		return
	}
	defer stmt.Close()
	//后续只需要拿到stmt去执行一些操作
	var m=map[string]int{
		"六七强":30,
		"王相机":32,
		"天说":72,
		"白慧姐":40,
	}
	for k,v:=range m{
		stmt.Exec(k,v)
	}
	stmt.Exec("10","zyj") //后续只需要传值
}

Go实现MySQL事务

//事务
func transaction() {
	//1.开启事务
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed:", err)
		return
	}
	//执行多个SQL操作
	sqlStr := `Update user set score=score-200 where id=1`
	sqlStr2 := `Update user set score=score-200 where id=2`
	//执行SQL1
	_, err = tx.Exec(sqlStr)
	if err != nil {
		//要回滚
		tx.Rollback()
		fmt.Println("执行SQL出错了,要回滚！")
		return
	}
	//执行SQL2
	_, err = tx.Exec(sqlStr2)
	if err != nil {
		//要回滚
		tx.Rollback()
		fmt.Println("执行SQL2出错了,要回滚！")
		return
	}
	//上面两步SQL都执行成功，就提交本次事务
	err = tx.Commit()
	if err != nil {
		//要回滚
		tx.Rollback()
		fmt.Println("提交出错了,要回滚！")
		return
	}
	fmt.Println("事务执行成功！")
}

//数据库的关闭
func close(){
	db.Close()
}

//sqlx的使用
package main

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
//Go连接MySQL示例
var db *sqlx.DB //是一个连接池对象

func connect() (err error) {
	//数据库信息
	dsn := "root:*******@tcp(localhost:3306)/login" //"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	//连接数据库
	db, err = sqlx.Open("mysql", dsn) //不会校验用户名密码是否正确，只校验数据源格式
	if err != nil {                   //dsn格式不正确时报错
		return
	}
	//设置数据库连接池的最大连接数
	db.SetMaxOpenConns(10) //设置最大数据库连接数
	return nil
}

type user struct {
	Id       string
	Password string
	Money    int
	Score    int
}

func insert() (err error) {
	//1.写SQL语句
	sqlStr := `Insert into user(id,password) values("10","qwe")`
	//2.exec
	ret, err := db.Exec(sqlStr)
	if err != nil {
		fmt.Println("insert failed,err", err)
		return
	}
	//如果是插入数据的操作，能够拿到插入数据的id
	id, err := ret.LastInsertId()
	if err != nil {
		fmt.Println("get id failed:", err)
		return
	}
	fmt.Println("id:", id)
	return nil
}


func main() {
	err := connect()
	if err != nil {
		fmt.Println("init DB failed,err", err)
		return
	}
	sqlStr := "Select * from user where id=1"
	var u user
	db.Get(&u, sqlStr)
	fmt.Println("u:", u)

	var userlist []user
	sqlStr2 := "Select * from user"
	err = db.Select(&userlist, sqlStr2)
	if err != nil {
		fmt.Println("Select err", err)
		return
	}
	fmt.Printf("userList:%#v\n", userlist)
}
//sqlx库其他操作相同

Redis：
参考博客

package main

import (
	"fmt"

	"github.com/go-redis/redis"
)

var redisdb *redis.Client

func initRedis() (err error) {
	redis.NewClient(&redis.Options{
		Addr:     "127.0.0.1:6379",
		Password: "",
		DB:       0,
	})
	_, err = redisdb.Ping().Result()
	return
}
func main() {
	err := initRedis()
	if err != nil {
		fmt.Println("connect redis failed:", err)
	}
	fmt.Println("连接redis成功")
	//zset
	key:="rank"
	items:=[]*redis.Z{
		&redis.Z{Score:99,Member:"PHP"},
		&redis.Z{Score:96,Member:"Golang"},
		&redis.Z{Score:97,Member:"Python"},
		&redis.Z{Score:99,Member:"Java"},
	}
	//把元素都追加到key
	redisdb.ZAdd(key,items...)
	//给Golang+10分
	newScore,err:=redis.ZIncrBy(key,10.0,"Golang").Result()
	if err!=nil{
		fmt.Printf("zincrby failed,err\n",err)
		return
	}
	fmt.Printf("Golang's score is %f now.\n",newScore)
}

//登陆系统
package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func conn() (err error) {
	dsn := "root:*******@tcp(localhost:3306)/login"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping()
	if err != nil {
		return
	}
	return
}

func check(id string, password string) bool {
	sqlStr := "select password from user where id=?"
	result := db.QueryRow(sqlStr, id)
	var pass string
	result.Scan(&pass)
	if pass == "" {
		fmt.Println("账号输入错误!")
		return false
	} else if pass != password {
		fmt.Println("密码输入错误")
		return false
	} else {
		return true
	}
}

func login() bool {
	var id, password string
	fmt.Print("请输入账号:")
	fmt.Scanln(&id)
	fmt.Print("请输入密码:")
	fmt.Scanln(&password)
	if check(id, password) {
		return true
	} else {
		return false
	}
}

func registe() bool {
	var id string
	fmt.Print("请输入注册账号:")
	fmt.Scanln(&id)
	var password string
	sqlStr := "select password from user where id=?"
	result := db.QueryRow(sqlStr, id)
	result.Scan(&password)
	if password != "" {
		fmt.Println("该用户已存在！")
		return false
	}
	fmt.Print("请输入注册密码:")
	fmt.Scanln(&password)
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("系统出错！")
		return false
	}
	_, err = tx.Exec("insert into user(id,password)values(?,?)", id, password)
	if err != nil {
		fmt.Println("输入有误")
		tx.Rollback()
		return false
	}
	tx.Commit()
	return true
}

func game() {

}

func main() {
	err := conn()
	if err != nil {
		fmt.Println("连接失败,err:", err)
		return
	}
	for {
		fmt.Println(`
	1.登入
	2.注册
	3.退出
	`)
		var n int
		fmt.Scanf("%d\n", &n)
		switch n {
		case 1:
			if login() {
				fmt.Println("登入成功!")
				game()
			}
		case 2:
			if registe() {
				fmt.Println("注册成功！")
			}
		case 3:
			return
		}
	}
}

从数据库中读取数据放到csv中

package main

import (
	"database/sql"
	"encoding/csv"
	_ "github.com/go-sql-driver/mysql"
	"os"
)

var (
	db  *sql.DB
	err error
)

type user struct {
	Uid      string
	Name     string
	Phone    string
	Email    string
	Password string
}

func connect() {
	db, err = sql.Open("mysql", "root:*******@tcp(127.0.0.1:3306)/user")

	if err != nil {
		panic(err)
	}

}

func queryData() []user {
	rows, err := db.Query("select * from `user` where uid>?", 1)
	if err != nil {
		panic(err)
	}
	users := []user{}
	u := user{}
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone, &u.Email, &u.Password)
		if err != nil {
			panic(err)
		}
		users = append(users, u)
	}
	return users
}

func writeToCSV(filename string, data [][]string) {
	fp, err := os.Create(filenam

我是 PHP 程序员

golang入门笔记—数据库操作