文章目录
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')