【MySQL笔记】数据库的查询

数据库的查询

注：文中 [ ...] 代表该部分可以去掉。
理论基础：对表对象的一组关系运算，即选择（selection）、投影（projection）和连接（join）

1、select语句

子语句顺序：
select  [distinct | distinctRow | all]    ----distinct关键字会在结果集中去掉重复的值而只保留一个值，否则有重复值
from
where
group by
having
order by                                            ----order by中加入关键字desc，升序则加入关键字asc,默认是升序
limit
procedure
into outfile 'filename'                        ----格式的select语句将选择的行写入一个文件，文件在服务器上被创建，并且不能是已经存在的，且在服务器主机上
                          还必须有file权限以使用这种select

2、列的选择与指定

2.1 基本查询：

  1. select * from table;
2.2 定义并使用列的别名：（注：where子句不允许有列别名）
  1. select colname1,colname2 as 第二列, colname3 from table;

2.3 更改查询结果数据表：

  1. select cust_name,
  2. 	case 
  3. 		when cust_sex='M' then '男'
  4. 		else '女'
  5. 		end [as 性别]
  6. from table;

2.4 查询+计算：查询cust_i列加上100后的值

  1. select cust_name,cust_sex,cust_id+100
  2. from table;

2.5 聚合函数（aggregation function）：一般和group by子句连用，否则只返回一行结果

注：除count以外的函数都会忽略空值。        
2.5.1常用聚合函数有

2.5.2MySQL的行转列、列转行、连接字符串  concat、concat_ws、group_concat函数

2.5.2.3GROUP_CONCAT函数
1）格式
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC |DESC] [,col_name ...]] [SEPARATOR str_val])
2）例子
  1. SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses 
  2. WHERE student_id=2 
  3. GROUP BY student_id;
结果
+------------   +---------   +
| student_id | courses |
+------------   +---------   +
| 2                 | 3,4,5      |
+------------   +---------   + 
3）修改自定义分隔符，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：
  1. SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses 
  2. WHERE student_id=2 
  3. GROUP BY student_id;
+------------   +---------   +
| student_id | courses |
+------------   +---------   +
| 2                 | 3|||4|||5   |
+------------   +---------   + 
4）排序再连接成字符串，例如按courses_id降序来排：
  1. SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses 
  2. WHERE student_id=2 
  3. GROUP BY student_id;
+------------   +---------   +
| student_id | courses |
+------------   +---------   +
| 2                 | 5,4,3      |
+------------   +---------   + 
2.5.2.4GROUP_CONCAT注意事项
1）int字段的连接陷阱：当你用group_concat的时候请注意，连接起来的字段如果是int型，一定要转换成char再拼起来，否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串，而是byte[]。

解决办法：CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。
CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
这个类型type可以是以下值其中的 一个：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
2）长度陷阱：用了group_concat后，select里如果使用了limit是不起作用的。用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。
 
使用group_concat_max_len系统变量，你可以设置允许的最大长度。程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
SET [SESSION | GLOBAL] group_concat_max_len = val;
若已经设置了最大长度，则结果被截至这个最大长度。在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。

参考链接：
其它实例：

2.6 From子句与连接表

1、交叉连接（笛卡尔积，也称没有连接no join），连接后行数为两表行数相乘，FROM子句中关键字：CROSS JOIN。
2、内连接：系统默认的表连接。FROM子句中关键字：INNER JOIN 或者 JOIN。ON子句设置连接的条件。WHERE子句过滤最终表内容
3、相等连接是内连接的一种，ON子句后用运算符“=”。FROM子句中关键字：INNER JOIN 或者 JOIN
4、不等连接也是内连接的一种，ON子句后用除“=”以外的比较运算符。FROM子句中关键字：INNER JOIN 或者 JOIN
5、自连接也是内连接的一种，将表与自身相连。FROM子句中关键字：INNER JOIN 或者 JOIN
6、自然连接：两张表中名称都相同时，返回笛卡儿积的结果集。FROM子句中关键字：NATURAL JOIN。

7、外连接：连接的两张表分别为基表和参考表，以基表为依据返回满足和不满足条件的记录。
左外连接：左边为基表，可以一对多关系的连接。左表有而右表没有的情况，会将右表的列设为NULL。FROM子句中关键字：LEFT [OUTER] JOIN。
右外连接：同上。

  • 内、外连接的区别：内连接连接的左右表共有的，外连接会以基表为依据连接，返回结果最少也有基表行条数据。

2.7 Where子句与文本匹配

1、比较运算：
1）any 是任意一个
例：查询出01班中，年龄大于 02班所有人的同学
  1. select * from student where 班级='01' and age > all (select age from student where 班级='02');
等同于：
  1. select * from student where 班级='01' and age > (select max(age) from student where 班级='02');

2）all 是所有
例：查询出01班中，年龄大于 02班任意一个的同学
  1. select * from student where 班级='01' and age > any (select age from student where 班级='02');
等同于：
  1. select * from student where 班级='01' and age > (select min(age) from student where 班级='02');

注：“=”是等于运算符；
当两个表达式值中有一个为空值或都为空值时，则返回UNKNOWN；
当使用“<=>”，若两个表达式相等或者都为空值，返回TRUE，若表达式不相等或者其中一个为空值，则返回FALSE。不会返回UNKNOWN！

2、字符串匹配：LIKE语句
常用通配符：
1、%：表示任何字符串，可出现任何次数（包括0次）
2、_：只用匹配单个字符，不是多个也不是0个
3、ESCAPE指定转义字符，来临时改变通配符的作用和意义。如下，ESCAPE指定转义字符‘#’，改变‘_’原有的特殊作用，使其在搜索模式中成为一个普通字符。
  1. select colname from table
  2. where colname like '%#_%' escape '#';
注意事项：
  • 不要过度使用通配符，通配符检索的处理一般会比其他检索花费更长的时间。
  • 尽量不要把通配符用在搜索模式的开始处，否则会使检索更慢。
3、文本匹配：
正则表达式是用来匹配文本的特殊串或字符集合，是文本匹配运算中的一种搜索模式。使用关键字REGEXP指定正则表达式。使用正则表达式进行文本匹配需要注意大小写。
1）基本字符匹配
  1. select colname from table
  2. where colname regexp '市';
等同于字符串匹配：
  1. select colname from table
  2. where colname like '%市%';
2）选择匹配：”|“
3）范围匹配”[a-d]
4）特殊字符匹配："\\"+特殊字符
5）字符类匹配："[:upper:]"表示任意大写字母
6）重复匹配：p73

7）定位符匹配：
4、判定范围
1）BETWEEN AND
  1. select * from tabel
  2. where colname between 1 and 3;
2）IN 和 NOT IN
  1. select * from table
  2. where colname in (1,2.3);
5、子查询
多层查询时，MySQL会从内层向外向上移动到外层（主）查询，在这个过程中每个查询结果集都赋值给包围它的父查询，接着父查询也会把结果集赋值给它的父查询。
1）结合关键字 IN 和 NOT IN 使用的子查询
2）结合比较运算符使用的子查询
    比较运算符以及{ALL|SOME|ANY}(SOME和ANY同意)
3）结合关键字EXIST使用的子查询，子查询返回逻辑值，若为True，父查询才进行查询。
    用于判断子查询的结果是否为空
6、UNION语句与联合查询
同时执行多个查询，并将结构作为单个查询结果集返回。
1）UNION：去除重复值
2）UNION ALL：不去除重复值

2.8 Group by 和 Having子句

1）GROUP BY：将结果集中的数据行根据选择列的值进行逻辑分组

ASC|DESC：排序
WITH ROLLUP：计算各分组的汇总行
2）HAVING：查询结果集中，过滤结果，必须跟在Group by后面！
  • Where 和 Having的区别：Where子句过滤数据行，是在数据分组之前进行的过滤，Having子句过滤分组，且可以包含聚合函数，是在分组进行后过滤。

2.9其它子句

1）Order by子句：排序
  1. select colname from table
  2. order by colname1 DESC, colname2 ASC;
2）Limit子句：限制select返回行数：limit [offset] row_count
如果给定1个参数，它指出返回行的最大数目；如果给定2个参数，第一个指定要返回的第一行的偏移量，第二个指定返回行的最大数目，初始行的偏移量是0。

如下返回从第5位用户（偏移4位）开始的3位客户
  1. select cust_id from table
  2. order by cust_id
  3. limit 4,3;

参考资料：

21分钟 MySQL 入门教程：http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html#c2







posted @ 2016-08-28 11:46  萱草yy  阅读(508)  评论(0)  编辑  收藏  举报