2019/6/20-SQL练习

1、创建学生表

create table student
(
  student_no varchar2(5) not null,
  student_name varchar2(10) not null,
  student_sex varchar2(3) not null,
  student_birthday date,
  student_class varchar2(5)
);
comment on column student.student_no
  is '学号';
comment on column student.student_name
  is '学生姓名';
comment on column student.student_sex
  is '学生性别';
comment on column student.student_birthday
  is '学生出生年月';
comment on column student.student_class
  is '学生所在班级';
alter table student
  add constraint PK_student_no primary key (student_no)

2、创建教师表

create table teacher
(
  t_no       VARCHAR2(3) not null,
  t_name     VARCHAR2(4) not null,
  t_sex      VARCHAR2(2) not null,
  t_birthday DATE,
  prof      VARCHAR2(6),
  depart    VARCHAR2(10) not null
);
comment on table teacher
is '教师表';
comment on column teacher.t_no
  is '教工编号';
comment on column teacher.t_name
  is '教工姓名';
comment on column teacher.t_sex
  is '教工性别';
comment on column teacher.t_birthday
  is '教工出生年月';
comment on column teacher.prof
  is '职称';
comment on column teacher.depart
  is '教工所在部门';
alter table teacher
  add constraint PK_t_no primary key (t_no)

3、 创建课程表

create table course
(
  course_no varchar2(5) not null,
  course_name varchar2(10) not null,
  teacher_no varchar2(5) not null
);
comment on column course.course_no
  is '课程号';
comment on column course.course_name
  is '课程名称';
comment on column course.teacher_no
  is '教师编号';

alter table course
  add constraint pk_course primary key (course_no);
alter table course
  add constraint fk_teacher_no foreign key (teacher_no)
  references teacher (t_no);

4、创建成绩表

create table score
(
  s_no   varchar2(5) not null,
  c_no   varchar2(5) not null,
  degree Number(4,1)
);
comment on table score
  is '成绩表';
comment on column score.s_no
  is '学号';
comment on column score.c_no
  is '课程号';
comment on column score.degree
  is '成绩';
  
alter table score
  add constraint fk_s_no foreign key (s_no)
   references  student (student_no);
alter table score
  add constraint fk_c_no foreign key (c_no)
   references  course (course_no);

5、向学生表插入数据

// 注意：插入数据时，被关联的表的数据要先插入，不然会报：违反完整约束条件 未找到父项关键字
insert into student values('108','曾华','男',to_date ('1977-09-01','yyyy-mm-dd'),'95033');

6、删除名字为曾华的学生信息

delete from student where student_name = '曾华'

7、更新课程号3-105的分数为60分

update score set degree = 60  where c_no = '3-105'

8、修改表名

alter table 表名 rename to 新的名字；

9、新增字段

alter table 表名 add 新字段 字段的类型；

10、修改字段类型

alter table 表名  modify  字段  字段的新类型

11、修改字段

alter table 表名  change 旧得字段  新的字段 字段的数据类型；

12、查询Student表中的所有记录的name、sex和Class列。

select t.student_name,t.student_sex,t.student_class from student t;

13、查询教师所有的单位即不重复的Depart列。

select distinct t.depart from teacher t;

14、作用于多列实际上是对性别和所在部门这两个字段去重，distinct必须放在前面，不然会报错

select distinct t.t_sex,t.depart from teacher t;

15、算出不同部门的数量

select count（distinct t.depart） as 部门数量 from teacher t;

16、算出性别和部门都不同的数量

select count(*) from (select distinct t_sex,depart from teacher) M;

17、查询Student表的所有记录。

select * from student;

18、查询Score表中成绩在60到80之间的所有记录。

select * from score where degree > 60 and degree < 80;

19、查询Score表中成绩为60，81或88的记录。

select * from score where degree in (60,81,88);
select * from score where degree = 60 or degree = 81 or degree = 88;

20、查询Student表中“95031”班或性别为“女”的同学记录。

select * from student where student_class = '95031' or student_sex = '女';

21、以Class降序查询Student表的所有记录。

select * from student order by student_class desc;

22、以no升序查询Student表的所有记录。

select * from student order by student_no asc;

23、以Cno升序、Degree降序查询Score表的所有记录。

select * from score order by c_no asc,degree desc;

24、查询“95031”班的学生人数。group by 查询字段一定不能是*，而是某一个列或者某个列的聚合函数

select count(*) from (select * from student where student_class = 95031) t;
select student_class,count(1) as 人数 from student where student_class ='95031' group by student_class;
select student_class,count(student_no) as 人数 from student where student_class = '95031' group by student_class