mysql学习笔记二-基础篇

1. 函数

字符串函数

常用函数表

函数	说明
concat(str1, str2, ...)	字符串拼接
lower(str)	将字符串转小写
upper(str)	将字符串全部转为大写
lpad(str, n, pad)	左填充,用字符串pad填充str达到n个字符
rpad(str, n, pad)	右填充,用字符串pad填充str达到n个字符
trim(str)	去掉字符串头部和尾部的空格
substring(str, start, len)	字符串裁剪,str: 字符串

具体使用

select concat("你好", "世界");

select concat("你好", "世界", "V我50吃个炸鸡");

select lower("HellO, WORlD!");

select upper("HellO, WORlD!");

使用null填充上一条语句的内容(填充至15个字符):

select lpad(upper("HellO, WORlD!"), 15, "NULL");

填充至20个字符:

select lpad(upper("HellO, WORlD!"), 20, "NULL");

右填充：

select rpad(upper("HellO, WORlD!"), 20, "NULL");

先用左填充填充一个带空格的数据:

select lpad(upper("HellO, WORlD!"), 20, " ");

觉得嵌套麻烦直接用这个:

select trim(" hello ");

清除上面的填充的空格:

select trim(lpad(upper("HellO, WORlD!"), 20, " "));

select substring("hello", 1, 2);

select substring(trim(lpad(upper("HellO, WORlD!"), 20, " ")), 3, 5);

数值函数

常用函数表

函数	说明
ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回x/y的模(余数)
rand()	返回0~1内的随机数
round(x, y)	求参数x四舍五入的值，保留y位小数

具体使用

select ceil(4.2);

select floor(4.8);

select mod(6, 5);select mod(11, 5);

select rand();

将随机数返回的值四舍五入一下(保留两位小数)：

select round((rand()), 2);

日期函数

函数	说明
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前时间和日期
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date, interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的值
datediff(date1, date2)	返回两个时间直接间隔的天数

具体使用

select curdate();select curtime();

使用now或者拼接concat()拼接一个时间:

select now();select concat((curdate()), " ", (curtime()));

获取时间中的年、月、日:

select year((now())), month((now())), day((now()));

将当前时间分别加上3天、3小时、3分钟、3秒显示:

select date_add(now(), interval 3 day),date_add(now(), interval 3 hour),date_add(now(), interval 3 minute),date_add(now(), interval 3 second);

当前时间分别加上3天、3个月之间间隔的时间差:

select datediff(date_add(now(), interval 3 month),date_add(now(), interval 3 hour));

流程函数

常用函数表

函数	说明
if(value, t, f)	判断条件value为true返回t，否则返回f
ifnull(value1, value2)	如果value1不为空返回value1，否则返回value2
case [expr] when val1 then res1when val2 then res2....else default end	类似于switch...case...语句
case when val1 then res1....else default end	与上面相比就是直接判断val的条件是否为ture

具体使用

select if(1=1, 1, 0);select if(1=2, 1, 0);

生成一个随机数判断是否在0.5到0.9之间:

select if (round(rand(), 1) between 0.5 and 0.9, true, false);

select ifnull(NULL, "是空值");select ifnull("aw", "是空值");

case判断正确值:

select case when 1=2 then "1=2正确" when 1=1 then "1=1正确" else "全部错误" end;

将1=1修改为1=3：

select case when 1=2 then "1=2正确" when 1=3 then "1=3正确" else "全部错误" end;

这里用之前的表判断账号数量

select case (select count(username) from test_table) when 4 then "有4位用户" when 5 then "有5位用户" else "数据量无法匹配" end;

2. 约束

概念: 约束是作用于表中字段上的规则，用于现在存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类：

• 非空约束(NOT NULL)：限制该字段的数据不能为空。
• 唯一约束(UNIQUE)：保证该字段的所有数据都是唯一、不重复的。
• 主键约束(PRIMARY KEY)：主键是一行数据的唯一标识，要求不能为空也并且唯一。
• 默认约束(DEFAULT)：保存数据时，如果未指定该字段的值，则采用默认值。
• 检查约束(CHECK)：保证字段值满足某一个条件。
• 外键约束(FOREIGN KEY)：用来让两张表之间建立连接，保证数据的一致性和完整性。

具体使用

先准备表

grant ALL on [数据库名].* to test; -- 如果没有权限先赋予权限
flush privileges; -- 刷新权限
create table test_user(id int primary key auto_increment, name varchar(10) unique not null, age int check(age > 0 and age <= 120), status char(1) default '1', gender char(1));

插入数据

insert into test_user(name, age, gender) values(null,168,'男');

这里可以看到显示错误name列不可以为空，将name列填充数据重新添加。

insert into test_user(name, age, gender) values("小白",168,'男');

注: 因为 mysql<8.0.16之前的版本之前check是默认不生效的, 所以数据库依旧可以添加成功。

在插入数据的语句中是没有指定status的，但是可以看到默认插入'1'。再次插入名为小白的用户

insert into test_user(name, age, gender) values("小白",18,'男');

因为存在唯一约束，数据插入失败。

外键约束

添加外键

添加外键的两种语法：

• 方法一：CREATE TABLE [表名]([字段1] [类型], ..., [字段n] [类型] CONSTRAINT [外键名称(可选)] FOREIGN KEY([字段名]) REFERENCES [主表名]([主表列名]));
• 方法二：ALTER TABLE [表名] ADD CONSTRAINT [外键名称] FOREIGN KEY([字段名]) REFERENCES [主表名]([主表列名])

先建立一张用于测试的表

drop table if exists test_table;
create table group_table(id int primary key auto_increment, name varchar(20));
create table test_table(
  id int primary key auto_increment, 
  name varchar(20) unique not null,
  age int,
  group_id int, foreign key(group_id) references group_table(id) 
);
-- 插入测试数据
insert into group_table(name) value ("分组一"), ("分组二"), ("分组三");
insert into test_table(name, age, group_id) value("小红", 16, 1), ("小白", 18, 2),
("小美", 19, 2), ("小张", 16, 3), ("小李", 17, 1), ("小郭", 18, 3);

我遇到的两个小问题：

1. 在mysql小于8.0之前的版本不支持列级外键的写法，"group_id int"这里结束要加 ',' 号
2. 我用的小皮面板自带的mysql的5.7版本，默认存储引擎设置的是MyISAM，而MyISAM不支持外键存储，需要修改 my.ini 文件，将他修改为InnoDB。

删除外键

ALTER TBALE [表名] DROP FOREIGN KEY [外键名称];

注：如果建表的时候没有设置外键名称，可用show create table [表名]查看默认建表外键名称.

alter table test_table drop foreign key test_table_ibfk_1;

外键约束的删除和更新行为

语法: ALTER TABLE [表名] ADD CONSTRAINT [外键名称] FOREIGN KEY([外键字段]) REFERENCES [主表名]([主表字段]) ON UPDATE [行为] ON DELETE [行为];

行为	说明
NO ACTION
在父表中删除更新对应记录时，首先检查是否有外键记录，如果有则不允许删除
RESTRICT	在父表中删除更新对应记录时，首先检查是否有外键记录，如果有则不允许删除
CASCADE	在父表中删除更新对应记录时，首先检查是否有外键记录，如果有则更新外键在子表中的记录
SET NULL	在父表中删除更新对应记录时，首先检查是否有外键记录，如果有将子表中的外键值设置为NULL
SET DEFAULT	父表有变更时，将子表的外键值设置为一个默认的值

具体使用

这里将更新时候设置为随父表更新，删除时候设置为空：

alter table test_table ADD constraint group_key foreign key(group_id) references group_table(id) on update cascade on delete set null;

将分组一的id修改为4:

update group_table set id=4 where id=1;

删除分组一：

delete from group_table where id=4;

3. 多表查询

多表关系

多表关系一般就是三种: 多对多 ， 一对多(多对一) ， 多对多 。

多表查询概述

简单的多表查询

select * from test_table, group_table;

数据是重复的，需要设置条件消除无效的重复值

select * from test_table, group_table where test_table.group_id = group.id;

对于

他们俩的组ID为NUll所以无法筛选。

多表查询的分类

内连接: 相当于查询A，B交集部分的数据

外连接：

• 左外连接：查询左表的所有数据以及两张表的交集部分。
• 右外连接：查询右表的所有数据以及两张表的交集部分。

自连接：当前表与自身的连接查询，自连接必须使用表别名。

内连接

隐式内连接

语法： SELECT [字段列表] FROM [表1, 表2] WHRER 条件...;

使用

和上面给的示例差不多:

select test_table.name, group_table.name from test_table, group_table where test_table.group_id = group_table.id;

显式内连接

语法: SELECT [字段列表] FROM [表1] [INNER] JOIN [表2] ON 连接条件...;

使用

select test_table.name, group_table.name from test_table inner join group_table on test_table.group_id = group_table.id;

外连接

左外连接

相当于查询左表全部数据然后查出与右表交集的部分（ 以左表为主查询 ）。

语法: SELECT [字段列表] FROM [表1] LEFT OUTER JOIN [表2] ON [条件];

使用

select * from test_table left outer join group_table on test_table.group_id = group_table.id;

右外连接

相当于查询右表全部数据然后查出与右表交集的部分（ 以右表为主查询 ）。

语法: SELECT [字段列表] FROM [表1] RIGHT OUTER JOIN [表2] ON [条件];

使用

select * from test_table right outer join group_table on test_table.group_id = group_table.id;

自连接

先给表加一列用来模仿原课程的直属领导

alter table test_table add manager_id int;
update test_table set manager_id=1 where id=1;
update test_table set manager_id=1 where id=2;
update test_table set manager_id=1 where id=3;
update test_table set manager_id=2 where id=4;
update test_table set manager_id=2 where id=5;
update test_table set manager_id=2 where id=6;

自连接语法: SELECT [字段列表] FROM [表1] [别名1] JOIN [表2] [别名2] ON [条件];

使用

select table1.name '领导', table2.name '员工' from test_table table1 left join test_table table2 on table2.manager_id=table1.id;

联合查询

把多次查询的结果合并起来。

语法: SELECT [字段列表] FROM [表1] UNION SELECT [字段列表] FROM [表2];

使用

查询年龄大于18并且管理员id为1:

select * from test_table where age>=18 union select * from test_table where manager_id=1;

注意事项：

1. 对于联合查询的多张表的列数必须一致，字段类型也必须保持一致。
2. union all会将所有的数据合并到一起，而union会合并并去重

子查询

概念: 在sql语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

根据子查询结果不同分为：

• 标量子查询: 查询结果为单个值
• 列子查询: 子查询结果为一列
• 行子查询: 子查询结果为一行
• 表子查询: 子查询结果为多行多列

根据子查询位置分为：where之后，from之后， select之后。

标量子查询

先查询分组二id然后根据分组二id查询属于分组二的人：

select * from test_table where group_id=(select id from group_table where name="分组二");

列子查询

常用操作符表

操作符	说明
IN	在指定的集合范围之内
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

in

select * from test_table where group_id=(select id from group_table where name="分组二");

not in

因为我的表中数据除了分组表中的id就是空，所以查出来就是空:

select * from test_table where group_id not in (2, 5);

any，some，all

在老师给的案例里面感觉就是比最大值高(all)和比最小值高(any),理解不是很透彻。

select * from test_table where id > any(select id from group_table);

select * from test_table where id > all(select id from group_table);

行子查询

常用操作符: =, <>, in, not in

查询和小白相同年龄并且领导是小白的人:

select * from test_table where (manager_id, age)=(select group_id,age from test_table where name="小白");

表子查询

类似于多行子查询，使用in连接。

查询有和小红手下的人分组和年龄都相等的人：

select * from test_table where (group_id, age) in (select group_id,age from test_table where manager_id=1) and manager_id != 1;

4. 事务

概念: 事务是一组操作的集合，事务会把所有的操作提打包为一个整体一起向系统提交，这些操作要么一起成功要么一起失败。

事务的操作流程: 开启事务 ==> 出错(回滚事务)

==> 无报错(提交事务)

mysql默认是自动提交事务的，当执行多个sql语句会自动提交执行，即使报错也不会停止。

事务操作

• 开启事务: start transaction;
• 查看事务提交方式: select @@autocommit;
• 设置事务提交方式: set @@autocommit = 0;
• 提交事务: commit;
• 回滚事务: collback;

具体使用

先将提交方式设置为手动提交。

注: 该设置只作用于当前窗口会话，如果会话掉线就会失效。

开启一个事务

修改组id执行

update test_table set group_id=2 where id=1;awgehrtyh;update test_table set group_id=3 where id=2;

这里查看sql是正常执行的，这是因为事务只作用于当前会话，在当前会话显示还是正常执行结果，只有在执行commit的时候才会去操作数据库执行修改。

执行rollback可以回滚操作。

事务的四大特性

• 原子性(Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency): 事务完成时，必须所有的数据都保持一致状态。
• 隔离性(Isolation): 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability)：事务一旦提交或者回滚，他对数据库的改变就是永久的。

并发事务的问题

问题	说明
脏读	一个事务中查询到了另一个事务中还未提交的数据
不可重复读	同一个事务中先后读取同一条记录得到了不同的结果
幻读	一个事务按照条件查询一条数据不存在，当插入的时候突然存在了

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	x	√	√
Repeatable Read(mysql默认)	x	x	√
Serializable	x	x	x

基本操作

查看事务隔离级别: select @@transaction_isolation;

设置事务隔离级别: set [SESSION | GOLBAL] transaction isolation level [隔离级别];

演示

先开两个会话窗口进行并设置为手动提交。

set @@autocommit=0;select @@autocommit;

脏读

set session transaction isolation level read uncommitted;select @@transaction_isolation;

start transaction;update test_table set manager_id=5 where name="小郭";

这时候表中就会查询到窗口2中还未提交的数据，发生脏读。

将其设置为read committed则可以解决此问题。

不可重复读

这里重复指的是在同一个事务中查询到了两次不同的数据。

set session transaction isolation level read committed;select @@transaction_isolation;

使用会话2窗口进行数据改写

在会话1中的一个事务里面读出了两种不同的结果。

提升隔离级别为Repeatable Read则解决该问题。

幻读

同样先进行基本设置

会话一先进行一次查询

此时是没有姓名为小兰的用户，这时候先在另一个事务中进行插入，这时候事务一是不知道事务2插入这条数据的，继续插入就会报错。

如果将事务隔离级别改为Serializable：set session transaction isolation level Serializable;select @@transaction_isolation;sql将会进入阻塞状态。

事务1通过查询id拿到id为1的锁（只有具有唯一约束的列才会获得锁）

事务2进行修改，此时事务2插入数据就会进入阻塞状态

只有在事务1提交之后事务2执行。