Mysql数据库（三）Mysql表结构管理

銆€銆€涓€銆丮ySQL鏁版嵁绫诲瀷

銆€銆€1.鏁板瓧绫诲瀷

銆€銆€锛?锛夋暣鏁版暟鎹被鍨嬪寘鎷琓INYINT/BIT/BOOL/SMALLINT/MEDIUMINT/INT/BIGINT

銆€銆€锛?锛夋诞鐐规暟鎹被鍨嬪寘鎷現LOAT/DOUBLE/DECIMAL

銆€銆€鍘熷垯锛?1.閫夋嫨鏈€灏忕殑鍙敤绫诲瀷锛屽鏋滃€兼案杩滀笉瓒呰繃127锛屽垯浣跨敤TINYINT姣擨NT寮恒€?

銆€銆€銆€銆€銆€ 2.瀵逛簬瀹屽叏閮芥槸鏁板瓧鐨勶紝鍙互閫夋嫨鏁存暟绫诲瀷銆?

銆€銆€銆€銆€聽聽聽 3.娴偣绫诲瀷鐢ㄤ簬鍙兘鍏锋湁灏忔暟閮ㄥ垎鐨勬暟銆傚璐х墿鍗曚环銆佺綉涓婅喘鐗╀氦浠橀噾棰濈瓑銆?

銆€銆€2.瀛楃涓茬被鍨?

銆€銆€锛?锛夋櫘閫氱殑鏂囨湰瀛楃涓茬被鍨嬪寘鎷珻HAR锛堝彇鍊间负1~255锛夊拰VARCHAR锛堝彉闀跨殑瀛楃涓诧級

銆€銆€锛?锛塗EXT鍜孊LOB绫诲瀷銆傚畠浠殑澶у皬鍙互鏀瑰彉锛孴EXT绫诲瀷閫傚悎瀛樺偍闀挎枃鏈紝鑰孊LOB绫诲瀷閫傚悎瀛樺偍浜岃繘鍒舵暟鎹紝鏀寔浠讳綍鏁版嵁锛屼緥濡傛枃鏈€佸０闊冲拰鍥惧儚绛夈€?

銆€銆€锛?锛夌壒娈婄被鍨婼ET鍜孍NUM銆?

銆€銆€鍘熷垯锛毬犅?1.浠庨€熷害鏂归潰鑰冭檻锛岃閫夋嫨鍥哄畾鐨勫垪锛屽彲浠ヤ娇鐢–HAR绫诲瀷銆?

銆€銆€銆€銆€銆€銆€2.瑕佽妭鐪佺┖闂达紝浣跨敤鍔ㄦ€佸湴鍒楋紝鍙互浣跨敤VARCHAR绫诲瀷銆?

銆€銆€銆€銆€銆€銆€3.瑕佸皢鍒椾腑鐨勫唴瀹逛粰涔嬪瓙涓€绉嶉€夋嫨锛屽彲浠ヤ娇鐢‥NUM绫诲瀷銆?

銆€銆€銆€銆€銆€銆€4.鍏佽鍦ㄤ竴涓垪涓湁澶氫簬涓€涓殑鏉＄洰锛屽彲浠ヤ娇鐢⊿ET绫诲瀷銆?

銆€銆€銆€銆€銆€銆€5.濡傛灉瑕佹悳绱㈢殑鍐呭涓嶅尯鍒嗗ぇ灏忓啓锛屽彲浠ヤ娇鐢═EXT绫诲瀷銆?

銆€銆€銆€銆€銆€銆€6.濡傛灉瑕佹悳绱㈢殑鍐呭鍖哄垎澶у皬鍐欙紝鍙互浣跨敤BLOB绫诲瀷銆?

銆€銆€3.鏃ユ湡鍜屾椂闂存暟鎹被鍨?

銆€銆€锛?锛塂ATE锛歒YYY-MM-DD

銆€銆€锛?锛塗IME锛欻H:MM:SS

銆€銆€锛?锛塂ATETIME锛歒YYY-MM-DD HH:MM:SS

銆€銆€锛?锛塗IMESTAMP锛氭椂闂存爣绛撅紝鍦ㄥ鐞嗘姤鍛婃椂浣跨敤鐨勬樉绀烘牸寮忓彇鍐充簬M鐨勫€笺€?

銆€銆€锛?锛塝EAR锛氬勾浠藉彲鎸囧畾涓や綅鏁板瓧鍜屽洓浣嶆暟瀛楃殑鏍煎紡銆?

銆€銆€浜屻€佸垱寤鸿〃

mysql> use db_library;
Database changed
mysql> CREATE TABLE tb_bookinfo(
    -> barcode varchar(30),
    -> bookname varchar(70),
    -> typeid int(10) unsigned,
    -> author varchar(30),
    -> ISBN varchar(20),
    -> price float(8,2),
    -> page int(10) unsigned,
    -> bookcase int(10) unsigned,
    -> inTime date,
    -> del tinyint(1) DEFAULT'0',
    -> id int(11) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

聽銆€銆€1.璁剧疆榛樿鐨勫瓨鍌ㄥ紩鎿?

mysql> CREATE TABLE tb_booktype(
    -> id int(10) unsigned NOT NULL,
    -> typename varchar(30),
    -> days int(10) unsigned
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

聽銆€銆€2.璁剧疆鑷绫诲瀷瀛楁鐨勫悓鏃讹紝鏈€濂藉皢鍏惰缃负涓婚敭锛堟敞鎰忓弽寮曞彿鍜屽崟寮曞彿鐨勫尯鍒紝鍙嶅紩鍙锋槸Esc涓嬮潰閭ｄ釜锛?

mysql> CREATE TABLE tb_booktype1(
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> typename varchar(30),
    -> days int(10) unsigned,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

聽銆€銆€3.璁剧疆瀛楃闆?

mysql> CREATE TABLE tb_booktype2(
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> typename varchar(30),
    -> days int(10) unsigned,
    -> PRIMARY KEY (`id`)
    -> )DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)

聽銆€銆€4.澶嶅埗琛ㄧ粨鏋勶紝瀹炵幇鍦ㄦ暟鎹簱db_library涓垱寤轰竴浠芥暟鎹〃tb_bookinfo鐨勫壇鏈瑃b_bookinfobak銆?

銆€銆€锛?锛夊悜鏁版嵁琛╰b_bookinfo涓彃鍏ヤ竴鏉℃暟鎹?

mysql> INSERT INTO tb_bookinfo VALUES ('17120107','Java King','3','LianJiang','115',49.80,350,1,'2018-04-17',0,1);
Query OK, 1 row affected (0.00 sec)

聽銆€銆€锛?锛夊垱寤轰竴浠芥暟鎹〃tb_bookinfo鐨勫壇鏈瑃b_bookinfobak

mysql> CREATE TABLE tb_bookinfobak LIKE tb_bookinfo;
Query OK, 0 rows affected (0.02 sec)

聽銆€銆€锛?锛夋煡鐪嬩袱涓〃鐨勭粨鏋?

mysql> DESC tb_bookinfo;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> DESC tb_bookinfobak;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

聽銆€銆€锛?锛夊垎鍒煡鐪嬩袱涓〃鐨勫唴瀹癸紝鍙戠幇澶嶅埗鐨勮〃涓病鏈夊鍒惰〃涓殑鏁版嵁

mysql> SELECT * FROM tb_bookinfobak;
Empty set (0.00 sec)

mysql> SELECT * FROM tb_bookinfo;
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
1 row in set (0.00 sec)

聽銆€銆€锛?锛夊鏋滃湪澶嶅埗鏁版嵁琛ㄦ椂锛屾兂瑕佸悓鏃跺鍒跺叾涓殑鏁版嵁鐨勮瘽锛岄渶瑕佽繖鏍峰疄鐜般€備絾鏄紝鏂板鍒跺嚭鏉ョ殑鏁版嵁琛ㄥ苟涓嶅寘鎷師琛ㄤ腑璁剧疆鐨勪富閿€佽嚜鍔ㄧ紪鍙风瓑鍐呭銆?

mysql> CREATE TABLE tb_bookinfobak1 AS SELECT * FROM tb_bookinfo;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb_bookinfobak1;
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
1 row in set (0.00 sec)

聽銆€銆€锛?锛夊鏋滆澶嶅埗涓€涓嬭〃缁撴瀯鍜屾暟鎹兘瀹屽叏涓€鏍风殑鏁版嵁琛紝閭ｄ箞闇€瑕佸簲鐢ㄤ笅闈袱鍙ヨ鍙ュ疄鐜般€?

聽

mysql> CREATE TABLE tb_bookinfobak2 LIKE tb_bookinfo;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tb_bookinfobak2 SELECT * FROM tb_bookinfo;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb_bookinfobak2;
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
1 row in set (0.00 sec)

聽銆€銆€涓夈€佷慨鏀硅〃缁撴瀯

銆€銆€1.鍦ㄦ暟鎹〃tb_bookinfobak涓坊鍔犱竴涓猼ranslator瀛楁锛屽苟涓斿皢inTime鐨勭被鍨嬬敱date鏀逛负DATETIME(6)

mysql> desc tb_bookinfobak;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> alter table tb_bookinfobak add translator varchar(30) not null,
    -> modify inTime DATETIME(6);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_bookinfobak;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| barcode    | varchar(30)      | YES  |     | NULL    |       |
| bookname   | varchar(70)      | YES  |     | NULL    |       |
| typeid     | int(10) unsigned | YES  |     | NULL    |       |
| author     | varchar(30)      | YES  |     | NULL    |       |
| ISBN       | varchar(20)      | YES  |     | NULL    |       |
| price      | float(8,2)       | YES  |     | NULL    |       |
| page       | int(10) unsigned | YES  |     | NULL    |       |
| bookcase   | int(10) unsigned | YES  |     | NULL    |       |
| inTime     | datetime(6)      | YES  |     | NULL    |       |
| del        | tinyint(1)       | YES  |     | 0       |       |
| id         | int(11)          | NO   |     | NULL    |       |
| translator | varchar(30)      | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

聽銆€銆€2.淇敼绾︽潫鏉′欢

銆€銆€锛?锛夋坊鍔犱富閿害鏉熸牸寮忎负Alter TABLE 鏁版嵁琛ㄥ悕 ADD CONSTRAINT 绾︽潫鍚?绾︽潫绫诲瀷锛堝瓧娈靛悕锛?

銆€銆€銆€銆€聽鍏朵腑绾︽潫绫诲瀷鍖呮嫭锛歅RIMARY KEY锛堜富閿害鏉燂級銆丏EFAULT锛堥粯璁ゅ€肩害鏉燂級銆乁NIQUE KEY锛堝敮涓€绾︽潫锛夈€丯OT NULL锛堥潪绌虹害鏉燂級銆丗OREIGN KEY锛堝閿害鏉燂級銆?

mysql> Alter TABLE tb_bookinfo ADD CONSTRAINT mrprimary PRIMARY KEY(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_bookinfo;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   | PRI | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

聽銆€銆€锛?锛夊垹闄ょ害鏉熸潯浠?

銆€銆€銆€銆€鍒犻櫎涓婚敭绾︽潫锛欰LTER TABLE 琛ㄥ悕 DROP PRIMARY KEY;

銆€銆€銆€銆€鍒犻櫎澶栭敭绾︽潫锛欰LTER TABLE 琛ㄥ悕 DROP FOREIGN 绾︽潫鍚嶏紱

銆€銆€銆€銆€鍒犻櫎鍞竴鎬х害鏉燂細ALTER TABLE 琛ㄥ悕 DROP INDEX 鍞竴绱㈠紩鍚嶏紱

銆€銆€锛?锛変慨鏀硅〃鐨勫叾浠栭€夐」

銆€銆€銆€銆€淇敼琛ㄧ殑瀛樺偍寮曟搸锛欰LTER TABLE 琛ㄥ悕 ENGINE=鏂扮殑瀛樺偍寮曟搸绫诲瀷

銆€銆€銆€銆€淇敼琛ㄧ殑瀛楃闆嗭細ALTER TABLE 琛ㄥ悕 DEFAULT CHARSET=鏂扮殑瀛楃闆?

銆€銆€銆€銆€淇敼琛ㄧ殑鑷绫诲瀷瀛楁鐨勫垵濮嬪€硷細ALTER TABLE 琛ㄥ悕 AUTO_INCREMENT=鏂扮殑鍒濆鍊?

銆€銆€锛?锛変慨鏀硅〃鍚?

銆€銆€銆€銆€閲嶅懡鍚嶏細RENAME TABLE 鏁版嵁琛ㄥ悕1 To 鏁版嵁琛ㄥ悕2

銆€銆€鍥涖€佸垹闄よ〃

銆€銆€銆€銆€銆€銆€ DROP TABLE 鏁版嵁琛ㄥ悕锛?

銆€銆€ 銆€銆€銆€銆€DROP TABLE IF EXISTS 鏁版嵁琛ㄥ悕锛?

銆€銆€浜斻€佽缃储寮?

銆€銆€1.绱㈠紩鏄竴绉嶅皢鏁版嵁搴撲腑鍗曞垪鎴栬€呭鍒楃殑鍊艰繘琛屾帓搴忕殑缁撴瀯銆傚湪MySQL涓紝绱㈠紩鐢辨暟鎹〃涓殑涓€鍒楁垨澶氬垪缁勫悎鑰屾垚锛屽垱寤虹储寮曠殑鐩殑鏄负浜嗕紭鍖栨暟鎹簱鐨勬煡璇㈤€熷害銆?

銆€銆€聽聽閫氳繃绱㈠紩鏌ヨ鏁版嵁锛屼笉浣嗗彲浠ユ彁楂樻煡璇㈤€熷害锛屼篃鍙互闄嶄綆鏈嶅姟鍣ㄧ殑璐熻浇銆傚垱寤虹储寮曞悗锛岀敤鎴锋煡璇㈡暟鎹椂锛岀郴缁熷彲浠ヤ笉蹇呴亶鍘嗘暟鎹〃涓殑鎵€鏈夎褰曪紝鑰屾槸鏌ヨ绱㈠紩鍒椼€傝繖鏍峰氨鍙互鏈夋晥鍦版彁楂樻暟鎹簱绯荤粺鐨勬暣浣撴€ц兘銆傝繖鍜屾垜浠€氳繃鍥句功鐨勭洰褰曟煡鎵炬兂瑕侀槄璇荤殑绔犺妭鍐呭涓€鏍凤紝鍗佸垎鏂逛究銆?

銆€銆€聽浣嗘槸锛屽垱寤虹储寮曞拰缁存姢闇€瑕佽€楄垂鏃堕棿鍚屾椂杩橀渶瑕佸崰鐢ㄧ墿鐞嗙┖闂达紝缁欐暟鎹殑缁存姢閫犳垚寰堝楹荤儲銆傚悜鏈夌储寮曠殑琛ㄤ腑鎻掑叆璁板綍鏃讹紝鏁版嵁搴撶郴缁熶細鎸夌収绱㈠紩杩涜鎺掑簭銆傛墍浠ワ紝鐢ㄦ埛鍙互灏嗙储寮曞垹闄ゅ悗鍐嶆彃鍏ユ暟鎹紝褰撴暟鎹彃鍏ユ搷浣滃畬鎴愬悗锛岀敤鎴峰彲浠ラ噸鏂板垱寤虹储寮曘€?

銆€銆€聽甯歌MySQL绱㈠紩鍖呮嫭锛氭櫘閫氥€佸敮涓€鎬с€佸叏鏂囥€佸崟鍒椼€佸鍒椼€佺┖闂淬€?

銆€銆€2.鍒涘缓绱㈠紩

銆€銆€锛?锛夊湪寤虹珛鏁版嵁琛ㄦ椂鍒涘缓绱㈠紩锛屽苟涓旀煡鐪嬭〃缁撴瀯銆?

mysql> CREATE TABLE tb_score(
    -> id int(11) auto_increment primary key not null,
    -> name varchar(50) not null,
    -> math int(5) not null,
    -> english int(5) not null,
    -> chinese int(5) not null,
    -> index(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE tb_score;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_score | CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `math` int(5) NOT NULL,
  `english` int(5) NOT NULL,
  `chinese` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

銆€銆€锛?锛夊湪宸插缓绔嬬殑鏁版嵁琛ㄤ腑鍒涘缓绱㈠紩

mysql> CREATE INDEX idx_name ON tb_bookinfo(bookname);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_bookinfo;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_bookinfo | CREATE TABLE `tb_bookinfo` (
  `barcode` varchar(30) DEFAULT NULL,
  `bookname` varchar(70) DEFAULT NULL,
  `typeid` int(10) unsigned DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `ISBN` varchar(20) DEFAULT NULL,
  `price` float(8,2) DEFAULT NULL,
  `page` int(10) unsigned DEFAULT NULL,
  `bookcase` int(10) unsigned DEFAULT NULL,
  `inTime` date DEFAULT NULL,
  `del` tinyint(1) DEFAULT '0',
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`bookname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

聽銆€銆€3.鍒犻櫎绱㈠紩

mysql> DROP INDEX idx_name ON tb_bookinfo;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_bookinfo;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_bookinfo | CREATE TABLE `tb_bookinfo` (
  `barcode` varchar(30) DEFAULT NULL,
  `bookname` varchar(70) DEFAULT NULL,
  `typeid` int(10) unsigned DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `ISBN` varchar(20) DEFAULT NULL,
  `price` float(8,2) DEFAULT NULL,
  `page` int(10) unsigned DEFAULT NULL,
  `bookcase` int(10) unsigned DEFAULT NULL,
  `inTime` date DEFAULT NULL,
  `del` tinyint(1) DEFAULT '0',
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

聽銆€銆€鍏€佸畾涔夌害鏉?

銆€銆€1.瀹氫箟涓婚敭绾︽潫

銆€銆€涓婚敭鍙互浣胯〃涓殑鏌愪竴鍒楋紝涔熷彲浠ユ槸琛ㄤ腑澶氫釜鍒楁墍鏋勬垚鐨勪竴涓粍鍚堛€傚叾涓紝鐢卞涓垪缁勫悎鑰屾垚鐨勪富閿篃鎴愪负澶嶅悎涓婚敭銆?

銆€銆€鍘熷垯锛氭瘡涓〃鍙兘瀹氫箟涓€涓富閿€?

銆€銆€銆€銆€ 聽聽鍞竴鎬у師鍒欍€備富閿殑鍊煎繀椤昏兘澶熷敮涓€鏍囪瘑琛ㄤ腑姣忎竴琛岃褰曪紝鍒囦笉鑳戒负NULL銆?

銆€銆€銆€銆€ 聽聽鏈€灏忓寲瑙勫垯銆傚鍚堜富閿笉鑳藉寘鍚笉蹇呰鐨勫浣欏垪銆?

銆€銆€銆€銆€銆€ 涓€涓垪鍚嶅湪澶嶅悎涓婚敭鐨勫垪琛ㄤ腑鍙兘鍑虹幇涓€娆°€?

銆€銆€锛?锛変綔涓哄垪鐨勫畬鎴愭€х害鏉燂細鍦ㄨ〃鐨勬煇涓垪鐨勫睘鎬у畾涔夋椂锛屽姞涓婂叧閿瓧PRIMARY KEY

mysql> CREATE TABLE tb_manager(
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(30),
    -> PWD varchar(30)
    -> );
Query OK, 0 rows affected (0.02 sec)

銆€銆€锛?锛変綔涓鸿〃鐨勫畬鏁存€х害鏉燂細鍦ㄨ〃鐨勬墍鏈夊垪鐨勫睘鎬у畾浠ュ悗锛屽姞涓奝RIMARY KRY锛坕nsex_col_name,...锛?

mysql> create table tb_student(
    -> id int(10) auto_increment,
    -> name varchar(30) not null,
    -> sex varchar(2),
    -> classid int not null,
    -> birthday date,
    -> PRIMARY KEY(id,classid)
    -> );
Query OK, 0 rows affected (0.02 sec)

銆€銆€2.瀹氫箟鍊欓€夐敭绾︽潫

銆€銆€濡傛灉涓€涓睘鎬ч泦鑳藉敮涓€鏍囪瘑鍏冪粍锛屼笖鍙堜笉鍚湁澶氫綑鐨勫睘鎬э紝閭ｄ箞杩欎釜灞炴€ч泦绉颁负鍏崇郴鐨勫€欓€夐敭銆傚€欓€夐敭鐨勫€煎繀椤绘槸鍞竴鐨勶紝涓斾笉鑳戒负绌恒€傚叾瀹炵幇鏂规硶涓庝富閿害鏉熺被浼硷紝涔熸槸鍙綔涓哄垪鐨勫畬鏁存€х害鏉熸垨鑰呰〃鐨勫畬鏁存€х害鏉熴€傚€欓€夐敭鍜屼富閿殑鍖哄埆鏈夛細涓€涓〃鍙兘鍒涘缓涓€涓富閿紝浣嗗彲浠ュ畾涔夎嫢骞蹭釜鍊欓€夐敭锛涘畾涔変富閿害鏉熸椂锛岀郴缁熶細鑷姩鍒涘缓PRIMARY KEY绱㈠紩锛岃€屽畾涔夊€欓€夐敭绾︽潫鏃讹紝绯荤粺浼氳嚜鍔ㄥ垱寤篣NIQUE绱㈠紩銆?

mysql> CREATE TABLE tb_managerbak(
    -> id int(10) unsigned NOT NULL,
    -> bookname varchar(70) UNIQUE
    -> );
Query OK, 0 rows affected (0.02 sec)

銆€銆€3.瀹氫箟闈炵┖绾︽潫

銆€銆€閫氳繃CREATE TABLE鎴朅LTER TABLE璇彞涓紝鏌愪釜鍒楀畾涔夊悗闈㈠姞涓婂叧閿瓧NOT NULL鏉ュ畾涔夈€?

銆€銆€4.瀹氫箟CHECK绾︽潫

銆€銆€锛?锛夊鍒楀疄鏂紺HECK绾︽潫锛岄檺鍒跺叾age瀛楁鐨勫€煎彧鑳芥槸7~18锛堜笉鍖呮嫭18锛?

mysql> CREATE TABLE tb_managerbak1(
    -> id int(10) unsigned NOT NULL,
    -> age int not null CHECK(age>6 and age<18)
    -> );
Query OK, 0 rows affected (0.03 sec)

聽銆€銆€锛?锛夊琛ㄥ疄鏂紺HECK绾︽潫锛岄檺鍒跺叾typeid瀛楁鐨勫€煎彧鑳芥槸tb_booktypr琛ㄤ腑id瀛楁鐨勬煇涓€涓猧d鍊?

mysql> CREATE TABLE tb_managerbak2(
    -> id int(10) unsigned NOT NULL,
    -> typeid int(10) unsigned,
    -> CHECK(typeid IN (SELECT id FROM tb_booktype))
    -> );
Query OK, 0 rows affected (0.03 sec)

聽

銆€銆€

杞浇浜?https://www.cnblogs.com/BigJunOba/p/8862718.html

Mysql数据库（三）Mysql表结构管理

继续阅读

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

NOSQL安全攻击

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

登录plsql 报错 the account is locked --用户被锁

Effective Java 8:通用程序设计

SequoiaDB巨杉数据库C++驱动概述

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method