Database – ページ 5

sqlite3 基本コマンドのまとめ

投稿日: 2019-01-08 投稿者: Hisa

.table : テーブルの一覧


sqlite> .table
log_apacheaccess  log_df            log_jstat         log_rsyncbackup 
log_apachests     log_dircntsz      log_loadave       log_rxtx        
log_cpu           log_free          log_ping          log_videoupload

.schema : 指定したテーブルの show create table


sqlite> .schema log_free
CREATE TABLE "log_free" ("tm" timestamp NOT NULL ,"used" integer NOT NULL ,"free" integer NOT NULL );
CREATE UNIQUE INDEX log_free_tm on log_free(tm);

.exit : 終了

.quit : 終了

.show Shell設定状態表示


sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 
sqlite>

.database : 使用中のデータベースを表示


seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/sqlite/logs

.help : ヘルプを表示


.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off

詳しくは : https://qiita.com/sotetsuk/items/cd2aeae4ba7e72faad47

[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]

Learn SQLite in 24 Hours【電子書籍】[ Alex Nordeen ]
価格：1334円 (2023/1/11時点)

楽天で購入

SQLポケットリファレンス改訂第4版（Pocket　reference） [ 朝井淳 ]
価格：2178円（税込、送料無料) (2023/1/29時点)

楽天で購入

MySQL の特徴 (他DBと比べて)

投稿日: 2019-01-05 投稿者: Hisa

経験上の羅列です。

1) || での連結は出来ない (CONCAT関数で同じことは出来る)

=> sql_modeの設定で出来ることがわかりました。http://wp.saoline.co/wordpress/?p=1305
2) WITHは5.7までないので、導出テーブルが入り組んだ場合、わかりにくい
3) 関数は普通に揃っている。(なければUDFを作ればよい)
4) インデックスが1テーブル1個しか効かない。(複合インデックスで対応、列の順番がパフォーマンスに影響)
5) 導出テーブルでLIMITが使えない
6) ストアドの結果を導出テーブルに出来ない
7) ストレージエンジンを選べるので。用途に合わせて最適に出来る
8) テーブル名の大文字小文字区別あり
9) 日付条件の問い合わせで数値が使える (20190101, ‘2019-01-01’ は同じ)
10) DUAL表の指定は不要
12) CONSTRAINTがない (値制約はトリガーで判断し、エラーを生成して処理しないことにすればもどきなことは出来る)
13) ユーザー認証に必ずホスト名(IP)が必要
14) 列毎にテーブル定義とは別のキャラクタセットが指定出来る
15) バルクインサートが超高速
16) SELECT検索結果をCSVに出力できる
17) JSONが格納できる(JSON型, 5.7から)
18) 日時の型が豊富で便利 (DATE, DATETIME, TIMESTAMP, TIME, YEAR)
19) innodb (デフォルト)の場合、パフォーマンス確保の為、データベースのサイズと同じ位のメモリをバッファプールで設定するのが望ましい
20) ダンプバックアップの種類が多く、便利

詳しくは、Oracleの公式ページを見て下さい。

http://otndnld.oracle.co.jp/document/products/sql/12/doc_cd/doc/appdev.120/E06020-01/oracle_mysql_compared.htm

これも詳しい : https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html

MySQL カレンダーストアド応用編

投稿日: 2019-01-05 投稿者: Hisa

基本編で紹介したものに、データをUDFで拾ってきて、CONCATしてます。

[ 実行結果 ]

[ コード ]


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar_vslccpp`(
	IN `ym` INT,
	IN `vslnm` VARCHAR(36),
	IN `idmultiple` SMALLINT

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '船寄港国単月カレンダー (使用)'
BEGIN	

DECLARE ymprev INT;
DECLARE ymnext INT;

SET ymprev = ym - 1;
SET ymnext = ym + 1;

IF (ymprev % 100 = 0) THEN
  SET ymprev = ymprev - 88;  
END IF;
IF (ymnext % 100 = 13) THEN
  SET ymnext = ymnext + 88;  
END IF;



SELECT
  YWK * idmultiple AS id,
  YWK, 
	
	
	MON, 
	TUE, WED, THU, FRI, SAT, 
	SUN,
	
	DTMIN, DTMAX,
	
	YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
	YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
	
FROM
	(
	SELECT
	  
  	YWK,
  	MIN(YM) AS YMMIN,
  	MAX(YM) AS YMMAX,
  	MAX(SUN) AS SUN,
  	MAX(MON) AS MON,
  	MAX(TUE) AS TUE,
  	MAX(WED) AS WED,
  	MAX(THU) AS THU,
  	MAX(FRI) AS FRI,
  	MAX(SAT) AS SAT,
  	MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
	FROM
		(
		SELECT
  		dt, 
  
  		CASE WHEN (WEEKOFYEAR(dt) = 53) THEN 
			  (YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
	  	
	  		ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)  
	  	  
			END AS YWK,
	
			YEAR(dt) * 100 + MONTH(dt) AS YM,
  
  		CASE WHEN (DAYOFWEEK(dt) = 1) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt)) 
			ELSE '' END AS SUN,
			
			CASE WHEN (DAYOFWEEK(dt) = 2) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS MON,
			
			CASE WHEN (DAYOFWEEK(dt) = 3) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS TUE,
			
			CASE WHEN (DAYOFWEEK(dt) = 4) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS WED,
			
			CASE WHEN (DAYOFWEEK(dt) = 5) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS THU,
			
			CASE WHEN (DAYOFWEEK(dt) = 6) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS FRI,
			
			CASE WHEN (DAYOFWEEK(dt) = 7) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS SAT
			
		FROM
  		bimpsch.m_date
		WHERE
			YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
			
		) a
	GROUP BY
  	YWK
	) b	
WHERE
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR 
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
	
	
ORDER BY
  DTMIN;	
	
	
END

[ 利用例 ]

船の寄港国、ポップアップで港が現れる

右クリックコンテキストメニューを配置 => 列値、行IDとかで更新処理できる

[ jqGrid colModel例 ]


          colNames: ['月', '火', '水', '木', '金', '土', '日', '', '', ''],
          colModel: [
            {name: 'mon', width: '140px', classes: 'caltd calentd'},
            {name: 'tue', width: '140px', classes: 'caltd'},
            {name: 'wed', width: '140px', classes: 'caltd'},
            {name: 'thu', width: '140px', classes: 'caltd'},
            {name: 'fri', width: '140px', classes: 'caltd'},
            {name: 'sat', width: '140px', classes: 'caltd'},
            {name: 'sun', width: '140px', classes: 'caltd'},
            {name: 'id', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmin', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmax', hidden: true,
              editrules: {edithidden: true}
            },
          ],

MySQL カレンダーストアド基本編

投稿日: 2019-01-05 投稿者: Hisa

グリッドでカレンダーの配置が必要になり、いろいろ応用が効いて重宝してます。

[ 実行と結果 ]

[ コード ]


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar`(
	IN `ym` INT


)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '単月カレンダー'
BEGIN	

DECLARE ymprev INT;
DECLARE ymnext INT;

SET ymprev = ym - 1;
SET ymnext = ym + 1;

IF (ymprev % 100 = 0) THEN
  SET ymprev = ymprev - 88;  
END IF;
IF (ymnext % 100 = 13) THEN
  SET ymnext = ymnext + 88;  
END IF;



SELECT
  YWK AS id,
  YWK, 
	
	
	MON, 
	TUE, WED, THU, FRI, SAT, 
	SUN,
	
	DTMIN, DTMAX,
	
	YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
	YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
	
FROM
	(
	SELECT
	  
  	YWK,
  	MIN(YM) AS YMMIN,
  	MAX(YM) AS YMMAX,
  	MAX(SUN) AS SUN,
  	MAX(MON) AS MON,
  	MAX(TUE) AS TUE,
  	MAX(WED) AS WED,
  	MAX(THU) AS THU,
  	MAX(FRI) AS FRI,
  	MAX(SAT) AS SAT,
  	MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
	FROM
		(
		SELECT
  		dt, 
  
  		CASE WHEN (WEEKOFYEAR(dt) = 53) THEN 
			  (YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
	  	
	  		ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)  
	  	  
			END AS YWK,
	
			YEAR(dt) * 100 + MONTH(dt) AS YM,
  
  		CASE WHEN (DAYOFWEEK(dt) = 1) THEN 
			DATE_FORMAT(dt, '%e') 
			ELSE '' END AS SUN,
			
			CASE WHEN (DAYOFWEEK(dt) = 2) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS MON,
			
			CASE WHEN (DAYOFWEEK(dt) = 3) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS TUE,
			
			CASE WHEN (DAYOFWEEK(dt) = 4) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS WED,
			
			CASE WHEN (DAYOFWEEK(dt) = 5) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS THU,
			
			CASE WHEN (DAYOFWEEK(dt) = 6) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS FRI,
			
			CASE WHEN (DAYOFWEEK(dt) = 7) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS SAT
			
		FROM
  		m_date
		WHERE
			YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
			
		) a
	GROUP BY
  	YWK
	) b	
WHERE
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR 
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
	
	
ORDER BY
  DTMIN;	
	
	
END


CREATE TABLE `m_date` (
	`dt` DATE NOT NULL COMMENT '日付',
	`IS_HOLIDAY` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '祝日フラグ',
	PRIMARY KEY (`dt`)
)
COMMENT='連続日付マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

15時間でわかるMySQL集中講座 [ 株式会社ハートビーツ　馬場俊彰 ]

楽天で購入

MySQL ストアドプロシージャーカーソル

投稿日: 2019-01-05 投稿者: Hisa

骨だけで使ってません。カーソルのテーブルを適時変更してもらえば、コピペして使えます。


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_loopupdt_shporder_deliv`(
	IN `ivtrydt` DATE,
	IN `sessionid` VARCHAR(70)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '出庫指図在庫引き当て履歴更新店別明細(これは不要なので処理は空 テンプレート用)'
BEGIN	

/* 変数宣言 */
DECLARE noskid INT;
DECLARE noskcdgoods VARCHAR(20);
DECLARE noskcddeliv VARCHAR(8);
DECLARE noskqty INT;

/* カーソル継続判断フラグ */
DECLARE done INT DEFAULT 0;

/* カーソル定義 */
DECLARE nosk CURSOR FOR
SELECT
  ID, CD_GOODS, CD_DELIV, QTY
FROM
	h_shporder
WHERE
  DT_IVTRY = ivtrydt;	

/* 走査継続判断用 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN nosk;

delete from tst where 1 = 1;

REPEAT
	
	/* 変数に格納 */
	FETCH nosk INTO noskid, noskcdgoods, noskcddeliv, noskqty;
	
	/* ここにやりたい処理を入れるとよい */
	/* MySQLではストアドのデバッグは出来ないので、デバッグは一時テーブルとかで行う */
		

UNTIL done END REPEAT;
CLOSE nosk;   /* カーソルのクローズ */

END

MySQL IDEのおすすめ

投稿日: 2019-01-05 投稿者: Hisa

何使ってもよく、好みに左右されると思いますが、

HeidiSQL と MySQL WorkBench をお勧めします。

HeidiSQLの便利なところ

テーブル定義で列のコピーペーストが出来て、他のテーブルで使える
インデックス設定の列追加で、ドラッグドロップが使える
履歴が使いやすく、他に保存する必要なくなる

HeidiSQLの不便なところ

ビューが1行で表示されてしまう

MySQL WorkBenchの便利なところ

EXPLAINがビジュアル

ビューが整形されて表示される

MySQL WorkBenchの不便なところ

日本語化されてない

2024年5月
日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

2024年5月
日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31