Category Archives: SQL

primary key 複数

複数のprimary keyは設定できない

mysql> create table test2 (id integer not null primary key, name varchar(10) not null primary key);
ERROR 1068 (42000): Multiple primary key defined

複合プライマリーキーは可能

mysql> create table test2 (id integer not null, name varchar(10) not null, primary key (id,name));
Query OK, 0 rows affected (0.14 sec)
// 1レコード目を追加
mysql> insert into test2 (id,name) values(1,"test");
Query OK, 1 row affected (0.01 sec)

// id,nameが同じレコードをもう一回追加しようとするとエラー
mysql> insert into test2 (id,name) values(1,"test");
ERROR 1062 (23000): Duplicate entry '1-test' for key 'PRIMARY'

// idはそのままで、nameを別のものに変えるとinsertできる
mysql> insert into test2 (id,name) values(1,"test2");
Query OK, 1 row affected (0.00 sec)

mysqlユーザー作成

// ユーザー確認
select user,host from mysql.user;

// ユーザー作成(全権限付与。どこからでもアクセス可能。)
grant all privileges on *.* to {ユーザー名}@"%" identified by '{パスワード}' with grant option;

// ユーザー削除
drop user test_user1;

mysql db コピー(dump)

コマンド

db上のデータをローカルにコピーする場合

// 設定及びデータをsqlファイルに出力
mysqldump -h {対象のIPアドレス} -u root {データベース名} > dump.sql

// 入れる先のdbを作成
create db {対象のdb名}

// dbに入れる
mysql -u root {データベース名} < dump.sql 

参考

http://phpspot.net/php/pgmysqldump%E3%81%A7%E3%83%90%E3%83%83%E3%82%AF%E3%82%A2%E3%83%83%E3%83%97%EF%BC%86%E5%BE%A9%E5%85%83.html

dbのデータ型

テーブル作成

create table test (id int, name varchar(10));

テーブルにデータ挿入

insert into test (id,name) values (1,1),(2,2);

rubyからデータ抽出

require 'mysql2'

HOST = "127.0.0.1"
USERNAME = "root"
PASSWORD = ""
DATABASE = "test"

client = Mysql2::Client.new(
  :host =>  HOST,
  :username => USERNAME ,
  :password => PASSWORD ,
  :database => DATABASE
)

query = "select * from test"
records = client.query(query
p records.to_a

出力結果

// idは数字。nameは文字型になっている。
=> [{"id"=>1, "name"=>"1"}, {"id"=>2, "name"=>"2"}]

SQL View

View作成

create view view1 as
  (select
    id ,
    count(*) as id_count
  from
    test_db
  group by
    id);

view一覧取得

select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_TYPE = 'VIEW' and  TABLE_SCHEMA = '{データベース名}';

mysql テーブル作成

create table test (id int auto_increment, key1 int, key2 int, created_at timestamp not null default current_timestamp, primary key (id));
update test set test.active = 1
where (id) in
(select id
from test
where (kr,created_at) in
(select kr, max(created_at) from test group by kr))

primaryとindex

テーブル作成

create table test1 (id int, name varchar(10), primary key(id, name));
create table test2 (id int, name varchar(10), index(id));
create table test3 (id int, name varchar(10), unique(id, name));

テーブル定義

// プライマリーの場合は、nullが許可されていない
mysql> show columns from test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

// index設定をしただけの場合は、nullは許可されている
mysql> show columns from test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

// ユニーク設定をしただけの場合もnullは許可されている
mysql> show columns from test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

その他備考

key = MUL

は、multiple keyの略で、重複可能なキーのこと

テーブル定義

// 以下でテーブル定義をcreate文の形式で確認することができる
show create table {テーブル名};