[[mysql]]
[[マニュアル:https://dev.mysql.com/doc/refman/5.6/ja/]]
[[【MySQL, SQL】データベースを扱う基本SQL一覧:https://qiita.com/knife0125/items/bb095a85d1a5d3c8f706]]
[root@pc0700-vm wp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select Host, User from mysql.user;
+-----------+------------------+
| Host | User |
+-----------+------------------+
| localhost | admin |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | phpmyadmin |
| localhost | redmine |
| localhost | root |
| localhost | wordpress |
+-----------+------------------+
8 rows in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| redmine |
| sys |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| redmine |
| sys |
| test |
| wordpress |
+--------------------+
8 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql>
mysql> create table test (
-> id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> name varchar(64) NOT NULL,
-> address varchar(128) NOT NULL,
-> tel varchar(64) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> show create table test;
..
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`address` varchar(128) NOT NULL,
`tel` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
..
1 row in set (0.00 sec)
mysql> insert into test ( id, name, address, tel) values(1, 'sekigawa', 'kanagawa', '0123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+----+----------+----------+------+
| id | name | address | tel |
+----+----------+----------+------+
| 1 | sekigawa | kanagawa | 0123 |
+----+----------+----------+------+
1 row in set (0.00 sec)
mysql>
mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| wordpress |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> show tables;
+-----------------------+
| Tables_in_wordpress |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)
mysql>
mysql> show create table wp_options\G
*************************** 1. row ***************************
Table: wp_options
Create Table: CREATE TABLE `wp_options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
`autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`),
KEY `autoload` (`autoload`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
1 row in set (0.00 sec)
mysql>
mysql> select * from wp_options limit 5;
+-----------+--------------------+-----------------------------+----------+
| option_id | option_name | option_value | autoload |
+-----------+--------------------+-----------------------------+----------+
| 1 | siteurl | http://192.168.0.38/wp | yes |
| 2 | home | http://192.168.0.38/wp | yes |
| 3 | blogname | mpiano | yes |
| 4 | blogdescription | Just another WordPress site | yes |
| 5 | users_can_register | 0 | yes |
+-----------+--------------------+-----------------------------+----------+
5 rows in set (0.00 sec)
mysql>
mysql> select option_value from wp_options limit 5;
+-----------------------------+
| option_value |
+-----------------------------+
| http://192.168.0.38/wp |
| http://192.168.0.38/wp |
| mpiano |
| Just another WordPress site |
| 0 |
+-----------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> select option_value from wp_options\G
*************************** 1. row ***************************
option_value: http://192.168.0.38/wp
*************************** 2. row ***************************
option_value: http://192.168.0.38/wp
*************************** 3. row ***************************
option_value: mpiano
*************************** 4. row ***************************
option_value: Just another WordPress site
*************************** 5. row ***************************
option_value: 0
mysql>
mysql> SELECT 10 * 8 + 4 FROM DUAL;
+------------+
| 10 * 8 + 4 |
+------------+
| 84 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> update wp_options set option_value='http://192.168.0.41/wp' where option_id='1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select option_value from wp_options limit 5;
+-----------------------------+
| option_value |
+-----------------------------+
| http://192.168.0.41/wp |
| http://192.168.0.38/wp |
| mpiano |
| Just another WordPress site |
| 0 |
+-----------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> GRANT ALL ON *.* TO admin@localhost;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> SHOW COLUMNS FROM articles;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| body | text | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
