三个主文件 main 包
运行命令: go run .\main.go .\model.go .\server.go
第一个:
/*
查询
sql.DB 类型上用于查询的方法有: Query QueryRow QueryContext QueryRowContext
Query QueryContext 返回的类型
type Rows struct {}
Rows的方法
func(rs *Row) Close() error
func (rs *Row) ColumnTypes([]*ColumnType ,error)
func(rs *Row) Columns()([]string,err)
func(rs *Row) Err() error
func(rs *Row) Next() bool
func(rs *Row) NextResultSet() bool
func(rs *Row) Scan(dest ...interface{} ) error
运行命令: go run .\main.go .\model.go .\server.go 多个main文件
跟新 sql.DB 类型上的跟新(执行命令) 方法有 : Exec ExecContext
*/
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/denisenkom/go-mssqldb" // 根据自己放的驱动位置引用
)
var Db *sql.DB
var IsOpen bool = false
const (
dataSource = "localhost" // 本地 也可以填写网址
port = 1433 //数据端口号 目前是缺省值
user = "sa" //登录数据库的用户名
pwd = "123456" //登录密码
database = "MyText" //数据库 根据自己数据库填写
sqlType = "sqlserver" //数据类型
)
func main() {
//1. 连接数据库
//fmt.Println(OpenDataSource())
//2. 获取数据中的一条记录
//one := getOneRow(5)
//fmt.Println(one)
//3. 查询所有记录并且打印
//queAllData("TabSocre")
//4.修改
// one.Name = "房管局"
// one.OtherInfo = "修改好了"
// one.Score = -100
// one.Subject = "自然与人"
// fmt.Println(one.AlterData())
//5. 插入一条记录
cous := TabSocre{
Name: "飞龙上天",
Score: -200,
Subject: "巴拉巴拉巴拉",
OtherInfo: "插入的记录测试",
}
//fmt.Println(cous.InsertOne())
fmt.Println(cous.InsertOnereturnId())
//6. 删除一条记录
fmt.Println(Delet(1))
}
func OpenDataSource() bool {
conStr := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;encrypt=disable", dataSource, user, pwd, port, database) //encrypt=disable 登录方式很重要 否则会出现登录不成功
var err error
Db, err = sql.Open(sqlType, conStr)
if err != nil {
log.Fatalln("open函数发生错误!")
IsOpen = false
return IsOpen
}
//defer Db.Close()
err = Db.Ping()
if err != nil {
fmt.Println("连接数据库失败!" + conStr + err.Error())
IsOpen = false
return IsOpen
}
fmt.Println("连接上数据库:" + conStr)
IsOpen = true
return IsOpen
}
第二个: 更新 和 插入 方法
package main
import (
"database/sql"
"fmt"
)
type TabSocre struct {
ID int
Subject string
Name string
Score int
OtherInfo string
}
//更新方法
func (its *TabSocre) AlterData() bool {
if !IsCanOpenDB() {
return false
}
_, err := Db.Exec("update TabSocre set Subject=@sub, Score=@sco, Name=@name, OtherInfo=@other where ID=@id",
sql.Named("sub", its.Subject), sql.Named("sco", its.Score), sql.Named("name", its.Name), sql.Named("other", its.OtherInfo), sql.Named("id", its.ID))
if err != nil {
return false
}
return true
}
//数据库是否能够打开
func IsCanOpenDB() bool {
if !IsOpen {
if !OpenDataSource() {
return false
}
}
return true
}
//插入一条数据
func (its *TabSocre) InsertOne() (isOK bool) {
isOK = false
//1. 判断数据库是否打开
if !IsCanOpenDB() {
return
}
//2. 执行sql 语句
_, err := Db.Exec("insert into TabSocre (Subject,Score,Name,OtherInfo)values (@sub,@sco,@name,@other)",
sql.Named("sub", its.Subject), sql.Named("sco", its.Score), sql.Named("name", its.Name), sql.Named("other", its.OtherInfo), sql.Named("id", its.ID))
if err != nil {
return
}
isOK = true
return
}
func (its *TabSocre) InsertOnereturnId() (insID int) {
insID = -1
//1. 判断数据库是否打开
if !IsCanOpenDB() {
return
}
smt, err := Db.Prepare("insert into TabSocre (Subject,Score,Name,OtherInfo)values (@sub,@sco,@name,@other) ;select isNull(SCOPE_IDENTITY(),-1)")
if err != nil {
return
}
defer smt.Close()
err = smt.QueryRow(sql.Named("sub", its.Subject), sql.Named("sco", its.Score), sql.Named("name", its.Name), sql.Named("other", its.OtherInfo), sql.Named("id", its.ID)).Scan(&insID)
if err != nil {
fmt.Println(err.Error())
return
}
return
}
第三个文件:
package main
import (
"database/sql"
"fmt"
"time"
)
func getOneRow(id int) TabSocre {
socre := TabSocre{}
//row := Db.QueryRow(fmt.Sprintf("select ID,Subject,Name,Score,OtherInfo from TabSocre where ID= %d", id))
//带参数的sql语句查询
row := Db.QueryRow("select ID,Subject,Name,Score,OtherInfo from TabSocre where ID= @id", sql.Named("id", id))
if row != nil {
err := row.Scan(&socre.ID, &socre.Subject, &socre.Name, &socre.Score, &socre.OtherInfo)
if err != nil {
return socre
}
}
return socre
}
//查询所有表名 并进行打印
func queAllData(tabName string) {
conStr := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;encrypt=disable", dataSource, user, pwd, port, database) //encrypt=disable 登录方式很重要 否则会出现登录不成功
Db, err := sql.Open(sqlType, conStr)
//1. 查询表 TabSocre 拼接sql语句
stmt, err := Db.Prepare("select * from " + tabName)
if err != nil {
fmt.Println("创建查询语句失败!" + err.Error())
return
}
rows, err := stmt.Query()
if err != nil {
fmt.Println("查询数据失败!" + err.Error())
return
}
//打印列信息
clos, err := rows.Columns()
if err != nil {
return
}
data := make([]interface{}, len(clos))
for i := 0; i < len(clos); i++ {
data[i] = new(interface{})
fmt.Printf("%s\t", clos[i])
}
fmt.Println()
// 遍历每一行
for rows.Next() {
//塞数据
rows.Scan(data...)
PrintlnData(data)
fmt.Println()
}
defer rows.Close()
}
func PrintlnData(data []interface{}) {
if data == nil {
return
}
for _, val := range data {
switch v := (*(val.(*interface{}))).(type) {
case nil:
fmt.Print("Null")
case bool:
if v {
fmt.Print("True")
} else {
fmt.Print("False")
}
case time.Time:
fmt.Print(v.Format(time.RFC3339))
default:
fmt.Print(v)
}
fmt.Print("\t")
}
}
//删除一条记录
func Delet(id int) bool {
if !IsCanOpenDB() {
return false
}
// 1. 执行sql语句
_, err := Db.Exec("delete from TabSocre where ID=@id", sql.Named("id", id))
if err != nil {
fmt.Println(err.Error())
return false
}
return true
}
所用到的go的连接sqlserver驱动库是:https://download.csdn.net/download/weixin_43542114/14929391
欢迎不足之处 前来讨论,目前代码是鞥跑起来的。