データベースの作成
CREATE DATABASE [データベース名]
テーブルの作成
create table if not exists [テーブル名] (
id unsigned auto_increment primary key not null,
staff_id varchar(255),
name varchar(255) not null,
section varchar(255),
position varchar(255),
created timestamp default 0,
modified timestamp not null on update current_timestamp default current_timestamp)
engine=InnoDB default charset=utf8;
データベスとテーブル削除
DROP DATABASE データベース名;
DROP TABLE テーブル名;
テーブルのデータを削除
delete from テーブル名;
テーブルの先頭から数件を指定して削除
delete from テーブル名 limit 件数;
ユニークキーの設定方法
alter table テーブル名 add unique (対象フィールド);
trunkcate
truncate table テーブル名;
truncateとdeleteの違い
プライマリーキー(idなど)が初期化されるのがtruncateで、deleteは消した時の最終プライマリーキー番号から次のデータが始まる
カラムの追加・削除
// 一番下に追加
ALTER TABLE テーブル名 ADD カラム名 VARCHAR(255) NOT NULL;
//最初に追加
ALTER TABLE テーブル名 ADD カラム名 VARCHAR(255) NOT NULL FIRST;
//途中に追加
ALTER TABLE テーブル名 ADD カラム名 VARCHAR(255) NOT NULL AFTER カラム名;
//カラムの削除
ALTER TABLE テーブル名 DROP COLUMN カラム名;
データの表示
SELECT カラム名 FROM テーブル名;
ソート
#2016-0¥1分のデータ取得
SELECT * from テーブル名 where `date`>='2016-01-01' and `date`<'2016-02-01' order by `department`,`name`,`date` asc
ORDER[group] BYの後にソートを行うカラム名を指定。
昇順(例:a,b,c) : ASC
降順(例:c,b,a) : DESC
*省略した場合は昇順
*ソートする対象のカラムはカンマ区切りで複数指定可能
=> 最初のカラムでソート
=> 次に最初のカラムで同じ値だったものだけを対象に2番目のカラムでソート
// 3つ以上指定した場合も同様
レコード数の取得
select count(*) from テーブル名;
重複を含まないレコードの件数
SELECT COUNT(DISTINCT 列名)FROM テーブル名;
エイリアス(別名)をつける
select [カラム名] as [別名], [カラム名] as [別名] from [テーブル名]
データ編集
update [テーブル名] set [カラム名]=値 where [カラム名]=値;
テーブルから少〜し細かく指定
select [カラム名(id,name,company_idなど)] from [テーブル名] where [カラム名(name)]=['値'('hoge')] AND [カラム名(company_id)]=['値'('00438')]\G;
*\Gで見やすくする
応用(複数のテーブルを結合)
サブクエリを利用して、二度おいしい
SELECT * FROM
(SELECT a.`date`,a.company_id,a.`clock_in`,a.`clock_out`,a.`rest`,a.`sum`,a.`note`,b.`in_out`,b.`time` FROM attendance AS a
LEFT JOIN pc_log AS b
ON (a.company_id=b.company_id and a.date=b.date)) h
where h.company_id='VC00483' order by h.`date`,h.`in_out`
select [* : 全て] from
[select ... : サブクエリで取得したデータから]
[h : エイリアス(別名)]
where [h.company_id= : エイリアス.欲しい値] [order by : ソート]
*attendanceとpc_logを外部結合して得た両テーブル全ての値から、自分の欲しい値のみを取得
*where の後のh.company_idはサブクエリで得られた値
ASで演じる
下記のsql文を実行する...
select i.`date`,i.`company_id`,i.`name`,i.`clock_in`,i.`clock_out`,i.`rest`,i.`sum`,i.`note`,j.`time` FROM attendance as i
left join (select a.`company_id`,a.`name`,a.`date`,a.`time`,b.`time` from pc_log a left join pc_log b on(a.`company_id`=b.`company_id` and a.`date`=b.`date`) where (a.`in_out`='in' and b.`in_out`='out') order by a.`company_id`,a.`date`,a.`in_out`,b.`company_id`,b.`date`,b.`in_out`) as j
on (i.`company_id`=j.`company_id` and i.`date`=j.`date`) order by i.`date`
と...Duplicate column name timeと怒られた!
調べてみると、pザーラにpザハットの注文をするような感じらしい...
まあ、原因はサブクエリにa.time
とb.time
が二つあるから、本家では判断できないらしい...という事なので、下記二役の代役をASに頼んでみたら、良いよと言うので、お願いした。
a.`time` =>a.`time` as in_time
b.`time` => a.`time` as out_time
select i.`date`,i.`company_id`,i.`name`,i.`clock_in`,i.`clock_out`,i.`rest`,i.`sum`,i.`note`,j.`in_time`,j.`out_time` FROM attendance as i
left join (select a.`company_id`,a.`name`,a.`date`,a.`time` as in_time,b.`time`as out_time from pc_log a left join pc_log b on(a.`company_id`=b.`company_id` and a.`date`=b.`date`) where (a.`in_out`='in' and b.`in_out`='out') order by a.`company_id`,a.`date`,a.`in_out`,b.`company_id`,b.`date`,b.`in_out`) as j
on (i.`company_id`=j.`company_id` and i.`date`=j.`date`) order by i.`date`
すると、画面が満員御礼拍手喝采めでたしめでたし。
参考資料
http://tkzwhdyk.com/?p=81
http://nexseed.net/tech-blog/basic-sql-usages/
・重複カラムに対するAS(エイリアスのつけ方)
http://phpjavascriptroom.com/?t=mysql&p=join