MySQL数据库基础与高级操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/xy294636185/article/details/124338075

一、MySQL数据库基础

数据库泛型（范式）

第一范式：表中不能有重复字段，并且不能拆分。

二、MYSQL基本操作

数据库类型

整数类型：tinyint(4),smallint(6),mediumint(9),int(11),bigint(20)

浮点类型：FLOAT(4),DOUBLE(8)

定点数类型：DECIMAL(M,D)(M+2)

注意：DECIMAL的取值范围和DOUBLE相同，但是有效范围由M和D决定，字节数是M+2，存储空间是由其精度确定的。

特别注意：mysql中定点数以字符串形式存储。因此，精度比浮点数要高。而且浮点数会有误差。

日期和时间类型：YEAR(1),DATE(4)TIME(3)DATETIME(8)TIMESTAMP(4)

字符串类型：CHAR,VARCHAR,BLOB,TEXT,ENUM,SET

注意：SET和ENUM类型对取值在一定范围的离散值有效，SET可在取值列表中取多个值，且都不是直接插入数据，而是插入列表中编号。

二进制类型：BINARY,VAARBINARY,BIT,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB

数据库引擎

InnoDB存储引擎：提供事务，回滚，崩溃修复和多版本并发控制的事务安全。支持自增长列。支持外键。读写效率差，占用空间大。

MuISAM存储引擎：占用空间小，处理速度快。不支持事务的完整性和并发性。

索引

索引是创建在表上对列进行排序的一种结构。

普通索引：可以创建在任何数据类型中。

唯一性索引：需限制索引的值必须是唯一的。

全文索引：使用FULLTEXT设置，且只能创建在CHAR，VARCHAR和TEXT字段

单列索引：只对应一个字段

多列索引：多个字段创建索引，但是只有查询条件中使用这些字段的第一个字段时才被使用。

空间索引：使用SPATIAL参数设置，只能创建在GEOMETRY，POINT，LINESTRING和POLYGON上，目前只有MyISAM存储引擎支持空间索引。

索引设计原则

1.选择唯一性索引

2.经常需要排序，分组和联合操作的字段

3.作为查询条件的字段

4.限制索引的数目

5.尽量使用前缀来索引

触发器

创建只有一个执行语句的触发器：

create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句

eg：create trigger dept_trig before insert on department for each row insert into trigger_time values(now());

创建有多个执行语句的触发器：

create trigger 触发器名 before|after 触发事件

        on 表名 for each row

begin

                执行语句

end

eg：

delimiter &&

create trigger dept_trig after delete on department for each row

begin

insert into trigger_time values('value');

insert into trigger_time values('value2');

end

&&

delimiter

注意：

问题：mysql创建多条执行语句触发器总是遇到分号就结束创建，然后报错？

一般情况下，mysql默认是以；为结束执行语句，在创建触发器时用到；，为解决这个问题，可以用delimiter语句，如使用“deliniter &&”，可以将结束符号编程“&&”。当触发器创建完成后，可以用“deliniter;“来将结束符号编程”;“

mysql中，一个表在相同触发时间的相同触发事件，只能创建一个触发器

查看触发器：

show triggers; //查看触发器的基本信息

select * from information_schema.triggers; //查看数据库中所有触发器详细信息

select * from information_schema.triggers where trigger_name = '触发器名'

备注：所有触发器信息都存储在information_schema数据库下的triggers表中。

触发器的使用：

在Mysql中，触发器执行顺序是before触发器，表操作（insert，update和delete）和after触发器。

//创建before insert触发器

create trigger before_insert before insert

on department for each row

insert into trigger_test values(null,"before_insert");

在目标表中插入数，如果执行成功，则可在trigger_test表中查看。

删除触发器：

drop trigger 触发器名;

二、MySql查询语句

分组查询：

GROUP BY 属性名 [HAVING 条件表达式] [WITHROLLUP]

//“属性名”：指按照该字段的值进行分组；“HAVING条件表达式”：用来限制分组后的显示；“WITH ROLLUP”：将会在所有记录的最后加上一条记录，该记录是上面所有记录的和。

GROUP BY关键字与GROUP——CONCAT()函数一起使用

select sex,group_concat(name) from employee group by sex;

//group_concat()函数会把每个分组中指定字段值显示出来。

GROUP BY关键字与HAVING一起使用

select sex,count(sex) from employee group by sex having count(sex) >= 3;

//HAVING条件表达式与 WHERE条件表达式都是用来限制显示的。但是，两者起作用的地方不一样。WHERE作用于表或视图，是表和视图的查询条件。HAVING作用于分组后的记录，用于选择满足条件的组。

GROUP BY关键字与WITH ROLLUP一起使用

select sex,group_concat(name) from employee group by sex with rollup;

//使用with rollup时，将会在所有记录的最后加上一条记录，这条记录是上面所有记录的总和。

LIMIT关键字

LIMIT 初始位置，记录数

//初始位置参数指定从哪开始，记录数参数表示显示记录的条数。

注意：limit关键字是mysql中所特有的，limit关键字可以指定需要显示的记录的初始位置。

集合函数使用注意事项：

1.sum()函数只能计算数值类型的字段，包括int类型，float类型，double类型，decimal类型。字符类型的字段不能使用sum函数进行计算，结果会是0

2.max(),min()等函数使用字符对应的ASCII码进行计算。

子查询

带EXISTS关键字的子查询

（NOT）EXISTS关键字表示存在，使用EXISTS，内查询不返回查询的记录，而是返回一个真假值。

eg：select* from employee where exists(select d_name from department where id = 1);

带ANY关键字的子查询

ANY关键字表示满足任一条件。ALL表示满足所有

eg: select * from computer_stu where score >= any(select score from sholarship);

合并查询

注意：UNION和UNION ALL关键字都可以合并查询结果，区别：union会将相同的记录清除掉，union all不会消除记录。

正则表达式查询

正则表达式的模式字符
正则表达式的模拟字符	含义
^	匹配字符串开始的部分
$	匹配字符串结束的部分
.	代表字符串中的任意一个字符，包括回车和换行
[字符集合]	匹配“字符集合”中的任何一个字符
[^字符集合]	匹配除了“字符集合”以外的任何一个字符
S1\|S2\|S3	匹配S1，S2和S3中的任意字符
*	代表多个该符号之前的字符，包括0和1个
+	代表多个该符号之前的字符，包括1个
字符串{N}	字符串出现N次
字符串{M,N}	字符串出现至少M次，最多N次

正则用于匹配一类字符串的方式。使用REGEXP关键字来匹配查询正则表达式。

eg：

查询以L开头名字：select * from info where name regexp '^L';

查询以L开头，以字母y结尾，中间有两个任意字符的记录：

select * from info where name regexp '^L..y$'

查询字段中包含c、e和o3个字母的任意一个记录：

select * from info where name regexp '[ceo]'

（常用）查询字段中包含0-9数字和a-c字母的字段：

select * from info where name regexp '[0-9a-c]'; #[a-z]所有字母 [0-9]所有数字

查询包含a到w字母和数字以外的字符记录：

select * from info where name regexp '[^a-w0--9]';

查询包含'ic'、'uc'和'ab'这3个任意一个：

select * from info where name regexp 'ic|uc|ab';

查询c之前出现过a的记录：

select * from info where name regexp 'a*b'; #注意*和+的区别

查询字段中ab最少一次，最多3次的记录

select * from info where name regexp 'ab{1,3}';

注意：使用[]匹配需要的字符时，使用[abc]指定字符集合，每个字符不要隔开。

常见问题：

1.MySql中通配符与正则表达式的区别？

同配和正则都是用来字符串匹配的，两者都可以及进行模糊查询。通配符与LIKE关键字一起使用。正则要与REGEXP关键字使用。

2.使用limit来限制查询结果数量？

查询5-8名的信息，就可以使用LIMIT从第5条开始，显示4条记录。

插入、删除数据

将查询结果插入到表中

insert into product(id,function) selectid,function from medicine;

如何进行联表删除？

通过外键进行删除，联表删除可以保证数据库中数据的一致性。

MySQL运算符

mysql算术运算符
符号	表达式的形式	作用（如果y是0，则结果是null）
/	x / y	除法，返回x除以y商
DIV	x DIV y	除发，返回商。同/
%	x % y	求余，返回x除以y的余数
MOD	MOD(x,y)	求余，返回余数，同%

运算符=，<=>

=不能判空，使用ASCII进行判断

<=> 与=是一样的，但是<=>可以判断null

注意：=,<>,!=,>,>=,<,<=等操作都不能用来判空，如果要判空，可以使用<=>,is null,is not null。

运算符like

like和'_'和'%'使用，'_'代表单个字符，'%'代表任意长度字符

逻辑运算符
符号	名称	符号	名称
&&或者AND	与	！或者NOT	非
\|\|或者OR	或	XOR	异或

注意：mysql中所有异或中，大于-1小于1的数字都被视为逻辑0，其他为逻辑1

位运算符
符号	名称	符号	名称
&	按位与	^	按位异或
\|	按位或	<<	按位左移
~	按位取反	>>	按位右移

注意：位运算都是在二进制上进行的。

问题：

运算符优先级？

！最高，=最低，其他自行百度

位运算的禁止问题？

位运算必须是十进制的。十进制和二进制之间的互换是数据库实现的。如果操作数是二进制，八进制，十六进制时，要先通过conv()函数将操作数转换为十进制数。

MySQL函数

数学函数（部分）
函数	作用
ceil(x),ceiling(x)	返回大于x最小整数
sign(x)	返回x的符号
truncate(x,y),round(x,y)	保留小数，后者四舍五入
pow(x,y)，sqrt(x)	次方，开方

字符串函数(部分)
函数	作用
char_length(s),length(s)	返回s的字符数，长度
concat(s1,s2)	将字符s1，s2合并
concat_ws(x,s1,w2)	合并每个字符串前+x
insert(s1,x,len,s2)	将字符串s2替换s1的x位置开始长度为len的字符串
lpad(s1,len,s2)	字符串s2填充s1的开始处，使长度到len
trim(s1 from s)	去掉s开始处和结尾处的空格
strcmp(s1,s2)	比较s1和s2
locate(s1,s),position(s1 in s)，instr(s,s1)	从s处获取s1的开始位置
reverse(s)	逆序
field(s,s1,s2)	返回第一个与字符串s匹配的字符串位置

日期和时间函数
函数	作用
curdate(),current_timestamp(),now()	返回日期，时间
month(d),monthname(d)	返回月份1-12，名称
dayname(d),daofweek(d),weekday(d),week(d),weekofyear(d)	返回d星期名称，开始日期分别是日或一的格式，本年0-53或1-53
dayofyear(d),dayofmonth(),year(d)	返回本年，本月第几天和年份值
quarter(d)	返回季度1-4
hour(t),minute(t),second(t)	返回t的小时，分钟，秒
time_to_sec(t),sec_to_time(t)	t和秒互转
to_days(d),from_day(d)	d~0000年1月1号天数互转
datediff(d1,d2)	d1~d2间隔天数
adddate(d,n),subdate(d,n)	d加上减去n天
adddate(d interval xepr type),subdate(d interval xepr type)	d日期加上减去一个时间端后的日期
addtime(t,n),subtime(t,n)	计算t加减n秒的时间
get_format(type,s)	s获取type类型数据的显示格式

MySQL的日期间隔类型
类型	expr表达式的形式
YEAR	YY
MONTH	MM
DAY	DD
HOUR	hh
MINUTE	mm
SECOND	ss
YEAR_MONTH	YY和MM之间任意符号隔开

注意：adddate(d interval expr type)的type必须是上表中的。

MySQL日期时间格式
%Y	2022
%y 2位数年	22
%m 2位数字月份	01，02
%c 数字月份	1，2
%M 月份	January
%b 月份	Jan
%U 星期，Sun是第一天	00-52
%u 星期，Mon是第一天	00-52
%j 3位年中天数	001-336
%d 2位数字月份日期	01,02,...,31
%e 数字月份日期	1,2,...,31
%D 英文月份	1st,2nd,...
%w 数字星期	0:Sunday,1Monday
%W 星期	Monday,...,Sunday
%a 星期	Mon,...,Sun
%T 24小时	00:00:00-23:59:59
%r12小时制AM	12:00:00AM
%p	AM
%k 24小时制	0,1,...,23
%l 12小时制	1,2,...,12
%H 小时	00,01,...,23
%h,%I 小时	01,02,...,12
%i 分	00,01,...,59
%S,%s 时	00,01,...,23
%%	%

加密函数

加密函数PASSWORD(str)

selet password('abcd'); #PASSWORD(str)加密不可逆

加密函数MD5(str)

select md5('abcd');

加密函数encode(str,pswd_str)

insert into b values(encode('abcd','aa')); #encode(str,pswd_str)函数使用pswd_str来加密str，加密结果是一个二进制，必须使用blob类型保存。

解密函数decode(crypt_str,pswd_str)

select decode('encode('abcd','aa'),'aa'); #decode(crypt_str,pswd_str)函数使用pswd_str来为crpt_str解密，

其他函数

IP地址与数字转换函数

select INET_ATON('59.65.226.15'),INET_NTOA(994173455)

加锁函数和解锁函数

select get_lock('MYSQL',10); #增加一个名为MYSQL的锁，10s

注意：当执行release_lock()、一个新的get_lock获取线程终止，那么之前的锁都会解除。

重复执行指定操作函数

select benchmark(10000,now()); #返回系统时间10000次

问题：如何改变字符串的字符集？

MySQL可以重新配置字符集来修改，也可以在MySQL的安装路径下修改my.ini。将default-charset-set的值改变来修改字符集。如果只是修改某个字符串的字符集，可以使用convert(s using cs)函数

存储过程和函数

创建存储过程：创建存储过程和哈数是指将常用的一组SQL语句的组合起来，并将这些SQL语句当作一个整体存储在MYSQL服务器中。

create procedure sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

#sp_name 参数是存储过程的名称；proc_parameter表示存储过程的参数列表，characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容。可以用BEGIN...END来标志开始结束。

#proc_parameter中每个参数由3部分组成 [IN|OUT|INOUT] param_name type

技巧：创建存储过程时，系统默认指定contains sql，表示存储过程中使用了sql语句，但是，如果存储过程中没有使用sql语句，最好设置no sql，而且。存储过程中最好在comment部分对存储过程进行简单的注释。

创建一个名称num_from_employee的存储过程。

create procedure num_from_employee(IN emp_id INT,OUT count_num INT)

reads sql data

begin

select count(*) into count_num from employee where d_id = emp_id;

end

#存储过程名num_from_employee，输入变量emp_id，输出变量ount_num。select语句

从employee表查询d_id等于emp_id的记录，并用count激素那条数，最后将结果存入count_num中。

创建存储函数

create function sp_name ([func_parameter[,...]]) returns type [characteristic ...] routine_body

创建一个名为name_from_employee的存储函数

create function name_from_employee (emp_id INT)

returns varchar(20)

begin

retrun (select name from employee where num = emp_id);

end

定义变量

declare my int default 10; #定义变量my 类型int 默认10

set my = 30; #赋值

select d_id into my from employee where id = 2; #将id-2的记录赋给my

定义条件

#定义“ERROR 1146(42S02)”这个错误，名称为can_not_find

//方法一

declare can_not_find condition for SQLSTATE '42S02';

//方法二

declare can_not_find condition for 1146;

定义处理程序

//捕获sqlstate_value

declare continue handler for sqlstate '42S02' set @info='CAN NOT FIND'

//捕获mysql_error_code

declare continue handler for 1146 set @info='CAN NOT FOUND'

//先定义条件在使用

declare can_bot_find condition for 1146;

declare continue handler for can_not_find set @info='CAN NOT FOUND'

//使用sqlwarning

declare exit handler for sqlwarning set @info='ERROR'

//使用not found

declare exit handler for not found set @info='CAN NOT FOUND'

//使用sqlexception

declare exit handler for sqlexception set @info='ERROR'

注意：执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是MySQL不支持UNDO操作。因此遇到错误最好执行EXIT操作。如果能事先预知错误类型并且相应处理，那么可以执行continue操作。

流程控制语句的使用：

if语句，case语句，loop语句，leave语句，iterate语句，repeat语句，wehile语句

LOOP语句：loop可实现简单的循环，但是不能停止，需要leave语句才能停止。

add_num:loop set @count = @count + 1; end loopadd_num; //死循环+1

add_num:loop set @count = @count + 1;if @count = 100 then leave add_num; end loop add_num; //当count=100跳出循环

ITERATE语句：跳出本次循环，然后进入下一次循环。

add_num:loop set @count = @count+1; if @count = 100 then leave add_num; else if mod(@count,3) = 0 then iterate add_num; select * from employee; end loop add_num;

//如果count能够整除3.则跳出本次循环，不再执行select语句

WHILE语句：当条件满足是执行循环语句

while @count < 100 do set @count = @count + 1; end while;

调用存储过程：mysql使用call来调用存储过程。

//创建存储过程

delimiter &&

create procedure num_from_employee(in emp_id int,out count_num int)

reads sql data

begin

select count(*) into count_num from employee where d_id = emp_id;

end &&

delimiter;

//调用存储过程
call num_from_employee(1002,@n);

调用存储函数：同上

查看存储过程和函数：

show procedure status like 'num_from_employee'\G

注意：show status语句支持能查看存储过程是哪个数据库，详细定义需要使用show create语句。

从informaation_schema.Routines表中查看存储过程

select * from information_schema.Routines where routine_name = 'sp_name' //查看sp_nama的存储信息

修改，删除存储过程：Alter、DROP

问题：

1.一个存储过程中可以调用其他的存储过程吗？

可以，用户通过call语句调用已经定义好的sql语句。

2.存储过程和存储函数的区别是什么？

存储过程的参数有3类：IN，OUT和INOUT，通过OUT，INOUT将存储过程的执行结果输出，而且存储过程中可以有多个OUT，INOUT类型的变量，可输出多个值。

存储函数中的参数都是输入参数，函数中的运算结果通过return语句来返回且只能返回一个结果。

3.存储函数和mysql内部函数有什么区别？

存储函数是用户自己定义的函数。并且通过调用来执行函数中的sql语句，函数执行完成后，通过return语句来执行返回执行结果。

四、MySQL数据库高级管理

用户管理

权限表：安装Mysql会自动创建一个名为mysql的数据库。mysql数据库下存储的都是权限表，用户登录后，会为每个用户赋予相应的权限，其中user、db和host表比较重要。

user表中重要字段：

Gran_priv：是否拥有GRANT权限，Shutdown_priv：是否拥有停止mysql服务的权限，Super_priv：是否拥有超级权限，Execue_priv：是否拥有execute权限。

db表和host表：db表存储某个用户对一个数据库的权限。

数据备份

mysqldump：mysqldump命令可以将数据库中的数据备份成一个文本文件，表的结构和表中的数据将存储在生成的文本文件中。

mysqldump-u username -p dbname table1 tables2 > BackupName.sql //也可生成txt文件

mysqlhotcopy：如果备份不能停止mysql服务，可采用此工具

mysqlhotcopy [option] dbname1 dbname2 backupDir/

数据还原：

mysql -u root -p [dbname] < backup.sql

数据库迁移：

相同版本的数据库迁移：

mysqldump -h name1 -u root ==password = password1 --all -databases | mysql -h host2 -u root --password = password2

//“|”：代表管道，password1是name1主机上root用户的密码

不同版本的数据库迁移：

建议使用ETL工具

MySQL日志

Mysql日志可分为二进制日志，错误日志，通用日志和慢查询日志。除二进制日志外，其他都是文本文件。

注意：默认只启动错误日志功能，启动日志会降低MySQL数据库的执行速度，并且日志文件会占用大量硬盘空间。

二进制日志（update log）：以二进制文件的形式记录了数据库中的操作，但不记录查询语句

开启二进制日志

#my.ini

log-bin [=DIR\[filename]]

//DIR指二进制文件的存储路径，filename指二进制文件名。每次重启都会生成一个新的二进制文件

注意：二进制文件和数据库数据文件最好分开存储，即使一个硬盘坏了，也不会丢失数据

查看二进制文件

mysqlbinlog filename.number //mysqlbinlog命令将在当前文件夹下查找指定的二进制日志。

mysqlbinlog C:\log\mylog.000001 //绝对路径查找

删除二进制文件

RESET MASTER //删除所有

PURGE MASTER LOGS TO 'filename.number' //根据编号删除二进制文件

二进制文件还原数据库

mysqlbinlog filename.number | mysql -u root -p //注意还原的文件编号的时序性

错误日志：记录MySQL服务器的启动，关闭和运行错误等消息，默认开启，无法禁止

设置错误日志存储位置

#my.ini

log-error=DIR/[filename] //重启后参数生效

删除

mysqladmin -u root -p flush-logs

通用日志：记录用户登录和记录查询的信息

开启通用日志

#my.ini

log=[=DIR\[filename]]

慢查询日志：记录执行时间超过指定时间的操作

开启

#my.ini

log-slow0queries [=DIR\[filename]]

long_query_time=n //n:设定的时间值单位秒

性能优化

优化查询：使用EXPLAIN和DESCRIBE语句分析语句。

explain select * from staudent\G

//id:表示select语句的编号

//select_type：表示select语句的类型（SIMPLE、PRIMARY、UNION）

//table：表

//type：连接类型

//possible_keys：可能使用的索引

//key：使用的索引

//key_len：索引长

//ref：列或常数与索引一起来查询记录

//rows：查询的行数

//Extra：查询中的附件信息