DDL(Data Definition Language) and DML(Data Manipulation Language)

001
 USER MASTER ;/*切换到MASTER数据库*/
DROP DATABASE student_course;/*先检测有没有存在student_course数据库，有则删除*/
/*创建student_course数据库，并打开*/
CREATE DATABASE student_course ON
(
          NAME=student_course_data,
          FILENAME='C:\0801110404\080111040420101108\student_course_data.mdf',
          SIZE=10MB,
          FILEGROWTH=15%
     )
LOG ON 
(
          NAME=student_course_log,
          FILENAME='C:\0801110404\080111040420101108\student_course_log.ldf',
          SIZE=3MB,
          FILEGROWTH=15%
);
002
---USE student_course DATABASE-------------------------------------------
USE student_course;
DROP TABLE Student;
DROP TABLE Course;
DROP TABLE SC;
---CREATE Student TABLE---------------------------------------------------
CREATE TABLE Student
(
          Sno CHAR(9)PRIMARY KEY,
          Sname CHAR(20) UNIQUE,
          Ssex CHAR(2),
          Sage SMALLINT,
          Sdept CHAR(20)
);
---CREATE Course TABLE---------------------------------------------------
CREATE TABLE Course
(
          Cno CHAR(4)PRIMARY KEY,
         Cname CHAR(40),
          Cpno CHAR(4),                             /*Cpno表示先修课程*/
          Ccredit SMALLINT,                         /*Cpno表示学分*/
          FOREIGN KEY(Cpno)REFERENCES Course(Cno)/*外码Cpno参照了表Course的Cno*/
);
---CREATE SC TABLE--------------------------------------------------------
CREATE TABLE SC
(
          Sno CHAR(9),
          Cno CHAR(4),
          Grade SMALLINT,
          PRIMARY KEY(Sno,Cno),                    /*Sno是外码，Student是被参照表*/
          FOREIGN KEY(Cno)REFERENCES Course(Cno)/*Cno是外码，Course是被参照表*/
);
 
003
---ALTER 的使用CREATE Student2 TABLE-----------------------------------------
CREATE TABLE Student2
(
          Sno CHAR(9)PRIMARY KEY,
          Sname CHAR(20) UNIQUE,
          Ssex CHAR(2),
          Sage SMALLINT,
          Sdept CHAR(20)
);
SELECT * FROM Student2;
 
---向student2表增加"入学时间列"------------------------------------------------
ALTER TABLE Student2
ADD S_entrance DATETIME;
 
004
---将student表中的年龄的数据类型修改为int类型-----------------------------------
EXEC SP_HELP STUDENT /*查看studnet表属性*/
 
ALTER TABLE Student
ALTER COLUMN Sage INT;
---修改后的年龄数据类型如下，由原来的smallint类型修改为int类型-------------------
 
005
---增加课程名称必须取唯一的约束条件----------------------------------------------
ALTER TABLE Course
ADD UNIQUE(Cname);
EXEC SP_HELPINDEX Course;
 
006
---向student表中插入四条记录---------------------------------------------------
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('200215121','李勇','男',20,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('200215122','刘晨','女',19,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('200215123','王敏','女',18,'MA');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('200215125','张立','男',18,'TS');
SELECT * FROM Student;
 
---向Course表插入记录-------------------------------------------------------
INSERT INTO Course(Cno,Cname,Ccredit)VALUES('2','数学',2);
INSERT INTO Course(Cno,Cname,Ccredit)VALUES('6','数据处理',2);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('4','操作系统','6',3);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('7','PASCAL','6',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('5','数据结构','7',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库','5',4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('3','信息系统','1',4);
SELECT * FROM Course;
 
---向Course表插入记录-------------------------------------------------------
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','1',92);
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','2',85);
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','3',88);
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215122','2',90);
INSERT INTO SC(Sno,Cno,Grade)VALUES('200215122','3',80);
SELECT * FROM SC;
 
007
---例4对每一个系，求学生的平均年龄，并把结果存入数据库。----------------------------
DROP TABLE Dept_age;
CREATE TABLE Dept_age
(
      dept char(15),/*系名*/
      Avg_age SMALLINT/*学生平均年龄*/
);
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,Avg(Sage)
FROM Student
GROUP BY Sdept
SELECT * FROM Dept_age;/*查看每一个系的学生平均年龄结果*/
 
---例5将学生的sno为200215121的sage修改为25------------------------------------
SELECT * FROM student
 
UPDATE student
SET Sage=25
WHERE Sno='200215121';
SELECT * FROM student;
 
---例6将所有学生的年龄（sage）增加一岁----------------------------------------
UPDATE Student
SET Sage=Sage+1;
SELECT * FROM student;
 
008
---例将计算机科学系所有学生的成绩(Grade)设置为0--------------------------------
SELECT * FROM SC;
 
UPDATE SC
SET Grade=0
WHERE 'CS'=
(
SELECT Sdept
FROM Student
         WHERE Student.Sno=SC.Sno
);
SELECT * FROM SC;
 
---向student表插入一条记录-------------------------------------------------
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('200215128','郭靖','男',19,'IS');
SELECT * FROM student WHERE Sno='200215128';
 
---例删除学号（sno）为的学生记录
DELETE 
FROM Student
WHERE Sno='200215128';
SELECT * FROM student
 
---删除计算机科学系所有学生选课记录------------------------------------------
 
DELETE 
FROM SC 
WHERE 'CS'=
(
          SELECT Sdept
          FROM Student
          WHERE Student.Sno=SC.Sno
);