在上一章大概讲了一部分关于分组查询的知识,这一章再详细的讲解部分。
根据源表的结构,快速创建一张表结构一样的新表。
create table 新表名 like 源表名;
首先先建立一张表:
create database if not exists store character set utf8mb4 collate utf8mb4_0900_ai_ci;
use store;
drop table if exists goods;
create table goods(
id bigint comment '商品编号',
name varchar(50) comment '商品名',
unitprice decimal(12, 2) comment '零售单价',
costprice decimal(12, 2) comment '成本价',
category varchar(20) comment '商品类型' ,
provider varchar(50) comment '供应商'
)character set utf8mb4 collate utf8mb4_0900_ai_ci;
1、聚合函数
1.1、常用函数
函数 | 说明 |
COUNT(DISTINCT expr) | 返回查询到数据的数量(统计结果集的记录数) |
SUM(DISTINCT expr) | 返回到查询的数据的总和, 不是数字没有意义(针对列的操作,要求是数值类型) |
AVG(DISTINCT expr) | 返回查询到的数据的平均值,不是数字没有意义 |
MAX(DISTINCT expr) | 返回查询到的数据的最大值,不是数字没有意义 |
MIN(DISTINCT expr) | 返回查询到的数据的最小值,不是数字没有意义 |
1.1.1、COUNT()函数
select count(*) from goods where (unitprice - costprice) < 5;
而这段代码表示的是统计满足(unitprice - costprice) < 5的所有行。
其中count(*)表示的是数count(*)会统计满足查询条件的所有行,包括包含NULL值的行。(比如id的行是8个 则统计出的值为8)而count(列名)只会统计该列非NULL值的行数。
count(常数)、count(*) 效果是一样的 但我们更推荐使用count(*),在阿里的SQL的编码规范中明确的说不要使用count(常数)。
select count(1) from goods where (unitprice - costprice) < 5;
并不是所有的数据都支持这种写法,网上说有点文章效率比count(*)高,但具有争议。
select count(id) from goods;
其中id为主键列
select count(name) from goods;
这是普通列,但效率不如主键列。
select count(unitprice) from goods;
单独计算一列时,列的值如果是NULL则忽略。
1.1.2、SUM函数
(1)统计所有学生的总分。
sum(unitprice) from goods;
在进行求和运算时会忽略无效的NULL值。
(2)不能统计非数值的列
例如:
select sum(name) from goods;
它运行出来的结果:
用sum计算char类型的时候不会报错但也不会进行运算。
1.1.3、Avg函数
(1)统计unitprice的平均成绩
select avg(unitprice) from goods;
这里涉及到格式化输出小数round(6.15381, 2); 这段代码运行后会输出保留后的两位小数。
select round(6.15381, 2);
这里运用这个格式化输出小数
select round (avg(unitprice), 2) unitprice from goods;
(2)统计平均总数
select round (avg(unitprice - costprice), 2) 平均利润 from goods;
1.1.4、MAX函数
查询unitprice的最大值
select max(unitprice) from goods;
1.1.5、MIN函数
查询unitprice的最小值
select min(unitprice) from goods;
数字运算的聚合函数都忽略了NULL值。
2、Group by 分组查询
GROUP BY ⼦句的作⽤是通过⼀定的规则将⼀个数据集划分成若⼲个⼩的分组,然后针对若⼲个分组进⾏数据处理,⽐如使⽤聚合函数对分组进⾏统计。
SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)
FROM table_references
GROUP BY {col_name | expr}, ...
[HAVING where_condition]
(1)col_name | expr:要查询的列或表达式,可以有多个,必须在 GROUP BY ⼦句中作为分组的依
据
(2)aggregate_function:聚合函数,⽐如COUNT(), SUM(), AVG(), MAX(), MIN()
(3)aggregate_expr:聚合函数传⼊的列或表达式,如果列或表达式不在 GOURP BY ⼦句中,必须
包含中聚合函数中
例如:统计category的类别:
select category, count(*) from goods group by category;
需求:对分组之后的结果进行过滤,比如,找出平均unitprice大于5的商品名称。
select * from 表名 where 条件 group by 列名 order by 列名 limit
where是对表中的数据行进行条件过滤再group by之前where已经执行了。
group by 是对符合条件的(已查出来的)结果集进行分组。
3、having子句
3.1、示例
select category, avg(unitprice) from goods group by category having avg(unitprice) > 10;
having中可以用别名进行过滤。avg(unitprice)后面可以跟别名。
3.2、Having 与Where 的区别
(1)Having ⽤于对分组结果的条件过滤
(2)Where ⽤于对表中真实数据的条件过滤
4、内置函数
MySQL中内部实现好的一些函数,可以直接使用。
4.1、日期函数
函数
|
说明
|
CURDATE()
|
返回当前⽇期,同义词
CURRENT_DATE ,
CURRENT_DATE()
|
CURTIME()
|
返回当前时间,同义词
CURRENT_TIME
,
CURRENT_TIME([fsp])
|
NOW()
|
返回当前⽇期和时间,同义语
CURRENT_TIMESTAMP
,
CURRENT_TIMESTAMP
|
DATE(data)
|
提取date或datetime表达式的⽇期部分
|
ADDDATE(date,INTERVAL expr unit)
|
向⽇期值添加时间值(间隔),同义词
DATE_ADD()
|
SUBDATE(date,INTERVAL expr unit)
|
向⽇期值减去时间值(间隔),同义词
DATE_SUB()
|
DATEDIFF(expr1,expr2)
|
两个⽇期的差,以天为单位,expr1 - expr2
|
4.1.1、示例
mysql> select CURDATE();
select curtime();
select now();
(4)提取指定datatime的⽇期部分
select date('2025-1-23 15:00');
4.2、字符串处理函数
函数
|
说明
|
CHAR_LENGTH(str)
|
返回给定字符串的⻓度,同义词
CHARACTER_LENGTH()
|
LENGTH(str)
|
返回给定字符串的字节数,与当前使⽤的字符编码集有关
|
CONCAT(str1,str2,...)
|
返回拼接后的字符串
|
CONCAT_WS(separator,str1,str2,...)
|
返回拼接后带分隔符的字符串
|
LCASE(str)
|
将给定字符串转换成⼩写,同义词
LOWER()
|
UCASE(str)
|
将给定字符串转换成⼤写,同义词
UPPER()
|
HEX(str), HEX(N)
|
对于字符串参数str, HEX()返回str的⼗六进制字符串表⽰形式,对于数字参数N, HEX()返回⼀个⼗六进制字符串表⽰形式
|
INSTR(str,substr)
|
返回substring第⼀次出现的索引
|
INSERT(str,pos,len,newstr)
|
在指定位置插⼊⼦字符串,最多不超过指定的字符数
|
SUBSTR(
str
,
pos
)
SUBSTR(str FROM pos FOR len)
|
返回指定的⼦字符串,同义词
SUBSTRING(
str
,
pos
)
,
SUBSTRING(str FROM pos FOR len)
|
REPLACE(str,from_str,to_str)
|
把字符串str中所有的from_str替换为to_str,区分⼤⼩写
|
STRCMP(expr1,expr2)
|
逐个字符⽐较两个字符串,返回 -1, 0 , 1
|
LEFT(str,len)
,RIGHT(str,len)
|
返回字符串str中最左/最右边的len个字符
|
LTRIM(str)
,
RTRIM(str)
,TRIM(str)
|
删除给定字符串的前导、末尾、前导和末尾的空格
|
TRIM([{LEADING | TRAILING | BOTH } [
remstr
] FROM]
str
)
|
删除给定符串的前导、末尾或前导和末尾的指定字符串
|
4.2.1、示例
select concat(name, '的单价:', unitprice, '成本价:', costprice) from goods;
5、数学函数
函数
|
说明
|
ABS(X)
|
返回X的绝对值
|
CEIL(X)
|
返回不⼩于X的最⼩整数值,同义词是
CEILING(X)
|
FLOOR(X)
|
返回不⼤于X的最⼤整数值
|
CONV(N,from_base,to_base)
|
不同进制之间的转换
|
FORMAT(X,D)
|
将数字X格式化为“#,###,###”的格式。##',四舍五⼊到⼩数点后D
位,并以字符串形式返回
|
RAND([N])
|
返回⼀个随机浮点值,取值范围 [0.0, 1.0)
|
ROUND(X), ROUND(X,D)
|
将参数X舍⼊到⼩数点后D位
|
CRC32(expr)
|
计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数
|
Round(x,y)保留y位小数。
例如:
select ABS(-100);
这几个函数的使用都极其相似,记住即可。
6、数据库约束
是关系型数据库的一个重要功能。
主要的作用是保证数据的有效性,也可以理解为数据的正确性(数据本身是否正确,关联关系是否正确)人工检查数据的完整性工作量非常大,在数据库中定义一些约束,那么数据在写入数据库的时候,就会帮我们做一些校验。约束一般是在指定的列上创建的。
6.1、约束类型
类型 | 说明 |
NOT NULL⾮空约束
|
指定⾮空约束的列不能存储 NULL 值
|
DEFALUT 默认约束
|
当没有给列赋值时使⽤的默认值
|
UNIQUE 唯⼀约束
|
指定唯⼀约束的列每⾏数据必须有唯⼀的值
|
PRIMARY KEY 主键约束
|
NOT NULL 和 UNIQUE的结合,可以指定⼀个列或多个列,有助于防⽌数据 重复和提⾼数据的查询性能
|
FOREIGN KEY 外键约束
|
外键约束是⼀种关系约束,⽤于定义两个表之间的关联关系,可以确保数据 的完整性和⼀致性
|
CHECK 约束
|
⽤于限制列或数据在数据库表中的值,确保数据的准确性和可靠性
|
6.2、NOT NULL非空约束
当我们在定义表时某列不允许为NULL值时,可以为列添加非空约束。
就比如说创建一个学生表,当学生名为NULL时,这条记录是不完整的。
create table student(
id bigint,
name varchar(20)
);
此时的姓名如果为NULL值则没有意义。
create table student(
id bigint,
name varchar(20) NOT NULL;
);
在不可以为空的列中加NOT NULL 即可。
6.3、UNIQUE唯一约束
指定了唯一约束的列,该列的值在所有记录中不能重复,比如说一个人的身份证号,学生的学生号等。
create table student(
id bigint UNIQUE,
name varchar(20) NOT NULL
);
这段代码中在唯一的列 id中加了unique关键字。
只加了唯一约束的列可以写入NULL值,且可以写入多个NULL值。唯一值的校验包含数值和字符串。
6.4、PRIMARY KEY 主键约束
主键约束唯一标识数据库表中的每条记录。(数据管理数据时,使用主键列作为数据行的“身份证编号”)
主键必须包含唯一的值,且不能包含NULL值。(非空约束 + 唯一约束)
每个表只能有一个主键,可以由单个列或多个列组成。(复合主键)
通常为每张表都指定一个主键,主键列建议使用BIGINT类型(范围足够大,不会溢出)
复合主键:由多个列共同组成的主键,主键是否冲突以多个列的组成进⾏判定
例如:
drop table if exists student;
create table student(
id bigint PRIMARY KEY,
name varchar(20) NOT NULL
);
desc student;
这个在功能上等效于:
drop table if exists student;
create table student(
id bigint NOT NULL UNIQUE,
name varchar(20) NOT NULL
);
desc student;
NOT NULL UNIQUE = PRIMARY KEY。非空和唯一同时定义相当于该列定义了主键
drop table if exists student;
create table student(
id bigint PRIMARY KEY AUTO_INCREMENT,
name varchar(20) NOT NULL
);
desc student;
AUTO_INCREMENT自增的关键字,一个表中只能有一个列是自增列。自增标识
底层存储数据的时候会根据主键进行排序。
#不指定主键
insert into student(name, sno) values ('李四', '100002');
#主键列的值为NULL
insert into student(id, name, sno) values (NULL, '张三', '100001');

6.5、DEFALUT 默认值约束
drop table student;
# 为年龄列加⼊默认约束
create table student (
id bigint,
name varchar(20) not null,
age int DEFAULT 18
);
可以看到设置了默认值约束后的表在default会显示设置的值。