oracle 学习(三)


---创建学生表---
create table student(
STU_NO number(10) NOT NULL,
STU_NAME varchar2(20) NOT NULL,
STU_AGE number(10) NOT NULL,
constraint PK_STUDENT PRIMARY KEY(STU_NO)--设为主健
);
---插入数据---
insert into student
   select 1001,'AE','12' from dual
   union
   select 1002,'BT','14' from dual
   union
   select 1003,'AS','20' from dual
   union
   select 1004,'SE','18' from dual;
   commit;

--查询年纪为12的学生姓名--
SELECT STU_NAME FROM STUDENT WHERE STU_AGE='12'

--查询年纪在12至16岁之间的学生姓名--

SELECT STU_NAME FROM STUDENT WHERE STU_AGE BETWEEN '12' and   '16'

 --查询年纪不在12至16岁之间的学生姓名--

SELECT STU_NAME FROM STUDENT WHERE STU_AGE  NOT BETWEEN '12' and   '16'

---查询所有姓名以A开头的学生的姓名;---
SELECT STU_NAME FROM STUDENT WHERE STU_NAME LIKE 'A%'

---列出所有学生年纪的和---
SELECT SUM(STU_AGE) FROM STUDENT
---年纪的平均值---
SELECT avg(STU_AGE) FROM STUDENT
---最大值,最小值,最大值与最小值之间的差值;---
SELECT MAX(STU_AGE) FROM STUDENT
SELECT MIN(STU_AGE) FROM STUDENT
SELECT   MAX(STU_AGE)-MIN(STU_AGE) FROM STUDENT

--- 将所有学生按学号顺序升序排列---
SELECT * FROM STUDENT order by STU_NO

--- 将所有学生按学号顺序降序排列---
SELECT * FROM STUDENT order by STU_NO desc


---创建COURSE表---
create table course(
COU_NO varchar2(10) NOT NULL,
COU_NAME varchar2(20) NOT NULL,
constraint PK_course PRIMARY KEY(COU_NO)--设为主健
);
select * from course

---插入数据---
insert into course
select 'A001','COURSE_NAME' from dual
union
select 'A002','SQLSERVER' from dual
union
select 'A003','JavaEE' from dual
union
select 'A004','.NET' from dual

---创建STUDENT_COURSE表---
 create table STUDENT_COURSE
( stu_no   number(10)  not null,
  course_no  varchar2(10)  not null);


select * from STUDENT_COURSE


---插入数据---
insert into STUDENT_COURSE
select 1001,'A001' from dual
union
select 1001,'A003' from dual
union
select 1004,'A002' from dual
union
select 1004,'A004' from dual
union
select 1004,'A001' from dual
union
select 1003,'A003' from dual
----创建自增长-----

CREATE SEQUENCE STU_NO_SEQ INCREMENT BY 1  START  WITH  1001
MAXVALUE  99999  CYCLE  NOCACHE;
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');
INSERT into STUDENT_COURSE(STU_NO, COURSE_NO) values (STU_NO_SEQ .NEXTVAL, 'A001');

DROP SEQUENCE STU_NO_SEQ

DROP table STUDENT_COURSE
---列出每个学生姓名以及他的学习的所有课程。---
select STU_NAME,COU_NAME  from STUDENT s, course c,STUDENT_COURSE sc where s.stu_no=sc.stu_no and c.cou_no=sc.course_no

---查询出没有选修任何课程的学生的学号和姓名---
select STU_NO,STU_NAME from STUDENT s where s.stu_no not in (select  stu_no from STUDENT_COURSE)

---统计每个学生学习的课程数量并按课程数从大到小排序。(包括没有学习任何课程的学生记录)---
SELECT STU_NAME, COUNT(COU_NO) FROM STUDENT s, course c

select STU_NAME, COUNT(course_no)
    from student s left outer join student_course sc
    on(s.stu_no=sc.stu_no)
    group by s.stu_name
    order by count(course_no) desc;


---查询所有学习JavaEE的学生的学号和姓名---
select STU_NO,STU_NAME from STUDENT s  where  STU_NO IN (
SELECT STU_NO FROM STUDENT_COURSE SC WHERE SC.COURSE_NO IN(
select COU_NO from course c where c.cou_name='JavaEE')) 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值