MySQL笔记（一）

日月卿_宇
201 阅读5分钟

一、检索数据

book表创建语句

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `type_id` int(11) DEFAULT NULL COMMENT '书籍类型id',
  `type` varchar(255) DEFAULT NULL COMMENT '类型',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  `number` int(11) DEFAULT NULL COMMENT '数量',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

image.png

1、select语句

检索单个列

select `name` from `book`;

image.png

检索所有列

select * from `book`;

image.png

检索前n行

SELECT * FROM `book` limit 2;

2、排序检索数据

升序，默认为升序，asc可以不填

SELECT * 
FROM `book`
order by create_time asc;

image.png

降序排序

SELECT * 
FROM `book`
order by create_time desc;

image.png

多条件排序

SELECT * 
FROM `book`
order by create_time desc,update_time asc;

image.png

3、过滤数据

匹配检查

SELECT * 
FROM `book`
where type = '信息技术';

image.png

不匹配检查

SELECT * 
FROM `book`
where type != '信息技术';

image.png

空置查询

SELECT * 
FROM `book`
where description is null;

image.png

多条件查询,and

SELECT * 
FROM `book`
where type = '信息技术'
and create_time > '2023-05-21 00:00:00';

image.png

多条件查询，or

SELECT * 
FROM `book`
where type = '信息技术'
or type = '学习资料';

image.png

in 操作符

SELECT * 
FROM `book`
where type in ('信息技术','教育知识');

image.png

Not 操作符

SELECT * 
FROM `book`
where type not in ('学习资料','教育知识');

image.png

4、通配符过滤

百分号(%)通配符 ：%表示任何字符出现任意次数

SELECT * 
FROM `book`
where type like '信息%';

image.png

下划线（_)通配符

SELECT * 
FROM `book`
where type like '学习__';

image.png

5、数据分组

group by

select type_id,count(number)
from book 
group by type_id;

注意事项

MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。

于是查询时报错

select type_id,type,count(number)
from book 
group by type_id;

image.png

this is incompatible with sql_mode=only_full_group_by 这与sqlmode=only_full_group_by不兼容

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值。 MySQL提供了any_value() 函数来抑制ONLY_FULL_GROUP_BY值被拒绝。 any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。

select type_id,any_value(type),count(number)
from book 
group by type_id;

image.png

6、过滤分组

select type_id,ANY_VALUE(type),count(number)
from book 
group by type_id
having count(*) > 2

image.png

总结

子句说明是否必须使用
select要返回的列或表达式是
from从中检索的表仅在从表选择数据时使用
where行级过滤否
group by分组说明仅在按组计算聚集时使用
having组级过滤否
order by输出排序顺序否
limit要检索的行数否

7、正则表达式

regexp + 正则表达式

SELECT
	type,
	name
FROM
	`book`
where 
	name REGEXP '^J';

image.png

二、MySQL函数

1、拼接字段

SELECT id,type,concat('《',name,'》') ,concat(type,'-',name) 
FROM book;

image.png

使用别名

SELECT 
    id,type,concat('《',name,'》') as name,concat(type,'-',name) as description
FROM book;

image.png

2、RTrim()函数

删除值右边所有空格

原数据

image.png

SELECT 
    id,type,concat('"',rtrim(description),'"') as description
FROM book;

image.png

3、LTrim()函数

删除值左边所有空格 同上。

4、执行算数计算

SELECT
	id,type,name,number * price as total_price
FROM
	book;

image.png

5、Upper()函数

将文本转换为大写

SELECT
	id,type,upper(name),price
FROM
	book;

image.png

6、Date()函数

SELECT
	* 
FROM
	book
where 
	date(create_time) = '2023-05-02';

image.png

7、AVG()函数

返回某列的平均值

SELECT
	AVG(price)	
FROM
	book;

image.png

distinct字段

对数值不同参数进行就平均值

image.png

SELECT
	AVG(distinct price)	
FROM
	book;

image.png

8、COUNT()函数

返回某列的函数

SELECT
	COUNT(price)	
FROM
	book;

image.png

9、MAX()函数

返回某列的最大值

SELECT
	MAX(price)	
FROM
	book;

image.png

10、MIN()函数

返回某列的最小值

SELECT
	MIN(price)	
FROM
	book;

image.png

11、SUM()函数

返回某列值之和

SELECT
	SUM(price)	
FROM
	book;

image.png

Between字段

查询范围内的数据

SELECT
	* 
FROM
	book
where 
	date(create_time) between '2023-05-02' and '2023-05-03'

image.png

组合聚集函数

SELECT
	AVG(price),
	COUNT(price),
	MAX(price),
	MIN(price),
	SUM(price)
FROM
	book;

image.png

常用的文本处理函数

函数说明
Left()返回串左边的字符
Length()返回串的长度
Locate()找出串的一个字符串
Lower()将传转换为小些
Right()返回串右边的字符
Soundex()返回字符串的soundex值
SubString()返回字串的字符
Upper()将串转换为大写

日期和时间处理函数

函数说明
AddDate()增加一个日期（天、周等）
AddTime()增加一个时间（时、分等）
CurDate()返回当前日期
CurTime()返回当前时间
Date()返回日期时间的日期部分
DateDiff()计算两个日期之差
Date_Add()高度灵活的日期运算函数
Date_Format()返回一个格式化的日期或时间串
Day()返回一个日期的天数部分
DayOfWeek()对于一个日期，返回对应的星期几
Hour()返回一个时间的小时部分
Minute()返回一个时间的分钟部分
Month()返回一个日期的月份部分
Now()返回当前日期和时间
Second()返回一个时间的秒部分
Time()返回一个日期时间的时间部分
Year()返回一个时间的年部分

数值处理函数

函数说明
Abs()返回一个数的绝对值
Cos()返回一个角度的余弦
Exp()返回一个数的指数值
Mod()返回除操作的余数
Pi()返回圆周率
Rand()返回一个随机数
Sin()返回一个角度的正弦
Sqrt()返回一个数的平方根
Tan()返回一个角度的正切
avatar
文章
阅读
粉丝