MySQL的基础知识(二)-----分组查询&约束

在上一章大概讲了一部分关于分组查询的知识,这一章再详细的讲解部分。

根据源表的结构,快速创建一张表结构一样的新表。

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子句

        使⽤GROUP BY对结果进⾏分组处理之后,对分组的结果进⾏过滤时,不能使⽤ WHERE ⼦句,⽽要使⽤ 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、示例

(1)获取当前⽇期
mysql> select CURDATE();

(2)获取当前时间
select curtime();

(3)获取当前⽇期和时间
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。非空和唯一同时定义相当于该列定义了主键

(1)通常把主键列设置为⾃动增⻓,让数据库维护主键值。
drop table if exists student;
create table student(
  id bigint PRIMARY KEY AUTO_INCREMENT,
  name varchar(20)  NOT NULL
);
desc student;

AUTO_INCREMENT自增的关键字,一个表中只能有一个列是自增列。自增标识

底层存储数据的时候会根据主键进行排序。

(2)插⼊数据时不设置主键列的值
#不指定主键
insert into student(name, sno) values ('李四', '100002');
#主键列的值为NULL
insert into student(id, name, sno) values (NULL, '张三', '100001');

(3)查看表结构,Extra列显⽰auto_increment 表⽰⾃增
当手动设置主键列的值比AUTO_INCREMENT小时,不更新AUTO_INCREMENT的值。

6.5、DEFALUT 默认值约束

DEFAULT 约束⽤于向列中插⼊默认值,如果没有为列设置值,那么会将默认值设置到该列。
例如:
drop table student;
# 为年龄列加⼊默认约束
create table student (
 id bigint,
 name varchar(20) not null,
 age int DEFAULT 18
);

 可以看到设置了默认值约束后的表在default会显示设置的值。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值