LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@dym330

SQL(MySQL) 備忘録

Posted at

データベースの作成・削除

create database test_db;
drop database test_db;

テーブルの作成

create table test_db.test_table (
	id int(6) unsigned default 0 comment 'ID',
	val varchar(20) default 'hello' comment '値'
);

※int unsignedで正の整数を指定できる

テーブルの削除

drop table test_db.test_table;

テーブル定義の確認

いずれかで確認可能（表示方法は違う）

desc test_db.test_table;
show full columns from test_db.test_table;
show create table test_db.test_table;

DBのアクティブ切り替え

use test_db; --切り替え
select database(); --確認方法

制約

UNIQUE: 一意制約
NOT NULL: NOT NULL制約
CHECK: チェック制約（MySQL8.0より　条件式が使える）
PRIMARY KEY: 主キー制約
FOREIGN KEY: 外部キー制約

create table test_table (
	id int not null default 0 comment 'ID',
	val varchar(20) unique comment '値'
);

キー

主キー

create table test_db.test_table (
	key1 int primary key
);

複合主キー

create table test_db.test_table (
	key1 int,
	key2 int,
	primary key (key1, key2)
);

自動IDの付与

  • auto_incrementはindexが振られていないといけない

    (primary key, uniqueなどは自動で振られている)
  • 一つのテーブルに一つのみ使用可能
  • default値は使用不可
create table test_db.test_table (
	key1 int auto_increment primary key
);

indexの確認方法

show index from test_table;

カラム追加

alter table test_db.test_table
add column key2 varchar(20) not null,
add column key3 varchar(20) not null;

-- 指定してカラムのあとに追加
alter table test_db.test_table
add column key4 varchar(20) not null after key2;

-- 先頭に追加
alter table test_db.test_table
add column key5 varchar(20) not null first;

カラムの修正

alter table test_db.test_table
modify column key5 int;

カラムの削除

alter table test_db.test_table
drop column key5;

-- 主キーの削除m
alter table test_db.test_table
drop primary key;

外部キーの設定

CASCADE:
親テーブルの行を削除または更新し、子テーブル内の一致する行を自動的に削除または更新します。

RESTRICT:
親テーブルに対する削除または更新操作を拒否します。

alter table テーブル名
add constraint 制約名（※削除する際に使用）
foreign key (対象のキー名)
references 親テーブル名(テーブルキー名)
on update cascade 
on delete restrict; -- 省略可

alter table test_db.stocks 
add constraint fk_product_id
foreign key (product_id)
references products(id)
on update cascade
on delete restrict,
add constraint fk_shop_id
foreign key (shop_id)
references shops(id)
on update cascade
on delete restrict;

トランザクションテーブルとマスタテーブル

トランザクションテーブル
アプリから頻繁にデータ更新を行うテーブル
テーブル名の先頭にENT, TXN, TRNをつける場合が多い

マスタテーブル
参照値を保持するテーブル。アプリから値を挿入、変更はしない
先頭にMSTとつける場合が多い

論理削除

レコードの有効性を示すためにカラムに追加する
例）delete_flg = 1の場合には無効

更新日、更新者の導入

レコードがいつ誰によって保存されたかを示すためにカラムに追加する

updated_at timestamp default current_timestamp on update current_timestamp,
updated_by varchar(20) not null,

レコードの追加

insert into mst_prefs (name, updated_by) values ("山形", "yy"),
("青森", "yy");

レコードの削除

delete from mst_prefs;

レコードの検索

select id, name as "都道府県名" from mst_prefs ;

重複を省くときはdistinctを使用する

auto_incrementの初期化

alter table test_db.mst_prefs auto_increment = 1;

auto_incrementの確認方法

show table status where name = '対象テーブル名';

where条件

-- = : 一致
select * from txn_stocks ts 
where product_id  = 1;
-- <>, != : 非一致
select * from txn_stocks ts 
where product_id  <> 1;
-- >, >=, <, <= : 数値の比較
select * from txn_stocks ts 
where amount  > 50;
-- A and B : A かつ B
select * from txn_stocks ts 
where product_id  = 1 and shop_id = 1;
-- A or B : A または B
select * from txn_stocks ts 
where product_id  = 1 OR shop_id = 1;
-- () : 条件をくくる
select * from txn_stocks ts 
where (product_id  = 1 and shop_id = 1)
or (product_id  = 2 and shop_id = 2);
-- like : %で部分一致検索 _で１文字
select * from mst_shops
where name like '__A';
-- in : いずれかの値に一致
select * from mst_shops
where name in ("店舗A", "店舗B");
-- not in : いずれの値にも一致しない
select * from mst_shops
where name not in ("店舗A", "店舗B");
-- between A and B: A から B の値 
select * from txn_stocks
where amount between 60 and 100;
-- is not null : null以外に一致
select * from txn_stocks
where amount is not null;	
-- is null : nullに一致
select * from txn_stocks
where amount is	null;

ソートの方法

-- 単一ソート, desc 降順
SELECT * FROM txn_stocks
WHERE amount > 50
order by amount desc;
-- 複数のソート
SELECT * FROM txn_stocks
order by product_id desc,
shop_id asc;

個数指定

SELECT * FROM txn_stocks
order by product_id desc,
shop_id asc
limit 2 offset 1;
-- offsetは0から始まる
-- 下記でも同じ意味
limit 1, 2

データの更新

UPDATE txn_stocks set amount = amount - 10, delete_flg = 1
WHERE product_id = 1 AND shop_id = 1;

テーブルの内部結合

SELECT ms.name "店舗名", mp.name as "都道府県名" FROM mst_shops ms
inner join mst_prefs mp 
on ms.pref_id = mp.id
where ms.id = 1;

-- これでも同じ意味
SELECT ms.name "店舗名", mp.name as "都道府県名" 
FROM mst_shops ms, mst_prefs mp 
where ms.pref_id = mp.id;

テーブルの外部結合

SELECT mp.name "都道府県名", ms.name "店舗名" FROM mst_prefs mp 
left join mst_shops ms 
ON mp.id = ms.pref_id ;

トランザクション

start transaction;
-- 処理
commit; --成功時にDBに反映
rollback; --start transaction を宣言した地点まで戻る

処理待機中の確認

select * from information_schema.innodb_lock_waits

デッドロックの確認

show engine innodb status;

truncate テーブルの切り捨て

  • rollbackで戻すことができない
  • whereは使用不可
  • deleteより高速
  • auto_incrementは初期値になる
truncate テーブル名

システム変数

システム変数の確認

-- 確認方法
show variables;
-- like検索
show variables like "%auto%";
--session内のみ確認
show session variables;
--全体の確認
show global variables;
-- @@を用いた確認方法
select @@local.autocommit;
-- 更新
set session autocommit = 1;

ユーザー定義変数

-- set方法
set @s_id = 2;

-- 以下でもset可能
select @s_name := name from mst_shops ms
where ms.id = 2;

日時の違い

TIMESTANP 4byte
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07'

DATETIME 5byte
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
※タイムゾーンを考慮しない

ユーザーの確認と作成

現在のユーザーの確認
root@localhostはrootにはlocalhostからしか入れない

select user();

ユーザーの追加
create user {ユーザー名}@{接続元のホスト名} identified by 'password';

create user 'test_user'@'localhost' identified by 'pwd';

ユーザーの確認

select * from mysql.`user`;

権限の付与
grant {権限名} on {対象のDBオブジェクト} to {ユーザー};

grant select on test_db.* to 'test_user'@'localhost';
grant update, insert on test_db.* to 'test_user'@'localhost';
grant create, alter on test_db.* to 'test_user'@'localhost';

-- 全てのデータベースの全てのオブジェクトに対してかかる
grant create, alter on *.* to 'test_user'@'localhost';
-- 全てのデータベースの全てのオブジェクトに全権限を与える
grant all on *.* to 'test_user'@'localhost';
-- 権限の確認
show grants for 'test_user'@'localhost';
-- 権限の削除
revoke all on test_db.* from 'test_user'@'localhost';

文字コード

-- Databaseの文字コード指定
create database utf8mb4_db
    character set 'utf8mb4';

-- テーブルへの設定
create table tbl_name (column) character set 'utf8mb4';

CREATE TABLE test_db.char_test (
	mb4 varchar(20) character set 'utf8mb4',
	mb3 varchar(20) character set 'utf8'
);

照合順序

一覧の表示

show collation where charset like '%utf8%';

テーブル作成時に設定

create table test_db.collate_test (
  col varchar(20)
) 
character set 'utf8mb4'
collate 'utf8mb4_general_ci';

検索

select col "UNI" from test_db.collate_test where col COLLATE utf8mb4_unicode_ci = 'はは';
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?