一、删除表中数据
1、实践删除表中的数据
1）命令语法：delete from 表名 where 表达式
a.实践，例如：删除表test中编号为1的记录
mysql> use oldboy Database changed mysql> select * from test; ---- ----------- | id | name | ---- ----------- | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | xiaozhang | ---- ----------- 6 rows in set (0.00 sec) mysql> delete from test where id=1; Query OK, 1 row affected (0.01 sec) mysql> select * from test; ---- ----------- | id | name | ---- ----------- | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | xiaozhang | ---- ----------- 5 rows in set (0.00 sec) mysql> delete from test where id>3; Query OK, 3 rows affected (0.00 sec) mysql> select * from test; ---- --------- | id | name | ---- --------- | 2 | oldgirl | | 3 | inca | ---- --------- 2 rows in set (0.00 sec)
提示：不加条件就是全部删除，也是非常危险的操作，delete from test
命令：truncate table 表名
truncate table test；#清空表中所有内容
mysql> select * from test; ---- --------- | id | name | ---- --------- | 2 | oldgirl | | 3 | inca | ---- --------- 2 rows in set (0.00 sec) mysql> truncate table test; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; Empty set (0.00 sec) mysql>
truncate table test；和delete from test；区别
1、truncate table test；更快。清空物理文件。
2、delete from test；逻辑清除，按行删。
二、增删改表的字段
1、命令语法及默认添加演示
1）命令语法：alter table 表名 add 字段 类型 其他
2）测试表数据
mysql> desc test; ------- ---------- ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ---------------- | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | sex | char(4) | YES | | NULL | | ------- ---------- ------ ----- --------- ---------------- 3 rows in set (0.00 sec)
默认添加到末尾 mysql> alter table test add age int(4) after name; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; ------- ---------- ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ---------------- | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | ------- ---------- ------ ----- --------- ---------------- 4 rows in set (0.00 sec)
这里只有俩种思想：1、添加到开头用参数 first
2、添加到中间或末尾 after 没有before
三、更改表名
1、rename 法
1）命令语法：rename table 原表名 to 新表名；
mysql> show tables -> ; ------------------ | Tables_in_oldboy | ------------------ | SC | | course | | student | | test | ------------------ 4 rows in set (0.00 sec) mysql> rename table test to test1; Query OK, 0 rows affected (0.01 sec) mysql> show tables; ------------------ | Tables_in_oldboy | ------------------ | SC | | course | | student | | test1 | ------------------ 4 rows in set (0.00 sec) mysql>
2、alter法
mysql> show tables; ------------------ | Tables_in_oldboy | ------------------ | SC | | course | | student | | test1 | ------------------ 4 rows in set (0.00 sec) mysql> alter table test1 rename to test; Query OK, 0 rows affected (0.00 sec) mysql> show tables; ------------------ | Tables_in_oldboy | ------------------ | SC | | course | | student | | test | ------------------ 4 rows in set (0.00 sec) mysql>