(改)修改、更新数据表中的数据记录
UPDATE表名 SET 字段名1=字段值1,[字段名2=字段值2] [WHERE 条件表达式];
MySQL [mysql_chuid]> insert into student(id,name,sex)values(1,'chd','man');
Query OK, 1 row affected (0.00 sec)
MySQL [mysql_chuid]> insert into student(id,name,sex)values(2,'chuid','man');
Query OK, 1 row affected (0.02 sec)
MySQL [mysql_chuid]> insert into student(id,name,sex)values(3,'anivd','man'); # 添加三行新内容
Query OK, 1 row affected (0.02 sec)
MySQL [mysql_chuid]> select * from student; # 查询student表中所有的数据信息
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 1 | chd | man |
| 2 | chuid | man |
| 3 | anivd | man |
+----+-------+------+
3 rows in set (0.00 sec)
MySQL [mysql_chuid]> update students set id=5 where name_list='anivd'; # 将name_list=anivd的id改为5
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [mysql_chuid]> select * from students;
+----+-----------+------+
| id | name_list | sex |
+----+-----------+------+
| 1 | chd | man |
| 2 | chuid | man |
| 5 | anivd | man |
+----+-----------+------+
4 rows in set (0.00 sec)
(改)修改表名
ALTER TABLE旧表名 RENAME 新表名;
MySQL [mysql_chuid]> alter table student rename students;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql_chuid]> show tables;
+-----------------------+
| Tables_in_mysql_chuid |
+-----------------------+
| students |
+-----------------------+
1 row in set (0.00 sec)
(改)扩展表结构 [增加字段]
ALTER TABLE表名 ADD address varchar(50) default ‘’;
MySQL [mysql_chuid]> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | chd |
| 2 | chuid |
| 3 | anivd |
+----+-------+
3 rows in set (0.00 sec)
MySQL [mysql_chuid]> alter table student add sex char(3) default 'man'; # 增加表的字段
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [mysql_chuid]> select * from student;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 1 | chd | man |
| 2 | chuid | man |
| 3 | anivd | man |
+----+-------+------+
3 rows in set (0.00 sec)
(改)修改字段(列)名,添加唯一键
ALTER TABLE表名 CHANGE 旧字段名 新字段名 新数据类型 [unique key];
1)旧字段名:指修改前的字段名;
2)新字段名:指修改后的字段名;
3)新数据类型:指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样,但数据类型不能为空。
4)unique key : 唯一键(唯一键的作用:是为了解决表中字段需要具有唯一性的问题,也就是说唯一键的这个字段只会有一个)
# 修改表名students的字段name为name_list
MySQL [mysql_chuid]> alter table students change name name_list varchar(10) unique key;
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [mysql_chuid]> select * from students;
+----+-----------+------+
| id | name_list | sex |
+----+-----------+------+
| 1 | chd | man |
| 2 | chuid | man |
| 3 | anivd | man |
+----+-----------+------+
3 rows in set (0.01 sec)
MySQL [mysql_chuid]> insert into students(id,name_list,sex)values(5,'chen','man'); # 创建内容,指定name_list为chen
Query OK, 1 row affected (0.00 sec)
MySQL [mysql_chuid]> select * from students;
+----+-----------+------+
| id | name_list | sex |
+----+-----------+------+
| 1 | chd | man |
| 2 | chuid | man |
| 3 | anivd | man |
| 5 | chen | man |
+----+-----------+------+
4 rows in set (0.00 sec)
MySQL [mysql_chuid]> insert into students(id,name_list,sex)values(6,'chen','man'); # 再次想创建name_list为chen就会报错
ERROR 1062 (23000): Duplicate entry 'chen' for key 'name_list'
(改)修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。
修改字段数据类型的语法格式:ALTER TABLE <表名> MODIFY <字段名> <数据类型>
1)表名:指要修改数据类型的字段所在表的名称;
2)字段名:指需要修改的字段;
3)数据类型:指修改后字段的新数据类型。
MySQL [mysql_chuid]> desc class_1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| claID | int(5) | YES | | NULL | |
| id | int(5) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| hometown | varchar(20) | YES | | NULL | |
| sex | int(5) | YES | | NULL | |
| age | int(5) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
MySQL [mysql_chuid]> ALTER table class_1 MODIFY name varchar(15); # 将name字段的数据类型改为VARCHAR(15)
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [mysql_chuid]> desc class_1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| claID | int(5) | YES | | NULL | |
| id | int(5) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| hometown | varchar(20) | YES | | NULL | |
| sex | int(5) | YES | | NULL | |
| age | int(5) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)