gin_DB接口

// First  // 按照主键顺序的第一条记录，（主键升序）
	var user relate_tables.User
	db.First(&user)
	fmt.Println(user) // {2 林锡宏 32 揭阳市龙江镇 2}
	//2
	var user2 relate_tables.User
	db.First(&user2,"name=?","樱木")
	fmt.Println(user2) // {3 樱木 18 神奈川 3}

	// 3
	var user3 relate_tables.User
	ret := db.Where("name=?","樱木").First(&user3)
	fmt.Println(user3)
	fmt.Println(ret.RowsAffected) // 影响的行数
	fmt.Println(ret.Error) //错误信息
	fmt.Println(user3.Id) // 返回主键

// sql语句：SELECT * FROM users ORDER BY id LIMIT 1;

//FirstOrCreate
// 未找到 user，则根据给定条件创建一条新纪录
	var user5 relate_tables.User
	user4 := relate_tables.User{
		Name: "三井",
		Age: 19,
		Addr: "神奈川",
	}
	ret1 := db.FirstOrCreate(&user5,user4)
	fmt.Println(user5)
	fmt.Println(ret1.RowsAffected)

// Last 获取最后一条记录（主键降序）
	var user6 relate_tables.User
	db.Last(&user6)
	fmt.Println(user6)

var user model.User

// 所有记录
db.Find(&users, []int{1,2,3})
// sql语句：// SELECT * FROM users WHERE id IN (1,2,3);

result := db.Find(&users)
// sql语句：SELECT * FROM users;

// 根据指定条件查询
db.Find(&user, "name = ?", "hallen")

//或者结合where
db.Where("name = ?", "hallen").Find(&users)
// sql语句：SELECT * FROM users WHERE name = 'hallen';

db.Where("name LIKE ?", "%ha%").Find(&users)
// sql语句：SELECT * FROM users WHERE name LIKE '%hal%';

// where
var user model.User


// 根据条件查询得到满足条件的第一条记录
db.Where("role_id = ?", "2").First(&user)
fmt.Println(user)

var users []model.User

// 根据条件查询得到满足条件的所有记录
db.Where("user_id = ?", "1").Find(&users)
fmt.Println(users)

// like模糊查询
db.Where("role_id like ?", "%2").Find(&users)
fmt.Println(users)

db.Where("updated_at > ?", "2019-02-08 18:08:27").Find(&users)
fmt.Println(users)

// struct结构查询条件
db.Where(&DqmUserRole{RoleId: "1,2", UserId: "1"}).First(&user)
fmt.Println(user)


条件：
=
LIKE
IN：Where("name IN ?", []string{"hallen", "hallen2"})
AND：Where("name = ? AND age >= ?", "jinzhu", "22")
Time：Where("updated_at > ?", lastWeek)
BETWEEN：Where("created_at BETWEEN ? AND ?", lastWeek, today)

//Select
//指定要从数据库检索的字段，默认情况下，将选择所有字段;

db.Select("name, age").Find(&users)
 SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
 SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()    // COALESCE:聚合
 SELECT COALESCE(age,'42') FROM users;

Create

//1.插入单条

user := models.User{
Name:"李四",Age:18,Addr:"xxx",Pic:"/static/upload/pic111.jpg",Phone:"13411232312"}
result := db.Create(&user)

user.ID             // 返回插入数据的主键
result.Error        // 返回 error
result.RowsAffected // 返回插入记录的条数

save

var user model.User

db.First(&user)

user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)

update

var users []model.User
db.Where("active = ?", true).find(&users).Update("name", "hello")


db.Where("active = ?", true).find(&users).Updates(User{Name: "hello", Age: 18})


// update也可以使用map：map[string]interface{}{"name": "hello", "age": 18}

// 也可以使用save更新

delete

db.Delete(&user,1)

// 批量删除
db.Where("age = ?", 20).Delete(&User{})

Not

var user model.User

db.Not(User{Name: "hallen", Age: 18}).First(&user)

// SELECT * FROM `users`  WHERE (`users`.`name` <> 'hallen6') AND (`users`.`age` <> 19);

Or

var users []model.User


db.Where("name = 'hallen'").Or(User{Name: "hallen2", Age: 18}).Find(&users)
// SELECT * FROM users WHERE name = 'hallen' OR (name = 'jinzhu 2' AND age = 18);

Order

var users []model.User
db.Order("age desc").Find(&users) // 注意这里的order要在find前面，否则不生效
fmt.Println(users)

// SELECT * FROM users ORDER BY age desc;

默认为asc

Limit和Offset

//Limit 指定获取记录的最大数量 Offset 指定在开始返回记录之前要跳过的记录数量

var users []model.User


db.Limit(3).Find(&users)  // 三条
// SELECT * FROM users LIMIT 3;

db.Limit(10).Offset(5).Find(&users) // 从5开始的10条数据
// SELECT * FROM users OFFSET 5 LIMIT 10;

Scan

//将结果扫描到另一个结构中。

type Result struct {
        Id int64
    }
var results []Result
db.Select("id").Where("user_id in (?)", []string{"1", "2"}).Find(&dqmUserRole20).Scan(&results)
fmt.Println(results)

Count

//获取模型的记录数

db.Where("name = ?", "hallen").Find(&users).Count(&count)
// SELECT count(*) FROM users WHERE name = 'jinzhu'

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
// SELECT count(*) FROM deleted_users;