-----------------------------------------下面会继续更新，这些内容都是在SQL2005上正确运行的----------------------------------------------------------先在D盘创建一个“TEST”的文件夹CREATE DATABASE DB1--数据库名称ON --定义数据库的数据文件(NAME = DB1,--逻辑文件名，设置引用时的名称 FILENAME = 'D:\TEST\DB2.MDF'--物理文件名，设置文件在磁盘上的路径和名称)-----------------------------------------------------------------------------先在D盘创建一个TEST的文件夹CREATE DATABASE DB3ON(NAME = DB3, --逻辑文件名 FILENAME = 'D:\TEST\DB3.MDF', --物理文件名 SIZE = 10MB, --文件初始大小 MAXSIZE = 100MB, --文件最大容量FILEGROWTH = 5% --文件容量增长幅度)---------------------------------------------------------------------------DROP DATABASE DB3 --删除数据库DB3---------------------------------------------------------------------------CREATE DATABASE DB4ON --创建数据库文件( NAME = DB4, FILENAME = 'D:\TEST\DB4.MDF', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 5%)LOG ON --创建数据库日志文件 ( NAME = DB4_LOG, FILENAME = 'D:\TEST\DB4_LOG.LDF')---------------------------------------------------------------------------ALTER DATABASE DB5MODIFY NAME = DB1--修改数据库名称---------------------------------------------------------------------------
ALTER DATABASE DB1ADD FILE (NAME = DB11, FILENAME = 'D:\TEST\DB11.NDF')--添加数据库文件ALTER DATABASE DB1ADD LOG FILE (NAME = DB11_LOG, FILENAME = 'D:\TEST\DB11_LOG.LDF') --添加事务日志文件
---------------------------------------------------------------------------ALTER DATABASE DB1ADD FILEGROUP FD88 --添加一个文件组，名称为FD88ALTER DATABASE DB1MODIFY FILEGROUP FD88 NAME = FD8 --修改文件组FD88的名称为FD8ALTER DATABASE DB1--在数据库DB1中增加两个数据文件到文件组FD8中，--并将该文件组设为默认文件组 ADD FILE(NAME = DB81, FILENAME = 'D:\TEST\DB81.NDF'), (NAME = DB82, FILENAME = 'D:\TEST\DB82.NDF') TO FILEGROUP FG8 GOALTER DATABASE DB1 MODIFY FILEGROUP FD8 DEFAULT --设FD8文件组为默认文件组 ALTER DATABASE DB1 --修改数据库文件DB11的名称为DDD MODIFY FILE (NAME = DB11, NEWNAME = DDD, FILENAME = 'D:\TEST\DDD.NDF')-----------------------------------------------------------------------------EXEC SP_HELPDB 'DB1' --指定要查看数据库DB1信息，如不指定，会显示所有数据库的信息EXEC SP_HELPDB --显示服务器上所有数据库的信息---------------------------------------------------------------------------CREATE DATABASE XSQKON( NAME = XSQK, FILENAME = 'D:\TEST\XSQK.MDF', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 5%)LOG ON( NAME = XSQK_LOG, FILENAME = 'D:\TEST\XSQK_LOG.LDF')-----------------------------------------------------------------------------创建数据库SPJ----------------------------------------------CREATE DATABASE SPJON( NAME = SPJ, FILENAME = 'D:\TEST\SPJ.MDF')LOG ON( NAME = SPJ_LOG, FILENAME = 'D:\TEST\SPJ_LOG.LDF')USE SPJ;--创建s表----------------------------------------------CREATE TABLE S( SNO CHAR(5) PRIMARY KEY, SNAME CHAR(20), STATUS INT, CITY CHAR(10) DEFAULT '北京');--创建P表----------------------------------------------CREATE TABLE P( PNO CHAR(5) PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(4), WEIGHT INT);--创建P表----------------------------------------------CREATE TABLE J( JNO CHAR(5) PRIMARY KEY, JNAME CHAR(20), CITY CHAR(10)DEFAULT '北京');CREATE TABLE SPJ( SNO CHAR(5) FOREIGN KEY REFERENCES S(SNO), PNO CHAR(5) FOREIGN KEY REFERENCES P(PNO), JNO CHAR(5) FOREIGN KEY REFERENCES J(JNO), QTY INT); --找出上海厂商供应的所有零件的名称及其数量SELECT PNOFROM SPJWHERE SPJ.SNO IN (SELECT SNO FROM S WHERE CITY = '上海');--找出使用上海产的零件的工程号码SELECT DISTINCT JNOFROM SPJWHERE SPJ.PNO IN (SELECT PNO FROM S WHERE CITY = '上海');--找出没有使用天津产的零件的工程号码SELECT DISTINCT JNOFROM SPJWHERE SPJ.PNO NOT IN ( SELECT SNO FROM S WHERE CITY = '上海');--把所有的红色零件的颜色改成蓝色UPDATE PSET COLOR = '蓝'WHERE COLOR = '红';--由S5供给J4的零件P6改为由S3供应UPDATE SPJSET SNO = 'S3'WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';--从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录DELETE FROM SPJWHERE SNO = 'S2';DELETEFROM SWHERE SNO = 'S2';--先在S表插入S2,否则无法插入行到SPJ中,想想为什么!INSERT INTO S(SNO)VALUES ('S2');INSERT INTO SPJVALUES ('S2', 'P4', 'J6', 200);EXEC SP_HELPDB 'DB1' --指定要查看数据库DB1信息，如不指定，会显示所有数据库的信息EXEC SP_HELPDB --显示服务器上所有数据库的信息---------------------------------------------------------------------------CREATE DATABASE XSQKON( NAME = XSQK, FILENAME = 'D:\TEST\XSQK.MDF', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 5%)LOG ON( NAME = XSQK_LOG, FILENAME = 'D:\TEST\XSQK_LOG.LDF')-----------------------------------------------------------------------------创建数据库SPJ----------------------------------------------CREATE DATABASE SPJON( NAME = SPJ, FILENAME = 'D:\TEST\SPJ.MDF')LOG ON( NAME = SPJ_LOG, FILENAME = 'D:\TEST\SPJ_LOG.LDF')USE SPJ;--创建s表----------------------------------------------CREATE TABLE S( SNO CHAR(5) PRIMARY KEY, SNAME CHAR(20), STATUS INT, CITY CHAR(10) DEFAULT '北京');--创建P表----------------------------------------------CREATE TABLE P( PNO CHAR(5) PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(4), WEIGHT INT);--创建P表----------------------------------------------CREATE TABLE J( JNO CHAR(5) PRIMARY KEY, JNAME CHAR(20), CITY CHAR(10)DEFAULT '北京');CREATE TABLE SPJ( SNO CHAR(5) FOREIGN KEY REFERENCES S(SNO), PNO CHAR(5) FOREIGN KEY REFERENCES P(PNO), JNO CHAR(5) FOREIGN KEY REFERENCES J(JNO), QTY INT); --找出上海厂商供应的所有零件的名称及其数量SELECT PNOFROM SPJWHERE SPJ.SNO IN (SELECT SNO FROM S WHERE CITY = '上海');--找出使用上海产的零件的工程号码SELECT DISTINCT JNOFROM SPJWHERE SPJ.PNO IN (SELECT PNO FROM S WHERE CITY = '上海');--找出没有使用天津产的零件的工程号码SELECT DISTINCT JNOFROM SPJWHERE SPJ.PNO NOT IN ( SELECT SNO FROM S WHERE CITY = '上海');--把所有的红色零件的颜色改成蓝色UPDATE PSET COLOR = '蓝'WHERE COLOR = '红';--由S5供给J4的零件P6改为由S3供应UPDATE SPJSET SNO = 'S3'WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';--从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录DELETE FROM SPJWHERE SNO = 'S2';DELETEFROM SWHERE SNO = 'S2';--先在S表插入S2,否则无法插入行到SPJ中,想想为什么!INSERT INTO S(SNO)VALUES ('S2');INSERT INTO SPJVALUES ('S2', 'P4', 'J6', 200);CREATE TABLE Student( Sno char(10) PRIMARY KEY,--学号 Sname char(10) UNIQUE, --姓名 Ssex char(2), --性别 Sage INT CHECK(Sage >= 10 AND Sage <= 30),--年龄 Birthday CHAR(10) CHECK(BirthDay LIKE '____-__-__'),--出生日期 Hometown CHAR(10), --家乡 Enrolldate CHAR(10) CHECK(Enrolldate LIKE '____-__-__'),--入学日期 DormNo INT, --寝室号 BuildingNO INT, --几号楼 Phone CHAR(10), --电话号码 Nationality CHAR(10),--民族 )
