笔记

1.数据类型

MySQL 字段类型可以简单分为三大类：

• 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
• 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
• 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

null值:

null不可以直接与数值类比较，可以用到 ifnull 判断null值，将其转为0

也不可以用null进行计算，结果为null

比如： select name from customer where ifnull(referee_id,0) !=2;

枚举类型：

enum('F','M')

https://blog.csdn.net/weixin_43725548/article/details/121397067

数值类型：

decimal(a,b)---字符串形式的浮点数，金融计算用这个

a:指的是整数和小数所能存的最多的位数

b:指的是小数数字的位数，即a-b就是整数的位数

日期类型：

官网：https://dev.mysqlserver.cn/doc/refman/8.4/en/datetime.html

• DATE 类型用于具有日期部分但没有时间部分的值。MySQL 以 'YYYY-MM-DD' 格式检索和显示 DATE 值。支持的范围是从 '1000-01-01' 到 '9999-12-31'。
• DATETIME 类型用于包含日期和时间部分的值。MySQL 以 'YYYY-MM-DD hh:mm:ss' 格式检索和显示 DATETIME 值。支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
• 其秒数的默认精度是0，也就是显示格式为2023-04-26 20:36:42
• 也可指定其长度，比如：datetime(3) 显示格式为 -> 2023-04-26 20:36:42.000
• 就是可以指定时间的精度，精度最高可达微秒(6位小数)
• TIMESTAMP 数据类型用于包含日期和时间部分的值。TIMESTAMP 的范围是从 UTC 时间 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'。
• 其秒数的默认精度是0，也就是显示格式为2023-04-26 20:36:42
• 也可指定其长度，比如：timestamp(3) 显示格式为 -> 2023-04-26 20:36:42.000
• 就是可以指定时间的精度，精度最高可达微秒(6位小数)

关于datetime和timestamp的区别：

• 相同点：二者都是可以显示时间部分的，并且可以指定时间的精度，最高可达微秒
• 不同点：
• 表示范围
• DATETIME：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
• TIMESTAMP：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UT
• 空间占用
• TIMESTAMP ：占 4 个字节（小数秒+3 个字节）
• DATETIME：在 MySQL 5.6.4 之前，占 8 个字节 ，之后版本，占 5 个字节。（小数秒+3 个字节）
• 时区
• 在mysql中，只有timestamp时间类型是有时区的。当前时间对应不同时区是不一样的，就是时差嘛。UTC是全群统一时间用的一个时区，之后的时区都是基于这个时区时间+或-,就是相对的往东走或往西走
• 像datetime是没有时区的，我们通过insert语句插入到数据库的时间是什么就存什么
• 而我们的timestamp是会先做时区转化，插入到timestamp的步骤：
• 先转换为UTC: 2025-11-22 09:18:49 UTC
• 内部实际存的是时间戳(时间戳就是从1970年1月1日 00:00:00 UTC 到现在的秒数)，一个整数。不论用什么时区表示时间这个数值都是固定的，比如：
• 北京时间：2025-11-22 17:18:49 (UTC+8)
• UTC时间：2025-11-22 09:18:49 (UTC+0)
• 时间戳：1742815129（秒）
• 如果我们设置了精度，会特意存下精度
• INSERT INTO time_precision (ts_second) VALUES ('2025-11-22 17:18:49'); 实际存储：174281512
• INSERT INTO time_precision (ts_millis) VALUES ('2025-11-22 17:18:49.123');实际存储：1742815129.123 (秒 + 3位小数)
• 读取时（select）再转换回当前时区显示(就是看当前mysql会话采用什么时区)

-- 1.创建表
CREATE TABLE time_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts_col TIMESTAMP,
    dt_col DATETIME
);
-- 2.插入一条数据，使用相同的字符串时间值
INSERT INTO time_test (ts_col, dt_col) VALUES ('2025-11-22 17:30:00', '2025-11-22 17:30:00');
-- 3.在当前时区（+08:00）下查询（默认会话时区就是mysql服务器操作系统所在时区，即我们电脑的时区东八区）
SELECT * FROM time_test;
/*结果应该是：（因为现在时区就是东八区，）
ts_col: 2025-11-22 17:30:00
dt_col: 2025-11-22 17:30:00
*/
-- 4.改变会话时区为UTC（+00:00）再查询
SET time_zone = '+00:00';
SELECT * FROM time_test;
/*结果应该是：
ts_col: 2025-11-22 09:30:00 （因为TIMESTAMP存储的是UTC时间，在UTC时区下显示为09:30，即比+08:00早8小时）
dt_col: 2025-11-22 17:30:00 （DATETIME不受时区影响，还是显示原来的时间）
*/

DATETIME：适用于存储不受时区影响的具体日期和时间，常用于表示某个特定时刻的事件，如生日、会议时间等。

TIMESTAMP：适用于存储与时区相关的日期和时间，常用于记录数据的创建和更新时间，便于在不同时间区域进行时间转换和比较。

简单处理查询结果-去重，限值行数，重命名，排序

去重：distinct

distinct 字段1，字段2...是根据这所有的字段去的，不是单单指字段1|

放在字段前的 如count(distinct device_id)

限值行数：limit

• LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
• LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
• 如果只给定一个参数，它表示返回最大的记录行数目。
• 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
• 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。
• 初始记录行的偏移量是 0(而不是 1)。
• 检索记录行 6-10
• 检索记录行 11-last SELECT* FROMtableLIMIT 10,-1
• 检索前 5 个记录行 SELECT* FROMtableLIMIT 5

重命名： as (可省略)

在某些情况下必须重命名：

例如：select distinct qpd.device_id,qpd.date,qpd2.date

表的列名是 device_id date date

所以在对这个表select时会出现列名重复的错误

一定要起别名

排序：order by

ORDER BY <列名> [ASC | DESC ](默认升序)

按<列名>进行升序（ASC）或降序（DESC）排序，还可以按照别名或序号进行排序。

如:order by gpa desc,age desc 先按照gpa、年龄降序排序

order by gpa,age 先按照gpa升序排序，再按照年龄升序排序输出

3.运算符

= 或 ！= 只能判断基本数据类型

is 关键字只能判断null

<=> 既能判断null 又能判断 基本数据类型

4.条件筛选

where

后加筛选条件（不可用函数）

(not)like

模糊查询

匹配串中可包含如下四种通配符：

_：匹配任意一个字符；

%：匹配0个或多个字符；

[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；

[^ ]：不匹配[ ]中的任意一个字符。

where XXX in()和where XXX not in()

如：where university in('北京大学','复旦大学','山东大学')

5.函数

聚合函数

只能放在select 或 having 或 order by后面

AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。

COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。

COUNT(*) 返回表中的行数(包括有NULL值的列)。

MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。

MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。

SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。

count和sum的区别

1.count(某字段名) 把该字段的行数（非null）数出来，非null都当有效行

sum(某字段名) 把该字段的数值加起来(自动过滤null值)

2.count(if((...),true,null)) ，if(...)返回的是boolean值,意思是if(...)成立为true是有效行,不成立是null为无效行

若写成count(if((...),1,0)) 意思是if(...)成立为1，非null是有效行,不成立是0仍为非null为有效行，所以会把所有字段加一起，与

count(某字段)无异。

3.sum(if((...),1,0)),if(...)返回的是具体数值,意思是if(...)成立返回1，不成立返回0 用这个返回值进行相加

sum(if(a.result='right', 1, 0))

sum count 里面加if ifa.result='right,返回1，不等于返回0

字符串相关

1、在mysql中，字符串下标是从1开始的

2、字符串长度按字符数 char_length(str)

（1）计算单位：字符

（2）不管汉字还是数字或者是字母都算是一个字符

3、字符串长度按字节数 length(str)

（1）计算单位：字节

（2）utf8编码：一个汉字三个字节，一个数字或字母一个字节。

（3）gbk编码：一个汉字两个字节，一个数字或字母一个字节。

4、字符串的截取

（1）SUBSTRING_INDEX(str,delim,count)

（2）substr(obj，start，length)

• obj：从哪个内容中截取，可以是数值或字符串。
• start：从哪个字符开始截取（1开始，而不是0开始）
• length：截取几个字符（空格也算一个字符）

5、替换函数

replace(字符串，要替换的字符，替换的字符)

eg.replace("H ell o"," ","") 将空格换为没有

6、逆序函数

reverse("123")-->"321"

7、查找a字符串在b字符串从pos位置开始出现的第一个地方

locate(a,b,pos)

eg.select locate('Q',"SQLSERVERSQLSERVER",4);--->11

数字函数

MySQL数学函数

• abs(x) 求绝对值
• PI() 返回圆周率
• sqrt(x) x的平方根
• mod(x,y) x除以y的余数
• ceil()、ceiling() 返回大于或者等于x的最小整数
• floor(x) 返回小于或者等于x的最小整数
• rand() rand(x) 返回0~1的随机数，x相同时返回值相同
• round(x) round(x,y) 返回整数，四舍五入。返回x保留到小数点后y位的值
• truncate(x,y) 截断返回x保留到小数点后y位的值（不四舍五入）
• sign(x) 返回x的符号，负数、0、正数分别返回-1、0、1
• pow(x,y) power(x,y) 返回x的y次方
• exp(x) 返回e的x次方
• log(x) 返回x的自然对数
• log10(x) 返回以10为底的对数
• radians(x) 将角度转换为弧度
• degrees(x) 将弧度转换为角度
• sin(x) 正弦函数，返回正弦值，x是弧度
• asin(x) 反正弦函数，返回反正弦值，x是弧度
• cos(x) 余弦函数，返回余弦值，x是弧度
• acos(x) 反余弦函数，返回反余弦值，x是弧度
• tan(x) 正切函数，返回正切值，x是弧度
• atan(x) atan2(x) 反正切函数，返回反正切值，x是弧度
• cot(x) 余切函数，返回余切值，tan(x)的倒数

条件函数

case函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。分为：简单CASE函数 搜索CASE函数

简单case函数 case var when 1 then ... when 2 then ... else ... end

搜索case函数

eg:

select device_id,gender,

case

when age<20 then '20岁以下'

when age>=20 and age<=24 then '20-24岁'

when age>=25 then '25岁及以上'

else '其他'

end as age_cut

from user_profile

if函数

IF(expr,result_true,result_false)

eg.

SELECT IF(sva=1,"男","女") AS s FROM table_name

WHERE sva != '';

注意：if的控制语句和循环语句只能用在存储过程或函数中

日期函数

日期表示

2021年8月，写法有很多种

• 比如用year/month函数的year(date)=2021 and month(date)=8
• 比如用date_format函数的eg1.date_format(date, "%Y-%m")="2021-08" eg2.date_format(now(), "%Y.%m.%d")="2024.05.20"

date_format是用来对日期进行格式化的，可以使用的格式有很多，常用的：%Y 年, %m 月, %d 天 ,%w 周的天 （0=星期日, 6=星 期六）

获取现在的时间（4种）

可以用上面三种来显示

还可以用 CURRENT_TIMESTAMP 获取当前时间 如：2025-04-17 23:38:27

查询日期属于当年中的第几天,第几周,第几月

第几天

• date_format(日期,'%j') eg.SELECT date_format(now(),'%j');
• dayofyear(日期) 当年第几天 eg.select dayofyear(NOW());--->141

第几周

• select date_format('2023-01-01','%v') #返回52，小写，星期一是一周的第一天，2023年的新的星期还没开始，返回了2022年的第52周，实际上是2023年的第0周
• select date_format('2023-01-01','%V') #返回01，大写，星期天是一周的第一天
• 还有其他方法具体看 获取指定日期是当年第几周，指定日期所在周的开始和结束日期

第几月

日期+n

DATE_ADD() 函数向日期添加指定的时间间隔。

DATE_ADD(date,INTERVAL expr type)

 date参数是合法的日期表达式。 expr参数是您希望添加的时间间隔。

如：date_add(date1, interval 1 day)=date2

计算两个日期的时间差

可以用 TIMESTAMPDIFF 函数,需要传入三个参数，第一个是比较的类型，可以比较FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER或 YEAR几种类型，第二个和第三个参数是待比较的两个时间，比较是后一个时间减前一个时间，具体用法如下:

SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); //这里计算的是两个日期的天数差 返回结果为104

SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); //这里计算的是两个日期的月份差 返回结果为3

窗口函数

只能用在select中

具体看：https://zhuanlan.zhihu.com/p/585363716?utm_psn=1758220079525822464

基本语法：

function (expression)

over ( partition by column

order by column ABS/SESC

rows [...] )

• over()是必须的，其余的不是必须的。
• group by 会影响行数
• partition by 分组总行数不变

over的定义：

over用于为定义一个窗口，它对一组值进行操作，不需要使用group by子句对数据进行分组，能够在同一行中同时返回基础行和列。

over的语法：

partition by 子句进行分组

order by 子句进行排序

窗口函数over()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

over的用法：

over开窗函数必须与聚合函数或排序函数一起使用

聚合函数一般指sum(),max(),min(),count(),avg()等常见函数。

排序函数一般指rank(),row_number(),dense_rank(),ntile()等。

（其中DENSE_RANK() 和rank()，区别：

原始数据：100, 100, 90, 80

DENSE_RANK() 结果：1, 1, 2, 3

RANK() 结果：1, 1, 3, 4

）

使用例子

select *,sum(Salary) over(partition by Groupname) 每个组的总工资,

sum(Salary) over(partition by Groupname order by id) 每个组的累计总工资,

sum(Salary) over(order by id) 累计工资,

sum(Salary) over() 总工资

from Employee

注意：

select后数据显示的顺序就是窗口函数依次执行完后的结果。

6.查询

select

from

where

(

关联查询join...on

)

group by ... having...

order by

limit

sql语句的执行顺序为

 from子句 where 子句 group by 子句 having 子句 select 子句 order by 子句 limit字句

ps: SELECT* from 表名 ; 实际开发中效率低（因为需要将* 转化为每一个列名）

使用SELECT +(所有列名) from 表名 然后结尾 ; 这样读取效率最高

注意点：

1.group by

运行过程，如下图

所以，我们在用group by后，select的对象只能是分组字段或者聚合函数的其他字段

2.having...

分组后可以用来筛选结果

注意：在Having子句中，拿聚合函数跟具体的数据做比较是没有问题的，比如“HAVING COUNT(*)>=2;”是没有问题的；

但是，拿聚合函数跟某个字段作条件判断是不行的，这个必须用表连接才能实现

如：gpa=min(gpa)是错误的

3.关联查询

注意这些都只能用在两表之间，多表要写完on之后接着写，比如：

select * from table1 inner join table2 on table1.c1=table2.c1 inner join table3 on table1.c2=table3.c2 ....

from 多表

当我们直接用form 多表 时，用的就是cross join

cross join是mysql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。

在MySQL中，当CROSS JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。通常，如果每个表分别具有n和m行，则结果集将具有n*m行

如果用了WHERE子句，则相当于内连接，效率和内连接一致的，是在连接表时判断该元组是否符合连接条件，而不是整个生成笛卡尔积后筛选条件；（原因就是在执行时发现from中有逗号','，则看where中的条件，选择最优算法来拼接）

• 有连接条件的 FROM 多表 + WHERE = 交集（等同于内连接）
• 无连接条件的多表 = 笛卡尔积（组合所有行）

内连接 inner join ... on （取左右交集）

外连接 left join ...on right join...on

写在left join ... on 的后面的条件 会保留左边数据，用none代替空值

4.子查询

select ... from 表名 where 属性名 in(=,>,<...) (select ...)

适用于结果只在一个表中，但需要另一个表来筛选哪条是需要的结果

5.select语句合并

union操作符，合并两个或多个 SELECT 语句的结果。默认把重复的值去掉，要留下重复的值，可以用union all。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

6.update和select语句结合使用

方式一：

UPDATE table1 SET column1 = (SELECT column FROM table2 [WHERE condition])

WHERE table1.column2 = value;

注意看，这个select语句中直接可以使用table1的字段，不用连表。

方式二：

UPDATE table1 inner/left/right join table2/(select columns from table3

[inner/left/right join on condition] [where conditions]) as t3

ON condition

SET column1 = value1,column2 = value2,...

[WHERE conditions];

例子：

UPDATE A INNER JOIN

(SELECT B.B1 as B1,B.B2 as B2,C.C1 as C1 from B LEFT JOIN C on

B.B3=C.C3) as t

ON A.A3=t.B1

set A.A1=t.B2,

A.A2=t.C1;

7.视图相关

https://blog.csdn.net/baidu_15952103/article/details/109193385

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名[(属性清单)]

AS SELECT语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

8.事务相关

默认情况下，MySQL在启用自动提交模式的情况下运行。 这意味着，只要您执行更新（修改）表的语句，MySQL就会将更新存储在磁盘上以使其永久生效。 更改无法回滚。

使用START TRANSACTION，自动提交将保持禁用状态，直到您使用COMMIT或ROLLBACK结束事务。 自动提交模式然后恢复到之前的状态。恢复自动提交。

在我们没有commit前，我们select也会看到未提交的修改，但这个是可以rollback的，一旦commit后就不可以rollback了。