前回の記事ではMySQLでデータべースとユーザーの作成したり、MySQLのデータ型などをおおまかにみてきました。
前回の記事
MySQLの基本的な使い方【データベース、ユーザーの作成、データ型】
今回はテーブルの操作を主に書いてます。
テーブル
フィールドの追加
テーブルにフィールドを追加する。テーブルにフィールドを追加するにはadd columnのあとに
フィールド名とフィールドの型を記述します。afterを使用すると指定したカラムの後にフィールドを追加することができます。
-- alter table tablename add column fieldname varchar(255); alter table tablename add column fieldname varchar(255) after name;
フィールドの削除
alter table tablename drop column fieldname;
フィールドの名前や型を変更する
フィールドの名前や型を変更するときはchangeを使います。chnageのあとに元のフィールド名 新しいフィールド名のような書き方になります。なお型指定は必須で、型指定しないとエラーになります。
alter table tablename change name user_name varchar(80);
テーブル名の変更
alter table oldtablename rename newtablename;
データの抽出
select * from users; -- 全データを抽出 select id, name from users; -- idとnameだけを抽出 select * from users where score >= 6.0; -- 条件つきデータ抽出
演算子
- = = !=
- is null, is not null
- and or not
- like
- binary
select * from users where name = 'hoge' or name = 'fuga';
select * from users where name in('hoge', 'fuga');
like演算子はワイルドカード%や_を指定することができます。
select * from users where name like 'ho%' select * from users where name like '____'
ソート
- order by
- limit
- offset
order byをつかうとscoreが昇順になります。order by descを使うと降順になります。
select * from users order by score; select * from users order by score;
limitを使うとデータの表示件数を制限することができます。
select * from users order by score limit 3;
offsetを使うと最初の x 件を除外します
select * from users order by score limit 3 offset 3;
データの更新
update users set score = 5.0; update users set score = 5.9 where id = 1; update users set score = 2.9 where name = 'sasaki'; delete from users where score < 5.0;
四則演算
+–*/%を使用できます
update users set score = score * 2.0 where name = 'tashiro'; update users set score = score + 1.0 where id % 2 = 0; select * from users;
組み込み
- select round();⇀ 四捨五入
- select floor();⇀ 切り上げ
- select ceil();⇀ 切り捨て
- select rand();⇀ ランダムな数値(0以上1未満の数字)
そのほかによく使いそうなものはlength();substr();lowwer();upper();concat(”,”);などがあります。
テーブルにorder by rand();とするとテーブルの中をランダムに並び替えてくれます。さらにlimitを使用することで抽選を行うことができます。名前の文字数順に並べ替えたりすることもできます。
select * from users order by rand(); select * from users order by rand() limit 1; select length(name), name from users order by length(name);
as
asはフィールドの名前を別名にすることができます。
select name as user_name from users;
enum()、set()
フィールドにenum()を使用すると、enum()で指定した値以外はいれないようにすることができます。またenum()で設定した値は配列のようになっていて、1番から連番で管理されています
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(20),
score float,
rank enum('gold', 'silver', 'bronze')
);
insert into users (name, score, rank) values ('hoge', 5.8, 'silver');
insert into users (name, score, rank) values ('fkoji', 8.2, 'gold');
insert into users (name, score, rank) values ('dotinstall', 6.1, 'silver');
insert into users (name, score, rank) values ('Tanaka', 4.2, 'bule'); --ここがエラーになる
フィールドにset()を使用すると複数の値をもたせることができます。値はカンマ区切りで指定しますがその後にスペースをいれるとエラーになります
またset()も配列のように番号で管理されて、最初の要素は 2の0乗番目からはじまります。
drop table if exists users;
create table users (
id int unsigned primary key auto_increment,
name varchar(20),
score float,
coins set('gold', 'silver', 'bronze')
);
insert into users (name, score, rank) values ('hoge', 5.8, 'silver,bronze');
insert into users (name, score, rank) values ('fkoji', 8.2, 'gold,sliver');
insert into users (name, score, rank) values ('dotinstall', 6.1, 'silver,bronze');
mysql> select * from users where coins = 3; --goldとsilverが含まれているものを返す
条件分岐
mysqlの条件分岐はifcaseを使います。ifやcaseはselect文の中で使います。
if
select name, score, if(score > 5, 'ok', 'ng') as result from users;
case
caseはcase句に値を指定するやり方とwhen句に直接条件を書き込むやり方があるようです。
select name, score, case floor(score) % 2 when 0 then 'even' when 1 then 'odd' else null end as type from users;
select name, score, case when score > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as type from users;
テーブルの複製
create tableとasを使うことによって抽出結果で新しいテーブルを作成することができます
create table users_team as select name, score, case when score > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as type from users; show tables;
それからテーブルをそっくりそのまま複製することもできるようです。
create table users_copy as --asは省略可能 select * from users; show tables;
テーブルの構造はそのままでデータはいらないという場合はlikeを使えばよいようです。
create table users_empty like users; desc users_empty; select * from users_empty;
まとめ
今回はおもにMySQLのデータの抽出に関してみましたが、こに書いた以外にもまだたくさんあるとは思いますのでとりあえずこのくらいにしておきたいと思います。これでおわりかとおもいきや、まだデータの集計がありますので、そちらは次の記事にしたいと思います。
次の記事はこちらです。↓
MySQLの基本的な使い方【データの集計他】
コメント