026. rails 和 database 中的查询与修改操作 - cwy007/tips-and-skills GitHub Wiki

数据库知识点

1. How about get, delete, insert, and update data?

--GET
SELECT * FROM People;
SELECT firstname, lastname, age FROM People;

--DELETE
DELETE FROM People;

--INSERT
INSERT INTO People VALUES ('Leandro', 'Tk', 'My Address 123', 'São Paulo', 23);
INSERT INTO People (firstname, lastname, age) VALUES ('Leandro', 'Tk', 23);

--UPDATE
UPDATE People SET firstname='Gennady', lastname='korotkevich';

2. Using conditions in our queries

DELETE From People WHERE lastname="Kinoshita";

UPDATE People SET firstname="Gennady" WHERE firstname="Leandro" AND lastname="Kinoshita";

SELECT * FROM People ORDER BY age;

3.rails 中和数据库中 relationship 关系的建立方式

  • 数据库表格间的关系通过外键foreign_key指定： user_id
  • 在 rails 中，model间的关系通过has_many , belongs_to, has_many ... through 指定
  • rails 中 model 类 对应 database 中 的table
  • objects 对应 rows
  • attributes 对应 fields

4. rails 中的查询

1. all 方法 从特定的model中获取所有的 对象objects

Post.all
# 对应的sql 
SELECT * FROM posts

2. find 通过 id 获取对象 object

Post.find(1)
=> #<Post id: 1, title: "Database & Rails", text: "Lorem Ipsum...", author_id: 1>
# sql 
SELECT * FROM posts WHERE id = 1

3. where 获取通过条件的对象 objects 集合

Post.where(title: "Database & Rails")
=> [#<Post id: 1, title: "Database & Rails", text: "Lorem Ipsum...", author_id: 1>]
Post.where(title: "Database & Rails").first
=> #<Post id: 1, title: "Database & Rails", text: "Lorem Ipsum...", author_id: 1>

# sql
SELECT * FROM posts WHERE title = 'Database & Rails

2. order 基于某一列排序对象

Post.all
=> [#<Post id: 1, title: "Database & Rails", text: "Lorem Ipsum...", created_at: "2015-10-13 20:00:00", author_id: 1>, #<Post id: 2, title: "Ruby on Rails: HTTP, MVC and Routes", text: "Lorem Ipsum2...", created_at: "2015-10-13 21:00:00", author_id: 1>]

Post.order("created_at DESC")
=> [#<Post id: 2, title: "Ruby on Rails: HTTP, MVC and Routes", text: "Lorem Ipsum2...", created_at: "2015-10-13 20:00:00", author_id: 1>, #<Post id: 1, title: "Database & Rails", text: "Lorem Ipsum...", created_at: "2015-10-13 21:00:00", author_id: 1>]
view raw

# sql
SELECT * FROM posts ORDER BY created_at DESC

link: https://medium.freecodecamp.org/understanding-the-basics-of-ruby-on-rails-sql-databases-and-how-they-work-7a628cd42073