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

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

父类

categoryIdcategoryName2信息技术3软件开发5美术设计

子类

categoryIdPIDcategoryName43数据库63web开发75ps技术82办公信息

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

-- ==== 自连接 === 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: 数据库本地编程（出发器、存储过程）和数据库集群有冲突