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'))