2021-01-31

表连接及子查询

–表连接【内连接 ，外连接，左连接，右链接】
SELECT DATABASE();
classes``classes
–内连接【selectFROM 表1 INNER JOIN 表2 ON 表1列=表2列】
–将stdents2与classes表进行内连接[迪卡尔积]
SELECT * FROM students2 INNER JOIN classes;
–只有students2.cls.id=classes.id条件
SELECT * FROM students2 AS s INNER JOIN classes AS c ON s.cls_id=c.id ;
–显示学生的所有信息，但只显示班级名称
SELECT s. ,c.name FROM students2 AS s INNER JOIN classes AS c ON s.cls_id=c.id ;

–将班级名称显示在第一列
SELECT c.name ,s.* FROM students2 AS s INNER JOIN classes AS c ON s.cls_id=c.id ;
–查询有能够对应班级的学生以及班级信息，按照班级进行排序
SELECT c.name ,s.* FROM students2 AS s INNER JOIN classes AS c ON s.cls_id=c.id ORDER BY c.id;
–当同一个班级时，按照学生的id进行从小到大排序
SELECT c.name ,s.* FROM students2 AS s INNER JOIN classes AS c ON s.cls_id=c.id ORDER BY c.id ,s.id ASC;
–左连接【selectFROM 表1 LEFT JOIN 表2 ON 表1列=表2列】
–使用students2表左连接classes 表【注意 ；1 左连接必须有on条件 ；2 左连接以左表为主】
SELECT * FROM students2 s LEFT JOIN classes c ON s.cls_id=c.id ;
–students2表左连接classes表，并且查看班级为null的数据
SELECT * FROM students2 s LEFT JOIN classes c ON s.cls_id=c.id WHERE c.id=NULL;
–左连接并且查询s.is_del=1,并且s.name="jack"的数据
SELECT * FROM students2 s LEFT JOIN classes c ON s.cls_id=c.id HAVING s.is_del=1 AND s.name =“jack”;
–右连接【【selectFROM 表1 RIGHT JOIN 表2 ON 表1列=表2列】】

–子查询
–显示最高的男生身高以及姓名【注；select MAX(high),NAME FROM students2 WHERE gender= 1; 报错】
–1，显示所有学生的身高及其姓名，2 指定条件为查询最高的男生身高
SELECT NAME,high FROM students2 s WHERE s.high =(SELECT MAX(high) FROM students2 WHERE gender=1);
–查询出身高高于平均身高的学生信息
SELECT NAME,high FROM students2 WHERE high >(SELECT AVG(high) FROM students2);
–查询学生班级号cls_id 能够对应的学生信息；

SELECT * FROM students2 WHERE cls_id IN(SELECT id FROM classes);

–查询最大年龄为女生的id
SELECT * FROM students2 WHERE age=(SELECT MAX(age) FROM students2 ) AND gender=2;