golang 操作MySQL

package main

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

//插入数据
func insertlab() {
	
	//1.打开连接
	dsn := `root:123456@tcp(localhost:3306/dbname)`

	db, err := sql.Open("mysql", dsn)
	db.Ping()

	//4.关闭数据库
	defer func() {
		if db != nil {
			db.Close()
		}
	}()

	if err != nil {
		fmt.Println("数据库连接失败")
		return
	}

	//2.预处理sql,?-->占位符
	insert := `insert into per values(?,?)`
	stmt, err := db.Prepare(insert)
	//4.关闭预处理语句
	defer func() {
		if stmt != nil {
			stmt.Close()
		}
	}()

	if err != nil {
		fmt.Println("预处理错误，")
		return
	}
	//参数和对应占位符号对应
	ret, err := stmt.Exec("张三", "北京海淀")
	if err != nil {
		fmt.Println("插入失败，")
		return
	}

	//3.读取结果
	count, err := ret.RowsAffected()
	if err != nil {
		fmt.Println("结果获失败，")
		return
	}
	if count > 0 {
		fmt.Println("新增成功！")
	} else {
		fmt.Println("新增失败！")
	}

	//其他 ，有时候需要获取新增主键的值
	id, _ := ret.LastInsertId()
	fmt.Println(id)

}

//更新
func updatelab() {
	//1.打开连接
	dsn := `root:123456@tcp(localhost:3306/dbname)`

	db, err := sql.Open("mysql", dsn)
	db.Ping()

	//4.关闭数据库
	defer func() {
		if db != nil {
			db.Close()
		}
	}()

	if err != nil {
		fmt.Println("数据库连接失败")
		return
	}

	//2.预处理sql,?-->占位符
	updatelab := `update  per  set name=? address=? whereid=?`
	stmt, err := db.Prepare(updatelab)
	//4.关闭预处理语句
	defer func() {
		if stmt != nil {
			stmt.Close()
		}
	}()

	if err != nil {
		fmt.Println("预处理错误，")
		return
	}
	//参数和对应占位符号对应
	ret, err := stmt.Exec("张三", "北京海淀", 3)
	if err != nil {
		fmt.Println("插入数据理错误，")
		return
	}
	count, _ := ret.RowsAffected()
	if count > 0 {
		fmt.Println("修改成功")
	} else {
		fmt.Println("修改失败")
	}

}

//删除数据
func deletelab() {

	//1.打开连接
	dsn := `root:123456@tcp(localhost:3306/dbname)`

	db, err := sql.Open("mysql", dsn)
	db.Ping()

	//4.关闭数据库
	defer func() {
		if db != nil {
			db.Close()
		}
	}()

	if err != nil {
		fmt.Println("数据库连接失败")
		return
	}

	//2.预处理sql,?-->占位符
	deltesql := `delete from  per where id=?`
	stmt, err := db.Prepare(deltesql)
	//4.关闭预处理语句
	defer func() {
		if stmt != nil {
			stmt.Close()
		}
	}()

	if err != nil {
		fmt.Println("预处理错误，")
		return
	}
	//参数和对应占位符号对应
	ret, err := stmt.Exec(3)
	if err != nil {
		fmt.Println("插入失败，")
		return
	}

	count, _ := ret.RowsAffected()
	if count > 0 {
		fmt.Println("删除成功！")
	}

}

//查询
func query() {
	//1.打开连接
	dsn := `root:123456@tcp(localhost:3306/dbname)`

	db, err := sql.Open("mysql", dsn)
	db.Ping()

	//4.关闭数据库
	defer func() {
		if db != nil {
			db.Close()
		}
	}()

	if err != nil {
		fmt.Println("数据库连接失败")
		return
	}

	//2.预处理sql,?-->占位符
	deltesql := `delete from  per where id=?`
	stmt, err := db.Prepare(deltesql)
	//4.关闭预处理语句
	defer func() {
		if stmt != nil {
			stmt.Close()
		}
	}()

	if err != nil {
		fmt.Println("预处理错误，")
		return
	}
	//查询参数
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println("查询失败！")
	}
	//循环遍历结果
	 rows.Next(){
		 var id int
		 var name,address string
		 //把行内值付给变量
		 rows.Scan(&id,&name,&address)
		 fmt.Println(id,name,address)

	}
	defer func() {
		if rows!=nil{
			rows.Close()
			fmt.Println("关闭结果集")
		}
	}()
}
func main() {
	query()

}