mysql中alter数据表中增加、删除字段与表名修改例子

alter是非常强大的一个功能我们可以利用alter来修改数据表表名字体名及一些其它的操作了,下面一起来看看mysql中alter数据表中增加、删除字段与表名修改的一个例子.

修改删除mysql数据库中的数据内容:

1. [root@hk~]#/usr/local/mysql/bin/mysql-uroot-p'admin'#进入mysql
2. mysql>createdatabasegbkdefaultcharactersetgbkcollategbk_chinese_ci;#建立一个名字叫做gbk的数据库
3. mysql>usegbk
4. mysql>showdatabases;
5. +--------------------+
6. |Database|
7. +--------------------+
8. |information_schema|
9. |gbk|
10. +--------------------+
11. mysql>showtables;
12. Emptyset(0.00sec)
13. mysql>createtabletest(#建立一个叫做test的数据表
14. ->idint(4)notnullprimarykeyauto_increment,
15. ->namechar(20)notnull
16. ->);
17. QueryOK,0rowsaffected(0.13sec)
18. mysql>showtables;
19. +---------------+
20. |Tables_in_gbk|
21. +---------------+
22. |test|
23. +---------------+
24. 1rowinset(0.00sec)
25. mysql>insertintotest(id,name)values(1,'zy');#插入部分内容
26. mysql>insertintotest(id,name)values(2,'binghe');
27. mysql>insertintotest(id,name)values(3,'zilong');
28. mysql>insertintotest(id,name)values(4,'feng');
29. mysql>select*fromtest;#检索整个test表
30. +----+--------+
31. |id|name|
32. +----+--------+
33. |1|zy|
34. |2|binghe|
35. |3|zilong|
36. |4|feng|
37. +----+--------+
38. 4rowsinset(0.00sec)
39. [root@hk~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'-Bgbk>/tmp/gbk.sql#备份gbk数据库
40. mysql>updatetestsetname='zy';#未定义
41. mysql>select*fromtest;#
42. +----+------+
43. |id|name|
44. +----+------+
45. |1|zy|
46. |2|zy|
47. |3|zy|
48. |4|zy|
49. +----+------+
50. [root@hk~]#/usr/local/mysql/bin/mysql-uroot-p'admin'mysql>usegbk--phpfensi.com
51. mysql>select*fromtest;
52. +----+--------+
53. |id|name|
54. +----+--------+
55. |1|zy|
56. |2|binghe|
57. |3|zilong|
58. |4|feng|
59. +----+--------+
60. mysql>updatetestsetname='yadianna'whereid=1;
61. mysql>select*fromtest;
62. +----+----------+
63. |id|name|
64. +----+----------+
65. |1|yadianna|
66. |2|binghe|
67. |3|zilong|
68. |4|feng|
69. +----+----------+
70. mysql>updatetestsetid=999wherename='yadianna';
71. mysql>select*fromtest;
72. +-----+----------+
73. |id|name|
74. +-----+----------+
75. |2|binghe|
76. |3|zilong|
77. |4|feng|
78. |999|yadianna|
79. +-----+----------+
80. mysql>deletefromtestwhereid=999;
81. mysql>select*fromtest;
82. +----+--------+
83. |id|name|
84. +----+--------+
85. |2|binghe|
86. |3|zilong|
87. |4|feng|
88. +----+--------+
89. mysql>deletefromtestwhereid<4;#以条件删除
90. mysql>truncatetabletest;#删除all
91. mysql>select*fromtest;
92. Emptyset(0.00sec)

接上上面,修改数据库中表名,表中增加、删除字段.

1. mysql>usegbk#进入gbk数据库
2. mysql>desctest;
3. +-------+----------+------+-----+---------+----------------+
4. |Field|Type|Null|Key|Default|Extra|
5. +-------+----------+------+-----+---------+----------------+
6. |id|int(4)|NO|PRI|NULL|auto_increment|
7. |name|char(20)|NO||NULL||
8. +-------+----------+------+-----+---------+----------------+
9. mysql>altertabletestaddgenderchar(4);#增加gender
10. mysql>desctest;
11. +--------+----------+------+-----+---------+----------------+
12. |Field|Type|Null|Key|Default|Extra|
13. +--------+----------+------+-----+---------+----------------+
14. |id|int(4)|NO|PRI|NULL|auto_increment|
15. |name|char(20)|NO||NULL||
16. |gender|char(4)|YES||NULL||
17. +--------+----------+------+-----+---------+----------------+
18. mysql>altertabletestaddageint(4)aftername;
19. mysql>desctest;
20. +--------+----------+------+-----+---------+----------------+
21. |Field|Type|Null|Key|Default|Extra|
22. +--------+----------+------+-----+---------+----------------+
23. |id|int(4)|NO|PRI|NULL|auto_increment|
24. |name|char(20)|NO||NULL||
25. |age|int(4)|YES||NULL||
26. |gender|char(4)|YES||NULL||
27. +--------+----------+------+-----+---------+----------------+
28. mysql>showtables;
29. +---------------+
30. |Tables_in_gbk|
31. +---------------+
32. |test|
33. +---------------+
34. mysql>renametabletesttohello;
35. mysql>showtables;
36. +---------------+
37. |Tables_in_gbk|
38. +---------------+
39. |hello|
40. +---------------+
41. mysql>altertablehellorenametoworld;
42. mysql>showtables;
43. +---------------+
44. |Tables_in_gbk|
45. +---------------+
46. |world|
47. +---------------+
48. mysql>altertableworlddropage;
49. mysql>descworld;
50. +--------+----------+------+-----+---------+----------------+
51. |Field|Type|Null|Key|Default|Extra|
52. +--------+----------+------+-----+---------+----------------+
53. |id|int(4)|NO|PRI|NULL|auto_increment|
54. |name|char(20)|NO||NULL||
55. |gender|char(4)|YES||NULL||
56. +--------+----------+------+-----+---------+----------------+
57. 3rowsinset(0.00sec)