数据库的三层(表示层也成为会话层;逻辑业务层;数据访问层)在数据库的学习中起着至关重要的作用,今天通过编写一个小程序(QQ)来了解数据库三层的实现过程。
开发工具是VS2013和SQLServer2014
首先建立数据库保存个人信息:
IF DB_ID(‘MYQQ’) IS NOT NULL
DROP DATABASE MYQQ
CREATE DATABASE MYQQ
ON
(
NAME=MYQQ_DBT,
FILENAME=’J:\SQL\MYQQ_DBT.MDF’,
SIZE=10,
FILEGROWTH=10
)
LOG ON
(
NAME=MYQQ_LOG,
FILENAME=’J:\SQL\MYQQ_LOG.LDF’,
SIZE=5,
FILEGROWTH=5
)
USE MYQQ
CREATE TABLE Users
(
id INT NOT NULL IDENTITY(1000,1),
LoginPwd VARCHAR(50) NOT NULL,
FriendshipPolicyid INT NOT NULL DEFAULT (0),
NickName VARCHAR(50) NOT NULL,
Faceid INT NOT NULL ,
Sex VARCHAR(50) NOT NULL,
Age INT NOT NULL ,
Name VARCHAR(50) NULL,
Starid INT NULL,
BloodTypeid INT NULL
)
CREATE TABLE BloodType
(
id INT NOT NULL,
BloodType VARCHAR(50) NOT NULL
)
CREATE TABLE Friends
(
id INT NOT NULL,
Hostid INT NOT NULL,
Friendid INT NOT NULL
)
CREATE TABLE FriendshipPolicy
(
id INT NOT NULL,
FriendshipPolicy VARCHAR(50) NOT NULL
)
CREATE TABLE [Messages]
(
id INT NOT NULL IDENTITY ,
FromUserid INT NOT NULL,
ToUserid INT NOT NULL,
[Message] VARCHAR(1000) NULL,
MessageTypeid INT NOT NULL,
MessageState INT NOT NULL,
MessageTime DATETIME NOT NULL
)
CREATE TABLE MessageType
(
id INT NOT NULL,
MessageType VARCHAR(50) NOT NULL
)
CREATE TABLE Star
(
id INT NOT NULL,
Star VARCHAR(50) NOT NULL
)
ALTER TABLE BloodType
ADD CONSTRAINT PK1_ID PRIMARY KEY (id)
ALTER TABLE FriendshipPolicy
ADD CONSTRAINT PK2_ID PRIMARY KEY (id)
ALTER TABLE MessageType
ADD CONSTRAINT PK3_ID PRIMARY KEY (id)
ALTER TABLE [Messages]
ADD CONSTRAINT FK7_ID FOREIGN KEY (MessageTypeid) REFERENCES MessageType (id)
ALTER TABLE Star
ADD CONSTRAINT PK4_ID PRIMARY KEY (id)
ALTER TABLE Users
ADD CONSTRAINT PK_ID PRIMARY KEY (id)
ALTER TABLE Users
ADD CONSTRAINT FK1_ID FOREIGN KEY (Starid) REFERENCES Star(id)
ALTER TABLE Users
ADD CONSTRAINT FK2_ID FOREIGN KEY (BloodTypeid) REFERENCES BloodType(id)
ALTER TABLE Users
ADD CONSTRAINT FK8_ID FOREIGN KEY(FriendshipPolicyid) REFERENCES FriendshipPolicy(id)
ALTER TABLE [Messages]
ADD CONSTRAINT FK5_ID FOREIGN KEY (FromUserid) REFERENCES Users (id)
ALTER TABLE [Messages]
ADD CONSTRAINT FK6_ID FOREIGN KEY (ToUserid) REFERENCES Users (id)
SELECT * FROM Users
SELECT * FROM Friends
SELECT * FROM FriendshipPolicy
SELECT * FROM [Messages]
SELECT * FROM MessageType
SELECT * FROM Star
SELECT * FROM BloodType
DROP TABLE Friends
DROP TABLE [Messages]
DROP TABLE Users
DROP TABLE BloodType
DROP TABLE Star
DROP TABLE MessageType
DROP TABLE FriendshipPolicy
INSERT INTO Star
SELECT 1,’白羊座’ UNION
SELECT 2,’水瓶座’ UNION
SELECT 3,’巨蟹座’ UNION
SELECT 4,’射手座’ UNION