--創建數據表

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排序

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的数据库原理上机实验内容报告代码的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。