ä¸ã约æ Constraints
å¨SQLiteæ°æ®åºä¸åå¨æ°æ®çæ¶åï¼æä¸äºæ°æ®æææ¾ç约ææ¡ä»¶ã æ¯å¦ä¸æå¦æ ¡å
³äºæå¸çæ°æ®è¡¨ï¼å
¶ä¸çå段åå¯è½æå¦ä¸çº¦æï¼
å¹´é¾ - è³å°å¤§äº20å²ãå¦æä½ æ³å½å
¥ä¸ä¸ªå°äº20å²çæå¸ï¼ç³»ç»ä¼æ¥é.
å½ç± - é»è®¤ä¸å½ãæè°é»è®¤ï¼å°±æ¯å¦æä½ ä¸å¡«åï¼ç³»ç»èªå¨å¡«ä¸é»è®¤å¼.
å§å - ä¸è½ä¸ºç©ºãæ¯ä¸ªäººé½æååå.
åå·¥å· - å¯ä¸ãè¿ä¸ªå¯ä¸è½ä¹±ï¼å·¥èµåéäºå°±éº»ç¦äº.
ä¸é¢æå°ç大äºãé»è®¤ãä¸è½ä¸ºç©ºãå¯ä¸ççï¼å°±æ¯æ°æ®ç约ææ¡ä»¶ã æ们å¨ç¨CREATE TABLE å建表çæ¶åï¼å°±åºè¯¥å°æ¯ä¸ªå段åç约ææ¡ä»¶äºå
说æï¼å¦ææçè¯ï¼ï¼ 以ååå¾è¡¨éè¾å
¥æ°æ®çæ¶åï¼ç³»ç»ä¼èªå¨ä¸ºæ们æ£æ¥æ¯å¦æ»¡è¶³çº¦ææ¡ä»¶ï¼å¦æä¸æ»¡è¶³ç³»ç»ä¼æ¥éã
SQLite æ°æ®åºå¸¸ç¨çº¦æå¦ä¸ï¼
NOT NULL - é空
UNIQUE - å¯ä¸
PRIMARY KEY - 主é®
FOREIGN KEY - å¤é®
CHECK - æ¡ä»¶æ£æ¥
DEFAULT - é»è®¤
äºãä¸»é® PRIMARY KEY
æ们è¿æ¯è¿å
¥SQLite å½ä»¤è¡ç¯å¢ï¼å»ºç«ä¸ä¸ª test.db æ°æ®åºç¨æ¥åå®éªï¼å¦ä¸
myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db -- Loading resources from /home/myqiao/.sqliterc SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables sqlite>
è¿è¡ .tables å½ä»¤æ²¡æè¿åï¼è¯´ææ°æ®åºæ¯ç©ºçãå¦æä½ çæ°æ®åºéé¢æå
容并影åå°ä¸é¢çå®éªï¼ ä½ å¯ä»¥ç¨æ们ä¸ä¸ç¯å¦ç DROP TABLE æ¥å é¤é æå½±åçè¡¨ï¼ æè
ç¨ALTER TABLE ... RENAME TO ... æ¥æ¹åã
ä¸é¢è¨å½æ£è½¬ï¼æ们æ¥è¯´è¯´ä¸»é® PRIMARY KEY ã
é¦å
ï¼æ°æ®è¡¨ä¸æ¯ä¸æ¡è®°å½é½æä¸ä¸ªä¸»é®ï¼ è¿å°±åæ们æ¯ç身份è¯å·ç ãåå·¥å·ãé¶è¡å¸å·ï¼ åè¿æ¥ä¹å¯ä»¥è¯´ï¼æ¯ä¸ä¸ªä¸»é®å¯¹åºçä¸æ¡æ°æ®è®°å½ã æ以ï¼ä¸»é®å¿
é¡»æ¯å¯ä¸çã
å
¶æ¬¡ï¼ä¸è¬æ
åµä¸ä¸»é®åæ¶ä¹æ¯ä¸ä¸ªç´¢å¼ï¼æ以éè¿ä¸»é®æ¥æ¾è®°å½é度æ¯è¾å¿«ã
第ä¸ï¼å¨å
³ç³»åæ°æ®åºä¸ï¼ä¸ä¸ªè¡¨ç主é®å¯ä»¥ä½ä¸ºå¦å¤ä¸ä¸ªè¡¨çå¤é®ï¼ è¿æ ·ï¼è¿ä¸¤ä¸ªè¡¨ä¹é´å°±éè¿è¿ä¸ªé®å»ºç«äºå
³ç³»ã
æåï¼ä¸»é®ä¸è¬æ¯æ´æ°æè
å符串ï¼åªè¦ä¿è¯å¯ä¸å°±è¡ã å¨ SQLite ä¸ï¼ä¸»é®å¦ææ¯æ´æ°ç±»åï¼è¯¥åçå¼å¯ä»¥èªå¨å¢é¿ã
ä¸é¢æ们æ¥åå®éªï¼
sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name) Values('å¼ ä¸'); sqlite> INSERT INTO Teachers(Name) Values('æå'); sqlite> INSERT INTO Teachers(Name) Values('çäºéº»å'); sqlite> SELECT * FROM Teachers; Id Name ---------- ---------- 1 å¼ ä¸ 2 æå 3 çäºéº» sqlite> INSERT INTO Teachers(Id,Name) Values(2,'åæ空'); Error: PRIMARY KEY must be unique sqlite>
æ们å
æ°å»ºäºä¸ä¸ª Teachers 表ï¼å¹¶è®¾ç½®äºä¸¤ä¸ªå段åï¼å
¶ä¸ Id å段å为主é®åã ç¶åï¼æ们åå
¶ä¸æå
¥ä¸æ¡æ°æ®å¹¶æ¥è¯¢ï¼åé¦ä¸åæ£å¸¸ã
注æï¼å¨æå
¥åä¸æ¡æ°æ®çæ¶åï¼å½ä»¤ä¸å¹¶æ²¡ææç¡®ææ Id çå¼ï¼ç³»ç»èªå¨èµå¼ï¼å¹¶ä¸æ°å¼èªå¨å¢é¿ã
æå
¥ç¬¬åæ¡æ°æ®çæ¶åï¼æç»äºä¸ä¸ªæç¡®ç Id ç¼å·ä¸º 2ï¼å 为æåçç¼å·å·²ç»æ¯ 2 äºï¼ æ以系ç»æ示æé误ï¼ä¸»é®å¿
é¡»å¯ä¸ã
ä¸ãé»è®¤å¼ DEFAULT
æä¸äºç¹å«çå段åï¼å¨æ¯ä¸æ¡è®°å½ä¸ï¼ä»çå¼åºæ¬ä¸é½æ¯ä¸æ ·çãåªæ¯å¨ä¸ªå«æ
åµä¸ææ¹ä¸ºå«çå¼ï¼è¿æ ·çå段åæ们å¯ä»¥ç»ä»è®¾ä¸ä¸ªé»è®¤å¼ã
ä¸é¢æ们æ¥åå®éªï¼
sqlite> sqlite> DROP TABLE Teachers; sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Country text DEFAULT 'ä¸å½'); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name) Values('å¼ ä¸'); sqlite> INSERT INTO Teachers(Name) Values('æå'); sqlite> INSERT INTO Teachers(Name) Values('çäºéº»å'); sqlite> INSERT INTO Teachers(Name,Country) Values('åæ空','天åº'); sqlite> SELECT * FROM Teachers; Id Name Country ---- --------------- --------------- 1 å¼ ä¸ ä¸å½ 2 æå ä¸å½ 3 çäºéº»å ä¸å½ 4 åæ空 å¤©åº sqlite>
å
æä¹åç Teachers 表å é¤ï¼ç¶åéæ°å建ãè¿å Teachers 表å¤äºä¸ä¸ª Country åæ®µï¼ å¹¶ä¸è®¾ç½®é»è®¤å¼ä¸ºâä¸å½âï¼ç¶åæ们æå
¥åæ¡æ°æ®å° Teachers 表ã
åä¸æ¡æ°æ®é½æ²¡ææç¡®ææ Country å段çå¼ï¼åªæ第åæ¡æ°æ®ææäºâåæ空âç Country 为â天åºâã
æ¥è¯¢æ°æ®ï¼åç°åä¸æ¡æ°æ®é½å¡«ä¸äºé»è®¤å¼ï¼å®éªæåã
æ°æ®æ¾ç¤ºæç¹èµ°æ ·ï¼å½ä»¤ .width 4 15 15 设置çå宽ï¼å¯ä»¥éè¿ .show æ¥çï¼ å¯è½æ¯å 为ä¸æçåå ï¼æ以没æ对é½ã
åãé空 NOT NULL
æä¸äºå段æ们å¯è½ä¸æ¶ä¸ç¥å°è¯¥å¡«äºä»ä¹ï¼åæ¶å®ä¹æ²¡è®¾å®é»è®¤å¼ï¼ å½æ·»å æ°æ®æ¶ï¼æ们æè¿æ ·çå段空çä¸å¡«ï¼ç³»ç»è®¤ä¸ºä»æ¯ NULL å¼ãä½æ¯è¿æå¦å¤ä¸ç±»å段ï¼å¿
须被填ä¸æ°æ®ï¼å¦æä¸å¡«ï¼ç³»ç»å°±ä¼æ¥éã è¿æ ·çå段被称为 NOT NULL é空å段,éè¦å¨å®ä¹è¡¨çæ¶åäºå
声æ
温馨提示:答案为网友推荐,仅供参考