本文整理自网络，侵删。
1、复杂SQL查询
1.1、单表查询
(1)选择指定的列
[例]查询全体学生的学号和姓名
select Sno as 学号,Sname as 姓名 from student; select Sno,Sname from student;
(2）查询全部列
[例]查询全体学生的详细信息
select * from student;
(3）对查询后的指定列进行命名
[例]查询全部学生的“姓名”及其“出生年”两列
select Sname as 姓名,(2014-Sage) as 出生年 from student; select Sname ,(2014-Sage) from student;
(4）消除取值重复的行
[例]查询选修了课程的学生学号
select distinct Sno as 选修了课程的学生学号 from SC; select distinct Sno from SC;
(5）选择表中若干元组（满足条件的）
1.2、大小比较
[例]查询计算机系（IS）全体学生名单
select Sname as 学生姓名 from student where Sdept='IS';
[例]查询全体20岁以下的学生姓名和年龄
select Sname as 姓名,Sage as 年龄 from student where Sage<20;
1.3、确定范围
[例]查询所有在20到23岁（含20和23）的学生姓名、系别和年龄
select Sname as 姓名,Sdept as 系别,Sage as 年龄 from student where Sage between20 and 23;
注意between 小数 and 大数。
1.4、in和not in确定集合
[例]查询IS系和CS系的全体学生姓名和性别
select Sname as 姓名,Ssex as 性别 from student where Sdept='IS' or Sdept='CS';
select Sname as 姓名,Ssex as 性别 from student where Sdept in ('IS','CS');
[例]查询既不属于IS系，也不属于MA系的学生姓名和年龄
select Sname as 姓名,Sage as 年龄 from student where Sdept !='IS'and Sdept!='CS';
select Sname as 姓名,Sage as 年龄 from student where Sdept not in('IS','MA');
1.5、字符匹配（like % _ ）
[例]查询所有姓李的学生姓名和性别
select Sname as 姓名,Ssex as 性别 from student where Sname like '李%';
[例]查询所有“2002”年入学的学生学号、姓名和系别
select Sno as 学号,Sname as 姓名,Sdept as 系别 from student where Sno like'2002%';
[例]查询所有不姓“刘”的学生信息
select * from student where Sname not like'刘%';
[例]查询名称含有“数据”的课程号、课程名及学分
select Cno as 课程号,Cname as 课程名,Ccredit as 学分 from course where Cname like '%数据%';
总结：
select * from course where cname like '%数据%';包含数据的字符串 select * from course where cname like '数据%';以数据开头的字符串 select * from course where cname like '%数据'; 以数据结尾的字符串
1.6、涉及空值的查询（is null）
[例]查询没有先修课的课程号和课程名
select Cno as 课程号,Cname as 课程名,Cpno from course where Cpno is null;
[例]查询所有有成绩的学生学号、课程号及成绩
select Sno as 学号,Cno as 课程号,Grade as 成绩 from SC where Grade is not null;
1.7、查询结果排序（order by ）
[例]查询选修了3号课程的学生学号和成绩，结果按成绩降序排列。
select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade desc;
[例]查询选修了3号课程的学生学号和成绩，结果按成绩升序排列。
select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade asc;
1.8、聚集函数
count、sum、avg、max、min
[例]查询学生总数
select count(*) as 学生总数 from student;
[例]查询所有课程的总学分
select sum(Ccredit) as 所有课程总学分 from course;
[例]查询全体学生平均年龄
select avg(Sage) as 平均年龄 from student;
[例]查询1号课程的最高分
select max(Grade) as 1号课程的最高分 from SC where Cno=1;
1.9、分组统计（group by）
[例]查询男女学生各有多少人。
select Ssex as 性别,count(*) as 人数 from student group by Ssex;
[例]查询每个课程的课程号和平均分。
select Cno as 课程号,avg(Grade) as 平均分 from SC group by Cno;
【例】查询选修了3门课程以上（含3门）的学生学号和选修课程数。
select Sno as 学号 ,count(course.Cno) as 选修课程数 From SC,course Where course.Cno=SC.Cno Group by Sno Having Count(course.Cno)>=3;
having 关键字后面直接跟聚集函数
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
【例】查询选修了2门课程以上（含2门，但不含1号课程）,学生学号和选修课程数。
select Sno as 学号 ,count(course.Cno) as 选修课程数 From SC,course Where course.Cno=SC.Cno and course.Cno !=1 Group by Sno Having Count(course.Cno)>=2;
【例】查询不及格门数2门以上的学生学号。
Select Sno from sc Where sc.Grade<60 Group by Sno Having count(Cno)>=2;
【例】查询有2名以上（含2名）学生选修了的课程号和选修人数。
Select Cno,count(Sno) From SC Group by Cno Having count(sno)>=2
2、连接查询
相关阅读 >>
c#连接sqlserver数据库、插入数据、从数据库获取时间示例
python 操作mysql数据中fetchone()和fetchall()方式
springboot之使用springdatajpa的自定义sql方式
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
