Java自學-MySQL(5)

1、自連接配接及聯表查詢練習

/*
自連接配接
   資料表與自身進行連接配接

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
    查詢父欄目名稱和其他子欄目名稱
*/

-- 建立一個表
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','辦公資訊');

-- 編寫SQL語句,将欄目的父子關系呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然後将這兩張表連接配接查詢(自連接配接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學資訊(學号,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學号,學生姓名,年級名)
SELECT studentno AS 學号,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學号 學生姓名 科目名稱 成績)
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'

2、分頁和排序

/*============== 排序 ================
文法 : ORDER BY
   ORDER BY 語句用于根據指定的列對結果集進行排序。
   ORDER BY 語句預設按照ASC升序對記錄進行排序。
   如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。
   
*/

-- 查詢 資料庫結構-1 的所有考試結果(學号 學生姓名 科目名稱 成績)
-- 按成績降序排序
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

/*============== 分頁 ================
文法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好處 : (使用者體驗,網絡傳輸,查詢壓力)

推導:
   第一頁 : limit 0,5
   第二頁 : limit 5,5
   第三頁 : limit 10,5
   ......
   第N頁 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:頁碼,pageSize:單頁面顯示條數]
   
*/

-- 每頁顯示5條資料
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 , studentno
LIMIT 0,5

-- 查詢 JAVA第一學年 課程成績前10名并且分數大于80的學生資訊(學号,姓名,課程名,分數)
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='JAVA第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10

3、子查詢和嵌套查詢

/*============== 子查詢 ================
什麼是子查詢?
   在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
   嵌套查詢可由多個子查詢組成,求解的方式是由裡及外;
   子查詢傳回的結果一般都是集合,故而建議使用IN關鍵字;
*/

-- 查詢 資料庫結構-1 的所有考試結果(學号,科目編号,成績),并且成績降序排列
-- 方法一:使用連接配接查詢
SELECT studentno,r.subjectno,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=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

-- 查詢課程為 高等數學-2 且分數不小于80分的學生的學号和姓名
-- 方法一:使用連接配接查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80

-- 方法二:使用連接配接查詢+子查詢
-- 分數不小于80分的學生的學号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基礎上,添加需求:課程為 高等數學-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等數學-2'
)

-- 方法三:使用子查詢
-- 分步寫簡單sql語句,然後将其嵌套起來
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名學生的成績資訊(學号,姓名,分數)
   使用子查詢,查詢郭靖同學所在的年級名稱
*/

4、MySQL常用函數

資料函數

SELECT ABS(-8);  /*絕對值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随機數,傳回一個0-1之間的随機數*/
 SELECT SIGN(0); /*符号函數: 負數傳回-1,正數傳回1,0傳回0*/

字元串函數

SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*傳回字元串包含的字元數*/
 SELECT CONCAT('我','愛','程式');  /*合并字元串,參數可以有多個*/
 SELECT INSERT('我愛程式設計helloworld',1,2,'超級熱愛');  /*替換字元串,從某個位置開始替換某個長度*/
 SELECT LOWER('KuangShen'); /*小寫*/
 SELECT UPPER('KuangShen'); /*大寫*/
 SELECT LEFT('hello,world',5);   /*從左邊截取*/
 SELECT RIGHT('hello,world',5);  /*從右邊截取*/
 SELECT REPLACE('狂神說堅持就能成功','堅持','努力');  /*替換字元串*/
 SELECT SUBSTR('狂神說堅持就能成功',4,6); /*截取字元串,開始和長度*/
 SELECT REVERSE('狂神說堅持就能成功'); /*反轉
 
 -- 查詢姓周的同學,改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 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 VERSION();  /*版本*/
 SELECT USER();     /*使用者*/

5、聚合函數及分組過濾

-- 聚合函數
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推薦*/
 
 -- 從含義上講，count(1) 與 count(*) 都表示對全部資料行的查詢。
 -- count(字段) 會統計該字段在表中出現的次數，忽略字段為null 的情況。即不統計字段為null 的記錄。
 -- count(*) 包括了所有的列，相當于行數，在統計結果的時候，包含字段為null 的記錄；
 -- count(1) 用1代表代碼行，在統計結果的時候，包含字段為null 的記錄 。
 /*
 很多人認為count(1)執行的效率會比count(*)高，原因是count(*)會存在全表掃描，而count(1)可以針對一個字段進行查詢。其實不然，count(1)和count(*)都會對全表進行掃描，統計所有記錄的條數，包括那些為null的記錄，是以，它們的效率可以說是相差無幾。而count(字段)則與前兩者不同，它會統計該字段不為null的記錄條數。
 
 下面它們之間的一些對比：
 
 1）在表沒有主鍵時，count(1)比count(*)快
 2）有主鍵時，主鍵作為計算條件，count(主鍵)效率最高；
 3）若表格隻有一個字段，則count(*)效率較高。
 */
 
 SELECT SUM(StudentResult) AS 總和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;
題目：

 -- 查詢不同課程的平均分,最高分,最低分
 -- 前提:根據不同的課程進行分組
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where寫在group by前面.
 要是放在分組後面的篩選
 要使用HAVING..
 因為having是從前面篩選的字段再篩選，而where是從資料表中的>字段直接進行的篩選的
 */

Java自學-MySQL(5)

1、自連接配接及聯表查詢練習

2、分頁和排序

3、子查詢和嵌套查詢

4、MySQL常用函數

5、聚合函數及分組過濾

繼續閱讀

ElasticSearch:Rest API操作

一張圖看懂OSPF鄰接關系建立及封包類型

Java自學--IO操作（1）File類

電子工程師名片——USB裝置枚舉過程

SQL資料庫優化--基礎

MyBatis-Plus allEq()的用法

UDP 協定解析

資料中台選型必讀（二）：資料中台如何搭建中繼資料管理中心

ES優化實戰-通過開啟copy_to提升一倍的檢索性能

Laravel資料庫擷取整合資料方法技巧合集（總）

圖解elasticsearch的_source、_all、store和index

自學Linux Shell12.4-for指令

中文排序規則

Mysql 8 - 檢查限制

網絡層 ICMP與ping：投石問路的偵察兵

mysql5.7的sql優化