欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySql数据库的学习(基础篇三)

程序员文章站 2022-05-27 23:44:29
...

概述

上篇,我们简单的学习了一下,数据表的约束操作,今天,我们接着上篇的内容来学习。

学习

修改数据表

我们有时候进行数据表操作时,会去增删约束、增删字段等,不可能每次都去删表重建,这里就有了语句对应这些操作。

添加单列

语法 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数据库的学习(基础篇三)