-
-
Save satomixx/5235990 to your computer and use it in GitHub Desktop.
DB入門 ~MySQL~ ref: http://qiita.com/items/b9abf3585bb18867bc0c
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. 商品番号1の商品のな前をください | |
2. ください 名前 商品 商品番号1 | |
3. SELECT 名前 FROM 商品 WHERE 商品番号が1 | |
-> SELECT item_name FROM items WHERE item_id = 1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. 新規登録 商品(商品番号,商品名,商品値段)値は(9991, 技術饅頭, 4096円) | |
-> INSERT INTO items (item_id, item_name, item_price) VALUES (9991, '技術饅頭', 4096); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. 編集 商品 商品値段を8000円に 商品番号が9991のやーつ | |
-> update imtes set item_price=8192 where iyrm_id=9991; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. 削除 商品 商品番号9991 | |
2. delete from 商品 where 商品番号9991 | |
-> delete from items where item_id = 9991; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ssh [hostname]@domain |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql -u -[loginname] -p [databasename]; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> select order_slip_id, order_date, user_id from order_slips where order_date between '2012-01-01 00:00:00' and '2012-01-03 00:00:00'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SELECT OS.order_slip_id , OS.user_id , OS.order_date , OSDETAIL.item_id , OSDETAIL.item_num , OSDETAIL.item_unit_price , (OSDETAIL.item_num * OSDETAIL.item_unit_price) as subtotal FROM order_slips OS LEFT JOIN order_slips_detail OSDETAIL ON OS.order_slip_id = OSDETAIL.order_slip_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT OS.order_slip_id /* どの伝票で */ | |
, OS.user_id /* 誰が */ | |
, sum(OSDETAIL.item_num * OSDETAIL.item_unit_price) as total /* いくら買ったか =「商品単価*売り上げ個数」のsum(集計)を取得 */ | |
FROM order_slips OS | |
LEFT JOIN order_slips_detail OSDETAIL | |
ON OS.order_slip_id = OSDETAIL.order_slip_id | |
GROUP by OS.order_slip_id; /* 伝票別に全件取得 */ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from order_slips OS |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select sum(----) as ---- |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// | |
BD baundle = db connecting function(id, pass, ....); | |
//エスケープ処理が一番大事だよ! | |
//これは大事なんですよー | |
id = db escape(id); | |
sql = 'select * from items where id = {id};'; | |
//ちょっと良くわからないけど処理 | |
sql = 'select * from items where id = 0;'; | |
db hundle->sql original one(); | |
db hundle->send data(array[id => {id}]) }; | |
// | |
sqlresult = db hundle->query(sql); | |
sql = 'select * from items where id = {id};'; | |
sql = 'select' * from items where id = 0; insert sukinadata;'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. Atomicity 全部実行か全部なし | |
2. Consistency おかしな値は入らない整合性 | |
3. Isolation transaction中は干渉されない | |
4. Durability transactionが終了したら結果は失われない |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. Consistency すべてのノードで同じ情報が帰ってくること | |
2. Availability システムが使えなくなる時間がないこと | |
3. Partition-tolerance ノードをネットワーク的に分断できること | |
- Scale-out サーバー数を増やして並列処理のスケールを上げる | |
- Scaoe-up サーバー単体の性能を増強する cpu, memory, hd, access speed | |
- CAP定理は2つしか満たすことができない | |
- C+A ネットワーク分断に弱いから拡張性に乏しい ->real money service, core data | |
- A+P データの更新性で遅延がある可能性 -> ordianry servieces, | |
- A+P 単一障害点がある,またデータの同期などで時々システムが使えなくなる -> only bank, basically unuseful. | |
3つやろうとすると論理的に破綻する |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
前提a=3, b=4, c=NULL | |
NOT(a=c): NULL | |
a>b OR b>c: NULL | |
a>b AND b>c: false | |
a<b OR b<c: true |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// CGIを受け取る | |
// 商品IDをチェック | |
// かごオブジェクトに突っ込む | |
// かごを保存 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
exit or ctl+d |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
slow_query |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
EXPLAIN |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
slow_query time = 0 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/etc/my/cnf |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-> select item_id, item_name, item_price, from items order by item_price order by desc limit 0,10; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-> select item_id, item_price, item_name from items_truth where category_id = 1 or category_id = 2 order by item_price desc limit 0,10; | |
これを書き換えて | |
-> select item_id, item_price, item_name from items_truth where category_id IN (1,2) order by item_price desc limit 0,10; | |
IN is faster than OR. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
decs [table名] テーブルの中身一覧 | |
show tabel テーブル一覧 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment