320
SQL從基礎到高級
-----------------------------------------下麵會繼續更新，這些內容都是在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),--民族
)
最後更新：2017-04-02 15:15:07