over 2 years ago

RDBMS

mysqld 是server程式
mysql 是client端

看Mysql灌在哪裡:
ps aux

進入mysql:
mysql -u root -p
-u:是user的意思
-p:是password的意思

看有多少資料庫:
show databases;
(要有;,才是一個完整的句子)

進入特定的資料庫:
use 某一個資料庫的名字;
會出現:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

就可以開始連線(?)

看特定table:
show tables;

看users的table內容:
select * from users

在RDBMS應該要打成SHOW TABLES;,都要大寫。
不過table name要按照原本的大小寫。SELECT * FROM users;
SQL就完全不分大小寫。

如何玩RAILS接到RDBMS去

00:05:00
做一個亂數產生器

100.times.do |i|
   user = User.create(:email => "#{i}@gamil.com", :password => i.to_s * 8)
   Order.create(:user_id, :status => rand(10), :total => rand(100), :update_at => Time.now + (rand(363*2*60*60*24)))
end
200.times.do |i|
   Order.create(:user_id, :status => rand(10), :total => rand(100)+1, :update_at => Time.now + (rand(363*2*60*60*24)))
end

user會有100個
order會有100 +200筆

抽出第一筆交易
SELECT * FROM orders LIMIT user 1;

10:40
join的動作
要先選一個起點,先從少的開始
101 user <=> 300 orders

SELECT * FROM users
先用*表示所有的東西
FROM後面接的是起點

建議起點後面接WHERE=>
SELECT * FROM users WHERE users.id = 1;

然後在選擇要的資料來取代 =>
SELECT id, email FROM users WHERE users.id =1 ;

JOIN有三種:
INNER JOIN:兩邊同時存在才列出來
LEFT JOIN :左邊有,右邊就會列出來,右邊如果沒有會出現NULL
RIGHT JOIN:右邊有,左邊就會列出來

INNER JOIN
SELECT users.id, users.email, orders.status FROM users INNER JOIN orders ON users.id = orders.user_id WHERE users.id =1;
ON後面是接連接詞:把兩個table接在一起的方法
如果有加連接詞的話,前面要指定table,本來的id要變成users.idemail要變成users.email,也可以把第二個表給拉出來 orders.status

LEFT JOIN
SELECT users.id, users.email, orders.status FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.id =1;

RIGHT JOIN
SELECT users.id, users.email, orders.status FROM users RIGHT JOIN orders ON users.id = orders.user_id WHERE users.id =1;

18:00
GROUP BY 加總:按什麼為key來做壓縮
SELECT users.id, users.email, orders.status FROM users INNER JOIN orders ON users.id = orders.user_id WHERE users.id IN (1,2) GROUP BY users.id;

22:00
rename是用AS,通常都放在table跟column的後面

25:00
接在最後面再過濾一次的HAVING語法
SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3;

26:00
LIMIT : 只想顯示兩筆
OFFSET :前面略過一筆

SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3 LIMIT 2;

SELECT u.id AS u_id, u.email AS email, COUNT(o.id) AS count, o.id FROM users AS u RIGHT JOIN orders AS o ON u.id = o.user_id WHERE u.id < 10 GROUP BY u.id HAVING count > 3 LIMIT 2 OFFSET 1;

LIMIT 30 OFFSET 30
每頁30筆,第二頁開始會漏掉前面的30筆(從31開始)

28:00
SUB SELECT:把一個select的結果,塞到另一個select去做過濾式

(1)SELECT u.id, COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id GROUP BY u.id HAVING count >3;

可以用在INNER JOIN
SELECT * FROM tablenameA INNER JOIN tablenameB
可以把(1)放到tablenameB去

SELECT u.id, u1.id FROM users AS u INNER JOIN (SELECT u.id, COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id GROUP BY u.id HAVING count >3) AS u1 ON u.id = u1.id

(1)也可以用在WHERE
SELECT * FROM users AS u WHERE id IN (SELECT o.user_id FROM orders AS o WHERE o.status >3 GROUP BY o.user_id);

改成ruby的打法
User.select('*').from('users AS u').where('id IN (SELECT o.user_id FROM orders AS o WHERE o.status > 3 GROUP BY o.user_id)')

00:41:00
如何製作報表:GROUP BY的應用 - 用日期去做壓縮

SELECT u.id, o.id. o.status, o.updated_at FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id;

mysql DATE_FORMAT
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

把%d改成01,就可以改成月報表
SELECT u.id, o.id. o.status, DATE_FORMAT(o.updated_at, "%Y-%m-01"), COUNT(o.id) AS count FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.update_at, %Y-%m-01);

計算最大值,最小值,平均,總和
SELECT u.id, o.id. o.status, DATE_FORMAT(o.updated_at, "%Y-%m-01"), MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.update_at, %Y-%m-01);

00:52:46
不建議每次都用SELECT:佔用table,會做table lock的動作,會影響到別人的使用狀況,別人就不能修改。

不可能每次都重跑,所以要在rails 把暫存表做出來
rails g migration add_statics

def change
  create_table :statics, :id => false do |t|
     t.integer :kind, :limit => 1, :default => 0, :null => false
     t.timestamp :time_at, :null => false
     t.integer :max
     t.integer :min
     t.integer :count
     t.integer :avg
     t.integer :sum
  end
  add_index :statics, [:kind, :time_at], :unique => true
end

rake db:migrate
就做好table了

如何把暫存表一次存光光
公式
INSERT INTO Table { Column1, Column2} VALUES { Value1, Value2}, { Value1, Value2}

不用加VALUES

INSERT INTO statics ( kind, time_at, max, min, count, avg ,sum) (SELECT 3 AS kind, DATE_FORMAT(o.updated_at, "%Y-01-01") AS time_at, MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.updated_at, "%Y-01-01"))

結果:
Query OK, 25 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

改成 3 As kind 跟 "%Y-01-01" 就是年報表
改成 2 As kind 跟 "%Y-%m-01" 就是月報表
改成 1 As kind 跟 "%Y-%m-%d" 就是日報表

語法其實是有問題的,因為有index,所以不能用第二次
會出現:
ERROR 1062(23000): Duplicate entry '' for key

所以要用mysql insert update
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a) +VALUES(b);

mysql insert on duplicate update value
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c)

所以應該要改成
INSERT INTO statics ( kind, time_at, max, min, count, avg ,sum) (SELECT 3 AS kind, DATE_FORMAT(o.updated_at, "%Y-01-01") AS time_at, MAX(o.status) AS max, MIN(o.status) AS min, COUNT(o.id) AS count, AVG(o.status) AS avg, SUM(o.status) AS sum FROM users AS u INNER JOIN orders AS o ON o.user_id = u.id GROUP BY DATE_FORMAT(o.updated_at, "%Y-01-01")) ON DUPLICATE KEY UPDATE max=VALUES(max), min=VALUE(min);
把要改的column寫上去,這邊只放兩個為例子
可以做成一個按鈕,讓你一直去更新資料

1:09:00
rails下如何去拿剛剛的資料
做statics.rb

Class Static < ActiveRecords::BASE

  KIND = [["0", 0],['1','Day'],['2','Month'],['3','Year']]

end


Static.where('kind = 3').each do |s|
 puts "#{s.time_at.strftime('Y%-%m-%d')} : sum : #{s.sum}"
end ; true

會出現
2015-01-01 : sum : 99
2016-01-01 : sum : 684
2017-01-01 : sum : 502

1:12:00
D3.js
BASIC CHARTS
HIGHCHARTS

1:14:00; end
用rails 塞資料,跟mysql 塞資料,速度的差別

t = Time.now.to_f ; 1000.times do |i| Static.create(:kind =>100, :time_at => Time.now + i ); end ; puts Time.now.to_f - t

跑一千筆花了七秒

mysql
ans = []
1000.times do ans << "(101, '#{Time.now + i). strftime('%Y-%m-%d %H:%M:%S')}'')" ; end ; true

ans就做好了
ans.join(',')然後就會變成我們要的sql
rails下可以直接執行sql
ActiveRecord::Base.connection.execute("INSERT INTO 'statics' ('kind', 'time_at') VALUES "#{ans.join(',')}")
跑一千筆只花了16.0ms

驗證數字是不是正確
Static.where('kind = 100').count
Static.where('kind = 101').count
都是1000筆

Static.where('kind' = 2).each do |s|
puts "#{s.time_at} : #{s.sum}"
end; true

就可以叫出來月的資料,然後拿去畫圖 HIGHCHARTS

1:24:00
把GROUP好的資料塞到另一個table去做cache
UPDATE FROM xxx WHERE id IN (SELECT)
UPDATE FROM xxx WHERE id IN (1,2,3,4,5)
DELETE FROM xxx WHERE id IN (SELECT)

← impressionist 欲增功能 →