SQL课堂练习20091009

最新推荐文章于 2024-07-27 18:44:10 发布

原创最新推荐文章于 2024-07-27 18:44:10 发布 · 873 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #table #database #schema #null

SQL2005学习日志专栏收录该内容

17 篇文章

订阅专栏

本文介绍了如何使用SQL语句创建一个名为'School'的数据库，包括数据文件和日志文件的配置。接着创建了学生信息表`Stu_info`和奖学金表`Scholarship`，并演示了插入、更新和查询数据的过程。

create database School on ( name = 'SchoolData', filename = 'D:/SchoolData.mdf', size = 20Mb, maxsize = 30Mb, filegrowth = 2Mb ) log on ( name = 'School_Log', filename = 'D:/Scholl_Log.ldf', size = 10Mb, maxsize = 20Mb, filegrowth = 2Mb ) go use School go create Schema Student create table Student.Stu_info ( sno char(5) not null, sname char(8), ssex char(1), sage int, sdept char(20), constraint PK_Stu_info primary key (sno) ) go insert into Student.Stu_info values('10001','王','1',23,'CS') go update Student.Stu_info set sno = '' where sdept = 'CS' go update Student.Stu_info set sno = '08099' where sname = '王' go select * from Stu_info go insert Student.Stu_info values('08099','刘','1',24,'CS') update Student.Stu_info set sno = null where sno = '08099' set xact_abort on begin transaction insert into Student.Stu_info values('08098','李','M',22,'MC'); insert into Student.Stu_info values('08097','成','0',23,'MC'); select * from Student.Stu_info; commit transaction t1 go set xact_abort on begin transaction t1 insert into Student.Stu_info values('08100','李','M',22,'MC'); insert into Student.Stu_info values('08100','成','O',23,'MC'); select * from Student.Stu_info; commit transaction t1 go create table Scholarship ( M_id varchar(10),Stu_id char(10),R_money int ) insert into Scholarship values('0001','70000',5000) insert into Scholarship values('0001','90000',7000) select *from Scholarship alter table Scholarship add constraint PK_Scholarship primary key(M_ID) go alter table Student.Stu_info drop constraint PK_Stu_info go alter table Student.Stu_info add constraint PK_Stu_info primary key(sno) go