实验代码

实验一

-- 1 创建数据库S_C
create database S_C;
-- 对S_C数据库进行操作
use S_C;
-- 2 创建Student表
CREATE TABLE Student (
  Sno CHAR(5) NOT NULL UNIQUE,
  Sname CHAR(8) UNIQUE,
  Ssex CHAR(2), 
  Sage INT,
  Sdept CHAR(20));
-- 创建Course表
CREATE TABLE Course (
  Cno CHAR(5) NOT NULL UNIQUE,
  Cname CHAR(20) UNIQUE,
  Cpno CHAR(5),
  Ccredit INT);
-- 创建SC表
CREATE TABLE SC(
  Sno CHAR(5) NOT NULL,
  Cno CHAR(5) NOT NULL,
  Grade INT);
-- 3 修改
ALTER TABLE Student
ADD Scome DATE,
MODIFY Sage smallint,
Drop Index sname;
-- 4 删除
DROP TABLE Student;
DROP DATABASE S_C;

实验室代码：

-- 1 创建数据库S_C
create database S_C;
-- 对S_C数据库进行操作
use S_C;
-- 2 创建Student表
CREATE TABLE Student (
  Sno CHAR(5) NOT NULL UNIQUE,
  Sname CHAR(8) UNIQUE,
  Ssex CHAR(2), 
  Sage INT,
  Sdept CHAR(20));
-- 创建Course表
CREATE TABLE Course (
  Cno CHAR(5) NOT NULL UNIQUE,
  Cname CHAR(20) UNIQUE,
  Cpno CHAR(5),
  Ccredit INT);
-- 创建SC表
CREATE TABLE SC(
  Sno CHAR(5) NOT NULL,
  Cno CHAR(5) NOT NULL,
  Grade INT);
-- 3 修改
ALTER TABLE Student
ADD Scome DATE;
-- 4 删除
DROP TABLE Student;
use master
go
DROP DATABASE S_C;

实验二

-- 1 创建数据库S_C
create database S_C;
-- 对S_C数据库进行操作
use S_C;
-- 2 创建Student表
CREATE TABLE Student (
  Sno CHAR(5) NOT NULL UNIQUE,
  Sname CHAR(8) UNIQUE,
  Ssex CHAR(2), 
  Sage INT,
  Sdept CHAR(20));
-- 创建Course表
CREATE TABLE Course (
  Cno CHAR(5) NOT NULL UNIQUE,
  Cname CHAR(20) UNIQUE,
  Cpno CHAR(5),
  Ccredit INT);
-- 创建SC表
CREATE TABLE SC(
  Sno CHAR(5) NOT NULL,
  Cno CHAR(5) NOT NULL,
  Grade INT);
-- 录入表数据
insert into student values
(95001,'李勇','男',20,'CS(计算机)'),
(95002,'刘晨','女',19,'IS(信息)'),
(95003,'王敏','女',18,'MA(数学)'),
(95004,'张立','男',19,'IS(信息)');
insert into course values
(1,'数据库',5,4),
(2,'数学',null,2),
(3,'信息系统',1,4),
(4,'操作系统',6,3),
(5,'数据结构',7,4),
(6,'数据处理',null,2),
(7,'PASCAL语言',6,4);
insert into sc values
(95001,1,92),
(95001,2,85),
(95001,3,88),
(95002,2,90),
(95002,3,80);
-- 3
SELECT *
FROM  Student;
SELECT Sname,Ssex 
FROM Student 
WHERE Sdept in('is','ma','cs');

实验三

-- 插入
insert into sc(sno,cno) values
('95020','1');
-- 修改
update sc
set grade=0
where 'cs'=(
  select sdept from student    
  where student.sno=sc.sno);
-- 删除
delete
from student
where sno='95019';

实验四

-- 准备阶段
drop table student;
drop table sc;
-- 1.
CREATE TABLE Student(
  sno CHAR(5) NOT NULL UNIQUE,
  sname CHAR(8),
  ssex CHAR(2),
  sage INT,
  sdept CHAR(20),
  CONSTRAINT  PK_Student  PRIMARY KEY(sno));
-- 1.(1)
Insert into student values('95001','啊啊','女',12,'cs');
Insert into student values('95002','啊啊啊啊','女',34,'cs');
Insert into student values('95003','ww','女',12,'cs');
-- 1.(2)
UPDATE Student SET sno='' WHERE sdept='cs';
-- 1.(3)
UPDATE Student SET sno='95002' WHERE sname='啊啊';
-- 2.
CREATE TABLE SC(sno CHAR(5) NOT NULL,
                cno CHAR(5) NOT NULL,  
                grade INT,
                CONSTRAINT K_SC foreign KEY(sno) REFERENCES student(sno));
-- 2.(1)
Insert into sc values('95001','1',78);
Insert into sc values('95002','1',88);
Insert into sc values('95003','1',68);
-- (2)
Insert into sc values('95004','1',68);
-- (3)
Update student set sno='95008' WHERE sno='95002';
-- (4)
Update student set sno='95008'  WHERE  sno='95002';
-- (5)
Delete from student where sno='95003';
-- 3
CREATE TABLE Student1
(sno CHAR(5) CONSTRAINT U1 NOT NULL UNIQUE,
 sname CHAR(8) CONSTRAINT U2 UNIQUE,
 sage INT CONSTRAINT U3 CHECK(sage<=28));
Insert into student1 values('95001','啊啊',18);
Insert into student1 values('95002','哈哈',20);
Update student1 set sname='哈哈'  WHERE sno='95001';
UPDATE  Student1  SET  sno=' '  WHERE  sno='95001';
UPDATE  Student1  SET  sno='95001' WHERE sno='95002';
UPDATE  Student1  SET  sage=56  WHERE  sno='95001';