有关数据的操作

数据的查询操作

1>查询结果不重复:
select distinct 字段名 from 表名

mysql> select *  from emp ;+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                || sdkf     | 06 |   NULL | NULL                |+----------+----+--------+---------------------+5 rows in set (0.00 sec)mysql> select distinct salary from emp;+--------+| salary |+--------+|   2000 ||   3000 ||   2500 ||   NULL |+--------+4 rows in set (0.00 sec)

提醒：distinct不能部分使用。
2>多列排序：

mysql> select * from emp order  by salary,username;+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| sdkf     | 06 |   NULL | NULL                || sdkf     | 05 |   2000 | NULL                || zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 |+----------+----+--------+---------------------+5 rows in set (0.00 sec)

提醒：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列排序。如果第一列数据中所有值都是唯一的，将不会再对第二列排序。

3>在group by 子句中使用 with rollup可以统计记录的数量。

4>使用LIMIT限制查询的数量：LIMIT [位置偏移量,] 行数
（位置偏移量是个可选参数，所以如果没有位置偏移量，默认查询结果是前几行）

mysql> select * from emp limit 1,2;+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 |+----------+----+--------+---------------------+2 rows in set (0.00 sec)

5>count()函数

使用方法有两种：
count(*)计算表中总的行数
count(字段名)计算指定列下总的行数，计算时将忽略空的行。

mysql> select * from emp;+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                || sdkf     | 06 |   NULL | NULL                |+----------+----+--------+---------------------+5 rows in set (0.00 sec)mysql> select count(*) from emp;+----------+| count(*) |+----------+|        5 |+----------+1 row in set (0.05 sec)mysql> select count(salary) from emp;+---------------+| count(salary) |+---------------+|             4 |+---------------+

6>连接查询

INNER JOIN (内连接)：只有满足条件的记录才能出现在结果关系中。

LEFT JOIN (左连接)：返回包括左表中所有记录和右表字段相等的记录。

RIGHT JOIN (右连接)：返回包括右表中所有记录和左表字段相等的记录

7>子查询
some、any二者为同义词，表示满足任一条件,他们允许创建一个表达式对子查询返回值列表进行比较，只要满足内层子查询的任何一个比较条件，就返回一个结果作为外层查询条件。

mysql>  select * from emp where salary < any(select salary from emp where salary=3000);+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                |+----------+----+--------+---------------------+3 rows in set (0.05 sec)

8>EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；否则EXISTS的结果为false,将不进行查询。

mysql> select * from emp  where exists (select id from emp where id='06' and salary is not null );Empty set (0.06 sec)mysql> select * from emp  where exists (select id from emp where id='06' and salary is null );+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                || sdkf     | 06 |   NULL | NULL                |+----------+----+--------+---------------------+5 rows in set (0.00 sec)

9>合并查询结果：UNION 和UNION ALL

二者区别：UNION ALL 的功能是不删除重复行；而union删除重复记录。

mysql> select * from emp where salary>=2500 union all    -> select * from emp where birthday is not null;+---    -------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 |+----------+----+--------+---------------------+5 rows in set (0.06 sec)

10>给表和字段取别名

mysql> select username as '用户名',id ID,salary ,birthday from emp e;+----------+----+--------+---------------------+| 用户名   | ID | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                || sdkf     | 06 |   NULL | NULL                |+----------+----+--------+---------------------+5 rows in set (0.00 sec)mysql> select username as '用户名',id ID,salary ,birthday from emp as e;+----------+----+--------+---------------------+| 用户名   | ID | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 || lisi     | 02 |   3000 | 2011-10-20 00:00:00 || wangwu   | 03 |   2500 | 2012-10-20 00:00:00 || sdkf     | 05 |   2000 | NULL                || sdkf     | 06 |   NULL | NULL                |+----------+----+--------+---------------------+5 rows in set (0.00 sec)

11>使用正则表达式查询

常用字符匹配：
^：匹配文本的开始字符
$:匹配文本的结束字符
.:匹配任何单个字符
*:匹配零个或多个它前面的字符
+:匹配一个或多个它前面的字符
<字符串>:匹配包含指定的字符串的文本
字符串{n,}匹配前面的字符串至少n次
字符串{n,m}匹配前面的字符串至少n次，至多m次。如果n为0,此参数为可选参数

mysql> select * from emp where username regexp '[a-w]{7,}';+----------+----+--------+---------------------+| username | id | salary | birthday            |+----------+----+--------+---------------------+| zhangsan | 01 |   2000 | 2010-10-20 00:00:00 |+----------+----+--------+---------------------+1 row in set (0.00 sec)

数据的增删改操作

1>如果想删除表中所有记录，

》DELETE FROM 表名;(删除表中所有记录)

》TRUNCATE TABLE 表名;(直接删除表)