第3章基礎知識とmysqlクライアント

第3章基礎知識とmysqlクライアント
3.2サーバへの接続
このログイン方式ではパスワードの入力を求められます

$ mysql -u russell -p

オプション-pとパスワードの間にスペースを残さないでください(このログイン方式は安全ではありません.パスワードは明示的に表示されます).

$ mysql -u russell -p666666

ログイン・ユーザーのデフォルトは、現在のシステム・ユーザー名と一致しています.

$ mysql -p

ログイン後のヒント

#       （;）    +g（\g）  
Welcome to the MySQL monitor.  Commands end with ; or \g.

#         
Your MySQL connection id is 15

#    
Server version: 5.6.37 MySQL Community Server (GPL)

...

# help      ，\c             
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

このコマンドではmysqlの使用方法について説明します

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (
) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

このコマンドは、MySQLまたはMariaDBのさまざまなヘルプを一覧表示します.

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help ', where  is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

このコマンドは、使用可能なすべてのデータ操作文を表示します.

mysql> help Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help ', where  is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

特定のコマンドの説明を表示

mysql> help DELETE

半分に入力したSQL文をキャンセル

mysql> show databases\c

3.3データベースの探索開始
データベースの表示

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MySQLはキーワード(SHOWなど)の大文字と小文字を区別しないので、showやsHoWを使うことができます.ただし、データベース、テーブル、カラムの名前は、大文字と小文字を区別する場合があります.
information_schemaデータベースには、サーバに関する情報が含まれています.mysqlデータベースには、ユーザー名、パスワード、権限が格納されます.
3.3.1最初のSQL文
データベースの作成

mysql> create database test;

データベースの切り替え

mysql> use test

テーブルの作成

#         test
mysql> create table books(book_id int, title text, status int);

 

#         test
mysql> create table test.books(book_id int, title text, status int);

表の表示

#         test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
+----------------+

 

#         test
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| books          |
+----------------+

テーブル構造の表示

mysql> describe books;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| book_id | int(11) | YES  |     | NULL    |       |
| title   | text    | YES  |     | NULL    |       |
| status  | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

3.3.2データの挿入と操作
データの挿入

mysql> insert into books values(100, 'Heart of Darkness', 0);

mysql> insert into books values(101, 'The Catcher of the Rye', 1);

mysql> insert into books values(102, 'My Antonia', 0);

データの問合せ

mysql> select * from books;
+---------+------------------------+--------+
| book_id | title                  | status |
+---------+------------------------+--------+
|     100 | Heart of Darkness      |      0 |
|     101 | The Catcher of the Rye |      1 |
|     102 | My Antonia             |      0 |
+---------+------------------------+--------+

WHERE句

mysql> select * from books where status = 1;
+---------+------------------------+--------+
| book_id | title                  | status |
+---------+------------------------+--------+
|     101 | The Catcher of the Rye |      1 |
+---------+------------------------+--------+

各レコードを複数行に分けて表示

mysql> select * from books where status = 0 \G
*************************** 1. row ***************************
book_id: 100
  title: Heart of Darkness
 status: 0
*************************** 2. row ***************************
book_id: 102
  title: My Antonia
 status: 0

データの更新

mysql> update books set status = 1 where book_id = 102;

複数のフィールドを同時に更新

mysql> update books
    -> set title = 'The Catcher in the Rye', status = 0
    -> where book_id = 101;

3.3.3もっと複雑に
別のテーブルの作成

mysql> create table status_names (status_id int, status_name char(8));

mysql> insert into status_names values(0, 'Inactive'), (1, 'Active');

mysql> select * from status_names;
+-----------+-------------+
| status_id | status_name |
+-----------+-------------+
|         0 | Inactive    |
|         1 | Active      |
+-----------+-------------+

テーブル接続

mysql> select book_id, title, status_name
    -> from books join status_names
    -> where status = status_id;
+---------+------------------------+-------------+
| book_id | title                  | status_name |
+---------+------------------------+-------------+
|     100 | Heart of Darkness      | Inactive    |
|     101 | The Catcher in the Rye | Inactive    |
|     102 | My Antonia             | Active      |
+---------+------------------------+-------------+