月: 2019年1月

SELECT ROW_COUNT();

[ 利用例 ]

更新結果をログテーブルに追加する

insert into test (
field1, field2
)
select * from test2;

insert into log_updt (NM_TBL, CNT_AFFECT) VALUES (
'test', (select ROW_COUNT())
); 

データベースの変更 : 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 ~]# 

(SELECT @num := -1) as dmy で連番用の仮想テーブルを記述、
@num := @num + 1 AS ID のような列を記述します。

mysql> SELECT
    ->   @num := @num + 1 AS ID,
    ->   CD,
    ->   K_NAME, H_NAME
    -> FROM
    ->   (SELECT @num := -1) as dmy,
    ->   m_todou
    -> LIMIT 0, 5;
+------+----+-----------+--------------------+
| ID   | CD | K_NAME    | H_NAME             |
+------+----+-----------+--------------------+
|    0 |  1 | 北海道    | ほっかいどう       |
|    1 |  2 | 青森県    | あおもりけん       |
|    2 |  3 | 岩手県    | いわてけん         |
|    3 |  4 | 宮城県    | みやぎけん         |
|    4 |  5 | 秋田県    | あきたけん         |
+------+----+-----------+--------------------+
5 rows in set (0.00 sec)

mysql> show create table m_todou;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| m_todou | CREATE TABLE `m_todou` (
  `CD` tinyint(4) NOT NULL,
  `K_NAME` varchar(8) NOT NULL,
  `H_NAME` varchar(12) NOT NULL,
  PRIMARY KEY (`CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='都道府県マスタ'        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

SQL 第2版 ゼロからはじめるデータベース操作【電子書籍】[ ミック ] 価格：2068円 (2022/12/27時点) 楽天で購入

【中古】実践ハイパフォ-マンスMySQL 第3版/オライリ-・ジャパン/バロン・シュワルツ（大型本） 価格：4770円（税込、送料無料) (2023/1/29時点) 楽天で購入