Mysql之查询

单表查询

对表的查询语句有:

select [all|distinct] <目标列表达式>...
from <表名或视图名> …
[ where <条件表达式> ]
[ group by <列名1> ]
[ having<条件表达式> ]
[ order by <列名2> [ ASC|DESC ] ]
[ limit [startIndex,]length ];

其中，

select子句：指定要显示的属性列
distinct : 则可以去掉重复的行（all不去重）
from子句：指定查询对象(基本表或视图)
where 子句：筛选出满足条件的记录
group by子句：对查询结果按某个字段（或者多个字段）进行分组，该属性列值相等的元组为一个组。
having短语：筛选出只有满足指定条件的组
ORDER BY子句：对查询结果表按指定列值的升序或降序排序
limit可以对需要返回记录的数量进行具体限定。

group by（关键字”每”）

group by子句将结果集分组，常与聚合函数连用，聚合函数对每个组内的数据进行统计。

where、having

where 筛选出满足条件的记录（行），作用于元组
having 筛选满足条件的组，作用于基本表或视图

where、group by、having

即先用where子句过滤不符合条件的数据记录，接着用group by子句对余下的数据记录进行分类汇总，最后再用having子句排除不符合条件的组。

group_concat()和concat()

• group_concat()函数将集合中的字符串连接起来，此时与字符串连接函数concat()的功能相似。
• group_concat()函数还可以按照分组字段，将分组字段（NULL值除外）使用逗号连接起来。
  e.g.
  select class_name 班级名,
group_concat(student_name) 学生名单,
concat(student_name) 部分名单
from classes left join student
on student.class_no = classes.class_no
group by classes.class_no;
  以上语句表示：将学生表中的学生按班级统计，并展示学生名单和部分学生

group by和with rollup

with rollup可以在每个分组后加上一条汇总记录

GROUP BY子句可以按多列分组，表示要基于这些列的唯一组合来进行分组

聚合函数

聚合函数（集函数），常见的集函数有：count()统计结果集中记录的行数、sum()、avg()、max()、min()…

分组中的集函数与不分组中的集函数（聚合函数）

简单区分一下：

• 分组中：select +分组字段（+集函数）from...where...
• 不分组中：集函数只返回一个值
  （感觉没有总结好，有点难表达…）

tips

• 聚合函数属于系统内置函数之一，能够对一组值执行计算并返回单一的值
• 除count(*)函数外，其他聚合函数忽略空值
• 聚合函数不能出现在WHERE子句中
• 聚合函数的计算范围是整张表中满足where条件的所有记录

order by

1. order by对结果集进行排序
2. asc（默认升序，可省略）、desc 降序

limit

查询前几行或某几行记录
limit [startIndex,]length
3. 只有一个参数length时（相当于startIndex为0），表示查询前length行记录（查询前几行）
4. 有两个参数，startIndex表示从索引为startIndex开始，检索length条记录（查询某几行）

条件转化：case

将结果以另外一种形式显示出来可以使用case子句指定数据的转换
case函数分为简单case函数和搜索case函数

简单case函数

简单CASE函数只做数据匹配，不进行比较运算和逻辑运算

CASE 列名
WHEN 值1 THEN 转换值1
WHEN 值2 THEN 转换值2
ELSE 转换值n
END

e.g 查询教师姓名、职称以及将职称以高级、中级和初级的形式显示
SELECT teacher_name, profession,
CASE profession
WHEN '助教' THEN '初级'
WHEN '讲师' THEN '中级'
ELSE '高级'
END aa
FROM teacher


搜索case函数

搜索CASE函数可以完成更为复杂的数据转换，与简单CASE函数区别：
1. CASE后无列名
2. WHEN 后是布尔表达式
CASE
WHEN 布尔表达式1 THEN 转换值1
WHEN 布尔表达式2 THEN 转换值2
ELSE 转换值n
END

e.g 查询choose表将成绩以“优”、“良”、“及格”和“不及格”的方式显示
SELECT student_no,course_no,
CASE
WHEN score >=90 THEN '优'
WHEN score BETWEEN 80 AND 89 THEN '良'
WHEN score BETWEEN 60 AND 79 THEN '及格'
WHEN score <60 THEN '不及格'
ELSE '成绩错误'
END as 等级
FROM choose;


一些小tips

• Mysql中尽量都使用单引号，不使用双引号
• null与null做所有比较，结果都为NULL，null is null为true（is null）
• as关键字：
  
  • 取别名（给字段名或表达式取别名，可省略）
  • 创建视图create view view_name as ...
  • ..
• substring（源字符串/字段名，子串起始位置，子串长度）
  e.g. //查询student表中姓张或姓田的学生的信息
  select * from student
where substring(student_name,1,1) in ('张','田');
• in关键字可以指定一个值表（集合），值表中列出所有可能的值，当表达式与值表中的任一个值匹配时，即返回TRUE，否则返回FALSE
• in可用or替换
  e.g. //查询teacher表中教师号为001，002，003的教师信息
  select * from teacher
where teacher_no in ('001','002','003')
• 所有’=’都可以换成in，但in不能随便换成’=’
• 通配符：
  
  • %：匹配零或多个字符
  • _：匹配任意一个字符
• like 模糊查询
• 空值查询：IS NULL / IS NOT NULL
• 优先级：not 、and、or

连接查询（多表查询）

同时涉及多个表的查询称为连接查询

内连接

在内连接中，参与连接的表是平等关系，会从两个表中提取满足条件的记录并组合成新的记录

内连接的基本格式：

• 格式1：
  SELECT 列名列表
  from 表1
  [连接类型] join 表2 on 表1和表2的连接条件
  [连接类型] join 表3 on 表2和表3的连接条件
• 格式2：
  SELECT 列名列表
  FROM 表1 ，表2，表3，…
  where 连接条件 and 连接条件 …
  ##### 等值/非等值连接、自然连接、自连接
  等值/非等值连接:比较运算符为 =/不是= 的连接
  自然连接：在查询结果中去掉重复列的等值连接
  自连接：一个表与其自己进行连接。

自连接

• 查询时需要两次用到同一张表
• 需要给表起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀
  e.g.查询既选修1，又选修了2课程的学生学号
  select c2.student_no
from choose c1,choose c2
where c1.student_no = c2.student_no
and c1.course_no = '1'
and c2.course_no = '2'


外连接(outer join)

左外连接、右外连接、完全连接

左外连接（left [outer] join）：结果表中除了包括满足连
接条件的行外，还包括左表的所有行（不满足连接条件） ；
右外连接（right [outer] join）：结果表中除了包括满足连
接条件的行外，还包括右表的所有行（不满足连接条件） ；
完全连接（full [outer] join）：结果表中除了包括满
足连接条件的行外，还包括两个表的所有行（不满足连接条
件）
外连接格式：
SELECT 列名列表
FROM 表A LEFT/RIGHT/FULL [OUTER] JOIN 表B
ON 表A.字段=表B.字段

交叉连接(cross join)

交叉连接返回被连接的两个表所有数据行的笛卡尔积 。用左表中的每一行与右表中的每一行进行连接。因此，结果集中的行数是左表的行数乘以右表的行数

注意：交叉连接没有实际意思，通常用于测试所有可能的情况

联合查询(UNION)

将两个或多个表的查询结果进行合并显示就是联合查询，使用的关键字是UNION

JOIN将信息水平连接(添加更多列)，而UNION将信息垂直连接(添加更多行)

• 所有查询语句中的列数和列的顺序必须相同
• 查询语句中的列的数据类型必须兼容
• union all表示合并的结果中包括所有行，不去除重复行，直接合并两个结果集，效率高于union
• union则在合并的结果去除重复行

表别名

• 多表查询时，任何子句中引用同名属性时，都必须加表名前缀
• 为了增加书写遍历及代码的可读性，可以使用表的别名
• 一旦使用了别名代替某个表名，则在查询时必须用表的别名，不能再用表的原名

嵌套查询

一个select-from-where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询，其中，外层查询为父查询，内层查询为子查询

特点

• 先子查询再父查询，是由里向外逐层处理
• 子查询的查询结果是作为父查询的查询条件
• 子查询的限制：
• 不能使用ORDER BY子句
• 子查询要加括号
• SQL语言允许多层嵌套查询
• 有些嵌套查询可以用连接运算替代

分为不相关子查询、相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

• 内层查询查什么 外层查询的条件就取什么（通常是外码主码相关）

相关子查询：子查询的查询条件依赖于父查询

• 执行过程：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，执行内层查询后将查询结果代入外层查询进行查询，就能得到结果；
  然后再取外层查询的下一个元组；
  重复这一过程，直至外层表全部检查完为止。

引出子查询的谓语

子查询的结果可以是单个属性值，也可以是一个集合。
子查询只返回一个值时:用比较符或in/not in;
子查询返回多个值时:（单值、多值、逻辑值）

• 单值：(in或not in或比较运算符) ：是或不是集合中的一个值
• 多值：
• (<>，>，< )all: 指‘不等于’‘大于’或‘小于’集合中的所有值。
• (=，>，< )any: 指至少‘等于’‘大于’或‘小于’集合中的一个值。
• 逻辑值：子查询返回逻辑值时:用exists/not exists（相关子查询）

引出子查询的谓语

EXISTS谓词

• 存在量词
• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”
• 若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值
• 由EXISTS引出的子查询，其目标列表达式通常都用*（子查询select后面的列）
• 由EXISTS引出的查询是相关子查询
• 在子查询的where语句中将内外层的查询相关属性作为查询条件
• not exists与exists相反
• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换