mysql后台修改表_mysql之约束以及修改数据表
數據約束的分類：
————————————————————————————————————————————————————
外鍵約束的要求解析：
//在my文件中的這句話代表著搜索引擎，如果不是的就需要技能型更改。然后重啟。my文件存在于我的電腦中的mysql文件夾里。
# Thedefault storage engine that will be used when create new tables when
default-storage-engine=INNODB
root@127.0.0.1 t2>CREATE TABLEprovince(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR(20) NOT NULL
->);
Query OK,0 rows affected (0.13sec)
root@127.0.0.1 t2>SHOW CREATE TABLEprovince;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| province | CREATE TABLE`province` (
`id`smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`username`varchar(20) NOT NULL,PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
root@127.0.0.1 t2>CREATE TABLEaname(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR(10) NOT NULL,-> pid BIGINT,-> FOREIGN KEY(pid) REFERENCESprovince(id)->);
ERROR1005 (HY000): Can't create table't2.aname'(errno: 150)
root@127.0.0.1 t2>CREATE TABLE aname(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY(pid) REFERENCES province(id)
-> );
Query OK, 0 rows affected (0.23 sec)
root@127.0.0.1 t2>SHOW INDEXES FROMprovince\G;*************************** 1. row ***************************
Table: province
Non_unique:0Key_name:PRIMARYSeq_in_index:1Column_name: id
Collation: A
Cardinality:0Sub_part:NULLPacked:NULL
Null:
Index_type: BTREE
Comment:
Index_comment:1 row in set (0.10sec)
ERROR:
No query specified
root@127.0.0.1 t2>SHOW INDEXES FROManame\G;*************************** 1. row ***************************
Table: aname
Non_unique:0Key_name:PRIMARYSeq_in_index:1Column_name: id
Collation: A
Cardinality:0Sub_part:NULLPacked:NULL
Null:
Index_type: BTREE
Comment:
Index_comment:*************************** 2. row ***************************
Table: aname
Non_unique:1Key_name: pid
Seq_in_index:1Column_name: pid
Collation: A
Cardinality:0Sub_part:NULLPacked:NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:2 rows in set (0.00sec)
ERROR:
No query specified
——————————————————————————————————————————————————————————————————
外鍵約束的參照操作：
root@127.0.0.1 t2>CREATE TABLEaname1(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR(10) NOT NULL,-> pid SMALLINTUNSIGNED,-> FOREIGN KEY(pid) REFERENCES province(id) ON DELETE CASCADE
->);
Query OK,0 rows affected (0.23sec)
root@127.0.0.1 t2>SHOW CREATE TABLEaname1;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aname1 | CREATE TABLE`aname1` (
`id`smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`username`varchar(10) NOT NULL,
`pid`smallint(5) unsigned DEFAULT NULL,PRIMARY KEY(`id`),KEY`pid` (`pid`),CONSTRAINT `aname1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@127.0.0.1 t2>INSERT province(username) VALUES('A');
Query OK,1 row affected (0.16sec)
root@127.0.0.1 t2>INSERT province(username) VALUES('B');
Query OK,1 row affected (0.14sec)
root@127.0.0.1 t2>INSERT province(username) VALUES('C');
Query OK,1 row affected (0.13sec)
root@127.0.0.1 t2>SELECT * FROMprocvince;
ERROR1146 (42S02): Table 't2.procvince' doesn't exist
root@127.0.0.1 t2>SELECT * FROM province;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('D',3);
Query OK, 1 row affected (0.16 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('E',1);
Query OK, 1 row affected (0.07 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('E',t);
ERROR 1054 (42S22): Unknown column't'in'field list'root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('F',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t2`.`aname1`, CONSTRAINT `aname1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('F',2);
Query OK, 1 row affected (0.05 sec)
root@127.0.0.1 t2>SELECT * FROM province;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
root@127.0.0.1 t2>SELECT * FROM aname1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | D | 3 |
| 2 | E | 1 |
| 4 | F | 2 |
+----+----------+------+
3 rows in set (0.00 sec)
root@127.0.0.1 t2>DELETE FROM province where id = 3;
Query OK, 1 row affected (0.20 sec)
root@127.0.0.1 t2>SELECT * FROM province;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
+----+----------+
2 rows in set (0.00 sec)
root@127.0.0.1 t2>SELECT * FROM aname1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 2 | E | 1 |
| 4 | F | 2 |
+----+----------+------+
2 rows in set (0.00 sec)
——————————————————————————————————————————————————————————————————
表級約束以及列級約束：
在實際開發的時候我們使用列級約束的較多，表級約束較少。default和not null只存在列級約束，其他的都存在表級約束。
————————————————————————————————————————————————————————————
修改數據表增加和刪除列：
添加(刪除)列 alter table +表明drop+列名
添加(刪除)多列 alter table +表明 【drop+列名，drop+列名】；(在添加單列的時候所有的列不需要指明小括號，在指明多列的時候，我們不能指定位子)
添加單列alert table tbl_name add +列名字+column_definition [firstafter col_name]
root@127.0.0.1 t2>ALTER TABLE aname1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK,2 rows affected (0.34sec)
Records:2 Duplicates: 0 Warnings: 0root@127.0.0.1 t2>SHOW COLUMNS FROManame1;+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
root@127.0.0.1 t2>ALTER TABLE aname1 ADD ab TINYINT UNSIGNED NOT NULLAFTER username;
Query OK,2 rows affected (0.30sec)
Records:2 Duplicates: 0 Warnings: 0root@127.0.0.1 t2>SHOW COLUMNS FROManame1;+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| ab | tinyint(3) unsigned | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
在我們進行操作的時候我們可以同時的進行刪除和增加的操作，我們只需要在兩個操作之間用‘，’進行分隔就好了。
——————————————————————————————————————————————————————————
修改數據表添加約束：
添加外鍵約束：ALTER TABLE name ADD FOREIGN KEY(pid) REFERENCES provinces (id);
添加默認約束：ALTER TABLE name ALTER age SET DEFAULT 15;
刪除默認約束：ALTER TABLE name ALTER age DROP DEFAULT;
數據表的修改操作：無非就是添加列，刪除列，添加約束，刪除約束。用的是ALTER，而INSERT是對數據表添加插入記錄用的
1、添加主鍵約束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...);
2、添加單個唯一約束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);
3、添加多個唯一約束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...),ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);
Ps1：CONSTRAINT 可加可不加，加該關鍵字后還可以選擇添加主鍵別名；
Ps2：唯一約束可以有多個，但主鍵約束有且只能有一個。
root@127.0.0.1 t2>ALTER TABLE user2 ADD CONSTRAINT id PRIMARY KEY(id);
Query OK,0 rows affected (0.34sec)
Records:0 Duplicates: 0 Warnings: 0root@127.0.0.1 t2>SHOW COLUMNS fromuser2;+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | 0 | |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
————————————————————————————————————————
修改數據表—刪除約束：
1、刪除主鍵約束：
ALTER TABLE table_name DROP PRIMARY KEY;
2、刪除唯一約束：
ALTER TABLE table_name DROP {INDEX} key_name;
3、刪除外鍵約束：
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;
Ps1：唯一約束的 index_name 可通過 SHOW INDEX FROM table_name\G; 查詢。
Ps2：外鍵約束的名字 fk_symbol 可通過 SHOW CREATE TABLE table_name; 查詢。
Ps3：INDEX：index是索引標識，和create table name 中的table是相同的標識。
Ps4：當字段id smallint unsigned auto_increment primary key時，不可以刪除主鍵約束；必須先修改為 id smallint unsigned，再刪除主鍵約束。
————————————————————————————————————————
修改列定義以及更名數據表：
修改列定義和更名數據表1、修改列定義(列類型/列位置)ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST AFTER col_name];2、修改列名稱ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRSTAFTER col_name];3、數據表更名
方法1：ALTER TABLE tbl_name RENAME [TO/AS]new_tbl_name
方法2：
RENAMETABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]...(這種方法可以給多個數據表更名)
Ps1：修改數據類型(特別是大類型轉到小類型),需注意數據丟失的問題。
Ps2：盡量不要修改數據表名和列名，以免影響后臺等問題。
root@127.0.0.1 t2>SHOW COLUMNS fromtb3;+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.02sec)
root@127.0.0.1 t2>ALTER TABLE tb3 MODIFY id SMALLINT UNSIGNED NOT NULL;
Query OK,0 rows affected (0.34sec)
Records:0 Duplicates: 0 Warnings: 0root@127.0.0.1 t2>SHOW COLUMNS fromtb3;+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
我們在修改定義的時候我們不需要帶上主鍵的名稱。
總結
以上是生活随笔為你收集整理的mysql后台修改表_mysql之约束以及修改数据表的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python office自动化_Pyt
- 下一篇： springboot设置运行内存_doc