Chapter聽3.聽Tutorial - MySQL 5.1参考手册中文版

mysql> CREATE DATABASE menagerie;

mysql> USE menagerie

Database changed

shell> mysql -h host -u user -p menagerie

Enter password: ********

鍒涘缓鏁版嵁搴撴槸寰堝�规槗鐨勯儴鍒嗭紝浣嗘槸鍦ㄨ繖鏃跺畠鏄�绌虹殑锛屾�ｅ��SHOW TABLES灏嗗憡璇変綘鐨勶細

mysql> SHOW TABLES;

Empty set (0.00 sec)

杈冮毦鐨勯儴鍒嗘槸鍐冲畾浣犵殑鏁版嵁搴撶粨鏋勫簲璇ユ槸浠�涔堬細浣犻渶瑕佷粈涔堟暟鎹�搴撹〃锛屽悇鏁版嵁搴撹〃涓�鏈変粈涔堟牱鐨勫垪銆�

浣犲皢闇�瑕佷竴涓�鍖呭惈浣犳瘡涓�瀹犵墿鐨勮�板綍鐨勮〃銆傚畠鍙�绉颁��pet琛�锛屽苟涓斿畠搴旇�ュ寘鍚�锛屾渶灏戯紝姣忎釜鍔ㄧ墿鐨勫悕瀛椼�傚洜涓哄悕瀛楁湰韬�涓嶆槸寰堟湁瓒ｏ紝琛ㄥ簲璇ュ寘鍚�鍙﹀�栫殑淇℃伅銆備緥濡傦紝濡傛灉鍦ㄤ綘璞㈠吇瀹犵墿鐨勫�跺涵鏈夎秴杩囦竴涓�浜猴紝浣犲彲鑳芥兂瑕佸垪鍑烘瘡涓�鍔ㄧ墿鐨勪富浜恒�備綘鍙�鑳戒篃鎯宠�佽�板綍渚嬪�傜�嶇被鍜屾�у埆鐨勪竴浜涘熀鏈�鐨勬弿杩颁俊鎭�銆�

骞撮緞鍛�锛熼偅鍙�鑳芥湁瓒ｏ紝浣嗘槸瀛樺偍鍒颁竴涓�鏁版嵁搴撲腑涓嶆槸涓�浠跺ソ浜嬫儏銆傚勾榫勯殢鐫�鏃堕棿娴侀�濊�屽彉鍖栵紝杩欐剰鍛崇潃浣犲皢瑕佷笉鏂�鍦版洿鏂颁綘鐨勮�板綍銆傜浉鍙�, 瀛樺偍涓�涓�鍥哄畾鍊间緥濡傜敓鏃ユ瘮杈冨ソ锛岄偅涔堬紝鏃犺�轰綍鏃朵綘闇�瑕佸勾榫勶紝鍙�浠ヤ互褰撳墠鏃ユ湡鍜屽嚭鐢熸棩鏈熶箣闂寸殑宸�鏉ヨ�＄畻瀹冦��MySQL鎻愪緵浜嗘棩鏈熻繍绠楀嚱鏁帮紝鍥犳�よ繖骞朵笉鍥伴毦銆傚瓨鍌ㄥ嚭鐢熸棩鏈熻�岄潪骞撮緞杩樻湁鍏跺畠浼樼偣锛�

路         浣犲彲浠ヤ娇鐢ㄦ暟鎹�搴撳畬鎴愯繖鏍风殑浠诲姟锛屼緥濡傜敓鎴愬嵆灏嗗埌鏉ョ殑瀹犵墿鐢熸棩鐨勬彁绀恒�傦紙濡傛灉浣犺�や负杩欑被鏌ヨ�㈡湁鐐硅牏锛屾敞鎰忥紝杩欎笌浠庡晢鍔℃暟鎹�搴撴潵璇嗗埆鍑轰笉涔呰�佸彂缁欑敓鏃ョ�濊春鐨勫�㈡埛鏄�鍚屼竴涓�闂�棰橈紝鍥犱负璁＄畻鏈哄府鍔╃�佷汉鑱旂粶銆傦級

路         浣犲彲浠ョ浉瀵逛簬鏃ユ湡鑰屼笉姝㈡槸褰撳墠鏃ユ湡鏉ヨ�＄畻骞撮緞銆備緥濡傦紝濡傛灉浣犲湪鏁版嵁搴撳瓨鍌ㄦ�讳骸鏃ユ湡锛屼綘鑳藉緢瀹规槗鍦拌�＄畻鍑轰竴鍙�瀹犵墿姝绘椂鏈夊�氬ぇ銆�

浣犲彲鑳芥兂鍒�pet琛ㄤ腑鍏跺畠鏈夌敤鐨勫叾瀹冪被鍨嬩俊鎭�锛屼絾鏄�鍒扮洰鍓嶄负姝㈣繖浜涘凡缁忚冻澶熶簡锛氬悕瀛椼�佷富浜恒�佺�嶇被锛屾�у埆銆佸嚭鐢熷拰姝讳骸鏃ユ湡銆�

浣跨敤涓�涓�CREATE TABLE璇�鍙ユ寚瀹氫綘鐨勬暟鎹�搴撹〃鐨勫竷灞�锛�

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR閫傚悎浜�name銆�owner鍜�species鍒楋紝鍥犱负鍒楀�兼槸鍙橀暱鐨勩�傝繖浜涘垪鐨勯暱搴︿笉蹇呴兘鐩稿悓锛岃�屼笖涓嶅繀鏄�20銆備綘鍙�浠ユ寫閫変粠1鍒�65535鐨勪换浣曢暱搴︼紝浠庝腑閫夋嫨涓�涓�鏈�鍚堢悊鐨勫�笺�傦紙濡傛灉閫夋嫨寰椾笉鍚堥�傦紝鍚庢潵璇佹槑浣犻渶瑕佷竴涓�鏇撮暱鐨勫瓧娈碉��MySQL鎻愪緵涓�涓�ALTER TABLE璇�鍙ャ�傦級

鍙�浠ョ敤澶氱�嶇被鍨嬬殑鍊兼潵琛ㄧず鍔ㄧ墿璁板綍涓�鐨勬�у埆锛屼緥濡傦紝"m"鍜�"f"锛屾垨"male"鍜�"female"銆備娇鐢ㄥ崟瀛楃��"m"鍜�"f"鏄�鏈�绠�鍗曠殑鏂规硶銆�

寰堟樉鐒讹紝birth鍜�death鍒楀簲閫夌敤DATE鏁版嵁绫汇��

鍒涘缓浜嗘暟鎹�搴撹〃鍚庯��SHOW TABLES搴旇�ヤ骇鐢熶竴浜涜緭鍑猴細

mysql> SHOW TABLES;

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

| Tables in menagerie |

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

| pet                 |

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

涓轰簡楠岃瘉浣犵殑琛ㄦ槸鎸変綘鏈熸湜鐨勬柟寮忓垱寤猴紝浣跨敤涓�涓�DESCRIBE璇�鍙ワ��

mysql> DESCRIBE pet;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| name    | varchar(20) | YES  |     | NULL    |       |

| owner   | varchar(20) | YES  |     | NULL    |       |

| species | varchar(20) | YES  |     | NULL    |       |

| sex     | char(1)     | YES  |     | NULL    |       |

| birth   | date        | YES  |     | NULL    |       |

| death   | date        | YES  |     | NULL    |       |

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

浣犲彲浠ラ殢鏃朵娇鐢�DESCRIBE锛屼緥濡傦紝濡傛灉浣犲繕璁拌〃涓�鐨勫垪鐨勫悕绉版垨绫诲瀷鏃躲��

鍒涘缓琛ㄥ悗锛岄渶瑕佸～鍏ュ唴瀹广�傞�氳繃LOAD DATA鍜�INSERT璇�鍙ュ彲浠ュ畬鎴愯�ヤ换鍔°��

鍋囧畾浣犵殑瀹犵墿绾�褰曟弿杩板�備笅銆傦紙鍋囧畾鍦�MySQL涓�鏈熸湜鐨勬棩鏈熸牸寮忔��YYYY-MM-DD锛涜繖鍙�鑳戒笌浣犱範鎯�鐨勪笉鍚屻�傦級

鍥犱负浣犳槸浠庝竴涓�绌鸿〃寮�濮嬬殑锛屽～鍏呭畠鐨勪竴涓�绠�鏄撴柟娉曟槸鍒涘缓涓�涓�鏂囨湰鏂囦欢锛屾瘡涓�鍔ㄧ墿鍚勪竴琛岋紝鐒跺悗鐢ㄤ竴涓�璇�鍙ュ皢鏂囦欢鐨勫唴瀹硅�呰浇鍒拌〃涓�銆�

浣犲彲浠ュ垱寤轰竴涓�鏂囨湰鏂囦��鈥�pet.txt鈥�锛屾瘡琛屽寘鍚�涓�涓�璁板綍锛岀敤瀹氫綅绗�(tab)鎶婂�煎垎寮�锛屽苟涓斾互CREATE TABLE璇�鍙ヤ腑鍒楀嚭鐨勫垪娆″簭缁欏嚭銆傚�逛簬涓㈠け鐨勫��(渚嬪�傛湭鐭ョ殑鎬у埆锛屾垨浠嶇劧娲荤潃鐨勫姩鐗╃殑姝讳骸鏃ユ湡)锛屼綘鍙�浠ヤ娇鐢�NULL鍊笺�備负浜嗗湪浣犵殑鏂囨湰鏂囦欢涓�琛ㄧず杩欎簺鍐呭�癸紝浣跨敤\N锛堝弽鏂滅嚎锛屽瓧姣�N锛�銆備緥濡傦紝Whistler楦熺殑璁板綍搴斾负(杩欓噷鍊间箣闂寸殑绌虹櫧鏄�涓�涓�瀹氫綅绗�)锛�

瑕佹兂灏嗘枃鏈�鏂囦��鈥�pet.txt鈥�瑁呰浇鍒�pet琛ㄤ腑锛屼娇鐢ㄨ繖涓�鍛戒护锛�

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

璇锋敞鎰忓�傛灉鐢�Windows涓�鐨勭紪杈戝櫒锛堜娇鐢�\r\n鍋氫负琛岀殑缁撴潫绗�锛�鍒涘缓鏂囦欢锛屽簲浣跨敤锛�

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet

    -> LINES TERMINATED BY '\r\n';

锛堝湪杩愯��OS X鐨�Apple鏈轰笂锛屽簲浣跨敤琛岀粨鏉熺��'\r'銆傦級

濡傛灉浣犳効鎰忥紝浣犺兘鏄庣‘鍦板湪LOAD DATA璇�鍙ヤ腑鎸囧嚭鍒楀�肩殑鍒嗛殧绗﹀拰琛屽熬鏍囪�帮紝浣嗘槸榛樿�ゆ爣璁版槸瀹氫綅绗﹀拰鎹㈣�岀�︺�傝繖瀵硅�诲叆鏂囦��鈥�pet.txt鈥�鐨勮��鍙ュ凡缁忚冻澶熴��

濡傛灉璇ヨ��鍙ュけ璐ワ紝鍙�鑳芥槸浣犲畨瑁呯��MySQL涓嶄笌浣跨敤榛樿�ゅ�肩殑鏈�鍦版枃浠跺吋瀹广�傚叧浜庡�備綍鏇存敼璇峰弬瑙�5.6.4鑺傦紝鈥淟OAD DATA LOCAL瀹夊叏闂�棰�鈥�銆�

濡傛灉鎯宠�佷竴娆″�炲姞涓�涓�鏂拌�板綍锛屽彲浠ヤ娇鐢�INSERT璇�鍙ャ�傛渶绠�鍗曠殑褰㈠紡鏄�锛屾彁渚涙瘡涓�鍒楃殑鍊硷紝鍏堕『搴忎笌CREATE TABLE璇�鍙ヤ腑鍒楃殑椤哄簭鐩稿悓銆傚亣瀹�Diane鎶婁竴鍙�鏂颁粨榧犲懡鍚嶄��Puffball锛屼綘鍙�浠ヤ娇鐢ㄤ笅闈㈢��INSERT璇�鍙ユ坊鍔犱竴鏉℃柊璁板綍锛�

mysql> INSERT INTO pet

    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

娉ㄦ剰锛岃繖閲屽瓧绗︿覆鍜屾棩鏈熷�煎潎涓哄紩鍙锋墿璧锋潵鐨勫瓧绗︿覆銆傚彟澶栵紝鍙�浠ョ洿鎺ョ��INSERT璇�鍙ユ彃鍏�NULL浠ｈ〃涓嶅瓨鍦ㄧ殑鍊笺�備笉鑳戒娇鐢�LOAD DATA涓�鎵�绀虹殑鐨�\N銆�

浠庤繖涓�渚嬪瓙锛屼綘搴旇�ヨ兘鐪嬪埌娑夊強寰堝�氱殑閿�鍏ョ敤澶氫釜INSERT璇�鍙ヨ�岄潪鍗曚釜LOAD DATA璇�鍙ヨ�呰浇浣犵殑鍒濆�嬭�板綍銆�

鈥滄渶澶х殑鐗╁搧鍙锋槸浠�涔堬紵鈥�

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

SELECT article, dealer, price

FROM   shop

WHERE  price=(SELECT MAX(price) FROM shop);

SELECT article, dealer, price

FROM shop

ORDER BY price DESC

LIMIT 1;

浠诲姟锛氭瘡椤圭墿鍝佺殑鐨勬渶楂樹环鏍兼槸澶氬皯锛�

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

浠诲姟锛氬�规瘡椤圭墿鍝侊紝鎵惧嚭鏈�璐典环鏍肩殑鐗╁搧鐨勭粡閿�鍟嗐��

鍙�浠ョ敤杩欐牱涓�涓�瀛愭煡璇㈣В鍐宠�ラ棶棰橈細

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

浣犲彲浠ユ竻绌�MySQL鐢ㄦ埛鍙橀噺浠ヨ�板綍缁撴灉锛屼笉蹇呭皢瀹冧滑淇濆瓨鍒板�㈡埛绔�鐨勪复鏃跺彉閲忎腑銆傦紙鍙傝�� 9.3鑺傦紝鈥滅敤鎴峰彉閲忊��.锛夈��

渚嬪�傦紝瑕佹壘鍑轰环鏍兼渶楂樻垨鏈�浣庣殑鐗╁搧鐨勶紝鍏舵柟娉曟槸锛�

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

鍦�MySQL涓�锛�InnoDB琛ㄦ敮鎸佸�瑰�栭儴鍏抽敭瀛楃害鏉熸潯浠剁殑妫�鏌ャ�傚弬瑙�15.2鑺傦紝鈥淚nnoDB瀛樺偍寮曟搸鈥�銆傝繕鍙�浠ュ弬瑙� 1.8.5.5鑺傦紝鈥滃�栭敭鈥�銆�

鍙�鏄�鑱旀帴涓や釜琛ㄦ椂锛屼笉闇�瑕佸�栭儴鍏抽敭瀛椼�傚�逛簬闄�InnoDB绫诲瀷鐨勮〃锛屽綋浣跨敤REFERENCES tbl_name(col_name)瀛愬彞瀹氫箟鍒楁椂鍙�浠ヤ娇鐢ㄥ�栭儴鍏抽敭瀛楋紝璇ュ瓙鍙ユ病鏈夊疄闄呯殑鏁堟灉锛�鍙�浣滀负澶囧繕褰曟垨娉ㄩ噴鏉ユ彁閱掞紝浣犵洰鍓嶆�ｅ畾涔夌殑鍒楁寚鍚戝彟涓�涓�琛ㄤ腑鐨勪竴涓�鍒椼��鎵ц�岃�ヨ��鍙ユ椂锛屽疄鐜颁笅闈㈠緢閲嶈�侊��

路         MySQL涓嶆墽琛岃〃tbl_name 涓�鐨勫姩浣滐紝渚嬪�備綔涓轰綘姝ｅ畾涔夌殑琛ㄤ腑鐨勮�岀殑鍔ㄤ綔鐨勫搷搴旇�屽垹闄よ�岋紱鎹㈠彞璇濊�达紝璇ュ彞娉曚笉浼氳嚧浣�ON DELETE鎴�ON UPDATE琛屼负锛堝�傛灉浣犲��REFERENCES瀛愬彞涓�鍐欏��ON DELETE鎴�ON UPDATE瀛愬彞锛屽皢琚�蹇界暐锛夈��

路         璇ュ彞娉曞彲浠ュ垱寤轰竴涓�column锛涗絾涓嶅垱寤轰换浣曠储寮曟垨鍏抽敭瀛椼��

路         濡傛灉鐢ㄨ�ュ彞娉曞畾涔�InnoDB琛�锛屽皢浼氬�艰嚧閿欒��銆�

浣犲彲浠ヤ娇鐢ㄤ綔涓鸿仈鎺ュ垪鍒涘缓鐨勫垪锛屽�備笅鎵�绀猴細

CREATE TABLE person (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    name CHAR(60) NOT NULL,

    PRIMARY KEY (id)

);

CREATE TABLE shirt (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,

    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

    PRIMARY KEY (id)

);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES

(NULL, 'polo', 'blue', @last),

(NULL, 'dress', 'white', @last),

(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES

(NULL, 'dress', 'orange', @last),

(NULL, 'polo', 'red', @last),

(NULL, 'dress', 'blue', @last),

(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;

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

| id | name                |

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

|  1 | Antonio Paz         |

|  2 | Lilliana Angelovska |

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

SELECT * FROM shirt;

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

| id | style   | color  | owner |

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

|  1 | polo    | blue   |     1 |

|  2 | dress   | white  |     1 |

|  3 | t-shirt | blue   |     1 |

|  4 | dress   | orange |     2 |

|  5 | polo    | red    |     2 |

|  6 | dress   | blue   |     2 |

|  7 | t-shirt | white  |     2 |

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

SELECT s.* FROM person p, shirt s

 WHERE p.name LIKE 'Lilliana%'

   AND s.owner = p.id

   AND s.color <> 'white';

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

| id | style | color  | owner |

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

|  4 | dress | orange |     2 |

|  5 | polo  | red    |     2 |

|  6 | dress | blue   |     2 |

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

鎸夌収杩欑�嶆柟寮忎娇鐢�锛�REFERENCES瀛愬彞涓嶄細鏄剧ず鍦�SHOW CREATE TABLE鎴�DESCRIBE鐨勮緭鍑轰腑:

SHOW CREATE TABLE shirt\G

*************************** 1. row ***************************

Table: shirt

Create Table: CREATE TABLE `shirt` (

`id` smallint(5) unsigned NOT NULL auto_increment,

`style` enum('t-shirt','polo','dress') NOT NULL,

`color` enum('red','blue','orange','white','black') NOT NULL,

`owner` smallint(5) unsigned NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

鍦ㄥ垪瀹氫箟涓�锛屾寜杩欑�嶆柟寮忎娇鐢�REFERENCES浣滀负娉ㄩ噴鎴栤�滄彁绀衡�濋�傜敤浜庤〃MyISAM鍜�BerkeleyDB銆�

鍙�浠ュ厖鍒嗗埄鐢ㄤ娇鐢ㄥ崟鍏抽敭瀛楃��OR瀛愬彞锛屽�傚��AND鐨勫�勭悊銆�

涓�涓�姣旇緝鐏垫椿鐨勪緥瀛愭槸瀵绘壘涓や釜閫氳繃OR缁勫悎鍒颁竴璧风殑鍏抽敭瀛楋細

SELECT field1_index, field2_index FROM test_table

WHERE field1_index = '1' OR  field2_index = '1'

璇ユ儏褰㈡槸宸茬粡浼樺寲杩囩殑銆傚弬瑙�7.2.6鑺傦紝鈥滅储寮曞悎骞朵紭鍖栤��銆�

杩樺彲浠ヤ娇鐢�UNION灏嗕袱涓�鍗曠嫭鐨�SELECT璇�鍙ョ殑杈撳嚭鍚堟垚鍒颁竴璧锋潵鏇存湁鏁堝湴瑙ｅ喅璇ラ棶棰樸�傚弬瑙�13.2.7.2鑺傦紝鈥淯NION璇�娉�
鈥�銆�

姣忎釜SELECT鍙�鎼滅储涓�涓�鍏抽敭瀛楋紝鍙�浠ヨ繘琛屼紭鍖栵細

SELECT field1_index, field2_index

    FROM test_table WHERE field1_index = '1'

UNION

SELECT field1_index, field2_index

    FROM test_table WHERE field2_index = '1';

涓嬮潰鐨勪緥瀛愭樉绀轰簡濡備綍浣跨敤浣嶇粍鍑芥暟鏉ヨ�＄畻姣忎釜鏈堜腑鐢ㄦ埛璁块棶缃戦〉鐨勫ぉ鏁般��

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

             day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

            (2000,2,23),(2000,2,23);

绀轰緥琛ㄤ腑鍚�鏈変唬琛ㄧ敤鎴疯�块棶缃戦〉鐨勫勾锛嶆湀锛嶆棩鍊笺�傚彲浠ヤ娇鐢ㄤ互涓嬫煡璇㈡潵纭�瀹氭瘡涓�鏈堢殑璁块棶澶╂暟锛�

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1

       GROUP BY year,month;

灏嗚繑鍥烇細

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

| year | month | days |

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

| 2000 |    01 |    3 |

| 2000 |    02 |    2 |

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

璇ユ煡璇㈣�＄畻浜嗗湪琛ㄤ腑鎸夊��/鏈堢粍鍚堢殑涓嶅悓澶╂暟锛屽彲浠ヨ嚜鍔ㄥ幓闄ら噸澶嶇殑璇㈤棶銆�

鍙�浠ラ�氳繃AUTO_INCREMENT灞炴�т负鏂扮殑琛屼骇鐢熷敮涓�鐨勬爣璇嗭細

CREATE TABLE animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

 );

INSERT INTO animals (name) VALUES 

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

灏嗚繑鍥烇細

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

| id | name    |

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

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | whale   |

|  6 | ostrich |

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

浣犲彲浠ヤ娇鐢�LAST_INSERT_ID()SQL鍑芥暟鎴�mysql_insert_id() C API鍑芥暟鏉ユ煡璇㈡渶鏂扮殑AUTO_INCREMENT鍊笺�傝繖浜涘嚱鏁颁笌鍏蜂綋杩炴帴鏈夊叧锛屽洜姝ゅ叾杩斿洖鍊间笉浼氳��鍏跺畠鎵ц�屾彃鍏ュ姛鑳界殑杩炴帴褰卞搷銆�

娉ㄩ噴锛�瀵逛簬澶氳�屾彃鍏ワ��LAST_INSERT_ID()鍜�mysql_insert_id()浠庢彃鍏ョ殑绗�涓�琛屽疄闄呰繑鍥�AUTO_INCREMENT鍏抽敭瀛椼�傚湪澶嶅埗璁剧疆涓�锛岄�氳繃璇ュ嚱鏁板彲浠ュ湪鍏跺畠鏈嶅姟鍣ㄤ笂姝ｇ‘澶嶅埗澶氳�屾彃鍏ャ��

瀵逛簬MyISAM鍜�BDB琛�锛屼綘鍙�浠ュ湪绗�浜屾爮鎸囧��AUTO_INCREMENT浠ュ強澶氬垪绱㈠紩銆傛�ゆ椂锛�AUTO_INCREMENT鍒楃敓鎴愮殑鍊肩殑璁＄畻鏂规硶涓猴細MAX(auto_increment_column) + 1 WHERE prefix=given-prefix銆傚�傛灉鎯宠�佸皢鏁版嵁鏀惧叆鍒版帓搴忕殑缁勪腑鍙�浠ヤ娇鐢ㄨ�ユ柟娉曘��

CREATE TABLE animals (

    grp ENUM('fish','mammal','bird') NOT NULL,

    id MEDIUMINT NOT NULL AUTO_INCREMENT,

    name CHAR(30) NOT NULL,

    PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name) VALUES 

    ('mammal','dog'),('mammal','cat'),

    ('bird','penguin'),('fish','lax'),('mammal','whale'),

    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

灏嗚繑鍥烇細

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

| grp    | id | name    |

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

| fish   |  1 | lax     |

| mammal |  1 | dog     |

| mammal |  2 | cat     |

| mammal |  3 | whale   |

| bird   |  1 | penguin |

| bird   |  2 | ostrich |

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

璇锋敞鎰忓湪杩欑�嶆儏鍐典笅锛�AUTO_INCREMENT鍒楁槸澶氬垪绱㈠紩鐨勪竴閮ㄥ垎锛夛紝濡傛灉浣犲湪浠讳綍缁勪腑鍒犻櫎鏈夋渶澶�AUTO_INCREMENT鍊肩殑琛岋紝灏嗕細閲嶆柊鐢ㄥ埌AUTO_INCREMENT鍊笺�傚�逛��MyISAM琛ㄤ篃濡傛��,瀵逛簬璇ヨ〃涓�鑸�涓嶉噸澶嶄娇鐢�AUTO_INCREMENT鍊笺��

濡傛灉AUTO_INCREMENT鍒楁槸澶氱储寮曠殑涓�閮ㄥ垎锛�MySQL灏嗕娇鐢ㄨ�ョ储寮曠敓鎴愪��AUTO_INCREMENT鍒楀紑濮嬬殑搴忓垪鍊笺�傘�備緥濡傦紝濡傛灉animals琛ㄥ惈鏈夌储寮�PRIMARY KEY (grp, id)鍜�INDEX(id)锛�MySQL鐢熸垚搴忓垪鍊兼椂灏嗗拷鐣�PRIMARY KEY銆傜粨鏋滄槸锛岃�ヨ〃鍖呭惈涓�涓�鍗曚釜鐨勫簭鍒楋紝鑰屼笉鏄�绗﹀��grp鍊肩殑搴忓垪銆�

瑕佹兂浠�AUTO_INCREMENT鍊煎紑濮嬭�屼笉鏄�1锛屼綘鍙�浠ラ�氳繃CREATE TABLE鎴�ALTER TABLE鏉ヨ�剧疆璇ュ�硷紝濡備笅鎵�绀�:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

鍏充簬AUTO_INCREMENT鐨勮�︾粏淇℃伅锛�

路         濡備綍涓哄垪鎸囧畾AUTO_INCREMENT灞炴�э細13.1.5鑺傦紝鈥淐REATE TABLE璇�娉曗��鍜� 13.1.2鑺傦紝鈥淎LTER TABLE璇�娉曗��銆�

路         AUTO_INCREMENT鐨勫姩浣滃彇鍐充簬SQL妯″紡锛�5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊��銆�

路         鎵惧嚭鍚�鏈夋渶鏂�AUTO_INCREMENT鍊肩殑琛岋細12.1.3鑺傦紝鈥滄瘮杈冨嚱鏁板拰鎿嶄綔绗︹��銆�

路         璁剧疆灏嗙敤鍒扮殑AUTO_INCREMENT鍊�: 13.5.3鑺傦紝鈥淪ET璇�娉曗�� 銆�

路         AUTO_INCREMENT鍜屽�嶅埗锛�6.7鑺傦紝鈥滃�嶅埗鐗规�у拰宸茬煡闂�棰樷��.

路         AUTO_INCREMENT鐩稿叧鐨勫彲鐢ㄤ簬澶嶅埗鐨�Server-system鍙橀噺(auto_increment_increment鍜�auto_increment_offset)锛�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

涓嬪垪鏌ヨ�㈢敤鏉ュ喅瀹氳皝杩涘叆椤圭洰鐨勭��浜岄儴鍒嗭��

SELECT
    CONCAT(p1.id, p1.tvab) + 0 AS tvid,
    CONCAT(p1.christian_name, ' ', p1.surname) AS Name,
    p1.postal_code AS Code,
    p1.city AS City,
    pg.abrev AS Area,
    IF(td.participation = 'Aborted', 'A', ' ') AS A,
    p1.dead AS dead1,
    l.event AS event1,
    td.suspect AS tsuspect1,
    id.suspect AS isuspect1,
    td.severe AS tsevere1,
    id.severe AS isevere1,
    p2.dead AS dead2,
    l2.event AS event2,
    h2.nurse AS nurse2,
    h2.doctor AS doctor2,
    td2.suspect AS tsuspect2,
    id2.suspect AS isuspect2,
    td2.severe AS tsevere2,
    id2.severe AS isevere2,
    l.finish_date
FROM
    twin_project AS tp
    /* For Twin 1 */
    LEFT JOIN twin_data AS td ON tp.id = td.id
              AND tp.tvab = td.tvab
    LEFT JOIN informant_data AS id ON tp.id = id.id
              AND tp.tvab = id.tvab
    LEFT JOIN harmony AS h ON tp.id = h.id
              AND tp.tvab = h.tvab
    LEFT JOIN lentus AS l ON tp.id = l.id
              AND tp.tvab = l.tvab
    /* For Twin 2 */
    LEFT JOIN twin_data AS td2 ON p2.id = td2.id
              AND p2.tvab = td2.tvab
    LEFT JOIN informant_data AS id2 ON p2.id = id2.id
              AND p2.tvab = id2.tvab
    LEFT JOIN harmony AS h2 ON p2.id = h2.id
              AND p2.tvab = h2.tvab
    LEFT JOIN lentus AS l2 ON p2.id = l2.id
              AND p2.tvab = l2.tvab,
    person_data AS p1,
    person_data AS p2,
    postal_groups AS pg
WHERE
    /* p1 gets main twin and p2 gets his/her twin. */
    /* ptvab is a field inverted from tvab */
    p1.id = tp.id AND p1.tvab = tp.tvab AND
    p2.id = p1.id AND p2.ptvab = p1.tvab AND
    /* Just the screening survey */
    tp.survey_no = 5 AND
    /* Skip if partner died before 65 but allow emigration (dead=9) */
    (p2.dead = 0 OR p2.dead = 9 OR
     (p2.dead = 1 AND
      (p2.death_date = 0 OR
       (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
        >= 65))))
    AND
    (
    /* Twin is suspect */
    (td.future_contact = 'Yes' AND td.suspect = 2) OR
    /* Twin is suspect - Informant is Blessed */
    (td.future_contact = 'Yes' AND td.suspect = 1
                               AND id.suspect = 1) OR
    /* No twin - Informant is Blessed */
    (ISNULL(td.suspect) AND id.suspect = 1
                        AND id.future_contact = 'Yes') OR
    /* Twin broken off - Informant is Blessed */
    (td.participation = 'Aborted'
     AND id.suspect = 1 AND id.future_contact = 'Yes') OR
    /* Twin broken off - No inform - Have partner */
    (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                  AND p2.dead = 0))
    AND
    l.event = 'Finished'
    /* Get at area code */
    AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
    /* Not already distributed */
    AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
    /* Has not refused or been aborted */
    AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
    OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
    tvid;

涓�浜涜В閲婏細

路         CONCAT(p1.id, p1.tvab) + 0 AS tvid

鎴戜滑鎯宠�佸��id鍜�tvab鐨勮繛鎺ヤ笂浠ユ暟瀛楅『搴忔帓搴忋�傜粨鏋滃姞0浣垮緱MySQL鎶婄粨鏋滃彉涓轰竴涓�鏁板瓧銆�

路         鍒�id

杩欐爣璇嗕竴瀵瑰��鐢熴�傚畠鏄�鎵�鏈夎〃涓�鐨勪竴涓�閿�銆�

路         鍒�tvab

杩欐爣璇嗗��鐢熶腑鐨勪竴涓�銆傚畠鐨勫�间负1鎴�2銆�

路         鍒�ptvab

杩欐槸tvab鐨�涓�涓�閫嗚繍绠椼�傚綋tvab鏄�1锛屽畠鏄�2锛屽弽涔嬩害鐒躲�傚畠鐢ㄦ潵淇濆瓨杈撳叆骞朵笖浣�MySQL鐨�浼樺寲鏌ヨ�㈡洿瀹规槗銆�

杩欎釜鏌ヨ�㈣〃鏄庯紝鎬庢牱鐢ㄨ仈缁�(p1鍜�p2)浠庡悓涓�涓�琛ㄤ腑鏌ユ壘琛ㄣ�傚湪渚嬪瓙涓�锛岃繖琚�鐢ㄦ潵妫�鏌ュ��鐢熺殑涓�涓�鏄�鍚﹀湪65宀佸墠姝讳簡銆傚�傛灉濡傛�わ紝琛屼笉杩斿洖鍊笺��

涓婅堪鎵�鏈夊��鐢熶俊鎭�瀛樺湪浜庢墍鏈夎〃涓�銆傛垜浠�瀵�id,tvab锛堟墍鏈夎〃锛夊拰id,ptvab (person_data) 涓婇噰鐢ㄩ敭浠ヤ娇鏌ヨ�㈡洿蹇�銆�

鍦ㄦ垜浠�鐨勭敓浜ф満鍣ㄤ��(涓�鍙�200MHz UltraSPARC)锛岃繖涓�鏌ヨ�㈣繑鍥炲ぇ绾� 150-200 琛屽苟涓旀椂闂翠笉瓒呰繃涓�绉掋��