首页
运维教程
Linux基础
系统服务
系统架构
数据库
shell脚本
虚拟化
大数据
DevOps
企业案例
运维开发
python
go语言
运维安全
行业资讯
网络基础
系统安全
运维面试
学习路线
学习方法
面试题库
职场解惑
常用软件
运维软件
办公软件
书籍资源
日知录
招聘信息
读书计划
智码微课
关于我们
首页 运维教程数据库运维必须掌握的SQL语句

运维必须掌握的SQL语句

1.学生表Student(SID,Sname,Sage,Ssex)

  • SID 学生编号,
  • Sname 学生姓名,
  • Sage 出生年月,
  • Ssex 学生性别

2.课程表:Course(CID,Cname,TID)

  • CID 课程编号,
  • Cname 课程名称,
  • TID 教师编号

3.教师表:Teacher(TID,Tname)

  • TID 教师编号
  • Tname 教师姓名

4.成绩表:SC(SID,CID,score)

  • SID 学生编号,
  • CID 课程编号,
  • score 分数

创建数据库:tdb

create database tdb;

添加测试数据

1.学生表

创建表

create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

向表插入数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

2.课程表

创建表

create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));

向表插入数据

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

3.教师表

创建表

create table Teacher(TID varchar(10),Tname nvarchar(10));

向表插入数据

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

4.成绩表

创建表

create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));

向表插入数据

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数
1.1、查询同时存在”01″课程和”02″课程的情况

select a.* , b.score 课程01的分数,c.score 课程02的分数
from Student a , SC b , SC c
where
a.SID = b.SID
and a.SID = c.SID
and b.CID = ’01’
and c.CID = ’02’
and b.score > c.score

—-1.2、查询同时存在”01″课程和”02″课程的情况和存在”01″课程但可能不存在”02″课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a
left join SC b on a.SID = b.SID and b.CID = ’01’
left join SC c on a.SID = c.SID and c.CID = ’02’
where b.score is not null

–3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.SID

–4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩–4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
运行时间 1>2>3,推荐使用第三种方法
法一

SELECT s.*
from student s
where s.SID NOT IN(select SID from sc)

法二

SELECT s.*
from student s
LEFT JOIN sc a ON s.SID = a.SID
WHERE a.SID is NULL

法三

SELECT s.*
from student s
WHERE
(SELECT COUNT(1) FROM sc a WHERE s.SID = a.SID) = 0

–5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
—-5.1、查询所有有成绩的SQL。

select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a , SC b
where a.SID = b.SID
group by a.SID
order by a.SID

—-5.2、查询所有(包括有成绩和无成绩)的SQL。

select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a left join SC b
on a.SID = b.SID
group by a.SID
order by a.SID

–6、查询”李”姓老师的数量

–方法1
select count(Tname) 李姓老师的数量 from Teacher where Tname like ‘李%’

–方法2
select count(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = ‘李’

–7、查询学过”张三”老师授课的同学的信息

select distinct Student.*
from Student , SC , Course , Teacher
where Student.SID = SC.SID
and SC.CID = Course.CID
and Course.TID = Teacher.TID
and Teacher.Tname = ‘张三’
order by Student.SID

–8、查询没学过”张三”老师授课的同学的信息

select m.*
from Student m
where SID not in (
select distinct SC.SID
from SC , Course , Teacher
where SC.CID = Course.CID
and Course.TID = Teacher.TID
and Teacher.Tname = ‘张三’)
order by m.SID

–9、查询学过编号为”01″并且也学过编号为”02″的课程的同学的信息

–方法1
select Student.*
from Student , SC
where Student.SID = SC.SID
and SC.CID = ’01’
and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = ’02’)
order by Student.SID

–方法2

select m.*
from Student m
where SID in(
select SID from(
select distinct SID from SC where CID = ’01’
union all
select distinct SID from SC where CID = ’02’) t
group by SID having count(1) = 2
)
order by m.SID

–10、查询学过编号为”01″但是没有学过编号为”02″的课程的同学的信息

–方法1
select Student.*
from Student , SC
where Student.SID = SC.SID
and SC.CID = ’01’
and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = ’02’)
order by Student.SID

–方法2
select Student.*
from Student , SC
where Student.SID = SC.SID
and SC.CID = ’01’
and Student.SID not in (Select SC_2.SID from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = ’02’)
order by Student.SID

–11、查询没有学全所有课程的同学的信息

SELECT s.*,COUNT(a.CID)
FROM student s
LEFT JOIN sc a ON s.SID = a.SID
GROUP BY s.SID
HAVING COUNT(a.CID)<(SELECT COUNT(CID) FROM course)

–12、查询至少有一门课与学号为”01″的同学所学相同的同学的信息

select distinct Student.*
from Student , SC
where Student.SID = SC.SID
and SC.CID in (select CID from SC where SID = ’01’)
and Student.SID <> ’01’

–13、查询和”01″号的同学学习的课程完全相同的其他同学的信息

SELECT tt.SID FROM(
SELECT s.SID ,GROUP_CONCAT(s.CID separator ‘,’) AS cc
FROM(SELECT * FROM sc ORDER BY SID,CID ) s
GROUP BY s.SID) tt
WHERE tt.cc =(
SELECT GROUP_CONCAT(a.CID separator ‘,’)
FROM (SELECT * FROM sc WHERE SID=’01’ ORDER BY SID,CID) a )

–14、查询没学过”张三”老师讲授的任一门课程的学生姓名

select student.*
from student
where student.SID not in
(select distinct sc.SID
from sc , course , teacher
where sc.CID = course.CID and course.TID = teacher.TID and teacher.tname = ‘张三’)
order by student.SID

–15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

select student.SID , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc
where student.SID = SC.SID and student.SID in (select SID from SC where score < 60 group by SID having count(1) >= 2)
group by student.SID , student.sname

–16、检索”01″课程分数小于60,按分数降序排列的学生信息

select student.* , sc.CID , sc.score from student , sc
where student.SID = SC.SID and sc.score < 60 and sc.CID = ’01’
order by sc.score desc

–17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩–17.1 SQL 2000 静态

select a.SID 学生编号 , a.Sname 学生姓名 ,
max(case c.Cname when ‘语文’ then b.score else null end) 语文 ,
max(case c.Cname when ‘数学’ then b.score else null end) 数学 ,
max(case c.Cname when ‘英语’ then b.score else null end) 英语 ,
cast(SUM(b.score)/(SELECT COUNT(1) FROM course) as decimal(18,2)) 平均分
from Student a
left join SC b on a.SID = b.SID
left join Course c on b.CID = c.CID
group by a.SID , a.Sname
order by 平均分 desc

本文链接:http://www.geekyunwei.com/1642.html

网友评论comments

发表评论

邮箱地址不会被公开。 必填项已用*标注

暂无评论

Copyright © 2021 极客运维 备案号: 京ICP备2021004281号
扫二维码
扫二维码
返回顶部