MySQL

查询(DQL)

分组函数(多行处理函数)

• 特点：
  输入多行，输出一行。
• 5个函数：
  count 计数
  sum 求和
  avg 求平均值
  max 求最大值
  min 求最小值

注意

1. 分组函数在使用的时候必须先分组，然后再使用。
2. 如果你没有对数据进行分组，则默认整表为一组。
3. 分组函数自动忽略NULL，不需要提前对NULL进行手动处理。
4. count(具体字段)：表示统计**该字段下(该列)**所有不为NULL的元素总数。
   count(*)：统计表当中的总行数。(只要某行中有一列数据不为NULL，则这行数据有效)
5. 分组函数不能直接使用在where子句中(因为分组函数在使用时必须先分组后使用；where执行的时候，还没有分组；所以不能在where后出现分组函数)。
6. 所有的分组函数可以组合起来一起用。

分组查询(非常重要)

什么是分组查询

• 在实际的应用中，可能有这样的需求，需要先分组，再对每一组的数据进 行操作。这个时候我们就要使用分组查询。
• 格式：select … from… group by…

执行顺序

• 关键字执行顺序：
  1. from
  2. where
  3. group by
  4. select
  5. order by

注意

1. 当select语句中有group by语句的话，select后面只能跟：**参加分组的字段，以及分组函数。**其他一律不能跟 。
2. 两个字段联合分组：两个字段当作一个字段看。

having

• having可以对分组后的数据进行过滤，而且having只能跟在group by后使用。

效率

• 先过滤(用where)再分组(用group by)效率更高。
• where和having，优先使用where，除非where完成不了。

distinct关键字

• 把查询结果去除重复记录
  1. 注意：原表数据不会被修改，只是查询结果去重。
  2. 去重需要一个关键字：distinct。
• distinct只能出现在所有字段的最前方。
• distinct出现在多个字段之前，表示多个字段联合起来去重。

连接查询

• 从一张表中单独查询称为单表查询；多张表联合起来查询称为连接查询。

• 根据表的连接方式分类：
  内连接：

  • 等值连接
  • 非等值连接
  • 自连接

  外连接：

  • 左外连接（左连接）
  • 右外连接（右连接）

  全连接

笛卡尔乘积

• 当两张表进行连接查询，没有任何条件限制时，最终查询的结果是两张表条数的乘积，这种现象被称为笛卡尔积现象。
• 如何避免笛卡尔积现象？
  连接时加条件(where关键字)，满足这个条件的记录被筛选出来。
  注意：加条件后只是最终查询的结果减少了，匹配的次数并没有减少。
  通过笛卡尔积现象可以得出：表的连接次数越多效率越低，应尽量避免标的连接。

内连接(inner join)

• 特点：完成能够匹配上这个条件的数据查询出来。

等值连接

• SQL99语法：select…from…(表1) join…(表2) on…(连接条件) where…(筛选条件)

非等值连接

• 连接条件不是一个等值条件。

自连接

• 技巧：一张表看作两张表。

外连接(outer join)

• 特点：任何一个右连接都有左连接的写法；任何一个左连接都有右连接的写法。
• 外连接的查询结果条数一定 >= 内连接的查询结果条数。

右外连接

• 使用right关键字，格式：(表1) right join (表2)；right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据查询出来，捎带着关联查询左边的表。

左外连接

• 与右外连接原理相同，只是关键字换成left，主表为左表。

三张表、四张表怎么连接？

• 语法：
  select … from a join b
  on a和b的连接条件
  join c on a和c的连接条件
  join d on a和d的连接条件

子查询

• 什么是子查询？
  select语句中嵌套select语句，被嵌套的select语句称为子查询。
• 子查询可以出现在哪里？
  select … (select) from … (select) where… (select)

where子句中的子查询

from子句中的子查询

• 技巧：from后面的子查询，可以将子查询的结果看作一张临时表 。

select后面出现的子查询

• 注意：对于select后面的子查询来说，这个子查询只能返回1条结果，多于1条就报错！

union合并查询结果集

• union的效率相较于表连接要高一些，union可以减少匹配的次数(union把乘法变成了加法)，同时完成两个结果集的拼接。
• 结果集合并时要求两个结果集的列数相同并且列与列的数据类型要一致。

limit(分页查询)

• limit是将查询结果集的一部分取出来，通常使用在分页查询中。
• 分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差，可以一页一页翻页看。

怎么使用limit？

• 完整用法：limit startIndex, length
  startIndex是起始下标，length是长度。
  起始下标从0开始。
• 缺省用法：limit 5；这是取前5.
• 注意：在MySQL中limit是在order by之后执行的。

通用分页

每页显示pageSize条记录
第pageNO页：limit (pageNo - 1) * pageSize , pageSize
用户提交过来一个页码以及每页显示的条数。

DQL总结

表的创建(DDL)

建表

建表的语法格式:

• DDL包括：create, drop, alter;
• 建表语法格式:
  create table 表名(字段名1 数据类型 default 默认值, 字段名2 数据类型)；
• 表名：建议以t_或者tbl_开始，可读性强。
• default用于设定该字段的默认值，若不设定，则默认位NULL。

数据类型：

• varchar(最长255)
  可变长度的字符串
  比较智能，节省空间
  会根据实际的数据长度动态分配空间

  优点：节省空间
  缺点：需要动态内存分配，速度慢

• char(最长255)
  定长字符串
  不管实际数据长度是多少。
  分配固定的长度空间去存储数据。
  使用不当会导致空间的浪费。

  优点：不需要动态分配空间，速度快。
  缺点：使用不当会导致空间的浪费。

• int(最长11位)

• bigint
  等同于Java的long

• float

• double

• date
  短日期类型(只包括年月日信息) 默认格式：%Y-%m-%d

• datetime
  长日期类型(包括年月日时分秒信息) 默认格式：%Y-%m-%d %h:%i:%s

• clob
  字符大对象
  最多可存储4G的字符串
  比如存储一篇文章
  超过255个字符的都要采用clob字符大对象来存储。

• blob
  二进制大对象
  专门来存储图片、声音、视频等流媒体数据。
  往blob类型的字段上插入数据的时候，例如插入一个图片、视频等，你需要使用IO流才行。

删除(drop)

• 语法格式：drop table if exists 表名;

插入(insert)(DML语句)

• 语法格式：
  insert into 表名(字段1, 字段2, 字段3…) values(值1, 值2, 值3);
  注意：字段名和值要一一对应。
  什么是一一对应呢？ 数量要对应，数据类型要对应。
• 使用insert语句，如果只是给部分字段指定值的话，则未指定的字段的值默认是NULL。
• 若字段名都省掉了的话，等于都写上了，所以值也要都写上。

insert插入日期

• 数字格式化：format
  格式化数字：format(数字, ‘格式’)

• str_to_date: 将字符串varchar类型转换成日期date类型，通常使用在插入insert方面。
  语法格式：str_to_date(‘字符串日期’, ‘日期格式’)
  特殊：若你提供的日期字符串恰好是以下格式，则不需要使用str_to_date函数了。
  %Y-%m-%d

  MySQL的日期格式
  %Y: 年
  %m: 月
  %d: 日
  %h: 时
  %i: 分
  %s: 秒

• date_format：将date类型转换成具有一定格式的varchar字符串类型。
  格式：date_format(日期数据类型，‘日期格式’)
  这个函数通常在查询日期时使用，用以设置展示的日期格式。
  若不进行日期展示格式设置，则默认格式为：’%Y-%m-%d’

• 注意：数据库中有一条命名规范：
  所有的标识符全部小写，单词与单词之间使用下划线衔接。（不同于Java）

• 在MySQL当中是如何获取系统当前时间的？
  now( ) 函数，并且获取的时间带有时分秒信息，是datetime类型的。

update语句（改）