文章目录
MySQL系列:
初识MySQL,MySQL常用数据类型和表的操作
有些前面博客提及的知识点这里都可能会省略,有兴趣的可以去观看前面内容。
1.CRUD简介
CRUD是对数据库中的记录进⾏基本的增删改查操作:
• Create (创建)
• Retrieve (读取)
• Update (更新)
• Delete (删除)
2.Create(创建)
语法: INSERT [INTO] table_name
[(column [, column] …)]
VALUES
(value_list) [, (value_list)] …
value_list: value, [, value] …
创建一个用于演示的表
2.1单行数据全列插入
value_list 中值的数量必须和定义表的列的数量及顺序⼀致
2.2 单行数据指定插入
value_list 中值的数量必须和指定列数量及顺序⼀致
2.3 多⾏数据指定列插⼊
在⼀条insert语句中也可以指定多个value_list,实现⼀次插⼊多⾏数据
在单行数据插入时推荐使用指定插入,当表数据多时可以更清楚的知道插入的内容.
指定位置去插入表属性的顺序也可以调换
注意插入时表属性不能缺少或者不赋值
Create(创建)代码:
#2.1单行数据全列插入
-- 插入第一行数据
insert into user values (1,'刘1');
-- 插入第二行
insert into user values (2,'刘2');
#2.2单行数据指定插入
insert into user(id,name) values (3,'张三');
insert into user(name,id) values ('赵六',6);
#2.3 多行数据指定列插入
insert into user(id,name) values (4,'李四'),(5,'王五');
3.Retrieve(读取)
语法:
SELECT
[DISTINCT]
select_expr [, select_expr] ...
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr}, ...]
[HAVING where_condition]
[ORDER BY {col_name | expr } [ASC | DESC], ... ]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
创建一个用于演示的表:
3.1 Select查询
3.1.1 全列查询
查询全部数据
3.1.2 指定列查询
查询所有人的编号、姓名和数学成绩
查询的顺序没有要求:
3.1.3 查询字段为表达式(都是临时表不会对原有表数据产生影响)
常量表达式:
也可以是常量的运算
表达式中包含⼀个字段(列于常量运算)
表达式中多个字段(列于列运算):
3.1.4为查询结果指定别名
语法:
1 SELECT column [AS] alias_name [, ...] FROM table_name;
AS可以省略,别名如果包含空格必须⽤单引号包裹
将每个人的总分展示出来:
3.1.5查询结果去重
查看两条math为98的数据进行去重
去重的条件是所要求值全部相同
以下math相同但id分别为1,3
注意:
•
查询时不加限制条件会返回表中所有结果,如果表中的数据量过⼤,会把服务器的资源消耗殆尽
•
在⽣产环境不要使用不加限制条件的查询
Retrieve(Select )代码:
CREATE TABLE if not exists exam(
id BIGINT,
name VARCHAR(20) COMMENT '同学姓名',
chinese float COMMENT '语文成绩',
math float COMMENT '数学成绩',
english float COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam (id,name, chinese, math, english) VALUES
(1, '唐三藏', 67, 98, 56),
(2, '孙悟空', 87, 78, 77),
(3, '猪悟能', 88, 98, 90),
(4, '曹孟德', 82, 84, 67),
(5, '刘孟德', 55, 85, 45),
(6, '孙权', 70, 73, 78),
(7, '宋公明', 75, 65, 30);
#3.1.1 全列查询
#使用*可以查询表中所有列的值
select * from exam;
#3.1.2 指定列查询
#• 查询所有人的编号、姓名和数学成绩
select id,name,math from exam;
#查询的顺序没有要求:
select name,english,id from exam;
# 查询字段为表达式
#常量表达式:
select id,name,1 from exam;
#也可以是常量的运算
select id,name,1+1 from exam;
# 表达式中包含一个字段
select id,name,math+10 from exam;
#表达式中多个字段:
select id,name,chinese+math+english from exam;
# 3.1.3为查询结果指定别名
#将每个人的总分展示出来:
select id,name,chinese+math+english as 总分 from exam;
# 3.1.4查询结果去重
#查看两条math为98的数据进行去重
select math from exam;
3.2 where 条件查询
语法:
SELECT
select_expr [, select_expr] ... [FROM table_references]
WHERE where_condition
比较运算符:
< , > , >= , <= 小于,大于,大于等于,小于等于
= MySQL中=同时代表赋值和判断 ,对于NULL不安全,NULL=NULL还是NULL
<=> 代表等于 对于NULL相对安全 NULL<=>NULL 结果为TRUE(1)
!= ,<> 代表不等于
IS NULL 是NULL
IS NOT NULL 不是NULL
value BETWEEN a0 AND a1
范围匹配,[a0, a1],如果a0 <= value <= a1,返回TRUE或1,NOT BETWEEN则取反
value IN (option, …) 如果value 在optoin列表中,则返回TRUE(1),NOT IN则取反
LIKE 模糊匹配,% 表⽰任意多个(包括0个)字符;_ 表⽰任意⼀个字符,NOT LIKE则取反
逻辑运算符:
AND 多个条件必须都为 TRUE(1),结果才是 TRUE(1)
OR 任意⼀个条件为 TRUE(1), 结果为 TRUE(1)
NOT 条件为 TRUE(1),结果为 FALSE(0)
3.2.1 where 基础查询
(1)查询语文<=70的
(2)查询数学高于语文的
(3)查询总分低于250的
这里我们需要了解select与from与where之间的优先级
首先执行的是from找到这个表,然后执行符合where条件的,
最后执行select返回在符合条件的要显示的列
所以是错误的当whiere执行时 total还没有被定义,select执行完后chinese+math+english as total 执行 total才定义完成
3.2.2where 范围查询
查询英语成绩在60~80之间的
查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
3.2.3 where 模糊查询
%表示任意多个(包括0个)字符;
%表示所有,等于没有指定条件
%xxx,表示以xxx结束,前面可以包含任意多个字符
xxx%,表示以xxx开头,后面可以包含任意多个字符
%xxx%,前面和后面可以包含任意多个字符,中间必须有xxx
表示任意一个字符,
严格匹配 写多少个_就表示多少个字符
是一个占位符
表示只有一个字符
_ xxx,表示以xxx结束,前面可以包含一个字符
xxx _,表示以xxx开头,后面可以包含一个字符
_XXX _,前面和后面可以包含一个字符,中间必须是xxx
%系列:
_系列:
3.2.4 where NULL查询
对NULLL与其他值进行运算结果为NULL
3.2.5where AND,OR查询
观察AND与OR的优先级:
AND的优先级高于OR
Retrieve(where )代码:
#3.2.1基础查询
#查询语文<=70的
#查询数学高于语文的
#查询总分低于250的
select id,name,chinese from exam where chinese<=70;
select id,name,chinese,math from exam where math>chinese;
select id,name, chinese+math+english as total from exam where (chinese+math+english)<250;
#3.2.2范围 查询英语成绩在60~80之间的 查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
select id,name,english from exam where english between 60 and 80;
select id,name,math from exam where math in(78,79,98,99);
#3.2.3 where 模糊查询
select id,name from exam where name like '%孟%';
select id,name from exam where name like '孙%';
select id,name from exam where name like '%德';
#_系列
select id,name from exam where name like '孙_';
select id,name from exam where name like '孙__';
select id,name from exam where name like '_孟_';
#3.2.3 where NULL查询
#插入一条null
insert into exam values (8,'张飞',88,98,NULL);
select *from exam where english is null;
select *from exam where english is not null;
#对NULLL与其他值进行运算结果为NULL
select id,name,chinese+math+english as total from exam;
3.3 Order by查询
语法:
-- ASC 为升序(从⼩到⼤)
-- DESC 为降序(从⼤到⼩)
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | DESC], ... ;
3.3.1ASC查询
对语文进行ASC
3.3.2 desc查询
对数学进行desc
3.3.3 ASC与desc查询
改一下数据观察同时对语文成绩进行asc,数学进行desc
来观察是否可以使⽤列的别名进⾏排序
注意在排序时NULL比任何值都小, 改一负数进行观察
Retrieve(Order by)代码:
#Order by查询
#对语文进行ASC
select id,name,chinese from exam order by chinese asc;
#对数学进行desc
select id,name,math from exam order by math desc;
#改一下数据观察同时对语文成绩进行asc,数学进行desc
select id,name,chinese,math from exam order by chinese asc, math desc;
#来观察是否可以使⽤列的别名进⾏排序
select id,name,chinese+math+english as total from exam order by chinese+math+english desc;
select id,name,chinese+math+english as total from exam order by total desc;
#注意在排序时NULL比任何值都小, 改一负数进行观察
select id,name,chinese+math+english as total from exam order by total desc;
3.4 分页查询
语法:
-- 起始下标为 0
-- 从 0 开始,筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT num;
-- 从 start 开始,筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT start, num;
-- 从 start 开始,筛选 num 条结果,⽐第⼆种⽤法更明确建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT num OFFSET start;
分页查询主要掌握查询页数与每页查询多少列之间的关系
插入一列数据:
insert into exam(id,name,chinese,math,english) values (9,‘李白’,94,91,77);
接下来将数据增加到9条分5页(第一条为0下标)
num=2;
start=(页数-1)*num
进行分页查询;
Retrieve(分页查询)代码:
#3.4 分页查询
#插入一列
insert into exam(id,name,chinese,math,english) values (9,'李白',94,91,77);
select * from exam order by id desc limit 0,2;
select * from exam order by id desc limit 2,2;
select * from exam order by id desc limit 4,2;
select * from exam order by id desc limit 6,2;
select * from exam order by id desc limit 8,2;
4.Update(更新)
语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment [, assignment] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
4.1.1 改写指定单列数据
将孙悟空的语文数学成绩都是加10
4.1.2 改写指定多列数据
将所有英语成绩*2
注意:• 不加where条件时,会导致全表数据被列新,谨慎操作
Update(更新)代码:
# 4.Update(更新)
# 4.1.1 改写指定单行数据
select name,chinese,math from exam where name ='孙悟空';
update exam set chinese=chinese+10,math=math+10 where name '孙悟空';
select name,chinese,math from exam where name ='孙悟空';
# 4.1.2 改写指定多行数据,将所有英语成绩*2
id,name,english from exam order by english asc ;
update exam set english=english*2;
id,name,english from exam order by english asc ;
5.Delete(删除)
语法: DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
5.1.1指定单列删除
删除名为张飞的数据
5.1.2删除整张表,插入一张演示表
注意:Delete操作非常危险,执⾏Delete时不加条件会删除整张表的数据,谨慎操作
Delete(删除)代码:
#5.Delete
# 5.1.1指定单列删除,删除名为张飞的数据
delete from exam where name='张飞';
select name from exam where name='张飞';
#5.1.2删除整张表,插入一张演示表
create table if not exists t_student(
id bigint not null comment'编号',
name varchar(20) not null comment'用户名'
);
insert into t_student values (1,'小明'),(2,'小龙'),(3,'小兰');
6.截断表
语法:TRUNCATE [TABLE] tbl_name
准备一个测试的新表来演示:
直接在删除后再插入可以观察到id序号不会重置
所以当此时再插入一列序号为1
Truncate:
•只能对整表操作,不能像 DELETE ⼀样针对部分数据
• 不对数据操作所以⽐DELETE更快,TRUNCATE在删除数据的时候,不经过真正的事物,所以⽆法回滚
• 会重置 AUTO_INCREMENT 项
使用到的代码:
#1.截断表
create table t_data(
id int primary key auto_increment comment '序号',
name varchar(20)
);
insert into t_data(name) values('a'),('b'),('c'),('d');
select * from t_data;
insert into t_data(name) values('d');
show create table t_data;
truncate table t_data;
7.插入查询结果
语法:INSERT INTO table_name [(column [, column ...])] SELECT ...
实现思路:原始表中的数据⼀般不会主动删除,但是真正查询时不需要重复的数据,如果每次查询都使⽤DISTINCT进⾏去重操作,会严重效率。可以创建⼀张与 t_use表结构相同的表,把去重的记录写⼊到新表中,以后查询都从新表中查,这样真实的数据不丢失,同时⼜能保证查询效率
相关代码:
create table if not exists t_use(
id bigint,
name varchar(20)
);
insert into t_use values(1,'aaa'),(1,'aaa'),(2,'bbb'),(2,'bbb'),(3,'ccc'),(3,'ccc');
create table t_use_new like t_use;
insert into t_use_new select distinct * from t_use;
select * from t_use_new;
8.聚合函数
COUNT([DISTINCT] expr) 返回查询到的数据的数量
SUM([DISTINCT] expr) 返回查询到的数据的总和,不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的平均值,不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的最⼤值,不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的最⼩值,不是数字没有意义
这里使用的是前面一篇博文的表exam
count:
sum:
avg:
max,min:
相关代码:
select count(*) from exam;
select count(1) from exam;
select count(id) from exam;
select count(english) from exam;
select sum(chinese) from exam;
select sum(english) from exam;
select sum(name) from exam;
select avg(english) from exam;
select avg(chinese+math+english) 总平均分 from exam; select Round(avg(chinese+math+english),1) 总平均分 from exam;
select max(chinese+math+english) from exam;
select min(chinese+math+english) 总分 from exam;
select max(chinese+math+english) 总分最多 ,min(chinese+math+english) 总分最少 from exam;
9.Group by 分组查询
分组查询的基本语法如下:
SELECT 列, 聚合函数(列)
FROM 表
WHERE 筛选条件
GROUP BY 分组的列
ORDER BY 子句;
在使用GROUP BY时,需要注意以下几点:除了GROUP BY后面的字段,如果要在SELECT后查询其他字段,必须用聚合函数进行聚合。
分组查询中的筛选条件分为两类:分组前筛选和分组后选分组前筛选使用WHERE子句,数据源是原始表;分组后筛选使用HAVING子句,数据源是分组后的结果集。
创建一个新表来观察
having 子句:
使用Grounp by 对其处理后不能使用where进行过滤,要使用having 子句
Having 与Where 的区别
• Having ⽤于对分组结果的条件过滤
• Where ⽤于对表中真实数据的条件过滤
相关代码:
drop table if exists emp;
create table emp (
id bigint primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary decimal(10, 2) not null
);
insert into emp values (1, '小胖', '老板', 1500000.00);
insert into emp values (2, '小腾', '老板', 1800000.00);
insert into emp values (3, '小鑫', '主管', 13000.00);
insert into emp values (4, '小亮', '主管', 12000.00);
insert into emp values (5, '小平', '员工', 9000.00);
insert into emp values (6, '小莹', '员工', 8000.00);
insert into emp values (7, '孙悟空', '游戏人物', 956.8);
insert into emp values (8, '猪悟能', '游戏人物', 700.5);
insert into emp values (9, '沙和尚', '游戏人物', 333.3);
select * from emp;
select role,avg(salary) from emp group by role;
select role,avg(salary) from emp group by role;
select role,avg(salary) from emp group by role order by avg(salary) asc;
select role,ROUND(avg(salary),2) 平均工资 from emp group by role having avg(salary)>10000;
10.内置函数
10.1日期函数
CURDATE() 返回当前日期 同义词:CURRENT_DATE,CURRENT_DATE()
CURTIME() 返回当前时间 同义词 CURRENT_TIME , CURRENT_TIME([fsp])
NOW() 返回当前日期和时间 :CURRENT_TIMESTAMP
DATE(date) 提取date或datetime表达式的⽇期部分
ADDDATE(date,INTERVAL expr unit) 向⽇期值添加时间值(间隔),同义词 DATE_ADD()
SUBDATE(date,INTERVAL expr unit)
向⽇期值减去时间值(间隔),同义词 DATE_SUB()
DATEDIFF(expr1,expr2) 两个⽇期的差,以天为单位expr1 - expr2
参考链接14.7 Date and Time Functions
11.5 Expressions
相关代码:
#5.1日期函数
select CURDATE();
select CURRENT_DATE;
select CURRENT_DATE();
select CURTIME();
select CURRENT_TIME;
select CURRENT_TIME();
select NOW();
select CURRENT_TIMESTAMP;
select DATE('2000-2-17 10:15:23');
select DATE('2000-222-17 10:15:23');
select ADDDATE('2025_2_7 10:10:10',interval 10 minute);
select ADDDATE('2025_2_7 10:10:10',interval 10 day);
select ADDDATE('2025_2_7 10:10:10',interval 10 month);
select SUBDATE('2025_2_7 10:10:10',interval 10 minute);
select DATEDIFF('2025_2_7 10:10:10','2026_2_7 10:10:10');
select DATEDIFF('2027_2_7 10:10:10','2024_2_7 10:10:10');
10.2字符串处理函数
CHAR_LENGTH(str) 返回给定字符串的⻓度,同义词
CHARACTER_LENGTH()
LENGTH(str) 返回给定字符串的字节数,与当前使⽤的字符编码集有关
CONCAT(str1,str2,…) 返回拼接后的字符串
CONCAT_WS(separator,str1,s
tr2,…)返回拼接后带分隔符的字符串
LCASE(str) 将给定字符串转换成⼩写,同义词 LOWER()
UCASE(str) 将给定字符串转换成⼤写,同义词 UPPER()
HEX(str), HEX(N) 对于字符串参数str, HEX()返回str的⼗六进制字符串表⽰形式,对于数字参数N, HEX()返回⼀个⼗六进制字符串表⽰形式
INSTR(str,substr) 返回substr第⼀次出现的索引
INSERT(str,pos,len,newstr)在指定位置插⼊⼦字符串,最多不超过指定的字符数
SUBSTR(str,pos) SUBSTR(str FROM pos FOR len)
返回指定的⼦字符串,同义词 SUBSTRING(str,pos) ,
SUBSTRING(str FROM pos FOR len)
PLACE(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]
删除给定符串的前导、末尾或前导和末尾的指定字符串
参考:14.8 String Functions and Operators
相关代码:
#5.2字符串处理函数
select concat(name,'的语文成绩:',chinese,' 数学成绩:',math,' 英语成 绩:',english) 成绩汇总 from exam;
select concat_ws(',',chinese,math,english) as'语文,数学,英语'from exam;
select lcase('ABC');
select ucase('abc');
select HEX('abc');
select HEX(100);
select instr('abcdef','b');
select insert('Hello, Database',8,0, 'MySQL');
select insert('Hello, Database',8,20, 'MySQL');
select insert('Hello, Database',8,2, 'MySQL');
select SUBSTR('Hello, Database',8);
select SUBSTR('Hello, Database' from 8 for 1);select SUBSTR('Hello, Database' from 8 for 100);
select REPLACE('Hello Database,' ,'Database,','MySQL' );select REPLACE('Hello Database' ,'Database','MySQL' );
select strcmp('abc','ac');
select strcmp('abc','abc');
select strcmp('abc','ab');
select left('abcdef',3);
select right('abcdef',3);
select trim('xxx' from 'xxxabcxxx');
select trim(leading'xxx' from 'xxxabcxxx');
select trim(trailing'xxx' from 'xxxabcxxx');
10.3数字函数
ABS(X) 返回X的绝对值
CEIL(X) 返回不⼩于X的最⼩整数值,同义词是 CEILING(X)
FLOOR(X) 返回不⼤于X的最⼤整数值
CONV(N,from_base,to_base) 不同进制之间的转换
RAND([N]) 返回⼀个随机浮点值,取值范围 [0.0, 1.0)
FORMAT(X,D) 将数字X格式化为“#,###,###”的格式。##',四舍五⼊到⼩数点后D位,并以字符串形式返回
ROUND(X), ROUND(X,D) 将参数X舍⼊到⼩数点后D位
CRC32(expr) 计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数
参考14.6 Numeric Functions and Operators
相关代码:
#5.3数字函数
select abs(-22);
select ceil(11.11);
select floor(11.11);
select conv(15,10,16);
select conv(15,10,8);
select rand();
select format(123456789,3);
select round(rand(),3);
select crc32("abc");
select version();
10.4 其他常⽤函数:
version() 显⽰当前数据库版本
database() 显⽰当前正在使⽤的数据库
user() 显⽰当前⽤户
md5(str) 对⼀个字符串进⾏md5摘要,摘要后得到⼀个32位字符串
ifnull(val1, val2) 如果val1为NULL,返回val2,否则返回val1
select version();
select database();
select user();
select md5('abc');
select ifnull(1,2);
select ifnull(null,2);