【MySQL】ユーザー作成と基本的なデータベース定義

points and lines.jp owner

5年前

1 MySQLでユーザーとデータベースを作成する

MySQLを使用した、基本的なデータベース定義の方法をメモします。

Mac OSへインストールしたMySQLにて操作を解説しています。

環境 : Mac OS High Sierra 10.13.12、MySQL Ver 8.0.16 for osx10.13 on x86_64 (Homebrew)

MySQLでユーザーとデータベースを作成する

ユーザーを作成する

まずはターミナルからMySQLへ、一番大きな権限をもつrootでログインする

mysql -u root

後ほど作成するデータベースを操作するためのユーザーを作成します。
（データベースごとに作業用ユーザーを作成するのが一般的なため）

※基本的にMySQL内でのコマンドは文末に;（セミコロン）を付けて実行します。

ユーザー作成のSQL

CREATE USER ユーザ名@localhost IDENTIFIED BY ‘password’

@以降にIPアドレスやホスト名を指定（今回はローカル環境なのでlocalhostとしている）
IDENTIFIED BY で接続する際のパスワードを指定

CREATE USER tester@localhost IDENTIFIED BY ‘tester’

データベースを作成

データベース作成のSQL

CREATE DATABASE 作成するデータベース名 CHARACTER SET UTF8

文字コードにUTF-8を指定して作成します。

CREATE DATABASE testerdb CHARACTER SET UTF8;

CREATE DATABASE testerdb CHARACTER SET UTF8;

作成したデータベースの操作権限をユーザーへ与える

GRANT ALL ON データベース名.* TO ユーザ名@ホスト名

GRANT ALL ON testerdb.* TO tester@localhost;

🔽testerdbと、後ほど作成する全てのテーブルの全ての権限をユーザーtesterへ与えている。

GRANT ALL ON testerdb.* TO tester@localhost;

権限を除外するには。。REVOKE

GRANTとは反対に権限を除外する場合はREVOKE文を使用します。
（ユーザー指定の前をTOではなく、FROM にするのがポイント）
REVOKE 権限名 ON データベース名.* FROM ユーザ名@ホスト名

🔽ユーザーtesterから、データベースtesterdbのテーブル削除の権限を除外する例

REVOKE DROP ON testerdb.* FROM tester@localhost

権限を表示する
SHOW GRANTS FOR ユーザー名@ホスト名

ユーザーでログインする

データベースの作成が出来たら、作成したユーザーでログインし直します。
\q
でrootユーザをデータベースからログアウト。

先ほどCREATE USERで作成したユーザでログインし直す。

mysql -u ユーザ名 -p データベース名

mysql -u tester -p testerdb

パスワードを入力する必要があるのでユーザ作成時に決めたパスワードを入力します。

接続が出来たら以下のコマンドで接続中のユーザ名を確認してみます。

select user();

（正しく接続出来ていることを確認する）

操作対象のデータベースが作成したものになっているかも確認しておきます。

select database();

NULLと表示されていた場合は use コマンドで操作対象のデータベースを選択します。
use testerdb

データベースにテーブルを作成する

データベース内にデータを格納するテーブルを作成します

テーブル作成のSQL

CREATE TABLE テーブル名（カラム名データ型制約の種類（あれば）,）
テーブル内に作成したい列（カラム）名ごとにカッコの中を ,（カンマ）で区切ります。

テーブル作成の一例
id, name, category_id という3つのカラムを作成している。
（列ごとにそれぞれ制約を設けています）

CREATE TABLE shop (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR (30) UNIQUE,
    category_id varchar (20) NOT NULL
);

作成が成功したら、以下のコマンドで確認することが出来ます。

作成したテーブルの一覧を表示
show tables

テーブルの構造を見る（カラムのデータ型や制約など）
desc テーブル名

テーブルのカラム（列）に対しての制約などについては、次回以降の記事にて解説したいと思います。

データベース基本操作コマンドまとめ

操作	コマンド
現在のユーザで操作対象のデータベースを選択する（rootユーザなど複数のデータベースへ権限がある場合）	use データベース名
現在のユーザで操作権限のあるデータベース一覧を表示	show databases
ログイン中のユーザーを確認	select user()
現在の操作対象のデータベースを確認	select database()
現在操作中のデータベース内のテーブルを一覧表示	show tables
現在操作中のデータベース内のテーブルの詳細（構造）を表示	desc テーブル名