通过前面的学习,相信读友们对数据库和数据表的基本操作有了一定的了解,但要想操作数据库中的数据,则需要通过数据操纵语言实现。MySQL提供的数据操纵语言以INSERT、UPDATE、和DELETE语句为核心,使用这三中语句分别可以完成数据的插入、更新和删除。这一章通过对这三种语句对数据的操作进行讲解。
插入数据
数据表创建好后就可以插入数据了。在MySQL中可以使用INSERT语句向数据表中一次插入单条或多条数据,这个我在前面的文章中也是提到过怎么插入数据的,下面带大家深入了解。
一次插入单条数据
向数据表中插入单条数据的语法具体如下:
INSERT [INTO] 数据表名(字段名) values(值)
在上述语法格式中,数据表名是指要插入的数据的数据表的名称;字段名指的是插入数据的具体字段;值指的是插入字段对应的数据
需要注意的是,使用INSERT语句插入数据时,字段名称是可以省略的(不指定的)。如果不指定字段名称,表示插入的数据将按照数据表的字段的顺序向所有字段插入输入,如果不按照顺序插入会报错,少插入也会报错。如果指定需要插入数据的字段名,则插入的值的顺序与指定字段名名顺序需要保持一致。
接下来对向所有字段和部分字段插入数据进行详解。
1.向所有字段插入数据
向数据表所有字段插入数据时,可以指定所有的字段名,也可以省略所有的字段名。
指定所有字段名
创建一个班级表,一个班级一般都学生,有学生那就有学号,还有成绩,还分男女,就设置这几个字段
先看一下表的结构
mysql> desc class02;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(12) | NO | | NULL | |
| score | int | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
id是学号,设置了主键约束;name是姓名,非空约束;score是成绩;sex是性别,就先设置这几个几段。创建数据表的语句及设置约束都在前面的几章,不会的可以去回忆回忆。这里我就不啰嗦了。
现在我们插入一些数据:
mysql> INSERT INTO class02(id,name,score,sex) VALUES(001,'李白',100,'男');
Query OK, 1 row affected (0.01 sec)
可以看到我们插入了一条数据,并且是按照顺序插入的数据。想要验证数据是否按要求插入到数据表中,我们使用SELECT语句查询数据(查询语句后面会详细的讲,这里会使用就行),具体SQL语句及执行结果如下:
语法格式:SELECT*FROM 表名
mysql> SELECT*FROM class02;
+----+--------+-------+------+
| id | name | score | sex |
+----+--------+-------+------+
| 1 | 李白 | 100 | 男 |
+----+--------+-------+------+
1 row in set (0.00 sec)
从上述执行结果来看,班级表中成功的插入了一条数据,1 row in set表示查询出一条数据。
省略所有字段名
mysql> INSERT INTO class02 VALUES(002,'杜甫',100,'男');
Query OK, 1 row affected (0.00 sec)
这个语句相比较于上面的语句来说表的后面少了字段名,也就是把字段名省略了,但语句还是执行成功了。
使用SELECT语句查看数据是否成功插入:
mysql> SELECT*FROM class02;
+----+--------+-------+------+
| id | name | score | sex |
+----+--------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
+----+--------+-------+------+
2 rows in set (0.00 sec)
结果显示成功插入了数据,这里给大家说一下因为id字段的数据类型我创建语句设置的是int整型,因此001=1,会把前面的两个0省略,如果不想省略的话去看我前面写的关于数据类型的文章。
2.向部分字段插入数据
有时我们只需要或者只有部分数据,那么可以选择向部分字段插入数据。例如做销售的一般只会先获取客户的部分信息,此时需要往客户管理系统中添加客户信息时,可以先将该部分信息存入系统。
举个例子:
这个是我根据刚创建的班级表举的不恰当的例子,理解我想要表达的是什么即可。
假如班级里的同学们的成绩大部分都出来了,但后面的因为系统故障成绩无法显示,出成绩单的时候表格里面是空的,只有学号,姓名,但没有成绩。这个就相当于插入部分数据,注意如果设置了非空约束或者主键约束,里面的数据不能为空。
具体SQL语句及执行结果如下:
mysql> INSERT INTO class02(id,name) VALUES(003,'张三');
Query OK, 1 row affected (0.00 sec)
这个语句只插入了张三同学的id,姓名,没有成绩和性别,使用SELECT语句查看是否插入成功:
mysql> SELECT*FROM class02;
+----+--------+-------+------+
| id | name | score | sex |
+----+--------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
+----+--------+-------+------+
3 rows in set (0.00 sec)
从结果来看数据插入成功了,但是字段score和sex的值为NULL。这是因为在添加新数据时,如果没有为某个字段赋值系统会自动为该字段添加默认值。
字段的默认值等信息可以通过数据表的创建语句进行查看,可以使用SQL语句
SHOW CREATE TABLE 要查看的数据表;
查看创建语句的细节,具体SQL语句及执行结果如下:
mysql> SHOW CREATE TABLE class02;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class02 | CREATE TABLE `class02` (
`id` int NOT NULL,
`name` varchar(12) NOT NULL,
`score` int DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
从数据表的创建语句可以看出,字段score和sex的默认值为NULL, DEFAULT NULL是默认为NULL的意思。因为没有给这两个字段赋值,因此会默认为NULL。
一次插入多条数据
如果需要一次性插入多条数据,像上面那样一条一条的添加会显得太麻烦,还浪费时间。为解决一次插入多条数据,MySQL允许使用一条INSERT语句同时添加多条数据,其语法格式如下:
INSERT [INTO] 表名 VALUES
(值1,值2,......),
(值1,值2,......),
(值1,值2,......),
相信经常看我文章的小伙伴都知道[]里面的代表什么吧,怕有的不知道我再说一遍[]里面的内容是可选项,可以选择使用也可以选择省略。一般来说尽量不要省略,不然到时候人家写的语句你一看很陌生也很尴尬。
下面再为班级表中同时添加多条数据,具体SQL语句及执行结果如下:
mysql> INSERT INTO class02 VALUES
-> (004,'李四',70,'男'),
-> (005,'李李',80,'女'),
-> (006,'李清照',100,'女');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
从执行的结果来看,INSERT语句成功执行。在执行结果中Records: 3表示添加了三条数据,Duplicates: 0表示添加的数据没有重复的, Warnings: 0表示添加数据时没有警告。
在添加多条数据时可以不指定字段列表,只需要保证VALUES后面跟随的值列表依照字段在数据中定义的顺序即可。
接下来通过SELECT查询语句检查是否插入成功:
mysql> SELECT*FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 70 | 男 |
| 5 | 李李 | 80 | 女 |
| 6 | 李清照 | 100 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
从结果来看班级表中添加了三条数据,还是对应的,因此插入数据成功了。
更新数据
更新数据是指对数据表中已经存在的数据进行修改,例如某个部门名称变更了,就需要对数据表中部门名称字段的值进行修改。MySQL中使用UPDATE语句更新表中的数据,其基本的语法格式如下。
UPDATE数据表名SET 字段名1=值1[,字段名2=值2,…][WHERE条件表达式];
在上述语法格式中,字段名用于指定要更新的字段名称,值表示字段更新后的数据。WHERE子句中的条件表达式用于指定更新数据需要满足的条件。
UPDATE语句可以更新数据表中的部分数据和全部数据,下面对这两种情况进行讲解。
1.更新部分数据
更新部分数据是指根据指定条件更新数据表中的某条或某几条数据,需要使用WHERE子句来指定更新数据的条件。
下面通过一个案例演示使用UPDATE语句更新部分数据。
由于系统出现故障,李四的成绩出错了,本来是八十分的,但表上是七十分,因此要更改李四的成绩,那么使用SQL语句的结果是:
先使用SELECT语句查询一下李四的信息
mysql> select*from class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 70 | 男 |
| 5 | 李李 | 80 | 女 |
| 6 | 李清照 | 100 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
下面使用UPDATE语句更新李四的成绩,将score的值更新为80:
mysql> UPDATE class02 SET score=80 WHERE name='李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
我简单的解释一下这个SQL语句的意思,UPDATE是更新的意思,也就是更新哪个数据表;SET是设置的意思,一般来说后面都是字段,这里是设置score这个字段的值为80,也就是李四的成绩为80;WHERE是条件的意思,后面一般都是条件,这里是更改score字段的值,条件为name字段的值为李四;
下面再使用SELECT语句查看李四的信息:
mysql> SELECT*FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 80 | 男 |
| 5 | 李李 | 80 | 女 |
| 6 | 李清照 | 100 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
可以看到李四的成绩由70改为了80,说明我们更新成功了。
接下来,通过一个案例来演示WHERE字句中条件表达式有多条数据满足时更新数据的操作。例如张三和李李同学看到其他同学都是100分,因此发愤图强,也把分数提到了100分,那么数据表中的成绩又要更新了,这次不是更新一条数据,而是两条。
在更新数据前先来查看一下成绩小于100分的同学的数据:
可以看到李四的成绩由70改为了80,说明我们更新成功了。
接下来,通过一个案例来演示WHERE字句中条件表达式有多条数据满足时更新数据的操作。例如张三和李李同学看到其他同学都是100分,因此发愤图强,也把分数提到了100分,那么数据表中的成绩又要更新了,这次不是更新一条数据,而是两条。
在更新数据前先来查看一下成绩小于100分的同学的数据:
可以看到成绩低于100分的就是李四和李李同学,那么下面就要使用UPDATE语句将成绩小于100的涨到100
具体SQL语句及执行结果如下:
mysql> UPDATE class02 SET score=score+20 WHERE id>3 AND id<6;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
从结果来看,UPDATE语句执行成功。为验证数据是否更新成功,使用SELECT语句查看更新后的数据:
mysql> SELECT * FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 100 | 男 |
| 5 | 李李 | 100 | 女 |
| 6 | 李清照 | 100 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
可以看到数据是成功的更新了。
这里的部分还可以理解为公司里的多个部门,一般公司都是由几个部门组成的,由于某个部门表现突出,公司给其部门下的员工涨工资,其语句就是
UPDATE 公司表 SET score=score+20 WHERE 部门=要涨工资的部门;
2.更新全部数据
UPDATE语句中如果没有使用WHERE语句,则会将表中所有数据的指定字段都进行更新。
例如,由于学生们由小学生变为中学生,那么成绩就会由100变为120,那么他们成绩的上限就是120,而他们几个因为成绩优异,在中学还是考取了满分,因此成绩要全部更新。
在更新之前先查看他们的详细信息:
mysql> SELECT * FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 100 | 男 |
| 2 | 杜甫 | 100 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 100 | 男 |
| 5 | 李李 | 100 | 女 |
| 6 | 李清照 | 100 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
一共是有6条数据,现在要使用UPDATE语句把他们的score字段全部加20分,具体SQL语句及执行结果如下:
mysql> UPDATE class02 SET score=score+20;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 6 Changed: 5 Warnings: 0
UPDATE语句执行成功,那么接下来就是使用SELECT语句查看信息了:
mysql> SELECT * FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 120 | 男 |
| 2 | 杜甫 | 120 | 男 |
| 3 | 张三 | NULL | NULL |
| 4 | 李四 | 120 | 男 |
| 5 | 李李 | 120 | 女 |
| 6 | 李清照 | 120 | 女 |
+----+-----------+-------+------+
6 rows in set (0.00 sec)
这里解释一下张三的成绩为什么没有更新,因为NULL不是数值,没有办法进行计算,只有对张三的成绩进行赋值才会更新张三的成绩。
删除数据
删除数据是指对数据表中已经存在的数据进行删除,例如张三因为转学了,其数据在这个班级已经没有意义了,因此要进行删除。删除数据的语法格式如下:
DELETE FROM 数据表名 WHERE 条件表达式;
在上面语法格式中,数据表名是指定要删除的数据表的名称,WHERE子句为可选项,用于指定删除的条件,满足条件就会删除,和上面更新数据的用法一样。之所以说是可选项是因为有的是更新或者删除全部数据,就像上面更新成绩的时候是不需要额外的条件进行范围限制的。
DELETE语句可以删除表中的部分数据和全部数据,下面对这两种情况进行讲解
1.删除部分数据
接下来使用DELETE语句删除张三的数据信息:
mysql> DELETE FROM class02 WHERE name='张三';
Query OK, 1 row affected (0.00 sec)
从上述执行结果来看,DELETE语句执行成功。接下来再使用SELECT语句查看表的信息:
mysql> SELECT * FROM class02;
+----+-----------+-------+------+
| id | name | score | sex |
+----+-----------+-------+------+
| 1 | 李白 | 120 | 男 |
| 2 | 杜甫 | 120 | 男 |
| 4 | 李四 | 120 | 男 |
| 5 | 李李 | 120 | 女 |
| 6 | 李清照 | 120 | 女 |
+----+-----------+-------+------+
5 rows in set (0.00 sec)
可以看到张三的信息已经被删除了,细心的同学可能会发现id不连续了,这是因为我们删除一条数据之后id是不会重新排序的,一般创建好一张完整的表后是不建议进行修改的。
2.删除全部数据
DELETE语句如果没有使用WHERE子句,则会将数据表中的所有数据都删除。
具体SQL语句及执行结果如下:
mysql> DELETE FROM class02;
Query OK, 5 rows affected (0.00 sec)
DELETE语句是成功执行了,接下来使用SELECT语句查看表中是否还有数据:
mysql> SELECT*FROM class02;
Empty set (0.00 sec)
从执行结果来看表中已经没有数据了。
在这里提醒大家一下,DELETE是删除表中的数据,即使里面的数据全部删除了,这个表还是会存在的,其结构也是完整的,只是里面没有内容而已。DROP才是删除数据表的语句,大家不要弄混了。
再扩展一下,在MySQL数据库中还可以使用TRUNCATE关键字删除表中的数据。其语法格式如下:
TRUNCATE [TABLE] 数据表名;
本来是想让你们自己去写的,但还是给你们写了吧,因为这个关键字只能删除全部数据,不能删除部分数据,语法如下:
因为刚把数据表清空,所以我又插入了一条数据:
mysql> INSERT INTO class02 VALUES(1,'张三丰',150,'男');
Query OK, 1 row affected (0.00 sec)
然后我试了一下添加条件,结果是:
mysql> TRUNCATE TABLE class02 WHERE name='张三丰';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE name='张三丰'' at line 1
报错了,语法错误,说明这个关键字不支持条件语句。
只能删除全部数据:
mysql> TRUNCATE TABLE class02;
Query OK, 0 rows affected (0.03 sec)
最后感谢大家的支持。