Mysql数据库基础（3）

一、数据库的备份与还原

1、备份的目的

• 做灾难恢复：对损坏的数据进行恢复和还原

• 需求改变：因需求改变而需要把数据还原到改变以前测试（测试新功能是否可用）

2、备份命令

（1）cmd命令行

示例：

mysqldump -u username -ppassword database > D:\backup.sql;

执行完后如下：

（2）SQLyog

  在yog可视化工具中找到对应的数据库右击，在下面的工具栏内找到备份数据库：

  在转入到的框内数据路径和文件名，点击导出。

3、将备份文件导入mysql数据库的命令

（1）cmd命令行

• 在导入备份文件时需要注意备份文件的数据库，如果我这里备份的是db5数据库，那么在导入备份文件的时候一定是要导入db5数据库。否则导入失败。当然也可以将备份文件内的数据库名进行修改。导入修改后的数据库也是可以的。

mysql -u username -ppassword database < D:/a2.sql;

 （2）SQLyog

  导入mysql备份文件的操作大致同上，找到对应sql脚本文件上传即可。

•  找到文件执行，这样就导入成功了。

二、事务安全

  事务：transaction，一系列要发生的连续的操作。

  事务安全：一种保护连续操作的机制。

  事务安全的意义：保证数据操作的完整性。

1、事务的特点

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2、事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

3、事务处理

  用 BEGIN，ROLLBACK，COMMIT来实现：

• BEGIN开始一个事务

• ROLLBACK事务回滚

• COMMIT事务确认

  这里以ROLLBACK为例，执行连接mysql数据库插入数据的示例：

import pymysql
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="******",
    db="db7"
)
cur = con.cursor()
try:
    sql1 = "INSERT INTO student VALUE(8, '小明', 67, 40, 65);"
    sql2 = "INSERT INTO student VALUE(9, '小鼎', 67, 40, 65);"
    sql3 = "INSERT INTO student VALUE(10, '小磊', 67, 40, 65);"
    sql4 = "INSERT INTO student VALUE(11, '小程', 67, 40, 65);"
    print(ss)
    cur.execute(sql1)
    cur.execute(sql2)
    cur.execute(sql3)
    cur.execute(sql4)
    con.commit()
except Exception as e:
    print("出现异常")
    con.rollback()

sql5 = "SELECT * FROM student;"
cur.execute(sql5)
value = cur.fetchall()

for i in value:
    print(i)

cur.close()
con.close()

三、Mysql数据库函数的使用

  MySQL中的函数主要分为以下四类：字符串函数、数值函数、日期函数、流程函数。

1、字符串函数

  mysql中内置了很多字符串函数，常用的几个如下：

演示如下：

• concat：字符串拼接

  select concat('Hello' , ' MySQL');

• lower: 全部转小写

  select lower('Hello');

• upper: 全部转大写

  select upper('Hello');

• lpad: 左填充

  select lpad('01', 5, '-');

• rpad: 右填充

  select rpad('01', 5, '-');

• trim: 去除空格

  select trim(' Hello MySQL ');

• substring: 截取子字符串

  select substring('Hello MySQL',1,5);

案例：

  由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员 工的工号应该为00001。

  执行lpad左填充语句

update emp set workno = lpad(workno, 5, '0');

  处理完毕之后，具体的数据为：

2、数值函数

  常见的函数如下：

演示如下：

• ceil：向上取整

  select ceil(1.1);

• floor：向下取整

  select floor(1.9);

• mod：取模

  select mod(7,4);

• rand：获取随机数

  select rand();

• round：四舍五入

  select round(2.344,2);

  案例：

  通过数据库的函数，生成一个六位数的随机验证码。

  思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础 上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0。

select lpad(round(rand()*1000000 , 0), 6, '0');

3、日期函数

  常见的日期函数如下：

演示如下：

• curdate：当前日期

  select curdate();

• curtime：当前时间

  select curtime();

• now：当前日期和时间

  select now();

• YEAR , MONTH , DAY：当前年、月、日

  select YEAR(now());
  select MONTH(now());
  select DAY(now());

• date_add：增加指定的时间间隔

  select date_add(now(), INTERVAL 70 YEAR );

• datediff：获取两个日期相差的天数

  aselect datediff('2021-10-01', '2021-12-01');a

案例：

  查询所有员工的入职天数，并根据入职天数倒序排序。

  思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

# 数据库准备
CREATE TABLE t_emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(22) NOT NULL,
	age INT(10)  NOT NULL,
	entrydate DATE NOT NULL
)

INSERT INTO t_emp VALUE (NULL, "张三", 20, "2023-01-03");
INSERT INTO t_emp VALUE (NULL, "李四", 30, "2023-01-05");
INSERT INTO t_emp VALUE (NULL, "王五", 40, "2023-01-07");
INSERT INTO t_emp VALUE (NULL, "赵六", 50, "2023-01-09");

# 查询语句
select 
	name, datediff(curdate(), entrydate) as 'entrydays' 
from 
	emp 
order by 
	entrydays desc;

4、流程函数

  流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

• if

  select if(false, 'Ok', 'Error');

• ifnull

  select ifnull('Ok','Default');
  select ifnull('','Default');
  select ifnull(null,'Default');

案例：

  判断学员的成绩是否合格

CREATE TABLE score(
  id INT COMMENT 'ID',
  NAME VARCHAR(20) COMMENT '姓名',
  math INT COMMENT '数学',
  english INT COMMENT '英语',
  chinese INT COMMENT '语文'
)


INSERT INTO 
	score (id, NAME, math, english, chinese)
VALUES 
	(1, 'Tom', 67, 88, 95), 
	(2, 'Rose' , 23, 66, 90),
	(3, 'Jack', 56, 98, 76);


SELECT
  id,
  NAME,
  (CASE WHEN math >= 85 THEN '优秀' WHEN math >=60 THEN '及格' ELSE '不及格' END )
'数学',
  (CASE WHEN english >= 85 THEN '优秀' WHEN english >=60 THEN '及格' ELSE '不及格' END )
'英语',
  (CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >=60 THEN '及格' ELSE '不及格' END ) 
'语文'
FROM score;

  结果如下：

  到这里关于数据库mysql的基础知识点就结束了，后续多加练习和复习进行巩固。