go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

type User struct {
  Name string `gorm:"<-:create"` // allow read and create
  Name string `gorm:"<-:update"` // allow read and update
  Name string `gorm:"<-"`        // allow read and write (create and update)
  Name string `gorm:"<-:false"`  // allow read, disable write permission
  Name string `gorm:"->"`        // readonly (disable write permission unless it configured)
  Name string `gorm:"->;<-:create"` // allow read and create
  Name string `gorm:"->:false;<-:create"` // createonly (disabled read from db)
  Name string `gorm:"-"`            // ignore this field when write and read with struct
  Name string `gorm:"-:all"`        // ignore this field when write, read and migrate with struct
  Name string `gorm:"-:migration"`  // ignore this field when migrate with struct
}

dsn := "u:p@tcp(127.0.0.1:3306)/banner_oa"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
   fmt.Println("err")
}

// 通过数据的指针来创建
user := User{0," ","keqing", "shuju","go",13148785215,"henan","biye","123456","laopo"}
result := db.Create(&user)
id := user.ID                   // 返回插入数据的主键
err = result.Error              // 返回 error
affected := result.RowsAffected // 返回插入记录的条数
fmt.Println(id,err,affected)

//定义接收查询结果的结构体变量
user := User{}
//定义接收查询所有表结果的结构体切片
var users []User
// 根据主键查询第一条记录  SELECT * FROM user ORDER BY id LIMIT 1;
//db.First(&user)
 随机获取一条记录 SELECT * FROM user LIMIT 1;
//db.Take(&user)
//
 根据主键查询最后一条记录 SELECT * FROM user ORDER BY id DESC LIMIT 1;
//db.Last(&user)
//
 查询所有的记录 SELECT * FROM user
//db.Find(&users)
//
// 查询指定的某条记录(仅当主键为整型时可用)
// SELECT * FROM user WHERE id = 9;
//db.Take(&user, 9)
//
//这种写法是直接往Select函数传递数组，数组元素代表需要选择的字段名
//db.Select([]string{"id", "name"}).Where("id = ?", 8).Take(&user)
//fmt.Println(user)
fmt.Println("查询的单条数据为",user)
//查询一列数据
db.Model(User{}).Pluck("Name", &users)
for _, v := range users {
  fmt.Println(v.Name)
}
//遍历表
db.Find(&users)
for _, v := range users {
   fmt.Println(v)
}

//Save 会保存所有的字段，即使字段是零值
user := User{}
db.Take(&user,"name=?","绫人")
user.Name = "绫华"
db.Save(&user)
//根据自定义条件更新记录，而不是根据主键id
//等价于: UPDATE `user` SET `password` = 123456  WHERE (password > 123456)
db.Model(User{}).Where("password > ?", 123456).Update("password", 123456)

//删除
//gorm具有软删除能力,通过一个特定的字段
//删除模型数据一般用于删除之前查询出来的模型变量绑定的记录。
//用法：db.Delete(模型变量)
//例子：
user := User{}
//先查询一条记录, 保存在模型变量user
//等价于: SELECT * FROM `user`  WHERE (id = '8') LIMIT 1
db.Where("id = ?", 8).Take(&user)
//删除food对应的记录，通过主键Id标识记录
//等价于： DELETE from `user` where id=8;
db.Delete(&user)
//等价于：DELETE from `user` where (`type` = 5);
db.Where("id = ?", 9).Delete(&User{})

// 开启事务
tx := db.Begin()
//在事务中执行数据库操作，使用的是tx变量，不是db。
//密码减一
//等价于: UPDATE `user` SET `password = password - 1  WHERE `user`.`id` = '20'
//RowsAffected用于返回sql执行后影响的行数
user := User{}
f1 := tx.Model(&user).Where("id = 20").Update("password", gorm.Expr("password - 1")).RowsAffected
f2 := tx.Model(&user).Where("id = 21").Update("password", gorm.Expr("password - 1")).RowsAffected
//if rowsAffected == 0 {
// //如果密码-1操作，返回影响行数为0，说明改密码失败了，20号的密码也不-1了
// tx.Rollback()
// log.Println("回滚完成，密码更改失败")
// return
//}
suc := f1 + f2
user = User{0," ","砂糖", "shuju","go",13148785215,"henan","biye","123456","laopo"}
f3 := tx.Create(&user).RowsAffected
//删除砂糖
//db.Where("name = ? ","砂糖").Take(&user)
//fmt.Println(user)
//f3 := tx.Delete(&user).RowsAffected
suc = suc + f3
//事务处理失败，则回滚事务
if suc != 3 {
   tx.Rollback()
   log.Println("回滚")
} else {
   tx.Commit()
}