数据库原理上机实验内容报告代码

--创建数据表

create table stu_info(
stu_id char(10) not null constraint pk_stu_id primary key,--主键
name nvarchar(20) not null,
birthday date null, 

address nvarchar(20) null,
mark int null,
major nvarchar(20) null,
sdept nvarchar(20) null
)

select * from stu_info

create table course_info(
course_id char(3) not null constraint pk_course_id primary key,--主键
course_name nvarchar(20) not null, 
course_type nvarchar(20) null default '考试',
course_mark tinyint NULL,
course_time tinyint NULL,
pre_course_id char(3) null constraint fk_pre_course_id foreign key references
course_info(course_id)--外键
);

select * from course_info;

create table stu_grade(
stu_id char(10) not null constraint fk_stu_id foreign key references stu_info(stu_id),--外键
course_id char(3) not null constraint fk_course_id foreign key references course_info(course_id),--外键
grade tinyint null
);

select * from stu_grade;

--stu_id与course_id合在一起作为主键
alter table stu_grade
add constraint pk_stu_course primary key(stu_id, course_id)

select * from stu_grade;

--修改数据表

alter table stu_info
add code char(18) null

select * from stu_info;

select * from stu_info;

alter table stu_info
drop column code

select * from stu_info;

alter table stu_grade
add constraint ck_grade check(grade between 0 and 100)--check约束，限制输入到一列或多列的值的范围

select * from stu_info;

select * from stu_info;

alter table stu_info
add sex nchar(1) null default'男';

insert into stu_info(stu_id,name,sex,birthday,address,mark,major,sdept)
values('2007070101','张元','男','1985-10-09','河南许昌',576,'计算机科学与技术','信息学院')

    
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070102','张红','女','1985-01-14','河南开封',565,'计算机科学与技术','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070103','王明','男','1985-01-14','河南洛阳',565,'计算机科学与技术','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070104','李伟','男','1985-01-14','河南郑州',565,'计算机科学与技术','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070105','郑澜','女','1985-01-14','河南平顶山',565,'电子商务','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070106','赵恒','男','1985-01-14','河南周口',565,'电子商务','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070107','张兰','女','1985-01-14','河南郑州',565,'电子商务','信息学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070108','李伟','男','1985-01-14','河南安阳',565,'会计学','会计学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070109','钱丽','女','1985-01-14','河南南阳',565,'会计学','会计学院');
insert into stu_info(stu_id,name, sex, birthday, address,mark,major,sdept)
values('2007070110','孙楠','男','1985-01-14','河南许昌',565,'财务管理','会计学院');    

select * from course_info;
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('701','计算机基础','考试',3.50,null)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('702','操作系统','考试',4.50,701)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('703','计算机网络','考试',4.50,701)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('704','数据库原理','考查',3.50,701)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('706','Java','考查',3.40,704)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('801','宏观经济学','考试',4.50,null)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('802','初级会计','考试',4.50,null)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('803','财政学','考试',3.50,null)
insert into course_info(course_id,course_name,course_type,course_mark,pre_course_id) values('804','会计电算化','考查',3.00,null)
    
select * from stu_grade;
insert into stu_grade(stu_id,course_id,grade) values('2007070101','701',89);
insert into stu_grade(stu_id,course_id,grade) values('2007070101','702',81);
insert into stu_grade(stu_id,course_id,grade) values('2007070101','703',96);

insert into stu_grade(stu_id,course_id,grade) values('2007070102','701',85);
insert into stu_grade(stu_id,course_id,grade) values('2007070102','702',74);
insert into stu_grade(stu_id,course_id,grade) values('2007070102','703',77);

insert into stu_grade(stu_id,course_id,grade) values('2007070104','701',91);
insert into stu_grade(stu_id,course_id,grade) values('2007070104','702',88);

insert into stu_grade(stu_id,course_id,grade) values('2007070101','801',79);

insert into stu_grade(stu_id,course_id,grade) values('2007070101','802',91);

insert into stu_grade(stu_id,course_id,grade) values('2007070102','801',87);

insert into stu_grade(stu_id,course_id,grade) values('2007070101','803',75);

insert into stu_grade(stu_id,course_id,grade) values('2007070101','804',82);

--修改表中数据
update stu_info
set sdept='会计学院' where stu_id='2007070102'

select * from stu_info

delete from stu_grade where grade<60

delete from stu_info where sex='男' and sex='女'

--简单的数据查询
--1
select * from stu_info
--2
select * from stu_grade
--3
select * from course_info
--4
select stu_id,name,sdept 
from stu_info
--5
select name,sex,address,sdept
from stu_info
where stu_id='2007070103'
--6
select stu_id as '学号', name as '姓名', sdept as '院系'
from stu_info
where sex='女'
--7
select birthday
from stu_info
--8
select name,sex,address
from stu_info
where address like '%阳%'
--9
select grade    
from stu_grade
where course_id=702 and grade between 70 and 80

--数据汇总
--1
select AVG(mark) as '平均分'
from stu_info
where sdept='信息学院'
--2    
select MAX(mark) as '最高分',MIN(mark) as '最低分'
from stu_info
--3
select COUNT(stu_id) as '会计学院总人数'
from stu_info
where sdept='会计学院'
--4
select sum(grade)
from stu_grade
where stu_id='2007070101'

--GROUP BY分组汇总
--1
select sdept,COUNT(stu_id)as '总人数'
from stu_info
group by sdept
--2
select stu_id,AVG(grade) as '平均分',count(course_id) as '课程数'
from stu_grade
group by stu_id

--ORDER BY排序