SQLserver数据库之创建数据库、表、约束、新增查询表数据操作（1）

--创建数据库

USE master
go
if exists(select * from sysdatabases where name='EmployeeSys')
drop database EmployeeSys
go

CREATE DATABASE EmployeeSys
 ON 
(
  NAME = ' EmployeeSys_data',                             --主数据文件的逻辑名
  FILENAME = 'D:\EmployeeSys_data.mdf' ,  --主数据文件的物理名
  SIZE = 10 MB,  --主数据文件初始大小
  FILEGROWTH = 20 % 
)                              
 LOG ON  
(
  NAME = 'EmployeeSys_log', 
  FILENAME = 'D:\EmployeeSys_log.ldf' ,
  SIZE = 3MB, 
  MAXSIZE = 20MB,
  FILEGROWTH = 1MB
)
GO

USE EmployeeSys
go

if exists(select * from sysobjects where name='Employee')
drop table Employee
go
create table Employee
(
    EmpId int identity(1,1) primary key not null,
    EmpName varchar(50) not null,
    Age int not null,
    Sex char(2) not null,
    AddTime datetime not null,
    Wage money not null,
    DepId int not null,
    State int not null 
)

if exists(select * from sysobjects where name='Department')
drop table Department
go
create table Department
(
    DepId int identity(1,1) primary key not null,
    DepName varchar(50) not null
)

if exists(select * from sysobjects where name='Users')
drop table Users
go
create table Users
(
    Uid int identity(1,1) primary key not null,
    UName varchar(50) not null,
    UPwd varchar(50) not null
)

ALTER TABLE Employee --默认约束
ADD CONSTRAINT DF_State DEFAULT ('未删除') FOR State

ALTER TABLE Employee --添加外键约束
ADD CONSTRAINT FK_Department    
    FOREIGN KEY(DepId) REFERENCES Department(DepId)

insert into Department values('人事部');
insert into Department values('产品开发部');
insert into Department values('开发部');
insert into Department values('经理部');
insert into Department values('空运部');

insert into Employee values('张三',23,'男','2011-9-1',5000,1,0);
insert into Employee values('李四',29,'男','2010-6-1',7000,2,1);
insert into Employee values('王五',15,'男','2012-5-1',6000,3,0);
insert into Employee values('李丽',25,'女','2000-3-1',2000,4,1);
insert into Employee values('李宁',18,'女','2011-10-1',4000,5,1);

insert into Users values('admin','admin');

select * from Employee;
select * from Department;
select * from Users;