MySQL个人笔记
卢阳成
MySQ_8.0.21 安装
下载二进制包并解压至指定目录
设置环境变量
配置 /etc/my.cnf
# 以下为简化版配置：
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
character-set-server=UTF8MB4
port=3306
user=mysql
socket=/var/lib/mysql/mysql.sock
# 取消客户端域名解析，可提高连接速度
skip-name-resolve
skip-symbolic-links
[mysqld_safe]
log-error=/var/log/mysql.log
pid-file=/var/run/mysql.pid
初始化
mysqld --initialize # 初始化 会在最后生成初始密码
mysql_ssl_rsa_setup # 设置加密连接 可选
登录并修改初始密码
mysql -u root -p # 使用初始化生成的密码登录
alter user 'root'@'lcoalhost' identified by 'newpassword'; # 更新密码
加入 systemctl 服务项
cd /usr/lib/systemd/system
vim mysql.service
[Unit]
Description=mysql
After=network.target remote-fs.target nss-lookup.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecReload=/usr/local/mysql/support-files/mysql.server restart
ExecStop=/usr/local/mysql/support-files/mysql.server stop
PrivateTmp=true
[Install]
WantedBy=multi-user.target
# 重新加载服务配置文件
systemctl daemon-reload
systemctl enable mysql # 开机自启
systemctl start mysql # 启动
systemctl stop mysql # 关闭
systemctl restart mysql # 重启
systemctl status mysql # 查看状态
添加远程访问权限
# mysql8.0版本需要先添加用户，再设置权限
# use mysql;
create user 'username'@'host' identified by 'password';
grant all privileges on database.table to 'username'@'host' [with grant option];
flush privileges;
防火墙允许3306端口，使用navicat等工具连接
一般命令
desc database.table; # 显示表概述，类似DataFrame.describe()
show grants ['username'@'ip']; # 显示当前用户或指定用户的权限信息
查询命令
select field [as] newname from table; # 通过as为字段或表赋予别名，as可省略
select distinct field1,field2 from table; # 查询唯一值，只能放在字段前面，只能作用于全局
select field from table limit n; # 查询前n行
select field from table limit start step; # 从start开始进行步长为step的查询【从0开始！】
select table.field from database.table; # 字段名可以用表名限定、表名可以用数据库名限定
select * from table order by field1 desc,field2; # 先按field1降序排序，再按field2升序排序
select * from table where field is null; # 条件查询，field为null的行
select * from table where field in (12,13); # 查询field为12或13的列
select * from table where field not in (12,13); # not 对条件取反
# 通配符查询 常与 like 搭配
# 百分号% 任意字符出现任意次数
select * from table where field like 'fff%'; # 查询以 fff 开头
# 下划线_ 任意字符出现一次
select * from table where field like '_fff'; # 查询 afff、0fff等
# 正则表达式查询 常与 regexp 搭配
select * from table where field regexp 'fff'; # 查询 field包含fff的行
# 注意，上式如果将 regexp 换成 like，则会查询 field=fff的行
# 若要使用 regexp 达到同样效果，可使用；
select * from table where field regexp '^fff$'; # 表示以f开头且以f结尾
# 小数点. 表示任意字符出现一次
select * from table where field regexp '.fff'; # 查询包含 aff、3fff等
# 竖杠| 表示or
select * from table where field regexp '233|666'; # 查询包含233 或666的行
# 上式可用中括号[]限定
select * from table where field regexp '[233|666]'; # 查询包含233 或666的行
# 在某些情况下可以省略中间的竖杠
select * from table where field regexp '[24]'; # 查询包含2 或4的行
# [1-5] = [12345] = [1|2|3|4|5] = '1|2|3|4|5' 表示匹配1到5之间的任意一个
# 同理 [b-h] 表示匹配b到h之间的任意一个
# 查询有歧义时，使用 \\ 转义
select * from table where field regexp '\\.'; # 查询包含小数点的行
select * from table where field regexp '\\\'; # 查询包含 \ 的行
# 正则固定用法
[:alnum:] # 任意大小写字母或数字 = [a-zA-Z0-9]
[:alpha:] # 任意大小写字母 = [a-zA-Z]
[:digit:] # 任意数字 = [0-9]
[:lower:] # 任意小写字母 = [a-z]
[:upper:] # 任意大写字母 = [A-Z]
[:space:] # 任意空白字符
# 匹配多次出现
# * 出现任意次数
# + 至少出现一次
# ? 出现一次或没有
# {n} 出现n次
# {n,} 至少出现n次
# {n,m} 出现n次 或 n+1次……或m次 m需要小于255
[[:digit:]]{4} 匹配4个数字，为什么？
# 正则位置匹配
^ # 匹配开头
$ # 匹配结尾
[[:<:]] # 匹配词的开头
[[:>:]] # 匹配词的结尾
# 注意！[^abc] 表示对集合取反，即对 [abc]取反
# 计算字段
# concat(field,string,symbol) # 字符拼接
select concat(field1,':',field2) from table;
# 加减乘除
# 常用文本处理函数
left() # 返回左边指定长度部分
right() # 返回右边指定长度部分
length() # 返回串长度
ltrim() # 去掉左边空格
rtrim() # 去掉右边空格
lower() # 转为小写
upper() # 转为大写
# 常用时间处理函数
curdate() # 当前日期 年月日
curtime() # 当前时间 时分秒
now() # 当前日期和时间 年月日时分秒
date() # 返回日期
time() # 返回时间
year() # 日期的年份
month()
day()
dayname() # 返回星期几（一周从周日开始）
dayofweek() # 返回一周索引（周日为0）
hour() # 时间的小时
minute()
second()
adddate() # 增加日期或时间 adddate(curdate(),interval 1 day)
date_add()
datediff() # 日期天数差 datediff('2020-9-10','2020-9-20')
avg() # 忽略null
min()
max()
count()
count(*) #返回行数，包括null
count(列名) # 返回非空值
sum()
sum(A*B) # 也可以合计计算字段
# 分组 group by
# 过滤分组用 having condition
select sex,count(*) from table group by sex having avg(age)>20;
# 先where 再group by 最后having
# where在分组前对行进行过滤
# having在分组后对组进行过滤
# 使用联结
join # 内连接
left join # 左连接，左边为主表，左表所有记录都存在
right join # 右连接，右边为主表，右表所有记录都存在
# 插入数据
insert into table (field1,field2) values (value1,value2),(value1,value2);
# 插入select 数据，字段按位置对应
insert into table (field1,field2) select (field1,field2) from othertable;
# 更新表
update [ignore] table set field1 = value1,field2 = value2 where condition;
# 删除表中某些行
delete from table where condition;
# 删除整张表
drop table tablename;
# 重命名表
rename table oldname to newname,oldname1 to newname1,……
# 更改表结构，增加、删除或修改列
alter table sometable [操作语句]
alter table sometable add column newcol int [first/after somecol]; # 在指定位置增加列，并设置数据类型，默认加到最后面
alter sometable change column oldname newname datatype; # 更换列名并修改类型，类型不允许为空
alter table sometable modify somecol newtype; # 更改字段数据类型
alter table sometable drop somecol; # 删除字段
alter table oldname rename [to] newname; # 表改名
# 视图：一种对查询语句的封装，可以重用，可以从创建好的视图中查询数据，简化查询流程（类比python中的函数）
# 创建视图（不存在则创建，否则更新）
create or replace view name as select语句
# 存储过程：也是对查询语句的封装，与视图相比，可以传入参数或输出变量，更像python函数
# 注意：存储过程必须在一开始就声明所有需要的变量！
# 创建存储过程，根据传入条件，返回计数
create procedure mypro(
in field1_name str, # in 表示调用时需要传入的参数，需要在参数名后面指定类型
out n int) # out 表示调用时会输出的变量
begin # begin 表示过程体开始
select count(*) into n from table_name where field1 = field1_name; # 使用into保存变量
end; # end 表示过程体结束
# 另需注意：过程体内的分号与end后面的分号容易引起歧义 使用 delimiter // 指定双斜杠为mysql分隔符，在end后面使用双斜杠// 同时过程体内照常使用分号
# 调用存储过程
call procedure mypro('balabala',@count); # 传入参数直接写，输出变量需要在变量名前加@
# 存储过程被调用后，@count变量就存储了field1=‘balabala’的计数信息 使用select 查看
select @count;
# 查看存储过程
show create procedure mypro;
show procedure status like 'mypro';
# 删除存储过程
drop procedure mypro;
# MySQL三种循环：while loop repeat
# 计算1到n的累加
# while循环
create procedure getsum1(n int)
begin
declare sum int default 0;
declare i int default 1;
while i<=n do
set sum=sum+i;
set i=i+1;
end while;
select sum;
end;
# loop循环
create procedure getsum2(n int)
begin
declare sum int default 0;
declare i int default 1;
add_loop:loop
set sum=sum+i;
set i=i+1;
if i>n then
leave add_loop;
end if;
end loop;
select sum;
end;
# repeat循环
create procedure getsum3(n int)
begin
declare sum int default 0;
declare i int default 1;
repeat
set sum=sum+i;
set i=i+1;
until i>n end repeat;
select sum;
end;
# 游标：可以对遍历查询结果集的每一行，并执行相应操作，常与循环搭配使用
create procedure cur()
begin
declare i int DEFAULT 1;
declare id int;
declare done boolean default false; # done:循环结束的标志
declare cc CURSOR for SELECT hostid from hosts; # 创建游标
declare continue handler for not found set done=1; # 找不到下一行数据时，设置done为真
open cc; # 打开游标
repeat # 循环
fetch cc into id; # 将游标数据赋给id
select id; # 输出id
#set i=i+1;
#until i=10 end repeat; # 停机条件
until done end repeat;
close cc; # 关闭游标
end;
# 触发器 可设置在增删改前后自动执行
# 事务：成批的sql语句，必须一起执行
# 使用事务可以回退之前的状态
# 既可以全盘还原，也可以设定还原点并还原
start transaction; # 开启事务
# sql语句
# 程序若在此时被迫终止，以上所有语句都不会真正生效
commit; # 使以上所有语句真正生效，同时结束该事务
rollback; # 撤销以上所有语句，同时结束该事务
# 事务中间可以穿插检查点
savepoint s1;
rollback to s1; # 回退到检查点
release savepoint s1;# 删除检查点
# case when 语句
select
case id
when >10 then 1
else 0
end as newid
from table;
全文本搜索（有别于Like和正则）
！进行全文本搜索，必须事先对要搜索的列建立全文本索引
select * from table where match(field) against('str'); # 查找field字段包含str的记录，返回数据按相关性排序
# 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
# 默认不区分大小写
排序
DENSE_RANK()
连续排序，重复值排名相同，两个第二名跟着第三名
DENSE_RANK() OVER( [PARTITION BY field1] ORDER BY field2)
#以field2排序，以field1分割，比如根据学号进行单独的排序
RANK()
跳跃排序，重复值排名相同，两个第二名跟着第四名
RANK() OVER( [PARTITION BY field1] ORDER BY field2)
#以field2排序，以field1分割，比如根据学号进行单独的排序
ROW_NUMBER()
递增排序，重复值排名递增
ROW_NUMBER() OVER([PARTITION BY FIELD1] ORDER BY FIELD2)
#以field2排序，以field1分割，比如根据学号进行单独的排序
MySQL自定义变量
系统变量——两个@前缀
全局变量
会话变量
自定义变量
局部变量
定义在存储过程、函数等中的变量，只在调用过程中有效，用declare声明变量类型
declare var_demo int [default 2]; # 如果不设置默认值，则为NUll
用户变量——一个@前缀
只在当前连接有效，关闭客户端或退出连接后失效
局部变量和用户变量区别
局部变量只有变量名字，没有@符号；用户变量名前有@符号。
都是先定义，再使用；未定义的变量，select值为空。
局部变量只在存储过程内部使用，在过程体外是没有意义的，当begin-end块处理完后，局部变量就消失；而用户变量可以用在存储过程的内部和外部。
一般使用 := 为变量赋值，在不会引起歧义的情况下，也可以使用 = 赋值
例：
set @a; # 直接定义变量@a，值为null
set @a=3; # 把3赋给@a
set @a=@a+1 # 把@a+1赋给@a
# 使用set直接赋值，不返回结果
select @b:=4 # 把4赋给@b，这里不能用 = ，否则会被视为是否相等的判断语句
select 4 into @b; # 把4赋给@b (直接赋值，不返回结果)
select @c:=id from table; # 相当于多次赋值，最终结果为最后一次赋值，即最后一条记录中的id值
# （赋值的同时返回结果）
MySQL数据类型
整数类型
| 类型 | 大小 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 字节 | -128 to 127 | 0 to 255 |
| SMALLINT | 2 字节 | -32768 to 32767 | 0 to 65535 |
| MEDIUMINT | 3 字节 | -8388608 to 8388607 | 0 to 16777215 |
| INT | 4 字节 | -2147483648 to 2147483647 | 0 to 4294967295 |
| BIGINT | 8 字节 | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 |
浮点数类型
| 类型 | 大小 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| FLOAT | 4字节 | -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38 | 0, and 1.175494351E-38 to 3.402823466E+38 |
| DOUBLE | 8字节 | -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308 | 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308 |
| DECIMAL | ？ | 根据M和D的值 | 根据M和D的值 |
decimal(8,3) # 最多8位数字，其中3位小数
float和double只能近似计算
decimal可以精确计算，但精确计算代价高
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| BINARY | 0-255 字节 | 定长二进制 |
| VARBINARY | 0-65535 字节 | 变长二进制 |
| TINYBLOB | 0-255 字节 | 变长二进制 |
| BLOB | 0-65535 字节 | 变长二进制 |
| MEDIUMBLOB | 0-16,777,215 字节 | 变长二进制 |
| LONGBLOB | 0-4,294,967,295 or 4GB (232 − 1) 字节 | 变长二进制 |
| TINYTEXT | 0-255 字节 | 变长字符串 |
| TEXT | 0-65535 字节 | 变长字符串 |
| MEDIUMTEXT | 0-16,777,215 字节 | 变长字符串 |
| LONGTEXT | 0-4,294,967,295 or 4GB (232 − 1) 字节 | 变长字符串 |
日期和时间类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 字节 | ‘1000-01-01’ to ‘9999-12-31’ | YYYY-MM-DD | 日期值 |
| TIME | 3 字节 | ‘-838:59:59’ to ‘838:59:59’ | hh:mm:ss | 时间值 |
| YEAR | 1 字节 | 1901 to 2155 | YYYY | 年份值 |
| DATETIME | 8 字节 | ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 日期和时间值 |
| TIMESTAMP | 4 字节 | ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC | YYYY-MM-DD hh:mm:ss | 日期和时间值 |
类似资料：