データベースの変更 : use [ データベース名 ]
mysql> use mysql 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
データベース一覧 : show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
テーブル一覧 : show tables;
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
テーブルスキーマ (1) : show create table [ テーブル名 ]
mysql> show create table time_zone;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_zone | CREATE TABLE `time_zone` (
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
PRIMARY KEY (`Time_zone_id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones' |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
テーブルスキーマ (2) : desc [ テーブル名 ]
mysql> desc m_todou; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | CD | tinyint(4) | NO | PRI | NULL | | | K_NAME | varchar(8) | NO | | NULL | | | H_NAME | varchar(12) | NO | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
テーブルスキーマ (3) : show full columns from [ テーブル名 ]
mysql> show full columns from m_todou; +--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | CD | tinyint(4) | NULL | NO | PRI | NULL | | select,insert,update,references | | | K_NAME | varchar(8) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | | H_NAME | varchar(12) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | +--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 3 rows in set (0.00 sec)
ストアドスキーマ : show create procedure [ ストアドプロシージャー名 ]
mysql> show create procedure test \G;
*************************** 1. row ***************************
Procedure: test
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
IN `tstvalue` INT
)
COMMENT 'nothing to reffer'
BEGIN
SELECT tstvalue;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
ファンクションスキーマ : show create function [ ファンクション名 ]
mysql> show create function fc_csv_idxtxt \G;
*************************** 1. row ***************************
Function: fc_csv_idxtxt
sql_mode: NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `fc_csv_idxtxt`(
`src` VARCHAR(9000),
`idx` smallint
) RETURNS varchar(9000) CHARSET utf8
COMMENT 'CSV列指定位置取得'
begin
declare rtntxtcsv varchar(9000) default '';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(src, ',', idx), ',', -1) INTO rtntxtcsv;
return rtntxtcsv;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ユーザー一覧 : select host,user,plugin from mysql.user;
mysql> select host,user,plugin from mysql.user limit 0, 3; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | +-----------+------------------+-----------------------+ 3 rows in set (0.00 sec)
終了 : exit or quit or \q
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 592 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> exit Bye [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 593 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> quit Bye [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 596 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> \q Bye [root@localhost ~]#