Java教程

常用sql 语句

本文主要是介绍常用sql 语句,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

  三张表 学生信息、 课程表、分数表

  创表语句

  -- 学生信息

  create table t0_student

  (

  studentid varchar(10),

  classid varchar(10),

  name varchar(20),

  sex varchar(1)

  );

  comment on column t0_student.studentid is '学生编码';

  comment on column t0_student.classid is '学生班级 1 班级1 ,2 班级2';

  comment on column t0_student.name is '学生姓名';

  comment on column t0_student.sex is '学生性别,1 男,2 女';

  alter table t0_student add constraint pk_t0_student primary key (studentid,classid)

  -- 课程表

  create table to_course

  (

  courseid varchar(10),

  coursename varchar(10)

  );

  comment on column to_course.courseid is '课程编码,1,2,3,4,5';

  comment on column to_course.coursename is '课程名称 1英语,2语文,3数学,4物理,5化学';

  alter table to_course add constraint pk_to_course primary key (courseid);

  -- 分数表

  create table to_score

  (

  courseid varchar(10),

  classid varchar(10),

  studentid varchar(10),

  score number

  );

  comment on column to_score.courseid is '课程编码';

  comment on column to_score.classid is '班级编码';

  comment on column to_score.studentid is '学生编码';

  comment on column to_score.score is '分数';

  alter table to_score add constraint pk_to_score primary key (courseid,classid,studentid);

  insert into t0_student values('1','1','张三','1');

  insert into t0_student values('2','1','李四','2');

  insert into t0_student values('3','1','王五','1');

  insert into t0_student values('4','1','赵六','2');

  insert into t0_student values('5','2','张三','1');

  insert into t0_student values('6','2','李四','2');

  insert into t0_student values('7','2','王五','1');

  insert into t0_student values('8','2','赵六','2');

  insert into t0_student values('9','1','jackMa','1');

  insert into to_course values('1','英语');

  insert into to_course values('2','语文');

  insert into to_course values('3','数学');

  insert into to_course values('4','物理');

  insert into to_course values('5','化学');

  insert into to_score values('1','1','1',50);

  insert into to_score values('1','2','1',70);

  insert into to_score values('2','1','2',100);

  insert into to_score values('2','2','2',100);

  insert into to_score values('3','1','3',90);

  insert into to_score values('3','2','1',70);

  insert into to_score values('4','1','4',80);

  insert into to_score values('4','2','1',60);

  insert into to_score values('5','1','4',90);

  insert into to_score values('4','2','5',80);

  insert into to_score values('3','2','5',80);

  insert into to_score values('2','2','5',80);

  insert into to_score values('1','2','5',80);

  insert into to_score values('5','2','6',75);

  insert into to_score values('4','2','6',60);

  insert into to_score values('3','2','6',60);

  insert into to_score values('2','2','6',60);

  insert into to_score values('1','2','6',90);

  /**********************sql 语句 *********************/

  --- 2班张三分数列表

  select student.name, decode(student.sex,'1','男','2','女') sex,course.coursename,score.score

  from to_score score, t0_student student, to_course course

  where student.studentid='5' and student.classid='2'

  and score.courseid=course.courseid

  and student.studentid=score.studentid

  and student.classid=score.classid

  --- 2班 成绩行转列方式展示

  with t as(

  select student.name, decode(student.sex,'1','男','2','女') sex,course.coursename,score.score

  from to_score score, t0_student student, to_course course

  where score.courseid=course.courseid

  and student.studentid=score.studentid

  and student.classid=score.classid and student.classid='2' )

  select * from(select * from t

  )

  pivot (sum(score) for coursename in ('英语','语文','数学','物理','化学'));

  --- 查询分数大于80分 的人数

  select count(distinct studentid) from to_score

  where score>80

  --查询有两科成绩大于 80分记录数

  select studentid,score,count(*) from to_score

  where score>=80

  group by studentid,score

  having count(*)>2

  --- 查询所有的英语成绩 按成绩 由高往低排序

  select * from to_score where courseid='1' order by score desc

  -- 查询 所有后英语成绩的总分,最高分,最低分,平均分(保留两位小数)

  select sum(score) 总分,max(score) 最高分,min(score) 最低分,round(sum(score)/count(*),2) 平均分 from to_score where courseid='1'

  -- 查询所有成绩 优秀人数 (>=90),良好人数(80-90),及格人数(60-80), 不及格人数(<60)

  select suM(case

  when score>=90 then 1

  else 0 end ) 优秀人数,

  suM( case

  when score >=80 and score

  when score >=60 and score

  else 0 end) 良好人数,

  suM(case

  when score >=60 and score

  else 0 end) 及格人数,

  suM( case

  when score

  else 0 end) 不及格人数

  from to_score

  ---查询90分以上各分数人数。rollup 统计总人次

  select score ,count(*) from to_score where score >=90 group by rollup(score)

  --- 删除名字重复的 学生 (如果没有studentid 这样的主键列的时候 可以通过rownum 增加一个虚拟列然后再删除)

  delete t0_student

  where studentid in(select studentid from (

  select max(studentid) studentid ,name from t0_student group by name ))

  --查询为非汉字的学生名字

  select * from t0_student where asciistr(name) not like '%\%';

  --随机抽取成绩中10% 数据

  select * from to_score sample block(10);

这篇关于常用sql 语句的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!