Day04 狂神说Java-MySQL＞P20-P25

P20 联表查询JoinOn详解

7种Join查询

-- 查询参加了考试的同学(学号、姓名、科目编号、分数)
SELECT * FROM student
SELECT * FROM result
/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接方式查询
确定交叉点
判断条件 学生表中的StudentNo = 成绩表StudentNo
*/
-- join on 连接查询
-- where   等值查询
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.studentNo

-- Right Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.studentNo

-- Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.studentNo

-- 查询缺考的同学
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.studentNo
WHERE StudentResult IS NULL

-- 思考题(查询了参加考试的同学：学号、学生姓名、科目名和分数)
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo

-- 我要查询哪些数据
-- 从哪几个表中 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两种表然后再慢慢增加

P21 自连接及联表查询练习

-- 自连接
CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 自己的表跟自己的表连接，核心：一张表拆为两张一样的表
-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
-- 查询学员所属年级(学号、姓名、年级名称)
SELECT studentNo,studentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`
-- 查询科目所属的年级（科目名称、年级名称）
SELECT subjectName,gradeName
FROM `subject` s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`

P22 分页和排序

-- 分页Limit 排序order by
-- 排序：升序ASC，降序DESC
-- ORDER BY
-- 查询的结果 根据成绩降序 排序
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'
ORDER BY StudentResult ASC

-- 分页
-- 为什么要分页
-- 缓解数据库压力   现在是瀑布流
-- 分页，每页只显示五条数据
-- 语法limit当前页，页面大小
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'
ORDER BY StudentResult ASC
LIMIT 1,5 -- 起始和步长

-- (n-1) * pagesize, pagesize
-- n是页数 pagesize是每页显示的数据条目
-- 数据总数/页面大小 = 总页数

P23 子查询和嵌套查询

-- 子查询
-- 在where语句中嵌套一个子查询语句
-- 1. 查询数据库结构-1的所有考试结果(学号、科目名和成绩)，降序排列
-- 使用连接查询
SELECT `StudentNo`,`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '数据库结构-1'
ORDER BY StudentResult DESC

-- 使用子查询
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE SubjectNo = (
-- 查询所有数据库结构-1 的学生学号
-- 由里即外
      SELECT SubjectNo
      FROM `subject`
      WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80

SELECT `StudentNo`,`StudentName`
FROM student s
WHERE (
      SELECT StudentNo
      FROM result
      StudentResult>=80
)

-- 在这个基础上增加一个科目，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80 AND `SubjectNo` (
     SELECT `SubjectNo` FROM `subject`
     WHERE `SubjectName` = '高等数学-2'
)

-- 查询课程为高等数学-2 且分数不小于80的同学的学号和姓名
SELECT 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 `SubjectName` = '高等数学-2' AND StudentResult >= 80

P24 MySQL常用函数

常用函数和聚合函数

-- 常用函数
-- 数学运算
SELECT ABS(-8)
SELECT CEILING(9.4)
SELECT FLOOR(9.4)
SELECT RAND()
SELECT SIGN(-2)
-- 字符串函数
SELECT CHAR_LENGTH('哈哈')
SELECT CONCAT('我','爱','你')
SELECT INSERT ('我爱编程',1,2,'超级热爱')
SELECT LOWER('gfwshgweg')
SELECT UPPER('fwfffw')
SELECT INSTR('kuangshen','h')
SELECT REPLACE('坚持就能成功','坚持','成功')
SELECT SUBSTR('坚持就能成功',4,6)
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 MIN(NOW())
-- 系统用户
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

P25 聚合函数及分组过滤

SELECT COUNT(studentname) FROM student; -- 查询字段，会忽略所有null值
SELECT COUNT(*) FROM student; -- 不会忽略null 相当于所有列 计算行数
SELECT COUNT(1) FROM student; -- 不会忽略null 相当于忽略所有列计算行数
SUM(`StudentResult`) AS 总和 FROM result
AVG(`StudentResult`) AS 平均值 FROM result
MAX(`StudentResult`) AS 最大值 FROM result
MIN(`StudentResult`) AS 最小值 FROM result

-- 查询不同课程的平均分、最高分和最低分
-- 根据不同课程进行分组
SELECT subjectname, AVG(`StudentResult`),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
-- where AVG(`StudentResult`)>80
GROUP BY r.subjectno
HAVING AVG(`StudentResult`)>80