MySQL

2022年10月3日2022年11月29日

mysql -u root -proot（管理者でログイン）

コマンドプロンプトで管理者（root）ログインする時

１行で入力する時（パスワード表示）
mysql -u root -proot

２行で入力する時（パスワード非表示）
mysql -u root -p（でエンター押して、次にパスを入力するだけ）
Enter password: ****

・ｐ（パスワード）の後は空白不要（入れると空白もパスワードと認識する為）
・コマンドプロンプトで改行する時は^（キャレット）
・「mysql」は、mysql.exeをcmd(コマンドプロンプト)から動かす事
・rootは木の根っこという意味
・最後にセミコロン不要

ログイン直後のコード

C:●●●\●●●\フォルダ名>mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and

Google翻訳は下記の通り。

C:（省略）mysql -u root -proot mysql: [警告] コマンド ライン インターフェイスでパスワードを使用すると、安全でない可能性があります。 MySQL モニターへようこそ。 コマンドは ; で終わります。 または \g. MySQL 接続 ID は 6 です サーバーのバージョン: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, オラクル

show databases;（データベースを見る）

-- データベースをみる
show databases;

・「->」はまだ次の文字の入力待ちの状態
・文章の終わりは「;」or「\g」or「\c」

exit（or quitでmysqlから出る）

exitかquitで出る

カラムとレコード

縦の列がカラム（項目）。

横の行がレコード。

create database データベース名（データベース作成）

-- データベースAを作成
create database A

・セミコロン不要

use データベース名（データベースの使用宣言）

-- A（データベース）を使用
use A

use データベース名を入力すると「Database changed」と表示される（使用宣言）。

・セミコロン不要

create table テーブル名 （項目名 データ型,…);（テーブルの定義）

create table テーブル名(
項目名　データ型　primary key,
項目名　データ型,
);

-- 例）
create table テーブル名 (
   id int
primary key,
   name varchar(桁数),
   birthyear year
);

・項目名の最後の行はカンマ不要。
・途中でミスったら\cで終わらし、最初から打ち直す。
・スペースで空白はOK、tabキーはダメ（別物）。
・普通はテラパッドなどで入力してから貼り付ける。

ユーザーの削除

-- 例）ユーザー●●を削除
drop user ●●@localhost;

データ型とオプション

＜データ型＞
int型・・・整数
char型・・・固定長の文字列←文字数を指定（例、一文字、4桁等）
varchar型・・・可変長の文字列←文字数を指定　（例　20文字迄）
year型・・・年
date型・・・日付（yyyy-mm-dd)

＜オプション＞
primary key・・・表に１つ必ず必要な独自キー。（idなどの管理番号)
auto_increment・・・自動連番
not null・・・入力必須

show tables;（テーブルの確認）

-- テーブルを確認
show tables;

・データベースを使用宣言している状態で実行可能っぽい

desc テーブル名 ;（定義を確認できる）

-- テーブル定義を確認
desc テーブル名;

insert into テーブル名（データの挿入）

-- 例）
insert into テーブル名
  (id, name, age, birthyear, dept)
values
  (1, '●●●●', ●●, 19●●, ●●部),
  (2, '●●●●', ●●, 19●●, ●●部);

select * from テーブル名;（一覧表示）

select * from テーブル名;で一覧表示される

-- 例）テーブルAを一覧表示
select * from A;

-- 例）指定の項目だけ一覧表示
select
  id,
  name
from テーブル名;

select * from テーブル名 where 条件（データの抽出）

-- 例）テーブルAから20才以上の人を抽出
select
*
from テーブルA
where
　　age >= 20 ;

-- 例)テーブルAから帰宅部を抽出
select
  *
from A
where
  A like
'帰宅%';

・likeを＝でもいいが、曖昧な時はlike。

update テーブル名 set 修正内容 where プライマリーキー（データの修正）

-- 例）テーブルAのidの３を相撲部に修正
update A
set
dept = '相撲部'
where
id = 3;

delete from テーブル名 where プライマリーキー（データの削除）

-- 例）idが５の人を削除
delete
from テーブル名
where
id = 5;

CRUD（Create Read Update Delete）

永続的なデータを取り扱うソフトに要求される4つの基本機能。
データの作成（Create）
読み出し（Read）
更新（Update）
削除（Delete）の頭文字を繋げて、クラッド。

insert（作成）
select（読込）
update（更新）
delete（削除）

drop table テーブル名（テーブル削除）

-- 例）テーブルAを削除
drop table A;

・dropは●●毎削除、deleteはデータ（一部分を）削除。

コマンドプロンプトを開く場所の移動

1. 「cd」+　スペース
2. 開きたい場所のアドレス

-- 例）
C:\Users\user>cd
C:\Users\user\Documents\●●
　　　　　↓
C:\Users\user\Documents\●●

ユーザー作成と権限付与

-- 例）データベースの全ての権限をローカルホストのユーザー名に付与しパスワードを指定
grant all on
データベース名.*
　to 'ユーザー名'@'localhost'
　identified by 'ユーザーのパスワード';

-- 例）２行で書く場合（ユーザー作成→権限付与）
CREATE USER 'ユーザー名'@'localhost' IDENTIFIED BY 'パス';
GRANT ALL ON
データベース名.*
TO 'ユーザー名'@'localhost';

・grant＝権限
・identified by～　＝　～によって識別される
・ユーザー作成→権限付与の順で別々にする事も可

select user, host from データベース名.user;（ユーザーの確認）

-- データベースmysqlのユーザーを確認する
select user, host, db from mysql.db;

・mysqlのデータベースから、ユーザーとホストとデータベースの項目だけ選択して表示するという意味。

ファイルの読み込み（\. ）

\. ファイル名（又は source ファイル名）

-- データベースの私用宣言してから、●●●.sqlを読込む
use データベース名
¥.●●●.sql

・最後にセミコロン不要

SQL文のコメントアウト

— ●●（１行）

又は

/* ●●● */　（複数行可)

IF EXISTS ~（もし存在したら～）

IF EXISTS　～　（もし存在したら～）
IF NOT EXISTS　～　（もし無ければ～）

-- 例）もしデータベースAが無ければ、データベースを作る
CREATE DATABASE IF NOT EXISTS データベースA

・EXISTS・・・存在する

自動連番を初期化する

-- 例） 自動連番を１から始める（初期化）
ALTER TABLE テーブル名 AUTO_INCREMENT = 1

・ALTER・・・変更する

まとめ（ユーザー作成～ファイル読込）

-- ユーザー作成とパスワード設定
-- 権限設定
CREATE USER 'ユーザー名'@'localhost' IDENTIFIED BY 'パス';
GRANT ALL ON データベースA.* TO 'ユーザー名'@'localhost';

-- データベースAがなければ作る
CREATE DATABASE IF NOT EXISTS データベースA;

-- データベースAを使用する
USE データベースA;

-- テーブルBが既にあれば削除
DROP TABLE IF EXISTS テーブルB;

-- テーブルBを作成
-- id はINT型で自動連番、独自キー
-- 項目BはVARCHAR型で最大20文字迄
CREATE TABLE テーブルB (
  id INT AUTO_INCREMENT PRIMARY KEY,
  項目B VARCHAR(20)
);

-- 自動連番を1からはじめる
ALTER TABLE テーブルB AUTO_INCREMENT = 1;

-- テーブルBにデータA～Bを入れる
INSERT INTO テーブルB
  (id, 項目B)
VALUES
  ('1', 'データA'),
  ('2', 'データB');

-- テーブルBを確認
SELECT * FROM テーブルB;
-------------------------------------------------
上記の●.sqlファイルを読み込む
¥. ●.sql

　

テーブルを内部結合（JOIN句）

-- 例）テーブルA・Bの内部結合
select
 *
from
テーブルA
inner join
テーブルB
on
A.id
=
B.id;

・INNERは省略可

内部結合～コードの省略化等

-- 例）テーブルAとテーブルBを内部結合し、id・タイトル・カテゴリー表示

-- そのまま結合
SELECT
  *
FROM テーブルA
INNER JOIN テーブルB
  ON テーブルA.id = テーブルB.id;

-- 項目を絞って結合
SELECT
  テーブルA.id,
  title,
  category
FROM テーブルA
INNER JOIN テーブルB
  ON テーブルA.id = テーブルB.id;

-- 項目を日本語名に変更
SELECT
  テーブルA.id,
  title as 文字,
  category as 文字
FROM テーブルA
INNER JOIN テーブルB
  ON テーブルA.id = テーブルB.id;

-- 指定を簡略化(テーブル名を省略）
SELECT
  a.id,
  title,
  category
FROM テーブルA as a
INNER JOIN テーブルB as b
  ON a.id = b.id

-- 指定を簡略化(FROMとJOINのASを省略）
SELECT
  a.id,
  title,
  category
FROM テーブルA a
INNER JOIN テーブルB b
  ON a.id = b.id
ORDER BY ID;

・「●● as 文字」で項目名を文字表示

３個以上の表を内部結合

-- 表Aと表Bを結合
-- 上記結合後に表C,表Dの順に結合
SELECT
  (表示する項目を指定)
FROM 表A
INNER JOIN 表B
ON 表Aの同項目 = 表Bの同項目
INNER JOIN 表C
ON 表Aの同項目 = 表Cの同項目
INNER JOIN 表D
ON 表Aの同項目 = 表Dの同項目
    ORDER BY id;

・二つずつ結合していく

外部結合

-- 左外部結合
SELECT
  *
FROM テーブルA
LEFT OUTER JOIN テーブルB
  ON テーブルA.id = テーブルB.id;


-- 右外部結合
SELECT
  *
FROM テーブルA
RIGHT OUTER JOIN テーブルC
  ON テーブルA.id = テーブルC.id;

・メインテーブルを左と考えるらしい

timestampdiff(YEAR, birthday, curdate()) （年齢計算）

-- 例）テーブルAのidと名前と年齢表示
SELECT
  id,
  name AS 名前,
  timestampdiff(YEAR, birthday, curdate()) AS 年齢,
FROM テーブルA 

-- birthday=生年月日

・diffは差分
・curは現在
・現在の日付と誕生日の時間差を年で返す（的な意味？）

フォルダ作成

mkdir フォルダ名

バックアップをとる

mysqldump -u root -p -B データベース名 > データベース名.dump
-- 次の行でパスワードを入力

mysqldump・・・バックアップするコマンド的な物
-B データベース名・・・データベースを指定（Bは大文字）
>・・・保存（＜・・・読込？）
データベース名.dump　・・・　データベースを表示

（要約すると、指定したデータベースを表示して保存する）

VIEW（欲しいデータの条件を定義）

ビューは、テーブルから取得したいデータの条件を定義した仮想テーブル。
ビューそのものにデータは無く、元のテーブルからデータを参照する。

-- viewの作成
CREATE VIEW ビュー名 as 定義（SELECT分）;

-- viewの使い方（テーブルと一緒）
select * from ビュー名;

-- viewの変更（alter or create)
alter view ビュー名 as 新定義;
create view or replace レビュー名 as 定義;

-- viewの削除
drop view ビュー名;

外部キー制約

テーブル間を結ぶ為の列を、外部キー（フォーレンキー）。

設定元が親テーブルで、参照側が子テーブル。
例）部署コード表が親で、従業員表が子テーブル

親テーブルに存在しないコードの入力を制限したりするのが、外部キーの制約。

FOREIGN KEY(列名) REFERENCES 親テーブル名(親列名)

-- 例
CREATE TABLE 子テーブル名 (
  id CHAR(2),
  name VARCHAR(15) NOT NULL,
  列名 CHAR(2),
  PRIMARY KEY(id),
  FOREIGN KEY (列名) REFERENCE
親テーブル名 (親列名) 
);

FOREIGN KEY行の次に下記を入れる事も可。

ON DELETE SET NULL（親側を削除すると、小側がNULLになる）
ON UPDATE CASCADE（親側を更新すると、小側も反映）

又、削除も更新もエラーにするには下記。

ON DELETE RESTRICT（削除するとエラー）
ON UPDATE RESTRICT（更新するとエラー）

FOREIGN KEY行の前に下記を入れると、制約に名称を付けられる。

CONSTRAINT 名称

NOT NULL追加

ALTER TABLE テーブル名
MODIFY
　　カラム名 NOT NULL;

項目追加

ALTER TABLE テーブル名
ADD
　　カラム名 カラム定義,
ADD
    カラム名　カラム定義;

プライマリーキー追加

ALTER TABLE テーブル名
ADD
  PRIMARY KEY (カラム名);

・一つのテーブルで複数のプライマリーキーの存在は有り、ゼロは無し。

表のデータ更新

UPDATE テーブル名
SET
  カラム名 = 訂正データ
WHERE
  条件;

-- 例）テーブルAのid=002を004へ更新
update A
set
 id = '004'
where
 id = '002';

表のデータの削除

DELETE FROM テーブル名
WHERE 条件;

-- 例）テーブルAのid＝002を削除
delete from A
where id = '002';

全データ削除

DELETE FROM テーブル名

検索

SELECT
  *
FROM テーブル名
WHERE
  カラム名 LIKE ’%●%’;