help;

select user();

show databases;

create database db名;

drop database db名;

select database();

use データベース名;

create user ユーザ名@localhost identified by 'password'

grant all on db名.* to ユーザー名@localhost;

drop user ユーザ名@localhost;

select host,user,password from mysql.user;

# myappデータベースがあれば先にdropする
drop database if exists myapp;

# データベースを作る
create database myapp;

-- grantとcreate userは一緒に書くことができる
grant all on myapp.* to myapp_user@localhost identified by 'myapp_user';

mysql -u root < create_myapp.sql

\. ./create_myapp.sql

-- 一行
# 一行
/* 
複数行
*/

drop table if exists users;
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  -- score float not null
  score float default 0.0
);

show tables;

desc テーブル名;

drop table テーブル名;

例）　char(4)、varchar(255)

rank enum('gold','silver','bronze')

coins set('gold','silver','bronze')
insert into users (name, score, coins) values ('taguchi', 5.8,'gold,silver');

insert into users(id,name,score) values
  (1,'taguchi',5.8),
  (2,'fkoji',8.2),
  (3,'dotinstall',6.1),
  (4,'yamada',null);

insert into users(id,name) values
  (5,'tanaka');

末尾に追加
alter table users add column email varchar(255);

nameの後に追加
alter table users add column email varchar(255) after name;

alter table users drop column score;

名前をnameからuser_nameに変更し、varchar(80)に変更する
alter table users change name user_name varchar(80);

alter table users rename persons;

select * from users where name like binary 'T%';

nameが6文字だけを抽出
select * from users where name like '______';

nameの２文字目がaだけを抽出
select * from users where name like '_a%';

select * from users limit 3;

select * from users limit 3 offset 3;

select * from users order by score desc limit 3;

update users set name = 'sasaki', score = 2.9 where name = 'tanaka';

delete users where 条件式;

+ - * / %　全て使える

select round(5.355); -- 5
select round(5.355,1); -- 5.4

select floor(5.833); -- 5

select ceil(5.238); --6

select rand();

select * from users order by rand() limit 1;

select length('Hello'); -- 5

select substr('Hello',2); -- ello
select substr('Hello',2,3); -- ell

select upper('Hello'); -- HELLO

select lower('Hello'); -- hello

select concat('Hello','World'); -- HelloWorld

select length(name) as len, name from users order by len

select 
  name,
  score,
  if (score > 5.0,'OK','NG') as result
from 
users;

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 floor(score) % 2 = 0 then 'even'
    when floor(score) % 2 = 1 then 'odd'
    else null
    end as type
from users;

create table users_with_team as 
select
  id,
  name,
  score,
  case
    when score > 8.0 then 'Team-A'
    when score > 6.0 then 'Team-B'
    else 'Team-C'
  end as team
from
  users;

create users_copy select * from users;

create table users_empty like users;

select count(score) * from users_with_team;
select count(*) * from users_with_team;

select sum(score) from users_with_team;
select min(score) from users_with_team;
select max(score) from users_with_team;
select avg(score) from users_with_team;

select distinct team from users_with_team; -- チーム一覧
select distinct count(team) from users_with_team; -- チーム数

select sum(score),team from users_with_team group by team;

select sum(score),team from users_with_team group by team having sum(score) > 10;

select
  sum(t.score),
  t.team
from
  (select
    id,
    name,
    score,
    case
      when score > 8.0 then 'Team-A'
      when score > 6.0 then 'Team-B'
      else 'Team-C'
    end as team
  from users) as t 
group by t.team;

create view ビュー名 as セレクト文;
create view top3 as select * from users order by score desc limit 3;
select * from top3;

show create view top3;

drop table if exists users;
drop view if exists top3;

start transaction;

update users set score = score - 1.2 where name = 'fkoji';
update users set score = score + 1.2 where name = 'taguchi';

commit;

alter table テーブル名 add index 索引名 (索引を設定したいカラム名);
alter table users add index index_score (score);

show index from テーブル名;
show index from users;

explain select文;
explain select * from users where score > 5.0;

alter table テーブル名 drop index 索引名;
alter table users drop index index_score;

select posts.id, posts.title, posts.body, comments.body from posts inner join comments on posts.id = comments.post_id;

select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts right outer join comments on posts.id = comments.post_id;

alter table 外部テーブル名 add constraint 制約名 foreign key (外部キー) references 内部テーブル（内部キー）;
alter table comments add constraint fk_comments foreign key (post_id) references posts(id);

alter table テーブル名 drop foreign key 制約名
alter table comments drop foreign key fk_comments;

insert into posts(title,body) value('new title','new body');
insert into comments (post_id, body) values(last_insert_id(),'first comment for new post');

create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 処理名 何のテーブルに 何の値を
create trigger post_insert_trigger after insert on posts for each row insert into logs values('post added!');

drop trigger if exists post_insert_trigger;

show triggers;
show triggers \G;

delimiter 別の文字
delimiter //
処理;
処理;
処理//
delimiter ;

delimiter //
create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 
 begin
  処理名 何のテーブルに 何の値を;
  処理名 何のテーブルに 何の値を;
 end
//
delimiter ;

drop trigger if exists logs;
delimiter //
create trigger posts_update_trigger after update on posts for each row
 begin
     insert into logs(msg) values('post updated!');
     insert into logs(msg) values(concat(old.title, ' -> ', new.title));
 end
//
delimiter ;
insert into posts (title, body) values ('title 1', 'body 1');
insert into posts (title, body) values ('title 2', 'body 2');
insert into posts (title, body) values ('title 3', 'body 3');
update posts set title = 'title 2 updated' where id = 2;

SELECT DISTINCT(name) FROM words;