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相关

参考


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!