MySQL自学笔记(三)

三、MySql函数

3.1 常用函数

-- 数学函数
-- 绝对值
SELECT ABS(-8)
-- 向上取整
SELECT CEILING(9.3)
-- 向下取整
SELECT FLOOR(9.3)
-- 生成0~1之间随机数
SELECT RAND()
-- 判读一个数的符合 返回 正数1，负数-1，0返回0
SELECT SIGN(2)

-- 字符串函数 
-- 返回字符串长度
SELECT CHAR_LENGTH('asd123123')
-- 拼接字符串
SELECT CONCAT('1','2')
-- 替换字符串  12245678
SELECT INSERT('12345678',2,2,'22')
-- 转大写
SELECT UPPER('asdasd')
-- 转小写
SELECT LOWER('ASDASD')
-- 查询指定字符串出现的位置 3
SELECT INSTR('1234','3')
--替换指定字符串 156456
SELECT REPLACE('123456','23','56')
--截取指定长度字符串 234
SELECT SUBSTR('123456',2,3)
--反转字符串 4321
SELECT REVERSE('1234')

-- 时间、日期函数

SELECT CURRENT_DATE
SELECT CURRENT_TIME
SELECT CURRENT_TIMESTAMP

SELECT NOW()
SELECT LOCALTIMESTAMP()
SELECT SYSDATE()

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

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

3.2 聚合函数

函数名称	描述
CONUT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

SELECT COUNT(id) FROM tablename
SELECT COUNT(1) FROM tablename
SELECT COUNT(*) FROM tablename
/* 从执行效果来看
count(1) and count(*)：
基本没差别，都是求表的总行数
count(*)包括了所有的列，相当于求记录总行数，在统计结果的时候，不会忽略NULL
count(1) and count(列名)：
（1） count(1) 会统计表中的所有的记录数，不会忽略NULL，包含字段为null 的记录。
（2） count(列名) 会统计该列字段在表中出现的次数，会忽略字段为null 的情况，即不统计字段为null 的记录。 
从执行效率来看
若列名为主键，count(列名)会比count(1)快  
若列名不为主键，count(1)会比count(列名)快  
若表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
若表有主键，则 select count（主键）的执行效率是最优的  
若表只有一个字段，则 select count（*）最优。
*/

SELECT SUM(parent_id) FROM tdb_cates 
SELECT AVG(parent_id) FROM tdb_cates

3.3 数据级别的MD5加密

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

CREATE TABLE `testmd5`(
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `pwd` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO testmd5 VALUES(1,'张三','123123');
INSERT INTO testmd5 VALUES(2,'李四','123123');
INSERT INTO testmd5 VALUES(3,'王五','123123');
INSERT INTO testmd5 VALUES(4,'赵六','123123');
INSERT INTO testmd5 VALUES(5,'tom','123123');
INSERT INTO testmd5 VALUES(6,'jack','123123');
INSERT INTO testmd5 VALUES(7,'alixe','123123');

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

INSERT INTO testmd5 VALUES(8,'aasdf',MD5('123123'));

SELECT * FROM testmd5 WHERE name='aasdf' AND pwd= MD5('123123')

四、事务

事务原则：ACID 原子性 一致性 隔离性 持久性 （脏读、幻读……）

• Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离导致一些问题

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.02 sec)

脏读

指一个事务读取了另外一个事务未提交的数据

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。

虚读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务

set autocommit=0 /*关闭*/
START TRANSACTION
/*数据库操作*/
--提交
COMMENT
-- 回滚
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(20) 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高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

参考资料：CodingLabs - MySQL索引背后的数据结构及算法原理

5.1 主键索引

• 主键索引 （PRIMARY KEY）

主键不可重复

• 唯一索引 （UNIQUE KEY）

避免重复的列出现 唯一索引可以重复出现 多个列都可以表示唯一索引

• 常规索引 （KEY/INDEX）

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

• 全文索引 （FullText）

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

5.2 测试索引

CREATE DEFINER=`root`@`localhost` FUNCTION `mock_data`() RETURNS int(11)
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),'123123@qq.com',FLOOR(CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000))),CEILING(RAND()*(2)),UUID(),FLOOR(RAND()*100));
	SET i = i + 1 ;
END WHILE;
RETURN i;
END
-- 执行
SELECT mock_data();


SELECT * FROM app_user WHERE `name`='用户999999'
> OK
> 时间: 0.877s


-- CREATE INDEX id_tablename_字段 ON tablename(`字段`)
CREATE INDEX id_app_user_name ON app_user(`name`)

EXPLAIN SELECT * FROM app_user WHERE `name`='用户999999'
> OK
> 时间: 0s

5.3 索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加载常用来查询的字段上

索引的数据结构

Hash 类型索引

BTREE：默认类型

六、权限管理和备份

6.1 用户管理

SQL 命令

-- 创建用户
CREATE USER lxf IDENTIFIED BY '123123'

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

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

-- 用户授权 无法给他人授权
GRANT ALL PRIVILEGES ON *.* TO lxf

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

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM lxf

-- 删除用户

6.2 MySQL 备份

1. 直接拷贝物理文件
2. 在数据库管理工具中手动导出

• 在想要导出的数据库右键，选择备份或导出
  

1. 使用命令导出 mysqldump 命令

mysqldump -h localhost -u root -p123456 -P 13306 school student >E:/a.sql

1. 数据库导入

C:\Users\82127>mysql -uroot -p123456 -P13306
mysql> source E:/a.sql

七、 数据库设计

7.1 设计

7.2 三大范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三 范式。