MySQL(四)——子查询和嵌套查询

子查询和嵌套查询

- ===========where============
-- 查询数据库结构-1科目所有考试结果（学号 科目名 成绩）降序排列
-- 方式一：使用联表查询
SELECT StudentNo, r.SubjectName,SubjectResult
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
WHERE subjectName='数据库结构-1'
ORDER BY SubjectResult DESC;
-- 方式二：使用子查询（）
SELECT StudentNo, r.SubjectName,SubjectResult
FROM result r
-- 等价于WHERE subjectNo=xxxx（数据库-1编号）
WHERE subjectNo=(
-- 查询结果为数据库-1对应的编号
	SELECT SubjectNo FROM `subject` 
	WHERE subjectName ='数据库结构-1';
)
ORDER BY SubjectResult DESC;
-- 查询数据库-1 编号
SELECT SubjectNo FROM `subject` WHERE subjectName ='数据库结构-1';

注意子查询结果必须是一行，否则报错Subquery returns more than 1 row
解决方法：limit 1

分组和过滤

- 查询不同科目的平均分最高分和最低分 平均分大于80
-- 由于按照顺序执行，所以where不能限定分组后计算出的平均值等信息
-- 可以使用having
SELECT SubjectName,AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>=80;

函数

常用函数

• 数学运算

-- 数学运算
SELECT ABS(-10);-- 绝对值
SELECT CEIL(10.1);-- ceil和ceiling 向上取整
SELECT FLOOR(10.1);-- 向下取整
SELECT RAND();-- 返回0-1随机数
SELECT SIGN(NULL);-- 返回参数的符号 负数返回-1 正数返回1 
-- 0和非数字返回0 null返回null

• 字符串函数

-- 字符串函数
SELECT CHAR_LENGTH('fwebw');-- 返回字符串长度
SELECT CONCAT('1','2','3');-- 拼接字符串
SELECT INSERT('123456',1,3,'0');-- 先把第一个字符串的第1位起的0个字符去除
-- 然后再把第二个字符串插入到对应位置
SELECT LOWER('JIOji');-- 转小写字母
SELECT UPPER('wfdINOI');-- 转大写字母 
SELECT INSTR('asdasd','sd');-- 返回第一个字串出现的位置
SELECT REPLACE('123456789123','123','hhh');-- 把s1中s2部分替换为s3（所有s2）
SELECT SUBSTR('123456789',2,4);-- 截取字符串，从第2个字符开始截取4个字符
SELECT REVERSE('123465');-- 翻转字符串

-- 查询姓周的同学并把姓换为邹
SELECT REPLACE(studentName,'周','邹') FROM students
WHERE studentName LIKE '周%';

时间和日期函数

-- 时间和日期函数
SELECT CURRENT_DATE();-- 获取当前日期
SELECT CURDATE()-- 获取当前日期
SELECT CURRENT_TIME();-- 获取当前时间（时分秒）
SELECT SYSDATE();-- 获取系统时间
SELECT NOW();-- 获取时间
SELECT YEAR(NOW());-- 获取当前年
SELECT MONTH(NOW());-- 获取当前月
-- ······获取日时分秒同理

• 系统

-- 系统
SELECT SYSTEM_USER();
SELECT USER();-- 获取当前用户
SELECT VERSION(); -- 获取当前版本信息

聚合函数（常用）

函数	描述
COUNT()	计数
SUM()	求和
AVG()
MAX()	最大值
MIN()	最小值

-- ===========聚合函数========
-- count 查询数据条数，若有主键 查主键效率高
SELECT COUNT(studentname) FROM students;-- 会忽略所有null值
SELECT COUNT(*) FROM students;-- 不会忽略null
SELECT COUNT(1) FROM students;-- 不会忽略null
-- sum() avg() max() min()
SELECT SUM(id) FROM students;
SELECT AVG(id) FROM students;
SELECT MAX(id) FROM students;
SELECT MIN(id) FROM students;

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公开密钥认证或是数字签名等用途。 ——百度百科

MD5是不可逆的，即通过加密后的密文是不能够计算出加密前的明文的，但是，因为算法固定，相同明文的MD5秘文是相同的。因此，简单明文的MD5密文可以通过穷举暴力破解。

-- ============MD5=========
CREATE TABLE `testmd5`(
	`id` INT(10) 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');
-- md5密码
UPDATE testmd5 SET pwd=MD5(pwd);
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'hhh',MD5('qwerty'));
-- 验证 由于不可逆，可以把用户提交的密码进行md5运算后和库中比对
SELECT MD5('qwerty')=pwd FROM testmd5 WHERE id=4;-- 1
SELECT MD5('qwe')=pwd FROM testmd5 WHERE id=4;-- 0