MySQL数据库应用(11)DML之表和字段

时间:2022-05-12 02:35:32

一、删除表中数据

    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

 

MySQL数据库应用(11)DML之表和字段

 

 MySQL数据库应用(11)DML之表和字段

 

 

三、更改表名

    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>