员工管理数据库的创建

/*创建数据库*/
CREATE DATABASE YGGL
ON
(NAME= 'YGGL_Data',
FILENAME='D:\STUDY\SQL Server\data\YGGL.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%)
LOG ON
(NAME='YGGL_Log',
FILENAME='D:\STUDY\SQL Server\data\YGGL_Log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)
GO
/*创建表Employees */
USE YGGL
CREATE TABLE Employees
(Employeeid char(6) not null,
Name char(10) not null,
Brithday datetime not null,
Sex bit not null,
Address char(20) null,
Zip char(6) null,
Phonenumber char(12) null,
Emailaddress char(20) null,
Departmentid char(3) not null
)


/*创建表Departments：*/
CREATE TABLE Departments
(
Departmentid char(3) not null,
Departmentname char(20) not null,
Note text
)
/*创建表Salary:*/
CREATE TABLE Salary
(
Employeeid char(6) not null,
Income float not null,
Outcome float not null
)
/*查看表中所有的记录*/

USE YGGL
SELECT * FROM Employees
SELECT * FROM Departments
SELECT * FROM salary

/*增加主键*/
USE YGGL
ALTER TABLE Employees
ADD CONSTRAINT Pk_Employees
PRIMARY KEY (Employeeid)
/*增加主键*/
ALTER TABLE Departments
ADD CONSTRAINT Pk_Departments
PRIMARY KEY (Departmentid)
/*增加主键*/
ALTER TABLE salary
ADD CONSTRAINT Pk_salary
PRIMARY KEY (Employeeid)

/*在建好的Employees表中添加外键DepartmentID，引用Departments表的DepartmentID列（注意建立外键引用的表以及引用列必须存在）*/
USE YGGL
ALTER TABLE Employees
ADD CONSTRAINT fk_DepratmentID
FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID)

/*删除表*/
DROP TABLE Employees
DROP TABLE Salary
DROP TABLE Departments

/*创建表Departments：*/
CREATE TABLE Departments
(
Departmentid char(3) not null PRIMARY KEY,
Departmentname char(20) not null,
Note text
)
/*创建表Employees*/
CREATE TABLE Employees
(Employeeid char(6) not null PRIMARY KEY,
Name char(10) not null,
Brithday datetime not null,
Sex bit not null,
ADDress char(20) null,
Zip char(6) null,
Phonenumber char(12) null,
Emailaddress char(20) null,
Departmentid char(3) not null FOREIGN KEY REFERENCES Departments(Departmentid)
)
/*创建表Salary:*/
CREATE TABLE Salary
(
Employeeid char(6) not null PRIMARY KEY,
Income float not null,
Outcome float not null)

/*删除主键*/
ALTER TABLE salary
DROP CONSTRAINT PK__Salary__25869641

/*删除外键*/
ALTER TABLE Employees
DROP CONSTRAINT FK__Employees__Depar__239E4DCF