Day14 MySQL基础07

Day14 MySQL基础07

4.7分组与过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT any_value(`subjectname`),AVG(`studentresult`)AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`-- 通过什么字段来分组
HAVING 平均分 >80

5.MySQL函数

5.1常用函数

-- 数学运算
SELECT ABS(-98)-- 绝对值
SELECT CEILING(8.8)-- 向上取整
SELECT FLOOR(8.8)-- 向下取整
SELECT RAND()-- 返回一个0-1之间的随机数
SELECT SIGN(999)-- 判断一个数的符号  负数-1 正数1

-- 字符串函数
SELECT CHAR_LENGTH('我c你m')-- 字符串长度
SELECT CONCAT('你马','个比的')-- 拼接字符串
SELECT INSERT('你好戈比的',1,2,'我艹你嘛')-- 查询，从某个位置开始替换某个长度
SELECT LOWER('DASDSDS')-- 小写
SELECT UPPER('dsdfdx') -- 大写
SELECT INSTR('sy','y')-- 返回第一次出现子串的索引
SELECT REPLACE('我抄你嘛','抄','艹')-- 替换出现指定的字符串
SELECT SUBSTR('我抄你嘛',2,3)-- 返回指定的子字符串,从第二个开始，截3个（原字符串，截取的位置，截取的长度）
SELECT REVERSE('你好戈比的')-- 反转字符串

-- 查询姓名L的同学  名字
SELECT * FROM `student`
WHERE `studentname`LIKE'L%'

SELECT REPLACE(`studentname`,'L','S') FROM `student`
WHERE `studentname`LIKE'L%'

-- 时间和日期函数（记住）
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()

5.2聚合函数

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

-- 聚合函数
-- 能够统计表中的数据  （想查询表中有多少个记录，就用count()）
SELECT COUNT(studentname)FROM student -- count(指定列(字段))，会忽略所有的null值 
SELECT COUNT(*)FROM student-- count(*)，不会忽略null值，本质 计算行数
SELECT COUNT(1)FROM student-- count(1)，不会忽略所有的null值  本质 计算行数


SELECT SUM(`studentresult`)AS 总和 FROM`result`
SELECT AVG(`studentresult`)AS 平均 FROM`result`
SELECT MAX(`studentresult`)AS 最高分 FROM`result`
SELECT MIN(`studentresult`)AS 最低分 FROM`result`




-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT any_value(`subjectname`),AVG(`studentresult`)AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`-- 通过什么字段来分组
HAVING 平均分 >80

5.3数据库级别的MD5加密

什么是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,'sy','123456'),(2,'ltt','223456'),(3,'sb','323456')

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd)

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


-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'mimi',MD5('123456'))


-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='ltt' AND pwd=MD5('223456')

select总结

6.事务

6.1什么是事务

要么都成功，要么都失败

---

1.SQL执行 A给B转账 A 1000 ---->200 B 200

2.SQL执行 B收到A的钱 A 800 ----> B400

---

将一组SQL放在一个批次中去执行

事务原则：ACID原则 原子性，抑制性，隔离性，持久性

原子性表示，这两个步骤一起成功，或一起失败，不能只发生其中一个动作

一致性，最终一致性，针对一个事务操作前与操作后的状态一致

持久性：事务没有提交，恢复到原装 事务已经提交，持久化到数据

事务一旦提交就不可逆

---

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

不可重复读，幻读（虚读）

---

执行事务

 -- 事务
-- mysql是默认开启事务提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认的）

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION  -- 标记一个事务的开始
-- 从这个之后的sql都在同一个事务内


-- 提交：持久化(成功)
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启自动提交

SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

---

模拟场景

-- 模拟转账
CREATE DATABASE BANK CHARACTER SET utf8 COLLATE utf8_general_ci
USE BANK
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.00),('B',1000.00)

-- 模拟转账：事务
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.索引

7.1索引的分类

• 主键索引 PRIMARY KEY

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

• 唯一索引（unique key）

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

• 常规索引（key/index）

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

• 全文索引（fulltext）

在特定的数据库引擎下才有，Myisam

快速定位数据

-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引   索引名  列名
ALTER TABLE school.`student` ADD  FULLTEXT INDEX`studentname`(`studentname`)

-- explain 分析SQL执行的状况
EXPLAIN SELECT*FROM student   -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('l')

SHOW INDEX FROM student

-- 增加一个全文索引   索引名  列名
ALTER TABLE school.`student` ADD  FULLTEXT INDEX`studentname`(`studentname`)

-- explain 分析SQL执行的状况
EXPLAIN SELECT*FROM student   -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('l')