(My)SQL DML语句

DML操作是指对数据库中表记录的操作，主要包括表记录的插入(insert)，更新(update)，删除(delete)，和查询(select)，是开发人员日常使用的最平凡的操作。

1:插入记录：

表创建好以后就可以往里插入记录了。

例如:向表emp中插入以下记录:ename为zzx1,hiredata为2000-01-01,sal为2000,deptno为1:

其中ename,hiredata等为字段名。

mysql> insert into emp (ename,hiredata,sal,deptno) values('zzx1','2000-01-01','2000',1);

也可以不用指定字段名，但是values后面的顺序应该和字段的排列顺序一致。

mysql> insert into emp values('zzx1','2000-01-01','2000',1);

含可空字段、非空但是含有默认值的字段、自增字段，可以不用在insert后的字段列表里面出现，values后面只写对应字段名称的value。这些没写的字段可以自动摄这位NULL，默认值，自增的下一个数字。

例如只对表中的ename和sal字段显示插入值:

mysql> insert into emp (ename,sal) values('dony',1000);

在MySQL中，insert语句还有一个很好的特性，可以一次性的插入多条记录。

例如，对表dept一次性插入2条记录：
记录之间用","分隔

mysql> insert into dept values (5,'dept5'),(6,'dept6');

2:更新记录:

表里的记录可以通过update命令进行更改。

例如，将表中emp中ename为"lisa"的薪水(sal)从3000更新为4000:

mysql> update emp set sal=4000 where ename='lisa'; 

在MySQL中，update命令可以同时更新多个表中数据：

例如，同时更新表emp中的字段sal和表dept中的字段deptname:

mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

注意:多表更新的语法更多地用在了根据一个表的字段来更新另外一个表的字段。

3:删除记录:

如果记录不需要了可以用delete命令删除:

例如，在emp中将ename为"dony"的记录全部删除:

mysql> delete from emp where ename='dony';

在MySQL中可以一次删除多个表的数据:

例如，同时删除表emp和dept中deptno为3的记录:

如果from后面的表名用别名，则delete后面也要用相应的别名，否则会提示语法错误。

mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

注意:不管是单表还是多表，不加where条件将会把表中的所有记录删除，所以操作时一定要小心。

4:查询记录:

数据插入到数据库中后，就可以用SELECT命令进行各种各样的查询，使得输出的结果符合用户的要求。SELECT语句很复杂，这里只介绍最基本的语法:

查询最简单的方式是将记录全部选出。在下面的例子中，将emp中的记录全部查询出来:

mysql> select * from emp;

其中"*"号表示将所有的记录都删选出来，也可以用逗号分割的所有字段来代替，例如，以下两个查询是等价的:

mysql> select * from emp;

mysql> select ename,hiredata,sal,deptno from emp;

"*"的好处是当需要查询所有字段信息时很方便，但是只查询部分字段的时候，必须要将字段一个一个列出来。

1)查询不重复的记录:
有时需要将表中的记录去掉重复后显示出来，可以用distinct关键字来实现。

mysql> select distinct name from test;

2)条件查询:

在很多情况下，用户并不需要查询所有的记录，而只是需要根据限定条件来查询一部分数据，用where关键字可以实现这样的操作。

例如，需要查询人名为’lisa’的人:

mysql> select * from test where name='lisa';

显而易见，结果集中将符合条件的记录删选出来。在where后除了=,还可以使用>,<,>=,<=,!=等比较运算符。多个条件之间还可以使用or、and等逻辑运算符进行多条件联合查询。

以下是使用多字段查询的例子:

mysql> select * from emp where deptno=1 and sal<3000;

3)排序和限制:
我们经常会有这样的需求，取出按照某个字段进行排序后的记录结果集，这就用到了数据库的排序操作，用关键字ORDER BY来实现。

语法:
SELECT FROM tablename [WHERE CONDITION] [ORDER BY field1[DESC|ASC],field2[DESC|ASC]]
DESC:降序排列。
ASC:升序排列。
如果不写这些关键字则默认升序排列。

mysql> select * from test order by score desc;

ORDER BY后面可以跟多个不同的排序字段，并且每个字段可以有不同的排序顺序。如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。如果只有一个字段，那么这些字段相同的记录将无序排列。

mysql> select * from emp order by deptno,sal desc;

对于排序后的记录，如果只希望显示一部分，而不是全部，这时就可以使用LIMIT关键字来实现，limit经常和order by一起配合使用来继续记录的分页显示。LIMIT语法如下:
SELECT…[LIMIT offset_start,row_count]
其中offset_start表示记录的起始偏移量,row_count表示显示的行数。
在默认的情况下，起始偏移量为0，只需要写记录的行数就可以了。

mysql> select * from emp order by sal limit 3;

若要显示emp表中按照sal排序后从第二条记录开始的3条记录，可以使用如下命令:

mysql> select * from emp order by sal limit 1,3;

注意:limit属于MySQL扩展SQL92后的语法，在其他数据库上并不能通用。

4)聚合:

很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这时就要用到SQL的聚合操作。

聚合操作的语法如下:
SELECT [field1,field2,field3…] fun_name FROM tablename [WHERE where_conditon] [GROUP BY field1,field2…[WITH ROLLUP]] [HAVING where_condition]

fun_name:表示要做的聚合操作,也就是聚合函数，常用的有sum(求和)、count(*)(记录数)、max(最大值)、min(最小数)。

GROUP BY：在其之后跟着要进行分类聚合的字段。

WITH ROLLUP:为可选语法，表明是否对分类聚合后的结果进行再汇总。

HAVING:表示对分类后的结果再进行条件过滤。
注意: having和where的区别在于having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where先过滤记录，这样因为结果集减少，将对聚合的效率大大提高，最后再根据逻辑看是否用having再进行过滤。

例如，要在emp表中统计总人数:

mysql> select count(1) from emp;

在此基础上，若要统计各个部门的人数:

mysql> select deptno, count(1) from emp group by deptno;

若既要统计各部门人数，又要统计总人数:

mysql> select deptno, count(1) from emp group by deptno with rollup;

统计人数大于1的部门:

mysql> select deptno, count(1) from emp group by deptno having count(1)>1;

统计公司所有员工的薪水总额，最高和最低薪水:

mysql> select sum(sal),max(sal),min(sal) from emp;

5)表连接:

当需要显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上，表连接分为内连接和外连接。
内连接:仅选出两张表中互相匹配的记录。
外连接:会选出其他不匹配的记录。

例如，按照test和test2中name值来显示:gender,score,mood,class:

mysql> select gender,score,mood,class from test,test2 where test.name=test2.name;

外连接又分为左连接和右连接:。
左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

左连接例子:

mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;

显而易见，emp中所有的用户名都被列举了出来，即使其中的dony在右边表中没有与之匹配的部门名称(NULL)。

以上例子改为右连接:

mysql> select ename,deptname from dept right join emp on dept.deptno=emp.deptno;

6)子查询:
某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候就要用到子查询。用于子查询的关键字主要包括in、not in、=、!=、exists、not exits。

例如，从emp表中查询出所有部门在dept表中的所有记录:

mysql> select * from emp where deptno in(select deptno from dept);

某些情况下，子查询能够转换为表连接：

mysql> select emp.* from emp,dept where emp.deptno=dept.deptno;

可以发现此内连接显示的记录与上例子查询相同。

注意: 子查询和表连接的转换主要应用在两个方面。
1:MySQL 4.1以前的版本不支持子查询，需要用表连接来实现子查询的功能。
2:表连接在很多情况下用于优化子查询。

7)记录联合:
我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候，就需要用到union和unionall关键字来实现这样的功能。具体语法如下:
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
UNION|UNION ALL
SELECT * FROM tn

UNIONALL:把结果集直接合并在一起。
UNION:将UNION ALL后的结果进行一次去重在显示。

例如:将emp和dept表中的部门编号的集合显示出来:

mysql> select deptno from emp
    -> union all
    -> selct deptno from dept;

若要去重:

mysql> select deptno from emp
    -> union
    -> selct deptno from dept;