MySql数据库的学习(基础篇三)
概述
上篇,我们简单的学习了一下,数据表的约束操作,今天,我们接着上篇的内容来学习。
学习
修改数据表
我们有时候进行数据表操作时,会去增删约束、增删字段等,不可能每次都去删表重建,这里就有了语句对应这些操作。
添加单列
语法 ALTER TABLE tbl_name ADD [COLUMNS] col_name colimn_definition [FIRST | AFTER col_name]
如: 我们用户表的字段为:
mysql> show columns from userinfor1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| _id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | int(10) unsigned | YES | MUL | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
我们如果给它加上一个工资salary属性要怎么处理呢?
这里就可以这么做:
mysql> alter table userinfor1 add salary float(8,2) unsigned not null after user
name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from userinfor1;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| _id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| salary | float(8,2) unsigned | NO | | NULL | |
| pid | int(10) unsigned | YES | MUL | NULL | |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
添加多列
语法 ALTER TABLE tbl_name ADD [COLUMNS] (col_name column_definition,…)
我们再给用户表加上两个字段sex和phone
mysql> alter table userinfor1 add (sex varchar(5) not null,phone varchar(11) not
null);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from userinfor1;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| _id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| salary | float(8,2) unsigned | NO | | NULL | |
| pid | int(10) unsigned | YES | MUL | NULL | |
| sex | varchar(5) | NO | | NULL | |
| phone | varchar(11) | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除列
语法 ALTER TABLE tbl_name DROP [COLUMN] col_name (多列用’,'隔开)
如这里我们将添加的列全删掉
mysql> alter table userinfor1 drop salary,drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table userinfor1 drop phone;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加/删除主键约束
语法 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] PRIMARY KEY [index_type]] (index_col_name,…)
mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| provinces |
| students |
| tab1 |
| tab2 |
| tab3 |
| tab4 |
| tab5 |
| userinfor1 |
+--------------------+
8 rows in set (0.00 sec)
如这里为表tab1添加主键
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> alter table tab1 add _id int unsigned;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tab1 add primary key (_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
删除主键
语法 ALTER TABLE tbl_name DROP PRIMARY KEY
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> alter table tab1 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
添加/删除唯一约束
语法 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_type] (index_col_name,…)
同样,我们对tab1进行操作
mysql> alter table tab1 add unique (username);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除唯一约束
语法 ALTER TABLE tbl_name DROP {INDEX | KEY} index_name
mysql> alter table tab1 drop index username;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
添加/删除外键约束
语法 ALTER TABLE tbl_name ADD FOREIGN KEY (col_name) REFERENCES (father_col_name)
mysql> alter table tab2 add id int unsigned;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| salary | float(8,2) unsigned | NO | | NULL | |
| id | int(10) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table tab2 add foreign key(id) references tab1(_id) on delete casca
de;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| salary | float(8,2) unsigned | NO | | NULL | |
| id | int(10) unsigned | YES | MUL | NULL | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除外键约束
语法 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
这里的fk_symbol我们可以从表结构得到
mysql> show create table tab2\G;
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`username` varchar(20) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`salary` float(8,2) unsigned NOT NULL,
`id` int(10) unsigned DEFAULT NULL,
KEY `id` (`id`),
CONSTRAINT `tab2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `tab1` (`_id`) ON DELET
E CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
其中的 ‘tab2_ibfk_1’ 就是。
mysql> alter table tab2 drop foreign key tab2_ibfk_1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tab2\G;
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`username` varchar(20) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`salary` float(8,2) unsigned NOT NULL,
`id` int(10) unsigned DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
添加/删除默认约束
语法 ALTER TABLE tbl_name ALTER col_name SET DEFAULT number;
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table tab1 alter age set default 20;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 20 | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除默认约束
语法 ALTER TABLE tbl_name ALTER col_name DROP DEFAULT;
mysql> alter table tab1 alter age drop default;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from tab1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| _id | int(10) unsigned | NO | PRI | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
基础三到此,转到基础四
欢迎大家关注我的公众号
上一篇: MySql数据库的学习(基础篇二)
下一篇: MySql授权某个表的权限给新建用户