MySQL学习笔记三——DQL和函数分类

DQL查询表中的记录

基础查询

语法：

select 查询列表 from 表名

特点：

①查询列表可以是：表中的字段、常量值、表达式、函数

②查询的结果是一个虚拟的表格

stu 表：

id ：主键，自增

name ：姓名，不为空

stu_number ：学号，唯一，不为空

age ：年龄，不为空

score ：分数

create_time ：创建时间

modify_time ：修改时间

操作：

①查询表中的单个字段

SELECT name FROM stu;

②查询表中多个字段

SELECT stu_number,name FROM stu;

③查询表中的全部字段 ( 也可以把每个字段列出来 )

SELECT * FROM stu;

④查询常量值

SELECT 100 ;

SELECT 'john' ;

SELECT 100 % 100 ;

SELECT version();

取别名

使用 AS

① SELECT 100 AS 结果 ;

使用空格

① SELECT stu_number ' 学号 ',name ' 姓名 ' from stu;

好处：

①便于理解

②如果要查询的字段有重复的情况，使用别名可以区分

注意：如果取的别名有特殊符号或者是数据库关键字，比如 OUT ，空格， # 号等，需要给别名加上引号

去重

在查询语句中， select 后面加上 distinct 。

select distinct name from user;

+号的作用

作用：仅仅只有做加法运算功能。

例如：

①两个操作数都是数值型，做加法运算

SELECT 122 + 20 ;

②其中一个是字符型，会试图将字符型数值转换成数值型，成功就做加法运算

SELECT "222" + 20 ;

③其中一个是字符型，会试图将字符型数值转换成数值型，失败就会将字符型数值转换成 0 ，在做加法运算

SELECT "john" + 20 ;

④只要有一个字符为 null ，结果就是 null

SELECT NULL + 20 ;

注意：如果要把两个字符型数值连在一起，就要使用 concat 函数。

例如：

SELECT CONCAT(stu_number,`name`) from stu;

concat_ws( 分隔符 , 字段 1, 字段 2) ，使用分隔符连接字符串

select concat_ws( ',' ,stu_number,`name`) from stu;

条件查询

语法： select 查询列表 from 表名 where 条件

分类：

①按条件表达式筛选

> : 大于

< ：小于

= ：等于

!= ：不等于

<> ：不等于

> = ：大于等于

<= ：小于等于

例如：

SELECT * FROM stu WHERE age > 22 ;

is null ：判断字段为空

例如：

SELECT age,name FROM stu WHERE money IS NULL ;

注意：解决了 money = NULL 查询不出来的问题

is not null: 判断字段不为空

例如：

SELECT age,name FROM stu WHERE money IS NOT NULL ;

用来过滤掉字段为空的数据记录

②按逻辑表达式筛选

and or not （建议使用）

&& || !

例如：

SELECT * FROM stu WHERE age <> 22 AND `name` != ' 小红 ' ;

③模糊查询 ( 条件查询的一种 )

like ， between and ， in

Like：

例如：

SELECT * FROM stu WHERE `name` LIKE '% 红 %' ;

注意：

like 一般和通配符一起使用，常用通配符

% ：代表 0 到多个字符，包括 0

_ ：代表任意单个字符 SELECT * FROM stu WHERE name LIKE ' 小_';

特殊情况：查询带有 _的人名，需要转义

SELECT * FROM stu WHERE `name` LIKE '\_%' ;

between and ：等价于大于等于第一个值，小于等于第二个值

例如：

SELECT * FROM stu WHERE age between 22 AND 23;

注意：

可以提高语句的简洁度；包含临界点；临界值不能颠倒；

如果时间字段使用 between and，需要注意右边界的时间格式，如：

select * from user where create_time between '2022­04­13' and '2022­04­14' ;

有边界的时间没有明确时分秒， mysql 会将时间默认为 2022-04-14 00:00:00 ，返回的结果集可能会不包含 2022- 04-14的数据

in 判断某个字段的值是否属于 in 列表中的某一项，可以满足列表中的一项或多项，如果不满足则返回空

例如：

SELECT age,name FROM stu WHERE stu_number in ( 003 , 002 );

SELECT age,name FROM stu WHERE (age,name) in (( 22 , ' 小红 ' ),( 22 , ' 小明 ' ));

注意：可以提高语句的简洁度； in 列表的值类型必须统一或兼容；不支持通配符

排序查询

语法： select 查询列表 from 表名 [where 条件 ] order by 排序列表 [ASC|DESC]

例如：

1. 基本排序

SELECT age FROM stu ORDER BY age ASC ;

2. 按条件排序

SELECT age FROM stu WHERE age > 22 ORDER BY age ASC ;

3. 按表达式排序

SELECT age,money* 12 年薪 FROM stu ORDER BY 年薪 ASC ;

4. 按函数排序

SELECT LENGTH(`name`) 字节长度 FROM stu ORDER BY 字节长度 ASC ;

5. 按多个字段排序

SELECT LENGTH(`name`) 姓名字节长度 ,age FROM stu ORDER BY 姓名字节长度 ASC ,age DESC ;

6. 随机排序

# 从 stu 表中随机获取 3 条记录

select * from stu order by rand() limit3;

#order by rand() 比较消耗性能，效率比较低，建议最好不用

说明： ORDER BY 先按第一个字段排序，在第一个字段排序的基础上，再按第二个字段排序

特点：

① ASC 表示升序； DESC 表示降序；不写默认是 ASC

② ORDER BY 子句中支持单个字段，多个字段，函数，别名等

③ ORDER BY 一般是放在查询语句的最后， limit 子句除外

分组查询

格式： SELECT 字段名 FROM 表名 group by 字段名 [having] 条件

例如：

SELECT sex FROM stu GROUP BY sex;

SELECT sex FROM stu GROUP BY sex HAVING sex!= ' 女 ' ;

注意：

sql 执行顺序： from --> where -- > group by --> having --> select --> order by --> limit

having 与 where 的区别：

1. 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组；

2.where 后面不可以使用聚合函数，如 SUM(),MIN(),Max() 等；因为聚集函数要对全列数据实行计算，因而使用它

的前提是：结果集已经确定！而 where 子句还处于 “ 确定 ” 结果集的过程中，因而不能使用聚集函数；

3.having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤；

4.having 后面可以使用聚合函数。

5. 使用 having 时，要和 group by 一起使用，并且 having 在 group by 后边，筛选条件的字段需要包含在查询字段

中，如：

select age, count ( 1 ) from user1 group by age having sex>= 0 ;

由于查询字段中没有包含 having 中的 sex 字段，执行结果会报错： Unknown column 'sex' in 'having clause'

select age,sex, count ( 1 ) from user1 group by age having sex>= 0 ;

在查询字段中添加 sex 字段， sql 即可执行

where 筛选条件的字段不需要包含在查询字段中：

select count ( 1 ) from user1 where age= 20 ;

union和union all

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的

顺序必须相同。

select id from student

union

select stu_id from score;

UNION 默认会去掉重复记录值再合并成结果集，如果需要保留重复的记录值，请使用 UNION ALL 。 当 ALL 随 UNION 一起使用时 ( 即 UNION ALL) ，不消除重复行

select id from student

union all

select stu_id from score;

函数分类

数据库中，函数分为标量函数和聚合函数。

标量函数

1 、 UCASE() 函数：将某个字段转换为大写

例如：

SELECT UCASE(`name`) FROM stu;

2 、 LCASE() 函数：将某个字段转换为小写

例如：

SELECT LCASE(`name`) FROM stu;

3 、 LENGTH() 函数：返回某个文本字段的长度

例如：

  SELECT  LENGTH(`name`)  FROM  stu;

4 、 ROUND() 函数：对某个数值字段进行指定小数位数的四舍五入

例如：

  SELECT  ROUND(money)  FROM  stu；

5 、 NOW() 函数：返回当前的系统日期和时间

例如:

SELECT  NOW()  FROM  stu；

6 、 FORMAT(N,D,locale) 函数：

格式化某个字段的显示方式

FORMAT 函数接受三个参数：

N 是要格式化的数字。 

D 是要四舍五入的小数位数。

locale 是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略 locale 操作符， MySQL 将默认使用

en_US 。

例如：

  SELECT  FORMAT( 14500.2018 ,  2 );

7 、 DATA_FORMAT(date,format) 函数

date 参数是合法的日期。 format 规定日期 / 时间的输出格式。

使用 DATA_FORMAT() 函数参照下表

 案例：求年龄

 SELECT id,name,DATE_FORMAT(NOW(),'%Y') ­ DATE_FORMAT(birthday,'%Y') 年龄 FROM stu;

8 、 timestampdiቹ() 函数：格式化某个字段的显示方式

TIMESTAMPDIFF(unit,begin,end):

Unit 码表：

Unit参数	描述
SECOND	秒
MINUTE	分
HOUR	小时
DAY	天
MONTH	月
YEAR	年

TIMESTAMPDIFF 函数返回 begin-end 的结果，其中 begin 和 end 是 DATE 或 DATETIME 表达式； TIMESTAMPDIFF 函数允许其参数具有混合类型，例如，begin 是 DATE 值， end 可以是 DATETIME 值。

例如：

  SELECT  TIMESTAMPDIFF( year ,birthday,NOW())  FROM  stu;

聚合函数

念：之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值NULL 。

如下：

AVG() ­-  返回平均值  

COUNT() -­  返回行数  

MAX() ­-  返回最大值  

MIN() ­-  返回最小值  

SUM() -­  返回总和   

问题： count( 普通字段 ) 、 count(*) 、 count( 主键 id) 、 count(1) 哪个查询速度更快？

count( 普通字段 )<count( 主键 id)<count(1) ≈ count(*)

count(*)  ：对于这个命令，会每遍历一条数据进行累加，排除全字段为空的数据情况下  count++ 

count(1)  ：相当于新加一列，列值为 1 ，此时每一行数据都会进行累加  count++ 

count( 主键字段 )  ：对于主键进行遍历，然后按行累加  count++ 【主键不为空】  

count( 普通字段 )  ：针对普通字段进行逐行遍历，每当该字段值不为空的时候进行累加 1 

对于 count(*),count(1) 和 count( 主键 ) 而言，在底层的执行都是通过查询主键索引树来进行遍历，只要遍历一个索引树就进行加一，这样就会将速率提高，对于一定程度而言，三者的查询效率的几乎相近的。

但是对于 count( 主键 ) 而言，实际在执行主键索引树时，他会将查询的 id 进行返回，然后再进行累加，所以一定程度上速率是比前两者的速率要慢的。

对于 count( 普通字段 ) 而言，在查询的时候是不走索引树的，但是在执行的时候会进行 全表扫描 ，然后判断是否为空再进行累加

3 、 MAX() 函数：返回最大值

例如：

select  max(timestampdiff( year ,birth_date,now())) max_age  from  employees;

select  max(date_format(now(), '%Y' )­-date_format(birth_date, '%Y' )) max_age  from  employees;

4 、 MIN() 函数：返回最小值

例如：

  select  min(timestampdiff( year ,birth_date,now())) min_age  from  employees;

select  min(date_format(now(), '%Y' )­date_format(birth_date, '%Y' )) min_age  from  employees;

5 、 SUM() 函数：返回总和

例如：

  select  emp_no,sum(salary) sum_salary  from  salaries  group   by  emp_no;