2021.9.7使用DQL查询数据(一)

USE school;
CREATE TABLE result(
stu_id INT(4),
sub_no INT(4),
exam_date DATE,
score DOUBLE(5,2)
);

学号1-14 科目1-8 成绩30-100 保留两位小数
INSERT INTO result VALUES
(FLOOR(RAND()*14+1),FLOOR(RAND()*8+1),NOW(),
ROUND(RAND()*70+30,2))

IN嵌套查询
SELECT stu_id AS'刘同学学号',score AS '成绩' FROM result WHERE
stu_id IN(SELECT stu_id FROM student WHERE stu_name LIKE '刘%');

关联:
SELECT stu_name,sub_name,score FROM result r
JOIN student s ON r.`stu_id`=s.`stu_id`
JOIN SUBJECT su ON r.`sub_no`=su.`sub_no`
WHERE stu_name LIKE '刘%';
自连接:
SELECT a.stu_id,a.`sub_no`,a.score,b.score FROM result a ,result b
WHERE a.`stu_id`=b.`stu_id` AND a.`sub_no`=b.`sub_no`
AND a.`score`>60 AND b.`score`<60 
自连接JOIN ON 版：
SELECT a.stu_id,a.`sub_no`,a.score,b.score FROM result a 
JOIN result b ON
a.`stu_id`=b.`stu_id` AND a.`sub_no`=b.`sub_no`
WHERE a.`score`>60 AND b.`score`<60 

SELECT a.categoryName AS '父栏目名称', 
b.categoryName AS '子栏目名称' FROM 
category a JOIN category b
ON a.categoryId=b.pid

以谁做主表 不管数据关联与否 主表数据始终存在
SELECT stu_name,score FROM result r
RIGHT JOIN student s ON r.`stu_id`=s.`stu_id`;

SELECT stu_name,score FROM student r
LEFT JOIN result s ON r.`stu_id`=s.`stu_id`;