語句ログ - ニュース、プログラミング、システムトレードの用語解説

MySQLのINSERT ... SELECT文でLIMIT句を使用して上位3件のレコードをテーブルに追加する

Fri, 30 Sep 2016 15:29:37 +0900

MySQLでは、INSERT ... SELECTを使用することで1つまたは複数のテーブルからレコードをテーブルに追加できます。
また、SELECTでLIMIT句とORDER BYを使用して上位n件のレコードのみテーブルに追加することもできます。

構文：
INSERT
INTO テーブル
[(カラム, ...)]
SELECT ...

追加前にORDER BYで指定した方法で並べ替えられ、LIMIT句で指定した行数のレコードが追加されます。

例えば、データベースtestのsalesテーブルに10件のレコードが格納されていて、priceの値が低い順に3件のレコードをjunkテーブルに追加する場合は以下のSQL文になります。

INSERT INTO test.junk (product,price) SELECT product,price FROM test.sales ORDER BY price Limit 3;

実際に実行してみると以下のような結果になります。

salesテーブルのデータ：

mysql> select * from test.sales order by price;
+----------+-------------+---------+----------+
| ordernum | product     | price   | quantity |
+----------+-------------+---------+----------+
|        3 | WII         |    4500 | 2        |
|        5 | PS3         |   10000 | 4        |
|        8 | G-SHOCK     |   12000 | 9        |
|        9 | EXILIM      |   14000 | 7        |
|        1 | PS4         |   20000 | 1        |
|        6 | COOLPIX     |   23000 | 1        |
|        7 | amazon fire |   25000 | 6        |
|       10 | GoPro       |   27000 | 3        |
|        4 | iPhone SE   |   50000 | 4        |
|        2 | iPhone 6    | 1000000 | 2        |
+----------+-------------+---------+----------+
10 rows in set (0.00 sec)

実行前のjunkテーブルのデータ：

mysql> select * from test.junk;
Empty set (0.00 sec)

INSERT ... SELECT文を実行：

mysql> INSERT INTO test.junk (product,price) SELECT product,price FROM test.sales ORDER BY price Limit 3;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

実行後のjunkテーブルのデータ：

mysql> select * from test.junk;
+---------+-------+
| product | price |
+---------+-------+
| WII     |  4500 |
| PS3     | 10000 |
| G-SHOCK | 12000 |
+---------+-------+
3 rows in set (0.00 sec)

参考：
MySQL 5.6 リファレンスマニュアル INSERT ... SELECT 構文

Link(3) | Trackback(1) | Comment(4)

キーワード

MySQL 上位n件 UPDATE SQL

語句ログ - ニュース、プログラミング、システムトレードの用語解説 - 日本の注目ニュース、コンピュータープログラミング（C/C++,Perl,PHP,SQL）、システムトレード（FX,CFD,株価指数,先物）に関する語句（Word）を日本語や英語で解説

MySQLのUPDATE文でLIMIT句を使用して上位3件のレコードを更新する

Fri, 30 Sep 2016 11:43:12 +0900

MySQLでは、UPDATEでLIMIT句とORDER BYを使用して上位n件のレコードを更新することができます。

更新前にORDER BYで指定した方法で並べ替えられ（ASCは昇順、DESCは降順、省略した場合は昇順）、LIMIT句で指定した行数のレコードが更新されます。

構文：
UPDATE テーブル
SET カラム１={expr1|DEFAULT} [, カラム２={expr2|DEFAULT}] ...
[WHERE 条件]
ORDER BY カラム [ASC | DESC]
LIMIT 行数;

例えば、データベースtestのsalesテーブルに10件のレコードが格納されていて、priceの値が低い順に3件のレコードのquantityに1を加算する場合は以下のSQL文になります。

UPDATE test.sales SET quantity = quantity + 1 ORDER BY price LIMIT 3;

実際に実行してみると以下のような結果になります。

更新前のデータ：

mysql> select * from test.sales order by price;
+----------+-------------+---------+----------+
| ordernum | product     | price   | quantity |
+----------+-------------+---------+----------+
|        3 | WII         |    4500 | 1        |
|        5 | PS3         |   10000 | 3        |
|        8 | G-SHOCK     |   12000 | 8        |
|        9 | EXILIM      |   14000 | 7        |
|        1 | PS4         |   20000 | 1        |
|        6 | COOLPIX     |   23000 | 1        |
|        7 | amazon fire |   25000 | 6        |
|       10 | GoPro       |   27000 | 3        |
|        4 | iPhone SE   |   50000 | 4        |
|        2 | iPhone 6    | 1000000 | 2        |
+----------+-------------+---------+----------+
10 rows in set (0.00 sec)

strong>UPDATE文を実行：

mysql> UPDATE test.sales SET quantity = quantity + 1 ORDER BY price LIMIT 3;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3 Changed: 3 Warnings: 0

更新後のデータ：

mysql> select * from test.sales order by price;
+----------+-------------+---------+----------+
| ordernum | product     | price   | quantity |
+----------+-------------+---------+----------+
|        3 | WII         |    4500 | 2        |
|        5 | PS3         |   10000 | 4        |
|        8 | G-SHOCK     |   12000 | 9        |
|        9 | EXILIM      |   14000 | 7        |
|        1 | PS4         |   20000 | 1        |
|        6 | COOLPIX     |   23000 | 1        |
|        7 | amazon fire |   25000 | 6        |
|       10 | GoPro       |   27000 | 3        |
|        4 | iPhone SE   |   50000 | 4        |
|        2 | iPhone 6    | 1000000 | 2        |
+----------+-------------+---------+----------+
10 rows in set (0.00 sec)

上位3件のレコードだけquantityが1加算されていることがわかると思います。

参考：
MySQLリファレンスマニュアル 13.2.11 UPDATE 構文

Link(3) | Trackback(1) | Comment(0)

キーワード

MySQL 上位n件 UPDATE

MySQLのDELETE文でLIMIT句を使用して上位3件のレコードを削除する

Thu, 12 May 2016 11:11:37 +0900

MySQLでは、DELETE文でLIMIT句とORDER BYを使用して上位n件のレコードを削除することができます。

削除前にORDER BYで指定した方法で並べ替えられ（ASCは昇順、DESCは降順、省略した場合は昇順）、LIMIT句で指定した行数のレコードが削除されます。

構文：
DELETE FROM テーブル [WHERE 条件]
ORDER BY カラム [ASC | DESC]
LIMIT 行数;

例えば、データベースtestのsalesテーブルに10件のレコードが格納されていて、priceの値が低い順に3件のレコードを削除する場合は以下のSQL文になります。

DELETE FROM test.sales ORDER BY price LIMIT 3;

実際に実行してみると以下のような結果になります。

削除前のデータ：

mysql> select * from test.sales order by price;
+----------+-------------+---------+----------+
| ordernum | product     | price   | quantity |
+----------+-------------+---------+----------+
|        3 | WII         |    4500 | 1        |
|        5 | PS3         |   10000 | 3        |
|        8 | G-SHOCK     |   12000 | 8        |
|        9 | EXILIM      |   14000 | 7        |
|        1 | PS4         |   20000 | 1        |
|        6 | COOLPIX     |   23000 | 1        |
|        7 | amazon fire |   25000 | 6        |
|       10 | GoPro       |   27000 | 3        |
|        4 | iPhone SE   |   50000 | 4        |
|        2 | iPhone 6    | 1000000 | 2        |
+----------+-------------+---------+----------+
10 rows in set (0.01 sec)

DELETE文を実行：

mysql> DELETE FROM test.sales ORDER BY price LIMIT 3;
Query OK, 3 rows affected (0.08 sec)

削除後のデータ：

mysql> select * from test.sales order by price;
+----------+-------------+---------+----------+
| ordernum | product     | price   | quantity |
+----------+-------------+---------+----------+
|        9 | EXILIM      |   14000 | 7        |
|        1 | PS4         |   20000 | 1        |
|        6 | COOLPIX     |   23000 | 1        |
|        7 | amazon fire |   25000 | 6        |
|       10 | GoPro       |   27000 | 3        |
|        4 | iPhone SE   |   50000 | 4        |
|        2 | iPhone 6    | 1000000 | 2        |
+----------+-------------+---------+----------+
7 rows in set (0.00 sec)

Link(4) | Trackback(1) | Comment(0)

キーワード

3件上位 レコード MySQL LIMIT DELETE

SQLiteのSELECT文でn件目～m件目までのデータを取得する

Sat, 30 Apr 2016 17:11:59 +0900

SQLiteのSELECT文でn件目～m件目までのデータを取得する場合も、上位n件の場合と同様にLIMIT句を使用しますが、開始位置の指定が必要になります。データは開始位置で指定した値の次のレコードから取得されます。

構文：
SELECT カラム名, ... FROM テーブル名 LIMIT 開始位置, 行数;

または

SELECT カラム名, ... FROM テーブル名 LIMIT 行数 OFFSET 開始位置;

例えば、ordernum, product, price, quantityの4つのカラムを持つsalesテーブルに10件のレコードが存在していて、priceの値が小さい順に3件目から7件目まで5件のレコードを取得する場合のSELECT文は以下のようになります。

SELECT * FROM sales ORDER BY price LIMIT 2, 5;

または

SELECT * FROM sales ORDER BY price LIMIT 5 OFFSET 2;

このSELECT文を実行すると先頭から2件のレコードは取得されずに、3件目から7件目までの5件分のレコードが取得されます。

LIMIT句を使用しない場合の実行結果：

ordernum, product, price, quantity
3, WII, 4500, 1
5, PS3, 10000, 3
8, G-SHOCK, 12000, 8
9, EXILIM, 14000, 7
1, PS4, 20000, 1
6, COOLPIX, 23000, 1
7, amazon fire, 25000, 6
10, GoPro, 27000, 3
4, iPhone SE, 50000, 4
2, iPhone 6, 1000000, 2

LIMIT句に「LIMIT 5 OFFSET 2」を指定した場合の実行結果：

ordernum, product, price, quantity
8, G-SHOCK, 12000, 8
9, EXILIM, 14000, 7
1, PS4, 20000, 1
6, COOLPIX, 23000, 1
7, amazon fire, 25000, 6

SQL ゼロからはじめるデータベース操作
翔泳社

amazon.co.jpのカスタマーレビューを見る
powered by amalink

Link(3) | Trackback(0) | Comment(0)

キーワード

SQL select SQLite 上位 レコード n件 m件

SQLiteのSELECT文で上位 (TOP) n件のデータを取得する

Sat, 30 Apr 2016 16:43:14 +0900

SQLiteでは、AccessやMicrosoft SQL ServerのようにTOP句を使用して上位n件のデータを取得することはできません。SQLiteのSELECT文で上位n件のデータを取得する場合はLIMIT句を使用します。

構文：
SELECT カラム名, ... FROM テーブル名 LIMIT 行数;

例えば、ordernum, product, price, quantityの4つのカラムを持つsalesテーブルに10件のレコードが存在していて、priceの値が小さい順に3件取得する場合のSELECT文は以下のようになります。

SELECT * FROM sales ORDER BY price LIMIT 3;

LIMIT句を使用しない場合の実行結果：

ordernum, product, price, quantity
3, WII, 4500, 1
5, PS3, 10000, 3
8, G-SHOCK, 12000, 8
9, EXILIM, 14000, 7
1, PS4, 20000, 1
6, COOLPIX, 23000, 1
7, amazon fire, 25000, 6
10, GoPro, 27000, 3
4, iPhone SE, 50000, 4
2, iPhone 6, 1000000, 2

LIMIT句に3を指定した場合の実行結果：

ordernum, product, price, quantity
3, WII, 4500, 1
5, PS3, 10000, 3
8, G-SHOCK, 12000, 8

SQLite ポケットリファレンス
技術評論社著者：五十嵐貴之

amazon.co.jpのカスタマーレビューを見る
powered by amalink

Link(3) | Trackback(1) | Comment(0)

キーワード

SQL 先頭 10件 SQLite 上位 レコード TOP

PHPからXMLを出力するサンプルコード

Thu, 21 Feb 2013 20:19:18 +0900

PHPでXMLを扱う場合、SimpleXMLやDOMを使うというのが一般的なようですが、今回はそれらは使わずに単に出力したいXML形式の文字列を生成してそれ出力するというサンプルコードです。

サンプルコードの処理内容は、MySQLのテーブルからスレッドIDを取得してURLを生成し、Googleのウェブマスターツールに登録するためにSitemapプロトコルに対応したXMLを出力するというものです。

<?php
//------------------------------------------------------------------------------
//    sitemap.php
//------------------------------------------------------------------------------

mb_language("Japanese");
mb_internal_encoding("UTF-8");

require_once('./config.php');
require_once('./util.php');

$siteurl = SITE_URL;
$itemlist = <<<EOL
   <url>
    <loc>$siteurl</loc>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
   </url>
EOL;

// DB接続
$dbh =_db_connect();

$sql = "SELECT threadid FROM thread WHERE status = 1 ORDER BY threadid";

$result = mysql_query($sql, $dbh);
if (!$result) {
    _error("SQL Error: " . mysql_error() . "\n");
}

while ($row = mysql_fetch_assoc($result)) {
    $itemurl = SITE_URL . '?thread=' . $row['threadid'];
    $itemlist .= <<<EOL
   <url>
    <loc>$itemurl</loc>
    <changefreq>daily</changefreq>
    <priority>0.7</priority>
   </url>
EOL;

}
mysql_free_result($result);

$sitemap = <<<EOL
<?xml version="1.0" encoding="UTF-8"?>
  <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
   $itemlist
  </urlset>
EOL;

mysql_close($dbh);

header("Content-Type: text/xml; charset=utf-8");
echo $sitemap;

exit();

?>

上記のようなベタなやり方でなく、SimpleXMLやDOMを使いたいという場合は、以下のページが参考になると思います。

PHP 開発者のための XML: 第 1 回 PHP での XML を 15 分で学ぶ

プロになるための PHPプログラミング入門
技術評論社著者：星野香保子

amazon.co.jpのカスタマーレビューを見る
powered by amalink

Link(1) | Trackback(0) | Comment(0)

キーワード

PHP XML サンプル コード

PHPでMySQLに接続して、SELECTでデータを取得するサンプルプログラム

Thu, 19 Aug 2010 20:04:16 +0900

PHPのプログラムからMySQLに接続し、SELECTでデータを取得する処理のサンプルプログラムです。

あらかじめphpMyAdminを使用してMySQLにテータベース「testdb」を作成し、以下のSQLを実行しテーブル「users」を作成、3件のレコードを登録しておきます。

CREATE TABLE `users` (  `userid` varchar(100) COLLATE utf8_bin NOT NULL,  `password` varchar(250) COLLATE utf8_bin NOT NULL,  `status` int(11) NOT NULL,  `auth` int(11) NOT NULL,  `username` varchar(250) COLLATE utf8_bin NOT NULL,  `address` varchar(250) COLLATE utf8_bin NOT NULL,  `mailaddr` varchar(100) COLLATE utf8_bin NOT NULL,  `hpurl` varchar(250) COLLATE utf8_bin NOT NULL,  `widgets` text COLLATE utf8_bin NOT NULL,  `createdate` datetime NOT NULL,  `lastupdate` datetime NOT NULL,  PRIMARY KEY (`userid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('admin','1234','1','0','webmaster','',  webmaster100@59log.com','http://59log.com/','',now(),now());insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('test1','5678','1','1','user 1','',  'test1@59log.com','http://59log.com/','',now(),now());insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('test2','abcd','1','1','user 2','',  'test2@59log.com','http://59log.com/','',now(),now());

以下のPHPプログラム(文字コードはUTF-8)では、テーブル「users」からレコードを全て取得しHTMLページとして出力しています。

--- mysql_connect.php ---

<html><head><title>PHPでMySQLに接続するサンプルプログラム</title></head><body><?php$domain = "localhost";$user = "test";$password = "abcd12";$dbname = "testdb";// MySQLに接続$dbconnect = mysql_connect($domain, $user, $password)             or die(mysql_error());mysql_select_db($dbname, $dbconnect)             or die(mysql_error());$sql = "SELECT * FROM users";// クエリの実行$result = mysql_query($sql, $dbconnect);if (!$result) {    $message  = 'Invalid query: ' . mysql_error() . "\n";    $message .= 'Whole query: ' . $query;    die($message);}// 結果セットの行数を取得$rows = mysql_num_rows($result);echo $rows . '件のレコード<br />';echo '<br />';// 結果セットを表示while ($row = mysql_fetch_assoc($result)) {    echo $row['userid'] . '<br />';    echo $row['username'] . '<br />';    echo $row['mailaddr'] . '<br />';    echo $row['hpurl'] . '<br />';    echo '<br />';}mysql_free_result($result);mysql_close($dbconnect);?></body></html>

Link(6) | Trackback(0) | Comment(0)

キーワード

PHP MySQL サンプルプログラム

[Perl]DBI経由でMySQLに接続して、SELECT文でデータを取得するサンプルプログラム

Mon, 26 Jul 2010 00:12:18 +0900

PerlのプログラムからDBI経由でMySQLに接続し、SELECT文でデータを取得する処理のサンプルプログラムです。

あらかじめphpMyAdminを使用してMySQLにテータベース「testdb」を作成し、以下のSQLを実行しテーブル「users」を作成、3件のレコードを登録しておきます。

CREATE TABLE `users` (  `userid` varchar(100) COLLATE utf8_bin NOT NULL,  `password` varchar(250) COLLATE utf8_bin NOT NULL,  `status` int(11) NOT NULL,  `auth` int(11) NOT NULL,  `username` varchar(250) COLLATE utf8_bin NOT NULL,  `address` varchar(250) COLLATE utf8_bin NOT NULL,  `mailaddr` varchar(100) COLLATE utf8_bin NOT NULL,  `hpurl` varchar(250) COLLATE utf8_bin NOT NULL,  `widgets` text COLLATE utf8_bin NOT NULL,  `createdate` datetime NOT NULL,  `lastupdate` datetime NOT NULL,  PRIMARY KEY (`userid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('admin','1234','1','0','webmaster','',  webmaster100@59log.com','http://59log.com/','',now(),now());insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('test1','5678','1','1','user 1','',  'test1@59log.com','http://59log.com/','',now(),now());insert into `users` (`userid`,`password`,`status`,`auth`, `username`,`address`,`mailaddr`,`hpurl`,`widgets`, `createdate`,`lastupdate`)  values('test2','abcd','1','1','user 2','',  'test2@59log.com','http://59log.com/','',now(),now());

以下のPerlプログラムでは、テーブル「users」からステータスが1のレコードを全て取得、一旦配列に格納してから標準出力に出力しています。

検索条件の設定にはプレースホルダを使用し、DB処理でエラーが発生した場合は、例外処理に飛んでエラーの内容が出力されるように、RaiseErrorに1を設定しています。

--- mysql_select.pl ---

#!/usr/bin/perluse strict;use warnings;use DBI;my $data_source = "DBI:mysql:testdb"; # 接続先はtestdbmy $username = "test"; # データベースへのアクセス権限を持つユーザーを指定my $password = "abcd"; # そのパスワードmy $status = 1;my @recs = ();eval {    my $dbh = DBI->connect($data_source, $username, $password,                          {RaiseError => 1, PrintError => 0});    my $sql  = "select * from users where status = ?";    my $sth = $dbh->prepare($sql);    $sth->execute($status);    while (my @rec = $sth->fetchrow_array) {        push @recs, [@rec];    }    $sth->finish;    $dbh->disconnect;};if ($@) {    print "Error : $@\n";}foreach my $rec (@recs) {    print join(",", @{$rec}), "\n";}

Link(6) | Trackback(0) | Comment(0)

キーワード

Perl DBI MySQL サンプル プログラム

プログラマのためのSQL 第2版ジョー・セルコ著

Thu, 09 Apr 2009 08:29:34 +0900

SQLの実務経験のあるプログラマを対象として、トピックごとにSQLプログラミングの基本的な考え方から、データベースの設計、数値・文字・時間といったデータ型、DELETE・INSERT・UPDATEといったテーブル操作、SELECT文、述語や論理演算、グループ化や集計関数、集合演算、部分集合、クエリーの最適化、データ設計などが詳しく解説されているSQLプログラミングのバイブルともいえる書籍。高度なSQLプログラミングテクニックを習得したい人におすすめです。

プログラマのためのSQL 第2版
ピアソンエデュケーション著者：ジョーセルコ価格：4,725円評価：★★★★★

Smarty ★★★★★

キーワード

SQL プログラミング 解説書 テクニック

SQLのSELECT文でn件目からm件目までのレコードを取得する方法（Oracle、SQL Server、MySQL、PostgreSQL）

Mon, 21 Mar 2005 16:41:09 +0900

SQLのSELECT文を使用して先頭からn件のレコードを取得するだけでなく、n件目からm件目までのレコードを取得することも可能です。

Oracle8i R8.1.5 以降
Oracle8i R8.1.5 以降では、Table1のField1の昇順に11件目から15件目までの結果セットを取得する場合は以下のSQL文で取得できます。

SELECT *
FROM (SELECT *, ROWNUM SEQNUM
FROM (SELECT * FROM Table1 ORDER BY Field1) A) B
WHERE SEQNUM > 10 AND SEQNUM <= 15;

Microsoft SQL Server 7.0 以降
Microsoft SQL Server 7.0 以降ではTOPを使用して、以下のSQL文で Table1の11件目から15件目までの結果セットを取得できます。

SELECT TOP 5 * FROM Table1 A
WHERE NOT EXISTS
(SELECT * FROM (SELECT TOP 10 * FROM Table1) AS B
WHERE A.Field1 = B.Field1)
ORDER BY A.Field1

その他のRDB
また、上記以外のRDBでも以下のSQL文で同様の結果が得られます。

SELECT DISTINCT COUNT(*), A.Field1, A.Field2, A.Field3
FROM Table1 A, Table1 B
WHERE (A.Field1 >= B.Field1)
GROUP BY A.Field1, A.Field2, A.Field3
HAVING COUNT(*) > 10 AND COUNT(*) <= 15

Link(11) | Trackback(1) | Comment(0)

キーワード

SQL n件 Oracle

語句ログ - ニュース、プログラミング、システムトレードの用語解説

MySQLのINSERT ... SELECT文でLIMIT句を使用して上位3件のレコードをテーブルに追加する

キーワード

MySQLのUPDATE文でLIMIT句を使用して上位3件のレコードを更新する

キーワード

MySQLのDELETE文でLIMIT句を使用して上位3件のレコードを削除する

キーワード

SQLiteのSELECT文でn件目～m件目までのデータを取得する

キーワード

SQLiteのSELECT文で上位 (TOP) n件のデータを取得する

キーワード

PHPからXMLを出力するサンプルコード

キーワード

PHPでMySQLに接続して、SELECTでデータを取得するサンプルプログラム

キーワード

[Perl]DBI経由でMySQLに接続して、SELECT文でデータを取得するサンプルプログラム

キーワード

プログラマのためのSQL 第2版 ジョー・セルコ著

キーワード

SQLのSELECT文でn件目からm件目までのレコードを取得する方法（Oracle、SQL Server、MySQL、PostgreSQL）

キーワード

プログラマのためのSQL 第2版ジョー・セルコ著