欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL常用SQL语句总结包含复杂SQL查询

程序员文章站 2022-06-22 09:13:42
1、复杂sql查询 1.1、单表查询 (1)选择指定的列 [例]查询全体学生的学号和姓名 select sno as 学号,sname as 姓名 from...

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、连接查询

(1)等值与非等值连接查询

[例]查询每个学生及其的选修课程情况

select student.sno as 学号,course.cno as 选修课号,sc.grade as 成绩 
from student,course,sc 
where student.sno=sc.sno and course.cno=sc.cno ;

(2)自身连接

[例]查询每个学生的间接选修课

select sc.sno as 学号,
first.cname as 直接选修课,
second.cname as 间接选修课
from sc,
course as first,
course as second
where first.cno=sc.cno
and first.cpno=second.cno;

(3)外连接

[例]查询所有学生选修课程情况(含没选修课程的学生)

select student.sno as 学号,
sname as 姓名,
sc.cno as 选修课程号
from student 
left outer join sc on student.sno=sc.sno;

join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据

join: 如果表中有至少一个匹配,则返回行
left join: 即使右表中没有匹配,也从左表返回所有的行
right join: 即使左表中没有匹配,也从右表返回所有的行
full join: 只要其中一个表中存在匹配,就返回行
union 操作符用于合并两个或多个 select 语句的结果集。
请注意,union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 select 语句中的列的顺序必须相同。

3 、嵌套查询

(1)带有in谓词的子查询( 属性 in (子查询的查询结果) )

【例】查询与王敏同学在同一个系的学生信息。

select *
from student
where sdept in (
 select sdept
 from student
 where sname='王敏'
);

【例】查询不与王敏同学不在同一个系的学生信息。

select *
from student
where sdept not in (
 select sdept
 from student
 wheresname='王敏'
);

【例】查询选修了课程名是“信息系统”的学生学号和姓名。

select student.sno as 学号, sname as 姓名
from student,sc
where student.sno=sc.sno and cno in (
 select cno
 from course
 where cname='信息系统'
)

【例】查询曾与刘晨一同上课的学生学号和姓名。(假设:一个课程只有一个上课班)

select distinct student.sno as 学号, sname as 姓名
from student,sc
where student.sno=sc.sno and cno in (
 select cno
 from sc,student
 where sc.sno=student.sno and student.sno in (
 select sno
 from student
 where student.sname='刘晨'
 )
)
  • 内层in 查出刘晨的学号sno,外层in查出刘晨所上课程的课程号。

(2)带有比较运算符的子查询(=,>=,<=,<>或!=)

【例】查询与王敏同学在同一个系的所有学生信息  (=判断)

select *
from student
where sdept=(
 select sdept
 from student
 where sname='王敏'
)

【例】查询每个学生超过该课程最低分的课程号。(同类课程不是最低分的),子查询的结果返回一个数的时候,这个子查询就可以当一个数用?可以使用in符号,或者大于小于符号。

select cno
from sc a
where grade> (
 select min(grade)
 from sc b
 where a.cno=b.cno
)

【例】查询每个学生超过他选修课程平均成绩的课程号。

select cno
from sc a
where grade> (
 select avg(grade)
 from sc b
 where a.sno=b.sno
)

(3)带有any或all谓词的子查询

  • any表示任何一个,all表示所有,可以用在子查询的括号前面

【例】查询其他系中比计算机系某一学生年龄小的学生姓名,性别、年龄和所在系。

select sname as 姓名,ssex as 性别, sage as 年龄, sdept as 所在系
from student
where sage <(
 select sage
 from student
 where sdept='cs'
);

【例】查询其他系中比计算机系所有年龄都小的学生姓名和年龄。

select sname as 姓名, sage as 年龄
from student
where sdept<>'cs' and sage <all (
 select sage
 from student
 where sdept='cs'
);

(4 )带有exists谓词的子查询

【例】查询所有选修了1号课程的学生姓名。

select sname as 姓名
from student
where exists (
 select *
 from sc
 where cno=1 and sno=student.sno
);

4、集合查询

(1)并union

【例】 查询计算机系的学生及年龄不大于19岁的学生详细信息。

select *
from student
where student.sdept='cs'
union
select *
from student
where student.sage<=19;

(2)交intersect

【例】查询选修了1号课程的与年龄不大于19岁的 学生 详细信息 的交集。

select *
from student,sc
where student.sno=sc.sno and sc.cno=1
intersect
select *
from student
where student.sage<=19;

(3)差except

【例】查询计算机科学系的学生与年龄不大于19岁的学生详细信息的差集。

select *
from student
where student.sdept='sc'
except
select *
from student
where student.sage<=19;

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。如果你想了解更多相关内容请查看下面相关链接