MySQL3----分页，排序，子查询，分组和过滤，常用函数，聚合函数，数据库级别的MD5加密

自连接：自己的表和自己的表连接，核心：把一张表当成两张表用即可

父类

categoryId	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

categoryId	PID	categoryName
4	3	数据库
6	3	web开发
7	5	ps技术
8	2	办公信息

要求查询结果是：父类和子类相对应

-- ==== 自连接 ===
SELECT c1.`categoryid` AS '父栏目',c1.`categoryName` '父栏目名',c2.`categoryid` '子栏目',c2.`categoryName` '子栏目名'
FROM `category` c1
INNER JOIN `category` c2
ON c2.`pid` = c1.`categoryid`;

连表查询练习

-- === 连表连接联系 ===
-- 1.查询学生的所属年级（学生id，学生姓名，学生年级）
SELECT `studentno` '学生id',`studentname` '学生姓名',`gradeName` '所属年级'
FROM `student` s
LEFT JOIN `grade` g 
ON s.`gradeid` = g.`gradeid`;

-- 2.查询科目所属的年级 （subjectno subjectname gradename）
 SELECT `subjectno`,`subjectname`,`gradename`
 FROM `subject` s
 RIGHT JOIN `grade` g
 ON s.`gradeid` = g.`gradeid`;
 
 -- 3.查询参加 数据库结构-1 考试的学生 (学号，学生姓名，考试科目，考试成绩）
 -- ====1.重命名应该尽可能的清楚 2.要注意有无 ; 在句子末尾
 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`studentno` = r.`studentno` 
 INNER JOIN `subject` sub
 ON r.`subjectno` = sub.`subjectno` 
 WHERE `subjectname` = '数据库结构-1';

---

4.5 分页(limit)和排序(order by)

排序（ORDER BY）

-- ===== 分页 limit 和 排序order by =====
-- 排序：1,升序 ASC 2,降序 DESC 
-- 语法：order by + 字段名  +排序方式
-- 查询的结果根据成绩降序排序
  SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM `student` s
 INNER JOIN `result` r
 ON  r.`studentno` = s.`studentno` 
 INNER JOIN `subject` sub
 ON r.`subjectno` = sub.`subjectno` 
 WHERE `subjectname` = '数据库结构-1'
 ORDER BY `studentresult` DESC

语法：order by + 字段名 + 排序方式

排序

 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM `student` s
 INNER JOIN `result` r
 ON  r.`studentno` = s.`studentno` 
 INNER JOIN `subject` sub
 ON r.`subjectno` = sub.`subjectno` 
 WHERE `subjectname` = '数据库结构-1'
 ORDER BY `studentresult` DESC
 LIMIT 0,2
 -- 排序 limit (a,x) a是从第几条数据开始，0是第一条数据的位置；x是一次显示数据的条数
 -- 第 1 页 (0,5)  ,起始值规律：(1-1)*5
 -- 第 2 页 (5,5)  ,            (2-1)*5
 -- 第 3 页 (10,5)  ,           (3-1)*5
 -- 第 n 页 ((n-1)*5,5),        (n-1)*5
 -- pagesize : 一个页面容纳数据的最大条数
 -- 起始值： (n-1)*5
 -- 当前页: n;
 -- 总的页数： 数据条数除以pagesize向上取整


-- 查询 Java程序设计-1 考试成绩前10的学生 并且分数要大于80分的学生信息
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s 
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE sub.`subjectname` = 'Java程序设计-1' AND `studentresult` >= 80
ORDER BY `studentresult` DESC 
LIMIT 0,10

---

4.6 子查询

子查询在where里面嵌套一个查询语句

-- 分数不小于80分的学生的学号和姓名
-- 方式1 .`result`连表查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80

-- 高等数学-2 分数不小于80分的学生的学号和姓名
-- 方式1 .连表查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno` 
WHERE r.`studentresult` >= 80 AND `subjectname` = '高等数学-2'

-- 方式2 join on 和子查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80 AND `subjectno` = (
   SELECT `subjectno` FROM `subject`
   WHERE `subjectname` = '高等数学-2'
)

-- 方式3 单纯的嵌套子查询
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
   SELECT  `studentno`
   FROM `result`
   WHERE `studentresult` > 80 AND `subjectno` = (
   	    SELECT `subjectno` 
       	FROM `subject`
   	    WHERE `subjectname` ='高等数学-2'
   )
)	

-- 查询 C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 方式1：连表查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON `subjectname` = 'C语言-1' 
WHERE r.`subjectno` = sub.`subjectno`
ORDER BY `studentresult` DESC
LIMIT 0,5

-- 方式2 嵌套查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `subjectno` = (
   SELECT `subjectno` 
   FROM `subject`
   WHERE `subjectname` = 'C语言-1'
   )
ORDER BY `studentresult` DESC
LIMIT 0,5

4.7 分组和过滤

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

4.8 select 小结

SELECT [ALL|DISTINCT]
{|table|[table.field1[as alias1][,table.field2]][,...]]}
FROM table_name [as table_alias]
	[left |right|inner join table_name2] -- 联合查询
	[WHERE ...] -- 指定结果需满足的条件
	[GROUP BY] -- 指定结果需要按照哪几个字段来分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {offset,}row_count | row_countOFFSET offset}];--指定查询的记录从哪条到哪条

注意：[]括号代表可选，{}括号代表必选的

---

5. MySQL的函数

MySQL的官网地址.

5.1 MySQL的常用函数

-- =========== 常用函数 ===========
-- 数学运算
SELECT ABS(-99) -- 返回99 求绝对值函数 

SELECT CEILING(9.1)  -- 返回10 作用：向上取整

SELECT FLOOR(9.1) -- 返回9 作用： 向下取整

SELECT RAND() -- 返回一个0~1之间的随机数

SELECT SIGN(0) -- 判断一个值的符号，正值返回1，负值返回-1，0返回0

-- 字符串
SELECT CHAR_LENGTH('急急急') -- 返回字符串长度

SELECT CONCAT('我','爱','你') -- 拼接字符串

-- 替换字符串,从1开始计数，第一个数字是开始的位置，第二个数字是替换的长度，最后的是要替换的字符串
SELECT INSERT('今天晚饭要吃什么好呢？',7,5,'大众餐啊') 

SELECT LOWER('Swrici')  -- 转小写

SELECT UPPER('Swrici')  -- 转大写

SELECT INSTR('ILoveYou','you')  -- 查询字符在字符串中第一次出现的位置,大小写不敏感
  
SELECT REPLACE ('我要吃晚餐','晚','大');  -- 替换字符串

-- 截取字符串，第一个数字是位置，第二个数字是截取长度，不写第一个会截取到字符串末尾
SELECT SUBSTR('今天晚上不知道会不会下雨',3,5);  

SELECT REVERSE('今天晚上到底会不会下雨呢~')  -- 反转字符串

-- 查询姓张的同学，把张替换成王
SELECT REPLACE(`studentname`,'张','王') FROM student WHERE `studentname` LIKE '张%'

-- 时间和日期函数（记住）
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.1 聚合函数（这个才是真的常用）

函数名称	描述
count()	计数
sum()	求和
AVG()	求平均
max()	求最大值
min()	求最小值
…	…

-- ======= 聚合函数 =======
-- count(字段) count(*) count(1) 都能统计表中的数据
-- 对于主键使用 count(`主键`)更快一些
SELECT COUNT(`loginpwd`) 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 SUM(`studentresult`)/COUNT(*) AS '总和' FROM `result` -- 求平均值

SELECT AVG(`studentresult`) AS '平均分' FROM `result`; -- 求平均值

SELECT MAX(`studentresult`)  FROM `result` ; -- 最高分

SELECT MIN(`studentresult`) FROM `result`; -- 最低分

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

5.3 数据库级别的MD5加密（扩展）

什么是MD5?

主要增强了算法的复杂性和不可逆性
虽然MD5不可逆，但是具体的值的MD5是一样的

-- ========== 数据库md5加密 ===========
CREATE TABLE `testmd5`(
	`id` INT(5) NOT NULL ,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `testmd5`(`id`,`name`,`pwd`) VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456'),
(4,'刘六','123456'),
(5,'薛七','123456')
 
 UPDATE `testmd5` SET `pwd` = MD5(`pwd`) WHERE `id`='1';
 
 UPDATE `testmd5` SET `pwd` = MD5(`pwd`) WHERE `id` != 1;
 
 -- 插入时转换
 INSERT INTO `testmd5`(`id`,`name`,`pwd`) VALUE (6,'邱八',MD5('12345'))
 
 -- 如何校验， 将用户传递进来的值进行md5加密，然后进行匹配
 SELECT * FROM `testmd5` WHERE `name` = '李四' AND `pwd` = MD5('123456')

ps: 数据库本地编程（出发器、存储过程）和数据库集群有冲突