mysql
下载
注意记住密码
https://juejin.cn/book/6844733802426662926/section/6844733802607017991
增加path到系统变量
为的是不必要进入到mysql\bin
,在cmd
弹出的窗口就可以操作mysql
的命令行
// bin路径
C:\Program Files\MySQL\MySQL Server 8.0\bin
前后对比
进入mysql
连接navicat
用管理员身份打开cmd,输入"mysql -u root -p [-h locahost -P 3306]"(中括号内是默认内容)回车输入root用户密码进入MySQL客户端,然后
newpassword
是自己的密码
mysql> alter user root@localhost identified by 'newpassword' password expire never;
mysql> alter user root@localhost identified with mysql_native_password by 'newpassword';
mysql> flush privileges; # 刷新权限
beego连接mysql
新建一个名字为beego_project
的数据库
// conf/app.conf
include "mysql.conf"
// conf/mysql.conf
username = root
password = 123456
host = 127.0.0.1
port = 3306
database = beego_project
在main中初始化
// main.go
package main
import (
_ "beego_project/routers"
_ "github.com/go-sql-driver/mysql"
"github.com/astaxie/beego"
"github.com/astaxie/beego/orm"
"fmt"
)
func init() {
username := beego.AppConfig.String("username")
password := beego.AppConfig.String("password")
host := beego.AppConfig.String("host")
port := beego.AppConfig.String("port")
database := beego.AppConfig.String("database")
datasource := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&loc=Local",username,password,host,port,database)
orm.RegisterDriver("mysql",orm.DRMySQL)
orm.RegisterDataBase("default","mysql",datasource)
fmt.Println("连接数据")
}
func main() {
beego.Run()
}
自动建表
建表前
modle
// models/chapter07/model.go
package chapter07
import (
"github.com/astaxie/beego/orm"
)
type Article struct {
Id int
Name string
Author string
}
func init(){
orm.RegisterModel(new(Article))
}
// main.go
package main
import (
_ "beego_project/routers"
_ "github.com/go-sql-driver/mysql"
_ "beego_project/models/chapter07" //引入model
"github.com/astaxie/beego"
"github.com/astaxie/beego/orm"
"fmt"
)
func init() {
username := beego.AppConfig.String("username")
password := beego.AppConfig.String("password")
host := beego.AppConfig.String("host")
port := beego.AppConfig.String("port")
database := beego.AppConfig.String("database")
datasource := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&loc=Local",username,password,host,port,database)
orm.RegisterDriver("mysql",orm.DRMySQL)
orm.RegisterDataBase("default","mysql",datasource)
fmt.Println("连接数据")
// 自动建表
name := "default"
force := false
verbose := true
err := orm.RunSyncdb(name,force,verbose)
if err != nil {
panic(err)
}
}
func main() {
orm.RunCommand() //// 自动建表
beego.Run()
}
建表后
模型定义
代码定义
type User struct {
Id int `orm:"pk;auto"` // 主键id,自动增长
Name string `orm:"index;unique"` //索引,唯一
Age int
Author string `orm:"null";column(address)` //默认为null,列名(字段名)
Desc string `orm:"size(2000)"` //strign的长度
Price float64 `orm:"digits(12);decimals(4)"` //浮点型,12位,8位整数,4位小数
CreateTime time.Time `orm:"auto_now_add;type(datetime)"` //时间类型,自动创建时默认值为当前时间
Status int `orm:"default(1);description(状态,1启用,0停用)"` // 默认值,注释
XXX string `orm:"-"` //忽略,不创建些字段
}
func (u *User)TableName() string { //自定义表名
return "sys_user"
}
建成的表
参数详解
1.忽略字段structtag:`orm:"-"`
2.pk设置为主键,适用于自定义其他类型为主键
3.null:Name string `orm:“null”`
4.auto:自增长
Id int orm:"pk;auto"
5.index:索引
Name string orm:"index"
索引的作用:优化查询,相当于图书的目录
6.unique
Name string orm:"unique"
7.column
Name string orm:"column(user_name)"
8.size:对string起作用
Title string orm:"size(60)"
9.digits / decimals 4 2 12.21
Money float64 orm:"digits(12);decimals(4)" 总长度12,小数点后有4位小数
10.auto_now / auto_now_add
Created time.Time orm:"auto_now_add;type(datetime)"
Updated time.Time orm:"auto_now;type(datetime)"
auto_now 每次 model 保存时都会对时间自动更新
auto_now_add 第一次保存时才设置时间
11.type:日期还是时间
Created time.Time orm:"auto_now_add;type(date)"
Created time.Time orm:"auto_now_add;type(datetime)"
12.default
Gender float64 orm:"default(0)"
13.description
修改源码:cmd_utils.go的 getColumnAddQuery()最后加:fi.description,
Name string orm:"description(这是标签名称)"
表关系
rel
正向关系reverse
反向关系
一对一
- User模型中: Profile *Profile
orm:“rel(one)”
正向关系,有外键字段 - Profile模型中: User *User
orm:“reverse(one)”
反向关系,只是关系,没有外键
package chapter07
import (
"github.com/astaxie/beego/orm"
"time"
)
type User struct {
Id int `orm:"pk;auto"`
Name string `orm:"index;unique"`
Age int
Author string `orm:"null";column(address)`
Desc string `orm:"size(2000)"`
Price float64 `orm:"digits(12);decimals(4)"`
CreateTime time.Time `orm:"auto_now_add;type(datetime)"`
Status int `orm:"default(1);description(状态,1启用,0停用)"`
XXX string `orm:"-"`
Profile *Profile `orm:"rel(one)"`
}
type Profile struct{
Id int
IdCard string
Cover string
User *User `orm:"reverse(one)"`
}
func (u *User)TableName() string { //自定义表名
return "sys_user"
}
func (p *Profile)TableName() string {
return "sys_profile"
}
func init(){
orm.RegisterModel(new(User),new(Profile))
}
一对多
- article模型中:User *User
orm:“rel(fk)”
正向关系,有外键,外键在多的一方 - user模型中:Articles []*Articles `orm:“reverse(many)” 反向关系,没有外键
package chapter07
import (
"github.com/astaxie/beego/orm"
"time"
)
type User struct {
Id int `orm:"pk;auto"`
Name string `orm:"index;unique"`
Age int
Author string `orm:"null";column(address)`
Desc string `orm:"size(2000)"`
Price float64 `orm:"digits(12);decimals(4)"`
CreateTime time.Time `orm:"auto_now_add;type(datetime)"`
Status int `orm:"default(1);description(状态,1启用,0停用)"`
XXX string `orm:"-"`
Profile *Profile `orm:"rel(one)"`
Articles []*Article `orm:"reverse(many)"`
}
type Article struct{
Id int
Title string
Content string `orm:"size(2000)"`
User *User `orm:"rel(fk)"`
}
func (u *User)TableName() string { //自定义表名
return "sys_user"
}
func (a *Article)TableName() string {
return "sys_article"
}
func init(){
orm.RegisterModel(new(User),new(Article))
}
多对多
- post模型中:Tags []*Tag
orm:“rel(m2m)”
正向关系,没有外键 - tag模型中:Posts []*Post
orm:“reverse(many)”
反向关系,没有外键 - 会额外生成一个表,组成多对多关系
package chapter07
import (
"github.com/astaxie/beego/orm"
"time"
)
type Post struct {
Id int
Title string
Tags []*Tag `orm:"rel(m2m)"`
}
type Tag struct{
Id int
Name string
Posts []*Post `orm:"reverse(many)"`
}
func (a *Post)TableName() string {
return "sys_post"
}
func (a *Tag)TableName() string {
return "sys_tag"
}
func init(){
orm.RegisterModel(new(Post),new(Tag))
}
exper表达式
controller
package chapter07
import (
"github.com/astaxie/beego"
"github.com/astaxie/beego/orm"
"beego_project/models/chapter07"
"fmt"
)
type ExperController struct {
beego.Controller
}
func (c *ExperController) Get() {
o := orm.NewOrm()
qs := o.QueryTable(new(chapter07.User))
user := chapter07.User{}
// exact
qs.Filter("name","hallen").One(&user)
// qs.Filter("name__contains","lie").One(&user)
// qs.Filter("age__gt",19).One(&user)
// qs.filter("age__lt",19).One(&user)
// qs.Filter("name__startswith","ha").One(&user)
// qs.Filter("name__endswith","en").One(&user)
// qs.Filter("age__in",15,16,17).One(&user)
// qs.Filter("address__isnull",true).One(&user)
//
fmt.Println(user)
c.Data["user"] = user
c.TplName = "chapter07/test_exper.html"
}
查看orm执行的sql语句
-
只作用于当前的模型:当前模型的init中orm.Debug = true
-
作用于所有模型:beego.Run()前面设置orm.Debug = true
QueryTable
users := []chapter07.User{}
qs := o.QueryTable(new(chapter07.User))
所有数据
qs.All(&users)
qs.Filter("age",15).All(&users)
Limit,Offset
跳过1条,查询5条
qs.Limit(5).Offset(1).All(&users)
Count
总数
count,_ := qs.Filter("age",15).Count()
count,_ := qs.Count()
更新编辑
qs.Filter("age",15).Update(orm.Params{
"desc":"rrrr",
})
删除
qs.Filter("age",11).Delete()
插入
普通插入
post := chapter07.Post{}
post.Title = "aa" //注意要大写
_,err := o.Insert(&post)
fmt.Println(err)
User与Profile关联
user2 := chapter07.User{}
user2.Name = "zhiliao8"
profile := chapter07.Profile{}
user2.Profile = &profile
_,err := o.Insert(&user2)