7. 数据库的备份和还原
-
命令行:
备份 mysqldump -u用户名 -p密码 数据库名称 > 保存的路径 mysqldump -uroot -proot db1>d://a.sql 还原: 1. 登录数据库 2. 创建数据库 creat database db1 3. 使用数据库 use database db1 4. 执行文件。 source d://a.sql
-
图形化工具
8. DCL
DCL:管理用户,授权
-
DBA:数据库管理员
-
DCL:管理用户,授权
-
管理用户
- 添加用户:
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- 删除用户:
语法:DROP USER '用户名'@'主机名';
- 修改用户密码:
USE mysql; UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
- 查询用户:
-- 1. 切换到mysql数据库 USE myql; -- 2. 查询user表 SELECT * FROM USER;
-
权限管理
-
查询权限:
-- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';
-
授予权限:
-- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限,在任意数据库任意表上 GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-
撤销权限:
-- 撤销权限: revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
-
-
9. 事务
9.1 事务的基本介绍
-
概念: 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
-
操作:
-
开启事务:
start transaction;
-
回滚:
rollback;
-
提交:
commit;
-
-
例子:
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); SELECT * FROM account; UPDATE account SET balance = 1000; -- 张三给李四转账 500 元 -- 0. 开启事务 START TRANSACTION; -- 1. 张三账户 -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2. 李四账户 +500 -- 出错了... UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- 发现执行没有问题,提交事务 COMMIT; -- 发现出问题了,回滚事务 ROLLBACK;
-
MySQL数据库中事务默认自动提交
事务提交的两种方式:- 自动提交:
- mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。- 手动提交:
- Oracle 数据库默认是手动提交事务
- 需要先开启事务,再提交
- Oracle 数据库默认是手动提交事务
- 手动提交:
- mysql就是自动提交的
- 修改事务的默认提交方式:
- 查看事务的默认提交方式:SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
- 修改默认提交方式: set @@autocommit = 0;
- 查看事务的默认提交方式:SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
- 自动提交:
9.2 事务的四大特征(ACID)
- 原子性(atomicity):是不可分割的最小操作单位,要么同时成功,要么同时失败。
- 持久性(durability):当事务提交或回滚后,数据库会持久化的保存数据。
- 隔离性(isolation):多个事务之间。相互独立。
- 一致性(consistency):事务操作前后,数据特征不变总量不变。
9.3 事务的隔离级别(了解)
-
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
存在问题:
-
脏读:一个事务,读取到另一个事务中没有提交的数据
-
不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
-
幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
事务在插入已经检查过不存在的记录时,惊奇的发现这些数据已经存在了,之前的检测获取到的数据如同鬼影一般。
例子:
在事务1中,查询User表id为1的是用户否存在,如果不存在则插入一条id为1的数据。
在事务1查询结束后,事务2往User表中插入了一条id为1的数据。
此时,由于事务1查询到id为1的用户不存在,因此插入1条id为1的数据。
但是由于事务2已经插入了1条id为1的数据,因此此时会报主键冲突,对于事务1 的业务来说是执行失败的,这里事务1 就是发生了幻读,因为事务1读取的数据状态并不能支持他的下一步的业务,见鬼了一样。这里要灵活的理解读取的意思,第一次select是读取,第二次的insert其实也属于隐式的读取,只不过是在mysql的机制中读取的,插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。
-
-
隔离级别:
-
read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
-
read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
-
repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
-
serializable:串行化
可以解决所有的问题
- **注意:**隔离级别从小到大安全性越来越高,但是效率越来越低
- MySQL数据库查询隔离级别:
- select @@tx_isolation;
- 数据库设置隔离级别:
- set global transaction isolation level 级别字符串;
-
10. MySQl数据类型
10.1 整型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大 |
10.2 浮点型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
10.3 日期和时间类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/ 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/ ‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/ 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 开始时间1970-01-01 00:00:00 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
10.4 字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR(N) | N个字节, 0<=N<=255 | 定长字符串 (不足N时补充空格) |
VARCHAR(N) | L+1个字节,其中L<=N且0<=M<=65535(2^16 -1) | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes 2 | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
11. 函数
11.1 MySQL常用内置函数
- 字符串函数
- 数字函数
- 日期函数
- 高级函数
详情查看 菜鸟教程 https://www.runoob.com/mysql/mysql-functions.html
- 加密函数
- MD5() 信息摘要算法
- PASSWORD() 密码算法
CASE函数
SELECT NAME,
CASE ID
WHEN '1' THEN 'yes'
ELSE 'no'
END 'id' -- 别名 id
FROM account;
SELECT NAME,
CASE
WHEN ID = '1' THEN 'yes'
ELSE 'no'
END 'id' -- 别名 id
FROM account;
11.2 自定义函数
11.2.1 概念
自定义函数 (user-defined function, UDF) 是一种对MySQL扩展的途径, 其用法与内置函数相同.
两个必要条件:
-
参数: 可以有零个或者多个
-
返回值: 只能有一个返回值
函数可以返回任意类型的值, 同样可以接受这些类型的参数, 函数的参数与返回值没有必然联系
11.2.2 创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
-
函数体由合法的SQL语句构成;
-
函数体可以是简单的SELECT或INSERT语句;
-
函数体如果为复合结构则使用BEGIN…END语句;
-
复合结构可以包含声明,循环,控制结构;
SET NAMES UTF8;
改变客户端编码格式(不影响服务器端存储)
11.2.3 不带参数的自定义函数
日期格式化输出(原始语句)
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
函数式语句
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')
使用
SELECT f1()
11.2.4 带参数的自定义函数
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
使用
SELECT f2(10,15);
创建好的函数可以在navicat左侧视图查看
更改MySQL语句结束分割符
DELIMITER //
DELIMITER ;
11.2.5 具有复合结构函数体的自定义函数
此时函数体需要使用BEGIN…END包裹语句;
CREATE FUNCTION addaccount(NAME VARCHAR(10))
RETURNS INT UNSIGNED
BEGIN
INSERT account(name,balance) values(NAME,50);
RETURN LAST_INSERT_ID();
END
12. 存储过程
12.1 概念介绍
MySQL SQL语句命令执行流程 SQL命令->MySQL引擎(分析)->语法正确->可识别命令(编译)->执行->执行结果->返回结果至客户端
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uviBiec8-1600497838872)(Pictures/SQL语句命令执行流程.png)]
存储过程是SQL语句和控制语句的预编译集合, 以一个名称存储并作为一个单元来处理, 存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户声明变量 以及进行流程控制,存储过程可以接收参数,可以接受输入类型的参数,也可以接收输出类型的参数,并且可以返回多个返回值,其执行效率比单一SQL语句的执行效率高.
存储过程只在第一次进行语法分析与编译
优点
- 增强SQL语句的功能和灵活性(控制语句,流程控制)
- 实现较快的执行速度
- 减少了网络流量
12.2 创建存储过程
基础语法
CREATE
[DEFINER={USER|CURREN_USER}] -- 一般可省略
PROCEDURE sp_name([proc_parameter[,...]]) -- 零到多个参数
[characteristic ...] routine_body
proc_parameter:
[IN|OUT|INOUT]param_name type
- IN, 表示该参数的值必须在调用存储过程时指定
- OUT, 表示该参数的值可以被存储过程改变, 并且可以返回
- INOUT, 表示该参数在调用存储过程时指定,并且可以被改变和返回
过程体
-
过程体由合法的SQL语句构成;
-
过程体可以是’任意’SQL语句;
-
过程体如果为复合结构则使用BEGIN…END语句;
-
复合结构可以包含声明,循环,控制结构;
12.2.1 创建不带参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
调用存储过程
CALL sp_name([parameter[,...]]) -- or
CALL sp_name[()]
CALL sp1;
12.2.2 创建带IN参数的存储过程
CREATE PROCEDURE removeAccountById(IN ac_id INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
END
-- 调用
CALL removeAccountById(5);
删除
DROP PROCEDURE IF EXISTS sp_name;
12.2.3 创建带IN OUT参数的存储过程
CREATE PROCEDURE removeAccountAndReturnUserNums(IN ac_id INT UNSIGNED,OUT accountNums INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
SELECT count(id) FROM account INTO accountNums;
END
-- 调用
CALL removeAccountAndReturnUserNums(3,@nums)
SELECT @nums;
例子
-- 定义结束符为“$$”,mysql默认结束符为“;”
-- 意思是告诉mysql解释器,该段命令是否已经结束了,即标识一段命令起始和结束
delimiter $$
-- 创建存储过程
-- sp_char_split_inser:存储过程名称
-- strs:存储过程参数名称
-- in:表示该参数为输入参数;out:表示该参数为输出参数;inout:表示该参数为输入输出参数。不写时默认为in,即输入参数。
create procedure sp_char_split_inser(in strs text)
begin
declare i int default 0;
declare leng int default 0;
declare word char(1);
-- 判断字符串是否为空或空字符串
if(strs is not null && strs <> '') then
-- 获取字符串长度
set leng = char_length(strs);
-- 循环
while i < leng do
-- 获取第一个字符
set word=left(strs,1);
if(word is not null && word <> '') then
-- 判断该条数据是否存在
if not exists(select 1 from demo.charinfo where Hanzi=word limit 1) then
-- 插入数据
insert into demo.charinfo(Hanzi) values(word);
end if;
end if;
-- 截取除第一个字符之外的所有字符
set strs=substring(strs,2);
set i=i+1;
end while;
end if;
end;
-- 命令结束
$$
delimiter ;
12.2.4 创建带有多个OUT类型参数的存储过程
row_count()得到最近一次操作被影响到的记录总数.
CREATE PROCEDURE removeAccountByIdAndReturnInfos(IN ac_id INT UNSIGNED,OUT deleteAccounts INT UNSIGNED,OUT accountCounts INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
SELECT ROW_COUNT() INTO deleteAccounts;
SELECT COUNT(id) FROM account INTO accountCounts;
END
调用
CALL removeAccountByIdAndReturnInfos(6,@uuu,@uuu2)
-- 调用时 @uuu,@uuu2 可不用提前定义
-- 定义和查询成员变量方法
SET @uuu=7; SELECT @uuu
12.2.5 返回表的存储过程写法
CREATE PROCEDURE selectIds()
BEGIN
SELECT * FROM account;
END
12.2.6 存储过程与自定义函数的区别
- 存储过程实现的功能要复杂一些, 而函数的针对性更强;
- 存储过程可以返回多个值, 函数只能有一个返回值;
- 存储过程一般独立的来执行, 而函数可以作为其他SQL语句的组成部分来出现
利用存储过程能提高程序运行效率
13. MySQL变量的使用
在mysql文档中,mysql变量可分为两大类,即系统变量和用户变量。
13.1 MySQL变量概念
但根据实际应用又被细化为四种类型,即局部变量、用户变量、会话变量和全局变量。
一、局部变量
mysql局部变量,只能用在begin/end语句块中,比如存储过程中的begin/end语句块。
其作用域仅限于该语句块。
-- declare语句专门用于定义局部变量,可以使用default来说明默认值
declare age int default 0;
-- 局部变量的赋值方式一
set age=18;
-- 局部变量的赋值方式二
select StuAge
into age
from demo.student
where StuNo='A001';
二、用户变量
mysql用户变量,mysql中用户变量不用提前申明,在用的时候直接用“@变量名”使用就可以了。其作用域为当前连接。
-- 第一种用法,使用set时可以用“=”或“:=”两种赋值符号赋值
set @age=19;
set @age:=20;
-- 第二种用法,使用select时必须用“:=”赋值符号赋值
select @age:=22;
select @age:=StuAge
from demo.student
where StuNo='A001';
三、会话变量
mysql会话变量,服务器为每个连接的客户端维护一系列会话变量。
其作用域仅限于当前连接,即每个连接中的会话变量是独立的。
-- 显示所有的会话变量
show session variables;
-- 设置会话变量的值的三种方式
set session auto_increment_increment=1;
set @@session.auto_increment_increment=2;
set auto_increment_increment=3; -- 当省略session关键字时,默认缺省为session,即设置会话变量的值
-- 查询会话变量的值的三种方式
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like '%auto_increment_increment%'; -- session关键字可省略
-- 关键字session也可用关键字local替代
set @@local.auto_increment_increment=1;
select @@local.auto_increment_increment;
四、全局变量
mysql全局变量,全局变量影响服务器整体操作,当服务启动时,它将所有全局变量初始化为默认值。要想更改全局变量,必须具有super权限。
其作用域为server的整个生命周期。
-- 显示所有的全局变量
show global variables;
-- 设置全局变量的值的两种方式
set global sql_warnings=ON; -- global不能省略
set @@global.sql_warnings=OFF;
-- 查询全局变量的值的两种方式
select @@global.sql_warnings;
show global variables like '%sql_warnings%';
13.2 几种变量的对比使用
操作类型 | 全局变量 | 会话变量 | 用户变量 | 局部变量(参数) |
---|---|---|---|---|
文档常用名 | global variables | session variables | user-defined variables | local variables |
出现的位置 | 命令行、函数、存储过程 | 命令行、函数、存储过程 | 命令行、函数、存储过程 | 函数、存储过程 |
定义的方式 | 只能查看修改,不能定义 | 只能查看修改,不能定义 | 直接使用,@var 形式 | declare count int(4); |
有效生命周期 | 服务器重启时恢复默认值 | 断开连接时,变量消失 | 断开连接时,变量消失 | 出了函数或存储过程的作用域,变量无效 |
查看所有变量 | show global variables; | show session variables; | - | - |
查看部分变量 | show global variables like 'sql%'; | show session variables like 'sql%'; | - | - |
查看指定变量 | select @@global.sql_mode 、 select @@max_connections; | select @@session.sql_mode; 、 select @@local.sql_mode; 、 select @@sql_mode; | select @var; | select count; |
设置指定变量 | set global sql_mode=''; 、 set @@global.sql_mode=''; | set session sql_mode = ''; 、 set local sql_mode = ''; 、 set @@session.sql_mode = ''; 、 set @@local.sql_mode = ''; 、 set @@sql_mode = ''; 、 set sql_mode = ''; | set @var=1; 、 set @var:=101; 、 select 100 into @var; | set count=1; 、 set count:=101; 、 select 100 into count; |