SQL简介：数据操纵语言DML

点击上面“天码营”，加入我们，快速成长~

「内容简介」数据操纵语言 （Data Manipulation Language, DML）包含了数据库数据的增删改查操作，也是我们经常会见到的'CRUD'操作，其主要包括INSERT、SELECT、UPDATE和DELETE四条命令。

DML

数据操纵语言 （Data Manipulation Language, DML）包含了数据库数据的增删改查操作，也是我们经常会见到的'CRUD'操作，其主要包括INSERT、SELECT、UPDATE和DELETE四条命令。

插入数据

我们已经学习了如何通过HTML为用户展示一个注册的表单，并且通过Servlet处理该表单提交过来的数据。实际上，当我们拿到表单数据之后，我们最终需要通过SQL的插入语句对数据库进行操作，将表单数据存储到数据库中。

我们可以一次插入一条数据，如：

insert into `user`(username, password, avatar, title, email, description) values('Ricky', 'pwd', 'avatar', 'Ricky', '[e-mail]', 'Ricky's blog');

也可以一次插入多条数据：

insert into `user`(username, password, avatar, title, email, description) values('Harttle', 'pwd', 'avatar', 'Harttle', '[e-mail]', 'Harttle's blog'), ('Cliff', 'pwd', 'avatar', 'Cliff', '[e-mail]', 'Cliff's blog');

插入成功后，就代表着该用户已经注册成功了，在这里，我们先后注册了3个用户，加上之前插入的一条记录，我们的user表一共有4条记录。

查询数据

获取用户列表

注册成功后，让我们来看看我们的用户列表：

mysql> SELECT * FROM `user`; ---- ---------- ---------- -------- --------- ------------------------ ---------------- ------------- | id | username | password | avatar | title   | email                  | description    | createdTime | ---- ---------- ---------- -------- --------- ------------------------ ---------------- ------------- |  1 | David    | pwd      | avatar | David   | [e-mail]   | David's blog   | NULL        ||  2 | Ricky    | pwd      | avatar | Ricky   | [e-mail]   | Ricky's blog   | NULL        ||  3 | Harttle  | pwd      | avatar | Harttle | [e-mail] | Harttle's blog | NULL        ||  4 | Cliff    | pwd      | avatar | Cliff   | [e-mail]   | Cliff's blog   | NULL        | ---- ---------- ---------- -------- --------- ------------------------ ---------------- ------------- 4 rows in set (0.00 sec)

登录

我们的登录操作实际上也是查询操作，当用户名密码匹配时，我们的查询结果应为一条数据，则表示登录成功。

mysql> SELECT * FROM `user` where username = 'David' and password = 'pwd'; ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- | id | username | password | avatar | title | email                | description  | createdTime | ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- |  1 | David    | pwd      | avatar | David | [e-mail] | David's blog | NULL        | ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- 1 row in set (0.01 sec)

当用户名密码不匹配时，查询结果为空，这时候应用就需要处理登录失败的情况。

mysql> SELECT * FROM `user` where username = 'David' and password = 'tianmaying';Empty set (0.00 sec)

联合查询

很多情况下，我们会涉及到多表查询的情况，例如我要通过一条SQL语句查询出某篇博客的作者信息。我们可以通过两条SELECT语句来实现。加入博客的id为1，则：

mysql> select * from post where id = 1; ---- ------- --------- --------- --------------------- | id | title | content | creator | createdTime         | ---- ------- --------- --------- --------------------- |  1 | test  | test    |       1 | 2016-01-01 00:00:00 | ---- ------- --------- --------- --------------------- 1 row in set (0.00 sec)mysql> select * from user where id = 1; ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- | id | username | password | avatar | title | email                | description  | createdTime | ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- |  1 | David    | pwd      | avatar | David | [e-mail] | David's blog | NULL        | ---- ---------- ---------- -------- ------- ---------------------- -------------- ------------- 1 row in set (0.00 sec)

连续两次SQL操作过于麻烦，需要根据第一次结果creator字段的值才能拼出第二次的SQL语句，还增加了一次与MySQL数据库连接，影响效率。

接下来，我们就通过联表查询通过一条SQL语句获取用户David的登录状态：

mysql> select * from post left join user on post.creator = user.id where post.id  =  1; ---- ------- --------- --------- --------------------- ------ ---------- ---------- -------- ------- ---------------------- -------------- ------------- | id | title | content | creator | createdTime         | id   | username | password | avatar | title | email                | description  | createdTime | ---- ------- --------- --------- --------------------- ------ ---------- ---------- -------- ------- ---------------------- -------------- ------------- |  1 | test  | test    |       1 | 2016-01-01 00:00:00 |    1 | David    | pwd      | avatar | David | [e-mail] | David's blog | NULL        | ---- ------- --------- --------- --------------------- ------ ---------- ---------- -------- ------- ---------------------- -------------- ------------- 1 row in set (0.00 sec)

我们可以看到，我们通过post left join user on post.creator = user.id将post表和user表连接了起来，其中LEFT JOIN表示两表的连接方式为左联接，即以左边的表post表为基础，post表的记录将全部显示出来，而user表则只显示符合条件的记录。相对LEFT JOIN，还有另一种联接方式为RIGHT JOIN。大家可以尝试以下两个语句，理解一下两种联接方式的区别：

SELECT * FROM `post` LEFT JOIN `user` on post.creator = user.id ;

SELECT * FROM `post` RIGHT JOIN `user` on post.creator = user.id ;

修改数据

我们会经常遇到修改密码的需求，修改密码实际上就是修改数据库中的某一条数据。通常，我们会用主键id作为筛选条件，修改某一条记录的数据。

UPDATE `user` set password = 'tianmaying' where id = 1;

修改完成后，让我们重复之前的登录过程，我们发现登录使用密码passwod登录失败，而使用密码tianmaying登录成功：

mysql> SELECT * FROM `user` where username = 'David' and password = 'pwd';Empty set (0.00 sec)

mysql> SELECT * FROM `user` where username = 'David' and password = 'tianmaying'; ---- ---------- ------------ -------- ------- ---------------------- -------------- ------------- | id | username | password   | avatar | title | email                | description  | createdTime | ---- ---------- ------------ -------- ------- ---------------------- -------------- ------------- |  1 | David    | tianmaying | avatar | David | [e-mail] | David's blog | NULL        | ---- ---------- ------------ -------- ------- ---------------------- -------------- ------------- 1 row in set (0.01 sec)

删除数据

现在让我们来学习如何删除数据，实际上我们不应该去删除User表中的数据，但是为了练习SQL语句，我们还是将id为4的用户删除：

mysql> DELETE FROM `user` WHERE id = 4;Query OK, 1 row affected (0.06 sec)

删除之后，我们看看我们的用户列表，发现已经没有Cliff这个用户了：

mysql> SELECT * FROM `user`; ---- ---------- ------------ -------- --------- ------------------------ ---------------- ------------- | id | username | password   | avatar | title   | email                  | description    | createdTime | ---- ---------- ------------ -------- --------- ------------------------ ---------------- ------------- |  1 | David    | tianmaying | avatar | David   | [e-mail]   | David's blog   | NULL        ||  2 | Ricky    | pwd        | avatar | Ricky   | [e-mail]   | Ricky's blog   | NULL        ||  3 | Harttle  | pwd        | avatar | Harttle | [e-mail] | Harttle's blog | NULL        | ---- ---------- ------------ -------- --------- ------------------------ ---------------- ------------- 3 rows in set (0.00 sec)

最后，我们尝试删除一下用户David，由于我们添加了外键约束<Post.creator, User.id>，并在post表中添加了与用户David相关的数据，我们最终看到MySQL抛出了错误，这样就保证我们不会在误操作中删除有用的数据：

mysql> DELETE FROM `user` WHERE id = 1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`tianmayingblog`.`post`, CONSTRAINT `post_ibfk_1` FOREIGN KEY (`creator`) REFERENCES `user` (`id`))