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('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
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()
聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| … |
数据库级别MD5加密（拓展）
什么是MD5
主要增强算法复杂度不可逆性。
MD5不可逆，具体的MD5是一样的
MD5破解原理，背后有一个字典，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,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))
-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')
事务
什么是事务
事务原则 ： ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保持一致
隔离性(Isolation)
事务产生多并发时，互不干扰
持久性（Durability）–事务提交
事务一旦提交就不可逆转，被持久化到数据库中
执行事务：
-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启（默认的）
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQP都在同一个事务内
INSERT XX
INSERT XX
-- 提交 ： 持久化(成功)
COMMIT
-- 回滚： 回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点
模拟转账事务：
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 转账给B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到钱
COMMIT ; -- 提交事务
ROLLBACK ; -- 回滚
SET autocommit=1 -- 恢复默认值
索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
主键索引 （PRIMARY KEY）
唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 （UNIQUE KEY）
避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）
默认的，index,key关键字来设置
全文索引（FULLTEXT）
在特点的数据库引擎下才有，MyISAM
快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM 表
-- 增加一个索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名（字段名）
-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘')
测试索引
添加100万条数据测试索引对查询速度的提升
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入100万数据
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`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 接近半秒
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询99999条记录
-- id _ 表名_字段名
-- create index on 字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询一条记录
索引在小数据的时候，用处不大，但是在大数据的时候，区别十分明显
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree: 默认innodb 的数据结构
阅读： http://blog.codinglabs.org/articles/theory-of-mysql-index.html
权限管理和备份
用户管理
SQLyog 可视化管理
SQL命令操作
用户表：mysql.user
本质：对这张表进行，增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sanjin IDENTIFIED BY '123456'
-- 修改密码（修改当前密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR sanjin = PASSWORD('111111')
-- 重命名 rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2
-- 用户授权 ALL PRIVILEGES 全部的权限 库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO sanjin2
-- 查询权限
SHOW GRANTS FOR sanjin2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2
-- 删除用户
DROP USER sanjin2
MySQL备份
为什么备份：
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键选择备份和导出
规范数据库设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 E-R图
设计数据库的步骤（个人博客为例）
收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者某些主字段）
- 说说表（发表心情…id ,content ,time）
标识实体（把需求落地到每个字段）
标识实体之间的关系
- 写博客 user–>blog
- 创建分类 user–>category
- 关注 user–>user
- 友链–>links
- 评论 user–>user
三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 删除异常
无法正常显示异常
丢失有效的信息
三大范式
第一范式（1NF）
- 原子性：保证每一列不可再分
第二范式（2NF）
- 前提：满足第一范式
- 每张表只描述一件事情
第三范式（3NF）
- 前提：满足第一范式和第二范式
- 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范性和性能的问题
- 关联查询的表，不得超过三张表
- 考虑商业化的需求和目标（成本和用户体验） 数据库的性能更加重要
- 再规范性能的问题的时候，需要适当的考虑一下，规范性
- 故意给某些表加一些冗余的字段（从多表，变成单表）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
