package main
import (
"fmt"
_ "github.com/jinzhu/gorm/dialects/mysql"
"github.com/jinzhu/gorm"
"time"
)
var (
//变量db 通过init直接初始化
db *gorm.DB
err error
dbinfo string
)
const (
//定义db的连接信息
dbuser string = "root"
dbpassword = "123456"
dbip = "xxxxxxx"
dbport = "3306"
database = "users"
)
//定义user结构体
//不嵌入gorm的model
type User struct {
//gorm.Model
Id int
Name string `gorm:"type:varchar(30);not null;default:' ' "`
Password string
Birthday time.Time
Tel string `gorm:"column:phone"`
Addr string
Descs string
}
func init() {
//拼接数据库连接信息
dbinfo = fmt.Sprintf("%s:%s@(%s:%s)/%s?charset=utf8&parseTime=True&loc=Local",dbuser,dbpassword,dbip,dbport,database)
//初始化db
db,err = gorm.Open("mysql", dbinfo)
if err != nil {
fmt.Println("mysql打开失败",err)
return
}
//创建表关联user结构体
db.AutoMigrate(&User{})
}
func (u *User) TableName() string {
return "user"
}
func NewUser(name ,password ,tel ,addr,desc string,birthday time.Time) *User{
return &User{
Name: name,
Password: password,
Birthday: birthday,
Tel: tel,
Addr: addr,
Descs: desc,
}
}
func main() {
//true之后 会把去数据库中操作的sql打印出来
db.LogMode(true)
//user := NewUser("c","b","123"," "," ",time.Date(2005,6,7,1,1,1,1,time.UTC))
//检查主键是否为空 创建信息之后会把id赋值如果连续create同一个user 因为id相同会报错
//fmt.Println(db.NewRecord(user))
//db.Create(user)
//fmt.Println(db.NewRecord(user))
user1 := User{}
user2 := User{}
//查询符合的第一条数据 有则返回
db.First(&user1,"password = ?","b")
//查询符合的最后一条数据 有则返回
db.Last(&user2)
//查询符合的所有数据
users := []User{}
db.Find(&users)
fmt.Println(user1,user2)
//id 是1跟4的时候
db.Where("id in (?)",[]int{1,4}).Find(&users)
fmt.Println(users)
//name=a and password=b
db.Where("name = ? and password = ?","a","b").Find(&user1)
fmt.Println(user1)
//name=a password=b
db.Where("name = ? ","a").Where("password = ?","b").Find(&user1)
fmt.Println(user1)
//name=a phone!=4
var user3 User
db.Where("name = ? ","a").Not("phone","4").Find(&user3)
fmt.Println(user3)
var user4 []User
//name=c 或者phone=4
db.Where("name = ? ","c").Or("phone = ?","4").Find(&user4)
fmt.Println(user4)
var user5 []User
//只查询name phone 也就是select name,phone from user
db.Select([]string{"name,phone"}).Find(&user5)
fmt.Println(user5)
var user6 []User
//name 降序 id升序
//db.Order("name desc,id asc").Find(&user6)
//表示读取2条数据从第五条开始读
db.Order("name desc,id asc").Limit(2).Offset(5).Find(&user6)
fmt.Println(user6)
var count int
db.Model(&User{}).Count(&count) //查询数据条数 也可以结合where 查询指定的有多少条总数
fmt.Println(count)
var name ,password string
rows ,_ := db.Model(&User{}).Select("name,password").Rows()
for rows.Next(){
rows.Scan(&name,&password)
fmt.Println(name,password)
}
//查询name跟phone字段并且按照phone字段的重复数量分组 显示重复多少次 并且次数大于1次的
var cont,phone string
rows ,_ = db.Model(&User{}).Select("name ,phone,count(*)").Group("phone").Having("count(*) > ?",1).Rows()
for rows.Next(){
rows.Scan(&name,&phone,&cont)
fmt.Println(name,phone,cont)
}
//err判断 有时候可能我们查询的数据不存在那么需要判断
var user7 User
//这里是返回一个新的db所以不能用db.error
err := db.First(&user7,"name = ?","a").Error
if err !=nil {
fmt.Println(err)
}
user7.Name = "dashabi"
err = db.Save(&user7).Error
if err != nil {
fmt.Println(err)
}
fmt.Println(user7)
//批量更新某一个字段的值
err = db.Model(&User{}).Where("id >?",4).UpdateColumn("phone","aaaaa").Error
if err != nil {
fmt.Println(err)
}
//批量更新某几个字段的值
err = db.Model(&User{}).Where("id >?",4).UpdateColumns(map[string]interface{}{"phone":"asdasdada","name":"woshihaoren"}).Error
if err != nil {
fmt.Println(err)
}
//批量更新
err = db.Model(&User{}).Where("id >?",4).Updates(&User{Tel: "aaa123"}).Error
if err != nil {
fmt.Println(err)
}
//批量删除
err = db.Model(&User{}).Where("id >?",4).Delete(&User{}).Error
if err != nil {
fmt.Println(err)
}
//单个删除
db.Delete(&User{Id: 2})
}