第一次sql语句的小记

• sql语句的学习

--数据库的创建 
Create DataBase 学生数据库
ON primary(
name = 'Students_data.mdf' ,
FILENAME  = 'D:\Data\Students_data.mdf'
)
log on(
name = 'Students_log.ldf' ,
FILENAME  = 'D:\Data\Students_log.ldf'
)
--student表的创建
Create Table Student(
Sno CHAR(7) Primary key ,
Sname CHAR(10) ,
Ssex CHAR(2),
Sage tinyint,
Sdept varchar(20),
Sid CHAR(10),
Sdate date
)
--Student表数据的插入
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values
	('0811101','李勇','男',21,'计算机系'),
	('0811102','刘晨','男',20,'计算机系'),
	('0811103','王敏','女',20,'计算机系'),
	('0811104','张小红','女',19,'计算机系'),
	('0821101','张立','男',20,'信息管理系'),
	('0821102','吴宾','女',19,'信息管理系'),
	('0821103','张海','男',20,'信息管理系'),
	('0831101','钱小平','女',21,'通信工程系'),
	('0831102','王大力','男',20,'通信工程系'),
	('0831103','张姗姗','女',19,'通信工程系')

--course表的创建
Create Table Course(
Cno CHAR(10) Primary key ,
Cname varchar(20),
Credit int,
Semester smallint 
) 
--Course表数据的插入
insert into Course(Cno,Cname,Credit,Semester) values
	('C001','高等数学',4,1),
	('C002','大学英语',3,1),
	('C003','大学英语',3,2),
	('C004','计算机文化学',2,2),
	('C005','VB',2,3),
	('C006','数据库基础',4,5),
	('C007','数据结构',4,4),
	('C008','计算机网络',4,4)


--SC表的创建
Create Table SC(
Sno CHAR(7),
Cno CHAR(10),
Grade smallint,
Primary key(Sno,Cno),
FOREIGN key (Sno) REFERENCES Student (Sno),
FOREIGN key (Cno) REFERENCES Course (Cno)
)
--SC表数据的插入
insert into SC(Sno,Cno,Grade) values
	('0811101','C001',96),('0811101','C002',80),('0811101','C003',84),
	('0811101','C005',62),('0811102','C001',92),('0811102','C002',90),
('0811102','C004',84),('0821102','C001',76),('0821102','C004',85),
('0821102','C005',73),('0821102','C007',NULL),('0821103','C001',50),
	('0821103','C004',80),('0831101','C001',50),('0831101','C004',80),
	('0831102','C007',NULL),('0831103','C004',78),('0831103','C005',65),
	('0831103','C007',NULL)

--Teacher表的创建
Create Table Teacher(
Tno CHAR(8),
Tname CHAr(10),
Salary numeric(6,2) 
--定点小数
)

--Teacher表数据的插入
insert into Teacher(Tno,Tname,Salary) values
	('T001','张美霞','5000'),
	('T002','王洪林','5500'),
	('T003','李丽芬','4800'),
	('T004','周良水','6000'),
	('T005','吴翔','7000')


Alter Table Teacher ADD  Title nchar(4) ;
Alter Table Course Alter column Credit tinyint ;
Alter Table Student Drop Column Sid,Sdate  ;
Alter Table Teacher ADD PRIMARY KEY(Tno) ;