MySql学习笔记

基础知识：

冷知识：schema = database。show databases; = show schemas;

MySQL数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1 Bytes	-128 到 127	0 到 255
SMALLINT	2 Bytes	-32768 到 32767	0 到 65535
MEDIUMINT	3 Bytes	-8388608 到 8388607	0 到 16777215
INT或INTEGER	4 Bytes	-2147483648 到 2147483647	0 到 4294967295
BIGINT	8 Bytes	-9223372036854775808 到 9223372036854775807	0 到 18446744073709551615
FLOAT	4 Bytes	-3.402823466E+38 到 3.402823466E+38	反正很大，略
DOUBLE	8 Bytes	-1.7976931348623157E+308 到 1.7976931348623157E+308	反正很大，略
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值

日期和时间类型

类型	大小 ( bytes)	范围	格式
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS
YEAR	1	1901/2155	YYYY
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss

字符串类型

类型	大小
CHAR	0-255 bytes
VARCHAR	0-65535 bytes
TINYBLOB	0-255 bytes
TINYTEXT	0-255 bytes
BLOB	0-65 535 bytes
TEXT	0-65 535 bytes
MEDIUMBLOB	0-16 777 215 bytes
MEDIUMTEXT	0-16 777 215 bytes
LONGBLOB	0-4 294 967 295 bytes
LONGTEXT	0-4 294 967 295 bytes

root改密码登录

Mysql安装完成以后，默认的root用户使用 auth_socket 验证方式，而不是caching_sha2_password密码方式，可以使用这个命令来验证一下：

1

select authentication_string ,User,Host,plugin from user;

而auth_socket是根据当前登录用户来验证身份的，所以无法使用密码方式登录Mysql。所以要想Mysql可以通过密码登录那就需要修改其plugin为 caching_sha2_password 。可以使用下面的语句来修改：

1
2


alter user 'root'@'localhost' identified with caching_sha2_password by '12345';
flush privileges;

有一些网站上的教程说使用 mysql_native_password 作为验证插件，这也可以，但是不推荐，不到万不得已还是用新的 caching_sha2_password 好一点。

字符集

在使用Mysql的时候经常会遇到各种中文字符乱码的问题，那就可能是charset设置有错误，所以在创建数据库的时候就推荐：

1

create database xxx default charset utf8mb4;

密码策略

当修改密码但是不符合Mysql的策略时，可能会遇到下面的错误： ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 但是你依然想要修改密码，好的！下面的sql语句可以帮到你：

1
2
3
4


SHOW VARIABLES LIKE 'validate_password%';
# 可以修改以下参数，来设置弱密码，虽然不建议，但是用起来真的很爽！😍
SET GLOBAL validate_password.policy=LOW;
SET GLOBAL validate_password.length=4;

查询数据库版本

1

select version()

数据库操作

1
2
3
4
5


create database (if not exists) xxx (default charset utf8mb4);
drop database (if exists) xxx;
show databases;
select database();
use xxx;

数据表结构操作

查询相关操作

1
2
3


show tables;
desc xxx;
show create table xxx;# 查询创建表的语句

创建相关操作

1
2
3
4
5
6


create table xxx(
	field type (comment '注释'),
	field type (comment '注释'),
    ... ...
	field type (comment '注释')
)[comment '注释']

修改相关操作

添加字段

1

alter table xxx add field type (comment 'xxx');

修改字段

1
2


alter table xxx modify field new_type;# 只修改type
alter table xxx change field new_field type (comment 'xxx');# 修改field和type

删除字段

1

alter table drop field;

修改数据表的名称

1

alter table xxx rename to xxx;

删除相关操作

1
2


drop table (if exists) xxx;
truncate table xxx;# 清空所有数据

数据表数据操作

插入相关操作

给指定字段插入数据

1

insert into xxx (field1 field2...)values(value1,value2...);

给全部字段插入数据

1

insert into xxx values(value1,value2...);

批量插入数据

1
2


insert into xxx (field1 field2...)values(value1,value2...),(value1,value2...),(value1,value2...);# 批量给指定字段插入数据
insert into xxx values(value1,value2...),(value1,value2...),(value1,value2...);# 批量给所有字段插入数据

更新相关操作

1

update xxx set field1=value1, field2=value2, ... (where 条件)# 没有where表示修改整个表

删除相关操作

1

delete from xxx (where 条件)# 不加条件就是删除所有的数据

查询相关操作

下面是一个包含了各种情况的数据查询语句，sql里面查询是使用次数最多的，当时为了应对各种各样的需求，就有了各种各样的查询语句。

1

select field1,field2 from xxx where 条件 group by 条件 having 条件 order by 条件 limit 条件

基本查询

1
2
3
4


select field1, field2, ... from xxx
select field1 as a, field2 as b, ... from xxx# 设置别名
select * from xxx;
select distinct (* | field) from xxx;# 去重查询

条件查询(where)

1

select field1, field2, ... from xxx where 条件

具体有哪些条件可以看下面的：

>、 <、 =、!=、between x and x、in(x)、like、is null、and && 、or || 、**not !**等

1
2
3


select * from xxx where age in (10,20,30);
select * from xxx where age = 10 or age = 20 or age = 30
# 上面两种写法一个意思，in就是来简化这种多or情况的

1
2
3


select * from xxx where age between 20 and 40;
select * from xxx where age >= 20 and age <=40;
# 这两条语句也是一样的between...and...是包括查询的数据本身的

1
2


select * from xxx where name like '__';# 查询任意满足两个字符的的数据；
select * from xxx where name like '张%'# 查询张开头的；

聚合函数

min、max、sum、count、avg

作用于数据表里所有的字段，很简单就不多介绍了，一个例子就够了

1

select sum(age) from xxx;

分组查询(group by)

1

select * from xxx (where 条件) group by field (having 条件)

其中 where 里面不可以使用聚合函数，但是 having 里面可以

1
2


select city,count(city) from xxx where age > 20 group by city having count(city) > 10;
# 查询年龄大于20岁的，并且分组后city的数量大于10的记录

排序查询(order by)

也很简单就 order by + 条件

1
2


select * from xxx order by age,name desc;# 不写默认是asc升序
# 先按field1排序，再按field2排序

分页查询(limit)

limit 的用法很简单，limit 起始索引，页面展示数

1
2


select * from xxx limit 10;# 从0开始查询10条数据
select * from xxx limit 10,20;# 从第十条数据开始，第二页数据展示20条数据；

SQL冷知识：虽然我们的sql编写顺序为 select -> from -> where -> group by -> having -> order by -> limit

但是真正的执行顺序为 from -> where -> group by -> having -> select -> order by -> limit 也就是说在前面执行的别名可以在后面执行过程中使用，而没有执行的别名无法在前面使用

用户管理

创建用户

1
2


use mysql;
create user 'f1'@'localhost' identified by '123456';# 如果需要任意主机登录用%来替换localhost；

修改用户

1
2
3


use mysql;
alter user 'f1'@'%' identified with mysql_native_password by 'xxx';# 这个是MySQL之前的plugin存储密码的方式
alter user 'f1'@'%' identified with caching_sha2_password by 'xxx';# 这个是最新的Mysql存储密码的方式

删除用户

1
2


use mysql;
drop user 'f1'@'localhost';

权限管理

首先来介绍一下Mysql里面常见的那些权限：

all/all privileges	所有权限
select	查询记录
insert	插入记录
update	修改记录
delete	删除记录
alter	修改表
drop	删除库/表/视图
create	创建库/表

查询用户权限

1

show grants for 'f1'@'%';

授权用户

1

grant select on scheme.table to 'f1'@'%';# 如果需要给所有数据库都授权，那就用 *.* 来替代 schema.table

取消权限

1

revoke update on schema.table from 'f1'@'%';

SQL函数

字符串函数

函数名称	功能
concat(x)	拼接字符串
lower(x)	转小写
upper(x)	转大写
lpad(x,y,z)	👈左填充
rpad(x,y,z)	👉右填充
trim(x)	去除头尾空格
substring(x,y,z)	切分字符串

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


select concat("你好"," halo");
#+-------------------------+
#| concat("你好","halo")   |
#+-------------------------+
#| 你好halo                |
#+-------------------------+

select lower("QWER");
#+---------------+
#| lower("QWER") |
#+---------------+
#| qwer          |
#+---------------+

# upper()略

select lpad("12",5,"#");
#+------------------+
#| lpad("12",5,"#") |
#+------------------+
#| ###12            |
#+------------------+
# 一个参数为需要填充的字符串，第二个参数是填充以后的总字符，第三个参数是以什么字符串填充

# rpad()略

select trim("            拿 你号 ");
#+---------------------------------+
#| trim("            拿 你号 ")    |
#+---------------------------------+
#| 拿 你号                         |
#+---------------------------------+

select substring("ABC的风格",2,3);
#+-------------------------------+
#| substring("ABC的风格",2,3)    |
#+-------------------------------+
#| BC的                          |
#+-------------------------------+

注意substring和别的语言不一样，第二个参数表示从哪里开始，sql里面是从索引是从1开始，也就是说，第一个就是1，第二个就是2。第三个表示要截取几个字符这个和别的语言一样。

那么这些函数怎么使用呢？

1
2


update xxx set id = lpad(id,6,0);
# 该操作是把原来的id补0；还有一个前提，修改的field是字符串哈！

数值函数

函数名称	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的余数
rand()	随机数0-1
round(x,y)	x四舍五入保留y位小数

下面这个小例子生产随机6位数就用到了上面的函数。

1

select lpad(round(rand()*1000000,0),6,"0");

日期函数

curdate()	获取当前日期
curtime()	获取当前时间
now()	获取当前日期时间
year(date)	获取date的年
month(date)	获取date的月
day(date)	获取date的日
date_add(date,interval x type)	获取date x 个type后的日期
datediff(date,date)	获取两个date间隔的时间

一个小例子就可以了

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


select concat(curdate()," | ",curtime()," | ",year(now())," | ",date_add(now(),interval 30 day));
#+-------------------------------------------------------------------------------------------+
#| concat(curdate()," | ",curtime()," | ",year(now())," | ",date_add(now(),interval 30 day)) |
#+-------------------------------------------------------------------------------------------+
#| 2023-03-10 | 16:09:24 | 2023 | 2023-04-09 16:09:24                                        |
#+-------------------------------------------------------------------------------------------+

select datediff(curdate(),"2021-11-11");
#+----------------------------------+
#| datediff(curdate(),"2021-11-11") |
#+----------------------------------+
#|                              484 |
#+----------------------------------+

select name,datediff(curdate(),birthday) as bd from user order by bd desc;

流程控制

if(value,t,f)	value为true返回t，否则返回f
ifnull(value1,value2)	value1不为空返回value1，否则返回value2
case when value1 then res1… else x end	value1为true，返回res1… 否则返回x
case when expr then res1… else x end	expr = true，返回res1… 否则返回x

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


select name,case address when "上海" then "大城市" when "安徽" then "小城市" end as "所住城市规模" from users;
#+--------+--------------+
#| name   | 所住城市规模  |
#+--------+--------------+
#| TianLu | 小城市       |
#| HeTian | 大城市       |
#+--------+--------------+

# 把上面的写法换成下面的也可以

select name,case when address = "上海" then "大城市" when address = "安徽" then "小城市" end as "所住城市规模" from users;
#+--------+--------------+
#| name   | 所住城市规模  |
#+--------+--------------+
#| TianLu | 小城市       |
#| HeTian | 大城市       |
#+--------+--------------+

约束

非空约束	not null
唯一约束	unique
主键约束	primary key
默认约束	default
检查约束（8.0.16）	check
外键约束	foreign key

自增 auto_increment

外键约束

字段可以同时有多个约束，别的约束都简单，那就都杂糅在外键约束里面记录了。

先说一下外键约束的作用：可以保证数据的一致性和完整性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


create table school_t1(
	name varchar(20) unique not null,
    sid varchar(6) primary key
);

insert into school_t1 values ("安徽大学","ah003"),("江西理工大学","jx005");

create table user_t1(
	name varchar(10) not null,
    age int not null default 18 check(age > 18 and age < 80),
    uuid varchar(20) primary key,
    city varchar(20) not null,
    phone char(11) not null unique,
    user_name varchar(30) unique,
    school varchar(20),
    constraint fk_school foreign key (school) references school_t1(sid)#这是在创建表的时候就添加约束
);

insert into user_t1 values("frelon",23,"w2f5-gsa-f3f","安徽",'12345678912',null,"ah003"),("Jing",22,"adwb-ser2-saw","江西",'12345678911',null,"jx005");

# 而当试图插入外键约束不允许的值就会报错
insert into user_t1 values("jsts",26,"wrde-pna-bny","安徽",'12345678916',null,"ah006");
# ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ut`.`user_t1`, CONSTRAINT `fk_school` FOREIGN KEY (`school`) REFERENCES `school_t1` (`sid`))

创建外键约束

还可以在表创建以后再添加约束

1
2
3


alter table user_t1 add constraint fk_school foreign key(school) references school_t1(sid);
# 还可以在创建表的时候写像下面这样写
constraint fk_school foreign key (school) references school_t1(sid)

删除外键约束

1

alter table user_t1 drop foreign key fk_school;

删除/更新行为

一般这种 连级操作 都是并不允许使用的。

默认有以下几种行为RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

restrict 和 no action 一样！更新记录时，如果没有对应的外键记录，则不允许更新。删除时，还存在着外键记录，则也一样不允许删除。

cascade 更新/删除时会检查外键，如果有那也一起更新/删除。

set null 更新/删除时，把外键相应记录设置为null。

set default 更新/删除时，把外键设置为default。

设置也很简单，只需要在设置外键的后面加上 on update on delete 就可以了。

1

alter table user_t1 add constraint fk_school foreign key(school) references school_t1(sid) on update no action on delete no action;

多表查询

最简单的多表查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


create table vip_users(
	name varchar(10) not null,
	age tinyint,
	sign_way char(1),
	vip_level char(1) default 1,
	cellphone char(11) unique,
	sign_date datetime
);

insert into vip_users (name,age,sign_way,cellphone,sign_date) values ("frelon",23,'a',12345678901,now()),("fbi",44,'b',12345678902,now()),("Tom",56,'d',12345678903,now());

create table sign_way(
	sign_way_id char(1) primary key,
	sign_way varchar(20)
):

insert into sign_way values ('a',"ads"),('b',"朋友推荐"),('c',"自主下载"),('d',"强制安装");

alter table vip_users add constraint fk_sign_way foreign key (sign_way) references sign_way(sign_way_id);

# 懂得都懂

1

select * from vip_users,sign_way where vip_users.sign_way = sign_way.sign_way_id;

这就是最简单的多表查询也就是隐式内连接。

内连接

用于查询两表交集的部分的数据

1
2


select * from table1,table2 where 条件; # 隐式内连接
select * from table1 (inner) join table2 on table1.x = table2.y; # 显式内连接

以上面创建的表为例，使用内连接查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


select * from vip_users,sign_way where vip_users.sign_way = sign_way.sign_way_id;
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| name   | age  | sign_way | vip_level | cellphone   | sign_date           | sign_way_id | sign_way     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| frelon |   23 | a        | 1         | 12345678901 | 2023-03-10 22:05:23 | a           | ads          |
#| fbi    |   44 | b        | 1         | 12345678902 | 2023-03-10 22:05:23 | b           | 朋友推荐     |
#| Tom    |   56 | d        | 1         | 12345678903 | 2023-03-10 22:05:23 | d           | 强制安装     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+

select * from vip_users as vip join sign_way as way on vip.sign_way = way.sign_way_id;
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| name   | age  | sign_way | vip_level | cellphone   | sign_date           | sign_way_id | sign_way     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| frelon |   23 | a        | 1         | 12345678901 | 2023-03-10 22:05:23 | a           | ads          |
#| fbi    |   44 | b        | 1         | 12345678902 | 2023-03-10 22:05:23 | b           | 朋友推荐     |
#| Tom    |   56 | d        | 1         | 12345678903 | 2023-03-10 22:05:23 | d           | 强制安装     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+

外连接

left join

left join 就是完全包含 left 部分的表，还包含两表重合部分的表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


insert into vip_users (name,age,cellphone,sign_date) values ("Joker",11,12345678904,now());

select * from vip_users v left join sign_way s on v.sign_way = s.sign_way_id;
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| name   | age  | sign_way | vip_level | cellphone   | sign_date           | sign_way_id | sign_way     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| frelon |   23 | a        | 1         | 12345678901 | 2023-03-10 22:05:23 | a           | ads          |
#| fbi    |   44 | b        | 1         | 12345678902 | 2023-03-10 22:05:23 | b           | 朋友推荐     |
#| Tom    |   56 | d        | 1         | 12345678903 | 2023-03-10 22:05:23 | d           | 强制安装     |
#| Joker  |   11 | NULL     | 1         | 12345678904 | 2023-03-10 22:28:14 | NULL        | NULL         |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+


 select * from vip_users v join sign_way s on v.sign_way = s.sign_way_id;
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| name   | age  | sign_way | vip_level | cellphone   | sign_date           | sign_way_id | sign_way     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| frelon |   23 | a        | 1         | 12345678901 | 2023-03-10 22:05:23 | a           | ads          |
#| fbi    |   44 | b        | 1         | 12345678902 | 2023-03-10 22:05:23 | b           | 朋友推荐     |
#| Tom    |   56 | d        | 1         | 12345678903 | 2023-03-10 22:05:23 | d           | 强制安装     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+

可以看到虽然新添加的Joker用户没有sign_way，但是 left join 也查询出来了，但是如果我们使用 join 就查不到该用户的数据了。

right join

right join 就是完全包含 right 部分的表，还包含两表重合部分的表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


insert into vip_users (name,age,sign_way,cellphone,sign_date) values ("Jing",11,'a',12345678905,now());

select * from vip_users v right join sign_way s on v.sign_way = s.sign_way_id;
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| name   | age  | sign_way | vip_level | cellphone   | sign_date           | sign_way_id | sign_way     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+
#| frelon |   23 | a        | 1         | 12345678901 | 2023-03-10 22:05:23 | a           | ads          |
#| Jing   |   11 | a        | 1         | 12345678905 | 2023-03-10 22:33:18 | a           | ads          |
#| fbi    |   44 | b        | 1         | 12345678902 | 2023-03-10 22:05:23 | b           | 朋友推荐     |
#| NULL   | NULL | NULL     | NULL      | NULL        | NULL                | c           | 自主下载     |
#| Tom    |   56 | d        | 1         | 12345678903 | 2023-03-10 22:05:23 | d           | 强制安装     |
#+--------+------+----------+-----------+-------------+---------------------+-------------+--------------+

可以看到 right join 就会把右边的表的数据都查不来，null也给你展示，而左表之前新建的Joker因为sign_way为null，就没有查询出来。