该系åæç« ç³»ä¸ªäººè¯»ä¹¦ç¬è®°åæ»ç»æ§å 容ï¼ä»»ä½ç»ç»å个人ä¸å¾è½¬è½½è¿è¡åä¸æ´»å¨ï¼
Â
èªæç表设计ï¼ä¸ºä»ä¹è¦è§èå
Â
æ¬ç« å¤æ¯ç论ï¼è¯·æ³¨æç解ï¼
   æ们å°ç®å为æ¢å建ç表ï¼é½æ²¡æç»è¿ä»ç»èèï¼éçæ°æ®çè¶æ¥è¶å¤ï¼æ们éè¦èèçæ´å¤ï¼å¥½è®©ç°å¨çWHEREåå¥ç®åä¸ç¹ï¼æ们éè¦è®©è¡¨æ´æ£å¸¸ãæ´è§èï¼
Â
ä¸¤å¼ é±¼ç表ï¼
表ä¸ï¼
é±¼çå¦åââé±¼çä¿åââééââæè·å°ç¹
common       species     weight  location
èç           è鱼        500g     æ¥å¹²æ¹ï¼åæ
Â
表äºï¼
æè·è çå§åââé±¼çä¿åââééââæè·æ°´åââæè·å°åºââæè·æ¥æ
fitst_name  last_name  common  weight  location  state           date
hua          Q             èç     500g   æ¥å¹²æ¹Â     åæ          6/2/2018
Â
表ä¸ç¼ºä¹æè·è çä¿¡æ¯ï¼
  ä½æ¯ä¸å鱼类ç 究è ï¼å¨æ¥æ¾è¡¨çæ¶åï¼ä¸è¿°4个å段ææ¯ä»å ³æ³¨çï¼
Â
表äºçä¿¡æ¯ç¸å¯¹æ´ç»è´ï¼
  ä½ä¸ºä¸åæå¿æ°ç¨¿äººï¼äºè§£é±¼çè¿äºä¿¡æ¯è¿æ¯å¾æå¿ è¦çï¼
Â
对äºä¸è¿°ä¸¤å¼ 表é对åæææçé±¼çè®°å½ç¼åSQLï¼
   SELECT * FROM biao1 WHERE location LIKE '%åæ';
   SELECT * FROM biao2 WHERE state = 'åæâ;
Â
æ们çå°ï¼
对äºè¡¨ä¸ï¼æä»¬å¿ é¡»ä½¿ç¨LIKEå ³é®ååå¾æ¥è¯¢ç»æï¼
对äºè¡¨äºï¼æ们ç´æ¥éè¿ä¸ä¸ªå°åºå段ä½ä¸ºæ¥è¯¢æ¡ä»¶ï¼
Â
两ç§æ¹å¼å¯¹äºä¸åç使ç¨è¡¨ç人ï¼é½å¯ä»¥æ»¡è¶³åèªçéæ±ï¼ä½æ¯ï¼
  å¦æä½ ç表ä¸å å«å¤æä¿¡æ¯çè¯ï¼LIKEæ索精确æ°æ®çè½åå°±æ¾å¾ä¸è¶³äºï¼
  æ¥è¯¢è¶ç®åè¶å¥½ï¼
Â
æ¯ä¸æ¯å¯ä»¥ä½¿ç¨å¤ä¸ªååå¨å°åå¢ï¼ä¸ååå¨è¯¦ç»çå°åï¼ä¸ååå¨ç®åçå°åï¼
  è¿çä¼¼æ¯ä¸ªä¸éçæ¹æ³ï¼ä½æ¯æ两个é®é¢ï¼
  é¦å ï¼è¿åå ç¨æ´å¤ç空é´ï¼
Â Â å ¶æ¬¡ï¼ç±äºä¸¤åçæ°æ®æéå çå°æ¹ï¼è¿ä»£è¡¨æ¯æ¬¡ä¿®æ¹æ°æ®çæ¶åé½è¦å¨UPDATEè¯å¥ä¸å¤å ä¸ä¸ªåå¥ï¼
Â
使ç¨æ°æ®çæ¹å¼ å 表ç设计æ¹å¼ æ¯ç¸äºå½±åçï¼
  è¯å¥½ç表ç»æ设计å¯ä»¥è®©æ°æ®çå¤çæ¹å¼æ´ç®åï¼
Â
SQLæ¯ä¸ç§ç¨äºå ³ç³»æ°æ®åºçè¯è¨ï¼é£âå ³ç³»âæ¯ä»ä¹ï¼
Â
表é½æ¯åºäºå ³ç³»çï¼
  SQLå å ³ç³»æ°æ®åºç®¡çç³»ç»RDBMSèåºåï¼
  对äºè®¾è®¡è¡¨ç人èè¨ï¼å°±æ¯è¦è®¾è®¡ä¸ä¸ªææ级ç表ï¼å¿ é¡»èèåå½¼æ¤ä¹é´çå ³ç³»ï¼å¤ä¸ªåæ¯å¦ä½ä¸èµ·æè¿°æ项äºç©çï¼
Â
é¾ç¹å¨äºä½¿ç¨åæè¿°äºç©ï¼å¥½è®©åå¾æ°æ®æ´å æ¹ä¾¿ï¼
设计æ¹ååå³äºæ们对表çéæ±ï¼
Â
å建表æ¥éª¤ï¼
1.æåºäºç©ï¼æåºä½ å¸æ表æè¿°çææ ·äºç©ï¼
2.ååºä¸ä»½å ³äºé£æ ·äºç©çä¿¡æ¯å表ï¼è¿äºä¿¡æ¯é½æ¯ä½¿ç¨è¡¨æ¶çå¿ è¦ä¿¡æ¯ï¼  Â
  å¦ä½ä½¿ç¨è¿å¼ 表ï¼
3.使ç¨ä¿¡æ¯å表ï¼æå ³äºé£æ ·äºç©ç综åä¿¡æ¯æåæå°åä¿¡æ¯ï¼ä»¥ä¾¿ç¨äºç»ç»è¡¨ï¼
  轻æ¾æ¥è¯¢è¿å¼ 表ï¼
Â
è¿éççé®ï¼
  鱼类ç 究è çéæ±ï¼éè¿æ¥æ¾é±¼çå¦åæä¿åå¾ç¥é¨çééåå°ç¹ï¼
  æå¿æ°ç¨¿äººçéæ±ï¼é说ä¿åï¼å¾ç¥æè·è çå§åãééãæè·æ°´åãæè·å°åºãæè·æ¥æï¼
Â
对äºä¸åç使ç¨è æ¥è®²ï¼ä¸è¿°ç表设计é½æåèªçåçæ§ï¼
  æ们æâå ³äºé£æ ·äºç©ç综åä¿¡æ¯æåæå°åä¿¡æ¯âçç»è´ç¨åº¦ï¼ç¨ååæ§æ¥æè¿°ï¼
  个人认为ï¼å ³äºæ°æ®ååæ§ççå®ï¼åå³äºæ们å®é çéæ±ï¼
  Â
ååæ§æ°æ®ï¼
  ååï¼atomï¼æ¯ä¸åæ æ³æä¸åºè¯¥åå²çä¿¡æ¯ï¼
  对äºæ°æ®èè¨ï¼æ°æ®çååæ§ï¼å°±è¡¨ç¤ºå®å·²ç»è¢«åå²ä¸ºæå°åï¼å·²ç»ä¸è½æä¸åºè¯¥å被åå²ï¼
Â
æ¯å¦å¯¹ä¸ä¸ªéè´§åæ¥è¯´ï¼ä¸ååå¨è¡éåé¨çå·çå就已ç»å ·æäºååæ§ï¼å¦æå°æ°æ®æåæè¡éå称+é¨çå·ä¸¤åï¼æ¥è¯¢åèä¼æ´é¿ï¼
Â
ååæ§åä½ ç表ï¼
å¦ä½ç解表ä¸å 容ï¼
1.表å¨æè¿°ä»ä¹äºç©ï¼
2.以ä½ç§æ¹å¼ä½¿ç¨è¡¨è½æ´å¥½åå¾æè¿°çäºç©ï¼
3.åæ¯å¦å å«ååæ§æ°æ®ï¼è®©æ¥è¯¢æ´ç®çåç¡®ï¼
Â
让æ°æ®å ·æååæ§ï¼
   并ä¸æ¯å°æ°æ®å°½å¯è½çåå²çé常å°ï¼èæ¯è¦åå²æå建ææçç表æéçæå°ç段
  å«ææ°æ®åå²çè¶ åºå¿ è¦ï¼å¦æä¸éè¦é¢å¤å¢å åï¼å°±å«å 为å¯ä»¥å¢å èå¢å ï¼
  ååæ§æå©äºç¡®ä¿è¡¨å 容çåç¡®æ§ï¼ä¹è½ä½¿æ¥è¯¢æ´ææçï¼å 为æ¥è¯¢ä¼å ååæ§èæ´å®¹æ设计ï¼
Â
ååæ§æ°æ®çæ£å¼è§åï¼
è§åä¸ï¼
Â Â å ·æååæ§æ°æ®çåä¸ä¸ä¼æå¤ä¸ªç±»åç¸åçå¼ï¼
  æ¯å¦ï¼å欢çè¿å¨å段sportä¸ï¼å¼ä¸ºè¶³çã篮çâ¦â¦Â  Â
  ä¿ææ°æ®çç®æ´æ§ï¼å¦åå¦ææ¥è¯¢å ·ä½æä¸ªå ´è¶£å°æ¯ä¸åºå©æ¢¦ï¼
first_nameââlast_nameââintrests
hua              Q              football,backetball
Â
è§åäºï¼
Â Â å ·æååæ§æ°æ®ç表ä¸ä¸ä¼æå¤ä¸ªåå¨åç±»æ°æ®çåï¼
  åçååä¸åºè¯¥æ涵ä¹ä¸ç交åï¼
teacherââstudent1ââstudent2ââstudent3
Yuan        hua1        hua2           hua3
Â
è§èåçåå ï¼
  让表è§èåï¼normalizationï¼è¡¨ç¤ºè¡¨éµå¾ªæäºæ åï¼å³ä½¿æ¯åæ¥è§¦çæ°è®¾è®¡å¸ä¹è½ç解ï¼
Â
让æ°æ®å ·æååæ§æ¯å建ä¸ä¸ªè§èå表ç第ä¸æ¥ï¼
Â
è§èå表çä¼ç¹ï¼
1.è§èå表ä¸æ²¡æéå¤çæ°æ®ï¼å¯ä»¥åå°æ°æ®åºç大å°ï¼
2.å 为æ¥æ¾çæ°æ®è¾å°ï¼ä½ çæ¥è¯¢ä¼æ´ä¸ºå¿«æ·ï¼
Â
è¾¾æ1NFçåè·¯ä¸ï¼
Â Â å ·æååæ§çæ°æ®åªè®©æ们ç表ç°è§èäºä¸åï¼
  å®å ¨çè§èå表示æ们å¤äºç¬¬ä¸èå¼ï¼First Normal Formï¼çç¶æï¼ç®ç§°1NFï¼
Â
1NFï¼
1ï¼æ¯è¡æ°æ®è¡å¿ é¡»å å«å ·æååæ§çå¼ï¼
2ï¼æ¯ä¸ªæ°æ®è¡å¿ é¡»æç¬ä¸æ äºçè¯å«é¡¹ï¼ç§°ä¸ºâ主é®âï¼Primary Keyï¼ï¼
Â
æ以è¦è®©è¡¨å®æ´çè§èï¼éè¦ä¸ºæ¯æ¡è®°å½å ä¸ä¸»é®ï¼
Â
主é®è§åï¼
  æ为主é®çåå¿ é¡»å¨å建表çæ¶åä¸å¹¶è®¾ç½®ï¼
  Â
æ们å æ¥çç主é®æ¯ä»ä¹ï¼
  主é®æ¯è¡¨ä¸çæ个åï¼ä»å¯ä»¥è®©æ¯ä¸æ¡è®°å½æ为å¯ä¸çï¼
  主é®ç¨äºç¬ä¸æ äºå°è¯å«åºæ¯æ¡è®°å½ï¼
  å³ï¼ä¸»é®ä¸çæ°æ®ä¸è½éå¤ï¼
Â
æ¯å¦å¯¹äºä¸ªäººä¿¡æ¯ï¼å¯ä»¥ä½¿ç¨èº«ä»½è¯å·è¿è¡æ 示ï¼ä½æ¯ä½ å¯ä»¥ä¿è¯æ°æ®åºç»å¯¹å®å ¨åï¼å¦åå¦ææ°æ®è¢«å·èµ°ï¼ä½ ç客æ·èµæä¹å°±ä¸å¹¶æå äºï¼
Â
主é®çç¹å¾ï¼
1ï¼ä¸»é®ä¸å¯ä»¥ä¸ºNULLï¼å 为NULLå¼ä¸å¯ä¸
2ï¼æå ¥æ°çºªå½æ¶å¿ é¡»æå®ä¸»é®å¼ï¼å¦å主é®å°±æå¯è½æ¯NULLï¼
3ï¼ä¸»é®å¿ é¡»ç®æ´ï¼åªæ¯ä¸ä¸ªç¬ä¸æ äºçæ°æ®ï¼ä¸è¯¥æå ¶ä»å 容ï¼
4ï¼ä¸»é®å¼ä¸å¯ä»¥è¢«ä¿®æ¹ï¼æå¤çè¾å ¥å¼å¯è½ç ´å主é®çå¯ä¸æ§ï¼
Â
æ佳主é®å¯è½æ¯æ°ç主é®ï¼
  æä½³æ¹å¼æ¯å¦å¤å建ä¸ä¸ªå å«å¯ä¸æ§ä¸»é®çåï¼å¯ä»¥ç§°ä¹ä¸ºIDï¼
Â
å¦ä¸è¡¨ç»æï¼
idââlast_nameââfirst_name
1     hua              Q1
2     hua              Q2
Â
IDå让表ä¸è®°å½å ·æäºå¯ä¸æ§ï¼è¿å¼ 表ä¹å æ¤å¤äºç¬¬ä¸èå¼çç¶æï¼
Â
人é ï¼syntheticï¼ä¸»é®ï¼ä½¿ç¨èæçIDåä½ä¸ºä¸»é®ï¼
èªç¶ï¼naturalï¼ä¸»é®ï¼ä½¿ç¨äººç身份è¯ä¿¡æ¯ä½ä¸ºä¸»é®ï¼
Â
æ们åç»ä¼ç»§ç»è®¨è®ºï¼
  第äºèå¼ã第ä¸èå¼ï¼æ¯ä¸çº§èå¼é½ä¼å¢å æ´ä¸¥æ ¼ãæ´ç²¾ç¡®çè§åï¼
  å¨è¡¨è®¾è®¡çè¿ç¨ä¸ï¼æäºå段å¯è½æ²¡æååæ§ï¼æ¯å 为æ们并ä¸éè¦é£æ ·åï¼æ¯å¦ä½¿ç¨å ³é®è¯è¿è¡è¯è®ºï¼è¯è®ºå段ä¸çæ°æ®æ ååæ§ï¼ä½æ¯å®ä¹å¹¶ä¸éè¦åååï¼
Â
æè§èååè¿ï¼
  让æ°æ®å ·æååæ§ï¼
  å ä¸ä¸»é®ï¼ä¸»é®çå建é常ä¼å¨ç¼åCREATE TABLEæ¶è¿è¡ï¼
Â
æ¢ç¶è¿æ ·ï¼æ们æ¯ä¸æ¯å¯ä»¥è¿æ ·ä¿®æ¹æ们ä¹åä¸å®ç¾ç表ï¼
1ï¼æ¥åºæææ°æ®ï¼
2ï¼å建ä¸å¼ è§èåç表ï¼
3ï¼ææ§æ°æ®INSERT INTOå°æ°è¡¨ï¼å¹¶æ¹åæ¯ä¸è¡ä»¥ç¬¦åæ°è¡¨çç»æï¼
4ï¼DROP TABLEæ就表ï¼
Â
ä½æ¯ï¼å·²ç»è£ 满æ°æ®ç表ï¼æ们并ä¸å¸æDROP TABLEä¹åï¼åéæ°è¾å ¥æææ°æ®â¦â¦
Â
æ以ï¼æ们éè¦å¯¹ä¸å¼ ä¸å ·ååæ§ï¼æ²¡æ主é®ç表è¿è¡æ´æ°ï¼æ们ä»ç»ä¸ä¸ªæ°çå½ä»¤ï¼
ä½å¨ä¿®çå·²æ表çæ¹å¼åï¼æ们æ¥ççå¦ä½éæ°è®¾è®¡ä¸å¼ æ´å æ åç表ï¼
Â
æ们设计çCREATE TABLEï¼
   æ们å顾ä¸ç¬¬ä¸ç« å建ç表my_contacts:
professionãlocationåseekingè¿äºåè½ä¸è½è®¾è®¡çæ´å å ·æååæ§å¢ï¼
åæ¶æ们ä¹åç°ï¼è¿å¼ 表没æ主é®ï¼
Â
æ们æ¥çä¸è¡¨ç»æï¼
mysql> DESC my_contacts;
+------------+--------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| last_name | varchar(30) | YES |    | NULL   |      |
| first_name | varchar(20)Â | YESÂ | Â Â Â | NULLÂ Â Â | Â Â Â Â Â |
| emal      | varchar(50) | YES |    | NULL   |      |
| gender    | char(1)     | YES |    | NULL   |      |
| birthdy   | date        | YES |    | NULL   |      |
| profession | varchar(50)Â | YESÂ | Â Â Â | NULLÂ Â Â | Â Â Â Â Â |
| location  | varchar(20) | YES |    | NULL   |      |
| status    | varchar(100) | YES |    | NULL   |      |
| seeking   | varchar(100) | YES |    | NULL   |      |
+------------+--------------+------+-----+---------+-------+
9 rows in set (0.03 sec)
设置表æ¶æç¨çSQLï¼
  æ们æ³ççä¸æ¯è¡¨ä¸çå段ï¼èæ¯æ³çCREATEç代ç ï¼è¿æ ·æè½ç¥é表ä¸å¼å§æ¯å¦ä½è®¾è®¡çï¼
  使ç¨å½ä»¤SHOW CREATE TABLE table; å°è¿åå¯ä»¥é建表ä½æ²¡ææ°æ®çCREATE TABLEè¯å¥ï¼
  è¿æ ·ä¸æ¥ï¼å°±å¯ä»¥éæ¶æ¥ç表çå建æ¹å¼ï¼
mysql> SHOW CREATE TABLE my_contacts;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_contacts | CREATE TABLE `my_contacts` (
 `last_name` varchar(30) DEFAULT NULL,
 `first_name` varchar(20) DEFAULT NULL,
 `emal` varchar(50) DEFAULT NULL,
 `gender` char(1) DEFAULT NULL,
 `birthdy` date DEFAULT NULL,
 `profession` varchar(50) DEFAULT NULL,
 `location` varchar(20) DEFAULT NULL,
 `status` varchar(100) DEFAULT NULL,
 `seeking` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
å¯ä»¥çå°ï¼
  ååå表æååçåæå·ä¼å¨è¿è¡è¯¥å½ä»¤çæ¶ååºç°ï¼(æå ³åæå·çä½ç¨æ们ä¸ä¼ä¼æ说æ)
  é¤éæ们æå®ï¼å¦åæ°æ®åé½ä¼å设æææ°æ®çé»è®¤å¼é½æ¯NULLï¼
  ç»ææ¬å·ä¹åçæåï¼è¯´æäºæ°æ®å¦ä½åå¨ï¼ä»¥å使ç¨çå符éï¼ç°å¨æ¯é»è®¤å³å¯ï¼ï¼
Â
æ们è¦åºäºä¹åçå建表çå½ä»¤ï¼å建ä¸å¼ è§èåçæ°è¡¨ï¼
  é¤éå·²ç»å é¤äºæ§è¡¨ï¼å¦åæ们éè¦ä¸ä¸ªæ°ç表åï¼
  åªé å¤å¶ç²è´´æ¯ä¸å¤çï¼æ们è¿éè¦åæ´å¤çäºï¼
Â
å ä¸ä¸»é®çCREATE TABLEï¼
  å é¤åæå·åæåä¸è¡ï¼
  添å contact_idå并设置为NOT NULLï¼
  å¨åå表çæä¸æ¹æ·»å ä¸è¡PRIMARY KEYï¼ææ°æ·»å çcontact_id设å®ä¸ºä¸»é®ï¼ï¼ä¸ä¸ºNULLï¼ç¬ä¸æ äºï¼
  设置主é®çè¯æ³ï¼PRIMARY KEYï¼contact_idï¼ï¼å³æ主é®åçå称æ¾å¨æ¬å·ä¸ï¼
Â
æ们æ°å»ºä¸å¼ 表ï¼flower_contacts
mysql> CREATE TABLE flower_contacts (
   ->    contact_id INT NOT NULL,
   ->    last_name varchar(30) DEFAULT NULL,
   ->    first_name varchar(20) DEFAULT NULL,
   ->    emal varchar(50) DEFAULT NULL,
   ->    gender char(1) DEFAULT NULL,
   ->    birthdy date DEFAULT NULL,
   ->    profession varchar(50) DEFAULT NULL,
   ->    location varchar(20) DEFAULT NULL,
   ->    status varchar(100) DEFAULT NULL,
   ->    seeking varchar(100) DEFAULT NULL,
   ->    PRIMARY KEY (contact_id)
   -> );
Query OK, 0 rows affected (0.11 sec)
ç¸åºçï¼
mysql> DESC flower_contacts;
+------------+--------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| contact_id | int(11)Â Â Â Â Â | NO Â | PRI | NULLÂ Â Â | Â Â Â Â Â |
| last_name | varchar(30) | YES |    | NULL   |      |
| first_name | varchar(20)Â | YESÂ | Â Â Â | NULLÂ Â Â | Â Â Â Â Â |
| emal      | varchar(50) | YES |    | NULL   |      |
| gender    | char(1)     | YES |    | NULL   |      |
| birthdy   | date        | YES |    | NULL   |      |
| profession | varchar(50)Â | YESÂ | Â Â Â | NULLÂ Â Â | Â Â Â Â Â |
| location  | varchar(20) | YES |    | NULL   |      |
| status    | varchar(100) | YES |    | NULL   |      |
| seeking   | varchar(100) | YES |    | NULL   |      |
+------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
è¿é没æè ¢é®é¢ï¼
1.æ们已ç»ç¥éæ¯æ¬¡æå ¥æ°çºªå½é½å¿ 须为主é®åèµæ°å¼ï¼æ没ææ´å¥½çåæ³ï¼
  æ两ç§æ¹å¼ï¼ä¸ç§æ¯ç´æ¥å©ç¨åæ¥å°±æå¯ä¸æ§çæ°æ®ä½ä¸ºä¸»é®ï¼è¿ç§å¯è½ä¸å¤ªå®¹æå®ç°ï¼
  æ¯è¾ç®åçä¸ç§æ¯ä½¿ç¨å ¨æ°ç主é®åï¼ç¨å®æ¥åå¨ç¬ä¸æ äºçå¼ï¼
  æ¯å½ä½¿ç¨ä¸»é®æ¶ï¼æ们å¯ä»¥è¦æ±SQL软件èªå¨ä¸ºä¸»é®å¡«å ¥æ°çå¼ï¼
2.SHOWå½ä»¤ç使ç¨åºæ¯ï¼
  æ¾ç¤ºè¡¨ä¸ååå ¶æ°æ®ç±»åï¼SHOW COLUMNS FROM table;
  æä¾é建æ°æ®åºçè¯å¥ï¼SHOW CREATE DATABASE database;     Â
  æ¾ç¤ºä»»ä½ç¼äºç´¢å¼çå以åç´¢å¼ç±»åï¼SHOW INDEX FROM table;
  ç®å为æ¢ï¼æ们å¯ä¸çå°çç´¢å¼å°±æ¯ä¸»é®ï¼
  è¿æä¸ä¸ªå¾æç¨çå½ä»¤ï¼SHOW WARNINGS;
     å¦æä½ ä»æ§å¶å°æ¶å°SQLå½ä»¤é æçé误信æ¯ï¼é®å ¥è¿ä¸ªå½ä»¤å°±å¯åå¾ç¡®åçè¦åå 容ï¼
3.åæå·çä½ç¨ï¼
  åæå·çåå¨æ¯å 为RDBMSææ¶æ æ³å辨ååï¼
  å¦æå¨ååååå ä¸è¯¥å符就è½ä»¥SQLä¿çåä½ä¸ºååï¼`select`å°±æ¯ä¸ä¸ªææçååï¼
4.ç¨ä¿çåä½ä¸ºååçé®é¢ï¼
  è¿æ¯ä¸ä¸ªå¾ç³ç³ç主æï¼è¿åè®©ä½ çæ¥è¯¢å¾æ··ä¹±ï¼ä¹ä¼å¼å ¥æ¯æ¬¡é½è¾å ¥åæå·ç麻ç¦ï¼
  èä¸selectä¹ä¸æ¯ä¸ä¸ªå¥½ååï¼æ æ³è¯´æ该åå å«çæ°æ®ï¼
Â
1ã2ã3â¦â¦èªå¨éå¢âå ³é®åAUTO_INCREMENT
  为contact_idåå ä¸å ³é®åAUTO_INCREMENTï¼å°±å¯ä»¥è®©SQL软件èªå¨ä¸ºè¯¥åå¡«å ¥æ°æ®ï¼ç¬¬ä¸è¡å¡«å ¥1ï¼åç»éå¢ï¼
  æäºRDBMS类似åè½çå ³é®åå¯è½æ¯INDEXï¼
Â
æ们çä¸æ档对该AUTO_INCREMENTç说æï¼
mysql> help auto_increment
Name: 'AUTO_INCREMENT'
Description:
The AUTO_INCREMENT attribute can be used to generate a unique identity
for new rows:
URL:Â http://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
Examples:
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;
æ们è¿è¡äºè¿ä¸ªç¤ºä¾ï¼
mysql> SELECT * FROM animals;
+----+---------+
| id | name   |
+----+---------+
| 1 | dog    |
| 2 | cat    |
|Â 3 | penguin |
| 4 | lax    |
| 5 | whale  |
|Â 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
ç°å¨ï¼æ们修æ¹å建flower_contacts表çSQLï¼
DROP TABLE flower_contacts;
CREATE TABLE flower_contacts (
    contact_id INT NOT NULL AUTO_INCREMENT,
    last_name varchar(30) DEFAULT NULL,
    first_name varchar(20) DEFAULT NULL,
    emal varchar(50) DEFAULT NULL,
    gender char(1) DEFAULT NULL,
    birthdy date DEFAULT NULL,
    profession varchar(50) DEFAULT NULL,
    location varchar(20) DEFAULT NULL,
    status varchar(100) DEFAULT NULL,
    seeking varchar(100) DEFAULT NULL,
    PRIMARY KEY (contact_id)
);
ç°å¨ç表æ¯è¿æ ·çï¼
mysql> DESC flower_contacts;
+------------+--------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra         |
+------------+--------------+------+-----+---------+----------------+
| contact_id | int(11)Â Â Â Â Â | NO Â | PRI | NULLÂ Â Â | auto_increment |
| last_name | varchar(30) | YES |    | NULL   |               |
| first_name | varchar(20)Â | YESÂ | Â Â Â | NULLÂ Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
| emal      | varchar(50) | YES |    | NULL   |               |
| gender    | char(1)     | YES |    | NULL   |               |
| birthdy   | date        | YES |    | NULL   |               |
| profession | varchar(50)Â | YESÂ | Â Â Â | NULLÂ Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
| location  | varchar(20) | YES |    | NULL   |               |
| status    | varchar(100) | YES |    | NULL   |               |
| seeking   | varchar(100) | YES |    | NULL   |               |
+------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
æ们å°è¯æä½è¿å¼ 表ï¼æ¥çä¸AUTO_INCREMENTæ¯å¦ä½è¿ä½çï¼
æ们å æ¥çå æ¡SQLï¼
INSERT INTO flower_contacts
(last_name,seeking)
VALUES
('hua1','working,girlfriend'),
('hua2','working,girlfriend'),
('hua3','working,girlfriendâ);
INSERT INTO flower_contacts
(contact_id,last_name,seeking)
VALUES
(5,'hua4','working,girlfriend'),
(7,'hua5','working,girlfriend');
INSERT INTO flower_contacts
(last_name,seeking)
VALUES
('hua6','working,girlfriend'),
('hua7','working,girlfriend'),
('hua8','working,girlfriendâ);
INSERT INTO flower_contacts
(contact_id,last_name,seeking)
VALUES
(10,'hua9','working,girlfriend');
INSERT INTO flower_contacts
(contact_id,last_name,seeking)
VALUES
(NULL,'hua10','working,girlfriend');
DELETE FROM flower_contacts WHERE contact_id = 11;
INSERT INTO flower_contacts
(contact_id,last_name,seeking)
VALUES
(NULL,'hua10','working,girlfriend');
SELECT contact_id,last_name,seeking FROM flower_contacts;
æç»æ¥ç表flower_contactsçå 容å¦ä¸ï¼
mysql> SELECT contact_id,last_name,seeking FROM flower_contacts;
+------------+-----------+--------------------+
| contact_id | last_name | seeking           |
+------------+-----------+--------------------+
|Â Â Â Â Â Â Â Â Â 1 | hua1Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 2 | hua2Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 3 | hua3Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 5 | hua4Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 7 | hua5Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 8 | hua6Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 9 | hua7Â Â Â Â Â | working,girlfriend |
| Â Â Â Â Â Â Â 10 | hua8Â Â Â Â Â | working,girlfriend |
| Â Â Â Â Â Â Â 12 | hua10 Â Â Â | working,girlfriend |
+------------+-----------+--------------------+
9 rows in set (0.00 sec)
åæï¼
hua1ã2ã3 æ们æ¯æç §ææ¡£ä¾åçæ¹å¼æå ¥çï¼è¿è¡æ£å¸¸ï¼
hua4ã5两æ¡åæ¯æ们æå®çcontact_idè¿è¡çæå ¥ï¼è¿è¡æ£å¸¸ï¼
hua6ã7ã8è½ç¶ä¹æ£å¸¸æå ¥äºï¼ä½æ¯æ们çå°ï¼èªå¢çæ°å¼æ¯åºäºå½åæåä¸æ¡ä¸»é®çå¼è¿è¡çèªå¢ï¼ä¹å°±æ¯7ä¹åç8ï¼
hua9çæå ¥æ们åä¸æ¬¡æå®äºcontact_id为10ï¼ä½è¿ä¸æ¬¡å¤±è´¥äºï¼
mysql> INSERT INTO flower_contactsÂ
   -> (contact_id,last_name,seeking)
   -> VALUES
   -> (10,'hua6','working,girlfriend');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                               |
+-------+------+----------------------------------------+
| Error | 1062 | Duplicate entry '10' for key 'PRIMARY' |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
å 为æ们æå ¥ç主é®10éå¤äºï¼
Â
hua10çæå ¥å¾æææï¼ä¹æ以æåï¼æ¯å 为AUTO_INCREMENTä¼å¿½ç¥NULLï¼ï¼æ²¡æå®çè¯å°±æ¥éäºï¼
   å¾ææ¾hua10çcontact_id=11ï¼
ç´§æ¥çï¼æ们å é¤äºcontact_id = 11çè¿æ¡è®°å½ï¼æ们继ç»å°hua10è¿æ¡è®°å½æå ¥ï¼æ¤æ¶hua10çcontact_idåæ¯åºäº11çå¢å ï¼å¼ä¸º12ï¼è½ç¶11è¿æ¡è®°å½å·²ç»å é¤äºï¼
Â
å¯ä»¥å¾åºçç»è®ºæ¯ï¼
   èªå¢çæ°å¼æ¯åºäºå½åæåä¸æ¡ä¸»é®çå¼è¿è¡çèªå¢ï¼å³ä¾¿ä»å·²ç»è¢«å é¤äºï¼
  åªè¦æå ¥è¿çè®°å½å°±ä¼å¼èµ·ä¸»é®çAUTO_INCREMENT;
Â
å½ç¶ï¼contact_id=11ï¼æ们è¿å¯ä»¥ç»§ç»ä½¿ç¨ï¼
INSERT INTO flower_contacts
(contact_id,last_name,seeking)
VALUES
(11,'hua10','working,girlfriend');
æç»ï¼è¡¨çå 容å¦ä¸ï¼
mysql> SELECT contact_id,last_name,seeking FROM flower_contacts;
+------------+-----------+--------------------+
| contact_id | last_name | seeking           |
+------------+-----------+--------------------+
|Â Â Â Â Â Â Â Â Â 1 | hua1Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 2 | hua2Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 3 | hua3Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 5 | hua4Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 7 | hua5Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 8 | hua6Â Â Â Â Â | working,girlfriend |
|Â Â Â Â Â Â Â Â Â 9 | hua7Â Â Â Â Â | working,girlfriend |
| Â Â Â Â Â Â Â 10 | hua8Â Â Â Â Â | working,girlfriend |
| Â Â Â Â Â Â Â 11 | hua10 Â Â Â | working,girlfriend |
| Â Â Â Â Â Â Â 12 | hua10 Â Â Â | working,girlfriend |
+------------+-----------+--------------------+
10 rows in set (0.00 sec)
å½ç¶ï¼è¿æ¯æ们éæ°ï¼å¼å§å»ºè¡¨å®ç°äºè§èåç设计ï¼ç°å¨æ们è¦é对my_cotacts表ï¼ä»ç»æ们çæ°å½ä»¤äºï¼
Â
ALTERè¯å¥ï¼
  æ¹ç¨ALERTè¯å¥ï¼æ们ä¸ç¨åéæ°å¼å§äºï¼(注æï¼ï¼ï¼ï¼ï¼ä¸æ¯alertï¼èæ¯alter)
  带ææ°æ®ç表ä¸åºè¯¥ç»å被丢å¼ãå¸é¤ãé建çæ¥éª¤ï¼æ们éè¦çæ¯æ¹åç°æç表ï¼
  ALTERè¿ä¼å¨ä¸ä¸ç« è¿ä¸æ¥ä»ç»ï¼æ们å ççä»çç¨æ³ï¼
 Â
é¦å 请åçä¸ä¸åè¾¹æå°çmy_contacts表åå ¶å 容ï¼
Â
为ç°æç表添å 主é®ï¼
  ALTER TABLE 并添å PRIMARY KEYï¼
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (contact_id);
Â
FIRSTï¼
   æ¯è¦æ±è½¯ä»¶å§æ°åæ¾å¨æåé¢ï¼è¿æ¯ä¸ä¸ªå¯éçå ³é®åï¼
  æ主é®æ¾å¨æåé¢æ¯ä¸ä¸ªå¾å¥½çä¹ æ¯ï¼
Â
ADD COLUMNï¼
  添å ä¸ä¸ªæ°åï¼
Â
æ们çå°ï¼æ4æ¡è®°å½æ·»å äºæ°åï¼åæ¶æ们ä¹çå°ï¼ä¸»é®çå¼å·²ç»å ¨é¨é½å¡«ä¸äºï¼
Â
æ»ç»ï¼
ATOMIC DATAï¼æ°æ®ååæ§ï¼åä¸æ°æ®å·²æææ¥è¯¢æéçæå°åä½ï¼
ATOMIC DATAè§åä¸ï¼
Â Â å ·æååæ§è¡¨ç¤ºå¨åä¸åä¸ä¸ä¼åå¨å¤ä¸ªç±»åç¸åçæ°æ®ï¼
ATOMIC DATAè§åäºï¼
Â Â å ·æååæ§è¡¨ç¤ºä¸ä¼ç¨å¤ä¸ªåæ¥åå¨ç±»åç¸åçæ°æ®ï¼
SHOW CREATE TABLEï¼åç°å建ç°æ表çæ£ç¡®è¯æ³ï¼
PRIMARY KEYï¼ä¸»é®ï¼ä¸ä¸ªæä¸ç»è½è¯å«åºå¯ä¸æ°æ®è¡çåï¼
FIRST NORMAL FORMï¼1NFï¼ï¼
  第ä¸èå¼ï¼æ¯ä¸ªæ°æ®è¡åéå å«ååæ§æ°æ®å¼ï¼èä¸æ¯ä¸ªæ°æ®è¡åéæå¯ä¸çè¯å«æ¹æ³ï¼
AUTO_INCREMENTï¼
  å声ææ¶è¥ä½¿ç¨è¯¥å ³é®åï¼åæ¯æ¬¡æ§è¡INSERTå½ä»¤æå ¥æ°æ®æ¶ï¼é½ä¼èªå¨ç»åèµäºå¯ä¸çéå¢æ´æ°å¼ï¼
Â