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 #笔记 #常用命令
MySQL笔记
http://example.com/2021/11/22/MySQL笔记/
作者
zty
发布于
2021年11月22日
许可协议