MySQL数据库应用（11）DML之表和字段

一、删除表中数据

　　　　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>