Mysql（2）

五、DQL语言（接上一篇）

7.分页(LIMIT)、排序(ORDER BY)、分组(GROUP BY)和过滤(HAVING)

排序

--排序  ASC升序 DESC降序
SELECT `studentname`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `student` s
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`LIKE '高%'
ORDER BY `studentresult` DESC

分页

--LIMIT
SELECT `studentname`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `student` s
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`LIKE '高%'
ORDER BY `studentresult` DESC
LIMIT 0,1

格式：LIMIT  起始页，页面大小

常用计算方法

分组和过滤

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足

SELECT `studentname`,`subjectname`,`studentresult`,AVG(studentresult) AS 平均值
FROM `result` r
INNER JOIN `student` s
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
GROUP BY r.`subjectno` ASC
HAVING 平均值 > 80

8.子查询和嵌套查询

执行顺序是由里到外，先执行最里边的括号内容

--子查询
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` FROM `result` WHERE `studentresult`> 80 AND `subjectno`='1000'
)

--嵌套查询（子查询嵌套子查询）
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` FROM `result` WHERE `studentresult`>80 AND `subjectno`=(
		SELECT `subjectno`FROM `subject` WHERE `subjectname` = '高等数学-1'
	)
)

 9.常用函数

常用函数汇总

Mysql官网参考手册

10.聚合函数（常用）

COUNT():

count(1)、count(*) 与 count(列名) 的区别

列名为主键，count(列名)会比count(1)快。
列名不为主键，count(1)会比count(列名)快。
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）。
如果有主键，则 select count（主键）的执行效率是最优的。
如果表只有一个字段，则 select count（*）最优。

11.数据库级别的MD5加密

小结 

六、事务

1.事务的ACID原则

ACID

参考链接https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
 

2.事物的隔离级别：

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

不可重复读：在一个事务内读取表的某一行数据，多次读取的结果不同（这个不一定是错误，只是某些场合不对）

虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

3.测试事务实现转账

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

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

-- 模拟转账
SET autocommit = 0; -- 关闭自动提交

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

COMMIT;
ROLLBACK;

SET autocommit = 1; -- 开启事务

七、索引

1.索引的分类

主键索引（PRIMARY KEY）:唯一的标识，不可重复，只有一列可作为主键

唯一索引（UNIQUE KEY）：避免重复的列出现，唯一索引可以重复，多个列都可标识为 唯一索引

常规索引（KEY / INDEX）：默认的，index，key来设置

全文索引（FullText）：

2.基础语法

3.SQL编程100万数据测试索引

-- 建表代码
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`eamil` 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,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建100W数据
DELIMITER $$

USE `school`$$

DROP FUNCTION IF EXISTS `mock_data`$$

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`,`eamil`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END$$

DELIMITER ;

执行插入100万数据    SELECT mock_data()

测试索引

没加索引的执行时间

添加索引

CREATE INDEX id_app_user_name ON `app_user`(`name`);

再查询看看执行时间，明显提升了性能

注意：索引在数据量小的时候，用处不大；在大数据的时候区别很明显

4.索引的原则

不是越多越好

不要对经常变动的数据加索引

小数据量的表不需要索引

索引一般加在场用来查询的字段上

八、权限管理和备份

方式一：可视化操作

方拾二：

九、数据库设计的三大范式

参考链接：https://www.cnblogs.com/wsg25/p/9615100.html

第一范式（1NF）

原子性：每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

确保数据表每一列数据都和主键直接相关，而不能间接相关