また土曜日です～MySQL特訓～MySQL_PHPチュートリアル-PHPチュートリアル-php.cn

また土曜日です -MySQL特訓、-MySQL

こんにちは

また土曜日、長い朝ですよ〜お昼を食べてから始めましょう

1.MySQL

-----サブクエリと接続(3)-----

----INSERT...SELECT を使用してレコードを挿入します

--英語版のデータベースコンテンツ

WAMP の MYSQL コンソールで中国語が弱いことがわかり、変更プロセス中に疲れ果てたので、戦術を変更してデータをすべて英語に変更しました

。

--データテーブルを作成する

CREATE TABLE IF NOT EXISTS Goods(
Goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
Goods_name VARCHAR(150) NOT NULL,
Goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
Goods_price DECIMAL(15,3 ) UNSIGNED NOT NULL デフォルト 0、
is_show ブール値 NOT NULL デフォルト 1、
is_saleoff ブール値 NOT NULL デフォルト 0
);

-- 記録を書きます

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6Inches Laptop','Laptop','ASUS','3399',DEFAULT,DEFAULT);

商品を挿入 (goods_name,goods_cate) ,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0インチラップトップ','ラップトップ','Lenovo','4899',DEFAULT,DEFAULT);

商品を挿入 (goods_name,goods_cate,brand_name,goods_price,is_show, is_saleoff) VALUES('G150TH 15.6インチゲームブック','ゲームブック','ThunderGod','8499',DEFAULT,DEFAULT);

商品を挿入 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6インチ Laptop','Laptop','ASUS','2799',DEFAULT,DEFAULT);

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5 インチ Ultrabook',' Ultrabook','Lenovo','4999',DEFAULT,DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3 インチ Ultrabook','Ultrabook','Lenovo', '4299',DEFAULT,DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3 インチ Touch Ultrabook','Ultrabook','Sony','7999',DEFAULT, DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9 インチタブレット','タブレット','Apple','1998',DEFAULT,DEFAULT);

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) を挿入 VALUES('iPad Air MD788CH/A 9.7 インチタブレット (16G WiFi ビジョン)','タブレット','Apple','3388',DEFAULT,DEFAULT );

INSERT Goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A Retina スクリーン 7.9 インチタブレット (16G WiFi ビジョン)','タブレット','Apple','2788' ,DEFAULT,DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20 インチオールインワン ','デスクトップ','Lenovo','3499',DEFAULT, DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 デスクトップ','デスクトップ','Dell','2899',DEFAULT,DEFAULT);

INSERT Goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5インチオールインワン','デスクトップ','Apple','9188',DEFAULT,DEFAULT);

INSERT Goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP デスクトップ (i5-3450 4G 500G 2G 独立したグラフィックスカード DVD キーボードとマウス Linux )','デスクトップ','Acer','3699',DEFAULT, DEFAULT);

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PAWork station','server/Work station','HP','4288',DEFAULT,DEFAULT);

INSERT 商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 IIserver','server/Work station','Dell','5388',DEFAULT,DEFAULT);

INSERT 商品 (goods_name, Goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A Professional Desktop','server/Work station','Apple','28888',DEFAULT,DEFAULT);

商品を挿入 (goods_name,goods_cate) ,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W ヘッドセットディスプレイデバイス','ラップトップアクセサリ','Sony','6999',DEFAULT,DEFAULT);

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptopaccessory','Sony','99',DEFAULT,DEFAULT);

を挿入します。

商品を挿入 (商品名,商品カテゴリー,ブランド名,商品価格,is_show,is_saleoff) VALUES('X3250 M4 サーバー 2583i14','サーバー/ワークステーション','IBM','6888',DEFAULT,DEFAULT);

商品を挿入 ( Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Xuanlong ラップトップラジエーター','ラップトップアクセサリ','Windgod','',DEFAULT,DEFAULT);

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W ヘッドセットディスプレイデバイス','ラップトップアクセサリ','Sony','6999',DEFAULT,DEFAULT);

を挿入します。

商品 (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptop Accessories','Sony','99',DEFAULT,DEFAULT);

を挿入します。

---挿入...選択

INSERT [INTO] tbl_name [(col_name)] SELECT

使用するときはこのように、新しいテーブルを作成します

存在しない場合はテーブルを作成する cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

次に、元のデータテーブルにあるものを選択的に挿入します

CATES(CATE_NAME) に挿入 GOODS_CATE ごとに商品グループから GOODS_CATE を選択します;

カタログテーブルカテゴリに基づいてパラメータテーブルを更新する必要がある場合 - マルチテーブル更新

----複数のテーブルの更新

--複数のステップ

UPDATE table_ref SETcol_name=... ...

このうち、refはテーブルの参照関係です

ここにはINNER JOIN LEFT JOINなどの接続関係があります

最も単純なものはINNER、内なるつながりです

mysql> GOODS_CATE=CATE_NAME の GOODS 内部結合 CATES を更新します
-> SET GOODS_CATE=CATE_ID;

(小文字で入力していないことをご容赦ください...)

説明: 商品テーブルを更新し、カテゴリテーブルを内部的に接続します。どのような条件下で - Goods_cate=cate_name、設定値は何か、カテゴリテーブルの cate_id です

mysql> GOODSG から * を選択してください

--一歩

作成...選択...

つまり、テーブルの作成と接続されたデータの書き込みを同時に行うことです

ここでの例は次のとおりです: ブランドへの接続を更新する

mysql> ブランド名ごとに商品グループからブランド名を選択します;

mysql> 存在しない場合はテーブルを作成します brand(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> ブランド名ごとに商品グループからブランド名を選択します

INSERT操作を保存できます

ただし、現時点ではアップデートが失敗します

mysql> BRAND_NAME=BRAND_NAME の商品の内部結合ブランドを更新します
-> SET BRAND_NAME=BRAND_ID;
エラー 1052 (23000): チャンピオン: フィールドリストが曖昧です

名前が同じなので、フィールドを変更するか、エイリアス AS を使用する必要があります

mysql> 商品を G.BRAND_NAME=B.BRAND_NAME に結合ブランドとして更新します
-> G.BRAND_NAME=BRAND_ID;

外観は更新されましたが、たとえば、goods_cate の型は依然として varchar であり、実際にはメインテーブルでは int 型である必要があります。この時、フィールド名とデータ型を変更する必要があるため、ALTER CHANGEを使用します

mysql> テーブル GOODS を変更します
-> GOODS_CATE CATE_ID SMALLINT UNSIGNED NOT NULL、
-> BRAND_NAME BRAND_ID SMALLINT UNSIGNED NOT NULL;

この種の外部キーはファクト外部キーと呼ばれ、外部キーよりも多くの物理的な外部キーがあります

---接続

複数のテーブルの格納を実装しているため、表示やその他の要件により、複数のテーブルをリンクする必要があり、接続が必要です

データテーブル参照参照: tbl_name [[AS] エイリアス] table_subquery [AS] エイリアス

---内部結合

ON を使用して接続条件を設定します。通常、WHERE はさらなるフィルタリングを行います

内部接続、接続条件を満たすレコードのみを表示

GOODS.CATE_ID=CATES.CATE_ID;

GOODS.CATE_ID=CATES.CATE_ID の GOODS 内部結合 CATES から GOODS_ID、GOODS_NAME、CATE_NAME を選択します。

翻訳: Goods テーブルの Goods_id、 Goods_name、cate_name フィールドを選択しますが、表示する場合は、内部で cates テーブルを接続する必要があります (タイプの数値表現を表示したくないため)。条件は、ここでは、cate_id フィールドは等しいです。cate_id には別名が与えられていないため、テーブル名.cate_id

として記述されます。

この内部接続の表示には、オン条件の結果のみが表示されることがわかります

---外部結合

外部結合は左外部結合と右外部結合に分かれます

LEFT JOIN は、リンク条件を満たす左側のテーブルのすべてのレコードと右側のテーブルのレコードを表示します。例は次のとおりです。

注意最後の一つの记录の名前は null です

右結合はこれです

----多表接

mysql> G としての商品から GOODS_ID、GOODS_NAME、CATE_NAME、BRAND_NAME、GOODS_PRICE を選択します
->内部結合 CATES AS C ON G.CATE_ID=C.CATE_ID
->内部結合ブランドを B ON G.BRAND_ID=B.BRAND_ID;

每接続一表するには結合が必要です、注意起别名给データテーブル！

今回の表示は虽然跟一始まり一样ですが、现違い、意义大不一样

----無制限级分类表设计

一般实际中会出现無制限级データテーブル，不断往下分

举个例子

CREATE TABLE Goods_types(

type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT Goods_types(type_name,parent_id) VALUES('家電',DEFAULT);
INSERT Goods_types(type_name,parent_id) VALUES('PC、Office',DEFAULT);
INSERT Goods_types(type_name,parent_id) VALUES('家電' ,1);
INSERT Goods_types(type_name,parent_id) VALUES('生活家電',1);
INSERT Goods_types(type_name,parent_id) VALUES('タブレット TV',3);
INSERT Goods_types(type_name,parent_id) VALUES( 'エアコン',3);
INSERT Goods_types(type_name,parent_id) VALUES('扇風機',4);
INSERT Goods_types(type_name,parent_id) VALUES('ウォーターディスペンサー',4);
INSERT Goods_types(type_name, parent_id) VALUES('完成機',2);
INSERT Goods_types(type_name,parent_id) VALUES('アクセサリ',2);
INSERT Goods_types(type_name,parent_id) VALUES('ラップトップ',9);
INSERT Goods_types( type_name,parent_id) VALUES('Ultrabook',9);
INSERT Goods_types(type_name,parent_id) VALUES('Gamebook',9);
INSERT Goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT Goods_types (type_name,parent_id) VALUES('ホストコンピューター',10);

mysql> SELECT * FROM GOODS_TYPES;
+---------+------------------+-----------+
|タイプID |タイプ名 |親 ID |
+-----------+---------------------+-----------+
| 1 |ホームエレクトロニクス | 0 |
| 2 |パソコン?オフィス | 0 |
| 3 |家電 | 1 |
| 4 |生活家電 | 1 |
| 5 |タブレットテレビ | 3 |
| 6 |エアコン | 3 |
| 7 |扇風機 | 4 |
| 8 |ウォーターディスペンサー | 4 |
| 9 |完成したマシン | 2 |
| 10 |アクセサリー | 2 |
| 11 |ラップトップ | 9 |
| 12 |ウルトラブック | 9 |
| 13 |ゲームブック | 9 |
| 14 | CPU | 10 |
| 15 |ホストコンピュータ | 10 |
+-----------+---------------------+-----------+

注意parent_id，是変更点の级别，比如0，表示家電無し父节点，是个顶级节点

查找の実現は、自分自身の接続によって完了します

---自己接続

举个例，表示すべてのクラスの父类は何ですか

ここで、自分自身の接続は、右边有一模一样の表を想起することができます（左边も可、いくつかの西稍微变一下行）

mysql>左の GOODS_TYPES から S.TYPE_ID、S.TYPE_NAME、P.TYPE_NAME を選択し、P として GOODS_TYPES に結合します
-> ON S.PARENT_ID=P.TYPE_ID;
+---------+---------------------+---------- --------+
|タイプID |タイプ名 | TYPE_NAME |
+----------+----------------------+----------------- -+
| 1 |ホームエレクトロニクス | NULL |
| 2 |パソコン?オフィス | NULL |
| 3 |家電 |家電 |
| 4 |生活家電 |家電 |
| 5 |タブレットテレビ |家電 |
| 6 |エアコン |家電 |
| 7 |扇風機 |生活家電 |
| 8 |ウォーターディスペンサー |生活家電 |
| 9 |完成したマシン | PC?オフィス |
| 10 |アクセサリー | PC?オフィス |
| 11 |ラップトップ |完全なマシン |
| 12 |ウルトラブック |完全なマシン |
| 13 |ゲームブック |完全なマシン |
| 14 | CPU |アクセサリー |
| 15 |ホストコンピュータ |アクセサリー |
+-----------+---------------------+----------------- -+

翻訳一下、つまり、条件から先に、子表内のparent_id=父表内のtype_idを選択することができ、子表のtype_id、type_name、および父表のtype_nameが表示され、ここの項目は句名に含めることができます。末尾处显表示

要これを把握するための先说说/想想を明確にし、再翻訳してsql语句

反过来、查找すべてのクラスの子クラス

先考虑逻辑关系：当父表のtype_id=子表のparent_id時，P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME ——再写出语句

mysql> GOODS_TYPES から P.TYPE_ID、P.TYPE_NAME、S.TYPE_NAME を選択して左側に GOODS_TYPES を結合します
-> ON P.TYPE_ID=S.PARENT_ID;
+---------+---------------------+---------- --------+
|タイプID |タイプ名 | TYPE_NAME |
+----------+----------------------+----------------- -+
| 1 |ホームエレクトロニクス |家電 |
| 1 |ホームエレクトロニクス |生活家電 |
| 3 |家電 |タブレットテレビ |
| 3 |家電 |エアコン |
| 4 |生活家電 |扇風機 |
| 4 |生活家電 |ウォーターディスペンサー |
| 2 |パソコン?オフィス |完全なマシン |
| 2 |パソコン?オフィス |アクセサリー |
| 9 |完成したマシン |ラップトップ |
| 9 |完成したマシン |ウルトラブック |
| 9 |完成したマシン |ゲームブック |
| 10 |アクセサリー | CPU |
| 10 |アクセサリー |ホストコンピューター |
| 5 |タブレットテレビ | NULL |
| 6 |エアコン | NULL |
| 7 |扇風機 | NULL |
| 8 |ウォーターディスペンサー | NULL |
| 11 |ラップトップ | NULL |
| 12 |ウルトラブック | NULL |
| 13 |ゲームブック | NULL |
| 14 | CPU | NULL |
| 15 |ホストコンピュータ | NULL |
+----------+---------------------+----------------- -+

再复杂一点、そして留给你们自己翻訳

mysql> SELECT P.TYPE_ID,P.TYPE_NAME,COUNT(S.TYPE_NAME) CHILD_COUNT FROM GOODS_TYPES AS P LEFT GOODS_TYPES AS S に参加
-> ON P.TYPE_ID=S.PARENT_ID P.TYPE_NAME によるグループ ORDER BY P.TYPE_ID;
+---------+------------------+ -------------+
|タイプID |タイプ名 | CHILD_COUNT |
+-----------+------------------+-------------+
| 1 |ホームエレクトロニクス | 2 |
| 2 |パソコン?オフィス | 2 |
| 3 |家電 | 2 |
| 4 |生活家電 | 2 |
| 5 |タブレットテレビ | 0 |
| 6 |エアコン | 0 |
| 7 |扇風機 | 0 |
| 8 |ウォーターディスペンサー | 0 |
| 9 |完成したマシン | 3 |
| 10 |アクセサリー | 2 |
| 11 |ラップトップ | 0 |
| 12 |ウルトラブック | 0 |
| 13 |ゲームブック | 0 |
| 14 | CPU | 0 |
| 15 |ホストコンピュータ | 0 |
+----------+---------------------+---------------+

----多表删除

基本的な思路も一张表模倣多张表による操作

懒了、直接复制看吧、要求了再去做

-- 挿入 ... 選択实现复制

商品(商品名,商品ID,ブランドID)を挿入します商品名,商品ID,ブランドIDをFROM商品WHERE商品ID IN (19,20);

-- 查找重复记录

商品 GROUP BY 商品名 HAVING count(商品名) >= 2;から商品 ID、商品名を選択します

-- 删除重复记录

DELETE t1 FROM Goods AS t1 LEFT JOIN (SELECT Goods_id,goods_name FROM Goods GROUP BY Goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;