Mysql基礎教程 mysql進階

Mysql添加和刪除唯一



添加唯一


ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名稱](字段名稱,...)


添加唯一性約束或索引,測試添加唯一索引


mysql> CREATE TABLE IF NOT EXISTS user12(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> card CHAR(18) NOT NULL,
    -> test VARCHAR(20) NOT NULL,
    -> test1 CHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.29 sec)


mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI  | NULL    | auto_increment |
| username | varchar(20)      | NO   |     | NULL    |                |
| card     | char(18)        | NO   |     | NULL    |                |
| test     | varchar(20)      | NO   |     | NULL    |                |
| test1    | char(32)         | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)


選中表text12把username添加唯一約束


mysql> ALTER TABLE user12 ADD UNIQUE(username);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | NO   |     | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


查看表text12 創建時的詳細定義


mysql> SHOW CREATE TABLE user12;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


添加的同時加、指定索引名稱。名稱的作用是在刪除是好使用索引名稱。給user12添加索引并寫上名稱


mysql> ALTER TABLE user12 ADD CONSTARINT symbol uni_card UNIQUE KEY(card);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'symbol uni_card UNIQUE KEY(card)' at line 1


索引名稱要放在后面


mysql> ALTER TABLE user12 ADD CONSTARINT symbol  UNIQUE KEY uni_card(card);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'symbol  UNIQUE KEY uni_card(card)' at line 1


索引名稱要放在 symbol后面


mysql> ALTER TABLE user12 ADD CONSTRAINT symbol uni_card UNIQUE KEY(card);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uni_card UNIQUE KEY(card)' at line 1


索引名稱要放在最后面


mysql> ALTER TABLE user12 ADD CONSTRAINT symbol  UNIQUE KEY uni_card(card);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | NO   | UNI | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


查看表text12 創建時的詳細定義


mysql> SHOW CREATE TABLE user12;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                             |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `uni_card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


添加復合(聯合)索引


mysql> ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   | UNI | NULL    |                |
| card     | char(18)         | NO   | UNI | NULL    |                |
| test     | varchar(20)       | NO   | MUL | NULL    |                |
| test1    | char(32)         | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


查看表text12 創建時的詳細定義


mysql> SHOW CREATE TABLE user12;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `uni_card` (`card`),
  UNIQUE KEY `mulUni_test_test1` (`test`,`test1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



刪除唯一


ALTER TABLE tbl_name DROP {INDEX|KEY} index_name


刪除text12 username主鍵


mysql> ALTER TABLE user12 DROP INDEX username;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | NO   | UNI | NULL    |                |
| test     | varchar(20)         | NO   | MUL | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


刪除兩個唯一性索引


mysql> ALTER TABLE user12 DROP KEY uni_card;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 
mysql> ALTER TABLE user12 DROP KEY mulUni_test_test1;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | NO   |     | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


查看表text12 創建時的詳細定義


mysql> SHOW CREATE TABLE user12;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                     |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5 趣步靠赚钱 直播到底怎么赚钱的 爱彩人彩票安卓 玩快手赚钱秘诀 瑞彩网能赚钱吗 淘宝开什么店最赚钱 516千炮金蟾捕鱼 安徽麻将大全下载 手工编织店能够赚钱吗6 万彩会彩票游戏 刷照片赚钱的app 剑三95 采集赚钱 星露谷物语采矿怎么可以赚钱 供货给酒店什么最赚钱 梦幻新区打宝图赚钱吗 赚钱宝已绑定手机