一. 初始化连接
1 package model
2
3 import (
4 "fmt"
5
6 "gorm.io/driver/mysql"
7 "gorm.io/gorm"
8 )
9
10 /***
11 Navicat 数据库可视化工具: https://www.navicat.com/en/
12 GORM 操作数据库的Golang库: https://gorm.io/zh_CN/docs/index.html
13 ***/
14
15 var (
16 DB *gorm.DB
17 )
18
19 func init() {
20 var err error
21 // dsn := "user:pass@tcp(192.168.0.106:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
22 dsn := "root:123456@tcp(192.168.0.106:3306)/gin?charset=utf8mb4&parseTime=True&loc=Local"
23
24 DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
25 SkipDefaultTransaction: false, // true为禁用事务,官方说可以提升30%的性能
26 QueryFields: true, // 显示执行的SQL
27
28 })
29 if err != nil {
30 fmt.Println("Mysql 数据库连接失败!")
31 } else {
32 fmt.Println("Mysql 数据库连接成功!")
33 }
34 }
二. 单表增删改查
1 package model
2
3 import "fmt"
4
5 /***
6 注意事项:
7 1. 结构体的名称必须首字母大写,并和数据库表名称对应.
8 例如: 表名user,结构体名User
9 2. 结构体中字段名称首字母必须大写,并和数据库中字段一一对应.
10 例如: 结构体 数据库
11 Id id
12 Username username
13 AddTime add_time 驼峰命名在数据库中会以下划线分隔
14 3. 默认情况表名是结构体名称的复数形式. 修改表明,定义TableName函数返回表明就可以
15 例如: 结构体名称User 默认对应表明users
16
17 ***/
18
19 type User struct { // 默认表名是users, 可以通过TableName函数返回来设定
20 Id int
21 Username string
22 Age int
23 Email string
24 AddTime int
25 }
26
27 // 事务
28 func UserTransaction() {
29 // 开启事务
30 tx := DB.Begin()
31
32 // 捕获异常
33 defer func() {
34 if r := recover(); r != nil {
35 // 遇到错误回滚
36 tx.Rollback()
37 }
38 }()
39
40 // 在事务中执行一些业务
41 user := User{
42 Username: "chad",
43 Age: 29,
44 Email: "[email protected]",
45 AddTime: int(GetUnix()),
46 }
47 u1 := User{}
48
49 DB.Create(&user).Find(&u1)
50 fmt.Println("新增成功", u1)
51
52 // 提交事务
53 tx.Commit()
54 }
55
56 // 默认表名是users, 可以通过TableName函数返回来设定
57 func (User) TableName() string {
58 return "user"
59 }
60
61 // 执行原生SQL 这样就可以进行group统计了
62 func UserExecSql() {
63 userlist := []User{}
64 // Raw是执行查询数据
65 DB.Raw("select username, age, email from user where id > ? and username like '%ll%' order by username desc ", 0).Scan(&userlist)
66 fmt.Println("ExecSql:", userlist)
67
68 // Exec是执行更新数据
69 result := DB.Exec("delete from user where id=8;")
70 fmt.Println("Exec:", result.RowsAffected)
71
72 // 获取统计行数
73 var count int64
74 DB.Raw("select * from user;").Count(&count)
75 fmt.Println("Count:", count)
76 }
77
78 // Order排序,Limit, Offset
79 func UserOtherOpt() {
80 userlist := []User{}
81
82 // 排序
83 DB.Order("id desc").Order("username asc").Find(&userlist)
84 fmt.Println("Order:", userlist)
85
86 // 分页: 每页显示三条记录Limit(3),显示第二页Offset(2)
87 DB.Order("id desc").Order("username asc").Offset(2).Limit(3).Find(&userlist)
88 fmt.Println("Offset:", userlist)
89
90 // 计数
91 var count int64
92 DB.Order("id desc").Order("username asc").Find(&userlist).Count(&count)
93 fmt.Println("Count:", count)
94
95 }
96
97 // 删除
98 func UserDelete() {
99
100 // Delete from user where id=1;
101 u := &User{Id: 1}
102 DB.Delete(u)
103 fmt.Println("1:", u) // 1: &{1 0 0}
104 // Delete from user where id=2;
105 u = &User{}
106 DB.Where("id = ?", 2).Delete(u)
107 fmt.Println("2:", u) // 2: &{0 0 0}
108
109 // Delete from user where id=3;
110 ul := []User{}
111 DB.Delete(&User{}, 3).Find(&ul) // 按主键删除的
112 fmt.Println("3:", ul) // 3: []
113 // Delete from user where id=4;
114 ul = []User{}
115 DB.Delete(&User{}, "4").Find(&ul) // 按主键删除的
116 fmt.Println("4:", ul) // 4: []
117 // Delete from user where id in (5,6,7);
118 ul = []User{}
119 DB.Delete(&User{}, []int{5, 6, 7}).Find(&ul) // 按主键删除的
120 fmt.Println("5,6,7:", ul) // 5,6,7: []
121
122 }
123
124 // 修改
125 func UserEdit() {
126 user := User{Id: 1}
127
128 // 单条数据更新方式一
129 DB.Find(&user)
130 fmt.Println(user)
131 user.Age = 20
132 DB.Save(user)
133
134 // 单字段更新方式
135 DB.Find(&User{Id: 2}).Update("Age", 18)
136
137 // 单字段更新方式三
138 user1 := User{}
139 DB.Model(&user1).Where("age = ?", 19).Update("Age", 10)
140 fmt.Println(user1)
141
142 // 更新多列
143 /***
144 根据struct更新属性,只会更新非零的字段值
145 update user set username='hello', age=18 where id=3
146 ***/
147 DB.Model(&User{Id: 3}).Updates(User{Username: "hello", Age: 18})
148
149 /***
150 根据struct更新属性,只会更新非零的字段值
151 update user set username='hello', age=18 where id=3
152 ***/
153 DB.Model(&User{Id: 4}).Updates(map[string]interface{}{"username": "hello1", "age": 25})
154
155 }
156
157 // 新增
158 func UserInsert() {
159 user := User{
160 Username: "chad",
161 Age: 29,
162 Email: "[email protected]",
163 AddTime: int(GetUnix()),
164 }
165 u1 := User{}
166
167 DB.Create(&user).Find(&u1)
168 fmt.Println("新增成功", u1)
169 }
170
171 // 查询
172 func UserQuery() {
173 userList := []User{}
174 user := User{}
175
176 /***
177 查询所有
178 select * from user;
179 ***/
180 DB.Find(&userList)
181 fmt.Println("查询所有:", userList)
182
183 /***
184 获取第一条匹配的记录
185 select * from user name='zhangsan' order by id limit 1;
186 ***/
187 DB.Where("username = ?", "zhangsan").First(&user)
188 fmt.Println("获取第一条匹配的记录:", user)
189
190 /***
191 获取匹配的所有记录
192 select * from user name='zhangsan' order by id ;
193 ***/
194 DB.Where("username = ?", "zhangsan").Find(&userList)
195 fmt.Println("获取匹配的所有记录:", userList)
196
197 /***
198 in
199 select * from user name in ('zhangsan', 'lisi') order by id ;
200 ***/
201 DB.Where("username in ?", []string{"zhangsan", "lisi"}).Find(&userList)
202 fmt.Println("in:", userList)
203
204 /***
205 in
206 select * from user name in ('zhangsan', 'lisi') order by id ;
207 ***/
208 DB.Where("username in ?", []string{"zhangsan", "lisi"}).Find(&userList)
209 fmt.Println("in:", userList)
210
211 /***
212 like
213 select * from user name like '%ang%' order by id ;
214 ***/
215 DB.Where("username like ?", "%ang%").Find(&userList)
216 fmt.Println("like:", userList)
217
218 /***
219 and
220 select * from user name ='zhangsan' and age=18 order by id ;
221 ***/
222 DB.Where("username = ? and age >= ?", "zhangsan", 18).Find(&userList)
223 fmt.Println("and:", userList)
224
225 }
三. 多表关联查询
1 package model
2
3 import (
4 "fmt"
5
6 "gorm.io/gorm"
7 )
8
9 type Student struct { // 默认表名是users, 可以通过TableName函数返回来设定
10 Id int
11 Username string
12 Age int
13 Email string
14 AddTime int
15 /***
16 One2many关联表Artical, 通过反射标签来设定Artical表中与之关联的Many2one字段. 这样就构成了一对主从表.
17 foreignKey 设置的外健, references设置的主键.主键默认Id
18 ***/
19 Artical []Artical `gorm:"foreignKey:AuthorId;references:Id"`
20
21 /***
22 Many2many 关联表查询
23 ***/
24 Lesson []Lesson `gorm:"many2many:lesson_student"`
25 }
26
27 type Artical struct {
28 Title string
29 Content string
30
31 // Many2one关联表Student, 默认外健 AuthorID 或者可以通过反射标签定义外健名
32 Author Student `gorm:"foreignKey:AuthorId"`
33 AuthorId int
34 }
35
36 type Lesson struct {
37 Id int
38 Name string
39 Student []Student `gorm:"many2many:lesson_student"`
40 }
41
42 type LessonStudent struct {
43 LessonID int
44 StudentID int
45 }
46
47 // 关联查询
48 func ArticalSelect() {
49 // Many2one 查询
50 fmt.Println("Many2one 查询==========================")
51 articalList := []Artical{}
52 DB.Preload("Author").Find(&articalList)
53 fmt.Println("articalList:", articalList)
54
55 // One2many 查询
56 fmt.Println("\n\nOne2many 查询==========================")
57 userlist := []Student{}
58 DB.Preload("Artical").Find(&userlist)
59 fmt.Println("userlist:", userlist)
60
61 // Many2many 查询
62 fmt.Println("\n\nMany2many 查询==========================")
63 lessonlist := []Lesson{}
64 DB.Preload("Student").Offset(1).Limit(2).Order("id desc").Find(&lessonlist)
65 fmt.Println("lessonlist:", lessonlist)
66 // Many2many 预加载查询方式一
67 DB.Preload("Student", "username = ?", "zhangsan").Find(&lessonlist)
68 fmt.Println("lessonlist:", lessonlist)
69 // Many2many 预加载查询方式二
70 DB.Where("name like ?", "%inux%").Preload("Student", "username = ?", "zhangsan").Find(&lessonlist)
71 fmt.Println("lessonlist:", lessonlist)
72 // Many2many 预加载查询方式三 这样就可以根据关联字段值进行排序了
73 DB.Preload("Student", func(db *gorm.DB) *gorm.DB {
74 return db.Order("user.id asc")
75 }).Find(&lessonlist)
76 fmt.Println("lessonlist:", lessonlist)
77 // 多个Preload就是多表关联DB.Preload("Student").Preload("Class")
78 DB.Preload("Student", func(db *gorm.DB) *gorm.DB {
79 return db.Where("id > ?", 20).Preload("Lesson").Order("user.id asc")
80 }).Find(&lessonlist)
81 fmt.Println("lessonlist:", lessonlist)
82
83 }
84
85 func (Artical) TableName() string {
86 return "artical"
87 }
88
89 func (Student) TableName() string {
90 return "user"
91 }
92
93 func (Lesson) TableName() string {
94 return "lesson"
95 }
96
97 func (LessonStudent) TableName() string {
98 return "lesson_student"
99 }
标签：数据库,DB,GORM,Golang,id,user,Println,fmt,User From： https://www.cnblogs.com/watermeloncode/p/17859003.html