update set 语句是mysql最常用的修改和更新语句。
A update set 与where搭配使用,变更某些记录:
update +表名 +set+ 变更后的信息 +where子句;
例如:
update stu set birth=1988,department='中文系' where id=9 and name='张三';
注意:如果变更信息后面没有加where子句指定其变更的内容,那么update set语句就会把同字段中的所有信息全部更新,修改。
例如:
mysql> select * from c1score;
+-------+------+
| score | s |
+-------+------+
| 56 | 1 |
| 79 | 2 |
| 91 | 3 |
| 46 | 5 |
| 35 | 6 |
+-------+------+
5 rows in set (0.08 sec)
mysql> update c1score set score=score+8;
Query OK, 5 rows affected (0.13 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from c1score;
+-------+------+
| score | s |
+-------+------+
| 64 | 1 |
| 87 | 2 |
| 99 | 3 |
| 54 | 5 |
| 43 | 6 |
+-------+------+
5 rows in set (0.00 sec)
alter语句也具有修改的功能。而且alter的功能远远要比update set 多,它不仅仅可以1修改记录,2还可以修改表名和3字段属性。
1修改表名的语法:
alter table+旧表名+rename to 新表名;
例如:
mysql> select * from joke;
+-----+-----------+-------+------+
| uid | name | sex | age |
+-----+-----------+-------+------+
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan22 | 1male | 22 |
+-----+-----------+-------+------+
4 rows in set (0.01 sec)
2修改字段名的语法:
alter table + 表名 +change +原字段名+新字段名+TYPE(字符长度);
例如:
mysql> alter table joke change uid gid int (12);
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from joke;
+------+-----------+-------+------+
| gid | name | sex | age |
+------+-----------+-------+------+
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan22 | 1male | 22 |
+------+-----------+-------+------+
4 rows in set (0.00 sec)
3修改字段属性的语法:
alter table + 表名 +change +原字段名+原字段名+TYPE(字符长度);
mysql> desc joke;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid | int(12) | YES | | NULL | |
| name | varchar(123) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3把SC表中的ID字段变成SC表中的主键
alter table sc change id id int primary key;