最常用SQL语句----提升与总结

SQL的执行顺序为：
先where 再group 再having 再select 后order.

sql语句解析的顺序的问题：
先where条件过滤出需要的纪录，再对筛选出来的记录分组group加having。接下来就是选取字段的过滤select最后order排序。所以别名只有在select和order by内才可以只用

 

表名和字段

–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数

建表语句

--学生表

spool student.log

prompt
prompt Creating table STUDENT
prompt ======================
prompt
create table JCXT.STUDENT
(
  s_id    VARCHAR2(20) not null,
  s_name  VARCHAR2(20),
  s_birth VARCHAR2(20),
  s_sex   VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


spool off



--课程表

spool course.log

prompt
prompt Creating table COURSE
prompt =====================
prompt
create table JCXT.COURSE
(
  c_id   VARCHAR2(20),
  c_name VARCHAR2(20),
  t_id   VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


spool off

--教师表

spool teacher.log

prompt
prompt Creating table TEACHER
prompt ======================
prompt
create table JCXT.TEACHER
(
  t_id   VARCHAR2(20),
  t_name VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


spool off

--成绩表

spool score.log

prompt
prompt Creating table SCORE
prompt ====================
prompt
create table JCXT.SCORE
(
  s_id    VARCHAR2(20),
  c_id    VARCHAR2(20),
  s_score VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


spool off

测试数据

--插入学生表测试数据
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' , '女');


--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

SQL语句练习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select a.*,b.s_score as score_b,c.s_score as score_c from student a,score b,score c 
where   a.s_id=b.s_id 
        and a.s_id=c.s_id 
        and b.c_id='01' 
        and c.c_id='02' 
        and b.s_score>c.s_score

SQL语句运行后结果

 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select a.*, b.s_score as score_b, c.s_score as score_c
  from student a
  left join score b
    on a.s_id = b.s_id
   and b.c_id = '01'
    or b.c_id = null
  join score c
    on a.s_id = c.s_id
   and c.c_id = '02'
 where b.s_score < c.s_score

SQL语句运行后结果

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

select b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) as score_a
  from student b
  join score a
    on b.s_id = a.s_id
 group by b.s_id, b.s_name
having ROUND(AVG(a.s_score), 2) >= 60

 SQL语句运行后结果

4、--查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
                   (包括有成绩的和无成绩的) 

select a.s_id, a.s_name, round(avg(b.s_score), 2) as score_b
  from student a
  left join score b
    on a.s_id = b.s_id
 group by a.s_id, a.s_name
having round(avg(b.s_score), 2) < 60
union
select a.s_id, a.s_name, 0 as score_b
  from student a
 where a.s_id not in (select distinct s_id from score)

SQL语句运行后结果

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

 select a.s_id,
         a.s_name,
         count(b.c_id) as couse_count,
         sum(b.s_score) as score_count
    from student a
    left join score b
      on a.s_id = b.s_id
   group by a.s_id, a.s_name

 SQL语句运行后结果

6、查询"李"姓老师的数量 

select count(a.t_id) as count_teacher
  from teacher a
 where a.t_name like '李%'

SQL语句运行后结果

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

select a.*
  from student a
  left join score b
    on a.s_id = b.s_id
 where b.c_id in
       (select c_id
          from course
         where t_id = (select t_id from teacher where t_name = '张三'))

 SQL语句运行后结果

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

select c.*
  from student c
 where c.s_id not in
       (select a.s_id
          from student a
          join score b
            on a.s_id = b.s_id
           and b.c_id in (select a.c_id
                            from course a
                            join teacher b
                              on a.t_id = b.t_id
                             and b.t_name = '张三'))

 SQL语句运行后结果

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

select a.*
  from student a, score b, score c
 where a.s_id = b.s_id
   and a.s_id = c.s_id
   and b.c_id = '01'
   and c.c_id = '02'

 SQL语句运行后结果

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

select a.*
  from student a
 where a.s_id in (select s_id from score where c_id = '01')
   and a.s_id not in (select s_id from score where c_id = '02')

 SQL语句运行后结果

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

select s.*
  from student s
 where s.s_id in
       (select a.s_id
          from student a
          left join score b
            on a.s_id = b.s_id
         group by a.s_id
        having count(b.c_id) < (select count(*) from course))

SQL语句运行后结果

 另一种SQL语句写法

select *
  from student t
 where t.s_id not in
       (select s_id
          from score
         group by s_id
        having count(*) = (select count(distinct c_id) from course))

 SQL语句运行后结果

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

select *
  from student
 where s_id in
       (select distinct a.s_id
          from score a
         where a.c_id in (select c_id from score where s_id = '01'))

 SQL语句运行后结果

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

select a.*
  from student a
 where a.s_id in
       (select distinct s_id
          from score
         group by s_id
        having count(*) = (select count(c_id) from score where s_id = '01'))
      --找到学过‘01’同学没学过的课程，有哪些同学，并排除他们
   and a.s_id not in
       (select s_id
          from score
         where c_id in
               (select distinct c_id
                  from score
                 where c_id not in (select c_id from score where s_id = '01'))
         group by s_id)

SQL语句运行结果