go学习 --- go对mysql的增删改查

一、go实现对数据库的增删改查

package main

import (
	"database/sql"
	"fmt"
	_"github.com/go-sql-driver/mysql" //初始化
)

//定义一个全局变量
var db *sql.DB
//定义初始化数据库的函数
func initDB()(err error)  {
	dsn := "root:123456@tcp(127.0.0.1:3306)/db02?charset=utf8mb4&parseTime=True"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println("db err:",err)
		return err
	}
	//与数据库连接
	err = db.Ping()
	if err != nil {
		fmt.Println("连接数据库失败",err)
		return err
	}
	return nil
}
//创建与数据库映射的结构体
type user struct {
	id int
	name string
	age int
}
//添加数据
func insertSql(name string,age int)  {
	sqlStr := "insert into user(name,age) values (?,?)"
	exec, err := db.Exec(sqlStr, name,age)
	if err != nil {
		fmt.Println("添加数据错误:",err)
		return
	}
	//插入数据的id
	id, err := exec.LastInsertId()
	if err != nil {
		fmt.Println("id err:",err)
		return
	}
	fmt.Println("id=",id)
}
//单条数据查询
func selectSql(id int)  {
	selStr := "select id,name,age from user where id = ? "
	var u user
	//调用Scan函数释放数据库的连接
	err := db.QueryRow(selStr, id).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Println("scan err:",err)
		return
	}
	fmt.Println("id=",u.id,"name=",u.name,"age=",u.age)
}
//查询多条数据
func selectAll()  {
	selStr := "select id,name,age from user where id > ? "
	query, err := db.Query(selStr, 0)
	if err != nil {
		fmt.Println("query err:",err)
		return
	}
	//关闭连接
	defer query.Close()
	//循环读取数据
	for query.Next() {
		var u user
		err := query.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println("scan err:",err)
			return
		}
		//打印查询到的数据
		fmt.Println("id=",u.id,"name=",u.name,"age=",u.age)
	}
}
//修改数据
func updateSql()  {
	selStr := "update user set name = ? where id = ? "
	exec, err := db.Exec(selStr, "老李", 7)
	if err != nil {
		fmt.Println("exec err:",err)
		return
	}
	//操作影响的行数
	affected, err := exec.RowsAffected()
	if err != nil {
		fmt.Println("affected err:",err)
		return
	}
	fmt.Println("修改成功的行数",affected)
}
//删除数据
func deleteSql()  {
	selStr := "delete from user where id = ? "
	exec, err := db.Exec(selStr, 7)
	if err != nil {
		fmt.Println("exec err:",err)
		return
	}
	affected, err := exec.RowsAffected()
	if err != nil {
		fmt.Println("affected err:",err)
		return
	}
	fmt.Println("删除成功的行数",affected)
}
func main() {
	//调用函数
	err := initDB()
	if err != nil {
		fmt.Println("init db failed:",err)
		return
	}
	fmt.Println("连接数据库成功")
	//调用添加数据的函数
	insertSql("老王",25)
	insertSql("老王2",26)
	insertSql("老王3",27)
	//调用查询函数
	selectSql(7)
	selectAll()
	//调用修改函数
	updateSql()
	//调用删除函数
	deleteSql()
}