MySQL函数、事务、索引、权限管理和备份、规范数据库设计

官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

5.1、常用函数
-- ==============常用函数===============
-- 数字运算
SELECT ABS(-7)   -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()    -- 返回一个0~1之间的随机数
SELECT SIGN(10)   -- 判断一个数的符号 负数返回-1,正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('我爱数据库')    -- 字符串长度
SELECT CONCAT('我','爱','你们')      -- 拼接字符串
SELECT LOWER('ZhangGuorong')       -- 小写字母
SELECT UPPER('ZhangGuorong')       -- 大写字母
SELECT INSERT('我爱编程helloWorld',1,2,'超级热爱')   -- 查询,从某个位置开始替换某个长度
SELECT SUBSTR('荣哥说坚持就能成功',4,6)    -- 返回指定的子字符串
SELECT REPLACE('荣哥说坚持就能成功','坚持','努力')   -- 替换出现的指定字符串
SELECT REVERSE('你爱我')   -- 反转

-- 查询 姓 周的同学 ,把周替换成张
SELECT REPLACE(studentName,'周','张') FROM student
WHERE studentName LIKE '周%'

-- 时间和日期函数 (记住)
SELECT CURRENT_DATE()  -- 获取当前日期
SELECT LOCALTIME()     -- 本地时间
SELECT SYSDATE()    -- 系统时间

SELECT YEAR(NOW())  -- 显示当前年份
SELECT DAY(NOW())   -- 天
SELECT SECOND(NOW())   -- 秒

-- 系统
SELECT VERSION()
SELECT USER()
SELECT SYSTEM_USER()

 

5.2、聚合函数(常用)
函数名称 描述
count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
... ...
5.3数据库级别的MD5加密(扩展)

什么是MD5

主要增强算法的复杂性和不可逆性

MD5 不可逆,具体的值的 MD5 是一样的

MD5 破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值

-- 加密
UPDATE student SET pwd=MD5(pwd) WHERE id=1
UPDATE student SET pwd=MD5(pwd)   -- 加密全部的密码

-- 插入的时候加密
INSERT INTO student (`id`,`name`,`pwd`) VALUES('13','胡',MD5('123456'))

-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM student WHERE `name` = '胡' AND pwd=MD5('123456')
select总结

MySQL函数、事务、索引、权限管理和备份、规范数据库设计

 

6、事务

6.1、什么是事务

要么都成功,要么都失败

事务原则:ACID原则  原子性,一致性,隔离性,持久性 (脏读,幻读。。。)

原子性:要么都成功,要么都失败

一致性:事务前后的数据完整性要保证一致

持久性:事务一旦提交则不可逆,被持久化到数据库中

隔离性:事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数数据所干扰,多个并发事务之间要相互隔离

-- 模拟转账:事务
SET autocommit = 0  -- 关闭自动提交
START TRANSACTION   -- 开启一个事务

UPDATE `account` SET money = money-500 WHERE `name` = 'A'  -- A减500
UPDATE `account` SET money = money+500 WHERE `name` = 'B'  -- B加500

COMMIT; -- 提交事务,就被持久化了,这时候在按回滚就回不去了
ROLLBACK;  -- 回滚

SET autocommit = 1;  -- 恢复默认值

 

7、索引

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
  • 主键索引 (PRIMARY KEY)

    • 唯一的标识,主键不可重复,只能有一个列作为主键

  • 唯一索引(UNIQUE KEY)

    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引

  • 常规索引 (KEY/INDEX)

    • 默认的, index、key关键字来设置

  • 全文索引(FullText)

    • 在特定的数据库引擎下才有,MyISAM

    • 快速定位数据

 

8、权限管理和备份

8.1、用户管理
SQL yog 可视化管理

MySQL函数、事务、索引、权限管理和备份、规范数据库设计

 

SQL命令操作

用户表:mysql.user

本质:读这张表进行增删改查

-- 创建用户 create user 用户名 identified by '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('111111')

-- 重命名
RENAME USER kuangshen TO zhang

-- 用户授权 all privileges 全部的权限, 库.表
-- all privileges 除了给别人授权,其他都能干
GRANT USER PRIVILEGES ON *.* TO kuangshen2

-- 查询权限
SHOW GRANTS FOR kuangshen2  -- 查看指定用户权限
SHOW GRANTS FOR root@localhost

-- ROOT用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

-- 删除用户
DROP USER kuangshen
8.2、MySQL备份

为什么要备份:

  • 保证重要的数据不丢失

  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件(在date文件里面)

  • 在Sqlyog这种可视化工具中手动导出

    • 右键点击表,选择备份或导出

  • 使用命令行导出 mysqldump 命令行使用(cmd)

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school>d:/a.sql

#导入(先在命令行登录数据库,然后在use 要导入的数据库后在执行以下操作)
#登录的情况中,切换到指定的数据库
#source 备份文件
1.source d:/a.sql (推荐使用)
2.mysql -u用户名 -p密码 库名< 备份文件(不推荐)

假设你要备份数据库,防止数据丢失。

9、规范数据库设计

9.1、为什么需要设计

当数据库比较复杂的时候,我们就需要设计了

糟糕的数据库设计:

  • 数据冗余,浪费空间

  • 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】

  • 程序的性能差

良好的数据库设计:

  • 节省内存空间

  • 保证数据库的完整性

  • 方便我们开发系统

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理的数据库需求

  • 概要设计:设计关系图E-R图

MySQL函数、事务、索引、权限管理和备份、规范数据库设计

9.2、三大范式

为什么需要数据规范化?

  • 信息重复

  • 更新异常

  • 插入异常(无法正常显示信息)

  • 删除异常(丢失有效的信息)

三大范式(了解)

第一范式(1NF)

原子性:保证每一列不可再分

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提:满足第一、er范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

规范性 和 性能的问题

关联查询的表不得超过三张表

  • 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要

  • 在规范性能的问题的时候,需要适当的考虑以下规范性!

  • 故意给某些增加一些冗余的字段。(从多表查询中变为单表查询)

  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

  •  

上一篇:js几种加密/解密方法


下一篇:AngularJS学习