MySQL数据库（二）：常用函数、MD5加密、事物、索引、权限管理、数据库备份、数据库设计三大范式

最新推荐文章于 2021-08-05 12:06:01 发布

遗憾无人似你

最新推荐文章于 2021-08-05 12:06:01 发布

阅读量364

点赞数

分类专栏： JavaWeb

本文链接：https://blog.csdn.net/qq_39870032/article/details/104263306

版权

JavaWeb 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

代码（续）

MySQL常用函数

数学运算

SELECT ABS(-8)  -- 绝对值 
SELECT CEILING(9.4) -- 向上取整 
SELECT FLOOR(9.4)  -- 向下取整 
SELECT RAND() -- 返回一个 0~1 之间的随机数 
SELECT SIGN(10)   -- 判断一个数的符号   0-0   负数返回-1，正数返回 1

字符串函数

SELECT CHAR_LENGTH('我只想做你的太阳') -- 字符串长度 
SELECT CONCAT('哈','撒','剋') -- 拼接字符串 
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度 
SELECT LOWER('Stephen Curry') -- 小写字母 
SELECT UPPER('Stephen Curry') -- 大写字母 
SELECT INSTR('Stephen Curry','e') -- 返回第一次出现的子串的索引 
SELECT REPLACE('坚持就能成功','坚持','努力') --    替换出现的指定字符串 
SELECT SUBSTR('我不相信坚持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置，截取的长度)
SELECT REVERSE('清晨我上马') -- 反转

查询姓周的同学，名字邹

SELECT REPLACE(studentname,'周','邹') FROM student 
WHERE studentname LIKE '周%'

时间和日期函数

SELECT CURRENT_DATE() -- 获取当前日期 
SELECT CURDATE() -- 获取当前日期 
SELECT NOW() -- 获取当前的时间 
SELECT LOCALTIME() -- 本地时间 
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW()) 
SELECT MONTH(NOW()) 
SELECT DAY(NOW()) 
SELECT HOUR(NOW()) 
SELECT MINUTE(NOW()) 
SELECT SECOND(NOW())

系统

SELECT SYSTEM_USER()
SELECT USER() 
SELECT VERSION()

MD5加密

CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

明文密码

INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),
(2,'lisi','123456'), (3,'wangwu','123456')

在这里插入图片描述
加密

UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

在这里插入图片描述

事物

要么都成功，要么都失败
一一一一一一一一一一一
SQL 执行：
A 给 B 转账
A 1000 —> 200
B 200
SQL 执行
B 收到 A 的钱
A 800 —> B 400
一一一一一一一一一一一
将一组SQL 放在一个批次中去执行

执行事物代码流程

-- mysql 是默认开启事务自动提交的 
SET autocommit = 0 /* 关闭 */ 
SET autocommit = 1 /* 开启（默认的） */
-- 手动处理事务 
SET autocommit = 0 -- 关闭自动提交
-- 事务开启 
START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
INSERT xx 
INSERT xx
-- 提交： 持久化 （成功！） 
COMMIT 
-- 回滚： 回到的原来的样子 （失败！） 
ROLLBACK
-- 事务结束 
SET autocommit = 1 -- 开启自动提交

新建一个shop数据库

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci 
USE shop

建表

CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(9) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

插入原始数据

INSERT INTO `account`(`name`,`money`)
VALUES('A',2000.00),('b',10000.00)

在这里插入图片描述

模拟转账

-- 关闭自动提交
SET autocommit=0;
-- 开启一个事物
START TRANSACTION
-- 转账
UPDATE `account` SET money=money-500 WHERE `name`='A'
UPDATE `account` SET money=money+500 WHERE `name`='b'
-- 提交事物，事物就被持久化了
COMMIT;
-- 如果没提交成功，数值回滚
ROLLBACK;
-- 恢复默认事物自动提交
SET autocommit=1;

在这里插入图片描述

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的分类：

主键索引（PRIMARY KEY ）

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

唯一索引（UNIQUE KEY）

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

常规索引（KEY/INDEX）

默认的， index。key 关键字来设置

全文索引（FullText）

在特定的数据库引擎下才有，MyISAM 快速定位数据

测试：新建app_user表，在其中插入1000000条数据，然后比较有索引和没索引的查询时间

建表

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

在这里插入图片描述

插入1000000条数据的代码

DELIMITER $$ 
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO 
     INSERT INTO
     app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用 户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i+1;
   END WHILE;
   RETURN i;
END;

执行插入数据代码

SELECT mock_data();

在这里插入图片描述

在没有索引的情况下查询“用户1”

SELECT * FROM app_user WHERE `name` = '用 户1'

在这里插入图片描述

创建索引

CREATE INDEX id_app_user_name ON app_user(`name`);

在有索引的情况下查询“用户1”

SELECT * FROM app_user WHERE `name` = '用 户1'

在这里插入图片描述

通过在数据量很大的库中测试，我们发现搜索时间在创建了索引后变得很小
结论：索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

权限管理

我们可以通过 SQLyog中的可视化管理
在这里插入图片描述也可以通过代码来管理

创建用户

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' 
CREATE USER czx IDENTIFIED BY '123456'

在这里插入图片描述

修改用户密码

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

-- 修改密码 （修改指定用户密码） 
SET PASSWORD FOR czx = PASSWORD('123456')

在这里插入图片描述

修改用户名

重命名 RENAME USER 原来名字 TO 新的名字 
RENAME USER czx TO czx2

在这里插入图片描述

用户授权

-- 用户授权 ALL PRIVILEGES 全部的权限 ， 库表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干   
GRANT ALL PRIVILEGES ON *.* TO czx2

在这里插入图片描述

查询权限

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

在这里插入图片描述

撤销权限

-- 撤销权限 REVOKE 哪些权限， 在哪个库撤销，给谁撤销 
REVOKE  ALL PRIVILEGES ON *.* FROM czx2

在这里插入图片描述

删除用户

-- 删除用户 
DROP USER czx2

在这里插入图片描述

MySQL备份

备份的好处：
1.保证重要的数据不丢失
2.数据转移

备份操作
在想要备份的数据库或者表上右键，备份/导出（这里选择导出）

规范数据库设计

当数据库比较复杂的时候，我们就需要对数据库进行设计了

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
程序的性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
方便我们开发系统

数据库设计三大范式:

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式
每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

数据库部分完…

遗憾无人似你

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库（二）：常用函数、MD5加密、事物、索引、权限管理、数据库备份、数据库设计三大范式

代码（续）MySQL常用函数数学运算SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一个 0~1 之间的随机数 SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1，正数返回 1字符串函数SE...
复制链接

扫一扫

专栏目录