rails中mysql应用

地图汇内部培训，rails中的mysql、sql命令等用法。

Transcript

1. 1 云GIS与物联网分论坛 Mysql入门 地图汇 2014.7.10
2. 2 CURD update read delete
3. 3 CURD create database table record Index …
4. 4 database/table • mysql> create database dituhui; • create table <表名> (<字段名1> <类型1>, (< 字段名2> <类型2>); mysql> create table users(user_id int(4) not null primary key auto_increment,map_id char(20) not null);
5. 5 record • INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....) mysql> insert into users (user_id,map_id) values (10000,10000);
6. 6 index create index alter table
7. 7 alter table • ALTER TABLE用来创建普通索引、UNIQUE索引或 PRIMARY KEY索引 • ALTER TABLE table_name ADD INDEX index_name (column_list) mysql>alter table users add index user_id_index(user_id);
8. 8 alter table • ALTER TABLE table_name ADD UNIQUE (column_list) • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
9. 9 create index • CREATE INDEX index_name ON table_name (column_list) • CREATE UNIQUE INDEX index_name ON table_name (column_list)
10. 10 CURD create read delete
11. 11 CURD update table record …
12. 12 table alter table 表名 rename to 新表名 mysql>alter table users rename to user;
13. 13 table • ALTER TABLE `dituhui`.`mapbean_kinds` MODIFY COLUMN `description` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL; • ALTER TABLE users RENAME column map_id to maps_id ;
14. 14 record • UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 mysql>update user set user_id=10001 where map_id=10000;
15. 15 CURD create update delete
16. 16 CURD read database table record Index …
17. 17 databae • show databases;
18. 18 table • show tables; • desc [表名];
19. 19 record(基础查询) • select * from users; • select user_id from users where map_id=10000; • select user_id from users where map_id >10000 order by id desc limit 10; • select count(*) from users where map_id>10000;
20. 20 index • show index from table_name; • show keys from table_name
21. 21 CURD create update read
22. 22 CURD delete database table record Index …
23. 23 database • drop database [database_name];
24. 24 table • drop table [table_name];
25. 25 record • delete 表名称 WHERE 列名称 = 值 mysql> delete user where id=1000;
26. 26 index • DROP INDEX index_name ON talbe_name • ALTER TABLE table_name DROP INDEX index_name • ALTER TABLE table_name DROP PRIMARY KEY(不需要指定索引名称，只用一个主键)
27. 27 其他常用命令-distinct • distinct：去除重复 mysql>select distinct tag from table_name; tag view_count b 5000 c 1005 b 2500 a 1002 tag b c a
28. 28 like和通配符 mysql>select * from users where email like “xubingchao@gmail.com” mysql>select * from users where email like “%@gmail.com” mysql>select * from users where email like “_ubingchao@gmail.com”
29. 29 in • select * from users where id in (10000,10001,10002)
30. 30 between • mysql> select * from users where id between 10000 and 10002;
31. 31 primary key/foreign key
32. 32 join • INNER JOIN : 如果表中有至少一个匹配， 则返回行 • LEFT JOIN: 即使右表中没有匹配，也从左 表返回所有的行 • RIGHT JOIN: 即使左表中没有匹配，也从 右表返回所有的行 • FULL JOIN: 只要其中一个表中存在匹配， 就返回行
33. 33 inner join mysql>SELECT nickname,title FROM dituhui.users inner join dituhui.maps where dituhui.users.id=dituhui.maps.user_id;
34. 34 left join mysql>select nickname,title from dituhui.users left join dituhui.maps on dituhui.users.id=dituhui.maps.user_id;
35. 35 inner、left的区别 • INNER JOIN: 如果表中有至少一个匹配， 则返回行。（不匹配的不显示） • LEFT JOIN: 即使右表中没有匹配，也从左 表返回所有的。（以左表作为基准，右表能匹配上 的就显示出来，不匹配的就显示成NULL,记录个数满足左 表）
36. 36 alter • 1：删除列 ALTER TABLE 【表名字】 DROP 【列名称】 • 2：增加列 ALTER TABLE 【表名字】 ADD 【列名称】 【类型等】 • 3：修改列 ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】【类型等】 • 5：重命名表 ALTER TABLE 【表名字】 RENAME 【表新名字】 • 6：删除表中主键 ALTER TABLE 【表名字】 drop primary key; • 8：添加索引 ALTER TABLE 【表名字】ADD INDEX 【索引名】; • 10: 删除索引 ALTER table 【表名字】 DROP INDEX 【索引名】;
37. 37 rails中的sql where select group order reorder reverse_order limit offset joins includes lock readonly from having find …
38. 38 find/find_by_* • user=User.find(1000) mysql> SELECT * FROM usersWHERE (users.id = 10000) LIMIT 1 • user=User.find_by_nickname(“scottxu”) mysql> SELECT * FROM usersWHERE (users.nickname = ‘scottxu’) LIMIT 1
39. 39 find/find_by_* • User.find(:id=>[10000,10001,10003]) mysql>SELECT * FROM users WHERE (users.id IN (10000,10001,10003))
40. 40 first/last • user=User.first mysql> select * from users limit 1; • user=User.last mysql>select * from users order by id desc limit 1;
41. 41 where • User.where(:created_at => (params[:start_date].to_date)..(params[:end_da te].to_date)) mysql> SELECT “users".* FROM “users" WHERE (“users"."created_at" BETWEEN '2014-06-29' AND '2014-07-10')
42. 42 order • User.order(“created_at”) mysql>select * from users order by users.created_at asc; • User.order(“created_at desc”) mysql>select * from users order by users.created_at desc; • User.order(“created_at”). reverse_order
43. 43 select • User.select(“id,nickname,email”).where(“id<1 0005”) mysql> select users.id,users.nickname,users.email where users.id<10005;
44. 44 limit • User.limit(10) mysql>select * from users limit 10;
45. 45 group • Map.group(:user_id) mysql>select * from maps group by maps.user_id;
46. 46 joins • Map.joins(:permission).where(“maps.rating>0 and permissions.name=‘pub’ ”) mysql>select * from maps inner join permissions where maps.rating>0 and permissions.name=‘pub’ and maps.permission_id=permissions.id
47. 47 joins nested • Category.joins(:posts => [{:comments => :guest}, :tags]) SELECT categories.* FROM categories INNER JOIN posts ON posts.category_id = categories.id INNER JOIN comments ON comments.post_id = posts.id INNER JOIN guests ON guests.comment_id = comments.id INNER JOIN tags ON tags.post_id = posts.id
48. 48 includes • includes是干啥用地？
49. 49 N+1？ map = Map.includes(:app).find(params[:id]) if !map.app_name.include?('FOOTPRINT') render :json=>{:status=>200} end
50. 50 includes nest
51. 51 pluck • Map.where(“id<10005”).pluck • =>array
52. 52 function • User.count • Map.sum(“like”) • Map.minimum(“like") • Map.maximum(“like")
53. 53 rails migration create_table add_column add_index add_timestamps change_column change_table … drop_table remove_column remove_index rename_column remove_timestamps
54. 54 create_table • rails generate model Map title:string description:text => class CreateMaps < ActiveRecord::Migration def change create_table :mapsdo |t| t.string :title t.text :description t.timestamps end end end
55. 55 type :binary :boolean :date :datetime :decimal :float :integer :primary_key :string :text :time :timestamp
56. 56 add_column • rails generate migration add_description_to_geodata description:text => class AddDescriptionToGeodata < ActiveRecord::Migration def change add_column :geodata, :description, :text end end
57. 57 add_index • rails g migraion add_index_features
58. 58 change_column • rails g migration change_column_to_organizations