学习内容:
1.排序 ORDER BY升序,ORDER BY DESC降序
2.limit 分页查询
3.子查询 IN()
4.GROUP BY、HAVING 分组,筛选
5.函数
一.排序
有升序和降序
语法: ORDER BY升序,ORDER BY DESC降序
例:SELECT * FROM result ORDER BY studentresult DESC;
二.limit
分页查询功能
二.子查询
等于嵌套一个表在里面进行查询
语法: IN()
例:SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (SELECT StudentNo FROM result WHERE Studentresult>=80 && subjectNo IN(
SELECT subjectNo FROM `subject` WHERE subjectName='高等数学-2'));
#子查询案例
CREATE TABLE department(
did INT(2)PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20),
mdesc VARCHAR(20)
)
CREATE TABLE employees(
eid INT(2)PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
epwd VARCHAR(20),
did INT(2)
)
SELECT e.ename,e.did FROM employees e WHERE e.did IN(SELECT d.did FROM department d WHERE d.dname='研发部');
四.GROUP BY、HAVING
GROUP BY是分组,把相应数据分出独立的一组,而HAVING 是筛选,HAVING 的使用条件是要分组后才能使用
语法:GROUP BY HAVING
例:
#GROUP BY, HAVING,函数+右连接查询
SELECT sb.subjectname AS '课程名',MAX(r.StudentResult) AS '最高分',MIN(r.StudentResult) AS '最低分',AVG(r.StudentResult) AS '平均分' FROM `subject` sb LEFT JOIN result r ON sb.SubjectNo=r.SubjectNo
GROUP BY sb.SubjectName HAVING AVG(r.StudentResult)>60;
五.函数
SQL提供一些函数方便使用,可以在W3C找到
例如:
SELECT sb.subjectname AS '课程名',MAX(r.StudentResult) AS '最高分',MIN(r.StudentResult) AS '最低分',AVG(r.StudentResult) AS '平均分' FROM `subject` sb LEFT JOIN result r ON sb.SubjectNo=r.SubjectNo
GROUP BY sb.SubjectName HAVING AVG(r.StudentResult)>60;
http://www.w3school.com.cn/sql/sql_functions.asp
1.排序 ORDER BY升序,ORDER BY DESC降序
2.limit 分页查询
3.子查询 IN()
4.GROUP BY、HAVING 分组,筛选
5.函数
一.排序
有升序和降序
语法: ORDER BY升序,ORDER BY DESC降序
例:SELECT * FROM result ORDER BY studentresult DESC;
二.limit
分页查询功能
语法:LIMIT 0,10; 第一个参数代表从哪行开始,从0开始算,第二个参数代表显示多少行数据
例:SELECT st.StudentNo AS "学号",st.StudentName AS "姓名",sb.subjectName AS "JAVA第一学年",re.StudentResult AS "学生成绩" FROM student st,`subject` sb,result re WHERE st.StudentNo=re.StudentNo && re.StudentResult>80 && sb.SubjectName="JAVA第一学年" ORDER BY StudentResult DESC LIMIT 0,10;
二.子查询
等于嵌套一个表在里面进行查询
语法: IN()
例:SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (SELECT StudentNo FROM result WHERE Studentresult>=80 && subjectNo IN(
SELECT subjectNo FROM `subject` WHERE subjectName='高等数学-2'));
#子查询案例
CREATE TABLE department(
did INT(2)PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20),
mdesc VARCHAR(20)
)
CREATE TABLE employees(
eid INT(2)PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
epwd VARCHAR(20),
did INT(2)
)
SELECT e.ename,e.did FROM employees e WHERE e.did IN(SELECT d.did FROM department d WHERE d.dname='研发部');
四.GROUP BY、HAVING
GROUP BY是分组,把相应数据分出独立的一组,而HAVING 是筛选,HAVING 的使用条件是要分组后才能使用
语法:GROUP BY HAVING
例:
#GROUP BY, HAVING,函数+右连接查询
SELECT sb.subjectname AS '课程名',MAX(r.StudentResult) AS '最高分',MIN(r.StudentResult) AS '最低分',AVG(r.StudentResult) AS '平均分' FROM `subject` sb LEFT JOIN result r ON sb.SubjectNo=r.SubjectNo
GROUP BY sb.SubjectName HAVING AVG(r.StudentResult)>60;
五.函数
SQL提供一些函数方便使用,可以在W3C找到
例如:
SELECT sb.subjectname AS '课程名',MAX(r.StudentResult) AS '最高分',MIN(r.StudentResult) AS '最低分',AVG(r.StudentResult) AS '平均分' FROM `subject` sb LEFT JOIN result r ON sb.SubjectNo=r.SubjectNo
GROUP BY sb.SubjectName HAVING AVG(r.StudentResult)>60;
http://www.w3school.com.cn/sql/sql_functions.asp