MYSQL クエリ文字コード指定

#下記のコマンドを実行し、文字コードを指定
SET CHARACTER SET utf8

MYSQL ダンプ作成

#下記のコマンドを実行し、ダンプを作成
$ mysqldump -h 「サーバ名」 -u 「ユーザ名」 -p 「パスワード」 > dump_20120314.sql

ｐｈｐとmysqlでテーブルの存在確認

<?php
    $host = "localhost";
    $user   = "root";
    $pass = "mysql";
    $db = "test";
 $con = mysql_connect($host,$user,$pass) or die("db接続NG");
 print "db接続OK";
//テーブルチェック関数の呼出
    if(table_exists($db,'item',$con)){
     print "item table hit";
    }else{
     print "item table none";
    }
 mysql_close();


// テーブルの存在チェック関数の定義  start
 function table_exists($db_name,$tbl_name,$db)
 {
  // テーブルリストの取得
  $rs = mysql_query("SHOW TABLES FROM $db_name");
  while($arr_row = mysql_fetch_row($rs)){
    if(in_array($tbl_name,$arr_row)){
      return true;
    }
  }
  return false;
 }
// テーブルの存在チェック関数の定義  end
?>

MYSQL 入門４

-------------------ストアード-----------------
DELIMITER //
CREATE PROCEDURE TEST_1()
BEGIN
SELECT * FROM C1;
END
//
DELIMITER ;

CALL TEST_1;--ストアドの呼出
+----+---------------------+------------------+
| ID | NAME | HOYA_CATEGORY_ID |
+----+---------------------+------------------+
| 1 | BOOK | NULL |
| 2 | Fashion | NULL |
| 3 | electronics | NULL |
| 4 | Picture BOOK | 1 |
| 5 | IT BOOK | 1 |
| 6 | COOKING BOOK | 1 |
| 7 | TV electronics | 3 |
| 8 | Music electronics | 3 |
| 9 | Cooking electronics | 3 |
| 10 | Man Fashion | 2 |
| 11 | WOMAN Fashion | 2 |
| 12 | TEST | 100 |
+----+---------------------+------------------+

------------引数ありのストアド----------------
DELIMITER //
CREATE PROCEDURE TEST_2(OUT o_1 INT)
BEGIN
SELECT COUNT(*) INTO o_1 FROM C1;
END
//


mysql> CALL TEST_2(@i)--ストアドの呼出
-> //
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @i--戻りのOUTを表示
-> //
+------+
| @i |
+------+
| 12 |
+------+

---------ストアードファンクション------------

DELIMITER //
CREATE FUNCTION tEST9()
RETURNS DOUBLE
BEGIN
DECLARE r DOUBLE;
SELECT AVG(ID) INTO r FROM C1;
RETURN r;
END
//

------------ストレージエンジン---------------
/*MyISAM(マイアイサム)　　もっともよく利用される高速エンジン　　トランザクションは利用できない*/

/*InnoDB(イノディービー) トランザクションに対応*/

/*ストレージエンジンの確認*/
| tb1 | CREATE TABLE `tb1` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

/*レコードの確認*/
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test1 |
+------+-------+
1 row in set (0.00 sec)

/*トランザクションのスタート*/
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tb1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
Empty set (0.00 sec)

/*ロールバック*/
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

/*レコード確認*/
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test1 |
+------+-------+

/*--------------ファイルの取り扱い--------------------*/
CREATE TABLE address_zip (
jis varchar(10) NULL,
zip_old varchar(5) NULL,
zip varchar(7) NULL,
addr1_kana varchar(100) NULL,
addr2_kana varchar(100) NULL,
addr3_kana varchar(100) NULL,
addr1 varchar(100) NULL,
addr2 varchar(100) NULL,
addr3 varchar(100) NULL,
c1 int NULL,
c2 int NULL,
c3 int NULL,
c4 int NULL,
c5 int NULL,
c6 int NULL
);


LOAD DATA LOCAL INFILE 'C:/ken_all/KEN_ALL.CSV'
INTO TABLE `address_zip`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';


SELECT * INTO OUTFILE 'C:/DATA.CSV'
FIELDS TERMINATED BY ','
FROM ADDRESS_ZIP;

/*--------------バッチファイル--------------------*/
/*下記test.batで保存し実行するとdataファイルが作成されます*/
mysql test -uroot -pmysql -e "SELECT * INTO OUTFILE 'C:/DATA2.CSV' FIELDS TERMINATED BY ',' FROM ADDRESS_ZIP;"

/*--------------リダイレクト処理--------------------*/
/*下記はｃ直下にlog.txtファイルに実行結果を出力します*/
tee C:/log.txt /*出力開始*/
SELECT * FROM ADDRESS_ZIP LIMIT 5;
notee /*出力終了*/

/*-----------------------------------------------------------*/
/*　　　　　　　　　　バックアップ　　　　　　　　　　　　　　*/
/*-----------------------------------------------------------*/

mysqldump -uroot -pmysql test>C:/back_up.txt;

MYSQL 入門３

-------------LEFT JOIN--------------
mysql> SELECT * FROM USER;
+----+----------+------+---------+---------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   |
+----+----------+------+---------+---------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |
|  2 | KATO     |    4 |   90000 | OUI     |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |
|  5 | MIYAMOTO |    3 |   50000 | FUTUU   |
|  6 | TANAKA   |    4 |   90000 | OUI     |
+----+----------+------+---------+---------+


mysql> SELECT * FROM UNIT;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | SOUMU    |
|  2 | EIGYO    |
|  3 | JIMU     |
|  4 | GIJYUTSU |
+----+----------+
---UNITテーブルのIDにないレコードを追加----
UPDATE USER SET UNIT = '7' WHERE ID = 6;


/*----LEFT JOINでは左のテーブルのレコードは全て表示されます。----*/
mysql> SELECT *
    ->   FROM USER
    -> LEFT JOIN UNIT
    ->   ON USER.UNIT = UNIT.ID ;
+----+----------+------+---------+---------+------+----------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   | ID   | NAME     |
+----+----------+------+---------+---------+------+----------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |    1 | SOUMU    |
|  2 | KATO     |    4 |   90000 | OUI     |    4 | GIJYUTSU |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |    2 | EIGYO    |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |    1 | SOUMU    |
|  5 | MIYAMOTO |    3 |   50000 | FUTUU   |    3 | JIMU     |
|  6 | TANAKA   |    7 |   90000 | OUI     | NULL | NULL     |
+----+----------+------+---------+---------+------+----------+

/*----LEFT RIGHTでは右のテーブルのレコードは全て表示されます。USER.UNIT = UNIT.IDが一致しない
レコードは表示されません----*/
mysql>  SELECT *
    ->    FROM USER
    ->  RIGHT JOIN UNIT
    ->    ON USER.UNIT = UNIT.ID ;
+------+----------+------+---------+---------+----+----------+
| ID   | NAME     | Unit | KOZUKAI | BIKOU   | ID | NAME     |
+------+----------+------+---------+---------+----+----------+
|    1 | ASO      |    1 |   10000 | SUKUNAI |  1 | SOUMU    |
|    4 | SUZUKI   |    1 |   10000 | SUKUNAI |  1 | SOUMU    |
|    3 | YAMADA   |    2 |   10000 | SUKUNAI |  2 | EIGYO    |
|    5 | MIYAMOTO |    3 |   50000 | FUTUU   |  3 | JIMU     |
|    2 | KATO     |    4 |   90000 | OUI     |  4 | GIJYUTSU |
+------+----------+------+---------+---------+----+----------+


-------------------------自己結合----------------------------
/*－－－カテゴリテーブルの作成－－－*/
CREATE TABLE Category (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100),HOYA_CATEGORY_ID INT);

INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('BOOK',NULL),('Fashion',NULL),('electronics',NULL);--親カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('Picture BOOK',1),('IT BOOK',1),('COOKING BOOK',1);--子カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('Man Fashion',2),('WOMAN Fashion',2);--子カテゴリ
INSERT INTO CATEGORY(NAME,HOYA_CATEGORY_ID) VALUES('TV electronics',3),('Music electronics',3),('Cooking electronics',3);--子カテゴリ


mysql> SELECT * FROM CATEGORY;
+----+---------------------+------------------+
| ID | NAME                | HOYA_CATEGORY_ID |
+----+---------------------+------------------+
|  1 | BOOK                |             NULL |
|  2 | Fashion             |             NULL |
|  3 | electronics         |             NULL |
|  4 | Picture BOOK        |                1 |
|  5 | IT BOOK             |                1 |
|  6 | COOKING BOOK        |                1 |
|  7 | TV electronics      |                3 |
|  8 | Music electronics   |                3 |
|  9 | Cooking electronics |                3 |
| 10 | Man Fashion         |                2 |
| 11 | WOMAN Fashion       |                2 |
+----+---------------------+------------------+
--------自己結合によるカテゴリーの表示----------
mysql> SELECT *
    ->   FROM CATEGORY
    -> AS C_1
    ->   JOIN CATEGORY
    -> AS C_2
    -> WHERE C_1.ID = C_2.HOYA_CATEGORY_ID;
+----+-------------+------------------+----+---------------------+------------------+
| ID | NAME        | HOYA_CATEGORY_ID | ID | NAME                | HOYA_CATEGORY_ID |
+----+-------------+------------------+----+---------------------+------------------+
|  1 | BOOK        |             NULL |  4 | Picture BOOK        |                1 |
|  1 | BOOK        |             NULL |  5 | IT BOOK             |                1 |
|  1 | BOOK        |             NULL |  6 | COOKING BOOK        |                1 |
|  3 | electronics |             NULL |  7 | TV electronics      |                3 |
|  3 | electronics |             NULL |  8 | Music electronics   |                3 |
|  3 | electronics |             NULL |  9 | Cooking electronics |                3 |
|  2 | Fashion     |             NULL | 10 | Man Fashion         |                2 |
|  2 | Fashion     |             NULL | 11 | WOMAN Fashion       |                2 |
+----+-------------+------------------+----+---------------------+------------------+


---------------------------------サブクエリー------------------------------
/*サブクエリで最大IDを取得*/
mysql> SELECT * FROM CATEGORY WHERE ID IN (SELECT MAX(ID) FROM CATEGORY);
+----+---------------+------------------+
| ID | NAME          | HOYA_CATEGORY_ID |
+----+---------------+------------------+
| 11 | WOMAN Fashion |                2 |
+----+---------------+------------------+

mysql> SELECT * FROM CATEGORY WHERE ID IN (SELECT MAX(HOYA_CATEGORY_ID) FROM CATEGORY);
+----+-------------+------------------+
| ID | NAME        | HOYA_CATEGORY_ID |
+----+-------------+------------------+
|  3 | electronics |             NULL |
+----+-------------+------------------+

------------商品のカテゴリを表示-------------
CREATE TABLE PRODUCT(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(100),CATEGORY_ID INT);

INSERT INTO PRODUCT(NAME,CATEGORY_ID) VALUES('PC_WORK',5),('IT_WORK',5),('PAN_COOK',6),('CAMERA',4);


mysql> SELECT CAT.NAME AS 'カテゴリ',PRO.NAME AS '商品名'FROM  CATEGORY AS CAT ,PRODUCT AS PRO WHERE CAT.ID = PRO.CATEGORY_ID;
+--------------+----------+
| カテゴリ          | 商品名        |
+--------------+----------+
| IT BOOK      | PC_WORK  |
| IT BOOK      | IT_WORK  |
| COOKING BOOK | PAN_COOK |
| Picture BOOK | CAMERA   |
+--------------+----------+


----------商品が無いカテゴリを表示-----------
SELECT CATEGORY.NAME
  FROM CATEGORY
WHERE  NOT EXISTS (SELECT * FROM PRODUCT WHERE PRODUCT.CATEGORY_ID = CATEGORY.ID)
AND CATEGORY.HOYA_CATEGORY_ID IS NOT NULL
;
+---------------------+
| NAME                |
+---------------------+
| TV electronics      |
| Music electronics   |
| Cooking electronics |
| Man Fashion         |
| WOMAN Fashion       |
| TEST                |
+---------------------+

----------------カテゴリの商品件数-----------------
SELECT CAT.NAME,COUNT(*)
  FROM CATEGORY AS CAT,
        PRODUCT AS PRO
 WHERE CAT.ID = PRO.CATEGORY_ID
 GROUP BY CAT.ID;

 +--------------+----------+
| NAME         | COUNT(*) |
+--------------+----------+
| Picture BOOK |        1 |
| IT BOOK      |        2 |
| COOKING BOOK |        1 |
+--------------+----------+

----------------ビュー-----------------
CREATE VIEW C1
  AS
SELECT ID,NAME
  FROM CATEGORY;
 
SELECT * FROM C1;
+----+---------------------+
| ID | NAME                |
+----+---------------------+
|  1 | BOOK                |
|  2 | Fashion             |
|  3 | electronics         |
|  4 | Picture BOOK        |
|  5 | IT BOOK             |
|  6 | COOKING BOOK        |
|  7 | TV electronics      |
|  8 | Music electronics   |
|  9 | Cooking electronics |
| 10 | Man Fashion         |
| 11 | WOMAN Fashion       |
| 12 | TEST                |
+----+---------------------+

--------自己結合したテーブルのVIEW---------
/*ポイントは同じなのカラム名にならない事*/

mysql> CREATE VIEW C2 AS
    ->   SELECT
    ->     CAT.ID,
    ->     CAT.NAME,
    ->     C1.ID AS KO_ID,
    ->     C1.NAME AS KO_NAME
    ->   FROM CATEGORY AS CAT
    ->     JOIN C1
    ->   WHERE CAT.ID = C1.HOYA_CATEGORY_ID;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM C2;
+----+-------------+-------+---------------------+
| ID | NAME        | KO_ID | KO_NAME             |
+----+-------------+-------+---------------------+
|  1 | BOOK        |     4 | Picture BOOK        |
|  1 | BOOK        |     5 | IT BOOK             |
|  1 | BOOK        |     6 | COOKING BOOK        |
|  3 | electronics |     7 | TV electronics      |
|  3 | electronics |     8 | Music electronics   |
|  3 | electronics |     9 | Cooking electronics |
|  2 | Fashion     |    10 | Man Fashion         |
|  2 | Fashion     |    11 | WOMAN Fashion       |
+----+-------------+-------+---------------------+

-------------ビュー上書き-------------
CREATE OR REPLACE VIEW C2 AS SELECT NOW();


-------------ビュのカラム構造の変更-------------
ALTER VIEW C2 AS
SELECT
  CAT.ID,
  CAT.NAME,
  C1.ID AS KO_ID,
  C1.NAME AS KO_NAME
FROM CATEGORY AS CAT
  JOIN C1
WHERE CAT.ID = C1.HOYA_CATEGORY_ID;

-------------ビュ－の削除-------------
/*VIEWがあれば削除します。*/
DROP VIEW IF EXISTS C2;

MYSQL入門 ２

-------------OFFSET--------------

mysql> SELECT * FROM TEST5;
+-----+---------------------+
| ID2 | TIMES2              |
+-----+---------------------+
|   1 | 2012-01-04 17:47:38 |
|   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:38 |
|   5 | 2012-01-04 18:19:34 |
|   6 | 2012-01-04 18:19:34 |
|   7 | 2012-01-04 18:19:34 |
|   8 | 2012-01-04 18:19:34 |
+-----+---------------------+

mysql> SELECT * FROM TEST5 LIMIT 4 OFFSET 2;
+-----+---------------------+
| ID2 | TIMES2              |
+-----+---------------------+
|   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:38 |
|   5 | 2012-01-04 18:19:34 |
|   6 | 2012-01-04 18:19:34 |
+-----+---------------------+

---------------テストデータの作成------------------

mysql> CREATE TABLE USER(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20),Unit INT);

mysql> INSERT INTO USER(NAME,UNIT)VALUES('ASO',1),('KATO',4),('YAMADA',2),('SUZUKI',1),('MIYAMOTO',3),('TANAKA',4);

mysql> SELECT * FROM USER;
+----+----------+------+
| ID | NAME     | Unit |
+----+----------+------+
|  1 | ASO      |    1 |
|  2 | KATO     |    4 |
|  3 | YAMADA   |    2 |
|  4 | SUZUKI   |    1 |
|  5 | MIYAMOTO |    3 |
|  6 | TANAKA   |    4 |
+----+----------+------+

CREATE TABLE UNIT(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20));

INSERT INTO UNIT(NAME) VALUES('SOUMU'),('EIGYO'),('JIMU'),('GIJYUTSU');

mysql> SELECT * FROM UNIT;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | SOUMU    |
|  2 | EIGYO    |
|  3 | JIMU     |
|  4 | GIJYUTSU |
+----+----------+


-----グループ分けしカウント-----
mysql> SELECT UNIT.ID,UNIT.NAME ,COUNT(USER.UNIT) AS 'BUSYO_COUNT'
FROM USER
JOIN UNIT ON  USER.UNIT = UNIT.ID
GROUP BY USER.UNIT;

+----+----------+-------------+
| ID | NAME     | BUSYO_COUNT |
+----+----------+-------------+
|  1 | SOUMU    |           2 |
|  2 | EIGYO    |           1 |
|  3 | JIMU     |           1 |
|  4 | GIJYUTSU |           2 |
+----+----------+-------------+


-----グループ分けした後の抽出-----
mysql> SELECT UNIT.ID,UNIT.NAME ,COUNT(USER.UNIT) AS 'BUSYO_COUNT'
    -> FROM USER
    -> JOIN UNIT ON  USER.UNIT = UNIT.ID
    -> GROUP BY USER.UNIT
    -> HAVING BUSYO_COUNT >= 2;
+----+----------+-------------+
| ID | NAME     | BUSYO_COUNT |
+----+----------+-------------+
|  1 | SOUMU    |           2 |
|  4 | GIJYUTSU |           2 |
+----+----------+-------------+

-----------UPDATE-----------
ALTER TABLE USER ADD KOZUKAI INT;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |    NULL |
|  2 | KATO     |    4 |    NULL |
|  3 | YAMADA   |    2 |    NULL |
|  4 | SUZUKI   |    1 |    NULL |
|  5 | MIYAMOTO |    3 |    NULL |
|  6 | TANAKA   |    4 |    NULL |
+----+----------+------+---------+

UPDATE USER SET KOZUKAI = 10000;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   10000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   10000 |
|  6 | TANAKA   |    4 |   10000 |
+----+----------+------+---------+

--------条件が一致するレコードを修正---------
UPDATE USER SET KOZUKAI = 90000 WHERE UNIT = 4;

mysql>  SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   90000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   10000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

UPDATE USER SET KOZUKAI = 50000 WHERE UNIT = 3;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   90000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   50000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

SELECT * FROM USER ORDER BY KOZUKAI;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   50000 |
|  2 | KATO     |    4 |   90000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

-------------CASEを使ったUPDATE文----------------
ALTER TABLE USER ADD BIKOU VARCHAR(100);
UPDATE USER
SET BIKOU =
CASE
 WHEN KOZUKAI <= 10000 THEN 'SUKUNAI'
 WHEN (10000 < KOZUKAI AND KOZUKAI < 51000) THEN 'FUTU'
 WHEN 51000 < KOZUKAI THEN 'OUI'
END;

mysql> SELECT * FROM USER ORDER BY KOZUKAI;
+----+----------+------+---------+---------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   |
+----+----------+------+---------+---------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |
|  5 | MIYAMOTO |    3 |   50000 | FUTU    |
|  2 | KATO     |    4 |   90000 | OUI     |
|  6 | TANAKA   |    4 |   90000 | OUI     |
+----+----------+------+---------+---------+

MYSQL入門 １

　-------------TABLEの作成--------------
mysql> create table tb1(bang varchar(10),name varchar(10),tosi int);



-------------インサート--------------
mysql>INSERT INTO TB1 VALUES('TEST2','KAWA',11),('TEST3','TOSI',44),('TEST4','YAMA',20);

-------------テーブルのカラム構造のコピー--------------

CREATE TABLE TB1_COP LIKE TB1;

-------------テーブルのレコードのコピー--------------
INSERT INTO TB1_COP SELECT * FROM TB1;


-------------カラムデータ型を変更する--------------
mysql> ALTER TABLE tb1 mODIFY BANG varchar(200);


-------------カラムを追加--------------
mysql> ALTER TABLE tb1 ADD birthday DATETIME;


-------------主キー--------------
mysql> CREATE TABLE TB2(ID INT PRIMARY KEY, CATE VARCHAR(10));

-------------主キーオートインクリメント--------------
mysql> CREATE TABLE TB3(ID INT AUTO_INCREMENT PRIMARY KEY, CATE2 VARCHAR(10));


-------------主キーオートインクリメントにインサート--------------
mysql> INSERT INTO TB3(CATE2) VALUES('1'),('2'),('3');

-----------日付関数--------------
mysql> CREATE TABLE NOWTIME(ID INT AUTO_INCREMENT PRIMARY KEY,SHOW_TIME DATETIME);
mysql> INSERT INTO NOWTIME(SHOW_TIME) VALUES(NOW());

+----+---------------------+
| ID | SHOW_TIME           |
+----+---------------------+
|  1 | 2012-01-04 16:27:11 |
|  2 | 2012-01-04 16:27:13 |
|  3 | 2012-01-04 16:27:14 |
|  4 | 2012-01-04 16:27:15 |
|  5 | 2012-01-04 16:27:16 |
+----+---------------------+

----------リミット句--------------
mysql>SELECT * FROM NOWTIME LIMIT 3;

---------WHERE句--------------
mysql> SELECT * FROM NOWTIME WHERE ID = 3;
mysql> SELECT * FROM NOWTIME WHERE ID > 2 and id < 5


---------case when--------------
SELECT ID,
  CASE
    WHEN ID > 2 THEN '小さい'
    WHEN ID > 3 THEN 'すこし小さい'
    WHEN ID > 4 THEN '中ぐらい'
    WHEN ID > 5 THEN '大きい'
    ELSE '図れません'
  END
FROM TB3;
--------+
|  1 | 図れません

                |
|  2 | 図れません

                |
|  3 | 小さい

              |
|  4 | 小さい

              |
|  5 | 小さい

              |
|  6 | 小さい

              |
              |

---------UPDATE--------------
mysql> UPDATE 「テーブル名」  SET「カラム名」=「設定値」;
mysql> UPDATE 「テーブル名」  SET「カラム名」=「設定値」WHERE 条件;

---------複数テーブルのレコードを合わせて表示--------------
mysql> CREATE TABLE TEST4(ID1 INT AUTO_INCREMENT PRIMARY KEY, TIMES1 DATETIME);
mysql> CREATE TABLE TEST5(ID2 INT AUTO_INCREMENT PRIMARY KEY, TIMES2 DATETIME);


mysql> INSERT INTO TEST4(TIMES1) VALUES(NOW()),(NOW()),(NOW()),(NOW());
mysql> INSERT INTO TEST5(TIMES2) VALUES(NOW()),(NOW()),(NOW()),(NOW());


mysql> (SELECT ID1 FROM TEST4)
UNION
(SELECT ID2 FROM TEST5);

+-----+
| ID1 |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
4 rows in set (0.00 sec)


mysql> (SELECT ID1 FROM TEST4)
UNION ALL
(SELECT ID2 FROM TEST5);

+-----+
| ID1 |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
8 rows in set (0.00 sec)


---------複数テーブルを結合して表示   内部結合　JOIN と INNER JOINはキーが一致しているレコードを取り出します--------------
SELECT * FROM TEST4
JOIN TEST5
ON TEST4.ID1 = TEST5.ID2;

+-----+---------------------+-----+---------------------+
| ID1 | TIMES1              | ID2 | TIMES2              |
+-----+---------------------+-----+---------------------+
|   1 | 2012-01-04 17:47:05 |   1 | 2012-01-04 17:47:38 |
|   2 | 2012-01-04 17:47:05 |   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:05 |   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:05 |   4 | 2012-01-04 17:47:38 |
+-----+---------------------+-----+---------------------+

SELECT * FROM TEST4
JOIN TEST5 ON ID2 > 1
WHERE TEST4.ID1 = TEST5.ID2;
+-----+---------------------+-----+---------------------+
| ID1 | TIMES1              | ID2 | TIMES2              |
+-----+---------------------+-----+---------------------+
|   2 | 2012-01-04 17:47:05 |   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:05 |   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:05 |   4 | 2012-01-04 17:47:38 |
+-----+---------------------+-----+---------------------+
3 rows in set (0.00 sec)

MySQLをphpmyadminで操作

①xamppをインストール後、アパッチとmysqlを起動し、
C:\xampp\phpMyAdmin\config.inc.php
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';
と変更し、
②http://localhost/phpmyadmin/をブラウザーで表示、
変更が成功しているなら、IDとPSを求められるので入力しphpmyadminをしようします。

ありがちな受注管理システムのmysql文 2

#初回にrootユーザのパスワード設定コマンド
mysqladmin -u root password mysql

#rootユーザでmysqlにログイン
mysql -u root -p

★★★★#P476 データベースの作成
CREATE DATABASE 受注管理;

#MySQLのコマンド。カレントデータベースを変更する。
use 受注管理;

★★★★#P476 テーブルの作成
CREATE TABLE 顧客 (
顧客番号 CHAR(4) PRIMARY KEY,
顧客名 VARCHAR(20),
住所 VARCHAR(50),
電話番号 CHAR(15));

CREATE TABLE 受注 (
受注番号 CHAR(5) PRIMARY KEY,
受注年月日 DATE NOT NULL,
顧客番号 CHAR(4),
受注合計 DECIMAL,
FOREIGN KEY(顧客番号) REFERENCES 顧客(顧客番号));
##################
#FOREIGN KEYは指定した
#親テーブルに存在しない値を指定してデータを追加
#するとエラーとなります。
#################


CREATE TABLE 商品 (
商品番号 CHAR(3) PRIMARY KEY,
商品名 VARCHAR(20),
単価 DECIMAL );

CREATE TABLE 受注明細 (
受注番号 CHAR(5),
商品番号 CHAR(3),
数量 INTEGER,
受注小計 DECIMAL,
PRIMARY KEY(受注番号,商品番号),
FOREIGN KEY(受注番号) REFERENCES 受注(受注番号),
FOREIGN KEY(商品番号) REFERENCES 商品(商品番号));

★★★★#P496 レコードの挿入（1件のみ）
INSERT INTO 顧客 (顧客番号,顧客名,住所,電話番号)
VALUES ('1001','株式会社冨田貿易','東京都港区芝浦1-XX-XX','03-3256-XXXX');

#カラムを指定せずに複数行を挿入
INSERT INTO 顧客 VALUES
('1003','宇宙商事株式会社','東京都足立区神明22-XX','03-5126-XXXX'),
('1006','有限会社吉野物産','大阪府大阪市中央区城見23-XX','06-6112-XXXX');

INSERT INTO 商品 VALUES
('A01','テレビ（液晶大型）','200000'),
('A11','テレビ（液晶小型）','50000'),
('G02','DVDレコーダー','80000'),
('S05','ラジオ','3000');

INSERT INTO 受注 VALUES
('00001','2010/04/01','1001','640000'),
('00002','2010/04/02','1006','518000'),
('00003','2010/04/02','1003','600000'),
('00004','2010/04/05','1001','3000');

INSERT INTO 受注明細 VALUES
('00001','A01','2','400000'),
('00001','G02','3','240000'),
('00002','S05','6','18000'),
('00002','A11','10','500000'),
('00003','A01','3','600000'),
('00004','S05','1','3000');

★★★★P497 挿入(INSERT文)★★★★★
INSERT INTO 顧客 (顧客番号,顧客名)
VALUES ('2001','株式会社こあら百貨店');
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------------------+-----------------------------+--------------+

| 顧客番号 | 顧客名 | 住所 | 電話番号 |

+----------+----------------------+-----------------------------+--------------+

| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |

| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126-XXXX |

| 1006 | 有限会社吉野物産 | 大阪府大阪市中央区城見23-XX | 06-6112-XXXX |

| 2001 | 株式会社こあら百貨店 | NULL | NULL |

+----------+----------------------+-----------------------------+--------------+


★★★★★★★★#P497 更新(UPDATE文)★★★★
UPDATE 顧客 SET 住所='埼玉県入間市東町1-XX' WHERE 顧客番号='2001';
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------------------+-----------------------------+--------------+

| 顧客番号 | 顧客名 | 住所 | 電話番号 |

+----------+----------------------+-----------------------------+--------------+

| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |

| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126-XXXX |

| 1006 | 有限会社吉野物産 | 大阪府大阪市中央区城見23-XX | 06-6112-XXXX |

| 2001 | 株式会社こあら百貨店 | 埼玉県入間市東町1-XX | NULL |

+----------+----------------------+-----------------------------+--------------+


★★★★★★★★#P498 削除(DELETE文)★★★★
DELETE FROM 顧客 WHERE 顧客番号='2001';
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------------+-----------------------------+--------------+
| 顧客番号 | 顧客名 | 住所 | 電話番号 |
+----------+------------------+-----------------------------+--------------+
| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |
| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126-XXXX |
| 1006 | 有限会社吉野物産 | 大阪府大阪市中央区城見23-XX | 06-6112-XXXX |
+----------+------------------+-----------------------------+--------------+


★★★★★★★★#P479 ビューの定義★★★★
CREATE VIEW 顧客名簿 AS SELECT 顧客番号,顧客名 FROM 顧客;
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------------+
| 顧客番号 | 顧客名 |
+----------+------------------+
| 1001 | 株式会社冨田貿易 |
| 1003 | 宇宙商事株式会社 |
| 1006 | 有限会社吉野物産 |
+----------+------------------+

★★★★★★★★#P480 検索系のデータ操作(SELECT文)★★★★
★★★★#P481 全ての項目の検索
SELECT * FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00002 | 2010-04-02 | 1006 | 518000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
+----------+------------+----------+----------+


★★★★★★★★#P481 特定の項目の検索★★★★
SELECT 受注番号,受注合計 FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+
| 受注番号 | 受注合計 |
+----------+----------+
| 00001 | 640000 |
| 00002 | 518000 |
| 00003 | 600000 |
| 00004 | 3000 |
+----------+----------+

★★★★★★★★#P481 計算結果の表示★★★★
SELECT 受注番号,受注合計,受注合計*1.05-受注合計 AS 消費税 FROM 受注
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+----------+
| 受注番号 | 受注合計 | 消費税 |
+----------+----------+----------+
| 00001 | 640000 | 32000.00 |
| 00002 | 518000 | 25900.00 |
| 00003 | 600000 | 30000.00 |
| 00004 | 3000 | 150.00 |
+----------+----------+----------+


★★★★★★★★#P482 ひとつの項目で重複するデータを除いた検索★★★★
SELECT DISTINCT 受注年月日 FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+------------+
| 受注年月日 |
+------------+
| 2010-04-01 |
| 2010-04-02 |
| 2010-04-05 |
+------------+

★★★★★★★★#P482 複数の項目で重複するデータを除いた検索★★★★
SELECT DISTINCT 受注年月日,顧客番号 FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+------------+----------+
| 受注年月日 | 顧客番号 |
+------------+----------+
| 2010-04-01 | 1001 |
| 2010-04-02 | 1006 |
| 2010-04-02 | 1003 |
| 2010-04-05 | 1001 |
+------------+----------+


★★★★★★★★#P482 条件を指定したデータの検索★★★★
#前準備
INSERT INTO 受注 (受注番号,受注年月日,顧客番号) VALUES ('00005','2010/04/05','1001');
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00002 | 2010-04-02 | 1006 | 518000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
| 00005 | 2010-04-05 | 1001 | NULL |
+----------+------------+----------+----------+


★★★★#P484 条件を満たすデータの検索★★★★
SELECT * FROM 受注 WHERE 顧客番号='1001';
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
| 00005 | 2010-04-05 | 1001 | NULL |
+----------+------------+----------+----------+

★★★★#P484 すべての条件を満たすデータの検索★★★★
SELECT * FROM 受注 WHERE 顧客番号='1001' AND 受注合計>=600000;
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
+----------+------------+----------+----------+

★★★★#P484 いずれかの条件を満たすデータの検索★★★★
SELECT * FROM 受注 WHERE 顧客番号='1001' OR 受注合計>=600000;
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
| 00005 | 2010-04-05 | 1001 | NULL |
+----------+------------+----------+----------+

★★★★#P485 条件を満たさないデータの検索★★★★
SELECT * FROM 受注 WHERE NOT 顧客番号='1001';
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00002 | 2010-04-02 | 1006 | 518000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
+----------+------------+----------+----------+

★★★★#P485 NULLを含むレコードの検索★★★★
SELECT 受注番号,受注合計 FROM 受注 WHERE 受注合計 IS NULL;
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+
| 受注番号 | 受注合計 |
+----------+----------+
| 00005 | NULL |
+----------+----------+

★★★★#P485 NULLを含まないレコードの検索★★★★
SELECT 受注番号,受注合計 FROM 受注 WHERE 受注合計 IS NOT NULL;
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+
| 受注番号 | 受注合計 |
+----------+----------+
| 00001 | 640000 |
| 00002 | 518000 |
| 00003 | 600000 |
| 00004 | 3000 |
+----------+----------+

★★★★#P486 2つの値の間にあるデータを含むレコードの検索★★★★
SELECT 受注番号,受注合計 FROM 受注
WHERE 受注合計 BETWEEN 600000 AND 1000000;
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
+----------+------------+----------+----------+

★★★★#P486 リストの値と一致するデータを含むレコードの検索★★★★
SELECT 受注番号,受注合計 FROM 受注 WHERE 受注番号 IN ('00001','00004');
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
+----------+------------+----------+----------+

★★★★#P486 リストの値のすべてと一致しないデータを含むレコードの検索★★★★
SELECT 受注番号,受注合計 FROM 受注
WHERE 受注番号 NOT IN ('00001','00004');
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+
| 受注番号 | 受注合計 |
+----------+----------+
| 00002 | 518000 |
| 00003 | 600000 |
| 00005 | NULL |
+----------+----------+

★★★★#P487 文字列の一部を条件とした検索★★★★
SELECT 商品番号,商品名 FROM 商品 WHERE 商品名 LIKE 'ラ__';
+----------+--------+
| 商品番号 | 商品名 |
+----------+--------+
| S05 | ラジオ |
+----------+--------+
SELECT 商品番号,商品名 FROM 商品 WHERE 商品名 LIKE '%液晶%';
■■■■■■■■■結果■■■■■■■■■■■
+----------+--------------------+
| 商品番号 | 商品名 |
+----------+--------------------+
| A01 | テレビ（液晶大型） |
| A11 | テレビ（液晶小型） |
+----------+--------------------+

★★★★#P487 表の結合★★★★
#下準備
DELETE FROM 受注 WHERE 受注番号='00005';
■■■■■■■■■結果■■■■■■■■■■■


★★★★#P488 2つの表の結合★★★★
SELECT 受注番号,受注.顧客番号,顧客名 FROM 受注,顧客
WHERE 受注.顧客番号=顧客.顧客番号;

＊＊＊＊＊＊INNER　JOINを使った場合＊＊＊＊＊
SELECT 受注番号,受注.顧客番号,顧客名 FROM 受注
INNER JOIN 顧客
WHERE 受注.顧客番号=顧客.顧客番号;

■■■■■■■■■結果■■■■■■■■■■■
：：：：：：：：：受注テーブル：：：：：：：：
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00002 | 2010-04-02 | 1006 | 518000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
| 00005 | 2010-04-05 | 1001 | NULL |
+----------+------------+----------+----------+
：：：：：：：：：顧客テーブル：：：：：：：：：
+----------+------------------+-----------------------------+--------
| 顧客番号 | 顧客名 | 住所 | 電話番号
+----------+------------------+-----------------------------+--------
| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256
| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126
| 1006 | 有限会社吉野物産 | 大阪府大阪市中央区城見23-XX | 06-6112
+----------+------------------+-----------------------------+--------
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
+----------+----------+------------------+
| 受注番号 | 顧客番号 | 顧客名 |
+----------+----------+------------------+
| 00001 | 1001 | 株式会社冨田貿易 |
| 00004 | 1001 | 株式会社冨田貿易 |
| 00005 | 1001 | 株式会社冨田貿易 |
| 00003 | 1003 | 宇宙商事株式会社 |
| 00002 | 1006 | 有限会社吉野物産 |
+----------+----------+------------------+
★★★★#P488 3つ以上の表の結合★★★★
SELECT 受注.受注番号,受注.顧客番号,顧客名,受注明細.商品番号,商品名,単価,数量,受注小計
FROM 受注,顧客,受注明細,商品
WHERE 受注.顧客番号=顧客.顧客番号 AND 受注.受注番号=受注明細.受注番号 AND 受注明細.商品番号=商品.商品番号;
＊＊＊＊＊＊＊＊＊下記のテーブルを結合してSQLを実行します。＊＊＊＊＊＊＊＊＊
:::::::::::::::受注:::::::::::::::
+----------+------------+----------+----------+
| 受注番号 | 受注年月日 | 顧客番号 | 受注合計 |
+----------+------------+----------+----------+
| 00001 | 2010-04-01 | 1001 | 640000 |
| 00002 | 2010-04-02 | 1006 | 518000 |
| 00003 | 2010-04-02 | 1003 | 600000 |
| 00004 | 2010-04-05 | 1001 | 3000 |
| 00005 | 2010-04-05 | 1001 | NULL |
+----------+------------+----------+----------+
::::::::::::::顧客:::::::::::::
+----------+------------------+-----------------------------+--------------+
| 顧客番号 | 顧客名 | 住所 | 電話番号 |
+----------+------------------+-----------------------------+--------------+
| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |
| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126-XXXX |
| 1006 | 有限会社吉野物産 | 大阪府大阪市中央区城見23-XX | 06-6112-XXXX |
+----------+------------------+-----------------------------+--------------+
::::::::受注明細::::::::::::
+----------+----------+------+----------+
| 受注番号 | 商品番号 | 数量 | 受注小計 |
+----------+----------+------+----------+
| 00001 | A01 | 2 | 400000 |
| 00001 | G02 | 3 | 240000 |
| 00002 | A11 | 10 | 500000 |
| 00002 | S05 | 6 | 18000 |
| 00003 | A01 | 3 | 600000 |
| 00004 | S05 | 1 | 3000 |
+----------+----------+------+----------+
::::::::商品:::::::::::
+----------+--------------------+--------+
| 商品番号 | 商品名 | 単価 |
+----------+--------------------+--------+
| A01 | テレビ（液晶大型） | 200000 |
| A11 | テレビ（液晶小型） | 50000 |
| G02 | DVDレコーダー | 80000 |
| S05 | ラジオ | 3000 |
+----------+--------------------+--------+
↓↓↓↓上記のテーブルを結合して実行するSQL文↓↓↓↓
SELECT 受注.受注番号,受注.顧客番号,顧客名,受注明細.商品番号,商品名,単価,数量,受注小計
FROM 受注,顧客,受注明細,商品
WHERE 受注.顧客番号=顧客.顧客番号 AND 受注.受注番号=受注明細.受注番号 AND 受注明細.商品番号=商品.商品番号;
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+------------------+----------+--------------------+--------+------+----------+
| 受注番号 | 顧客番号 | 顧客名 | 商品番号 | 商品名 | 単価 | 数量 | 受注小計 |
+----------+----------+------------------+----------+--------------------+--------+------+----------+
| 00001 | 1001 | 株式会社冨田貿易 | A01 | テレビ（液晶大型） | 200000 | 2 | 400000 |
| 00003 | 1003 | 宇宙商事株式会社 | A01 | テレビ（液晶大型） | 200000 | 3 | 600000 |
| 00002 | 1006 | 有限会社吉野物産 | A11 | テレビ（液晶小型） | 50000 | 10 | 500000 |
| 00001 | 1001 | 株式会社冨田貿易 | G02 | DVDレコーダー | 80000 | 3 | 240000 |
| 00004 | 1001 | 株式会社冨田貿易 | S05 | ラジオ | 3000 | 1 | 3000 |
| 00002 | 1006 | 有限会社吉野物産 | S05 | ラジオ | 3000 | 6 | 18000 |
+----------+----------+------------------+----------+--------------------+--------+------+----------+



★★★★#P489 データの集計★★★★
★★★★#P489 レコード数の表示★★★★
SELECT COUNT(*) FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+

★★★★P#490 指定した項目の合計値の表示★★★★
SELECT SUM(受注合計) FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+---------------+
| SUM(受注合計) |
+---------------+
| 1761000 |
+---------------+


★★★★#P490 指定した項目の平均値の表示★★★★
SELECT AVG(受注合計) FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+---------------+
| AVG(受注合計) |
+---------------+
| 440250.0000 |
+---------------+

★★★★#P490 指定した項目の最大値の表示★★★★
SELECT MAX(受注合計) FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+---------------+
| MAX(受注合計) |
+---------------+
| 640000 |
+---------------+


★★★★#P490 指定した項目の最小値の表示★★★★
SELECT MIN(受注合計) FROM 受注;
■■■■■■■■■結果■■■■■■■■■■■
+---------------+
| MIN(受注合計) |
+---------------+
| 3000 |
+---------------+

★★★★#P491 データのグループ化★★★★
★★★★#P491 項目ごとにグループ化して表示★★★★
SELECT 受注年月日,COUNT(*),SUM(受注合計) FROM 受注 GROUP BY 受注年月日;
■■■■■■■■■結果■■■■■■■■■■■
+------------+----------+---------------+
| 受注年月日 | COUNT(*) | SUM(受注合計) |
+------------+----------+---------------+
| 2010-04-01 | 1 | 640000 |
| 2010-04-02 | 2 | 1118000 |
| 2010-04-05 | 2 | 3000 |
+------------+----------+---------------+
★★★★#P491 データのグループ化★★★★
★★★★#P491 項目ごとにグループ化して表示★★★★
SELECT 顧客番号,COUNT(*),SUM(受注合計) FROM 受注 GROUP BY 顧客番号;
+----------+----------+---------------+
| 顧客番号 | COUNT(*) | SUM(受注合計) |
+----------+----------+---------------+
| 1001 | 3 | 643000 |
| 1003 | 1 | 600000 |
| 1006 | 1 | 518000 |
+----------+----------+---------------+

★★★★#P492 項目ごとにグループ化して条件を絞り込んで表示★★★★
SELECT 受注年月日,COUNT(*),SUM(受注合計)
FROM 受注
GROUP BY 受注年月日
HAVING SUM(受注合計)>1000000;
■■■■■■■■■結果■■■■■■■■■■■
+------------+----------+---------------+
| 受注年月日 | COUNT(*) | SUM(受注合計) |
+------------+----------+---------------+
| 2010-04-02 | 2 | 1118000 |
+------------+----------+---------------+

★★★★#P492 データの並べ替え★★★★
★★★★#P493 ひとつの項目による並べ替え★★★★
SELECT 受注番号,顧客番号,受注合計
FROM 受注
ORDER BY 受注合計;
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+----------+
| 受注番号 | 顧客番号 | 受注合計 |
+----------+----------+----------+
| 00005 | 1001 | NULL |
| 00004 | 1001 | 3000 |
| 00002 | 1006 | 518000 |
| 00003 | 1003 | 600000 |
| 00001 | 1001 | 640000 |
+----------+----------+----------+

★★★★#P492 複数の項目による並べ替え★★★★
SELECT 受注番号,顧客番号,受注合計
FROM 受注
ORDER BY 顧客番号
ASC,受注合計 DESC;
■■■■■■■■■解説■■■■■■■■■
顧客番号順に並べ受注合計が多い多い順に表示
■■■■■■■■■結果■■■■■■■■■■■
+----------+----------+----------+
| 受注番号 | 顧客番号 | 受注合計 |
+----------+----------+----------+
| 00001 | 1001 | 640000 |
| 00004 | 1001 | 3000 |
| 00005 | 1001 | NULL |
| 00003 | 1003 | 600000 |
| 00002 | 1006 | 518000 |
+----------+----------+----------+


★★★★#P492 副問い合わせ★★★★
★★★★#P493 単一行問い合わせ★★★★
SELECT * FROM 顧客
WHERE 顧客番号=
(SELECT 顧客番号 FROM 受注 WHERE 受注番号='00001');
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------------+-----------------------+--------------+
| 顧客番号 | 顧客名 | 住所 | 電話番号 |
+----------+------------------+-----------------------+--------------+
| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |
+----------+------------------+-----------------------+--------------+

★★★★#P493 複数行問い合わせ★★★★
SELECT * FROM 顧客 WHERE 顧客番号 IN
(SELECT 顧客番号 FROM 受注 WHERE 受注合計>=600000);
■■■■■■■■■結果■■■■■■■■■■■
+----------+------------------+-----------------------+--------------+
| 顧客番号 | 顧客名 | 住所 | 電話番号 |
+----------+------------------+-----------------------+--------------+
| 1001 | 株式会社冨田貿易 | 東京都港区芝浦1-XX-XX | 03-3256-XXXX |
| 1003 | 宇宙商事株式会社 | 東京都足立区神明22-XX | 03-5126-XXXX |
+----------+------------------+-----------------------+--------------+

★★★★#P495 相関副問い合わせ★★★★
#下準備
INSERT INTO 商品 VALUES ('Z01','冷蔵庫','150000'),('Z11','エアコン','98000');
■■■■■■■■■結果■■■■■■■■■■■
+----------+--------------------+--------+
| 商品番号 | 商品名 | 単価 |
+----------+--------------------+--------+
| A01 | テレビ（液晶大型） | 200000 |
| A11 | テレビ（液晶小型） | 50000 |
| G02 | DVDレコーダー | 80000 |
| S05 | ラジオ | 3000 |
| Z01 | 冷蔵庫 | 150000 |
| Z11 | エアコン | 98000 |
+----------+--------------------+--------+


★★★★#P495 複数の表で一致するレコードの検索★★★★
SELECT 商品番号,商品名 FROM 商品 WHERE EXISTS
(SELECT * FROM 受注明細 WHERE 商品.商品番号=受注明細.商品番号);

*＊＊＊＊＊＊＊解説＊＊＊＊＊＊＊＊
EXISTS は TRUE になり、NOT EXISTS は FALSE になります。

■■■■■■■■■結果■■■■■■■■■■■
+----------+--------------------+
| 商品番号 | 商品名 |
+----------+--------------------+
| A01 | テレビ（液晶大型） |
| A11 | テレビ（液晶小型） |
| G02 | DVDレコーダー |
| S05 | ラジオ |
+----------+--------------------+


★★★★#P496 複数の表で一致しないレコードの検索★★★★
SELECT 商品番号,商品名 FROM 商品 WHERE NOT EXISTS
(SELECT * FROM 受注明細 WHERE 商品.商品番号=受注明細.商品番号);
■■■■■■■■■結果■■■■■■■■■■■
：：：：：：商品テーブル：：：：：：：：
+----------+--------------------+--------+
| A01 | テレビ（液晶大型） | 200000 |
| A11 | テレビ（液晶小型） | 50000 |
| G02 | DVDレコーダー | 80000 |
| S05 | ラジオ | 3000 |
| Z01 | 冷蔵庫 | 150000 |
| Z11 | エアコン | 98000 |
+----------+--------------------+--------+

：：：：：：受注明細テーブル：：：：：：：：
+----------+----------+------+----------+
| 受注番号 | 商品番号 | 数量 | 受注小計 |
+----------+----------+------+----------+
| 00001 | A01 | 2 | 400000 |
| 00001 | G02 | 3 | 240000 |
| 00002 | A11 | 10 | 500000 |
| 00002 | S05 | 6 | 18000 |
| 00003 | A01 | 3 | 600000 |
| 00004 | S05 | 1 | 3000 |
+----------+----------+------+----------+
↓↓↓↓↓結果↓↓↓↓↓
+----------+----------+
| 商品番号 | 商品名 |
+----------+----------+
| Z01 | 冷蔵庫 |
| Z11 | エアコン |
+----------+----------+

ありがちな受注管理システムのmysql文

*********************************************
ありがちな受注管理システムのmysql文です。
今回xamppのmysqlを使いました。

#初回にrootユーザのパスワード設定コマンド
mysqladmin -u root password mysql

#rootユーザでmysqlにログイン
mysql -u root -p

#P476 データベースの作成
CREATE DATABASE 受注管理;

#MySQLのコマンド。カレントデータベースを変更する。
use 受注管理;

#P476 テーブルの作成
CREATE TABLE 顧客 (
顧客番号 CHAR(4) PRIMARY KEY,
顧客名 VARCHAR(20),
住所 VARCHAR(50),
電話番号 CHAR(15));

CREATE TABLE 受注 (
受注番号 CHAR(5) PRIMARY KEY,
受注年月日 DATE NOT NULL,
顧客番号 CHAR(4),
受注合計 DECIMAL,
FOREIGN KEY(顧客番号) REFERENCES 顧客(顧客番号));



CREATE TABLE 商品 (
商品番号 CHAR(3) PRIMARY KEY,
商品名 VARCHAR(20),
単価 DECIMAL );

CREATE TABLE 受注明細 (
受注番号 CHAR(5),
商品番号 CHAR(3),
数量 INTEGER,
受注小計 DECIMAL,
PRIMARY KEY(受注番号,商品番号),
FOREIGN KEY(受注番号) REFERENCES 受注(受注番号),
FOREIGN KEY(商品番号) REFERENCES 商品(商品番号));

#P496 レコードの挿入（1件のみ）
INSERT INTO 顧客 (顧客番号,顧客名,住所,電話番号)
VALUES ('1001','株式会社冨田貿易','東京都港区芝浦1-XX-XX','03-3256-XXXX');

#カラムを指定せずに複数行を挿入
INSERT INTO 顧客 VALUES
('1003','宇宙商事株式会社','東京都足立区神明22-XX','03-5126-XXXX'),
('1006','有限会社吉野物産','大阪府大阪市中央区城見23-XX','06-6112-XXXX');

INSERT INTO 商品 VALUES
('A01','テレビ（液晶大型）','200000'),
('A11','テレビ（液晶小型）','50000'),
('G02','DVDレコーダー','80000'),
('S05','ラジオ','3000');

INSERT INTO 受注 VALUES
('00001','2010/04/01','1001','640000'),
('00002','2010/04/02','1006','518000'),
('00003','2010/04/02','1003','600000'),
('00004','2010/04/05','1001','3000');

INSERT INTO 受注明細 VALUES
('00001','A01','2','400000'),
('00001','G02','3','240000'),
('00002','S05','6','18000'),
('00002','A11','10','500000'),
('00003','A01','3','600000'),
('00004','S05','1','3000');

#P497 挿入(INSERT文)
INSERT INTO 顧客 (顧客番号,顧客名)
VALUES ('2001','株式会社こあら百貨店');

#P497 更新(UPDATE文)
UPDATE 顧客 SET 住所='埼玉県入間市東町1-XX' WHERE 顧客番号='2001';

#P498 削除(DELETE文)
DELETE FROM 顧客 WHERE 顧客番号='2001';

#P479 ビューの定義
CREATE VIEW 顧客名簿 AS SELECT 顧客番号,顧客名 FROM 顧客;

#P480 検索系のデータ操作(SELECT文)
#P481 全ての項目の検索
SELECT * FROM 受注;

#P481 特定の項目の検索
SELECT 受注番号,受注合計 FROM 受注;

#P481 計算結果の表示
SELECT 受注番号,受注合計*1.05 FROM 受注;

#P482 ひとつの項目で重複するデータを除いた検索
SELECT DISTINCT 受注年月日 FROM 受注;

#P482 複数の項目で重複するデータを除いた検索
SELECT DISTINCT 受注年月日,顧客番号 FROM 受注;

#P482 条件を指定したデータの検索
#前準備
INSERT INTO 受注 (受注番号,受注年月日,顧客番号) VALUES ('00005','2010/04/05','1001');

#P484 条件を満たすデータの検索
SELECT * FROM 受注 WHERE 顧客番号='1001';

#P484 すべての条件を満たすデータの検索
SELECT * FROM 受注 WHERE 顧客番号='1001' AND 受注合計>=600000;

#P484 いずれかの条件を満たすデータの検索
SELECT * FROM 受注 WHERE 顧客番号='1001' OR 受注合計>=600000;

#P485 条件を満たさないデータの検索
SELECT * FROM 受注 WHERE NOT 顧客番号='1001';

#P485 NULLを含むレコードの検索
SELECT 受注番号,受注合計 FROM 受注 WHERE 受注合計 IS NULL;

#P485 NULLを含まないレコードの検索
SELECT 受注番号,受注合計 FROM 受注 WHERE 受注合計 IS NOT NULL;

#P486 2つの値の間にあるデータを含むレコードの検索
SELECT 受注番号,受注合計 FROM 受注
WHERE 受注合計 BETWEEN 600000 AND 1000000;

#P486 リストの値と一致するデータを含むレコードの検索
SELECT 受注番号,受注合計 FROM 受注
WHERE 受注番号 IN ('00001','00004');

#P486 リストの値のすべてと一致しないデータを含むレコードの検索
SELECT 受注番号,受注合計 FROM 受注
WHERE 受注番号 NOT IN ('00001','00004');

#P487 文字列の一部を条件とした検索
SELECT 商品番号,商品名 FROM 商品 WHERE 商品名 LIKE 'ラ__';

SELECT 商品番号,商品名 FROM 商品 WHERE 商品名 LIKE '%液晶%';

#P487 表の結合
#下準備
DELETE FROM 受注 WHERE 受注番号='00005';

#P488 2つの表の結合
SELECT 受注番号,受注.顧客番号,顧客名 FROM 受注,顧客
WHERE 受注.顧客番号=顧客.顧客番号;

#P488 3つ以上の表の結合
SELECT 受注.受注番号,受注.顧客番号,顧客名,受注明細.商品番号,商品名,単価,数量,受注小計
FROM 受注,顧客,受注明細,商品
WHERE 受注.顧客番号=顧客.顧客番号 AND 受注.受注番号=受注明細.受注番号 AND 受注明細.商品番号=商品.商品番号;

#P489 データの集計
#P489 レコード数の表示
SELECT COUNT(*) FROM 受注;

#490 指定した項目の合計値の表示
SELECT SUM(受注合計) FROM 受注;

#P490 指定した項目の平均値の表示
SELECT AVG(受注合計) FROM 受注;

#P490 指定した項目の最大値の表示
SELECT MAX(受注合計) FROM 受注;

#P490 指定した項目の最小値の表示
SELECT MIN(受注合計) FROM 受注;

#P491 データのグループ化
#P491 項目ごとにグループ化して表示
SELECT 受注年月日,COUNT(*),SUM(受注合計) FROM 受注 GROUP BY 受注年月日;

#P492 項目ごとにグループ化して条件を絞り込んで表示
SELECT 受注年月日,COUNT(*),SUM(受注合計) FROM 受注 GROUP BY 受注年月日
HAVING SUM(受注合計)>1000000;

#P492 データの並べ替え
#P493 ひとつの項目による並べ替え
SELECT 受注番号,顧客番号,受注合計 FROM 受注 ORDER BY 受注合計;

#P492 複数の項目による並べ替え
SELECT 受注番号,顧客番号,受注合計 FROM 受注 ORDER BY 顧客番号 ASC,受注合計 DESC;

#P492 副問い合わせ
#P493 単一行問い合わせ
SELECT * FROM 顧客 WHERE 顧客番号=
(SELECT 顧客番号 FROM 受注 WHERE 受注番号='00001');

#P493 複数行問い合わせ
SELECT * FROM 顧客 WHERE 顧客番号 IN
(SELECT 顧客番号 FROM 受注 WHERE 受注合計>=600000);

#P495 相関副問い合わせ
#下準備
INSERT INTO 商品 VALUES ('Z01','冷蔵庫','150000'),('Z11','エアコン','98000');

#P495 複数の表で一致するレコードの検索
SELECT 商品番号,商品名 FROM 商品 WHERE EXISTS
(SELECT * FROM 受注明細 WHERE 商品.商品番号=受注明細.商品番号);

#P496 複数の表で一致しないレコードの検索
SELECT 商品番号,商品名 FROM 商品 WHERE NOT EXISTS
(SELECT * FROM 受注明細 WHERE 商品.商品番号=受注明細.商品番号);

Ubuntuサーバで文字化け解決

フィルターで文字コードの設定を変更、

Tomcatの所で文字コードの変更を行えない設定だったので
userSetの変更を行う、

また、SQLのドライバーが内と怒られる、
調べてみると、WEBINFのlibにドライバーを置いていなかったのが分かる、

Mysqlのテーブル名が小文字になる、

Mysqlでは
CREATE TABLE `COMPANY` (
とsqlを実行してもcompanyとなってしまう。

これを変更するには
my.iniﾌｧｲﾙの[mysqld]の項目に
lower_case_table_names=0を追加して保存、
Mysqlを再起動で解決！！

UbuntuのMysql外部アクセス設定

①Ubuntuのｍｙｓｑｌポートの解放、
②外部からの接続をOKにする

sudo vim /etc/mysql/my.cnで

を#bind-address       = 127.0.0.1 ←コメントアウト 

③$>sudo /etc/init.d/mysql restart　←再起動

④ mysql>grant all privileges on *.*

to ●●●●@"%" identified "●●●●"  with grant option;

↑↑↑↑↑↑↑↑ １番がユーザ名2番目がパスワード↑↑↑↑↑↑

⑤mysql>flush privileges;←設定反映 

⑥ sudo /etc/init.d/mysql restart←再起動

これで外部からMySqlが使えるはずです。

　　

Windows TOMCAT6 + MySql + eclipse 環境でデータソース

 ■前準備
①C:\Tomcat 6.0\libにmysql-connector-java-5.1.13-bin.jarをコピー
②eclipseの各プロジェクトからプロパティ⇒Javaビルドパス⇒外部JARの追加⇒mysql-connector-java-5.1.13-bin.jarを選択し追加⇒OK

■C:\Tomcat 6.0\conf\Catalina\localhost\Test.xmlのTest.xmlに下記のｘｍｌを追加、
私の環境だとeclipseでプロジェクトを作った際、自動でファイルが出来ていますので
そこに追加
<Context path="/Test" reloadable="true" docBase="C:\eclipse\workspace\Test" workDir="C:\eclipse\workspace\Test\work" >//ここは各自違います！！
    <Resource name="jdbc/tests"//データソース名
       auth="Container"
        type="javax.sql.DataSource"
        username=""//ｓｑｌのユーザ名
        password=""//ｓｑｌのパスワード名
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/test"//ｍｙｓｑｌのURLとデータベースを記述、今回はtestデータベースを選択、
    />
</Context>

■Jspソースの記述、今回はeclipseのTestプロジェクトのデフォルトパッケージにｊｓｐを置いています。
<%@ page contentType="text/html; charset=Windows-31J"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/tests");//ここはｘｍｌのResource nameと合わせて記述

%>
<% System.out.print(ds); %>//デバック用
<%
Connection conn= ds.getConnection();
Statement pstmt = conn.createStatement();
String sql = "SELECT * FROM ACCOUNT";
ResultSet rs = pstmt.executeQuery(sql);
while(rs.next()){ %>
    <%=rs.getString("NAME")%><BR>
<%}
pstmt.close();
rs.close();
conn.close();
%>

ロック

クライアントが同時にアクセスし、２つの処理が同時に実行された場合に情報の整合
性が取れなくなるのを防ぐ処理方法、


select * from ACCOUNTS WHERE IP=1 FOR UPDATE//FOR UPDATEでロックを行い、コミットでロック解除を行う、

Myql トランザクションのオートコミットをOFF

        Connection con =  DBManager.getConnection();
        con.setAutoCommit(false);//オートコミットをOFF

        String sql = "UPDATE ACCOUNT " + "SET MONEYS=MONEY-1000 WHERE IP=1" ;
        smt.executeUpdate(sql);

        sql = "UPDATE ACCOUNT" + "SET MONEYS=MONEYS-1000 WHERE IP=10" ;
        smt.executeUpdate(sql);


        smt.cancel();　//上記の二つのクエリが処理出来たならコミットする
        con.commit();
        con.close();

テーブルの結合表示

SELECT topicid,title,b_webdiary.catid,category
FROM b_webdiary INNER JOIN b_categories
ON b_webdiary.catid = b_categories.catid;

**********************************
SELECT カラム名,カラム名,テーブル名.カラム名,カラム名
FROM テーブル名 INNER JOIN テーブル名
ON テーブル名.カラム名 = テーブル名.カラム名;