go之gorm学习

一、gorm概念

Gorm 是 Golang 的一个 orm 框架。ORM 是通过实例对象的语法，完成关系型数据库的操作，是"对象-关系映射"(Object/Relational Mapping) 的缩写。使用 ORM 框架可以让我们更方便的操作数据库。

二、gorm连接数据库

package mainimport ("fmt""gorm.io/driver/mysql" #mysql驱动"gorm.io/gorm"#gorm)var (db *gorm.DBerr error)func init() {//sql日志newLogger := logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{SlowThreshold: time.Second,LogLevel:logger.Info,Colorful:true,})db, err = gorm.Open(mysql.Open("root:123456@tcp(127.0.0.1:3309)/test?charset=utf8mb4&parseTime=True&loc=Local"), &gorm.Config{Logger: newLogger})if err != nil {fmt.Println("connect database failed,err:", err)return}}

Debug

#debug为logger.Info级别db.Debug().Where("name = ?", "jinzhu").First(&User{})

三、创建表结构

3.1、gorm.Model

// gorm.Model 的定义type Model struct {ID uint `gorm:"primarykey"`CreatedAt time.Time//用于存储记录的创建时间UpdatedAt time.Time //用于存储记录的修改时间DeletedAt DeletedAt `gorm:"index"` //用于存储记录的删除时间}

3.2、字段标签

3.3、关联字段标记

3.4、Automigrate自动迁移

package mainimport "fmt"/*老师和课程一对一课程和分数一对一学生和分数一对多(分数)学生和课程多对多*/type Student struct {IDint`gorm:"type:int"`Sname string `gorm:"type:varchar(4);not null;comment '姓名'"`Gender string `gorm:"type:char(4);default:'男';comment '性别'"`Phone string `gorm:"char(11);not null;comment '手机号'"`Course []Course `gorm:"many2many:StudentCourse"`Grade []Grade `gorm:"ForeignKey:StudentId;AssociationForeignKey:ID;constraint:OnDelete:CASCADE"`}//默认情况表名为结构体名小写+s//重写表名func (table *Student) TableName() string {return "student"}type Teacher struct {IDint `gorm:"type:int"`Tname string `gorm:"type:varchar(4);not null;comment '老师姓名'"`Gender string `gorm:"type:char(4);default:'男';comment '性别'"`Phone string `gorm:"char(11);not null;comment '手机号'"`}func (table *Teacher) TableName() string {return "teacher"}type Course struct {ID int `gorm:"type:int"`Cnamestring `gorm:"type:varchar(10);not null;comment '课程名'"`TeacherId int `gorm:"type:int;default:null;comment '老师id'"`Teacher *Teacher `gorm:"ForeignKey:TeacherId;AssociationForeignKey:ID"` //is a 一对一关联Student []Student `gorm:"many2many:StudentCourse"`}func (table *Course) TableName() string {return "course"}type Grade struct {ID int`gorm:"type:int"`Scorefloat64 `gorm:"type:float;default:0;comment '分数'"`StudentId int`gorm:"type:int;default:null;comment '学生id'"`CourseId int`gorm:"type:int;default:null;comment '课程id'"`Course *Course `gorm:"ForeignKey:CourseId;AssociationForeignKey:ID;constraint"`}func (table *Grade) TableName() string {return "grade"}func DataMigration() {//迁移多张表if err := db.AutoMigrate(&Teacher{}, &Student{}, &Course{}, &Grade{}); err != nil {fmt.Println("数据迁移失败")return}fmt.Println("数据迁移成功")// 设置附加参数db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&Teacher{}, &Student{}, &Course{}, &Grade{})}

四、gorm之CRUD

4.1、create

func InsertTeacher() {//方法一teacher := Teacher{Tname: "张三",Gender: "男",Phone: "13333333333",}db.Create(&teacher)//方法二var teachers []Teacherteacher1 := Teacher{Tname: "李四",Gender: "男",Phone: "16666666666",}teacher2 := Teacher{Tname: "王五",Gender: "女",Phone: "19999999999",}teachers = append(teachers, teacher1)teachers = append(teachers, teacher2)db.CreateInBatches(&teachers, 2)}func InsertCourse() {//多对多表数据插入course1 := Course{Cname:"语文",TeacherId: 1,}course2 := Course{Cname:"数学",TeacherId: 2,}course3 := Course{Cname:"英语",TeacherId: 3,}//第一次插入db.Create(&[]Student{{Sname: "小明", Gender: "男", Phone: "14444444444", Course: []Course{course1, course2, course3}},})course4 := Course{ID: 1,}course5 := Course{ID: 2,}db.Create(&Student{Sname: "二明", Gender: "女", Phone: "16666666666", Course: []Course{course4, course5},})course6 := Course{ID: 3,}//第二次插入db.Create(&Student{Sname: "大明", Gender: "男", Phone: "18888888888", Course: []Course{course6},})}func InsertGrade() {grades := []Grade{{Score: 92, StudentId: 1, CourseId: 1},{Score: 88, StudentId: 1, CourseId: 2},{Score: 61, StudentId: 1, CourseId: 3},{Score: 95, StudentId: 2, CourseId: 1},{Score: 93, StudentId: 2, CourseId: 2},{Score: 74, StudentId: 3, CourseId: 3},}db.NewRecord(grades ) // 主键为空返回`true`db.Create(&grades)db.NewRecord(grades ) // 创建`grades `后返回`false`}

4.2、Delete

// 删除id=1的课程course := Course{}db.First(&course,1)db.Delete(&course)//删除学生id=1的分数db.Where("student_id = ?", 1).Delete(Grade{})

4.3、Update

student:= Student{}db.First(&student)student.Sname = "老大"student.Gender = "女"db.Save(&student)// 更新单个属性，如果它有变化db.Model(&student).Update("sname", "老二")// 根据给定的条件更新单个属性db.Model(&student).Where("id= ?", 3).Update("sname", "老三")// 使用 map 更新多个属性，只会更新其中有变化的属性db.Model(&student).Updates(map[string]interface{}{"sname": "老大", "gender": "女"})// 使用 struct 更新多个属性，只会更新其中有变化且为非零值的字段db.Model(&student).Updates(Student{Sname: "老大", Gender: "女"})//只更新某些字段，这里只更新姓名不更新性别db.Model(&student).Select("sname").Updates(map[string]interface{}{"sname": "老大","gender":"女"})//不更新某些字段，这里只更新性别不更新姓名db.Model(&student).Omit("sname").Updates(map[string]interface{}{"sname": "老大", "gender": "女"})

4.4、Read

// 根据主键查询第一条数据student := Student{}db.First(&student )// 随机获取一个数据db.Take(&student )// 根据查询最后一条数据db.Last(&student )// 查询指定iddb.First(&student , 10)students := []Student{}// 查询所有的数据db.Find(&students )// 查询匹配的一条数据db.Where("sname = ?", "老大").First(&student)// 查询匹配的所有数据db.Where("sname = ?", "老大").Find(&students)//不等于db.Where("sname <> ?", "老大").Find(&students)// indb.Where("sname IN (?)", []string{"老大", "老二"}).Find(&students)//likedb.Where("sname LIKE ?", "%老大%").Find(&students)// anddb.Where("sname = ? AND gender = ?", "老大", "女").Find(&students)// 时间db.Where("updated_at > ?", lastWeek).Find(&students)// betweengrades := []Grades{}db.Where("score BETWEEN ? AND ?", 60, 90).Find(&grades)

五、联表查询

/*老师和课程一对一课程和分数一对一学生和分数一对多(分数)学生和课程多对多*/// 一对一查// 查老师教的课程func FindTeacherCourse() {courses := []Course{}db.Preload("Teacher").Find(&courses)data, _ := json.Marshal(courses)var m []map[string]interface{}json.Unmarshal(data, &m)fmt.Println(m)}// 一对多// 学生对应的分数func FindStudentGrade() {students := []Student{}db.Preload("Grades").Find(&students)data, _ := json.Marshal(students)var m []map[string]interface{}json.Unmarshal(data, &m)fmt.Println(m)}// 学生对应课程分数type StudentCourseGrade struct {Sname string `json:"sname"`Cname string `json:"cname"`Score int `json:"score"`}func FindStudentCourseGrade() {student_course_grades := []StudentCourseGrade{}// grades := []Grade{}db.Debug().Table("grade").Joins("join student on student.id = grade.student_id").Joins("join course on course.id = grade.course_id").Select("student.sname,ame,grade.score").Scan(&student_course_grades)fmt.Println(student_course_grades)}// 查大明对应的分数func FindSomeoneGrade() {student := Student{}db.Where("sname = ?", "大明").First(&student)grades := []Grade{}db.Model(&student).Association("Grades").Find(&grades)fmt.Println(grades)}// 多对多// 学生对应的课程func FindStudentCourse() {students := []Student{}db.Preload("Course").Find(&students)fmt.Println(students)}