MySQL笔记
MySQL笔记
- 记录一下必知必会的MySQL命令,挂一漏万,持续补充
直接用图形界面也挺香的虽然说工作中说不定只能使用最基本的crud(create,read,update,delete)
连接MySQL服务器
- 在系统的命令行窗口使用mysql命令,常用参数如下,具体细节可用help查看
- -h:host,指定mysql所在的地址,默认为localhost
- -u:user,指定登录的用户,默认为root
- -p:password,用户对应的密码,默认为直接输入
- e.g.密码为aaaa,注意这里要-paaaa,而不是-p aaaa
- -P:port,端口号,默认为3306
- 例子如下
mysql -h 115.159.92.155 -P 3306 -u root -paaaa
- 远程连接下
- 直连
- 服务器主机应当开放对应的数据库端口,这可能涉及两方面,一个是服务器系统(如腾讯云)的端口,另一个是主机防火墙的端口
- 数据库应该授予某段IP登录该用户的权限(如使用'%')
- 使用SSH作为跳板连接
- 先使用密码或公钥连接SSH
- 再在该命令行界面登录MySQL
- 该方法主要出于安全性的考虑
- 直连
- 或者使用Navicat/workbench的图形界面进行配置
基本操作
- MySQL语句不区分大小写
- MySQL每条命令必须以';'结尾
- 所有命令都可以使用help命令帮助回忆或查找,或者直接查阅官方文档
- e.g. help 'show';
- 注释 --
- 打印字符串
select 'ybb';
- 显示所有的数据库
show databases;
- 选取使用一个数据库,如这里选取的是nodejs_test
mysql> use nodejs_test;
- 显示该数据库中所有的表
show tables;
- 显示某表的列信息,如这里选取的是test
show columns from test;
- desc命令也能实现一些类似的功能
SELECT
- 例子
select * from wp_users;
select ID from wp_users; -- 也可以选多列
select distinct ID from wp_users; -- 过滤重复
- limit
select ID from wp_users limit 5; -- 即0-4
select ID from wp_users limit 5,5; -- 即5~9,从下标5开始的5行
- 排序
select ID from wp_users order by ID; -- 默认升序asc
select ID from wp_users order by ID desc; -- 降序,也可以选多个属性进行排序
-- 文本排序的问题取决于数据库设置
- 运算和逻辑
select * from wp_users where ID=1; -- 还有<,>,!=,<>,<=,>=
select * from wp_users where ID between 1 and 2; -- 闭区间
-- 和C类似,MySQL中的逻辑运算符中and的优先级比or高
select * from wp_users where ID in (1,2); --in和or功能上是一样的,但一般会更快(O(logn)>O(n))
-- MySQL中的not支持对between,in,exists取反
- NULL
- 检查空值语句is null
- NULL值一般具有特殊含义,它被当做例外,在匹配过滤(包括like)或不匹配过滤时都不返回
- like&通配符,regexp&正则表达式
- 匹配中的大小写问题取决于数据库设置
- like和regexp的重要区别在于like是对整个文本进行匹配,而regexp是在整个文本内寻找匹配;但正则表达式是更强大的表达,可以使用定位符来实现like的效果
- 正则表达式详见编译原理
通配符 | 正则表达式 | 含义 |
---|---|---|
% | .* | 任何字符出现任意次数 |
_ | . | 任何字符出现一次 |
select * from wp_users where ID like '_';
select * from wp_users where ID regexp '.';
- 计算字段
- 基本数学运算
- 一些常用函数
- concat():拼接字符串
- trim():去掉文本两边空格,类似的还有ltrim(),rtrim()
- length():串长
- upper():转为全大写,类似的有lower()
- 日期和时间处理函数:众多,详见官网
- 数值处理函数:abs(),cos(),exp(),mod(),pi(),rand(),sqrt()等
- 不同数据库系统对函数的支持不一样,可移植性比SQL语句差
- 别名as
select concat(ID,': ',user_login) as user from wp_users;
- 聚集
- 聚集函数:avg(),count(),max(),min(),sum()
- 聚集函数可以搭配distinct使用
select avg(distinct ID) as avg_id from wp_users;
- 分组group by
- 一般会配合聚集使用,对每个组分别进行聚集
- having VS where
- where过滤的是行,在分组前过滤
- having过滤的是组,在分组后过滤
- with rollup可以产生所有分组的汇总数据,或者理解为不分组的聚集数据;当依据多个列进行分组时,with rollup会嵌套地依次产生多次汇总数据
select ID,user_login,avg(id) from wp_users group by ID,user_login with rollup;
- 语法顺序
- select from where group by having order by limit
- 嵌套查询
- 子查询可以作为过滤条件中in的范围,也可以作为计算字段
- 需要注意列的歧义问题,可以使用as消歧
- 嵌套查询的效率和可读性一般都会更好
- 联结
- 用于处理多个表相关的信息
- 使用时临时创建联结表,比较耗费资源,不建议联结太多表
- 可以用as为联结表取别名;MySQL支持自联结;也需要注意列的歧义问题
- \(联结 \begin{cases} 联结条件:针对属性 \begin{cases} natural \\ using \end{cases} \\ 联结类型:针对元组\begin{cases} inner(内联结) \\ left(right)\ outer\ join(左(右)外联结)\\ full\ outer\ join(全外联结)\\ on \end{cases} \end{cases}\)
- 以下内容涉及一些关系代数的知识
- natural:自动比对相同属性,取属性的交集
- 自然联结\(\Join\):特指natural inner join,MySQL中为natural join
- using:指定联结属性
- on:指定联结谓词进行过滤,外联结时条件不为真会返回含null的元组
- 和where的区别:on在联结时过滤,where在联结后过滤;
- 内联结 :丢弃不匹配的元组;在MySQL中需要手动实现,即在笛卡尔积的基础上加上过滤
- 外联结:
- 左(右)联结=\(\Join(\Join=)\):用Null补充右(左)集合中匹配不上的属性,即保留所有左(右)边的元组,MySQL中为right(left) join
- 全联结\(=\Join=\):左右同时补充,左右元组都保留;在MySQL中需要手动实现,用左右外连接的union替代
- 必须配合on或natural使用
- 联结\(\theta:r\Join_\theta s=\sigma_\theta(r\times s)\),笛卡尔积,MySQL中为','或join或inner join或cross join
select name from vendors, products; -- 笛卡尔积
select name from vendors, products on vendors.name=products.name; -- 内联结(如果两表只有name一个相同属性)
select name from vendors, products natural join products; -- 自然联结
select name from vendors, products letf join products on vendors.name=products.name; -- 左外联结
select name from vendors, products natural letf join products; -- 自然左外联结
- 组合
- 并集union,并集保留重复的行union all,可能存在隐式类型转换
- 交集,差集在MySQL中需要手动实现;交集可以通过内联结实现,差集可以通过左/右外联结实现
select name from vendors union select name from products;
- 全文本搜索
- 在MySQL中不同引擎对此支持不同InnoDB在MySQL 5.6后支持全文本搜索,MyISAM支持
- 通配符和正则语言的局限性
- 很少用到表索引,慢
- 搜索匹配控制不够明确
- 结果选择不够智能
- 声明类似主键声明方式,关键字fulltext;MySQL会自动维护该属性上的索引,但会一定程度上影响表的性能;小技巧,在导入全部数据后再定义fulltext会比先定义再导入更快
- 关键函数
- match():指定被搜索的列,需要和fulltext相匹配
- against():指定要使用的搜索表达式
- 查询拓展with query expansion:不仅返回匹配行,还返回高相关行
- 布尔文本搜索:提过更精细化的搜索控制,略
- 注意事项
- MySQL忽略不大于3个字符的词(可修改),忽略停词,忽略行出现频率高于50%的词,如果表总行数少于3,不返回结果
- 这里的一些概念和想法和NLP是相同的
select note_text from productnotes where match(note_text) against('rabbit');
select note_text, match(note_text) against('rabbit') as rank from productnotes; -- 作为计算字段
select note_text from productnotes where match(note_text) against('rabbit' with query expansion);
INSERT&UPDATE
- insert例子
insert into test values(4, 'ddd');
insert into test values(5, 'eee'),(6,'fff');
insert into test(a) select i from t1;
- update例子
update test set b=321 where b is null;
DELETE
- 例子
delete from test where a=6;
- 删所有行可以使用truncate table语句
- 实际上是重新创建表,所以更快
CREATE&ALTER&DROP
- create例子
create table var_type_test(
l char(6),
m varchar(7),
f numeric(2,1), -- 两位有效数字,小数点后一位
g decimal(4,3), -- 用法同numeric
h float(5),
a int(11),
d bit,
b tinyint default 1, -- 默认值为1
c smallint not null auto_increment, -- 不允许为null,自动增长,第一个为1
e bigint null, -- 默认值为null,默认隐式声明
i real,
j datetime,
primary key(c), -- c为主键
foreign key(a) references test(a) -- a为外键
)engine=InnoDB default charset=utf8; -- 指定引擎和字符集
select last_insert_id() from var_type_test; -- 获取表的自增值
show create table var_type_test; -- 显示建表语句
- 数据库引擎
- 不同数据库引擎性能和支持不同;Mysql5.1之前默认存储引擎是MyISAM,在此之后默认存储引擎是InnoDB
- InnoDB为事务处理引擎
- MEMORY的数据存在内存里,速度很快,一般用于临时表
- MyISAM性能极高,不支持事务
- 数据库引擎是表维度的,一个数据库中不同引擎可以混用
- 外键不能跨引擎
- alter例子
alter table var_type_test add nana7mi char(3);
alter table var_type_test drop column nana7mi;
alter table test add primary key(a);
alter table var_type_test add foreign key (a) references test(a);
alter table var_type_test
- drop例子
drop table var_type_test;
- 重命名
rename table var_type_test to nana7mi;
高级内容
视图
- 视图是虚拟的表,提供使用时动态检索数据的查询,基本可以当做一般的表使用;视图复杂时性能可能很差;提供SQL重用,化简,权限,控制显示等功能;
- 例子
create view ybb(l,m,id) as select l,m,a from nana7mi where a>10;
select id from ybb;
insert into ybb values('ybb','ybb',1);
drop view ybb;
- 更新:
- 存在以下情况视图不允许更新
- 分组
- 联结
- 子查询
- 并
- 聚集函数
- DISTINCT
- 计算字段
- 插入数据需要符合底层表的约束
- 更新检查with check option
- 不使用with check option时,通过view来更新可能会插入视图以外的脏数据
- MySQL默认with check option使用级联检查,即with cascaded check option
- 可以使用with local check option使得只检查当前视图的规则
- 视图更新不太常用,基本只用来查询
- 存在以下情况视图不允许更新
存储过程
- 类似函数;简便、安全、高性能;注意创建使用存储过程的权限可能需要设置
- 例子
delimiter // -- 当在命令行使用存储过程时,存储过程中的;会标志语句结束,故要暂时替换终止符为//
create procedure func()
begin
select * from ybb;
end //
delimiter ;
call func();
drop procedure func;
delimiter //
create procedure func(
in id int(11), -- 此处为形参,in表示输入,out表示输出,inout表示既输入又输出
out mm char(6),
out aa varchar(7)
)
begin
declare tem int(11); -- 声明局部变量
select m,a from nana7mi where a=id into mm,aa;
end//
delimiter ;
call func(1,@mn,@an); -- 变量以@开头,入参可以是数
select @mn,@an; -- 实参可以直接使用,若没赋值则为null
call func(@an,@mb,@ab); -- 也可以是变量
select @mb,@ab;
- 还可以使用if-else等控制流关键字,以及一系列循环关键字
IF search_condition THEN
RETURN()
ELSEIF search_condition THEN
RETURN()
ELSE
RETURN()
END IF
游标
- 在MySQL5以后可以在存储过程和函数中使用;在交互式应用中,为了对检索出的行进行移动和修改使用;它是一个被存储的数据库查询,不是一条select语句而是被该语句检索出来的结果集
- 使用前需要声明,但此时并没有进行检索,声明后需要打开游标进行实际检索,在结束游标使用时,需要关闭游标
- 例子
delimiter //
create procedure youbiao()
begin
declare tem int;
declare ptr cursor -- 创建游标
for
select a from test;
open ptr; -- 打开游标
fetch ptr into tem; -- fetch获取数据
select tem;
close ptr; -- 关闭游标
end//
delimiter ;
触发器
- 在MySQL5以后可以使用;可以理解为表发生更改时自动执行的存储过程;
- MySQL的每个触发器只对某个表的delete,insert,update其中的一个语句执行前或执行后进行响应
- 注意MySQL不允许某表触发器的操作是对本表的更改(可能是防止死循环的出现)
- 可以通过old和new两个虚拟表来访问执行前后的数据
- 例子
create trigger new_tri after insert on test for each row
insert into nana7mi(a,l) values(new.a,'ybb');
insert into test values(7, 'ybb');
drop trigger new_tri;
事务
- 例子
start transaction;
insert into test values(7, 'ybb');
insert into test values(8, 'ybb');
commit;
- create和drop语句无法被回滚
用户管理
- 创建用户
create user 'chx'@'localhost' IDENTIFIED BY '123456';
- 给用户赋权
grant all privileges on nodejs_test.* to chx@localhost identified by '123456';
flush privileges; -- 刷新
其它
show character set; -- 字符集
show collation; -- 校对顺序,和order by相关
参考
MySQL笔记
http://example.com/2021/11/22/MySQL笔记/