-----------------------------------------下面会继续更新，这些内容都是在SQL2005上正确运行的--------------------------------------------------------
--先在D盘创建一个“TEST”的文件夹
CREATE DATABASE DB1--数据库名称
ON --定义数据库的数据文件
(NAME = DB1,--逻辑文件名，设置引用时的名称
FILENAME = 'D:\TEST\DB2.MDF'--物理文件名，设置文件在磁盘上的路径和名称
)
---------------------------------------------------------------------------
--先在D盘创建一个TEST的文件夹
CREATE DATABASE DB3
ON
(NAME = DB3, --逻辑文件名
FILENAME = 'D:\TEST\DB3.MDF', --物理文件名
SIZE = 10MB, --文件初始大小
MAXSIZE = 100MB, --文件最大容量
FILEGROWTH = 5% --文件容量增长幅度
)
---------------------------------------------------------------------------
DROP DATABASE DB3 --删除数据库DB3
---------------------------------------------------------------------------
CREATE DATABASE DB4
ON --创建数据库文件
(
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 DB5
MODIFY NAME = DB1--修改数据库名称
---------------------------------------------------------------------------
ALTER DATABASE DB1
ADD FILE (NAME = DB11, FILENAME = 'D:\TEST\DB11.NDF')--添加数据库文件
ALTER DATABASE DB1
ADD LOG FILE (NAME = DB11_LOG, FILENAME = 'D:\TEST\DB11_LOG.LDF') --添加事务日志文件
---------------------------------------------------------------------------
ALTER DATABASE DB1
ADD FILEGROUP FD88 --添加一个文件组，名称为FD88
ALTER DATABASE DB1
MODIFY FILEGROUP FD88 NAME = FD8 --修改文件组FD88的名称为FD8
ALTER DATABASE DB1
--在数据库DB1中增加两个数据文件到文件组FD8中，
--并将该文件组设为默认文件组
ADD FILE(NAME = DB81, FILENAME = 'D:\TEST\DB81.NDF'),
(NAME = DB82, FILENAME = 'D:\TEST\DB82.NDF')
TO FILEGROUP FG8
GO
ALTER 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 XSQK
ON
(
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 SPJ
ON
( 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 PNO
FROM SPJ
WHERE SPJ.SNO IN (SELECT SNO
FROM S
WHERE CITY = '上海');
--找出使用上海产的零件的工程号码
SELECT DISTINCT JNO
FROM SPJ
WHERE SPJ.PNO IN (SELECT PNO
FROM S WHERE CITY = '上海');
--找出没有使用天津产的零件的工程号码
SELECT DISTINCT JNO
FROM SPJ
WHERE SPJ.PNO NOT IN (
SELECT SNO
FROM S
WHERE CITY = '上海');
--把所有的红色零件的颜色改成蓝色
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';
--由S5供给J4的零件P6改为由S3供应
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';
--从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录
DELETE FROM SPJ
WHERE SNO = 'S2';
DELETE
FROM S
WHERE SNO = 'S2';
--先在S表插入S2,否则无法插入行到SPJ中,想想为什么!
INSERT INTO S(SNO)
VALUES ('S2');
INSERT INTO SPJ
VALUES ('S2', 'P4', 'J6', 200);
EXEC SP_HELPDB 'DB1' --指定要查看数据库DB1信息，如不指定，会显示所有数据库的信息
EXEC SP_HELPDB --显示服务器上所有数据库的信息
---------------------------------------------------------------------------
CREATE DATABASE XSQK
ON
(
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 SPJ
ON
( 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 PNO
FROM SPJ
WHERE SPJ.SNO IN (SELECT SNO
FROM S
WHERE CITY = '上海');
--找出使用上海产的零件的工程号码
SELECT DISTINCT JNO
FROM SPJ
WHERE SPJ.PNO IN (SELECT PNO
FROM S WHERE CITY = '上海');
--找出没有使用天津产的零件的工程号码
SELECT DISTINCT JNO
FROM SPJ
WHERE SPJ.PNO NOT IN (
SELECT SNO
FROM S
WHERE CITY = '上海');
--把所有的红色零件的颜色改成蓝色
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';
--由S5供给J4的零件P6改为由S3供应
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';
--从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录
DELETE FROM SPJ
WHERE SNO = 'S2';
DELETE
FROM S
WHERE SNO = 'S2';
--先在S表插入S2,否则无法插入行到SPJ中,想想为什么!
INSERT INTO S(SNO)
VALUES ('S2');
INSERT INTO SPJ
VALUES ('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),--民族
)
