First, FirstOrCreate, Last

First 主键顺序的第一条记录（主键升序）

	//1  
	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) // 返回主键

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)

案例

//用户类
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

// 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

//Select
//指定要从数据库检索的字段，默认情况下，将选择所有字段;
 
db.Select("name, age").Find(&users)
 SELECT name, age FROM users;
 
db.Select([]string{"name", "age"}).Find(&users)
 SELECT name, age FROM users;

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

Create

//1.插入单条

user := models.User{
Name:"李四",
Age:18,
Addr:"xxx",
}
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)

Updata

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;