create table Student
(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char (2),
Sage smallint,
Sdept char(20)
);
create table Course
(Cno char(4) primary key,
Cname char(40) not NULL,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references Course(Cno)
);
create table SC
(Sno char(9),
Cno char(4),
Grade smallint,
primary key (Sno ,Cno),
foreign key(Sno)references Student (Sno),
foreign key(Cno)references Course(Cno)
);
select * from Student
insert into Student
values('201215121','李勇','男','20','CS'),('201215122','刘晨','女','19','CS'),('201215123','王敏','女
','18','MA'),('201215125','张立','男','19','IS');
insert into Course
values('1','数据库','5','4');--无先行课错误
insert into Course
values('2','数学',NULL,'2'),('6','数据处理',NULL,'2');
insert into Course
values('4','操作系统','6','3'),('7','PASCAL 语言','6','4');
insert into Course
values('5','数据结构','7','4');
insert into Course
values('1','数据库','5','4');
insert into Course
values('3','信息系统','1','4');
insert into SC
values('201215121','1','92'),('201215121','2','85'),('201215121','3','88'),('201215122','2','90'),('2
01215122','3','80');
insert into Student
values('201215126','张伟','男','20','CS'),('201215127','王伟','男','20','CS'),('201215128','王芳','男
','20','CS'),('201215129','王勇','男','20','CS'),
('201215131',' 李 伟 ',' 男 ','20','CS'),('201215132',' 张 敏 ',' 男 ','20','CS'),('201215133',' 张 静 ',' 男
','20','CS'),('201215134','张勇','男','20','CS'),
('201215135','王秀英','男','19','MA'),('201215136','李静','男','19','MA'),('201215137','李敏','男
','19','MA'),('201215138','王艳','男','19','MA'),
('201215139','李秀英','男','19','MA'),('201215140','张丽','男','19','MA'),('201215142','李杰','男
','19','MA'),
('201215143',' 李 娜 ',' 女 ','18','IS'),('201215144',' 王 静 ',' 女 ','18','IS'),('201215145',' 王 磊 ',' 女
','18','IS'),('201215146','王强','女','18','IS'),
('201215147',' 张 秀 英 ',' 女 ','18','IS'),('201215148',' 王 丽 ',' 女 ','18','IS'),('201215149',' 李 军 ',' 女
','18','IS'),('201215150','王军','女','18','IS'),
('201215151',' 刘 伟 ',' 女 ','18','IS'),('201215152',' 李 强 ',' 女 ','18','IS'),('201215153',' 刘 洋 ',' 女
','18','IS'),('201215154','张杰','女','18','IS');
insert into Course
values('8','思想道德与法律基础',NULL,'3'),('9','中国近代史纲要',NULL,'3'),('10','马克思主义基
本原理',NULL,'3'),('11','毛泽东思想与中国特色社会主义理论概述',NULL,'5'),
('12','形式与政策',NULL,'2'),('13','军事理论',NULL,'3'),('14','大学体育',NULL,'2'),('15','大学英语
',NULL,'3'),
('16','大学语文',NULL,'4'),('17','大学生职业规划',NULL,'2'),('18','创业基础',NULL,'2'),('19','艺术
导论',NULL,'2'),
('20','高等数学',NULL,'5'),('21','计算机科学导论',NULL,'3'),('22','普通物理',NULL,'3'),('23','普通
物理实验',NULL,'1');
insert into Course values
('24','线性代数','20','4'),('25','离散数学','20','4'),('26','概率统计','20','4'),('27','数字电路与逻辑设
计','22','3'),
('28','数字电路与逻辑设计实验','23','1'),('29','算法设计与分析','5','4'),('30','算法设计与分析实
验','5','1');
insert into Course values('31','运筹学','24','3'),('32','通信原理',NULL,'3'),('33','',NULL,''),('34','计
算机英语','15','3');
insert into Course values('35','数学模型','31','3');
insert into SC
values('201215126','1','92'),('201215126','8','93'),('201215126','9','96'),('201215126','10','62'),('
201215126','11','77'),
('201215127','12','93'),('201215127','13','96'),('201215127','14','91'),('201215128','15','62'),('20
1215128','16','78');
insert into SC
values('201215128','1','92'),('201215128','2','93'),('201215128','3','96'),('201215128','4','62'),('2
01215128','5','77');
('201215129','1','92'),('201215129','2','94'),('201215129','3','91'),('201215129','4','92'),('201215
129','5','57'),
('201215131','1','92'),('201215131','2','90'),('201215131','3','76'),('201215131','4','24'),('201215
131','5','45'),
('201215132','7','88'),('201215132','8','88'),('201215132','9','83'),('201215132','10','84'),('20121
5132','1','85');
('201215133','7','89'),('201215133','8','89'),('201215133','9','88'),('201215133','10','85'),('20121
5133','1','83'),
('201215134','7','87'),('201215134','8','82'),('201215134','9','82'),('201215134','10','87'),('20121
5134','1','83'),
('201215135','7','31'),('201215135','8','96'),('201215135','9','92'),('201215135','10','91'),('20121
5135','1','32'),
('201215136','13','98'),('201215136','14','96'),('201215136','15','99'),('201215136','16','98'),('20
1215136','17','99'),
('201215137','13','92'),('201215137','14','97'),('201215137','15','94'),('201215137','16','96'),('20
1215137','17','92'),
('201215138','13','86'),('201215138','14','96'),('201215138','15','97'),('201215138','16','98'),('20
1215138','17','95'),
('201215139','13','96'),('201215139','14','95'),('201215139','15','94'),('201215139','16','96'),('20
1215139','17','99'),
('201215140','13','86'),('201215140','14','98'),('201215140','15','94'),('201215140','16','94'),('20
1215140','17','99');
insert into SC
values
('201215142','18','86'),('201215142','19','98'),('201215142','20','94'),('201215142','21','94'),('20
1215142','22','99'),
('201215143','18','86'),('201215143','19','98'),('201215143','20','94'),('201215143','21','94'),('20
1215143','22','99'),
('201215144','18','86'),('201215144','19','98'),('201215144','20','94'),('201215144','21','94'),('20
1215144','22','99');
insert into SC
values
('201215145','20','86'),('201215145','24','98'),('201215145','26','94'),('201215145','22','94'),('20
1215145','27','99'),
('201215146','20','86'),('201215146','24','98'),('201215146','26','94'),('201215146','22','94'),('20
1215146','27','99'),
('201215147','20','86'),('201215147','24','98'),('201215147','26','94'),('201215147','22','94'),('20
1215147','27','99'),
('201215148','20','86'),('201215148','24','98'),('201215148','26','94'),('201215148','22','94'),('20
1215148','27','99');
insert into SC
values
('201215149','5','86'),('201215149','23','98'),('201215149','28','94'),('201215149','29','94'),('201
215149','30','99'),
('201215150','5','98'),('201215150','23','97'),('201215150','28','96'),('201215150','29','95'),('201
215150','30','94'),
('201215151','5','81'),('201215151','23','91'),('201215151','28','92'),('201215151','29','94'),('201
215151','30','94'),
('201215152','5','85'),('201215152','23','97'),('201215152','28','96'),('201215152','29','92'),('201
215152','30','99');
--修改基本表
alter table Student add S_entrance date;
alter table Student alter column Sage int;
alter table Course add unique(Cname);
--Student 表建立视图
--报错：必须是批处理中仅有的语句 单独执行就行了
create view IS_Student
as
select Sno,Sname,Sage
from Student
Where Sdept = 'IS';
--建立索引 默认升序
create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index SCno on SC(Sno ASC,Cno DESC);
--书上错误
alter index SCno rename to SCSno;
select Sname, Sno, Sdept
from Student
--选择全部
select *
from Student
--select 也可以是表达式
select Sname, 2014-Sage birth
from Student
--也可以是函数和字符串常量
select Sname, 'Year of Birth', 2014-Sage, lower(Sdept)
from Student
--distinct 去掉重复行 默认是 all 保留重复
select distinct Sno
from SC
select distinct Sno
from SC
where Grade < 60
--between 包括边界
select Sname, Sdept, Sage
from Student
where Sage between 19 and 23
--确定集合
select Sname,Ssex
from Student
where Sdept in('CS','MA');
--%任意长度
select Sname
from Student
where Sname like'刘%';
-- _一个字符
select Sname
from Student
where Sname like'_洋%';
--is 不能用 = 代替
select Sno, Cno
from SC
where Grade is NULL；
