MYSQL中添加、更新、删除数据
一：添加数据
1.准备工作，和以前一样，要先使用一个数据库，并添加一张数据表，具体操作如下：
1）使用数据库
mysql> USE itcsat;
Database changed
Database changed
2）创建数据表
mysql> CREATE TABLE student
-> (
-> id INT(5),
-> name CHAR(20)NOT NULL,
-> sex CHAR(5),
-> grade FLOAT(5)
-> );
Query OK, 0 rows affected
-> (
-> id INT(5),
-> name CHAR(20)NOT NULL,
-> sex CHAR(5),
-> grade FLOAT(5)
-> );
Query OK, 0 rows affected
3）查看数据表是否存在
mysql> SHOW TABLES;
+------------------+
| Tables_in_itcsat |
+------------------+
| student |
+------------------+
1 row in set
+------------------+
| Tables_in_itcsat |
+------------------+
| student |
+------------------+
1 row in set
4)查看数据表信息 （两种方法）
mysql> SHOW CREATE TABLE student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) DEFAULT NULL,
`name` char(20) NOT NULL,
`sex` char(5) DEFAULT NULL,
`grade` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(5) DEFAULT NULL,
`name` char(20) NOT NULL,
`sex` char(5) DEFAULT NULL,
`grade` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> DESC student;//查看的是表格的具体信息
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | char(20) | NO | | NULL | |
| sex | char(5) | YES | | NULL | |
| grade | float | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | char(20) | NO | | NULL | |
| sex | char(5) | YES | | NULL | |
| grade | float | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set
2.1第一种添加数据的方法
eg1：
INSERT INTO student(id,name,sex,grade)VALUES(1,"张三","男",89);
Query OK, 1 row affected
Query OK, 1 row affected
查看数据是否添加成功：SELECT *FROM student;
mysql> SELECT *FROM student;
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
+----+------+-----+-------+
1 row in set
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
+----+------+-----+-------+
1 row in set
注意：可以改变字段名的顺序，但是值也要相应改变，字段名必须和值相对应
eg2：
mysql> INSERT INTO student(name,id,grade,sex)VALUES("李四",2,80,"男");
Query OK, 1 row affected
Query OK, 1 row affected
mysql> SELECT *FROM student;
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
+----+------+-----+-------+
2 rows in set
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
+----+------+-----+-------+
2 rows in set
2.2第二种添加方法 //可以没有字段名，这样的话就和你建立的数据表的字段名一致
eg3：
mysql> INSERT INTO student VALUES(3,"王二","男",98);
Query OK, 1 row affected
Query OK, 1 row affected
mysql> SELECT *FROM student;
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
+----+------+-----+-------+
3 rows in set
+----+------+-----+-------+
| id | name | sex | grade |
+----+------+-----+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
+----+------+-----+-------+
3 rows in set
2.3第三种添加方法 //字段可以不全部添加完，只添加其中几个
eg4：
mysql> INSERT INTO student(id,name,grade)VALUES(4,"麻子",89);
Query OK, 1 row affected
Query OK, 1 row affected
mysql> SELECT * FROM student;
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
+----+------+------+-------+
4 rows in set
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
+----+------+------+-------+
4 rows in set
eg5：
mysql> INSERT INTO student(id,sex,grade)VALUES(5,"男",78);
Query OK, 1 row affected
Query OK, 1 row affected
mysql> SELECT *FROM student;
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
+----+------+------+-------+
5 rows in set
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
+----+------+------+-------+
5 rows in set
2.4第四种添加方法 //连续多行输入
INSERT INTO student(id,name,sex,grade)VALUES(6,"巴西","女",67),(7,"万茜","女",88);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT *FROM student;
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+------+------+-------+
7 rows in set
+----+------+------+-------+
| id | name | sex | grade |
+----+------+------+-------+
| 1 | 张三 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+------+------+-------+
7 rows in set
二.更新数据
1.更新一条记录中的一个字段
eg1：
mysql> UPDATE student SET name="图雪芳" WHERE id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT *FROM student;
+----+--------+------+-------+
| id | name | sex | grade |
+----+--------+------+-------+
| 1 | 图雪芳 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+------+-------+
7 rows in set
+----+--------+------+-------+
| id | name | sex | grade |
+----+--------+------+-------+
| 1 | 图雪芳 | 男 | 89 |
| 2 | 李四 | 男 | 80 |
| 3 | 王二 | 男 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+------+-------+
7 rows in set
2.更新几条记录中的相同字段
eg2：
mysql> UPDATE student SET sex="女" WHERE id<4;
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT *FROM student;
+----+--------+------+-------+
| id | name | sex | grade |
+----+--------+------+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+------+-------+
7 rows in set
+----+--------+------+-------+
| id | name | sex | grade |
+----+--------+------+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | NULL | 89 |
| 5 | | 男 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+------+-------+
7 rows in set
3.更新整个数据表数据的某个信息
eg3：
mysql> UPDATE student SET sex="女";
Query OK, 2 rows affected
Rows matched: 8 Changed: 2 Warnings: 0
Query OK, 2 rows affected
Rows matched: 8 Changed: 2 Warnings: 0
mysql> SELECT *FROM student;
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
| 5 | | 女 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+-----+-------+
7 rows in set
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
| 5 | | 女 | 78 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+-----+-------+
7 rows in set
三.删除数据
1.删除其中一条数据
eg1：
mysql> DELETE FROM student WHERE id=5;
Query OK, 1 row affected
Query OK, 1 row affected
mysql> SELECT *FROM student;
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+-----+-------+
6 rows in set
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
| 6 | 巴西 | 女 | 67 |
| 7 | 万茜 | 女 | 88 |
+----+--------+-----+-------+
6 rows in set
2.删除其中的几条数据
eg3：
mysql> DELETE FROM student WHERE id>4;
Query OK, 3 rows affected
Query OK, 3 rows affected
mysql> SELECT *FROM student;
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
+----+--------+-----+-------+
4 rows in set
+----+--------+-----+-------+
| id | name | sex | grade |
+----+--------+-----+-------+
| 1 | 图雪芳 | 女 | 89 |
| 2 | 李四 | 女 | 80 |
| 3 | 王二 | 女 | 98 |
| 4 | 麻子 | 女 | 89 |
+----+--------+-----+-------+
4 rows in set
3.表中的记录全部删除
eg4：
mysql> DELETE FROM student;
Query OK, 4 rows affected
Query OK, 4 rows affected
mysql> SELECT *FROM student;
Empty set
Empty set
准备工作：删除原来的student数据表，新建一个数据表，添加数据，查看一下，准备工作就准备完毕了。
mysql> DROP TABLE student;
Query OK, 0 rows affected
Query OK, 0 rows affected
mysql> SHOW TABLES;
Empty set
Empty set
mysql> CREATE TABLE student
-> (
-> id INT(10),
-> name CHAR(20)
-> );
Query OK, 0 rows affected
-> (
-> id INT(10),
-> name CHAR(20)
-> );
Query OK, 0 rows affected
mysql> DESC student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set
mysql> INSERT INTO student(name)VALUES("黄珊"),("黄蓉"),("郭襄"),("杨过"),("张无忌");
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT *FROM student;
+------+--------+
| id | name |
+------+--------+
| NULL | 黄珊 |
| NULL | 黄蓉 |
| NULL | 郭襄 |
| NULL | 杨过 |
| NULL | 张无忌 |
+------+--------+
5 rows in set
+------+--------+
| id | name |
+------+--------+
| NULL | 黄珊 |
| NULL | 黄蓉 |
| NULL | 郭襄 |
| NULL | 杨过 |
| NULL | 张无忌 |
+------+--------+
5 rows in set
使用TRUNCATE执行结果如下：
mysql> TRUNCATE TABLE student;
Query OK, 0 rows affected
Query OK, 0 rows affected
mysql> SELECT *FROM student;
Empty set
Empty set
TRUNCATE与DELETE的区别
